Difference between revisions of "VbzCart/queries/qryCat pages"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(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-…')
 
(documented data sources)
 
(3 intermediate revisions 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 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 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)
 
     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>

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,
  1. 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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qryCat_pages&oldid=14446"