Index...
Centrallix Documentation
|
11.1 QueryTree
QueryTree Objects
Centrallix supports a few ways of creating the equivalent of a stored procedure or database view. This one is the QueryTree Object; another is the Query Pivot Object and the third is the 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.
QueryTree Object format
A querytree object takes on the same familiar structure file format as other Centrallix objects like applications, reports, and more. Here is an example:
$Version=2$
files_qytree "system/querytree"
{
Files "system/querytree"
{
text = "Files";
ListOfFiles "system/querytree"
{
sql = " select * from identity /samples";
}
}
}
In the above querytree, a structure is generated with an object called "Files" which in turn contains (as subobjects) the list of files in the /samples directory in Centrallix. It would create a structure as follows (assuming the querytree file name is "files_qytree.qyt"):
files_qytree.qyt
|
+-- Files
|
+-- map_test.app
|
+-- mountain_peak.gif
|
+-- road_highway.gif
|
(and so on...)
Types of Objects
QueryTrees can contain three different types of objects: text objects, SQL-generated lists of objects, and objects from a specific source directory.
Text objects are statically named using a text= attribute. No SQL is run and no source directory lookup is performed.
SQL objects have a sql= attribute that provides a SQL statement to run. The SQL statement is expected to generate a list of objects that will be populated at this level in the querytree. If the SQL statement will only generate a single object with a well known name, such as the results of database update operation, a text= attribute can (and sometimes should, see below) also be specified with that object name.
Source objects have a source= attribute that provides a directory name to use as the object list at this level in the querytree. Source objects can also have a where= attribute that specifies criteria for which objects to display.
Invoking QueryTree Objects
Querytree 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.
Here are a few examples:
select * from /folder/myquerytree.qyt
select * from /folder/myquerytree.qyt/Files
select * from object /folder/myquerytree.qyt/Files/map_test.app
The use_having Flag
On SQL and Source objects, you can set the use_having flag to "yes" to force object name based lookups to use a HAVING clause instead of a WHERE clause. This allows those lookups to leverage computed 'name' attributes.
The force_leaf Flag
Setting force_leaf to "yes" on a SQL or Source object gives an indication that the object(s) returned by the query or source cannot contain further subobjects. This can be useful when presenting a querytree to the user in a Treeview widget, so the user is not tempted to further click for sub-objects when force_leaf is set to "yes".
The "recurse" property
If a particular querytree object represents a whole tree of objects, instead of just a single object or a list, use the "recurse" property, setting it to the querytree object name where recursion should continue. In the simple instance of a self-referencing table, the recurse property can point back to the object itself.
When using "recurse", it is useful to be able to access the parent object's properties. To do so, use the parent object reference operator "::". Here's an example:
$Version=2$
accounts "system/querytree"
{
top_level_accounts "system/querytree"
{
source="/my/db/account/rows";
where=":parent_account is null";
sub_accounts "system/querytree"
{
source="/my/db/account/rows";
where=":parent_account = ::account";
recurse = sub_accounts;
}
}
}
In the above example, for the first level of objects returned by sub_accounts, ::account refers back to the 'account' attribute of the object returned by top_level_accounts. For subsequent levels of objects returned by sub_accounts (after recursion), ::account refers to the previous level of objects returned by sub_accounts.
Thus, a table used with the above querytree and with the following properties:
| account | parent_account |
|---|
| 1000 | NULL |
| 1001 | 1000 |
| 1002 | 1000 |
| 1100 | 1000 |
| 1101 | 1100 |
| 2000 | NULL |
| 2001 | 2000 |
| 2002 | 2000 |
would result in a tree structure like this:
accounts.qyt
|
+-- 1000
| |
| +-- 1001
| |
| +-- 1002
| |
| +-- 1100
| |
| +-- 1101
|
+-- 2000
|
+-- 2001
|
+-- 2002
In the tree structure above, objects '1000' and '2000' would be returned by top_level_accounts, and objects '1001', '1002', '1100', '1101', '2001', and '2002' are returned at various levels by sub_accounts.
The same_children_as property:
Sometimes you may find that two different subtrees in a QueryTree need to have the same basic schema, and it would be cumbersome and unreliable to reproduce nearly identical code in both (or several) subtrees.
This is an application for the same_children_as property - the schema just needs to be defined in one subtree, and the others can reference it using same_children_as. This results in the same subtree structure being used in more than one object.
The subtree may need to determine some context from its parent. To this end, the parent object reference ::attributename (see SQL Operators) can be used to reference attributes in the parent object's source or query. Note that the operational parent is used for this, not the direct parent in the structure of the .qyt file. And, for the purposes of determining what object is used for the parent reference, "text" objects are skipped over to find the next "source" or "sql" object.
Notes
Setting the IDENTITY source - All SQL queries MUST have their main data source labeled as an IDENTITY data source (see above example).
Update Queries - If you want a querytree SQL object to perform database modifications, and if it won't naturally return records, you'll still need to return a record via a multi-statement query, or else set the text= property. If the text property is not set, then you will be limited to just one such SQL object at that querytree level.
Multiple SQL Queries - You can have more than one querytree object at each branch of the tree. However, multiple SQL or Source objects in one branch will be tried in succession until one results in a matching object (or will be run in succession to generate a list of subobjects). Sometimes, especially when a query performs an update or otherwise has side effects, this may not be the desired result. In those cases, either separate the queries into different branches of the tree, or if the queries are just update/insert/delete queries that only return a nominal result set, use the text= attribute (as described above) to specify the name of the object from the query, so that the SQL or source objects are not tried in succession in search of a particular named object.
Comments...
(none yet)
Add a Comment...
|
