From 2d338c37ba685cde824f79432978f3a7524378be Mon Sep 17 00:00:00 2001
From: Galen Charlton <gmc@equinoxinitiative.org>
Date: Sun, 2 Apr 2017 15:15:56 -0400
Subject: [PATCH] LP#1678638: add text versions the qstore and query_schema
 docs

qstore.txt includes some AsciiDoc markup, while query_schema.txt
is just the results of passing query_schema.odt through unoconv
and pandoc.

Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
 docs/TechRef/qstore/qstore.txt       | 390 ++++++++++++
 docs/TechRef/qstore/query_schema.txt | 856 +++++++++++++++++++++++++++
 2 files changed, 1246 insertions(+)
 create mode 100644 docs/TechRef/qstore/qstore.txt
 create mode 100644 docs/TechRef/qstore/query_schema.txt

diff --git a/docs/TechRef/qstore/qstore.txt b/docs/TechRef/qstore/qstore.txt
new file mode 100644
index 0000000000..db5c713e82
--- /dev/null
+++ b/docs/TechRef/qstore/qstore.txt
@@ -0,0 +1,390 @@
+Qstore
+======
+
+The qstore server (open-ils.qstore) executes database queries that have
+been previously defined, in an abstract form, within the database
+itself.  Such queries may be used for periodic reporting, ad hoc
+investigations, or automated updates.
+
+In some cases a query may be qualified by values to be supplied at
+execution time, called bind variables.  For example, a query may be
+limited to a particular org unit, or a short list of user ids.
+
+Before executing a query, the qstore client must open an application
+session with the qstore server by sending it a CONNECT message.  Then it
+must perform a series of steps to set up the query, execute it, and get
+the results.  Finally, the client may close the query explicitly with an
+additional method call.  Alternatively it may simply close the
+application session by sending a DISCONNECT message to the server.
+
+Here is a brief summary of the available methods.  Each will be
+discussed in greater detail in a later section:
+
+.  open-ils.qstore.prepare – load the query
+.  open-ils.qstore.sql – return the query as SQL
+.  open-ils.qstore.param_list – return a list of bind variables
+.  open-ils.qstore.bind_param – assign values to one or more bind
+variables
+.  open-ils.qstore.execute – execute the query and return the results
+.  open-ils.qstore.execute.atomic – an atomic version of the execute
+method
+.  open-ils.qstore.columns – return a list of column names for the
+results
+.  open-ils.qstore.finish – close a query, freeing any associated
+resources
+.  open-ils.qstore.messages – return any error message for a specified
+query
+
+The prepare method
+------------------
+Summary: Load a specified query.   Return a list of bind variables, and
+a token by which the query may be referenced in future method calls.
+
+Parameter: The id of a row in the query.stored_query table, identifying
+the stored query..
+
+Returns: A JSON object with two members:
+
+.  “token”: a text string to be used as a token for identifying the
+query in future method calls.  This string is designed to be unique, but
+otherwise has no very useful meaning.
+.  “bind_variables”: a (possibly empty) JSON object defining any bind
+variables required by the query.  See the discussion of the bind_param
+method.
+
+A client may juggle multiple queries in the same application session,
+using the corresponding tokens to identify the query to which each
+method call applies.
+
+The sql method
+--------------
+Summary: Return the query as an SQL string.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: A string containing the generated SQL corresponding to the
+stored query.  It will incorporate the specified values of any bind
+variables.  If no value has been assigned to a given bind variable, and
+there is no default value for it, then the generated SQL will include
+the name of the bind variable wherever it appears, preceded by a colon
+to distinguish it from a column name.  The user may review such a query
+but cannot execute it.
+
+The param_list method
+---------------------
+Summary: Returns information about the bind variables required by the
+query, if any.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: A JSON object keyed on the bind variable name.  The data
+associated with each name is another JSON object, with the following
+entries:
+
+.  “label”: the contents of query.bind_variable.label for this
+variable.  This is the identifier usually shown to the user for this
+variable.
+.  “type”: the contents of query.bind_variable.type for this variable.
+It is one of “string”, “number”, “string_list”, or “number_list”.
+.  “description”: the contents of query.bind_variable.description for
+this variable.
+.  “default_value”: the value that will be assigned to the variable if
+the user doesn't override it.
+.  “actual_value”: the value assigned by the user, overriding any
+default.
+
+Depending on the type, the default or actual value of a bind variable
+may be a string, a number, a JSON array of strings, or a JSON array of
+numbers.
+
+If a given variable has no default value, then there will be no entry
+for “default_value”.  On the other hand if the default value is a null,
+then there will be an entry for “default_value” whose associated data is
+a JSON null.  Likewise for “actual_value”.
+
+The bind_param method
+---------------------
+Summary: Assign a value to one or more bind variables.
+
+Parameter: This is the only qstore method that requires two parameters:
+
+.  The token returned previously by the prepare method for the same
+query.
+.  A (possibly empty) JSON object keyed on bind variable name.  The
+value associated with each bind variable name is the value to be
+assigned to the corresponding bind variable, overriding any default, and
+replacing any value previously assigned.
+
+The execute method
+------------------
+Summary: Execute the specified query, and return the results.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: Zero or more responses, each containing one row returned by the
+query.  Each row is represented by a JSON array of values.
+
+The execute.atomic method
+-------------------------
+Summary: Execute the specified query, and return the results.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: A JSON array containing zero or more entries.  Each entry
+represents a row as a JSON array of values.
+
+The columns method
+------------------
+Summary: Return the column names assigned by PostgreSQL to the result
+set of the query.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: An array of strings, each string being a column name.
+
+The finish method
+-----------------
+Summary: Close a query, freeing any resources associated with it, and
+rendering the token invalid for future method calls.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Closing the application session will finish any unfinished queries for
+that session.
+
+The messages method
+-------------------
+Summary: Return any error messages associated with a query.
+
+Parameter: The token returned previously by the prepare method for the
+same query.
+
+Returns: A JSON array of strings, each string being an error message
+issued in connection with the specified query.  The messages appear in
+the order in which they were issued.  Typically the first message
+describes the error as it was first encountered, and subsequent messages
+describe the context in which the error occurred.
+
+The messages returned include all those issued for the specified query,
+including any issued for previous method calls.  Since currently there
+is no method for purging error messages, they just accumulate.
+
+In many cases (but not all), qstore writes similar messages to its log.
+ The messages method is based on the notion that most users shouldn't
+have to examine log files.  They may however need help in interpreting
+the error messages.
+
+Example
+
+The following srfsh session illustrates these methods.  After opening an
+application session, we prepare query 12:
+
+----
+srfsh# open open-ils.qstore
+
+Service open-ils.qstore opened
+
+srfsh# request open-ils.qstore open-ils.qstore.prepare 12
+
+Received Data: {
+  "token":"1_1279135310_6487",
+  "bind_variables":{
+    "ou":{
+      "label":"lib",
+      "type":"number",
+      "description":"org unit"
+    }
+  }
+}
+----
+
+The server concocts a unique token, "1_1279135310_6487”, that we will
+use henceforth to identify this query.  We could also prepare an
+unrelated query, or even the same query more than once, and qstore would
+assign each of them a different token and keep them all straight.  For
+this example, though, we'll stick to the one query.
+
+This query has one bind variable named “ou”, whose value needs to be
+numeric.  The label, “lib”, is a short name for handy reference
+(although in this case it's actually longer than the name used
+internally).  The description tells us that it identifies an org unit.
+ In a real example the description probably should be more verbose, so
+that user could see it in a tool tip (for example) and figure out what
+to do.
+
+In this case there is no default value, i.e. there is no entry for
+“default_value”.  That means we can't run the query yet – but we can
+still look at the SQL for it:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.sql "1_1279135310_6487"
+
+Received Data: "SELECT\n   \"aou\".id,\n   \"aou\".name,\n  
+\"aou\".shortname,\n  
+
+\"aou\".opac_visible,\n   \"aou\".parent_ou \nFROM\n   actor.org_unit AS
+\"aou\" \nWHERE\n   \"aou\".id = :ou;\n"
+----
+
+When we call the sql method, we pass it the token that the prepare
+method assigned earlier.  The server returns the generated SQL, trying
+to make it readable by inserting newlines and indentation.  In srfsh,
+though, the output is pretty ugly because all the newlines and quotation
+marks are escaped within a JSON string.  In this document, at least,
+it's line-wrapped so that it fits on the page.  In a proper GUI it
+should look much nicer.
+
+At the end of the SQL, the query refers to the bind variablee as “:ou”,
+the variable preceded by a colon.  The colon indicates that “ou” is a
+variable name rather than a column name.  It also makes the SQL invalid
+until we replace the variable with a real value.  Let's do that now, and
+then look at the SQL again:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.bind_param \
+"1_1279135310_6487" {"ou":3}
+
+srfsh# request open-ils.qstore open-ils.qstore.sql "1_1279135310_6487"
+
+Received Data: "SELECT\n   \"aou\".id,\n   \"aou\".name,\n  
+\"aou\".shortname,\n  
+\"aou\".opac_visible,\n   \"aou\".parent_ou \nFROM\n   actor.org_unit
+AS \"aou\" \nWHERE\n   \"aou\".id = 3;\n"
+----
+
+When we call the bind_param method we pass not only the token but also a
+JSON object assigning a value to one or more bind variables – just one
+in this case.  Now the generated SQL looks normal.
+
+We can also verify the substitution by calling the param_list method:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.param_list \
+"1_1279135310_6487"
+
+Received Data: {
+  "ou":{
+    "label":"lib",
+    "type":"number",
+    "description":"org unit",
+    "actual_value":3
+  }
+}
+----
+
+Now we see an “actual_value” of 3.  The SQL is ready to go.
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.execute
+"1_1279135310_6487"
+
+Received Data: [
+  3,
+  "Example System 2",
+  "SYS2",
+  "t",
+  1
+]
+----
+
+This query returns only one row, so we get only one response.  If there
+were multiple rows, srfsh would display multiple lines of “Received
+Data.”  The one response is a JSON array of column values.
+
+Here's the atomic version of the same method call:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.execute.atomic \
+"1_1279135310_6487"
+
+Received Data: [
+  [
+    3,
+    "Example System 2",
+    "SYS2",
+    "t",
+    1
+  ]
+]
+----
+
+The difference isn't obvious because there's only one row, but notice
+the an extra layer of square brackets.  This result is an array of
+arrays of column values.  If there were multiple rows, they'd all be in
+the same array.
+
+This response does not identify the columns.  For that we must make
+another call:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.columns
+"1_1279135310_6487"
+
+Received Data: [
+  "id",
+  "name",
+  "shortname",
+  "opac_visible",
+  "parent_ou"
+]
+----
+
+The result is a JSON array of column names.  These are the same names
+that you would get if you ran the query in psql.  They may not be unique
+or even helpful.  Ideally the query should assign good column aliases,
+but if it doesn't, you have to take what you can get.
+
+Now let's make a mistake, just so that we can see an error message.
+ We're going to assign a value to a bind variable that doesn't exist,
+and then ask for any error messages:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.bind_param
+"1_1279135310_6487" {"goober":3}
+
+Received Exception:
+Name: osrfMethodException
+Status: Unable to apply values to bind variables
+Status: 400
+Received Exception:
+Name: osrfMethodException
+Status: An unknown server error occurred
+Status: 404
+
+srfsh# request open-ils.qstore open-ils.qstore.messages
+"1_1279135310_6487"
+
+Received Data: [
+  "Can't assign value to bind variable \"goober\": no such variable"
+]
+----
+
+The result is a JSON array of error messages as strings.  In this case
+there's only one message.  In other cases there may be a series of
+messages, the first one describing the error at the lowest level, and
+the rest providing additional context.
+
+Now that we're done with this query, we can shut it down:
+
+----
+srfsh# request open-ils.qstore open-ils.qstore.finish
+"1_1279135310_6487"
+
+Received no data from server
+
+srfsh# close open-ils.qstore
+
+Service "open-ils.qstore" closed
+
+srfsh# exit
+----
+
+The finish method closes the query and frees associated memory.  In this
+case we could have skipped it, because we immediately close the session
+anyway, thereby closing any outstanding queries.
diff --git a/docs/TechRef/qstore/query_schema.txt b/docs/TechRef/qstore/query_schema.txt
new file mode 100644
index 0000000000..91d24b468b
--- /dev/null
+++ b/docs/TechRef/qstore/query_schema.txt
@@ -0,0 +1,856 @@
+The Query Schema
+
+ 
+
+Introduction
+
+The query schema stores user-defined queries in an abstract form.  The
+qstore server reads the query tables, constructs the corresponding SQL,
+executes the query, and returns the result set.  This machinery supports
+three main kinds of uses:
+
+1.  Ad hoc queries
+2.  Repeated queries for reports or other kinds of extracts
+3.  Identifying rows that may be subject to automated updates
+
+Queries may be customized at run time through the use of bind variables.
+ For example, a query might  extract circulation statistics for a given
+branch.  It could include a bind variable as a placeholder for the org
+unit id, which the user would supply at run time.  A bind variable may
+represent a single value or a variable-length list of values.
+
+Although there are some limitations, the query tables can represent most
+of the queries that anyone is likely to want.  In particular they
+support many SQL constructs that json_query does not support.
+
+Warning: the machinery comprising qstore and the query tables is a text
+generator with little understanding of how databases work.  Depending on
+the contents of the query tables, it may generate invalid SQL.
+ PostgreSQL is the final arbiter.
+
+Summary of Tables
+
+The query schema includes the following tables, each of which is
+described in a later section:
+
+1.  stored_query – stores the topmost level of a query or subquery:
+SELECT, UNION, INTERSECT, or EXCEPT.  Other tables link to
+query.stored_query, directly or indirectly.
+2.  query_sequence – specifies the sequence of subordinate queries
+within a UNION, INTERSECT, or EXCEPT.
+3.  expression – each row represents an expression, often a
+subexpression of some larger expression.
+4.  from_relation – each row represents a FROM clause, or part of a FROM
+clause, identifying a table, view, subquery, or function from which the
+data are to be drawn.
+5.  select_item – each row specifies the location and content of an
+entry in a SELECT list.
+6.  order_by_item – each row specifies the location and content of an
+entry in an ORDER BY list.
+7.  function_sig – represents the names and return types of functions.
+8.  case_branch – represents branches in CASE expressions.
+9.  datatype – defines datatypes that may be used in CAST expressions.
+10. bind_variable – represents bind variables whose values may be
+supplied at execution time.
+
+Three other tables are currently unused, and will not be discussed in
+any detail here:
+
+1.  record_column – defines column sets for functions in a FROM clause.
+2.  function_param_def – defines the parameters of functions.
+3.  subfield – defines the components of composite types.
+
+The latter two may or may not turn out to be useful for the user
+interface code.
+
+Query.stored_query
+
+The stored_query table is the entry point into the query schema.  When
+you want qstore to construct a query, you give it the id of a row in
+query.stored_query.  Then qstore reads that row and all the rows
+connected to it, directly or indirectly, that collectively define the
+entire query.
+
+The columns are as follows:
+
+        id                        integer                primary key
+
+        type                        text                not null
+
+        use_all                        boolean        not null default
+false
+
+        use_distinct                boolean        not null default
+false
+
+        from_clause                integer                points to
+query.from_relation
+
+        where_clause                integer                points to
+query.expression
+
+        having_clause                integer                points to
+query.expression
+
+        limit_count                integer                points to
+query_expression
+
+        offset_count                integer                points to
+query_expression
+
+The id is normally assigned by a database sequence.
+
+The type column must be one of SELECT, UNION, INTERSECT, or UNION.  Most
+queries, of course, are SELECT statements.  Neither the query schema nor
+qstore supports queries in the form of VALUES lists.
+
+The use_all column indicates whether there will be an ALL clause on a
+UNION, INTERSECT, or UNION.  It is not meaningful for a SELECT.
+
+The use_distinct column indicates whether there will be a DISTINCT
+clause.  It is meaningful only for a SELECT.
+
+The from_clause column is meaningful only for a SELECT.  It points to
+the query.from_relation table to define the top-level or core relation
+in a FROM clause.
+
+.
+
+The where_clause and having_clause columns point to the query.expression
+table to define a WHERE and HAVING clause, respectively.  The
+expressions must evaluate to a boolean result, or else PostgreSQL will
+reject the query.  These columns are meaningful only for a SELECT.
+
+The limit_count and offset_count columns point to the query.expression
+table to define values for a LIMIT and OFFSET clause, respectively.  The
+expressions must evaluate to a numeric result, or else PostgreSQL will
+reject the query.  These columns are meaningful only for a SELECT.
+
+For GROUP BY clauses, see the section on the query.select_item table.
+
+Query.query_sequence
+
+The query.query_sequence table defines the sequence of subordinate
+queries within a UNION, INTERSECT, or EXCEPT query.  It provides a layer
+of indirection so that the same query can appear in multiple contexts.
+
+Its columns are as follows:
+
+        id                        integer                primary key
+
+        parent_query                integer                not null;
+points to query.stored_query
+
+        seq_no                        integer                not null
+
+        child_query                integer                not null
+
+The id is normally assigned by a database sequence.
+
+The parent_query column points to the UNION, INTERSECT, or EXCEPT query
+to which the subordinate query is subordinate.
+
+The seq_no column defines the placement of a given subordinate query
+within the parent.  No two subordinates of the same parent may have the
+same value for seq_no.
+
+The child_query column points to the subordinate query.  Typically it
+points to a SELECT, but it may point to a nested UNION, INTERSECT, or
+EXCEPT.
+
+Query.expression
+
+The query.expression table is easily the most complicated of the tables
+in the query schema.  There are many types of expressions, and they may
+be combined into structures of arbitrary complexity.  Expressions may
+appear in several different places within a query: in a SELECT list, in
+a WHERE, ORDER BY, or ON clause, or as subexpressions within larger
+expressions.
+
+Different kinds of expressions call for different combinations of
+ columns to be populated, as described in the Appendix.  However the
+following columns are relevant to all kinds of expressions:
+
+        id                        integer                primary key
+
+        type                        text                not null
+
+        parenthesize                boolean        not null; default
+false
+
+        parent_expr                integer                points to
+query.expression
+
+        seq_no                        integer                not null;
+default 1
+
+        negate                        boolean        not null; default
+false
+
+The id is normally assigned by a database sequence.
+
+The type column currently has sixteen possible values, which we will
+examine briefly below after introducing the other columns.
+
+If set to true, the parenthesize column tells qstore to enclose the
+entire expression in parentheses.  Usually qstore can figure out for
+itself when it needs to insert parentheses, but this column is available
+when you need it.
+
+The parent_expr column identifies the larger expression to which a
+subexpression belongs.  It isn't needed for every subexpression; only
+for those that may form series of two or more subexpressions, such as
+the parameters of a function call or the branches of a CASE expression.
+
+The seq_no column defines the sequence of subexpressions within the same
+larger expression.  No two expressions with the same parent expression
+may have the same sequence number.
+
+If true, the negate column tells qstore to negate the entire expression
+by inserting a NOT somewhere.  It is sensible to use it only when the
+expression evaluates to a boolean result.
+
+The usage of the remaining columns depends on the value of the type
+column, as detailed in the Appendix.  Here's a summary:
+
+The literal column contains a number (as text) or a string literal.  It
+may also contain “true” or “false” as a boolean literal.
+
+The column_name column contains the name of a column.  It may optionally
+be qualified by the table_alias column.
+
+The left_operand and right_operand columns point to subexpressions to
+appear with a designated operator.  The left_operand operator is also
+used to point to subexpressions in several other kinds of expressions,
+such as IN expressions and casts.
+
+The function_id column, pointing to a row in query.function_sig, is used
+to express a function call.
+
+The subquery column, pointing to a row in query.stored_query, refers to
+a subquery.
+
+The cast_type column, pointing to a row in query.datatype, is used to
+express a CAST expression.
+
+The bind_variable column, pointing to a row in query.bind_variable,
+identifies a placeholder whose value will be supplied by the user when
+he or she executes the query.
+
+Currently there are sixteen allowed values for the type column,
+signifying sixteen kinds of expressions:
+
+1.  xbet                BETWEEN expression
+2.  xbind                bind variable
+3.  xbool                boolean literal
+4.  xcase                CASE expression
+5.  xcast                CAST expression
+6.  xcol                column reference
+7.  xex                EXISTS expression
+8.  xfunc                function call
+9.  xin                IN expression
+10. xisnull                IS NULL expression
+11. xnull                null
+12. xnum                numeric literal
+13. xop                operator with one or two operands
+14. xser                series of subexpressions separated by operators
+or commas
+15. xstr                string literal
+16. xsubq                subquery
+
+For each expression type there is an updatable view containing only the
+columns that are relevant to that type.  The name of the view is the
+type prefaced by “expr_”; e.g.. query.exp_xbet.
+
+Neither the query schema nor qstore tries to determine the datatype of
+an expression.  For example, you can encode a nonsensical expression
+like 'W' + 3, or NOT CURRENT_DATE.  Though qstore will blithely generate
+the corresponding SQL, PostgreSQL will reject it.
+
+Query.from_relation
+
+A row in query.from_relation defines a table, view, function or subquery
+in the FROM clause, from which the SELECT will draw its data.
+
+Query.from_relation includes the following columns:
+
+        id                        integer                primary key
+
+        type                        text                not null
+
+        table_name                text
+
+        class_name                text
+
+        subquery                integer                points to
+query.stored_relation
+
+        function_call                integer                points to
+query.expression
+
+        table_alias                text
+
+        parent_relation        integer                points to
+query.from_relation
+
+        seq_no                        integer                not null;
+default 1
+
+        join_type                text
+
+        on_clause                integer                points to
+query.expression
+
+The id is normally assigned by a database sequence.
+
+The type must be one of RELATION (meaning table or view), SUBQUERY, or
+FUNCTION.  Depending on the type, different combinations of the other
+columns may be populated or not populated.
+
+The table_name column may be populated for a RELATION to specify the
+name of a table or view.
+
+The class_name column is another way to specify a table or view for a
+RELATION.  If table_name is null, qstore looks up the class_name in the
+IDL in order to get the name of the table or view – or in some cases the
+body of a subquery defined in the IDL.
+
+If the type is SUBQUERY, then the subquery column must point to a row in
+query.stored_query.  Otherwise this column has no meaning.
+
+If the type is FUNCTION, then the function_call column must point to a
+row in query.expression, and that row must represent a function call
+expression.  Otherwise this column has no meaning.
+
+The table_alias column defines an alias to be used for the table, view,
+subquery, or function.  If table_alias is null, but class_name is
+populated, then qstore will use the class_name as an alias.
+
+The parent_relation column is used for joins.  If a relation is joined
+to the top-level relation (the one to which the query.stored_query table
+points), then parent_relation points to the top level.  Otherwise it
+points to a relation that points to the top level, directly or
+indirectly.
+
+The seq_no field defines the sequence of relations with the same parent.
+ No two rows with the same value of parent_relation may have the same
+seq_no.
+
+If parent_relation is populated, then the join_type column must be
+populated with one of INNER, LEFT, RIGHT or FULL to indicate the type of
+join.
+
+The on_clause column is meaningful only if parent_relation is populated.
+ It points to a row in query.expression representing the join condition,
+which must evaluate to a boolean result.
+
+Query.select_item
+
+Each row in query.select_item represents an item in a SELECT list.  The
+columns are as follows:
+
+        id                        integer                primary key
+
+        stored_query                integer                not null
+
+        seq_no                        integer                not null
+
+        expression                integer                not null
+
+        column_alias                text
+
+        grouped_by                boolean        not null; default false
+
+The id is normally assigned by a database sequence.
+
+The stored_query column points to the query to whose SELECT list the
+item belongs.  The query must be a SELECT.
+
+The seq_no column defines the sequence of items within the SELECT list.
+ No two items within the same SELECT list may have the same value of
+seq_no.
+
+The expression column points to a row of any type in query.expression.
+
+The column_alias column specifies a column alias to be supplied in an AS
+clause.  The generated SQL will enclose the column alias in double
+quotes.
+
+The grouped_by column stipulates that the SELECT item be referenced in a
+GROUP BY clause.  The generated SQL references the item by its ordinal
+position within the list, which may or may not be the same as the value
+of the seq_no column.  It's up to you to ensure that the resulting GROUP
+BY clause is valid; i.e. if any item is in a GROUP BY clause, then every
+other item that isn't an aggregate function must also be included in the
+GROUP BY clause.
+
+In SQL it is possible, though seldom useful, to include something in the
+GROUP BY clause that is not included in the SELECT list.  However the
+query schema provides no way to encode such a query directly.  The
+workaround, should you ever need it, is to do the GROUP BY in a subquery
+that includes everything it needs in the SELECT list, while the outer
+query picks out only the items you want to keep.
+
+Query.order_by_item
+
+Each row in query.order_by_item specifies an expression in an ORDER BY
+list.  Its columns are as follows:
+
+        id                        integer                primary key
+
+        stored_query                integer                not null;
+points to query.stored_query
+
+        seq_no                        integer                not null
+
+        expression                integer                not null;
+points to query.expression
+
+The id is normally assigned by a database sequence.
+
+The stored_query column identifies the query to which the ORDER BY
+clause applies.  This query must be a SELECT.
+
+The seq_no column defines the sequence of items in the ORDER BY clause.
+ No two ORDER BY items for the same query may have the same value in
+seq_no.
+
+The expression column, pointing to a row in query.expression, identifies
+an expression by which the query results will be sorted.
+
+The generated ORDER BY clause includes the specified expressions bodily,
+rather than by referring to items by their ordinal position in the
+SELECT clause.  As a result, you can include expressions that aren't in
+the SELECT clause at all.
+
+As a further result, the ORDER by clause becomes ugly and bulky if the
+expressions are large and complicated.  If you really want to reference
+expressions in the SELECT list by number, use the corresponding numeric
+constants as your ORDER BY expressions.
+
+It may seem confusing that ORDER BY doesn't work the same way as GROUP
+BY (see the discussion of the latter in the section on the
+query.select_item table).  In SQL, either clause can reference an
+expression outside of the SELECT clause, but the query schema allows
+such a reference only for ORDER BY.  For GROUP BY you can get the same
+effect only through an awkward workaround.
+
+These design choices reflect a sense that having to use a workaround, in
+order to list an expression not in the SELECT list, is more likely to be
+a problem for ORDER BY than for GROUP BY.
+
+Query.function_sig.
+
+The query.function_sig table stores information about function
+signatures:
+
+        id                        integer                primary key
+
+        function_name        text                not null
+
+        return_type                integer                points to
+query.datatype
+
+        is_aggregate                boolean        not null; default
+false
+
+The id is normally assigned by a database sequence.
+
+The function_name column stores the name of the function.
+
+The return_type column, pointing to a row in query.datatype, indicates
+the return type of the function.
+
+The is_aggregate column, if true, indicates that the function is an
+aggregate function such as max() or sum().  Aggregate functions
+typically don't have specific return types, because the effective return
+type depends on the type of the argument.
+
+Qstore pays attention only to the id and function_name columns; the
+other two columns may be useful to the user interface.  Likewise qstore
+pays no attention to the query.function_param_def table, which defines
+the datatypes of the function parameters.
+
+Query.case_branch
+
+The query schema represents a CASE expression as a row in
+query.expression, with the type column set to “xcase”.  For each branch
+of the CASE expression there is a row in query.case_branch.  Its columns
+are as follows:
+
+        id                        integer                primary key
+
+        parent_expr                integer                not null;
+points to query.expression
+
+        seq_no                        integer                not null
+
+        condition                integer                points to
+query.expression
+
+        result                        integer                not null;
+points to query.expression
+
+The id is normally assigned by a database sequence.
+
+The parent_expr column points to a row in query.expression representing
+the entire CASE expression to which the branch belongs.
+
+The seq_no column defines the sequence of branches within the CASE
+expression.  No two branches within the same CASE expression may have
+the same value of seq_no.
+
+The condition column, pointing to a row in query.expression, represents
+a possible value of the expression being tested.  In the generated SQL,
+the corresponding expression will follow the WHEN keyword.
+
+The result column, pointing to a row in query.expression, represents the
+value to which the CASE expression evaluates if the branch is followed.
+ In the generated SQL, the corresponding expression will follow the THEN
+or ELSE keyword.
+
+If the condition column is null, then the branch is the ELSE branch.
+ There may be no more than one such branch in a given CASE statement,
+and it must be the last branch.
+
+Query.datatype
+
+The query schema represents a CAST expression with a row in
+query.expression, where the type column is set to “xcast”.  To identify
+the datatype to which the operand is being cast, the query.row.datatype
+column points to a row in query.datatype, which has the following
+columns:
+
+        id                        integer                primary key
+
+        datatype_name        text                not null
+
+        is_numeric                boolean        not null; default false
+
+        is_composite                boolean        not null; default
+false
+
+The id is normally assigned by a database sequence.
+
+The datatype_name column, of course, the name of the datatype.
+
+The is_numeric column, if true, indicates that the the type is numeric.
+
+The is_composite column, if true, indicates that the datatype is
+composed of two or more subfields, which may themselves be defined in
+the query.subfield table.
+
+Qstore pays attention only to the datatype_name and id columns.  The
+other two columns, and the query.subfield table, may be useful for the
+user interface.
+
+Query.bind_variable
+
+The query.bind_variable table defines variables that may appear within
+the query.  Before executing the query, the user must supply a value for
+each such variable, or accept the default value if one is defined.  The
+columns are as follows:
+
+        name                        text                primary key
+
+        type                        text                not null
+
+        description                text                not null
+
+        default_value                text
+
+        laqbel                        text                not null
+
+The name column is the primary key, and contains the name of the
+variable
+
+Depending on what kind of value the variable may hold, the type column
+contains one of “string”, “number”, “string_list”, or “number_list”..
+ The first two denote individual scalar values, and the latter two
+denote comma-separated lists of scalars.  A null value may be encoded by
+the JSON keyword “null”.
+
+The description column describes the variable so that the user can know
+what it's for.
+
+The default_value column, if populated, contains the value that will be
+used if the user does not specify some other value.  This value must be
+encoded as JSON; a list type must be encoded as a JSON array.
+
+The label column is the identifier that will normally be shown to the
+user.  It should be reasonably short and descriptive, but it need not be
+unique.  The name provides uniqueness, and since it will mainly be used
+internally, need not be as human-friendly as the label.
+
+If qstore is asked to generate SQL for query with a bind variable that
+has not been assigned a value, it will include the bind variable name in
+the output SQL, preceded by a colon to mark it as a bind variable.  Such
+a query cannot be executed, but it can be displayed to the user for
+review.
+
+Appendix: Expressions
+
+A row in the query.expression table may represent any of several kinds
+of expressions, as denoted by the contents of the type column.  As noted
+earlier, some of the columns in query.expression apply to all kinds of
+expressions.  The rest apply only to some kinds of expressions and not
+to others, in various combinations.
+
+This appendix discusses each expression type in turn, and how to
+represent it.
+
+xbet: BETWEEN
+
+An “xbet” expression involves three subexpressions:
+
+        A BETWEEN B AND C
+
+The left_operand column points to subexpression A.  There must be
+exactly two other rows representing subexpressions B and C, whose
+parent_expr columns point to the “xbet” row.
+
+The values of their seq_no columns determine which one comes first.
+
+If the negate column is set to true, then the result is a NOT BETWEEN
+expression.
+
+xbind: Bind Variable
+
+An “xbind” expression refers to a bind variable, i.e. a value or series
+of values that the user must supply before executing the query.  In
+query.expression, the bind_variable column points to a row in the
+ query.bind_variable table, which defines a name and a label for the
+bind variable, and possibly a default value.
+
+xbool: BOOLEAN
+
+An “xbool” expression is a boolean literal.  The literal column contains
+“true” or “false” in any combination of upper, lower, or mixed case.
+
+xcase: CASE
+
+An “xcase” expression represents a CASE structure, as in the following
+example:
+
+        CASE A
+
+                WHEN B THEN C
+
+                WHEN D THEN E
+
+                ELSE F
+
+        END
+
+The left_operand column contains A, the value being tested.  Each branch
+of the CASE is represented by a row in query.case_branch, where the
+condition column points to subexpressions B and D, and the result column
+points to subexpressions C, E, and F.  For the ELSE branch, the
+condition column is null.
+
+In the query.case_branch table, the seq_no column defines the order in
+which the branches appear.  If there is an ELSE branch, it must come
+last.
+
+xcast: CAST
+
+An “xcast” expression casts a subexpression to a datatype:
+
+        CAST (A AS B)
+
+The left_operand column points to A, the expression being cast.  The
+cast_type column points to a row in query.datatype that defines the
+datatype B.
+
+xcol: Column Reference
+
+An “xcol” expression refers to the contents of a column, optionally
+qualified by an alias for a table, view, or other relation:
+
+        “A”.B
+
+The column_name column contains the name of the column B.  The
+table_alias column, if not null, contains the alias A.  Since qstore
+always encloses the alias in quotation marks, there is no way to qualify
+a column name by a raw table name.
+
+xex: EXISTS
+
+An “xex” expression is an EXISTS clause with a subquery.  The subquery
+column points to a row in query.stored_query.
+
+If the negate column is set to true, the result is a NOT EXISTS
+expression.
+
+xfunc: Function Call
+
+An “xfunc” expression is a function call:
+
+        A( B, C, D ... )
+
+The function_id column points to a row in query.function_sig that
+defines the function name A and other aspects of the function
+signature..  Each parameter B, C, etc. is represented by a row in
+query.expression, where parent_expr points to the “xfunc” row.  The
+seq_no columns for the various parameters define their positions within
+the parameter list.
+
+If a function returns a composite type, it is possible to specify a
+subfield of the return value:
+
+        (A( B, C, D ... )).”E”
+
+In such a case, the column_name column contains the subfield name E.
+
+Some built-in SQL functions don't follow the usual syntax of
+parameter-passing.  For example, the following function not only don't
+accept any parameters, they don't even accept empty parentheses:
+
+        current_date
+
+        current_time
+
+        current_timestamp
+
+        localtime
+
+        localtimestamp
+
+Qstore treats these functions as special exceptions in order to avoid
+adding empty parentheses.
+
+The extract function requires an extra keyword within the parameter
+list:
+
+        extract( A FROM B )
+
+...where A is one of a short list of unquoted strings.  Qstore treats
+calls to extract() as a special exception: pass A as if it were a string
+literal, and qstore will build the call with a FROM and an unquoted A.
+
+Qstore does not currently support other irregular functions.
+
+xin: IN
+
+An “xin” expression may take either of two forms.  One form involves a
+subquery:
+
+        A IN ( subquery )
+
+The left_operand column contains a pointer to another row in
+query.expression, representing the value A to be tested.  The subquery
+column points to a row in query.stored_query, defining the subquery.
+
+The other form involves a list of values:
+
+        A IN (B, C, D ... )
+
+Again, the left_operand indicates the value to be tested.  Each value in
+the list is represented by a row in query.expression whose parent_expr
+column points to the “xin” row.  The seq_no columns of the subexpression
+rows define the order of their appearance.
+
+If the negate column is set to true, then the result is a NOT IN
+expression.
+
+xisnull: IS NULL
+
+An “xisnull” expression tests whether a given value is null:
+
+        A IS NULL
+
+The left_operand column points to row in query.expression representing
+the value to be tested.
+
+If the negate column is set to true, then the result is an IS NOT NULL
+expression.
+
+xnull: NULL
+
+An “xnull” expression represents a null value (and not a test for
+nullity).
+
+xnum: NUMBER
+
+An “xnum” expression represents a numeric literal.  The literal column
+contains the value as a string.  This string may contain leading and/or
+trailing white space, but otherwise must be numeric – possibly including
+a leading minus sign, a decimal point, and/or scientific notation.
+ Currently this validation applies JSON's rules, which may differ in
+some respects from SQL's rules.
+
+xop: Operator
+
+An “xop” expression consists of an operator and one or two operands:
+
+        A operator B
+
+        C operator
+
+        operator D
+
+The operator column contains the operator as a string.  This string may
+contain any of the usual SQL operators.  It may also contain a
+non-standard custom operator, as long as it does not include white space
+or a semicolon.  (This support for custom operators was inherited from
+json_query, where it makes sense.  In qstore this support is unnecessary
+and may be withdrawn in future releases.)
+
+As special exceptions, the phrases "similar to", "is distinct from", and
+"is not distinct from" may be used as binary operators, in any
+combination of upper, lower, and mixed case, provided that they contain
+no additional white space.
+
+For a binary operator, then the left_operand column points to another
+row in query.expression that represents the operand to the left of the
+operator.  Likewise the right_operand column identifies the expression
+on the right.
+
+A few operators take only one operand.  Accordingly only the
+left_operand or right_operand column should be populated, depending on
+whether the operand should appear to the left of the operator (such as
+the factorial operator “!”) or to its right (such as the unary minus
+operator).
+
+xser: Series
+
+An “xser” expression is a series of expressions separated by a specified
+operator, or (if no operator is specifed) by commas:
+
+        A operator B operator C operator ... D
+
+        A, B, C, ... D
+
+ Typically the operator will be AND or OR, combining multiple conditions
+in the WHERE clause.  It is also possible to use, for example, an
+arithmetic operator like “+”, or the concatenation operator “||”.
+
+If the operator column is null, then qstore separates the expressions
+with commas.  By enclosing such a series in parentheses you can
+construct a tuple.
+
+Each subexpression in the series is represented by another row in
+query.expression, whose parent_expr column points to the “xser” row.
+ The seq_no columns of the subexpressions define the order of their
+appearance within the series.
+
+The same operator is used for the entire series.  If you need to combine
+different operators in the same expression, as in A + B – C, then you
+must nest multiple “xser” and “xop” expressions as needed.
+
+Strictly speaking, the “xser” type isn't necessary.  You can create all
+the same expressions by nesting “xop” expressions, although it may be
+rather cumbersome to do so.  The “xser” type is merely a convenience,
+making it easier to express certain common constructs.
+
+xstr: Character String
+
+An “xstr” expression consists of a character string, which must be
+stored in the literal column.  If the string contains any special
+characters such as quotation marks or backslashes, qstore will escape
+them as needed when it constructs the query.
+
+xsubq: Subquery
+
+An “xsubq” expression represents a subquery.  The subquery column points
+to a row in query.stored_query to identify the query.
-- 
2.43.2