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