Ferreteria/v0.42/log

The event-logging system uses the object-data system, including the table structure (under different names). Schema documentation here will be kept separate in case of tweaks, and to make it easier to construct the tables.

Tentatively, the "log.Type" field will be slightly repurposed from its usage in node.

History

• 2020-02-22 restored the log_leaf.Type field, because we need the "array" value for detecting sub-nodes.

Tables

log

Same structure as Template:L/version

CREATE TABLE `log` (
  `ID`       BIGINT       NOT NULL AUTO_INCREMENT,
  `ID_Mom`   BIGINT       DEFAULT NULL COMMENT "ID of parent event, if any",
  `Type`     VARCHAR(255) NOT NULL COMMENT "name of event type",
  `WhenMade` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(`ID`)
) ENGINE=InnoDB;

log_leaf

same structure as Template:L/version

CREATE TABLE `log_leaf` (
  `ID`      BIGINT        NOT NULL AUTO_INCREMENT,
  `ID_Node` BIGINT        NOT NULL COMMENT "ID of event to which this value belongs",
  `Name`    VARCHAR(255)  NOT NULL COMMENT "field name of this value",
  `Type`    VARCHAR(255) DEFAULT NULL COMMENT 'name of leaf (field) type',
  `Value`   LONGBLOB  DEFAULT NULL COMMENT "value, in string format",
  PRIMARY KEY(`ID`),
  UNIQUE KEY(`ID_Node`,`Name`)
) ENGINE=InnoDB;

The Type field, which was originally slated to be removed, is now going to be used in detecting sub-Leafs. A Leaf of Type "array" is the top of a sub-Leaf, and its value is the ID of the Node which has those Leafs.

Migration

Migrate core events:

INSERT log(ID,Type,WhenMade) SELECT ID,IFNULL(TypeCode,'2020-was-null'),WhenStart FROM event;

Migrate fields from event table to log_leaf:

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID,'ID_Session','id',ID_Session FROM event WHERE ID_Session IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID,'ID_Acct','id',ID_Acct FROM event WHERE ID_Acct IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID,'Descrip','text',Descrip FROM event WHERE Descrip IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID,'Stash','text',Stash FROM event WHERE Stash IS NOT NULL;

Migrate fields from old event-leaf records:

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'WhenFinish','date',WhenFinish FROM event_done WHERE WhenFinish IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Finish.Code','string',StateCode FROM event_done WHERE StateCode IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Finish.Descrip','text',Descrip FROM event_done WHERE Descrip IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Finish.Stash','text',Stash FROM event_done WHERE Stash IS NOT NULL;

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Table.ID','string',TableKey FROM event_in_table WHERE TableKey IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Table.Row','id',TableRow FROM event_in_table WHERE TableRow IS NOT NULL;

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'Notes','text',Notes FROM event_notes WHERE Notes IS NOT NULL;

Migrate fields from VbzCart:

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.bin.ID','id',ID_Bin FROM event_vc_bin WHERE ID_Bin IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.bin.Srce','id',ID_Srce FROM event_vc_bin WHERE ID_Srce IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.bin.Dest','id',ID_Dest FROM event_vc_bin WHERE ID_Dest IS NOT NULL;

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.Where','string',EvWhere FROM event_vc_legacy WHERE EvWhere IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.Params','string',Params FROM event_vc_legacy WHERE Params IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.who.admin','string',WhoAdmin FROM event_vc_legacy WHERE WhoAdmin IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.who.system','string',WhoSystem FROM event_vc_legacy WHERE WhoSystem IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.who.network','string',WhoNetwork FROM event_vc_legacy WHERE WhoNetwork IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event,'vc.a97.flags','string',
    CONCAT_WS(' ',
            IF(isError,
                CONCAT('err=', isError),
                NULL),
            IF(isSevere,
                CONCAT('severe=', isSevere),
                NULL),
            IF(isDebug,
                CONCAT('debug=', isDebug),
                NULL))
        AS val
    FROM event_vc_legacy WHERE (isError != 0) || (isSevere != 0) || (isDebug != 0);

INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event, 'vc.ord-hold.ID_Order','id',ID_Order FROM event_vc_ord_hold WHERE ID_Order IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event, 'vc.ord-hold.ID_Type','id',ID_Type FROM event_vc_ord_hold WHERE ID_Type IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event, 'vc.ord-hold.Notes','text',Notes FROM event_vc_ord_hold WHERE Notes IS NOT NULL;
INSERT log_leaf(ID_Node,Name,Type,Value) SELECT ID_Event, 'vc.ord-hold.flags','string',
CONCAT_WS(' ',
            IF(isRelease,
                CONCAT('is.release=', isRelease),
                NULL),
            IF(doHoldRstk,
                CONCAT('do.hold.restock=', doHoldRstk),
                NULL),
            IF(doHoldChrg,
                CONCAT('do.hold.charge=', doHoldChrg),
                NULL),
            IF(doContact,
                CONCAT('do.contact=', doContact),
                NULL),
            IF(doExamine,
                CONCAT('do.examine=', doExamine),
                NULL)
      ) AS val
FROM event_vc_ord_hold WHERE isRelease || doHoldRstk || doHoldChrg || doContact || doExamine;