NT Fan:你要的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

原文转自：http://www.ltesting.net