IDC资讯中心
/****************************************************************************/
/* */
/* 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
