Difference between revisions of "VbzCart/tables/stk history legacy"
Jump to navigation
Jump to search
(changes, round 1: NOT NULLing) |
(merged & deprecated) |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
| + | * '''Status''' Deprecated. | ||
| + | ** This table is no longer needed, and is not really something that needs to be part of VbzCart proper. Its only use, other than dealing with imported data from older versions of VbzCart, would be an aid in importing from other systems -- and it would probably be better just to hand-craft any intermediate tables for that purpose. | ||
* '''Purpose''': For accommodating old stock history data (possibly imported from another application) where some rows don't meet the stricter requirements of {{vbzcart/table|stk_history}}. This table should have all the same fields as that table, but some fields may have looser requirements and additional fields may be added. | * '''Purpose''': For accommodating old stock history data (possibly imported from another application) where some rows don't meet the stricter requirements of {{vbzcart/table|stk_history}}. This table should have all the same fields as that table, but some fields may have looser requirements and additional fields may be added. | ||
* '''Usage''': Feel free to NOT-NULLify any existing fields, or even add additional fields to preserve your old data. If you have no old data to deal with, this table is unnecessary; you can either leave it empty, or modify {{vbzcart/query|qryStk_History}} not to require it. | * '''Usage''': Feel free to NOT-NULLify any existing fields, or even add additional fields to preserve your old data. If you have no old data to deal with, this table is unnecessary; you can either leave it empty, or modify {{vbzcart/query|qryStk_History}} not to require it. | ||
| Line 7: | Line 9: | ||
*** Found that there were no NULL values in my data for '''ID_StkBin''', '''ID_StkLine''', '''CH_OthType''', '''IDS_OthCont''', '''QtyAdded''' -- so made them NOT NULL | *** Found that there were no NULL values in my data for '''ID_StkBin''', '''ID_StkLine''', '''CH_OthType''', '''IDS_OthCont''', '''QtyAdded''' -- so made them NOT NULL | ||
**** Actually, '''QtyAdded''' was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly. | **** Actually, '''QtyAdded''' was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly. | ||
| + | ** '''2016-03-03''' | ||
| + | *** Preparing to merge with {{l/vc/table|stk_history}}: set NULL ID_Item fields by looking up stk_lines.ID_Item from ID_StkLine. ID_Item can now be NOT NULL, as in stk_history. | ||
| + | *** Merged with stk_history into updated (INNODB) stk_history table, and deprecated. | ||
==SQL== | ==SQL== | ||
<mysql>CREATE TABLE `stk_history_legacy` ( | <mysql>CREATE TABLE `stk_history_legacy` ( | ||
| Line 16: | Line 21: | ||
`ID_OthLine` INT /*NOT NULL*/ COMMENT "[container type's line table].ID of of the Other place", | `ID_OthLine` INT /*NOT NULL*/ COMMENT "[container type's line table].ID of of the Other place", | ||
`IDS_OthCont` VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place", | `IDS_OthCont` VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place", | ||
| − | `ID_Item` INT | + | `ID_Item` INT NOT NULL COMMENT "cat_items.ID of item being moved", |
`QtyBefore` INT /*NOT NULL*/ COMMENT "quantity found in this stock line before the move", | `QtyBefore` INT /*NOT 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)", | `QtyAdded` INT NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)", | ||
| Line 38: | Line 43: | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
| + | ===Migration=== | ||
| + | This was used to copy stk_history_legacy data into stk_history_new, to be renamed stk_history: | ||
| + | <mysql>INSERT INTO stk_history_new ( | ||
| + | ID_StkBin, | ||
| + | ID_StkLine, | ||
| + | CH_OthType, | ||
| + | ID_OthCont, | ||
| + | ID_OthLine, | ||
| + | IDS_OthCont, | ||
| + | ID_Item, | ||
| + | QtyBefore, | ||
| + | QtyAdded, | ||
| + | QtyAfter, | ||
| + | QtyBinBefore, | ||
| + | QtyBinAfter, | ||
| + | WhenStarted, | ||
| + | What, | ||
| + | WhoAdmin, | ||
| + | WhoSystem, | ||
| + | WhoNetwork, | ||
| + | Notes | ||
| + | ) | ||
| + | SELECT | ||
| + | ID_StkBin, | ||
| + | ID_StkLine, | ||
| + | CH_OthType, | ||
| + | ID_OthCont, | ||
| + | ID_OthLine, | ||
| + | IDS_OthCont, | ||
| + | ID_Item, | ||
| + | QtyBefore, | ||
| + | QtyAdded, | ||
| + | QtyAfter, | ||
| + | QtyBinBefore, | ||
| + | QtyBinAfter, | ||
| + | `When`, | ||
| + | What, | ||
| + | 'woozle', | ||
| + | 'from stk_history_legacy', | ||
| + | '50.111.67.243', | ||
| + | Notes | ||
| + | FROM stk_history_legacy | ||
| + | </mysql> | ||
| + | Note that there were some NULL values in When, which were translated into "zero date" values in stk_history_new. At the moment, I can't think of any way to recover those timestamps. | ||
Latest revision as of 22:56, 3 March 2016
About
- Status Deprecated.
- This table is no longer needed, and is not really something that needs to be part of VbzCart proper. Its only use, other than dealing with imported data from older versions of VbzCart, would be an aid in importing from other systems -- and it would probably be better just to hand-craft any intermediate tables for that purpose.
- Purpose: For accommodating old stock history data (possibly imported from another application) where some rows don't meet the stricter requirements of
- REDIRECT Template:l/vc/table. This table should have all the same fields as that table, but some fields may have looser requirements and additional fields may be added.
- Usage: Feel free to NOT-NULLify any existing fields, or even add additional fields to preserve your old data. If you have no old data to deal with, this table is unnecessary; you can either leave it empty, or modify
- REDIRECT Template:l/vc/query not to require it.
- History:
- 2008-12-29 Created so that old data could be accommodated without compromising integrity of new data added to stk_history.
- 2016-03-02
- Found that there were no NULL values in my data for ID_StkBin, ID_StkLine, CH_OthType, IDS_OthCont, QtyAdded -- so made them NOT NULL
- Actually, QtyAdded was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly.
- Found that there were no NULL values in my data for ID_StkBin, ID_StkLine, CH_OthType, IDS_OthCont, QtyAdded -- so made them NOT NULL
- 2016-03-03
- Preparing to merge with stk_history: set NULL ID_Item fields by looking up stk_lines.ID_Item from ID_StkLine. ID_Item can now be NOT NULL, as in stk_history.
- Merged with stk_history into updated (INNODB) stk_history table, and deprecated.
SQL
<mysql>CREATE TABLE `stk_history_legacy` (
`ID` INT NOT NULL AUTO_INCREMENT, `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 /*NOT NULL*/ COMMENT "[container type's table].ID of the Other place", `ID_OthLine` INT /*NOT NULL*/ COMMENT "[container type's line table].ID of of the Other place", `IDS_OthCont` VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place", `ID_Item` INT NOT NULL COMMENT "cat_items.ID of item being moved", `QtyBefore` INT /*NOT 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 /*NOT NULL*/ COMMENT "quantity remaining in this stock line after the move", `QtyBinBefore` INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin before the move", `QtyBinAfter` INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin after the move", `When` DATETIME /*NOT NULL*/ COMMENT "when the move happened", `What` VARCHAR(63) DEFAULT NULL COMMENT "brief automatic description of operation", `Notes` TINYTEXT DEFAULT NULL COMMENT "optional human-added explanatory notes",
/* fields specific to vbz.net data migration -- these may be omitted for normal usage */
`oIDS_ContSrce` VARCHAR(31), `oIDS_LineSrce` VARCHAR(31), `oIDS_ContDest` VARCHAR(31), `oIDS_LineDest` VARCHAR(31), `oID_StkBin` INT, `oID_StkLine` INT, `oQtyFnd` INT, `oQtyDone` INT, `oQtyLeft` INT, `wasSrce` BIT COMMENT "how the data was handled at migration: TRUE = stock is source", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>
Migration
This was used to copy stk_history_legacy data into stk_history_new, to be renamed stk_history: <mysql>INSERT INTO stk_history_new (
ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, WhenStarted, What, WhoAdmin, WhoSystem, WhoNetwork, Notes )
SELECT
ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, `When`, What, 'woozle', 'from stk_history_legacy', '50.111.67.243', Notes
FROM stk_history_legacy </mysql> Note that there were some NULL values in When, which were translated into "zero date" values in stk_history_new. At the moment, I can't think of any way to recover those timestamps.