exasol / sql-statement-builder

Build SQL statements safely without string concatenation

SQL Statement Builder

The Exasol SQL Statement Builder abstracts programmatic creation of SQL statements and is intended to replace ubiquitous string concatenation solutions which make the code hard to read and are prone to error and security risks.

Goals:

1. Foster clean and readable code
2. Allow for thorough validation of dynamic parts
3. Detect as many errors as possible at compile time
4. Don't repeat yourself (DRY)
5. Allow extension for different SQL dialects

In a Nutshell

The following example gives you an idea about what you can do with the SQL Statement Builder. Check our user guide for more details.

Select select = StatementFactory.getInstance().select()
    .field("fieldA", "tableA.fieldB", "tableB.*");
select.from().table("schemaA.tableA");
select.limit(10);
StringRendererConfig config = StringRendererConfig.builder().quoteIdentifiers(true).build();
SelectRenderer renderer = new SelectRenderer(config);
select.accept(renderer);
String sql = renderer.render();

Table of Contents

Information for Users

"Users" from the perspective of the sql-statement-builder are developers integrating the module into their own software.

• User Guide
• API Documentation
• MIT License
• Changelog
• Dependencies

Information for Developers

• System Requirement Specification
• Design

sql-statement-builder's Issues

Always add day to string representation of INTERVAL DAY TO SECOND

Description

Writing to a column of type DAY TO SECOND fails if the day is omitted.

Steps to Reproduce

1. Try writing a string that contains the time only (e.g. 00:00:01.001)
2. Error occurs

Expected behavior

Rendered string representation can be used in INSERT command without modification.

Environment

• SQL Statement Builder: 0.1.1

Implement CAST function

Add support for special cases of scalar functions

Currently unsupported or partly supported scalar function:

String Functions:

• POSITION https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/position.htm#POSITION
• SUBSTR (partly supported without key words) https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/substr[ing].htm#SUBSTR[ING]
• TRIM (partly supported without key words) https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/trim.htm#TRIM

Date/Time Functions:

