/****************************************************************************/
/* */
/* FileName: bbs.sql */
/* */
/* Description: bbs數(shù)據(jù)結(jié)構(gòu) */
/* */
/* Table: */
/* */
/* Procedure: */
/* */
/* Author: bigeagle http://bigeagle.yeah.net */
/* */
/* Date: 2001/1/29 */
/* */
/* History: */
/* */
/****************************************************************************/

/*數(shù)據(jù)結(jié)構(gòu)*/

/*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 , --排序基數(shù)
UserID int default 0 not null , --發(fā)言人ID
ForumID tinyint default 1 not null , --版面ID
Subject varchar(255) default '' not null , --主題
Content text default '' not null , --內(nèi)容
FaceID tinyint default 1 not null , --表情
Hits int default 0 not null , --點(diǎn)擊數(shù)
IP varchar(20) default '' not null , --發(fā)貼IP
Time datetime default getdate() not null , --發(fā)表時(shí)間
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

/*精華區(qū)*/
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 , --發(fā)言人ID
ForumID tinyint default 1 not null , --版面ID
Subject varchar(255) default '' not null , --主題
Content text default '' not null , --內(nèi)容
FaceID tinyint default 1 not null , --表情
Hits int default 0 not null , --點(diǎn)擊數(shù)
IP varchar(20) default '' not null , --發(fā)貼IP
Time datetime default getdate() not null --發(fā)表時(shí)間
)
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 , --貼子總數(shù)
Time datetime default getdate() not null , --創(chuàng)建時(shí)間
IsOpen bit default 0 not null --是否開放
)
go

insert into forum(rootid , fatherid , layer , title , description , masterid) values(1 , 0 , 0 , "談天說地" , "在不違犯國(guó)家法律的情況下，你可以發(fā)表你自己的言論。" , 1)
insert into forum(rootid , fatherid , layer , title , description , masterid) values(2 , 0 , 0 , "體育" , "在不違犯國(guó)家法律的情況下，你可以對(duì)體育發(fā)表你自己的評(píng)論。" , 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 , "體育沙龍" , "體育總和評(píng)論。" , 1)
insert into forum(rootid , fatherid , layer , title , description , masterid) values(2,2 , 1 , "足球" , "足球評(píng)論。" , 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

/***********以下為存儲(chǔ)過程************************************************************/

/*************************************************************************/
/* */
/* procedure : up_GetBBSInfo */
/* */
/* Description: 取得整個(gè)論壇的相關(guān)信息 */
/* */
/* 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: 取得指定版面的相關(guān)信息 */
/* */
/* 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: 取得指定版面精華區(qū)的相關(guān)信息 */
/* */
/* 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