Difference between revisions of "VbzCart/queries"

Latest revision as of 23:12, 3 March 2016

Navigation

Overview

What MS Access calls "queries" are called "views" in MySQL, i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).

Some common prefixes:

qryCbx_: queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further qryCbx_ queries which show a subset of the results.
qryCat_: queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was qryCatNum_.)

by category

Inactive

/deprecated: queries we're trying to get rid of, once we're sure nothing uses them
/discarded: queries apparently no longer in use

Catalog

/qryCat_Depts
Title-centric:
- /qryTitles_Item_info
- /qryTitles_ItTyps_grpItems
- /qryTitles_ItTyps_ItTyps
- /qryTitles_ItTyps_Titles
- /qryTitles_Imageless - titles with no active images
- /qryCat_Titles
- /qryCat_Titles_Item_stats -- item/stock statistics
- /qryCat_Titles_Item_count -- simpler query for maintenance
- /qryCat_Titles_web
- /qryCbx_Titles
Item-centric:
- /qryCat_Items_Stock: cat_items with stock info
ItTyp-centric:
- /qryItTypsDepts_grpItems
- /qryItTypsDepts_ItTyps
Image-centric:
- /qryImgs_byTitle: Image info by Title
/qryCat_pages: maps http path info to catalog entities

Catalog Items

/qryCbx_Items_data
- /qryCbx_Items
- /qryCbx_Items_active
- /qryCbx_Items_for_sale
- /qryCbx_Items_opt: abbreviated version for contexts where Title is already known
/qryItems_prices: what uses this?

Catalog Sources

Catalog Topics

titles x topics:
- /qryTitleTopic_Titles: more title information
- /qryTitleTopic_Topics: more topic information
- /qryTitleTopic_Title_avail: title availability information

Ordering

Carts

/qryCarts_info
- /qrySub_Carts_info_data
- /qrySub_Carts_info_items

Customers

Orders

Stock

new queries

/qryStk_lines_Title_info

old queries

This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.

Caching

Caching should only be used for catalog display.

/qryCache_Flow_Procs

@@ Line 2: / Line 2: @@
 [[VbzCart]]: [[VbzCart data views|data views]]
 ==Overview==
-"Views" in [[MySQL]] are like SELECT "queries" in [[MS Access]], i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).
+What [[MS Access]] calls "queries" are called "views" in [[MySQL]], i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).
-==Views==
 Some common prefixes:
