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. 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" = event
• "legacy" = event_vc_legacy
• "notes" = event_notes
• "hold" = event_vc_ord_hold

...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 -> notes.Notes
• VbzUser -> legacy.WhoAdmin
• SysUser -> legacy.WhoSystem
• Machine -> legacy.WhoNetwork

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
• (Note: no separate record of who released each pull, so leave applicable fields blank)

SQL

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

</mysql>