Главная > Блог > Web-разработка > PHP и MySQL >

Миграции базы данных

14.11.2018

Теги: CLI • MySQL • PHP • Web-разработка • БазаДанных • Запрос • Миграции

Версия базы данных — определенное состояние структуры базы данных. Миграция — это обновление структуры базы данных от одной версии до другой, обычно более новой. Есть много готовых разнообразных инструментов, которые занимаются миграциями. Но, очень часто, нужно просто залить в базу изменения, которые сделаны другим разработчиком. Давайте реализуем такую возможность.

Как это работает

Идея довольно проста: создадим директорию sql, куда будем складывать файлы миграций, то есть — файлы с SQL-запросами, которые меняют состояние базы, а также напишем php-скрипт, который эти миграции и накатывает.

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

Файлы миграций

Файл sql/0001_create_database.sql:

-- Структура таблицы `current_state_database`
CREATE TABLE IF NOT EXISTS `current_state_database` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

-- Структура таблицы `products`
CREATE TABLE IF NOT EXISTS `products` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `price` decimal(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

-- Дамп данных таблицы `products`
INSERT INTO `products`
    (`id`, `name`, `price`)
VALUES
    (1, 'Первый товар', '1000.00'),
    (2, 'Второй товар', '2000.00');

Файл sql/0002_add_table_users.sql:

-- Структура таблицы `users`
CREATE TABLE IF NOT EXISTS `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    `email` varchar(255) NOT NULL DEFAULT '',
    `password` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Файл sql/0003_insert_data_into_users.sql:

-- Новые записи таблицы `users`
INSERT INTO `users`
    (`id`, `name`, `email`, `password`)
VALUES
    (1, 'Иванов Иван Иванович', 'ivanov.i@mail.ru', 'qwerty'),
    (2, 'Петров Петр Петрович', 'petrov.p@mail.ru', '123456');

Файл sql/0004_add_column_active_to_users.sql:

-- Новая колонка `active` таблицы `users`
ALTER TABLE
    `users`
ADD
    `active` tinyint unsigned NOT NULL DEFAULT '0'
AFTER
    `password`;

Скрипт, который накатывает миграции

Скрипт запускается из командной строки и поддерживает 5 опций:

> php database.php -h
Usage: php database.php -h|-s|-m|-b|-r
Options:
    -h --help       Show this message
    -s --state      Show current state
    -m --migrate    Change current state
    -b --backup     Create database backup
    -r --restore    Restore database from backup

> php database.php -s
Old files in folder D:/work/localhost6/www/sql/:
    Old files not found
New files in folder D:/work/localhost6/www/sql/:
    1. 0001_create_database.sql
    2. 0002_add_table_users.sql
    3. 0003_insert_data_into_users.sql
    4. 0004_add_column_active_to_users.sql

> php database.php -m
Start database migration
Execute file 0001_create_database.sql
Execute file 0002_add_table_users.sql
Execute file 0003_insert_data_into_users.sql
Execute file 0004_add_column_active_to_users.sql
Database migration complete

> php database.php -b
Create backup of current state

> php database.php -r
Choose backup file to restore:
1. test-19.11.2018-10.13.58.sql
2. test-19.11.2018-10.14.58.sql
Enter number of backup file: 2
Create backup of current state
Restore database from backup

Исходный код скрипта:

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_USER', 'root');
define('DB_PASS', '');
define('TABLE_CURRENT_STATE', 'current_state_database');

require 'lib/Database.php';
require 'lib/Migration.php';

// поддерживаются пять опций: help, state, migrate, backup и restore
$help  = 'Usage: php ' . $argv[0] . ' -h|-s|-m|-b|-r' . PHP_EOL;
$help .= 'Options:' . PHP_EOL;
$help .= '    -h --help       Show this message' . PHP_EOL;
$help .= '    -s --state      Show current state' . PHP_EOL;
$help .= '    -m --migrate    Change current state' . PHP_EOL;
$help .= '    -b --backup     Create database backup' . PHP_EOL;
$help .= '    -r --restore    Restore database from backup';

if ($argc == 2) { // должна быть только одна опция и это обязательно
    $params = array(
        'h::' => 'help::',
        's::' => 'state::',
        'm::' => 'migrate::',
        'b::' => 'backup::',
        'r::' => 'restore::'
    );
    $options = getopt(implode('', array_keys($params)), $params);

    $migration = new Migration(
        DB_HOST,
        DB_NAME,
        DB_USER,
        DB_PASS,
        TABLE_CURRENT_STATE
    );
    if (isset($options['help']) || isset($options['h'])) {
        // опция help (справка по использованию)
        echo $help;
    } elseif (isset($options['state']) || isset($options['s'])) {
        // опция state (текущее состояние базы данных)
        $migration->state();
    } elseif (isset($options['migrate']) || isset($options['m'])) {
        // опция migrate (изменить состояние базы данных)
        $migration->migrate();;
    } elseif (isset($options['backup']) || isset($options['b'])) {
        // опция backup (создание резервной копии)
        $migration->backup();
    } elseif (isset($options['restore']) || isset($options['r'])) {
        // опция restore (восстановление из резервной копии)
        $migration->restore();
    } else {
        echo 'Syntax error, unkhown option', PHP_EOL;
        echo $help;
    }
} else {
    echo 'Syntax error, must be one option', PHP_EOL;
    echo $help;
}

Скипт в своей работе использует два класса:

<?php
/**
 * Класс для изменения состояния базы данных и учета этих изменений
 */
class Migration {

    /**
     * Хост базы данных
     */
    private $host;
    /**
     * Имя базы данных
     */
    private $name;
    /**
     * Пользователь базы данных
     */
    private $user;
    /**
     * Пароль базы данных
     */
    private $pass;

    /**
     * Имя таблицы БД для учета миграций
     */
    private $stateTable;
    /**
     * Директория с SQL-файлами
     */
    private $sqlDir;
    /**
     * Директория с резервными копиями
     */
    private $backupDir;

    /**
     * Для хранения экземпляра класса для работы с базой данных
     */
    private $database;


    public function __construct($host, $name, $user, $pass, $stateTable, $sqlDir = 'sql', $backupDir = 'backup') {
        $this->host = $host;
        $this->name = $name;
        $this->user = $user;
        $this->pass = $pass;

        $this->stateTable = $stateTable;
        $this->sqlDir = str_replace('\\', '/', realpath($sqlDir)) . '/';
        $this->backupDir = str_replace('\\', '/', realpath($backupDir)) . '/';
 
        Database::init($host, $name, $user, $pass);
        $this->database = Database::getInstance();
    }

    /**
     * Функция несколько раз изменяет состояние базы данных, выполняет запросы
     * из тех SQL-файлов, которые еще не выполнялись ранее
     */
    public function migrate() {

        // получаем список файлов для миграции
        $files = $this->getNewFiles();

        // нечего делать, база данных в актуальном состоянии
        if (empty($files)) {
            echo 'Your database in latest state';
            return;
        }

        // создаем резервную копию текущего состояния
        if (!$this->isEmpty()) {
            $this->backup();
            echo PHP_EOL;
        }

        echo 'Start database migration', PHP_EOL;
        // выполняем SQL-запросы из каждого файла
        foreach ($files as $file) {
            $this->execute($file);
            echo 'Execute file ', basename($file), PHP_EOL;
        }

        echo 'Database migration complete';
    }

    /**
     * Функция показывает список SQL-файлов для миграций
     */
    public function state() {
        // выводим список старых файлов
        $oldFiles = $this->getOldFiles();
        echo 'Old files in folder ' . $this->sqlDir . ':';
        if (!empty($oldFiles)) {
            $i = 1;
            foreach ($oldFiles as $file) {
                echo PHP_EOL, '    ', $i, '. ', basename($file);
                $i++;
            }
        } else {
            echo PHP_EOL, '    Old files not found';
        }
        // выводим список новых файлов
        $newFiles = $this->getNewFiles();
        echo PHP_EOL, 'New files in folder ' . $this->sqlDir . ':';
        if (!empty($newFiles)) {
            $i = 1;
            foreach ($newFiles as $file) {
                echo PHP_EOL, '    ', $i, '. ', basename($file);
                $i++;
            }
        } else {
            echo PHP_EOL, '    New files not found';
        }
    }
    
    /**
     * Функция создает резервную копию базы данных
     */
    public function backup() {
        // резервную копию создаем, если в БД есть таблицы
        if ($this->isEmpty()) {
            echo 'No tables found in database, nothing to do';
            return;
        }
        // предупреждаем, если резервных копий накопилось много
        $items = scandir($this->backupDir);
        if (count($items) > 12) {
            echo 'Warning! Too many backup files', PHP_EOL;
        }
        // выполняем команду mysqldump
        echo 'Create backup of current state';
        $backupName = $this->backupDir . $this->name . '-' . date('d.m.Y-H.i.s') . '.sql';
        if ($this->pass != '') {
            $command = 'mysqldump -u' . $this->user . ' -p' . $this->pass . ' -h ' . $this->host .
                       ' -B ' . $this->name . ' > ' . $backupName;
        } else {
            $command = 'mysqldump -u' . $this->user . ' -h ' . $this->host .
                       ' -B ' . $this->name . ' > '.$backupName;
        }
        shell_exec($command);
    }

    /**
     * Функция восстанавливает базу данных из резервной копии
     */
    public function restore() {
        // получаем имя файла резервной копии
        $backupName = $this->choose();
        if (false === $backupName) {
            return;
        }
        // создаем резервную копию текущего состояния
        if (!$this->isEmpty()) {
            $this->backup();
            echo PHP_EOL;
        }
        // удаляем все таблицы из базы данных
        $query = 'SHOW TABLES';
        $rows = $this->database->fetchAll($query);
        foreach ($rows as $row) {
            $query = 'DROP TABLE `' . $row['Tables_in_'.$this->name] . '`';
            $this->database->execute($query);
            
        }
        // восстанавливаем базу данных
        echo 'Restore database from backup';
        if ($this->pass != '') {
            $command = 'mysql -u' . $this->user . ' -p' . $this->pass . ' -h '.$this->host .
                       ' -D ' . $this->name . ' < ' . $backupName;
        } else {
            $command = 'mysql -u' . $this->user . ' -h ' . $this->host .
                       ' -D ' . $this->name . ' < ' . $backupName;
        }
        shell_exec($command);
    }

    /**
     * Функция возвращает массив старых файлов миграций, т.е.
     * тех, которые уже были применены к БД
     */
    private function getOldFiles() {
        $oldFiles = array();
        if ($this->isEmpty()) {
            return $oldFiles;
        }
        $query = 'SELECT `name` FROM `'.$this->stateTable.'` WHERE 1';
        $rows = $this->database->fetchAll($query);
        foreach ($rows as $row) {
            $oldFiles[] = $this->sqlDir . $row['name'];
        }
        return $oldFiles;
    }

    /**
     * Функция возвращает массив новых файлов миграций, т.е.
     * тех, которые еще не были применены к БД
     */
    private function getNewFiles() {
        // получаем список всех sql-файлов
        $items = scandir($this->sqlDir);
        $allFiles = array();
        foreach ($items as $item) {
            if ($item == '.' || $item == '..') {
                continue;
            }
            $allFiles[] = $this->sqlDir . $item;
        }
        // получаем список старых файлов
        $oldFiles = $this->getOldFiles();

        return array_diff($allFiles, $oldFiles);
    }

    /**
     * Функция выполняет запросы из sql-файла
     */
    private function execute($file) {
        if ($this->pass != '') {
            $command = 'mysql -u' . $this->user . ' -p' . $this->pass . ' -h ' . $this->host .
                       ' -D ' . $this->name . ' < ' . $file;
        } else {
            $command = 'mysql -u' . $this->user . ' -h ' . $this->host .
                       ' -D ' . $this->name . ' < ' . $file;
        }
        shell_exec($command);

        // добавляем запись в таблицу учета миграций, отмечая тот факт,
        // что состояние базы данных изменилось
        $query = 'INSERT INTO `' . $this->stateTable . '` (`name`) VALUES ("' . basename($file) . '")';
        $this->database->execute($query);
    }

    /**
     * Функция проверяет, есть ли в базе данных таблицы
     */
    private function isEmpty() {
        $query = 'SHOW TABLES';
        $rows = $this->database->fetchAll($query);
        return empty($rows);
    }

    /**
     * Вспомогательная функция для выбора файла резервной копии
     */
    private function choose() {
        $items = scandir($this->backupDir);
        if (count($items) == 2) {
            echo 'Backup files not found', PHP_EOL;
            return false;
        }
        // выводим список всех файлов резервных копий с номерами 1,2,3,...
        echo 'Choose backup file to restore:', PHP_EOL;
        $i = 0;
        $numbers = array(); // массив всех номеров файлов, для дальнейшей проверки
        foreach ($items as $item) {
            if ($item == '.' || $item == '..') {
                continue;
            }
            $i++;
            $numbers[] = $i;
            echo $i, '. ', $item, PHP_EOL;
        }
        while (true) { // пока не будет выбран правильный номер файла
            echo 'Enter number of backup file: ';
            $number = fgets(STDIN);
            if (in_array($number, $numbers)) { // проверяем корректность номера файла
                break;
            }
        }
        // получаем имя файла резервной копии по ее номеру в списке
        $i = 0;
        foreach ($items as $item) {
            if ($item == '.' || $item == '..') {
                continue;
            }
            $i++;
            if ($i == $number) {
                // возвращем полное имя файла резервной копии
                return $this->backupDir . $item;
            }
        }
    }

}

<?php
/**
 * Класс Database, предоставляет доступ к базе данных,
 * реализует шаблон проектирования «Одиночка»
 */
class Database {

    /**
     * для хранения единственного экземпляра данного класса
     */
    private static $instance;

    /**
     * для хранения экземпляра класса PDO
     */
    private $pdo;


    /**
     * Метод инициализации, необходимо запускать перед началом работы
     */
    public static function init($host, $database, $user, $password) {
        self::$instance = new self($host, $database, $user, $password);
    }

    /**
     * Функция возвращает ссылку на экземпляр данного класса,
     * реализация шаблона проектирования «Одиночка»
     */
    public static function getInstance() {
        return self::$instance;
    }

    /**
     * Закрытый конструктор, необходим для реализации шаблона
     * проектирования «Одиночка»
     */
    private function __construct($host, $database, $user, $password) {
        // создаем новый экземпляр класса PDO
        $this->pdo = new PDO(
            'mysql:host=' . $host . ';dbname=' . $database,
            $user,
            $password,
            array(
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
            )
        );
    }

    /**
     *  Метод-обертка для PDOStatement::execute()
     */
    public function execute($query, $params = array()) {
        // подготавливаем запрос к выполнению
        $statementHandler = $this->pdo->prepare($query);
        // выполняем запрос
        return $statementHandler->execute($params);
    }

    /**
     * Метод-обертка для PDOStatement::fetchAll()
     */
    public function fetchAll($query, $params = array()) {
        // подготавливаем запрос к выполнению
        $statementHandler = $this->pdo->prepare($query);
        // выполняем запрос
        $statementHandler->execute($params);
        // получаем результат
        $result = $statementHandler->fetchAll(PDO::FETCH_ASSOC);
        // возвращаем результаты запроса
        return $result;
    }

    /**
     * Метод-обертка для PDOStatement::fetch()
     */
    public function fetch($query, $params = array()) {
        // подготавливаем запрос к выполнению
        $statementHandler = $this->pdo->prepare($query);
        // выполняем запрос
        $statementHandler->execute($params);
        // получаем результат
        $result = $statementHandler->fetch(PDO::FETCH_ASSOC);
        // возвращаем результат запроса
        return $result;
    }

    public function fetchOne($query, $params = array()) {
        // подготавливаем запрос к выполнению
        $statementHandler = $this->pdo->prepare($query);
        // выполняем запрос
        $statementHandler->execute($params);
        // получаем результат
        $result = $statementHandler->fetch(PDO::FETCH_NUM);
        // возвращаем результат запроса
        if (false === $result) {
            return false;
        }
        return $result[0];
    }

    public function lastInsertId() {
        return (int)$this->pdo->lastInsertId();
    }

    public function beginTransaction() {
        return $this->pdo->beginTransaction();
    }

    public function commit() {
        return $this->pdo->commit();
    }

    public function rollBack() {
        return $this->pdo->rollBack();
    }
}

Дополнительно

Скачать исходные коды на GITHUB.COM

Поиск: MySQL • PHP • Web-разработка • База данных • Миграции