This chapter describes main structural elements of HTSQL language.
HTSQL query may be considered in two perspectives. Since HTSQL translates query into SQL, each HTSQL syntax construct has SQL equivalent. That’s why SQL and database structure perspective can be used for understanding HTSQL syntax issues. HTSQL has full analogs of different SQL statements, SELECT and WHERE clauses, grouping and aggregation, etc.
On the other hand, HTSQL has object-oriented traits and some issues are easier to understand in object-oriented perspective. For example, specifier or context terms, identifier resolution are much like objects and their properties.
These two perspective are used both in current documentation.
Double quoted symbols come as is into resulting construction.
Non-quoted terms come for basic language elemets or other subconstructions, defined elsewhere (hyperlinks to subconstructions are indicated right below the diagram).
Some diagrams are split into several lines for better readability.
HTSQL doesn’t address database objects directly. It handles its own entities instead, which are mapped on database objects. Text-based file, that describes database structure and htsql entities mapping, is called catalog.
Structure of HTSQL catalog is out of scope of the current reference, but htsql entities and their mapping are considered here. HTSQL entities are:
- authority
- perspective
- aspect
- field
Entities are linked with “contains” relationship in a hierarchical manner, where authority is top level and field is bottom level.
Authority identifies actor, on whose behalf htsql queries are executed. Authority is mapped on database user.
Perspective identifies set of privileges, applyed to an authority. Each authority has list of allowed perspectives and default one, which is applied if no perspective is explicitly specified. For some database management systems perspective is mapped on database role. Privileges in HTSQL are defined on aspects and grant rights to select, insert, update or delete.
Aspect is htsql entity for database table. Aspect full name is composite and consists of namespace and short name. Namespace for aspect stands for database shema.
Field of an aspect is htsql entity for database column of a table.
Catalog also describes primary keys and foreign key references to discover aspect links and perform valid joins.
HTSQL entities may be mapped on database objects not directly, hence considering HTSQL query it’s more correct to talk in terms of aspects and fields rather than tables and columns. However once difference between these terms is realized, for more plainness and clarity further in this reference term table in most cases stands both for database object and HTSQL aspect. The same way term column stands both for database object and HTSQL field.
Primitive types:
| Domain | Subtype | Description |
|---|---|---|
| boolean | boolean data, taking one of true and false | |
| string | character data | |
| number | generic number type | |
| decimal | synonym for number | |
| integer | integer | |
| float | floating point number | |
| binary | binary data | |
| bitstring | bit string data | |
| date | stores year, month and day | |
| time | without timezone | stores hours, minutes, seconds and fractional didgits of second |
| with timezone |
|
|
| datetime | without timezone | stores year, month, day, hours, minutes, seconds and fractional didgits of second |
| with timezone |
|
|
| timedelta | stores days, hours, minutes, seconds and fractional didgits of second represents time interval for date and time arithmetic | |
| unknown | represents any other SQL data type, which HTSQL does not support directly |
Compound types:
| Domain | Description |
|---|---|
| array |
|
| tuple |
|
| composite | represents SQL composite data types |
HTSQL types determine the way data are manipulated and represented, but not the way data are stored. Exact implementation of the data type may differ depending on underlying RDBMS system.
Boolean operators list see in section Boolean Operators
Boolean function list see in section Boolean Functions
Boolean conversion details see in section Boolean conversion
String operators list see in section String Operators
String function list see in section String Functions
String conversion details see in section String conversion
Numeric operators list see in section Numeric Operators
Numeric function list see in section Numeric Functions
Numeric conversion details see in section Numeric conversion
4 domains belong to this group: date, time, datetime, and timedelta.
Date and Time operators list see in section Date And Time Operators
Date and Time function list see in section Date And Time Functions
Date and Time conversion and constructor details see in section Date and Time conversion
Date and Time domains provide list of properties to access stored fields separately. List of properties is common, but they are applicable not to all 4 date-time domains, but to some of them, depending on wether particular domain stores such field or not.
Refer to the section below for detailed information:
Array operators list see in section Array Operators
Array function list see in section Array Functions
Array constructor details see in section Array constructor
Literals are constants of primitive types, while sequences and maps are constants of compound types.
String and numeric literals are supported by HTSQL.
More strictly, literals belong to special domain untyped until their actual type is determined from context. For example, within conditional expressions or boolean comparison ‘true’ and ‘false’ literals are implicitly cast to boolean. But in string comparison they are treated as string literals.
String literals are single-quoted (‘) character sequences. If signle quote character occurs within a string value, it must be doubled. For example:
'O''Reilly'
--->
O'Reilly
Numeric literals need not be quoted and are just sequences of didgits. All numeric literals are decimal and may contain decimal didgits only (0-9).
Sequence is a constant of array type and represents ordered set of elemets. Sequence definition:
"[" constant [ "," constant ]... "]"
Sequence elements may be constants of compound type themselves.
Map is a constant of tuple type and represents set of named elemets. Map definition:
"{" name ":" constant [ "," name ":" constant ]... "}"
Where name is a string literal. Map values may be constants of compound type themselves.
If a column in a row has no value, then the column is said to be null, or to contain null. All operators return null when given a null operand. Exceptions are special null-safe equality operators and null-checking function is_null .
Difference between them and is_null() function is shown in the table:
| x | y | x = y | x != y | x == y | x !== y | is_null(x) | is_null(y) |
|---|---|---|---|---|---|---|---|
| 10 | 10 | true | false | true | false | false | false |
| null | 10 | null | null | false | true | true | false |
| 10 | null | null | null | false | true | false | true |
| null | null | null | null | true | false | true | true |
As shown in the table, simple equality evaluates to null if one of the operands is null, while null-safe equality treats null as another regular value.
Function is_null(x) is equivalent to x == null()
Identifiers can be double-quoted (“), or unquoted. If identifier contains double-quotes itself, the symbol is escaped by one more double-quote. For example, Hotel “California” quoted is “Hotel “”California”“”
Catalog identifiers when quoted are resolved using exact matching, while unquoted matches HTSQL entities using following rules:
- Case-insensitive comparison is used to match object’s name.
- Underscore symbol (_) can be used to match space, dash, or any other non-alphanumeric character.
For instance, test_3 matches aspects named TEST-3 and Test 3.
This matching scheme permits HTSQL identifiers to serve as valid Javascript identifiers. It also permits HTSQL URIs to be used in double-quoted XML/HTML attributes without percent-encoding.
If unquoted identifier matches two or more objects, quoted identifier must be used. For instance, in case if aspects TEST-3 and Test 3 from previous example both exist.
Aspects, functions, operators and commands have a namespace. Fully qualified object name must be unique and is defined as:
namespace ":" identifier
Namespace of an aspect corresponds to database schema.
Aliases are symbol names, that are defined to represent some expression. Once defined, aliases may be used in further within a query. In the resulting SQL aliases are substituted with original expression.
Aliases make code reusable within the same query and also affect output: aliases, given to an expression within a selector, are also name of result columns.
Note, that both return expression itself, so they can be used within more complicated expression.
Alias must be a valid identifier, not clashing with database objects or earlier defined aliases.
Since HTSQL query is an URL, it may be used as html form action. Note, that form method POST is required, since GET method will add parameters to URL and break htsql query. Form parameters, sent to the server, may be used within htsql query expressions. For this purpose htsql syntax supports placeholders for form parameters, called variables:
"$" name
Variable name should match form parameter name. At query execution time variables are substituted with corresponding parameter value.
HTSQL uses UTF-8 encoding for query transmission and result representation.
No special escaping for non-printable characters is required, since HTSQL query string is a URI locator (as described in RFC 2396) and can be percent-encoded if required. Because of percent-encoding percent sign itself must be written in encoded form: %25.
HTSQL catalog provides information about authorities (users), perspectives (roles) and privileges.
HTSQL core performs no actor’s identity check, but it is supposed to be done by authenication plugins. Those perform authenication in their specific way (depending on authenication policy and RDBMS authenication method) and pass approved authority to HTSQL core, where one of perspectives and related set of privileges are applied.
HTSQL executes each query in separate transaction. However it’s possible to execute several queryies within single transaction: using transaction() command. Although the rule “one query - one transaction” is kept, the command allows to define embedded queries, which are executed within common transaction. Refer to Transactions Supporting Command for more details.