Читаем и пишем файлы прямо из SQLite

У CLI-утилиты sqlite есть встроенные функции writefile и readfile. Я их немного доработал и оформил в виде расширения fileio.

Пишем в файл:

select writefile('hello.txt', 'hello world');

Читаем:

select readfile('hello.txt');

Выводим содержимое каталога:

select * from lsdir('.');

Для любителей делать все на свете, не покидая SQL ツ

Unix timestamp

SQLite 3.38 получит функцию даты/времени в формате unix timestamp! А пока он не вышел, можно попробовать в моем расширении isodate:

select unixepoch('2021-12-22 12:34:45');
1640176485

https://github.com/nalgeon/sqlean/issues/27#issuecomment-998138191

Все расширения SQLite

Мне очень нравится SQLite. Это миниатюрная встраиваемая база, которая отлично подходит как для исследовательского анализа данных, так и в качестве хранилища для небольших приложений.

Но есть у нее один недостаток: маловато встроенных функций по сравнению с PostgreSQL или Oracle.

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

Мне хотелось большой системности.

Так появился проект sqlean — в нем я собираю вместе недостающие в SQLite функции, распределяю их по модулям, рефакторю код, пишу тесты и документацию. Получается что-то вроде стандартной библиотеки, как в Python или Go, только для SQLite.

Там есть регулярные выражения, работа с файлами, мат. статистика, поддержка CSV. Даже массивы есть! (почти как в постгресе).

Планирую по каждому модулю написать отдельную статью, а пока вот краткий обзор:

https://antonz.ru/sqlean/

Стрелочка для JSON

В SQLite может появиться специальный синтаксис для JSON, как в постгресе!

Было:

select json_extract(person,'$.name') ...

Стало:

select person->'name' ...

Не 100%, но весьма вероятно.

https://sqlite.org/src/doc/json-enhancements/doc/json-enhancements.md

Гибкие типы данных

Люди часто не понимают, что за ерунда происходит в SQLite с типами данных. Давайте разберемся.

SQLite использует пять типов данных:

— INTEGER — целые числа,
— REAL — действительные числа,
— TEXT — строки,
— BLOB — бинарные данные,
— NULL — пустое значение.

Пока ничего необычного (только типов маловато по сравнению с другими СУБД).

А как вам такое:

SQLite может хранить данные любого типа — вне зависимости от того, какой тип указан на столбце.

Хотите записать число 3.14 в integer-поле? Пожалуйста (и никакого округления). Или, может быть, 'привет' в поле типа real? Без проблем.

Дело в том, что SQLite хранит тип не только на столбце, но и на каждом значении в таблице. Именно поэтому в одном столбце без проблем хранятся значения разных типов. Тип на столбце используется как рекомендация: при вставке SQLite пытается привести значение к рекомендуемому типу, но если не получилось — сохраняет «как есть».

Это удобно для анализа данных — можно сначала все загрузить, а потом средствами SQL разбираться с проблемными значениями. Любая другая СУБД выдаст ошибку при импорте и заставит вас «шерстить» файл скриптами или вручную.

С другой стороны, это неожиданное поведение, за которое многие недолюбливают SQLite и даже прозвали его «JavaScript в мире баз данных». Со временем разработчики SQLite прислушались и сделали альтернативный механизм «строгих» таблиц — но о нем в другой раз.

Люди и код

Неожиданно для себя стал гостем подкаста. Мы с Тимуром Тукаевым хотели сделать статью, но в аудио-формате она получилась живее и интереснее.

Послушайте, если вам интересно про sqlite, мои проекты или открытый код в целом.

Конкретно sqlite обсуждаем с 12:15.

https://we.fo/1604736632

P.S. Поскольку к записи мы специально не готовились, звучу я как из ведра. Но если потерпеть минуту, вы привыкнете 😁

Новости песочницы

Обновил SQLite-песочницу! Самое главное — теперь можно сохранять сниппеты, не указывая API-ключ гитхаба. Жмакнули на «share» и готово. Так что песочницей смогут пользоваться люди, далекие от гитхаба 🎉

