6.10 Activity-Level Logging

6.10.1 Problem

You want to implement a global logging mechanism that can be used by an application to record arbitrary events. Instead of each event representing a change to a specific database row, each of these events will represent some high-level action taken by a user.

6.10.2 Solution

To create a global logging mechanism for use in tracking high-level activities, perform the following steps:

6.10.2.1 Step 1: Create a global log table

The first step is to create a global log table to record events as they occur. The log table should not be linked to any one base table. Instead, it should be flexible enough to record events involving any table, or set of tables, in the database. The following table shows one possible implementation of a log table for recording activities:

CREATE TABLE EventLog (
   EventId INTEGER IDENTITY,
   CodeId INTEGER NOT NULL,
   IdPointer VARCHAR(40) NULL,
   IdPointerType CHAR(1) NULL,
   EventMessage VARCHAR(255) NULL,
   PRIMARY KEY(EventId)
)

The EventLog table's IdPointer and IdPointerType columns are used to generically point to an important row in a table being modified by the activity being logged. The IdPointer column is used to record primary key values, while the IdPointerType column identifies the table containing the row corresponding to the given primary key. For example, if you want an event-log entry to point to an order record, you would insert the order ID value into the IdPointer column and an 'O' into the IdPointerType column. On the other hand, if you want to associate an event with an invoice, you would store the invoice ID into the IdPointer column and an 'I' into the IdPointerType column.

6.10.2.2 Step 2: Create an event master table

The next step is create an event master table that defines all possible events that can be logged. The EventLogMaster table shown in the following example represents one possible implementation that you can use:

CREATE TABLE EventLogMaster (
   CodeID INTEGER NOT NULL,
   MessageText VARCHAR(255),
   Severity INTEGER,
   LangId CHAR(1),
   PRIMARY KEY(codeID,langID)
)

This particular event master table allows you to define event messages in more than one language. The LangId column identifies the language used for the message text and it forms part of the table's primary key. The Severity column stores the security level associated with an event. As you'll see later, the code in this recipe allows you to limit the recording of events by severity level.

6.10.2.3 Step 3: Create a global severity-limit variable

Your next task is to implement a mechanism allowing you to globally define the minimum severity level that you are interested in logging. Only events with a severity level equal to or higher than the one you define will be logged to the EventLog table.

If your database system supports global variables, use them. Declare a global variable, and set it to whatever severity level you wish. By adjusting this severity level up and down, you can dynamically control the level of detail recorded in your EventLog table. This can be an invaluable debugging aid. If your database system doesn't support global variables, you can use a small table to record the current severity-limit setting. This is the solution used in the code for this particular recipe. For example:

CREATE TABLE GlobalVariables(
   VarName VARCHAR(10), 
   VarValue INT)

INSERT INTO GlobalVariables (VarName, VarValue) VALUES ('Severity',3)

This table contains rows that you can think of as corresponding to global variables. The VarName column identifies a global variable name, and the VarValue column contains the value for that variable. In this case, the global variable name is "Severity," and the value has been set to 3.

6.10.2.4 Step 4: Create a stored procedure to log events

Finally, you need to create a stored procedure that you can use to log an event. Call the procedure whenever you want to record an event. The procedure created by this recipe is named EventMessage. It allows you to pass several values as parameters. Values that you can pass to the procedure include: the error-message identifier, a pointer to a database row together with a code identifying the type of row, and an arbitrary event message. The @EventMessage parameter stores the event message, which is a generic ASCII string you build that contains data specific to a particular event. Here is the code for the EventMessage procedure:

CREATE PROCEDURE EventMessage
   @CodeID INTEGER,
   @IdPointer VARCHAR(40)=null,
   @IdPointerType VARCHAR(1)=null,
   @EventMessage VARCHAR(255) = null
   
AS
   DECLARE @Severity INTEGER
   DECLARE @SevPerm INTEGER

   SELECT 
      @Severity=MAX(Severity) 
   FROM EventLogMaster 
   WHERE CodeID=@CodeID

   SELECT 
      @SevPerm=VarValue 
   FROM GlobalVariables 
   WHERE VarName='Severity'

   IF @Severity>=@SevPerm        
      INSERT INTO EventLog(CodeId, IdPointer,
         IdPointerType, EventMessage) 
      VALUES(@CodeID, @IdPointer, @IdPointerType, 
         @EventMessage)

