SQLite developers often prefer to work on database internals. For an external observer nothing really changes. 2020 was a pleasant exception - SQLite received a bunch of nice features for ordinary users, such as generated columns,
UPDATE FROM and fantastic
.mode box in the shell.
There’s every chance that 2021 will continue the tradition. Here’s what shipped in the 3.35 release today:
- Math functions ‼️
- Column removal ❗
RETURNINGprocessed rows for
- Materialized CTEs.
Here are some details about each feature.
For many years, users literally begged SQLite devs to add basic functions like
pow(). The answer was always about the same (not a direct quote):
SQLite is called ‘lite’ for a reason. If you need functions, add them yourself.
An understandable position indeed. But refusing to add the square root? At the same time implementing window functions, recursive queries and other advanced SQL magic? Seriously?
Maybe SQLite developers prefer to focus on features that large customers are willing to pay for. Anyway, after 20 years we now have mathematical functions!
Here is the full list:
acos(X) acosh(X) asin(X) asinh(X) atan(X) atan2(X,Y) ceil(X) ceiling(X) cos(X) cosh(X) degrees(X) exp(X) floor(X) ln(X) log(B,X) log(X) log10(X) log2(X) mod(X,Y) pi() pow(X,Y) power(X,Y) radians(X) sin(X) sinh(X) sqrt(X) tan(X) tanh(X) trunc(X)
Probably the second most popular source of user suffering. It’s incredibly annoying that you can create as many columns as you want, but there is no way to delete them. Want to delete a column? Make a copy of the table without it, and delete the old one.
Now this pain is also going away!
ALTER TABLE DROP COLUMN, how long have we been waiting for you.
To delete a column, SQLite have to completely overwrite the table - so the operation is not fast. But it’s still nice.
UPDATE queries now can return the rows that they deleted, added, or changed respectively.
E.g., return the
id of a new record:
create table users ( id integer primary key, first_name text, last_name text ); insert into users (first_name, last_name) values ('Jane', 'Doe') returning id;
Or return products with increased price:
update products set price = price * 1.10 where price <= 99.99 returning name, price as new_price;
CTE (Common Table Expression) is a great way to make a query more expressive. E.g., count the number of cities founded in each century:
create table city( city text, timezone text, geo_lat real, geo_lon real, population integer, foundation_year integer ); -- insert data ... with history as ( select city, (foundation_year/100)+1 as century from city ) select century || ' century' as dates, count(*) as city_count from history group by century order by century desc;
If the same CTE occurs multiple times in a query, SQLite calculates it multiple times. For large tables this may not be fast.
With a materialized CTE, SQLite executes the query once, remembers the result and does not recalculate it (within the same query):
with history as materialized ( select ... ) select ... from history where ... except select ... from history where ... ;
Four great features, all in the same release! Awesome ツ
Follow @ohmypy on Twitter to keep up with new posts!