Inside Technique : Adding Discussion Forums : Stored Procedures

The discussion forum uses a collection of stored procedures for posting and editing messages.

We will now take you through each stored procedure.

Listing Forums and Messages

The stored procedures sp_gettopicsforforum and sp_getmessagesfortopic are used to retrieve individual messages and the messages within each forum. (we create the list of forums directly from the t_forums table so no stored procedure is used).

sp_gettopicsforforum

This stored procedure returns two recordsets given a forum id: one containing the forum information and the other containing the list of topics for the specified forum. The forum information allows you to access meta-information (eg., forum name and description). The list of topics is based on all messages in the forum that have a topic_parent of -1. This defines that the message is the first message in the topic. All replies have a topic_parent pointing to the originating message.

Create Procedure sp_getTopicsForForum
  @forum_id int
As
  set nocount on
  SELECT * FROM t_forums WHERE forum_id=@forum_id
  SELECT *, t_user.u_name, t_user.u_id FROM t_topics 
    INNER JOIN t_user ON t_topics.u_id = t_user.u_id 
    WHERE (t_topics.forum_id = @forum_id) AND (t_topics.topic_parent = - 1) 
    ORDER BY t_topics.topic_update DESC
  return 

sp_getmessagesfortopic

This stored procedure returns two recordsets representing a topic and all its replies. The first recordset returns the original message with information about the forum the message is contained within. The second recordset returns all the replies to the original message. Message replies are easily retrieved by finding messages with the topic_parent equal to the starting topic's ID.

Create Procedure sp_GetMessagesForTopic
  @topic_id int
As
  set nocount on
  SELECT t_forums.forum_name, t_user.u_name, t_topics.forum_id, 
         t_topics.topic_id, t_topics.topic_name, t_topics.topic_create, 
         t_topics.u_id, t_topics.topic_update, t_topics.topic_count, 
         t_topics.topic_message 
    FROM t_topics 
    INNER JOIN t_forums ON t_topics.forum_id = t_forums.forum_id 
    INNER JOIN t_user ON t_topics.u_id = t_user.u_id 
    WHERE (t_topics.topic_id = @topic_id)
  SELECT t_user.u_name, t_topics.*  
    FROM t_topics 
    INNER JOIN t_user ON t_topics.u_id = t_user.u_id 
    WHERE (t_topics.topic_parent = @topic_id) 
    ORDER BY t_topics.topic_create DESC
  return 

sp_getforum

This stored procedure is very simple and just returns all the meta-information for a particular forum.

Create Procedure sp_GetForum
  @forum_id int
As
  set nocount on
  SELECT forum_id, forum_name, forum_desc, forum_update 
    FROM t_forums 
    WHERE (forum_id = @forum_id)
  return 

Message Manipulation

The remaining stored procedures are used to post, edit, and delete messages.

sp_deletemessage

This stored procedure is used to delete an individual message from the message board. This stored procedure is very simple and only deletes a single message.

Create Procedure sp_DeleteMessage
  @topic_id int
As
  set nocount on
  DELETE FROM t_topics WHERE (topic_id = @topic_id)
  return 

By itself, this stored procedure is inadequate. For example, when you delete a message with replies you most likely want the replies to also be deleted. This does happen, but it happens inside the t_topics table where through a delete trigger. Triggers are specialized stored procedures that are automatically executed when a table is updated either through an insert, edit, or delete operation.

If you delete the first message in a topic, we go ahead and delete all replies. If the message you are deleting is a reply, then we decrement the counter that tracks how many replies have been posted.

Create Trigger t_topics_Delete
On dbo.t_topics
For Delete
As
  Declare @topic_id int
  SELECT @topic_id = (select topic_parent from deleted)
  if @topic_id=-1 
    begin
      DELETE t_topics FROM t_topics a , deleted b 
        WHERE (a.topic_parent = b.topic_id)		
    end
  else
    update t_topics 
      set a.topic_count = a.topic_count -1 
      from t_topics a, deleted b 
      where a.topic_id = b.topic_parent

sp_editmessage

This stored procedure is used by the administration screens to allow you to update an existing message.

Create Procedure sp_EditMessage
  @topic_id int,
  @topic_name varchar(200)="",
  @topic_message text
As
  set nocount on
  UPDATE t_topics 
    SET topic_name = @topic_name, topic_message=@topic_message 
    WHERE (topic_id = @topic_id)
  return

sp_starttopic

This stored procedure is used to post new messages to a specific forum. By specifying a topic_parent, you can use this procedure to reply to a specific message.

Create Procedure sp_StartTopic
  @forum_id int,
  @topic_parent int = null,
  @u_id int,
  @topic_name varchar(200),
  @topic_message text
As
  set nocount on
  INSERT INTO 
    t_topics(forum_id, u_id, topic_name, topic_parent, topic_message) 
    VALUES (@forum_id, @u_id, @topic_name, @topic_parent,@topic_message)	
  return 

Whenever a message is posted, we call an update trigger on the t_topics table. This trigger updates the message counter and last update information:

Create Trigger t_topics_Insert
On dbo.t_topics
For Insert
As
  if (select topic_parent from inserted)<>-1
    update t_topics set a.topic_count = a.topic_count + 1, 
       a.topic_update = getDate() 
    from t_topics a, inserted b 
    where a.topic_id = b.topic_parent
  update t_forums 
    set forum_update = getDate() 
    from t_forums a, inserted b 
    where a.forum_id = b.forum_id

These are all the stored procedures used by the message forum. If you are looking for a challenge, try extending the stored procedures and table to support a threaded message forum (hint - think about how you can use the topic_parent field). If you choose to add threading, be sure to update the delete stored procedure and update and delete triggers. They are not currently designed to work correctly with a threaded system.

We are now ready to take you through the ASP Scripts that interact with the database and expose a simple user-interface.

© 1997-2000 InsideDHTML.com, LLC. All rights reserved.