Обновление сайта, обновление схемы БД (MySQL)

    Проблема — нужно обновлять сайт (ака «svn up») плюс обновить схему БД — добавить таблицы, индексы и т.п.
    SQL запросы на обновление БД хранятся в репозитории, необходимо запустить нужный SQL после обновления кода приложения.

    Сложность: 1) нельзя, чтобы один и тот же SQL выполнился два раза. 2) выполнять запросы нужно в определенной последовательности (нельзя сделать ALTER TABLE до создания).



    Как?



    1. Регламент относительно SQL запросов на изменение структуры БД — разработчик знает, что SQL быдет выполняться на LIVE сервере со всеми вытекающими.

    2. Регламент относительно именования файлов с SQL запросами

    0034.users_added_balance_column.sql

    файлы нумеруются для того, чтобы однозначно сохранялся порядок выполнения

    3. Используется специальный формат файлов с SQL запросами (шаблон):

    SET @version='users_added_balance_column';

    CREATE TABLE IF NOT EXISTS `dbversions` (`version` varchar(200) NOT NULL,`dt_applied` datetime default NULL,UNIQUE KEY `version` (`version`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; DROP PROCEDURE IF EXISTS prc_update;
    DELIMITER //
    CREATE PROCEDURE prc_update(version_to_check VARCHAR(200)) BEGIN SET @isversion=(SELECT `version` FROM `dbversions` WHERE `version`=version_to_check); IF ISNULL(@isversion) THEN
    INSERT SQL HERE (BELOW)

    ALTER TABLE `user_groups` ADD INDEX ( `userId` );

    ALTER TABLE `user_groups`
    ADD CONSTRAINT `user_groups_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

    END, DO NOT INSERT SQL BELOW THIS LINE
    INSERT INTO `dbversions` SET `version`=version_to_check, dt_applied=NOW(); SET @echo_string = CONCAT('Executed ', version_to_check);
    ELSE SET @echo_string = CONCAT('Skipped ', version_to_check); END IF; SELECT @echo_string AS '';
    END //
    DELIMITER;
    CALL prc_update(@version);DROP PROCEDURE IF EXISTS prc_update;SET @version=NULL;


    * This source code was highlighted with Source Code Highlighter.


    То есть используется всегда «шапка» и «подвал», которые, собственно и гарантируют, что SQL выполниться один раз. В начале файла прописывается метка (совпадает с именем файла).

    Суть происходящего: в таблице dbversions хранятся метки всех выполненных запросов. Хранимая процедура каждый раз проверяет, не был ли запущен данный SQL запрос на данной базе.

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

    #!/usr/bin/php
    <?php

      require( dirname(__FILE__).'/../bootstrap_cli.php' );

      $dir = dirname(__FILE__);
      
      list($dbName, $dbUser, $dbPassword, $dbHost) = split('/', Config::$databasesConnections['main']);
      
      if (!empty($dbPassword)) {
        $dbPassword_cmdln = '-p'.$dbPassword;
      } else {
        $dbPassword_cmdln = '';
      }
      
      foreach (glob($dir.'/*.sql') as $sqlFile) {

        system("mysql -u {$dbUser} {$dbPassword_cmdln} {$dbName} < {$sqlFile}");
      }


    * This source code was highlighted with Source Code Highlighter.


    Всё это позволяет оперативно обновлять сервер (staging, live, рабочую копию) — svn up, потом updatedb.php — без боязни забыть что-то обновить либо поломать базу.

    Благодаря такой организации, приложение «поднимается» практически на любой машине за несколько минут -дампы БД не нужны — приложение целиком находиться в репозитории (SVN).
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 44

      +9
      Это называется database (schema) migrations и в гугле легко находится десяток готовых решений, как тривиальных так и весьма навороченных.
      И в них решены некоторые проблемы, с которыми вам вероятно придётся столкнуться при столь прямолинейном подходе.
        +4
        Одно из них — liquibase, которое здесь не раз уже упоминалось
          +1
          возможно я не умею эффективно пользоваться гуглом, но поиск навскидку ничего годного не показал. ткните плиз носом? :-)
        +6
        а откат как делать?
          0
          если учесть, что операция отката не всегда возможна — то оптимальнее просто сохранить дамп до операции и если что вернуть его на место.
            –1
            то есть как это не возможна? 0_0"
              0
              расскажите, как бы вы реализовали операцию отката удаления поля или таблицы.
                0
                я его не буду удалять до тех пор, пока откат не потеряет смысла
                  0
                  в чём тогда смысл миграций, если они не выполняются?
                    0
                    а в чём смысл миграций, если их нельзя откатить?
                      0
                      смысл миграций в переезде со старой схемы на новую.
                        0
                        нет, их смысл — обеспечить актуальность структуры бд
                          0
                          актуальность это и есть смена схемы.
                            0
                            угу, как в прямую сторону, так и в обратную при откате
                              0
                              совершенно верно. через минуту после того как вы совершите удаление («я его не буду удалять до тех пор, пока откат не потеряет смысла „) вам потребуется таки вернуть всё как было ибо найден страшный-страшный баг.
                              ваши действия? :-)
                                0
                                если он пережил несколько релизов, значит он афайк не критичен.
                                  0
                                  баг исключительно в текущей версии продукта. в которой как раз и изменилась схема. в прошлой версии — его не было.
                                    0
                                    ну вот и что ты тогда будешь делать со своими необратимыми изменениями?
                                      0
                                      как я и говорил — просто будет возврат на бэкап. а вот что вы-то будете делать?
                                        0
                                        и потерять все данные пользователей, занесённые пока ты решал стоит ли откатываться?

                                        а я не делаю необратимых изменений при релизе :-Р
                                          0
                                          ну всё, фиаско :-)
                                          моя антагонистическая точка зрения таки проиграла. в качестве утешительного приза: назовите плиз стоящую реализацию для осуществления миграций на mysql (и ms sql) ;-)
                                            0
                                            мы это в ручную делаем…
                                              0
                                              слишком рутинно и подвержено человеческим ошибкам, вон люди в первом комменте говорили о наличии клёвых инструментов, правда на коммент не ответили пока :-(

                                              ps: как же мне дороги эти кружочки слева от постов, из-за которых мой ff просто по швам трещит при рендеринге страницы
                                                0
                                                да, эти деревянные обсуждения — такая гадость… тем более реализованные через вложенные списки +_+"
          +4
          Не увидел в SQL коде транзакций… Процедура — атомарна?

          ЗЫ: «Оперативно», говорите? А откуда тогда берутся топики про «как добавить колонку в много гигабайтную таблицу»?
            +1
            ALTER TABLE в транзакции… мсье думает, что он — в сказке?
              0
              Мьсе думает что чтение+запись из таблицы dbversions должны быть атомарной операцией.
            0
            > дампы БД не нужны — приложение целиком находиться в репозитории
            Это как? Или Вы про «начальное» состояние БД?
              0
              Чего только люди не придумают, чтобы не пользовать миграции.
                0
                это и есть миграции, только наколенные.
                –1
                Вместо подобных решений я использую проверку существования изменяемых/добавляемых полей в ALTER TABLE. Пример:
                $db->sql_query(«select nocashe from `config`;») or $db->sql_query(«ALTER TABLE `config` ADD `nocashe` text NOT NULL;»);
                Т.е. если поля «nocashe» в таблице «config» нет, оно будет добавлено.

                С проверкой таблиц итак все давно известно: CREATE TABLE IF NOT EXISTS…

                Для того, чтобы обновлять все устаревшие сайты, я использую один постепенно растущий скрипт, в который добавляю подобные команды по мере каких-либо изменений в БД. В итоге никакими средствами автоматизации пользоваться не приходится вообще.
                  0
                  Для меня самое удобный инструмент по решению подобных задач — SqlYog с встроенной функцией Schema Synchronization Tool
                    +1
                    Аналогично, очень удобно, т.к. можно выбрать что залить, а что нет.
                      –1
                      это если внешние подключения разрешены
                        0
                        не только, в комплект программы входит замечательный туннель на PHP (SQLyogTunnel.php)
                        0
                        Если сайтов много и обновлений много, то это нереально. С одним — двумя проектами можно.
                        0
                        как и svn up на боевой копии — хороший способ отстрелить себе ногу в случае неаккуратного использования.
                        пункт два по нумерации невыполним, когда над проектом работает несколько разработчиков, собьётся очерёдность. дифф понадёжнее.
                          +1
                          А как поступаете, если один разработчик шлет в репозиторий 0034.users_added_balance_column.sql, а второй 0034.users_added_saldo_column.sql?
                            0
                            почти что rake db:migrate
                              0
                              Не совсем, конечно, для PHP, но все же.
                                +1
                                Не вижу LOCK TABLES и UNLOCK TABLES.
                                Или не возникало такой необходимости?
                                • НЛО прилетело и опубликовало эту надпись здесь

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

                                  Самое читаемое