/****************************************************************************/ /* */ /* FileName: bbs.sql */ /* */ /* Description: bbs数据结构 */ /* */ /* Table: */ /* */ /* Procedure: */ /* */ /* Author: bigeagle http://bigeagle.yeah.net */ /* */ /* Date: 2001/1/29 */ /* */ /* History: */ /* */ /****************************************************************************/
/*数据结构*/
/*bbs用户表*/ if exists(select * from sysobjects where id = object_id('BBSUser')) drop table BBSUser go
create table BBSUser ( id int identity primary key , UserName varchar(20) default '' not null , Password varchar(10) default '' not null , Email varchar(100) default '' not null , Homepage varchar(150) default '' not null , Signature varchar(255) default '' not null , SignDate datetime default getdate() not null , Point int default 0 not null )
go
create index ix_bbsuser on bbsuser (id , username , password)
/*bbs表情表*/ if exists(select * from sysobjects where id = object_id('Face')) drop table Face go
create table Face ( id tinyint identity primary key , Face varchar(30) default '' not null ) go
/*bbs表*/ if exists(select * from sysobjects where id = object_id('BBS')) drop table BBS go
create table BBS ( id int identity primary key , RootID int default 0 not null , --根ID FatherID int default 0 not null , --父ID Layer tinyint default 0 not null , --层 OrderNum float(53) default 0 not null , --排序基数 UserID int default 0 not null , --发言人ID ForumID tinyint default 1 not null , --版面ID Subject varchar(255) default '' not null , --主题 Content text default '' not null , --内容 FaceID tinyint default 1 not null , --表情 Hits int default 0 not null , --点击数 IP varchar(20) default '' not null , --发贴IP Time datetime default getdate() not null , --发表时间 Posted bit default 0 not null --是否精华贴子 ) go
create index ix_bbs on bbs(id , rootid ,layer , fatherid , subject,posted) with DROP_EXISTING create index ix_bbs1 on bbs(fatherid , forumid) with DROP_EXISTING create index ix_bbs2 on bbs(forumid , rootid , ordernum) with drop_existing
/*精华区*/ if exists(select * from sysobjects where id = object_id('PostedTopic')) drop table PostedTopic go
create table PostedTopic ( id int identity primary key , UserID int default 0 not null , --发言人ID ForumID tinyint default 1 not null , --版面ID Subject varchar(255) default '' not null , --主题 Content text default '' not null , --内容 FaceID tinyint default 1 not null , --表情 Hits int default 0 not null , --点击数 IP varchar(20) default '' not null , --发贴IP Time datetime default getdate() not null --发表时间 ) go
/*forum版面表*/ if exists(select * from sysobjects where id = object_id('forum')) drop table forum go
create table Forum ( ID tinyint identity primary key , RootID tinyint default 0 not null , --根ID FatherID tinyint default 0 not null , --父ID Layer tinyint default 0 not null , --层 Title varchar(50) default '' not null , --版面名称 Description varchar(255) default '' not null , --版面描述 MasterID int default 1 not null , --版主ID TopicCount int default 0 not null , --贴子总数 Time datetime default getdate() not null , --创建时间 IsOpen bit default 0 not null --是否开放 ) go
insert into forum(rootid , fatherid , layer , title , description , masterid) values(1 , 0 , 0 , "谈天说地" , "在不违犯国家法律的情况下,你可以发表你自己的言论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2 , 0 , 0 , "体育" , "在不违犯国家法律的情况下,你可以对体育发表你自己的评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(1 , 1 , 1 , "笑话站" , "笑话,让你在工作间隙轻松一下。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "体育沙龙" , "体育总和评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "足球" , "足球评论。" , 1) insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "海牛俱乐部" , "海牛球迷的讨论园地。" , 1)
select * from forum
/*论坛通告表*/ if exists(select * from sysobjects where id = object_id('Notify')) drop table Notify go
create table Notify ( ID int identity primary key , TopicID int default 0 not null , Closed bit default 0 not null , ) go select * from notify delete from notify where id=5
/***********以下为存储过程************************************************************/
/*************************************************************************/ /* */ /* procedure : up_GetBBSInfo */ /* */ /* Description: 取得整个论坛的相关信息 */ /* */ /* Parameters: none */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/3 */ /* */ /* History: */ /* */ /*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetBBSInfo')) drop proc up_GetBBSInfo go
create proc up_GetBBSInfo as declare @ForumCount int declare @TopicCount int declare @UserCount int
set nocount on select @ForumCount = count(*) from Forum where layer <> 0 select @TopicCount = count(*) from BBS select @UserCount = count(*) from BBSUser
/*取得论坛本身信息*/ select 'ForumCount' = @ForumCount , 'TopicCount' = @TopicCount , 'UserCount' = @UserCount
go up_getbbsinfo /*************************************************************************/ /* */ /* procedure : up_GetForumInfo */ /* */ /* Description: 取得指定版面的相关信息 */ /* */ /* Parameters: @a_intForumID */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/3 */ /* */ /* History: */ /* */ /*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetForumInfo')) drop proc up_GetForumInfo go
create proc up_GetForumInfo @a_intForumID int as declare @intTopicCount int declare @intRootTopicCount int set nocount on if not exists(select * from Forum where id=@a_intForumID) return 0 select @intTopicCount = count(*) from bbs where forumid = @a_intForumID select @intRootTopicCount = count(*) from bbs where forumID=@a_intForumID and fatherid=0 select * , 'TopicCount'=@intTopicCount , 'RootTopicCount' = @intRootTopicCount from Forum where id = @a_intForumID set nocount off go select id , rootid , title , fatherid from forum /*************************************************************************/ /* */ /* procedure : up_GetPostedForumInfo */ /* */ /* Description: 取得指定版面精华区的相关信息 */ /* */ /* Parameters: @a_intForumID */ /* */ /* Use table: forum , bbs , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/17 */ /* */ /* History: */ /* */ /*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_GetPostedForumInfo')) drop proc up_GetPostedForumInfo go
create proc up_GetPostedForumInfo @a_intForumID int as declare @intTopicCount int declare @intRootTopicCount int set nocount on if not exists(select * from Forum where id=@a_intForumID) return 0 select @intTopicCount = count(*) from bbs where forumid = @a_intForumID and posted=1 select * , 'TopicCount'=@intTopicCount , 'RootTopicCount' = @intTopicCount from Forum where id = @a_intForumID set nocount off go /*************************************************************************/ /* */ /* procedure : up_GetForumList */ /* */ /* Description: 取得版面列表 */ /* */ /* Parameters: None */ /* */ /* Use table: forum , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/10 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_GetForumList')) drop proc up_GetForumList go
create proc up_GetForumList as select a.id , a.rootid , a.fatherid , a.layer , a.title , a.topiccount , a.description , 'UserID'=b.id , b.UserName , b.Email , b.Homepage , b.Signature from forum as a join BBSUser as b on a.Masterid=b.ID order by rootid , layer go select id , title , rootid from forum up_getforumlist
/*************************************************************************/ /* */ /* procedure : up_InsertForum */ /* */ /* Description: 新建版面 */ /* */ /* Parameters: @a_strName : 版面名称 */ /* @a_strDescription: 版面描述 */ /* @a_intFatherID: 分类ID,如果是0说明是大分类 */ /* */ /* Use table: forum */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/23 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_InsertForum')) drop proc up_InsertForum go
create proc up_InsertForum @a_strName varchar(50) , @a_strDescription varchar(255) , @a_intFatherID tinyint as /*定义局部变量*/ declare @intLayer tinyint declare @intRootID tinyint
/*如果是版面并且没有指定分类,则返回-1*/ if(@a_intFatherID <> 0 and not exists(select * from forum where id = @a_intFatherID)) return(-1)
/*根据@a_intFatherID计算layer , rootid*/ if(@a_intFatherID = 0) begin select @intLayer = 0 select @intRootID = 0 end else begin select @intLayer = 1 select @intRootID = @a_intFatherID end
Insert into Forum(rootid , layer , fatherid , title , description) values(@intRootID , @intLayer , @a_intFatherID , @a_strName , @a_strDescription) if (@a_intFatherID = 0) begin select @intRootID = @@identity update Forum set rootid = @intRootID where id = @intRootID end go
/*************************************************************************/ /* */ /* procedure : up_DeleteForum */ /* */ /* Description: 删除版面 */ /* */ /* Parameters: @a_intForumID : 版面id */ /* */ /* Use table: forum */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/23 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_DeleteForum')) drop proc up_DeleteForum go
create proc up_DeleteForum @a_intForumID tinyint as delete from Forum where id = @a_intForumID delete from Forum where RootID = @a_intForumID go
select id , title , rootid , fatherid from forum
/*************************************************************************/ /* */ /* procedure : up_PostTopic */ /* */ /* Description: 发贴子 */ /* */ /* Parameters: @a_intForumID : 版面id */ /* @a_intFatherID: 父贴ID,如果是新主题为0 */ /* @a_strSubject: 标题 */ /* @a_strContent: 内容 */ /* @a_intUserID: 发贴人ID */ /* @a_intFaceID: 表情ID */ /* @a_strIP: 发贴人IP */ /* */ /* Use table: bbs , forum , bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/13 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_PostTopic')) drop proc up_PostTopic go
create proc up_PostTopic @a_intForumID int , @a_intFatherID int , @a_strSubject varchar(255) , @a_strContent text , @a_intUserID int , @a_intFaceID int , @a_strIP varchar(255) as /*定义局部变量*/ declare @intRootID int --根id declare @dblOrderNum float(53) --排序基数 declare @intLayer int --层 declare @dblNextOrderNum float(53) --下一回贴的ordernum
/*判断有没有这个版面*/ if not exists(select * from forum where id = @a_intForumID) return(-1)
/*判断新贴子还是回应贴子*/ if (@a_intFatherID = 0) --根贴 begin select @intRootID = isnull(max(id) , 0) + 1 from bbs select @dblOrderNum = 9e+24 select @intLayer = 1 end else --回贴 begin select @intRootID = rootid , @intLayer = layer + 1 , @dblOrderNum = ordernum from bbs where id = @a_intFatherID
/*如果没找到父贴则返回错误*/ if (@@rowcount = 0) return -1
/*计算ordernum*/ select @dblNextOrderNum = isnull(max(ordernum), 0) from bbs where ordernum < @dblOrderNum and rootid=@intRootID select @dblOrderNum = (@dblOrderNum + @dblNextOrderNum) / 2 end
/*由于对两个表操作,用事务*/ Begin transaction /*插入贴子*/ insert into bbs(RootID , FatherID , Layer , OrderNum , UserID , ForumID , Subject , Content , FaceID , IP) values(@intRootID , @a_intFatherID , @intLayer , @dblOrderNum , @a_intUserID , @a_intForumID , @a_strSubject , @a_strContent , @a_intFaceID , @a_strIP) /*判断是否成功*/ if (@@error != 0) goto OnError
/*更新版面贴子数*/ update forum set topiccount = topiccount + 1 where id = @a_intForumID if (@@error != 0) goto OnError
/*更新用户分数*/ update BBSUser set point = point + 1 where id = @a_intUserID if (@@error !=0) goto OnError
/*执行*/ commit transaction return(0)
/*错误处理*/ OnError: rollback transaction return(-1)
go select id from bbs where fatherid=0 order by rootid desc, ordernum desc up_posttopic 1 , 12 , '哈哈哈,见笑了' , 'hello , world' , 1 , 1 , '203.93.95.10'
/*************************************************************************/ /* */ /* procedure : up_GetTopicList */ /* */ /* Description: 贴子列表 */ /* */ /* Parameters: @a_intForumID : 版面id */ /* @a_intPageNo: 页号 */ /* @a_intPageSize: 每页显示数,以根贴为准 */ /* */ /* Use table: bbs , forum */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/14 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_GetTopicList')) drop proc up_GetTopicList go
create proc up_GetTopicList @a_intForumID int , @a_intPageNo int , @a_intPageSize int as /*定义局部变量*/ declare @intBeginID int declare @intEndID int declare @intRootRecordCount int declare @intPageCount int declare @intRowCount int /*关闭计数*/ set nocount on
/*检测是否有这个版面*/ if not exists(select * from forum where id = @a_intForumID) return (-1)
/*求总共根贴数*/ select @intRootRecordCount = count(*) from bbs where fatherid=0 and forumid=@a_intForumID if (@intRootRecordCount = 0) --如果没有贴子,则返回零 return 0
/*判断页数是否正确*/ if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount return (-1)
/*求开始rootID*/ set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1 /*限制条数*/ set rowcount @intRowCount select @intBeginID = rootid from bbs where fatherid=0 and forumid=@a_intForumID order by id desc
/*结束rootID*/ set @intRowCount = @a_intPageNo * @a_intPageSize /*限制条数*/ set rowcount @intRowCount select @intEndID = rootid from bbs where fatherid=0 and forumid=@a_intForumID order by id desc
/*恢复系统变量*/ set rowcount 0 set nocount off
select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid , a.rootid , 'Bytes' = datalength(a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point from bbs as a join BBSUser as b on a.UserID = b.ID where Forumid=@a_intForumID and a.rootid between @intEndID and @intBeginID order by a.rootid desc , a.ordernum desc return(@@rowcount) --select @@rowcount go up_getTopiclist 3 , 1 , 20 select * from bbs where fatherid=0 order by id desc select * from bbsuser /*************************************************************************/ /* */ /* procedure : up_GetPostedTopicList */ /* */ /* Description: 精华区贴子列表 */ /* */ /* Parameters: @a_intForumID : 版面id */ /* @a_intPageNo: 页号 */ /* @a_intPageSize: 每页显示数,以根贴为准 */ /* */ /* Use table: bbs , forum */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/14 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_GetPostedTopicList')) drop proc up_GetPostedTopicList go
create proc up_GetPostedTopicList @a_intForumID int , @a_intPageNo int , @a_intPageSize int as /*定义局部变量*/ declare @intBeginID int declare @intEndID int declare @intRootRecordCount int declare @intPageCount int declare @intRowCount int /*关闭计数*/ set nocount on
/*检测是否有这个版面*/ if not exists(select * from forum where id = @a_intForumID) return (-1)
/*求总共根贴数*/ select @intRootRecordCount = count(*) from bbs where posted=1 and forumid=@a_intForumID if (@intRootRecordCount = 0) --如果没有贴子,则返回零 return 0
/*判断页数是否正确*/ if (@a_intPageNo - 1) * @a_intPageSize > @intRootRecordCount return (-1)
/*求开始rootID*/ set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize + 1 /*限制条数*/ set rowcount @intRowCount select @intBeginID = rootid from bbs where posted=1 and forumid=@a_intForumID order by id desc
/*结束rootID*/ set @intRowCount = @a_intPageNo * @a_intPageSize /*限制条数*/ set rowcount @intRowCount select @intEndID = rootid from bbs where posted=1 and forumid=@a_intForumID order by id desc
/*恢复系统变量*/ set rowcount 0 set nocount off
select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid , a.rootid , 'Bytes' = datalength(a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Point from bbs as a join BBSUser as b on a.UserID = b.ID where posted=1 and Forumid=@a_intForumID and a.rootid between @intEndID and @intBeginID order by a.rootid desc , a.ordernum desc return(@@rowcount) --select @@rowcount go select id , rootid , fatherid , forumid , posted from bbs up_getpostedtopiclist 3 ,1 , 20 /*************************************************************************/ /* */ /* procedure : up_GetTopic */ /* */ /* Description: 取贴子 */ /* */ /* Parameters: @a_intTopicID : 贴子id */ /* */ /* Use table: bbs */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/16 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_GetTopic')) drop proc up_GetTopic go
create proc up_GetTopic @a_intTopicID int as /*如果没有这贴子*/ if not exists (select * from bbs where id = @a_intTopicID) return (-1)
/*更新该贴的点击数*/ update bbs set hits = hits + 1 where id = @a_intTopicID
select a.* , 'Bytes' = datalength(a.content) , b.UserName , b.Email , b.Homepage , b.point , b.Signature from bbs as a join BBSUser as b on a.UserID = b.id where a.id = @a_intTopicID go
up_getTopic 11
/*************************************************************************/ /* */ /* procedure : up_DeleTopic */ /* */ /* Description: 删除贴子及子贴,更新发贴人信息 */ /* */ /* Parameters: @a_intTopicID : 贴子id */ /* */ /* Use table: bbs */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/2/24 */ /* */ /* History: */ /* */ /*************************************************************************/
if exists(select * from sysobjects where id = object_id('up_DeleTopic')) drop proc up_DeleTopic go
create proc up_DeleTopic @a_intTopicID int as
/*定义局部变量*/ declare @intRootID int declare @intLayer int declare @floatOrderNum float(53) declare @floatNextOrderNum float(53) declare @intCounts int declare @intForumID int
/*取消计数*/ set nocount on
/*首先查找这个贴子的rootid和ordernum,没有则返回*/ select @intRootID = RootID , @floatOrderNum = OrderNum , @intLayer = layer , @intForumID = forumid from bbs where id = @a_intTopicID if @@rowcount = 0 return (-1)
/*取下一个同层贴子的ordernum*/ select @FloatNextOrderNum = isnull(max(ordernum) , 0) from bbs where RootID=@intRootID and layer=@intLayer and ordernum < @floatOrderNum
/*多表操作,用事务*/ begin transaction
/*首先删贴*/
delete from bbs where rootid=@intRootID and ordernum > @floatNextOrderNum and ordernum <= @floatOrderNum select @intCounts = @@rowcount if (@@error != 0) goto Error
/*论坛贴子数减少*/ update forum set topiccount = topiccount - @intCounts where id=@intForumID if (@@error != 0) goto Error
/*完成事务,返回*/ commit transaction set nocount off return(0)
Error: rollback transaction set nocount off return (-1) go
select forumid from bbs update bbs set forumid=4
/*************************************************************************/ /* */ /* procedure : up_GetUserInfo */ /* */ /* Description: 取得发贴人信息 */ /* */ /* Parameters: @a_strUserName : 用户笔名 */ /* */ /* Use table: bbsuser */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/16 */ /* */ /* History: */ /* */ /*************************************************************************/ if exists(select * from sysobjects where id = object_id('up_GetUserInfo')) drop proc up_GetUserInfo go
create proc up_GetUserInfo @a_strUserName varchar(20) as declare @m_intOrder int --排名 declare @m_intPoint int --积分
set nocount on
/*如果没有找到该用户,则返回-1*/ select @m_intPoint = point from bbsuser where username=@a_strUserName if(@@rowcount = 0) return(-1)
/*求排名*/ select @m_intOrder = count(*) + 1 from bbsuser where point > @m_intPoint
select * , 'order' = @m_intOrder from bbsuser where username=@a_strUserName
set nocount off go up_getuserinfo '廖家远'
/*************************************************************************/ /* */ /* procedure : up_PostedTopic */ /* */ /* Description: 将贴子转入精华区 */ /* */ /* Parameters: @a_intTopicID 贴子id */ /* */ /* Use table: bbs, postedtopic */ /* */ /* Author: bigeagle@163.net */ /* */ /* Date: 2000/4/17 */ /* */ /* History: */ /* */ /*************************************************************************/
if exists(select * from sysobjects where id= object_id('up_postedtopic')) drop proc up_postedtopic go
create proc up_PostedTopic @a_intTopicID int as /*定义局部变量*/ declare @m_intUserID int --发贴人ID
/*查找是否有这个贴子*/ select @m_intUserID = userid from bbs where id = @a_intTopicID if(@@rowcount != 1) return -1
/*因为对两个表操作所以用事务*/ begin transaction update bbs set posted = 1 where id = @a_intTopicID if(@@error <> 0) goto Error update bbsuser set point = point + 3 where id = @m_intUserID if(@@error <> 0) goto Error Commit transaction return (0) Error: rollback transaction go |