Difference between revisions of "VbzCart/tables/stk history"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(saving revisions, but not ready for prime-time yet)
m (updated syntax highlighting tags)
 
(19 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
 
* '''Purpose''': log of all stock movement
 
* '''Purpose''': log of all stock movement
−
* '''Refers to''': {{vbzcart/table|stk_containers}}, {{vbzcart/table|cat_items}}
+
* '''Refers to''': {{l/vbzcart/query|qryStock_containers}}, {{l/vbzcart/table|cat_items}}
−
* '''History''':
 
−
** '''2008-12-26''' Changed field names, added new fields:
 
−
*** QtyFound → QtyBefore – quantity in this stock line ''before'' moving
 
−
*** QtyDone → QtyAdded – quantity added to this stock line
 
−
*** QtyLeft → QtyAfter – quantity in this stock line ''after'' moving
 
−
*** + QtyTotBefore – total of item in this bin ''before'' move)
 
−
*** + QtyTotAfter – total of item in this bin ''after'' move)
 
 
* '''Rules''':
 
* '''Rules''':
 
** Stock items must always be moved ''to'' or ''from'' a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
 
** Stock items must always be moved ''to'' or ''from'' a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
 
** The sign of QtyAdded indicates whether the movement was ''to'' or ''from''.
 
** The sign of QtyAdded indicates whether the movement was ''to'' or ''from''.
−
* '''Notes''':
+
 
−
** The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).
+
==Fields==
−
** Commented-out bits can be included if you have no incomplete legacy data to deal with.
+
* '''CH_OthType''': appropriate type code -- see {{l/vbzcart/query|qryStock_containers}} for codes
 +
* '''IDS_OthCont''': type code plus separator (period) plus ID unique to that type
 +
* '''ID_Event''': where known, this is the ID of the corresponding event in {{l/vbzcart/table|event_log}}
 +
** This is a sort of first step towards making this log part of the unified event log.
 +
* '''WhoSystem''': is optional and possibly redundant, but seems to be useful for marking imported/migrated records.
 +
 
 +
'''Future''':
 +
* Rename '''QtyBefore''' and '''QtyAfter''' to QtyLineBefore and QtyLineAfter.
 +
 
 +
==History==
 +
* '''2008-12-26''' Changed field names, added new fields:
 +
** QtyFound → QtyBefore – quantity in this stock line ''before'' moving
 +
** QtyDone → QtyAdded – quantity added to this stock line
 +
** QtyLeft → QtyAfter – quantity in this stock line ''after'' moving
 +
** + QtyTotBefore – total of item in this bin ''before'' move)
 +
** + QtyTotAfter – total of item in this bin ''after'' move)
 +
* '''2008-12-27''' More field name changes, to show grouping of local ("stock") and external ("other") locations:
 +
** ID_Stock → ID_StkLine
 +
** ID_Bin → ID_StkBin
 +
** ID_Cont → ID_OthCont
 +
** ID_Line → ID_OthLine
 +
* '''2008-12-29''' Final revisions to match what I ended up with in Access for the migration:
 +
** +CH_OthType
 +
** +ID_OthCont
 +
** Created {{l/vbzcart/table|stk_history_legacy}} to handle legacy data properly; turned on all NOT NULLs
 +
* '''2009-12-20''' Added fields WhoAdmin, WhoSystem, WhoNetwork
 +
* '''2009-12-22''' Renamed When -> WhenStarted; added WhenFinished; QtyAfter and QtyBinAfter can now be NULL so we can record them when the event is complete.
 +
* '''2010-12-01''' Added '''ID_Event''' field -- working towards including this in unified event logging
 +
