SQLite 3.9.0, released in Oct 2015, introduced the json1 extension that provides functions to manage JSON content stored in an SQLite database. The extension source code is included in the SQLite source amalgamation and is enabled by the -DSQLITE_ENABLE_JSON1 option.
The following assumes that the json1 functionality is available. As sample data, I use the stocks data set from jsonstudio.com. Here's what the wrapped first line of the JSON data file looks like partially:
To load the file into an SQLite database using NBSQLite3:
Note the invocation of the json() SQL function, which verifies that its argument is a valid JSON string and returns a minified version of that JSON string.
Let's inspect the created database with the sqlite3 shell tool:
The json_extract(X, P1, P2, ...) function extracts and returns one or more values from the JSON structure X:
The json_each() table-valued function walks the JSON value provided as its argument and returns one row for each element. The schema for the table returned by json_each() is as follows:
Here's json_each() in action:
Let's run the same query in Pharo:
Transcript displays thusly. As we can see, SQLite and NBSQLite3 handle numerical values in the JSON data properly.
Notice that the first row is well-formed JSON. If we use json_tree() instead of json_row(), that JSON structure will be returned as a key/value row as well.
Finally, let's read the first JSON record using NeoJSON:
This is powerful stuff. Well worth a look.
Masashi Umezawa graciously granted me write-access to the PunQLite wrapper repository. (Thanks, Masashi!) I've now added functionality to set Jx9 variables from Smalltalk.
Here's an example. The variable $inject is to be set from Smalltalk.
Here's the Smalltalk code:
Transcript should show "true". Round-trip set/get tests between Pharo and the Python wrapper by Charles Leifer were successful.
I added an assert-filled version of the above code as a unit test. Running the test by itself succeeds. Running it from Test Runner fails...
I won't go figure, though. I don't intend to spend any more time on Jx9. Using Jx9 with Smalltalk effectively requires managing Jx9 source as strings within Pharo. I might as well stick with SQLite and SQL strings in that case. If I must have key-value stores I can always use 2-column tables.
The Python wrapper carries this note: "The authors of UnQLite, Symisc, have informed me that UnQLite is no longer being developed actively."
Here's an example Jx9 program:
And here's Smalltalk code to execute the above Jx9 program:
PunQLite supports extracting values from an executed Jx9 program, as in "exe @ 'sid'" above. PunQLite currently does not support UnQLite's APIs for exchanging JSON objects between Smalltalk and an Jx9 program which provide functionality similar to parameter binding in SQL APIs. That means Jx9 programs, including data to be stored, has to be constructed from strings... As OWASP says about NoSQL injection:
Because these NoSQL injection attacks may execute within a procedural language, rather than in the declarative SQL language, the potential impacts are greater than traditional SQL injection.
I'm reading the book Seven Databases in Seven Weeks by Eric Redmond and Jim Wilson. The databases covered are PostgreSQL, Riak, HBase, MongoDB, CouchDB, Neo4J and Redis.
There are several Pharo/Squeak libraries for PostgreSQL:
- PostgresV3 on SqueakSource.
- PostgresV2 on SqueakSource.
- Squeak_PostgreSQL by Jim Menard. Last updated in 2006.
- DBXTalk, using OpenDBX, which wraps libpq. Also has GlorpDBX, which is an ORM that uses DBXTalk or the PostgresV2 driver.
HBase runs on JVM. It supports a RESTful HTTP API, Thrift, and a Java API. The first is probably the easiest way to write a Smalltalk interface.
- Estaban Lorenzano's Voyage is an object persistence layer that works with MongoDB.
- MongoTalk, by Kent Beck and others.
- SCouchDB by Igor Stasenko.
- SCouchDBViewServer, by Jesus Mari Aguirre, a fork and superset that includes other functionality.
Neo4J is a graph database. It is provides a RESTful API. I've not played with Neo4J, but I'd imagine the Smalltalk environment, and by extension any Smalltalk object persistence mechanism, make up a graph database. Probably speaking from ignorance here, but I'm not sure what interest a Smalltalk programmer will have in a graph database written in Java. :-)
Finally, there is Redis Client by Mike Hale and others.
I haven't finished the book, but so far I haven't seen any discussion on authentication or security of these HTTP-speaking NoSQL databases. If the database is lacking authentication or SSL, and if your threat model covers that, probably the easiest is to put these behind a proxy. And, for database and other such connectivity from the Smalltalk client, I suggest SpsSplitPasswordStore.