"database/sql"
"fmt"
_ "github.com/lib/pq"
+ "strings"
"strconv"
"sync"
)
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
// ---------------------------------------------------------------------------
type Field string
const (
- Author, Series, Title Field = "aut", "ser", "tit"
+ Author, Language, List, Series, Sort, Title Field = "aut", "lan", "lst", "ser", "srt", "tit"
)
func (f Field) String() string {
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()
g_mutex.Lock()
defer g_mutex.Unlock()
if nil == g_db {
- config := getConfig()
+ config := GetConfig()
g_db = openDb(config.user, config.pass, config.dbName)
}
}
}
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
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 {
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)
}
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" +
+ " 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)
+ conjunction := "WHERE"
+ count := 0
+ sort := ByAuthor
- for i, criterion := range criteria {
- if 0 == i {
- query += " WHERE "
- } else {
- query += " AND "
- }
+ for _, criterion := range criteria {
switch criterion.Attribute {
case Author:
- query += " a.grouping LIKE $" + 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:
+ 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 += conjunction + " UPPER(l.code) IN ("
+
+ for j, code := range codes {
+ if j > 0 {
+ query += ","
+ }
+ query += "UPPER($" + strconv.Itoa(count + j) + ")"
+ args = append(args, code)
+ }
+ query += ")"
+ count += len(codes) - 1
case Series:
- query += " s.descr LIKE $" + 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 += " b.title LIKE $" + 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
}
- args[i] = criterion.Text
}
- query += " ORDER BY 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{}