cson  Artifact Content

Artifact 8f991f78510d692d525e9b0f719f9227cf138f42:

Wiki page [cson_sqlite3] by stephan 2011-05-09 17:58:45.
D 2011-05-09T17:58:45.496
L cson_sqlite3
P 343e33bb6f98fc3fbfa062ae8799f9e3e95804d2
U stephan
W 5028
<strong>ACHTUNG: THIS PAGE IS NOW MAINTAINED IN THE NEW WIKI:</strong> [http://whiki.wanderinghorse.net/wikis/cson/?page=cson_sqlite3]


See also: [cson_cpdo]

<h1>cson_sqlite3</h1>

<tt>[/finfo?name=include/wh/cson/cson_sqlite3.h|cson_sqlite3.h]</tt> includes code for returning [http://sqlite.org|sqlite3] database query results as JSON.

Here's a demonstration:

<verbatim>
#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);
}
</verbatim>

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

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

<b>Achtung #2:</b> 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.)

<h2>Sample Output</h2>

The DB code:
<verbatim>
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);
</verbatim>

The query:
<verbatim>
SELECT * FROM stuff;
</verbatim>

<tt>cson_sqlite3_sql_to_json()</tt> 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):

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

In "fat" mode:

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

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 <tt>cson_output_xxx()</tt>, and not when the JSON object tree is generated.

<h2>Converting "Thin" to "Fat" on the Client Side</h2>

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:

<verbatim>
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;
}
</verbatim>

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

<verbatim>
[
    { "name": "this", "count": 3 },
    { "name": "that", "count": 2 },
    { "name": "theOtherThing", "count": null }
]
</verbatim>

Z 6bc50cb83c788718de01fad4d93b077b