Centrallix - It's Different than what you Think

Search...

Index...

1. Getting Started
2. Applications Overview
3. Application Components
4. Reports Overview
5. Report Components
6. SQL Language

6.1 SELECT Statements
6.2 WHERE Clause
6.3 FROM Clause
6.4 ORDER BY Clause
6.5 GROUP BY Clause
6.6 HAVING Clause
6.7 Data Types
6.8 Functions and Operators
6.9 Subqueries
6.10 INSERT Statements
6.11 UPDATE Statements
6.12 DELETE Statements

7. Process Modeling
8. Application Modeling
9. Role-Based Security
10. Business Logic Modeling
11. Other Objects

6.3 FROM Clause

(0 comments)

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.

The IDENTITY, (INCLUSIVE) SUBTREE, OBJECT, and WILDCARD 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. 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

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

Comments...

(none yet)

Add a Comment...

Project Hosting Provided By: