From 78b4a26e251479d066150f268e11024c7201956e Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Sun, 23 Jan 2022 23:24:36 -0500 Subject: [PATCH] Add ability to select sort order --- app/index.html | 15 ++++++-- js/src/SearchController.js | 13 +++---- main/db.go | 73 +++++++++++++++++++++++++++----------- main/handler.go | 2 +- 4 files changed, 73 insertions(+), 30 deletions(-) diff --git a/app/index.html b/app/index.html index faa558c..af1c79d 100644 --- a/app/index.html +++ b/app/index.html @@ -5,15 +5,24 @@ - - + +

diff --git a/js/src/SearchController.js b/js/src/SearchController.js index a02d27b..557fc1a 100644 --- a/js/src/SearchController.js +++ b/js/src/SearchController.js @@ -5,7 +5,8 @@ var SearchController = (function () { var my = {}, booksModel = undefined; - const terms = ['aut', 'lan', 'lst', 'ser', 'tit']; + const textFields = ['aut', 'lan', 'ser', 'tit']; + const terms = textFields.concat(['lst', 'srt']); // ============== // Public methods @@ -13,8 +14,8 @@ var SearchController = (function () { my.init = function(linkedBooksModel) { booksModel = linkedBooksModel; - for (var idx in terms) { - addEnterListener(terms[idx]); + for (var idx in textFields) { + addEnterListener(textFields[idx]); } }; @@ -80,11 +81,11 @@ var SearchController = (function () { else { url += '&'; } - if (term === 'lst') { - url += term + '=' + encodeURIComponent('' + value); + if (textFields.includes(term)) { + url += term + '=' + encodeURIComponent('%' + value + '%'); } else { - url += term + '=' + encodeURIComponent('%' + value + '%'); + url += term + '=' + encodeURIComponent('' + value); } } } diff --git a/main/db.go b/main/db.go index 53feb90..fbc67c8 100644 --- a/main/db.go +++ b/main/db.go @@ -30,7 +30,7 @@ type Book struct { // --------------------------------------------------------------------------- type Field string const ( - Author, Language, List, Series, Title Field = "aut", "lan", "lst", "ser", "tit" + Author, Language, List, Series, Sort, Title Field = "aut", "lan", "lst", "ser", "srt", "tit" ) func (f Field) String() string { @@ -43,10 +43,29 @@ type SearchTerm struct { Text string } +// --------------------------------------------------------------------------- +type SortOrder string +const ( + ByArrival, ByAuthor, ByPublication, ByTitle SortOrder = "arr", "aut", "pub", "tit" +) + +func (so SortOrder) String() string { + return string(so) +} + +// --------------------------------------------------------------------------- var g_db *sql.DB = nil var g_mutex = &sync.Mutex{} // ============================================================================ +func conditional(count int) (string, int) { + if (count == 0) { + return "WHERE", 1 + } else { + return "AND", (count + 1) + } +} + func dbShutdown() { if nil != g_db { g_db.Close() @@ -190,54 +209,68 @@ func queryIds(criteria []SearchTerm) []int { " INNER JOIN Authors a ON a.id=b.author" + " LEFT OUTER JOIN Series s ON s.id=b.series" + " LEFT OUTER JOIN Lists_Books lb ON b.id=lb.book" + - " LEFT OUTER JOIN Lists l ON l.id=lb.list" + " LEFT OUTER JOIN Lists l ON l.id=lb.list" + + " " args := make([]interface{}, 0) - - i := 0 + conjunction := "WHERE" + count := 0 + sort := ByAuthor for _, criterion := range criteria { - if 0 == i { - query += " WHERE " - } else { - query += " AND " - } - switch criterion.Attribute { case Author: - query += " UPPER(a.grouping) LIKE UPPER($" + strconv.Itoa(i + 1) + ")" + conjunction, count = conditional(count) + query += conjunction + " UPPER(a.grouping) LIKE UPPER($" + strconv.Itoa(count) + ")" args = append(args, strings.Replace(criterion.Text, " ", "", -1)) case Language: - query += " UPPER(b.language) LIKE UPPER($" + strconv.Itoa(i + 1) + ")" + conjunction, count = conditional(count) + query += conjunction + " UPPER(b.language) LIKE UPPER($" + strconv.Itoa(count) + ")" args = append(args, criterion.Text) case List: + conjunction, count = conditional(count) codes := strings.Split(criterion.Text, ",") - query += " UPPER(l.code) IN (" + query += conjunction + " UPPER(l.code) IN (" for j, code := range codes { if j > 0 { query += "," } - query += "UPPER($" + strconv.Itoa(i + j + 1) + ")" + query += "UPPER($" + strconv.Itoa(count + j + 1) + ")" args = append(args, code) } query += ")" - i += len(codes) - 1 + count += len(codes) - 1 case Series: - query += " UPPER(s.descr) LIKE UPPER($" + strconv.Itoa(i + 1) + ")" + conjunction, count = conditional(count) + query += conjunction + " UPPER(s.descr) LIKE UPPER($" + strconv.Itoa(count) + ")" args = append(args, criterion.Text) + case Sort: + sort = SortOrder(criterion.Text) case Title: - query += " UPPER(b.title) LIKE UPPER($" + strconv.Itoa(i + 1) + ")" + conjunction, count = conditional(count) + query += conjunction + " UPPER(b.title) LIKE UPPER($" + strconv.Itoa(count) + ")" args = append(args, criterion.Text) default: report("Error: unrecognized search field in queryIds(): " + criterion.Attribute.String(), nil) return nil } - - i++ } - query += " ORDER BY a.grouping,s.descr,b.volume,b.title,b.path" + switch sort { + case ByArrival: + query += " ORDER BY b.arrived DESC,a.grouping,s.descr,b.volume,b.title,b.path" + case ByAuthor: + query += " ORDER BY a.grouping,s.descr,b.volume,b.title,b.path,b.arrived DESC" + case ByPublication: + report("Error: cannot sort by publication (not yet implemented)", nil) + return nil + case ByTitle: + query += " ORDER BY b.title,a.grouping,s.descr,b.volume,b.path,b.arrived DESC" + default: + report("Error: unrecognized sort order in queryIds(): " + sort.String(), nil) + return nil + } res := []int{} diff --git a/main/handler.go b/main/handler.go index 18366b9..bdd6410 100644 --- a/main/handler.go +++ b/main/handler.go @@ -190,7 +190,7 @@ func handleInfo(w http.ResponseWriter, r *http.Request) { func handleSearch(w http.ResponseWriter, r *http.Request) { var err error - fields := []Field{Author, Language, List, Series, Title} + fields := []Field{Author, Language, List, Series, Sort, Title} terms := make([]SearchTerm, len(fields)) -- 2.30.2