程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> 數據庫安裝包和升級包腳本工具RedGate使用介紹

數據庫安裝包和升級包腳本工具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用不到的情況下,我們也可以把它刪除掉。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved