stormify/onl.ycode.stormify/SqlDialect

SqlDialect

enum SqlDialect : Enum<SqlDialect>

SQL dialect definitions for different database systems.

This enumeration provides database-specific SQL generation for:

Sequence value retrieval (Oracle, PostgreSQL, SQL Server, H2, HSQLDB, Derby)
Generated key retrieval strategies (IDENTITY, RETURNING, etc.)
Pagination queries (LIMIT/OFFSET, ROWS FETCH, ROW_NUMBER())
Special ORDER BY handling for specific row positioning

Supported Databases

Full Support:

MySQL 5.x, 8.x+ (auto-increment, sequences in 8.0+)
MariaDB <10.3, 10.3+ (sequences supported in 10.3+)
PostgreSQL (sequences, RETURNING clause)
Oracle 11g and older (sequences, ROW_NUMBER pagination)
Oracle 12c+ (sequences, OFFSET/FETCH pagination)
SQL Server 2008 and older (sequences, ROW_NUMBER pagination)
SQL Server 2012+ (sequences, OFFSET/FETCH pagination)
SQLite (auto-increment, no sequences)
H2 (sequences, PostgreSQL-compatible)
HSQLDB (sequences)
Apache Derby (sequences)

Automatic Detection

The dialect is automatically detected from JDBC DatabaseMetaData:

val stormify = Stormify(dataSource)
println(stormify.sqlDialect) // e.g., MYSQL_NEW, POSTGRESQL, etc.

Detection is based on:

Database product name (case-insensitive matching)
Major and minor version numbers
Product version string (for MariaDB detection)

Dialect Features

Each dialect defines:

Sequence SQL: How to fetch next value from a sequence

Oracle: SELECT seq_name.NEXTVAL FROM dual
PostgreSQL: SELECT nextval('seq_name')
SQL Server/H2/HSQLDB/Derby: SELECT NEXT VALUE FOR seq_name
MySQL/SQLite: null (use auto-increment)

Generated Key Retrieval: How auto-generated IDs are retrieved

BY_INDEX: Get generated key by index (MySQL, MariaDB, SQLite, H2)
BY_NAME: Get generated key by column name (PostgreSQL, SQL Server, HSQLDB, Derby)
NONE: No automatic retrieval, manual handling (Oracle)

Pagination: SQL syntax for LIMIT/OFFSET functionality

LIMIT/OFFSET: MySQL, MariaDB, PostgreSQL, SQLite, H2
OFFSET/FETCH: Oracle 12c+, SQL Server 2012+, Derby
ROW_NUMBER(): Oracle 11g, SQL Server 2008

Fallback Behavior

If database is not recognized, uses UNKNOWN dialect with:

No sequence support
No generated key retrieval
LIMIT/OFFSET pagination (most compatible)

Entries

MARIA_DB_OLD

The MariaDB dialect for versions older than 10.3.

MARIA_DB_NEW

The MariaDB dialect for versions 10.3 and newer.

MYSQL_OLD

The MySQL dialect for versions older than 8.

MYSQL_NEW

The MySQL dialect for versions 8 and newer.

ORACLE_NEW

The Oracle dialect for versions 12 and newer.

ORACLE_OLD

The Oracle dialect for versions older than 12.

POSTGRESQL

The PostgreSQL dialect.

SQL_SERVER_NEW

The SQL Server dialect for versions 2012 and newer.

SQL_SERVER_OLD

The SQL Server dialect for versions older than 2012.

SQLITE

The SQLite dialect.

The H2 database dialect.

HSQLDB

The HSQLDB (HyperSQL) database dialect.

DERBY

The Apache Derby database dialect.

UNKNOWN

The dialect that is used when the database product name cannot be determined.

FAILSAFE

A failsafe dialect, mostly in case of an error.

Types

GeneratedKeyRetrieval

enum GeneratedKeyRetrieval : Enum<SqlDialect.GeneratedKeyRetrieval>

Strategy for retrieving auto-generated keys after an INSERT.

Properties

entries

val entries: EnumEntries<SqlDialect>

Returns a representation of an immutable list of all enum entries, in the order they're declared.

generatedKeyRetrieval

val generatedKeyRetrieval: SqlDialect.GeneratedKeyRetrieval

The method to create the query, how to retrieve the generated key from the database.

orderByIdDialect

val orderByIdDialect: (String, NativeBigInteger?) -> String?

A helper method to ask for order by id on different databases. This is used to create a query that, before any other sorting, fetches a specific entity first.

sequenceDialect

val sequenceDialect: (String, Int) -> String?

A helper method to ask for sequences on different databases. As input is the name of the sequence and as output the query to get the next value from the sequence.

supportsReleaseSavepoint

val supportsReleaseSavepoint: Boolean

Whether this dialect supports the JDBC releaseSavepoint() operation. Oracle and SQL Server do not support it.

supportsWindowFunctions

val supportsWindowFunctions: Boolean

Whether this dialect supports standard SQL window functions (COUNT(*) OVER (), DENSE_RANK(), ROW_NUMBER()). Used to merge count and page into a single roundtrip where possible.

Functions

caseInsensitiveLike

fun caseInsensitiveLike(column: String): Pair<String, String>

Returns the column expression and LIKE operator for case-insensitive matching.

castToDate

fun castToDate(placeholder: String): String

Wraps a bind placeholder with a dialect-specific cast to DATE. Used by raw facets with onl.ycode.stormify.biglist.Facet.Type.DATE where the DB cannot implicitly convert an ISO string bind parameter to a date.

castToTime

fun castToTime(placeholder: String): String

Wraps a bind placeholder with a dialect-specific cast to TIME. Oracle has no native TIME, so it falls back to TO_TIMESTAMP; SQLite and MySQL/MariaDB are passthrough (MySQL prepared-statement CAST(? AS TIME) mis-parses a bound string to 00:00:00 — plain comparison relies on implicit coercion instead); everyone else uses CAST(? AS TIME).

castToTimestamp

fun castToTimestamp(placeholder: String): String

Wraps a bind placeholder with a dialect-specific cast to the datetime type. Used by raw facets with onl.ycode.stormify.biglist.Facet.Type.TIMESTAMP.

likeEscapeClause

fun likeEscapeClause(): String

Returns the ESCAPE clause for LIKE expressions. MySQL/MariaDB treat '\' as an escape within string literals, so they need '\\\\' (double-escaped) while other databases use `'\\'.

queryFormatter

fun queryFormatter(columns: String, distinct: String, tableName: String, constraints: String, sorting: String, lowBound: Int, upperBound: Int): String

Builds a paginated SELECT — required wrapper: Kotlin won't auto-bind the extension receiver when queryFormatterImpl is invoked as a member property.

quoteAlias

fun quoteAlias(alias: String): String

Quotes a column alias if it would be rejected as an unquoted identifier. Oracle requires double-quoting for aliases starting with _ or digits; other databases accept them unquoted.

transformLikeValue

fun transformLikeValue(value: String): String

Transforms the LIKE value for case-insensitive matching. Only lowercases on dialects that use LOWER() wrapping.

valueOf

fun valueOf(value: String): SqlDialect

Returns the enum constant of this type with the specified name. The string must match exactly an identifier used to declare an enum constant in this type. (Extraneous whitespace characters are not permitted.)

values

fun values(): Array<SqlDialect>

Returns an array containing the constants of this enum type, in the order they're declared.