Building An Online Asp Based Discussion Forum
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Building An Online Asp Based Discussion Forum as PDF for free.
More details
- Words: 9,836
- Pages: 39
Building an Online ASP based discussion forum In part II of our membership and personalization series, we are going to enhance the membership database introduced in our first article with a flexible discussion forum built using SQL Server and ASP. By the end of this article, you will have the knowledge and scripts necessary to add multiple forums to your web-site. We are going to create a discussion forum with the following features: • • • • •
Unlimited number of forums Unlimited number of single-threaded topics per forum (easily extended to fullthreading) Automatic page-chunking of topics Integration with the Site Membership table Easy Administration Features o Add/ Edit/ Remove Forums o Quickly Edit/ Delete any Message
The Discussion Forum is built using the same methodology as the Site Membership database. All logic that manipulates the database is contained within stored procedures and database triggers. The ASP scripts are simply a middle tier for interfacing the user with the database. The front-end client is a simple, script-free HTML user-interface. On the last page you will be able to download all the necessary SQL scripts and sample files to start adding site membership and discussion forums. These scripts were run and tested against IIS 4.0 with SQL Server 6.5. If you are familiar with SQL you should be able to adapt them for other SQL backends.
Interface Our goal is to provide you with a framework for building your own interactive and personalized community. For this reason, the sample code merely demonstrates each feature. We leave the task of clean integration with your system and backend up to you. The basic integration point is by calling each stored procedure. By understanding the arguments required by each stored procedure, you can easily build your own custom interface. For example, we created a simple index page for listing each discussion forum. This list is displayed in the order the forums were added. Since many sites rarely define new forums this page may be better served by a statically designed page. For example, lets look at the loop that generates the list of forums and see how the URL for viewing a forum is constructed: while not oRS.eof Response.Write("") Response.Write(oRS.fields("forum_name") & "")
if not isnull(oRS.fields("forum_update")) then Response.Write(" (last updated " & oRS.fields("forum_update") & ")") end if Response.Write("
" & oRS.fields("forum_desc")) oRS.movenext wend Each forum URL is constructed as pageName.asp?argumentList. For reading a forum, the URL is viewForum.asp?forum_id=id. Therefore, to link directly to a particular forum you can create a link similar to the following: Discuss Web Development With this approach you can build any custom layout or quickly link to the discussion page from anywhere on your site. This is the simplest integration point. This gets slightly more complex when you want to interact with the user. For example, let's imagine you are creating a new message page for a specific discussion forum. First, looking at the stored procedure for adding messages, sp_starttopic, you will notice that the stored procedure requires the forum id, a parent id if this is a reply, the user id posting the message, a message name, and the message itself. If you were to create a form for starting new topics, you need to make sure all this information is specified and then passed back to the server. When creating the form, we use hidden fields for contextual information (forum id, etc) the user does not need to see. For example: ' BuildForm and BuildInput are helper functions for generating the FORM and INPUT tags. Response.Write("") Response.Write("
") This creates the front-end interface. The next step is to write the interface between the user's input and the backend database. This is as simple as transferring the user's arguments into the sp_starttopic stored procedure: if (request.form("action")=ACTIONSAVE) then Set oCmd = GetStoredProcedure(getConnection(),"sp_StartTopic") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,forumID) oCmd.Parameters.append oCmd.CreateParameter("topic_parent", adInteger, adParamInput,10,-1) oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id"))
oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,escapeString(Request.Form("topic_name"))) oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() end if By building your server interfaces correctly, you are essentially creating an HTTP based API for your web-site. With a little more work, you create an interface page that could allow external agents to manipulate and query your backend database. With this approach, you would most likely return structured information instead of traditional web pages. We will be pursuing this concept more in future articles. Next, we continue by looking at the backend implementation. First we start with the database schema.
Schema The discussion forum consists of two tables (three if you include the user membership table):
t_user Table This is the user membership table from the first article. This table stores information about each registered user. The most important field in the t_user table is u_id which represents an individual user. This field is used to associate a user with a particular message.
t_forums Table This table contains the list of message forums. Adding new forums is as simple as adding new records to this table.
Column Name forum_id
Data Type Special Notes (length) Integer Auto-incrementing primary key. Used to associate the posted message with a particular forum. forum_name String (50) The name of the forum. forum_desc String (255) A short description of the forum. forum_update DateTime The date and time of the last update. This field is automatically updated whenever a message is posted. Create Table dbo.t_forums ( forum_id int Not Null Identity (1, 1), forum_name varchar(50) Not Null, forum_desc varchar(255) Not Null, forum_update datetime Null ) Go Alter Table dbo.t_forums Add Constraint PK_t_forums Primary Key Nonclustered ( forum_id
) Go
t_topics Table This table contains the list of messages. Each message is associated with a specific forum through the forum_id field. We designed this table so that it can be extended to support threaded messages. However, for this example, we limit all discussions to a single thread (just like the SiteExperts.com discussion forums). This table also contains two triggers for handling message deletions, message counts and last updates. We will explain the triggers in more detail when we walk through the stored procedures.
forum_id
Data Type (length) Integer
topic_id
Integer
u_id topic_name topic_create
Integer String (200) DateTime
topic_update
DateTime
topic_count
Integer
topic_parent
Integer
Column Name
topic_message Text
Special Notes The forum the message is posted in. Auto-incrementing primary key representing the individual message. The ID of the user who posted the message. The name of the discussion thread. The time and date the message was posted. The time and date the thread was responded to. Only updated for the original message of the thread. The number of replies to the message. The topic_id of the parent message. Defaults to -1 for new threads. The actual message.
Create Table dbo.t_topics ( forum_id int Not Null, topic_id int Not Null Identity (1, 1), u_id int Not Null, topic_name varchar(200) Null, topic_create datetime Not Null Constraint DF_t_topics_topic_date Default (getdate()), topic_update datetime Not Null Constraint DF_t_topics_topic_update Default (getdate()), topic_count int Not Null Constraint DF_t_topics_topic_count Default (0), topic_parent int Not Null Constraint DF_t_topics_topic_parent Default ((-1)), topic_message text Null ) Go Alter Table dbo.t_topics Add Constraint FK_t_topics_t_forums Foreign Key ( forum_id ) References dbo.t_forums ( forum_id )
Go Alter Table dbo.t_topics Add Constraint FK_t_topics_t_user Foreign Key ( u_id ) References dbo.t_user ( u_id ) Go 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 Go 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 Go
Table Relationships The relationships between each table is fairly straightforward. We reuse the same field name in all tables to represent the relationship. All our relationships are one-tomany relationships between the primary and foreign key. This means that each primary key can be related with any number of records in the related table. For example, looking at the first relationship below, an individual user can post any number of messages.
Primary Key t_user->u_id t_forums->forum_id t_topics->topic_id
Foreign Key t_topics->u_id t_topics->forum_id t_topics->topic_parent
Our approach is to always build and test the database before writing any of the ASP pages. Therefore, next we walk you through the stored procedures used to access and manipulate the discussion forums. These stored procedures expose all the operations for the discussion forum.
Stored Procedures The discussion forum uses a collection of stored procedures for posting and editing messages.
Store Procedure sp_deletemessage
Description Used by the site administrator to delete a message or message thread from the system. sp_editmessage Used by the site administrator (and possibly the message poster) to edit a specific message. sp_getforum Returns all the information (name, description, and update) for a specific forum. sp_getmessagesfortopic Given a topic id, returns the message and all the replies to the message. sp_gettopicsforforum Given a forum id, returns all the top-level topics sorted by most recent update. sp_saveforum Creates or edits an existing forum name and description. sp_starttopic Used to post a new message or reply to an existing message. 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 metainformation (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.
The ASP Scripts Our ASP scripts are designed to act as the foundation for your discussion forums. The user-interface is minimal and designed to just expose necessary functionality. We created three ASP pages for administering the discussion forums four ASP pages that provide the end-user experience:
User Pages forums.asp viewForum.asp
Dipslay a list of each discussion forum and a description. Display a list of messages within a specific forum. Displays an individual message with all the replies. This page includes viewTopic.asp a form for replying to the thread. startTopic.asp Displays a form for starting a new topic. Administration Pages editForum.asp Allows you to select a forum to edit or to add new forums. AdminForum.asp Used to edit the messages within an individual forum. adminTopic.asp Used to edit or delete an individual message or reply.
The user pages integrate with the site membership information introduced in our first article. If you are not logged in, you can only read the articles. To post or reply to an article, you must be logged into the system. Next we take you through the user pages. The entry page to the discussion forum is forums.asp. This page simply enumerates and outputs all the discussion forums with their descriptions. From this page, the user can select an individual forum taking them to the viewForum.asp.
Forums.asp The script for forums.asp is very simple. Rather than execute a query, we merely open the t_forums table and output each record. In a production system, you may want to add ordering information to the table and apply a sort, or depending upon your layout needs, you may want to create an index page manually. <%@ Language=VBScript %> <% option explicit %> <% Dim oRS, oCmd, oConn Const ACTIONSAVE = "Save..." set oConn = GetConnection() Response.Write("
viewForum.asp viewForum.asp is a much more interesting script. This page outputs the topics within a particular forum. To accomodate larger topic lists, this page also supports paging by displaying 20 topics at a time (this value can be changed).
Below we take you through the commented script. <%@ Language=VBScript %> <% option explicit Dim forumID, iStart ' The forum being viewed forumID = Request.QueryString("forum_id") ' The start record iStart = Request.QueryString("start") ' Make sure forum and start records are numbers if forumID="" or not isnumeric(forumid) then Response.redirect("forums.asp") if not isnumeric(iStart) or iStart<0 then iStart=0 Dim oCmd, oRS ' Get the list of messages Set oCmd = GetStoredProcedure(getConnection(),"sp_getTopicsForForum") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,forumID) set oRS = oCmd.execute() ' Two recordsets are returned. The first recordset contains ' information about the forum. If no forum exists (bad id) then ' redirect back to the forum list if oRS.eof then Response.Redirect("forums.asp") ' Get forum information Dim sForumName, sForumDesc sForumName = oRS.fields("forum_name") sForumDesc = oRS.fields("forum_desc") ' Get next recordset - the list of topics set oRS = oRS.nextRecordset
%> <TITLE><%=sForumName%> <% ' Output Header Response.Write("
User Pages - Viewing Messages Now we take you through the two asp pages for displaying messages and starting new topics.
viewTopic.asp When you view an individual topic, we first display the original message then we display all the replies. For this page, we did not implement chunking of the replies. In an active message board you may want to enhance this page with message chunking. You can easily add chunking by copying the script from the previous page. <%@ Language=VBScript %> <% option explicit dim topicid ' The topic being viewed topicid = Request.QueryString("topic_id") ' Make sure topic is a number if topicID="" or not isnumeric(topicID) then Response.redirect("forums.asp") Dim oCmd, oRS if Request.Form("action")=actionSave then ' Save a message reply ' Replies are submitted directly back to the viewing page. ' This reply is automatically returned with the query that populates the page. Set oCmd = GetStoredProcedure(getConnection(),"sp_StartTopic") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,Request.Form("forum_id")) oCmd.Parameters.append oCmd.CreateParameter("topic_parent", adInteger, adParamInput,10,Request.Form("topic_id")) oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id")) oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,"") oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() Response.Write("Message Posted") end if ' Get Message for specified topic Set oCmd = GetStoredProcedure(getConnection(),"sp_getMessagesForTopic") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,topicID) set oRS = oCmd.execute() ' Two recordsets are returned. The first recordset ' contains forum information, the number of replies, ' and the original message
' If bad forum ID no records are returned if oRS.eof then Response.Redirect("forums.asp") ' Get forum information and reply count Dim sForumName, sForumDesc, iForumID, iReplies iForumID = oRS.fields("forum_id") sForumName = oRS.fields("forum_name") iReplies = oRS.fields("topic_count") %> <TITLE><%=sForumName%> <% const actionSave = "Post..." Response.Write("
") ' Get next recordset. This contains all the replies ' to the original message set oRS = oRS.nextrecordset ' If replies, start outputting if (not oRS.eof) then ' First display number of replies if iReplies=1 then Response.Write("
Post Reply...") Response.Write("") Response.Write("
") else ' Not logged in - can't reply Response.Write("") Response.Write("
")
end if %> This defines all the user pages for the discussion forum. Next we take you through the pages for administrating and managing the message boards.
Administration Pages Equally important as the user pages are the administration pages. From these pages you can create new forums and edit or delete existing messages. The pages for creating forums and choosing a topic are very similar to the pages used to view forums and topics. Therefore, we are not going to go into those pages details (they are included in the download). However, we are going to show you the script for the edit topic page. This page is interesting because it provides one-click in place editing of each message. <%@ Language=VBScript %> <% option explicit ' The ID of the topic you are editing dim topicid topicid = Request.QueryString("topic_id")
if topicID="" or not isnumeric(topicID) then Response.redirect("forums.asp") ' This should be updated to the administrators account ' (for real security - the admin pages should be ' in an NT-secured administrator directory if session("u_id")="" then Response.Redirect("/members/logon.asp") Dim oCmd, oRS ' The const const const
3 actions available ACTIONDELETE = "Delete" ACTIONEDIT = "Edit" ACTIONSAVE = "Save"
if Request.Form("action")=ACTIONDELETE then ' Delete the message Set oCmd = GetStoredProcedure(getConnection(),"sp_DeleteMessage") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,200,Request.Form("topic_id")) oCmd.execute() end if if Request.Form("action")=ACTIONSAVE then ' Save Updated Message Set oCmd = GetStoredProcedure(getConnection(),"sp_EditMessage") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,Request.Form("topic_id"))
oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,escapeString(Request.Form("topic_name"))) oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() end if ' Get the messages for the topic ' (same technique as the user page) Set oCmd = GetStoredProcedure(getConnection(),"sp_getMessagesForTopic") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,topicID) set oRS = oCmd.execute() if oRS.eof then Response.Redirect("editForums.asp") Dim sForumName, sForumDesc, iForumID, iReplies ' Forum information iForumID = oRS.fields("forum_id") sForumName = oRS.fields("forum_name") iReplies = oRS.fields("topic_count") %> <TITLE><%=sForumName%> <% Response.Write("") if cInt(Request.Form("topic_id"))=cInt(oRS.fields("topic_id")) and Request.Form("action")=ACTIONEDIT then ' Editing the original topic Response.Write("
") set oRS = oRS.nextrecordset ' Output messages ' We reused much of the code from viewTopic.asp if (not oRS.eof) then if iReplies=1 then Response.Write("" & BuildTextArea("topic_message",oRS.fields("topic_message"),15,35) & "
" & BuildInput("hidden","topic_name","","","") & BuildInput("hidden","topic_id",oRS.fields("topic_id"),"","") & BuildInput("submit","action",ACTIONSAVE,"","") & " " & BuildInput("submit","action",ACTIONDELETE,"","") & "") else ' Not editing reply - output Response.Write("" & BuildInput("hidden","topic_id",oRS.fields("topic_id"),"","")) Response.Write(BuildInput("submit","action",ACTIONEDIT,"","") & " ") Response.Write(BuildInput("submit","action",ACTIONDELETE,"","") & "
%> That's it! You now have seen all the SQL queries and ASP scripts required to add simple discussion forums to your web site. On the next page we explain how to download and install the sample files
Download and Installation These scripts require a Window's NT machine with IIS 4.0 and SQL Server. To install this discussion forum, you need to download discussion.zip and decompress all the files and directories into your web-sites root directory. Once the files are in your root directory, you need to create your SQL Server database. We built this demonstration using SQL Server 6.5 but there is no reason it should not run with SQL Server 7.0. To make it easy to create the database, you will find a file called discussion.sql that contains all the SQL commands necessary to create the tables and stored procedures. You just need to execute this SQL file against a new SQL Server database. (In SQL Server 6.5, after creating the database, open the SQL Enterprise Manager, select the database, choose SQL Query Tool from the Tools menu and open and run the discussion.sql file). After creating your database you need to create an ODBC DSN (from the ODBC control panel) so the ASP scripts can connect to the database. After creating the DSN, you need to update the GetConnection function inside of utility.asp (in the inc directory) with the correct DSN name, user, and password: function GetConnection() const DSN = "membershipdb" const UID = "webuser" const PASSWORD = "password" Dim p_oConn, sDSN Set p_oConn = server.createObject("ADODB.Connection") sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD p_oConn.open sDSN Set GetConnection = p_oConn end function Included in this download are all the files for creating the membership table from the article Adding Site Membership. If you have already set up the user member table, be sure to delete the SQL statements that generate the t_user table or you will lose your data. As with all articles in this series we are merely providing the core pieces for adding membership and community features. Before introducing this into a production system you need to evaluate the performance, security and error handling of the scripts. We permit you to reuse these scripts as a learning device. To reuse this work in a production system, we ask that you make available to us for possible publishing any updates or changes you make to the database or interactions with the database. This includes any extensions that are built over this code.
Adding Site Registration By Scott Isaacs This is the first article in a new series demonstrating how to personalization and community features to your web-site. Each article will contain all the ASP scripts and SQL queries for a basic implementation. However, as with all database and transaction-based systems, you should do a careful review to meet an appropriate level of security, performance and error handling. In writing the samples we are focusing on simplicity and clarity sometimes at the expense of robustness. To keep things straightforward, the user-interface used in all samples is very minimalistic. The web pages merely act as a demonstration on how to interface with the backend, rather than how to create a rich user experience. In this article we are going to cover the first step and show you how to create a membership database. The membership directory supports the following features: • • • •
Joining the Web-Site Logging in the Web-Site Changing Passwords Member List Administration
We organized this article into two parts. First we explain the backend database. The backend database includes the user table and all the stored procedures used to manipulate the data. Once finished, we explain the ASP scripts which interact with the backend database and produce the web-pages for the user. First, we start with explaining the user table.
Defining the User Table We store all user information in a single table, t_user. This table stores the user name, password, e-mail address, and other user-specific information. We set-up our membership table as follows:
Column Name Data Type (length) Special Notes u_id Integer Auto-incremented Primary Key u_name String (50) Unique Index u_password String (12) u_firstname String (50) u_lastname String (50) u_email String (50) Indexed (non-unique) u_logcount Integer The number of log-ons u_createDate DateTime Defaults to user creation date u_lastDate DateTime The date and time of the last log-on For the SQL guru's, below is the SQL queries that generates the user table.
Create Table dbo.t_user ( u_id int Not Null Identity (1, 1), u_name varchar(50) Not Null, u_password varchar(12) Not Null, u_firstname varchar(50) Not Null, u_lastname varchar(50) Not Null, u_email varchar(50) Not Null, u_logcount int Not Null Constraint DF_t_user_u_logcount Default (1), u_createDate datetime Not Null Constraint DF_t_user_u_createDate Default (getdate()), u_lastDate datetime Not Null Constraint DF_t_user_u_lastDate Default (getdate()) ) Go Alter Table dbo.t_user Add Constraint PK_t_user_name Primary Key Nonclustered ( u_id ) Go Alter Table dbo.t_user Add Constraint IX_t_user_name Unique Nonclustered ( u_name ) Go Create Nonclustered Index IX_t_user_email On dbo.t_user ( u_email ) Go Depending upon your needs, this table can also be extended with additional information. For example, you can have fields for the user's address, phone number, etc. Now that this table is created, the next step is to write the necessary scripts that interact with the user table. Our approach to building a web-based database application is to encapsulate all the database manipulations and queries into stored procedures and use ASP scripts to enumerate and process query results. This separation allows us to build and test our database interactions separate from the testing the web-site. Next we explain the stored procedures used to add, edit, log-in, and find users.
The Stored Procedures We built all the database manipulations directly into the SQL database as stored procedures. This approach allows us to test the database independently from the ASP scripts. Therefore, our first step is to explain each of the stored procedures and how they are used in the system. Once we are finish exploring the stored procedures, we explain how these procedures are manipulated from the ASP scripts. For the most part, the ASP scripts are quite straightforward, but they do demonstrate techniques for processing forms on the server. However, in a few cases we did include validation logic that could
easily be incorporated into the backend database rather than manipulated through script. First, let's look at the stored procedures we created for our membership database:
Stored Procedure Description sp_adduser This stored procedure first verifies a user does not exist and then adds the user to the database. sp_changepassword This stored procedure is used to change a user's password. sp_getuser This stored procedure is used by the site manager to returns all the information for a specified user. sp_logonuser This stored procedure takes a user name and password and tries to log in a user. This also updates the log-in count for the user and the time stamp for the last visit. sp_saveuser This stored procedure is used by the site manager to update a user's record. We start by explaining the two stored procedures for adding and logging in users.
sp_adduser Adding a user to the database is a very simple process with one simple caveat - you must first ensure the specified user name is available. We accomplish this with a simple check against the user table before adding the record. If no record exists for the specified user-name we add the user. If a user exists, we return a user-id of -1 which signifies the duplicate. Create Procedure sp_adduser @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50), @u_id int output As set nocount on if not exists(select u_id from t_user where u_name=@u_name) begin INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname, u_email) VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email) select @u_id=@@identity end else select @u_id=-1 return This stored procedure is missing data validation. For example, there is no validation that the password meets a certain constraint (eg., between 4 and 12 characters). We currently do this validation in our ASP scripts (explained later). In a production system, such constraints may be better defined within the stored procedure with the addition of more detailed return codes. In addition, a production
system should probably encrypt the user's password. In this demonstration, the password is stored as entered.
sp_logonuser This stored procedure is very simple. Given a user name and password, we locate and return the corresponding record: Create Procedure sp_logonuser @u_name varchar(50), @u_password varchar(12) As set nocount on UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE (u_name = @u_name) AND (u_password = @u_password) SELECT * from t_user where u_name=@u_name and u_password=@u_password return The first query in this procedure increments the number of visits and updates the last log-in date. Once updated the user record itself is returned to the user. We do not need to check for the existance of the user record as this is handled automatically by the where clause that tests for a matching user-name and password. If there is no match an empty recordset is returned. Next we explain the three stored procedures related to administration.
The Stored Procedures We built all the database manipulations directly into the SQL database as stored procedures. This approach allows us to test the database independently from the ASP scripts. Therefore, our first step is to explain each of the stored procedures and how they are used in the system. Once we are finish exploring the stored procedures, we explain how these procedures are manipulated from the ASP scripts. For the most part, the ASP scripts are quite straightforward, but they do demonstrate techniques for processing forms on the server. However, in a few cases we did include validation logic that could easily be incorporated into the backend database rather than manipulated through script. First, let's look at the stored procedures we created for our membership database:
Stored Procedure Description sp_adduser This stored procedure first verifies a user does not exist and then adds the user to the database. sp_changepassword This stored procedure is used to change a user's password. sp_getuser This stored procedure is used by the site manager to returns all the information for a specified user. sp_logonuser This stored procedure takes a user name and password and tries to log in a user. This also updates the log-in count for the user and the time stamp for the last visit. sp_saveuser This stored procedure is used by the site manager to update a user's
record. We start by explaining the two stored procedures for adding and logging in users.
sp_adduser Adding a user to the database is a very simple process with one simple caveat - you must first ensure the specified user name is available. We accomplish this with a simple check against the user table before adding the record. If no record exists for the specified user-name we add the user. If a user exists, we return a user-id of -1 which signifies the duplicate. Create Procedure sp_adduser @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50), @u_id int output As set nocount on if not exists(select u_id from t_user where u_name=@u_name) begin INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname, u_email) VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email) select @u_id=@@identity end else select @u_id=-1 return This stored procedure is missing data validation. For example, there is no validation that the password meets a certain constraint (eg., between 4 and 12 characters). We currently do this validation in our ASP scripts (explained later). In a production system, such constraints may be better defined within the stored procedure with the addition of more detailed return codes. In addition, a production system should probably encrypt the user's password. In this demonstration, the password is stored as entered.
sp_logonuser This stored procedure is very simple. Given a user name and password, we locate and return the corresponding record: Create Procedure sp_logonuser @u_name varchar(50), @u_password varchar(12) As set nocount on UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE (u_name = @u_name) AND (u_password = @u_password) SELECT * from t_user where u_name=@u_name and u_password=@u_password return
The first query in this procedure increments the number of visits and updates the last log-in date. Once updated the user record itself is returned to the user. We do not need to check for the existance of the user record as this is handled automatically by the where clause that tests for a matching user-name and password. If there is no match an empty recordset is returned. Next we explain the three stored procedures related to administration.
Administration Procedures There are three stored procedures related to site administration. One, sp_changepassword, is intended for use by your members, and the other two, sp_getuser and sp_saveuser are intended for use by the site administrator.
sp_changepassword This simple stored procedure is used to change the user's password: Create Procedure sp_ChangePassword @u_id int, @old_password varchar(12), @new_password varchar(12), @success int output As set nocount on if exists (SELECT u_id FROM t_user WHERE (u_id = @u_id) AND (u_password = @old_password)) begin UPDATE t_user SET u_password = @new_password WHERE (u_id = @u_id) select @success = 1 end else select @success = -1 return Before updating the password, first we verify that the user entered the correct password for their user-id. If the password is correct, we simply update the password field. If the old password is entered incorrectly, we return a failure code.
sp_getuser This is a very simple and self-explanatory stored procedure that returns the complete user record for a specified user-id. Create Procedure sp_GetUser @u_id int As set nocount on select * from t_user where u_id=@u_id return We use this stored procedure to allow the site administrator to retrieve any user's record for editing. To complement this stored procedure, we created sp_saveuser which is used to update the user's record.
sp_saveuser
This stored procedure is used to update all the fields (except the user's id) for any particular user: Create Procedure sp_SaveUser @u_id int, @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50) As set nocount on if not exists(select u_id from t_user where u_name=@u_name and u_id<>@u_id) UPDATE t_user SET u_name = @u_name, u_password = @u_password, u_firstname = @u_firstname, u_lastname = @u_lastname, u_email = @u_email WHERE (u_id = @u_id) return Before updating the record, we first verify that no other user is already using a particular name. If none exists, we update all the fields for the specified user. This concludes our introduction to all the stored procedures. Next we walk you through the ASP scripts used to coordinate and inteface with the backend database.
Adding Site Registration : The ASP Scripts You have seen how the backend was created. Now we take you through the ASP scripts that interact with the database. To manage our database connections, we create an ASP file, utility.asp. This asp page is included in all pages that interact with the database. We use the ASP extension instead of INC (the typical include extension) to ensure that this page cannot be easily viewed by the client since it contains the name and password to connect to the database. In a production system, this file should be placed in a non-browsable directory with the appropriate NT security permissions. Below are the commented functions used that interact with the database. function GetConnection() ' GetConnection 'Used to obtain a connection to the backend database ' Update the following for your connection const DSN = "membershipdemo" const UID = "webuser" const PASSWORD = "mypassword" Dim p_oConn, sDSN Set p_oConn = server.createObject("ADODB.Connection") sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD p_oConn.open sDSN Set GetConnection = p_oConn end function
function GetStoredProcedure(oConnection, sName) ' GetStoredProcedure ' A helper function for quickly creating ' a stored procedure object Dim oCmd Set oCmd = Server.createobject("ADODB.Command") Set oCmd.ActiveConnection = oConnection oCmd.CommandText = sName oCmd.CommandType = adCmdStoredProc Set GetStoredProcedure = oCmd end function function GetTable(oConnection, sName) ' GetTable ' A helper function for quickly opening a ' table. Set GetTable = oConnection.Execute(sName) end function In addition, the utility.asp file contains a few generic functions that help simplify constructing a form. While these functions are very simplistic they help to provide style guidelines. For example, we created the BuildInput() function to make sure that all text input fields have an appropriate size and maximum length specified. Without this function, it becomes very easy to forget this information. function BuildInput(sType,sName,sValue,iSize,iLength) ' A helper function for building input boxes. select case sType case "text": BuildInput = "" case "password" BuildInput = "" case "submit" BuildInput = "" end select end function function EscapeString(str) ' Used to prevent HTML from being entered and ' and to escape any quotation marks. ' Escaping quotes is necessary so when the values can be ' specified in an input field. str = replace(str,"<","<") str = Trim(replace(str,"""",""")) escapeString = str end function function BuildForm(sName,sMethod) ' Returns a simple form tag. Used to ensure tha the appropriate ' submission method is specified
BuildForm = " One problem with the above approach is that you need to remember to update the script if the submit button's text ever changes. To avoid this, we store the value in a constant and use this constant throughout the page. With this approach, we can easily update the button's value without worrying about not updating the script. Continuing with the join.asp page, you will see we declare a constant ACTIONJOIN. This constant represents the submit button text. Below we outline the remainder of the page's logic: <% Response.Write(outputHeader()) ' The text for the submit button const ACTIONJOIN = "Join..."
Dim sName,sFirstName,sLastName,sEmailName, sError, bJoin ' Initialize bJoin = false sName="" sFirstName="" sLastName="" sEmailName="" sError=""
if (Request.Form("action")=ACTIONJOIN) then ' Joining Dim sPassword sName = Request.Form("u_name") sFirstName = Request.Form("u_firstname") sLastName = Request.Form("u_lastname") sEmailName = Request.Form("u_emailname") sPassword = Request.Form("u_password") ' In a production system, this validation may be better served inside ' the database itself. ' Do simple validation if len(sPassword)<4 or len(sPassword)>12 or sPassword="" then sError = sError & "
Your password must be between 4 and 12 characters" end if if trim(sFirstName)="" or trim(sLastName)="" or trim(sEmailName)="" or trim(sPassword)="" then sError = sError & "
You must fill out all fields to join." end if if sPassword<>Request.Form("u_password2") then sError = sError & "
Your passwords do not match" end if if sError="" then ' No errors - try to add user Dim oCmd, oReturn, oConn set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_adduser") oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar, adParamInput,50,sName) oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar, adParamInput,12,spassword) oCmd.Parameters.append oCmd.CreateParameter("u_firstName", adVarChar, adParamInput,50,sFirstName) oCmd.Parameters.append oCmd.CreateParameter("u_lastName", adVarChar, adParamInput,50,sLastName) oCmd.Parameters.append oCmd.CreateParameter("u_email", adVarChar, adParamInput,50,sEmailName) set oReturn = oCmd.CreateParameter("u_id", adInteger, adParamOutput) oCmd.Parameters.append oReturn oCmd.execute() if oReturn.value=-1 then ' Duplicate user name sError = sError & "
Your user name already exists. Please select a new one." else ' Success - Login and store information in the session object bJoin = true session("u_id") = oReturn.value session("u_name") = sName session("u_lastdate") = "" session("u_logcount") = 1 end if end if end if if not bJoin then
Response.Write("") Response.Write("
") else ' Output welcome text Response.Write("
You did not enter a name and/ or password." else ' Try to log the user in Dim oCmd, oConn, oRS set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_logonuser") oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar, adParamInput,50,sName) oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar, adParamInput,12,spassword) set oRS = oCmd.execute() ' If no record returned - login failed if oRS.eof then sError = "
Invalid User Name or Password." else ' Store user information in session object
session("u_id") = oRS.fields("u_id") session("u_name") = oRS.fields("u_name") session("u_lastdate") = oRS.fields("u_lastdate") session("u_logcount") = oRS.fields("u_logcount") bLogon = false end if end if end if if bLogon then Response.Write("") Response.Write("
") Response.Write("") else ' User logged in - output welcome back message Response.Write("
changepassword.asp All membership related sites should allow the user to update their password. Creating the change password page is very simple. First, you make sure the user is logged in, then you ask the user for their old password and a new one. Below is the complete script for the changepassword.asp page: <%@ Language=VBScript %> <% option explicit %> <% ' Make sure user is logged in if session("u_name")="" then Response.Redirect("logon.asp") %>
<% Dim sError const ACTION="Change Password" sError = "" if Request.Form("action") = ACTION then ' Try and update password if len(Request.Form("newpassword"))<4 or len(Request.Form("newpassword"))>12 then ' Make sure password is appropriate length sError = "
Your password must be between 4 and 12 characters." elseif Request.Form("newpassword")=Request.Form("newpassword2") then ' Make sure new password and confirmation are the same. ' If so, try and change password Dim oCmd, oReturn, oConn set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_ChangePassword") oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id")) oCmd.Parameters.append oCmd.CreateParameter("old_password", adVarChar, adParamInput,12,Request.Form("oldpassword")) oCmd.Parameters.append oCmd.CreateParameter("new_password", adVarChar, adParamInput,50,Request.Form("newpassword")) set oReturn = oCmd.CreateParameter("success", adInteger, adParamOutput) oCmd.Parameters.append oReturn oCmd.execute() if oReturn.value=-1 then ' Failure - report error sError = "
Password not changed. Did you enter your current password correctly?" end if else ' New password and New Password confirmation do not match. sError = "
New passwords do not match." end if end if Response.write(outputHeader()) if Request.Form("action")="" or sError<>"" then if sError<>"" then ' Report any errors. Response.Write("") Response.Write("
") end if else ' Output all users. Set oRS = GetTable(oConn,"t_user") Response.Write("
user id plus the appropriate form submission, the user's data will be updated and the form will be redisplayed. All of this is handled by the large if-then blocks: if Request.Form("action")=ACTIONSAVE then ' Save user information end if if Request.QueryString("u_id")<>"" then ' Output form else ' Output list of users end if Next we wrap up our introduction to adding site membership and explain how to download and install the demonstration
Conclusion and Download Files You have seen how to add a site membership table to your web-site. By itself, this membership table is not very useful. However, by combining the site membership table with additional features you can create a rich, personalized experience. For example, in the t_user table every user has a unique id. This id serves as the user's identifier throughout the system. For example, in a message board, this id is used to associate a message post with a particular user. For our next article, we plan on extending the concepts and database introduced in this to create a simple message board.
Installing the Demo This demonstration requires a Window's NT machine with IIS 4.0 and SQL Server. To install this demonstration, you need to download membership.zip and decompress all the files and directories into your web-sites root directory. Once the files are in your root directory, you need to create your SQL Server database. We built this demonstration using SQL Server 6.5 but there is no reason it should not run with SQL Server 7.0. To make it easy to create the database, you will find a file called membership.sql that contains all the SQL commands necessary to create the tables and stored procedures. You just need to execute this SQL file against a new SQL Server database. (In SQL Server 6.5, after creating the database, open the SQL Enterprise Manager, select the database, choose SQL Query Tool from the Tools menu and open and run the membership.sql file). As we said in the beginning of this article, by itself this code is not intended to serve as the foundation for a user membership system. Before introducing this into a production system you need to evaluate the performance, security and error handling of the scripts. We permit you to reuse these scripts as a learning device. To reuse this work in a production system, we ask that you make available to us for possible publishing any updates or changes you make to the database or interactions with the database. This includes any extensions built into the system (eg., adding message boards, etc). THE END Thanks
Unlimited number of forums Unlimited number of single-threaded topics per forum (easily extended to fullthreading) Automatic page-chunking of topics Integration with the Site Membership table Easy Administration Features o Add/ Edit/ Remove Forums o Quickly Edit/ Delete any Message
The Discussion Forum is built using the same methodology as the Site Membership database. All logic that manipulates the database is contained within stored procedures and database triggers. The ASP scripts are simply a middle tier for interfacing the user with the database. The front-end client is a simple, script-free HTML user-interface. On the last page you will be able to download all the necessary SQL scripts and sample files to start adding site membership and discussion forums. These scripts were run and tested against IIS 4.0 with SQL Server 6.5. If you are familiar with SQL you should be able to adapt them for other SQL backends.
Interface Our goal is to provide you with a framework for building your own interactive and personalized community. For this reason, the sample code merely demonstrates each feature. We leave the task of clean integration with your system and backend up to you. The basic integration point is by calling each stored procedure. By understanding the arguments required by each stored procedure, you can easily build your own custom interface. For example, we created a simple index page for listing each discussion forum. This list is displayed in the order the forums were added. Since many sites rarely define new forums this page may be better served by a statically designed page. For example, lets look at the loop that generates the list of forums and see how the URL for viewing a forum is constructed: while not oRS.eof Response.Write("
if not isnull(oRS.fields("forum_update")) then Response.Write(" (last updated " & oRS.fields("forum_update") & ")") end if Response.Write("
" & oRS.fields("forum_desc")) oRS.movenext wend Each forum URL is constructed as pageName.asp?argumentList. For reading a forum, the URL is viewForum.asp?forum_id=id. Therefore, to link directly to a particular forum you can create a link similar to the following: Discuss Web Development With this approach you can build any custom layout or quickly link to the discussion page from anywhere on your site. This is the simplest integration point. This gets slightly more complex when you want to interact with the user. For example, let's imagine you are creating a new message page for a specific discussion forum. First, looking at the stored procedure for adding messages, sp_starttopic, you will notice that the stored procedure requires the forum id, a parent id if this is a reply, the user id posting the message, a message name, and the message itself. If you were to create a form for starting new topics, you need to make sure all this information is specified and then passed back to the server. When creating the form, we use hidden fields for contextual information (forum id, etc) the user does not need to see. For example: ' BuildForm and BuildInput are helper functions for generating the FORM and INPUT tags. Response.Write("
Start new topic in " & sForumName) Response.Write(BuildForm("startTopic","post")) Response.Write("
| Forum: | " &_ sForumName & " |
| User Name: | " &_ Session("u_name") & " |
| Topic: | " &_ BuildInput("text","topic_name","",20,200) & " |
| Message | " &_ BuildTextArea("topic_message","",10,35) & " |
| " &_ BuildInput("submit","action",ACTIONSAVE,"","") & " | |
oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,escapeString(Request.Form("topic_name"))) oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() end if By building your server interfaces correctly, you are essentially creating an HTTP based API for your web-site. With a little more work, you create an interface page that could allow external agents to manipulate and query your backend database. With this approach, you would most likely return structured information instead of traditional web pages. We will be pursuing this concept more in future articles. Next, we continue by looking at the backend implementation. First we start with the database schema.
Schema The discussion forum consists of two tables (three if you include the user membership table):
t_user Table This is the user membership table from the first article. This table stores information about each registered user. The most important field in the t_user table is u_id which represents an individual user. This field is used to associate a user with a particular message.
t_forums Table This table contains the list of message forums. Adding new forums is as simple as adding new records to this table.
Column Name forum_id
Data Type Special Notes (length) Integer Auto-incrementing primary key. Used to associate the posted message with a particular forum. forum_name String (50) The name of the forum. forum_desc String (255) A short description of the forum. forum_update DateTime The date and time of the last update. This field is automatically updated whenever a message is posted. Create Table dbo.t_forums ( forum_id int Not Null Identity (1, 1), forum_name varchar(50) Not Null, forum_desc varchar(255) Not Null, forum_update datetime Null ) Go Alter Table dbo.t_forums Add Constraint PK_t_forums Primary Key Nonclustered ( forum_id
) Go
t_topics Table This table contains the list of messages. Each message is associated with a specific forum through the forum_id field. We designed this table so that it can be extended to support threaded messages. However, for this example, we limit all discussions to a single thread (just like the SiteExperts.com discussion forums). This table also contains two triggers for handling message deletions, message counts and last updates. We will explain the triggers in more detail when we walk through the stored procedures.
forum_id
Data Type (length) Integer
topic_id
Integer
u_id topic_name topic_create
Integer String (200) DateTime
topic_update
DateTime
topic_count
Integer
topic_parent
Integer
Column Name
topic_message Text
Special Notes The forum the message is posted in. Auto-incrementing primary key representing the individual message. The ID of the user who posted the message. The name of the discussion thread. The time and date the message was posted. The time and date the thread was responded to. Only updated for the original message of the thread. The number of replies to the message. The topic_id of the parent message. Defaults to -1 for new threads. The actual message.
Create Table dbo.t_topics ( forum_id int Not Null, topic_id int Not Null Identity (1, 1), u_id int Not Null, topic_name varchar(200) Null, topic_create datetime Not Null Constraint DF_t_topics_topic_date Default (getdate()), topic_update datetime Not Null Constraint DF_t_topics_topic_update Default (getdate()), topic_count int Not Null Constraint DF_t_topics_topic_count Default (0), topic_parent int Not Null Constraint DF_t_topics_topic_parent Default ((-1)), topic_message text Null ) Go Alter Table dbo.t_topics Add Constraint FK_t_topics_t_forums Foreign Key ( forum_id ) References dbo.t_forums ( forum_id )
Go Alter Table dbo.t_topics Add Constraint FK_t_topics_t_user Foreign Key ( u_id ) References dbo.t_user ( u_id ) Go 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 Go 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 Go
Table Relationships The relationships between each table is fairly straightforward. We reuse the same field name in all tables to represent the relationship. All our relationships are one-tomany relationships between the primary and foreign key. This means that each primary key can be related with any number of records in the related table. For example, looking at the first relationship below, an individual user can post any number of messages.
Primary Key t_user->u_id t_forums->forum_id t_topics->topic_id
Foreign Key t_topics->u_id t_topics->forum_id t_topics->topic_parent
Our approach is to always build and test the database before writing any of the ASP pages. Therefore, next we walk you through the stored procedures used to access and manipulate the discussion forums. These stored procedures expose all the operations for the discussion forum.
Stored Procedures The discussion forum uses a collection of stored procedures for posting and editing messages.
Store Procedure sp_deletemessage
Description Used by the site administrator to delete a message or message thread from the system. sp_editmessage Used by the site administrator (and possibly the message poster) to edit a specific message. sp_getforum Returns all the information (name, description, and update) for a specific forum. sp_getmessagesfortopic Given a topic id, returns the message and all the replies to the message. sp_gettopicsforforum Given a forum id, returns all the top-level topics sorted by most recent update. sp_saveforum Creates or edits an existing forum name and description. sp_starttopic Used to post a new message or reply to an existing message. 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 metainformation (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.
The ASP Scripts Our ASP scripts are designed to act as the foundation for your discussion forums. The user-interface is minimal and designed to just expose necessary functionality. We created three ASP pages for administering the discussion forums four ASP pages that provide the end-user experience:
User Pages forums.asp viewForum.asp
Dipslay a list of each discussion forum and a description. Display a list of messages within a specific forum. Displays an individual message with all the replies. This page includes viewTopic.asp a form for replying to the thread. startTopic.asp Displays a form for starting a new topic. Administration Pages editForum.asp Allows you to select a forum to edit or to add new forums. AdminForum.asp Used to edit the messages within an individual forum. adminTopic.asp Used to edit or delete an individual message or reply.
The user pages integrate with the site membership information introduced in our first article. If you are not logged in, you can only read the articles. To post or reply to an article, you must be logged into the system. Next we take you through the user pages. The entry page to the discussion forum is forums.asp. This page simply enumerates and outputs all the discussion forums with their descriptions. From this page, the user can select an individual forum taking them to the viewForum.asp.
Forums.asp The script for forums.asp is very simple. Rather than execute a query, we merely open the t_forums table and output each record. In a production system, you may want to add ordering information to the table and apply a sort, or depending upon your layout needs, you may want to create an index page manually. <%@ Language=VBScript %> <% option explicit %> <% Dim oRS, oCmd, oConn Const ACTIONSAVE = "Save..." set oConn = GetConnection() Response.Write("
" & HOMEPAGE & " : " & SITENAME & " Discussion Forums
") Set oRS = GetTable(oConn,"t_forums") Response.Write("- ") while not oRS.eof Response.Write("
- ") Response.write(oRS.fields("forum_name") & "") if not isnull(oRS.fields("forum_update")) then ' Output date of last update if one exists Response.Write(" (last updated " & oRS.fields("forum_update") & ")") end if Response.Write("
" & oRS.fields("forum_desc")) oRS.movenext wend Response.Write("
viewForum.asp viewForum.asp is a much more interesting script. This page outputs the topics within a particular forum. To accomodate larger topic lists, this page also supports paging by displaying 20 topics at a time (this value can be changed).
Below we take you through the commented script. <%@ Language=VBScript %> <% option explicit Dim forumID, iStart ' The forum being viewed forumID = Request.QueryString("forum_id") ' The start record iStart = Request.QueryString("start") ' Make sure forum and start records are numbers if forumID="" or not isnumeric(forumid) then Response.redirect("forums.asp") if not isnumeric(iStart) or iStart<0 then iStart=0 Dim oCmd, oRS ' Get the list of messages Set oCmd = GetStoredProcedure(getConnection(),"sp_getTopicsForForum") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,forumID) set oRS = oCmd.execute() ' Two recordsets are returned. The first recordset contains ' information about the forum. If no forum exists (bad id) then ' redirect back to the forum list if oRS.eof then Response.Redirect("forums.asp") ' Get forum information Dim sForumName, sForumDesc sForumName = oRS.fields("forum_name") sForumDesc = oRS.fields("forum_desc") ' Get next recordset - the list of topics set oRS = oRS.nextRecordset
%> <TITLE><%=sForumName%> <% ' Output Header Response.Write("
" & HOMEPAGE & " : Discussion Forums : " & sForumName & " Forum
" & sForumDesc) ' This defines the number of records to display per page const chunking = 20
Dim iCount iCount = 0 ' A link to start a new topic (security check occurs on the starttopic.asp page) Response.Write("
Start New Topic") ' If messages exist, move to the start record (for paging) if not oRS.eof then oRS.move iStart ' If starting past first record, display previous page link if iStart>0 then Response.Write("
Previous Messages...") end if ' If no more records, display message if oRS.eof then Response.Write("
<EM>No more messages") end if ' Output each topic until no more topics or reach per page record limit Response.Write("
- ") while not oRS.eof and iCount
User Pages - Viewing Messages Now we take you through the two asp pages for displaying messages and starting new topics.
viewTopic.asp When you view an individual topic, we first display the original message then we display all the replies. For this page, we did not implement chunking of the replies. In an active message board you may want to enhance this page with message chunking. You can easily add chunking by copying the script from the previous page. <%@ Language=VBScript %> <% option explicit dim topicid ' The topic being viewed topicid = Request.QueryString("topic_id") ' Make sure topic is a number if topicID="" or not isnumeric(topicID) then Response.redirect("forums.asp") Dim oCmd, oRS if Request.Form("action")=actionSave then ' Save a message reply ' Replies are submitted directly back to the viewing page. ' This reply is automatically returned with the query that populates the page. Set oCmd = GetStoredProcedure(getConnection(),"sp_StartTopic") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,Request.Form("forum_id")) oCmd.Parameters.append oCmd.CreateParameter("topic_parent", adInteger, adParamInput,10,Request.Form("topic_id")) oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id")) oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,"") oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() Response.Write("Message Posted") end if ' Get Message for specified topic Set oCmd = GetStoredProcedure(getConnection(),"sp_getMessagesForTopic") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,topicID) set oRS = oCmd.execute() ' Two recordsets are returned. The first recordset ' contains forum information, the number of replies, ' and the original message
' If bad forum ID no records are returned if oRS.eof then Response.Redirect("forums.asp") ' Get forum information and reply count Dim sForumName, sForumDesc, iForumID, iReplies iForumID = oRS.fields("forum_id") sForumName = oRS.fields("forum_name") iReplies = oRS.fields("topic_count") %> <TITLE><%=sForumName%> <% const actionSave = "Post..." Response.Write("
" & HOMEPAGE & " : Discussion Forums : " & sForumName & " Forum
") ' Output original message Response.Write("
| " Response.Write("" & oRS.fields("topic_name") & " |
| " & oRS.fields("topic_message") & " |
| <SMALL><EM>Started By " & oRS.fields("u_name")) Response.Write(" on:" & oRS.fields("topic_create") & " |
1 Reply") else Response.Write("
" & iReplies & " Replies") end if ' Output each reply while not oRS.eof Response.Write("
" & oRS.fields("u_name") & " on " & oRS.fields("topic_update") & "
") Response.Write(oRS.fields("topic_message")) oRS.movenext wend end if ' If user logged in display "reply" form if session("u_id")<>"" then Response.Write(BuildForm("startTopic","post"))
' Store topic and forum in a hidden field Response.Write(BuildInput("hidden","topic_id",TopicID,10,10)) Response.Write(BuildInput("hidden","forum_id",iForumID,10,10)) Response.Write("
Post Reply...") Response.Write("
| User Name: | " & Session("u_name") & " |
| Message: | " & BuildTextArea("topic_message","",10,35) & " |
| " & BuildInput("submit","action",ACTIONSAVE,"","") & " | |
You must Log-on to post replies") end if %> The last task for creating the user pages is to create a page for starting new topics.
startTopic.asp Compared to the previous page, this page is quite simple. It contains a form for starting new topics. For simplicity, we filter and automatically escape all HTML. This can be easily changed by modifying the escape* functions contained within the utility.asp file. Allowing HTML can create a real challenge to administrating and managing your discussion forums. We allow HTML on our message boards. However, our algorithm for checking HTML is very simplistic so we often have to manually fix messages that unintentionally disrupt the page's layout. <%@ Language=VBScript %> <% option explicit
dim forumID ' Start topic in which forum forumID = Request.QueryString("forum_id") if forumID="" or not isnumeric(forumid) then Response.redirect("forums.asp") ' Make sure logged in if session("u_id")="" then Response.Redirect("/members/logon.asp") Dim oCmd, oRS ' Get forum information Set oCmd = GetStoredProcedure(getConnection(),"sp_getForum") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,forumID) set oRS = oCmd.execute() ' Make sure valid forum if oRS.eof then Response.Redirect("forums.asp")
Dim sForumName, sForumDesc ' Retrieve forum information sForumName = oRS.fields("forum_name") sForumDesc = oRS.fields("forum_desc") oRS.close() %>
" & HOMEPAGE & " : Discussion Forums : " & sForumName & " Forum
") if Request.Form("action")=actionSave then ' Save new topic Set oCmd = GetStoredProcedure(getConnection(),"sp_StartTopic") oCmd.Parameters.append oCmd.CreateParameter("forum_id", adInteger, adParamInput,10,forumID) oCmd.Parameters.append oCmd.CreateParameter("topic_parent", adInteger, adParamInput,10,-1) oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id")) ' Escape all HTML oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,escapeString(Request.Form("topic_name"))) oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() Response.Write("Topic: " & escapeString(Request.Form("topic_name")) & "
") Response.Write(escapeStringwithCR(Request.Form("topic_message"))) Response.Write("
View Messages...") else ' Output form for posting message Response.Write("
Start new topic in " & sForumName) Response.Write(BuildForm("startTopic","post")) Response.Write("
| Forum: | " & sForumName & " |
| User Name: | " & Session("u_name") & " |
| Topic: | " & BuildInput("text","topic_name","",20,200) & " |
| Message | " & BuildTextArea("topic_message","",10,35) & " |
| " & BuildInput("submit","action",ACTIONSAVE,"","") & " | |
end if %> This defines all the user pages for the discussion forum. Next we take you through the pages for administrating and managing the message boards.
Administration Pages Equally important as the user pages are the administration pages. From these pages you can create new forums and edit or delete existing messages. The pages for creating forums and choosing a topic are very similar to the pages used to view forums and topics. Therefore, we are not going to go into those pages details (they are included in the download). However, we are going to show you the script for the edit topic page. This page is interesting because it provides one-click in place editing of each message. <%@ Language=VBScript %> <% option explicit ' The ID of the topic you are editing dim topicid topicid = Request.QueryString("topic_id")
if topicID="" or not isnumeric(topicID) then Response.redirect("forums.asp") ' This should be updated to the administrators account ' (for real security - the admin pages should be ' in an NT-secured administrator directory if session("u_id")="" then Response.Redirect("/members/logon.asp") Dim oCmd, oRS ' The const const const
3 actions available ACTIONDELETE = "Delete" ACTIONEDIT = "Edit" ACTIONSAVE = "Save"
if Request.Form("action")=ACTIONDELETE then ' Delete the message Set oCmd = GetStoredProcedure(getConnection(),"sp_DeleteMessage") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,200,Request.Form("topic_id")) oCmd.execute() end if if Request.Form("action")=ACTIONSAVE then ' Save Updated Message Set oCmd = GetStoredProcedure(getConnection(),"sp_EditMessage") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,Request.Form("topic_id"))
oCmd.Parameters.append oCmd.CreateParameter("topic_name", adVarChar, adParamInput,200,escapeString(Request.Form("topic_name"))) oCmd.Parameters.append oCmd.CreateParameter("topic_message", adLongVarChar, adParamInput,32000,escapeStringWithCR(Request.Form("topic_message"))) oCmd.execute() end if ' Get the messages for the topic ' (same technique as the user page) Set oCmd = GetStoredProcedure(getConnection(),"sp_getMessagesForTopic") oCmd.Parameters.append oCmd.CreateParameter("topic_id", adInteger, adParamInput,10,topicID) set oRS = oCmd.execute() if oRS.eof then Response.Redirect("editForums.asp") Dim sForumName, sForumDesc, iForumID, iReplies ' Forum information iForumID = oRS.fields("forum_id") sForumName = oRS.fields("forum_name") iReplies = oRS.fields("topic_count") %> <TITLE><%=sForumName%> <% Response.Write("
" & HOMEPAGE & " : Site Administrator : Forum Manager : " & sForumName & "
") Response.Write("You can edit or delete messages in this topic. Deleting the starting message removes the entire topic.") Response.Write(BuildForm("","POST") & "
| " & BuildInput("text","topic_name",oRS.fields("topic_name"),25,200) & " |
| " & BuildTextArea("topic_message",oRS.fields("topic_message"),15,35) & " |
| <SMALL><EM>Started By " & oRS.fields("u_name") & " on:" & oRS.fields("topic_create") & " |
| " & BuildInput("hidden","topic_id",oRS.fields("topic_id"),"","") & BuildInput("submit","action",ACTIONSAVE,"","") & " " & BuildInput("submit","action",ACTIONDELETE,"","") & " |
| " & oRS.fields("topic_name") & " |
| " & oRS.fields("topic_message") & " |
| <SMALL><EM>Started By " & oRS.fields("u_name")) Response.Write(" on:" & oRS.fields("topic_create") & " |
| " & BuildInput("hidden","topic_id",oRS.fields("topic_id"),"","")) Response.Write(BuildInput("submit","action",ACTIONEDIT,"","") & " ") Response.Write(BuildInput("submit","action",ACTIONDELETE,"","") & " |
1 Reply") else Response.Write("
" & iReplies & " Replies") end if while not oRS.eof Response.Write(BuildForm("","POST")) if cInt(Request.Form("topic_id"))=cInt(oRS.fields("topic_id")) and Request.Form("action")=ACTIONEDIT then ' Editing reply - display form Response.Write("
" & oRS.fields("u_name") & " on " & oRS.fields("topic_update") & "
") Response.Write("
" & BuildInput("hidden","topic_name","","","") & BuildInput("hidden","topic_id",oRS.fields("topic_id"),"","") & BuildInput("submit","action",ACTIONSAVE,"","") & " " & BuildInput("submit","action",ACTIONDELETE,"","") & "") else ' Not editing reply - output Response.Write("
" & oRS.fields("u_name") & " on " & oRS.fields("topic_update") & "
") Response.Write(oRS.fields("topic_message")) ' Output Edit and Delete buttons Response.Write("
%> That's it! You now have seen all the SQL queries and ASP scripts required to add simple discussion forums to your web site. On the next page we explain how to download and install the sample files
Download and Installation These scripts require a Window's NT machine with IIS 4.0 and SQL Server. To install this discussion forum, you need to download discussion.zip and decompress all the files and directories into your web-sites root directory. Once the files are in your root directory, you need to create your SQL Server database. We built this demonstration using SQL Server 6.5 but there is no reason it should not run with SQL Server 7.0. To make it easy to create the database, you will find a file called discussion.sql that contains all the SQL commands necessary to create the tables and stored procedures. You just need to execute this SQL file against a new SQL Server database. (In SQL Server 6.5, after creating the database, open the SQL Enterprise Manager, select the database, choose SQL Query Tool from the Tools menu and open and run the discussion.sql file). After creating your database you need to create an ODBC DSN (from the ODBC control panel) so the ASP scripts can connect to the database. After creating the DSN, you need to update the GetConnection function inside of utility.asp (in the inc directory) with the correct DSN name, user, and password: function GetConnection() const DSN = "membershipdb" const UID = "webuser" const PASSWORD = "password" Dim p_oConn, sDSN Set p_oConn = server.createObject("ADODB.Connection") sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD p_oConn.open sDSN Set GetConnection = p_oConn end function Included in this download are all the files for creating the membership table from the article Adding Site Membership. If you have already set up the user member table, be sure to delete the SQL statements that generate the t_user table or you will lose your data. As with all articles in this series we are merely providing the core pieces for adding membership and community features. Before introducing this into a production system you need to evaluate the performance, security and error handling of the scripts. We permit you to reuse these scripts as a learning device. To reuse this work in a production system, we ask that you make available to us for possible publishing any updates or changes you make to the database or interactions with the database. This includes any extensions that are built over this code.
Adding Site Registration By Scott Isaacs This is the first article in a new series demonstrating how to personalization and community features to your web-site. Each article will contain all the ASP scripts and SQL queries for a basic implementation. However, as with all database and transaction-based systems, you should do a careful review to meet an appropriate level of security, performance and error handling. In writing the samples we are focusing on simplicity and clarity sometimes at the expense of robustness. To keep things straightforward, the user-interface used in all samples is very minimalistic. The web pages merely act as a demonstration on how to interface with the backend, rather than how to create a rich user experience. In this article we are going to cover the first step and show you how to create a membership database. The membership directory supports the following features: • • • •
Joining the Web-Site Logging in the Web-Site Changing Passwords Member List Administration
We organized this article into two parts. First we explain the backend database. The backend database includes the user table and all the stored procedures used to manipulate the data. Once finished, we explain the ASP scripts which interact with the backend database and produce the web-pages for the user. First, we start with explaining the user table.
Defining the User Table We store all user information in a single table, t_user. This table stores the user name, password, e-mail address, and other user-specific information. We set-up our membership table as follows:
Column Name Data Type (length) Special Notes u_id Integer Auto-incremented Primary Key u_name String (50) Unique Index u_password String (12) u_firstname String (50) u_lastname String (50) u_email String (50) Indexed (non-unique) u_logcount Integer The number of log-ons u_createDate DateTime Defaults to user creation date u_lastDate DateTime The date and time of the last log-on For the SQL guru's, below is the SQL queries that generates the user table.
Create Table dbo.t_user ( u_id int Not Null Identity (1, 1), u_name varchar(50) Not Null, u_password varchar(12) Not Null, u_firstname varchar(50) Not Null, u_lastname varchar(50) Not Null, u_email varchar(50) Not Null, u_logcount int Not Null Constraint DF_t_user_u_logcount Default (1), u_createDate datetime Not Null Constraint DF_t_user_u_createDate Default (getdate()), u_lastDate datetime Not Null Constraint DF_t_user_u_lastDate Default (getdate()) ) Go Alter Table dbo.t_user Add Constraint PK_t_user_name Primary Key Nonclustered ( u_id ) Go Alter Table dbo.t_user Add Constraint IX_t_user_name Unique Nonclustered ( u_name ) Go Create Nonclustered Index IX_t_user_email On dbo.t_user ( u_email ) Go Depending upon your needs, this table can also be extended with additional information. For example, you can have fields for the user's address, phone number, etc. Now that this table is created, the next step is to write the necessary scripts that interact with the user table. Our approach to building a web-based database application is to encapsulate all the database manipulations and queries into stored procedures and use ASP scripts to enumerate and process query results. This separation allows us to build and test our database interactions separate from the testing the web-site. Next we explain the stored procedures used to add, edit, log-in, and find users.
The Stored Procedures We built all the database manipulations directly into the SQL database as stored procedures. This approach allows us to test the database independently from the ASP scripts. Therefore, our first step is to explain each of the stored procedures and how they are used in the system. Once we are finish exploring the stored procedures, we explain how these procedures are manipulated from the ASP scripts. For the most part, the ASP scripts are quite straightforward, but they do demonstrate techniques for processing forms on the server. However, in a few cases we did include validation logic that could
easily be incorporated into the backend database rather than manipulated through script. First, let's look at the stored procedures we created for our membership database:
Stored Procedure Description sp_adduser This stored procedure first verifies a user does not exist and then adds the user to the database. sp_changepassword This stored procedure is used to change a user's password. sp_getuser This stored procedure is used by the site manager to returns all the information for a specified user. sp_logonuser This stored procedure takes a user name and password and tries to log in a user. This also updates the log-in count for the user and the time stamp for the last visit. sp_saveuser This stored procedure is used by the site manager to update a user's record. We start by explaining the two stored procedures for adding and logging in users.
sp_adduser Adding a user to the database is a very simple process with one simple caveat - you must first ensure the specified user name is available. We accomplish this with a simple check against the user table before adding the record. If no record exists for the specified user-name we add the user. If a user exists, we return a user-id of -1 which signifies the duplicate. Create Procedure sp_adduser @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50), @u_id int output As set nocount on if not exists(select u_id from t_user where u_name=@u_name) begin INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname, u_email) VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email) select @u_id=@@identity end else select @u_id=-1 return This stored procedure is missing data validation. For example, there is no validation that the password meets a certain constraint (eg., between 4 and 12 characters). We currently do this validation in our ASP scripts (explained later). In a production system, such constraints may be better defined within the stored procedure with the addition of more detailed return codes. In addition, a production
system should probably encrypt the user's password. In this demonstration, the password is stored as entered.
sp_logonuser This stored procedure is very simple. Given a user name and password, we locate and return the corresponding record: Create Procedure sp_logonuser @u_name varchar(50), @u_password varchar(12) As set nocount on UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE (u_name = @u_name) AND (u_password = @u_password) SELECT * from t_user where u_name=@u_name and u_password=@u_password return The first query in this procedure increments the number of visits and updates the last log-in date. Once updated the user record itself is returned to the user. We do not need to check for the existance of the user record as this is handled automatically by the where clause that tests for a matching user-name and password. If there is no match an empty recordset is returned. Next we explain the three stored procedures related to administration.
The Stored Procedures We built all the database manipulations directly into the SQL database as stored procedures. This approach allows us to test the database independently from the ASP scripts. Therefore, our first step is to explain each of the stored procedures and how they are used in the system. Once we are finish exploring the stored procedures, we explain how these procedures are manipulated from the ASP scripts. For the most part, the ASP scripts are quite straightforward, but they do demonstrate techniques for processing forms on the server. However, in a few cases we did include validation logic that could easily be incorporated into the backend database rather than manipulated through script. First, let's look at the stored procedures we created for our membership database:
Stored Procedure Description sp_adduser This stored procedure first verifies a user does not exist and then adds the user to the database. sp_changepassword This stored procedure is used to change a user's password. sp_getuser This stored procedure is used by the site manager to returns all the information for a specified user. sp_logonuser This stored procedure takes a user name and password and tries to log in a user. This also updates the log-in count for the user and the time stamp for the last visit. sp_saveuser This stored procedure is used by the site manager to update a user's
record. We start by explaining the two stored procedures for adding and logging in users.
sp_adduser Adding a user to the database is a very simple process with one simple caveat - you must first ensure the specified user name is available. We accomplish this with a simple check against the user table before adding the record. If no record exists for the specified user-name we add the user. If a user exists, we return a user-id of -1 which signifies the duplicate. Create Procedure sp_adduser @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50), @u_id int output As set nocount on if not exists(select u_id from t_user where u_name=@u_name) begin INSERT INTO t_user(u_name, u_password, u_firstname, u_lastname, u_email) VALUES (@u_name, @u_password, @u_firstname, @u_lastname, @u_email) select @u_id=@@identity end else select @u_id=-1 return This stored procedure is missing data validation. For example, there is no validation that the password meets a certain constraint (eg., between 4 and 12 characters). We currently do this validation in our ASP scripts (explained later). In a production system, such constraints may be better defined within the stored procedure with the addition of more detailed return codes. In addition, a production system should probably encrypt the user's password. In this demonstration, the password is stored as entered.
sp_logonuser This stored procedure is very simple. Given a user name and password, we locate and return the corresponding record: Create Procedure sp_logonuser @u_name varchar(50), @u_password varchar(12) As set nocount on UPDATE t_user SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1 WHERE (u_name = @u_name) AND (u_password = @u_password) SELECT * from t_user where u_name=@u_name and u_password=@u_password return
The first query in this procedure increments the number of visits and updates the last log-in date. Once updated the user record itself is returned to the user. We do not need to check for the existance of the user record as this is handled automatically by the where clause that tests for a matching user-name and password. If there is no match an empty recordset is returned. Next we explain the three stored procedures related to administration.
Administration Procedures There are three stored procedures related to site administration. One, sp_changepassword, is intended for use by your members, and the other two, sp_getuser and sp_saveuser are intended for use by the site administrator.
sp_changepassword This simple stored procedure is used to change the user's password: Create Procedure sp_ChangePassword @u_id int, @old_password varchar(12), @new_password varchar(12), @success int output As set nocount on if exists (SELECT u_id FROM t_user WHERE (u_id = @u_id) AND (u_password = @old_password)) begin UPDATE t_user SET u_password = @new_password WHERE (u_id = @u_id) select @success = 1 end else select @success = -1 return Before updating the password, first we verify that the user entered the correct password for their user-id. If the password is correct, we simply update the password field. If the old password is entered incorrectly, we return a failure code.
sp_getuser This is a very simple and self-explanatory stored procedure that returns the complete user record for a specified user-id. Create Procedure sp_GetUser @u_id int As set nocount on select * from t_user where u_id=@u_id return We use this stored procedure to allow the site administrator to retrieve any user's record for editing. To complement this stored procedure, we created sp_saveuser which is used to update the user's record.
sp_saveuser
This stored procedure is used to update all the fields (except the user's id) for any particular user: Create Procedure sp_SaveUser @u_id int, @u_name varchar(50), @u_password varchar(12), @u_firstname varchar(50), @u_lastname varchar(50), @u_email varchar(50) As set nocount on if not exists(select u_id from t_user where u_name=@u_name and u_id<>@u_id) UPDATE t_user SET u_name = @u_name, u_password = @u_password, u_firstname = @u_firstname, u_lastname = @u_lastname, u_email = @u_email WHERE (u_id = @u_id) return Before updating the record, we first verify that no other user is already using a particular name. If none exists, we update all the fields for the specified user. This concludes our introduction to all the stored procedures. Next we walk you through the ASP scripts used to coordinate and inteface with the backend database.
Adding Site Registration : The ASP Scripts You have seen how the backend was created. Now we take you through the ASP scripts that interact with the database. To manage our database connections, we create an ASP file, utility.asp. This asp page is included in all pages that interact with the database. We use the ASP extension instead of INC (the typical include extension) to ensure that this page cannot be easily viewed by the client since it contains the name and password to connect to the database. In a production system, this file should be placed in a non-browsable directory with the appropriate NT security permissions. Below are the commented functions used that interact with the database. function GetConnection() ' GetConnection 'Used to obtain a connection to the backend database ' Update the following for your connection const DSN = "membershipdemo" const UID = "webuser" const PASSWORD = "mypassword" Dim p_oConn, sDSN Set p_oConn = server.createObject("ADODB.Connection") sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD p_oConn.open sDSN Set GetConnection = p_oConn end function
function GetStoredProcedure(oConnection, sName) ' GetStoredProcedure ' A helper function for quickly creating ' a stored procedure object Dim oCmd Set oCmd = Server.createobject("ADODB.Command") Set oCmd.ActiveConnection = oConnection oCmd.CommandText = sName oCmd.CommandType = adCmdStoredProc Set GetStoredProcedure = oCmd end function function GetTable(oConnection, sName) ' GetTable ' A helper function for quickly opening a ' table. Set GetTable = oConnection.Execute(sName) end function In addition, the utility.asp file contains a few generic functions that help simplify constructing a form. While these functions are very simplistic they help to provide style guidelines. For example, we created the BuildInput() function to make sure that all text input fields have an appropriate size and maximum length specified. Without this function, it becomes very easy to forget this information. function BuildInput(sType,sName,sValue,iSize,iLength) ' A helper function for building input boxes. select case sType case "text": BuildInput = "" case "password" BuildInput = "" case "submit" BuildInput = "" end select end function function EscapeString(str) ' Used to prevent HTML from being entered and ' and to escape any quotation marks. ' Escaping quotes is necessary so when the values can be ' specified in an input field. str = replace(str,"<","<") str = Trim(replace(str,"""",""")) escapeString = str end function function BuildForm(sName,sMethod) ' Returns a simple form tag. Used to ensure tha the appropriate ' submission method is specified
BuildForm = " One problem with the above approach is that you need to remember to update the script if the submit button's text ever changes. To avoid this, we store the value in a constant and use this constant throughout the page. With this approach, we can easily update the button's value without worrying about not updating the script. Continuing with the join.asp page, you will see we declare a constant ACTIONJOIN. This constant represents the submit button text. Below we outline the remainder of the page's logic: <% Response.Write(outputHeader()) ' The text for the submit button const ACTIONJOIN = "Join..."
Dim sName,sFirstName,sLastName,sEmailName, sError, bJoin ' Initialize bJoin = false sName="" sFirstName="" sLastName="" sEmailName="" sError=""
if (Request.Form("action")=ACTIONJOIN) then ' Joining Dim sPassword sName = Request.Form("u_name") sFirstName = Request.Form("u_firstname") sLastName = Request.Form("u_lastname") sEmailName = Request.Form("u_emailname") sPassword = Request.Form("u_password") ' In a production system, this validation may be better served inside ' the database itself. ' Do simple validation if len(sPassword)<4 or len(sPassword)>12 or sPassword="" then sError = sError & "
Your password must be between 4 and 12 characters" end if if trim(sFirstName)="" or trim(sLastName)="" or trim(sEmailName)="" or trim(sPassword)="" then sError = sError & "
You must fill out all fields to join." end if if sPassword<>Request.Form("u_password2") then sError = sError & "
Your passwords do not match" end if if sError="" then ' No errors - try to add user Dim oCmd, oReturn, oConn set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_adduser") oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar, adParamInput,50,sName) oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar, adParamInput,12,spassword) oCmd.Parameters.append oCmd.CreateParameter("u_firstName", adVarChar, adParamInput,50,sFirstName) oCmd.Parameters.append oCmd.CreateParameter("u_lastName", adVarChar, adParamInput,50,sLastName) oCmd.Parameters.append oCmd.CreateParameter("u_email", adVarChar, adParamInput,50,sEmailName) set oReturn = oCmd.CreateParameter("u_id", adInteger, adParamOutput) oCmd.Parameters.append oReturn oCmd.execute() if oReturn.value=-1 then ' Duplicate user name sError = sError & "
Your user name already exists. Please select a new one." else ' Success - Login and store information in the session object bJoin = true session("u_id") = oReturn.value session("u_name") = sName session("u_lastdate") = "" session("u_logcount") = 1 end if end if end if if not bJoin then
Response.Write("
Joining " & SITENAME & " allows you to personalize and improve your user experience.") if sError<>"" then ' Output error if failed join attempt Response.Write("
Your membership was not added because:" & sError & "") end if Response.Write(BuildForm("username","post")) Response.Write("
| User Name | ") Response.Write("" & BuildInput("text","u_name",escapeString(sName),20,50) & " |
| Password | ") Response.Write("" & BuildInput("password","u_password","",12,12) & " |
| Confirm Password | ") Response.Write("" & BuildInput("password","u_password2","",12,12) & " |
| First Name | ") Response.Write("" & BuildInput("text","u_firstname",escapeString(sFirstName),20,50) & " |
| Last Name | ") Response.Write("" & BuildInput("text","u_lastname",escapeString(sLastName),20,50) & " |
| Email Name | ") Response.Write("" & BuildInput("text","u_emailname",escapeString(sEmailName),20,50) & " |
| " & BuildInput("submit","action",ACTIONJOIN,"","") & " | |
Welcome to the Community!") end if %> The other pages in the system work in a very similar manner. Each page contains an input form and the necessary logic to process the form's input. For subsequent visits, we need a log-in page and a place where users can change their password. Next we explain how to create both these pages.
Logging into a Site Once a user joins, they need to be able to log back in and change their password. Logging users in is accomplished with the logon.asp page, and password management is handled by changepassword.asp. First we will look at logon.asp.
logon.asp
The logon.asp page serves two purposes. To allow existing users to log into the system and to allow users to manually log themselves off. Since the session object eventually times out automatically logging the user off, the log out functionality is provided mostly as a convenience to the user. Since this page supports two distinct actions, we use the form management technique presented on the previous page. Below we walk you through the complete logon.asp script: <% @Language=VBScript %> <% Option Explicit %>
You did not enter a name and/ or password." else ' Try to log the user in Dim oCmd, oConn, oRS set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_logonuser") oCmd.Parameters.append oCmd.CreateParameter("u_name", adVarChar, adParamInput,50,sName) oCmd.Parameters.append oCmd.CreateParameter("u_password", adVarChar, adParamInput,12,spassword) set oRS = oCmd.execute() ' If no record returned - login failed if oRS.eof then sError = "
Invalid User Name or Password." else ' Store user information in session object
session("u_id") = oRS.fields("u_id") session("u_name") = oRS.fields("u_name") session("u_lastdate") = oRS.fields("u_lastdate") session("u_logcount") = oRS.fields("u_logcount") bLogon = false end if end if end if if bLogon then Response.Write("
Logon " & SITENAME & " or Join Now.") if sError<>"" then ' Report any errors Response.Write("
You were not logged in because:" & sError & "") end if Response.Write(BuildForm("username","post")) Response.Write("
| User Name | ") Response.Write("" & BuildInput("text","u_name",sName,20,50) & " |
| Password | ") Response.Write("" & BuildInput("password","u_password","",12,12) & " |
| " & BuildInput("submit","action",LOGON_ACTION,"","") & " | |
Welcome Back " & session("u_name") & "
") Response.Write(BuildForm("","post")) Response.Write(BuildInput("submit","action",LOGOFF_ACTION,"","")) Response.Write("") end if %>changepassword.asp All membership related sites should allow the user to update their password. Creating the change password page is very simple. First, you make sure the user is logged in, then you ask the user for their old password and a new one. Below is the complete script for the changepassword.asp page: <%@ Language=VBScript %> <% option explicit %> <% ' Make sure user is logged in if session("u_name")="" then Response.Redirect("logon.asp") %>
<% Dim sError const ACTION="Change Password" sError = "" if Request.Form("action") = ACTION then ' Try and update password if len(Request.Form("newpassword"))<4 or len(Request.Form("newpassword"))>12 then ' Make sure password is appropriate length sError = "
Your password must be between 4 and 12 characters." elseif Request.Form("newpassword")=Request.Form("newpassword2") then ' Make sure new password and confirmation are the same. ' If so, try and change password Dim oCmd, oReturn, oConn set oConn = GetConnection() Set oCmd = GetStoredProcedure(oConn,"sp_ChangePassword") oCmd.Parameters.append oCmd.CreateParameter("u_id", adInteger, adParamInput,10,session("u_id")) oCmd.Parameters.append oCmd.CreateParameter("old_password", adVarChar, adParamInput,12,Request.Form("oldpassword")) oCmd.Parameters.append oCmd.CreateParameter("new_password", adVarChar, adParamInput,50,Request.Form("newpassword")) set oReturn = oCmd.CreateParameter("success", adInteger, adParamOutput) oCmd.Parameters.append oReturn oCmd.execute() if oReturn.value=-1 then ' Failure - report error sError = "
Password not changed. Did you enter your current password correctly?" end if else ' New password and New Password confirmation do not match. sError = "
New passwords do not match." end if end if Response.write(outputHeader()) if Request.Form("action")="" or sError<>"" then if sError<>"" then ' Report any errors. Response.Write("
The following errors occurred when changing your password:" & sError) end if Response.Write("
Change your password:") Response.Write(BuildForm("password","post")) Response.Write("
| Current Password | ") Response.Write("" & BuildInput("password","oldpassword","",12,12) & " |
| New Password | ") Response.Write("" & BuildInput("password","newpassword","",12,12) & " |
| Confirm New Password | ") Response.Write("" & BuildInput("password","newpassword2","",12,12) & " |
| ") Response.Write(BuildInput("submit","action",ACTION,"","") & " | |
| User Name | ") Response.Write("" & BuildInput("text","u_name",escapeString(oRS.fields("u_name")),20,50) & " |
| Password | ") Response.Write("" & BuildInput("text","u_password",oRS.fields("u_password"),12,12) & " |
| First Name | ") Response.Write("" & BuildInput("text","u_firstname",escapeString(oRS.fields("u_firstname")) ,20,50) & " |
| Last Name | ") Response.Write("" & BuildInput("text","u_lastname",escapeString(oRS.fields("u_lastname")),2 0,50) & " |
| Email Name | ") Response.Write("" & BuildInput("text","u_email",escapeString(oRS.fields("u_email")),20,50) & " |
| " Response.Write(BuildInput("submit","action",ACTIONSAVE,"","") & " | |
- ") while not oRS.eof Response.Write("
- " & oRS.fields("u_name")) oRS.movenext wend Response.Write("
user id plus the appropriate form submission, the user's data will be updated and the form will be redisplayed. All of this is handled by the large if-then blocks: if Request.Form("action")=ACTIONSAVE then ' Save user information end if if Request.QueryString("u_id")<>"" then ' Output form else ' Output list of users end if Next we wrap up our introduction to adding site membership and explain how to download and install the demonstration
Conclusion and Download Files You have seen how to add a site membership table to your web-site. By itself, this membership table is not very useful. However, by combining the site membership table with additional features you can create a rich, personalized experience. For example, in the t_user table every user has a unique id. This id serves as the user's identifier throughout the system. For example, in a message board, this id is used to associate a message post with a particular user. For our next article, we plan on extending the concepts and database introduced in this to create a simple message board.
Installing the Demo This demonstration requires a Window's NT machine with IIS 4.0 and SQL Server. To install this demonstration, you need to download membership.zip and decompress all the files and directories into your web-sites root directory. Once the files are in your root directory, you need to create your SQL Server database. We built this demonstration using SQL Server 6.5 but there is no reason it should not run with SQL Server 7.0. To make it easy to create the database, you will find a file called membership.sql that contains all the SQL commands necessary to create the tables and stored procedures. You just need to execute this SQL file against a new SQL Server database. (In SQL Server 6.5, after creating the database, open the SQL Enterprise Manager, select the database, choose SQL Query Tool from the Tools menu and open and run the membership.sql file). As we said in the beginning of this article, by itself this code is not intended to serve as the foundation for a user membership system. Before introducing this into a production system you need to evaluate the performance, security and error handling of the scripts. We permit you to reuse these scripts as a learning device. To reuse this work in a production system, we ask that you make available to us for possible publishing any updates or changes you make to the database or interactions with the database. This includes any extensions built into the system (eg., adding message boards, etc). THE END Thanks
Related Documents
Building An Online Asp Based Discussion Forum
June 2020 1
Case Based Discussion Psikotik.docx
November 2019 15
Internet Based Discussion Form.docx
July 2020 7
Case Based Discussion Dimuthimami.docx
June 2020 6