Webchat with MySQL

We use the recipes of the preceding sections and the MySQL C API available through libmysqlclient to build a web chat with MySQL. We suggest you acquaint yourself with phpMyAdmin and the mysql command line interface in order to facilitate your work with MySQL.

We will work with a database that stores two types of information: session data such as the user’s name and how often her chat screen should be refreshed and the chat data, which consists of open and private channels that are protected by a password and the messages themselves.

The structure of the chat can be described by the three different screens it uses:

The login sequence is as follows: the user identifies herself in the login screen, which causes a new cookie, i.e. session identifier with a certain expiry time to be sent, selects or starts a new channel and starts using the chat through the main screen. The program must handle all of these steps. Furthermore, it must bracket its actions by a read and write of the session data, which it reads into a dictionary whose keys are the individual items.

5.4.2 MySQL tables used

There are three tables: session, channels and messages. The first holds session data. There is one entry for each session initiated by a client. The second, i.e. the table channels, records channel properties and the the third, messages, records the actual messages. We now discuss the fields of each channel in detail. Note that the string ’%d’ in the create statements is a printf-like placeholder that will be replaced by a constant.

CREATE TABLE session (
  sessid char(%d) NOT NULL,
  time BIGINT,
  login char(%d),
  chanid BIGINT,
  refresh INT,
  open ENUM(’Y’, ’N’),
  UNIQUE KEY (sessid),
  PRIMARY KEY (sessid)
)

CREATE TABLE channels (
  id BIGINT NOT NULL AUTO_INCREMENT,
  time BIGINT NOT NULL,
  name char(%d),
  password char(%d) BINARY,
  open ENUM(’Y’, ’N’),
  PRIMARY KEY (id)
)

CREATE TABLE messages (
  time BIGINT NOT NULL,
  login char(%d),
  chanid BIGINT,
  msg BLOB,
  PRIMARY KEY (time)
)

5.4.3 Preliminaries

There is a wrapper script that works like the wrappers in the recipes for working with cookies and form variables.

There is an important note concerning the makefile. We must make sure that the libmysqlclient library is linked after gnustep-base. The following makefile accomplishes this. (We have moved the code from the cookie and form recipes into the file CGIaux.m.)

5.4.4 Implementation I: auxiliary functions and definitions

Start by defining some constants to tune the behavior of the program. You are encouraged to experiment with these. The meaning of each constant follows.

#include <Foundation/Foundation.h>
#include <mysql/mysql.h>

#include ”CGIaux.h”

#define CHATCGI ”/cgi-bin/webchat.sh”

#define MYSQL_USER        ”chatuser”
#define MYSQL_PASSWORD    ”webchat”
#define CHATDB                ”chatdb”

#define LEN_LOGIN                 16
#define LEN_SESSID                16
#define LEN_CHANNEL              32
#define LEN_MSG                   256
#define LEN_PASSWORD             32

#define DISPLAY_MAX              10

#define SESSPERSIST           10*60
#define CHANPERSIST (SESSPERSIST+5*60)
#define MSGPERSIST (CHANPERSIST+5*60)

A series of useful macros follows. The macro MYSQL_ERR turns the most recent error message from MySQL into an NSString object. The macros FMT1 to FMT6 help simplify the use of strings obtained from a format string, which occurs a lot in this application.

#define MYSQL_ERR [NSString stringWithCString: \
                                      mysql_error(&mysql)]

#define FMT1(_f, _a1) \
       [NSString stringWithFormat:_f, _a1]
#define FMT2(_f, _a1, _a2) \
       [NSString stringWithFormat:_f, _a1, _a2]
#define FMT3(_f, _a1, _a2, _a3) \
       [NSString stringWithFormat:_f, _a1, _a2, _a3]
#define FMT4(_f, _a1, _a2, _a3, _a4) \
       [NSString stringWithFormat:_f, _a1, _a2, _a3, _a4]
#define FMT5(_f, _a1, _a2, _a3, _a4, _a5) \
       [NSString stringWithFormat:_f, _a1, _a2, _a3, _a4, _a5]
#define FMT6(_f, _a1, _a2, _a3, _a4, _a5, _a6) \
       [NSString stringWithFormat:_f, _a1, _a2, _a3, _a4, _a5, _a6]

We now define a set of auxiliary functions that we will use in the main procedure of the script. The function Empty tests whether a string object is empty, i.e. whether it is nil or has length zero.

BOOL inline Empty(NSString *str)
{
return (str==nil ∣∣ ![str length]);
}

