SQL Query

Status: implemented 20111008

Required privileges: "a" or "s"

Request: /json/query

Potential FIXME: restrict this to queries which return results, as opposed to those which may modify data.

Options:

sql=string The SQL code to run. It is expected that it be a SELECT statement, but that is not enforced. This parameter may be set as a POST.payload property, as the POST.payload itself, GET, or as a positional parameter coming after the command name (CLI and HTTP modes, though the escaping would be unsightly in HTTP mode).
format=string (default="o"). "o" specifies that each result row should be in the form of key/value pairs (o=object). "a" means each row should be an array of values.

Example request:

POST to: /json/query

{
"authToken": "...",
"payload": {
  "sql": "SELECT * FROM reportfmt",
  "format": "o"
  }
}

Response payload example: (assuming the above example)

{
"columns":[
  "rn",
  "owner",
  "title",
  "mtime",
  "cols",
  "sqlcode"
 ],
  "rows":[
   {
    "rn":1,
    "owner":"drh",
    "title":"All Tickets",
    "mtime":1303870798,
   },
  …
  ]
}

The column names are provided in a separate field is because their order is guaranteed to match the order of the query columns, whereas object key/value pairs might get reordered (typically sorted by key) when travelling through different JSON implementations. In this manner, clients can e.g. be sure to render the columns in the proper (query-specified) order.

When in "array" mode the "rows" results will be an array of arrays. For example, the above "rows" property would instead look like:

[ [1, "drh", "All Tickets", 1303870798, … ], … ]

Note the column names are never guaranteed to be exactly as they appear in the SQL unless they are qualified with an AS, e.g. SELECT foo AS foo.... When generating reports which need fixed column names, it is highly recommended to use an AS qualifier for every column, even if they use the same name as the column. This is the only way to guaranty that the result column names will be stable. (FYI: that behaviour comes from sqlite3, not the JSON bits, and this behaviour has been known to change between sqlite3 versions (so this is not just an idle threat of potential future incompatibility).)