Открыть список
Как стать автором
Обновить

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

raw SQL vs PHP api

ORM в миграциях нужна не столько для «удобства», сколько для потенциальной возможности смены СУБД, при чистом SQL придется его переписывать, с ORM — нет. Конечно при специфичных моментах в диалектах это не поможет, но переписывать в любом случае придется меньше. + превратить orm код в чистый sql легко, в отличие от обратного

Транзакции в DDL

Почему только Postgress? К чему этот пункт? Разве есть современные субд, которые не поддерживают транзакции?
Разве есть современные субд, которые не поддерживают транзакции?

MyISAM

Под современным я имел ввиду все-таки те, которым меньше 10 лет. А с Mysql 5.5 этот движок уже не являлся основным
ORM в миграциях нужна не столько для «удобства», сколько для потенциальной возможности смены СУБД

В миграциях этого точно не нужно — они одноразовые.

Транзакции в DDL

НЕ все базы поддерживают транзакции на уровне запросов по изменению структуры БД.
Долго работал и мучался с MySQL, у которой этого не было. Как там сейчас уже не в теме.
> В миграциях этого точно не нужно — они одноразовые.
А как же кейс когда приходит новый разработчик и разворачивает проект имея пустую базу? Ему дампом структуру базы выдают?
Когда проекту больше 5 лет и миграций за тысячу — как-то по другому надо

Хранить начальную структуру в какому-нибудь initial.sql, рядом миграции. Раз в пару-тройку месяцев снимать актуальную структуру в initial.sql, при этом удаляя миграции.

И большинство из них работают с миграциями — командами для внесения инкрементных изменений в схему базы данных.

Я не очень глубоко влазил в Doctrine по этому вопросу, но у меня сложилось впечатление, что Doctrine DBAL считывает текущую схему данных из базы, позволяет разработчику через PHP-код модифицировать её путем удаления/добавления таблиц/столбцов, затем сама генерирует набор SQL-запросов, переводящих структуру данных из начального состояния в желаемое.


Разумеется, что делает она это не настолько хорошо, как это может сделать сам разработчик на чистом SQL, заточенном под конкретную СУБД, но меня во всём этом привлекает идея создания моделей схемы данных — начальной и конечной. Doctrine создает модель в памяти из PHP-объектов, но можно модель описывать в виде XML/JSON/YAML/… В этом случае вся схема данных, допустим в XML, ложится под стандартный контроль версий (как и SQL-файлы).


Для двух различных моделей схем данных можно прогонять миграцию прямую и обратную (rollback). Два развития одной базовой модели от двух (и более) разных девелоперов можно прогнать через процедуру слияния (текст структурированный), либо вообще собирать конечную модель из отдельных фрагментов, где каждый девелопер сам развивает свой участок общей схемы данных.


Для таблиц такой структурированный подход уже работает (Magento 2.3). Для views/triggers/procedures/… более проблематично (здесь, скорее всего, пойдут вставки чистого SQL, что нивелирует преимущества структурирования информации).


Тем не менее, если не увлекаться DB-программированием (триггеры и SQL-процедуры), то можно достаточно успешно структурировать SELECT, который лежит в основе создания views. А tables & views — основа схемы данных. Т.е., если переводить XML/JSON/YAML-модель в Doctrine-модель, то далее Doctrine DBAL уже сама сможет выполнять миграцию.


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

Мы пишем миграции на чистом SQL. Многие инструменты предоставляют PHP-api для написания инструкций транслируемых в SQL-код. Теперь я вообще не понимаю зачем это?

Есть такие штуки, как продукты, которые должны работать с любыми SQL-базами. CMS, например. Это не про модели и константы, которые да, меняются. Это про DSL в самих миграциях.

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

Для этого нужно вести не только миграции, но и полноценное версионированное описание схемы. Больше того — это описание первично по отношению к миграциям (которые всего лишь описывают один из бесконечного числа возможных путей перевода БД из состояния А в состояние Б).
А как вы это делаете?
Пока, в основном, в мечтах :)
Но в комментариях к одной из приведённых статей звучали термины state-driven deployment и change-driven deployment.
Общая идея, в целом, достаточно проста: у вас должны быть скрипты, описывающие состояние схемы. Из них можно в любой момент собрать любую версию БД «с нуля» (и по ним же посмотреть кто, что и когда изменил). А миграции — это отдельная часть проекта (вторичная по отношению к схеме), причём имея в системе контроля версий состояния вы всегда можете убедиться в том, что ваша миграция делает именно то, что вы хотели (собрав с нуля схему версии N, а рядом версии N-1 + накат на неё миграции и сравнив их между собой).
Это если вкратце.
У нас для этого есть две консольные команды, условно export и import. Соотвественно, первая — умеет делать дамп схемы, хранимых процедур и данных из заранее указанных таблиц — словари, миграции и т.п. Вторая команда умеет создавать базу из такого дампа и загружать данные в нужные таблицы. Собранные дампы добавлены в систему контроля версий. При создании новой миграции, после её применения, мы запускаем первую команду. Это обновляет дампы и даёт возможность отслеживать изменения с помощью системы контроля версий, без анализа самих миграций. Эти команды так же используются для разворачивания проекта и автоматического тестирования. Что позволяет избегать запуска сотен миграций. Всё это замечательно работает на базе Yii уже много лет.
Интересно, сможете показать исходники export?
Тут нечего показывать. В проекте только MySQL. Родной дамп оказался быстрее и проще. Для схемы используется банальный mysqldump:
mysqldump --add-drop-table --no-data --routines $DBNAME > $SCHEMA
Аналогично для выборки данных из нужных таблиц. Данные из таблиц храним в отдельных файлах, так с ними удобнее работать.

