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 -- laying groundwork for a unified log
  • 2010-10-19 Added DescrFin field
• 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
  • DescrFin: See "Start/Finish"
• 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, stk_bin_history, stk_history

Start/Finish

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.

DescrFin: It also turns out to be useful to be able to record additional descriptive information at the time of completion, because there are frequently bits of information which are discovered or calculated during the process of the event. You'd have to do the calculations twice (once without writing, once with) to be able to log them before doing the actual data change.

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      VARCHAR(15)  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) DEFAULT NULL COMMENT "any relevant parameters",
   Descr        VARCHAR(255)              COMMENT "description of event [about to be] attempted",
   DescrFin     VARCHAR(255) DEFAULT NULL COMMENT "further details discovered during attempt",
   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        VARCHAR(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>