Как написать скрипт sql

октябрь
16
, 2016

Когда разрабатываешь веб-приложение не один, а в команде и/или на нескольких машинах, рано или поздно сталкиваешься с проблемой синхронизации кода проекта и базы данных. Для управления кодом есть системы контроля версий, в частности, git, а для СУБД придуманы миграции.

Есть много готовых разнообразных инструментов, которые занимаются миграциями, но!

Очень часто все, что мы хотим — это просто залить в базу изменения, которые сделаны другим разработчиком или же самим собой на другой машине. И желательно при этом затратив минимальные усилия, в том числе и на изучение и настройку незнакомой системы. К тому же далеко не всегда мы располагаем полным доступом к серверу для установки оных инструментов.

Поэтому всех, кому интересно узнать, как самим сделать простую утилиту миграций, написав полсотни строк php-кода, прошу в статью.

Идея миграций

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

Нужно учесть 2 вещи: во-первых, каждая миграция должна выполняться строго один раз, а во-вторых, в строго определенном порядке.
Это разумно и обязательно, потому как если нам прилетают от коллеги 2 миграции, одна из которых создает таблицу users,
а другая добавляет в нее тестовых пользователей, то мы хотим выполнить эти скрипты именно в таком порядке
и не добавить при этом данные в базу больше одного раза.

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

Создаем тестовые sql-скрипты

Пусть у нас есть тестовая база данных под названием test.
Мы работаем с ней какое-то время и решили внедрить миграции.
Есть разумное правило: самая первая миграция должна содержать в себе полный дамп уже существующих сущностей в базе.
Уточню: миграции помогают не только последовательно расширять уже существующую базу, но еще и накатить эту самую базу с нуля,
например, для новых людей в команде.

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

В тестовом примере рассмотрим такой ход событий.

Первая миграция накатывает уже существующие данные и создает таблицу versions — она содержит уже выполненные скрипты.

Вторая создает таблицу users с тремя полями: id, email, password.

Третья добавляет в users три тестовых записи.

А четвертая добавляет в users новую колонку active.

Напишем первую миграцию: дамп базы и таблица versions.
Пусть на момент внедрения миграций у нас есть таблица goods с парой товаров.
Их нужно скинуть в скрипт и в тот же скрипт добавить таблицу versions.
В итоге файл будет выглядеть так.

