> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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 creating
an instance of the class `com.activeviam.database.jdbc.api.SqlDialect`.

DirectQuery supports some databases through a SQL dialect implementation:

* [Dremio](https://activeviam.jfrog.io/artifactory/activepivot-mvn-release/com/activeviam/database/jdbc/dialect/dremio/dremio-dialect)

```xml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
<dependency>
    <groupId>com.activeviam.database.jdbc.dialect.dremio</groupId>
    <artifactId>dremio-dialect</artifactId>
    <version>${atoti.server.version}</version>
</dependency>
```

## Sql Dialect

The dialect implementation is split across a handful of interfaces, each with a default implementation based on standard SQL.
Each interface covers a different aspect of the SQL dialect, such as keywords, types, functions, ...

However, every database can have slightly different features, and would require the overriding of some methods in these interfaces.

It's then possible to create a new instance of `com.activeviam.database.jdbc.api.SqlDialect` via its builder:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
return SqlDialect.builder()
    .sqlFormatter(SQL_FORMATTER)
    .keywords(new TransactSqlSqlDialectForKeywords())
    .types(new TransactSqlSqlDialectForTypes())
    .functions(new TransactSqlSqlDialectForFunctions())
    .aggregations(new TransactSqlSqlDialectForAggregations())
    .build();
```

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

### `com.activeviam.database.jdbc.api.ISqlDialectForKeywords`

This interface defines standard keywords, such as "SELECT", "FROM", ...
It is possible to override one of these methods if necessary, but a large majority of databases shouldn't require it.

### `com.activeviam.database.jdbc.api.ISqlDialectForTypes`

This interface defines standard SQL types, as well as how to serialize literals.

### `com.activeviam.database.jdbc.api.ISqlDialectForFunctions`

This interface defines SQL functions, i.e. functions transforming an expression, such as `abs`, `nullif`, ...
They are taking one (or more) `SqlPartPlaceholder` as argument, and return a list of `ISqlPart`. <br />

`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:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
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.

### `com.activeviam.database.jdbc.api.ISqlDialectForAggregationFunctions`

This interface defines SQL aggregation functions, i.e. functions aggregating rows, such as `sum`, `min`, ...
Their implementation is similar to the SQL functions, explained above.

### `com.activeviam.database.jdbc.api.ISqlDialectForTableManagement`

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.

### SQL formatter

Optionally, it's also possible to add a SQL formatter to the dialect. This formatter is used when printing queries into the logs,
and might make the queries easier to read.

## JDBC Type

The connector tries its best to match the column's data type in the external database to the relevant Atoti type.
However, each database (and the JDBC driver) can implement the types slightly differently (e.g. the management
of timezones for timestamps and dates).

The API offers the option of overriding the default behavior, by providing an implementation of
`com.activeviam.database.sql.api.ICustomJdbcTypeMatcher` to the builder of `com.activeviam.directquery.jdbc.api.GenericJdbcClientSettings`.

Here is an example of such a custom matcher:

```java theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
externalJdbcType -> {
  if (externalJdbcType.jdbcType() == JDBCType.TIMESTAMP) {
    final String typeName = externalJdbcType.externalTypeName();
    final boolean isNull = externalJdbcType.isNullable();
    return switch (typeName) {
      case SnowflakeSqlDialect.TIMESTAMPNTZ_TYPE ->
          isNull ? StandardTypes.NULLABLE_LOCAL_DATE_TIME : StandardTypes.LOCAL_DATE_TIME;
      case SnowflakeSqlDialect.TIMESTAMPLTZ_TYPE ->
          isNull ? StandardTypes.NULLABLE_TIMESTAMP : StandardTypes.TIMESTAMP;
      default -> throw new IllegalArgumentException("Invalid Timestamp type: " + typeName);
    };
  }
  return null;
};
```

## 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.<br />
Read this [page](../versioning) 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](../vectors#vector-emulation) (multi-rows and multi-columns).
Native vectors are unlikely to be supported, especially in regard to 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.<br />
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.<br />
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.

## Testing your dialect implementation

To test your dialect implementation, you can use the `com.activeviam.database.jdbctck.api.ATestGenericJdbcUponTck` abstract class.
You need to implement a few simple methods, to indicate the names of the catalog and schema, the naming standard of your database (are they lower or upper case),
the actual dialect to test as well as the JDBC properties used to connect to the testing database.

Ask your favorite data squad for the CSV files containing the testing data.