VbzCart/tables/ord pull/migration - Revision history

Woozle: updated syntax highlighting tag

2020-02-03T15:56:27Z

updated syntax highlighting tag

← Older revision		Revision as of 15:56, 3 February 2020
Line 67:		Line 67:
	No need to back up event_vc_ord_hold since it is blank when we start. (To restore it: drop, then recreate.)		No need to back up event_vc_ord_hold since it is blank when we start. (To restore it: drop, then recreate.)

	<mysql>/* == STEP 0: preparation == */		<source lang=mysql>/* == STEP 0: preparation == */

	/* back up the original */		/* back up the original */
Line 140:		Line 140:
	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)' ;		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)' ;

	</~~mysql~~>		</source>
	The following tables can now be dropped:		The following tables can now be dropped:
	* ord_pull		* ord_pull

Woozle: update orders

2017-06-03T16:30:41Z

update orders

← Older revision		Revision as of 16:30, 3 June 2017
Line 51:		Line 51:
	* FALSE -> doExamine		* 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)

			===step 3: modify order records===
			* Modify {{l/vbzcart/table\|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===		===do later===
	Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.		Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/table\|event_notes}} table.

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

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

	/* back up the original */		/* back up the original */
Line 121:		Line 125:

	/* There is no need to create event_vc_legacy records because ord_pull did not separately keep track of who did the releases. */		/* 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)' ;

	</mysql>		</mysql>

Woozle: /* Concrete */ minor corrections, and a list of tables to drop

2017-06-03T12:54:58Z

Concrete: minor corrections, and a list of tables to drop

← Older revision		Revision as of 12:54, 3 June 2017
Line 59:		Line 59:
	* ord_pull		* ord_pull
	* event		* event
	* event_vc_ord_hold
	* event_vc_legacy		* 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.)

	<mysql>/* == STEP 0: preparation (optional) == */		<mysql>/* == STEP 0: preparation (optional) == */
Line 89:		Line 90:
	/* == STEP 1: process all pulls (hold invocations) == */		/* == STEP 1: process all pulls (hold invocations) == */

	UPDATE `ord_pull` SET ID=ID+~~1500~~;		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)		INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)
Line 101:		Line 103:
	INSERT INTO event_vc_legacy (ID_Event,EvWhere,Params,WhoAdmin,WhoSystem,WhoNetwork,isError,isSevere,isDebug)		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		SELECT ID, 'SQL', NULL, VbzUser, SysUser, Machine, FALSE, FALSE, FALSE
	FROM ord_pull;		FROM ord_pull
			WHERE (VbzUser IS NOT NULL) OR (SysUser IS NOT NULL) OR (Machine != "");

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

	UPDATE ord_pull SET ID=ID+1700;		/* 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)		INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)
Line 112:		Line 117:

	INSERT INTO event_vc_ord_hold (ID_Event,`ID_Order`,`ID_Type`,`isRelease`,`doHoldRstk`,`doHoldChrg`,`doContact`,`doExamine`,`Notes`)		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		SELECT ID, ID_Ord, ID_Type, TRUE, FALSE, FALSE, FALSE, FALSE, NotesFree
	FROM ord_pull WHERE WhenFreed IS NOT NULL;		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. */		/* There is no need to create event_vc_legacy records because ord_pull did not separately keep track of who did the releases. */

	</mysql>		</mysql>
			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?)

Woozle: /* Conceptual */ oops, need a bit more room for the second renumbering

2017-06-03T12:46:47Z

Conceptual: oops, need a bit more room for the second renumbering

← Older revision		Revision as of 12:46, 3 June 2017
Line 3:		Line 3:
	This is complicated by the fact that we will have '''two''' hold-event records for pulls which have been released.		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"~~.		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.

	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...)		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.)		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.)
Line 51:		Line 53:
	===do later===		===do later===
	Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.		Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.

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

Woozle: /* Concrete */ let's just back up all possibly-affected tables to disk, shall we?

2017-06-03T12:05:33Z

Concrete: let's just back up all possibly-affected tables to disk, shall we?

← Older revision		Revision as of 12:05, 3 June 2017
Line 52:		Line 52:
	Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.		Migrate the event_vc_ord_hold.Notes field to the {{l/ferreteria/event_notes}} table.
	==Concrete==		==Concrete==
	<mysql>/* == STEP 0: preparation == */		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 */		/* back up the original */

Woozle: /* Concrete */ step 2, and maybe done with first draft?

2017-06-03T11:52:35Z

Concrete: step 2, and maybe done with first draft?

← Older revision		Revision as of 11:52, 3 June 2017
Line 82:		Line 82:

	INSERT INTO event (ID,WhenStart,ID_Session,ID_Acct,TypeCode,Descrip,Stash)		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		SELECT ID, WhenPulled, NULL, NULL, 'vc.order.hold.on', 'order hold invoked (2017 migration)', NULL
	FROM ord_pull;		FROM ord_pull;

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

	UPDATE ord_pull SET ID=ID+		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>		</mysql>

Woozle: renumbering can't enforce sequentiality, for now

2017-06-03T11:45:32Z

renumbering can't enforce sequentiality, for now

← Older revision		Revision as of 11:45, 3 June 2017
Line 6:		Line 6:

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

			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:		Where:

Woozle: /* Concrete */ starting step 2, but need to note a change in the details

2017-06-03T11:42:10Z

Concrete: starting step 2, but need to note a change in the details

← Older revision		Revision as of 11:42, 3 June 2017
Line 75:		Line 75:
	INSERT INTO ord_pull SELECT * FROM `ord_pull OLD`;		INSERT INTO ord_pull SELECT * FROM `ord_pull OLD`;

	/* == STEP 1: process all hold invocations == */		/* == STEP 1: process all pulls (hold invocations) == */

	UPDATE `ord_pull` SET ID=ID+1500;		UPDATE `ord_pull` SET ID=ID+1500;
Line 90:		Line 90:
	SELECT ID, 'SQL', NULL, VbzUser, SysUser, Machine, FALSE, FALSE, FALSE		SELECT ID, 'SQL', NULL, VbzUser, SysUser, Machine, FALSE, FALSE, FALSE
	FROM ord_pull;		FROM ord_pull;

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

			UPDATE ord_pull SET ID=ID+

	</mysql>		</mysql>

Woozle: first pass at SQL through Step 1

2017-06-03T11:17:30Z

first pass at SQL through Step 1

← Older revision		Revision as of 11:17, 3 June 2017
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>

Woozle: extracted from main page; start of actual SQL

2017-06-02T15:43:49Z

extracted from main page; start of actual SQL

New page

=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" = {{l/ferreteria/table|event}}
* "legacy" = {{l/vbzcart/table|event_vc_legacy}}
* "notes" = {{l/ferreteria/table|event_notes}}
* "hold" = {{l/vbzcart/table|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>