VbzCart/queries/qryCat pages - Revision history

Woozle: documented data sources

2010-11-12T02:06:16Z

documented data sources

← Older revision		Revision as of 02:06, 12 November 2010
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types		* '''Purpose''': Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types
			* '''Requires''': {{vbzcart\|table\|cat_supp}}, {{vbzcart/query\|qryCat_Depts}}, {{vbzcart/query\|qryCat_Titles}}, {{vbzcart\|table\|cat_images}}
	* '''History''':		* '''History''':
	** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names		** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names

Woozle: ID->ID_Row

2010-11-08T20:00:01Z

ID->ID_Row

← Older revision		Revision as of 20:00, 8 November 2010
Line 3:		Line 3:
	* '''History''':		* '''History''':
	** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names		** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names
			** '''2010-11-08''' Renaming ID to ID_Row so this query can be used as a drop-in for cat_pages
	==SQL==		==SQL==
	<mysql>CREATE OR REPLACE ~~SQL SECURITY DEFINER~~ VIEW `qryCat_pages` AS		<mysql>CREATE OR REPLACE VIEW `qryCat_pages` AS
	SELECT		SELECT
	concat_ws('-','S',s.ID) AS AB,		concat_ws('-','S',s.ID) AS AB,
	lcase(s.CatKey) AS Path,		lcase(s.CatKey) AS Path,
	s.ID,		s.ID AS ID_Row,
	'S' AS Type		'S' AS Type
	FROM cat_supp AS s		FROM cat_supp AS s
Line 15:		Line 16:
	concat_ws('-','D',d.ID) AS AB,		concat_ws('-','D',d.ID) AS AB,
	lcase(d.CatWeb_Dept) AS Path,		lcase(d.CatWeb_Dept) AS Path,
	d.ID,		d.ID AS ID_Row,
	'D' AS Type		'D' AS Type
	FROM qryCat_Depts AS d		FROM qryCat_Depts AS d
Line 22:		Line 23:
	concat_ws('-','T',t.ID) AS AB,		concat_ws('-','T',t.ID) AS AB,
	replace(lcase(t.CatWeb),'-','/') AS Path,		replace(lcase(t.CatWeb),'-','/') AS Path,
	t.ID,		t.ID AS ID_Row,
	'T' AS Type		'T' AS Type
	FROM qryCat_Titles AS t		FROM qryCat_Titles AS t
Line 29:		Line 30:
	concat_ws('-','I',i.ID) AS AB,		concat_ws('-','I',i.ID) AS AB,
	lcase(concat_ws('/',replace(t.CatWeb,'-','/'),i.AttrFldr,i.Ab_Size)) AS Path,		lcase(concat_ws('/',replace(t.CatWeb,'-','/'),i.AttrFldr,i.Ab_Size)) AS Path,
	i.ID,		i.ID AS ID_Row,
	'I' AS Type		'I' AS Type
	FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID)		FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID)

Woozle: /* SQL */ replaced missing underscore

2009-12-11T12:06:08Z

SQL: replaced missing underscore

← Older revision		Revision as of 12:06, 11 December 2009
Line 4:		Line 4:
	** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names		** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names
	==SQL==		==SQL==
	<mysql>CREATE OR REPLACE SQL SECURITY DEFINER VIEW `~~qryCat pages~~` AS		<mysql>CREATE OR REPLACE SQL SECURITY DEFINER VIEW `qryCat_pages` AS
	SELECT		SELECT
	concat_ws('-','S',s.ID) AS AB,		concat_ws('-','S',s.ID) AS AB,

Woozle: archive of original definition

2009-12-11T00:29:23Z

archive of original definition

← Older revision		Revision as of 00:29, 11 December 2009
Line 33:		Line 33:
	FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID)		FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID)
	WHERE (i.Ab_Size NOT IN ('th','sm'));</mysql>		WHERE (i.Ab_Size NOT IN ('th','sm'));</mysql>
			===Archive===
			This is how the query was actually defined (which didn't work anymore, by 2009-12-10):
			<mysql>CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `VbzCart`.`_cat_pages` AS
			select
			concat_ws(_utf8'-',_utf8'S',`VbzCart`.`suppliers`.`ID`) AS `AB`,
			lcase(`VbzCart`.`suppliers`.`CatKey`) AS `Path`,
			`VbzCart`.`suppliers`.`ID` AS `ID`,
			_utf8'S' AS `Type`
			from `suppliers`
			union
			select
			concat_ws(_utf8'-',_utf8'D',`VbzCart`.`_depts`.`ID`) AS `AB`,
			lcase(`VbzCart`.`_depts`.`CatWeb_Dept`) AS `Path`,
			`VbzCart`.`_depts`.`ID` AS `ID`,
			_utf8'D' AS `Type`
			from `_depts`
			where `VbzCart`.`_depts`.`cntForSale`
			union
			select
			concat_ws(_utf8'-',_utf8'T',`VbzCart`.`_titles`.`ID`) AS `AB`,
			replace(lcase(`VbzCart`.`_titles`.`CatWeb`),_latin1'-',_latin1'/') AS `Path`,
			`VbzCart`.`_titles`.`ID` AS `ID`,_utf8'T' AS `Type`
			from `_titles`
			union
			select
			concat_ws(_utf8'-',_utf8'I',`i`.`ID`) AS `AB`,
			lcase(concat_ws(_latin1'/',replace(`t`.`CatWeb`,_latin1'-',_latin1'/'),`i`.`AttrFldr`,`i`.`Ab_Size`)) AS `Path`,
			`i`.`ID` AS `ID`,
			_utf8'I' AS `Type`
			from (`cat_images` `i` left join `_titles` `t` on((`i`.`ID_Title` = `t`.`ID`)))
			where (`i`.`Ab_Size` not in (_latin1'th',_latin1'sm'))</mysql>

Woozle: Created page with '==About== * '''Purpose''': Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types * '''History''': ** '''2009-…'

2009-12-11T00:25:10Z

Created page with '==About== * '''Purpose''': Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types * '''History''': ** '''2009-…'

New page

==About==
* '''Purpose''': Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types
* '''History''':
** '''2009-12-10''' Reconstructed from query _cat_pages as already defined on Rizzo (apparently was not documented), but using newer table/query names
==SQL==
<mysql>CREATE OR REPLACE SQL SECURITY DEFINER VIEW `qryCat pages` AS
SELECT
concat_ws('-','S',s.ID) AS AB,
lcase(s.CatKey) AS Path,
s.ID,
'S' AS Type
FROM cat_supp AS s
UNION
SELECT
concat_ws('-','D',d.ID) AS AB,
lcase(d.CatWeb_Dept) AS Path,
d.ID,
'D' AS Type
FROM qryCat_Depts AS d
UNION
SELECT
concat_ws('-','T',t.ID) AS AB,
replace(lcase(t.CatWeb),'-','/') AS Path,
t.ID,
'T' AS Type
FROM qryCat_Titles AS t
UNION
SELECT
concat_ws('-','I',i.ID) AS AB,
lcase(concat_ws('/',replace(t.CatWeb,'-','/'),i.AttrFldr,i.Ab_Size)) AS Path,
i.ID,
'I' AS Type
FROM (cat_images AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title = t.ID)
WHERE (i.Ab_Size NOT IN ('th','sm'));</mysql>