VbzCart/tables/ord pull/migration

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | tables‎ | ord pull
Revision as of 11:42, 3 June 2017 by Woozle (talk | contribs) (→‎Concrete: starting step 2, but need to note a change in the details)
Jump to navigation Jump to search

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:

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

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/ord_pull/migration&oldid=22697"