0000_base.sql

    -- Дамп существующей базы --
    
    -- Таблица товаров --
    create table `goods` (
        `id` int(10) unsigned not null auto_increment,
        `name` varchar(255) not null,
        `price` int(11) not null,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;
    
    -- Данные из таблицы товаров --
    insert into `goods` (`name`, `price`) values 
        ('Ноутбук', 30000),
        ('Телефон', 20000);
    
    -- /Дамп существующей базы --
    
    
    -- Таблица versions --
    create table if not exists `versions` (
        `id` int(10) unsigned not null auto_increment,
        `name` varchar(255) not null,
        `created` timestamp default current_timestamp,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;    

В файле мы видим структуру goods и 2 строчки с данными, а также versions.
Эта таблица содержит 3 столбца: первичный ключ id, name — имя файла с миграцией, created — дата ее накатывания.
Как Вы догадываетесь, в versions мы будем добавлять строки после накатывания новых миграций.
А пока создадим оставшиеся 3 миграции.

0002_add_users.sql

    -- Таблица пользователей --
    create table if not exists `users` (
        `id` int(10) unsigned not null auto_increment,
        `email` varchar(255) not null,
        `password` varchar(255) not null,
        primary key (id)
    )
    engine = innodb
    auto_increment = 1
    character set utf8
    collate utf8_general_ci;

0003_insert_data_into_users.sql

    -- Добавляем тестовых пользователей --
    insert into `users` (`email`, `password`) values 
        ('test1@gmail.com', '111111'),
        ('test2@gmail.com', '222222'),
        ('test3@gmail.com', '333333')

0004_add_column_active_to_users.sql

    -- Добавляем колонку active в users --
    alter table `users` 
        add column `active` tinyint(1) not null default 1 after `password`;

На заметку: возможно, Вам не удобно писать sql-скрипты руками, Вы привыкли создавать и заполнять таблицы через phpMyAdmin
или другой инструмент. Спешу успокоить, все известные мне утилиты позволяют генерировать такой sql-код автоматически.
То есть Вы можете работать с базой, как удобно, а при подготовке файла-миграции вытащить нужный скрипт из условного phpMyAdmin-a в режиме copy-paste.

Обратим внимание на правила наименования файлов — это важная часть.

• 0001_base.sql
• 0002_add_users.sql
• 0003_insert_data_into_users.sql
• 0004_add_column_active_to_users.sql

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

В нашем варианте мы предполагаем, что миграций не будет больше 9999 штук, и номер всегда должен состоять из 4-х цифр.
Например, 10-я миграция будет называться 0010_description, а 101-я — 0101_description.

Теперь у нас все готово к написанию php-скрипта для выполнения миграций — migration.php.

Пишем php-код для запуска миграций

Подумаем, что нам нужно уметь делать.

• Во-первых, получить список всех sql-файлов из папки sql.
• Во-вторых, понять, какие из них уже накатились ранее (сравнить этот список с тем, что лежит в таблице versions).
• В-третьих, залить содержимое отобранных файлов в базу, заодно записав в versions их названия.

Пожалуй все, остальные операции довольно просты, разберем их по ходу пьесы.

Можно писать код.

Каркас migration.php, базовые константы и функции

Нам нужно написать фунцию подключения к базе данных и общую логику работы скрипта.
Создадим файл migration.php, кинем его в папку sql рядом с миграциями и напишем в нем следующее:

    // Объявляем нужные константы
    define('DB_HOST', 'localhost');
    define('DB_USER', 'root');
    define('DB_PASSWORD', 'root');
    define('DB_NAME', 'test');
    define('DB_TABLE_VERSIONS', 'versions');
    
    
    // Подключаемся к базе данных
    function connectDB() {
        $errorMessage = 'Невозможно подключиться к серверу базы данных';
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        if (!$conn)
            throw new Exception($errorMessage);
        else {
            $query = $conn->query('set names utf8');
            if (!$query)
                throw new Exception($errorMessage);
            else
                return $conn;
        }
    }
    
    
    // Получаем список файлов для миграций
    function getMigrationFiles($conn) {
        // ...
    }
    
    
    // Накатываем миграцию файла
    function migrate($conn, $file) {
        // ...
    }
    
    
    // Стартуем
    
    // Подключаемся к базе
    $conn = connectDB();
    
    // Получаем список файлов для миграций за исключением тех, которые уже есть в таблице versions
    $files = getMigrationFiles($conn);
    
    // Проверяем, есть ли новые миграции
    if (empty($files)) {
        echo 'Ваша база данных в актуальном состоянии.';
    } else {
        echo 'Начинаем миграцию...
';
    
        // Накатываем миграцию для каждого файла
        foreach ($files as $file) {
            migrate($conn, $file);
            // Выводим название выполненного файла
            echo basename($file) . '
';
        }
    
        echo '
Миграция завершена.';    
    }

Пробежимся по коду.

• Сначала объявляем константы для подключения к базе и с названием таблицы versions (она будет упоминаться в коде несколько раз).
• Функция connectDB возвращает mysqli-объект для работы с базой.
• getMigrationFiles($conn) возвращают список актуальных, еще не выполненых миграций.
• migrate($conn, $file) накатывает одну миграцию — по имени файла.

Дальше сама логика работы.

• Подключаемся к базе и получаем список актуальных миграций.
• Если он пустой, то просто выводим сообщение, что база в актуальном состоянии.
• Если файлы миграции имеются, то запускаем для каждого функцию migrate, выводя название файла для информации.
  А в конце пишем позитивное сообщение, что миграция завершена.

Теперь нам нужен код для getMigrationFiles()

    // Получаем список файлов для миграций
    function getMigrationFiles($conn) {
        // Находим папку с миграциями
        $sqlFolder = str_replace('\', '/', realpath(dirname(__FILE__)) . '/');
        // Получаем список всех sql-файлов
        $allFiles = glob($sqlFolder . '*.sql');
    
        // Проверяем, есть ли таблица versions 
        // Так как versions создается первой, то это равносильно тому, что база не пустая
        $query = sprintf('show tables from `%s` like "%s"', DB_NAME, DB_TABLE_VERSIONS);
        $data = $conn->query($query);
        $firstMigration = !$data->num_rows;
        
        // Первая миграция, возвращаем все файлы из папки sql
        if ($firstMigration) {
            return $allFiles;
        }
    
        // Ищем уже существующие миграции
        $versionsFiles = array();
        // Выбираем из таблицы versions все названия файлов
        $query = sprintf('select `name` from `%s`', DB_TABLE_VERSIONS);
        $data = $conn->query($query)->fetch_all(MYSQLI_ASSOC);
        // Загоняем названия в массив $versionsFiles
        // Не забываем добавлять полный путь к файлу
        foreach ($data as $row) {
            array_push($versionsFiles, $sqlFolder . $row['name']);
        }
    
        // Возвращаем файлы, которых еще нет в таблице versions
        return array_diff($allFiles, $versionsFiles);
    }

Почти каждая строка прокомментирована, но поясню еще подробнее.

В переменную $sqlFolder попадает полный абсолютный путь к текущему файлу migration.php.
А соответственно, и к папке sql с миграциями.
Замена \ на / нужна для тех случаев, когда realpath возвращает путь с обратными слешами вместо прямых
(например, в windows). Первый нужен для экранирования второго.

Дальше функция glob вытащит все файлы из указанной папки по нужной маске *.sql.

Затем нужно понять, не в первый ли раз мы собираемся накатывать миграции.
Определить это можно только по наличию таблицы versions в базе.
Мы же помним, что она создается в самой первой миграции.
Если запрос show tables from test like «versions» вернет пустой список, значит таблицы еще нет,
в переменную $firstMigration запишем true и вернем из функции весь список файлов.

Если же таблица versions существует, то вытаскиваем из нее список файлов — уже выполненные миграции.
И возвращаем из функции файлы, которых нет в таблице versions.
В этом поможет array_diff.

Все, список нужных файлов мы получили, осталось понять, как закинуть их содержимое в базу — функция migrate.

    // Накатываем миграцию файла
    function migrate($conn, $file) {
        // Формируем команду выполнения mysql-запроса из внешнего файла
        $command = sprintf('mysql -u%s -p%s -h %s -D %s < %s', DB_USER, DB_PASSWORD, DB_HOST, DB_NAME, $file);    
        // Выполняем shell-скрипт
        shell_exec($command);
    
        // Вытаскиваем имя файла, отбросив путь
        $baseName = basename($file);
        // Формируем запрос для добавления миграции в таблицу versions
        $query = sprintf('insert into `%s` (`name`) values("%s")', DB_TABLE_VERSIONS, $baseName);
        // Выполняем запрос
        $conn->query($query);
    }

Из интересного в этой функции только shell_exec($command) — выполнение строки $command в терминале.
Залить содержимое sql-файла в базу можно такой командой

mysql -uuser -ppassword -h host -D database < file.sql

Оную команду формируем с помощью sprintf и передаем ее в shell_exec.
Собственно миграция накатилась, осталось добавить соответствующее название файла в таблицу versions —
это заурядный запрос вида

insert into `versions` (name) values(‘0001_base.sql’)

И это весь код, который нужен нам для создания простой системы миграций.
Теперь создадим пустую базу данных test, закинем, если еще не успели, папку sql куда-нибудь в проект и откроем в браузере sql/migration.php.

На заметку: если Вы не хотите на боевом сайте светить содержимым своих миграций возможным злоумышленникам,
то поместите сами sql-миграции в место, недоступное для просмотра извне.
migration.php можно закинуть куда угодно, хоть в корень проекта, только придется немного поправить пути к папке с миграциями.
Думаю, с этим делом Вы без труда справитесь сами

Итак, migration.php в браузере запущен, если все сделано правильно, то увидим такую картину

    Начинаем миграцию...
    
    0001_base.sql
    0002_add_users.sql
    0003_insert_data_into_users.sql
    0004_add_column_active_to_users.sql
    
    Миграция завершена.

Посмотрите в базу и убедитесь, что нужные таблицы и данные созданы, в versions лежат 4 строки с названиями файлов.
Теперь обновите страницу в браузере и увидите сообщение «Ваша база данных в актуальном состоянии.»
Все правильно, все миграции сделаны, а новых пока нет.

Чтобы окончательно убедиться в работоспособности системы, удалите все три таблицы из базы и оставьте в папке sql только первую миграцию.
Обновите страницу — накатится одна миграция, таблица goods и versions с единственной строкой.
Добавьте в папку миграции 0002_add_users.sql и 0003_insert_data_into_users.sql, обновите, выполнятся еще 2 миграции и в базе появится users с данными.
И наконец последний файл и снова запуск — в users добавится новая колонка active.

Итоги и исходники

Что же у нас получилось?

Все работает, как мы и задумали.

Теперь можно править базу, добавляя все изменения с ней в миграции,
не боясь, что кто-то и когда-то будет работать с неактуальной базой.

Главное, не забывать в нужные моменты при изменении базы создавать соответствующие миграции, соблюдая правила именования файлов,
и запускать скрипт migration.php.

Как видим, код получился действительно короткий и простой (половину занимают комментарии).
И при этом прямые свои обязанности он выполняет — накатывает миграции, не выполняя ничего лишнего.

Я намеренно не стал усложнять код, добавив обработку ошибок при накатывании скриптов.
Также не рассматривал тему даунгрейда — так называемые обратные миграции,
которые по идее должны автоматически откатывать неудачные изменения к предыдущим версиям,
но на практике это опасная процедура и ее все равно приходится делать руками.

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

Но не секрет, что в таких проектах уже давно подобная система используется, Вы можете даже не знать, в какой момент сборщик накатывает новые миграции.
Кстати, это можно сделать и при помощи моего любимого gulp-a, но это уже другая история, и возможно, тема отдельной статьи.

Задавайте вопросы и оставляйте отзывы в комментариях, и конечно, скачивайте исходники