cs_schema_patch_2.1.11.sql

来自「community server 源码」· SQL 代码 · 共 55 行

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 = 11;

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 ##

exec sp_ExecuteSQL N'ALTER TABLE cs_Posts ADD
	PostMedia int NOT NULL CONSTRAINT DF_cs_Posts_PostMedia DEFAULT 0'

-- Video
exec sp_ExecuteSQL N'UPDATE cs_Posts SET PostMedia = 2, PostType = 1 WHERE PostType = 4'

-- Polls (within forums)
exec sp_ExecuteSQL N'UPDATE cs_Posts SET PostMedia = 8 WHERE PostType = 2 OR (ApplicationPostType = 2 and SectionID in (select SectionID from cs_Sections where ApplicationType = 0))' 

-- Image
exec sp_ExecuteSQL N'UPDATE cs_Posts SET PostMedia = 1, PostType = 1 WHERE PostType = 3'

-- Set PostMedia to Image for all Photo Gallery posts
exec sp_ExecuteSQL N'UPDATE cs_Posts SET PostMedia = 1 WHERE PostMedia=0
		AND (SectionID IN (Select s.SectionID From cs_Sections s Where s.ApplicationType = 2))'

-- Reset all Forum PostTypes to Post (1)
exec sp_ExecuteSQL N'UPDATE cs_Posts SET ApplicationPostType = 1 WHERE ApplicationPostType <> 1
		AND (SectionID IN (Select s.SectionID From cs_Sections s Where s.ApplicationType = 0))'

--## 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