X-Git-Url: http://jaekl.net/gitweb/?p=quanweb.git;a=blobdiff_plain;f=main%2Fdb.go;h=21c6818d8c5e6ad23998d30cd5ce10b35a881b94;hp=0f7bf2b9379386611ed6cc6feec81175025eff45;hb=23d77d1681abb3361f68c56bf0a78481ea479248;hpb=d4b5c2903e7b0c2267aa7bfdef514a3d1e447de3

diff --git a/main/db.go b/main/db.go
index 0f7bf2b..21c6818 100644
--- a/main/db.go
+++ b/main/db.go
@@ -15,6 +15,7 @@ type Book struct {
   AuthorGrouping string  // unique rendering of the author's name, used for internal grouping 
   AuthorReading  string	 // reading order of author's name, e.g. "Charles Dickens"
   AuthorSort     string  // sort order of author's name, e.g. "Dickens, Charles"
+  CoverId        int     // index into EFS table for cover, if there is one
   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)
@@ -63,6 +64,13 @@ func getDb() (*sql.DB) {
   return g_db
 }
 
+func niVal(ni sql.NullInt64) int {
+  if ni.Valid {
+    return int(ni.Int64)
+  }
+  return 0
+}
+
 func nsVal(ns sql.NullString) string {
   if ns.Valid {
     return ns.String
@@ -88,7 +96,7 @@ func openDb(user, pass, dbName string) (*sql.DB) {
 }
 
 func queryBooksByIds(ids []int) []Book {
-  query := `SELECT s.age,a.grouping,a.reading,a.sort,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,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
@@ -120,8 +128,9 @@ func queryBooksByIds(ids []int) []Book {
     row := ps.QueryRow(id)
 
     var age, grouping, reading, sort, ddc, description, genre, lcc, name, title, volume sql.NullString
+    var cover sql.NullInt64
 
-    err = row.Scan(&age, &grouping, &reading, &sort, &ddc, &description, &genre, &lcc, &name, &title, &volume)
+    err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &lcc, &name, &title, &volume)
     if err != nil {
       report("Error:  Failed to read book:" + strconv.Itoa(id) + ":", err)
     } else {
@@ -131,6 +140,7 @@ func queryBooksByIds(ids []int) []Book {
       b.AuthorGrouping = nsVal(grouping)
       b.AuthorReading = nsVal(reading)
       b.AuthorSort = nsVal(sort)
+      b.CoverId = niVal(cover)
       b.DDC = nsVal(ddc)
       b.Description = nsVal(description)
       b.Genre = nsVal(genre)
@@ -151,10 +161,33 @@ func queryBooksByIds(ids []int) []Book {
   return res
 }
 
+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 {
+  fmt.Println("queryIds():", criteria)
+
   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" 
 
   args := make([]interface{}, len(criteria))
 
@@ -178,6 +211,8 @@ func queryIds(criteria []SearchTerm) []int {
     args[i] = criterion.Text
   }
 
+  query += " ORDER BY b.path"
+
   res := []int{}
 
   ps, err := getDb().Prepare(query)
@@ -195,7 +230,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)
@@ -204,6 +239,27 @@ func queryIds(criteria []SearchTerm) []int {
   return res
 }
 
+func queryMimeTypeByEfsId(efsId int) string {
+  const query = "SELECT mimeType FROM Efs WHERE id=$1"
+
+  ps, err := getDb().Prepare(query)
+  if nil != err {
+    report("Failed to Prepare query:  " + query, err)
+    return ""
+  }
+  defer ps.Close()
+
+  row := ps.QueryRow(efsId)
+  var mimeType sql.NullString
+  err = row.Scan(&mimeType)
+  if nil != err {
+    report(fmt.Sprintf("Failed to retrieve mimeType for id %v: ", efsId), err)
+    return ""
+  }
+  
+  return nsVal(mimeType)
+}
+
 func report(msg string, err error) {
   fmt.Println("Error:  " + msg, err)
 }