SQLite 3.31.1 New Features – Xojo Programming Blog

Xojo 2020 Release 1 upgraded SQLiteDatabase to SQLite 3.31.1. The prior version of Xojo used SQLite 3.29.0. Although that seems like only a small jump there are a few notable new features that you can now use.

Order Your Nulls

You can now control where NULL values appear in columns when you use ORDER BY. Normally NULLs appear first in ascending sorts and last in descending sorts. But you can reverse that by using the NULLS FIRST or NULLS LAST syntax like this:

SELECT Name FROM Team ORDER BY Team NULLS LAST

PRAGMA Lists

There are three new PRAGMA commands that return various meta data about SQLite:

PRAGMA function_list
PRAGMA module_list
PRAGMA pragma_list

You won’t really use this in your code, but they can be useful as a sort of help in your database tool to show you what’s available. I think PRAGMA function_list will be the most useful one.

Generated (Computed) Columns

I’ve saved the best for last. You can now Add columns to your tables whose values are calculated from other column values using standard operators and built-in SQLite functions (see the function_list above).

SQLite calls these “generated columns”, but “computed columns” or “calculated columns” are other terms for them.

These columns can be either Virtual or Stored. A Virtual Generated Column is calculated each time it is accessed so it can be slower, especially if the calculation is complex. A Stored Virtual Column is calculated when the row is added or updated and saved in the database. This means it will take up space in the database file, but will return its result faster.

To create one of these columns you use the GENERATED command after the type followed by the calculation after the AS and ended with either VIRTUAL or STORED. For example:

CREATE TABLE Product (
  ID INTEGER PRIMARY KEY,
  Price REAL,
  Quantity INT,
  Total INT GENERATED AS (Price*Quantity) VIRTUAL
)

You can INSERT some data into this table:

INSERT INTO Product (Price, Quantity) VALUES (5.00, 2)

Now you can query the table and ask for the Total column:

SELECT Total FROM Product

The result returns 10.

These type of columns are common in more powerful databases and it is great to see them in SQLite. Learn more about Generated Columns at the official SQLite doc page: https://sqlite.org/gencol.html