cson  cson_sqlite3

ACHTUNG: THIS PAGE IS NOW MAINTAINED IN THE NEW WIKI: http://whiki.wanderinghorse.net/wikis/cson/?page=cson_sqlite3

See also: cson_cpdo

cson_sqlite3

cson_sqlite3.h includes code for returning sqlite3 database query results as JSON.

Here's a demonstration:

#define CSON_ENABLE_SQLITE3 1 // See notes below!
#include "wh/cson/cson_sqlite3.h"
// Or:
#include "cson_amalgamation.h"
...

char const * select = "SELECT * FROM stuff";
cson_value * json = NULL;
int rc = cson_sqlite3_sql_to_json(
          mySqlite3Db, // the db to operate on
          &json,       // where to store the resulting JSON
          select,      // the SQL code
          0            // 0="thin", non-0="fat" (see below)
          );
if( 0 != rc ) {
  fprintf(stderr, "Error: %d (%s)\n", rc, cson_rc_string(rc));
}
else {
    cson_output_FILE( json, stdout, NULL );
    cson_value_free(json);
}

That's all there is to it. cson_sqlite3_stmt_to_json() does the same as cson_sqlite3_sql_to_json() but traverses a prepared sqlite3_stmt. Only queries which return data are supported (SELECT queries and SELECT-like pragmas).

Achtung #1: to enable this support, CSON_ENABLE_SQLITE3 must be defined to a true value when compiling the cson library and when compiling client code. This "unfortunate feature" is so that the amalgamation build can include the sqlite3 add-ons without breaking clients who do not use sqlite3. If you use the amalgamation build, you can edit cson_amalgamation.h and change the default value of CSON_ENABLE_SQLITE3 to 1 if you like. If you do not use the amalgamation build, you can change the default value in cson_sqlite3.h if you like (and if all clients will be linking against sqlite3).

Achtung #2: when fetching TEXT and BLOB fields from the database, results are undefined if the data is not UTF8 or NULL. i.e. if it contains UTF16 or binary data, that data will be stuffed into a JSON string, and that will cause all kinds of grief downstream. (Well, probably.)

Sample Output

The DB code:

CREATE TABLE IF NOT EXISTS stuff (name UNIQUE ON CONFLICT REPLACE, count );
DELETE FROM stuff;
INSERT INTO stuff (name,count) VALUES('this',3);
INSERT INTO stuff (name,count) VALUES('that',2);
INSERT INTO stuff (name,count) VALUES('theOtherThing',NULL);

The query:

SELECT * FROM stuff;

cson_sqlite3_sql_to_json() and friends' final parameter (a boolean) determines whether the output is in "thin" format or "fat" format.

The JSON, in "thin" mode (indented for clarity):

{
    "columns":[
        "name",
        "count"
    ],
    "rows":[
        [
            "this",
            3
        ],
        [
            "that",
            2
        ],
        [
            "theOtherThing",
            null
        ]
    ]
}

In "fat" mode:

{
    "columns":[
        "name",
        "count"
    ],
    "rows":[
        {
            "name":"this",
            "count":3
        },
        {
            "name":"that",
            "count":2
        },
        {
            "name":"theOtherThing",
            "count":null
        }
    ]
}

In the "fat" format, the order of the names in the "columns" field is guaranteed to match those of the underlying query. The order of the "rows" keys is an implementation detail, and may in fact be changed when passing the data through other JSON implementations, depending on how they implement key/value pairs in objects. In "thin" mode, the order is well-defined for both the "columns" and "rows" lists.

Note that whether or not to indent the output (i.e. to make it more human-readable) is decided when you call cson_output_xxx(), and not when the JSON object tree is generated.

Converting "Thin" to "Fat" on the Client Side

While the "thin" format is much smaller, the "fat" format is much more user-friendly. Given the thin format, we can easily transform it to fat format (or any other format) using client-side script code like this JavaScript:

function thinToFat(thin) {
    var list = [], obj, i, x, row;
    for( i = 0; i < thin.rows.length; ++i ) {
        row = thin.rows[i];
        obj = {};
        for( x = 0; x < thin.columns.length; ++x ) {
            obj[thin.columns[x]] = row[x];
        } 
        list.push(obj);
    }
    return list;
}

The output of that function, using the thin-format data shown above, is a list of objects which looks like:

[
    { "name": "this", "count": 3 },
    { "name": "that", "count": 2 },
    { "name": "theOtherThing", "count": null }
]