Difference between revisions of "VbzCart/tables/event log"

Latest revision as of 01:18, 9 July 2014

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

@@ Line 1: / Line 1: @@
+<hide>
+[[thing type::table design]]
+[[project::VbzCart]]
+[[subsystem::framework]]
+[[status::active]]
+</hide>
 ==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 When and Where are keywords.
+** Have to use "Ev" prefix because Where is a keyword.
-** Decided not to have "WhenStarted" and "WhenFinished" because:
+* '''Future''':
-*** There are relatively few events that "complete"
+** Change EvWhere to a name which more clearly indicates that it's where ''in the code'' the event took place
-*** This leaves no place to record completion status (e.g. how many records were affected)
+** 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.
-*** It makes the code more complicated and less elegant
+* '''Related''':
+** Other event logs: {{vbzcart|table|shop_cart_event}}, <s>{{vbzcart|table|ord_event}}</s>, {{vbzcart|table|stk_bin_history}}, {{vbzcart|table|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==
-<section begin=sql /><mysql>DROP TABLE IF EXISTS `event_log`;
+<mysql>CREATE TABLE `event_log` (
+     ID     INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
-CREATE TABLE `event_log` (
+     WhenStarted  DATETIME     DEFAULT NULL COMMENT "set just before starting the event",
-     ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
+    WhenFinished DATETIME     DEFAULT NULL COMMENT "set after completing the event; indicates the code did not crash",
-     EvWhen DATETIME NOT NULL COMMENT "when the event started",
+    WhatSQL      TEXT         DEFAULT NULL COMMENT "any SQL executed for this event",
-     EvWhere varchar(255) COMMENT "where in the code the event happened (suitable for filtering)",
+    ModType      VARCHAR(15)  DEFAULT NULL COMMENT "type of modification done by event",
-     Params varchar(255) COMMENT "any relevant parameters",
+    ModIndex     INT(4)       DEFAULT NULL COMMENT "ID of row being modified by this event",
-     Descr varchar(255) COMMENT "description of error",
+     EvWhere      VARCHAR(255)              COMMENT "where in the code the event happened (suitable for filtering)",
-     Code INT DEFAULT NULL COMMENT "numeric error code unique to location (EvWhere)",
+     Params       VARCHAR(255) DEFAULT NULL COMMENT "any relevant parameters",
-     VbzUser varchar(127) COMMENT "VbzCart username, for when we have a user system",
+     Descr        TEXT                      COMMENT "description of event [about to be] attempted",
-     SysUser varchar(127) COMMENT "who logged into the operating system",
+    DescrFin     TEXT         DEFAULT NULL COMMENT "further details discovered during attempt",
-     Machine varchar(64) COMMENT "network name of machine from which the event was initiated, if applicable",
+     Code         VARCHAR(7)   DEFAULT NULL COMMENT "mnemonic error code unique within table type (ModType)",
-     isError BOOL COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix",
+     WhoAdmin     VARCHAR(127)              COMMENT "VbzCart username",
-     isSevere BOOL COMMENT "TRUE = important enough to send email to admin immediately",
+     WhoSystem    VARCHAR(127)              COMMENT "who logged into the operating system on the client machine",
-     Notes varchar(255) DEFAULT NULL COMMENT "manually-entered notes",
+     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>
-<section end=sql />

Difference between revisions of "VbzCart/tables/event log"

Latest revision as of 01:18, 9 July 2014

About

Start/Finish

SQL

Navigation menu

Search