存储过程|数据|数据结构/****************************************************************************/
/* */
/* 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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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: bigeagle163.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