Also the repo class stores write and delete timestamps as separate columns so I can have CDC. CDC is used for building cached view models and is pushed to object storage every 5 minutes for backup as NDJSON. Another process on home server is restoring the db every couple of minutes for second backup and ready to use DB in case it's needed.
I know there are things like Litestream, I wanted something in process and something that can send alerts on failed backups.
It would be super cool if somehow SwiftData could translate computed properties of @Model objects into these generated columns via the #Expression macro!
JSON extensibility and virtual columns help _a lot_ with variable metadata.
Here's an example i saw yesterday from mariadb, which is improving its json support in its upcoming releases.
https://mariadb.com/docs/server/ha-and-performance/optimizat...
``` CREATE TABLE t1 (json_data JSON); INSERT INTO t1 VALUES('{"column1": 1234}'); INSERT INTO t1 ... ```
In order to do efficient queries over data in JSON, you can add a virtual column, and an index on that column:
``` ALTER TABLE t1 ADD COLUMN vcol1 INT AS (cast(json_value(json_data, '$.column1') AS INTEGER)), ADD INDEX(vcol1);
```
https://youtu.be/b2F-DItXtZs?is=HlayyJ_DPb8NzbS4
(lol! couldn’t resist)
You may construct it back on retrieval, if you need it in results.
https://sqlite-tutorial-pycon-2023.readthedocs.io/en/latest/...
So I can have cassette.propxyz = {1, 2, “abc”, true}
And
local anotherprop = cassette.leprop
In code, I could effectively mark which class members need to be stored/restored, and optionally provide a custom serialization function for them if needed.
The latter was effectively never necessary, because all the bases types and multi-dimensional arrays were handled by templates.
Really wish I open-sourced that thing then, but the corporate bureaucracy around that was tricky.
I remember sufficiently little about implementation details now that I think I can get to writing it again, without producing a copypasta of that code - and maybe I should :)
Or just download the source and build it! (Gasp!)
2) "Document" has undergone a bit of semantic drift thanks to HTML and XML. In an informational context it means "structured, hierarchical unit of data containing mostly text". The data from forms, invoices, and the like needs to be collected and stored, even if it isn't properly normalized and relationalized (or is en route to being such) so a "document database" is thought to be suited to this task
I dunno, whatever, I'm in the "just fucking use postgres until you can justify why you shouldn't" camp.
But nothing prevents you from treating your relational database as a document database, set it up as a key-value store where each key is is the document title and each value is a large blob of document data. If your documents are fairly consistent it is also easy enough to build indexes and query features to regain some of the analytical ability of relational data.
The point is exactly that it means you can selectively retrospectively add virtual columns, optionally backed with an index, as you decide which fields you need more structured access to.
The example you're giving is in principle the same as the "ALTER TABLE ... GENERATED ALWAYS AS ... VIRTUAL" example + a subsequent index in the Sqlite example.
That was the part I really missed from my Google days.
That, insane achievement badges, and terrible-ideas-discuss (if anyone at Google is reading this, I have one word for you: dirigibles). It was like /r/NonCredibleDefense but for Google.
> We write software to improve our lives and the lives of others. Usually this involves taking some mundane information—such as contacts, invoices, or receipts—and manipulating it using a computer application. CouchDB is a great fit for common applications like this because it embraces the natural idea of evolving, self-contained documents as the very core of its data model.
> Self-Contained Data
> An invoice contains all the pertinent information about a single transaction—the seller, the buyer, the date, and a list of the items or services sold. As shown in Figure 1, “Self-contained documents”, there’s no abstract reference on this piece of paper that points to some other piece of paper with the seller’s name and address. Accountants appreciate the simplicity of having everything in one place. And given the choice, programmers appreciate that, too.
> Yet using references is exactly how we model our data in a relational database! Each invoice is stored in a table as a row that refers to other rows in other tables—one row for seller information, one for the buyer, one row for each item billed, and more rows still to describe the item details, manufacturer details, and so on and so forth.
https://guide.couchdb.org/editions/1/en/why.html
Iow, a document database stands in contrast to a relational db in that these JSON things we store in them are more stand-alone “documents” compared to storing data in rows and columns in a relational db like PostgreSQL or SQLite.
CREATE TABLE t1 (data JSONB);
INSERT INTO t1 VALUES ('{"column1":1234}');
CREATE INDEX t1column1 ON t1(data->'column1');
SELECT * FROM t1 WHERE data1->'column1' = '1234'; // not sure about data typeWhat I'm trying to figure out is if they're related concepts. This may be a very naive question awkwardly asked.
Basically a document is a report, a large disjoint volume of information on a subject, I consider this the natural form of data because this is how it is collected and how most people think about it. relational is sort of like storing that data as vertical slices through your stack of reports. Not natural at all but much nicer for analysis across the data set.
Personally I prefer the relational stance, and there are a lot of people who don't get it who say things like "this data isn't relational", but that's not the argument GP made.
SQLite has had JSON support for a while.
However recently it added a killer feature: generated columns. (This was added in 3.31.0, released 2020-01-22.)
This makes it possible to insert JSON straight into SQLite and then have it extract data and index them, i.e. you can treat SQLite as a document database. This has been possible with PostgreSQL and obviously is what something like Elastic provides but having it available in an embedded database is very nice for lightweight stuff.
Let's get started:
$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
body TEXT,
d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL);
sqlite> insert into t values(json('{"d":"42"}'));
sqlite> select * from t WHERE d = 42;
{"d":"42"}|42
It's that simple, the d column is extracted from the provided JSON.
(Aside: The hard bit may be getting a new enough SQLite, at the time of writing Homebrew on macOS has it, else you likely need to use an unstable source like nixpkgs-unstable.)
There's some nice properties of this. Normally it's encouraged to minifiy and validate JSON when inserting (via the json() function) as because SQLite doesn't have a JSON type it will allow anything. However nothing enforces that, you could add a constraint but will probably forget... Having GENERATED ALWAYS using json_extract means invalid JSON will get a Error: malformed JSON at INSERT time.
This can be taken further:
sqlite> CREATE TABLE x (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL);
sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: x.id
We can enforce items are present in the inserted JSON, here by adding NOT NULL, but we could also use constraints and other SQLite features!
You'll notice I've used VIRTUAL with the generated column in these examples. There's also the option of using STORED to essentially cache the values, although a downside is you can't add those columns via ALTER TABLE.
However you can always create an index on a column, even if it's defined a virtual one:
CREATE INDEX xid on x(id);
Then check that's going to work as expected:
EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
QUERY PLAN
`--SEARCH TABLE x USING INDEX xid (id=?)
Combined with ALTER TABLE we can add a new column and index it:
ALTER TABLE x ADD COLUMN text TEXT
GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
CREATE INDEX xtext ON x(text);
The benefit here is you can start off with a table which could be as simple as just a single JSON column, and add columns and indexes as you find useful data in that JSON. For example this can work really well for webhooks, insert all the data you are sent straight into a table, then pull out the useful stuff later. Have fun.
17th June 2020 in code