* '''2014-05-26''' Made fields DEFAULT NULL instead of NOT NULL so we don't have to know all of them before the event is started: '''ID_StkLine''', '''ID_OthCont''', '''ID_OthLine''', '''IDS_OthCont'''
 +
** Left these  as NOT NULL: '''ID_StkBin''',  '''CH_OthType''' -- Bins must be explicitly created by an administrator, and we should always know what ''type'' of container the Other is.
 +
* '''2014-06-26''' Lengthened '''What''' from 63 chars to 255 because descriptions were being cut off.
 +
* '''2016-02-24''' Apparently I never changed the actual DB; this time, lengthened '''What''' from 63 chars to TEXT.
 +
* '''2016-03-03'''
 +
** No NULL values found in ID_StkLine (or in legacy data), so changing it to NOT NULL.
 +
** Setting additional NOT NULL fields to DEFAULT NULL in order to accommodate {{l/vc/table|stk_history_legacy}} data: '''QtyBefore''', '''QtyBinBefore'''
 +
*** I decided it was better to tolerate incomplete event-record data than to risk losing the record altogether when an event-write attempt doesn't include all the required fields, since there's currently no guarantee that a failed write will generate an alert anywhere.
 +
** Officially changing engine to INNODB. Migrated data from stk_history_legacy and then (what is now) `stk_history OLD 2`.
 +
 
 +
==Notes==
 +
* The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).
 
==SQL==
 
==SQL==
−
<section begin=sql /><mysql>DROP TABLE IF EXISTS `stk_history`;
+
<source lang=mysql>CREATE TABLE `stk_history` (
−
 
+
   `ID`          INT             NOT NULL AUTO_INCREMENT,
−
CREATE TABLE `stk_history_new` (
+
   `ID_Event`    INT         DEFAULT NULL COMMENT "ID of corresponding event in unified log",
−
   `ID`          INT   NOT NULL   AUTO_INCREMENT,
+
   `ID_StkBin`   INT             NOT NULL COMMENT "stock item is being moved to or from this bin",
−
   `ID_Stock`    INT   NOT NULL   COMMENT "stk_items.ID of item being moved",
+
  `ID_StkLine`  INT              NOT NULL COMMENT "stk_items.ID of item in this bin being moved",
−
   `ID_Bin`       INT   NOT NULL   COMMENT "stock item is being moved to or from this bin",
+
  `CH_OthType`  CHAR            NOT NULL COMMENT "type for 'other' container & line",
−
   `ID_Item`      INT /*NOT NULL*/ COMMENT "cat_items.ID of item being moved",
+
  `ID_OthCont`  INT          DEFAULT NULL COMMENT "[container type's table].ID of the Other place",
−
   `QtyBefore`    INT /*NOT NULL*/ COMMENT "quantity found in this stock line before the move",
+
  `ID_OthLine`  INT          DEFAULT NULL COMMENT "[container type's line table].ID of of the Other place",
−
   `QtyAdded`    INT   NOT NULL   COMMENT "quantity moved (Qty fields at targ/dest may change value later)",
+
  `IDS_OthCont`  VARCHAR(31)  DEFAULT NULL COMMENT "container.IDS of the Other place",
−
   `QtyAfter`    INT   NOT NULL   COMMENT "quantity remaining in this stock line after the move",
+
   `ID_Item`      INT             NOT NULL COMMENT "cat_items.ID of item being moved",
−
   `QtyBinBefore` INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin before the move",
+
   `QtyBefore`    INT         DEFAULT NULL COMMENT "quantity found in this stock line before the move",
−
   `QtyBinAfter`  INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin after the move",
+
   `QtyAdded`    INT             NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)",
−
   `When`        DATETIME      NOT NULL   COMMENT "when the move happened",
+
   `QtyAfter`    INT         DEFAULT NULL COMMENT "quantity remaining in this stock line after the move",
−
   `IDS_Cont`    VARCHAR(31) /*NOT NULL*/  COMMENT "_stk_containers.IDS of where the item was moved to/from",
+
   `QtyBinBefore` INT         DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin before the move",
−
   `ID_Line`     VARCHAR(31) /*NOT NULL*/  COMMENT "hypothetical _stk_lines.IDS of where item was moved to/from",
+
   `QtyBinAfter`  INT         DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin after the move",
−
   `What`         VARCHAR(63) DEFAULT NULL COMMENT "brief automatic description of operation",
+
   `WhenStarted` DATETIME         NOT NULL COMMENT "when the move was started",
−
   `Notes`       VARCHAR(255) DEFAULT NULL COMMENT "optional human-added explanatory notes",
+
   `WhenFinished` DATETIME     DEFAULT NULL COMMENT "when the move was completed",
 +
  `What`        TEXT        DEFAULT NULL COMMENT "automatic, succinct description of operation",
 +
   `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",
 +
  `Notes`        TINYTEXT            COMMENT "optional human-added explanatory notes",
 
   PRIMARY KEY(`ID`)
 
   PRIMARY KEY(`ID`)
−
) ENGINE = MYISAM;</mysql>
+
) ENGINE = INNODB;</source>
−
<section end=sql />
+
===Migration===
 +
The following was used to migrate records from what is now "stk_history OLD 2":
 +
<source lang=mysql>INSERT INTO stk_history_new (
 +
  ID_Event,
 +
  ID_StkBin,
 +
  ID_StkLine,
 +
  CH_OthType,
 +
  ID_OthCont,
 +
  ID_OthLine,
 +
  IDS_OthCont,
 +
  ID_Item,
 +
  QtyBefore,
 +
  QtyAdded,
 +
  QtyAfter,
 +
  QtyBinBefore,
 +
  QtyBinAfter,
 +
  WhenStarted,
 +
  WhenFinished,
 +
  What,
 +
  WhoAdmin,
 +
  WhoSystem,
 +
  WhoNetwork,
 +
  Notes
 +
  )
 +
SELECT
 +
  ID_Event,
 +
  ID_StkBin,
 +
  ID_StkLine,
 +
  CH_OthType,
 +
  ID_OthCont,
 +
  ID_OthLine,
 +
  IDS_OthCont,
 +
  ID_Item,
 +
  QtyBefore,
 +
  QtyAdded,
 +
  QtyAfter,
 +
  QtyBinBefore,
 +
  QtyBinAfter,
 +
  WhenStarted,
 +
  WhenFinished,
 +
  What,
 +
  WhoAdmin,
 +
  'from old stk_history',
 +
  WhoNetwork,
 +
  Notes
 +
FROM stk_history</source>
 +
There were no errors or warnings.

Latest revision as of 15:59, 3 February 2020

About

  • Purpose: log of all stock movement
  • Refers to: qryStock_containers, cat_items
  • Rules:
    • Stock items must always be moved to or from a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
    • The sign of QtyAdded indicates whether the movement was to or from.

Fields

  • CH_OthType: appropriate type code -- see qryStock_containers for codes
  • IDS_OthCont: type code plus separator (period) plus ID unique to that type
  • ID_Event: where known, this is the ID of the corresponding event in event_log
    • This is a sort of first step towards making this log part of the unified event log.
  • WhoSystem: is optional and possibly redundant, but seems to be useful for marking imported/migrated records.

Future:

  • Rename QtyBefore and QtyAfter to QtyLineBefore and QtyLineAfter.

History

  • 2008-12-26 Changed field names, added new fields:
    • QtyFound → QtyBefore – quantity in this stock line before moving
    • QtyDone → QtyAdded – quantity added to this stock line
    • QtyLeft → QtyAfter – quantity in this stock line after moving
    • + QtyTotBefore – total of item in this bin before move)
    • + QtyTotAfter – total of item in this bin after move)
  • 2008-12-27 More field name changes, to show grouping of local ("stock") and external ("other") locations:
    • ID_Stock → ID_StkLine
    • ID_Bin → ID_StkBin
    • ID_Cont → ID_OthCont
    • ID_Line → ID_OthLine
  • 2008-12-29 Final revisions to match what I ended up with in Access for the migration:
    • +CH_OthType
    • +ID_OthCont
    • Created stk_history_legacy to handle legacy data properly; turned on all NOT NULLs
  • 2009-12-20 Added fields WhoAdmin, WhoSystem, WhoNetwork
  • 2009-12-22 Renamed When -> WhenStarted; added WhenFinished; QtyAfter and QtyBinAfter can now be NULL so we can record them when the event is complete.
  • 2010-12-01 Added ID_Event field -- working towards including this in unified event logging
  • 2014-05-26 Made fields DEFAULT NULL instead of NOT NULL so we don't have to know all of them before the event is started: ID_StkLine, ID_OthCont, ID_OthLine, IDS_OthCont
    • Left these as NOT NULL: ID_StkBin, CH_OthType -- Bins must be explicitly created by an administrator, and we should always know what type of container the Other is.
  • 2014-06-26 Lengthened What from 63 chars to 255 because descriptions were being cut off.
  • 2016-02-24 Apparently I never changed the actual DB; this time, lengthened What from 63 chars to TEXT.
  • 2016-03-03
    • No NULL values found in ID_StkLine (or in legacy data), so changing it to NOT NULL.
    • Setting additional NOT NULL fields to DEFAULT NULL in order to accommodate stk_history_legacy data: QtyBefore, QtyBinBefore
      • I decided it was better to tolerate incomplete event-record data than to risk losing the record altogether when an event-write attempt doesn't include all the required fields, since there's currently no guarantee that a failed write will generate an alert anywhere.
    • Officially changing engine to INNODB. Migrated data from stk_history_legacy and then (what is now) `stk_history OLD 2`.

Notes

  • The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).

SQL

CREATE TABLE `stk_history` (
  `ID`           INT              NOT NULL AUTO_INCREMENT,
  `ID_Event`     INT          DEFAULT NULL COMMENT "ID of corresponding event in unified log",
  `ID_StkBin`    INT              NOT NULL COMMENT "stock item is being moved to or from this bin",
  `ID_StkLine`   INT              NOT NULL COMMENT "stk_items.ID of item in this bin being moved",
  `CH_OthType`   CHAR             NOT NULL COMMENT "type for 'other' container & line",
  `ID_OthCont`   INT          DEFAULT NULL COMMENT "[container type's table].ID of the Other place",
  `ID_OthLine`   INT          DEFAULT NULL COMMENT "[container type's line table].ID of of the Other place",
  `IDS_OthCont`  VARCHAR(31)  DEFAULT NULL COMMENT "container.IDS of the Other place",
  `ID_Item`      INT              NOT NULL COMMENT "cat_items.ID of item being moved",
  `QtyBefore`    INT          DEFAULT NULL COMMENT "quantity found in this stock line before the move",
  `QtyAdded`     INT              NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)",
  `QtyAfter`     INT          DEFAULT NULL COMMENT "quantity remaining in this stock line after the move",
  `QtyBinBefore` INT          DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin before the move",
  `QtyBinAfter`  INT          DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin after the move",
  `WhenStarted`  DATETIME         NOT NULL COMMENT "when the move was started",
  `WhenFinished` DATETIME     DEFAULT NULL COMMENT "when the move was completed",
  `What`         TEXT         DEFAULT NULL COMMENT "automatic, succinct description of operation",
  `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",
  `Notes`        TINYTEXT             COMMENT "optional human-added explanatory notes",
  PRIMARY KEY(`ID`)
) ENGINE = INNODB;

Migration

The following was used to migrate records from what is now "stk_history OLD 2": 

INSERT INTO stk_history_new (
  ID_Event,
  ID_StkBin,
  ID_StkLine,
  CH_OthType,
  ID_OthCont,
  ID_OthLine,
  IDS_OthCont,
  ID_Item,
  QtyBefore,
  QtyAdded,
  QtyAfter,
  QtyBinBefore,
  QtyBinAfter,
  WhenStarted,
  WhenFinished,
  What,
  WhoAdmin,
  WhoSystem,
  WhoNetwork,
  Notes
  )
SELECT
  ID_Event,
  ID_StkBin,
  ID_StkLine,
  CH_OthType,
  ID_OthCont,
  ID_OthLine,
  IDS_OthCont,
  ID_Item,
  QtyBefore,
  QtyAdded,
  QtyAfter,
  QtyBinBefore,
  QtyBinAfter,
  WhenStarted,
  WhenFinished,
  What,
  WhoAdmin,
  'from old stk_history',
  WhoNetwork,
  Notes
FROM stk_history

There were no errors or warnings.

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/stk_history&oldid=25901"