From d4b5c2903e7b0c2267aa7bfdef514a3d1e447de3 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Fri, 3 Nov 2017 20:49:13 +0900 Subject: [PATCH] Refactor into a few files. Add web service JSON query support. --- main/config.go | 10 +++ main/db.go | 209 ++++++++++++++++++++++++++++++++++++++++++++++++ main/handler.go | 97 ++++++++++++++++++++++ main/main.go | 121 ++-------------------------- 4 files changed, 323 insertions(+), 114 deletions(-) create mode 100644 main/config.go create mode 100644 main/db.go create mode 100644 main/handler.go diff --git a/main/config.go b/main/config.go new file mode 100644 index 0000000..7aa8195 --- /dev/null +++ b/main/config.go @@ -0,0 +1,10 @@ +package main + +type dbConfig struct { + user, pass, dbName string +} + +func getConfig() dbConfig { + // TODO: use a real password, and load config info from a file + return dbConfig{user:"quanlib", pass:"quanlib", dbName:"quanlib"} +} 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) +} diff --git a/main/handler.go b/main/handler.go new file mode 100644 index 0000000..52d08e5 --- /dev/null +++ b/main/handler.go @@ -0,0 +1,97 @@ +package main + +import ( + "encoding/json" + "fmt" + "net/http" + "strconv" + "strings" +) + +const PARAM_IDS = "ids" +const MAX_TERMS = 10 + +// ============================================================================ +func handler(w http.ResponseWriter, r *http.Request) { + err := r.ParseForm() + if nil != err { + fmt.Fprintln(w, "ERROR!", err) + return + } + + action := r.URL.Path[1:] + + switch(action) { + case "info": + handleInfo(w, r) + case "search": + handleSearch(w, r) + default: + fmt.Fprintf(w, "Unrecognized request: %s\n", r.URL.Path[1:]) + fmt.Fprintf(w, "id: %s\n", r.FormValue("id")) + + fmt.Fprintln(w, "URL: ", r.URL) + fmt.Fprintln(w, "Query: ", r.URL.Query()) + } +} + +func handleInfo(w http.ResponseWriter, r *http.Request) { + idParams := r.Form[PARAM_IDS] + if 1 != len(idParams) { + fmt.Fprintln(w, "ERROR! Detected either zero or multiple ids= parameters. Exactly one expected.") + return + } + + idParam := idParams[0] + idStrings := strings.Split(idParam, ",") + ids := make([]int, len(idStrings)) + for i, v := range(idStrings) { + var err error + ids[i], err = strconv.Atoi(v) + if nil != err { + ids[i] = 0 + } + } + + books := queryBooksByIds(ids) + + var jsonValue []byte + var err error + jsonValue, err = json.Marshal(books) + if nil != err { + fmt.Fprintln(w, "ERROR!", err) + } else { + w.Write(jsonValue) + } +} + +func handleSearch(w http.ResponseWriter, r *http.Request) { + fields := []Field{Author, Title, Series} + + terms := make([]SearchTerm, len(fields)) + + count := 0 + for _, fv := range(fields) { + paramName := fv.String() + paramValues := r.Form[paramName] + for _, pv := range(paramValues) { + if count >= len(terms) { + fmt.Printf("WARNING: limit of %v search terms exceeded. One or more terms ignored.") + break + } + terms[count] = SearchTerm{Attribute:fv, Text:pv} + count++ + } + } + + terms = terms[:count] + + ids := queryIds(terms) + + jsonValue, err := json.Marshal(ids) + if nil != err { + fmt.Fprintln(w, "ERROR!", err) + } else { + w.Write(jsonValue) + } +} diff --git a/main/main.go b/main/main.go index c510422..99c09a0 100644 --- a/main/main.go +++ b/main/main.go @@ -2,123 +2,16 @@ package main import ( "database/sql" - "fmt" - _ "github.com/lib/pq" -// "json" + "net/http" ) -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 -} - +// ============================================================================ func main() { - // TODO: protect DB with a real password, and read DB config from an external file - db := openDb("quanlib", "quanlib", "quanlib") - if nil == db { - return - } - defer db.Close() - - query := "SELECT COUNT(1) FROM Books" - ps, err := db.Prepare(query) - if nil != err { - fmt.Println("Error: failed to prepare statement: " + query) - return - } - - var count int - err = ps.QueryRow().Scan(&count) - if sql.ErrNoRows == err { - fmt.Println("Error: No rows returned by statement: " + query) - } else { - fmt.Println("Found ", count, " books.") - } - - ids := []int {1041, 1951, 2148, 103} - books := queryBooksByIds(db, ids) - - fmt.Println("Found books:", books) -} - -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 { - fmt.Println("Error: DB arguments incorrect?", err) - return nil - } + defer dbShutdown() - err = db.Ping() - if nil != err { - fmt.Println("Error: could not connect to DB.", err) - db.Close() - return nil - } + var b sql.NullString + nsVal(b) - return db + http.HandleFunc("/", handler) + http.ListenAndServe(":8001", nil) } - -func queryBooksByIds(db *sql.DB, 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 := db.Prepare(query) - if nil != err { - fmt.Println("Error: failed to prepare statement: " + query) - return nil - } - defer ps.Close() - - res := make([]Book, len(ids)) - - for n, id := range ids { - 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 { - fmt.Println("Error: Failed to read book:", 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[n] = b - } - } - - return res -} - -- 2.39.2