sqlite3_serializer

• Can be used as a conventional Serializer. That is, just like another data format.
• Can be hooked in to s11nlite via s11nlite::interface(), which enables it to intercept all save/load requests on behalf of s11nlite.

Main features

• Can be used with s11nconvert and s11nbrowser to convert s11n data to and from databases.
• Database-stored s11n data can be queried and edited via SQL tools like sqlite's console.
• Can save directly to a database or to SQL. When writing to files it uses databases and over streams it writes SQL (the db layer cannot deal with streams).
• Versions => 2005.12.09 can deserialize from both SQL and database files. (The first release could not read from SQL at all.) It can load SQL over streams, but not databases over streams (again, the db layer can't work with streams).

Known caveats and bugs:

• When saving to streams it writes SQL. When saving to files it writes sqlite3 databases.
• It reads really slowly on large data sets. On small- to mid-sized sets (say, up to 5k nodes), it reads around 10-15k objects/second. On large sets (say, 10k nodes + 10k properties) it reads much, much more slowly (a few hundred nodes/second). For small sets it writes database almost as quickly as other formats, slowing down notably large data sets. (i don't yet know if the "large DB problem" is an inherent property of sqlite or a problem in my implementation.) Writing SQL is as fast as, if not faster than, most other formats.
• When writing SQL, the output can be huge. When writing to a db we have programmatic access to things like database record IDs, so we can build up our DOM relationships via the C API. In SQL we do not, so it outputs lots of extra code in order to build the parent/child relationships as the object nodes are inserted. On one 55k-object dataset, with an additional 50k properties, the SQL output was 23MB (compared to a 6MB database). On the other hand, outputing the SQL is much, much faster than writing to a database.
• Deserializing from SQL is rather inefficient because to parse the SQL we create an in-memory database. We don't buffer the SQL itself (only a few lines at a time in the normal case), but the db itself effectively is a buffer, and will be of a size proportional to (but likely larger than) the object tree we are deserializing.

Using the Serializer via s11nlite:

std::string found = s11n::plugin::open( "sqlite3_serializer" );

#include <s11n.net/s11n/io/sqlite3/s11nlite_api.hpp>
...
s11n::io::sqlite3::s11nlite_api apihandler;
s11nlite::instance( & apihandler );

Using the Serializer via s11nconvert and s11nbrowser:

~> s11nconvert -dl sqlite3_serializer ... other args ...

~> alias sc='s11nconvert -dl sqlite3_serializer'
# List known Serializers/formats:
~>sc -K
51191011 compact expat funtxt funxml parens simplexml sqlite3 wesnoth
# Convert a large s11n file to SQL:
~> time sc -f 54400.s11n -s sqlite3 > 54400.sql
real    0m3.356s
user    0m2.984s
sys     0m0.264s
# Convert that same file to a database:
~> time sc -f 54400.s11n -s sqlite3 -o 54400.sq3
real    0m9.154s
user    0m9.001s
sys     0m0.140s
# Now browse the data using a db client:
~> sqlite3 54400.sq3
SQLite version 3.2.7
Enter ".help" for instructions
sqlite> select count(*) from p; select count(*) from n;
55552
54400
sqlite> .schema
CREATE TABLE n(rowid INTEGER PRIMARY KEY AUTOINCREMENT,parent_id INTEGER, class, name);
CREATE TABLE p(node_id INTEGER REFERENCES n(rowid),key TEXT,value TEXT);
CREATE INDEX ndx_nodes ON n (rowid,parent_id);
CREATE INDEX ndx_props ON p (node_id);
CREATE TRIGGER tr_cleanup_node BEFORE DELETE ON n
FOR EACH ROW BEGIN
DELETE FROM p WHERE node_id = OLD.rowid;
DELETE FROM n WHERE parent_id = OLD.rowid;
END;
# We could alternately create a new database by importing the SQL:
~> sqlite3 -init 54400.sql
# If you want to save the results in a non-temporary DB:
~> sqlite3 -init 54400.sql mydatabase