VbzCart/tables/event log

About

• Purpose: admin error/message log
• History:
  • 2010-01-06 EvWhen -> WhenFinished; +WhenStarted; VbzUser->WhoAdmin, SysUser->WhoSystem, Machine->WhoNetwork
    • also +ModType, ModIndex
• Fields:
  • ModType: which type of event -- each type relates to a specific table whose data is affected by the event
  • ModIndex: ID of row affected (modified/added) within that table
• Notes:
  • This is a log specifically of administration activity (shipping, catalog updates, etc.) so should probably be renamed admin_log or similar. There will be a separate log for shopping activity.
  • Have to use "Ev" prefix because Where is a keyword.
• Future:
  • Change EvWhere to a name which more clearly indicates that it's where in the code the event took place
  • Add a field called ConnMeth (connection method) as a VARCHAR(255), and make it part of the primary key. Any code which INSERTs to this table should set this to a string which identifies the server. This will allow merging of event logs from multiple servers.
• Related:
  • Other event logs: shop_cart_event. ord_event

Timestamps

I had originally decided not to have "WhenStarted" and "WhenFinished" because:

• There are relatively few events that "complete"
• This leaves no place to record completion status (e.g. how many records were affected)
• It makes the code more complicated and less elegant

Most events, however, do involve some sort of modification of data. Attempts to write data may cause the code to crash, so it would be a good idea to first log that we're about to attempt a change, then log the fact that the change was completed. Any events with a "WhenStarted" time but NULL in the "WhenFinished" field then become flags that something is going wrong.

Events which don't involve writing data can set the "WhenFinished" stamp only.

If we want to record how many records were affected, there can be a field for that (will wait for any specific examples before adding this, however).

SQL

<mysql>DROP TABLE IF EXISTS `event_log`;

CREATE TABLE `event_log` (

   ID     INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   WhenStarted  DATETIME DEFAULT NULL COMMENT "set just before starting the event",
   WhenFinished DATETIME DEFAULT NULL COMMENT "set after completing the event; indicates the code did not crash",
   ModType      INT      DEFAULT NULL COMMENT "type of modification done by event",
   ModIndex     INT(4)   DEFAULT NULL COMMENT "ID of row being modified by this event",
   EvWhere      VARCHAR(255)          COMMENT "where in the code the event happened (suitable for filtering)",
   Params       VARCHAR(255)          COMMENT "any relevant parameters",
   Descr        VARCHAR(255)          COMMENT "description of error",
   Code         INT DEFAULT NULL      COMMENT "numeric error code unique to location (EvWhere)",
   WhoAdmin     VARCHAR(127)          COMMENT "VbzCart username",
   WhoSystem    VARCHAR(127)          COMMENT "who logged into the operating system on the client machine",
   WhoNetwork   VARCHAR(64)           COMMENT "network name or IP address of client system from which the event was initiated",
   isError      TINYINT(1)            COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix",
   isSevere     TINYINT(1)            COMMENT "TRUE = important enough to send email to admin immediately",
   Notes        VARCAHR(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>