Еще обновил SQLite до свежайшей версии 3.37.2. И включил пачку стандартных расширений вроде R*Tree. А в планах еще приделать все расширения из sqlean, вот тогда заживем вообще.

https://sqlime.org/

Как хранить дату

В SQLite нет отдельных типов для даты и времени. Поэтому у вас есть только два варианта, каждый со своими достоинствами и недостатками. Рассмотрим их вкратце.

ISO 8601

Международный стандарт, который записывает дату как 2022-01-27, а дату-время как 2022-01-27 19:30:15. Иногда добавляют еще миллисекунды: 2022-01-27 19:30:15.123.

Стандарт включает кучу нюансов, но основные формы записи именно такие.

Дата при этом хранится в SQLite как текст. Занимает 12 байт (без времени), 21 байт (до секунд) или 25 байт (с миллисекундами).

➕ Человекочитаемая дата
➕ Удобно сравнивать ('2021-12-30' < '2022-01-27')
➖ Неудобно считать разницу между датами
➖ Занимает много места

Unix Time

Unix Time — это количество секунд, прошедшее с 01.01.1970. Например unix time для 2022-01-27 19:30:15 = 1643311815, а для 1960-03-25 = -308361600.

Дата при этом хранится в SQLite как число: целое, если достаточно точности до секунды, или действительное — если нужны миллисекунды. Занимает 8 байт.

➕ Удобно сравнивать
➕ Удобно считать разницу между датами
➕ Компактное представление
➖ Непонятна человеку

Часовые пояса

Если вы работали раньше с «международными» датами в любом виде, то знаете, как больно бывает от часовых поясов. В SQLite с ними все просто: часовые пояса не поддерживаются.

Поэтому, какой бы формат вы не выбрали (ISO 8601 или Unix Time), соблюдайте главное правило:

Хранить даты только в часовом поясе UTC

Иначе никогда концов не найдете.

⚫ В О Й Н А

Курс «SQLite на практике»

Запускаю новый курс! Подойдёт всем, кто знаком с основами SQL, и хочет узнать о возможностях и особенностях SQLite.

Начал с трех модулей, хорошо знакомых выпускникам другого моего курса, «SQLite для аналитики»:

— Импорт, анализ и экспорт данных.
— Типы данных в SQLite.
— Работа с JSON.

В планах и другие модули, посвященные специфике SQLite.

Курс пока бесплатный, но это не навсегда.

Записывайтесь, если хотите прокачать понимание SQLite и не боитесь командной строки:

SQLite на практике

История команд в консоли

Если вы используете консоль SQLite (sqlite3), то она помнит последние 2000 выполненных команд. Чтобы повторить последнюю команду, достаточно нажать клавишу ↑, а поиск работает по сочетанию Ctrl+R.

По умолчанию файл истории хранится в каталоге пользователя и называется .sqlite_history. Он текстовый, так что можно просматривать хоть в редакторе. Если хотите сохранить в другом месте — укажите полный путь в переменной окружения SQLITE_HISTORY.

История записывается в файл при штатном выходе из консоли, так что если «моргнет» электричество или еще что — команды, выполненные с последнего запуска sqlite3, потеряются.

Конечно, запись истории — не единственная фича консоли. Например, через нее удобно импортировать и экспортировать данные или работать с несколькими базами одновременно. А вот отлаживать многострочные запросы — не слишком удобно 🤷‍♀️

Многострочные запросы в консоли

Стоило вчера мне написать, что отлаживать многострочные запросы в консоли SQLite неудобно, как интернеты принесли классный трюк.

Достаточно вместо Enter использовать для перехода на новую строку сочетание Ctrl+V, Ctrl+J — и тогда многострочный запрос редактируется одним куском!

Вычисляемые столбцы

Иногда поле в запросе рассчитывают на основе других столбцов таблицы.

Например, есть столбец income с годовым доходом и tax_rate с налоговой ставкой. Тогда можно посчитать годовой налог:

select
id,
income * tax_rate as tax
from people;

А чтобы не таскать везде эти вычисления, удобно создать виртуальный столбец.

В общем виде синтаксис такой:

alter table ТАБЛИЦА
add column СТОЛБЕЦ ТИП as (ВЫРАЖЕНИЕ);

Для налогов будет так:

alter table people
add column tax real as (
income * tax_rate
);

После этого столбец можно использовать в запросах точно так же, как обычные столбцы:

select id, tax
from people;

Виртуальные столбцы не хранятся в базе и рассчитываются «на лету». Но по ним вполне можно построить индекс, если захотите ускорить выборку.

Строго говоря, в SQLite есть виртуальные (virtual) вычисляемые столбцы и хранимые (stored). Хранимые сохраняются на диске, но создать их через alter table невозможно, поэтому в основном пользуются виртуальными.

Вычисляемые столбцы могут использовать любые столбцы таблицы, но не другие таблицы и не результаты подзапросов. Оно и к лучшему: для более сложных комбинаций есть представления (views) и временные таблицы (temp tables). Но о них как-нибудь в другой раз.

JSON и виртуальные столбцы

Допустим, вы решили вести журнал событий, которые происходят в системе. События бывают разных типов, у каждого свой набор полей. Например, вход в систему:

{
  "timestamp": 1652614531,
  "object": "user",
  "object_id": 11,
  "action": "login",
  "details": {
    "ip": "192.168.0.1"
  }
}

Или пополнение счета:

{
  "timestamp": 1652614584,
  "object": "account",
  "object_id": 12,
  "action": "deposit",
  "details": {
    "amount": "1000",
    "currency": "USD"
  }
}

Вы решаете не заниматься нормализацией по таблицам, а хранить прямо в JSON. Заводите таблицу events с единственным полем value:

select value from events;

{"timestamp":1652614531,...
{"timestamp":1652614584,...
{"timestamp":1652614644,...

И выбираете события по конкретному объекту:

select
  json_extract(value, '$.object'),
  json_extract(value, '$.action')
from events
where json_extract(value, '$.object_id') = 11;

┌────────┬────────┐
│ object │ action │
├────────┼────────┤
│ user   │ login  │
└────────┴────────┘

Все здорово, но json_extract() при вызове каждый раз парсит текст, так что на сотне тысяч записей запрос будет работать медленно. Что делать?

Создать виртуальные столбцы:

alter table events
add column object_id integer
as (json_extract(value, '$.object_id'));

Построить индекс:

create index events_object_id on events(object_id);

Теперь запрос работает моментально:

select object, action
from events
where object_id = 11;

Благодаря виртуальным столбцам получилась практически NoSQL база данных ツ

песочница

Временные таблицы

Бывает, хочется собрать данные из нескольких таблиц в одну, и дальше с ними поработать. Например, для удобства выбрать вакансии вместе с работодателями и регионами:

select v.*, e.name, a.name
from vacancy as v
  join employer as e on e.id = v.employer_id
  join area as a on a.id = v.area_id

Теперь хочется запускать запросы по сводным данным. Есть три способа это сделать:

1) Common Table Expression (CTE)
2) Представление (view)
3) Временная таблица (temporary table)

CTE я описывал в отдельной заметке, не буду повторяться. Важно, что CTE каждый раз вычисляется заново, так что если наш сводный селект не особо быстрый — запросы по нему будут тормозить.

Представление — это такой именованный селект, к которому можно обращаться как к обычной таблице:

create view vacancy_view as
select ... from ... join ...;

select * from vacancy_view;

Очень похоже на CTE, только не приходится каждый раз повторять селект в запросе — достаточно написать название представления. Как и CTE, представление тоже вычисляется каждый раз.

Во многих СУБД есть материализованные представления, которые сохраняют данные на диск — но не в SQLite.

Временная таблица — точно такая же, как настоящая: хранит данные на диске, можно построить индексы для быстрой выборки. Но существует она, только пока открыто соединение с базой данных.

create temporary table vacancy_temp as
select ... from ... join ...;

select * from vacancy_temp;

Временные таблицы отлично подходят для экспериментов, когда вы только знакомитесь с данными. Можно позволить себе любые безумства — ведь после отключения от базы все будет забыто ツ

