SQL Compare是一款比較和同步SQL Server數(shù)據(jù)庫(kù)結(jié)構(gòu)的工具。現(xiàn)有超過(guò)150,000的數(shù)據(jù)庫(kù)管理員、開發(fā)人員和測(cè)試人員在使用它。當(dāng)測(cè)試本地數(shù)據(jù)庫(kù)，暫存或激活遠(yuǎn)程服務(wù)器的數(shù)據(jù)庫(kù)時(shí)，SQL Compare將分配數(shù)據(jù)庫(kù)的過(guò)程自動(dòng)化。

點(diǎn)擊下載SQL Compare試用版

第三版

這次，我們決定更改出版物，我們不僅僅只涉及一個(gè)主題，還允許應(yīng)用多個(gè)子主題。我們這樣做是為了說(shuō)明遷移步驟，該步驟將需要在遷移腳本中添加一些其他遷移代碼。

使用我們剛剛保存的v2.1.7構(gòu)建腳本，我們type從titles表中刪除該列并創(chuàng)建兩個(gè)新表。其中一個(gè)是稱為的標(biāo)簽列表，TagName另一個(gè)是稱為的標(biāo)簽列表，TagTitle用于將一個(gè)或多個(gè)標(biāo)簽與標(biāo)題相關(guān)聯(lián)，但是每個(gè)標(biāo)題只有一個(gè)主標(biāo)簽。同樣，我使用構(gòu)建腳本來(lái)執(zhí)行此操作，因?yàn)楦拇吮聿?chuàng)建另外兩個(gè)表會(huì)產(chǎn)生影響。

