Difference between revisions of "VbzCart/queries/qryCat pages"
Jump to navigation
Jump to search
m (→SQL: replaced missing underscore) |
(documented data sources) |
||
| (One intermediate revision by the same user not shown) | |||
| 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 | ||
| + | ** '''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 | + | <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 17: | ||
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 24: | ||
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 31: | ||
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) | ||
Latest revision as of 02:06, 12 November 2010
About
- Purpose: Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types
- Requires: cat_supp,
- REDIRECT Template:l/vc/query, qryCat_Titles, cat_images
- History:
- 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
<mysql>CREATE OR REPLACE VIEW `qryCat_pages` AS
SELECT
concat_ws('-','S',s.ID) AS AB,
lcase(s.CatKey) AS Path,
s.ID AS ID_Row,
'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 AS ID_Row,
'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 AS ID_Row,
'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 AS ID_Row,
'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>
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>