In Xojo 2022r4, we have updated our SQLite library to SQLite v3.39.4. Previously Xojo was using 3.36.0, and since there have been quite a few SQLite updates since then I thought I’d highlight a few notable features:
- STRICT tables
- PRAGMA table_list
- RIGHT and FULL OUTER JOIN
- Built-in JSON support
More about each of these below.
STRICT Tables
One of SQLite’s most unusual capabilities is that it does not care about what data goes into a column. Although you could specify a type for a column, it was really more of a suggestion as other types of data could be put into the column. This behavior is different than most other relational databases and can sometimes be a source of confusion.
Now you can create your tables using the STRICT keyword to force them to require column types and to force the column types to always be checked when putting data into them.
You are still limited to INT, INTEGER, REAL, TEXT and BLOB. That means there is still no DATE or DATETIME type like you might find in other databases. Instead use TEXT with YYYY-MM-DD format.
However, an ANY type was added which essentially allows you to clearly state that the column can contain anything. This allows you to have a mixture of specific types and generic types in your STRICT tables.
Note that the STRICT keyword goes at the end of the CREATE TABLE command:
CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID)) STRICT;
The SQLite docs have more information about the new STRICT table feature.
PRAGMA table_list
With Xojo you could always get the list of tables by using the Database.Tables() method. However if you wanted to get the list of table using SQL you had to directly query the sqlite_master table.
Now there is a simple PRAGMA that can do the same thing:
PRAGMA table_listIt returns a list of tables and some other details about the table (which may change over time according to the SQLite docs).
RIGHT and FULL OUTER JOIN
Joining tables is a common task with SQL. The most common type of join is an INNER JOIN where only the rows common to both tables are included in the result. Other less common types of joins include LEFT OUTER, RIGHT OUTER and FULL OUTER (sometimes OUTER is omitted when referring to these types of joins).
SQLite has had support for LEFT OUTER joins for a long time, but support for RIGHT OUTER and FULL OUTER were missing. But now they are here, giving your more complicated queries better compatibly with the “big name” databases.
Learn more about these types of joins at W3 schools.
JSON Support
I’ve saved the big one for last: your SQL databases can now work with JSON data within columns.
Here is a sample table to work with with some JSON data that is stored in the players column:
CREATE TABLE team(id INTEGER PRIMARY KEY, Name TEXT, players TEXT);
INSERT INTO TEAM VALUES (NULL, 'Seagulls', '[ {"Name":"Bob","position":"1B"}, {"Name":"Tom","position":"2B"} ]')
INSERT INTO TEAM VALUES (NULL, 'Pigeons', '[ {"Name":"Bill","position":"1B"}, {"Name":"Tim","position":"2B"} ]')
INSERT INTO TEAM VALUES (NULL, 'Crows', '[ {"Name":"Betty","position":"1B"}, {"Name":"Tina","position":"2B"} ]')
Let’s say you want to get the first player on each team. Without SQLite JSON support you would have to pull out the JSON column data and parse it out separately. But now you can do it with this SQL like this:
SELECT players -> 0 FROM team
The above SQL says: for each row fetch the first array element from the JSON data in players.
This is how you would list all the players on all the teams:
SELECT team.Name, json_each.value -> 'Name' FROM team, json_each(team.players)
And if you want to get the actual value without the quotes, you can use the ->> operator (and also rename the result):
SELECT team.Name, json_each.value ->> 'Name' As PlayerName FROM team, json_each(team.players)
The SQLite JSON support can do much, much more which you can read about on the SQLite JSON doc page.
Paul learned to program in BASIC at age 13 and has programmed in more languages than he remembers, with Xojo being an obvious favorite. When not working on Xojo, you can find him talking about retrocomputing at Goto 10 and on Mastodon @lefebvre@hachyderm.io.