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

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

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

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

一、数据库安装或升级

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

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

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

  我们原来有一张UserParameter表,结构如下:

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


此时,如果旧版本的数据库有数据,在升级过程中添加新字段后由于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用不到的情况下,我们也可以把它删除掉。

(0)

相关推荐

  • 5个常用的MySQL数据库管理工具详细介绍

    1.phpMyAdmin phpMyAdmin是最常用的MySQL维护工具,是一个用PHP开发的基于Web方式架构在网站主机上的MySQL管理工具,支持中文,管理数据库非常方便.不足之处在于对大数据库的备份和恢复不方便.  2.MySQLDumper MySQLDumper使用PHP开发的MySQL数据库备份恢复程序,解决了使用PHP进行大数据库备份和恢复的问题,数百兆的数据库都可以方便的备份恢复,不用担心网速太慢导致中间中断的问题,非常方便易用.这个软件是德国人开发的,还没有中文语言包. 3.

  • 批处理写的 oracle 数据库备份还原工具

    这是针对oracle编写的,可以用在不同的电脑上,因为它会自动读取当前电脑的环境变量,从而取得oracle的安装路径,唯一有一点不足的地方是程序中运行过程中会产生一个中间文本文件,不过这并不碍大事,备份或者还原完了以后将会自动删除产生文本文件,代码如下: 复制代码 代码如下: @echo off&setlocal enabledelayedexpansion color 0a :start for /f "tokens=2 delims==" %%a in ('path') d

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

    这篇日志记录一下我在公司所学习到的数据库安装包的设计.正好这些内容也是我最近工作遇到的一些问题,在此记录并分享一下. 在产品的开发和版本更新过程中,数据库的结构难免会一直发生变化.为了尽量减少升级时的工作量,设计一个好的数据库升级方式就显得很重要.在设计数据库安装包时,既要考虑到全新安装时如何生成默认数据,也要考虑从老版本升级时旧的数据如何迁移如有必要). 基本上,安装包可以分成三个部分:Pre-script,数据库安装或升级和Post-script. 一.数据库安装或升级 首先,我们使用到的是

  • 各种Python库安装包下载地址与安装过程详细介绍(Windows版)

    在用Python开发时(Windows环境),会碰到需要安装某个版本的第三方库,为了以后查找.安装方便,总结如下: windows版的各种Python库安装包下载地址: http://www.lfd.uci.edu/~gohlke/pythonlibs/ 一.打开cmd 二.将cmd当前目录切换到Downloads,命令为cd Downloads 三.在文章开头链接地址下载所需第三方库,以SciPy为例:0.18.1是库版本号:cp27.cp35分别对应Python2.7.Python3.5:w

  • shell 备份数据库、代码上线的脚本

    Shell 脚本 Shell 脚本(shell script),是一种为 shell 编写的脚本程序. 业界所说的 shell 通常都是指 shell 脚本,但读者朋友要知道,shell 和 shell script 是两个不同的概念. 备份MySQL数据库 场景: 一台MySQL服务器,跑着5个数据库,在没有做主从的情况下,需要对这5个库进行备份 需求: 1)每天备份一次,需要备份所有的库 2)把备份数据存放到/data/backup/下 3)备份文件名称格式示例:dbname-2019-11

  • 最新Adobe2022 全家桶永久激活无限使用 附安装包下载(一键安装)

    目录 Adobe 2022(Win10) 软件安装教程 Adobe作为目前市面上最主流的后期编辑工具,他的每次大小更新都会时刻受到关注,后期君也会及时给大家带来软件同步! 时间一晃又是一年过去了,按照惯例 Adobe是每年 10月份中下旬的一次大版本更新如期而至. 根据往年的经验,每一年新版本的Adobe软件都会在10月中下旬进行发布.今年也不例外,就在刚刚,Adobe 2022版本正式与我们相见. Adobe 2022(Win10) Adobe2022全家桶总共有15个安装包,都是平时最常用到

  • 使用VS2019生成C#应用安装包的方法步骤

    首先创建一个窗体应用 选择管理扩展 下载扩展(visual studio Marketplace-工具-安装和部署-installer projects) 文件-添加,选择添加新项目 搜索setup,选择Setup Project. 这里名称可以改成自己需要的名字,位置默认即可. 弹出的面板中选择Application Folder,右键Add-项目输出 弹出窗口右上角选择自己的项目,选择主输出 如需添加图标,要先在项目属性中的图标和清单中添加图标. 然后再次回到弹出面板-Application

  • jar包打包成exe安装包的实现

    目录 1.介绍 2.打包配置 mysql配置 3.项目配置 4.项目打包 1.介绍 jar包启动我们通常是部署在服务器上,用户通过访问服务器来使用系统,但是如果用户想弄成电脑本端单机版,这时候部署的问题就比较麻烦了,不可能每个用户都部署一遍,那么这时候需要弄成exe安装形式,或者脚本安装形式,一键安装部署! 现在我就介绍如何将jar包打包成exe安装包 介绍:比如一个普通的springboot前后端分离项目,含有组件:redis,Nginx , 数据库: Mysql         思路:将用到

  • shell结合expect写的批量scp脚本工具

    在部署一个任务时,其中有一项必须的过程就是将一些文件,如安装包发送到大量的服务器上去.虽然已有宇哥的脚本可用:通过paramiko模块提供的ssh和scp功能编写的python脚本.但我到现在还在对python的恐惧之中(虽然已经在空闲时间努力去学习了),所以使用了shell和expect脚本结合的方式,写了这个批量scp的脚本工具. expect用于自动化地执行linux环境下的命令行交互任务,例如scp.ssh之类需要用户手动输入密码然后确认的任务.有了这个工具,定义在scp过程中可能遇到的

  • MSI之制作免输序列号安装包的方法

    本文用到的工具:Orca,可以在 http://www.crsky.com/soft/8680.html 下载到 挺象orz的 呵呵 以下是orca的简介: Orca 是一款由微软提供的用于 Windows Installer 数据库表编辑器.可用来编辑 Windows Installer 数据库文件 (.msi) 文件,合并模块 (.msm) 文件,补丁 (.msp) 文件,内部一致性计算程序 (.cub) 文件和补丁创建属性 (.pcp) 文件.是修改和本地化 Windows Install

  • 已证实Au_.exe是NSIS安装包的一个组成部分与说明

    已证实Au_.exe是NSIS安装包的一个组成部分,不是病毒其卸载360safe时的确会连接如下地址的80端口 60.195.253.85 抓包如下: 源地址:10.1.5.189  端口:1214        目标地址:60.195.253.85  端口:80        TTL: 64        PacketSize:64 协议:TCP                TCP 标志:ACK|URG 0x02 0x04 0x05 0xAC 0x01 0x03 0x03 0x02 0x01

  • mysql中优化和修复数据库工具mysqlcheck详细介绍

    一.mysqlcheck简介 mysqlcheck客户端可以检查和修复MyISAM表.它还可以优化和分析表. mysqlcheck的功能类似myisamchk,但其工作不同.主要差别是当mysqld服务器在运行时必须使用mysqlcheck,而myisamchk应用于服务器没有运行时.使用mysqlcheck的好处是不需要停止服务器来检查或修复表.使用myisamchk修复失败是不可逆的. Mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE.REPAIR TABLE.

随机推荐