Как автоматически создать (My) Sql ALTER скрипт в установщике?

Есть два подхода к этой проблеме.

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

2. Сценарии изменения предназначены для воздействия на схему при сохранении данных.

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

в второй подход, Прежде всего, вы должны знать, что это невозможно сделать независимо от того, с какой СУБД вы собираетесь работать, поскольку SQL не так стандартен, как кажется. Имея в виду некоторые конкретные СУБД, общим подходом к этой проблеме может быть создание обновленной версии вашей схемы в СУБД и сравнение ее с вашей текущей версией. Вот список инструментов Вы могли бы найти полезным для MySQL.

Вещи, которые вы сможет сделать в этом подходе:

• Проверьте и посмотрите, не удалена ли таблица.

• Проверьте и посмотрите, является ли таблица новой.

• Проверьте и посмотрите, удалено ли поле.

• Проверьте и посмотрите, является ли поле новым.

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

• Проверьте и посмотрите, не изменены ли свойства поля.

Вещи, которые вы не могу сделать в этом подходе:

• Проверьте и посмотрите, переименована ли таблица.

• Проверьте и посмотрите, переименовано ли поле.

Другими словами, переименованные объекты приведут к DROP заявление и CREATE тот, который приведет к потере ваших данных. Это логическая проблема такого подхода, и преодолеть его невозможно. Единственный способ узнать, переименована ли таблица или поле, — это просмотреть список команд alter и найти соответствующие (в случае, если у вас есть список оператора alter, а не только окончательная схема). И реализация этого — отдельная проблема.

Есть один другой ВАЖНЫЙ проблема с этим подходом также; Поскольку мы выбираем ближайший путь к нашей целевой схеме, мы можем пропустить некоторые важные шаги в этом процессе. А именно, подумайте о сценариях, которые вы могли выполнить, которые влияют на данные вашей базы данных, но не на ее схему. Такие операторы не могут быть извлечены с помощью какого-либо инструмента сравнения, поскольку у вас нет ссылок на ваши данные (если только у вас их нет, чего я не считаю вашим). В этом случае ваш единственный выбор — применить список скриптов один за другим в том же порядке, в котором они должны быть применены. И иметь такой список возможно только в том случае, если у вас есть механизм управления версиями или человек должен составить список путем анализа. Я не могу придумать инструмент, который поможет вам в этом случае (если у вас нет версий для ваших баз данных). По крайней мере, я не знаю ни одного!

Похоже, эти сценарии будут статичными. Не могли бы вы включить все сценарии (версии от x.1 до x.2 и от x.2 до x.3, ..etc) и запустить конкретные сценарии, которые нужны пользователю?

В моем приложении я сохранил значение версии базы данных в базе данных.

Мое приложение имеет требуемую версию базы данных.

Вот часть моего кода Pascal-Oracle. Надеюсь, это даст вам хорошую идею.

const
ApplicationsDBVersion = 26 ;
.....
.....
if CurrentDBVersion = ApplicationsDBVersion then
Exit ;
if CurrentDBVersion < 0 then // just in a case that the database is out of sync.
Exit;
updtScript := tStringList.Create ;
if CurrentDBVersion < 1 then
Upgrade2Version1 ;
if CurrentDBVersion < 2 then
Upgrade2Version2 ;
if CurrentDBVersion < 3 then
upgrade2Version3 ;
.....
.....
.....
procedure Upgrade2Version3 ;
begin
UpdateParameter(-3) ; // set that database is in inconsitent state
AddField('tableX','ColX','CHAR(1)') ; // I've plenty of such routines (AddRef, AlterField, DropField,AddTable etc...
AddField('tableX','ColY','char(1) constraint CKC_checkConstraint check (ColY is null or (Coly in (''E'',''H'')))') ;
AddField('TableY','Colz','NUMBER(3)') ;
UpdateParameter(3); // set that database is in consistent state ( no fail in scripts )
runScript(3) ; // actually do the job...
end;
...
procedure UpdateParameter (_dbVersion : Integer) ;
begin
if CurrentDBVersion = 0 then
updtScript.Add('Insert into parametre (parametre,sira_no,deger) values ('+QuotedStr(cPRM_VeriTabaniSurumu)+',1,''1'')')
else
updtScript.Add('update parametre set deger = '+IntToStr(_dbVersion) + ' where parametre = '+QuotedStr(cPRM_VeriTabaniSurumu));
end ;

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

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

Функция принимает (в качестве второго аргумента) массив массивов, где каждый из последних содержит в позиции:

0 - Column name
1 - MySql column type (ex. "int" or "varchar(30)").
2 - whether columns is nullable (true for allow null, false for forbid)
3 - The default value for column (ie. "0").
4 - true, when column is part of primary key
5 - old name of a column (thus column of name in 5., if exists, is going to be renamed to column of name in 0.)

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

Извините за отвратительный контракт, но эта функция никогда не была частью публичного интерфейса. 🙂

Здесь идет CreateOrUpdateTable тело функции (ссылки объяснены позже):

