# MariaDbLenientDialect.java

    /*
    ** Module   : MariaDbLenientDialect.java
    ** Abstract : Concrete implementation of Dialect for a lenient MariaDb backend.
    **
    ** Copyright (c) 2022-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- ---------------------------------------Description---------------------------------------
    ** 001 OM  20221018 Created initial version with implementation extracted from MariaDb. This latter class
    **                  becomes a subclass and will implement the methods to adhere to MariaDb strict(er) mode.
    **     OM  20221025 Improve sequence compatibility with 4GL.
    **     OM  20221026 Excluded table name from index name by default. It will be added as needed by dialects.
    **     RAA 20230109 Overrided method getSequenceSetValString().
    **     ECF 20230122 Changed range parameter inlining default to false.
    **     BS  20230126 Added support for SAVE CACHE.
    **     ECF 20230127 Data type adjustment for SAVE CACHE.
    **     OM  20230131 Code cleanup.
    ** 002 RAA 20230208 Added parameter for getCreateTemporaryTablePostfix.
    ** 003 BS  20230331 Added support for sequences.
    ** 004 DDF 20230306 Added a parameter to getSqlMappedType() (refs: #7108).
    ** 005 DDF 20230406 Removed isAutoRtrimAndCi. Added isAutoRtrim and isAutoCi methods.
    ** 006 GBB 20230512 Logging methods replaced by CentralLogger/ConversionStatus.
    ** 007 OM  20230614 Added 'lead' as reserved keyword (abbreviation of leading?). Fixed scale value for non-
    **                  character column data types.
    ** 008 OM  20230615 Converting the MAX-WIDTH from size of UTF-16 encoding to number of characters.
    ** 009 OM  20230908 Added checkIndexConstraints() method.
    ** 010 IAS 20230910 Fixed flags for word tables' generation.
    ** 011 DDF 20231127 Added isUniqueConstraintViolation() method.
    ** 012 OM  20231128 Enlarged BLOB capacity to 4GiB by using LONGBLOB instead.
    ** 013 OM  20231218 Added implementation for DBCODEPAGE and DBCOLLATION 4GL functions.
    ** 014 IAS 20230208 Added error handler support.
    ** 015 OM  20240131 Fixed flaw in DBCODEPAGE / DBCOLLATION implementation.
    ** 016 TJD 20240220 Java 17 javadoc fix.
    ** 017 OM  20240718 Fixed the list of columns in foreign key when creating FK constraints.
    ** 018 OM  20250117 Added support for multiple dialects for multi-tenant landlord database.
    ** 019 OM  20250331 Added c3p0 connection parameters.
    */

    /*
    ** This program is free software: you can redistribute it and/or modify
    ** it under the terms of the GNU Affero General Public License as
    ** published by the Free Software Foundation, either version 3 of the
    ** License, or (at your option) any later version.
    **
    ** This program is distributed in the hope that it will be useful,
    ** but WITHOUT ANY WARRANTY; without even the implied warranty of
    ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    ** GNU Affero General Public License for more details.
    **
    ** You may find a copy of the GNU Affero GPL version 3 at the following
    ** location: https://www.gnu.org/licenses/agpl-3.0.en.html
    **
    ** Additional terms under GNU Affero GPL version 3 section 7:
    **
    **   Under Section 7 of the GNU Affero GPL version 3, the following additional
    **   terms apply to the works covered under the License.  These additional terms
    **   are non-permissive additional terms allowed under Section 7 of the GNU
    **   Affero GPL version 3 and may not be removed by you.
    **
    **   0. Attribution Requirement.
    **
    **     You must preserve all legal notices or author attributions in the covered
    **     work or Appropriate Legal Notices displayed by works containing the covered
    **     work.  You may not remove from the covered work any author or developer
    **     credit already included within the covered work.
    **
    **   1. No License To Use Trademarks.
    **
    **     This license does not grant any license or rights to use the trademarks
    **     Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
    **     of Golden Code Development Corporation. You are not authorized to use the
    **     name Golden Code, FWD, or the names of any author or contributor, for
    **     publicity purposes without written authorization.
    **
    **   2. No Misrepresentation of Affiliation.
    **
    **     You may not represent yourself as Golden Code Development Corporation or FWD.
    **
    **     You may not represent yourself for publicity purposes as associated with
    **     Golden Code Development Corporation, FWD, or any author or contributor to
    **     the covered work, without written authorization.
    **
    **   3. No Misrepresentation of Source or Origin.
    **
    **     You may not represent the covered work as solely your work.  All modified
    **     versions of the covered work must be marked in a reasonable way to make it
    **     clear that the modified work is not originating from Golden Code Development
    **     Corporation or FWD.  All modified versions must contain the notices of
    **     attribution required in this license.
    */

    package com.goldencode.p2j.persist.dialect;

    import com.goldencode.p2j.convert.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.deploy.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.sequence.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.ErrorManager.*;
    import com.mchange.v2.c3p0.*;
    import java.io.*;
    import java.sql.*;
    import java.sql.Date;
    import java.text.*;
    import java.util.*;
    import java.util.logging.*;
    import java.util.regex.*;
    import com.goldencode.p2j.util.logging.*;
    import org.apache.commons.lang.*;

    /**
     * Extends abstract {@link Dialect} for an MariaDb/MySql backend. This is a lenient implementation and not all
     * 4GL features are supported. The customer which uses this is responsible for any unexpected results of
     * processing his data in lenient (non-strict) mode.
     */
    public class MariaDbLenientDialect
    extends Dialect
    {
       /** The id of this dialect variant as a string. */
       public static final String DIALECT_ID = "mariadblenient";

       /** The maximum size of a character / VARCHAR field in MariaDb. */
       protected static final int MAX_VARCHAR_SIZE = 100;

       /** The limit at which VARCHAR columns are converted to TEXT columns in MariaDb. */
       protected static final int MIN_TEXT_SIZE = 512;

       /** The maximum key size in a MariaDb index. */
       private static final int INDEX_MAX_KEY_SIZE = 3072;

       /**
        * The number of bytes per character in the used text encoding (assuming utf8mb4 / utf32) used when
        * computing the space requirements.
        */
       private static final int BPC = 4;

       // #1074 - Column length too big for column 'X' (max = 16383); use BLOB or TEXT instead
       // #1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535.
       //         Otherwise: you have to change some columns to TEXT or BLOBs
       // #1071 - Specified key was too long; max key length is 3072 bytes

       /** Regex Pattern for parsing message cased by error raised by UDF. */
       protected static final Pattern UDF_ERROR_MESSAGE = Pattern.compile(
                "^(\\(conn=[0-9]+\\) )(\\*\\* )?(.*)(\\.)? *\\((-?[0-9]*)\\).*$");

       /** Lowest date supported by MariaDb (?) */
       protected static final Date lowDate;

       /** Highest date supported by MariaDb (?) */
       protected static final Date highDate;

       /** Mapping between the FWD possible field/column types and their SQL counterparts. */
       protected static final Map<String, String> fwd2sql = new HashMap<>();

       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(MariaDbLenientDialect.class);

       /** Flag indicating whether range parameters should be inlined */
       private static boolean inlineRangeParms = false; // just to allow dynamic value changes in debug

       /*
        * Initialization of static data.
        *
        * @see  https://mariadb.com/kb/en/data-types/
        */
       static
       {
          fwd2sql.put("blob",       "longblob"); // 4GiB, alternatives: MEDIUMBLOB (16MiB) and BLOB (64 KiB)
          fwd2sql.put("character",  "varchar(" + SCALE_TOK + ")");
          fwd2sql.put("clob",       "mediumtext"); // 16MiB, alternatives: TEXT (64 KiB) and LONGTEXT (4GiB)
          fwd2sql.put("comhandle",  "text");
          fwd2sql.put("date",       "date");
          fwd2sql.put("datetime",   "datetime(3)");
          fwd2sql.put("datetimetz", "timestamp(3)"); // lacks "with time zone". All values adjusted automatically to session's time zone
          fwd2sql.put("decimal",    "decimal(50," + SCALE_TOK + ")");
          fwd2sql.put("handle",     "bigint");
          fwd2sql.put("integer",    "integer");
          fwd2sql.put("Integer",    "integer");
          fwd2sql.put("int64",      "bigint");
          fwd2sql.put("logical",    "boolean");
          fwd2sql.put("Long",       "bigint");
          fwd2sql.put("object",     "bigint");
          fwd2sql.put("String",     "varchar(" + SCALE_TOK + ")");
          fwd2sql.put("recid",      "bigint"); // not integer ?
          fwd2sql.put("raw",        "varbinary(" + SCALE_TOK + ")");
          fwd2sql.put("rowid",      "bigint"); // UUID desired

          // prepare supported date values.  We use the GMT timezone to define our date range, since
          // this is how com.goldencode.p2j.util.date handles its dates.

          TimeZone tz = TimeZone.getTimeZone("GMT");
          GregorianCalendar cal = new GregorianCalendar(tz);

          cal.set(Calendar.ERA, GregorianCalendar.AD);
          cal.set(Calendar.YEAR, 1000);
          cal.set(Calendar.MONTH, Calendar.JANUARY);
          cal.set(Calendar.DATE, 1);
          lowDate = new Date(cal.getTime().getTime()); // 1000-01-01 AD

          cal.clear();
          cal.set(Calendar.ERA, GregorianCalendar.AD);
          cal.set(Calendar.YEAR, 9999);
          cal.set(Calendar.MONTH, Calendar.DECEMBER);
          cal.set(Calendar.DATE, 31);
          highDate = new Date(cal.getTime().getTime()); // 9999-12-31 AD
       }

       /**
        * Get a collection of reserved keywords specific to this dialect.
        * <p>
        * The {@link com.goldencode.p2j.convert.NameConverter} uses thes list to create an exclusion
        * list of names so there will not be schema collisions with identifiers (tables, fields) from
        * the generated schema.
        *
        * @return  A list with MariaDb reserved keywords.
        *
        * @see     <a href="https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.110%29.aspx">
        *          https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.110%29.aspx</a>
        */
       public static List<String> getReservedKeywords()
       {
          // SQL Server keywords, some may overlap ANSI
          String[] keywords = {
             "accessible",
             "add",
             "all",
             "alter",
             "analyze",
             "and",
             "as",
             "asc",
             "asensitive",
             "before",
             "between",
             "bigint",
             "binary",
             "blob",
             "both",
             "by",
             "call",
             "cascade",
             "case",
             "change",
             "char",
             "character",
             "check",
             "collate",
             "column",
             "condition",
             "constraint",
             "continue",
             "convert",
             "create",
             "cross",
             "current_date",
             "current_role",
             "current_time",
             "current_timestamp",
             "current_user",
             "cursor",
             "database",
             "databases",
             "day_hour",
             "day_microsecond",
             "day_minute",
             "day_second",
             "dec",
             "decimal",
             "declare",
             "default",
             "delayed",
             "delete",
             "delete_domain_id",
             "desc",
             "describe",
             "deterministic",
             "distinct",
             "distinctrow",
             "div",
             "do_domain_ids",
             "double",
             "drop",
             "dual",
             "each",
             "else",
             "elseif",
             "enclosed",
             "escaped",
             "except",
             "exists",
             "exit",
             "explain",
             "false",
             "fetch",
             "float",
             "float4",
             "float8",
             "for",
             "force",
             "foreign",
             "from",
             "fulltext",
             "general",
             "grant",
             "group",
             "having",
             "high_priority",
             "hour_microsecond",
             "hour_minute",
             "hour_second",
             "if",
             "ignore",
             "ignore_domain_ids",
             "ignore_server_ids",
             "in",
             "index",
             "infile",
             "inner",
             "inout",
             "insensitive",
             "insert",
             "int",
             "int1",
             "int2",
             "int3",
             "int4",
             "int8",
             "integer",
             "intersect",
             "interval",
             "into",
             "is",
             "iterate",
             "join",
             "key",
             "keys",
             "kill",
             "leading",
             "lead",
             "leave",
             "left",
             "like",
             "limit",
             "linear",
             "lines",
             "load",
             "localtime",
             "localtimestamp",
             "lock",
             "long",
             "longblob",
             "longtext",
             "loop",
             "low_priority",
             "master_heartbeat_period",
             "master_ssl_verify_server_cert",
             "match",
             "maxvalue",
             "mediumblob",
             "mediumint",
             "mediumtext",
             "middleint",
             "minute_microsecond",
             "minute_second",
             "mod",
             "modifies",
             "natural",
             "not",
             "no_write_to_binlog",
             "null",
             "numeric",
             "offset",
             "on",
             "optimize",
             "option",
             "optionally",
             "or",
             "order",
             "out",
             "outer",
             "outfile",
             "over",
             "page_checksum",
             "parse_vcol_expr",
             "partition",
             "position",
             "precision",
             "primary",
             "procedure",
             "purge",
             "range",
             "read",
             "reads",
             "read_write",
             "real",
             "recursive",
             "ref_system_id",
             "references",
             "regexp",
             "release",
             "rename",
             "repeat",
             "replace",
             "require",
             "resignal",
             "restrict",
             "return",
             "returning",
             "revoke",
             "right",
             "rlike",
             "rows",
             "schema",
             "schemas",
             "second_microsecond",
             "select",
             "sensitive",
             "separator",
             "set",
             "show",
             "signal",
             "slow",
             "smallint",
             "spatial",
             "specific",
             "sql",
             "sqlexception",
             "sqlstate",
             "sqlwarning",
             "sql_big_result",
             "sql_calc_found_rows",
             "sql_small_result",
             "ssl",
             "starting",
             "stats_auto_recalc",
             "stats_persistent",
             "stats_sample_pages",
             "straight_join",
             "table",
             "terminated",
             "then",
             "tinyblob",
             "tinyint",
             "tinytext",
             "to",
             "trailing",
             "trigger",
             "true",
             "undo",
             "union",
             "unique",
             "unlock",
             "unsigned",
             "update",
             "usage",
             "use",
             "using",
             "utc_date",
             "utc_time",
             "utc_timestamp",
             "values",
             "varbinary",
             "varchar",
             "varcharacter",
             "varying",
             "when",
             "where",
             "while",
             "window",
             "with",
             "write",
             "xor",
             "year_month",
             "zerofill",
          };

          return Arrays.asList(keywords);
       }

       /**
        * Get dialect id.
        *
        * @return  the id of the dialect.
        */
       @Override
       public String id()
       {
          return MariaDbLenientDialect.DIALECT_ID;
       }

       /**
        * Get the name of the JDBC database type (as in JBC URL).
        *
        * @return  The name of the JDBC database type.
        */
       @Override
       public String getJdbcDatabaseType()
       {
          return "mariadb";
       }

       /**
        * Get the name of the JDBC driver class associated with this dialect.
        *
        * @return   The fully qualified driver class name.
        *
        * @see <a href="https://mariadb.org/connector-java/all-releases/">Download MariaDB Connector/J</a>
        */
       @Override
       public String getDriverClassName()
       {
          return "org.mariadb.jdbc.Driver";
       }

       /**
        * Get c3p0 ConnectionCustomizer class for the dialect.
        * @param cfg
        *        Database configuration.
        * @return c3p0 ConnectionCustomizer class for the dialect.
        */
       @Override
       public Class<? extends AbstractConnectionCustomizer> connectionCustomizer(DatabaseConfig cfg)
       {
          return FWDMariaDBConnectionCustomizer.class;
       }

       /**
        * Activate error handler at the database side.
        * @param  conn
        *         Database connection
        * @throws SQLException
        *         on error.
        */
       @Override
       public void activateErrorHandler(Connection conn)
       throws SQLException
       {
          conn.prepareCall("{ call activateErrorHandler()}").executeUpdate();
       }

       /**
        * Reset error handler at the database side.
        * @param  conn
        *         Database connection
        * @throws SQLException
        *         on error.
        */
       @Override
       public void resetErrorHandler(Connection conn)
       throws SQLException
       {
          conn.prepareCall("{ call resetErrorHandler()}").executeUpdate();
       }

       /**
        * Get a new instance of a splitter for DDL scripts. Reuses the splitter for PG dialect.
        *
        * @return  A new instance of splitter for DDL scripts.
        */
       @Override
       public ScriptSplitter scriptSplitter()
       {
          return new P2JPostgreSQLDialect.PgScriptSplitter()
          {
             @Override
             public State state(String line)
             {
                if (line != null)
                {
                   if (line.startsWith("DELIMITER ") || line.startsWith("$$") || line.startsWith(";"))
                   {
                      return State.IGNORE;
                   }

                   if (line.startsWith("##"))
                   {
                      if (currentState == State.DDL)
                      {
                         return currentState;
                      }
                      return State.IGNORE;
                   }
                }
                return super.state(line);
             }
          };
       }

       /**
        * Get a ScriptRunner instance for the dialect.
        *
        * @return a ScriptRunner instance for the dialect.
        *
        */
       public ScriptRunner getScriptRunner()
       {
          return new MariaDbScriptRunner(this);
       }

       /**
        * Create a {@link Blob} instance from the specified bytes.
        *
        * @param   conn
        *          The JDBC connection.
        * @param   bytes
        *          The blob data.
        *
        * @return  The blob instance.
        */
       @Override
       public Blob blobCreator(Connection conn, byte[] bytes)
       {
          try
          {
             Blob blob = conn.createBlob();
             blob.setBytes(1, bytes);

             return blob;
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       /**
        * Create a {@link Clob} instance from the specified value.
        *
        * @param   conn
        *          The JDBC connection.
        * @param   value
        *          The clob data.
        *
        * @return  The clob instance.
        */
       @Override
       public Clob clobCreator(Connection conn, String value)
       {
          try
          {
             Clob clob = conn.createClob();
             clob.setString(1, value);

             return clob;
          }
          catch (SQLException e)
          {
             throw new RuntimeException(e);
          }
       }

       private static boolean udf4contains = true; // just to allow dynamic value changes in debug
       /**
        * Check if UDF should be used for converted CONTAINS operator.
        *
        * @return  {@code true} if UDF should be used for converted CONTAINS operator.
        */
       @Override
       public boolean useUdf4Contains()
       {
          UnimplementedFeature.missing("MariaDbDialect.useUdf4Contains()");
          return udf4contains;
       }

       /**
        * Check if word tables should be used for converted CONTAINS operator.
        *
        * @return  {@code true} if tables should be used for converted CONTAINS operator.
        */
       public boolean useWordTables()
       {
          return false;
       }

       /**
        * Check if native UDFs are supported for the dialect.
        *
        * @return  {@code true} since we want tho TEST this NEW feature.
        */
       @Override
       public boolean isNativeUDFsSupported()
       {
          return true;
       }

       /**
        * Get name of the checkError UDF.
        * @return the name of the checkError UDF.
        */
       @Override
       public String checkErrorFn()
       {
          return "checkError_BB";
       }

       /**
        * Get name of the initError UDF.
        * @return the name of the initError UDF.
        */
       @Override
       public String initErrorFn()
       {
          return "initError_B";
       }

       /**
        * Check if the exception was caused by an error in UDF.
        *
        * @param   e
        *          The exception in question.
        *
        * @return  {@code true} if the exception was caused by error in UDF.
        */
       public boolean isUdfOriginatedError(SQLException e)
       {
          return "FWD00".equals(e.getSQLState());
       }

       /**
        * Get regex Pattern for parsing message cased by error raised by UDF.
        *
        * @return  regex Pattern for parsing message cased by error raised by UDF.
        */
       public Pattern getUdfErrorMessagePattern()
       {
          return UDF_ERROR_MESSAGE;
       }

       /**
        * Check if the exception was caused by a warning in UDF.
        *
        * @param   e
        *          The exception in question.
        *
        * @return  {@code true} if the exception was caused by error in UDF.
        */
       public boolean isUdfOriginatedWarning(SQLException e)
       {
          return "01FWD".equals(e.getSQLState());
       }

       /**
        * Create the error descriptor from the {@code SQLException} message
        *
        * @param   message
        *          {@code SQLException} message
        *
        * @return  Error descriptor.
        */
       @Override
       public ErrorEntry errorEntry(String message)
       {
          Matcher matcher = UDF_ERROR_MESSAGE.matcher(message);
          if (matcher.matches() && matcher.groupCount() == 5)
          {
             try
             {
                return new ErrorEntry(Integer.parseInt(matcher.group(5)),
                         matcher.group(3), !StringUtils.isBlank(matcher.group(2)));
             }
             catch (NumberFormatException e)
             {
                // no-op
             }
          }

          return new ErrorEntry(-1, message, false);
       }

       /**
        * Worker method for {@code generateTriggerDDLs()}. It generates the DDLs needed to create all
        * triggers in a database with a specific schema and using a certain dialect.
        *
        * @param   dbName
        *          The target schema.
        * @param   out
        *          The {@code PrintStream} used for output.
        * @param   eoln
        *          OS-specific end of line terminator.
        * @param   wordTables
        *          The collection of word tables, ordered by name.
        */
       @Override
       public void generateWordTablesDDLImpl(String dbName,
                                             PrintStream out,
                                             String eoln,
                                             Collection<WordTable> wordTables)
       {
          UnimplementedFeature.missing("MariaDbDialect.generateWordTablesDDLImpl()");

          out.print("-- MISSING: MariaDbDialect.generateWordTablesDDLImpl()");
          out.print(eoln);
       }

       /**
        * Format a date object as a string which is appropriate for use in an SQL statement. The preferred format
        * is {@code YYYY-MM-DD}.
        *
        * @param   d
        *          Date to be formatted as a string.
        *
        * @return  Formatted representation of the given date.
        *
        * @see <a href="https://mariadb.com/kb/en/date/">DATE Syntax</a>
        */
       @Override
       public String formatDate(date d)
       {
          NumberFormat nf = NumberFormat.getInstance();
          nf.setMinimumIntegerDigits(4);
          nf.setGroupingUsed(false);

          StringBuilder buf = new StringBuilder();
          int year = d.getYear();
          buf.append(nf.format(Math.abs(year))).append('-');
          nf.setMinimumIntegerDigits(2);
          buf.append(nf.format(d.getMonth())).append('-').append(nf.format(d.getDay()));

          if (year < 0)
          {
             buf.append(" BC");
          }

          return buf.toString();
       }

       /**
        * Given a column expression as queried from database metadata, extract the root name of a column.
        * <p>
        * Since the {@code VARCHAR(n)} datatype does not require the column to be processed in any way before
        * being added to an index, this method returns the parameter unchanged.
        *
        * @param   expr
        *          Expression containing column name as natively represented by the database dialect.
        *
        * @return  Basic column name.
        */
       @Override
       public String extractColumnName(String expr)
       {
          return expr;
       }

       /**
        * Adjust a character column name appropriate to this dialect, such that it can be inserted into an index.
        * This may involve wrapping it within one or more functions, prepending a special prefix to designate a
        * computed column, or any other transformation appropriate to the database syntax.
        *
        * TODO: the case-sensitive fields might present the "binary" prefix.
        *
        * @param   name
        *          Base column name.
        * @param   ignoreCase
        *          {@code true} to cause case to be ignored, else {@code false}.
        *
        * @return  Processed column name.
        */
       @Override
       public String getProcessedCharacterColumnName(String name, boolean ignoreCase)
       {
    //    If we observe the indexes on VARCHAR columns will not work as expected we will inject computed columns
    //    for the fields needed to be indexed. For the moment we hope the automatic TRIM-ing will work and get
    //    this feature for free.
          return name;

    //      StringBuilder buf = new StringBuilder();
    //      if (ignoreCase)
    //      {
    //         buf.append("upper(");
    //      }
    //      buf.append("rtrim(").append(name).append(")");
    //      if (ignoreCase)
    //      {
    //         buf.append(")");
    //      }
    //
    //      return buf.toString();
       }

       /**
        * Report whether the given column name represents an alias or an expression which indicates the column is
        * case-insensitive. Assumes the column represented by <code>name</code> is already determined to be the
        * appropriate data type for textual data.
        *
        * @param   name
        *          Column name, alias, or expression.
        *
        * @return  {@code true} if {@code name} represents case-insensitive data.
        */
       @Override
       public boolean isCaseInsensitiveColumn(String name)
       {
          return true;
    //      return !name.startsWith("binary ");
       }

       /**
        * Indicate whether this dialect requires computed columns to participate
        * within index definitions, instead of embedded expressions.
        *
        * @return  {@code true} to indicate computed columns are required;
        *          {@code false} if the backing database can embed an expression within an index definition.
        */
       @Override
       public boolean needsComputedColumns()
       {
          return true;
       }

       /**
        * Returns {@code true} if the dialect handles automatically rtrimming for
        * character columns.
        *
        * @return  as described above.
        */
       public boolean isAutoRtrim()
       {
          return true;
       }

       /**
        * Returns {@code true} if the dialect handles case-insensitive compare
        * operations for case-insensitive character columns
        *
        * @return  as described above.
        */
       public boolean isAutoCi()
       {
          return true;
       }

       /**
        * Get the prefix, if any, which should be prepended to computed column and/or property names. In the case
        * of this dialect the SQL server handles automatically the case-insensitive strings the same way as 4GL.
        * The computed column is a NO-OP.
        *
        * @param   caseSensitive
        *          {@code true} if this is a character field was declared with {@code CASE-SENSITIVE} attribute.
        *          In each case the column name will be name-decorated differently.
        *
        * @return  Computed column/property prefix, or {@code null} if the backing database does not use computed
        *          columns.
        *
        * @see     #needsComputedColumns()
        */
       @Override
       public String getComputedColumnPrefix(boolean caseSensitive)
       {
          return "";
       }

       /**
        * Returns the formula for character computed columns. In the case of this dialect the SQL server handles
        * automatically the case-insensitive strings the same way as 4GL. The computed column is a NO-OP.
        * The case-sensitive cases must be handled, later, on a fully qualified column, by adding {@code binary}
        * token.
        *
        * @param   colName
        *          The base column name.
        * @param   ignoreCase
        *          {@code true} if this is a char field which has the "ignore case" flag set.
        *
        * @return  The column name.
        */
       @Override
       public String getComputedColumnFormula(String colName, boolean ignoreCase)
       {
          return colName;
       }

       /**
        * Report whether the given column name represents an alias or an expression which indicates the column is
        * computed. Assume the column represented by {@code name} is already determined to be the appropriate data
        * type for textual data.
        *
        * @param   name
        *          Column name, alias, or expression.
        *
        * @return  always {@code false}.
        */
       @Override
       public boolean isComputedColumn(String name)
       {
          // MariaDb cannot properly distinct whether a column is computed or not
          return false;
       }

       /**
        * Indicate whether this dialect natively supports the overloading of user defined functions by parameter
        * number and data type. If not, the overloaded UDFs must be decorated in some way.
        *
        * @return  {@code false} since the backing database does not support overloading natively.
        */
       @Override
       public boolean supportsFunctionOverloading()
       {
          return false;
       }

       /**
        * Indicates whether this dialect supports boolean datatype. Support includes:
        * <ul>
        *    <li>usage of {@code true} and {@code false} literals;
        *    <li>operations with boolean values;
        *    <li>allowing functions to have booleans as parameters and return datatype.
        * </ul>
        *
        * @return  {@code true} if the above-mentioned items are supported or {@code false} if they are handled by
        *          a replacement datatype (integer).
        *
        * @see <a href="https://mariadb.com/kb/en/boolean/">BOOLEAN Syntax</a>
        */
       @Override
       public boolean supportsBooleanDatatype()
       {
          return true;
       }

       /**
        * Generate a DDL string which will create a global sequence with the given starting value.
        *
        * @param   name
        *          Sequence name.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        *
        * @see <a href="https://mariadb.com/kb/en/create-sequence/">CREATE SEQUENCE Syntax</a>
        */
       @Override
       public String getCreateSequenceString(String name)
       {
          return "create sequence " + name;
       }

       /**
        * Generate a DDL string which will create a global sequence with the given starting value.
        *
        * @param   name
        *          Sequence name.
        * @param   start
        *          Sequence starting value.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        *
        * @see <a href="https://mariadb.com/kb/en/create-sequence/">CREATE SEQUENCE Syntax</a>
        */
       @Override
       public String getCreateSequenceString(String name, long start)
       {
          return "create sequence " + name + " start with " + start;
       }

       /**
        * Generate a DDL string which will create a sequence with given properties.
        *
        * @param   name
        *          Sequence name. Mandatory not null.
        * @param   init
        *          Sequence starting value. Mandatory(not null).
        * @param   increment
        *          Sequence incrementing value. Mandatory(not null).
        * @param   minVal
        *          Sequence minimum value. Optional (may be null).
        * @param   maxVal
        *          Sequence maximum value. Optional (may be null).
        * @param   cycle
        *          Sequence cycling property. Optional, if missing assumed not cycling.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        *
        * @see <a href="https://mariadb.com/kb/en/create-sequence/">CREATE SEQUENCE Syntax</a>
        */
       @Override
       public String getCreateSequenceString(String name,
                                             Long init,
                                             Long increment,
                                             Long minVal,
                                             Long maxVal,
                                             Boolean cycle)
       {
          // compute the default values
          if (increment == null || increment == 0)
          {
             increment = 1L;
          }
          long calcMinVal = (minVal != null) ? minVal : (increment > 0 ? 1L : Long.MIN_VALUE + 1);
          long calcMaxVal = (maxVal != null) ? maxVal : (increment > 0 ? Long.MAX_VALUE - 1 : -1L);

          if (init == null)
          {
             init = (increment > 0) ? calcMinVal : calcMaxVal;
          }

          // validation
          boolean minAltered = false;
          boolean maxAltered = false;
          String minStr = String.valueOf(calcMinVal);
          String maxStr = String.valueOf(calcMaxVal);

          if (calcMaxVal < init)
          {
             calcMaxVal = init;
             maxStr = calcMaxVal + " /* adjusted from " + maxStr + " */";
             maxAltered = true;
          }
          else if (calcMaxVal == Long.MAX_VALUE)
          {
             calcMaxVal = Long.MAX_VALUE - 1;
             maxStr = calcMaxVal + " /* adjusted from " + Long.MAX_VALUE + " */";
             maxAltered = true;
          }

          if (calcMinVal > init)
          {
             calcMinVal = init;
             minStr = calcMinVal + " /* adjusted from " + minStr + " */";
             minAltered = true;
          }
          else if (calcMinVal == Long.MIN_VALUE)
          {
             calcMinVal = Long.MIN_VALUE + 1;
             minStr = calcMinVal + " /* adjusted from " + Long.MIN_VALUE + " */";
             minAltered = true;
          }

          assert (calcMaxVal >= calcMinVal);

          StringBuilder sb = new StringBuilder("create sequence ");
          sb.append(name);
          sb.append(" increment by ").append(increment);

          if (minVal == null && !minAltered)
          {
             sb.append(" nominvalue");
          }
          else
          {
             sb.append(" minvalue ").append(minStr);
          }

          if (maxVal == null && !maxAltered)
          {
             sb.append(" nomaxvalue");
          }
          else
          {
             sb.append(" maxvalue ").append(maxStr);
          }

          sb.append(" start with ").append(init);
          sb.append(cycle != null && cycle ? " cycle" : " nocycle");
          return sb.toString();
       }

       /**
        * Generate a DDL string which will drop a sequence.
        *
        * @param   name
        *          The name of the sequence to be dropped.
        *
        * @return  Dialect-specific string to drop the sequence, or {@code null} if not supported.
        *
        * @see <a href="https://mariadb.com/kb/en/create-sequence/">DROP SEQUENCE Syntax</a>
        */
       @Override
       public String getDropSequenceString(String name)
       {
          return "drop sequence if exists " + name;
       }

       /**
        * Generate the appropriate select statement to reset the value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence.
        * @param   newVal
        *          The new value to be set.
        *
        * @return  Dialect-specific string to set the current value of the sequence, or {@code null}
        *          if not supported.
        *
        * @see  <a href="https://mariadb.com/kb/en/setval/">SETVAL() Function</a>
        */
       @Override
       public String getSequenceSetValString(String sequenceName, long newVal)
       {
          return "select setval(" + sequenceName + ", " + newVal + ", true)";
       }

       /**
        * Generate the appropriate select statement to reset the value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence.
        *
        * @return  Dialect-specific string to set the current value of the sequence, or {@code null}
        *          if not supported.
        *
        * @see  <a href="https://mariadb.com/kb/en/setval/">SETVAL() Function</a>
        */
       @Override
       public String getSequenceSetValString(String sequenceName)
       {
          return "select setval(" + sequenceName + ", ?)";
       }

       /**
        * Generate the appropriate select statement to reset the value of a sequence.
        *
        * @return  Dialect-specific string to set the current value of the sequence, or {@code null}
        *          if not supported.
        *
        * @see  <a href="https://mariadb.com/kb/en/setval/">SETVAL() Function</a>
        */
       @Override
       public String getSequenceSetValString()
       {
          return "select setval(?, ?)";
       }

       /**
        * Generate the appropriate select statement to retrieve the next value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence to be queried.
        *
        * @return  Dialect-specific string to query the sequence, or {@code null} if not supported.
        *
        * @see <a href="https://mariadb.com/kb/en/next-value-for-sequence_name/">NEXT VALUE for sequence_name</a>
        */
       @Override
       public String getSequenceNextValString(String sequenceName)
       {
          return "select next value for " + sequenceName;
       }

       /**
        * Generate the appropriate select statement to retrieve the current value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence to be queried.
        *
        * @return  Dialect-specific string to query the sequence, or {@code null} if not supported.
        *
        * @see  <a href="https://mariadb.com/kb/en/previous-value-for-sequence_name/">
        *       PREVIOUS VALUE for sequence_name</a>
        */
       @Override
       public String getSequenceCurrValString(String sequenceName)
       {
          return "select previous value for " + sequenceName;
       }

       /**
        * Returns {@code true} if this dialect fully support the sequences as 4GL language (most important:
        * bounded values, cycle). If not fully supported, additional code is needed in P2J sequence class
        * implementation to fix this.
        *
        * @return  {@code true} if this dialect fully support 4GL sequences
        */
       @Override
       public boolean hasFullSequenceSupport()
       {
          return true;
       }

       /**
        * Generate the appropriate select statement to pre-fetch multiple, ascending values from a sequence.
        * The values need not be contiguous.
        *
        * @param   sequenceName
        *          The name of the sequence from which the values are to be retrieved.
        *
        * @return  At the moment MariaDb does not support prefetch for sequences. Returning {@code null}.
        *
        * @see  <a href="https://mariadb.com/kb/en/sequence-functions/">SEQUENCE Functions</a>
        */
       @Override
       public String getSequencePrefetchString(String sequenceName)
       {
          return null;
       }

       /**
        * Given an exception, this method will check if it indicates a database connectivity error.
        *
        * @param   exc
        *          The exception to be checked.
        *
        * @return  {@code true} if the exception indicates a database connectivity error.
        *
        * @see <a href="https://mariadb.com/kb/en/mariadb-error-codes/">MariaDB Error Codes</a>
        */
       @Override
       public boolean isConnectionError(Exception exc)
       {
          Throwable next = exc;
          while (next != null)
          {
             if (next instanceof SQLException)
             {
                SQLException sqlEx = (SQLException) next;
                String state = sqlEx.getSQLState();
                int errorCode = sqlEx.getErrorCode();

                return "08S01".equals(state) ||
                       "08004".equals(state) ||
                       "HY000".equals(state) &&
                             (errorCode == 1077 || // ER_NORMAL_SHUTDOWN
                              errorCode == 1078 || // ER_GOT_SIGNAL
                              errorCode == 1079);  // ER_SHUTDOWN_COMPLETE
             }
             next = next.getCause();
          }

          return false;
       }

       /**
        * Indicate whether a query parameter which is part of a range check should
        * be inlined into the where clause, or whether it should be substituted
        * into a prepared statement.  This will result in an "inlined"
        * substitution parameter within a where clause.  For example, given a
        * where clause of:
        * <pre>
        *    where value &ge; ? and id = ?
        * </pre>
        * along with respective substitution parameters of 45 and 100, the
        * inlined version would read:
        * <pre>
        *    where value &ge; 45 and id = ?
        * </pre>
        * Only the <code>id</code> substitution parameter of 100 would be
        * substituted into the prepared statement.
        * <p>
        * Inlining in such a way can give some database dialects the opportunity
        * to choose a better query plan, than if the parameter being tested with
        * a range check were to be substituted at statement execution time.
        * <p>
        * Note:  this serves as a hint which may be overridden by runtime logic or
        * other configuration settings.
        *
        * @return  <code>true</code> to inline range check parameters;
        *          <code>false</code> to use normal parameter substitution.
        */
       @Override
       public boolean isQueryRangeParameterInlined()
       {
          return inlineRangeParms;
       }

       /**
        * Enable/disable limit clause inlining. Inlining the limit clause will cause the limit/max value to be
        * hard-coded into the SQL statement, rather than being bound as a substitution parameter.
        * <p>
        * Dialects are free to treat this method as a hint.
        *
        * @param   on
        *          {@code true} to enable inlining; {@code false} to disable it.
        */
       @Override
       public void inlineLimit(boolean on)
       {
          // ignoring this directive, for the moment
       }

       /**
        * Determines whether substitution parameters should be explicitly cast to their datatypes inside
        * {@code "then ? else ?"} statement.
        *
        * @return  {@code true}: this dialect requires casting in this case.
        */
       @Override
       public boolean requiresExplicitCastInsideTernary()
       {
          return true;
       }

       /**
        * Build the DDL to drop an index based upon the specified definition. The SQL is generated by using the
        * database-specific dialect.
        *
        * @param   index
        *          Definition of the index to be dropped.
        *
        * @return  The DDL to drop the given index.
        *
        * @see <a href="https://mariadb.com/kb/en/drop-index/">DROP INDEX Syntax</a>
        */
       @Override
       public String getDropIndexString(P2JIndex index)
       {
          // unlike the other dialects, in MariaDb, the index names are NOT unique between tables, so the table
          // name is mandatory in this statement
          return "drop index if exists " + quote(getSqlIndexName(index)) + " on " + index.getTable();
       }

       /**
        * Prepare the given metadata query parameter.
        *
        * @param   param
        *          The metadata query parameter.
        *
        * @return  the prepared parameter.
        */
       @Override
       public String prepareMetadataParameter(String param)
       {
          return param == null ? null : param.toLowerCase();
       }

       /**
        * Process the given metadata query result.
        *
        * @param   value
        *          The metadata query result.
        *
        * @return  the processed result.
        */
       @Override
       public String processMetadataResult(String value)
       {
          return value == null ? null : value.toLowerCase();
       }

      /**
       * Check if the given value is an ascending clause for a column index.
       *
       * @param   value
       *          The value to be checked.
       *
       * @return  {@code true} if the value is the "ascending" clause.
       */
       @Override
       public boolean isMetadataSortDesc(String value)
       {
          return "D".equalsIgnoreCase(value);
       }

       /**
        * Check if the given value is a descending clause for a column index.
        *
        * @param   value
        *          The value to be checked.
        *
        * @return  {@code true} if the value is the "descending" clause.
        */
       @Override
       public boolean isMetadataSortAsc(String value)
       {
          return "A".equalsIgnoreCase(value);
       }

       /**
        * Check if the dialect needs to set an explicit collation during schema DDL generation.
        *
        * @return  {@code true} if the collation needs to be explicitly set.
        */
       @Override
       public boolean explicitSetCollation()
       {
          return false;
       }

       /**
        * Get the statement delimiter for this dialect.
        *
        * @return  the statement delimiter.
        */
       @Override
       public String getDelimiter()
       {
          return ";";
       }

       /**
        * Build the URL to be used for a remote connection, based on the given URL and database.
        *
        * @param    url
        *           The URL as received from the remote server.
        * @param    database
        *           The database to which this URL belongs.
        *
        * @return   The built URL as to be used for a remote connection.
        *
        * @throws   PersistenceException
        *           if the specified connection URL is invalid.
        */
       @Override
       public String buildRemoteURL(String url, Database database)
       throws PersistenceException
       {
          return DialectHelper.buildRemoteURL(url, database);
       }

       /**
        * Creates and returns a {@code SequenceHandler} for a database.
        *
        * @param   ldbName
        *          The logical name of the database to create the {@code SequenceHandler} for.
        *
        * @return  A {@code SequenceHandler} for respective database.
        */
       @Override
       public SequenceHandler createSequenceHandler(String ldbName)
       {
          return new MariaDbSequenceHandler(ldbName);
       }

       /**
        * This MariaDb dialect variant does NOT honour the "nulls last" feature of 4GL.
        *
        * @return  {@code false}.
        */
       @Override
       public boolean injectComputedColumns()
       {
          return false;
       }

       /**
        * Checks if the SQLState of the SQLException matches the dialect specific UNIQUE_VIOLATION
        * code for MariaDB (23000).
        *
        * @param   sqle
        *          The SQLException that is causing the unique constraint violation.
        *
        * @return  <code>true</code> if the SQLState matches the dialect specific code for
        *          UNIQUE_VIOLATION, <code>false</code> otherwise.
        */
       @Override
       public boolean isUniqueConstraintViolation(SQLException sqle)
       {
          String sqleState = sqle.getSQLState();
          if ("23000".equals(sqleState))
          {
             return true;
          }
          return false;
       }

       /**
        * Returns the size of the synthetic part of the index. At this moment only the {@code id} of the record
        * may be additionally added to an index to add custom properties (eg uniqueness) but other fields could be
        * added. This part of the index is not visible using index reflection routines from {@code IndexHelper}.
        * The synthetic components may differ between unique and non-unique indexes.
        *
        * @param   unique
        *          {@code true} if the index is unique
        *
        * @return  The size of synthetic part of the index, in bytes.
        */
       @Override
       public int getSyntheticIndexSize(boolean unique)
       {
          return 8;
       }

       /**
        * Composes a {@code rtrim} equivalent call for a specified expression. This is dialect specific. Usually
        * the returned value looks like:
        * <pre>
        *    rtrim(expr)
        * </pre>
        * The {@code rtrim}-med expression is appended to the string builder. The return value (the number of
        * occurrences of the initial {@code expr} added to {@code sb}) should be use by caller to count the number
        * of possible parameters from a query.
        *
        * @param   expr
        *          The expression to be {@code rtrim}-med. It's up to the caller if it is a qualified field or not
        *          or any other kind of character-type expression.
        * @param   sb
        *          The {@link StringBuilder} to append the trimmed field to.
        */
       @Override
       public void addRtrimmedExpression(String expr, StringBuilder sb)
       {
          sb.append("rtrim(").append(expr).append(")");
       }

       /**
        * Return the beginning of the DDL statement that creates a temporary table.
        *
        * @param   ifNotExist
        *          If {@code true} a dialect-specific, optional {@code if not exist} clause is added.
        *
        * @return  A string representing the beginning of the DDL statement that creates a temporary table.
        *
        * @see <a href="https://mariadb.com/kb/en/create-table/">CREATE TABLE Syntax</a>
        */
       @Override
       public String getCreateTemporaryTableString(boolean ifNotExist)
       {
          return "create temporary table " + (ifNotExist ? "if not exists " : "");
       }

       /**
        * Return the ending of the DDL statement that creates a temporary table.
        *
        * @param   noUndo
        *          This parameter is not used at the moment. The intention was to generate a keyword for
        *          no-undo tables. Only {@link P2JH2Dialect} benefits from this parameter.
        *
        * @return  A string representing the ending of the DDL statement that creates a temporary table, if any,
        *          or empty string otherwise.
        *
        * @see <a href="https://mariadb.com/kb/en/create-table/#transactional">CREATE TABLE TRANSACTIONAL</a>
        */
       @Override
       public String getCreateTemporaryTablePostfix(boolean noUndo)
       {
          return " transactional";
       }

       /**
        * Creates and returns a string which represent the {@code alter table} statement for adding a new
        * foreign key constraint.
        *
        * @param   table
        *          The table to be altered.
        * @param   fkName
        *          The name of the constraint.
        * @param   fkFields
        *          Comma separated names of the fields which make the reference to parent table.
        * @param   refTable
        *          The parent / referred table.
        * @param   refFields
        *          Comma separated names of the fields which uniquely identify the record in parent table.
        * @param   indent
        *          The indentation for pretty print the result.
        * @param   eoln
        *          OS-specific end of line terminator (if the result is a multiline).
        *
        * @return  A string used to create the foreign key constraint as described above.
        */
       @Override
       public String getAddForeignKeyConstraintString(String table,
                                                      String fkName,
                                                      String fkFields,
                                                      String refTable,
                                                      String refFields,
                                                      String indent,
                                                      String eoln)
       {
          if (indent == null)
          {
             indent = "";
          }
          if (eoln == null || eoln.isEmpty())
          {
             eoln = " ";
          }
          return "alter table " + table + eoln +
                 indent + "add constraint " + fkName + eoln +
                 indent + "foreign key (" + fkFields + ")" + eoln +
                 indent + "references " + refTable + "(" + refFields + ")" + eoln +
                 indent + "on delete cascade";
       }

       /**
        * Provides catalog matching pattern to load data correctly from JDBC.
        *
        * @param   database
        *          database object
        * @return  catalog pattern
        */
       @Override
       public String getCatalogPattern(Database database)
       {
          return database.getName();
       }

       /**
        * Provides schema matching pattern to load data correctly from JDBC.
        *
        * @param   database
        *          database object
        * @return  schema pattern
        */
       @Override
       public String getSchemaPattern(Database database)
       {
          return database.getName();
       }

       /**
        * Provides SQL to load sequences.
        *
        * @param   database
        *          database object
        * @return  sequence SQL
        */
       @Override
       public String getSequenceSQL(Database database)
       {
          return "SELECT `table_name` AS sequence_name FROM information_schema.tables WHERE `table_schema` = '" + database.getName() + "' AND `table_type` = 'SEQUENCE'";
       }

       /**
        * Obtain a DDL statement which drops a SQL index.
        *
        * @param   ifExists
        *          Flag that requires that the DDL include an optional {@code IF EXIST} clause.
        * @param   idxName
        *          The name of the index to be dropped by this DDL.
        * @param   tableName
        *          The name of the table to which the index belongs to. (Optional, is needed by the dialect).
        *
        * @return  a DDL statement which drops a SQL index.
        *
        * @see <a href="https://mariadb.com/kb/en/drop-index/">DROP INDEX Syntax</a>
        */
       @Override
       public String getDropIndexString(boolean ifExists, String idxName, String tableName)
       {
          // unlike the other dialects, in MariaDb, the index names are NOT unique between tables, so the table
          // name is mandatory in this statement
          return "drop index " + (ifExists ? "if exists " : "") + idxName  + " on " + tableName;
       }

       /**
        * Obtain a DDL statement which drops a SQL table.
        *
        * @param   ifExists
        *          Flag that requires that the DDL include an optional {@code IF EXIST} clause.
        * @param   tableName
        *          The name of the table to be dropped by this DDL.
        *
        * @return  a DDL statement which drops a SQL index.
        *
        * @see <a href="https://mariadb.com/kb/en/drop-table/">DROP TABLE Syntax</a>
        */
       @Override
       public String getDropTableString(boolean ifExists, String tableName)
       {
          return "drop table " + (ifExists ? "if exists " : "") + tableName + " cascade";
       }

       /**
        * Construct and return a string which represents the {@code analyze} or similar statements for this
        * dialect. If the {@code table} is not {@code null} the statement will analyze only the respective table.
        * Otherwise, all the tables are analyzed.
        *
        * @param   table
        *          The name of the table to be analyzed. If {@code null}, all tables from the database will be
        *          analyzed, if possible.
        *
        * @return  The ANALYZE statement as requested. If the dialect does not support the {@code ANALYZE}
        *          statement in the form requested {@code null} is returned.
        *
        * @see <a href="https://mariadb.com/kb/en/analyze-table/">ANALYZE TABLE Syntax</a>
        */
       @Override
       public String getAnalyzeString(String table)
       {
          return table == null ? "analyze" : "analyze table " + table;
       }

       /**
        * Converts and return the specified {@code FqlType} to a string representation which can be used in a
        * SQL query ({@code cast} or other construct).
        *
        * @param   type
        *          The type to be converted to string.
        *
        * @return  A dialect specific string representation of the respective fql data type.
        */
       @Override
       public String getSpecificTypeAsString(FqlType type)
       {
          switch (type.getSqlType())
          {
             case Types.BIT: return "boolean";
             case Types.INTEGER: return "integer";
             case Types.BIGINT: return "bigint";
             case Types.NUMERIC: return "decimal";
             case Types.DATE: return "date";
             case Types.VARBINARY: return "VARBINARY(" + MAX_VARCHAR_SIZE + ")";
             case Types.TIMESTAMP: return "timestamp";
             case Types.TIMESTAMP_WITH_TIMEZONE: return "timestamp"; // no time zone
             case Types.ARRAY: return "array";
             case Types.BLOB: return "blob";
             case Types.VARCHAR: // fall through
             case Types.CLOB: return "text";
             default: return type.toString(); // maybe log this?
          }
       }

       /**
        * Translation from database column type to {@code ParmType}.
        *
        * @param   type
        *          Column type.
        * @param   comment
        *          comment with optional specification of type
        *
        * @return  ParmType
        *
        * @throws  IllegalArgumentException
        *          for unsupported types.
        */
       @Override
       public ParmType translateToParmType(String type, String comment)
       {
          ParmType parmTypeFromComment = DialectHelper.getParmTypeFromComment(comment);
          if (parmTypeFromComment != null)
          {
             return parmTypeFromComment;
          }

          switch (type.toUpperCase())
          {
             case "BIT":
             case "BOOLEAN":
                return ParmType.LOG;
             case "INT":
                return ParmType.INT;
             case "BIGINT":
                return ParmType.INT64;
             case "DECIMAL":
                return ParmType.DEC;
             case "VARCHAR":
             case "TEXT":
                return ParmType.CHAR;
             case "DATE":
                return ParmType.DATE;
             case "DATETIME":
             case "DATETIME(3)":
                return ParmType.DT;
             case "TIMESTAMP":
             case "TIMESTAMP(3)":
                return ParmType.DTTZ;
             case "BLOB":
                return ParmType.BLOB;
             case "MEDIUMTEXT":
                return ParmType.CLOB;
             case "VARBINARY":
                return ParmType.RAW;
             default:
                throw new IllegalArgumentException("DB column type " + type + " is not supported");
          }
       }

       /**
        * Get the SQL mapping for a specific FWD data type.
        *
        * @param   fwdType
        *          The FWD type.
        * @param   scale
        *          Optional scale parameter (for {@code decimal} data type).
        *
        * @return  The string representation of the SQL datatype mapped by {@code fwdType}.
        */
       @Override
       public String getSqlMappedType(String fwdType, int scale, Boolean caseSensitive)
       {
          String s = fwd2sql.get(fwdType);
          if (s == null)
          {
             CentralLogger log = getLog();
             if (log.isLoggable(Level.WARNING))
             {
                log.log(Level.WARNING, "Unknown '" + fwdType + "' type for MariaDb dialect.");
             }

             return fwdType; // returning the [fwdType]!
          }

          int k = s.indexOf(SCALE_TOK);
          if (k > 0)
          {
             if ("character".equalsIgnoreCase(fwdType) || "String".equalsIgnoreCase(fwdType))
             {
                if (scale >= MIN_TEXT_SIZE)
                {
                   return "text";
                }

                if (scale == 0)
                {
                   scale = DEFAULT_MAX_WIDTH;
                }
             }

             return s.substring(0, k) + scale + s.substring(k + SCALE_TOK.length());
          }

          return s;
       }

       /**
        * This dialect has a constraints of 3KiB on the index key size. This method does a heuristic calculation
        * and report a possible error/warning when the database schema is set.
        *
        * @param   p2JIndex
        *          The index to be processed.
        * @param   fields
        *          All table fields, mapped by their legacy name.
        *
        * @see     <a href="https://mariadb.com/kb/en/data-type-storage-requirements/">Data Type Requirements<a>
        */
       public void checkIndexConstraints(P2JIndex p2JIndex, Map<String, P2JField> fields)
       {
          int indexRowLen = 0;
          for (P2JIndexComponent comp : p2JIndex.components())
          {
             P2JField f = fields.get(comp.getLegacyName());
             if (f != null)
             {
                switch (f.getType())
                {

                   case CHAR:
                   case LC:
                   case CLOB:
                   case TEXT:
                   case MEM:
                   case RAW:
                   case BIN:
                      if (f.getMaxWidth() > MIN_TEXT_SIZE)
                      {
                         LOG.log(Level.WARNING,
                                 "MariaDbLenient constraint: Column '" + comp.getColumnName() +
                                 "' has TEXT type and cannot be indexed without a specified length!");
                      }
                      indexRowLen += BPC + BPC * f.getMaxWidth(); break;

                   case BLOB:
                      indexRowLen += f.getMaxWidth(); break;

                   case INT:
                      indexRowLen += 4; break;

                   case NUM:
                   case NUM_VARARGS:
                   case DEC:
                      // Highly heuristic first implementation: "Each nine-digit multiple requires 4 bytes,
                      //    followed by a number of bytes for whatever remains... " (see online resource)
                      indexRowLen += 6 * 4; break;

                   case ROWID:
                   case RECID:
                   case INT64:
                      indexRowLen += 8; break;

                   case DATE:
                      indexRowLen += 3; break;

                   case DT:
                   case DTTZ:
                      indexRowLen += 8; break;

                   case LOG:
                      indexRowLen += 1; break;

                   case COMHANDLE:
                   case OBJECT:
                   case JOBJECT:
                   case WID:
                   case HANDLE:
                      indexRowLen += 8; break;

                   case TEXT_VARARGS:
                   case RECORD:
                   case ROWID_ARRAY:
                   case BDT:
                   case BDT_ARRAY:
                   case BDT_VARARGS:
                      break; // unknown (?)
                }
             }
          }

          if (indexRowLen >= INDEX_MAX_KEY_SIZE)
          {
             LOG.log(Level.WARNING,
                   "MariaDbLenient constraint: Index '" + p2JIndex.getLegacyName() + "' exceeded 3KiB limit of MariaDb! "+
                   "Computed key size is: " + indexRowLen + ".");
          }
       }

       /**
        * Prepare the specified database by creating all necessary function aliases and setting global parameters.
        *
        * @param   db
        *          Database to prepare.
        *
        * @throws  PersistenceException
        *          if there is any error executing DDL.
        */
       @Override
       public void preparePermanentDatabase(Database db)
       throws PersistenceException
       {
          MariaDbHelper.registerOverloadedFunctions(db);
       }

       /**
        * Obtains an object responsible with providing dialect specific SQL statements for tenant management.
        */
       @Override
       public Dialect.TenantSQL getTenantSql()
       {
          return new Dialect.TenantSQL()
          {
             /** SQL for creating the master table, if it doesn't already exist. */
             @Override
             public String getCreateMasterTable()
             {
                return "CREATE TABLE IF NOT EXISTS master (\n"+
                       "       tenant_id          UUID NOT NULL,\n" +
                       "       tenant_name        VARCHAR(255) NOT NULL,\n" +
                       "       tenant_description VARCHAR(1023),\n" +
                       "       tenant_info        VARCHAR(1023),\n" +
                       "       status             BOOLEAN,\n" +
                       "    PRIMARY KEY(tenant_id))";
             }

             /**
              * Obtain the SQL statement for creating the {@code tenant_databases} table, if it doesn't already
              * exist. The statement must declare at least the following fields: {@code tenant_name (string)},
              * {@code pdb_name (string)}, {@code ldb_name (string)}, {@code url (string)},
              * {@code username (string)}, {@code password (string)}, {@code C3P0_MAX_STMTS (integer)},
              * {@code C3P0_MIN_POOL (integer)},  {@code C3P0_MAX_POOL (integer)}, {@code C3P0_ACQ_INC (integer)},
              * {@code C3P0_MAX_IDLE (integer)}.
              *
              * @return  the SQL statement for creating the {@code tenant_databases} table, if it doesn't already
              *          exist.
              */
             @Override
             public String getCreateDatabasesTable()
             {
                return "CREATE TABLE IF NOT EXISTS tenant_databases (\n" +
                       "       tenant_name    VARCHAR(255) NOT NULL,\n" +
                       "       pdb_name       VARCHAR(255) NOT NULL,\n" +
                       "       ldb_name       VARCHAR(255) NOT NULL,\n" +
                       "       url            VARCHAR(1023),\n" +
                       "       username       VARCHAR(255),\n" +
                       "       password       VARCHAR(255),\n" +
                       "       " + C3P0_MAX_STMTS + " INTEGER,\n" +
                       "       " + C3P0_MIN_POOL  + " INTEGER,\n" +
                       "       " + C3P0_MAX_POOL  + " INTEGER,\n" +
                       "       " + C3P0_ACQ_INC   + " INTEGER,\n" +
                       "       " + C3P0_MAX_IDLE  + " INTEGER,\n" +
                       "    PRIMARY KEY(tenant_name, pdb_name))";
             }

             /** SQL for creating an index for the master table on the {@code tenant_name} column. */
             @Override
             public String getCreateMasterIndex()
             {
                return "CREATE INDEX IF NOT EXISTS master_idx ON master (tenant_name)";
             }

             /**
              * Obtain the SQL statement for creating an index for the {@code tenant_databases} table.
              *
              * @return  the SQL statement for creating an index for the {@code tenant_databases} table.
              */
             @Override
             public String getCreateTenantDbIndex()
             {
                return "CREATE INDEX IF NOT EXISTS tenant_db_idx ON tenant_databases (tenant_name, pdb_name)";
             }
          };
       }

       /**
        * Use the provided database connection to extract the codepage/CharSet used by the database and report it
        * back to the caller.
        *
        * @param   conn
        *          The JDBC database connection to be used.
        *
        * @return  The name of the codepage/CharSet used by the database to store character data.
        */
       public String getCodepage(Connection conn)
       {
          try
          {
             PreparedStatement ps = conn.prepareStatement(
                   "SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=DATABASE()");
             ResultSet rs = ps.executeQuery();

             if (rs.next())
             {
                return mapCP(rs.getString(1)); // DEFAULT_COLLATION_NAME
             }
          }
          catch (SQLException e)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.warning("Failed to retrieve CODEPAGE from database.", e);
             }
          }

          // fallback for all errors
          return null;
       }

       /**
        * Use the provided database connection to extract the collation used by the database and report it
        * back to the caller.
        *
        * @param   conn
        *          The JDBC database connection to be used.
        *
        * @return  The name of the collation used by the database to store character data.
        */
       @Override
       public String getCollation(Connection conn)
       {
          try
          {
             PreparedStatement ps = conn.prepareStatement(
                   "SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=DATABASE()");
             ResultSet rs = ps.executeQuery();

             if (rs.next())
             {
                return mapCollation(rs.getString(1)); // DEFAULT_COLLATION_NAME
             }
          }
          catch (SQLException e)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.warning("Failed to retrieve COLLATION from database.", e);
             }
          }

          // fallback for all errors
          return null;
       }

       /**
        * Get the logger specific to this MariaDb dialect variant.
        *
        * @return  the logger specific to this MariaDb dialect variant.
        */
       protected CentralLogger getLog()
       {
          return LOG;
       }

       /**
        * Maps/extract from the MariaDb specific charset names to 4GL codepage names.
        *
        * @param   collation
        *          A MariaDb specific collation name. (Ex: "latin1_swedish_ci", "cp1250_polish_ci")
        *
        * @return  The 4GL codepage name of {@code h2Charset}.
        */
       private static String mapCP(String collation)
       {
          if (collation == null)
          {
             return null;
          }

          int k = collation.indexOf('_');
          if (k == -1)
          {
             return collation;
          }
          collation = collation.substring(0, k);

          switch (collation.toUpperCase())
          {
             case "ASCII":     return "1252"; // not perfect match
             case "CP850":     return "IBM850";
             case "CP852":     return "IBM852";
             case "LATIN1":    return "ISO8859-1";
             case "LATIN2":    return "ISO8859-2";
             case "LATIN5":    return "ISO8859-9";
             case "LATIN7":    return "ISO8859-13";
             case "LATIN9":    return "ISO8859-15";
             case "UTF8MB4":   return "UTF-8";
             default:          return collation;
          }
       }

       /**
        * Maps the MariaDb specific collation names to 4GL collation names.
        *
        * @param   collation
        *          A MariaDb specific collation name. (Ex: "latin1_swedish_ci", "cp1250_polish_ci")
        *
        * @return  The 4GL codepage name of {@code h2Charset}.
        */
       private static String mapCollation(String collation)
       {
          if (collation == null)
          {
             return null;
          }

          int k = collation.indexOf('_');
          if (k == -1)
          {
             return collation;
          }
          collation = collation.substring(k + 1);

          k = collation.indexOf('_');
          if (k == -1)
          {
             return collation;
          }
          collation = collation.substring(0, k);

          switch (collation.toUpperCase())
          {
             case "UNICODE":   return "BASIC";
             default:          return collation;
          }
       }
    }