Creating a Web-based BBS, Part 1

For a period of time last year, the buzzword on the Web was “community”. Everyone wanted to build a virtual community, allowing people to interact on-line in much the same way as they interact in real life.

While virtual communities were (and are) overhyped, it is true that the Internet has produced a number of such on-line groups, many of whose members have never met in person. If you're reading this magazine, you probably participate in at least one e-mail list, chat system or Usenet newsgroup. Indeed, Linux would probably not be the success it is today were it not for communities of developers and users sharing information with each other via the Internet.

Several methods are available for creating an on-line community, beginning with the oldest and best-known, an e-mail list. Setting up a mailing list is relatively easy, and only minimal resources are necessary to keep a list running. Another popular option is a Usenet newsgroup, which uses a similar format but a different distribution mechanism than e-mail.

Still another option is a web-based bulletin board system. While such systems are neither as flexible nor as powerful as Usenet or e-mail lists, they do offer a number of advantages. They are resistant to spam, can be easily integrated into other aspects of a web site and give visitors to the web site a chance to participate in discussion without having to register. Many commercial web sites now offer bulletin boards for their users, in the hopes of turning their site into a truly interactive and two-way experience, rather than another distribution medium for their content.

Starting in this issue, we will take a three-part look at how to create a simple bulletin board system of our own. This project was suggested by reader Dwight Johnson and also influenced by my creation of an “At the Forge” home page that will include examples of the programs presented in these columns, as well as a central place for readers to discuss the programs.

This month, we will look at the basic guts of the bulletin board system to be used on the ATF site. As you will see, I have decided to keep the software and the BBS very simple, without certain advanced features such as hierarchies and threading. However, it should not be difficult to add these features to the software, or to use this as a base for a more advanced system. Next month, we will add enough features to make this a serviceable BBS. Finally, in the third part of this series, we will look at ways in which we can add a number of useful features to the system.

The first consideration is the look and feel of the BBS, since that will force our hand on a number of other issues. As I indicated above, it is my goal to keep this software as simple as possible. I decided to keep discussions in a non-hierarchical manner. Each message belongs to a single thread within the BBS. We will not keep track of replies or allow sub-threads. Messages within a thread will be presented in chronological order, from the newest message to the oldest.

The user will thus have several possible options at any given point: starting a new thread, posting a new message to an existing thread, listing the current threads, or looking through the messages in one thread.

While I briefly considered storing messages in ASCII text files, I quickly decided to use a relational database. A database makes it easier to handle future expansion, since more features can be provided by adding one or more columns to a table. Databases also free us from having to worry about file formats, locking and other problems which inevitably occur when we use ASCII text files.

My database of choice is the “mostly free” MySQL. The programs will be written in Perl and will use Perl's database interface, known as DBI. See the “Resources” sidebar for pointers to information about any or all of these.

If you have been following this column over the last few months, you may be surprised to see that I have implemented it using simple CGI programs. I could have used mod_perl, a module that embeds a Perl binary inside of the Apache HTTP server. I could also have used HTML::Embperl, the templating language we explored in this column's previous two installments.

However, reality is often the compelling factor and the web space provider I use has not yet installed mod_perl. These programs should run just fine under Apache::Registry, the modules for mod_perl that provide emulation of the CGI standard.

If we are going to store information in a relational database, the first technical decision involves the database itself. What information do we want to store, and how do we want to store it?

Because we are storing messages and threads, I designed the system with two tables, ATFThreads and ATFMessages. Each message, including information about the author and the posting date, is stored in ATFMessages. Each message in the table points to a single thread in ATFThreads, allowing us to sort messages by thread.

Here, for instance, is the definition of ATFThreads:

CREATE TABLE ATFThreads (
    id SMALLINT UNSIGNED AUTO_INCREMENT
        PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    author VARCHAR(60) NOT NULL,
    email VARCHAR(60) NOT NULL,
    text TEXT NOT NULL,
    date DATETIME NOT NULL,
    UNIQUE(subject)
 );

Each thread is stored in a single row of the database, uniquely identified by its id column, which we define to be a SMALLINT UNSIGNED. (We are thus allowed 65,535 different topics, which should suffice for now.) By declaring the column to be AUTO_INCREMENT, we are asking MySQL to give the id column a new value each time we insert a new row. By declaring it to be the PRIMARY KEY, we indicate that the id column will uniquely identify a row.

The other columns are fairly self-explanatory: subject contains the subject of the thread, while author and email contain the thread creator's name and e-mail address, respectively.

Each thread has an opening message that starts the discussion; it is stored in the text column in a column of type TEXT. TEXT fields can contain amounts of text larger than the 255-character maximum given to us by VARCHAR columns. VARCHAR columns are stripped of trailing whitespace, sparing us from at least one housekeeping chore when working with the database.

Finally, we give each thread a date column in which we record the creation date and time with a DATETIME element. We also ensure that the human-readable subject line for the thread is unique with the UNIQUE keyword at the end of the table definition. This prevents us from having two threads named “Problems with MySQL”, for example.

Now that we have seen how to create ATFThreads, we can define ATFMessages. The two are quite similar, the main difference being a reference to a thread ID:

CREATE TABLE ATFMessages (
    id MEDIUMINT UNSIGNED AUTO_INCREMENT
        PRIMARY KEY,
    thread SMALLINT UNSIGNED NOT NULL,
    subject VARCHAR(60) NOT NULL DEFAULT
        "No subject",
    date DATETIME NOT NULL,
    author VARCHAR(60) NOT NULL DEFAULT
        "Mr. Nobody",
    email VARCHAR(60) NOT NULL DEFAULT
        "atf@lerner.co.il",
    text TEXT NOT NULL
 );

Once again, we create a column with an auto-incrementing primary key named id. Different tables can have identically named keys just as different hashes can. If we are referring to both tables in a single query, we can distinguish between the two by using the table.column syntax, as in ATFMessages.id and ATFThreads.id.

Notice how we have used the DEFAULT keyword to assign default values to each of the elements. Truth be told, the way the database-handling programs are written makes it unlikely we will ever see these defaults. (Empty strings are passed to the database as empty strings, rather than as NULL values. To get a true NULL, we must pass an undefined scalar.) However, it is always a good idea to build multiple checks into your programs just in case one of the other levels does not work in the way you expected. This can also help us track down problems; if we notice that many users are identified as “Mr. Nobody”, we can assume something has gone wrong with our posting software.

We can create the tables by entering the above SQL commands at the interactive mysql prompt. Once they have been created, we are ready to start working on the programs.