数据库安装包和升级包脚本工具RedGate使用介绍

冬天里的一缕阳光 發表於 2023-9-20 00:00:00

数据库安装包和升级包脚本工具RedGate使用介绍

这篇日志记录一下我在公司所学习到的数据库安装包的设计。正好这些内容也是我最近工作遇到的一些问题，在此记录并分享一下。

　　在产品的开发和版本更新过程中，数据库的结构难免会一直发生变化。为了尽量减少升级时的工作量，设计一个好的数据库升级方式就显得很重要。在设计数据库安装包时，既要考虑到全新安装时如何生成默认数据，也要考虑从老版本升级时旧的数据如何迁移如有必要）。

基本上，安装包可以分成三个部分：pre-script，数据库安装或升级和post-script。

一、数据库安装或升级

　　首先，我们使用到的是red gate工具。这个工具会自动比较现有数据库和目标数据库在结构上的差异，并自动生成一个脚本进行升级（实际上是执行一连串的sql语句）。这是个很好的工具，推荐使用（好像要收钱），可以减少很多的工作量。

　　如果red gate发现目标表在旧版本的数据库不存在，它会自动创建这个表并设置好主键、外键和其他约束。这个没什么要说的。

　　如果目标表已经存在，那么就会对原有的表进行更新，在此要特别注意要更改的表结构如何变化。举个例子：

　　我们原来有一张userparameter表，结构如下： <img title="数据库安装包和升级包脚本工具RedGate使用介绍" alt="数据库安装包和升级包脚本工具RedGate使用介绍" src="https://zhuji.jb51.net/uploads/img/202305/625b87a3b4477537507886aed838a25f.jpg">

　　现在，我们希望增加一个parametertype字段，与userid字段构成联合主键：　

<img title="数据库安装包和升级包脚本工具RedGate使用介绍" alt="数据库安装包和升级包脚本工具RedGate使用介绍" src="https://zhuji.jb51.net/uploads/img/202305/014603751dd150130aa9eb8f1d0ad8f9.jpg"> 
此时，如果旧版本的数据库有数据，在升级过程中添加新字段后由于parametertype为空，会导致表的结构修改失败，这样安装包就会出错。

　　解决方法是为这个字段加一个默认值。一般做法是在数据库项目的schema objects – tables – contraints下加一个default constraint的约束：
<div class="codetitle">
复制代码 代码如下:</div>
<div class="codebody" id="code4290">
 
alter table . 
 add constraint 
 default n'su' 
 for </div>



二、pre-script和post-script

　一般来说，大部分数据表的结构变化都可以又redgate自动完成，我们要做的只是注意设置好默认值即可。但还有一些其他情况需要自行书写脚本来完成，这里举几个例子。

 
1.默认数据 
　　默认数据是在数据库创建完后加上的。我们可以在post-script中加一个名为defaultdata.sql的脚本，范例如下：
<div class="codetitle">
复制代码 代码如下:</div>
<div class="codebody" id="code96004">

 
set nocount on 
set xact_abort on 
begin transaction 
-- new default for flooralertorder 
if not exists (select 1 from tms. where = 1 and = 7) 
 insert into . (, , ) values (7, 10, 1)



-- tms.user

if not exists (select 1 from . where xref = 'host') 
 insert into . 
 ( 
 , 
 , 
 , 
 , 
 , 
 , 
 , 
 , 
 ,) 
 values 
 (1 
 ,'host' 
 ,'host' 
 ,'host' 
 ,'host' 
 ,getutcdate() 
 ,0 
 ,0 
 ,'host' 
 ,'host') 
commit transaction 
go
</div>



这个脚本唯一要考虑的就是数据库不一定是空的，可能是升级来的，所以就需要判断一下原来有没有数据。另外在写这些脚本时最好放在事务中，安装失败时可以把未提交的数据撤销掉，这样用户在排查了问题之后就可以直接重新再安装一次。