• ADD_ functions (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_days.htm#ADD_DAYS
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_hour.htm#ADD_HOUR
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_minutes.htm#ADD_MINUTES
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_months.htm#ADD_MONTHS
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_seconds.htm#ADD_SECONDS
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_weeks.htm#ADD_WEEKS
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/add_years.htm#ADD_YEARS

• CONVERT_TZ (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/convert_tz.htm#CONVERT_TZ

• DATE_TRUNC
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/date_trunc.htm#DATE_TRUNC

• DAY (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/day.htm#DAY

• _BETWEEN functions (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/days_between.htm#DAYS_BETWEEN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/hours_between.htm#HOURS_BETWEEN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/minutes_between.htm#MINUTES_BETWEEN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/month_between.htm#MONTH_BETWEEN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/seconds_between.htm#SECONDS_BETWEEN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/years_between.htm#YEARS_BETWEEN

• EXTRACT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/extract.htm#EXTRACT

• HOUR (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/hour.htm#HOUR

• MINUTE (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/minute.htm#MINUTE

• MONTH (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/month.htm#MONTH

• WEEK
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/week.htm#WEEK

• YEAR (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/year.htm#YEAR

• ROUND
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/round%20(datetime).htm#ROUND_(datetime)

• SECOND (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/second.htm#SECOND

Conversion Functions:

• CAST
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/cast.htm#CAST

• CONVERT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/convert.htm#CONVERT

• TO_CHAR (datetime) (can contain keywords, supported without them now)
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/to_char%20(datetime).htm#TO_CHAR_(datetime)

Functions for Hierarchical Queries:

• CONNECT_BY_ISCYCLE, CONNECT_BY_ISLEAF, LEVEL, SYS_CONNECT_BY_PATH
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/sys_connect_by_path.htm#SYS_CONNECT_BY_PATH

Other Scalar Functions

• CASE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/case.htm#CASE

• SPACE https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/space.htm#SPACE

We need to decide which of them we want to support and implement them if needed.

Support `WHERE` in `MERGE`

Situation

Basic MERGE support was introduced with #51. Support for WHERE is still missing in the THEN UPDATE, THEN DELETE and THEN INSERT clauses.

Acceptance Criteria

1. WHERE supported in THEN UPDATE, THEN DELETE and THEN INSERT.

Refactor `BooleanExpressionVisitor`

Currently, BooleanExpressionVisitor uses visit and leave. I've got the feeling that it would be better style to only use visit in combination with recursive calls, but I'm not sure about that.

Fluent interface broken

the fluent programming pattern is not used consequently any more:

Designed was:

final Select select = StatementFactory.getInstance()
    .select()
    .field("fieldA", "tableA.fieldB", "tableB.*")
    .from()
    .table("schemaA.tableA")
    .limit(10);

Now it is: (see #64 )

final Select select = StatementFactory.getInstance()
        .select();
select.field("fieldA", "tableA.fieldB", "tableB.*")
        .from()
        .table("schemaA.tableA");
select.limit(10);

Implement missed expressions and clauses for integration sql-statement-builder in virtual-schemas

Problem:

We want to use sql-statement-builder for rendering sql statements in virtual-schemas project.
Unfortunate, sql-statement-builder doesn't support all necessary functionalities.

Solution:

Add missed expressions and clauses.
For example:
-GROUP BY
-ORDER BY
-HAVING
etc.

Implement "create schema" and "drop schema" functionality

Acceptance criteria

1. CREATE SCHEMA my_schema
2. DROP SCHEMA IF EXISTS my_schema CASCADE/RESTRICT;

Refactor tests in com.exasol.sql.expression.function.exasol

Currently the tests in com.exasol.sql.expression.function.exasol directly use the select().function( API. These tests should however only test the API of their corresponding class. The select() API should be tested in its corresponding test.

Add issue templates

Add issue templates for:

• feature requests
• bug reports
• custom issues

Fix Travis CI Link in Readme

Description

Currently the Travis CI link in the Readme use EXASOL. It should be changed to exasol.

Steps to Reproduce

1. Update readme by renaming EXASOL -> exasol

Expected behavior

Should show travis ci results on readme instead of unknown.

Add Sonar

As a developer,
I want the CI build to automatically run static code analysis on all my branches,
so that I catch code smells that I overlooked as early as possible.

Select from Values table with no rows

Add suport for LIKE operator

Acceptance criteria:

I can build statements like SELECT * FROM BOOKS WHERE ISBN LIKE '123%'.

Support column references in value expressions

Situation

Column references — i.e. names of columns in an expression — are valid value expressions. That means that a column reference can stand where a value expression is expected.

Example:

MERGE INTO ... AS ...
USING ... AS "SRC" ON ...
WHEN NOT MATCHED THEN INSERT ("ID", ...) VALUES ("SRC"."ID", ...)

In the example above the content of VALUES is a list of value expressions. Column references are useful in that scenario.

Acceptance Criteria

1. Column references are accepted where value expressions are required.

Publish SQL Statement Builder's JavaDoc API and link it in user guide

We need to link SQL Statement Builder's JavaDoc API in CreateTable.md.

Add support for unsupported Aggregate and Analytic functions

Aggregate Functions

Unsupported

• GROUPING[_ID]
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/grouping[_id].htm

• PERCENTILE_CONT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/percentile_cont.htm

• PERCENTILE_DISC
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/percentile_disc.htm

Partly supported without keywords and OVER clause

• AVG
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/avg.htm

• CORR
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/corr.htm

• COUNT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/count.htm

• COVAR_POP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/covar_pop.htm

• COVAR_SAMP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/covar_samp.htm

• FIRST_VALUE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/first_value.htm

• GROUP_CONCAT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/group_concat.htm

• LAST_VALUE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/last_value.htm

• MAX
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/max.htm

• MEDIAN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/median.htm

• MIN
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/min.htm

• REGR_FUNCTIONS
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/regr_function.htm

• STDDEV
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/stddev.htm

• STDDEV_POP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/stddev_pop.htm

• STDDEV_SAMP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/stddev_samp.htm

• SUM
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/sum.htm

• VAR_POP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/var_pop.htm

• VAR_SAMP
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/var_samp.htm

• VARIANCE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/variance.htm

Analytic Functions

Unsupported

• CUME_DIST
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/cume_dist.htm

• DENSE_RANK
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/dense_rank.htm

• LAG
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/lag.htm

• LEAD
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/lead.htm

• NTH_VALUE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/nth_value.htm

• NTILE
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/ntile.htm

• PERCENT_RANK
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/percent_rank.htm

• PERCENTILE_CONT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/percentile_cont.htm

• PERCENTILE_DISC
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/percentile_disc.htm

• RANK
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/rank.htm

• RATIO_TO_REPORT
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/ratio_to_report.htm

• ROW_NUMBER
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/row_number.htm

Partly supported without keywords and OVER clause

• ANY
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/any.htm

• EVERY
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/every.htm

• LISTAGG
  https://docs.exasol.com/sql_references/functions/alphabeticallistfunctions/listagg.htm

Other

• NAMED WINDOW CLAUSE
  https://docs.exasol.com/sql_references/functions/analyticfunctions.htm#microcontent5
• over_clause
  https://docs.exasol.com/sql_references/functions/analyticfunctions.htm?Highlight=over_clause

User guide is 404

The user guide can't be opened. Page 404 appears. What's the problem?

Add `audit-dependencies` and Version Maven plugins

https://sonatype.github.io/ossindex-maven/maven-plugin/

ColumnReference not quoted

@Test
    void testQuotedIdentifiers() {
        final StringRendererConfig config = StringRendererConfig.builder().quoteIdentifiers(true).build();
        assertThat(this.select.where(eq(ExpressionTerm.stringLiteral("foo"), ColumnReference.of("test"))),
                rendersWithConfigTo(config, "SELECT * FROM \"person\" WHERE 'foo' = \"test\""));
    }

Expected: SELECT * FROM "person" WHERE 'foo' = "test"
     but: SELECT * FROM "person" WHERE 'foo' = test

Add SELECT and INSERT statements' description into User Guide

Create a guide describing existing behavior:

1. CREATE TABLE
2. SELECT
3. INSERT INTO

Add support for SELECT FROM (SELECT ...) queries

Add support for selecting from sub selects.

For example:
SELECT * FROM (SELECT * FROM ...)

Until now the sql-statement-builder has no support for adding subselcets to the from clause.

Implement "drop table" functionality

Acceptance criteria

"DROP TABLE IF EXISTS table_name CASCADE CONSTRAINTS" is implemented.

Add support for UDFs

Add support for UDF calls like:

SELECT sample_simple(id, user_name, page_visits, 20)
 EMITS (id INT, user_name VARCHAR(100), PAGE_VISITS int)
 FROM people;

In contrast to a normal function an UDF needs the EMITS clause with a DDL of the result columns.

Add support for building "select from values as ..." queries

I could not find a way to build queries like:
SELECT COL1 FROM VALUES (1, 2) AS T(COL1, COL2)
I was not able to build this query using the sql-statemant-builder as I could not find a way to build part is the AS T(COL1, COL2) part.

Injection safe interface for building literals

Right now now there is no possibility to build injection save statements directly. The only possible way is to use SQL prepared statements and place holders. This does however not work for all use cases. For example in a virtual schema the push down statement is just an string, not a SQL Statement. Thus place holders can't be used.

Add BigDecimal literal

Add a literal for BigDecimal values

Improve AbstractFragmentRenderer's methods naming

It's hard to use them now, because names seem quite similar.

Travis CI fails to build with openjdk11

Description

I did a small commit with one change in README.md file and after that Travis CI failed to build the project with openjdk11.

Steps to Reproduce

1. Commit into a new branch with pull request
2. Build with openjdk8 passed.
3. Build with openjdk11 failed.

Expected behavior

I expected build to be successfully passed.

Environment

• Java Version: openjdk11

Additional context

Error message:

[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 13.766 s
[INFO] Finished at: 2019-01-30T11:43:14Z
[INFO] Final Memory: 23M/84M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.apache.maven.plugins:maven-javadoc-plugin:3.0.1:jar (attach-javadocs) on project sql-statement-builder: MavenReportException: Error while generating Javadoc:
[ERROR] Exit code: 1 - javadoc: error - The code being documented uses modules but the packages defined in https://docs.oracle.com/javase/8/docs/api/ are in the unnamed module.
[ERROR]
[ERROR] Command line was: /home/travis/openjdk11/bin/javadoc @options @packages
[ERROR]
[ERROR] Refer to the generated Javadoc files in '/home/travis/build/exasol/sql-statement-builder/target/apidocs' dir.
[ERROR]
[ERROR] -> [Help 1]
[ERROR]
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR]
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException
The command "mvn clean install" exited with 1.

Replace <code> by {@code}

Implement "create table" functionality

As a software developer, I want to use the sql-statement-builder to create new tables in my database.

Acceptance criteria

1. "CREATE TABLE table name table element list" is implemented.

Release to maven central

Is your feature request related to a problem? Please describe.
No.

Describe the solution you'd like
I want to use the sql-statement-builder by including as a dependency from another project.

Describe alternatives you've considered
None.

Additional context
Should be released to maven central and easy to reference from another project.

INSERT documentation missing

Situation

Documentation for the INSERT statement is missing.

Acceptance Criteria

1. Documentation for the INSERT statement exists.

Port the project from Java 8 to Java 11

Add support for Exasol Aggregate and Analytic functions in SELECT statement

Improve `MERGE` documentation and example

Situation

It is confusing that the MERGE documentation does not explain that you have to pick at least one of the merge strategies for a full merge statement.

Acceptance Criteria

• MERGE strategy constraints are explained
• Examples are improved.

Copy-Paste-Error

There's a little copy-paste-error in the docs:
On the page https://github.com/exasol/sql-statement-builder/blob/master/doc/user_guide/statements/select.md, it says:
final Select selectWithOneField = factory.select().all();
shouldn't that be
final Select selectWithAllFields = factory.select().all();

Refactor ColumnsDefinitionRenderer

Refactor ColumnsDefinitionRenderer like #100

Support `FROM (VALUES('a', 'b))`

Situation

If you have a proprietary data format and want to convert the contents into a SELECT statement that list all contents, the VALUES list constructor is very helpful.

Acceptance Criteria

1. SSB support

   SELECT * FROM (VALUES('a', 'b'))

Refactor Comparison and Like

Refactor code so that Comparison and Like predicate have a common basis.

Support MERGE Statement

Situation

The MERGE statement provides a combination of INSERT, UPDATE and DELETE.

It is especially useful in cases where new data should be combined with data that already exists in a table.

Acceptance Criteria

1. MERGE supported without WHEN

Add Exasol functions

As a user, I want to use sql-statement-builder to build Exasol SQL functions(Aggregate, Scalar etc)

Example in Readme broken

in the example in the README.md in line StringRendererConfig.builder().useQuotes().build(); the useQuotes() method does not exist.

Replace validation in DropSchema class

Problem

At the moment we don't have validation for SQL statements. In the DropSchema class private void validateCascadeAndRestrict() was created, but it should be replaced with a proper validation.

Java primitive types as literals

Situation

Currently primitive types are not all supported where literals are expected (e.g. in value expressions). For the sake of uniformity and convenience all Java primitive types should be supported as value literals.

Acceptance

1. All primitive Java types are supported as value literals

Fix Sonar code smells

Fix the code smells Sonar found in the development version 0.3.0.

Support negative intervals

Is your feature request related to a problem? Please describe.
If you calculate how many days ago some data was created, you get a negative integer as result.

Describe the solution you'd like
The SQL query builder must support rendering and parsing negative intervals.

Refactor `ValueExpressionVisitor`

With adding more and more functions, ValueExpressionVisitor gets longish. We could split it up into two visitors. One for functions and one for literals, or similar.

Review and fix outdated documentation

Description

The documentation has not been updated for a while and contains at least a few outdated (wrong) examples:

#65
#61

We have to review and update all documentation files.

Fix JavaDoc in combination with Java 11

Description

When running a Travis CI build with Java 11 we get the following error:

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-javadoc-plugin:3.0.1:jar (attach-javadocs) on project sql-statement-builder: MavenReportException: Error while generating Javadoc: 
[ERROR] Exit code: 1 - javadoc: error - The code being documented uses modules but the packages defined in https://docs.oracle.com/javase/8/docs/api/ are in the unnamed module.
[ERROR] 
[ERROR] Command line was: /home/travis/openjdk11/bin/javadoc @options @packages
[ERROR] 
[ERROR] Refer to the generated Javadoc files in '/home/travis/build/exasol/sql-statement-builder/target/apidocs' dir.

Steps to Reproduce

1. Enable OpenJDK11 in .travis.yml
2. Run build in Travis CI

Expected behavior

Build succeeds.

Environment

• SQL Statement Builder: 0.3.0 SNAPSHOT
• Java Version: openjdk version "11.0.2" 2019-01-15
  OpenJDK Runtime Environment 18.9 (build 11.0.2+9)
  OpenJDK 64-Bit Server VM 18.9 (build 11.0.2+9, mixed mode)