An expression is a combination of database objects qualifiers, literals, htsql operators and functions, variables and aliases that evaluates to a value. An expression generally assumes the datatype of its components.
For example:
x+10*number(y) > 100
This expression consists of two column references (x, y), function (number()), operators and evaluates to boolean value.
Example of expression in scalar context:
/{sin(2.5)}
Example of expression in aspect context:
/organization{name.upper()}
Expressions may be used within selector or filter section of HTSQL query. Refer to HTSQL Query Syntax section for more information.
Example:
/organization{count(project), name.label(), is_active}?is_active !== null()
This query contains 4 expressions, 3 of them in selector part, one in filter part, but all of them are evaluated in organization aspect context.
Every expression is evaluated in some context, which is used to resolve identifiers within expression. Context is a base table reference for HTSQL query. It is specified right after path segment sign (/):
/organization
In this case we talk about aspect context. It states root table, which all other identifiers are specified relative to.
If context table is not specified we talk about scalar context. It is used to return single row containing set of calculated expressions:
/{pi(), sin(2)}
HTSQL uses mechanism of specifiers to resolve identifiers to actual database objects. Specifier is a prefix for identifier, that tells which object this identifier belongs. Usually specifier prefix is delimited from end identifier with dot symbol (.), for example:
/{avg(participation.billing_rate)}
Here participation is specifier for billing_rate. This is trivial case of specifier usage; the compound identifier stands for column within a table. In fact, specifier always stands for a table, but end identifier may have more than one specifier, coming sequentially. This case is usually used within table context and is described a little bit later.
Context is also a specifier, applied to all following identifiers. For example:
/participation{billing_rate}
actually refers to participation.billing_rate, but semantics of the expression is different. It is considered in HTSQL Query Syntax chapter. As you can see notation of context specifier is different from other specifiers.
In the following example:
/project{client.is_active}
refers to project.client.is_active, where identifier is_active has two specifiers: project and client.
In object-oriented perspective this means that we take client property of project object, which is an object itself, and fetch its is_active property.
In SQL perspective this means, that project table has foreign key client, which references another table, and we take is_active column of that table. Referred table name (organization) is not specified in the query, since it is not required - information about foreign key reference is taken from HTSQL catalog. In this manner consequent use of specifiers results in SQL join. More than two specifiers reflect a transitive join, forming a path from one table to another through intermediates. Note, that generated SQL joins are all OUTER, i.e. if project has null client reference, project.client.is_active will return NULL.
Example above refers to parent table by foreign key name omitting the name of referenced table. With the default HTSQL meta-data configurator, the opposite is also possible: when only one foreign key reference exists to a table, the name of the referenced table (organization) can be used as well as the referencing column (client):
/project{organization.is_active}
This is full synonym of previous example.
Nested context specifier may be used instead of dot-notation. This is useful, if several columns of referred table should be selected:
/project{prj_id, client{name, is_active}}
This is equivalent to:
/project{prj_id, client.name, client.is_active}
Nested context notation can be used only within selector part of HTSQL query. Refer to HTSQL Query Syntax chapter for more information.
So far relationship from child table context to the parent was considered, but it is possible to reverse the join direction. It’s obvious, that in this case child table can be referenced only by table name:
/organization?project.status='completed'
In this case, for each row being be returned in the result set, more than one row in the referencing table is matched. This is a plural join specifier. Refer to Plurality and Plural Expressions for more information.
Expressions that contain at least one plural reference and produce plural result are called plural expressions.
This way one term is defined through two others. Let’s consider them separately.
Plural reference is database object reference, that produces multiple values within current expression context. In other words plural reference corresponds to one-to-many relationship between expression context and referenced object. Non-plural references are called singular.
In scalar context each table reference is plural, since it contains set of rows. In aspect context plurality of the reference depends on cardinality of the relationship between context and referenced object:
/organization{worker}
In this example cardinality between organization and person objects is one-to-many, since many persons work in the same organization. This reference is plural. Note, that this example will produce an error, since selector section may contain only singular expressions.
In this example:
/organization{director}
cardinality between organization and director is one-to-one, since organization has only one director.
In SQL perspective each table reference B within context A (A{B}) results in join between A and B. Plural reference means, that B is child of A: join will produce more than one row in B for each row in A. Such a join is also called plural. In opposite case, if A is child of B, no more than one row in B will be produced for each row in A and such a join is singular and reference is also singular. Note, that reference may be indirect, but through other tables, linked with foreign key references, and result in series of joins. If at least one of them is plural, reference will be plural.
Note: Do not mix up plural expressions with expressions of composite type (array or tuple). Composite type values correspond to single row, while plural expression correspond to set of rows.
Expressions with plural references not obviously produce plural result. Aggregate functions is special class of functions to produce singular result from plural expression. This way aggregate function takes plural expression as argument, but is singular expression itself. Refer to Aggregate Functions for details.
Plural expressions like singular expressions have their type. For example:
/{organization.name}
has type plural string, while:
/{organization}
is plural tuple. This way you can think a table reference as a plural tuple expression.
Assignment expressions in htsql are used only in conjunction with data modification commands and set a value to specific table’s column.
assignment:
{ column_qualifier ":=" expression |
[ table_qualifier ] "{" column_qualifier, [ column_qualifier ]... "}" ":=" "{" expression [ "," expression ]... "}"
}
( := (assignment) )
Assignments may happen only in aspect context, telling which table data modification command affects.
As follows from syntax diagram, htsql supports two kinds of assignment: single and multiple depending on how many columns are defined with single assignment operator. Without table_qualifier multiple form just groups several columns into single assignment expression. With table_qualifier, pointing at the subordinant table, linked rows may be affected:
/organization[nnn]/insert()?name:='NNN'&project{prj_id,name,start_date}:={'nnn','nnn prj', today()}
Here assignments are used with insert() command to add new rows to the database. Multiple assignment form is used to insert data into dependent table project.
Assignments with update() command are used to modify existing data:
/organization[tst]/update()?name:='TST'&project{status,start_date}:={'completed', today()}
Here all linked rows in project table will be updated.
In another example:
/project[nnn]/update()?name:=name.upper()&client:=@organization[tst]
First assignment generates new column value based on current value: name:=name.upper(). Second assignment uses pointer to set foreign key column.
Pointer is a reference to a specific row within some table, which can be used in an expression. Pointer definition:
"@" table_qualifier [ locator_clause ]
( locator_clause )
Pointer locator, unlike other locators, must specify single row (wildcards and enumerations are not allowed).
Main purpose of pointers in conjunction with data modification commands is to define parent reference for a dependent table. For example:
/project[la-102]/update()?client:=@organization[meyers]
This updates project with id=’la-102’ and sets its parent organization to “Meyers Construction” (id=’meyers’).
However pointers are legal in other expressions too.