On Sun, Jan 21, 2018 at 3:50 PM, Stadin, Benjamin < benjamin.sta...@heidelberg-mobil.com> wrote:
> Hi Simon, > > I recently wrote a tool to convert an arbitrary SQLite result set to > properly typed json key/value pairs, using the SQLite type affinity of the > objects. Though the code is in C++. But it gives an idea how simple this is > when with a JSON library (I'm using RapidJson). Rapidjson can also be used > in buffered mode, so it's also easily possible to write out large tables of > any size. > > The code for the SQLite to Json exporter is below. For an importer using > Rapidjson, I can imagine this can be done fairly simple and efficiently > (SAX parsing mode) with a custom "filter" handler. For example, it should > be possible to keep track of when an object begins and ends, collect all > values as variant values with json type info, and write out each complete > object with proper types and using a prepared statement. > > Regards > Ben > > > Or in javascript... var sack = require( 'sack.vfs' ); function sqliteQueryToJson( dbPath, sql, jsonFile ) var db = sack.Sqlite( dbPath ); sack.Volume().file( jsonFIle).write( JSON.strinigfy( db.do( sql ) ) ); sack.JSON and sack.JSON6 have streaming JSON parsers... can read a stream of records with a callback for each value or object discovered along the stream. > Am 21.01.18, 06:55 schrieb "sqlite-users im Auftrag von Simon Slavin" < > sqlite-users-boun...@mailinglists.sqlite.org im Auftrag von > slav...@bigfraud.org>: > > Feature request for the Shell Tool: ".mode json". > > Output should be as a JSON array of objects, with one object for each > row of the table. Output should start with the "[" character and end with > "]". Rows should be separated with ",\n". Quotes in strings should be > escaped for JSON, with a leading backslash. NULL should be supported as > the four lower-case characters "null", ignoring ".nullvalue". > > The above setting should also affect the ".import filename [table]" > command as described in section 8 of <https://sqlite.org/cli.html> . > Columns should be created as necessary. Signed zeros should be imported as > zero. > > The above facilities should be implemented whether or not > DSQLITE_ENABLE_JSON1 was enabled when compiling the shell tool. They are > for export and import, not for handling JSON within SQLite. > > Implementation questions: > > I am not sure what the program should do if asked to import a value > which is an array or object. Perhaps, for compatibility with the JSON1 > extension, those should be imported as a string. > > I am not sure whether the program should respect the settings for > ".separator" for JSON mode, either for output or .import. > > I am not sure how BLOBs should be handled, either for output or > .import. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
