SQLite 3.16 Adds Pragma Functions
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.

sqlite

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, FacebookGoogle+ or Linkedin.

 

Banner


Malmo AI Available On Github
28/12/2016

Microsoft's Project Malmo platform for AI experimentation is now available on Github. The Malmo platform is built on top of Minecraft and uses its experiences and interactions to learn how to interact [ ... ]



Google Closing Much Of Hangouts API
11/01/2017

Google is shutting the Hangouts API, so that even existing apps that use it will cease working from April onwards.


More News

 

 
 
 

blog comments powered by Disqus

Last Updated ( Wednesday, 04 January 2017 )
 
 

   
RSS feed of news items only
I Programmer News
Copyright © 2017 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.
Banner