Difference between revisions of "VbzCart/tables/stk bin history"
Jump to navigation
Jump to search
(migration SQL) |
m (updated syntax highlighting tags) |
||
| Line 9: | Line 9: | ||
==SQL== | ==SQL== | ||
| − | <mysql>DROP TABLE IF EXISTS `stk_bin_history`; | + | <source lang=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 22: | Line 22: | ||
`Notes` VARCHAR(255) DEFAULT NULL COMMENT "optional explanatory notes", | `Notes` VARCHAR(255) DEFAULT NULL COMMENT "optional explanatory notes", | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
| − | ) ENGINE = MYISAM;</ | + | ) ENGINE = MYISAM;</source> |
==Migration== | ==Migration== | ||
The following were used to migrate from this table to EventPlex tables: | 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) | + | <source lang=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 | SELECT ID+30000 AS NewID, ID_Bin, ID_Srce, ID_Dest | ||
FROM stk_bin_history; | FROM stk_bin_history; | ||
| Line 44: | Line 44: | ||
INSERT INTO event (ID, WhenStart, TypeCode, Descrip) | INSERT INTO event (ID, WhenStart, TypeCode, Descrip) | ||
SELECT ID+30000 AS NewID, WhenDone, "legacy.bin", Descr | SELECT ID+30000 AS NewID, WhenDone, "legacy.bin", Descr | ||
| − | FROM stk_bin_history;</ | + | FROM stk_bin_history;</source> |
This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.) | This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.) | ||
Latest revision as of 16:00, 3 February 2020
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
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;
Migration
The following were used to migrate from this table to EventPlex tables:
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;
This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.)