📄 bbsmax.sql

📁 这是一个关于论坛的一点小的源代码
💻 SQL
📖 第 1 页 / 共 5 页
字号:
	@EndDate datetime
AS

SET NOCOUNT ON

UPDATE [bbsMax_Announcements] SET
	[AnnouncementType] = @AnnouncementType,
	[Subject] = @Subject,
	[Content] = @Content,
	[StartDate] = @StartDate,
	[EndDate] = @EndDate
WHERE
	[AnnouncementID] = @AnnouncementID
GO
PRINT N'正在创建 [dbo].[bbsMax_UpdateSortOrder]'
GO
CREATE FUNCTION [dbo].[bbsMax_UpdateSortOrder]
(
	@PostType tinyint,--1正常,2置顶,3总置顶,4待审核,5回收站 
	@OldSortOrder bigint
)
RETURNS bigint AS
BEGIN
	RETURN CAST(SUBSTRING(CAST(@OldSortOrder AS varchar(16)),2,15) AS bigint)+@PostType*1000000000000000
END
GO
PRINT N'正在创建 [dbo].[bbsMax_ShieldedUsers]'
GO
CREATE TABLE [dbo].[bbsMax_ShieldedUsers]
(
[ForumID] [int] NULL,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_ShieldedUsers] 的索引 [IX_bbsMax_ShieldedUsers]'
GO
CREATE CLUSTERED INDEX [IX_bbsMax_ShieldedUsers] ON [dbo].[bbsMax_ShieldedUsers] ([ForumID], [UserID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_PostIndexAlias]'
GO
CREATE TABLE [dbo].[bbsMax_PostIndexAlias]
(
[PostIndex] [int] NOT NULL,
[Alias] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS_WS NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[bbsMax_PostIndexAlias] 上创建主键 [PK_bbsMax_PostIndexAlias]'
GO
ALTER TABLE [dbo].[bbsMax_PostIndexAlias] ADD CONSTRAINT [PK_bbsMax_PostIndexAlias] PRIMARY KEY CLUSTERED  ([PostIndex]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllPostIndexAlias]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/2/6>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAllPostIndexAlias]
AS
	SET NOCOUNT ON
	SELECT * FROM [bbsMax_PostIndexAlias] WITH(NOLOCK)
	RETURN
GO
PRINT N'正在创建 [dbo].[bbsMax_RolesInOnline]'
GO
CREATE TABLE [dbo].[bbsMax_RolesInOnline]
(
[RoleID] [int] NOT NULL,
[LogoUrl] [nvarchar] (512) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[SortOrder] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateRolesInOnline]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/4/18>
-- Description:	<>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_CreateRolesInOnline]
	@RoleIDs varchar(8000),
	@LogoUrls varchar(8000)
AS
BEGIN

	SET NOCOUNT ON;

	BEGIN TRANSACTION
	---DELETE FROM [bbsMax_RolesInOnline];
	TRUNCATE TABLE [bbsMax_RolesInOnline];
	
	IF(@@error<>0)
	BEGIN
		ROLLBACK TRANSACTION
		RETURN (-1)
	END
				
	DECLARE @RoleID int,@SortOrder int,@LogoUrl nvarchar(512),@i int,@j int
	
	SET @RoleIDs=@RoleIDs+N','
	SET @LogoUrls=@LogoUrls+N','
	SET @SortOrder=0
	SELECT @i=CHARINDEX(',',@RoleIDs)
	SELECT @j=CHARINDEX(',',@LogoUrls)
	WHILE(@i>1 AND @j>1)
		BEGIN
			SELECT @RoleID=SUBSTRING(@RoleIDs,0, @i)
			SELECT @LogoUrl=SUBSTRING(@LogoUrls,0, @j)
			
			IF NOT EXISTS(SELECT * FROM [bbsMax_RolesInOnline] WHERE RoleID=@RoleID OR SortOrder=@SortOrder)
			BEGIN
				INSERT INTO [bbsMax_RolesInOnline](RoleID,LogoUrl,SortOrder) VALUES(@RoleID,@LogoUrl,@SortOrder)
				IF(@@error<>0)
				BEGIN
					ROLLBACK TRANSACTION
					RETURN (-1)
				END
			END
			SELECT @RoleIDs=SUBSTRING(@RoleIDs,@i+1,LEN(@RoleIDs)-@i)
			SELECT @i=CHARINDEX(',',@RoleIDs)
			
			SELECT @LogoUrls=SUBSTRING(@LogoUrls,@j+1,LEN(@LogoUrls)-@j)
			SELECT @j=CHARINDEX(',',@LogoUrls)
			
			SET @SortOrder=@SortOrder+1
		END
		
	COMMIT TRANSACTION
	RETURN (0);
