Difference between revisions of "VbzCart/queries"

Revision as of 18:03, 8 September 2009

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
/qryTitles_ItTyps_grpItems
/qryTitles_ItTyps_ItTyps
/qryTitles_ItTyps_Titles
/qryItTypsDepts_grpItems
/qryItTypsDepts_ItTyps
/qryCat_Titles
/qryCat_Titles_Item_stats
/qryCat_Titles_web
/qryCbx_Titles
/qryCat_Items_Stock: cat_items with stock info

Catalog Items

/qryCat_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

Ordering

Customers

Orders

Packages

Restocks

all restock requests:
active restocks:
- /qryRstks_active: not terminated = !(closed, orphaned or killed)
- /qryRstkItms_active
- /qryRstks_unsent: created but not ordered yet
- /qryRstkItms_unsent
- /qryRstkItms_unsent_for_order
- /qryRstkItms_expected

terminology

Active = not "terminated", i.e. not "closed", "killed", or "orphaned" (may or may not be "expected" yet)
Closed = received from supplier, nothing remaining on backorder
Expected = placed with supplier, but not yet received
Killed = canceled with supplier after having been placed
Orphaned = we don't have records that anything was received, but nothing further is expected (usually old data)
Terminated = closed, orphaned, or killed
Unsent = created but not yet placed with supplier, i.e. not "expected"

phases:

Time →
Created	(Unsent)	Ordered	(Expected)	Closed, Killed, or Orphaned
--(Active)--				--(Terminated)--

Shipping

/qryPkgs_status

Stock

new queries

/qryStk_Bins_w_info
/qryStk_lines_remaining_byBin
/qryStk_lines_remaining_forSale
/qryStk_lines_Title_info
/qryStkItms_for_sale
/qryStkItms_for_sale_wItem_data
/qryStock_forOpenOrders
/qryStock_byOpt_andType
/qryStock_by_Opt_Type
/qryStock_by_Supp_Type_Opt - unused (and should be Supp_Opt_Type)
/qryStock_Titles_most_recent
for stock history (

REDIRECT Template:l/vc/table):

- /qryStock_containers - generates IDS codes
- /qryStk_History - includes legacy table data

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.

v_data_flow

<mysql> CREATE OR REPLACE VIEW v_data_flow AS

   SELECT
     df.ID_Srce,
     df.ID_Dest,
     df.ID_Proc,
     dfx.doesClear
   FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID
   ORDER BY dfx.doesClear;</mysql>

@@ Line 7: / Line 7: @@
 * '''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 [[/discarded|get rid of]], once we're sure nothing uses them
@@ Line 23: / Line 24: @@
 * [[/qryCbx_Titles]]
 * [[/qryCat_Items_Stock]]: cat_items with stock info
+====Catalog Items====
-===Catalog Items===
 * [[/qryCat_Items]]
 * [[/qryCbx_Items_data]]
@@ Line 32: / Line 32: @@
 ** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
 * [[/qryItems_prices]]: what uses this?
+====Catalog Sources====
-===Catalog Sources===
 * [[/qryCtg_Sources_active]]
 * [[/qryCtg_Items_updates]]
@@ Line 48: / Line 47: @@
 ** [[/qryCtg_src_dups]]
 ** [[/qryCtgCk_dup_keys]]
+===Ordering===
-===Orders===
+====Customers====
+* [[/qryCustAddrs]]
+* [[/qryCbx_CustNames]]
+====Orders====
 * [[/qryCbx_Orders]]
 * [[/qryOrderLines_notPkgd]]
@@ Line 62: / Line 64: @@
 * [[/qryItms_to_restock]]
 * [[/qryItms_to_restock_w_info]]
-===Shipping===
+====Packages====
-* [[/qryPkgs_status]]
-===Customers===
-* [[/qryCustAddrs]]
-* [[/qryCbx_CustNames]]
-===Packages===
 * [[/qryPkgLines_byOrdLine_andItem]]
 * [[/qryOrdLines_PkgdQtys]]
@@ Line 75: / Line 70: @@
 * [[/qryOrders_Pulled]]
 * [[/qryPkgs_Pull_status]]
+====Restocks====
-===Restocks===
 * '''all restock requests''':
 ** [[/qryRstks_info]]
@@ Line 85: / Line 79: @@
 *** [[/qryRstkReq_by_status]]
 *** [[/qryRstkReq_by_PurchOrd]]
-**
 * '''active restocks''':
 ** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed)
@@ Line 93: / Line 86: @@
 ** [[/qryRstkItms_unsent_for_order]]
 ** [[/qryRstkItms_expected]]
-====terminology====
+'''terminology'''
 * '''Active''' = not "terminated", i.e. not "closed", "killed", or "orphaned" (may or may not be "expected" yet)
 * '''Closed''' = received from supplier, nothing remaining on backorder
@@ Line 101: / Line 95: @@
 * '''Terminated''' = closed, orphaned, or killed
 * '''Unsent''' = created but not yet placed with supplier, i.e. not "expected"
-Phases:
+'''phases''':
 {| border=1
 |-
@@ Line 110: / Line 104: @@
 | colspan=4 align=center | --(Active)-- || align=center | --(Terminated)--
 |}
+====Shipping====
+* [[/qryPkgs_status]]
 ===Stock===