bbs的数据结构和存储过程（一）

/****************************************************************************/

/* */

/* 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