public abstract class Dialect

The portable column kinds. These are the Java types an entity can hold, not SQL types: what each engine calls them is the point of this class.

BOOLEAN and TIMESTAMP are deliberately stored as integers on every engine – 0/1, and epoch milliseconds. A native BOOLEAN column reads back as a Long from all three anyway (PostgreSQL decodes bool to 0/1, MySQL sends TINYINT), but a native timestamp does NOT: it arrives as text whose format follows the server’s DateStyle and time zone, which is a second portability problem underneath the first one. Milliseconds in a BIGINT are the same number everywhere and need no parsing.

MariaDB, which is the MySQL dialect with one difference: the collation it can name for a text column.

Both need a collation that is case sensitive AND NO PAD, so “A” and “a” are two keys and “token " keeps its space. Neither server has the other’s. Measured, creating a key column under each name:

                     mariadb 10.11   mariadb 11.8   mysql 8.0
utf8mb4_0900_bin     unknown         ok             ok
utf8mb4_nopad_bin    ok              ok             unknown
utf8mb4_bin          collides        collides       collides

So there is no single name, and utf8mb4_bin – the one both have – is PAD SPACE on both. Which is chosen comes from the SERVER’s handshake banner rather than from the URL scheme, because a mysql:// URL points at a MariaDB server perfectly often. getName still answers “mysql”: this is the same wire protocol and the same SQL, and everything that branches on the engine name means that family.

identifier quoted so that its case is preserved and a reserved word is still usable as a name.

Quoting is not decoration here, it is what keeps a schema portable: PostgreSQL folds an UNQUOTED name to lower case while SQLite and MySQL preserve it, so a column written createdAt is createdat on one engine and createdAt on the other two, and code that reads rows by name stops finding it on exactly one of the three.

The quote character itself is doubled, which is the escape all three accept. A name carrying a NUL is refused rather than escaped: no engine can hold one, and SQLite reads statements as C strings, so passing it through would truncate the statement instead of failing.

The full declaration of a primary key column whose value the DATABASE assigns – everything after the column name.

This is the least portable line in any schema: SQLite wants INTEGER PRIMARY KEY AUTOINCREMENT and refuses the keyword on any other type, MySQL wants AUTO_INCREMENT on the type it was given, and PostgreSQL has neither and uses an identity column.

The declaration of a primary key column whose value the APPLICATION assigns – a UUID, a key issued by another service.

NOT NULL is not redundant, whatever the standard says. SQLite lets a PRIMARY KEY column that is not INTEGER PRIMARY KEY hold null, and even several nulls, where PostgreSQL and MySQL refuse: an insert that forgot its key succeeded in development and failed in production, and the row it wrote could not be found again by the generated id = ? predicate. The other two already imply the constraint, so saying it costs nothing there.

Whether a generated key comes back from the INSERT itself rather than from a follow-up question.

True for PostgreSQL alone, and it is not a preference: PostgreSQL has no last-insert-id concept, so INSERT … RETURNING is the only way to learn the key at all. Database.insert is what reads this; it exists so callers do not have to.

An INSERT that supplies NO columns, for the entity whose only persisted field is a key the database generates.

Another line the three spell differently, and the naive construction is not merely ugly but invalid: “INSERT INTO t () VALUES ()” is what an empty column list builds, and SQLite and PostgreSQL both refuse it. They want DEFAULT VALUES; MySQL wants the empty lists and has no DEFAULT VALUES form at all.

sql written in the portable form, rendered for this engine.

The portable form is the one SQLite and MySQL already use: ? for each parameter, in order. PostgreSQL is the engine that differs, and this is where that difference stops – it becomes $1, $2 and so on here rather than at every call site. A literal question mark that is NOT a parameter is written ?? – which matters only on PostgreSQL, whose jsonb operators are spelled ?, ?| and ?& – and collapses to a single ? on every engine, so the escape means the same thing everywhere.

A statement with no ? in it is passed through untouched and unchecked. That is what keeps engine-native SQL working: code that already writes $1 for PostgreSQL, or that calls a function whose name contains no placeholder at all, is not this method’s business. Once a statement DOES carry a placeholder it is in the portable form, and then the count has to match paramCount – a mismatch is refused here, naming both numbers, rather than reaching an engine that answers for it in three different ways (SQLite binds the missing ones to NULL and commits the row).

Placeholders are recognised only where a parameter can appear. String literals, quoted identifiers, dollar-quoted bodies and comments are scanned through, so a ? inside any of them stays what it was.

How many rows an INSERT’s VALUES clause writes, or -1 when the shape does not say – an INSERT … SELECT, or a statement with no VALUES at all.

Database.insert answers with one key, and a multi-row insert has no single key that means the same thing on three engines. Counting the tuples is what lets it refuse BEFORE the rows are written rather than after.

VERSION_GATED is the third answer: a MySQL statement whose tuples sit behind a version-gated executable comment inserts a number of rows that depends on the server, and this has no connection to ask.

The operator a portable like renders to, and the pattern it binds.

