The latest version of SQLite has been released with experimental support for PRAGMA functions.

SQLite is a widely used embeddable SQL database library that is found in many memory constrained gadgets such as cellphones, PDAs, and MP3 players.

SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file.

The updated version uses 9% fewer CPU cycles, continuing the performance gains seen in recent versions. The number of cycles used by SQLite on a standard workload has halved in just the past three years. The graph below shows how CPU usage has dropped over recent versions:

The experimental support for PRAGMA functions opens the possibility of using functions such as index_info pragma to return results that have no side-effects from ordinary SELECT statements as table-valued functions. SQLite's PRAGMA command can be used to control various environmental variables and state flags within the SQLite environment. There are corresponding table-valued functions with the same name as the PRAGMA with a 7-character "pragma_" prefix.

For example, information about the columns in an index can be read using the index_info pragma as follows:

PRAGMA index_info('idx52');

Or, the same content can be read using:

SELECT * FROM pragma_index_info('idx52');

The advantage of the table-valued function format is that the query can return just a subset of the PRAGMA columns, can include a WHERE clause, can use aggregate functions, and the table-valued function can be just one of several data sources in a join.

The date and time functions are another area that has been improved, so that the 'unixepoch' modifier works for the full span of supported dates. The "unixepoch" modifier causes dates in the DDDDDDDDDD format to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970.

Partial indexes are another area to have been improved. If you create a partial index on "WHERE x NOT NULL", it will now be usable even if the "x" column appears in a LIKE or GLOB operator. LIKE and GLOB have also received attention in the LIKE and GLOB matching algorithm, which is now faster for cases when the pattern contains multiple wildcards.

The 3.16 release has already received an update to 3.16.1 as a bug that affected the use of row values within triggers was reported 'moments' after the 3.16.0 release was published. This has now been fixed.

More Information

SQLite Download

Related Articles

SQLite 3.15 Adds Row Value Support

Portable Version Of DB Browser For SQLite

SQLite 3.9 

SQLite Updated

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

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