Pull to refresh

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

Reading time 3 min
Views 5.5K
Проблема — нужно обновлять сайт (ака «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).
Tags:
Hubs:
+7
Comments 44
Comments Comments 44

Articles