cs_procedures.sql

community server 源码

	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 

/***********************************************
* Patch: cs_Schema_Patch_2.1.07
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.07'

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

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

	
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_cs_RollerBlogPost_cs_RollerBlogFeeds]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogPost] DROP CONSTRAINT FK_cs_RollerBlogPost_cs_RollerBlogFeeds'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_cs_RollerBlogFeeds_cs_RollerBlogUrls]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogFeeds] DROP CONSTRAINT FK_cs_RollerBlogFeeds_cs_RollerBlogUrls'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RollerBlogFeeds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec sp_ExecuteSQL N'drop table [dbo].[cs_RollerBlogFeeds]'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RollerBlogPost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec sp_ExecuteSQL N'drop table [dbo].[cs_RollerBlogPost]'


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RollerBlogUrls]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
exec sp_ExecuteSQL N'drop table [dbo].[cs_RollerBlogUrls]'


exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RollerBlogFeeds] (
	[SectionID] [int] NOT NULL ,
	[SettingsID] [int] NOT NULL ,
	[UrlID] [int] NOT NULL ,
	[Enabled] [bit] NOT NULL ,
	[IntervalMinutes] [int] NOT NULL ,
	[Title] [nvarchar] (256) NULL ,
	[PostFullArticle] [bit] NOT NULL ,
	[ExerptSize] [int] NOT NULL ,
	[SubscribeDate] [datetime] NOT NULL ,
	[LastUpdateDate] [datetime] NOT NULL ,
	[LastModifiedDate] [datetime] NOT NULL ,
	[ETag] [nvarchar] (256) NULL ,
	[State] [int] NOT NULL 
) ON [PRIMARY]'


exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RollerBlogPost] (
	[SectionID] [int] NOT NULL ,
	[UrlId] [int] NOT NULL ,
	[PostID] [int] NOT NULL ,
	[PermaLink] [nvarchar] (512) NOT NULL ,
	[CommentUrl] [nvarchar] (512)  NULL ,
	[CommentCount] [int] NULL ,
	[GuidName] [nvarchar] (512) NULL ,
	[GuidIsPermaLink] [bit] NOT NULL 
) ON [PRIMARY]'

exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RollerBlogUrls] (
	[UrlID] [int] IDENTITY (1, 1) NOT NULL ,
	[Url] [nvarchar] (512) NOT NULL 
) ON [PRIMARY]'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogFeeds] ADD 
	CONSTRAINT [DF_cs_RollerBlogFeeds_Enabled] DEFAULT (1) FOR [Enabled],
	CONSTRAINT [DF_cs_RollerBlogFeeds_IntervalMinutes] DEFAULT (5) FOR [IntervalMinutes],
	CONSTRAINT [DF_cs_RollerBlogFeeds_PostFullArticle] DEFAULT (0) FOR [PostFullArticle],
	CONSTRAINT [DF_cs_RollerBlogFeeds_ExerptSize] DEFAULT (256) FOR [ExerptSize],
	CONSTRAINT [DF_cs_RollerBlogFeeds_State] DEFAULT (0) FOR [State],
	CONSTRAINT [PK_cs_RollerBlogFeeds] PRIMARY KEY  CLUSTERED 
	(
		[SectionID],
		[UrlID]
	)  ON [PRIMARY]'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogPost] ADD 
	CONSTRAINT [PK_cs_RollerBlogPost] PRIMARY KEY  CLUSTERED 
	(
		[SectionID],
		[UrlId],
		[PostID]
	)  ON [PRIMARY]'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogUrls] ADD 
	CONSTRAINT [PK_cs_RollerBlogUrls] PRIMARY KEY  CLUSTERED 
	(
		[UrlID]
	)  ON [PRIMARY]'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogFeeds] ADD 
	CONSTRAINT [FK_cs_RollerBlogFeeds_cs_RollerBlogUrls] FOREIGN KEY 
	(
		[UrlID]
	) REFERENCES [dbo].[cs_RollerBlogUrls] (
		[UrlID]
	),
	CONSTRAINT [FK_cs_RollerBlogFeeds_cs_Sections] FOREIGN KEY 
	(
		[SettingsID],
		[SectionID]
	) REFERENCES [dbo].[cs_Sections] (
		[SettingsID],
		[SectionID]
	)'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RollerBlogPost] ADD 
	CONSTRAINT [FK_cs_RollerBlogPost_cs_RollerBlogFeeds] FOREIGN KEY 
	(
		[SectionID],
		[UrlId]
	) REFERENCES [dbo].[cs_RollerBlogFeeds] (
		[SectionID],
		[UrlID]
	)'



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

/***********************************************
* Patch: cs_Schema_Patch_2.1.08
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.08'

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

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

alter table cs_PostMetadata alter column MetaKey nvarchar(100) not null
alter table cs_PostMetadata alter column MetaType nvarchar(100) not null
alter table cs_PostMetadata alter column MetaValue nvarchar(100) not null

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

/***********************************************
* Patch: cs_Schema_Patch_2.1.09
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.09'

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

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 [dbo].[cs_UserAvatar]
Add [Length] [int] NULL,
[ContentType] [nvarchar] (64) NULL,
[Content] [image] NULL,
[DateLastUpdated] [datetime] NOT NULL CONSTRAINT [DF_cs_UserAvatar_DateLastUpdated] DEFAULT (getdate())'

exec sp_ExecuteSQL N'UPDATE [dbo].[cs_UserAvatar] SET Length = i.Length, ContentType = i.ContentType, [Content] = i.[Content], [DateLastUpdated] = i.[DateLastUpdated] 
	FROM [dbo].[cs_UserAvatar] a JOIN [dbo].[cs_Images] i on i.ImageID = a.ImageID'
	
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_cs_UserAvatar_cs_Images]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_UserAvatar] DROP CONSTRAINT [FK_cs_UserAvatar_cs_Images]'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_UserAvatar] DROP COLUMN [ImageID]'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_Images]') and OBJECTPROPERTY(id, N'IsTable') = 1)
exec sp_ExecuteSQL N'DROP TABLE [dbo].[cs_Images]'

--SQL 2000 cant handle altering an image col
--exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_UserAvatar]
--ALTER COLUMN [Content] [image] NOT NULL'

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

/***********************************************
* Patch: cs_Schema_Patch_2.1.10
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.10'

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

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 dbo.cs_RollerBlogFeeds ADD IsBlogAggregated bit NOT NULL DEFAULT 1'
exec sp_ExecuteSQL N'ALTER TABLE dbo.cs_RollerBlogFeeds ADD IsBlogRollAggregated bit NOT NULL DEFAULT 1'
exec sp_ExecuteSQL N'ALTER TABLE dbo.cs_RollerBlogFeeds ADD IsRollerBlogAggregated bit NOT NULL DEFAULT 1'

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

/***********************************************
* Patch: cs_Schema_Patch_2.1.11
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.11'

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))'