Recipe 19.13. Web Page Access Logging

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.

Solution

Maintain a hit log rather than a simple counter.

Discussion

The hitcount table used in Section 19.12 records only the access count for each page registered in it. If you want to record other information about page access, use a different approach. Suppose that you want to track the client host and time of access for each request. In this case, you need to log a row for each page access 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)           CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,   hits  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   t     TIMESTAMP,   host  VARCHAR(255),   INDEX (path,hits) ) ENGINE = MyISAM; 

See Section 19.12 for notes on choosing the character set and collation for the path column.

To insert new rows into the hitlog table, use this statement:

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 rows.

• 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 row for that path. The counters are maintained separately for each distinct path value. This counting mechanism requires that you use the MyISAM (or BDB) storage engine, which is why the table definition includes an explicit ENGINE = MyISAM clause. (For more information on how multiple-column sequences work, see Section 11.11.)

• 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.

• To determine the current counter value for each page, select the row for each distinct path value that has the largest hits value:

  SELECT path, MAX(hits) FROM hitlog GROUP BY path; 

  
  

  To determine the counter for a given page, use this statement:

  SELECT MAX(hits) FROM hitlog WHERE path = 'path_name';