Data structure and stored procedure of BBS (i.)

Stored Procedures | data | structure/****************************************************************************/
/* */
* 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 default ' NOT NULL,
Password varchar (a) default ' NOT NULL,
Email varchar (MB) default ' NOT NULL ',
Homepage varchar 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 expression Form * *
if exists (select * from sysobjects where id = object_id (' face '))
drop table Face
Go

CREATE TABLE Face
(
ID tinyint identity primary key,
Face varchar default ' is NOT NULL
)
Go

/*bbs Table * *
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--root ID
Fatherid int default 0 NOT NULL,--Parent ID
Layer tinyint default 0 NOT NULL,--layer
Ordernum float default 0 NOT NULL,--Sort cardinality
UserID int default 0 NOT null--speaker ID
ForumID tinyint default 1 NOT NULL,--Layout ID
Subject varchar (255) Default ' NOT NULL '--theme
Content text default ' NOT null--contents
FaceID tinyint default 1 NOT null--expression
Hits int default 0 NOT null--number of clicks
IP varchar () default ' NOT NULL,--Post IP
Time datetime default GETDATE () is not NULL,--published
Posted bit default 0 NOT null--whether the essence posts
)
Go

CREATE INDEX Ix_bbs on BBS (IDs, 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

/* Essence Area * *
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--speaker ID
ForumID tinyint default 1 NOT NULL,--Layout ID
Subject varchar (255) Default ' NOT NULL '--theme
Content text default ' NOT null--contents
FaceID tinyint default 1 NOT null--expression
Hits int default 0 NOT null--number of clicks
IP varchar () default ' NOT NULL,--Post IP
Time datetime default GETDATE () not null--published
)
Go

/*forum Layout Form * *
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--root ID
Fatherid tinyint default 0 NOT NULL,--Parent ID
Layer tinyint default 0 NOT NULL,--layer
Title varchar default ' NOT NULL,--layout name
Description varchar (255) Default ' NOT NULL '--layout description
MasterID int default 1 NOT null--moderator ID
TopicCount int default 0 NOT null--total number of posts
DateTime default GETDATE () not NULL,--creation time
IsOpen bit default 0 NOT null-open
)
Go

INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (1, 0, 0, "chat", "without violating national laws, you can To publish your own words. ", 1)
INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (2, 0, 0, "Sport", "without violating the laws of the state, you can Sports to give your own comments. ", 1)
INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (1, 1, 1, "joke stand", "joke, let you relax in the work gap." ", 1)
INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (2,2, 1, "Sports Salon", "Sports summation review.") ", 1)
INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (2,2, 1, "Football", "football commentary.") ", 1)
INSERT INTO forum (Rootid, Fatherid, layer, title, description, MasterID) VALUES (2,2, 1, "The Manatee Club", "the Manatee fan's discussion corner.") ", 1)

SELECT * FROM Forum

/* Forum Notice form * *
if exists (select * from sysobjects where id = object_id