Ferreteria/sql/event: Difference between revisions
< Ferreteria | sql
Jump to navigation
Jump to search
(note about eventual archiving) |
No edit summary |
||
| Line 2: | Line 2: | ||
* '''Purpose''': base table for logging application events | * '''Purpose''': base table for logging application events | ||
** Records only the basic information common to all (or nearly all) 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. | * '''Future''': | ||
** Eventually we will probably want to archive session records, so there should be an archive version of this table which directly stores <s>at least the User ID and</s> 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== | ==History== | ||
* '''2017-02-06''' Adapting the good bits from [[VbzCart/tables/event log]] | * '''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== | ==SQL== | ||
<mysql>CREATE TABLE `event` ( | <mysql>CREATE TABLE `event` ( | ||
| Line 10: | Line 14: | ||
WhenStart DATETIME NOT NULL COMMENT "set just before starting the event", | WhenStart DATETIME NOT NULL COMMENT "set just before starting the event", | ||
ID_Session INT DEFAULT NULL COMMENT "session.ID of active session", | ID_Session INT DEFAULT NULL COMMENT "session.ID of active session", | ||
TypeCode | 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", | 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", | Stash TEXT DEFAULT NULL COMMENT "additional event-related data that doesn't need to be searchable", | ||
PRIMARY KEY (`ID`) | PRIMARY KEY (`ID`) | ||
) ENGINE = InnoDB;</mysql> | ) ENGINE = InnoDB;</mysql> | ||
Revision as of 19:50, 10 February 2017
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 andmaybe 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.
- Eventually we will probably want to archive session records, so there should be an archive version of this table which directly stores
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
<mysql>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;</mysql>