Difference between revisions of "VbzCart/tables/stk bin history"
Jump to navigation
Jump to search
(this is being replaced) |
(migration SQL) |
||
Line 9: | Line 9: | ||
==SQL== | ==SQL== | ||
− | + | <mysql>DROP TABLE IF EXISTS `stk_bin_history`; | |
CREATE TABLE `stk_bin_history` ( | CREATE TABLE `stk_bin_history` ( | ||
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
Line 23: | Line 23: | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
− | < | + | ==Migration== |
+ | The following were used to migrate from this table to EventPlex tables: | ||
+ | <mysql>INSERT INTO event_vc_bin (ID_Event, ID_Bin, ID_Srce, ID_Dest) | ||
+ | SELECT ID+30000 AS NewID, ID_Bin, ID_Srce, ID_Dest | ||
+ | FROM stk_bin_history; | ||
+ | |||
+ | INSERT INTO event_vc_legacy (ID_Event, WhoAdmin,WhoSystem,WhoNetwork) | ||
+ | SELECT ID+30000 AS NewID, WhoAdmin,WhoSystem,WhoNetwork | ||
+ | FROM stk_bin_history; | ||
+ | |||
+ | INSERT INTO event_notes (ID_Event,Notes) | ||
+ | SELECT ID+30000 AS NewID, Notes | ||
+ | FROM stk_bin_history | ||
+ | WHERE Notes IS NOT NULL; | ||
+ | |||
+ | INSERT INTO event_in_table (ID_Event,TableKey,TableRow) | ||
+ | SELECT ID+30000 AS NewID, "bin" AS TableKey, ID_Bin AS TableRow | ||
+ | FROM stk_bin_history; | ||
+ | |||
+ | INSERT INTO event (ID, WhenStart, TypeCode, Descrip) | ||
+ | SELECT ID+30000 AS NewID, WhenDone, "legacy.bin", Descr | ||
+ | FROM stk_bin_history;</mysql> | ||
+ | This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.) |
Revision as of 21:24, 22 April 2017
About
- Purpose: Documents things which happen to a stock bin -- usually changes to stk_bins data, but can also log collective movement of items to/from bins
- History:
- 2009-11-07 Added "Descr" field and made ID_Srce/ID_Dest NOT NULL
- 2009-12-20 Added fields WhoAdmin, WhoSystem, WhoNetwork
- 2017-04-17 Replacing this with event_vc_bin
- Fields:
- Descr is for events other than moving, e.g. field changes or stock recounts
SQL
<mysql>DROP TABLE IF EXISTS `stk_bin_history`; CREATE TABLE `stk_bin_history` (
`ID` INT NOT NULL AUTO_INCREMENT, `ID_Bin` INT NOT NULL COMMENT "stk_bins.ID of bin being moved", `WhenDone` DATETIME NOT NULL COMMENT "when the move happened", `WhoAdmin` VARCHAR(127) NOT NULL COMMENT "VbzCart admin username", `WhoSystem` VARCHAR(127) DEFAULT NULL COMMENT "who logged into the operating system, if using non-web client app", `WhoNetwork` VARCHAR(64) NOT NULL COMMENT "network name or IP address of client machine", `ID_Srce` INT NOT NULL COMMENT "stk_places.ID of where the bin came from (NULL = new bin)", `ID_Dest` INT NOT NULL COMMENT "stk_places.ID of where the bin was moved to (NULL = bin destroyed)", `Descr` VARCHAR(255) DEFAULT NULL COMMENT "code-generated description for non-move events", `Notes` VARCHAR(255) DEFAULT NULL COMMENT "optional explanatory notes", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>
Migration
The following were used to migrate from this table to EventPlex tables: <mysql>INSERT INTO event_vc_bin (ID_Event, ID_Bin, ID_Srce, ID_Dest)
SELECT ID+30000 AS NewID, ID_Bin, ID_Srce, ID_Dest FROM stk_bin_history;
INSERT INTO event_vc_legacy (ID_Event, WhoAdmin,WhoSystem,WhoNetwork)
SELECT ID+30000 AS NewID, WhoAdmin,WhoSystem,WhoNetwork FROM stk_bin_history;
INSERT INTO event_notes (ID_Event,Notes)
SELECT ID+30000 AS NewID, Notes FROM stk_bin_history WHERE Notes IS NOT NULL;
INSERT INTO event_in_table (ID_Event,TableKey,TableRow)
SELECT ID+30000 AS NewID, "bin" AS TableKey, ID_Bin AS TableRow FROM stk_bin_history;
INSERT INTO event (ID, WhenStart, TypeCode, Descrip)
SELECT ID+30000 AS NewID, WhenDone, "legacy.bin", Descr FROM stk_bin_history;</mysql>
This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.)