Ferreteria/sql/event

About

• Purpose: base table for logging application events
  • Records only the basic information common to all (or nearly all) events.
• Future:
  • Eventually we will probably want to archive session records, so there should be an archive version of this table which directly stores at least the User ID and maybe IP address and browser. Events whose sessions are being archived would be migrated to that table as part of the same process.
  • Maybe we will also want the data in TypeCode and Stash to be compressed.

History

• 2017-02-06 Adapting the good bits from VbzCart/tables/event log
• 2017-02-10 Belatedly figured out that Session records don't maintain User ID indefinitely, so we need to record that here.
  • Also expanding TypeCode from 7 chars to 255, so we can have prefixes to avoid event-name conflicts.

SQL

CREATE TABLE `event` (
    ID         INT              NOT NULL AUTO_INCREMENT COMMENT "log line identifier",
    WhenStart  DATETIME         NOT NULL COMMENT "set just before starting the event",
    ID_Session INT          DEFAULT NULL COMMENT "session.ID of active session",
    ID_Acct    INT          DEFAULT NULL COMMENT "user_acct.ID of active user account, if any",
    TypeCode   VARCHAR(255) DEFAULT NULL COMMENT "mnemonic event code",
    Descrip    TEXT         DEFAULT NULL COMMENT "code-generated description of event",
    Stash      TEXT         DEFAULT NULL COMMENT "additional event-related data that doesn't need to be searchable",
    PRIMARY KEY (`ID`)
 ) ENGINE = InnoDB;