Что такое SQLite: пример базы и выбор для задачи

SQLite — встраиваемая библиотека, которая реализует самодостаточный, бессерверный, транзакционный движок SQL-базы данных. Что такое SQLite: пример базы и выбор для задачи — это разбор рабочего примера на SQL, критериев выбора между локальной и сетевой схемой и трёх проверок результата. Библиотека живёт внутри приложения и пишет напрямую в обычные дисковые файлы.

Определение SQLite и границы применимости

В документации проекта на sqlite.org сказано: SQLite — это встраиваемый движок SQL-базы данных, который, в отличие от большинства других SQL-баз, не имеет отдельного серверного процесса. Движок читает и пишет напрямую в обычные дисковые файлы, а полная база с таблицами, индексами, триггерами и представлениями содержится в одном дисковом файле. Исходный код распространяется как общественное достояние и свободен для коммерческого и частного использования.

Там же перечислены свойства: полнофункциональный SQL, единый файл базы, компактный след, ACID-транзакции даже после потери питания, стабильный формат файла. Размер библиотеки со всеми включёнными возможностями может быть меньше 900 КиБ — это зависит от целевой платформы и настроек оптимизации компилятора. Максимальный размер базы в документации указан как 281 терабайт, максимальный размер строки — 1 гигабайт.

Отдельная строка документации формулирует позиционирование: думайте о SQLite как о замене fopen(). Это объясняет, для чего движок создавался — для локального хранения данных внутри программы.

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

Формат файла переносим между 32- и 64-битными системами, между big-endian и little-endian архитектурами. Это делает SQLite популярным выбором как формат файла приложения; формат рекомендован Библиотекой Конгресса США как формат хранения.

Пример базы: CREATE, INSERT, SELECT

Соберём учебную микробазу ремонта. Таблица jobs хранит заказы с идентификатором, названием предмета, статусом и стоимостью. Ограничение CHECK(cost >= 0) не даёт записать отрицательную стоимость.

CREATE TABLE jobs(
  id INTEGER PRIMARY KEY,
  item TEXT NOT NULL,
  status TEXT NOT NULL,
  cost INTEGER NOT NULL CHECK(cost >= 0)
);
INSERT INTO jobs VALUES
  (1,'лампа','open',700),
  (2,'утюг','done',1200),
  (3,'часы','done',500);
SELECT SUM(cost) AS paid FROM jobs WHERE status='done';

Три строки данных — лампа, утюг и часы. Запрос суммирует стоимость только выполненных заказов. Эталонный результат: paid = 1700. Это учебный пример.

Числа здесь проверяемы вручную: 1200 + 500 = 1700. Строка с лампой имеет статус open и в сумму не входит. Такой запрос удобно использовать как первую проверку после создания базы.

В официальном Quickstart на sqlite.org описан запуск командной строки: команда sqlite3 test.db создаёт новую базу с именем test.db. Дальше SQL-команды вводятся в приглашении и создают, наполняют базу. Тот же документ приводит примеры на Tcl и C: в C используются вызовы sqlite3_open(), sqlite3_exec() и sqlite3_close().

Для Python в документации PSF описан модуль sqlite3 — интерфейс DB-API 2.0. Соединение создаётся вызовом sqlite3.connect("tutorial.db"), курсор — методом con.cursor(). В примере PSF таблица создаётся через cur.execute("CREATE TABLE movie(title, year, score)"), а вставка строк выполняется через executemany с плейсхолдерами ?. Там же отмечено: плейсхолдеры применяют вместо форматирования строк, чтобы избежать SQL-инъекций.

Отрицательный INSERT и отказ CHECK

После основного примера выполним отдельную проверку — попытку вставить строку с отрицательной стоимостью:

INSERT INTO jobs VALUES (4,'радио','open',-1);

Этот INSERT отвергается ограничением CHECK(cost >= 0), заданным при создании таблицы. Строка с идентификатором 4 в базу не попадает. Данные, вставленные ранее, остаются на месте: повторный SELECT SUM(cost) AS paid FROM jobs WHERE status='done' по-прежнему возвращает 1700.

Так проверяются сразу два свойства. Первое — ограничение целостности работает и не пропускает заведомо некорректное значение. Второе — неудачная вставка не портит уже записанные строки.

Ограничение задаётся на уровне схемы, поэтому проверка действует для любой вставки, независимо от того, какая программа обращается к базе. Это переносимая защита данных.

Выбор: локальный A и сетевой B случаи

Документация проекта на sqlite.org описывает ситуации, где SQLite работает хорошо, и ситуации, где клиент-серверная СУБД может подойти лучше. Сравнение идёт по месту хранения данных и характеру записи.

