cs_schema_patch_2.1.07.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 = 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