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
  • 2010-10-23 Having Code as a numeric field simply isn't working well. A lot of code seems to expect it to be text, as it was in ord_event. Changing it to a 7-character string.
  • 2011-01-09 Added isDebug so that long strings of events (often spurious or inaccurate) generated during debugging can be excluded from the event list reported on most pages.
  • 2011-09-21 Added WhatSQL field
  • 2011-10-08 Made Descr and DescrFin into TEXT fields because descriptions were getting chopped off and I thought there was a bug and the fragmentary HTML was causing problems with event listings.
• 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",
   WhatSQL      TEXT         DEFAULT NULL COMMENT "any SQL executed for this event",
   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        TEXT                      COMMENT "description of event [about to be] attempted",
   DescrFin     TEXT         DEFAULT NULL COMMENT "further details discovered during attempt",
   Code         VARCHAR(7)   DEFAULT NULL COMMENT "mnemonic error code unique within table type (ModType)",
   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",
   isDebug      TINYINT(1)  DEFAULT FALSE COMMENT "TRUE = generated during debugging -- don't normally show in reports",
   Notes        VARCHAR(255) DEFAULT NULL COMMENT "manually-entered notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>