Случай A: один ремонтник ведёт локальную утилиту, данные лежат на том же устройстве, записи редкие и короткие. Здесь SQLite подходит как первый кандидат.

Случай B: много пользователей в сети, много одновременных записей, общая серверная инфраструктура. Здесь стоит рассмотреть серверную СУБД и проверить нагрузку. Это условные решения: сайт на SQLite не объявляется запрещённым.

В документации проекта отмечено: SQLite работает как движок базы для большинства сайтов с низким и средним трафиком. Там же приведена оценка, что сайт с менее чем 100 тысячами обращений в день должен работать нормально, и это консервативная оценка.

Сериализация writers и SQLITE_BUSY

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

Из этого не следует, что конфликт записи даёт мгновенный SQLITE_BUSY в любом тесте. В документации описан механизм: если пишущая инструкция выполняется, когда активна читающая транзакция, читающая транзакция повышается до пишущей, если это возможно. Если другое соединение уже изменило базу или находится в процессе изменения, повышение невозможно, и инструкция завершается с SQLITE_BUSY.

Значит, поведение зависит от режима транзакции и ожидания. В документации перечислены три режима: DEFERRED, IMMEDIATE и EXCLUSIVE. По умолчанию используется DEFERRED: транзакция фактически не начинается до первого обращения к базе. IMMEDIATE заставляет соединение начать новую запись сразу, не дожидаясь пишущей инструкции, и BEGIN IMMEDIATE может завершиться с SQLITE_BUSY, если другая пишущая транзакция уже активна на другом соединении.

В документации PSF описан параметр timeout функции sqlite3.connect(): это число секунд, которое соединение ждёт перед тем, как поднять OperationalError, когда таблица заблокирована. Значение по умолчанию — пять секунд. Там же сказано: если другое соединение открыло транзакцию для изменения таблицы, таблица будет заблокирована до фиксации транзакции.

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

Backup и три проверки результата

Документация PSF описывает метод backup() объекта Connection: он создаёт резервную копию базы SQLite и работает, даже если база используется другими клиентами или тем же соединением. Параметр pages задаёт число страниц, копируемых за раз; значение меньше или равное нулю копирует всю базу за один шаг. Параметр progress принимает функцию, которая вызывается на каждой итерации с тремя целыми аргументами: статусом последней итерации, числом оставшихся страниц и общим числом страниц.

Пример из документации PSF копирует базу example.db в backup.db с pages=1 и функцией прогресса. Второй пример копирует базу в соединение :memory:.

Три конкретные проверки после работы с примером:

1. SELECT SUM(cost) AS paid FROM jobs WHERE status='done' возвращает 1700.
2. INSERT INTO jobs VALUES (4,'радио','open',-1) отклонён ограничением CHECK, данные сохранены.
3. Резервная копия сделана через документированный backup API, и другим соединением проверены те же три строки и сумма 1700.

Копирование одного открытого файла без учёта журнала и транзакций не выдаётся надёжным backup. Документированный backup API учитывает состояние базы во время копирования, поэтому его и стоит использовать.

Частые вопросы

Что такое SQLite простыми словами?
Это библиотека, встроенная в приложение, которая реализует SQL-базу данных. Отдельного серверного процесса у неё нет: движок читает и пишет напрямую в обычные дисковые файлы. Вся база с таблицами, индексами, триггерами и представлениями лежит в одном дисковом файле. Исходный код распространяется как общественное достояние.

Нужен ли администратор базы данных для SQLite?
Отдельный серверный процесс не запускается, поэтому классической роли администратора сервера здесь нет. При этом задачи сопровождения остаются: резервное копирование, мониторинг и проверка целостности выполняются на стороне приложения. В документации проекта отмечено, что профессиональная поддержка доступна.

Можно ли использовать SQLite для сайта?
В документации проекта сказано, что SQLite работает как движок базы для большинства сайтов с низким и средним трафиком. Там же приведена оценка: сайт с менее чем 100 тысячами обращений в день должен работать нормально, и это консервативная оценка. Для сайта с интенсивной записью или требующего нескольких серверов документация советует рассмотреть клиент-серверную СУБД.

Что происходит при одновременной записи?
SQLite поддерживает множество одновременных читателей, но только одну пишущую транзакцию одновременно. Конфликт записи не даёт мгновенный SQLITE_BUSY в любом тесте: поведение зависит от режима транзакции и ожидания. Параметр timeout соединения задаёт число секунд ожидания перед ошибкой, по умолчанию пять секунд.

Как сделать резервную копию базы SQLite?
В документации PSF описан метод backup() объекта Connection, который создаёт копию базы и работает при активном использовании базы другими клиентами. Копирование одного открытого файла без учёта журнала и транзакций не выдаётся надёжным backup. После копии стоит открыть её другим соединением и проверить строки и суммы.