SQLite створення «гарячої» резервної копії даних
SQLite має механізм створення резервної копії бази «на льоту». Багато розробників про це не знають чомусь. Механізм цей є примітивним, але підходить для багатьох ситуацій. У цій статті ми хотіли б обговорити цю вбудовану можливість резервування, а також запропонувати архітектуру для власного механізму бекапу. Ну чи хоча б дати напрямок, у якому рухатись, якщо потрібно влаштувати складну реплікацію даних.
Взагалі, почати треба з найпростішого варіанту. База SQLite є одним файлом (за замовчуванням, режим журналуDELETE). Програма може регулярно завершувати всі транзакції, закривати всі з'єднання до бази та просто копіювати файл бази у резерв. Якщо файл БД розміром менше 100 Мб, це дія на сучасному комп'ютері займе пару секунд. І його можна прискорити - прочитати файл на згадку (зробити «знімок»), дозволити роботу з базою і, в окремому потоці, не поспішаючи, скидати вміст у файл на диску. Багатьом цього вистачає, як не дивно.
Online Backup API
Однак базу в пам'яті таким чином не скопіюєш. Отже, Online Backup API. Це SQLite API для створення резервної копії "на льоту". Влаштовано все досить просто. Функціяsqlite3_backup_initпочинає процес резервування:
У параметрах передаються об'єкти з'єднання до вихідної бази та бази призначення (як значення псевдоніма передається«main»для основної бази,«temp»для тимчасової або використаний при підключенні через операторATTACH). Повертається об'єкт управління резервуванням (якщо повернули 0, то помилку треба подивитися у з'єднанні до бази призначення), який треба передавати першим параметром до інших функцій API. Тепер можна виконуватирезервування, яке виконується посторінково. Для копіювання порції зnPageсторінок (або всіх, якщоnPage= -1) потрібно викликати функціюsqlite3_backup_step:
і спокійно спати, радіючи успішному бекапу. Для отримання інформації про поточний стан резервування є функції:
Повністю алгоритм бекапу базиSrcуDstблоками поSOME_PAGE_COUNTсторінок на псевдокоді а-ля паскаль виглядає приблизно так:
Переваги SQLite Online Backup API - вихідна база не блокується для читання і якщо вона оновлюється через єдине з'єднання, то і запис в базу не заважає. Що ж робити, якщо база дуже велика і часто оновлюється з різних програм? Настала черга задуматися про створення системи реплікації даних. Власне, нічого нового тут придумано не буде — за допомогою тригерів відстежуємо якісь записи змінилися і ведемо історію змін. Використовуємо той факт, що будь-яка таблиця містить стовпецьROWID, в якому знаходиться унікальний номер запису. Регулярно переносимо зміни до іншої бази. Просто покажемо, як це робиться в деталях.
Схема простої реплікації даних
Отже, у вихідній базі необхідно створити таблиці обліку порушених змінами записів:
Для включення реплікації за таблицеюFooслід додати її доsystem_replicate_table:
і створити для неї тригери реплікації:
Тригери влаштовані просто. Вони додаютьROWIDпорушених записів таблиці вsystem_replicate_record(проNEWіOLDу тригерах SQLite почитайте самостійно), якщо їх там ще немає. Отже, включаємо реплікацію за цікавими для нас таблицями та починаємо працювати з вихідною базою. Зміни даних відстежуються тригерами. В якийсь момент (за інтервалом часуабо за кількістю записів уsystem_replicate_record) виконуємо реплікацію даних, тобто переносимо зміни. Як реплікувати змінені записи з таблиціFooдо основи призначення? Це найскладніша частина реплікації. Ми будемо використовувати системну таблицюsqlite_master, де міститься SQL всіх об'єктів бази. SQL цей є оператором створення об'єкта (тобто для таблиці Foo там буде «CREATE TABLE Foo(. )»).
Алгоритм копіювання таблиціFoo, якщо її ще немає в базіDst.
1)Отримуємо SQL таблиці:
і виконуємо його в основі призначення «як є» (просто передаємо в спосіб execute з'єднання до БД призначення).2)Вибираємо всі записи та переносимо дані (як саме переносимо трохи пізніше):
3)Якщо потрібно індекси та тригери теж перенести, то виконуємо в базі призначення ще й SQL, який отримаємо так (виключаємо системні індекси та тригери):
Алгоритм реплікації таблиціFooз базиSrcдо базиDst.
1)Якщо таблиціFooуDstще немає, то копіюємо її туди зSrc(див. вище) і переходимо до>5)2)Інакше вибираємоROWIDпорушених записів:
5)Чистимо таблицю реплікації в базіSrc:
Нам залишилося зрозуміти як копіювати дані. Тут потрібно ще трохи програмістського шаманства. Записи вибираються таким запитом:
Тільки так можна гарантувати, щоROWIDбуде вилучено (і матиме ім'я «ROWID»). Для кожного вилученого запису формуємо SQL оператор вставки (у кодуванні UTF-8):
Слід обійти всі стовпці у вибраному записі та додати ім'я стовпця до частини " ", а значення - до частини "", розділяючи комами. Ім'я стовпця слід обрамити '[' і ']'. Значенняслід подати у вигляді SQL літералу. Як відомо, у SQLite є такі типи значень:
Нам потрібно навчитися отримати кожен у вигляді літералу 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транзакцію. При формуванні оператора вставки частину з іменами стовпців можна створити один раз і повторно.
Наведена архітектура не підтримує реплікацію схеми. Якщо ви змінюєте вихідну таблицю, додаючи до неї поля, це порушить її реплікацію. Слід або видалити таблицю в основі призначення (щоб вона повністю скопіювалася повторно), або ускладнити реплікацію, додавши синхронізацію схеми. Ті ж міркування стосуються новостворюваних індексів і тригерів.
PS. Використовуйте сучасний менеджер для адміністрування бази даних SQLite.
Хардкорна конфа за С++. Ми запрошуємо лише профі.