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