Что нового в SQLite 3.35

В новых релизах разработчики SQLite часто перебирают движок так и сяк, а для внешнего наблюдателя ничего особо не меняется. 2020 год стал приятным исключением — добавили кучу приятных фич для пользователей, вроде вычисляемых столбцов, UPDATE FROM и великолепного .mode box в консоли.

Есть все шансы, что 2021 год продолжит традицию. Вот что сделали в релизе 3.35 (вышел 12 марта):

1. Математические функции ‼️

2. Удаление столбцов ❗

3. Возврат обработанных строк для DELETE, INSERT и UPDATE (выражение RETURNING).

4. Материализованные CTE.

Подробнее о каждой возможности:

Математические функции

Много лет авторов SQLite буквально умоляли добавить базовые функции вроде sqrt(), log() и pow(). Ответ всегда был примерно одинаковый:

SQLite не просто так называется «lite». Нужны вам функции — добавьте сами.

В целом понятная позиция. Но не добавлять квадратный корень? И при этом реализовать аналитические функции, рекурсивные запросы и прочую продвинутую SQL-магию? Серьезно?

Возможно, дело в другом, и разработчики SQLite делают те фичи, за которые готовы платить крупные клиенты. Так или иначе, спустя 20 лет у нас появились математические функции!

Вот полный список:

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) 

Удаление столбцов

Наверно, второй по популярности источник страданий разработчиков. Раздражает, что можно создать сколько угодно столбцов в таблице, а удалить нельзя. Хочешь удалить столбец — делай копию таблицы без него, а старую удаляй.

Теперь эта боль тоже уйдет! ALTER TABLE DROP COLUMN, как долго мы тебя ждали.

Чтобы удалить столбец, SQLite придется полностью перезаписать таблицу — так что операция небыстрая. Но все равно приятно.

RETURNING

Из запросов DELETE, INSERT и UPDATE теперь можно возвращать строчки, которые они удалили, добавили или изменили.

Например, можно вернуть идентификатор новой записи:

create table users (   id integer primary key,   first_name text,   last_name text );  insert into users (first_name, last_name) values ('Нина', 'Жукова') returning id; 

Или вернуть товары, которым повысили цену:

update products set price = price * 1.10 where price <= 99.99 returning name, price as new_price; 

Материализованные CTE

CTE, или Common Table Expression — отличный способ сделать запрос короче и выразительнее. Например, посчитать количество городов, основанных в каждом столетии:

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 || '-й век' as dates,   count(*) as city_count from history group by century order by century desc; 

Если одно и то же CTE встречается в запросе несколько раз, SQLite каждый раз его вычисляет. Для больших таблиц это может быть небыстро.

С материализованным CTE SQLite выполнит запрос один раз, запомнит результат, и не будет его пересчитывать (в пределах запроса):

with history as materialized (   select ... ) select ... from history where ... except select ... from history where ... ; 

И все это в одном релизе! Невероятно ツ

Если интересно, как использовать SQLite в повседневных задачах — подписывайтесь на канал @sqliter