Как я базу в GIT закачивал

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

В других и вовсе приводит к засорению базы мусором с других площадок и к ошибкам после «простейшего мержа».

Знакомых с такими ситуациями, критиков и знающих точно, что я изобрел велосипед — приглашаю под кат.

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

Общая концепция проста как апельсин и принимает 2 установки:

1. База данных должна знать о том, какие патчи к ней применялись.
2. При создании связей в записях ни в коем случае не используются значения идентификаторов. (кроме полученных в вычислениях).

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

Итерации в процессе создания схемы.

«Кто создал столбец?»
«Что здесь должно храниться? Откуда эти числа? Пишите хотя бы комментарии!»
«Мы это уже 100 лет как не используем. Откуда оно здесь?»

Знакомо? Многие принимают как аксиому тот факт, что база данных существует «как есть». Это письмо Дяди Федора, которое не имеет своего автора. Но все так лишь отчасти. У каждого изменения, столбца и записи есть свой автор. Все изменения делаются на линии времени. Похоже на git / svn, etc.? Мы все активно пользуемся системами версионного контроля и прекрасно сдружились со всеми бонусами, которые дарит нам такой подход. Давайте попробуем применить его здесь.

Итак, переходим к практике и взглянем на несметное количество папок в проекте.

Для начала создадим таблицу с информацией о патчах. Здесь мы запомним какие патчи у нас отработали (name + type) и с каким результатом (result)

dc.sql

