package gorqlite import "errors" import "fmt" import "encoding/json" /* ***************************************************************** method: Connection.Query() This is the JSON we get back: { "results": [ { "columns": [ "id", "name" ], "types": [ "integer", "text" ], "values": [ [ 1, "fiona" ], [ 2, "sinead" ] ], "time": 0.0150043 } ], "time": 0.0220043 } or { "results": [ { "columns": [ "id", "name" ], "types": [ "number", "text" ], "values": [ [ null, "Hulk" ] ], "time": 4.8958e-05 }, { "columns": [ "id", "name" ], "types": [ "number", "text" ], "time": 1.8460000000000003e-05 } ], "time": 0.000134776 } or { "results": [ { "error": "near \"nonsense\": syntax error" } ], "time": 2.478862 } * *****************************************************************/ /* QueryOne() is a convenience method that wraps Query() into a single-statement method. */ func (conn *Connection) QueryOne(sqlStatement string) (qr QueryResult, err error) { if conn.hasBeenClosed { qr.Err = errClosed return qr, errClosed } sqlStatements := make([]string, 0) sqlStatements = append(sqlStatements, sqlStatement) qra, err := conn.Query(sqlStatements) return qra[0], err } /* Query() is used to perform SELECT operations in the database. It takes an array of SQL statements and executes them in a single transaction, returning an array of QueryResult vars. */ func (conn *Connection) Query(sqlStatements []string) (results []QueryResult, err error) { results = make([]QueryResult, 0) if conn.hasBeenClosed { var errResult QueryResult errResult.Err = errClosed results = append(results, errResult) return results, errClosed } trace("%s: Query() for %d statements", conn.ID, len(sqlStatements)) // if we get an error POSTing, that's a showstopper response, err := conn.rqliteApiPost(api_QUERY, sqlStatements) if err != nil { trace("%s: rqliteApiCall() ERROR: %s", conn.ID, err.Error()) var errResult QueryResult errResult.Err = err results = append(results, errResult) return results, err } trace("%s: rqliteApiCall() OK", conn.ID) // if we get an error Unmarshaling, that's a showstopper var sections map[string]interface{} err = json.Unmarshal(response, §ions) if err != nil { trace("%s: json.Unmarshal() ERROR: %s", conn.ID, err.Error()) var errResult QueryResult errResult.Err = err results = append(results, errResult) return results, err } /* at this point, we have a "results" section and a "time" section. we can igore the latter. */ resultsArray := sections["results"].([]interface{}) trace("%s: I have %d result(s) to parse", conn.ID, len(resultsArray)) numStatementErrors := 0 for n, r := range resultsArray { trace("%s: parsing result %d", conn.ID, n) var thisQR QueryResult thisQR.conn = conn // r is a hash with columns, types, values, and time thisResult := r.(map[string]interface{}) // did we get an error? _, ok := thisResult["error"] if ok { trace("%s: have an error on this result: %s", conn.ID, thisResult["error"].(string)) thisQR.Err = errors.New(thisResult["error"].(string)) results = append(results, thisQR) numStatementErrors++ continue } // time is a float64 thisQR.Timing = thisResult["time"].(float64) // column & type are an array of strings c := thisResult["columns"].([]interface{}) t := thisResult["types"].([]interface{}) for i := 0; i < len(c); i++ { thisQR.columns = append(thisQR.columns, c[i].(string)) thisQR.types = append(thisQR.types, t[i].(string)) } // and values are an array of arrays if thisResult["values"] != nil { thisQR.values = thisResult["values"].([]interface{}) } else { trace("%s: fyi, no values this query", conn.ID) } thisQR.rowNumber = -1 trace("%s: this result (#col,time) %d %f", conn.ID, len(thisQR.columns), thisQR.Timing) results = append(results, thisQR) } trace("%s: finished parsing, returning %d results", conn.ID, len(results)) if numStatementErrors > 0 { return results, errors.New(fmt.Sprintf("there were %d statement errors", numStatementErrors)) } else { return results, nil } } /* ***************************************************************** type: QueryResult * *****************************************************************/ /* A QueryResult type holds the results of a call to Query(). You could think of it as a rowset. So if you were to query: SELECT id, name FROM some_table; then a QueryResult would hold any errors from that query, a list of columns and types, and the actual row values. Query() returns an array of QueryResult vars, while QueryOne() returns a single variable. */ type QueryResult struct { conn *Connection Err error columns []string types []string Timing float64 values []interface{} rowNumber int64 } // these are done as getters rather than as public // variables to prevent monkey business by the user // that would put us in an inconsistent state /* ***************************************************************** method: QueryResult.Columns() * *****************************************************************/ /* Columns returns a list of the column names for this QueryResult. */ func (qr *QueryResult) Columns() []string { return qr.columns } /* ***************************************************************** method: QueryResult.Map() * *****************************************************************/ /* Map() returns the current row (as advanced by Next()) as a map[string]interface{} The key is a string corresponding to a column name. The value is the corresponding column. Note that only json values are supported, so you will need to type the interface{} accordingly. */ func (qr *QueryResult) Map() (map[string]interface{}, error) { trace("%s: Map() called for row %d", qr.conn.ID, qr.rowNumber) ans := make(map[string]interface{}) if qr.rowNumber == -1 { return ans, errors.New("you need to Next() before you Map(), sorry, it's complicated") } thisRowValues := qr.values[qr.rowNumber].([]interface{}) for i := 0; i < len(qr.columns); i++ { ans[qr.columns[i]] = thisRowValues[i] } return ans, nil } /* ***************************************************************** method: QueryResult.Next() * *****************************************************************/ /* Next() positions the QueryResult result pointer so that Scan() or Map() is ready. You should call Next() first, but gorqlite will fix it if you call Map() or Scan() before the initial Next(). A common idiom: rows := conn.Write(something) for rows.Next() { // your Scan/Map and processing here. } */ func (qr *QueryResult) Next() bool { if qr.rowNumber >= int64(len(qr.values)-1) { return false } qr.rowNumber += 1 return true } /* ***************************************************************** method: QueryResult.NumRows() * *****************************************************************/ /* NumRows() returns the number of rows returned by the query. */ func (qr *QueryResult) NumRows() int64 { return int64(len(qr.values)) } /* ***************************************************************** method: QueryResult.RowNumber() * *****************************************************************/ /* RowNumber() returns the current row number as Next() iterates through the result's rows. */ func (qr *QueryResult) RowNumber() int64 { return qr.rowNumber } /* ***************************************************************** method: QueryResult.Scan() * *****************************************************************/ /* Scan() takes a list of pointers and then updates them to reflect he current row's data. Note that only the following data types are used, and they are a subset of the types JSON uses: string, for JSON strings float64, for JSON numbers int64, as a convenient extension nil for JSON null booleans, JSON arrays, and JSON objects are not supported, since sqlite does not support them. */ func (qr *QueryResult) Scan(dest ...interface{}) error { trace("%s: Scan() called for %d vars", qr.conn.ID, len(dest)) if qr.rowNumber == -1 { return errors.New("you need to Next() before you Scan(), sorry, it's complicated") } if len(dest) != len(qr.columns) { return errors.New(fmt.Sprintf("expected %d columns but got %d vars\n", len(qr.columns), len(dest))) } thisRowValues := qr.values[qr.rowNumber].([]interface{}) for n, d := range dest { switch d.(type) { case *int64: f := int64(thisRowValues[n].(float64)) *d.(*int64) = f case *float64: f := float64(thisRowValues[n].(float64)) *d.(*float64) = f case *string: s := string(thisRowValues[n].(string)) *d.(*string) = s default: return errors.New(fmt.Sprintf("unknown destination type to scan into in variable #%d", n)) } } return nil } /* ***************************************************************** method: QueryResult.Types() * *****************************************************************/ /* Types() returns an array of the column's types. Note that sqlite will repeat the type you tell it, but in many cases, it's ignored. So you can initialize a column as CHAR(3) but it's really TEXT. See https://www.sqlite.org/datatype3.html This info may additionally conflict with the reality that your data is being JSON encoded/decoded. */ func (qr *QueryResult) Types() []string { return qr.types }