Импорт чуть более сложен, но только из-за добавленой интерактивности. Схема разбивается на части и выполняется по частям. Это позволяет видеть процесс по каждой таблице и сразу загружать её данные. Данные так же грузим частями, чтобы был виден прогресс и чтобы не спотыкаться на размере буфера. У нас в схеме больше сотни таблиц, некоторые словари имеют десятки тысяч записей и занимают несколько мегабайт. Без такой разбивки процесс импорта весьма скучен :)

Пример кода для импорта, чтобы было чуть более понятно
// search for all tables in the schema
if (preg_match_all('%\s*DROP\s+TABLE\s+(?:IF\s+EXISTS\s+)?(?#
	  )\`([a-z_A-Z]+)\`.+(?=DROP\s+TABLE|\/\*\!\d+\s+DROP\s+FUNCTION(?#
	  )|\/\*\!\d+\s+DROP\s+PROCEDURE)%sU', $sSchema, $aMatches))
{
	$sSql = '';
	foreach ($aMatches[1] as $i => $sTable)
	{
		$this->write(sprintf('    > create table %s ...', $sTable));

		$sSql = $aMatches[0][$i];

		// apply global settings from sql dump header
		if ($i === 0)
		{
			$sSql = substr($sSchema, 0, strpos($sSchema, $sSql)) 
				. $sSql;
		}

		$iTime = microtime(true);

		// clear auto increment value
		$sSql = preg_replace('/AUTO_INCREMENT=\d+/', '', $sSql);

		Yii::app()->db->createCommand($sSql)->execute();

		$this->write(sprintf(' done (time: %.3fs)' . PHP_EOL,
			microtime(true) - $iTime));
			
		$this->loadTableData($sTable);
	}

	// leave only routines in schema
	$sSchema = substr($sSchema, strpos($sSchema, $sSql) +
		strlen($sSql));
}

// and then routines
if (preg_match_all('%(?:/\*!\d+\s+)?DROP\s+(FUNCTION|PROCEDURE)\s+(?#
	  )(?:IF\s+EXISTS\s+)?`([a-z_A-Z]+)`.+\sDELIMITER\s\;\;(.+)\;\;(?#
	  )\s+DELIMITER\s\;\s%Ums', $sSchema, $aMatches))
{
	foreach ($aMatches[2] as $i => $sName)
	{
		$this->write(sprintf('    > create %s %s ...',
			strtolower($aMatches[1][$i]), $sName));

		$sDropSql = preg_replace('/;.+$/Ss', '', $aMatches[0][$i]);

		$iTime = microtime(true);

		// drop function or procedure if exists
		Yii::app()->db->createCommand($sDropSql)->execute();

		Yii::app()->db->createCommand($aMatches[3][$i])->execute();

		$this->write(sprintf(' done (time: %.3fs)' . PHP_EOL,
			microtime(true) - $iTime));
	}
}

Когда в MySQL нужно накатить дополнительный индекс или добавить колонку в таблицу с 50 миллионами записей — в своем большинстве эти инструменты никак не подходят.
Впервые об этом я узнал, когда по неопытности локнул таблицу на 4 часа…
После чего приходится использовать percona-toolkit, которая создает временную таблицу с новой структурой, а на старую цепляет триггеры. Это позволяет делать редактирование таблиц без простоев.
Как я понимаю, эта проблема актуальна не для всех баз, но подобная ошибка может быстро отучить от использования миграций на больших таблицах.

Придумали ли какое-то решение для этого сценария?
Обычно делаем CREATE INDEX CONCURRENTLY — PG создает индекс без блокировки таблицы в реалтайме. Ну и медленнее он работает конечно.
А про добавление колонок на больших таблицах — здесь хорошо описано:
Опыт 1440 миграций баз данных — habr.com/company/wrike/blog/414441
Колонка добавляется как nullable ну и дальше аккуратно заполняется.
как посмотреть историю изменений конкретного объекта структуры БД. По каждой таблице хочется посмотреть историю изменений в связи с решением конкретных задач.


Вот тут и тут рассказывалось об очень удобной тулзе RedGate SQL Compare. Использовали на проекте одном, вполне годная для версионирования изменений в базе данных, но конфликты она решать не умеет (например при добавлении not null поля в существующую таблицу с данными).

Также в Phalcon Framework видел хорошее решение подобной проблемы, но там тоже не нативный SQL, а DSL описание бд (тут).
Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.