|
Show Contents Previous Page Next Page
Chapter 5 - Maintaining State
Storing State Information in SQL Databases
In this section...
|
Persistent memory is only suitable for storing small amounts of state information for relatively short periods of time. If you need to reliably store lots of information for a long time, you need a server-side database.
The DBI library, designed by Tim Bunce and others, is a generic Perl interface to relational database management systems (DBMSs) that speak SQL (Standard Query Language). The DBI library speaks to specific databases by way of DBD (Database Driver) modules. You can make queries on any database that has a DBD module available for it. These modules are sometimes provided by the database vendor and sometimes by third parties. DBD modules for Oracle, Sybase, Illustra, mSQL, MySQL, and others can be found at CPAN.
Full information on using DBI can be found in its manual pages and in Advanced Perl Programming by Sriram Srinivasan (O'Reilly & Associates, 1997). We'll summarize just enough here so that you can follow the examples if you're not already familiar with DBI.
Before you can work with the DBI interface, you must select and install a relational database. If you have access to a Unix system and do not already have such a database installed, a good one to start with is MySQL, a popular database management system that you can freely download from http://www.tcx.se/.7
In relational databases, all information is organized in tables. Each row of the table is a data record, and each column is a field of the record. For example, here is one way to represent the hangman data:
table: hangman
+----------+--------+-------+------+---+------------+-----+--------------+
|session_id| WORD |GUESSED|GAMENO|WON|GUESSES_LEFT|TOTAL| modified|
+----------+--------+-------+------+---+------------+-----+--------------+
|fd2c95dd1 |entice |e | 10| 6| 6| 34|19980623195601|
|97aff0de2 |bifocals|aeilort| 4| 2| 3| 20|19980623221335|
+----------+--------+-------+------+---+------------+-----+--------------+
Most of the columns in the table above directly correspond to the fields in the now-familiar hangman state object. In addition to these fields we add two more columns. session_id is a string that uniquely identifies each user session and is used as a key into the table for fast record lookup. For reasons that will become apparent soon, we use a short hexadecimal string as the session ID. We also add a timestamp field named modified which holds the date and time at which the record was last changed. If you look carefully, you'll see that the column consists of the four-digit year and two digits each for the month, day, hour, minute, and second. This timestamp will come in handy for detecting old unused sessions and clearing them out periodically.
In SQL databases, each table column has a defined data type and a maximum field length. Available data types include integers, floating point numbers, character strings, date/time types, and sometimes more esoteric types. Unfortunately the data types supported by database management systems vary considerably, limiting the portability of applications among different vendors' products. In this and the next chapter, our examples use MySQL data types and functions. You may have to make some modifications in order to support another database system.
The most basic way to communicate with a SQL database is via a text monitor--a small terminal-like application in which you type SQL queries to the database and view the results. To create the definition for the table shown above, you could issue the SQL CREATE command:
mysql> CREATE TABLE hangman (
session_id char(8) primary key,
WORD char(30),
GUESSED char(26),
GAMENO int,
WON int,
GUESSES_LEFT int,
TOTAL int,
modified timestamp
);
This declares a table named hangman using the MySQL syntax. The session_id column is declared to be a string of at most eight characters, and it is also declared to be the primary key for the table. This ensures that a given session ID is unique, and speeds up table lookups considerably. The WORD and GUESSED columns are declared to be strings of at most 30 and 26 characters, respectively, and GAMENO, WON, GUESSES_LEFT, and TOTAL are declared to be integers (using the default length). We declare the column named modified to be a timestamp, taking advantage of a MySQL-specific feature that updates the field automatically whenever the record that contains it is changed.
You can then load some sample data into the database using a SQL INSERT statement:
mysql> INSERT INTO hangman (session_id,WORD,GUESSED,GAMENO,WON,
GUESSES_LEFT,TOTAL)
VALUES ('a0000001', 'spruce', '',1,0,6,0);
This inserts the indicated values for the columns session_id through TOTAL. We don't explicitly set the value of the modified column because MySQL takes care of that for us.
We can now perform some queries over the database using the SQL SELECT statement.
To see everything in the hangman table:
mysql> SELECT * FROM hangman;
+----------+--------+-------+------+---+------------+-----+--------------+
|session_id| WORD |GUESSED|GAMENO|WON|GUESSES_LEFT|TOTAL| modified|
+----------+--------+-------+------+---+------------+-----+--------------+
|fd2c95dd1 |entice |e | 10| 6| 6| 34|19980623195601|
|a0000001 |spruce | | 1| 0| 6| 0|19980625101526|
|97aff0de2 |bifocals|aeilort| 4| 2| 3| 20|19980623221335|
+----------+--------+-------+------+---+------------+-----+--------------+
The part of the query following the SELECT command chooses which columns to display. In this case we use * to indicate all columns. The FROM keyword names the table to select the data from.
If we wished to look at just the session_id, WORD, and GAMENO fields from the table, we could use this query:
mysql> SELECT session_id,WORD,GAMENO FROM hangman;
+------------+----------+--------+
| session_id | WORD | GAMENO |
+------------+----------+--------+
| fd2c95dd | entice | 10 |
| a0000001 | spruce | 1 |
| 97aff0de | bifocals | 4 |
+------------+----------+--------+
An optional WHERE clause allows us to filter the records so that only records matching a set of criteria are displayed. For example, this query shows only session records from players who have played five games or more:
mysql> SELECT session_id,WORD,GAMENO FROM hangman WHERE GAMENO >= 5;
+------------+--------+--------+
| session_id | WORD | GAMENO |
+------------+--------+--------+
| fd2c95dd | entice | 10 |
+------------+--------+--------+
This query retrieves the session with the ID a0000001:
mysql> SELECT session_id,WORD,GAMENO FROM hangman WHERE session_id='a0000001';
+------------+--------+--------+
| session_id | WORD | GAMENO |
+------------+--------+--------+
| a0000001 | spruce | 1 |
+------------+--------+--------+
Finally, this query retrieves all sessions that were modified within the past 24 hours:
mysql> SELECT session_id,WORD,GAMENO FROM hangman
WHERE unix_timestamp()-unix_timestamp(modified) < 60*60*24;
+------------+--------+--------+
| session_id | WORD | GAMENO |
+------------+--------+--------+
| a0000001 | spruce | 1 |
+------------+--------+--------+
The last example shows the use of the MySQL-specific unix_timestamp() function. Called without arguments, unix_timestamp() returns the current time and date as the number of seconds since the start of the Unix epoch. The function can also be called with a timestamp field as the argument, in which case it operates on the timestamp rather than the current time. The effect of the query above is to subtract the modified field from the current time and compare the difference to one day. The SQL language allows you to form queries that are substantially more complex than these, including ones that combine the results of multiple tables. We won't delve into the full SQL syntax, but you'll find the definitive reference in A Guide to the SQL Standard by C. J. Date with Hugh Darwen (Addison-Wesley, 1997), and plenty of practical examples in Advanced Perl Programming by Sriram Srinivasan.
The INSERT statement can only be used to create a new record (or row) of the table. If we were to try to execute the insertion statement shown earlier a second time, the attempt would fail because any given session ID can only occur once in the table. This feature guarantees the uniqueness of session IDs. To change the values in an existing record, we would use an UPDATE statement instead. A typical UPDATE statement looks like this:
mysql> UPDATE hangman SET GAMENO=GAMENO+1
WHERE session_id='a0000001';
Query OK, 1 row affected (0.09 sec)
Like the SELECT statement, UPDATE can have a WHERE clause which limits what records it affects. For each selected record, columns are updated according to one or more column=newvalue pairs. In the example shown above, we're incrementing the GAMENO column by one. A SELECT statement shows that the update worked.
mysql> SELECT session_id,WORD,GAMENO FROM hangman
WHERE session_id='a0000001';
+------------+--------+--------+
| session_id | WORD | GAMENO |
+------------+--------+--------+
| a0000001 | spruce | 2 |
+------------+--------+--------+
Lastly, the DELETE statement can be used to delete all records that satisfy the criteria set out in the WHERE clause. This query deletes all sessions older than a day:
mysql> DELETE FROM hangman
WHERE unix_timestamp()-unix_timestamp(modified)>60*60*24;
Query OK, 2 rows affected (0.00 sec)
If you forget to include a WHERE clause in the UPDATE and DELETE statements, every record in the database will be affected by the operation. This is generally to be avoided.
Show Contents Go to Top Previous Page Next PageCopyright © 1999 by O'Reilly & Associates, Inc.
