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

點擊下載SQL Compare試用版

第三版

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

使用我們剛剛保存的v2.1.7構(gòu)建腳本，我們type從titles表中刪除該列并創(chuàng)建兩個新表。其中一個是稱為的標(biāo)簽列表，TagName另一個是稱為的標(biāo)簽列表，TagTitle用于將一個或多個標(biāo)簽與標(biāo)題相關(guān)聯(lián)，但是每個標(biāo)題只有一個主標(biāo)簽。同樣，我使用構(gòu)建腳本來執(zhí)行此操作，因為更改此表并創(chuàng)建另外兩個表會產(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）運行完整的構(gòu)建腳本時，您會看到構(gòu)建錯誤，因為有幾個依賴的報告存儲過程reptq2和reptq3，它們使用type需要更改的舊列。

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

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

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

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

我們需要打開剛剛保存的遷移腳本并對其進(jìn)行編輯。我們創(chuàng)建一個臨時表，它是title的一個版本。我們使用它來將數(shù)據(jù)添加到兩個新表中。方便地，更改后的存儲過程reptq2可以用作方便的單元測試（有關(guān)完成的腳本，請參見migration_2-1-7_to_2-1-8.sql）。如果這兩個過程給出的結(jié)果與以前的版本相同，那么我們很可能會早點回家。如果要繼續(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)歷了三個版本。第一個是最小的更改，只是為了演示如何進(jìn)行版本檢查。第二個將所有這些varchar數(shù)據(jù)類型更改為更正確的NVARCHAR數(shù)據(jù)類型。最后一個使書籍分類系統(tǒng)更加有用，并更新了存儲過程以使用新系統(tǒng)。最后一個需要數(shù)據(jù)遷移。現(xiàn)在，我們可以進(jìn)行全部測試。我們想知道是否可以遷移從Internet下載的原始版本的數(shù)據(jù)庫，并將其升級到與源目錄中的原始版本相同的版本。

由于原始備份已從互聯(lián)網(wǎng)上消失了很長時間，因此我在Github存儲庫的備份目錄中添加了一個副本。現(xiàn)在，我們以正確的順序?qū)⑺膫€腳本應(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)的一個問題是沒有顯式名稱聲明的約束的問題。這對于臨時表和表變量來說很好，但是對于基表來說不是一個好習(xí)慣，因為它會使任何比較工作變得更加困難。當(dāng)您使用一系列現(xiàn)有的遷移腳本來創(chuàng)建具有完整現(xiàn)有數(shù)據(jù)的版本時，它也會產(chǎn)生連鎖反應(yīng)。如果您是從使用懶惰的約束定義的構(gòu)建腳本創(chuàng)建Pubs的初始副本的，那么這些遷移腳本在應(yīng)用于數(shù)據(jù)庫時有時會失敗，因為這些約束是在元數(shù)據(jù)中內(nèi)部指定的，例如'PK__TagName__3109E9F88C8DE0AD'。該隨機(jī)數(shù)將不會重復(fù)。這就是為什么我提供Pubs作為備份而不是發(fā)布的構(gòu)建腳本的原因。

結(jié)論

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

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

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

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

SQL Prompt：SQL語法提示工具

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

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