|
1
|
# JSON API: /query |
|
2
|
([⬑JSON API Index](index.md)) |
|
3
|
|
|
4
|
# SQL Query |
|
5
|
|
|
6
|
**Status:** implemented 20111008 |
|
7
|
|
|
8
|
**Required privileges:** "a" or "s" |
|
9
|
|
|
10
|
**Request:** `/json/query` |
|
11
|
|
|
12
|
Potential FIXME: restrict this to queries which return results, as opposed |
|
13
|
to those which may modify data. |
|
14
|
|
|
15
|
Options: |
|
16
|
|
|
17
|
- `sql=string` The SQL code to run. It is expected that it be a SELECT |
|
18
|
statement, but that is not enforced. This parameter may be set as a |
|
19
|
POST.payload property, as the POST.payload itself, GET, or as a |
|
20
|
positional parameter coming after the command name (CLI and HTTP |
|
21
|
modes, though the escaping would be unsightly in HTTP mode). |
|
22
|
- `format=string` (default="o"). "o" specifies that each result row |
|
23
|
should be in the form of key/value pairs (o=object). "a" means each |
|
24
|
row should be an array of values. |
|
25
|
|
|
26
|
**Example request:** |
|
27
|
|
|
28
|
POST to: `/json/query` |
|
29
|
|
|
30
|
```json |
|
31
|
{ |
|
32
|
"authToken": "...", |
|
33
|
"payload": { |
|
34
|
"sql": "SELECT * FROM reportfmt", |
|
35
|
"format": "o" |
|
36
|
} |
|
37
|
} |
|
38
|
``` |
|
39
|
|
|
40
|
**Response payload example:** (assuming the above example) |
|
41
|
|
|
42
|
|
|
43
|
``` |
|
44
|
{ |
|
45
|
"columns":[ |
|
46
|
"rn", |
|
47
|
"owner", |
|
48
|
"title", |
|
49
|
"mtime", |
|
50
|
"cols", |
|
51
|
"sqlcode" |
|
52
|
], |
|
53
|
"rows":[ |
|
54
|
{ |
|
55
|
"rn":1, |
|
56
|
"owner":"drh", |
|
57
|
"title":"All Tickets", |
|
58
|
"mtime":1303870798, |
|
59
|
}, |
|
60
|
… |
|
61
|
] |
|
62
|
} |
|
63
|
``` |
|
64
|
|
|
65
|
The column names are provided in a separate field because their order |
|
66
|
is guaranteed to match the order of the query columns, whereas object |
|
67
|
key/value pairs might get reordered (typically sorted by key) when |
|
68
|
travelling through different JSON implementations. In this manner, |
|
69
|
clients can e.g. be sure to render the columns in the proper |
|
70
|
(query-specified) order. |
|
71
|
|
|
72
|
When in "array" mode the "rows" results will be an array of arrays. For |
|
73
|
example, the above "rows" property would instead look like: |
|
74
|
|
|
75
|
`[ [1, "drh", "All Tickets", 1303870798, … ], … ]` |
|
76
|
|
|
77
|
Note the column *names* are never *guaranteed* to be exactly as they |
|
78
|
appear in the SQL *unless* they are qualified with an AS, e.g. `SELECT |
|
79
|
foo AS foo...`. When generating reports which need fixed column names, it |
|
80
|
is highly recommended to use an AS qualifier for every column, even if |
|
81
|
they use the same name as the column. This is the only way to guarantee |
|
82
|
that the result column names will be stable. (FYI: that behaviour comes |
|
83
|
from sqlite3, not the JSON bits, and this behaviour *has* been known to |
|
84
|
change between sqlite3 versions (so this is not just an idle threat of |
|
85
|
*potential* future incompatibility).) |
|
86
|
|