Difference between revisions of "VbzCart/tables/cache 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
(consolidating two usernames down to one)
(major updates for caching rewrite)
Line 1: Line 1:
 
==About==
 
==About==
 
* '''Purpose''': Log of all cache updates performed
 
* '''Purpose''': Log of all cache updates performed
 +
* '''Refers to''': {{vbzcart|table|cache_queries}}
 
* '''Notes''':
 
* '''Notes''':
 
** The stored procedures don't automatically update this log; that is handled by the DataMgr classes.
 
** The stored procedures don't automatically update this log; that is handled by the DataMgr classes.
Line 7: Line 8:
 
*** Also added VbzUser, SysUser, and Machine, while I'm here (to be consistent with {{vbzcart/table|event_log}}). Sure, some updates are automatic, but some aren't.
 
*** Also added VbzUser, SysUser, and Machine, while I'm here (to be consistent with {{vbzcart/table|event_log}}). 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).
 
** '''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 {{vbzcart|table|event_log}})
 +
*** (field) Machine -> WhoNetwork" (consistency with {{vbzcart|table|event_log}})
 +
*** (field) added "WhoSystem" (consistency with {{vbzcart|table|event_log}})
 +
*** removed DROP TABLE line -- not a good idea if table might have data which doesn't get automatically replaced
 
==SQL==
 
==SQL==
<section begin=sql /><mysql>DROP TABLE IF EXISTS `data_log`;
+
<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_Query      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` (
 
CREATE TABLE `data_log` (
Line 21: Line 44:
 
     PRIMARY KEY (`ID`)
 
     PRIMARY KEY (`ID`)
 
  ) ENGINE = MYISAM;</mysql>
 
  ) ENGINE = MYISAM;</mysql>
<section end=sql />
+
===Retired 2009-03-24===
===old version===
 
Retired 2009-03-24
 
 
<mysql>DROP TABLE IF EXISTS `data_log`;
 
<mysql>DROP TABLE IF EXISTS `data_log`;
 
    
 
    

Revision as of 14:29, 9 November 2010

About

  • Purpose: Log of all cache updates performed
  • Refers to: cache_queries
  • 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
  1. REDIRECT Template:l/vc/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 event_log)
      • (field) Machine -> WhoNetwork" (consistency with event_log)
      • (field) added "WhoSystem" (consistency with event_log)
      • removed DROP TABLE line -- not a good idea if table might have data which doesn't get automatically replaced

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

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>