В SQLite имеется механизм создания резервной копии базы «на лету». Многие разработчики об этом не знают, почему-то. Механизм этот примитивный, но подходит для многих ситуаций. В этой статье мы хотели бы обсудить эту самую встроенную возможность резервирования, а также предложить архитектуру для собственного механизма бэкапа. Ну или хотя бы дать направление, в котором двигаться, если нужно устроить сложную репликацию данных.
Вообще говоря, начать надо с самого простого варианта. База SQLite представляет из себя один файл (по умолчанию, режим журнала DELETE). Приложение может регулярно завершать все транзакции, закрывать все соединения к базе и просто копировать файл базы в резерв. Если файл БД размером меньше 100 Мб, то это действие на современном компьютере займет пару секунд. И его можно ускорить — прочитать файл в память (сделать «снимок»), разрешить работу с базой и, в отдельном потоке, не спеша, сбрасывать содержимое в файл на диске. Многим этого хватает, как ни удивительно.
Online Backup API
Однако, базу в памяти таким образом не скопируешь. Итак, Online Backup API. Это SQLite API для создания резервной копии «на лету». Устроено все довольно просто. Функция sqlite3_backup_init начинает процесс резервирования:
sqlite3_backup *sqlite3_backup_init(
sqlite3 *pDest, /* соединение к базе назначения */
const char *zDestName, /* псевдоним базы назначения */
sqlite3 *pSource, /* соединение к исходной базы */
const char *zSourceName /* псевдоним исходной базы */
);
В параметрах передаются объекты соединения к исходной базе и базе назначения (в качестве значения псевдонима передается «main» для основной базы, «temp» для временной или использованный при подключении через оператор ATTACH). Возвращается объект управления резервированием (если вернули 0, то ошибку надо посмотреть в соединении к базе назначения), который надо передавать первым параметром в остальные функции API. Теперь можно выполнять резервирование, которое выполняется постранично. Для копирования порции из nPage страниц (или всех, если nPage = -1) надо вызвать функцию sqlite3_backup_step:
int sqlite3_backup_step(sqlite3_backup *p, int nPage);
Если эта функция вернула SQLITE_DONE, то создание резервной копии завершено, все страницы скопированы. Если получены коды SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED, то копирование не завершено, но может быть нормально продолжено — надо еще вызывать функцию sqlite3_backup_step(). Другие коды возврата означают, что произошла ошибка. Если получен код SQLITE_DONE, то следует вызвать sqlite3_backup_finish():
int sqlite3_backup_finish(sqlite3_backup *p);
и спокойно спать, радуясь успешному бэкапу. Для получения информации о текущем состоянии резервирования служат функции:
int sqlite3_backup_remaining(sqlite3_backup *p); // сколько осталось страниц
int sqlite3_backup_pagecount(sqlite3_backup *p); // сколько всего страниц
Полностью алгоритм бэкапа базы Src в Dst блоками по SOME_PAGE_COUNT страниц на псевдокоде а-ля паскаль выглядит примерно так:
Backup = sqlite3_backup_init(Dst, 'main', Src, 'main'); if Backup = nil then Abort;
try
repeat
case sqlite3_backup_step(Backup, SOME_PAGE_COUNT) of
SQLITE_DONE: break;
SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED: continue;
else
Abort;
end;
Write('Осталось ', sqlite3_backup_remaining(Backup) * 100 div sqlite3_backup_pagecount(Backup), '%');
Sleep(SOME_TIME);
until false;
finally
sqlite3_backup_finish(Backup)
end;
При использовании этого API SQLite не блокирует исходную базу данных никаким способом. Можно не только читать, но и обновлять данные. Что произойдет, если мы копируем порциями (nPage > 0, т.е. не все страницы сразу за один вызов sqlite3_backup_step()) и база изменилась? Рестарт копирования! SQLite прозрачно для нас начнет копировать страницы с самого начала базы. Иными словами, если исходная база интенсивно меняется, есть шанс вообще не дождаться завершения резервирования. Есть и хорошие новости. Если изменение исходной базы ведется через то же соединение к ней, что и резервирование (пускай даже из другого потока), то SQLite прозрачно будет изменения дублировать в базу назначения и рестарт резервирования не случится.
Преимущества SQLite Online Backup API — исходная база не блокируется для чтения и, если она обновляется через единственное соединение, то и запись в базу не мешает. Что же делать, если база очень большая и часто обновляется из разных приложений? Настал черед задуматься о создании системы репликации данных. Собственно, ничего нового тут придумано не будет — с помощью триггеров отслеживаем какие записи изменились и ведем историю изменений. Пользуемся тем фактом, что любая таблица содержит столбец ROWID, в котором находится уникальный номер записи. Регулярно переносим изменения в другую базу. Просто покажем как это делается в деталях.
Схема простой репликации данных
Итак, в исходной базе нужно создать таблицы учета затронутых изменениями записей:
CREATE TABLE IF NOT EXISTS system_replicate_table(name TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS system_replicate_record(name TEXT, id INTEGER, PRIMARY KEY(name,id) );
Для включения репликации по таблице Foo следует добавить ее в system_replicate_table:
INSERT OR IGNORE INTO system_replicate_table(name) VALUES ('Foo');
и создать для нее триггеры репликации:
function CreateTrigger(const Operation, TableName: string): string;
begin
result := Format('CREATE TRIGGER IF NOT EXISTS system_trigger_%s_replicate_%s AFTER %s ON %s FOR EACH ROW BEGIN ', [ TableName, Operation, Operation, TableName ]);
if (Operation = 'INSERT') or (Operation = 'UPDATE') then
result := result + Format('INSERT OR IGNORE INTO system_replicate_record(name, id) VALUES("%s", NEW.ROWID); ', [ TableName ]);
if (Operation = 'DELETE') or (Operation = 'UPDATE') then
result := result + Format('INSERT OR IGNORE INTO system_replicate_record(name, id) VALUES("%s", OLD.ROWID); ', [ TableName ]);
result := result + ' END; ';
end;
Execute( CreateTrigger('INSERT', 'Foo') ); Execute( CreateTrigger('UPDATE', 'Foo') ); Execute( CreateTrigger('DELETE', 'Foo') );
Триггеры устроены просто. Они добавляют ROWID затронутых записей таблицы в system_replicate_record (про NEW и OLD в триггерах SQLite почитайте самостоятельно), если их там еще нет. Итак, включаем репликацию по интересным для нас таблицам и начинаем работать с исходной базой. Изменения данных отслеживаются триггерами. В какой-то момент (по интервалу времени или по количеству записей в system_replicate_record) выполняем репликацию данных, то есть переносим изменения. Как реплицировать измененные записи из таблицы Foo в базу назначения? Это наиболее сложная часть репликации. Мы будем использовать системную таблицу sqlite_master, в которой содержится SQL всех объектов базы. SQL этот представляет из себя оператор создания объекта (т.е. для таблицы Foo там будет «CREATE TABLE Foo(...)»).
Алгоритм копирования таблицы Foo, если ее еще нет в базе Dst.
1) Получаем SQL таблицы:
SELECT sql FROM sqlite_master WHERE tbl_name = 'Foo' and type = 'table';
и выполняем его в базе назначения «как есть» (просто передаем в метод execute соединения к БД назначения).
2) Выбираем все записи и переносим данные (как именно переносим чуть позже):
SELECT rowid as rowid, * FROM Foo
3) Если нужно индексы и триггеры тоже перенести, то выполняем в базе назначения еще и SQL, который получим так (исключаем системные индексы и триггеры):
SELECT sql FROM sqlite_master
WHERE tbl_name = 'Foo' and (type = "index" or type = "trigger")
and not name LIKE 'system_%' and not name LIKE 'sqlite_%'
Алгоритм репликации таблицы Foo из базы Src в базу Dst.
1) Если таблицы Foo в Dst еще нет, то копируем ее туда из Src (см. выше) и переходим к 5)
2) Иначе выбираем ROWID затронутых записей:
SELECT id FROM sqlite_replicate_record WHERE name = 'Foo';
и объединяем их через запятую в длинную строку R, т.е. должны получить в R что-то вроде «123,256,334,4700,...».
(Даже и не думайте выполнять эту операцию сложением строк! Выделите буфер и расширяйте его по необходимости. Также неплохо помнить, что ROWID является 8 байтовым целым со знаком).
3) Удаляем эти записи из таблицы Foo в базе Dst:
DELETE FROM [Foo] WHERE rowid IN (<сюда подставить значение R>);
4) выбираем данные из Foo в базе Src и копируем (чуть позже про собственно копирование данных) в базу Dst:
SELECT rowid as rowid, * FROM [Foo] WHERE rowid IN (SELECT id FROM system_replicate_record WHERE name = 'Foo');
5) Чистим таблицу репликации в базе Src:
DELETE FROM sqlite_replicate_record WHERE name = 'Foo';
Нам осталось понять — как копировать данные. Здесь требуется еще немного программистского шаманства. Записи выбираются следующим запросом:
SELECT rowid as rowid, * FROM [Foo]
[ WHERE ... ]
Только так можно гарантировать, что ROWID будет извлечен (и будет иметь имя «ROWID»). Для каждой извлеченной записи формируем SQL оператор вставки (в кодировке UTF-8):
INSERT INTO [Foo](<имена>) VALUES(<значения>)
Следует обойти все столбцы в выбранной записи и добавить имя столбца в часть "<имена>", а значение — в часть "<значения>", разделяя запятыми. Имя столбца следует обрамить '[' и ']'. Значение следует представить в виде SQL литерала. Как известно, в SQLite есть следующие типы значений:
SQLITE_INTEGER = 1;
SQLITE_FLOAT = 2;
SQLITE_TEXT = 3;
SQLITE_BLOB = 4;
SQLITE_NULL = 5;
Нам нужно научиться получить каждый в виде SQL литерала. Литерал SQLITE_NULL это «null». Литерал SQLITE_INTEGER это строковое представление целого числа (64 бита), 1234567890: «1234567890». Литерал SQLITE_FLOAT это строковое представление вещественного числа с точкой в качестве разделителя дробной и целой части, 123.456789: «123.456789». Для превращения строки (SQLITE_TEXT) в литерал следует удвоить в ней все одинарные кавычки и обрамить полученное одинарными кавычками, «Hello, Mc'Duck»: "'Hello, Mc''Duck'". Остался BLOB. Литералы SQLITE_BLOB (двоичных данных) в SQLite имеют вид «x'A0B1C2...'», где «A0» — hex код первого байта, «B1» — heх код второго байта и т.д.
Вот и все. Мы описали простейший рабочий вариант репликации данных, в которой запись копируется целиком. Здесь есть поле для оптимизации, разумеется. Полезно будет обернуть все изменения в базе Dst в транзакцию. При формировании оператора вставки часть с именами столбцов можно создать один раз и использовать повторно.
Приведенная архитектура не поддерживает репликацию схемы. Если вы меняете исходную таблицу, добавляя к ней поля, то это нарушит ее репликацию. Следует либо удалить таблицу в базе назначения (чтобы она полностью скопировалась повторно), либо усложнить репликацию, добавив синхронизацию схемы. Те же соображения касаются вновь создаваемых индексов и триггеров.
Автор: SqliteDog
