Автор:
Роман Чернышов (RXL)Дата написания: 8.10.2011
Некоторое время назад я заинтересовался SQLite. Это очень легкая встраиваемая реляционная база данных. Многие пользуются базами данных SQLite и даже не догадываются об этом.
Примеры широко распространенных приложений, использующих SQLite — Mozilla Firefox (и другие продукты на платформе XUL), фремворк Qt и Skype. Также SQLite используется в API платформы Android.
Раньше мне вполне хватало MySQL для web-проектов и приложений небольшого масштаба (для больших приложений хорошо подходит Oracle). Но для малых приложений, работающих на одном хосте с базой данных, это не всегда удобно, так как, во-первых, СУБД MySQL нужно устанавливать и настраивать (от правильной настройки зависит производительность), во-вторых, нужны административные действия по управлению базами и пользователями. В таких случаях применяют встраиваемые базы данных. SQLite — одна из них.
Вкратце распишу достоинства SQLite:
- Открытый исходный код и лицензия LGPL, позволяющая использовать библиотеки SQLite даже с коммерческими программами.
- Стандартный язык SQL.
- Поддержка весьма больших для встраиваемых баз объемов (штатно — до 64 ТБ, еще больших — через компиляцию с нужными параметрами).
- Очень приличные лимиты (SQLite — Ограничения). Самым жестким из них является ограничение на 10 баз данных на одно подключение к библиотеке.
- Поддержка доступа к файлу базы данных от нескольких потоков и процессов.
- Поддержка транзакций и точек сохранения, два варианта логов отката и автоматическое управление им.
- Поддержка расширений (доступен API).
- Поддержка UDF-функций, находящихся в самом приложении.
- Поддержка индексов — уникальных и не уникальных, с прямой и обратной сортировкой. Через расширение можно создать индекс полнотекстового поиска.
- Поддержка вложенных запросов, представлений, триггеров, внешних ключей и автоматической нумерации новых строк (autoincrement).
- Хранение строк таблицы в двоичном дереве. Каждая строка имеет идентификатор, по которому она находится в дереве быстрее, чем по индексам. В определенных условиях rowid совпадает с первичным ключом.
- Поддержка таблиц статистики.
- Поддержка баз только для чтения.
- Поддержка UTF-8 и UTF-16.
- Поддержка баз данных в файлах и в памяти.
- Простота создания копий (что весьма часто приходится делать при тестировании приложения): просто скопировать файл.
- Имеет поддержку для различных популярных языков программирования.
- Есть механизм подготовленных запросов с поддержкой именованных и неименованных параметров.
Но самое главное достоинство SQLite — скорость работы. А если отключить поддержку логов отката (и транзакций, соответственно), то скорость работы возрастет еще на 1-2 порядка. Конечно, на больших объемах и нагрузках серьезные СУБД будут намного быстрее и эффективнее. На их нишу SQLite не претендует.
Конечно, есть у SQLite и минусы:
- Система блокировок: shared или exclusive блокировка ставится на весь файл базы данных. Штатный режим работы транзакций — SERIALIZABLE. Хотя поддерживается и READ UNCOMMITED (соответственно, без блокировок). Но в режиме WAL возможны одновременные множественные запись и чтение в своих транзакциях.
- Ограниченный набор типов столбцов: INTEGER, REAL, TEXT и BLOB. Дату и время хранят в одном из первых трех перечисленных типов.
- Скудный набор встроенных функций.
- Нет пользовательских процедур и функций — только через UDF.
- ALTER TABLE допускает только добавление столбцов.
- Нет пользовательских переменных.
Создается база данных просто: если файла, указанного как база, не существует, то SQLite автоматически создает пустой и при любой DDL-операции заполняет его заголовок.
Рекомендую использовать SQLite не ниже версии 3.7.0. У меня в тестах использовалась именно эта версия.
В приложениях нередко требуется хранение полной истории объектов (таблиц). С увеличением количества объектов работа с ними становится сложнее, а запросы к базе данных становятся громоздкими и медленными. Приведу пример объекта jobcards (можно понимать как «заявки на выполнение работ» или «заявка-наряд»). В примерах я показываю два столбца «полезной нагрузки» только для наглядности.
CREATE TABLE jobcards
(
jobcard_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
ctime REAL NOT NULL,
data1 TEXT,
data2 TEXT
);
Запрос выборки:
SELECT *
FROM jobcards
WHERE jobcard_id = :JOBCARD_ID
Данный пример не поддерживает истории — возможна только одна строка на один идентификатор. Изменим таблицу:
CREATE TABLE jobcards
(
jobcard_id INTEGER NOT NULL,
ctime REAL NOT NULL,
data1 TEXT,
data2 TEXT,
PRIMARY KEY (jobcard_id, ctime)
);
Теперь, чтобы выбрать последнее состояние объекта, потребуется такой запрос:
SELECT *
FROM jobcards j1
WHERE j1.jobcard_id = :JOBCARD_ID
AND j1.ctime = (
SELECT MAX(t2.ctime)
FROM jobcards j2
WHERE j2.jobcard_id = j1.jobcard_id
)
Для первоначального состояния:
SELECT *
FROM jobcards j1
WHERE j1.jobcard_id = :JOBCARD_ID
AND j1.ctime = (
SELECT MIN(t2.ctime)
FROM jobcards j2
WHERE j2.jobcard_id = j1.jobcard_id
)
Когда таблиц будет много, то описывать взаимосвязь станет еще сложнее. От написания громоздких, плохо читаемых запросов нас спасут представления (VIEW), а чтобы не искать каждый раз MAX — индексная таблица.
CREATE TABLE jobcards__history
(
rowid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
jobcard_id INTEGER NOT NULL,
ctime REAL NOT NULL DEFAULT CURRENT_TIMESTAMP,
data1 TEXT,
data2 TEXT
);
CREATE TABLE jobcards__index
(
jobcard_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
deleted INTEGER(1) NOT NULL DEFAULT 0,
first_rowid INTEGER NOT NULL DEFAULT 0,
last_rowid INTEGER NOT NULL DEFAULT 0
);
CREATE VIEW jobcards AS
SELECT jci.jobcard_id, jch.ctime, jch.data1, jch.data2
FROM jobcards__index jci, jobcards__history jch
WHERE jch.rowid = jci.last_rowid
AND jci.deleted = 0;
Давайте прокомментирую некоторые моменты вышеприведенного кода.
Так как доступ по rowid быстрее и проще, то вместо индекса (jobcard_id, ctime) я сделал индекс по отдельному идентификатору. В SQLite столбец типа INTEGER и являющийся первичным ключом объединяется с rowid. По сути, вместо отдельного столбца через имя этого столбца осуществляется работа с rowid. Конечно, rowid нет необходимости объявлять — он есть у любой таблицы, но приведенная форма дает существенный плюс: значение rowid жестко зафиксировано за строкой. Для других случаев значение не фиксировано. Оно означает позицию строки в двоичном дереве и при выполнении команды VACUUM может поменяться (при этом происходит полное пересоздание базы данных и построение деревьев таблиц заново), а также, если сделать экспорт таблицы в SQL, то идентификатор не сохранится и связи будут неверными. Это все нужно для того, чтобы можно было надежно ссылаться на конкретные строки таблицы из других таблиц. Также отпадает нужда в других индексах (по крайне мере в рамках этой статьи). Штатный rowid доступен в запросах по следующим именам: rowid, oid и _rowid_. Любое из этих имен может быть использовано в качестве имени столбца — в этом случае оно не связано с настоящим rowid. В данном примере rowid и столбец — одно и тоже.
В нее я поместил два значения jobcards__history.rowid — для первой и последней строки истории объекта. Также добавил признак того, что объект завершил свое существование и в текущей ревизии списка отсутствует.
Интерфейс у него такой же, как и у первого варианта без поддержки истории. Но у нас есть «теневые» таблицы, и историю мы всегда сможем увидеть.
С такой моделью хранения данных можно работать по разному:
- Вариант 1: читать из jobcards, писать в jobcards__history и вручную или по триггеру корректировать jobcards__index, следить за тем, пишется ли в таблицу первое состояние объекта, или уже есть история, а также «удалять» объект через jobcards__index.
- Вариант 2: читать, вставлять, обновлять и удалять через jobcards с помощью набора триггеров «INSTEAD OF».
В SQLite в триггерах допустимы только DML-операторы SELECT, INSERT, UPDATE и DELETE. Причем переменных в SQLite нет и, соответственно, форма SELECT INTO не поддерживается.
Напрашивается вопрос: зачем вообще тут SELECT? Ответ неожиданный: для выполнения проверок и генерации исключений посредством функции RAISE.
В коде всех триггеров я опустил слова «FOR EACH ROW», так как они подразумеваются и другой формы SQLite не поддерживает.
Первая его задача — проверить, что строки с указанным идентификатором нет. За исключением случая, когда NEW.jobcard_id равен NULL. В этом случае генерируется новый уникальный номер посредством AUTOINCREMENT. Значение для ctime просто игнорируем — оно автоматически сгенерируется при вставке в jobcards__history.
CREATE TRIGGER jobcards__ii_update_index INSTEAD OF INSERT ON jobcards
BEGIN
SELECT
CASE
WHEN NEW.jobcard_id IS NOT NULL AND MIN(rowid) IS NOT NULL
THEN RAISE(ABORT, 'duplicate key')
END
FROM jobcards__index
WHERE jobcard_id = NEW.jobcard_id;
INSERT INTO jobcards__index (jobcard_id) VALUES (NEW.jobcard_id);
INSERT INTO jobcards__history
(jobcard_id, data1, data2)
VALUES (
IFNULL(NEW.jobcard_id, last_insert_rowid()),
NEW.data1,
NEW.data2
);
UPDATE jobcards__index
SET first_rowid = last_insert_rowid(),
last_rowid = last_insert_rowid()
WHERE jobcard_id = (
SELECT jobcard_id
FROM jobcards__history
WHERE rowid = last_insert_rowid()
);
END;
Протестируем триггер. Теневые таблицы пустые.
sqlite> INSERT INTO jobcards (data1, data2) VALUES ('aaa', 'bbb');
sqlite> INSERT INTO jobcards (data1, data2) VALUES ('ccc', 'ddd');
sqlite> SELECT * FROM jobcards__history;
rowid|jobcard_id|ctime|data1|data2
1|1|2011-10-08 13:56:24|aaa|bbb
2|2|2011-10-08 13:56:35|ccc|ddd
sqlite> SELECT * FROM jobcards__index;
jobcard_id|deleted|first_rowid|last_rowid
1|0|1|1
2|0|2|2
sqlite> SELECT * FROM jobcards;
jobcard_id|ctime|data1|data2
1|2011-10-08 13:56:24|aaa|bbb
2|2011-10-08 13:56:35|ccc|ddd
sqlite> INSERT INTO jobcards (jobcard_id, data1, data2) VALUES (1, 'ccc', 'ddd');
Error: duplicate key
Интересный факт: библиотека SQLite догадалась, что столбец REAL нужно интерпретировать как DATETIME. Видимо, по DEFAULT CURRENT_TIMESTAMP в описании таблицы. Подсветка строк — моих рук дело, утилита sqlite3 этим не занимается.
Должен проверить входные данные на следующие ошибки:
- Нельзя изменять идентификатор.
- Нельзя изменять дату.
- Если строка не существует, то молча выходим.
CREATE TRIGGER jobcards__iu_update_index
INSTEAD OF UPDATE OF data1, data2
ON jobcards
BEGIN
SELECT
CASE
WHEN NEW.jobcard_id != OLD.jobcard_id
THEN RAISE(ABORT, 'no change key')
WHEN NEW.ctime != OLD.ctime
THEN RAISE(ABORT, 'no change timestamp')
END;
SELECT
CASE
WHEN MIN(rowid) IS NULL
THEN RAISE(IGNORE)
END
FROM jobcards__index
WHERE jobcard_id = NEW.jobcard_id;
INSERT INTO jobcards__history
(jobcard_id, data1, data2)
VALUES (
NEW.jobcard_id,
NEW.data1,
NEW.data2
);
UPDATE jobcards__index
SET last_rowid = last_insert_rowid()
WHERE jobcard_id = NEW.jobcard_id;
END;
Тестируем триггер. Проверим на штатную ситуацию:
sqlite> UPDATE jobcards SET data1 = 'eee' WHERE jobcard_id = 1;
sqlite> SELECT * FROM jobcards__history;
rowid|jobcard_id|ctime|data1|data2
1|1|2011-10-08 13:56:24|aaa|bbb
2|2|2011-10-08 13:56:35|ccc|ddd
3|1|2011-10-08 14:10:05|eee|bbb
sqlite> SELECT * FROM jobcards__index;
jobcard_id|deleted|first_rowid|last_rowid
1|0|1|3
2|0|2|2
Проверим ошибочные ситуации:
sqlite> UPDATE jobcards SET ctime = CURRENT_TIMESTAMP WHERE jobcard_id = 1;
Error: cannot modify jobcards because it is a view
sqlite> UPDATE jobcards SET data1 = 'qqq', ctime = CURRENT_TIMESTAMP WHERE jobcard_id = 1;
Error: no change timestamp
sqlite> UPDATE jobcards SET jobcard_id = 111 WHERE jobcard_id = 1;
Error: cannot modify jobcards because it is a view
sqlite> UPDATE jobcards SET data1 = 'qqq', jobcard_id = 111 WHERE jobcard_id = 1;
Error: no change key
Отработало правильно. Обратите внимание на первый и третий оператор — это не мой триггер нашел ошибки, а SQLite сам так решил, на основе предоставленных ему инструкций: изменяемые столбцы перечислены в определении триггера. Второй и четвертый оператор — явная бага (или фича?) SQLite. Значит проверки на изменение запрещенных полей нужны обязательно.
В триггере удаления объекта возможны три варианта:
- Удалить всю историю объекта.
- Запретить удаление, вызвав исключение.
- Пометить объект удаленным, чтобы он был недоступен на чтение через представление.
Во всех трех вариантах проверки не требуются: если указанный идентификатор отсутствует, то ничего удалено или помечено не будет.
-- Вариант 1: удалить историю и индекс.
CREATE TRIGGER jobcards__id_update_index INSTEAD OF DELETE ON jobcards
BEGIN
DELETE FROM jobcards__index
WHERE jobcard_id = OLD.jobcard_id;
DELETE FROM jobcards__history
WHERE jobcard_id = OLD.jobcard_id;
END;
-- Вариант 2: запретить удаление.
CREATE TRIGGER jobcards__id_update_index INSTEAD OF DELETE ON jobcards
BEGIN
SELECT RAISE(ABORT, 'deletion disabled');
END;
-- Вариант 3: пометить виртуальную запись как удаленную.
CREATE TRIGGER jobcards__id_update_index INSTEAD OF DELETE ON jobcards
BEGIN
UPDATE jobcards__index
SET deleted = 1
WHERE jobcard_id = OLD.jobcard_id;
END;
Я выбираю вариант 3. Тестируем...
sqlite> DELETE FROM jobcards WHERE jobcard_id = 1;
sqlite> SELECT * FROM jobcards__history;
rowid|jobcard_id|ctime|data1|data2
1|1|2011-10-08 13:56:24|aaa|bbb
2|2|2011-10-08 13:56:35|ccc|ddd
3|1|2011-10-08 14:10:05|eee|bbb
sqlite> SELECT * FROM jobcards__index;
jobcard_id|deleted|first_rowid|last_rowid
1|1|1|3
2|0|2|2
sqlite> SELECT * FROM jobcards;
jobcard_id|ctime|data1|data2
2|2011-10-08 13:56:35|ccc|ddd
Обратите внимание: поле deleted приняло значение 1 и в представлении строка не отображается.
Если кто заметит ошибки или у вас есть, что сказать по теме — прошу писать в обсуждение статьи.