实现数据库版本控制

引言

在开发和支持企业级应用程序时，经常会看到应用程序的不同阶段以不同版本运行。或者，甚至不同的客户端可能在生产环境中运行应用程序的不同版本。本文提供了一个模板，用于在这种情况下强制执行数据库版本控制。

实施数据库版本控制

在多用户环境中，经常会看到为了测试和数据修复问题，数据库中创建或删除了某些对象（例如视图、约束、触发器或索引）。通过使用下面的脚本，数据库中的所有对象都会被删除，除了表之外，然后重新创建。因此，开发人员必须在一个地方添加必要的对象到脚本中，从而确保脚本运行时，数据库中的对象符合其应有的状态。

此脚本不会删除表，因为表中的数据对应用程序至关重要。相反，脚本会检查所有表是否按应用程序的预期存在，并且其中的列定义顺序是否正确。我们还可以创建一个名为 DBVersion 的表，其中包含适当的字段，并在运行脚本之前检查版本号。例如：如果应用程序从发布 1 升级到 2，在运行它之前，检查版本字段中的当前值是否为 1，并在运行脚本之后，将其更新为 2。（这甚至可以扩展到次要版本，例如 1.1.01 等）。这将确保脚本仅在具有适当版本的数据库上运行。

强制数据库版本控制的模板代码

BEGIN Transaction
--
-- Insert Change script here
--
PRINT 'Drop Constraints...'
GO


DECLARE @SQL nvarchar(4000)
DECLARE DropCursor CURSOR FOR
 SELECT 'ALETR TABLE [' + so1.name + '] 
        DROP CONSTRAINT [' + so2.name + ']'
 FROM sysconstraints sc   
 JOIN sysobjects so1 ON sc.ID = so1.ID   
 JOIN sysobjects so2 ON sc.constid = so2.ID   
 WHERE so2.xtype IN ('PK', 'F', 'D', 'UQ', 'C') 
 AND ObjectProperty(so2.ID, 'IsMSShipped') = 0    
 ORDER BY so2.xtype
OPEN DropCursor
FETCH NEXT FROM DropCursor INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
 Execute sp_ExecuteSQL @SQL
 FETCH NEXT FROM DropCursor INTO @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
PRINT 'Drop Indexes & Statistics...'
GO
DECLARE @SQL nvarchar(4000)
DECLARE DropCursor Cursor For
 SELECT CASE
   WHEN IndexProperty(so.id, si.name, 
            'IsStatistics') = 1 THEN 
    'Drop Statistics ['
   ELSE  'Drop Index ['    
  END + so.name + '].[' + si.name + ']'
 FROM sysindexes si
 JOIN sysobjects so On si.id = so.id
 WHERE si.indid Not In (0,255) 
 AND so.xtype = 'u' 
 AND ObjectProperty(so.id, 'IsMSShipped') = 0
OPEN DropCursor
FETCH NEXT FROM DropCursor Into @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
 EXECUTE sp_ExecuteSQL @SQL
 FETCH NEXT FROM DropCursor Into @SQL
END
CLOSE DropCursor
Deallocate DropCursor
GO
Print 'Drop Triggers...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Trigger [' + so1.name + ']'
 From sysobjects so1
 Join sysobjects so2 On so1.parent_obj = so2.id
 Where so1.xtype = 'TR'
 And ObjectProperty(so2.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Calculated Fields...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Alter Table [' + so.name + '] 
        Drop Column [' + sc.name + ']'
 From syscolumns sc 
 Join sysobjects so On sc.id = so.id
 Where sc.IsComputed = 1
 And so.xtype = 'U'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Procedures...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Procedure [' + so.name + ']'
 From sysobjects so
 Where so.xtype = 'P'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Views...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop View [' + so.name + ']'
 From sysobjects so
 Where so.xtype = 'V'
 And ObjectProperty(so.id, 'IsMSShipped') = 0
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Drop Functions...'
GO
Declare @SQL nvarchar(4000)
Declare DropCursor Cursor For
 Select 'Drop Function [' + so.name + ']'
 From sysobjects so
 Where xtype in ('FN', 'IF', 'TF')
Open DropCursor
Fetch Next From DropCursor Into @SQL
While @@FETCH_STATUS = 0
Begin
 Execute sp_ExecuteSQL @SQL
 Fetch Next From DropCursor Into @SQL
End
Close DropCursor
Deallocate DropCursor
GO
Print 'Create Independent Functions...'
GO
Print 'Create Base Tables...'
GO
If Not dbo.TableExists('Table1')
--(Get from sysobjects table)
Create Table Table1 (
 [ID] int NOT NULL ,
) ON [PRIMARY]
GO
If Not dbo.ColumnExists('Table1', 'Column1')
--(Get from sysobjects/syscolumns table)
 Alter Table dbo.Table1 Add
  Column1 int NOT NULL
GO
Print 'Create Base Table Dependent Procedures...'
GO
Print 'Create Application Tables...'
GO
--Checking if the Column of specified data type is defined
If  (Select SysColumns.Type from Sysobjects  
 Join  Syscolumns 
   On Sysobjects.ID = Syscolumns.ID
 Where  Sysobjects.Name = 'Table1' And Syscolumns.Name = 'Column1') = '111'
 Begin
  Alter Table Table1 Drop Column Column1
  Alter Table Table1 Add Column1 varchar(50) Null
 End
GO
Print 'Create Functions That Depend On Tables...'
GO
Print 'Create Calculated Fields...'
GO
Alter Table Table1 Add
 Column1 AS dbo.Function1(param)
 
Print 'Create Constraints...'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
 IX_Table1 UNIQUE NONCLUSTERED 
 (
 Column1,
 Column2
 ) ON [PRIMARY]
GO
Alter Table dbo.Table1 ADD 
 CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED 
 (
  [ID]
 ) ON [PRIMARY]
GO
Print 'Create Foreign Keys...'
GO
Alter Table Table1 ADD 
 CONSTRAINT FK_Table1_Table2 FOREIGN KEY 
 (
  [ID]
 ) REFERENCES Table2 (
  [FKID]
 ) ON DELETE CASCADE  ON UPDATE CASCADE ,
 
Print 'Create Views...'
GO
Print 'Create Functions That Depend On Views...'
GO
Print 'Create Indexes...'
GO
CREATE INDEX [IX_Table1_Column1Column2] 
       ON [dbo].[Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE UNIQUE  INDEX [IX_Column1Column2] 
       ON [Table1]([Column1], [Column2]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Column1] ON 
       dbo.VENDOR_MASTER(Column1) ON [PRIMARY]
GO
Print 'Create Procedures...'
GO
Print 'Create Triggers...'
GO
Print 'Set Database version to current number'
GO
Print 'Data Updates...'
GO
--
-- Data Updates Go here...
--
GO
Commit
GO

上述脚本定义了数据库中对象可以被删除和重新创建的顺序。此模板脚本需要为应用程序的不同版本维护（并进行适当的更改）。即使从一个版本到另一个版本没有太大变化，维护模板脚本的单独版本也是一个好的做法，因为这将提供一种更好的方式来强制执行数据库的正确版本控制。