在软件安装程序中,只要给定一个处于未知状态的运行数据库(例如数据结构版本x.5)和两个完整的DB(My)SQL create脚本(比如版本x.1到x.9),我就需要自动创建一个(我的)SQL ALTER脚本。
首先,我需要找到当前运行的版本(或者,如果可能的话,最接近的版本,可能有些安装有一些早期的更新错误,但这个功能是次要的)。然后,我想创建一个ALTER脚本来修复运行版本可能出现的错误。
之后,我想自动创建一个最新版本(x.9)的ALTER脚本,并应用此脚本。再次比较两个版本并重复,直到版本是最新的。
我不能使用GUI应用程序,因为它必须在安装程序中盲运行。目标平台将是Windows XP/7。在很长一段时间内,分期付款的数量将低于300(高度专业化的行业软件的更新软件)。所以我的问题是:
有什么好的(我的)SQL比较/差异/脚本生成库可以与C++/NSIS/一些其他安装程序框架一起使用吗?
感谢您的支持!
我一直在思考同一个话题,但还没有找到一个像样的方法。我会分享我所做的,希望它会有所帮助。
我目前的方法是应用一个SQL查询列表,该列表的设计方式适合于数据库的任何以前的模式版本。如果命令已经被应用,那么它将失败(例如添加字段或添加索引)。
这种方法限制了更改数据库模式的方式,也容易出错-例如,如果错误地将ENUM(a,b)字段扩展到ENUM(a,b,c),然后扩展到ENUM(a,b,c,d),则如果再次运行脚本,则值为d
的现有记录将损坏。如果只有一个针对最新格式的查询,则很容易解决此问题。
稍后我还添加了模式版本控制,目前使用一种简单但易于管理的更新文件格式——每行一个查询,以;
结尾,并添加了分隔模式版本的额外行:
-- version 105
有了这个升级,代码可以在一个处理所有版本转换的函数中得到极大的简化和统一。函数只需要处理--version <current version>
行之后的查询。在到达-- version
行之后,函数更新数据库中的模式版本。
此外,这种格式允许使用mysql-f mydb<myfile命令。在这种情况下,版本行将被忽略为注释,所有更改的所有命令都将在当前架构上尝试-这可以用来修复错误(我认为错误指的是比预期更早的架构)。更新存储过程的代码也有一个类似的技巧:
drop procedure if exists procname;
delimiter //
create procedure procname ...
//
delimiter ;
在你的问题中,你会问DB模式diff/patch——这只能在添加新字段/索引等的情况下进行概括。但不能自动处理重命名的字段或删除的字段。对于自动化过程来说,不可能通过查看现有模式和新模式来知道table1
中的字段a
应该重命名为b
,并保留现有数据(我认为必须保持现有数据不变)。
总之,在一般情况下,没有自动生成DB模式更新脚本的方法。
解决这个问题有两种方法。
-
更改脚本的目的是在不关心数据的情况下影响数据库的模式。
-
更改脚本是为了在保留数据的同时影响模式。
在第一种方法中,这可以通过删除当前数据库并生成新数据库来轻松完成。但我确信这不是你想要的,数据是你等式的重要组成部分。
在第二种方法中,首先,您需要知道,无论要处理什么DBMS,都无法做到这一点,因为SQL并不像听起来那么标准。考虑到一些特定的DBMS,解决这个问题的一般方法可能是在DBMS中创建最新版本的模式,并将其与当前版本进行比较。以下是您可能发现对MySQL有用的工具列表。
在这种方法中可以做的事情:
-
检查表是否已删除。
-
检查表是否为新表。
-
检查字段是否已删除。
-
检查字段是否为新字段。
-
检查表的属性是否已更改。
-
检查字段的属性是否已更改。
在这种方法中不能做的事情:
-
检查表是否已重命名。
-
检查字段是否已重命名。
换句话说,重命名的实体将导致DROP
语句和CREATE
语句,这将导致数据丢失。这是这种方法的一个逻辑问题,不可能克服它。查看表或字段是否被重命名的唯一方法是查看alter命令列表并寻找合适的命令(如果您有alter语句列表,而不仅仅是最终模式)。实现这一点本身就很麻烦。
这种方法还有另一个重要问题;由于我们走的是最接近目标模式的路径,因此可能会错过这个过程中的一些重要步骤。也就是说,考虑一下您可能已经执行的脚本,这些脚本会影响数据库的数据,但不会影响数据库的模式。这样的语句不能使用任何diff工具提取,因为你的数据没有引用(除非你真的有,我认为这不是你的情况)。在这种情况下,您唯一的选择是按照应该应用的顺序逐个应用脚本列表。只有当你有一个版本控制机制,或者一个人应该通过分析得出这个列表时,才有可能拥有这样的列表。在这种情况下,我几乎想不出有什么工具可以帮助你(以防你的数据库没有版本)。至少我一个都不认识!
听起来这些脚本是静态的。你能包括所有的脚本(版本x.1到x.2,以及x.2到x.3等)并运行用户需要的特定脚本吗?
我在应用程序中所做的就是在数据库中保留数据库版本值。
我的应用程序具有必需的数据库版本。
这是我的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语句(是:不返回结果;])
Select和AllRows是将行作为哈希表集合返回的快捷方式。
SaveToLog是-我想-显而易见的。:-)
Uti::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;
}
我希望这一切都有帮助。如果有任何问题,请随时在评论中提问。:-)