CREATE TABLE IF NOT EXISTS dc (
  id INTEGER(11) AUTO_INCREMENT NOT NULL,
  code VARCHAR(100) NOT NULL,
  type VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  result VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  m_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Чтобы все работало так, как мы хотим, добавим самый главный файл с оригинальным названием.

start.sh

#!/usr/bin/env bash

username="habr"
password="habr"
database="mydatabase"

cd COMMON
mysql --user ${username} --password=${password} -D${database} < dc.sql
if [ $? -eq "1" ]; then
    exit $?
fi

echo ''
echo '>>> TABLES'
echo ''
cd TABLE
FILES=*
for f in ${FILES}
do
  scriptName=`expr "$f" : '\([a-z_]*\)'`
  var=$(mysql --user ${username} --password=${password} -D${database} <<< "select count(*) from information_schema.tables as t where t.TABLE_NAME='${scriptName}'" -s)
  if [ ${var} -ne '1' ]; then
    echo "Processing $f file..."
    mysql --user ${username} --password=${password} -D${database} < ${f}
    mysql --user ${username} --password=${password} -D${database}<<<"INSERT INTO dc (code, type, result) values ('${f}', 'CREATE TABLE', "$?")"
    if [ $? -ne 0 ]; then
        exit $?
    fi
  else
    echo '--- Skip '${f}' ---'
  fi
done

echo ''
echo '>>> FOREIGN KEYS'
echo ''
cd ../F_KEY
FILES=*
for f in ${FILES}
do
  scriptName=`expr "$f" : '\([a-z_A-Z]*\)'`
  var=$(mysql --user ${username} --password=${password} -D${database} <<< "select count(*) from information_schema.table_constraints as t where t.constraint_name='${scriptName}'" -s)
  if [ ${var} -ne '1' ]; then
    echo "Processing $f file..."
    mysql --user ${username} --password=${password} -D${database} < ${f}
    mysql --user ${username} --password=${password} -D${database}<<<"INSERT INTO dc (code, type, result) values ('${f}', 'CREATE FK', "$?")"
    if [ $? -ne 0 ]; then
        exit $?
    fi
  else
    echo '--- Skip '${f}' ---'
  fi
done
echo ''


echo ''
echo '>>> LOAD DATA SCRIPTS'
echo ''
cd ../DATA
FILES=*
for f in ${FILES}
do
  scriptName=`expr "$f" : '\([a-z0-9]*\)'`
  var=$(mysql --user ${username} --password=${password} -D${database} <<< "select count(*) from ${database}.dc as t where t.code='${f}' and result='0'" -s)
  if [ ${var} -ne '1' ]; then
    echo "Processing $f file..."
    mysql --user ${username} --password=${password} -D${database} < ${f}
    mysql --user ${username} --password=${password} -D${database}<<<"INSERT INTO dc (code, type, result) values ('${f}', 'LOAD DATA', "$?")"
    if [ $? -ne 0 ]; then
        exit $?
    fi
  else
    echo '--- Skip '${f}' ---'
  fi
done
echo ''

echo ''
echo '>>> LOAD TRIGGERS'
echo ''
cd ../TRIGGER
FILES=*
for f in ${FILES}
do
  scriptName=`expr "$f" : '\([a-z_0-9]*\)'`
  var=$(mysql --user ${username} --password=${password} -D${database} <<< "select count(*) from information_schema.triggers as t where t.trigger_name='${f}'" -s)
  if [ ${var} -ne '1' ]; then
    echo "Processing $f file..."
    mysql --user ${username} --password=${password} -D${database} < ${f}
    mysql --user ${username} --password=${password} -D${database}<<<"INSERT INTO dc (code, type, result) values ('${f}', 'LOAD TRIGGER', "$?")"
    if [ $? -ne 0 ]; then
        exit $?
    fi
  else
    echo '--- Skip '${f}' ---'
  fi
done
echo ''


exit $?

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

Никаких коллизий идентификаторов

Гораздо интереснее наполнение каталогов данными. Оно проводится через исполнения go-скриптов в каталоге /COMMON/DATA.

Факт отработки скрипта фиксируется в нашей таблице dc (data containers). Если все было ок — при следующем прогоне файл пропускаем.

Исполнение идет в алфавитном порядке, так что наиболее универсальным является использование timestamp в имени.

В самом простом исполнении это обычные INSERT-запросы со светлой надеждой на стабильность и предсказуемость автоинкремента.

-- добавление базовых ресурсов

INSERT INTO ds.reso_type (name, description) VALUES ('Пища', 'Основной ресурс, обеспечивающий жизнь');
INSERT INTO ds.reso_type (name, description) VALUES ('Дерево', 'Основной ресурс, обеспечивающий строительство');
INSERT INTO ds.reso_type (name, description) VALUES ('Камень', 'Основной ресурс, обеспечивающий строительство');


# noinspection SqlResolve
INSERT INTO ds.human_type (name, description) VALUES ("Чернь", "Кто все эти люди?");

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

INSERT INTO reso_speed (resoId, popId, speed) VALUES (
  (SELECT ht.id
   FROM human_type ht
   WHERE ht.name = 'Чернь'),
  (SELECT rt.id
   FROM reso_type rt
   WHERE rt.name = 'Пища'),
  30);

Или же напишем вспомогательные функции.

#создание tasktype
DELIMITER //
CREATE FUNCTION installTaskType(name VARCHAR(50), title VARCHAR(100), groupId INT(11), description VARCHAR(1000))
  RETURNS int(11)
  BEGIN
    SELECT count(id) into @c from tasktype as t where t.name = name COLLATE 'utf8_unicode_ci';
    if (@c = 0) THEN
      insert  into `tasktype`(`name`,`title`,`group_id`,`description`)
      VALUES (name, title, groupId, description);
      RETURN LAST_INSERT_ID();
    END IF;
    SELECT id INTO @taskTypeId FROM tasktype as t where t.name = name COLLATE 'utf8_unicode_ci';
    RETURN @taskTypeId;
  END //
DELIMITER ;

# создание новых taskgroup
DELIMITER //
CREATE FUNCTION installTaskGroup(name VARCHAR(255))
  RETURNS INT(11)
  BEGIN
    SELECT count(id) into @c FROM taskgroup as t where t.name = name COLLATE 'utf8_unicode_ci';
    if (@c = 0) THEN
      INSERT INTO taskgroup (`name`) VALUES (name);
      RETURN LAST_INSERT_ID();
    END IF;
    SELECT id INTO @groupId FROM taskgroup as t WHERE t.name = name COLLATE 'utf8_unicode_ci';
    RETURN @groupId;
  END //
DELIMITER ;

В go-скрипт пишем:

SELECT installTaskGroup('TEST_GROUP') into @groupId;
SELECT installTaskType('TEST_TASK', 'Это тестовая задача отправки статьи на HABR', @groupId, '');

Итак, мы получили подход, который позволяет выработать единую систему управления базой данных на примере git + mysql.

В качестве профита получили:

— авторство + в некоторых случаях привязка задач таск-трекера;
— последовательность в накате данных;
— новый проект на jenkins;
— спокойные нервы.

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

Тапками сильно не закидывайте — это первая попытка опубликоваться на Хабре (девушкам можно).