CREATE TABLE [dbo].[titles](
    [title_id] [dbo].[tid] NOT NULL,
    [title] [nvarchar](120) NOT NULL,
    [pub_id] [char](10) NULL,
    [price] [money] NULL,
    [advance] [money] NULL,
    [royalty] [int] NULL,
    [ytd_sales] [int] NULL,
    [notes] [nvarchar](max) NULL,
    [pubdate] [datetime] NOT NULL,
 CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED 
(
    [title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE TagName (TagName_ID INT IDENTITY(1, 1) PRIMARY KEY, Tag VARCHAR(20) NOT NULL UNIQUE);
go
CREATE TABLE TagTitle
  (
  TagTitle_ID INT IDENTITY(1, 1),
  title_id dbo.tid NOT NULL REFERENCES titles (title_id),
  Is_Primary BIT NOT NULL DEFAULT 0,
  TagName_ID INT NOT NULL REFERENCES TagName (TagName_ID),
  CONSTRAINT PK_TagNameTitle PRIMARY KEY CLUSTERED (title_id ASC, TagName_ID) ON [PRIMARY]
  );

當(dāng)您為新版本（2.1.8）運(yùn)行完整的構(gòu)建腳本時(shí)，您會(huì)看到構(gòu)建錯(cuò)誤，因?yàn)橛袔讉€(gè)依賴的報(bào)告存儲(chǔ)過(guò)程reptq2和reptq3，它們使用type需要更改的舊列。

消息207，級(jí)別16，狀態(tài)1，過(guò)程reptq2，第4行[批處理開始行459]
無(wú)效的列名“類型”。
消息207，級(jí)別16，狀態(tài)1，過(guò)程reptq3，第10行[批處理開始行475]
無(wú)效的列名“類型”。

不過(guò)，您將擁有重構(gòu)的表，因此可以為它們?cè)O(shè)計(jì)新的代碼而不會(huì)出現(xiàn)太多問(wèn)題（我不會(huì)在這里顯示它，但是您很快就會(huì)在遷移腳本中看到它）。您還必須修復(fù)DEFAULT舊type列的約束，因此它引用新Tag列。

當(dāng)然，要測(cè)試這個(gè)新版本，我們現(xiàn)在需要用當(dāng)前版本（2.1.7）中的數(shù)據(jù)填充它，但是這次我們需要制定數(shù)據(jù)遷移腳本以將數(shù)據(jù)移到舊type列中到新表中，并填充其他新列。

解決了數(shù)據(jù)遷移的所有問(wèn)題并運(yùn)行了所有測(cè)試后，我們將使用v2.1.8標(biāo)記新的開發(fā)版本，并使用帶有該版本的SQL Compare作為源代碼和源代碼的內(nèi)容目錄作為目標(biāo)，以便更新對(duì)象腳本并保存“ 2.1.7 to 2.1.8”遷移腳本，這時(shí)會(huì)發(fā)出警告。

我們需要打開剛剛保存的遷移腳本并對(duì)其進(jìn)行編輯。我們創(chuàng)建一個(gè)臨時(shí)表，它是title的一個(gè)版本。我們使用它來(lái)將數(shù)據(jù)添加到兩個(gè)新表中。方便地，更改后的存儲(chǔ)過(guò)程reptq2可以用作方便的單元測(cè)試（有關(guān)完成的腳本，請(qǐng)參見migration_2-1-7_to_2-1-8.sql）。如果這兩個(gè)過(guò)程給出的結(jié)果與以前的版本相同，那么我們很可能會(huì)早點(diǎn)回家。如果要繼續(xù)學(xué)習(xí)，則需要使用SQL Compare生成腳本，然后在腳本中添加“插入代碼”注釋標(biāo)記的部分。

/*
Run this script on :
Script created by SQL Compare version 13.4.5.6953 from Red Gate Software Ltd at 12/05/2020 09:35:47
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL Serializable
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
--inserted code
Declare @version varchar(25);
SELECT @version= Coalesce(Json_Value(
  ( SELECT Convert(NVARCHAR(3760), value) 
      FROM sys.extended_properties AS EP
      WHERE major_id = 0 AND minor_id = 0 
        AND name = 'Database_Info'),'$[0].Version'),'that was not recorded');
IF @version <> '2.1.7'
  BEGIN
  RAISERROR ('The Target was at version %s, not the correct version (2.1.7)',16,1,@version)
  SET NOEXEC ON
  END
go
PRINT N'Saving TITLES table to temporary table'
SELECT titles.title_id, titles.title, titles.type, titles.pub_id, titles.price,
  titles.advance, titles.royalty, titles.ytd_sales, titles.notes,
  titles.pubdate
  INTO #titles
  FROM [dbo].[titles];
IF @@ERROR <> 0 SET NOEXEC ON
GO
--end of inserted code
PRINT N'Dropping constraints from [dbo].[titles]'
GO
ALTER TABLE [dbo].[titles] DROP CONSTRAINT [DF__titles__type__07F6335A]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[titles]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[titles] DROP
COLUMN [type]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TagName]'
GO
CREATE TABLE [dbo].[TagName]
(
[TagName_ID] [int] NOT NULL IDENTITY(1, 1),
[Tag] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__TagName__3109E9F88C8DE0AD] on [dbo].[TagName]'
GO
ALTER TABLE [dbo].[TagName] ADD PRIMARY KEY CLUSTERED  ([TagName_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding constraints to [dbo].[TagName]'
GO
ALTER TABLE [dbo].[TagName] ADD UNIQUE NONCLUSTERED  ([Tag])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[TagTitle]'
GO
CREATE TABLE [dbo].[TagTitle]
(
[TagTitle_ID] [int] NOT NULL IDENTITY(1, 1),
[title_id] [dbo].[tid] NOT NULL,
[Is_Primary] [bit] NOT NULL DEFAULT ((0)),
[TagName_ID] [int] NOT NULL
)
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_TagNameTitle] on [dbo].[TagTitle]'
GO
ALTER TABLE [dbo].[TagTitle] ADD CONSTRAINT [PK_TagNameTitle] PRIMARY KEY CLUSTERED  ([title_id], [TagName_ID])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[reptq2]'
GO
ALTER PROCEDURE [dbo].[reptq2] AS
select 
    case when grouping(TN.tag) = 1 then 'ALL' else TN.tag end as type, 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    avg(ytd_sales) as avg_ytd_sales
 FROM titles INNER JOIN tagtitle
ON TagTitle.title_id = titles.title_id
INNER JOIN dbo.TagName AS TN 
ON TN.TagName_ID = TagTitle.TagName_ID
where pub_id is NOT NULL AND is_primary=1
group by pub_id, TN.tag with rollup
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[reptq3]'
GO
ALTER PROCEDURE [dbo].[reptq3] @lolimit money, @hilimit money,
@type char(12)
AS
select 
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id, 
    case when grouping(TN.tag) = 1 then 'ALL' else TN.tag end as type, 
    count(titles.title_id) as cnt
from titles INNER JOIN tagtitle
ON TagTitle.title_id = titles.title_id
INNER JOIN dbo.TagName AS TN 
ON TN.TagName_ID = TagTitle.TagName_ID
where price >@lolimit AND is_primary=1 AND price <@hilimit AND TN.tag = @type OR TN.tag LIKE '%cook%'
group by pub_id, TN.tag with rollup
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Adding foreign keys to [dbo].[TagTitle]'
GO
ALTER TABLE [dbo].[TagTitle] ADD FOREIGN KEY ([TagName_ID]) REFERENCES [dbo].[TagName] ([TagName_ID])
GO
ALTER TABLE [dbo].[TagTitle] ADD FOREIGN KEY ([title_id]) REFERENCES [dbo].[titles] ([title_id])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering extended properties'
GO
BEGIN TRY
    EXEC sp_updateextendedproperty N'Database_Info', N'[{"Name":"Pubs","Version":"2.1.8","Description":"The Pubs (publishing) Database supports a fictitious bookshop.","Modified":"2020-05-06T13:57:56.217","by":"PhilFactor"}]', NULL, NULL, NULL, NULL, NULL, NULL
END TRY
BEGIN CATCH
    DECLARE @msg nvarchar(max);
    DECLARE @severity int;
    DECLARE @state int;
    SELECT @msg = ERROR_MESSAGE(), @severity = ERROR_SEVERITY(), @state = ERROR_STATE();
    RAISERROR(@msg, @severity, @state);
    SET NOEXEC ON
END CATCH
GO
--inserted code
INSERT INTO TagName (Tag) SELECT DISTINCT type FROM #titles;
IF @@ERROR <> 0 SET NOEXEC ON
INSERT INTO TagTitle (title_id,Is_Primary,TagName_ID)
  SELECT title_id, 1, TagName_ID FROM #titles 
    INNER JOIN TagName ON #titles.type = TagName.Tag;
IF @@ERROR <> 0 SET NOEXEC ON
DROP  TABLE #titles
go
--end of inserted code
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

我們已經(jīng)經(jīng)歷了三個(gè)版本。第一個(gè)是最小的更改，只是為了演示如何進(jìn)行版本檢查。第二個(gè)將所有這些varchar數(shù)據(jù)類型更改為更正確的NVARCHAR數(shù)據(jù)類型。最后一個(gè)使書籍分類系統(tǒng)更加有用，并更新了存儲(chǔ)過(guò)程以使用新系統(tǒng)。最后一個(gè)需要數(shù)據(jù)遷移。現(xiàn)在，我們可以進(jìn)行全部測(cè)試。我們想知道是否可以遷移從Internet下載的原始版本的數(shù)據(jù)庫(kù)，并將其升級(jí)到與源目錄中的原始版本相同的版本。

由于原始備份已從互聯(lián)網(wǎng)上消失了很長(zhǎng)時(shí)間，因此我在Github存儲(chǔ)庫(kù)的備份目錄中添加了一個(gè)副本。現(xiàn)在，我們以正確的順序?qū)⑺膫€(gè)腳本應(yīng)用于該腳本...

1. AddInitialVersion.sql –僅將版本添加到原始Pubs
2. migration_2-1-5_to_2-1-6.sql –從2.1.5遷移到2.1.6：僅更新PubInfo表的數(shù)據(jù)類型。
3. migration_2-1-6_to_2-1-7.sql –從2.1.6遷移到2.1.7：將所有varchar類型的數(shù)據(jù)類型更新為NVarchar。
4. migration_2-1-7_to_2-1-8.sql –從2.1.7遷移到2.1.8：拆分“標(biāo)題”表以允許分配多種圖書類型。演示數(shù)據(jù)遷移。

出現(xiàn)的一個(gè)問(wèn)題是沒(méi)有顯式名稱聲明的約束的問(wèn)題。這對(duì)于臨時(shí)表和表變量來(lái)說(shuō)很好，但是對(duì)于基表來(lái)說(shuō)不是一個(gè)好習(xí)慣，因?yàn)樗鼤?huì)使任何比較工作變得更加困難。當(dāng)您使用一系列現(xiàn)有的遷移腳本來(lái)創(chuàng)建具有完整現(xiàn)有數(shù)據(jù)的版本時(shí)，它也會(huì)產(chǎn)生連鎖反應(yīng)。如果您是從使用懶惰的約束定義的構(gòu)建腳本創(chuàng)建Pubs的初始副本的，那么這些遷移腳本在應(yīng)用于數(shù)據(jù)庫(kù)時(shí)有時(shí)會(huì)失敗，因?yàn)檫@些約束是在元數(shù)據(jù)中內(nèi)部指定的，例如'PK__TagName__3109E9F88C8DE0AD'。該隨機(jī)數(shù)將不會(huì)重復(fù)。這就是為什么我提供Pubs作為備份而不是發(fā)布的構(gòu)建腳本的原因。

結(jié)論

在數(shù)據(jù)庫(kù)開發(fā)過(guò)程中，優(yōu)秀的開發(fā)人員應(yīng)自由使用最佳工具來(lái)完成當(dāng)前任務(wù)。您可能需要使用導(dǎo)出新表設(shè)計(jì)的ER圖表工具。在項(xiàng)目的某個(gè)時(shí)刻，您可能想要使用傳統(tǒng)的構(gòu)建腳本，表構(gòu)建器工具，甚至是文本編輯器以及SSMS。如果您可以生成對(duì)象級(jí)腳本和遷移腳本的可交付成果，那么可以。

為了可靠地部署數(shù)據(jù)庫(kù)更改，我們需要在版本控制中同時(shí)使用每個(gè)版本的對(duì)象級(jí)源和在版本之間移動(dòng)的遷移腳本。當(dāng)某個(gè)版本成為發(fā)行候選版本時(shí)，將創(chuàng)建遷移腳本，該腳本將使用先前發(fā)行版中的數(shù)據(jù)庫(kù)。SQL Compare可以提供此腳本的“第一手資料”，只要SQL Compare無(wú)法滿足所有要求，便可以與自定義遷移代碼結(jié)合使用。當(dāng)然，您可以按順序運(yùn)行各個(gè)版本到版本的腳本，但是其中可能要管理的腳本太多，并且您可能會(huì)因無(wú)法在集成測(cè)試中幸存下來(lái)的設(shè)計(jì)思想而來(lái)來(lái)往往。每個(gè)版本一個(gè)遷移腳本更易于管理。

最后，自動(dòng)化很重要。由于在單元測(cè)試，集成測(cè)試，自動(dòng)化構(gòu)建和“辦公室工作”（檢查日志，團(tuán)隊(duì)協(xié)作，文檔，問(wèn)題管理和報(bào)告）的速度和數(shù)量方面的期望越來(lái)越高，因此自動(dòng)化成為實(shí)現(xiàn)更加愉快和富有成效的工作生活。在即將發(fā)表的文章中，我將展示開發(fā)階段的各種任務(wù)，如本文所述，將其插入自動(dòng)化的SQL Change Automation過(guò)程中。

相關(guān)產(chǎn)品推薦：

SQL Prompt：SQL語(yǔ)法提示工具

SQL Toolbelt：Red Gate產(chǎn)品套包

SQL Monitor：SQL Server監(jiān)控工具