資料庫安裝包和升級包指令碼工具RedGate使用介紹，安裝包redgate

資料庫安裝包和升級包指令碼工具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用不到的情況下，我們也可以把它刪除掉。