11 // ---------------------------------------------------------------------------
14 Age string // recommended age, e.g. "beginner", "junior", "ya" (Young Adult), "adult"
15 AuthorGrouping string // unique rendering of the author's name, used for internal grouping
16 AuthorReading string // reading order of author's name, e.g. "Charles Dickens"
17 AuthorSort string // sort order of author's name, e.g. "Dickens, Charles"
18 CoverId int // index into EFS table for cover, if there is one
19 DDC string // Dewey Decimal Classification
20 Description string // Back cover / inside flap blurb, describing the book
21 Genre string // e.g. "adventure", "historical", "mystery", "romance", "sf" (Science Fiction)
22 LCC string // Library of Congress Classification
28 // ---------------------------------------------------------------------------
31 Author, Series, Title Field = "aut", "ser", "tit"
34 func (f Field) String() string {
38 // ---------------------------------------------------------------------------
39 type SearchTerm struct {
44 var g_db *sql.DB = nil
45 var g_mutex = &sync.Mutex{}
47 // ============================================================================
54 func getDb() (*sql.DB) {
57 defer g_mutex.Unlock()
60 g_db = openDb(config.user, config.pass, config.dbName)
67 func niVal(ni sql.NullInt64) int {
74 func nsVal(ns sql.NullString) string {
81 func openDb(user, pass, dbName string) (*sql.DB) {
82 db, err := sql.Open("postgres","user=" + user + " password=" + pass + " dbname=" + dbName + " sslmode=disable")
84 report("Error: DB arguments incorrect?", err)
90 report("Error: could not connect to DB.", err)
98 func queryBooksByIds(ids []int) []Book {
99 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
101 INNER JOIN Books b ON a.id=b.author
102 LEFT OUTER JOIN Classifications c ON c.id=b.classification
103 LEFT OUTER JOIN Series s ON s.id=b.series
106 ps, err := getDb().Prepare(query)
108 report("Error: failed to prepare statement: " + query, err)
114 for _, id := range ids {
120 res := make([]Book, count)
123 for _, id := range ids {
128 row := ps.QueryRow(id)
130 var age, grouping, reading, sort, ddc, description, genre, lcc, name, title, volume sql.NullString
131 var cover sql.NullInt64
133 err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &lcc, &name, &title, &volume)
135 report("Error: Failed to read book:" + strconv.Itoa(id) + ":", err)
140 b.AuthorGrouping = nsVal(grouping)
141 b.AuthorReading = nsVal(reading)
142 b.AuthorSort = nsVal(sort)
143 b.CoverId = niVal(cover)
145 b.Description = nsVal(description)
146 b.Genre = nsVal(genre)
148 b.SeriesName = nsVal(name)
149 b.Title = nsVal(title)
150 b.Volume = nsVal(volume)
157 if count < len(res) {
164 func queryBookPathById(id int) (string) {
165 query := "SELECT b.path FROM Books b WHERE b.id=$1"
167 ps, err := getDb().Prepare(query)
169 report("Failed to Prepare query: " + query, err)
174 row := ps.QueryRow(id)
175 var path sql.NullString
176 err = row.Scan(&path)
178 report(fmt.Sprintf("Failed to retrieve path for book id %v: ", id), err)
185 func queryIds(criteria []SearchTerm) []int {
186 fmt.Println("queryIds():", criteria)
188 query := "SELECT b.id FROM Books b" +
189 " INNER JOIN Authors a ON a.id=b.author" +
190 " LEFT OUTER JOIN Series s ON s.id=b.series"
192 args := make([]interface{}, len(criteria))
194 for i, criterion := range criteria {
200 switch criterion.Attribute {
202 query += " a.grouping LIKE $" + strconv.Itoa(i + 1)
204 query += " s.descr LIKE $" + strconv.Itoa(i + 1)
206 query += " b.title LIKE $" + strconv.Itoa(i + 1)
208 report("Error: unrecognized search field in queryIds(): " + criterion.Attribute.String(), nil)
211 args[i] = criterion.Text
214 query += " ORDER BY b.path"
218 ps, err := getDb().Prepare(query)
220 report("Failed to Prepare query: " + query, err)
226 rows, err = ps.Query(args...)
228 report("Failed to execute query: " + query, err)
236 res = append(res, id)
242 func queryMimeTypeByEfsId(efsId int) string {
243 const query = "SELECT mimeType FROM Efs WHERE id=$1"
245 ps, err := getDb().Prepare(query)
247 report("Failed to Prepare query: " + query, err)
252 row := ps.QueryRow(efsId)
253 var mimeType sql.NullString
254 err = row.Scan(&mimeType)
256 report(fmt.Sprintf("Failed to retrieve mimeType for id %v: ", efsId), err)
260 return nsVal(mimeType)
263 func report(msg string, err error) {
264 fmt.Println("Error: " + msg, err)