/ ************************************************** *********************************** /
/ * * /
/ * Filename: bbs.sql * /
/ * * /
/ * DESCRIPTION: BBS data structure * /
/ * * /
/ * Table: * /
/ * * /
/ * Procedure: * /
/ * * /
/ * Author: BiGeagle http://bigeagle.yeah.net * /
/ * * /
/ * Date: 2001/1/29 * /
/ * * /
/ * History: * /
/ * * /
/ ************************************************** *********************************** /
/*data structure*/
/ * BBS user table * / 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 DATIME DEFAULT GETDATE () Not Null, Point Int DEFAULT 0 NOT NULL)
Go
Create index ix_bbsuser on bbsuser (ID, username, password)
/ * BBS expression table * / if EXISTS (Select * from sysobjects where id = Object_id ('face') Drop Table Facego
Create Table Face (ID Tinyint Identity Primary Key, Face VARCHAR (30) Default '' NOT NULL) Go
/ * BBS table * / if EXISTS (Select * from sysobjects where id = Object_id ('bbs')) Drop Table BBSGO
Create Table BBS (ID Int IDEFAULT 0 NOT NULL, - Root Idfatherid Int Default 0 Not Null, - Father Idlayer Tinyint Default 0 Not Null, - Layer ORDERNUM FLOAT (53) Default 0 Not Null, - Sort Base Userid Int Default 0 Not Null, - Spokesman IdforumID Tinyint Default 1 Not Null, - Layout IdSubject Varchar (255) Default 'NOT NULL, - Theme Content Text Default' NOT NULL, - Content Faceid Tinyint Default 1 NOT NULL, - Hits INT Default 0 Not Null, - Hits IP VARCHAR (20) Default '' NOT NULL, - Posting iptime DateTime Default getdate () Not Null, - Posted Posted bit default 0 not null - whether the essence of postings) gocreate index ix_bbs on bbs (id, rootid, layer, fatherid, subject, posted) with DROP_EXISTING create index ix_bbs1 on bbs (fatherid, forumid) with DROP_EXISTINGcreate index ix_bbs2 on bbs (forumid , rootid, ordernum) with drop_existing
/ * Essence * / if EXISTS (Select * from sysobjects where id = Object_id ('postedtopic')) Drop Table Postedtopicgo
create table PostedTopic (id int identity primary key, UserID int default 0 not null, - spokesman IDForumID tinyint default 1 not null, - the layout IDSubject varchar (255) default '' not null, - the topic Content text default '' NOT NULL, - Content Faceid Tinyint Default 1 NOT NULL, - Expression Int Default 0 Not Null, - Clicked IP VARCHAR (20) Default '' NOT NULL, - Posting iptime DateTime Default getDate () Not Null - Published time) Go
/ * Forum Lombard * / if EXISTS (Select * from sysobjects where id = Object_id ('forum')) Drop Table Forumgo
create table Forum (ID tinyint identity primary key, RootID tinyint default 0 not null, - the root IDFatherID tinyint default 0 not null, - the parent IDLayer tinyint default 0 not null, - layer Title varchar (50) default '' not null , - Layout Name Description Varchar (255) Default '' NOT NULL, - Layout Description Masterid Int Default 1 Not Null, - Moderator IDtopiccount Int Default 0 Not Null, - Total number Time DateTime Default getDate () NotDate () Not NULL, - Creating Time Isopen Bit Default 0 Not Null - Whether to open) GoInsert Into Forum (Rootid, FatherId, Layer, Title, Description, Masterid) Values (1, 0, 0, "Talk Talks", "Do not violate the country In the case of the law, you can post your own remarks. " In the case of the law, you can express your own comments on sports. ", 1) Insert Into Forum (Rooti, FatherId, Layer, Title, Description, Masterid) Values (1, 1, 1," Joke Stand ", Let you easily work in the work clearance. ", 1) Insert Into Forum (Rootid, FatherId, Layer, Title, Description, Masterid) Values (2, 2, 1," Sports Salon ", Sports Getting Comments." 1 ) INSERT INTO Forum (rootid, fatherid , Layer, Title, Description, Masterid Values (2, 2, 1, "Football", "Football Reviews. ", 1) Insert Into Forum (Rootid, FatherId, Layer, Title, Description, Masterid) Values (2, 2, 1," Sea Bull Club "," Sea Bull's Discussion Park. "1)
SELECT * FROM Forum
/ * Forum notice table * / if exists (SELECT * from sysobjects where id = Object_id ('notify')) Drop Table NotifyGo
Create Table Notify (ID Int Int "Primary KEY, TOPICID INT Default 0 Not Null, Closed Bit Default 0 NULL,) GOSELECT * from NOTIFYDELETE from Notify WHERE ID = 5
/ *********** The following is the stored procedure ***************************************** ********************************** /
/ ************************************************** ************************ / / * * // * procedure: Up_getbbsinfo * // * * // * Description: Take information about the entire forum * // * * // * 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_getbbsInfogo
Create proc up_getbbsinfoasdeclare @forumcount intdeclare @topiccount intdeclare @usercount int int INT
SET NOCOUNT = Count (*) from forum where layer <> 0select @topiccount = count (*) from bbsselect @usercount = count (*) from bbsuser
/ * Get the information of the forum itself * / select 'forumcount' = @forumcount, 'topiccount' = @topiccount, 'usercount' = @usercount
Goup_getbbsinfo / ************************************************************* ************************* / / * * // * procedure: up_GETFORUMINFO * / / / * * // * Description: Get the relevant layout Information * // * * // * 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_getforumfo')) Drop Proc Up_GetforumFogo
create proc up_GetForumInfo @a_intForumID intasdeclare @intTopicCount intdeclare @intRootTopicCount intset nocount onif not exists (select * from Forum where id = @ a_intForumID) return 0select @intTopicCount = count (*) from bbs where forumid = @a_intForumIDselect @intRootTopicCount = count (*) from bbs where forumID = @ a_intForumID and fatherid = 0 select *, 'TopicCount' = @ intTopicCount, 'RootTopicCount' = @intRootTopicCountfrom Forum where id = @a_intForumIDset nocount offgo select id, rootid, title, fatherid from forum / **** *********************************************************** ******************* // * * // * procedure: up_getpostedforumfo * // * * // * Description: Take the information of the specified layout essence * //// * * // * 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_getpostedforumfo') D ROP Proc Up_GetPostedforumFogo
create proc up_GetPostedForumInfo @a_intForumID intasdeclare @intTopicCount intdeclare @intRootTopicCount intset nocount onif not exists (select * from Forum where id = @ a_intForumID) return 0select @intTopicCount = count (*) from bbs where forumid = @a_intForumID and posted = 1select *, ' Topiccount '= @ INTTTOPICCOUNT,' Roottopiccount '= @inttopiccountfrom forum where id = @a_intforumidset NoCount Offgo
