The Exasol SQL Statement Builder (ESB) is a Java library that allows you to define SQL statements in a internal Domain-specific language (DSL). This means it uses standard Java language features to create a DSL.
The project uses agile software development process, so this document contains only the portion of requirement necessary for the current iteration.
The goals of the ESB are:
- Abstraction from the SQL text representation
- Compile-time error detection (where possible)
- Extensibility (for different SQL dialects, validators and renderers)
- User friendly API
- ESB: Exasol SQL Statement Builder
- Renderer: extension that turns the abstract representation into a different one (most notably an SQL string)
- Validator: extension that validates a statements structure and content
This document uses Augmented Backus–Naur Form (ABNF) for syntax definitions.
This subsection list the ABNF terminals used in this document. Terminals are ABNF rules that cannot be split further down, like string literals for example.
COMMA = ","
L-BRACKET = "("
R-BRACKET = ")"
EQUAL-OPERATOR = "="
NOT-EQUAL-OPERATOR = "<>"
LESS-THAN-OPERATOR = "<"
LESS-THAN-OR-EQUAL-OPERATOR = "<="
GREATER-THAN-OPERATOR = ">"
GREATER-THAN-OR-EQUAL-OPERATOR = ">="
feat~statement-definition~1
The ESB allows users to define SQL statements in abstract form.
Needs: req
feat~sql-string-rendering~1
The ESB renders abstract SQL statements into strings.
Rationale:
The SQL strings are necessary input for executing queries (e.g. with JDBC).
Needs: req
feat~compile-time-error-checking~1
ESB reports detectable errors at compile-time.
Rationale:
Making sure at compile time that illegal constructs do not compile make the resulting code safer and reduces debugging efforts.
Needs: req
feat~data-conversion~1
ESB converts between values of compatible data types.
Rationale:
Different databases and related tools use different ways to store and process similar data types. A collection of well-tested converters saves the API users time and trouble.
Needs: req
req~statement-structure.step-wise~1
ESB lets users build the statement structure step-by-step.
Rationale:
This is necessary since complex statements are usually build as a result of multi-layered decision trees and parts of the statements are constructed in different places.
Covers:
Needs: dsn
req~statement-structure-limited-at-compile-time~1
ESB lets users create only valid statement structures at compile-time.
Rationale:
If users can't get illegal structures to compile, they don't need to spend time debugging them later.
Covers:
Needs: dsn
req~arithmetic-operators~1
ESB supports the following arithmetic operators: +, -, *, /.
Covers:
Needs: dsn
req~comparison-operations~1
ESB supports the following comparison operations:
operation = left-operand operator right-operand
left-operand = field-reference / literal
operator = equal-operator / not-equal-operator / greater-operator / less-than-operator /
greater-or-equal-operator / less-than-or-equal-operator
right-operand = field-reference / literal
Covers:
Needs: dsn
req~boolean-operators~1
ESB supports the following boolean operators: AND, OR and NOT
Covers:
Needs: dsn
req~like-predicate~1
ESB supports the [NOT] LIKE predicate.
Covers:
Needs: dsn
req~predicate-operators~1
ESB supports the following predicate operators:
[NOT] BETWEENEXISTS[NOT] INIS [NOT] NULL
Covers:
Needs: dsn
req~boolean-literals~1
ESB can convert the following string literals into boolean values, independently of the case they are written in:
- true:
true,t,yes,y,on,enabled,1 - false:
false,f,no,n,off,disabled,0
Covers:
Needs: dsn
req~literal-values~2
ESB supports the following literal values: default, double, float, integer, long, null, string, BigDecimal.
Covers:
Needs: dsn
req~value-table~1
ESB supports the following way to construct tables from a value table:
value-table = VALUES value-row *( COMMA value-row )
value-row = L-BRACKET expression *( COMMA expression ) R-BRACKET
Covers:
Needs: dsn
req~create-statements~1
ESB supports the following CREATE statements.
Create schema:
create-statement = "CREATE SCHEMA" schema-reference
schema-reference = schema
Create table:
create-statement = "CREATE TABLE" table-reference table-element-list
table-reference = table
table-element-list = L-BRACKET table-element [*( COMMA table-element )] R-BRACKET
Covers:
Needs: dsn
req~drop-statements~1
ESB supports the following DROP statement.
Drop schema:
drop-statement = "DROP SCHEMA" [IF EXISTS] schema-reference [CASCADE / RESTRICT]
schema-reference = schema
Drop table:
drop-statement = "DROP TABLE" [IF EXISTS] table-reference [CASCADE CONSTRAINTS]
table-reference = table
Covers:
Needs: dsn
req~insert-statements~1
ESB supports the following INSERT statement:
insert-statement = "INSERT INTO" table-reference [insert-columns]
insert-source
table-reference = table [AS table-alias]
insert-columns = L-BRACKET column *( COMMA column ) R-BRACKET
Covers:
Needs: dsn
req~values-as-insert-source~1
ESB supports a list of explicit values as INSERT source:
insert-source =/ "VALUES" L-BRACKET ( value-expression /
"DEFAULT" ) R-BRACKET
Covers:
Needs: dsn
req~merge-statements~1
ESB supports the following MERGE statement:
merge-statement = "MERGE INTO" table-reference
"USING" table-reference
"ON" condition
[ "WHEN MATCHED THEN" ( merge-update-clause / merge-delete-clause ) ]
[ "WHEN NOT MATCHED THEN" merge-insert-clause ]
table-reference = table [AS table-alias]
merge-update-clause = "UPDATE SET" column "="
( expression / "DEFAULT" ) * ( COMMA ( expression / "DEFAULT" ) ) [ where-clause ]
[ merge-delete-clause ]
merge-delete-clause = "DELETE" where-clause
merge-insert-clause = "INSERT" [ L-BRACKET column *( COMMA column ) R-BRACKET ]
merge-values
merge-values = "VALUES" L-BRACKET ( expression / "DEFAULT" )
*( COMMA ( expression / "DEFAULT" ) ) R_BRACKET
[ where-clause]
Covers:
Needs: dsn
req~select-statements~1
ESB supports the following SELECT statement:
select-statement = "SELECT" select-list [table-expression]
select-list = asterisk / derived-column *( COMMA derived-column )
asterisk = *
derived-column = expression [as-clause]
expression = arithmetic-expression / column-reference / function
table-expression = from-clause [where-clause] [limit-clause] [group-by-clause] [order-by-clause]
from-clause = "FROM" table-reference
where-clause = "WHERE" boolean-term
limit-clause = "LIMIT" integer-literal ""
group-by-clause = "GROUP BY" column-reference *( COMMA column-reference ) [having-clause]
having-clause = "HAVING" boolean-term
order-by-clause = "ORDER BY" column-reference *( COMMA column-reference ) [ "ASC" / "DESC" ]
Covers:
Needs: dsn
req~rendering.sql.configurable-case~1
Users can choose whether the keywords in the SQL string should be rendered in upper case and lower case.
Rationale:
While keyword case is mostly an esthetic point, different users still have different preferences.
Covers:
Needs: dsn
req~rendering.sql.confiugrable-identifier-quoting~1
ESB allows users to choose whether the following identifiers should be quoted in the rendered query:
- Schema identifiers
- Table identifiers
- Column identifiers
Rationale:
The Exasol database for example requires identifiers to be enclosed in double quotes in order to enable case sensitivity.
Covers:
Needs: dsn
req~rendering.sql.select~1
ESB renders abstract SELECT statements into SQL query strings.
Covers:
Needs: dsn
req~rendering.sql.create~1
ESB renders abstract CREATE statements into SQL data definition language strings.
Covers:
Needs: dsn
req~rendering.sql.drop~1
ESB renders abstract DROP statements into SQL data definition language strings.
Covers:
Needs: dsn
req~rendering.sql.insert~1
ESB renders abstract INSERT statements into SQL data manipulation language strings.
Covers:
Needs: dsn
req~rendering.sql.merge~1
ESB renders abstract MERGE statements into SQL data manipulation language strings.
Covers:
Needs: dsn
req~integer-interval-conversion~1
ESB converts values of type INTERVAL to integer and vice-versa.
Rationale:
Neighboring systems of an Exasol database often do not have equivalent data types, so conversion to a primitive data type is required.
Covers:
Needs: dsn