VbzCart/tables/stk history legacy - Revision history

Woozle: merged & deprecated

2016-03-03T22:56:39Z

merged & deprecated

← Older revision		Revision as of 22:56, 3 March 2016
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.		** '''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` (

Woozle: migration

2016-03-03T22:33:52Z

migration

← Older revision		Revision as of 22:33, 3 March 2016
Line 39:		Line 39:
	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.

Woozle: looked up ID_Item -- can now be NOT NULL

2016-03-03T21:52:11Z

looked up ID_Item -- can now be NOT NULL

← Older revision		Revision as of 21:52, 3 March 2016
Line 7:		Line 7:
	*** 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.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `stk_history_legacy` (		<mysql>CREATE TABLE `stk_history_legacy` (
Line 16:		Line 17:
	`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 /NOT NULL/ COMMENT "cat_items.ID of item being moved",		`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)",

Woozle: changes, round 1: NOT NULLing

2016-03-02T23:21:22Z

changes, round 1: NOT NULLing

Woozle: a little tidying -- trying to figure out what is different from stk_history

2016-03-02T21:15:28Z

a little tidying -- trying to figure out what is different from stk_history

← Older revision		Revision as of 21:15, 2 March 2016
Line 5:		Line 5:
	** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table\|stk_history}}.		** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table\|stk_history}}.
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>~~DROP TABLE IF EXISTS `stk_history_legacy`;~~		<mysql>CREATE TABLE `stk_history_legacy` (

	CREATE TABLE `stk_history_legacy` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_StkBin` INT /NOT NULL/ COMMENT "stock item is being moved to or from this bin",		`ID_StkBin` INT /NOT NULL/ COMMENT "stock item is being moved to or from this bin",
Line 37:		Line 35:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	) ENGINE = MYISAM;</mysql>		) ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

Woozle: for legacy data

2008-12-29T18:48:33Z

for legacy data

New page

==About==
* '''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.
* '''History''':
** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table|stk_history}}.
==SQL==
<section begin=sql /><mysql>DROP TABLE IF EXISTS `stk_history_legacy`;

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>
<section end=sql />

← Older revision		Revision as of 23:21, 2 March 2016
Line 4:		Line 4:
	* '''History''':		* '''History''':
	** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table\|stk_history}}.		** '''2008-12-29''' Created so that old data could be accommodated without compromising integrity of new data added to {{vbzcart/table\|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.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `stk_history_legacy` (		<mysql>CREATE TABLE `stk_history_legacy` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_StkBin` INT /NOT NULL/ COMMENT "stock item is being moved to or from this bin",		`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",		`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",		`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_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",		`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 /NOT NULL/ COMMENT "cat_items.ID of item being moved",		`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",