Add support to query books on specified lists (of award winners).

[quanweb.git] / main / db.go

diff --git a/main/db.go b/main/db.go
index fa34828d5504562bea5837ff03917b05f8fe49a0..53feb90908e7e6fc3d33bb5d579c602a1a06a238 100644 (file)
--- a/main/db.go
+++ b/main/db.go
@@ -4,6 +4,7 @@ import (
   "database/sql"
   "fmt"
   _ "github.com/lib/pq"
+  "strings"
   "strconv"
   "sync"
 )
@@ -19,6 +20,7 @@ type Book struct {
   DDC            string  // Dewey Decimal Classification
   Description    string  // Back cover / inside flap blurb, describing the book
   Genre          string  // e.g. "adventure", "historical", "mystery", "romance", "sf" (Science Fiction)
+  Language       string
   LCC            string  // Library of Congress Classification
   SeriesName     string  
   Title          string
@@ -28,7 +30,7 @@ type Book struct {
 // ---------------------------------------------------------------------------
 type Field string
 const (
-  Author, Series, Title Field = "aut", "ser", "tit"
+  Author, Language, List, Series, Title Field = "aut", "lan", "lst", "ser", "tit"
 )
 
 func (f Field) String() string {
@@ -56,7 +58,7 @@ func getDb() (*sql.DB) {
     g_mutex.Lock()
     defer g_mutex.Unlock()
     if nil == g_db {
-      config := getConfig()
+      config := GetConfig()
       g_db = openDb(config.user, config.pass, config.dbName)
     }
   }
@@ -96,7 +98,7 @@ func openDb(user, pass, dbName string) (*sql.DB) {
 }
 
 func queryBooksByIds(ids []int) []Book {
-  query := `SELECT s.age,a.grouping,a.reading,a.sort,b.cover,c.ddc,b.description,s.genre,c.lcc,s.descr,b.title,b.volume
+  query := `SELECT s.age,a.grouping,a.reading,a.sort,b.cover,c.ddc,b.description,s.genre,b.language,c.lcc,s.descr,b.title,b.volume
             FROM Authors a 
             INNER JOIN Books b ON a.id=b.author
             LEFT OUTER JOIN Classifications c ON c.id=b.classification
@@ -127,10 +129,10 @@ func queryBooksByIds(ids []int) []Book {
 
     row := ps.QueryRow(id)
 
-    var age, grouping, reading, sort, ddc, description, genre, lcc, name, title, volume sql.NullString
+    var age, grouping, reading, sort, ddc, description, genre, language, lcc, name, title, volume sql.NullString
     var cover sql.NullInt64
 
-    err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &lcc, &name, &title, &volume)
+    err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &language, &lcc, &name, &title, &volume)
     if err != nil {
       report("Error:  Failed to read book:" + strconv.Itoa(id) + ":", err)
     } else {
@@ -144,6 +146,7 @@ func queryBooksByIds(ids []int) []Book {
       b.DDC = nsVal(ddc)
       b.Description = nsVal(description)
       b.Genre = nsVal(genre)
+      b.Language = nsVal(language)
       b.LCC = nsVal(lcc)
       b.SeriesName = nsVal(name)
       b.Title = nsVal(title)
@@ -161,35 +164,81 @@ func queryBooksByIds(ids []int) []Book {
   return res
 }
 
-func queryIds(criteria []SearchTerm) []int {
-  fmt.Println("queryIds():", criteria)
+func queryBookPathById(id int) (string) {
+  query := "SELECT b.path FROM Books b WHERE b.id=$1"
+
+  ps, err := getDb().Prepare(query)
+  if nil != err {
+    report("Failed to Prepare query:  " + query, err)
+    return ""
+  }
+  defer ps.Close()
 
+  row := ps.QueryRow(id)
+  var path sql.NullString
+  err = row.Scan(&path)
+  if nil != err {
+    report(fmt.Sprintf("Failed to retrieve path for book id %v: ", id), err)
+    return ""
+  }
+
+  return nsVal(path)
+}
+
+func queryIds(criteria []SearchTerm) []int {
   query := "SELECT b.id FROM Books b" +
            " INNER JOIN Authors a ON a.id=b.author" +
-           " LEFT OUTER JOIN Series s ON s.id=b.series"
+           " 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"
 
-  args := make([]interface{}, len(criteria))
+  args := make([]interface{}, 0)
 
-  for i, criterion := range criteria {
+  i := 0
+
+  for _, criterion := range criteria {
     if 0 == i {
       query += " WHERE "
     } else {
       query += " AND "
     }
+
     switch criterion.Attribute {
     case Author:
-      query += " a.grouping LIKE $" + strconv.Itoa(i + 1) 
+      query += " UPPER(a.grouping) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
+      args = append(args, strings.Replace(criterion.Text, " ", "", -1))
+    case Language:
+      query += " UPPER(b.language) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
+      args = append(args, criterion.Text)
+    case List:
+      codes := strings.Split(criterion.Text, ",")
+      query += " UPPER(l.code) IN ("
+
+      for j, code := range codes {
+        if j > 0 {
+          query += ","
+        }
+        query += "UPPER($" + strconv.Itoa(i + j + 1) + ")"
+        args = append(args, code)
+      }
+      query += ")"
+      i += len(codes) - 1
     case Series:
-      query += " s.descr LIKE $" + strconv.Itoa(i + 1)
+      query += " UPPER(s.descr) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
+      args = append(args, criterion.Text)
     case Title:
-      query += " b.title LIKE $" + strconv.Itoa(i + 1)
+      query += " UPPER(b.title) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
+      args = append(args, criterion.Text)
     default:
       report("Error:  unrecognized search field in queryIds():  " + criterion.Attribute.String(), nil)
       return nil
     }
-    args[i] = criterion.Text
+
+    i++
   }
 
+  query += " ORDER BY a.grouping,s.descr,b.volume,b.title,b.path"
+
   res := []int{}
 
   ps, err := getDb().Prepare(query)
@@ -207,7 +256,7 @@ func queryIds(criteria []SearchTerm) []int {
   }
   defer rows.Close()
 
-  for rows.Next(); rows.Next(); {
+  for rows.Next() {
     var id int
     rows.Scan(&id)
     res = append(res, id)