

Support for indexes that use deterministic expressions - mingodad
https://www.sqlite.org/src/info/2131a5ca53f0e9b0

======
mingodad
Example using the json1 extension:

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;

------
JohnBooty
If I understand correctly and this is similar to Postgres' functionality, this
is really awesome for any calculated value (particularly anything expensive)
that you frequently need to search on.

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

Hooray SQLite!

------
ThePhysicist
SQLite gets better and better all the time. Considering that hard disks and
memories get faster and cheaper as well SQLite is even becoming a serious
option for handling "big data".

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.

~~~
stevoski
If SQLite can handle your data handling needs, then almost by definition you
are not doing "big data".

Quoting my friend Wikipedia: "Big data is a broad term for data sets so large
or complex that traditional data processing applications are inadequate."

But yes, I agree that SQLite gets better all the time.

------
aw3c2
I really wish things like blog and forum software would use sqlite more often.
It makes installation and backup trivial while being good enough for the vast
majority of cases.

------
mappu
That puts it one (more) step ahead of MySQL, which still doesn't support
function indexes in 5.6.