END
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllRolesInOnlines]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/4/18>
-- Description:	<获取所有>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetAllRolesInOnlines]
AS
	SELECT * FROM [bbsMax_RolesInOnline] ORDER BY SortOrder
GO
PRINT N'正在创建 [dbo].[bbsMax_CreateAnnouncement]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2007/1/11>
-- Description:	<创建一个公告>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_CreateAnnouncement]
	@AnnouncementType tinyint,
	@PostUserID int,
	@PostUserName nvarchar(64),
	@Subject nvarchar(200),
	@Content ntext,
	@StartDate datetime,
	@EndDate datetime
AS

SET NOCOUNT ON

	DECLARE @SortOrder int
	EXECUTE bbsMax_Common_GetSortOrder 'bbsMax_Announcements','',@SortOrder output
	
INSERT INTO [bbsMax_Announcements] (
	[AnnouncementType],
	[PostUserID],
	[PostUserName],
	[Subject],
	[Content],
	[StartDate],
	[EndDate],
	[SortOrder]
) VALUES (
	@AnnouncementType,
	@PostUserID,
	@PostUserName,
	@Subject,
	@Content,
	@StartDate,
	@EndDate,
	@SortOrder
)
GO
PRINT N'正在创建 [dbo].[bbsMax_GetAllShieldedUsers]'
GO
CREATE PROCEDURE [dbo].[bbsMax_GetAllShieldedUsers]
AS
	SET NOCOUNT ON
	SELECT * FROM [bbsMax_ShieldedUsers] WITH(NOLOCK)
GO
PRINT N'正在创建 [dbo].[bbsMax_UpdatePostIcon]'
GO
-- =============================================
-- Author:		SEK
-- Create date: <2006/12/27>
-- Description:	<获取所有信息>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_UpdatePostIcon]
	@IconID int,
	@IconUrl nvarchar(256)
AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM [bbsMax_PostIcons] WITH (NOLOCK) WHERE IconUrl=@IconUrl AND IconID<>@IconID)
		RETURN (3) 
	ELSE
		BEGIN
		UPDATE [bbsMax_PostIcons] SET
		[IconUrl]=@IconUrl
		WHERE IconID=@IconID
		RETURN (0)
		END
