Skip to main content

Generic JDBC

Generic JDBC is a connector allowing to connect DirectQuery to any database, as long as it provides a JDBC driver.

For DirectQuery to generate the proper SQL queries, some customization is still required, and can be done by implementing the interface com.activeviam.database.sql.api.query.ISqlDialect.

Sql Dialect​

The interface has a default implementation based on standard SQL. However, every database can have slightly different features, and so would require the overriding of some methods in this interface.

It is recommended to read the documentation of this interface, along with the implementation of the various methods. They would be a good starting point for overriding any of them.

The dialect declares many methods, which can be roughly grouped into a few categories.

SQL keywords and special characters​

The dialect defines standard keywords, such as "SELECT", "FROM", ... as well as special characters, like argument separator (",") ... It is possible to override one of these methods if necessary, but the large majority of databases shouldn't require it.

SQL functions​

SQL functions are functions transforming an expression, such as abs, nullif, ... They are taking one (or more) SqlPartPlaceholder as argument, and return a list of ISqlPart.

ISqlPart is a sealed interface with 2 implementations: SqlPartPlaceholder and SqlPartString. The first one represents the input, while the second one is just a string.

So as an example, the implementation of abs would be:

List<ISqlPart> absFunction(final SqlPartPlaceholder column) {
    return List.of(
        new SqlPartString("abs("),
        column,
        new SqlPartString(")"));
  }

DirectQuery can replace the placeholder with the required expression and generate the actual query to be run.

SQL aggregation functions​

SQL aggregation functions are functions aggregating rows, such as sum, min, ... Their implementation is similar to the SQL functions, explained above.

SQL Data Definition Language (DDL)​

Finally, DirectQuery also helps with generating complex DDL queries (e.g. for aggregate tables), and requires the definition of methods to return keywords related to DDL statements such as CREATE TABLE or keywords related to data types.

Time-travel support​

Time-travel is not supported by Generic JDBC. This means that updating applications based on this Database are subject to data desynchronization in case of changes in the Database.
Read this page to learn more about the visible effects of this desynchronization.

Vectors support​

Vector support is a feature heavily dependent on the actual database.

Most databases should be able to handle emulated vectors (multi-rows and multi-columns). Native vectors are unlikely to be supported, especially regarding aggregation functions.

Gotchas​

Nullable fields​

Some databases can define fields with nullable types. DirectQuery is capable of detecting this and defines its internal model accordingly.
While this is not a problem in itself, it can conflict with the rule in Atoti cubes that all levels must be based on non-nullable values. This does not create an issue of any sort as the local model is updated behind the scene, assigning a default value based on the type.
It has a side effect on the query performance, as DirectQuery must convert on-the-fly null values to their assigned default values, as well as adding extra conditions during joins to handle null values on both sides.