Database Reference
In-Depth Information
you serve their ads. To do so, you must count the number of accesses for each. The
technique shown in this section can be adapted for all such purposes.
There are several methods for writing a page that displays a count of the number of
times it has been accessed. The most basic is to maintain the count in a file. For each
page request, open the file, read the count, increment it, write the new count to the file,
and display it in the page. This is easy to implement but requires a counter file for each
page that includes a hit count. It also doesn't work properly if two clients access the page
at the same time, unless you implement some kind of locking protocol in the file-access
procedure. It's possible to reduce counter file litter by keeping multiple counts in a single
file, but that makes it more difficult to access particular values within the file, and it
doesn't solve the simultaneous-access problem. In fact, the problem is worse because a
multiple-counter file has a higher likelihood of being accessed by multiple clients si‐
multaneously than does a single-counter file. So you end up implementing storage and
retrieval methods for processing the file contents, and locking protocols to keep multiple
processes from interfering with each other. Hmm... those sound suspiciously like the
problems that a database management system such as MySQL already takes care of !
Keeping the counts in the database centralizes them into a single table, SQL provides
the storage and retrieval interface, and the locking problem goes away because MySQL
serializes access to the table so that clients can't interfere with each other. Furthermore,
depending on how you manage the counters, you might be able to update the counter
and retrieve the new sequence value using a single statement.
Assume that you want to log hits for more than one page. To do that, create a table that
has one row for each page to be counted. This necessitates a unique identifier for each
page, so that counters for different pages don't get mixed up. Each page's path within
your web tree is unique, so just use that. (Web programming languages typically make
this path easy to obtain, as discussed in
Recipe 20.1
.) On that basis, create a
hitcount
table as follows:
CREATE
TABLE
hitcount
(
path
VARCHAR
(
255
)
CHARACTER
SET
latin1
COLLATE
latin1_general_cs
NOT
NULL
,
hits
BIGINT
UNSIGNED
NOT
NULL
,
PRIMARY
KEY
(
path
)
);
This table definition involves some assumptions:
• The
path
column that stores page pathnames has a character set of
latin1
and a
case-sensitive collation of
latin1_general_cs
. Use of a case-sensitive collation is
appropriate for a web platform where pathnames are case sensitive, such as most
versions of Unix. For Windows or for HFS+ filesystems under Mac OS X, filenames
are not case sensitive, so you would choose a collation that is not case sensitive,
