SQLite 3.16 Adds Pragma Functions
Written by Kay Ewbank   
Wednesday, 04 January 2017

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:

cpu usage


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.



Gemini Offers Huge Context Window

Google has announced a range of improvements to Gemini, along with the release of Gemma 2. The first enhancement is access to a 2 million context window for Gemini 1.5 Pro, backed up with context cach [ ... ]

APISEC Conference Sessions Now Available Online

The talks from APISEC|CON, the largest event dedicated to API security, are now available up on Youtube, for free. The virtual event covered AI and LLM security, defending APIs, API sprawl, gover [ ... ]

More News


kotlin book



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

Last Updated ( Wednesday, 04 January 2017 )