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


Python Overtakes Java In TIOBE Index
06/11/2020

This month's TIOBE brings dramatic news. Python has replaced Java as the second most popular language. Although this was bound to happen sometime, we had not imagined it would be this soon.



Guido van Rossum Joins Microsoft
13/11/2020

Creator of Python, Guido van Rossum, has joined Microsoft as a Distinguished Engineer in the Developer Division. His stated aim is to make Python better. We can't wait to find out what that results in [ ... ]


More News

square

 



 

Comments




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

Last Updated ( Thursday, 10 October 2019 )