песочница

Про курсы

Как знают давние подписчики канала, у меня есть три курса на Степике:

— Оконные функции SQL
— SQLite на практике
— SQLite для аналитики

Первые два частично охватывают материал третьего. Поэтому я решил, что будет правильно давать скидку на «SQLite для аналитики» для выпускников «окошек» и «на практике».

Так что если вы:

— успешно решили все задачки в «SQLite на практике» (скидка 500₽),
— или получили сертификат с отличием в «Оконные функции SQL» (скидка 500₽),
— и хотите пройти «SQLite для аналитики»,

то присылайте ссылку на свой Степик-профиль мне @nalgeon, чтобы получить промо-код.

Расширения SQLite для процессоров Apple

Наконец дошли руки настроить сборку расширений для ARM-процессоров Apple (M1, M2).

Плюс вместо кучи мелких файлов сделал по архиву для каждой операционной системы.

Заодно напомню, какие сейчас есть расширения:

— crypto: криптографические хеш-функции вроде SHA-1 и SHA-256.
— fileio: работа с файловой системой — чтение и запись файлов, создание каталогов.
— fuzzy: нечеткое сравнение строк, фонетические алгоритмы, транслитерация.
— ipaddr: манипуляция IP-адресами и подсетями.
— json1: работа с JSON.
— math: математические функции.
— re: регулярные выражения.
— stats: статистика — медиана, процентили, стандартное отклонение.
— text: работа со строками.
— unicode: поддержка юникода для функций upper(), lower() и LIKE.
— uuid: генерация уникальных идентификаторов.
— vsv: работа с CSV-файлами как с таблицами базы.

И еще вагон расширений в инкубаторе (для них сборку под ARM пока не делал).

https://github.com/nalgeon/sqlean/releases/latest

Пользовательские функции в SQLite

Вообще-то их нет. Да, можно написать функцию на С или Python, и зарегистрировать в SQLite. Но не прямо в SQL.

Но все меняется, если подключить расширение define! С ним можно писать функции на чистом SQL.

С define несложно определить собственную функцию:

select define('sumn', ':n * (:n + 1) / 2');

А затем использовать, как будто встроенную:

> select sumn(5);
15

Пользовательские функции могут принимать несколько параметров и вызывать другие функции.

Сгенерировать случайное N, такое что a ≤ N ≤ b:

> select define('randint', ':a + abs(random()) % (:b - :a + 1)');

> select randint(10, 99);
42

Можно даже задать функцию, которая возвращает несколько значений!

Подробности в документации

Выполнить произвольный SQL из строки

Задавать функции прямо в SQL — это здорово. Но что делать, если хочется динамически составить SQL-запрос и выполнить его?

Поможет функция eval():

> select eval('select 10 + 32');
42

Можно выполнять любые DDL и DML запросы:

select eval('create table tmp(value int)');
select eval('insert into tmp(value) values (1), (2), (3)');
select eval('select value from tmp');
select eval('drop table tmp');

Функция входит в расширение define.

SQLite в 2022 году

Вот заметные фичи, которые появились в SQLite в прошедшем году:

— Операторы -> и ->> в JSON (3.38)
— RIGHT и FULL JOIN (3.39)
— Функция unixepoch() (3.38)
— Оператор IS DISTINCT FROM для совместимости со стандартом (3.39)
— Расширение recovery для восстановления битых баз
— SQLite теперь компилируют в WASM (3.40)

Попробовать новые фичи всегда можно в песочнице sqlime

Регулярные выражения в SQLite

Регулярные выражения — пожалуй, самый мощный инструмент обработки текста без программирования.

По умолчанию «регулярки» отсутствуют в SQLite, но вы легко можете их добавить с новым расширением regexp.

В комплекте:

— проверка строки на соответствие шаблону;
— извлечение фрагмента строки по шаблону;
— замена по шаблону.

Поддерживается довольно навороченный синтаксис, включая всякие разные группы, ленивые квантификаторы и look-around.

Unicode тоже поддерживается, что для русского особенно приятно.

Главное, не злоупотребляйте :)