VbzCart/tables/cust addr - Revision history

Woozle: /* Investigation */

2019-10-27T21:14:40Z

Investigation

← Older revision		Revision as of 21:14, 27 October 2019
Line 113:		Line 113:

	==Investigation==		==Investigation==
			This seemed useful originally, but at the moment I'm not sure what it's good for:
	<source lang=mysql>SELECT		<source lang=mysql>SELECT
	COUNT(cn.ID) AS NameCount,		COUNT(cn.ID) AS NameCount,
Line 122:		Line 123:
	GROUP BY ca.ID		GROUP BY ca.ID
	HAVING COUNT(cn.ID) > 1</source>		HAVING COUNT(cn.ID) > 1</source>
			This finds order records where the name is known and matches a customer-name record but the name record is not identified. Problem, though: could be more than one customer with the same name.
			<source lang=mysql>SELECT
			cn.ID, o.*
			FROM
			`vbz-vc-dev`.orders AS o
			LEFT JOIN
			cust_names AS cn ON cn.Name = o.BuyerName
			WHERE
			(cn.ID IS NOT NULL)
			AND (o.ID_Buyer IS NULL);</source>

	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle: /* Discarded */

2019-10-27T16:04:41Z

Discarded

@@ Line 112: / Line 112: @@
    FROM cust_addrs_old;</source>
 ==Discarded==
 Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle: /* Migration */ a few fill-ins from related records

2019-10-27T01:16:47Z

Migration: a few fill-ins from related records

