SQLite 3.25 Adds Window Functions and Improves ALTER TABLE

The SQLite 3.25 release had two significant changes: Window Functions and an improved ALTER TABLE command.

ALTER TABLE

If you’ve been using SQLite for a while you probably know that the ALTER TABLE command has always been pretty restrictive. It really only let you change the table name or add new columns. You could not modify or remove columns. This meant that in order to modify or remove columns from a table you typically used a multi-step process of creating the new table the way you wanted, copying the data to it and then renaming the two tables so the new table now has the name of the original table. It was a hassle. And because this was manual it would break any triggers or views that used the old name.

Now with SQLite 3.25 you can directly rename columns on a table, which will update triggers and views as necessary.

The syntax is as you might expect:

ALTER TABLE MyTable RENAME COLUMN OldColumn TO NewColumn;

In Xojo code you send this command to the database using the SQLExecute method. Here’s an example that changes a column name:

Dim sql As String = "ALTER TABLE Team RENAME COLUMN Coach To HeadCoach;"
DB.SQLExecute(sql)

Sadly you still cannot remove a column so you’ll have to resort to the manual method described above.

Window Functions

According to the SQLite docs:

A window function is a special SQL function where the input values are taken from a “window” of one or more rows in the results set of a SELECT statement.

SQLite now has these built-in Window functions:

• row_number()
• rank()
• dense_rank()
• percent_rank()
• cume_dist()
• ntile(N)
• lag(expr), lag(expr, offset), lag(expr, offset, default)
• lead(expr), lead(expr, offset), lead(expr, offset, default)
• first_value(expr)
• last_value(expr)
• nth_value(expr, N)

This example uses the row_number function to assign a row number based on ordered City names while still ordering the overall results by team name:

SELECT Name, City, row_number() OVER (ORDER By City) AS row_num FROM Team ORDER BY Name;

Window Functions are powerful and can get complex quickly. Read more about them at the official SQLite Window Function doc page.