VbzCart/docs/tables/ord pull/migration

Migration Process

Conceptual

This is complicated by the fact that we will have two hold-event records for pulls which have been released.

There are 1665 Order Pull events to migrate, with 528 of those being released (freed), for a total of 2193 EventPlex records to create. We have a gap in the event table from IDs 1230 to 6354, so let's start the pulls at ID 1501 for "invoke" and (1500+1700=) 3201 for "release".

I'm not going to worry about events being IDed out of chronological order, at least for now. If it becomes important later, maybe we'll have an event-renumbering tool. (The only non-event table that references events by ID, so far, is orders.ID_Hold. Maybe it should be changed to a timestamp. There should probably be a rule against referencing event records from other records. If we ever need to, then maybe a "bookmarks" EventPlex extension is called for. What happens in the events log stays in the events log...)

Where:

• "event" = Template:L/ferreteria/table
• "legacy" = Template:L/vbzcart/table
• "notes" = Template:L/ferreteria/table
• "hold" = Template:L/vbzcart/table
• "type" = hold.ID_Type => Template:L/vbzcart/table

...migration requires the following steps:

step 1: all holds

• ID: renumber for invoke, then for each pull record:
  • -> event.ID
  • -> hold.ID_Event
• FALSE -> hold.isRelease
• ID_Ord -> hold.ID_Order
• ID_Type -> hold.ID_Type
• WhenPulled -> event.WhenStart
• NotesPull -> hold.Notes
• VbzUser -> legacy.WhoAdmin
• SysUser -> legacy.WhoSystem
• Machine -> legacy.WhoNetwork
• type.doHoldRstk -> hold.doHoldRstk
• type.doHoldChrg -> hold.doHoldChrg
• type.doContact -> hold.doContact
• type.doExamine -> hold.doExamine

step 2: released holds only

• ID: renumber (again) released pulls only, then for each pull record:
  • -> event.ID
  • -> hold.ID_Event
• TRUE -> hold.isRelease
• ID_Ord -> hold.ID_Order
• ID_Type -> hold.ID_Type
• WhenFreed -> event.WhenStart
• NotesFree -> notes.Notes
• FALSE -> doHoldRstk
• FALSE -> doHoldChrg
• FALSE -> doContact
• FALSE -> doExamine
• (Note: no separate record of who released each pull, so leave applicable fields blank)

do later

Migrate the event_vc_ord_hold.Notes field to the Template:L/ferreteria/event notes table.

Concrete

<mysql>/* == STEP 0: preparation == */

/* back up the original */

ALTER TABLE `vbz-vc`.`ord_pull` RENAME TO `vbz-vc`.`OLD ord_pull`;

/* create a new working copy */

CREATE TABLE `ord_pull` (

 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `ID_Ord` int(11) NOT NULL COMMENT 'core_orders.ID',
 `ID_Type` int(11) NOT NULL COMMENT 'ord_pull_type.ID',
 `WhenPulled` datetime NOT NULL COMMENT 'when this pull occurred',
 `WhenFreed` datetime DEFAULT NULL COMMENT 'when this pull was dropped',
 `VbzUser` varchar(127) CHARACTER SET latin1 DEFAULT NULL COMMENT 'VbzCart username, if available',
 `SysUser` varchar(127) CHARACTER SET latin1 DEFAULT NULL COMMENT 'operating system username, if available',
 `Machine` varchar(63) CHARACTER SET latin1 NOT NULL COMMENT 'network name or IP address of client',
 `NotesPull` mediumtext CHARACTER SET latin1 COMMENT 'human-added notes about the pull',
 `NotesFree` mediumtext CHARACTER SET latin1 COMMENT 'human-added notes about the release',
 PRIMARY KEY (`ID`)

) ENGINE=InnoDB;

INSERT INTO ord_pull SELECT * FROM `ord_pull OLD`;

/* == STEP 1: process all pulls (hold invocations) == */

UPDATE `ord_pull` SET ID=ID+1500;

INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)

 SELECT ID, WhenPulled, NULL, NULL, 'vc.order.hold.on', 'order hold active (migrated)', NULL
   FROM ord_pull;

INSERT INTO event_vc_ord_hold (ID_Event,`ID_Order`,`ID_Type`,`isRelease`,`doHoldRstk`,`doHoldChrg`,`doContact`,`doExamine`,`Notes`)

 SELECT op.ID, op.ID_Ord, op.ID_Type, FALSE, oht.doHoldRstk, oht.doHoldChrg, oht.doContact, oht.doExamine, op.NotesPull
   FROM ord_pull AS op LEFT JOIN ord_hold_type AS oht ON op.ID_Type=oht.ID;

INSERT INTO event_vc_legacy (ID_Event,EvWhere,Params,WhoAdmin,WhoSystem,WhoNetwork,isError,isSevere,isDebug)

 SELECT ID, 'SQL', NULL, VbzUser, SysUser, Machine, FALSE, FALSE, FALSE
   FROM ord_pull;

/* == STEP 2: process only released pulls (hold releases) == */

UPDATE ord_pull SET ID=ID+

</mysql>