[Evergreen.git] / docs / TechRef / qstore / qstore.adoc

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.