這篇日志記錄一下我在公司所學習到的數(shù)據(jù)庫安裝包的設計。正好這些內(nèi)容也是我最近工作遇到的一些問題，在此記錄并分享一下。
在產(chǎn)品的開發(fā)和版本更新過程中，數(shù)據(jù)庫的結構難免會一直發(fā)生變化。為了盡量減少升級時的工作量，設計一個好的數(shù)據(jù)庫升級方式就顯得很重要。在設計數(shù)據(jù)庫安裝包時，既要考慮到全新安裝時如何生成默認數(shù)據(jù)，也要考慮從老版本升級時舊的數(shù)據(jù)如何遷移如有必要）。
基本上，安裝包可以分成三個部分：Pre-script，數(shù)據(jù)庫安裝或升級和Post-script。
一、數(shù)據(jù)庫安裝或升級
首先，我們使用到的是Red Gate工具。這個工具會自動比較現(xiàn)有數(shù)據(jù)庫和目標數(shù)據(jù)庫在結構上的差異，并自動生成一個腳本進行升級（實際上是執(zhí)行一連串的SQL語句）。這是個很好的工具，推薦使用（好像要收錢），可以減少很多的工作量。
如果Red Gate發(fā)現(xiàn)目標表在舊版本的數(shù)據(jù)庫不存在，它會自動創(chuàng)建這個表并設置好主鍵、外鍵和其他約束。這個沒什么要說的。
如果目標表已經(jīng)存在，那么就會對原有的表進行更新，在此要特別注意要更改的表結構如何變化。舉個例子：
我們原來有一張UserParameter表，結構如下：
現(xiàn)在，我們希望增加一個ParameterType字段，與UserId字段構成聯(lián)合主鍵：
此時，如果舊版本的數(shù)據(jù)庫有數(shù)據(jù)，在升級過程中添加新字段后由于ParameterType為空，會導致表的結構修改失敗，這樣安裝包就會出錯。
解決方法是為這個字段加一個默認值。一般做法是在數(shù)據(jù)庫項目的Schema Objects – Tables – Contraints下加一個Default Constraint的約束：
復制代碼 代碼如下:
ALTER TABLE [TMS].[UserParameters]
ADD CONSTRAINT [DF_UserParameters_Type]
DEFAULT N'SU'
FOR [ParameterType]
二、Pre-script和Post-script
一般來說，大部分數(shù)據(jù)表的結構變化都可以又RedGate自動完成，我們要做的只是注意設置好默認值即可。但還有一些其他情況需要自行書寫腳本來完成，這里舉幾個例子。
1.默認數(shù)據(jù)
默認數(shù)據(jù)是在數(shù)據(jù)庫創(chuàng)建完后加上的。我們可以在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
這個腳本唯一要考慮的就是數(shù)據(jù)庫不一定是空的，可能是升級來的，所以就需要判斷一下原來有沒有數(shù)據(jù)。另外在寫這些腳本時最好放在事務中，安裝失敗時可以把未提交的數(shù)據(jù)撤銷掉，這樣用戶在排查了問題之后就可以直接重新再安裝一次。
2. 某個字段發(fā)生變化
比如我們有一張Rating表，里面有一個TerminalId字段，原來是VARCHAR類型，記錄的是機器名。現(xiàn)在我們的新版本把這個字段的類型改成int類型，并加一個關聯(lián)到Terminal表的外鍵約束。針對這種情況，就需要我們自己寫一個腳本了。
首先肯定不能放在Post-script里。在安裝數(shù)據(jù)庫的過程中，安裝程序會嘗試把字段改成int類型并加上外鍵約束，如果數(shù)據(jù)庫里本身有數(shù)據(jù)，會導致轉換成int失敗或者外鍵約束不成立。
為此，我們可以在Pre-script里面，把這些數(shù)據(jù)首先在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，此時需要進行轉換。如果不是（比如全新安裝；或是上一個版本已經(jīng)轉換成int了，下一個版本時這個Pre-script還是會執(zhí)行，所以也要考慮這種情況），就進行轉換。
腳本中，直接把查詢到的Terminal表的TerminalId更新到Rating表中，找不到的用默認值代替（int可以轉換成varchar，如果寬度足夠的話；此外，該列也可以為NULL值）。這樣，執(zhí)行完這個Pre-script后就已經(jīng)是目標值了，剩下的列的類型轉換和外鍵約束就交給Red Gate即可。
UPDATE語句也可以Join其他表，這一點很有意思，大家可以學習一下這條語句。
3. 某張表被刪除了
如果有一張表不需要了，那么Red Gate會直接把它刪掉。但如果這些數(shù)據(jù)還需要（比如存到別的系統(tǒng)中了），就要用Pre-script把這些數(shù)據(jù)存到別的地方去，否則安裝完后在Post-script執(zhí)行前表和數(shù)據(jù)就都沒有了。
比如我們有一張UserCard表，新版本中這些數(shù)據(jù)是由另一個系統(tǒng)負責，為此我們需要把這些數(shù)據(jù)轉移到另一個系統(tǒng)中去。
可以指定Red Gate升級的Schema類型，比如我們這只管TMS下的所有表，對于其他schema下的表直接忽略。利用這一點，可以在Pre-script中將這些數(shù)據(jù)移到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
這樣，在數(shù)據(jù)庫安裝完后，數(shù)據(jù)就在dbo.TempUserCard表中。這時在其他組件的安裝程序、或者Post-script、或者其他系統(tǒng)中就可以把這些表轉移過去。
使用這種設計應該能應對大多數(shù)情況，當然我們在設計數(shù)據(jù)庫的結構時就應該盡量考慮周全，以免頻繁修改數(shù)據(jù)表的結構造成Pre-script和Post-script非常多且亂。在確認某些script用不到的情況下，我們也可以把它刪除掉。
您可能感興趣的文章:- 5個常用的MySQL數(shù)據(jù)庫管理工具詳細介紹
- 批處理寫的 oracle 數(shù)據(jù)庫備份還原工具