Once you create the EventMessage procedure, you can invoke it from key locations in your application code. Events will then be logged as allowed by the current severity-level setting.

6.10.3 Discussion

The solution in this recipe uses a global list of possible event messages that are identified by an ID number. This ID number corresponds to the CodeID column in the EventLogMaster table. Each message has a severity level indicating its importance. When an error or important event is detected, your code can call the EventMessage procedure and pass in the appropriate CodeID value. The event will then be logged, as long as the severity level permits it.

If the event relates to a row in a table, that row can be identified by passing in the row's primary key using the @IdPointer variable. You would also need to identify the table by passing in an appropriate table code via the @IdPointerType parameter. This general framework allows you to reference different tables from the same log.

You also have the option of recording a specific message for each event. This message is passed to the procedure via the @EventMessage parameter and will be stored in the EventMessage column of the EventLog table. You can build the message using string concatenation to hold whatever values you believe are relevant for each particular event.

The EventLogMaster and EventLog tables can easily be extended to include system information such as a timestamp, the CURRENT_USER, or a detailed explanation of the cause for an event. The basic framework shown here provides you with a great deal of flexibility.

6.10.3.1 Defining severity levels

The current severity level is checked in the EventMessage procedure every time it is called. If the severity limit for a message is less than the current global severity level, this indicates that the message should be logged, and the EventMessage procedure will then record the event in the EventLog table. Messages with severity limits above the current global security level are not logged. This is useful for debugging purposes, because you can enable or disable the logging of certain events just by changing the global severity level. You could, for example, set the global severity level to a high value for normal operations. This would prevent any events from being logged. Then, if a user reported problems, you could decrease the global severity level, thereby dynamically enabling logging of the events needed to resolve the user's problem.

The number and type of severity levels can vary for each system that you design. Some systems use eight or more severity levels, some use fewer. Table 6-2 shows one possible severity-level classification scheme that you might use.

Table 6-2. Possible severity-level classifactions

Severity	Level	Description
0	Debug	An event logged only for debugging purposes.
1	Regular	A regular event.
2	Notice	A regular, but significant event.
3	Warning	A warning event, indicating a possible problem.
4	Error	An error event representing an error that the system is designed to handle gracefully.
5	Critical	An error event that the system is not designed to handle and that, therefore, represents a critical error.

In a development environment, you may want to have the debug level enabled, so that you can get the maximum amount of detail for use in resolving bugs. In a production environment, on the other hand, you may want to log only warning or higher events.

6.10.3.2 Extending error messages

With support of the string-manipulation functions, it is possible to create generic event messages that combine several values in a user-friendly manner. For example, assume that you have defined a message that records the purchase of a product. The generic message text in the EventLogMaster table might be "Purchase of product" or something similar. To record a purchase, and to record along with it the number and type of objects purchased, you could invoke the EventMessage procedure as follows:

EventMessage 456,'T357','O','Bananas 10'

In this example, the procedure was called for message number 456, which indicates that product was purchased. The next two parameters identify order #T357 as the record affected by this event. The last parameter indicates that order #T357 was for the purchase of 10 bananas.

6.10.3.3 Reporting

To report on the activity log described in this recipe, you need to join the EventLog and EventLogMaster tables together. The EventLog table records the specific events that occurred, while the EventLogMaster table records the text messages corresponding to those events. Remember that the language ID needs to be part of your query. For example:

SELECT EventId, MessageText, EventMessage 
FROM EventLog l JOIN EventLogMaster m 
ON l.CodeId=m.CodeID 
WHERE LangId='E'

A possible extension of this activity-logging technique is changing the message-text format to allow inline message expansion. Instead of "Purchase of product," the message text for event ID 3 could be defined as: 'Purchase of %2 %1'. If your server supports regular-expression evaluation, or even simple search and replace, you can substitute the name and quantity of the product purchased for the %2 and %1 placeholders.

An activity log, such as the one described here, cannot easily be used to reconstruct a snapshot of a table as of a specific point in time, because it does not record each and every SQL statement that modifies the data in the table.

Another possible query that you might want to implement for a log like this is one that scans for important messages with high severity levels. By running such a query periodically, you can identify important events that might otherwise remain unnoticed, allowing you to correct problems before they become critical.