VbzCart/queries/qryCat pages

About

 * Purpose: Maps {path info from http requests} to specific titles, departments, and suppliers; may eventually map to other page types
 * Requires:, , ,
 * 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
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'));

Archive
This is how the query was actually defined (which didn't work anymore, by 2009-12-10): 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'))