Index...
Centrallix Documentation
|
6.3 FROM Clause
FROM Clauses
Centrallix presents all data in a tree-structured ObjectSystem rather than via a standard relational model of databases and tables. As a result, Centrallix has some unique additional features in its SQL language. Some of those features apply to how FROM clauses are specified.
Where FROM clause modifier keywords are included, they must be used in the order shown below:
| Modifier | Description |
|---|
| IDENTITY | Specify the primary or "identity" data source for the query |
| NONEMPTY | Require that sequentially returned results from a data source all contain rows; the query ends when no rows are matched from the source. |
| OBJECT | Query from the specified object itself, rather than from its subobjects. |
| PRUNED | Causes a SUBTREE source to not recurse into a matching object. |
| INCLUSIVE | Causes a SUBTREE source to include the root of the tree. |
| SUBTREE | Recursively search subobjects and subobjects of subobjects. |
| WILDCARD | Perform pathname wildcard expansion on the source, and search all matching sources. |
| PAGED | Iteratively query a paginated source, such as an API. |
| EXPRESSION | Use an expression in place of a constant string to identify the data source. Properties from other sources can be used in building the expression, creating an expression join. |
| COLLECTION | Query a temporary collection rather than an OSML path. |
These modifier keywords are all Centrallix SQL extensions.
Specifying an Identity Data Source (IDENTITY keyword)
When providing an SQL statement in an environment where that SQL statement defines how to obtain, insert, and delete data, rather than just obtaining data, it can be important to define which data source forms the inherent object identity in the query. A common example of this is within a Query Object (.qy file). This is done with the IDENTITY keyword.
For example, say you are including information from a code table in your SQL query. When an object is deleted via the query, you don't want to delete a row from the code table, only from the main table. Thus, you would write your query as follows:
SELECT :i:item_name, :t:item_type_description
FROM IDENTITY /myDatabase/Item/rows i,
/myDatabase/ItemType/rows t
WHERE :i:item_type = :t:item_type
Iterating Through Result Set Pages (PAGED keyword)
When including a web service API endpoint as a FROM source in your query, you may need to iterate over multiple "pages" of API results, perhaps only retrieving 50 or 500 records at a time. Centrallix provides a way to automate this iteration in a SQL query, using the PAGED keyword in conjunction with a FROM clause expression (see From Clause Expressions, below).
To iterate over pages, use the PAGED EXPRESSION keywords and then an expression that utilizes the :cx__page virtual attribute (starting at page 1) on the data source. If your web service API iterates by page, beginning with page #1, your query might look like this:
SELECT
:first_name,
:last_name
FROM
PAGED EXPRESSION ( '/api_objects/personapi.http?page=' + :p:cx__page + '&length=100' ) p
Or, if your web service API iterates by starting record number, beginning with record #0, use this type of query:
SELECT
:first_name,
:last_name
FROM
PAGED EXPRESSION ( '/api_objects/personapi.http?start=' + ((:p:cx__page - 1)*100) + '&length=100' ) p
Note that the object name for the :cx__page property must match the object name associated with the data source ("p" in this case).
Iterating over API pages can also be done with the NONEMPTY keyword, see below, but this requires a second data source, typically with just one attribute, to provide the serially incrementing page number via an expression join.
Requiring Sequential Non-Empty Results (NONEMPTY keyword)
Sometimes you may have a join where the query should finish immediately if a data source returns an empty result set, rather than continuing to iterate through the parent nested data source and examining the source in question for further matching rows. In this case, use the NONEMPTY keyword with that data source.
This is commonly used when iterating through "pages" of records from a web service (API) - where you want to stop examining further pages if a page is empty. Just use the NONEMPTY keyword on the source (the web service in this case) being iterated by page, and the overall iteration will stop when a page is empty.
Subtree Selects (SUBTREE keyword)
You can also select an entire subtree of objects in a single SQL statement. To obtain an entire directory tree of file names including all levels of subdirectories and files, for example, do the following:
SELECT :f:name, :f:size
FROM SUBTREE /samples f
A subtree select will result in several special-purpose attributes being available for your SQL statement, although they still must be explicitly stated in the SELECT clause.
| Attribute | Description |
|---|
| __cx_path | The subtree pathname of the object. |
| __cx_parentpath | The subtree pathname of the parent of the object. |
| __cx_parentname | The name of the parent object. |
| __cx_depth | How many levels deep in the subtree is the current object's location. |
Including the root of the subtree with INCLUSIVE SUBTREE
Normally, a subtree select only includes those objects that are children of the indicated pathname (in the same way that a normal select only selects direct children of a given object). To also include the root of the given pathname in the select, use the keyword 'INCLUSIVE'.
For example, the following SQL would include the 'files' object when summing up the sizes of files:
SELECT sum(:size) from INCLUSIVE SUBTREE /my/files
Querying properties from an object instead of child objects (OBJECT keyword)
At times, you may need to query the properties of one particular known object rather than searching an entire list of objects. In this case, you can use the OBJECT keyword in the FROM clause.
For example, the following two queries return one 'row' each with the size of the 'files' object. One query uses the OBJECT keyword, and the other does not.
SELECT :size
FROM OBJECT /my/files
SELECT :size
FROM /my
WHERE :name = "files"
Using pathname expansion with wildcards (the WILDCARD keyword)
At times, you may want to retrieve data from multiple similar data sources that are in slightly different locations. In this case, the WILDCARD keyword can be used. The WILDCARD keyword causes the given pathname to be expanded using pathname expansion similar to what many command-line shells permit, and the resulting list of matching objects are used as sources for the query in succession.
Since the asterisk (wildcard) is a special character, the pathname must be quoted.
For example, say you have two financial transaction tables, one called trx_current which contains the current year's data and another trx_archive which contains archived data from prior years, and you want to SELECT data from both tables. The following query retrieves matching rows from both tables (assuming no other tables start with "trx_"), and is a much easier way of writing a UNION type query:
SELECT *
FROM WILDCARD "/somedatabase_DB/trx_*/rows"
WHERE :acct_code = '1001'
The following examples illustrate two ways that the sizes of all javascript files in a set of subdirectories can be summed up. The first statement combines the OBJECT and WILDCARD keywords to simplify the syntax of the query.
SELECT sum(:size)
FROM OBJECT WILDCARD "/myfiles/*/*.js"
SELECT sum(:size)
FROM WILDCARD "/myfiles/*"
WHERE right(:name,3) = ".js"
When using wildcards, sometimes it may be useful to know the pathnames, or parts thereof, that were matched. The following special attributes can be SELECTed to retrieve that information.
| Attribute | Description |
|---|
| cx__pathname | The full pathname of the currently selected object from the given FROM source. Note that this is not the pathname of the FROM source itself (unless the OBJECT keyword is used). |
| cx__pathpartN | The name of the individual pathname element (individual file, directory, table name, etc.) that is in position N (an integer) in the path. cx__pathpart1 refers to the first element in the path. |
For example, the following query will tally up the totals of .js file sizes and present the total by directory name:
SELECT dirname = :cx__pathpart2, sum(:size)
FROM OBJECT WILDCARD "/myfiles/*/*.js"
GROUP BY :cx__pathpart2
Using From Clause Expressions
For some data sources, the very pathname to the data source may need to be dynamic. This is especially useful when querying from hierarchical data such as from JSON or XML data sources. To query from a dynamic source, use the EXPRESSION keyword:
SELECT * FROM EXPRESSION ( string-expression )
The string-expression is any expression that generates a string value, and it can contain references to data from other data sources in the query. In that case, an implicit outer join is done. To effect an inner join instead, use a HAVING clause to further restrict the result set.
Using Temporary Collections
Temporary data sources (see DECLARE for more details) can be used by using the COLLECTION keyword:
SELECT * FROM COLLECTION collectionname
Comments...
(none yet)
Add a Comment...
|