function CreateOrUpdateTable($tablename, array $columns, $allowdropcolumn = false)
{
foreach($columns as &$column)
{
if ((!isset($column[0])) || (!preg_match('/^[a-zA-Z0-9_\-]+$/', $column[0])))
$column[0] = 'TableColumn' . array_search($column, $columns);
if ((!isset($column[1])) || (!preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $column[1])))
$column[1] = 'int';
if ((!isset($column[2])) || (!is_bool($column[2])))
$column[2] = ALLOW_NULL;
if ((!isset($column[3])) || (!is_string($column[3])))
$column[3] = (($column[2] == ALLOW_NULL || $column[1] === 'text') ? 'NULL' : ($column[1] == 'int' ? "'0'" : ($column[1] == 'tinyint' ? "'0'" : ($column[1] == 'decimal' ? "'0.00'" : ($column[1] == 'date' ? "'1900-01-01'" : ($column[1] == 'datetime' ? "'1900-01-01 00:00:00'" : "''"))))));
else
$column[3] = "'" . Uti::Sql($column[3]) . "'";
if ((!isset($column[4])) || (!is_bool($column[4])))
$column[4] = false;
}
unset($column);

if (!$this->TableExists($tablename))
{
$statements = array();
foreach ($columns as $column)
{
$statement = $this->ColumnCreationStatement($column);
if ($statement !== '')
$statements[] = $statement;
}

$this->Query("create table " . $tablename . "(" . implode(',', $statements) . ") ENGINE=InnoDB DEFAULT CHARSET=latin2");
}
else
{
$this->Select("show columns in " . $tablename);
$existing = $this->AllRows(null, 'Field');

$oldkeys = array(); $newkeys = array();
foreach ($existing as $e)
if ($e['Key'] === 'PRI')
$oldkeys[] = $e['Field'];

sort($oldkeys);
$oldkeys = implode(',', $oldkeys);

$lastcolumn = ''; // not 'FIRST' as we can extend existing table here providing only extending columns

foreach ($columns as $column)
{
if ($column[4])
$newkeys[] = $column[0];

$newtype = $column[1] . ($column[1] === 'int' ? '(11)' : ($column[1] === 'tinyint' ? '(4)' : ''));
$newnull = ($column[2] === ALLOW_NULL ? 'YES' : 'NO');
$newdefault = $column[3];

if (isset($existing[$column[0]]))
{
$oldtype = $existing[$column[0]]['Type'];
$oldnull = $existing[$column[0]]['Null'];
$olddefault = isset($existing[$column[0]]['Default']) ? "'" . Uti::Sql($existing[$column[0]]['Default']) . "'" : "NULL";

if (($oldtype != $newtype) || ($oldnull != $newnull) || ($olddefault != $newdefault))
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], changing: type [" .
$oldtype . "] => [" . $newtype . "] nullability [" . $oldnull . "] => [" . $newnull . "] default [" . $olddefault . "] => [" . $newdefault . "]", true);
$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " change " . $column[0] . " " . $statement);
}

unset($existing[$column[0]]);
}
else if (isset($column[5]) && (Uti::AnyExists(array_keys($existing), $column[5]) !== false))
{
$oldcolumn = Uti::AnyExists(array_keys($existing), $column[5]);

$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], renaming: name [" . $oldcolumn . "] => [" . $column[0] . "] " .
" type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);

$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " change " . $oldcolumn . " " . $statement);

unset($existing[$oldcolumn]);
}
else
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], adding: name [" . $column[0] . "] " .
" type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);

$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " add " . $statement . " " . $lastcolumn);
}

$lastcolumn = 'AFTER ' . $column[0];
}

if ($allowdropcolumn)
{
foreach ($existing as $e)
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $e['Field'] . "], dropping", true);

$this->Query("alter table " . $tablename . " drop " . $e['Field']);
}
}

sort($newkeys);
$newkeys = implode(',',$newkeys);

if ($oldkeys != $newkeys)
{
$this->SaveToLog("Altering table [" . $tablename . "], changing keys [" . $oldkeys . "] => [" . $newkeys . "]", true);

if ($oldkeys !== '')
$this->Query("alter table " . $tablename . " drop primary key");
if ($newkeys !== '')
$this->Query("alter table " . $tablename . " add primary key (" . $newkeys . ")");
}
}
}

Следующие внешние функции требуют объяснения:

ColumnCreationStatement обеспечивает изменение / создание фрагмента таблицы:

private function ColumnCreationStatement(array $columninfo, $includekey = true)
{
$r = '';

if ((count($columninfo) > 0) && (preg_match('/^[a-zA-Z0-9_\-]+$/', $columninfo[0])))
{
$r .= $columninfo[0];
if ((count($columninfo) > 1) && (preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $columninfo[1])))
$r .= ' ' . $columninfo[1];
else
$r .= ' int';
if ((count($columninfo) > 2) && is_bool($columninfo[2]))
$r .= ($columninfo[2] === NOT_NULL ? ' not null' : ' null');
if ((count($columninfo) > 3) && is_string($columninfo[3]) && ($columninfo[3] !== '') && ($columninfo[1] !== 'text'))
$r .= " default " . $columninfo[3];
if ((count($columninfo) > 4) && is_bool($columninfo[4]) && $includekey)
$r .= ($columninfo[4] === true ? ', primary key(' . $columninfo[0] . ')' : '');
}

return $r;
}

TableExists просто проверяет, доступна ли таблица в базе данных (используя show tables like).

запрос выполняет оператор MySql (и yes: не возвращает результата;])

Выбрать а также AllRows являются ярлыками для возврата строк в виде коллекции хеш-таблиц.

SaveToLog это — я думаю — очевидно. 🙂

А также Ути :: AnyExists выглядит так:

public static function AnyExists($haystack, $needles, $separator = ';')
{
if (!is_array($needles))
$needles = explode($separator, $needles);

foreach ($needles as $needle)
{
if (array_search($needle, $haystack) !== false)
return $needle;
}

return false;
}

Я надеюсь, что все это помогает. В случае каких-либо вопросов, пожалуйста, не стесняйтесь задавать в комментариях. 🙂