The latest version of SQLite has been released with support for JSON and indexes on expressions.

SQLLite is the world’s most widely used database, mainly due to its use as the database engine in memory constrained gadgets such as cellphones, PDAs, and MP3 players. It’s an in-process library that implements a self-contained, serverless, transactional SQL database engine. It doesn’t have a separate server process, and it reads and writes directly to ordinary files. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file.

The changes to the release start with the introduction of JSON SQL functions. The JSON support has been added as a loadable extension that implements eleven application-defined SQL functions and two table-valued functions that you can use to manage JSON content stored in a SQLite database. The functions deal with arrays and strings and let you validate, insert, delete and update values. The table-valued functions can be used to transform a JSON string into a virtual table, mapping the JSON elements to rows.

Version 5 of the full text search engine, FTS5, has also been added to this release. FTS5 is a SQLite virtual table module that provides full-text search features. It is an updated version of FTS4 that includes various fixes and solutions for problems that could not be fixed in FTS4 without sacrificing backwards compatibility. The main difference is that in FTS3/4, every instance-list is stored as a single large database record, whereas in FTS5 large instance-lists are divided between multiple database records. This means that is able to load instance-lists into memory incrementally to reduce memory usage and peak allocation size. This also means that if an instance-list grows so large that it exceeds the SQLITE_MAX_LENGTH limit, FTS3/4 is unable to handle it. FTS5 does not have this problem. The changes mean that complex queries may use less memory and run faster using FTS5.

Support for indexes on expressions is another addition to the new version, meaning you can create indexes on expressions referring to columns, in addition to the usual indexes on columns. Table-valued function support has also been added.  The full list of changes can be seen in the Release Log.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info