IslandSQL is an ANTLR 4 based parser for grammars used in SQL files. The parser requires a Java Virtual Machine supporting version 8 or newer and is available on Maven Central.
The following table shows the DBMS and their grammar versions in scope:
DBMS | Grammar | Version | HTML Reference (live) | PDF Reference (snapshot) |
---|---|---|---|---|
OracleDB | SQL*Plus | 23c | User's Guide and Reference | |
SQLcl | 23.4 | Users's Guide | ||
SQL | 23c | Language Reference | ||
PL/SQL | 23c | Language Reference | ||
PostgreSQL | psql | 16.2 | psql — PostgreSQL interactive terminal | |
SQL | 16.2 | The SQL Language | ||
PL/pgSQL | 16.2 | PL/pgSQL — SQL Procedural Language |
The HTML reference shows the latest version of the document. However, the latest snapshot version in PDF format represents the version that was used to define the grammar.
The current islands of interests are:
Statement | Notes |
---|---|
call |
complete statement |
delete |
complete statement |
explain plan |
complete statement |
insert |
complete statement |
lock table |
complete statement |
merge |
complete statement |
select |
complete statement (embedded PL/SQL only as list of tokens) |
update |
complete statement |
Tokens that are not part of the statements listed above are preserved as hidden tokens. As a result, the token stream represents the complete input (file).
The syntax diagrams of the IslandSQL grammar are produced by RR and can be found here.
The IslandSQL grammar is based on the grammars of the DBMSs in scope and not on the SQL:2023 standard. Nevertheless, it is interesting to consult the standard for constructs that are implemented differently in the DBMSs.
The table below shows links to the freely available BNF of the SQL:2023 standard. Furthermore, it contains links to some chosen root elements in the derived syntax diagrams of grammars produced by RR.
Please note that not all freely available parts of the standard contain a grammar definition in BNF.
If a SQL script runs without errors, but IslandSQL reports parse errors, then we consider this to be a bug. Exceptions are documented here.
Code that has been wrapped with the wrap utility can be installed in the target database. However, wrapped code is ignored by the IslandSQL grammar.
The grammar allows the use of keywords as identifiers. This makes the grammar robust and supports the fact OracleDB allows the use of keywords in various places.
However, there are cases where this leads to an unexpected parse tree, even if no keywords as identifiers are used. Here's an example:
select *
from emp
left join dept on emp.deptno = dept.deptno;
In this case left
is treated as a table alias of emp
, since join dept on emp.deptno = dept.deptno
is a valid innerCrossJoinClause
and the priority of the evaluation in ANTLR4 matches the order in the grammar.
Solving this issue is not simple, especially since OracleDB allows the use of left
or right
as valid table names and table aliases. Here's an another example:
with
right as (
select * from emp
)
select *
from right
right join dept on right.deptno = dept.deptno;
In this example OracleDB selects 15 rows (an empty emp for deptno 40
). The token right
on the last line is therefore treated as part of the outerJoinClause
by OracleDB and not as a table alias.
Prohibiting keywords as identifiers in certain places could lead to parse errors for working SQL. Therefore, the production of a false parse tree due to the support of keywords as identifiers is considered acceptable.
The following commands affect the grammar and are not interpreted by IslandSQL. The IslandSQL grammar is built on the default settings. As a result other values lead to errors.
However, the following psql meta commands are supported as alternative to a semicolon:
The grammar expects an SQL statement to end with a semicolon, unless it is the last statement in a file. Here is a valid example:
select 42 as result;
select 'fourty-two' as result
Removing the semicolon in the scripts will result in a parse error.
Substitution Variables can contain arbitrary text. They are replaced before the execution of a script. The IslandSQL grammar provides limited support for substitution variables. They can be used in places where a sqlName is valid.
Here's an example of a supported usage:
lock table &table_name in exclusive mode wait &seconds;
And here's an example of an unsupported usage:
lock table dept in &lock_mode mode nowait;
The grammar expects certain keywords at the position of &lock_mode
. Hence, this usage is not supported.
Variables in psql can contain arbitrary text. They are replaced before the execution of a script. The IslandSQL grammar provides limited support for these variables. They can be used in places where a sqlName is valid.
Here's an example of a supported usage:
\set schema public
select e.*, :'schema' as schema from :schema.emp as e;
And here's an example of an unsupported usage:
\set schema public.
select e.*, :'schema' as schema from :schema emp as e;
The grammar expects an identifier to not contain a period. Hence, this usage is not supported.
The access_parameters
clause used in inline_external_table or modified_external_table is driver specific. You can pass this access parameters as string or as a subquery returning a CLOB or embed the driver specific parameters directly. All variants are supported.
However, when you embed the drivers specific parameters directly, the parameters are parsed as a list of tokens. We do not plan to implement the driver specific grammars. See also:
- Oracle Database Utilities: External Tables
- Oracle Database Administrator's Guide: Using Inline External Tables
- Oracle Database Administrator's Guide: Overriding Parameters for External Tables in a Query
In OracleDB an unquoted identifier can contain a #
character. In PostgreSQL this is not allowed.
The IslandSQL grammar supports identifiers containing #
. As a result, in some cases, an expression containing a #
is interpreted as an identifier
instead of a bitwise exclusive OR expression
in IslandSQL.
Here are some examples:
Query | PostgreSQL | IslandSQL | Notes |
---|---|---|---|
select a#b from t; |
Bitwise XOR expression | Identifier a#b |
No whitespace around operator |
select a # b from t; |
Bitwise XOR expression | Bitwise XOR expression | Whitespace around operator |
select a #b from t; |
Bitwise XOR expression | Bitwise XOR expression | Identifier cannot start with a # |
select 1#2; |
Bitwise XOR expression | Bitwise XOR expression | Identifier cannot start with a digit |
Integrating custom operators in a generic way will most likely lead to conflicts with existing expressions and conditions. Therefore, the IslandSQL grammar does not support custom operators.
However, the custom operators provided by the PostGIS extension are included in the grammar.
IslandSQL is licensed under the Apache License, Version 2.0. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.