УРОК · База данных для каталога

Миграция схемы и резервная копия

Опубликовано Автор: Соловьёва Мария Андреевна

Структура таблицы будет меняться вместе с проектом. Изменения нужно делать предсказуемо.

Разберём по шагам

После изменения и удаления строк база хранит данные, которые уже жалко потерять. Перед добавлением поля created_at создадим резервную копию, восстановим её в отдельный файл и сравним прежние строки. Затем изменим схему таблицы; повторный запуск не должен добавлять столбец ещё раз.

Слова, которые пригодятся

Миграция
Сохранённый в файле шаг изменения структуры базы. Здесь migrate-created-at.php добавляет новый столбец и проверяет, не был ли он добавлен раньше.
Резервная и восстановленная копия
Резервная копия сохраняет состояние до изменения. Восстановленная копия — отдельный файл, который открывают и проверяют, прежде чем трогать рабочую базу.
Столбец и NULL
Столбец — одно поле каждой строки таблицы. У старых заметок новый created_at пока не заполнен; SQLite хранит там NULL, то есть значение отсутствует.

Остановите сервер через Ctrl+C и дождитесь завершения команд, работающих с базой. В корне notes-php создайте migrate-created-at.php. При первом запуске он копирует data/notes.sqlite в data/notes-before-created-at.sqlite, затем создаёт из этой копии отдельный data/notes-restored.sqlite. Код проверяет целостность восстановленного файла и сравнивает id, title, body с рабочей базой до изменения схемы. Если копия от другого опыта, миграция остановится, не переписывая её.

После проверки ALTER TABLE добавляет необязательный created_at TEXT. В SQLite здесь нет отдельного типа даты; позднее можно хранить дату согласованной текстовой строкой. Старые записи остаются с NULL; этот скрипт не заполняет даты и не меняет страницу. При повторе первоначальный backup не перезаписывается, а поле второй раз не добавляется. Новые записи после снимка могут отличаться от него — сравнение требуется перед первым изменением. Файлы *.sqlite находятся вне public и позднее будут исключены из Git. Простое copy() допустимо здесь только при остановленных подключениях; для работающей базы используют механизм резервного копирования SQLite.

Рабочий пример

<?php
declare(strict_types=1);
$dir = __DIR__ . '/data';
$database = $dir . '/notes.sqlite';
$backup = $dir . '/notes-before-created-at.sqlite';
$restored = $dir . '/notes-restored.sqlite';
if (!is_file($database)) {
    exit('Сначала выполните php setup.php' . PHP_EOL);
}
if (!is_file($backup) && !copy($database, $backup)) {
    throw new RuntimeException('Не удалось создать резервную копию');
}
if (!copy($backup, $restored)) {
    throw new RuntimeException('Не удалось восстановить тестовую копию');
}
$test = new PDO('sqlite:' . $restored, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$integrity = $test->query('PRAGMA integrity_check')->fetchColumn();
if ($integrity !== 'ok') {
    throw new RuntimeException('Восстановленная копия повреждена');
}
$testRows = $test->query('SELECT id, title, body FROM notes ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
$testCount = count($testRows);
$test = null;
$pdo = new PDO('sqlite:' . $database, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$columns = $pdo->query('PRAGMA table_info(notes)')->fetchAll(PDO::FETCH_ASSOC);
if (!in_array('created_at', array_column($columns, 'name'), true)) {
    $liveRows = $pdo->query('SELECT id, title, body FROM notes ORDER BY id')->fetchAll(PDO::FETCH_ASSOC);
    if ($liveRows !== $testRows) {
        throw new RuntimeException('Копия отличается от рабочей базы; проверьте её до миграции');
    }
    $pdo->exec('ALTER TABLE notes ADD COLUMN created_at TEXT');
    echo 'Поле created_at добавлено' . PHP_EOL;
} else {
    echo 'Поле created_at уже есть' . PHP_EOL;
}
$liveCount = (int) $pdo->query('SELECT COUNT(*) FROM notes')->fetchColumn();
echo 'Строк в восстановленной копии: ' . $testCount . PHP_EOL;
echo 'Строк в рабочей базе: ' . $liveCount . PHP_EOL;

Практика

Сделайте проверяемый снимок базы перед изменением её структуры. После урока останутся прежние заметки, новое пустое поле и исходная копия для отката учебного опыта.

  1. Оставьте в notes хотя бы одну строку после SQLite3. Остановите локальный сервер через Ctrl+C и дождитесь завершения всех CLI-скриптов. В Visual Studio Code создайте migrate-created-at.php в корне notes-php, вставьте полный пример и сохраните Ctrl+S.
  2. Из корня notes-php выполните php migrate-created-at.php. В data найдите три файла: notes.sqlite, notes-before-created-at.sqlite, notes-restored.sqlite. Ожидайте «Поле created_at добавлено» и равное число строк: содержимое проверено кодом до ALTER TABLE.
  3. Повторите команду без промежуточных правок заметок: получите «Поле created_at уже есть» и то же число строк. Первоначальная резервная копия не заменяется. Оставьте migrate-created-at.php рядом с setup.php: после блока Git он поможет восстановить схему в чистой копии проекта.

Как проверить себя

Восстановленная копия проходит проверку целостности, её прежние строки совпадают с рабочими перед миграцией; created_at добавлен один раз, повторный запуск сохраняет строки и исходный backup.

Если не получилось
  • no such table: notes? Проверьте запуск php setup.php и путь к одному notes-php/data/notes.sqlite.
  • Ошибка «Копия отличается» означает, что notes-before-created-at.sqlite уже содержит другой снимок. Не перезаписывайте его вслепую: определите, откуда файл, сохраните отдельно нужную версию и только затем повторяйте упражнение с согласованной копией.
  • copy() не сработал? Проверьте доступ на запись в data и что сервер и другие команды завершены.