SQLite’s LIKE folds ASCII case and PostgreSQL’s does not, so the same query answered different rows depending on the engine. The obvious fix – PRAGMA case_sensitive_like – is connection wide, and that reaches further than the ORM: measured, a table declared CHECK(v LIKE ‘A%’) accepts ‘abc’ under the default and REFUSES it once the pragma is on, so merely opening an existing database would change what writes it accepts, and an expression index built on LIKE would no longer agree with its own rows.

So SQLite renders GLOB instead, which is case sensitive, always present, and scoped to the one comparison being made. The pattern is translated to match: see likePattern.

One ORDER BY term, with NULLs placed the same way on every engine.

The engines disagree by default, measured over one null and two values:

                ASC       DESC
sqlite          null,a,b  b,a,null
postgresql      a,b,null  null,b,a
mysql           null,a,b  b,a,null

So orderBy("name", true).first() answered the null row on two engines and a real one on the third. NULL sorts LOWEST here – first ascending, last descending – which is what SQLite and MySQL already do, and PostgreSQL is brought to match rather than the other way round because two of three and the usual reading of NULL agree on it.

MySQL cannot say NULLS FIRST at all: it answers a syntax error, so the order is expressed there as a leading (col IS NULL) term, which is the documented way to get the same effect.

orderBy(String, boolean) for a column whose kind is known.

A TEXT column needs its collation pinned as well as its nulls placed. Measured over “Z” and “a”: SQLite orders them Z then a, because its default collation is byte order, while a PostgreSQL database initialised with a locale-aware collation orders a then Z – so orderBy("name", true).first() answered a different entity depending on the database. Byte order is the one all three can agree on: SQLite is already there, MySQL’s text columns carry a binary collation for the case-sensitivity reason, and PostgreSQL says it as COLLATE “C”.

The kind has to be passed because COLLATE is only valid on text. An integer column with one is a type error, not a no-op.

The left side of an ORDERING comparison – >, >=, <, <= – on a column of this kind.

The same reason orderBy(String, boolean, boolean) exists, for the same columns, and leaving the two inconsistent is worse than leaving both alone: ordering was pinned to byte order while the comparisons that decide which rows come back were not, so a query could exclude a row that its own ORDER BY would have placed first.

MEASURED on a database created the way the standard image creates one, with lc_collate en_US.utf8, against rows holding ‘Z’ and ‘a’:

WHERE v > 'Z'    SQLite: a    MySQL: a    PostgreSQL: (none)

SQLite compares BINARY and the generated MySQL column carries a binary collation, so both answer in byte order; PostgreSQL follows the database’s locale, where ‘a’ sorts before ‘Z’. One entity, one query, two answers.

EQUALITY is deliberately NOT routed through here. PostgreSQL requires a deterministic collation by default, and under one, equality is byte equality whatever the sort order is – so eq, ne and in already agree across the three, and collating them would be noise in every statement that uses them.

A statement that puts a generated key back in step with the rows that are there, or null when the engine needs none.

SUPPLYING YOUR OWN KEY IS A DOCUMENTED CAPABILITY – it is why PostgreSQL’s column is GENERATED BY DEFAULT rather than ALWAYS, so a data import or a test fixture can insert the value it already has. On SQLite and MySQL doing that also moves the counter, so the next generated insert carries on above it. On PostgreSQL it does NOT: the identity’s sequence is untouched by an explicit value, and the next generated insert reuses a key that is already there.

MEASURED on a fresh table, explicit id 1 then a generated insert:

SQLite: 2   MySQL: 2   PostgreSQL: duplicate key value violates unique
                                   constraint "idsync_pkey"

So the capability is portable only if the caller can put the sequence back, and this is what lets the ORM offer that in one call rather than leaving every importer to write engine-specific SQL it has no reason to know about.

The statement that holds inserts off table while resyncGeneratedKey runs, or null where none is needed.

Null here, and that is not an oversight for the two engines that answer it. Neither SQLite nor MySQL needs a resync at all – both advance their counter past an explicitly assigned key on their own – so resyncGeneratedKey is null for them and this is never reached.

Must run in the SAME TRANSACTION as the resync, which is the only thing that makes it a lock rather than a gesture: a lock taken by a statement of its own is released the moment that statement ends.

Whether anything follows the first statement in sql.

What Database refuses before dispatching, because SQLite would run the first statement and drop the rest in silence. See Placeholders.hasTrailingStatement.

Cuts a script into its statements, by this engine’s lexical rules.

For migration scripts: everything else hands Database one statement at a time. Semicolons inside literals, comments, dollar-quoted bodies and trigger bodies do not split, and a piece that holds only comments is dropped. MySQL’s DELIMITER is a directive of its command-line client, not SQL, and is not understood here.

Whether sql updates an existing row when it conflicts.

What Database.insert needs before it trusts a key that came from connection state. See Placeholders.updatesOnConflict.

Where sql stops being the statement and starts being its terminator, its trailing comment or trailing space.

What Database.insert needs to put RETURNING somewhere it will run. See Placeholders.endOfStatement.