Support for indexes that use deterministic expressions

BEGIN TRANSACTION;

CREATE TABLE json_tbl(id integer primary key, json text collate nocase); CREATE VIEW json_tbl_view AS SELECT id, json_extract(json, '$.value') AS val FROM json_tbl;

CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_view WHERE 'the_value_33' = val;

COMMIT;

It makes inserts and updates more expensive, because the calculated values must be recalculated, but it can be an awesome win.

Hooray SQLite!

I use it in several of my personal and professional projects, especially in cases where data can be sharded well. The one thing that is a bit annoying though is the locking mechanism in SQLite, which prevents simultaneous writes to the database and locks it for reading while a write transaction is underway. If they could find a way to improve that behavior I think the use cases of SQLite would grow way beyond embedded application databases. Whether or not this is a good thing is of course a different question, but having a zero-configuration "plug-and-play" database engine could be a very attractive proposition to many people.

BerkeleyDB for example, which handles concurrent access much better than SQLite and also comes in a high-availability version is used in many production environments today, most notably as the backend of Amazon's DynamoDB key/value store.