cs_procedures.sql

community server 源码

--## Schema Patch ##
/*****************************************  This has been moved to an addon ********************************************

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrlFeeds] ADD [SiteUrl] [nvarchar] (512) NULL'

exec sp_ExecuteSQL N'UPDATE [dbo].[cs_RssCtrlFeeds] Set SiteUrl = '''''

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrlFeeds] ALTER COLUMN [SiteUrl] [nvarchar] (512) NOT NULL'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrl] ADD [SettingsID] [int] NULL'

exec sp_ExecuteSQL N'UPDATE [dbo].[cs_RssCtrl] Set [SettingsID] = (select min(SettingsID) from cs_SiteSettings)'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrl] ALTER COLUMN [SettingsID] [int] NOT NULL'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrlFeeds] ADD [IntervalMinutes] [int] NULL'

exec sp_ExecuteSQL N'UPDATE [dbo].[cs_RssCtrlFeeds] Set [IntervalMinutes] = 5'

exec sp_ExecuteSQL N'ALTER TABLE [dbo].[cs_RssCtrlFeeds] ALTER COLUMN [IntervalMinutes] [int] NOT NULL'

*****************************************  This has been moved to an addon ********************************************/
--## 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.25
* File Date: 8/8/2006 10:57:09 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.25'

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

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  SpamScore [int] Not Null Constraint cs_Posts_DefaultSpamScore default(0)'

exec sp_ExecuteSQL N'Alter Table cs_Posts Add  PostStatus [int] Not Null Constraint cs_Posts_DefaultPostStatus 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.26
* File Date: 8/8/2006 10:57:09 AM
***********************************************/
Print 'Creating...cs_Schema_Patch_2.1.26'

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

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'update cs_Posts set PostMedia = 2 where SectionID in (select SectionID from cs_Sections where ApplicationType = 1) and ApplicationPostType in (1, 2) and PropertyNames like ''%VideoUrl%'''

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

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

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


UPDATE	PP
SET		PP.AllowMask = CONVERT(bigint, PP.AllowMask) | CONVERT(bigint, 0x0000000000000100)
FROM	cs_ProductPermissions PP INNER JOIN aspnet_Roles R ON PP.RoleID = R.RoleId
WHERE	ApplicationType = 0 AND R.LoweredRoleName IN ('moderator', 'forumsadministrator', 'systemadministrator')


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


/***********************************************
* View: cs_vw_EveryOne_Role
* File Date: 8/8/2006 10:56:53 AM
***********************************************/
Print 'Creating...cs_vw_EveryOne_Role'

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_EveryOne_Role]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_EveryOne_Role]
GO

CREATE View [dbo].cs_vw_EveryOne_Role
as

SELECT jA.LoweredApplicationName, jA.[ApplicationId], [RoleId], [RoleName], [LoweredRoleName], aspnet_Roles.[Description] 

FROM [aspnet_Roles]
JOIN aspnet_Applications jA on aspnet_Roles.ApplicationId = jA.ApplicationId

Where LoweredRoleName = 'everyone'












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant select on [dbo].[cs_vw_EveryOne_Role] to public
/***********************************************
* View: cs_vw_HasReadForum
* File Date: 8/8/2006 10:56:53 AM
***********************************************/
Print 'Creating...cs_vw_HasReadForum'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_HasReadForum]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_HasReadForum]
GO

Create View [dbo].cs_vw_HasReadForum

as
Select UserID, SectionID, MarkReadAfter, null as ThreadID, LastActivity FROM cs_SectionsRead 
Union
Select UserID, SectionID, null, ThreadID, null FROM cs_ThreadsRead


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant select on [dbo].[cs_vw_HasReadForum] to public
/***********************************************
* View: cs_vw_PostsWithAttachmentDetails
* File Date: 8/8/2006 10:56:53 AM
***********************************************/
Print 'Creating...cs_vw_PostsWithAttachmentDetails'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_PostsWithAttachmentDetails]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_PostsWithAttachmentDetails]
GO

Create View [dbo].cs_vw_PostsWithAttachmentDetails
as

SELECT 
	p.[PostID], p.[ThreadID], p.[ParentID], p.[PostAuthor], p.[UserID], p.[SectionID], 
	p.[PostLevel], p.[SortOrder], p.[Subject], p.[PostDate], p.[IsApproved], p.[IsLocked], 
	p.[TotalViews], p.[Body], p.[FormattedBody], p.[IPAddress], p.[PostType], p.[PostMedia], p.[EmoticonID], 
	p.[PropertyNames] as PostPropertyNames, p.[PropertyValues] as PostPropertyValues, p.[SettingsID], p.[AggViews], p.[PostConfiguration], 
	p.[isIndexed], p.[PostName], p.[UserTime], p.[ApplicationPostType], p.[Points], p.[RatingSum], 
	p.[TotalRatings], pa.[FileName] as AttachmentFilename, pa.[FileName], pa.[ContentType], pa.[ContentSize], pa.[Height], pa.[Width], pa.[IsRemote], pa.[FriendlyFileName], pa.Created,
	p.PostStatus, p.SpamScore

FROM cs_Posts p (nolock)
LEFT JOIN cs_PostAttachments pa (nolock) on p.PostID = pa.PostID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--Select * FROM cs_vw_PostsWithAttachmentDetails

grant select on [dbo].[cs_vw_PostsWithAttachmentDetails] to public



/***********************************************
* View: cs_vw_UsersInRoles
* File Date: 8/8/2006 10:56:53 AM
***********************************************/
Print 'Creating...cs_vw_UsersInRoles'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_UsersInRoles]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_UsersInRoles]
GO

CREATE VIEW [dbo].cs_vw_UsersInRoles
AS

SELECT 
	cu.UserID, ur.RoleId
FROM
	aspnet_UsersInRoles ur (nolock)
	INNER JOIN aspnet_Users au (nolock) on au.UserId = ur.UserId
	INNER JOIN cs_Users cu (nolock) on au.UserId = cu.MembershipID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant select on [dbo].[cs_vw_UsersInRoles] to public



/***********************************************
* View: cs_vw_Users_FullUser
* File Date: 8/8/2006 10:56:53 AM
***********************************************/
Print 'Creating...cs_vw_Users_FullUser'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_vw_Users_FullUser]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[cs_vw_Users_FullUser]
GO

Create View [dbo].cs_vw_Users_FullUser
as

Select 
	au.UserName, au.IsAnonymous, au.UserId, au.La