Correct approach to multiple queries Go Rest API -
i attempting create rest api in go. have partially working in return 4 separate json objects such:
[{"name":"qa1","server":"bot1","description":"tools","apps":""}, {"name":"qa1","server":"","description":"","apps":"duo"}, {"name":"qa1","server":"","description":"","apps":"git"}, {"name":"qa1","server":"","description":"","apps":"php"}] what want single returned object like:
[{"name":"qa1","server":"bot1","description":"tools","apps": "duo|git|php"}] i have way either making queries or structs (or both or else) not quite correct. want make sure understand how right because expand on other queries , such down road. have included "full" go code below.
to clear, i'm not looking solution (though of course appreciate compare with), i've gone wrong in thinking , correct approach be.
package main import ( "database/sql" "encoding/json" "fmt" _ "github.com/go-sql-driver/mysql" "io/ioutil" "log" "net/http" ) // there can 0 or more apps on volume type apps struct { name string } // volumes have name, description, on server , have multiple services/apps type volume struct { name string server string description string services apps } //handle requests func handler(response http.responsewriter, request *http.request) { response.header().set("content-type", "text/html") webpage, err := ioutil.readfile("index.html") if err != nil { http.error(response, fmt.sprintf("home.html file error %v", err), 500) } fmt.fprint(response, string(webpage)) } // db connection const ( db_host = "mydbhost" db_name = "mydb" db_user = "mydbuser" db_pass = "mydbpass" ) // respond urls of form /api func apihandler(response http.responsewriter, request *http.request) { //connect database dsn := db_user + ":" + db_pass + "@" + db_host + "/" + db_name + "?charset=utf8" db, err := sql.open("mysql", dsn) if err != nil { fmt.println(err.error()) } defer db.close() // open doesn't open connection. validate dsn data: err = db.ping() if err != nil { fmt.println(err.error()) } //set mime type json response.header().set("content-type", "application/json") result := []*volume{} switch request.method { case "get": srvrnm := request.url.query().get("srvrnm") appnm := request.url.query().get("appnm") srvrs, err := db.prepare("select volumes.name volnm, servers.name srvrnm, volumes.description descr volumes left join servers on volumes.server_id = servers.id servers.name = ?") if err != nil { fmt.print(err) } srvcs, err := db.prepare("select volumes.name volnm, supprtsvcs.name app_name volumes volumes join hostsvcs on volumes.id = hostsvcs.volume_id join supprtsvcs on supprtsvcs.id = hostsvcs.supportsvcs_id volumes.name = ?") if err != nil { fmt.print(err) } // run sql query volum & description hostname srvrrows, err := srvrs.query(srvrnm) if err != nil { fmt.print(err) } srvrrows.next() { var volnm string var srvrnm string var descr string // scan first query err = srvrrows.scan(&volnm, &srvrnm, &descr) if err != nil { fmt.println("error scanning: " + err.error()) return } // append slice results scan result = append(result, &volume{name: volnm, server: srvrnm, description: descr}) } // run sql query services/apps srvcrows, err := srvcs.query(appnm) if err != nil { fmt.print(err) } srvcrows.next() { var volnm string var appnm string // scan second query err = srvcrows.scan(&volnm, &appnm) if err != nil { fmt.println("error scanning: " + err.error()) return } // append slice results scan result = append(result, &volume{name: volnm, apps: appnm}) } default: } json, err := json.marshal(result) if err != nil { fmt.println(err) return } fmt.fprintf(response, string(json)) db.close() } func main() { port := "1236" var err string mux := http.newservemux() mux.handle("/api", http.handlerfunc(apihandler)) mux.handle("/", http.handlerfunc(handler)) // start listing on given port these routes on server. log.print("listening on port " + port + " ... ") errs := http.listenandserve(":"+port, mux) if errs != nil { log.fatal("listenandserve error: ", err) } }
from sounds of it, want result like:
[ {"name":"qa1","server":"bot1","description":"tools","apps": ["duo","git","php"] ] hence want volumes struct like:
type volume struct { name string server string description string services []apps } if want apps output duo|git|php then create custom type instead of []apps json marshaler implementation. return json.marshal(strings.join(names,"|"))
rather run 2 separate queries, more efficient run single query selects product of volumes & apps together. important query sorted volume volume rows contiguous. example query output be:
name | server | desc | app ---- | ------ | ----- | --- vol1 | srv1 | desc1 | app1 vol1 | srv1 | desc1 | app2 vol2 | srv2 | desc2 | app3 you loop on , detect if looking @ new volume. if so, create new entry in result. if not, add app list of apps. example:
var ( volnm string srvrnm string descr string appnm string v *volume result []*volume ) srvrrows.next() { if err = srvcrows.scan(&volnm, &srvrnm, &descr, &appnm);err!=nil { // handle error } // add app current volume if same, otherwise start new volume if v!=nil && v.name == volnm { v.services = append(v.services,apps{appnm}) } else { v = &volume{ name: volnm, server: svrnm, description: descr, services: []apps{appnm}} result = append(result,v) } } // finished, return result etc... when taking approach, need appropriate parent record discriminator. i'd used v.name == volnm illustration purposes should checking primary key. can make unexported (lowercase) field in struct if not wish export through api.
Comments
Post a Comment