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)

do later

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

Concrete

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

• ord_pull
• event
• event_vc_ord_hold
• event_vc_legacy

<mysql>/* == STEP 0: preparation (optional) == */

/* 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 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;

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

UPDATE ord_pull SET ID=ID+1700;

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, op.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 keep track of who did the releases. */

</mysql>