← Older revision		Revision as of 01:16, 27 October 2019
Line 49:		Line 49:
	Rename the old table to prevent code from accidentally using it:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`		Rename the old table to prevent code from accidentally using it:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`
	RENAME TO `vbz-vc-dev`.`cust_addrs_old` ;</source>		RENAME TO `vbz-vc-dev`.`cust_addrs_old` ;</source>
			Next, try to fill in as many blank timestamps as possible:
			<source lang=mysql>UPDATE cust_addrs_old AS ca
			LEFT JOIN
			orders AS o ON o.ID_Buyer = ca.ID_Cust
			SET
			ca.WhenEnt = o.WhenPorted
			WHERE
			(ca.WhenEnt = 0) AND (o.WhenPorted IS NOT NULL);

			UPDATE cust_addrs_old AS ca
			LEFT JOIN
			orders AS o ON o.ID_Recip = ca.ID_Cust
			SET
			ca.WhenEnt = o.WhenPorted
			WHERE
			(ca.WhenEnt = 0) AND (o.WhenPorted IS NOT NULL);</source>
			Since we're working with legacy data here, which is simply ''missing'' a lot of timestamp info, it is now necessary to override the canonical definition of WhenEnt by allowing it to be NOT NULL:
			<source lang=mysql>ALTER TABLE `cust_addr`
			CHANGE COLUMN `WhenEnt` `WhenEnt` DATETIME NULL COMMENT 'when record was first created' ;</source>

	Then import the main data:		Then import the main data:
	<source lang=mysql>INSERT `cust_addr` (		<source lang=mysql>INSERT `cust_addr` (
Line 76:		Line 96:
	`WhenAct`,		`WhenAct`,
	`WhenExp`,		`WhenExp`,
	`WhenEnt`,		IF(WhenEnt = 0,NULL,WhenEnt),
	`WhenUpd`,		`WhenUpd`,
	`WhenVoid`,		`WhenVoid`,
Line 91:		Line 111:
	`Descr`		`Descr`
	FROM cust_addrs_old;</source>		FROM cust_addrs_old;</source>
	~~To be fixed: Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'WhenEnt' at row 1~~
	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle: Woozle moved page VbzCart/tables/cust addrs to VbzCart/tables/cust addr: table renamed

2019-10-26T22:50:46Z

Woozle moved page VbzCart/tables/cust addrs to VbzCart/tables/cust addr: table renamed

← Older revision	Revision as of 22:50, 26 October 2019
(No difference)

Woozle at 18:04, 26 October 2019

2019-10-26T18:04:39Z

← Older revision		Revision as of 18:04, 26 October 2019
Line 21:		Line 21:
	** '''NameSearch''': NEW		** '''NameSearch''': NEW
	** '''Search_raw''': REMOVED		** '''Search_raw''': REMOVED
			* '''2019-10-26''' renaming from <code>cust_addrs</code> to <code>cust_addr</code>
	==SQL==		==SQL==
	<source lang=mysql>CREATE TABLE `~~cust_addrs~~` (		<source lang=mysql>CREATE TABLE `cust_addr` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
Line 46:		Line 47:
	ENGINE = InnoDB;</source>		ENGINE = InnoDB;</source>
	==Migration==		==Migration==
	<source lang=mysql>INSERT `~~cust_addrs_new~~` (		Rename the old table to prevent code from accidentally using it:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`
			RENAME TO `vbz-vc-dev`.`cust_addrs_old` ;</source>
			Then import the main data:
			<source lang=mysql>INSERT `cust_addr` (
	`ID`,		`ID`,
	`ID_Cust`,		`ID_Cust`,
Line 86:		Line 90:
	`Extra`,		`Extra`,
	`Descr`		`Descr`
	FROM ~~cust_addrs~~;</source>		FROM cust_addrs_old;</source>
	~~Then rename the tables~~:~~<source lang=mysql>ALTER TABLE `vbz-vc-dev`~~.~~`cust_addrs`~~		To be fixed: Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'WhenEnt' at row 1
	~~RENAME TO `vbz~~-vc-~~dev`.`cust_addrs_old` ;~~
	~~ALTER TABLE `vbz-vc-dev`.`cust_addrs_new`~~
	~~RENAME TO `vbz-vc-dev`.`cust_addrs` ;~~
	~~</source>~~
	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle at 17:37, 26 October 2019

2019-10-26T17:37:02Z

← Older revision		Revision as of 17:37, 26 October 2019
Line 87:		Line 87:
	`Descr`		`Descr`
	FROM cust_addrs;</source>		FROM cust_addrs;</source>
			Then rename the tables:<source lang=mysql>ALTER TABLE `vbz-vc-dev`.`cust_addrs`
			RENAME TO `vbz-vc-dev`.`cust_addrs_old` ;
			ALTER TABLE `vbz-vc-dev`.`cust_addrs_new`
			RENAME TO `vbz-vc-dev`.`cust_addrs` ;
			</source>
	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle: fixed migration SQL part 1

2019-10-26T17:34:56Z

fixed migration SQL part 1

← Older revision		Revision as of 17:34, 26 October 2019
Line 46:		Line 46:
	ENGINE = InnoDB;</source>		ENGINE = InnoDB;</source>
	==Migration==		==Migration==
	~~This is not syntactically correct yet:~~
	<source lang=mysql>INSERT `cust_addrs_new` (		<source lang=mysql>INSERT `cust_addrs_new` (
	`ID`,		`ID`,
Line 70:		Line 69:
	`ID`,		`ID`,
	`ID_Cust`,		`ID_Cust`,
	`Label`		`Label`,
	`WhenAct`,		`WhenAct`,
	`WhenExp`,		`WhenExp`,
Line 88:		Line 87:
	`Descr`		`Descr`
	FROM cust_addrs;</source>		FROM cust_addrs;</source>
	~~"Error Code: 1136. Column count doesn't match value count at row 1"~~
	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle: migration

2019-10-25T20:58:35Z

migration

@@ Line 9: / Line 9: @@
 ** '''Search_raw''' should probably be replaced by a hash at some point, because it's just a value to make it easier to detect when Search needs recalculation - it's currently (I think) just '''Name''', '''Street''', '''Town''', '''State''', '''Zip''', and '''Country''' fields appended together (with linebreaks).
 ** '''State''' probably needs to be separate fields for as-entered and code-if-known
-* '''History''':
+==History==
-** '''2009-07-09''' Moved to separate wiki page; no design changes
+* '''2009-07-09''' Moved to separate wiki page; no design changes
-** '''2011-11-22''' Added '''WhenEnt''' and '''WhenUpd'''
+* '''2011-11-22''' Added '''WhenEnt''' and '''WhenUpd'''
-** '''2012-01-03''' Added '''WhenVoid'''
+* '''2012-01-03''' Added '''WhenVoid'''
-** '''2012-01-11''' Tentative definitions for '''Full''' and  '''Search'''; added ID_Country
+* '''2012-01-11''' Tentative definitions for '''Full''' and  '''Search'''; added ID_Country
-** '''2013-11-26''' Renamed '''Name''' to '''Label'''; added 255-char '''Name''' field for addressee name
+* '''2013-11-26''' Renamed '''Name''' to '''Label'''; added 255-char '''Name''' field for addressee name
-** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
+* '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
 ==SQL==
 <source lang=mysql>CREATE TABLE `cust_addrs` (
@@ Line 26: / Line 31: @@
    `WhenUpd`    DATETIME DEFAULT NULL COMMENT "when record was last updated",
    `WhenVoid`   DATETIME DEFAULT NULL COMMENT "when this record was voided",
    `Name`       VARCHAR(255)          COMMENT "name of addressee",
    `Street`     VARCHAR(127)          COMMENT "address within town (street, apt., etc.)",
    `Town`       VARCHAR(63)           COMMENT "name of town",
@@ Line 40: / Line 45: @@
+)
 ENGINE = InnoDB;</source>
 ==Discarded==
 Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.

Woozle at 14:47, 1 August 2019

2019-08-01T14:47:58Z

← Older revision		Revision as of 14:47, 1 August 2019
Line 17:		Line 17:
	** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.		** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cust_addrs` (		<source lang=mysql>CREATE TABLE `cust_addrs` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
Line 39:		Line 39:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = ~~MYISAM~~;</~~mysql~~>		ENGINE = InnoDB;</source>
	==Discarded==		==Discarded==
	Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.		Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.
	<mysql>		<source lang=mysql>
	`ID_Country` INT NOT NULL COMMENT "ref_country.ID",		`ID_Country` INT NOT NULL COMMENT "ref_country.ID",
	</~~mysql~~>		</source>

Woozle: ID_Country discarded for now

2014-08-31T02:09:33Z

ID_Country discarded for now

← Older revision		Revision as of 02:09, 31 August 2014
Line 15:		Line 15:
	** '''2012-01-11''' Tentative definitions for '''Full''' and '''Search'''; added ID_Country		** '''2012-01-11''' Tentative definitions for '''Full''' and '''Search'''; added ID_Country
	** '''2013-11-26''' Renamed '''Name''' to '''Label'''; added 255-char '''Name''' field for addressee name		** '''2013-11-26''' Renamed '''Name''' to '''Label'''; added 255-char '''Name''' field for addressee name
			** '''2014-08-30''' Removed '''ID_Country''' for now -- not yet implemented in database, and we're not ready for it yet.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `cust_addrs` (		<mysql>CREATE TABLE `cust_addrs` (
Line 34:		Line 35:
	`Zip` VARCHAR(31) COMMENT "zipcode or postal code",		`Zip` VARCHAR(31) COMMENT "zipcode or postal code",
	`Country` VARCHAR(63) COMMENT "name of country as entered by customer",		`Country` VARCHAR(63) COMMENT "name of country as entered by customer",
	~~`ID_Country` INT NOT NULL COMMENT "ref_country.ID",~~
	`Extra` VARCHAR(63) COMMENT "additional instructions to always write on pkg",		`Extra` VARCHAR(63) COMMENT "additional instructions to always write on pkg",
	`Descr` VARCHAR(255) COMMENT "notes on this address",		`Descr` VARCHAR(255) COMMENT "notes on this address",
Line 40:		Line 40:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
			==Discarded==
			Might use this in the future, but things get tricky. Do we get a complete list of countries from somewhere, or do we allow customers to enter new ones? In the latter case, NULL would need to be acceptable.
			<mysql>
			`ID_Country` INT NOT NULL COMMENT "ref_country.ID",
			</mysql>