There are peculiarities pertaining to the way messages are being stored and retrieved that have to be taken into account. A message may contain characters that we would have to escape before the message can be given to MySQL. Furthermore, what if a message contains HTML tags? These could hamper the functionality of the message display screen. We avoid all these problems by encoding each byte of the message in two hexadecimal digits and store this safe representation. We reconstruct the original byte ’B’ when we decode the message and output it as the HTML entity ’&#B;’, which assures that even HTML tags will be displayed literally (as opposed to being interpreted as markup).

The function EncodeMsg iterates over each byte of the string and uses the upper and lower four bits as an index into a string of hexadecimal digits. It appends the two digits to the result. We are done when we have reached the end of the string. Of course this doubles the length of the string that we will store.

NSString *EncodeMsg(NSString *msg)
{
    NSString *result = @””;
    const char *ptr = [msg cString],
        *xdigits = ”0123456789ABCDEF”;

    while(*ptr){
        unsigned char item = *ptr++;
        result = [result stringByAppendingFormat:@”%c%c”,
                          xdigits[item/16], xdigits[item%16]];
    }

    return result;
}

The function DecodeMsgIntoEntities also iterates over the individual bytes of its argument. It processes two bytes, i.e. digits, at a time and depends on digits and letters being in sequence in the ASCII string encoding. If it finds a decimal digit, then its value is given by the difference between the digit and the character ’0’; the value of a hexadecimal digit is ten plus the difference between the digit and the character ’A’. It remains to compute the reconstructed byte and append the appropriate HTML entity to the result.

NSString *DecodeMsgIntoEntities(NSString *msg)
{
    NSString *result = @””;
    const char *ptr = [msg cString];

    while(ptr[0] && ptr[1]){
        int upper = (isdigit(ptr[0]) ?
                      ptr[0]-’0’ : ptr[0]-’A’+10);
        int lower = (isdigit(ptr[1]) ?
                      ptr[1]-’0’ : ptr[1]-’A’+10);
        result = [result stringByAppendingFormat:@”&#%d;”,
                          upper*16+lower];
        ptr += 2;
    }

    return result;
}

The next two functions encapsulate two chunks of HTML that is output at several places in the program. The function Footer outputs a separator (line), followed by a link to the author’s home page, followed by the name of the program, the host name and the current date. All of these should make it easy for the user to locate the server that is currently being used.

NSString *Footer()
{
    NSProcessInfo *pinfo = [NSProcessInfo processInfo];

    NSString *fstr =
        FMT3(@”<HR>\n”
             @”<ADDRESS>%@_by_marko_riedel,_”
             @”<A_HREF=”
             @”http://www.geocities.com/markoriedelde>\n”
             @”http://www.geocities.com/markoriedelde</A>\n”
             @”at_%@,_%@</ADDRESS>\n”,
             [pinfo processName], [pinfo hostName],
             [[NSDate date] description]);

    return fstr;
}

There are several screens, including those for error messages, where the user should be given the chance to start over. This is what the output of the function BackToLogin does. It outputs a form containing a single button and an important hidden field: cmd, i.e. “command.” This is set to “restart” and lets the user restart the session. We shall see later how commands are processed. In fact “restart” is not the only command; there is also “display” and “enter,” which display the two frames of the main screen, respectively.

NSString *BackToLogin()
{
    NSString *btologin =
        @”<BR><FORM_TARGET=_top_METHOD=POST_”
        @”ACTION=webchat.sh>\n”
        @”<INPUT_TYPE=HIDDEN_NAME=cmd_VALUE=restart>\n”
        @”<INPUT_TYPE=SUBMIT_”
        @”VALUE=\”Back_to_login\”>\n”
        @”</FORM>\n”;

    return btologin;
}

The function ErrmsgAndExit plays an important role in the program. We do extensive error checking (quite possibly a bit more than necessary) and we need a function that outputs an error message and lets the user start over. This is the purpose of ErrmsgAndExit. It is invoked with a short description of the error and a string that gives the details of the error that occurred. It also outputs the restart button.

void ErrmsgAndExit(NSString *desc, NSString *detail)
{
    NSString *body =
        FMT5(@”<HTML><HEAD>\n”
             @”<TITLE>%@</TITLE>\n”
             @”</HEAD><BODY_BGCOLOR=white>\n”
             @”<H1>%@</H1>\n”
             @”%@\n%@\n%@\n”
             @”</BODY></HTML>\n”,
             desc, desc, detail,
             BackToLogin(), Footer());

    printf(”Content-type:_text/html\r\n\r\n”);
    printf(”%s”, [body cString]);

    exit(1);
}

