VbzCart/docs/tables/stk bin history: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(this is being replaced)
m (Woozle moved page VbzCart/VbzCart/tables/stk bin history to VbzCart/docs/tables/stk bin history without leaving a redirect: part 4/5)
 
(2 intermediate revisions by one other user not shown)
Line 9: Line 9:


==SQL==
==SQL==
<section begin=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;</mysql>
) ENGINE = MYISAM;</source>
<section end=sql />
==Migration==
The following were used to migrate from this table to EventPlex tables:
<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
  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;</source>
This is the reverse of the order in which they were executed, but order shouldn't matter. (I think.)

Latest revision as of 01:57, 25 February 2024

About

  • Purpose: Documents things which happen to a stock bin -- usually changes to Template:Vbzcart 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.)