GO
PRINT N'正在创建 [dbo].[bbsMax_GetUnapprovedPostThreads]'
GO
-- =============================================
-- Author:		<SEK>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_GetUnapprovedPostThreads]
	@ForumID int,--大于0时按版块获取
	@UserID int,--大于0时按用户获取
	@PageIndex int,
	@PageSize int
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @Condition varchar(8000),@User nvarchar(100)
	
	IF @UserID>0
		SET @User=' P.UserID='+str(@UserID)+' AND '
	ELSE
		SET @User=''

	IF @ForumID=0
		SET @Condition='ThreadID in (SELECT DISTINCT P.ThreadID FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE '+@User+' P.SortOrder >= 5000000000000000 AND T.SortOrder<4000000000000000)'
	ELSE
		SET @Condition='ThreadID in (SELECT DISTINCT P.ThreadID FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE '+@User+' T.ForumID='+str(@ForumID)+' AND P.SortOrder >= 5000000000000000 AND T.SortOrder<4000000000000000)'

	DECLARE @TotalCount INT,@SQLString NVARCHAR(4000)
	
	IF @ForumID=0
		SET @SQLString='SELECT @TotalCount=COUNT(DISTINCT P.ThreadID) FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE '+@User+' P.SortOrder >= 5000000000000000 AND T.SortOrder<4000000000000000'
	ELSE
		SET @SQLString='SELECT @TotalCount=COUNT(DISTINCT P.ThreadID) FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE '+@User+' P.SortOrder >= 5000000000000000 AND T.ForumID='+str(@ForumID)+' AND T.SortOrder<4000000000000000'
	EXECUTE sp_executesql @SQLString,N'@TotalCount int output',@TotalCount output


	DECLARE @ResetOrder bit----------- 1表示读取数据的时候 排序要反过来
	EXECUTE bbsMax_Common_GetRecordsByPageSQLString
						@PageIndex,
						@PageSize,
						N'bbsMax_Threads',
						N'ThreadID',
						@Condition,
						N'[SortOrder]',
						1,
						@TotalCount,
						@ResetOrder OUTPUT,
						@SQLString OUTPUT
	
	EXEC ('DECLARE @ThreadIDTable table(ThreadID int NOT NULL);

		INSERT INTO @ThreadIDTable ' + @SQLString + ';

		SELECT T.*,IsClosed=CASE
WHEN ThreadType=1 THEN ( SELECT [dbo].bbsMax_IsExpired(ExpiresDate,getdate(),0) FROM bbsMax_Polls p1 WITH (NOLOCK) WHERE p1.ThreadID = T.ThreadID )
WHEN ThreadType=2 THEN ( SELECT [dbo].bbsMax_IsExpired(ExpiresDate,getdate(),IsClosed) FROM bbsMax_Questions q1 WITH (NOLOCK) WHERE q1.ThreadID = T.ThreadID )
ELSE 0
END,
		(SELECT COUNT(1) FROM bbsMax_Posts WHERE ThreadID=T.ThreadID  AND SortOrder >= 5000000000000000) as UnApprovedPostsCount FROM bbsMax_Threads T  WHERE T.ThreadID in (SELECT ThreadID FROM @ThreadIDTable) ORDER BY SortOrder DESC')
		
	
	
	SELECT @TotalCount
	
		--IF @PageIndex = 0 BEGIN
			--SELECT @SQLString = 'SELECT TOP ' + CAST(@PageSize as varchar(16)) + ' ThreadID FROM bbsMax_Threads WITH (NOLOCK) WHERE ' + @Condition + ' ORDER BY SortOrder DESC';
		--END
		--ELSE BEGIN
			--DECLARE @GetFromLast BIT
			--DECLARE @TotalPage INT,@ResidualCount INT
			--SET @ResidualCount=@TotalCount%@PageSize
			--IF @ResidualCount=0
				--SET @TotalPage=@TotalCount/@PageSize
			--ELSE
				--SET @TotalPage=@TotalCount/@PageSize+1
			--IF @PageIndex>@TotalPage/2 --从最后页算上来
				--SET @GetFromLast=1
			--ELSE
				--SET @GetFromLast=0
				
			--IF @GetFromLast=1 BEGIN
				--IF @PageIndex=@TotalPage-1 BEGIN
					--IF @ResidualCount=0
						--SET @ResidualCount=@PageSize;
					--SELECT @SQLString = N'SELECT TOP ' + STR(@ResidualCount)
						--+ ' ThreadID FROM bbsMax_Threads WITH (NOLOCK) WHERE ' + @Condition + ' ORDER BY PostID DESC';
				--END 
				--ELSE IF @PageIndex>@TotalPage-1 ----已经超过最大页数
					--SELECT @SQLString = N'SELECT PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE 1=2';
				--ELSE BEGIN
					--SET @PageIndex=@TotalPage-(@PageIndex+1)---
				--END  
			--END
			--IF @SQLString = '' BEGIN
				--DECLARE @TopCount INT,@Order NVARCHAR(100)
				--IF @GetFromLast=1 BEGIN
					--SET @TopCount=@PageSize * (@PageIndex-1)+@ResidualCount
					--SELECT @SQLString = 'SELECT TOP ' + CAST(@PageSize as varchar(16)) + ' PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE ' + @Condition + ' AND PostID < (SELECT MIN(PostID) FROM (SELECT TOP ' + CAST(@TopCount AS varchar(16)) + ' PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE ' + @Condition + ' ORDER BY PostID DESC) AS IDX)  ORDER BY PostID DESC';
				--END
				--ELSE BEGIN
					--SET @TopCount=@PageSize * @PageIndex
					--SELECT @SQLString = 'SELECT TOP ' + CAST(@PageSize as varchar(16)) + ' PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE ' + @Condition + ' AND PostID > (SELECT Max(PostID) FROM (SELECT TOP ' + CAST(@TopCount AS varchar(16)) + ' PostID FROM bbsMax_Posts WITH (NOLOCK) WHERE ' + @Condition + ' ORDER BY PostID) AS IDX)  ORDER BY PostID';
				--END
				
			--END
		--END	
/*
	CREATE TABLE #ThreadIDTable(ThreadID int NOT NULL)
	
	INSERT INTO #ThreadIDTable EXECUTE bbsMax_Common_GetRecordsByPage @PageIndex,@PageSize,N'bbsMax_Threads',N'ThreadID',@Condition,N'[SortOrder]',1,@TotalCount
	
	SELECT T.*,(SELECT COUNT(1) FROM bbsMax_Posts WHERE ThreadID=T.ThreadID  AND ForumID=-2) as UnApprovedPostsCount FROM bbsMax_Threads T  WHERE T.ThreadID in (SELECT ThreadID FROM #ThreadIDTable) ORDER BY SortOrder DESC
	
	DROP TABLE #ThreadIDTable
*/	
	--SELECT @TotalCount
/*	
	IF @ForumID=0
		EXEC('SELECT COUNT(DISTINCT P.ThreadID) FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE '+@User+' P.ForumID=-2 AND T.ForumID>0')
	ELSE
		EXEC('SELECT COUNT(DISTINCT P.ThreadID) FROM [bbsMax_Posts] P INNER JOIN [bbsMax_Threads] T ON P.ThreadID=T.ThreadID WHERE P.ForumID=-2 '+@User+' AND T.ForumID='+@ForumID)
*/
END
GO
PRINT N'正在创建 [dbo].[bbsMax_Common_DoSortOrder]'
GO
-- =============================================
-- Author:		<sek>
-- Create date: <2006/12/18>
-- Description:	<排序>
-- =============================================
CREATE PROCEDURE [dbo].[bbsMax_Common_DoSortOrder]
	@SortOrder1 int,
	@SortOrder2 int,--要移动的记录（SortOrder为@SortOrder2的记录排在@SortOrder1的后面，@SortOrder1为-1时排在最前面）
	@GroupColumn varchar(256),--分组字段（例如，"GroupID","DirectoryID"）,没有分组时用''表示
	@GroupID int,--分组ID值
	@TableName varchar(256),--表名
	@Condition nvarchar(400) = N''--其它条件
AS
	SET NOCOUNT ON 
	DECLARE @SQLString nvarchar(4000),@GroupCondition varchar(100),@TempOrder int
	IF(@GroupColumn<>'')
		SET @GroupCondition=' and '+@GroupColumn+'='+STR(@GroupID)
	ELSE
💿 文件大小 3572 K
👤 上传用户 feiguohaihu
📂 所属分类 SCSI/ASPI
🏷️ 相关标签

#论坛 #源代码
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -