VbzCart/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. However, the IDs actually go up to 1883 (there are no records from 739 to 1015). If I had noticed this, I would have done a renumber of 1016+ to fill the gap, but I didn't... so we need to accommodate a range of 1900 IDs.

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+1900=) 3401 for "release".

I'm not going to worry about events (especially releases) 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 -- which maybe 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...)

Note that it would be nice if we could number the releases sequentially to reduce usage of the ID space, but it's not clear how to do this just in SQL (and I'm hoping to avoid writing any PHP for this migration). The easier way to do it is just increment everything again, but only the released IDs end up creating new EventPlex records. (...and again, an event-renumbering tool is a thing that could happen later.)

Where:

• "event" = event
• "legacy" = event_vc_legacy
• "notes" = event_notes
• "hold" = event_vc_ord_hold
• "type" = hold.ID_Type => ord_hold_type

...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)

step 3: modify order records

• Modify orders to record hold-status with a timestamp field rather than a pointer to the most recent active hold-event.
• Fill in this field by JOINing with the event data.

do later

Migrate the event_vc_ord_hold.Notes field to the event_notes table.

Concrete

Before doing any of this, back up the following tables:

• ord_pull
• event
• event_vc_legacy

No need to back up event_vc_ord_hold since it is blank when we start. (To restore it: drop, then recreate.)

/* == 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+10000; /* move all records out of the target range */
UPDATE `ord_pull` SET ID=ID-8500;  /* move 10001 -> 1501 */

INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)
  SELECT ID, WhenPulled, NULL, NULL, 'vc.order.hold.on', 'order hold invoked (2017 migration)', 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
    WHERE (VbzUser IS NOT NULL) OR (SysUser IS NOT NULL) OR (Machine != "");

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

/* UPDATE ord_pull SET ID=ID+1700; this causes conflicts */
UPDATE `ord_pull` SET ID=ID+8500; /* move all records out of the target range (back out to 10001+) */
UPDATE `ord_pull` SET ID=ID-6600;  /* move 10001 -> 3401 */

INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)
  SELECT ID, WhenFreed, NULL, NULL, 'vc.order.hold.off', 'order hold released (2017 migration)', NULL
    FROM ord_pull WHERE WhenFreed IS NOT NULL;

INSERT INTO event_vc_ord_hold (ID_Event,`ID_Order`,`ID_Type`,`isRelease`,`doHoldRstk`,`doHoldChrg`,`doContact`,`doExamine`,`Notes`)
  SELECT ID, ID_Ord, ID_Type, TRUE, FALSE, FALSE, FALSE, FALSE, NotesFree
    FROM ord_pull WHERE WhenFreed IS NOT NULL;

/* There is no need to create event_vc_legacy records because ord_pull did not separately keep track of who did the releases. */

/* == STEP 3: update the modified orders table == */

ALTER TABLE `vbz-vc`.`orders` 
  ADD COLUMN `WhenHeld` DATETIME NULL DEFAULT NULL COMMENT 'most recent timestamp of an order hold (NULL = none active)' AFTER `WhenNeeded`;

UPDATE orders AS o LEFT JOIN `ord_pull OLD` as old ON old.ID_Ord=o.ID
  SET o.WhenHeld = old.WhenPulled
    WHERE old.WhenFreed IS NULL;

/* make the ID_Pull column inaccessible to existing code (either rename or remove; the following will rename it) */

ALTER TABLE `vbz-vc`.`orders` 
CHANGE COLUMN `ID_Pull` `ID_Pull OLD` INT(11) NULL DEFAULT NULL COMMENT 'ord_pulls.ID: ID of the currently active Pull for this order (NULL = order is active)' ;

The following tables can now be dropped:

• ord_pull
• ord_pull OLD
• ord_pull_type
• event_log? (unrelated to this migration -- but why is it still here?)