...
Synapse will automatically create an index of the current rows of each table entity. This index will be used to support all SQL-like queries against a table. Since the index only contains the most recent (or current) version of each row, it is only possible to query against the current version.
The following BNF defines the supported query language. This BNF is simplified sub-set of: sql-92, that excludes sub-queries, joins, aliases, and all DML.
| Code Block |
|---|
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::=
<asterisk>
| <column name> [ { <comma> <column name> }... ]
<table expression> ::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
[ <order by clause> ]
[ <pagination clause> ]
<from clause> ::= FROM <table name>
<where clause> ::= WHERE <search condition>
<group by clause> ::= GROUP BY <grouping column reference list>
<grouping column reference list> ::=
<column name> [ { <comma> <column name> }... ]
<order by clause> ::= ORDER BY <sort specification list>
<sort specification list> ::= <column name> [ { <comma> <column name> }... ]
<pagination clause> ::= LIMIT <literal> OFFSET <literal>
|
<search conditions>
| Code Block |
|---|
<search condition> ::=
<boolean term>
| <search condition> OR <boolean term>
<boolean term> ::=
<boolean factor>
| <boolean term> AND <boolean factor>
<boolean factor> ::= [ NOT ] <boolean test>
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]
<boolean primary> ::= <predicate> | <left paren> <search condition> <right paren>
<predicate> ::=
<comparison predicate>
| <between predicate>
| <in predicate>
| <like predicate>
| <null predicate>
| <quantified comparison predicate>
| <exists predicate>
| <match predicate>
| <overlaps predicate>
<comparison predicate> ::= <row value constructor> <comp op> <row value constructor>
<row value constructor> ::=
<row value constructor element>
| <left paren> <row value constructor list> <right paren>
| <row subquery>
<row value constructor element> ::=
<value expression>
| <null specification>
| <default specification>
<value expression> ::=
<numeric value expression>
| <string value expression>
| <datetime value expression>
| <interval value expression>
<numeric value expression> ::=
<term>
| <numeric value expression> <plus sign> <term>
| <numeric value expression> <minus sign> <term>
<term> ::=
<factor>
| <term> <asterisk> <factor>
| <term> <solidus> <factor>
<factor> ::= [ <sign> ] <numeric primary>
<numeric primary> ::= <value expression primary> | <numeric value function>
<value expression primary> ::=
<unsigned value specification>
| <column reference>
| <set function specification>
| <case expression>
| <left paren> <value expression> <right paren>
| <cast specification>
<unsigned value specification> ::= <unsigned literal> | <general value specification>
<unsigned literal> ::= <unsigned numeric literal> | <general literal>
<general value specification> ::=
<parameter specification>
| <dynamic parameter specification>
| <variable specification>
<parameter specification> ::= <parameter name> [ <indicator parameter> ]
<parameter name> ::= <colon> <identifier>
<indicator parameter> ::= [ INDICATOR ] <parameter name>
<dynamic parameter specification> ::= <question mark>
<variable specification> ::= <embedded variable name> [ <indicator variable> ]
<embedded variable name> ::= <colon><host identifier>
<qualifier> ::= <table name> | <correlation name>
<correlation name> ::= <identifier>
<set function specification> ::=
COUNT <left paren> <asterisk> <right paren>
| <general set function>
<general set function> ::=
<set function type> <left paren> [ <set quantifier> ] <value expression> <right paren>
<set function type> ::= AVG | MAX | MIN | SUM | COUNT
<set quantifier> ::= DISTINCT | ALL |
Table metadata Updates
Columns and constrains can be added or removed from a TableEntity at any time. This is done by updating the TableEntity (similar to all Entity updates). However, a few items should be considered:
...