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

From Woozle Writes Code
< VbzCart‎ | docs‎ | tables
Jump to navigation Jump to search
imported>Woozle
(migration SQL)
m (Woozle moved page VbzCart/VbzCart/tables/stk bin history to VbzCart/docs/tables/stk bin history without leaving a redirect: part 4/5)
 
(One intermediate revision by one other user not shown)
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;</mysql>
) 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;</mysql>
  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 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.)