One error condition that occurs quite frequently is when we expected an alphanumeric string and received something else, or that the length of the string is not what we require. The function CheckForAlNum tests for these conditions. First it verifies that the string has the right length and aborts with an error otherwise. Next it iterates over the characters that make up the string, testing each character with isalnum in turn. If it does not get to the terminating null byte then there was an illegal character. In this case it outputs a descriptive error message.

void CheckForAlNum(NSString *toCheck, NSString *desc,
                    int min, int max)
{
    int len = (toCheck==nil ? 0 : [toCheck length]);
    NSString *err, *aux;

    if(len<min ∣∣ len>max){
        err = FMT3(@”%@_empty_or_too_long;_”
                    @”min_%d,_max_%d_characters.”,
                    desc, min, max);
        aux = FMT1(@”Got_’%@’.”, toCheck);
        ErrmsgAndExit(err, aux);
    }

    const char *ptr = [toCheck cString], *base = ptr;
    while(isalnum(*ptr++));
    if(*(ptr-1)){
        err = FMT1(@”Illegal_character_”
                    @”in_%@;_must_be_alphanumeric.”, desc);
        aux = FMT2(@”Got_’&#%d’_”
                    @”at_position_%d.”,
                    (int)*(ptr-1), ptr-base);
        ErrmsgAndExit(err, aux);
    }
}

We now meet one of the most important functions of the program, i.e. OutputPageAndExit. Recall that we store the session data in the MySQL database. Whatever we do, the first step must be to read those session data into the session dictionary if present, and the last, to record the current contents of the dictionary. The read only occurs once, i.e. at the beginning of the program. The update may be invoked at different exit points of the program, which is why we put it into the function OutputPageAndExit.

There are two steps to this function: first, write the session data, and second, output the page and exit. The first step starts by building the MySQL query. It says to update the table session with the values from the session dictionary, which contains the session data that we retrieved from MySQL. Only the time is not set from a dictionary value. It is set to the current time, because that is the time of the most recent activity in the session.

void OutputPageAndExit(MYSQL mysql, NSMutableDictionary *session,
                        NSString *title, NSString *frames,
                        NSString *body, NSString *bodyargs)
{
    NSString *query =
        FMT6(@”UPDATE_session_SET_”
             @”time_=_’%lu’,_login=’%@’,_refresh=’%@’,_”
             @”chanid_=_’%@’,_open=’%@’_”
             @”WHERE_sessid=’%@’”,
             (long)[[NSDate date] timeIntervalSince1970],
             [session objectForKey:@”login”],
             [session objectForKey:@”refresh”],
             [session objectForKey:@”chanid”],
             [session objectForKey:@”open”],
             [session objectForKey:@”sessid”]);

The end of the first step is to actually do the query and generate an error message if it fails.

    if(mysql_query(&mysql, [query cString])){
        NSString *err =
            FMT1(@”Couldn’t_write_session_data:_%@”, query);
        ErrmsgAndExit(err, MYSQL_ERR);
    }

Step two is easy: output the page as described by the arguments. We include the footer. The argument bodyargs can be used to set BODY properties, such as an “onLoad” that starts a timer or sets the focus.

    printf(”Content-type:_text/html\r\n”);
    printf(”Pragma:_no-cache\r\n\r\n”);

    printf(”<HTML><HEAD><TITLE>%s</TITLE>\n%s</HEAD>\n”,
           [title cString], [frames cString]);
    printf(”<BODY%s>\n”, [bodyargs cString]);
    printf(”%s”, [body cString]);
    printf(”%s\n”, [Footer() cString]);
    printf(”</BODY></HTML>\n”);

    exit(0);
}

The function SetCookie is as important as OutputPageAndExit. There are three cases where it must be used. We must generate and output a new cookie if the script did not receive one, which signals the start of a new session. We should reset the expiry time of the cookie when the session is restarted. Finally, the expiry time must also be updated when the user enters a message, which indicates activity on the session.

The expiry time of the cookie should be SESSPERSIST seconds from now. We build the cookie dictionary with the expiry time, the path and most importantly, the session id, convert it to a string, then to a C string, and output the result.

5.4 Webchat with MySQL

5.4.1 Idea

5.4.2 MySQL tables used

5.4.3 Preliminaries

5.4.4 Implementation I: auxiliary functions and definitions