cs_procedures.sql

community server 源码

/***********************************************
* Generated at 8/8/2006 4:31:48 PM
***********************************************/


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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Update Apple release databases to Johnny... Use the 01 patch as a template this
--one has special prerequisite handling

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

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

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

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

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

--All we are doing is updating the schema revision

--## 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.01
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.01'

-- Add index date field to post table to support re-indexing posts after a
-- specified period of time

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

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_Posts] ADD [IndexDate] [smalldatetime]' 

--note the following line needs to be in sp_ExecuteSQL because the referenced column does not exist when SQL initally
--evaluates the SQL patch and will cause an error IndexDate does not exist...

--note - due to the potential that this update will take a very long time so we will allow the column to be null
--and account for it in the corrisponding job
--exec sp_ExecuteSQL N'UPDATE [dbo].[cs_Posts] SET [IndexDate] = [PostDate] WHERE IsIndexed = 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.02
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.02'

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

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_Sections ADD DefaultLanguage nvarchar(32) 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.03
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.03'

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

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 ##
	
CREATE TABLE [dbo].[cs_PageViews](
	[URL] [nvarchar](512) NOT NULL,
	[VisitCount] [int] NOT NULL,
	[DateTimeStamp] [datetime] NOT NULL
) ON [PRIMARY]

--## 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.04
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.04'

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

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_statistics_Site ADD ViewCount int NOT NULL DEFAULT 0'
exec sp_ExecuteSQL N'ALTER TABLE dbo.cs_statistics_Site ADD ReplyCount int NOT NULL DEFAULT 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 

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

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

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 sql patch updates are no longer required, the API has been updated to accept nulls
--The patch takes too long on large databases
	
--update cs_Posts Set UserTime = PostDate where UserTime is null
--ALTER TABLE cs_posts ALTER COLUMN UserTime DateTime 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.06
* File Date: 8/8/2006 10:57:08 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.06'

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

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 not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[csm_MailingLists]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[csm_MailingLists] (
		[SectionID] [int] NOT NULL ,
		[EmailAddress] [nvarchar] (100) NOT NULL ,
		[IsMailingList] [bit] NOT NULL ,
		[SettingsID] [int] NOT NULL 
	) ON [PRIMARY]'

	exec sp_ExecuteSQL N'ALTER TABLE [dbo].[csm_MailingLists] WITH NOCHECK ADD 
		CONSTRAINT [PK_le_MailingList] PRIMARY KEY  CLUSTERED 
		(
			[SectionID],
			[SettingsID]
		)  ON [PRIMARY]'
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[csm_EmailIds]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
	exec sp_ExecuteSQL N'CREATE TABLE [dbo].[csm_EmailIds] (
		[EmailID] [bigint] NOT NULL ,
		[UserID] [int] NOT NULL ,
		[SectionID] [int] NOT NULL 
	) ON [PRIMARY]'

	exec sp_ExecuteSQL N'ALTER TABLE [dbo].[csm_EmailIds] WITH NOCHECK ADD 
		CONSTRAINT [PK_csm_EmailIds] PRIMARY KEY  CLUSTERED 
		(
			[EmailID]
		)  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)