2. 某个字段发生变化

　　比如我们有一张rating表，里面有一个terminalid字段，原来是varchar类型，记录的是机器名。现在我们的新版本把这个字段的类型改成int类型，并加一个关联到terminal表的外键约束。针对这种情况，就需要我们自己写一个脚本了。

　　首先肯定不能放在post-script里。在安装数据库的过程中，安装程序会尝试把字段改成int类型并加上外键约束，如果数据库里本身有数据，会导致转换成int失败或者外键约束不成立。

　　为此，我们可以在pre-script里面，把这些数据首先在terminal表中查出来并更新：
<div class="codetitle">
复制代码 代码如下:</div>
<div class="codebody" id="code73978">

 
begin transaction 
begin try 
 
if exists(select 1 from information_schema.columns where table_name = 'rating' and column_name = 'terminalid' and data_type = 'varchar') 
begin 
 
 declare @defaultterminalid nvarchar(64) = (select top 1 terminalid from tms.terminal order by terminalid asc) 
 
 update r 
 set r.terminalid = isnull(t.terminalid, @defaultterminalid) 
 from tms.rating r 
 left join tms.terminal t on r.terminalid = t.name 
 
end 
 commit transaction 
end try 
 
begin catch 
 if @@trancount > 0 rollback 
end catch 
 
go




</div>



 
最开始，我们还是要考虑到多种情况：如果是从老版本升级，那么terminalid字段的类型就是varchar，此时需要进行转换。如果不是（比如全新安装；或是上一个版本已经转换成int了，下一个版本时这个pre-script还是会执行，所以也要考虑这种情况），就进行转换。

　　脚本中，直接把查询到的terminal表的terminalid更新到rating表中，找不到的用默认值代替（int可以转换成varchar，如果宽度足够的话；此外，该列也可以为null值）。这样，执行完这个pre-script后就已经是目标值了，剩下的列的类型转换和外键约束就交给red gate即可。

　　update语句也可以join其他表，这一点很有意思，大家可以学习一下这条语句。

 　3. 某张表被删除了

　　如果有一张表不需要了，那么red gate会直接把它删掉。但如果这些数据还需要（比如存到别的系统中了），就要用pre-script把这些数据存到别的地方去，否则安装完后在post-script执行前表和数据就都没有了。

　　比如我们有一张usercard表，新版本中这些数据是由另一个系统负责，为此我们需要把这些数据转移到另一个系统中去。

　　可以指定red gate升级的schema类型，比如我们这只管tms下的所有表，对于其他schema下的表直接忽略。利用这一点，可以在pre-script中将这些数据移到dbo下：
<div class="codetitle">
复制代码 代码如下:</div>
<div class="codebody" id="code13499">
 
-- backup usercard data, so that we could transfer them to sbdb when installing tms 
 
begin transaction 
begin try 
 
if exists(select * from information_schema.tables where table_name = 'tempusercard' and table_schema = 'dbo') 
 drop table . 
 
create table . (usercardid bigint not null, userid bigint not null, cardinfo nvarchar(256) not null) 
 
if exists(select * from information_schema.tables where table_name = 'usercard' and table_schema = 'tms') 
begin 
 
 insert into . 
 select usercardid, userid, cardinfo from . 
 
end 
 
commit transaction 
end try 
 
begin catch 
 if @@trancount > 0 rollback 
end catch 
 
go</div>



　这样，在数据库安装完后，数据就在dbo.tempusercard表中。这时在其他组件的安装程序、或者post-script、或者其他系统中就可以把这些表转移过去。

　　使用这种设计应该能应对大多数情况，当然我们在设计数据库的结构时就应该尽量考虑周全，以免频繁修改数据表的结构造成pre-script和post-script非常多且乱。在确认某些script用不到的情况下，我们也可以把它删除掉。

頁: [1]

琼殿技术论坛's Archiver

数据库安装包和升级包脚本工具RedGate使用介绍