-* '''qryCbx_''': queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further qryCbx_ queries which show a subset of the results.
+* '''qryCbx_''': queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further '''qryCbx_''' queries which show a subset of the results.
-* '''qryCatNum_''': queries which build catalog numbers, possibly including other information from the tables they draw on in so doing.
+* '''qryCat_''': queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was '''qryCatNum_'''.)
-===qryCbx_Items===
+==by category==
-<sql>SELECT
+===Inactive===
-  i.ID,
+* [[/deprecated]]: queries we're trying to [[/discarded|get rid of]], once we're sure nothing uses them
-  CONCAT(
+* [[/discarded]]: queries apparently no longer in use
-    IF(i.isPulled,CONCAT('!PULLED (',i.ID,')'),''),
-    i.CatNum,
+===Catalog===
-    IF(i.Supp_CatNum IS NULL,'',CONCAT('[',i.SuppCatNum,']')),
+* [[/qryCat_Depts]]
-    IF(i.isForSale,'',' n/a'),
+* '''Title'''-centric:
-    i.Descr)
+** [[/qryTitles_Item_info]]
-      AS Descr,
+** [[/qryTitles_ItTyps_grpItems]]
-  i.isForSale
+** [[/qryTitles_ItTyps_ItTyps]]
-FROM qryCatNum_Items AS i
+** [[/qryTitles_ItTyps_Titles]]
-ORDER BY NOT i.isPulled, i.isForSale, i.CatNum;</sql>
+** [[/qryTitles_Imageless]] - titles with no active images
+** [[/qryCat_Titles]]
+** [[/qryCat_Titles_Item_stats]] -- item/stock statistics
+** [[/qryCat_Titles_Item_count]] -- simpler query for maintenance
+** [[/qryCat_Titles_web]]
+** [[/qryCbx_Titles]]
+* '''Item'''-centric:
+** [[/qryCat_Items_Stock]]: cat_items with stock info
+* '''ItTyp'''-centric:
+** [[/qryItTypsDepts_grpItems]]
+** [[/qryItTypsDepts_ItTyps]]
+* '''Image'''-centric:
+** [[/qryImgs_byTitle]]: Image info by Title
+* [[/qryCat_pages]]: maps http path info to catalog entities
+====Catalog Items====
+* [[/qryCbx_Items_data]]
+** [[/qryCbx_Items]]
+** [[/qryCbx_Items_active]]
+** [[/qryCbx_Items_for_sale]]
+** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
+* [[/qryItems_prices]]: what uses this?
+====Catalog Sources====
+* [[/qryCtg_Sources_active]]
+* [[/qryCtg_Items_updates]]
+* [[/qryCtg_Items_updates_joinable]]
+* [[/qryCtg_Items_active]]
+* <s>[[/qryCtg_build_sub]]</s>
+* <s>[[/qryCtg_build]]</s>
+* '''[[VbzCart catalog building|building]] process''':
+** [[/qryCtg_Items_forUpdJoin]]
+** [[/qryCtg_Upd_join]]
+** [[/qryCtg_src_dups]]
+** [[/qryCtgCk_dup_keys]]
+====Catalog Topics====
+* '''titles x topics''':
+** [[/qryTitleTopic_Titles]]: more title information
+** [[/qryTitleTopic_Topics]]: more topic information
+** [[/qryTitleTopic_Title_avail]]: title availability information
+===Ordering===
+====Carts====
+* [[/qryCarts_info]]
+** [[/qrySub_Carts_info_data]]
+** [[/qrySub_Carts_info_items]]
+====Customers====
+* [[/qryCustAddrs]]
+* [[/qryCbx_CustNames]]
+====Orders====
+* [[/qryCbx_Orders]]
+* [[/qryOrderLines_notPkgd]]
+* [[/qryOrders_Active]]
+* [[/qryOrderLines_Active]]
+* [[/qtyOrderItems_Active]]
+* [[/qry_PkgItem_qtys_byOrder]]
+* [[/qryOrdItms_Pkg_qtys]]
+* [[/qryOrdItms_open]]
+* [[/qryItms_open]]
+* [[/qryItms_to_restock_union]]
+* [[/qryItms_to_restock]]
+====Packages====
+* [[/qryPkgLines_byOrdLine_andItem]]
+* [[/qryOrdLines_PkgdQtys]]
+* [[/qryOrdLines_open]]
+* [[/qryOrders_Pulled]]
+* [[/qryPkgs_Pull_status]]
+* for reports:
+** [[/qryRpt_Pkg_Lines]]
+*** [[/qryPkgLines_qtys_done]]
+*** <s>[[/qryPkgLines_qtys_done_ord_sum]]</s> - not used
+** [[/qryRpt_Pkg_Trx]]
+====Restocks====
+* '''all restock requests''':
+** [[/qryRstks_info]]
+** [[/qryRstkReq_Item_Rcd_status]]
+** [[/qryRstkReq_Item_status]]
+*** [[/qryRstkReq_Item_status_Req_info]]
+*** [[/qryRstkReq_Items_expected]]: show only expected items
+** [[/qryCbx_RstkReq]]
+*** [[/qryRstkReq_by_status]]
+*** [[/qryRstkReq_by_PurchOrd]]
+* '''filtered by status''':
+** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed - see {{l/vc/term|restock/status}})
+*** [[/qryRstks_unsent]]: created but not ordered yet
+**** [[/qryRstkItms_unsent]]
+***** [[/qryRstkItms_unsent_for_order]]
+** [[/qryRstks_inactive]]: all the rest
+====Shipping====
+* [[/qryPkgs_status]]
+===Stock===
+====new queries====
+* [[/qryStk_Bins_w_info]]
+* [[/qryStk_lines_remaining]]
+** [[/qryStk_lines_remaining_byBin]]
+** [[/qryStk_lines_remaining_forSale]]
+*** [[/qryStkItms_for_sale]]
+* [[/qryStk_items_remaining]]
+* [[/qryStk_byItem_byBin]]
+* [[/qryStk_lines_Title_info]]
+====old queries====
+This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
+* [[/v_stk_titles_remaining]]
+* [[/v_stk_byItemAndBin_wItemInfo]]
+===Caching===
+Caching should only be used for catalog display.
+* [[/qryCache_Flow_Procs]]