query.go 10.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395
  1. package gorqlite
  2. import "errors"
  3. import "fmt"
  4. import "encoding/json"
  5. /* *****************************************************************
  6. method: Connection.Query()
  7. This is the JSON we get back:
  8. {
  9. "results": [
  10. {
  11. "columns": [
  12. "id",
  13. "name"
  14. ],
  15. "types": [
  16. "integer",
  17. "text"
  18. ],
  19. "values": [
  20. [
  21. 1,
  22. "fiona"
  23. ],
  24. [
  25. 2,
  26. "sinead"
  27. ]
  28. ],
  29. "time": 0.0150043
  30. }
  31. ],
  32. "time": 0.0220043
  33. }
  34. or
  35. {
  36. "results": [
  37. {
  38. "columns": [
  39. "id",
  40. "name"
  41. ],
  42. "types": [
  43. "number",
  44. "text"
  45. ],
  46. "values": [
  47. [
  48. null,
  49. "Hulk"
  50. ]
  51. ],
  52. "time": 4.8958e-05
  53. },
  54. {
  55. "columns": [
  56. "id",
  57. "name"
  58. ],
  59. "types": [
  60. "number",
  61. "text"
  62. ],
  63. "time": 1.8460000000000003e-05
  64. }
  65. ],
  66. "time": 0.000134776
  67. }
  68. or
  69. {
  70. "results": [
  71. {
  72. "error": "near \"nonsense\": syntax error"
  73. }
  74. ],
  75. "time": 2.478862
  76. }
  77. * *****************************************************************/
  78. /*
  79. QueryOne() is a convenience method that wraps Query() into a single-statement
  80. method.
  81. */
  82. func (conn *Connection) QueryOne(sqlStatement string) (qr QueryResult, err error) {
  83. if conn.hasBeenClosed {
  84. qr.Err = errClosed
  85. return qr, errClosed
  86. }
  87. sqlStatements := make([]string, 0)
  88. sqlStatements = append(sqlStatements, sqlStatement)
  89. qra, err := conn.Query(sqlStatements)
  90. return qra[0], err
  91. }
  92. /*
  93. Query() is used to perform SELECT operations in the database.
  94. It takes an array of SQL statements and executes them in a single transaction, returning an array of QueryResult vars.
  95. */
  96. func (conn *Connection) Query(sqlStatements []string) (results []QueryResult, err error) {
  97. results = make([]QueryResult, 0)
  98. if conn.hasBeenClosed {
  99. var errResult QueryResult
  100. errResult.Err = errClosed
  101. results = append(results, errResult)
  102. return results, errClosed
  103. }
  104. trace("%s: Query() for %d statements", conn.ID, len(sqlStatements))
  105. // if we get an error POSTing, that's a showstopper
  106. response, err := conn.rqliteApiPost(api_QUERY, sqlStatements)
  107. if err != nil {
  108. trace("%s: rqliteApiCall() ERROR: %s", conn.ID, err.Error())
  109. var errResult QueryResult
  110. errResult.Err = err
  111. results = append(results, errResult)
  112. return results, err
  113. }
  114. trace("%s: rqliteApiCall() OK", conn.ID)
  115. // if we get an error Unmarshaling, that's a showstopper
  116. var sections map[string]interface{}
  117. err = json.Unmarshal(response, &sections)
  118. if err != nil {
  119. trace("%s: json.Unmarshal() ERROR: %s", conn.ID, err.Error())
  120. var errResult QueryResult
  121. errResult.Err = err
  122. results = append(results, errResult)
  123. return results, err
  124. }
  125. /*
  126. at this point, we have a "results" section and
  127. a "time" section. we can igore the latter.
  128. */
  129. resultsArray := sections["results"].([]interface{})
  130. trace("%s: I have %d result(s) to parse", conn.ID, len(resultsArray))
  131. numStatementErrors := 0
  132. for n, r := range resultsArray {
  133. trace("%s: parsing result %d", conn.ID, n)
  134. var thisQR QueryResult
  135. thisQR.conn = conn
  136. // r is a hash with columns, types, values, and time
  137. thisResult := r.(map[string]interface{})
  138. // did we get an error?
  139. _, ok := thisResult["error"]
  140. if ok {
  141. trace("%s: have an error on this result: %s", conn.ID, thisResult["error"].(string))
  142. thisQR.Err = errors.New(thisResult["error"].(string))
  143. results = append(results, thisQR)
  144. numStatementErrors++
  145. continue
  146. }
  147. // time is a float64
  148. thisQR.Timing = thisResult["time"].(float64)
  149. // column & type are an array of strings
  150. c := thisResult["columns"].([]interface{})
  151. t := thisResult["types"].([]interface{})
  152. for i := 0; i < len(c); i++ {
  153. thisQR.columns = append(thisQR.columns, c[i].(string))
  154. thisQR.types = append(thisQR.types, t[i].(string))
  155. }
  156. // and values are an array of arrays
  157. if thisResult["values"] != nil {
  158. thisQR.values = thisResult["values"].([]interface{})
  159. } else {
  160. trace("%s: fyi, no values this query", conn.ID)
  161. }
  162. thisQR.rowNumber = -1
  163. trace("%s: this result (#col,time) %d %f", conn.ID, len(thisQR.columns), thisQR.Timing)
  164. results = append(results, thisQR)
  165. }
  166. trace("%s: finished parsing, returning %d results", conn.ID, len(results))
  167. if numStatementErrors > 0 {
  168. return results, errors.New(fmt.Sprintf("there were %d statement errors", numStatementErrors))
  169. } else {
  170. return results, nil
  171. }
  172. }
  173. /* *****************************************************************
  174. type: QueryResult
  175. * *****************************************************************/
  176. /*
  177. A QueryResult type holds the results of a call to Query(). You could think of it as a rowset.
  178. So if you were to query:
  179. SELECT id, name FROM some_table;
  180. then a QueryResult would hold any errors from that query, a list of columns and types, and the actual row values.
  181. Query() returns an array of QueryResult vars, while QueryOne() returns a single variable.
  182. */
  183. type QueryResult struct {
  184. conn *Connection
  185. Err error
  186. columns []string
  187. types []string
  188. Timing float64
  189. values []interface{}
  190. rowNumber int64
  191. }
  192. // these are done as getters rather than as public
  193. // variables to prevent monkey business by the user
  194. // that would put us in an inconsistent state
  195. /* *****************************************************************
  196. method: QueryResult.Columns()
  197. * *****************************************************************/
  198. /*
  199. Columns returns a list of the column names for this QueryResult.
  200. */
  201. func (qr *QueryResult) Columns() []string {
  202. return qr.columns
  203. }
  204. /* *****************************************************************
  205. method: QueryResult.Map()
  206. * *****************************************************************/
  207. /*
  208. Map() returns the current row (as advanced by Next()) as a map[string]interface{}
  209. The key is a string corresponding to a column name.
  210. The value is the corresponding column.
  211. Note that only json values are supported, so you will need to type the interface{} accordingly.
  212. */
  213. func (qr *QueryResult) Map() (map[string]interface{}, error) {
  214. trace("%s: Map() called for row %d", qr.conn.ID, qr.rowNumber)
  215. ans := make(map[string]interface{})
  216. if qr.rowNumber == -1 {
  217. return ans, errors.New("you need to Next() before you Map(), sorry, it's complicated")
  218. }
  219. thisRowValues := qr.values[qr.rowNumber].([]interface{})
  220. for i := 0; i < len(qr.columns); i++ {
  221. ans[qr.columns[i]] = thisRowValues[i]
  222. }
  223. return ans, nil
  224. }
  225. /* *****************************************************************
  226. method: QueryResult.Next()
  227. * *****************************************************************/
  228. /*
  229. Next() positions the QueryResult result pointer so that Scan() or Map() is ready.
  230. You should call Next() first, but gorqlite will fix it if you call Map() or Scan() before
  231. the initial Next().
  232. A common idiom:
  233. rows := conn.Write(something)
  234. for rows.Next() {
  235. // your Scan/Map and processing here.
  236. }
  237. */
  238. func (qr *QueryResult) Next() bool {
  239. if qr.rowNumber >= int64(len(qr.values)-1) {
  240. return false
  241. }
  242. qr.rowNumber += 1
  243. return true
  244. }
  245. /* *****************************************************************
  246. method: QueryResult.NumRows()
  247. * *****************************************************************/
  248. /*
  249. NumRows() returns the number of rows returned by the query.
  250. */
  251. func (qr *QueryResult) NumRows() int64 {
  252. return int64(len(qr.values))
  253. }
  254. /* *****************************************************************
  255. method: QueryResult.RowNumber()
  256. * *****************************************************************/
  257. /*
  258. RowNumber() returns the current row number as Next() iterates through the result's rows.
  259. */
  260. func (qr *QueryResult) RowNumber() int64 {
  261. return qr.rowNumber
  262. }
  263. /* *****************************************************************
  264. method: QueryResult.Scan()
  265. * *****************************************************************/
  266. /*
  267. Scan() takes a list of pointers and then updates them to reflect he current row's data.
  268. Note that only the following data types are used, and they
  269. are a subset of the types JSON uses:
  270. string, for JSON strings
  271. float64, for JSON numbers
  272. int64, as a convenient extension
  273. nil for JSON null
  274. booleans, JSON arrays, and JSON objects are not supported,
  275. since sqlite does not support them.
  276. */
  277. func (qr *QueryResult) Scan(dest ...interface{}) error {
  278. trace("%s: Scan() called for %d vars", qr.conn.ID, len(dest))
  279. if qr.rowNumber == -1 {
  280. return errors.New("you need to Next() before you Scan(), sorry, it's complicated")
  281. }
  282. if len(dest) != len(qr.columns) {
  283. return errors.New(fmt.Sprintf("expected %d columns but got %d vars\n", len(qr.columns), len(dest)))
  284. }
  285. thisRowValues := qr.values[qr.rowNumber].([]interface{})
  286. for n, d := range dest {
  287. switch d.(type) {
  288. case *int64:
  289. f := int64(thisRowValues[n].(float64))
  290. *d.(*int64) = f
  291. case *float64:
  292. f := float64(thisRowValues[n].(float64))
  293. *d.(*float64) = f
  294. case *string:
  295. s := string(thisRowValues[n].(string))
  296. *d.(*string) = s
  297. default:
  298. return errors.New(fmt.Sprintf("unknown destination type to scan into in variable #%d", n))
  299. }
  300. }
  301. return nil
  302. }
  303. /* *****************************************************************
  304. method: QueryResult.Types()
  305. * *****************************************************************/
  306. /*
  307. Types() returns an array of the column's types.
  308. 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
  309. This info may additionally conflict with the reality that your data is being JSON encoded/decoded.
  310. */
  311. func (qr *QueryResult) Types() []string {
  312. return qr.types
  313. }