Class Dialect

java.lang.Object
com.codename1.backend.sql.Dialect
public abstract class Dialect extends Object

What the three engines spell differently, in one place.

A Database already hides which engine answered a query: rows come back as the same Java types whichever one produced them. What it could not hide was the STATEMENT. PostgreSQL binds $1 where SQLite and MySQL bind ?, it has no last-insert-id to ask for, and the three name their column types and quote their identifiers differently -- so portable-looking code still carried an "am I on PostgreSQL" branch at every call site. This repository's own database check is the proof: it grew a placeholders(postgres, n) helper and a separate INSERT ... RETURNING arm, and any application would grow the same.

A Dialect is that branch, written once. Statements are written in ONE portable form -- ? for every parameter, plain unquoted names -- and the dialect renders them for whichever engine the connection turns out to be.

Dialect d = db.dialect();
db.execute("INSERT INTO notes (title) VALUES (?)", new Object[] {title});

The rewrite happens inside Database, so the example above needs no dialect at all; the type is public because schema generation -- the ORM's, or an application's own migration code -- has to ask what this engine calls a 64-bit integer.

Nothing here queries the server: a Dialect is chosen from the URL scheme before a connection exists, and it holds no state, so the three instances are shared constants.

  • Field Summary

    Fields
    Modifier and Type
    Field
    Description
    static final int
    BIGINT
    A 64-bit integer.
    static final int
    BLOB
    Arbitrary bytes.
    static final int
    BOOLEAN
    True or false, stored as 0 or 1.
    static final int
    INTEGER
    A 32-bit integer.
    static final Dialect
    MARIADB
    MariaDB, which is the MySQL dialect with one difference: the collation it can name for a text column.
    static final Dialect
    MYSQL
     
    static final Dialect
    POSTGRES
     
    static final int
    REAL
    A double-precision floating point number.
    static final Dialect
    SQLITE
     
    static final int
    TEXT
    The portable column kinds.
    static final int
    TIMESTAMP
    A moment in time, stored as epoch milliseconds.
    static final int
    VERSION_GATED
    countInsertRows: the row count depends on the server version.

  • Method Summary

    Modifier and Type
    Method
    Description
    String
    assignedKeyColumn(int kind)
    The declaration of a primary key column whose value the APPLICATION assigns -- a UUID, a key issued by another service.
    String
    bind(String sql, int paramCount)
    sql written in the portable form, rendered for this engine.
    abstract String
    columnType(int kind)
    What this engine calls one of the portable kinds above.
    String
    comparison(String quotedColumn, boolean text)
    The left side of an ORDERING comparison -- >, >=, <, <= -- on a column of this kind.
    int
    countInsertRows(String sql)
    How many rows an INSERT's VALUES clause writes, or -1 when the shape does not say -- an INSERT ...
    int
    endOfStatement(String sql)
    Where sql stops being the statement and starts being its terminator, its trailing comment or trailing space.
    static Dialect
    forName(String engine)
    The dialect for an engine name, or null when the name is not one of the three.
    abstract String
    generatedKeyColumn(int kind)
     
    String
    generatedKeyColumn(int kind, String quotedColumn)
    The full declaration of a primary key column whose value the DATABASE assigns -- everything after the column name.
    boolean
    generatedKeysThroughReturning()
    Whether a generated key comes back from the INSERT itself rather than from a follow-up question.
    abstract String
    getName()
    "sqlite", "postgresql" or "mysql".
    boolean
    hasTrailingStatement(String sql)
    Whether anything follows the first statement in sql.
    String
    insertDefaults(String quotedTable)
    An INSERT that supplies NO columns, for the entity whose only persisted field is a key the database generates.
    String
    likeOperator()
    The operator a portable like renders to, and the pattern it binds.
    String
    likePattern(String pattern)
    pattern as likeOperator() expects it.
    String
    limit(int count, int offset)
    count rows starting at offset, or an empty string when both are unbounded.
    String
    lockForResync(String table)
    The statement that holds inserts off table while resyncGeneratedKey(String, String) runs, or null where none is needed.
    String
    orderBy(String quotedColumn, boolean ascending)
    One ORDER BY term, with NULLs placed the same way on every engine.
    String
    orderBy(String quotedColumn, boolean ascending, boolean text)
    orderBy(String, boolean) for a column whose kind is known.
    abstract String
    quote(String identifier)
    identifier quoted so that its case is preserved and a reserved word is still usable as a name.
    String
    resyncGeneratedKey(String table, String column)
    A statement that puts a generated key back in step with the rows that are there, or null when the engine needs none.
    String
    toString()
    Returns a string representation of the object.
    boolean
    updatesOnConflict(String sql)
    Whether sql updates an existing row when it conflicts.

    Methods inherited from class Object

    clone, equals, getClass, hashCode, notify, notifyAll, wait, wait, wait

  • Field Details

    • TEXT

      public static final int TEXT

      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.

      See Also:

    • INTEGER

      public static final int INTEGER
      A 32-bit integer.
      See Also:

    • BIGINT

      public static final int BIGINT
      A 64-bit integer.
      See Also:

    • REAL

      public static final int REAL
      A double-precision floating point number.
      See Also:

    • BLOB

      public static final int BLOB
      Arbitrary bytes.
      See Also:

    • BOOLEAN

      public static final int BOOLEAN
      True or false, stored as 0 or 1.
      See Also:

    • TIMESTAMP

      public static final int TIMESTAMP
      A moment in time, stored as epoch milliseconds.
      See Also:

    • SQLITE

      public static final Dialect SQLITE

    • POSTGRES

      public static final Dialect POSTGRES

    • MYSQL

      public static final Dialect MYSQL

    • MARIADB

      public static final Dialect MARIADB

      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.

    • VERSION_GATED

      public static final int VERSION_GATED
      countInsertRows: the row count depends on the server version.
      See Also:

  • Method Details

    • forName

      public static Dialect forName(String engine)
      The dialect for an engine name, or null when the name is not one of the three. Accepts the spellings that appear in a URL scheme, ignoring case -- "postgres" and "postgresql" are the same engine. "mysql" and "mariadb" are the same PROTOCOL but not the same dialect: they have no case-sensitive NO PAD collation in common, so each name answers its own. A connection picks between them by the server's handshake banner instead, which is the better answer when there is a server to ask; this is for callers that have only a name.

    • getName

      public abstract String getName()
      "sqlite", "postgresql" or "mysql".

    • quote

      public abstract String quote(String identifier)

      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.

    • columnType

      public abstract String columnType(int kind)
      What this engine calls one of the portable kinds above.

    • generatedKeyColumn

      public String generatedKeyColumn(int kind, String quotedColumn)

      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.

    • generatedKeyColumn

      public abstract String generatedKeyColumn(int kind)

    • assignedKeyColumn

      public String assignedKeyColumn(int kind)

      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.

    • generatedKeysThroughReturning

      public boolean generatedKeysThroughReturning()

      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(String, Object[], String) is what reads this; it exists so callers do not have to.

    • insertDefaults

      public String insertDefaults(String quotedTable)

      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.

    • limit

      public String limit(int count, int offset)
      count rows starting at offset, or an empty string when both are unbounded. All three accept LIMIT n OFFSET m; MySQL needs a limit before it will accept an offset at all, so an offset-only request is given the largest limit its parser takes.

    • bind

      public String bind(String sql, int paramCount) throws IOException

      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.

      Throws:
      IOException

    • countInsertRows

      public int countInsertRows(String sql) throws IOException

      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(String, Object[], String) 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.

      Throws:
      IOException

    • likeOperator

      public String likeOperator()

      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(String).

    • likePattern

      public String likePattern(String pattern)
      pattern as likeOperator() expects it. Unchanged except on SQLite, where GLOB spells its wildcards differently.

    • orderBy

      public String orderBy(String quotedColumn, boolean ascending)

      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

      public String orderBy(String quotedColumn, boolean ascending, boolean text)

      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.

    • comparison

      public String comparison(String quotedColumn, boolean text)

      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.

    • resyncGeneratedKey

      public String resyncGeneratedKey(String table, String column)

      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.

    • lockForResync

      public String lockForResync(String table)

      The statement that holds inserts off table while resyncGeneratedKey(String, String) 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.

    • hasTrailingStatement

      public boolean hasTrailingStatement(String sql) throws IOException

      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(String, boolean, boolean, boolean, boolean, boolean, boolean, int).

      Throws:
      IOException

    • updatesOnConflict

      public boolean updatesOnConflict(String sql) throws IOException

      Whether sql updates an existing row when it conflicts.

      What Database.insert(String, Object[], String) needs before it trusts a key that came from connection state. See Placeholders.updatesOnConflict(String, boolean, boolean, boolean, boolean, boolean, boolean, int).

      Throws:
      IOException

    • endOfStatement

      public int endOfStatement(String sql) throws IOException

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

      What Database.insert(String, Object[], String) needs to put RETURNING somewhere it will run. See Placeholders.endOfStatement(String, boolean, boolean, boolean, boolean, boolean, boolean, int).

      Throws:
      IOException

    • toString

      public String toString()
      Description copied from class: Object
      Returns a string representation of the object. In general, the toString method returns a string that "textually represents" this object. The result should be a concise but informative representation that is easy for a person to read. It is recommended that all subclasses override this method. The toString method for class Object returns a string consisting of the name of the class of which the object is an instance, the at-sign character `@', and the unsigned hexadecimal representation of the hash code of the object. In other words, this method returns a string equal to the value of: getClass().getName() + '@' + Integer.toHexString(hashCode())
      Overrides:
      toString in class Object