Supporting many database engines is easy to claim and hard to keep honest. For years the differences lived in one enumeration of about 3,700 lines, where every new engine meant another branch in every method. This release replaces it with a dialect per engine, each around 200 lines, behind one interface of 52 methods in nine groups.
The dialects
- Thirteen engines Informix, Oracle, PostgreSQL, MySQL and MariaDB, SQL Server, DB2, SAP HANA, Derby, H2, SQLite, Vertica, ClickHouse and Hive, each in its own class, looked up by name or by JDBC URL.
- One DML dialect of ten methods replaces twenty-six files: pagination (SKIP and FIRST before the columns on Informix, OFFSET and FETCH at the end on Oracle, LIMIT and OFFSET on PostgreSQL and MySQL), dummy tables, boolean wrapping, INSERT RETURNING on the four engines that have it, and the two engines with restrictions, Hive without OFFSET and ClickHouse without nested joins.
What the grammar gained
- MERGE Native on Oracle, SQL Server, Informix, Vertica, HANA, DB2 and H2; emulated through INSERT ON CONFLICT on PostgreSQL and ON DUPLICATE KEY on MySQL. A conditional WHEN MATCHED is emulated with CASE where the engine lacks it, SQL Server's WHEN NOT MATCHED BY SOURCE is supported, and a subquery may be the USING source. Tests pass on six engines.
- DROP IF EXISTS for sequences, tables and indexes: native where supported, a procedural block on Oracle and DB2, and exception handling in Java only for HANA and Derby. Temporary-table prefixes follow each engine.
- Trigonometry Seven functions across twelve engines, with SQL Server's ATN2 mapped.
The gain is not the thirteenth engine. It is that the fourteenth costs 200 lines and a test run, and that every engine's quirks are now listed in one place a reviewer can read.