18.14 Web Page Access Logging
18.14.1 Problem
You want
to know more about a page than just the number of times
it's been accessed, such as the time of access and
the host from which the request originated.
18.14.2 Solution
Maintain a hit log rather than a simple counter.
18.14.3 Discussion
The hitcount table records only the count for each
page registered in it. If you want to record other information about
page access, use a different approach. Suppose you want to track the
client host and time of
access for each request. In this case, you need a log for each page
rather than just a count. But you can still maintain the counts by
using a multiple-column index that combines the page path and an
AUTO_INCREMENT sequence column:
CREATE TABLE hitlog
(
path VARCHAR(255) BINARY NOT NULL,
hits BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
t TIMESTAMP,
host VARCHAR(64),
PRIMARY KEY (path,hits)
);
To insert new records, use this query:
INSERT INTO hitlog (path, host) VALUES(path_val,host_val);
For example, in a
JSP page, hits can be logged like
this:
<c:set var="host">
<%= request.getRemoteHost ( ) %>
</c:set>
<c:if test="${empty host}">
<c:set var="host">
<%= request.getRemoteAddr ( ) %>
</c:set>
</c:if>
<c:if test="${empty host}">
<c:set var="host">
UNKNOWN
</c:set>
</c:if>
<sql:update dataSource="${conn}">
INSERT INTO hitlog (path, host) VALUES(?,?)
<sql:param><%= request.getRequestURI ( ) %></sql:param>
<sql:param value="${host}" />
</sql:update>
The hitlog table has the following useful
properties:
Access times are recorded automatically in the
TIMESTAMP column t when you
insert new records.
By linking the path column to an
AUTO_INCREMENT column hits, the
counter values for a given page path increment automatically whenever
you insert a new record for that path. The counters are maintained
separately for each distinct path value. (For more
information on how multiple-column sequences work, see Recipe 11.15.)
There's no need to check whether the counter for a
page already exists, because you insert a new row each time you
record a hit for a page, not just for the first hit.
If you want to determine the current counters for each page, select
the record for each distinct path value that has
the largest hits value:
SELECT path, MAX(hits) FROM hitlog GROUP BY path;
|
No comments:
Post a Comment