/*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
/*論壇通告表*/ 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
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
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
