Getting Meta; A datastore in a datastore

Once upon a time, if you wanted-to store your application’s configuration parameters in SilverStripe’s database, your only choice was to use multiple Boolean or Varchar fields. That or use a separate NOSQL datastore like Redis. But do you want that overhead? Do you even have access to your host environment to install one?

Enter the JSONText module.

“This will never scale...”

One day, not so long ago, I was working on a project, and this person, whom we'll call the "product owner" (because that's who they were), wanted an unprecedented level of CMS control over how parts of their web application could be made to appear to users; accordions within accordions if I recall correctly. They wanted to be able to stipulate how wide each accordion could be within their layout's standard grid. There were several different types of these accordions and several different ways each could be displayed, depending on, well a whole bunch of things really. I think the current phase of the moon was one of them.

In this scenario, conventional wisdom would have had us implement on the order of 20 separate Varchar and Boolean fields for controlling this grim collection of foolhardy user interactivity and it got me to thinking one day after work, waiting for my train, with my laptop, in the rain, that there simply must be a better way (tm). To use more current parlance; "This could never scale"... which is of course rubbish. Of course it could scale, let's get real here; I'm a developer and I found this a sub-optimal, dirty, gutter-like approach to maintaining what were essentially terse system configuration parameters. And it got me thinking again whether there wasn't already a standard data-structure available to do this stuff with. Oh sure I mused; it's called JSON.

So twenty fields became one and each parameter became a key-value pair stored within its structure, itself stored in a standard Text field. Sounding a bit weird? Sure it is, but this approach is not unique to the module. Consider that Postgres, MySQL and SQL Server all have native JSON storage capabilities, albeit with an unstandardised query syntax.

Subversion…(No, not that one)

So how do we write data to this thing? SilverStripe's getCMSFields() method is inextricably tied to DataObject sub-classes isn't it? Well yes, but we're working with SilverStripe here and that means you can do practically anything with it, and do so fairly elegantly.

By declaring an array of field-names for each desired model, thus binding individual form-fields to specific JSON keys, the normal 1:1 data flow from form-field to DB-field is subverted into the JSON store, using that cornerstone of SilverStripe, its YAML config system.

XPath --> JSONPath

But how do we query this stuff? SQL can only be used in native JSON field-types for the likes of Postgres and MySQL but that necessitates modifications to SilverStripe's Database connectors. However; the problem did remind me how XPath works, which lead me to the Peekmo/JsonPath package, which gives us JSONPath.

Using JSONPath, we can construct XPath-like queries that allow our application access to individual JSON values via named keys, or via a key’s index, even when these data are buried many levels deep. The module gives you Postgres-like JSON query accessors: “#>” (Match by path), “->” (Match by integer) and “->>” (Match by string) as well as CSS inspired accessors in the form of first(), nth() and last(). You can also cast your returned data as "JSON", "Array" or "SilverStripe" - where results are cast to the relevant DBObject subclass.

Examples

$field = JSONText::create('MyJSON');
$field->setValue('{"a": "b", "c": "d"}');

// All return a JSON string: '{"c":"d"}'
$field->setReturnType('json');

$field->nth(0);
$field->first();
$field->query('->>', 'c');

// All return a PHP array: ['c' => 'd']
$field->setReturnType('array');   

$field->nth(0);
$field->first();
$field->query('->>', 'c'); 

// All return an instance of `DBVarchar` who's value is "d".
// If the JSON value of key "c" was `11` an instance of `DBInt` 
would be returned (If you used the int matcher: “->”), and so on
$field->setReturnType('silverstripe');

$field->nth(0);
$field->first();
$field->query('->>', 'c');

// More complex JSONPath query
$field = JSONText::create('MyJSON');
$field->setReturnType('json');
$field->setValue('{ "store": {
          "book": [{
                "category": "reference",
                    "author": "Nigel Rees"
                    },
                    {
                 "category": "fiction",
                    "author": "Evelyn Waugh"
                    }]
          }}'
     );

// Authors of all books
// Returns: "["Nigel Rees","Evelyn Waugh"]"
$field->query('$.store.book[*].author');

JSONPath also provides the ability to selectively update a blob of JSON, which JSONText also permits you to do if you're using its optional JSONTextExtension for use with the CMS, or updating the data programmatically via your app's public API. The module kinda tricks you though; you're not selectively updating anything, not at the database level anyway; the blob of JSON is put into memory and is indeed modified, but the entire stored JSON blob is then clobbered using standard ORM write logic, and written back to the relevant database field.

So you may be thinking that this is a ton of work for doing something as simple as storing config parameters in a single DB field. Well yes it is, but then work with me here; you're a developer too no? Now that we've discussed it a bit, doesn't 20 separate configuration fields irk you a bit too? If it doesn't, spend 5m looking through your old projects' DataObject and Page subclasses, chances are good there's at least one in there with a $db static that’s got 20+ entries.

Go on, tell me I'm wrong.

Using it

So what else can we do with this? Well say your application requires a timestamped notary feature applied to all uploaded PDFs (Digital notarisation via "Chainpoint Proofs" for example, where a hash of the file’s text is anchored to a decentralised system like a blockchain). Once stored, your system is passed-back a “receipt” in the form of a JSON-LD document containing the relevant timestamps and hashes.

How do we treat that JSON traditionally? Badly is how. We put it onto a cold, hard slab called a file-system and forget about it, or we write-it to the jail-cell that is a non-JSON aware DB field. But that's no help, we'd need to write an abstraction over the top to do anything more intelligent than return the entire JSON blob. So how about we free-up that data by persisting it within a JSONText field instead? The application need only call one of the module's string accessors to obtain the value of a specific key to retrieve data for.

If the module sounds useful, you can contribute! There are versions for both SilverStripe 3 and 4 and although it's fairly well-tested, there are always bugs, they just haven't been found yet.

Russ works for Catalyst I.T. as a Senior Dev on their SilverStripe Team. He has almost 20 years development experience, seven of them using SilverStripe and three and a half of those spent at SilverStripe Ltd. While SilverStripe is still his go-to tool for web-based application development inside and outside of work, web 2.0 has yielded somewhat to web 3.0 (or just "Web3") in the form of decentralised technologies like Bitcoin, IPFS, Ethereum and Hyperledger. Find him on LinkedIn and Twitter if you want to chat some more.

You can contribute to the JSONText module itself here. You can also review, comment and contribute to the new SilverStripe Verifiable module, which uses JSONText as a dependency.