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
• 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>