SQLite Improves Nulls Support
Written by Kay Ewbank   
Thursday, 10 October 2019

The latest release of SQLite is available with support for Nulls in Order By clauses and the ability to use Filter on aggregate functions.

SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. It supports full text search and has extensions offering JSON support. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file. The developers say SQLite is the most used database engine in the world, as it is built into all mobile phones, major web browsers and most operating systems. 

sqlite

The new filter clause lets you add an expression of the form Filter (Where expr) to the aggregate function. This will ensure that only only rows for which the expr is true are included in the aggregate.

The improved handling of Nulls in Order By overcomes the fact that SQLite treats Null values as being smaller than any other values for sorting purposes. Until now this has meant that rows with a Null value in the Order By column would appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can now be changed using either "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.

Index Info and xinfo pragmas have both been improved so they return information about the way tables created using Without RowIS are stored. Pragmas are used to return information about various aspects of data; for example, information about the columns in an index can be read using the index_info pragma like this:

PRAGMA index_info('idx52');

This returns one row for each key column in the named index. If there is no index named index-name but there is a Without RowID table with that name, the pragma now returns the Primary Key columns of the Without RowID table as they are used in the records of the underlying b-tree; in other words, with duplicate columns removed. Other Pragma handling features are now enabled by default.

Elsewhere, the schema parser has been changed so that it throws an error if various elements of the sqlite_master table have been corrupted. 

sqlite

More Information

SQLite Site

Related Articles

SQLite Introduces Vacuum Into

SQLite Adds Zipfile Support

SQLite 3.20 Improves Query Planner

SQLite 3.17 Adds SHA1 Extensions 

SQLite 3.16 Adds Pragma Functions  

SQLite 3.15 Adds Row Value Support

Portable Version Of DB Browser For SQLite

SQLite 3.9 

SQLite Updated

LiteCLI - SQLite Client with Autocomplete 

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.

Banner


PostgreSQL 12 Released
14/10/2019

PostgreSQL 12 has been released with improvements to partitioning and handling of btrees, along with support for multi-column most-common-value statistics.



Project Jacquard Products Materialize
09/10/2019

Google's Project Jacquard has finally launched wearables that seem stylish and incorporate technology in an appropriate and practical manner. Your choices are an Yves St Laurent backpack and range of  [ ... ]


More News

graphics

 



 

Comments




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

Last Updated ( Thursday, 10 October 2019 )