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

diff --git a/main/db.go b/main/db.go
new file mode 100644
index 0000000..0f7bf2b
--- /dev/null
+++ b/main/db.go
@@ -0,0 +1,209 @@
+package main
+
+import (
+  "database/sql"
+  "fmt"
+  _ "github.com/lib/pq"
+  "strconv"
+  "sync"
+)
+
+// ---------------------------------------------------------------------------
+type Book struct {
+  Id             int
+  Age            string  // recommended age, e.g. "beginner", "junior", "ya" (Young Adult), "adult"
+  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"
+  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)
+  LCC            string  // Library of Congress Classification
+  SeriesName     string  
+  Title          string
+  Volume         string
+}
+
+// ---------------------------------------------------------------------------
+type Field string
+const (
+  Author, Series, Title Field = "aut", "ser", "tit"
+)
+
+func (f Field) String() string {
+  return string(f)
+}
+
+// ---------------------------------------------------------------------------
+type SearchTerm struct {
+  Attribute Field
+  Text      string
+}
+
+var g_db *sql.DB = nil
+var g_mutex = &sync.Mutex{}
+
+// ============================================================================
+func dbShutdown() {
+  if nil != g_db {
+    g_db.Close()
+  }
+}
+
+func getDb() (*sql.DB) {
+  if nil == g_db {
+    g_mutex.Lock()
+    defer g_mutex.Unlock()
+    if nil == g_db {
+      config := getConfig()
+      g_db = openDb(config.user, config.pass, config.dbName)
+    }
+  }
+
+  return g_db
+}
+
+func nsVal(ns sql.NullString) string {
+  if ns.Valid {
+    return ns.String
+  }
+  return ""
+}
+
+func openDb(user, pass, dbName string) (*sql.DB) {
+  db, err := sql.Open("postgres","user=" + user + " password=" + pass + " dbname=" + dbName + " sslmode=disable")
+  if nil != err {
+    report("Error:  DB arguments incorrect?", err)
+    return nil
+  }
+
+  err = db.Ping()
+  if nil != err {
+    report("Error:  could not connect to DB.", err)
+    db.Close()
+    return nil
+  }
+
+  return 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
+            FROM Authors a 
+            INNER JOIN Books b ON a.id=b.author
+            LEFT OUTER JOIN Classifications c ON c.id=b.classification
+            LEFT OUTER JOIN Series s ON s.id=b.series
+            WHERE b.id=$1`
+
+  ps, err := getDb().Prepare(query)
+  if nil != err {
+    report("Error:  failed to prepare statement:  " + query, err)
+    return nil
+  }
+  defer ps.Close()
+
+  var count int = 0
+  for _, id := range ids {
+    if 0 != id {
+      count++
+    }
+  }
+    
+  res := make([]Book, count)
+  
+  count = 0
+  for _, id := range ids {
+    if 0 == id {
+      continue
+    }
+
+    row := ps.QueryRow(id)
+
+    var age, grouping, reading, sort, ddc, description, genre, lcc, name, title, volume sql.NullString
+
+    err = row.Scan(&age, &grouping, &reading, &sort, &ddc, &description, &genre, &lcc, &name, &title, &volume)
+    if err != nil {
+      report("Error:  Failed to read book:" + strconv.Itoa(id) + ":", err)
+    } else {
+      var b Book
+      b.Id = id
+      b.Age = nsVal(age)
+      b.AuthorGrouping = nsVal(grouping)
+      b.AuthorReading = nsVal(reading)
+      b.AuthorSort = nsVal(sort)
+      b.DDC = nsVal(ddc)
+      b.Description = nsVal(description)
+      b.Genre = nsVal(genre)
+      b.LCC = nsVal(lcc)
+      b.SeriesName = nsVal(name)
+      b.Title = nsVal(title)
+      b.Volume = nsVal(volume)
+
+      res[count] = b
+      count++
+    }
+  }
+
+  if count < len(res) {
+    res = res[:count]
+  }
+
+  return res
+}
+
+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"
+
+  args := make([]interface{}, len(criteria))
+
+  for i, criterion := range criteria {
+    if 0 == i {
+      query += " WHERE "
+    } else {
+      query += " AND "
+    }
+    switch criterion.Attribute {
+    case Author:
+      query += " a.grouping LIKE $" + strconv.Itoa(i + 1) 
+    case Series:
+      query += " s.descr LIKE $" + strconv.Itoa(i + 1)
+    case Title:
+      query += " b.title LIKE $" + strconv.Itoa(i + 1)
+    default:
+      report("Error:  unrecognized search field in queryIds():  " + criterion.Attribute.String(), nil)
+      return nil
+    }
+    args[i] = criterion.Text
+  }
+
+  res := []int{}
+
+  ps, err := getDb().Prepare(query)
+  if nil != err {
+    report("Failed to Prepare query:  " + query, err)
+    return nil
+  }
+  defer ps.Close()
+
+  var rows *sql.Rows
+  rows, err = ps.Query(args...)
+  if nil != err {
+    report("Failed to execute query:  " + query, err)
+    return nil
+  }
+  defer rows.Close()
+
+  for rows.Next(); rows.Next(); {
+    var id int
+    rows.Scan(&id)
+    res = append(res, id)
+  }
+
+  return res
+}
+
+func report(msg string, err error) {
+  fmt.Println("Error:  " + msg, err)
+}