VbzCart/docs/tables/ord pull/migration: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables‎ | ord pull
Jump to navigation Jump to search
imported>Woozle
(extracted from main page; start of actual SQL)
 
imported>Woozle
(first pass at SQL through Step 1)
Line 12: Line 12:
* "notes" = {{l/ferreteria/table|event_notes}}
* "notes" = {{l/ferreteria/table|event_notes}}
* "hold" = {{l/vbzcart/table|event_vc_ord_hold}}
* "hold" = {{l/vbzcart/table|event_vc_ord_hold}}
* "type" = hold.ID_Type => {{l/vbzcart/table|ord_hold_type}}


...migration requires the following steps:
...migration requires the following steps:
Line 23: Line 24:
* ID_Type -> hold.ID_Type
* ID_Type -> hold.ID_Type
* WhenPulled -> event.WhenStart
* WhenPulled -> event.WhenStart
* NotesPull -> notes.Notes
* NotesPull -> hold.Notes
* VbzUser -> legacy.WhoAdmin
* VbzUser -> legacy.WhoAdmin
* SysUser -> legacy.WhoSystem
* SysUser -> legacy.WhoSystem
* Machine -> legacy.WhoNetwork
* Machine -> legacy.WhoNetwork
* type.doHoldRstk -> hold.doHoldRstk
* type.doHoldChrg -> hold.doHoldChrg
* type.doContact -> hold.doContact
* type.doExamine -> hold.doExamine


===step 2: released holds only===
===step 2: released holds only===
Line 37: Line 42:
* WhenFreed -> event.WhenStart
* WhenFreed -> event.WhenStart
* NotesFree -> notes.Notes
* 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)
* (Note: no separate record of who released each pull, so leave applicable fields blank)
==SQL==
===do later===
<mysql>/* back up the original */
Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.
==Concrete==
<mysql>/* == STEP 0: preparation == */
 
/* back up the original */


ALTER TABLE `vbz-vc`.`ord_pull`  
ALTER TABLE `vbz-vc`.`ord_pull`  
Line 59: Line 72:
   PRIMARY KEY (`ID`)
   PRIMARY KEY (`ID`)
) ENGINE=InnoDB;
) ENGINE=InnoDB;
INSERT INTO ord_pull SELECT * FROM `ord_pull OLD`;
INSERT INTO ord_pull SELECT * FROM `ord_pull OLD`;


/* == STEP 1: process all 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;
</mysql>
</mysql>

Revision as of 11:17, 3 June 2017

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

</mysql>