Index...
Centrallix Documentation
|
11.15 Query Objects
Query Objects
Centrallix supports a few ways of creating the equivalent of a stored procedure or database view. One is the QueryTree Object, another is the Query Pivot Object, and the third is a Query Object.
QueryTrees are used for rearranging flat relational data or data from various sources into a more hierarchical form useful for APIs, for view models, or for presentation to the user in an organized tree-like format.
Query Pivots are used for rearranging (pivoting) EAV data (entity-attribute-value) into a more standard columnar form. EAV data storage is a way to store arbitrary (user-defined) attributes in a relational database without having to declare columns for them in advance, but it is quite difficult to work with when running queries. Query Pivots allow for selects, inserts, updates, and deletes to be done in a more conventional fashion.
Query Objects on the other hand use a single (but multi-statement if needed) SQL query with declared parameters, and are thus most similar to stored procedures and views in other database systems. Generally, query objects can be updateable (insert*, delete, and update are available), with some caveats which are discussed here.
Query Object format
A query object takes on the same familiar structure file format as other Centrallix objects like applications, reports, and more. Here is an example:
$Version=2$
myquery "system/query"
{
id "query/parameter" { type=integer; }
sql = " select
*
from
identity /myDatabase/myTable/rows t
where
(:parameters:id is null or :t:id = :parameters:id)
";
}
Passing Parameters
In the above example, the query has one parameter, id. If unset, the parameter defaults to being null. The parameter can be referenced via a parameters object within the query, as shown above. This method of making parameters available to the query eliminates the need for trouble-prone approaches like placeholders and dynamic SQL.
Parameters can have data types, defaults, and many other settings. See Presentation Hints for a complete list, but here are a couple of common examples:
myinteger "query/parameter" { type=integer; default=0; }
mystring "query/parameter" { type=string; style=strnull; }
notnullstr "query/parameter" { type=string; style=notnull; }
In the first example above, a default is provided for an integer (the same method works for other data types). The second example is a way of indicating that an empty string should be treated as a NULL value. This can be important, because there is not currently a way to distinguish between empty strings and null strings in the way that query objects are invoked (see below). The third example is a way to declare that the parameters must never be NULL. Since there is no default, if that parameter is not supplied, an error condition will result.
Invoking Query Objects
Query objects can be invoked by accessing them directly (by URL, etc.) or by use in another query. In other queries, they can be used as a data source for select, update, insert*, and delete queries. They can also be directly called via an exec statement.
Here are a few examples:
select * from /folder/myquery.qy
select * from /folder/myquery.qy?id=12345
update /folder/myquery.qy?id=12345 set :somevalue = 4
delete /folder/myquery.qy where :somevalue = 4
exec /folder/myquery.qy id=12345
* As of the current time, insert does not work through query objects.
Handling Synthetically-Named Objects
Sometimes the natural name of an object is not useful in your context, and sometimes (esp. when working with a remote API) the natural name of the object may be empty.
In these cases, you may want to explicitly name the objects coming from your .qy file by SELECTing the 'name' attribute explicitly.
If you do this, you also have to let the query object driver know how you're naming things, so it can effectively look them up. This is because the query object driver doesn't have direct access to the parsed SQL. To do this, supply a name_expression attribute in your .qy object. For example:
$Version=2$
myquery "system/query"
{
sql = " SELECT
name = 'object #' + :id,
:first_name,
:last_name
FROM
/my/data/source
";
name_expression = "'object #' + :id";
}
Caveats
It is not unusual for updateable views to have caveats. Here are a few things that you should keep in mind when creating query objects.
Setting the IDENTITY source - When your query has more than one data source, you must declare one as the primary source using the IDENTITY keyword - see FROM Clause for more information. This must be done whether or not you intend to do updates through the query object.
Multi-statement Queries and Individual Records - If you intend to access individual result set records by name (primary key) with a multi-statement query object, you will need to create a parameter called "name" and have each of your statements in the multi-statement query respect that parameter's value where appropriate, restricting their activity to just that particular named record. The importance of this is based on the context, but if you intend to update through the query object, it is required. It can also be required with read-only query objects, depending on how the query object is used.
Inserts - Inserting data through a query object is not currently supported.
External Criteria - if you restrict your query object results using external criteria (e.g. in a select statement that uses the query object as a data source), the external criteria will be applied to the query using HAVING semantics, not WHERE semantics. That means the criteria may be used less efficiently. For more efficiency and control, use parameters instead. If your query object uses a multi-statement query, you will need to use parameters. Parameterized query objects can be used as a data source in a select (or other) statement by using FROM EXPRESSION ( ... ) syntax to build the parameter list dynamicaly (which implicitly can create a join, even potentially an irreducible n-way join, which is supported).
Empty Strings and NULLs - Because parameters are passed to a query object using URL style parameters, there is no way to distinguish between an empty string and a NULL string. If your query needs empty string parameters to be treated as NULLs (which is often the case), then include the "strnull" style in the parameter - see above for details.
Comments...
(none yet)
Add a Comment...
|
