/ ************************************************** *********************************** /
/ * * /
/ * 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. ", 1) Insert Into Forum (Rootid, FatherId, Layer, Title, Description, Masterid) Values (2, 0, 0," Sports "," Do not violate the country 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 "," Joke 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
/ ************************************************** ************************ / / * * // * procedure: Up_getforumlist * // * * // * Description: Get layout list * ///// * * // * parameters: none * // * * // * Use table: forum, bbsuser * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/2 / 10 * // * * // * history: * // * * // **************************************** *************************************************** / IF EXISTS (Select * from sysobjects where id = object_id ( 'up_GetForumList')) drop proc up_GetForumListgocreate proc up_GetForumListasselect a.id, a.rootid, a.fatherid, a.layer, a.title, a.topiccount, a.description, 'UserID' = b.id , B.username, B.Email, B.HomePage, B.SignatureFrom Forum As a join bbsuser as b on a.masterid = B.ID ORDER BY ROOTID, LAYERGOSELECT ID, TITLE, ROOTID from Forumup_GetforumList
/ ************************************************** ************************ / / * * // * procedure: up_insertforum * // * * // * Description: New layout * // * * // * parameters: @a_strname: Layout Name * // * @a_strdescription: Layout Description * // * @a_intfatherid: Category ID, if it is 0 description is a big classification * // * * // * Use Table: forum * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/4/23 * // * * // * history: * // * * // **** *********************************************************** **************************** / IF EXISTS (SELECT * from sysobjects where id = Object_id ('up_insertforum') Drop Proc Up_Insertforumogo
create proc up_InsertForum @a_strName varchar (50), @a_strDescription varchar (255), @a_intFatherID tinyintas / * define local variables * / declare @intLayer tinyintdeclare @intRootID tinyint / * If the layout is not specified and classified -1 * / IF (@a_intfatherid <> 0 and not exists (select * from forum where id = @a_intfatherid) Return (-1)
/ * Calculate layer, rootid * / if (@a_intFatherID = 0) beginselect @intLayer = 0select @intRootID = 0endelsebeginselect @intLayer = 1select @intRootID = @a_intFatherIDendInsert into Forum (rootid, layer, fatherid, title, description) values according @a_intFatherID (@intRootID, @intLayer, @a_intFatherID, @a_strName, @a_strDescription) if (@a_intFatherID = 0) beginselect @intRootID = @@ identityupdate Forum set rootid = @intRootID where id = @intRootIDendgo
/ ************************************************** ************************ / / * * // * procedure: up_deleteforum * // * * // * Description: Delete layout * // * * // * parameters: @a_intforumid: Layout ID * // * * // * Use Table: forum * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000 / 4/23 * // * * /// History: * // * * // **************************************** *************************************************************** / IF EXISTS (SELECT * FROM Sysobjects where id = Object_id ('up_deleteforum') Drop Proc Up_Deleteforumogo
Create proc up_deleteforum @a_intforumid tinyintasdelete from forum where id = @a_intforumiddelete from forum where rootid = @a_intforumidgo
Select ID, Title, Rootid, FatherId from Forum
/ ************************************************** *********************** / / * * // * procedure: up_posttopic * // * * // * Description: Poston * // * * // * parameters: @a_intforumid: Layout ID * // * @a_intfatherid: Pupid ID, if it is a new topic 0 * // * @a_strsubject: Title * // * @a_strContent: content * // * @ A_InTuserid: Posted by the person ID * // * @a_intfaceid: Emure ID * // * @a_strip: Posted by ip * /////////wwwwle: bbs, forum, bbsuser * // * * // * Author: bigeagle@163.net * // * * // * Date: 2000/2/13 * // * * // * history: * // * * // *********** *********************************************************** ************ / if exists (select * from sysobjects where id = object_id ( 'up_PostTopic')) drop proc up_PostTopicgocreate proc up_PostTopic @a_intForumID int, @a_intFatherID int, @ a_strSubject varchar (255), @a_strContent text, @ a_intUserID int, @ a_intFaceID int, @ a_strIP varchar (255) as / * define local variables * / declare @intRootID int - root iddeclare @dblOrderNum float (53) - radix sort declare @intLayer int - Layerne Declare @dblnextordernum float (53) - Next back ORDERNUM
/ * Judging whether there is this version * / if not exists (SELECT * from forum where id = @a_intforumid) Return (-1)
/ * Judgment new post or respond to post * / if (@a_intfatherid = 0) - Root Begin select @introotid = isnull (max (id), 0) 1 from bbsselect @dblordernum = 9e 24select @intlayer = 1ENDELSE - Post back beginselect @introotid = rootid, @intlayer = layer 1, @dblordernum = OrdernumFrom bbs where id = @a_intfatherid
/ * If you have not found a father's post, return error * / if (@@ rowcount = 0) Return -1 / * calculates ORDERNUM * / SELECT @dblnextordernum = ISNULL (Max (ORDERNUM), 0) from bbs where ordernum <@dblordernum and ROOTID = @ \Trootidselect @dblordernum = (@dblordernum @dblnextordernum) / 2 end / * Due to two tables, transaction * / begin transaction / * Insert Post * / Insert Into BBS (Rootid, FatherId, Layer, Ordernum , UserID, forumID, Subject, Content, FaceID, IP) values (@intRootID, @a_intFatherID, @intLayer, @dblOrderNum, @a_intUserID, @a_intForumID, @ a_strSubject, @a_strContent, @a_intFaceID, @a_strIP) / * determine success * / if (@@ Error! = 0) goto onerror
/ * Update layout number * / update forum set topiccount = Topiccount 1 where id = @a_intforumidif (@@ error! = 0) goto onerror / * Update User Score * / Update Bbsuser set point = Point 1 where id = @a_intuseridif (@@ Error! = 0) goto Onerror / * Execution * / Commit TransactionReturn (0)
/ * Error handling * / OneRror: rollback transactionreturn (-1)
Go Select ID from bbs where fatherid = 0 Order by rootid desc, Ordernum desc up_posttopic 1, 12, 'Hahaha, laugh,' Hello, World ', 1, 1,' 203.93.95.10 '
/ ************************************************** ************************ / / * * // * procedure: Up_gettopiclist * // * * // * Description: Post list * // * * // * parameters: @a_intforumid: Layout ID * // * @a_intpageno: Page number * // * @a_intpagesize: The number per page is displayed in the root stickers * // * // * Use Table: BBS , Forum * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/2/14 * // * * // * History: * // * * // * *********************************************************** ********************** / if EXISTS (SELECT * from sysobjects where id = Object_id ('up_gettopiclist') Drop Proc Up_gettopiclistgocreate proc up_gettopiclist @a_intforumid int, @ a_intPageNo int, @ a_intPageSize int as / * define local variables * / declare @intBeginID intdeclare @intEndID intdeclare @intRootRecordCount intdeclare @intPageCount intdeclare @intRowCount int / * Close count * / set nocount on / * detecting whether the forum * / if not exists (select * from forum where id = @a_intForumID) return (-1) / * find the number of total root paste * / select @intRootRecordCount = count (*) from bbs where fatherid = 0 and forumid = @ a_intForumIDif (@intRootRecordCount = 0 ) - Returns if there is no post Zero Return 0 / * Correct page number * / if (@a_intpageno - 1) * @a_intpagesize> @IntrootrecordcountReturn (-1)
/ * Start request rootID * / set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize 1 / * limit the number of * / set rowcount @intRowCountselect @intBeginID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID order by ID DESC
/ * End rootID * / set @intRowCount = @a_intPageNo * @ a_intPageSize / * limit the number of * / set rowcount @intRowCountselect @intEndID = rootid from bbs where fatherid = 0 and forumid = @ a_intForumID order by id desc
/ * Restore system variable * / set rowcount 0set nocount off
Select A.ID, A.Layer, A.forumID, A.Subject, A.FaceId, A. Hits, A.Time, A.Userid, A.FATHERID, A.ROOTID, 'BYTES' = DATALENGTH (A.Content ), B.Username, B.Email, B.homepage, B. Signature, B.PointFrom b on a.userid = b.idwhere forumid = @ a_intforumidid and a.rootid betWeen @intendid and @intbeginidorder By A.Rootid Desc, A.ordernum Desc Return (@@ rowcount) - SELECT @@ RowCountGo Up_gettopiclist 3, 1, 20Select * from bbs Where fitherid = 0 ORDER BY ID Descselect * from bbsuser / ******* *********************************************************** **************** // * * // * procedure: up_getpostedtopiclist * // * * // * Description: Essence Post List * // * * // * Parameters : @a_intforumid: @a_int * // * @a_intpageno: Page number * // * @a_intpagesize: Each page is displayed, with a root sticker * // * * ////// * * * * // * author: BiGeagle@163.net * // * * // * Date: 2000/2/14 * // * * // * history: * // * * // ******* *********************************************************** **************** / IF EXISTS (SELECT * from sysobjects where id = Object_i D ('up_getpostedtopiclist')) Drop Proc Up_GetPostedtopiClistgo
create proc up_GetPostedTopicList @a_intForumID int, @a_intPageNo int, @ a_intPageSize int as / * define local variables * / declare @intBeginID intdeclare @intEndID intdeclare @intRootRecordCount intdeclare @intPageCount intdeclare @intRowCount int / * Close Count * / set nocount on / * detected Is there this layout * / if not exists (Select * from forum where id = @a_intforumid) Return (-1) / * seeks a total of total roots * / select @Introotrecordcount = count (*) from bbs where posted = 1 and forumID @ a_intforumidif (@introotrecordcount = 0) - If there is no post, return zero return 0 / * judgment page number correct * / if (@a_intpageno - 1) * @a_intpagesize> @ITROTRECORDCOUNTRETURN (-1) / * start rootID * / set @intRowCount = (@a_intPageNo - 1) * @a_intPageSize 1 / * limit the number of * / set rowcount @intRowCountselect @intBeginID = rootid from bbs where posted = 1 and forumid = @ a_intForumID order by id desc
/ * End rootID * / set @intRowCount = @a_intPageNo * @ a_intPageSize / * limit the number of * / set rowcount @intRowCountselect @intEndID = rootid from bbs where posted = 1 and forumid = @ a_intForumID order by id desc
/ * Restore system variable * / set rowcount 0set nocount off
Select A.ID, A.Layer, A.forumID, A.Subject, A.FaceId, A. Hits, A.Time, A.Userid, A.FATHERID, A.ROOTID, 'BYTES' = DATALENGTH (A.Content ), B.Username, B.Email, B.homepage, B. Signature, B.PointFrom b. A Join bbsuser as b on a.userid = B.Idwhere posted = 1 and forumid = @ a_intforumid and a.rootid betWeen @ intEndID and @intBeginIDorder by a.rootid desc, a.ordernum desc return (@@ rowcount) - select @@ rowcountgo select id, rootid, fatherid, forumid, posted from bbsup_getpostedtopiclist 3, 1, 20 / ****** *********************************************************** ***************** / / * * // * procedure: Up_gettopic * // * * // * Description: Abstracts 3 /// * * // * parameters: @A_INTTTOPICID: Post ID * / / * * / / * Use Table: BBS * / / * * // * Author: BiGeagle@163.net * // * * // * Date: 2000/2/16 * / / * * // * history: * // * * // ****************************************** ******************************************************************** / IF EXISTS (SELECT * from sysobjects where id = Object_ID ('up_gettopic')) Drop Proc Up_gettopicGOCREATE PROC UP_GETTOPIC @A_INTTOPI Cid Intas / * If there is no this post * / if not exists * / if id = @a_inttopicid) Return (-1) / * Update this note number * / update bbs set hits = HITS 1 WHERE ID = @A_INTTTOPICID
Select a. *, 'Bytes' = Datalength (A.Content), B.username, B.email, B.Homepage, B.Point, B.SignatureFrom BBS as a join bbsuser as b on a.userid = B.ID WHERE A.ID = @a_inttopicidgo
UP_GETTOPIC 11
/ ************************************************** ************************ / / * * // * procedure: up_deletopic * // * * // * Description: Delete Posts and Pick Update Posting Information * // * * // * Parameters: @a_inttopicid: Post ID * / / * * // * Use Table: BBS * / / / * * // * Author: BiGeagle@163.net * // * * // * Date: 2000/2/24 * // * * // * history: * // * * // ******************* *********************************************************** **** / if EXISTS (Select * from sysobjects where id = Object_id ('up_deletopic') Drop Proc Up_DeleTopicgo
Create proc up_deletopic @a_inttopicid Int as
/ * Define local variables * / declare @intRootID intdeclare @intLayer intdeclare @floatOrderNum float (53) declare @floatNextOrderNum float (53) declare @intCounts intdeclare @intForumID int / * Cancel count * / set nocount on
/ * First, look up the post of rootid and ordernum, no return * / select @intRootID = RootID, @floatOrderNum = OrderNum, @ intLayer = layer, @ intForumID = forumidfrom bbs where id = @a_intTopicID if @@ rowcount = 0 return (-1)
/ * Remove one of the same-level posts * / select @floatnextordernum = isnull (max (ordernum), 0) from bbswhere rootid = @ INTROOTID AND = @ Intlayer and ORdernum <@floatorNum
/ * Multi-table operation, with a transaction * / begin transaction / * First Shantie * / delete from bbs where rootid = @ intRootID and ordernum> @floatNextOrderNumand ordernum <= @floatOrderNum select @intCounts = @@ rowcountif (@@ error! = 0) Goto Error
/ * Forum Post Number * / Update Forum Set Topiccount = Topiccount - @intcounts where id = @ Intforumidif (@@ Error! = 0) goto error
/ * Complete the transaction, return * / commit transactions set nocount offreturn (0)
Error: Rollback TransactionsEt NoCount Offreturn (-1) Go
SELECT Forum ID = 4 / ********************************************************************************************************************************************************************************************* ********************************* / / * * / / * procedure: Up_getuserinfo * // * * // * Description: Number of posting people * // * * // * parameters: @a_strusername: User pen name * // * * // * Use table: bbsuser * // * * // * author: BiGeagle@163.net * // * * // * Date: 2000/4/16 * // * * // * history: * // * * // ***************** *********************************************************** ***** / if EXISTS (SELECT * from sysobjects where id = Object_id ('up_getuserinfo')) Drop Proc Up_getuserInfogo
Create proc up_getuserinfo @a_strusername varchar (20) asdeclare @m_intorder int - Rank Declare @m_intpoint int - Points
Set nocount on
/ * If the user is not found, return -1 * / select @m_intpoint = point from bbsuser where username = @ a_strusernameif (@@ rowcount = 0) Return (-1)
/ * Ask for ranking * / select @m_intorder = count (*) 1 from bbsuser where point> @m_intpoint
SELECT *, 'Order' = @m_intorder from bbsuser where username = @ a_strusername
Set NoCount Offgoup_getuserinfo 'Liao Jiayuan'
/ ************************************************** ************************ / / * * / / * procedure: up_postedtopic * // * * // * Description: Transfer post into the essence District * // * * // * parameters: @a_inttopicid Post ID * // * * // * Use Table: BBS, Postedtopic * // * * // * Author: BiGeagle@163.net * // * * // * Date: 2000/4/17 * // * * // * history: * // * * // ******************************* *********************************************************** /
If EXISTS (SELECT * from sysobjects where id = Object_id ('up_postedtopic')) Drop Proc Up_Postedtopicgo
CREATE PROC UPOPICTOPIC @A_INTTOPICID INTAS / * Defines Local Variables * / Declare @m_intuserid Int - Posted by ID
/ * Find no this post * / select @m_intuserid = userid from bbs where id = @a_inttopicidif (@@ rowcount! = 1) Return -1 / * Because of two tables, use transactions * / begin TransactionUpdate BBS set posted = 1 where id = @a_intTopicIDif (@@ error <> 0) goto Errorupdate bbsuser set point = point 3 where id = @m_intUserIDif (@@ error <> 0) goto ErrorCommit transactionreturn (0) error: rollback transactiongo