cs_schema_patch_2.1.15.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 = 15;

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 ##
/*****************************************  This has been moved to an addon ********************************************
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RssCtrl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RssCtrl] (
	[CtrlID] [int] NOT NULL ,
	[Title] [varchar] (256) NOT NULL ,
	[Name] [varchar] (256) NOT NULL ,
	[ExerptSize] [int] NOT NULL ,
	[PostCount] [int] NULL 
	) ON [PRIMARY]'

	exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrl] ADD 
	CONSTRAINT [DF_RssCtrl_ExerptSize] DEFAULT (125) FOR [ExerptSize],
	CONSTRAINT [DF_cs_RssCtrl_PostCount] DEFAULT (3) FOR [PostCount]'

	exec sp_ExecuteSQL N'CREATE  UNIQUE  INDEX [IX_cs_RssCtrl] ON [dbo].[cs_RssCtrl]([CtrlID]) ON [PRIMARY]'
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RssCtrlFeeds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RssCtrlFeeds] (
	[CtrlID] [int] NOT NULL ,
	[UrlID] [int] NOT NULL ,
	[Enabled] [bit] NOT NULL ,
	[Title] [nvarchar] (256) 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'ALTER TABLE [dbo].[cs_RssCtrlFeeds] ADD 
	CONSTRAINT [DF_cs_RssCtrlFeeds_Enabled] DEFAULT (1) FOR [Enabled],
	CONSTRAINT [DF_cs_RssCtrlFeeds_State] DEFAULT (0) FOR [State]'

	exec sp_ExecuteSQL N'CREATE  INDEX [IX_cs_RssCtrlFeeds] ON [dbo].[cs_RssCtrlFeeds]([CtrlID]) ON [PRIMARY]'
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RssCtrlPost]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RssCtrlPost] (
	[PostID] [int] IDENTITY (1, 1) NOT NULL ,
	[CtrlID] [int] NOT NULL ,
	[UrlID] [int] NOT NULL ,
	[Author] [nvarchar] (256) NOT NULL ,
	[Subject] [nvarchar] (256) NULL ,
	[Body] [ntext] NULL ,
	[PostDate] [datetime] NOT NULL ,
	[PermaLink] [nvarchar] (312) NOT NULL ,
	[CommentUrl] [nvarchar] (512) NULL ,
	[CommentCount] [int] NULL ,
	[GuidName] [nvarchar] (512) NULL ,
	[GuidIsPermaLink] [bit] NOT NULL 
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'

	exec sp_ExecuteSQL N'CREATE  UNIQUE  INDEX [IX_cs_RssCtrlPost] ON [dbo].[cs_RssCtrlPost]([PostID]) ON [PRIMARY]'

	exec sp_ExecuteSQL N'CREATE  INDEX [IX_cs_RssCtrlPost_1] ON [dbo].[cs_RssCtrlPost]([CtrlID]) ON [PRIMARY]'

	-- Index is too big for SQL2000 
	--exec sp_ExecuteSQL N'CREATE  UNIQUE  INDEX [IX_cs_RssCtrlPost_2] ON [dbo].[cs_RssCtrlPost]([PermaLink]) ON [PRIMARY]'
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_RssCtrlUrls]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[cs_RssCtrlUrls] (
	[UrlID] [int] IDENTITY (1, 1) NOT NULL ,
	[Url] [nvarchar] (512) NOT NULL 
	) ON [PRIMARY]'

	exec sp_ExecuteSQL N'CREATE  UNIQUE  INDEX [IX_cs_RssCtrlUrls] ON [dbo].[cs_RssCtrlUrls]([UrlID]) ON [PRIMARY]'
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