+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)
+}