by Marko Riedel
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:
There are a variety of error message screens.
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.
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.
Fields of the table session:
CREATE TABLE session (
sessid char(%d) NOT NULL,
open ENUM(’Y’, ’N’),
UNIQUE KEY (sessid),
PRIMARY KEY (sessid)
Fields of the table channels:
CREATE TABLE channels (
id BIGINT NOT NULL AUTO_INCREMENT,
time BIGINT NOT NULL,
password char(%d) BINARY,
open ENUM(’Y’, ’N’),
PRIMARY KEY (id)
Fields of the table messages:
CREATE TABLE messages (
time BIGINT NOT NULL,
PRIMARY KEY (time)
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.)
webchat_OBJC_FILES = webchat.m CGIaux.m
TARGET_SYSTEM_LIBS += -lmysqlclient
SHARED_CFLAGS += -g
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The end of the first step is to actually do the query and generate an error message if it fails.
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.
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.
It remains to discuss two auxiliary functions that encapsulate MySQL queries. The first of these, FirstFieldsFromResult, takes a MySQL result consisting of some number of rows, of which we are only interested in the first field. It produces an array of those fields. It fetches the rows in turn, converts the C string in the first field to an NSString and stores the latter in an array, which it returns when it has processed all the rows.
The second auxiliary function for use with MySQL is RowsFromQuery, which also processes rows resulting from a query. It returns an array of dictionaries, one dictionary per row. The keys of the dictionary are the names of the fields, and the values are the actual data. First we make the query and signal any errors that may have occurred.
Next we obtain the query result so that we may fetch the rows, again checking for possible errors.
We need the field names, which are the keys to the row dictionaries and need only be fetched once. We obtain the number of fields and a pointer to the fields themselves. We convert each field name to an NSString and add it to the array of field names. The name of an element at position k of a row is given by the name at position k in the array of field names.
We can process the rows now that we have the field names. We fetch the total number of rows and initialize a mutable array having this capacity. It will hold dictionaries.
Now iterate over the rows. Create a dictionary with the right capacity for each row. Then iterate over the fields, storing them as NSString objects. The key is obtained from the corresponding entry in the array of field names. Add the row dictionary to the data array once all the fields have been processed.
It remains to free the MySQL result and return the array of dictionaries.
This is the end of the first section.
We are now ready to discuss the function main, which does the actual work of generating the different screens and interacting with the database. We declare commonly used variables that hold a MySQL query, an error message and an error message description, repectively. The first thing to do is to initialize the MySQL client library and connect to the local host as user MYSQL_USER, with the password MYSQL_PASSWORD. We send any errors to the client, which is how all errors will be handled.
We ask for the list of databases that match the regular expression CHATDB and process any errors. We convert the result into an array of database names and free the MySQL result.
We have a problem if there was no database that matched the pattern. We try to create it if this is the case and abort with an error message if the create query failed.
We can select the chat database once we know that it exists.
We need to verify that the three tables session, channel and messages are present. Hence we ask MySQL for a list of tables. We may use FirstFieldsFromResult because every row of the result contains exactly one field.
What follows is a very important declaration. We declare an array that holds the names of the tables that must be present, and a second array containing CREATE statements for each table for use if the table is not present. The third array holds the maximum age of an entry for each table. These three arrays use the same order.
We iterate over these three arrays in parallel. If a table is not present in the list of tables that we obtained from MySQL, then it must be created, which we immediately try to do, checking for errors as we go.
The second half of the loop does what we may call house-keeping. It erases records that are too old. Errors are sent to the client. (We probably should have put the DELETE in an else clause, because there is nothing to delete from a new table, although it can do no harm.)
We are now ready to do some actual processing. We parse any GET or POST variables that were passed to the script. We also parse the cookie, if there was one, and look up the session id in the cookie dictionary.
If there was no session id in the cookie then we are either starting a new session or we are being called to display a portion of the main screen, but the cookie has expired. We send an explanatory error message in the latter case.
If there was no timeout but there is no session id, then we must generate and initialize a new one that should preferably not be easily guessable, so that no user can join someone else’s session by guessing an actual value of a session id that is in use and sending it as a cookie. We seed the random number generator and generate each character of the key in turn, asking for a random number from the interval [0,26 + 26 + 10 - 1]. Values from [0,26 - 1] yield lower case letters, values from [26,26 + 26 - 1], upper case letters and values from [26 + 26,26 + 26 + 10 - 1], digits. We append the character obtained in this manner to the session id.
But it is not enough merely to generate the key, we must also store it in the sessions table and initialize the remaining fields of the session. We use an INSERT query for this purpose. It stores the session id and the current time, leaving the other fields empty. Errors are sent to the client; e.g. the query will fail in the admittedly unlikely case that we generated a session id that is already in the table, because we declared the corresponding field to be unique. We are done initializing once we output the cookie for storage in the client with the session id using Set-Cookie.
We may now be certain that we have a valid session id, even if it has only just been generated. Hence we may read the session data into a dictionary. This is done with a SELECT query that selects all fields from those records that match the session id. There should be exactly one such record; everything else is an error. We extract the session dictionary.
We must respond to the command “restart” before we do any additional processing. We clear all fields in the session dictionary and output the cookie, which will now last an additional SESSPERSIST seconds. Remember that the session time stamp will be set by OutputPageAndExit.
We have now come to the point where we output the first of our several screens, which is the login screen. We output the login form if there is no user name in the session dictionary and we did not receive one via a GET or POST. The form contains a table with three rows, i.e. name, refresh and session type. There is a submit button.
The remaining screens require a time stamp, so we set it here for use in the rest of the program.
The first case was an empty session and no CGI variables, which lead to the login screen. The second case is an empty session and the appropriate CGI variables. We set the flag readParams in this case and copy the variables from the CGI dictionary to the session dictionary.
We may now read the settings that correspond to the data obtained from the login screen.
There is error checking to do if we obtained those settings from the CGI dictionary. We verify that the login name is not empty and consists of alphanumeric characters only. The refresh should be set to a positive interval.
We now have the data that correspond to the login screen. The channel screen is next. We generate this screen if the channel id has not been set and is not among the CGI parameters.
We will generate the output line by line or item per item in all cases that follow and only join those lines when everything has been generated. We do not send HTML as it is created. This is so that there is no mixing between regular output and error messages.
There are two cases that correspond to open and private channels. Both contain forms. They have in common that the first form contains a text input where the name of the channel may be entered. This can be used to select an existing channel or to create a new one.
Now is where the two cases (open vs. private) diverge. We output a password field when the user has selected private channels. The submit button is again common to both cases.
We wish to present a menu of currently open channels when the user has selected open channels. We find those channels with a SELECT query. We group the messages by channel id and select the channel name, the number of messages and the newest time stamp from the table channels, which is updated every time a message is recorded. The where clause binds the channel name from the table channels to the channel id from the table messages. The result is a set of rows with three fields, that hold the channel name, the number of messages for the channel, and the newest message time stamp for the channel. We must check the open property so that our menu will not display private channels. We let MySQL do the sorting. The result will list channels according to their time stamps, with the channel that most recently had any activity first.
We output the channel menu as a table, iterating over the channels with the enumerator.
Every entry in the menu is a form that contains a single button, which selects the respective channel. We must convert the time stamp into a readable date format, which we do with NSDate’s description. The number of messages is given by the appropriate field from the query.
An entry in the menu has three fields: the submit button, the time stamp, and the number of messages. The submit button is labeled with the name of the channel. This completes the form for the current entry, which we also could have put inside a single “TD” item rather than wrapping the entire row in the form.
The special case of the open chanel menu ends with a closing tag for the table. The last step is to output everything and exit the program. This step is again common to open and private channels.
If we have got this far, but do not have a channel id, then the user must have submitted a channel name and possibly a password using precisely the form whose generation we just described. Hence we extract the channel name and the password form the dictionary of CGI parameters. There are two cases: either the channel exists already, and we select it, otherwise, we create a new channel and select it. (We pre-declare some variables that will hold the dictionary for the current channel and its name.)
We will only accept channel names and passwords that are alphanumeric and have the right length.
We need to know whether this is a new channel or not, so we send the appropriate query to MySQL, using the channel name to select matching channels. We have a duplicate channel if we get more than one record with this name. (This should not happen.)
If we obtained a single record, then the user is trying to join an existing channel, in which case we must verify that the user’s choice of an open or private channel matches the setting in the record for the selected channel and signal an error otherwise.
We have a new channel if we did not obtain any records from the SELECT query. We build an INSERT query that contains the time stamp, the name, the open flag and the encrypted password. (The salt will be stored in the first two characters of the output from ENCRYPT.)
The channel was created if the query succeeded. We can set the channel name to the candidate value. We must ask MySQL for the channel id that it created. (Recall that AUTO_INCREMENT was set.)
At this point we can be certain of having a valid channel id and a channel name. It remains to check the password in the case of a logon to a private channel that wasn’t just created (chanCount==1). We ask MySQL for the id of any channel whose encrypted password matches the result of encrypting the user-supplied password, with the first two characters of the stored password being the salt.
The query must have succeeded and there must be a result set for us to process if we are to proceed. We could have used RowsFromQuery, except that we are not interested in the particular record, but rather in the number of records. There should be a single record.
We obtain the number of rows. There will be no rows if the password was wrong and one row otherwise. We send an error (“Permission denied.”) to the client if that was the case. This concludes the processing associated to channel selection.
The following else clause deals with the case where we had a valid channel id in the session dictionary. We need the channel’s name rather than its id for the display frame, which should not only display messages, but also indicate what user is viewing what channel. Hence we build a query that will yield the channel’s name and execute it.
There is something wrong if we obtained no entry or more than one entry. We set the channel name if everything was okay.
If we got this far in the program, then the session must already be fully initialized: we have a user name, a refresh setting, an “open” flag and a channel id and a channel name. We should output either the main chat screen (frame set), the upper frame (message display) or the lower frame (message entry form). What exactly we should do is determined by the parameter “cmd.” It is either “display” or “enter” or nil. Anything else is an error. We start by responding to the display command. We select the message text and the author’s name from the database. We again let MySQL do the sorting.
The messages go into a HTML table. We build the table line by line, storing lines in the array msgOutput. We obtain an iterator for the set of messages and loop over it.
The content of the message as retrieved from MySQL consists of a string of hexdecimal values, which we decode into HTML entities. There is one row with two columns for each message. The first column holds the name of the author and the second the message itself. We store the string for the current row in the output array.
We close the table once we have iterated over all messages and produce a single string by joining the output lines, separating them from each other with a newline character. We are now done processing the “display” command and may output the page.
The second command that we must implement is the command “enter,” which records a message and update’s the channel’s time stamp. There are two steps. If there is a message among the CGI parameters, then we must record it. We must also output a message submit form thereafter, regardless of whether there was a message or not.
In case of a message we encode it as a string of hexadecimal values first. Then we build the query, which records the time, the author, the channel id and the text of the message.
The query must succeed or we output an error message.
The process of recording a message includes an update of the channel’s time stamp to indicate that there has been activity on the channel. Recall that this time stamps determines how long the channel will stay in the database and where on the channel menu it will be displayed if it is an open channel. The query is simple: enter the time stamp in the record with the appropriate channel id.
This query, too, must succeed, or we signal an error.
We must reset the cookie’s expiry date because an enter signals that there has been activity for the current session. We invoke SetCookie for this purpose, and conclude by outputting the page.
We have now responded to all possible commands. Any other command is an error.
The final section of the program is for the case when all session variables were set, but no command was received. This is the default case and it indicates that we should display the main screen with its two frames. The frame set allocates 65% of the browser’s height to the upper frame, where messages are displayed, and 35% to the lower frame, where messages are entered. The request method by which commands are transmitted is GET. We can output the page once the arguments for OutputPageAndExit have been initialized. The exit statement at the end of the program is not reached.
This concludes the webchat recipe. It is based on a PHP webchat that I wrote some years ago, which in turn was inspired by a script I received from Igor Gilitschenski in July 2000.