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

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(changing ModType to a VARCHAR(15) so I don't have to foretell the future when assigning numbers)
(cleanup; smw)
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
<hide>
 +
[[thing type::table design]]
 +
[[project::VbzCart]]
 +
[[subsystem::framework]]
 +
[[status::active]]
 +
</hide>
 
==About==
 
==About==
 
* '''Purpose''': admin error/message log
 
* '''Purpose''': admin error/message log
Line 4: Line 10:
 
** '''2010-01-06''' EvWhen -> WhenFinished; +WhenStarted; VbzUser->WhoAdmin, SysUser->WhoSystem, Machine->WhoNetwork
 
** '''2010-01-06''' EvWhen -> WhenFinished; +WhenStarted; VbzUser->WhoAdmin, SysUser->WhoSystem, Machine->WhoNetwork
 
*** also +ModType, ModIndex -- laying groundwork for a unified log
 
*** 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''':
 
* '''Fields''':
 
** '''ModType''': which type of event -- each type relates to a specific table whose data is affected by the event
 
** '''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
 
** '''ModIndex''': ID of row affected (modified/added) within that table
 +
** '''DescrFin''': See "Start/Finish"
 
* '''Notes''':
 
* '''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.
 
** 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.
Line 14: Line 26:
 
** 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.
 
** 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''':
 
* '''Related''':
** Other event logs: {{vbzcart|table|shop_cart_event}}, {{vbzcart|table|ord_event}}, {{vbzcart|table|stk_bin_history}}, {{vbzcart|table|stk_history}}
+
** Other event logs: {{vbzcart|table|shop_cart_event}}, <s>{{vbzcart|table|ord_event}}</s>, {{vbzcart|table|stk_bin_history}}, {{vbzcart|table|stk_history}}
===Timestamps===
+
===Start/Finish===
 
I had originally decided not to have "WhenStarted" and "WhenFinished" because:
 
I had originally decided not to have "WhenStarted" and "WhenFinished" because:
 
* There are relatively few events that "complete"
 
* There are relatively few events that "complete"
Line 25: Line 37:
 
Events which don't involve writing data can set the "WhenFinished" stamp only.
 
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).
+
'''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==
 
==SQL==
<section begin=sql /><mysql>DROP TABLE IF EXISTS `event_log`;
+
<mysql>CREATE TABLE `event_log` (
 
 
CREATE TABLE `event_log` (
 
 
     ID    INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
 
     ID    INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
 
     WhenStarted  DATETIME    DEFAULT NULL COMMENT "set just before starting the event",
 
     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",
 
     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",
 
     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",
 
     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)",
 
     EvWhere      VARCHAR(255)              COMMENT "where in the code the event happened (suitable for filtering)",
     Params      VARCHAR(255)             COMMENT "any relevant parameters",
+
     Params      VARCHAR(255) DEFAULT NULL COMMENT "any relevant parameters",
     Descr        VARCHAR(255)              COMMENT "description of error",
+
     Descr        TEXT                      COMMENT "description of event [about to be] attempted",
     Code        INT          DEFAULT NULL COMMENT "numeric error code unique to location (EvWhere)",
+
    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",
 
     WhoAdmin    VARCHAR(127)              COMMENT "VbzCart username",
 
     WhoSystem    VARCHAR(127)              COMMENT "who logged into the operating system on the client machine",
 
     WhoSystem    VARCHAR(127)              COMMENT "who logged into the operating system on the client machine",
Line 44: Line 56:
 
     isError      TINYINT(1)                COMMENT "FALSE = this is just a message or normal event; TRUE = there is a problem to fix",
 
     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",
 
     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",
 
     Notes        VARCHAR(255) DEFAULT NULL COMMENT "manually-entered notes",
 
     PRIMARY KEY (`ID`)
 
     PRIMARY KEY (`ID`)
 
  ) ENGINE = MYISAM;</mysql>
 
  ) ENGINE = MYISAM;</mysql>
<section end=sql />
 

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:

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>