12 // ---------------------------------------------------------------------------
15 Age string // recommended age, e.g. "beginner", "junior", "ya" (Young Adult), "adult"
16 AuthorGrouping string // unique rendering of the author's name, used for internal grouping
17 AuthorReading string // reading order of author's name, e.g. "Charles Dickens"
18 AuthorSort string // sort order of author's name, e.g. "Dickens, Charles"
19 CoverId int // index into EFS table for cover, if there is one
20 DDC string // Dewey Decimal Classification
21 Description string // Back cover / inside flap blurb, describing the book
22 Genre string // e.g. "adventure", "historical", "mystery", "romance", "sf" (Science Fiction)
24 LCC string // Library of Congress Classification
30 // ---------------------------------------------------------------------------
33 Author, Language, List, Series, Title Field = "aut", "lan", "lst", "ser", "tit"
36 func (f Field) String() string {
40 // ---------------------------------------------------------------------------
41 type SearchTerm struct {
46 var g_db *sql.DB = nil
47 var g_mutex = &sync.Mutex{}
49 // ============================================================================
56 func getDb() (*sql.DB) {
59 defer g_mutex.Unlock()
62 g_db = openDb(config.user, config.pass, config.dbName)
69 func niVal(ni sql.NullInt64) int {
76 func nsVal(ns sql.NullString) string {
83 func openDb(user, pass, dbName string) (*sql.DB) {
84 db, err := sql.Open("postgres","user=" + user + " password=" + pass + " dbname=" + dbName + " sslmode=disable")
86 report("Error: DB arguments incorrect?", err)
92 report("Error: could not connect to DB.", err)
100 func queryBooksByIds(ids []int) []Book {
101 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
103 INNER JOIN Books b ON a.id=b.author
104 LEFT OUTER JOIN Classifications c ON c.id=b.classification
105 LEFT OUTER JOIN Series s ON s.id=b.series
108 ps, err := getDb().Prepare(query)
110 report("Error: failed to prepare statement: " + query, err)
116 for _, id := range ids {
122 res := make([]Book, count)
125 for _, id := range ids {
130 row := ps.QueryRow(id)
132 var age, grouping, reading, sort, ddc, description, genre, language, lcc, name, title, volume sql.NullString
133 var cover sql.NullInt64
135 err = row.Scan(&age, &grouping, &reading, &sort, &cover, &ddc, &description, &genre, &language, &lcc, &name, &title, &volume)
137 report("Error: Failed to read book:" + strconv.Itoa(id) + ":", err)
142 b.AuthorGrouping = nsVal(grouping)
143 b.AuthorReading = nsVal(reading)
144 b.AuthorSort = nsVal(sort)
145 b.CoverId = niVal(cover)
147 b.Description = nsVal(description)
148 b.Genre = nsVal(genre)
149 b.Language = nsVal(language)
151 b.SeriesName = nsVal(name)
152 b.Title = nsVal(title)
153 b.Volume = nsVal(volume)
160 if count < len(res) {
167 func queryBookPathById(id int) (string) {
168 query := "SELECT b.path FROM Books b WHERE b.id=$1"
170 ps, err := getDb().Prepare(query)
172 report("Failed to Prepare query: " + query, err)
177 row := ps.QueryRow(id)
178 var path sql.NullString
179 err = row.Scan(&path)
181 report(fmt.Sprintf("Failed to retrieve path for book id %v: ", id), err)
188 func queryIds(criteria []SearchTerm) []int {
189 query := "SELECT b.id FROM Books b" +
190 " INNER JOIN Authors a ON a.id=b.author" +
191 " LEFT OUTER JOIN Series s ON s.id=b.series" +
192 " LEFT OUTER JOIN Lists_Books lb ON b.id=lb.book" +
193 " LEFT OUTER JOIN Lists l ON l.id=lb.list"
195 args := make([]interface{}, 0)
199 for _, criterion := range criteria {
206 switch criterion.Attribute {
208 query += " UPPER(a.grouping) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
209 args = append(args, strings.Replace(criterion.Text, " ", "", -1))
211 query += " UPPER(b.language) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
212 args = append(args, criterion.Text)
214 codes := strings.Split(criterion.Text, ",")
215 query += " UPPER(l.code) IN ("
217 for j, code := range codes {
221 query += "UPPER($" + strconv.Itoa(i + j + 1) + ")"
222 args = append(args, code)
227 query += " UPPER(s.descr) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
228 args = append(args, criterion.Text)
230 query += " UPPER(b.title) LIKE UPPER($" + strconv.Itoa(i + 1) + ")"
231 args = append(args, criterion.Text)
233 report("Error: unrecognized search field in queryIds(): " + criterion.Attribute.String(), nil)
240 query += " ORDER BY a.grouping,s.descr,b.volume,b.title,b.path"
244 ps, err := getDb().Prepare(query)
246 report("Failed to Prepare query: " + query, err)
252 rows, err = ps.Query(args...)
254 report("Failed to execute query: " + query, err)
262 res = append(res, id)
268 func queryMimeTypeByEfsId(efsId int) string {
269 const query = "SELECT mimeType FROM Efs WHERE id=$1"
271 ps, err := getDb().Prepare(query)
273 report("Failed to Prepare query: " + query, err)
278 row := ps.QueryRow(efsId)
279 var mimeType sql.NullString
280 err = row.Scan(&mimeType)
282 report(fmt.Sprintf("Failed to retrieve mimeType for id %v: ", efsId), err)
286 return nsVal(mimeType)
289 func report(msg string, err error) {
290 fmt.Println("Error: " + msg, err)