VbzCart/docs/tables/cache log

About

• Purpose: Log of all cache updates performed
• Refers to: Template:Vbzcart
• Notes:
  • The stored procedures don't automatically update this log; that is handled by the DataMgr classes.
• History:
  • 2009-03-24 Belatedly realized that it makes no sense to log source and destination each time; running the procedure updates a preset destination (a given function always updates the same destination) from all the function's sources.
    • Also added VbzUser, SysUser, and Machine, while I'm here (to be consistent with Template:Vbzcart/table). Sure, some updates are automatic, but some aren't.
  • 2009-03-25 "VbzUser" makes this subsystem too interdependent with VbzCart, so reducing to just one user field (Username). The system initiating the cache update may prefix the username with a qualifier (e.g. "vbz:", "wiki:", "sys:", or whatever seems appropriate; it's for human consumption and doesn't need to match a key anywhere else).
  • 2010-11-09 Updating SQL before renaming page
    • (table) "data_log" -> "cache_log"
    • (field) "ID_Proc" -> "ID_Query" (now using VIEWs instead of PROCs)
    • (field) "Username" -> "WhoAdmin" (consistency with Template:Vbzcart)
    • (field) Machine -> WhoNetwork" (consistency with Template:Vbzcart)
    • (field) added "WhoSystem" (consistency with Template:Vbzcart)
    • removed DROP TABLE line -- not a good idea if table might have data which doesn't get automatically replaced

SQL

<section begin=sql /><mysql>CREATE TABLE `cache_log` (

   ID INT NOT NULL AUTO_INCREMENT   COMMENT "log line identifier",
   WhenStarted    DATETIME NOT NULL COMMENT "when this update was started",
   WhenFinished   DATETIME          COMMENT "when it was completed (NULL = it wasn't)",
   ID_Proc        INT NOT NULL      COMMENT "cache_queries.ID of stored procedure used",
   Caller         VARCHAR(63)       COMMENT "identifying string from code which caused the update",
   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"
   Notes          VARCHAR(255)      COMMENT "historical human-created notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

<section end=sql />

Old Versions

Retired 2010-11-09

<mysql>DROP TABLE IF EXISTS `data_log`;

CREATE TABLE `data_log` (

   ID INT NOT NULL AUTO_INCREMENT   COMMENT "log line identifier",
   WhenStarted    DATETIME NOT NULL COMMENT "when this update was started",
   WhenFinished   DATETIME          COMMENT "when it was completed (NULL = it wasn't)",
   ID_Proc        INT NOT NULL      COMMENT "data_procs.ID of stored procedure used",
   Caller         VARCHAR(63)       COMMENT "identifying string from code which caused the update",
   Username       VARCHAR(255)      COMMENT "identifier of user who initiated the change, if known",
   Machine        VARCHAR(64)       COMMENT "Network name (or IP address) of computer initiating the update, if any",
   Notes          VARCHAR(255)      COMMENT "historical human-created notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>

Retired 2009-03-24

<mysql>DROP TABLE IF EXISTS `data_log`;

CREATE TABLE `data_log` (

   ID INT NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
   WhenStarted DATETIME NOT NULL COMMENT "when this event was started",
   WhenFinished DATETIME COMMENT "when it was completed",
   ID_TableDest INT NOT NULL COMMENT "data_tables.ID of table being updated",
   ID_TableSrce INT NOT NULL COMMENT "data_tables.ID of triggering/source table",
   ID_Proc INT NOT NULL COMMENT "data_procs.ID of stored procedure used",
   Caller varchar(63) COMMENT "identifying string from code which caused the update",
   Notes varchar(255) COMMENT "historical human-created notes",
   PRIMARY KEY (`ID`)
) ENGINE = MYISAM;</mysql>