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
Sort of. I’ve run into situations with really long release-cycle distros (in this case, it was CentOS 7) having too old of a glibc version to compile sqlite versions newer than whatever was out in 2013.
This was actually a problem with running Django 2.0+ on those distros, as sqlite was a fairly hard requirement and it needed something much newer. Honestly, it’s stuff like this that makes me never want to use CentOS/RHEL or any other “super stable” distro
Agreed. But there are advantages to linking with SQLite as a dylib. Having multiple copies of SQLite statically linked into your executable can actually cause database corruption (they can step on each others’ file locks), so you have to be careful if one of your dependencies uses SQLite too. (This is documented in that How To Corrupt A Database page on the SQLite website.)
You don’t need generated columns to do this; they’re just syntactic sugar. All you have to do is use json_extract(...) in place of a column name, in any query or CREATE INDEX command. SQLite has supported indexes on expressions for years.
What is the behavior of inserts into a table where a row causes the index expression to error? The nice thing about the column method in the OP is that it can enforce constraints.
IIRC json_extract returns NULL on a parse error. I agree, the syntax checking constraint is nice. You could do it with a TRIGGER too, probably. I’m just pointing out that you don’t need the latest SQLite to do this stuff.
But… why? SQLite is ridiculously easy to compile from source thanks to the amalgamation.
Sort of. I’ve run into situations with really long release-cycle distros (in this case, it was CentOS 7) having too old of a glibc version to compile sqlite versions newer than whatever was out in 2013.
This was actually a problem with running Django 2.0+ on those distros, as sqlite was a fairly hard requirement and it needed something much newer. Honestly, it’s stuff like this that makes me never want to use CentOS/RHEL or any other “super stable” distro
Agreed. But there are advantages to linking with SQLite as a dylib. Having multiple copies of SQLite statically linked into your executable can actually cause database corruption (they can step on each others’ file locks), so you have to be careful if one of your dependencies uses SQLite too. (This is documented in that How To Corrupt A Database page on the SQLite website.)
Interesting. I guess that this mistake isn’t very difficult to do with a large and complex program.
You don’t need generated columns to do this; they’re just syntactic sugar. All you have to do is use
json_extract(...)in place of a column name, in any query or CREATE INDEX command. SQLite has supported indexes on expressions for years.What is the behavior of inserts into a table where a row causes the index expression to error? The nice thing about the column method in the OP is that it can enforce constraints.
IIRC json_extract returns NULL on a parse error. I agree, the syntax checking constraint is nice. You could do it with a TRIGGER too, probably. I’m just pointing out that you don’t need the latest SQLite to do this stuff.
Nice feature from sqlite.