cs_schema_patch_2.1.16.sql

community server 源码

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

DECLARE @Major int, @Minor int, @Patch int, @Installed DateTime, @Prereqs int

Set @Major = 2;
Set @Minor = 1;
Set @Patch = 16;

Select @Prereqs = isnull(Count(InstallDate),0)  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch<@Patch

Select @Installed = InstallDate  from cs_SchemaVersion where Major=@Major and Minor=@Minor and Patch=@Patch

If(@Installed is null AND @Prereqs = @Patch)
	BEGIN
--## Schema Patch ##

DECLARE @TABLE_SCHEMA NVARCHAR(50),@TABLE_NAME NVARCHAR(50),@COLUMN_NAME NVARCHAR(50),@CONSTRAINT_NAME NVARCHAR(50)

SET @TABLE_SCHEMA = 'dbo'
SET @TABLE_NAME = 'cs_RollerBlogFeeds'
SET @COLUMN_NAME = 'IsRollerBlogAggregated'

SELECT
	@CONSTRAINT_NAME = OBJECT_NAME(c1.cdefault)
FROM syscolumns AS c1 
		JOIN syscomments AS c2 
			ON c1.cdefault = c2.id 
WHERE 	OBJECT_NAME(c1.id) = @TABLE_NAME AND c1.name = @COLUMN_NAME 

/* the following does the same thing but is SQL 2005 only
select 
	@CONSTRAINT_NAME = d.name
  from sys.default_constraints as d
  join sys.objects as o
    on o.object_id = d.parent_object_id
  join sys.columns as c
    on c.object_id = o.object_id and c.column_id = d.parent_column_id
  join sys.schemas as s
    on s.schema_id = o.schema_id
WHERE s.name = @TABLE_SCHEMA and o.name = @TABLE_NAME and c.name = @COLUMN_NAME
*/

DECLARE @STRSQL NVARCHAR(4000);
if @CONSTRAINT_NAME is not null
BEGIN
    SELECT @STRSQL = N'ALTER TABLE [' + @TABLE_SCHEMA + '].['+ @TABLE_NAME +'] DROP CONSTRAINT ['+@CONSTRAINT_NAME+']'
	exec sp_ExecuteSQL @STRSQL
END
if exists (select * from dbo.sysobjects where id = object_id(N'[' + @TABLE_SCHEMA + '].['+ @TABLE_NAME +']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
    SELECT @STRSQL = N'ALTER TABLE [' + @TABLE_SCHEMA + '].['+ @TABLE_NAME +'] DROP COLUMN ['+@COLUMN_NAME+']'
	exec sp_ExecuteSQL @STRSQL
END
--## END Schema Patch ##
Insert into cs_SchemaVersion(Major, Minor, Patch, InstallDate) values (@Major, @Minor, @Patch, GetDate())

Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was applied successfully '

	END
ELSE IF(@Installed is not null)
	BEGIN
Print 'Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ' was already applied on ' + Convert(varchar(50), @Installed)  
	END 
ELSE
	BEGIN
Print 'The patch could not be applied because your current schema is missing previous updates (Schema Patch v' + Convert(Varchar(2),@Major) + '.' + Convert(Varchar(2),@Minor) + '.' +  Convert(Varchar(3),@Patch) + ')' 
	END