服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - 数据库技术 - 数据库安装包和升级包脚本工具RedGate使用介绍

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

2021-10-20 14:54数据库技术网 数据库技术

这篇文章主要介绍了数据库安装包和升级包脚本工具RedGate使用介绍,RedGate是一个SQL脚本生成工具,需要的朋友可以参考下

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

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

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

一、数据库安装或升级

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

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

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

  我们原来有一张userparameter表,结构如下:
数据库安装包和升级包脚本工具RedGate使用介绍

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

数据库安装包和升级包脚本工具RedGate使用介绍
此时,如果旧版本的数据库有数据,在升级过程中添加新字段后由于parametertype为空,会导致表的结构修改失败,这样安装包就会出错。

  解决方法是为这个字段加一个默认值。一般做法是在数据库项目的schema objects – tables – contraints下加一个default constraint的约束:

复制代码 代码如下:

alter table [tms].[userparameters]
   add constraint [df_userparameters_type]
   default n'su'
   for [parametertype]

 

二、pre-script和post-script

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


1.默认数据
  默认数据是在数据库创建完后加上的。我们可以在post-script中加一个名为defaultdata.sql的脚本,范例如下:

复制代码 代码如下:


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

 

-- tms.user

if not exists (select 1 from [tms].[user] where xref = 'host')
    insert into [tms].[user]
           ([active]
           ,[xref]
           ,[lastname]
           ,[firstname]
           ,[username]
           ,[creationtime]
           ,[dealer]
           ,[casinohost]
           ,[domainname]
           ,[cmsusername])
     values
           (1
           ,'host'
           ,'host'
           ,'host'
           ,'host'
           ,getutcdate()
           ,0
           ,0
           ,'host'
           ,'host')
commit transaction
go

 

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

2. 某个字段发生变化

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

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

  为此,我们可以在pre-script里面,把这些数据首先在terminal表中查出来并更新:

复制代码 代码如下:


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

 

 

 


最开始,我们还是要考虑到多种情况:如果是从老版本升级,那么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下:

复制代码 代码如下:

-- 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 [dbo].[tempusercard]
 
create table [dbo].[tempusercard] (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 [dbo].[tempusercard]
        select usercardid, userid, cardinfo from [tms].[usercard]
 
end
 
commit transaction
end try
 
begin catch
    if @@trancount > 0 rollback
end catch
 
go

 

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

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

延伸 · 阅读

精彩推荐