# P2JH2Dialect.java

    /*
    ** Module   : P2JH2Dialect.java
    ** Abstract : Concrete implementation of Dialect for an H2 backend
    **
    ** Copyright (c) 2008-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
    ** 001 ECF 20080223   @37519 Created initial version. Adds P2JDialect
    **                           method implementations to extend Hibernate's
    **                           H2Dialect.
    ** 002 ECF 20080611   @38701 Added isCaseInsensitiveColumn(). Provides a
    **                           rough test for case-insensitivity, based only
    **                           on an expression which contains a column
    **                           name, such as might be extracted from index
    **                           metadata. Commented out experimental code.
    ** 003 SVL 20080623   @38890 Column type for the VARBINARY type was
    **                           changed to "binary" (instead of
    **                           "binary($l)").
    ** 004 ECF 20080805   @39315 Added getCreateSequenceString(). Generates
    **                           DDL to create a sequence starting at a
    **                           particular value.
    ** 005 CA  20080815   @39443 Added isConnectionError method - it checks if
    **                           a given exception represents a connection
    **                           error.
    ** 006 ECF 20080930   @40002 Added isQueryRangeParameterInlined().
    **                           Indicates whether query substitution
    **                           parameters within range checks should be
    **                           inlined.
    ** 007 ECF 20081015   @40097 Added formatDate(). Formats a date into a
    **                           string suitable for use in an SQL statement.
    ** 008 ECF 20090310   @41500 Added inlineLimit(). Enables inlining the
    **                           limit/max for records returned by a query
    **                           into the SELECT statement. Currently it is
    **                           unimplemented.
    ** 009 SVL 20090810   @43574 Added requiresExplicitCastInsideTernary().
    **                           Determines whether substitution parameters should
    **                           be explicitly casted to their datatypes inside
    **                           "then ? else ?" statement.
    ** 010 GES 20100701          Added a type name override to allow honoring of
    **                           precision and scale in numeric types.
    ** 011 CA  20101026          Added methods to generate the DROP/CREATE INDEX
    **                           statements in a dialect specific form. Also,
    **                           added method to generate the ALTER TABLE statement
    **                           which sets the formula for the computed columns.
    **                           Added methods to prepare/process string metadata
    **                           query parameters and results, to get the database
    **                           prepare DDL statements and collation statement.
    ** 012 CA  20101202          Added method to build the URL for a remote
    **                           connection. Removed the "h2.sortNullsHigh"
    **                           setting, as it caused regressions; more testing
    **                           in a 4GL environment is needed, to determine
    **                           exactly how 4GL sorts the unknown value with the
    **                           temporary and permanent databases.
    ** 013 SVL 20110622          Added usesSingleBackslash.
    ** 014 OM  20121120          Added DDL/SQL generation support for sequences.
    ** 015 SVL 20130331          Upgraded to Hibernate 4.
    ** 016 ECF 20130510          Fixed sequence-related methods to eliminate use of case-sensitive
    **                           sequence names.
    ** 017 OM  20130712          Added if exists clause to drop sequence statement.
    **                           Implemented createSequenceHandler().
    ** 018 VMN 20131005          Added injectComputedColumns. Replaced $p to 50 in registerColumnType
    **                           for Types.NUMERIC.
    ** 019 ECF 20131007          Updated buildRemoteURL to enable secure, mixed-mode URLs for embedded
    **                           databases on remote P2J servers. Added "if exists" to drop index
    **                           string.
    ** 020 OM  20130820          Added support for PRIMARY KEY in mixed asc/desc indexes.
    ** 021 OM  20140410          Fixed support case-sensitive fields in indexes.
    **                           Added Override method annotations. Simplified string concatenations.
    ** 022 VMN 20140427          Added enhance schema name conversion support for hint "escape"
    **                           attribute.
    ** 023 OM  20140508          Added support for computing the index-key size.
    ** 024 OM  20140623          Added rtrim() dialect specific implementation. Added query max
    **                           parameters count support.
    ** 025 OM  20140924          Removed usesSingleBackslash.
    ** 026 OM  20150522          Added getReservedKeywords() static method.
    ** 027 OM  20151130          rtrim() dialect specific implementation returns the number of
    **                           occurrences of the expression processed. Implemented
    **                           supportsBooleanDatatype().
    ** 028 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 029 OM  20160603          Fixed rtrimming to space character only.
    ** 030 ECF 20160912          Added getSequencePrefetchString.
    ** 031 OM  20200610          Replaced Hibernate with customized ORM.
    **     CA  20200624          Augment the sort with the NULLS FIRST/LAST depending on the direction and dialect.
    **     CA  20200714          Updated the reserved keywords with the 1.4.200 version.
    **     AIL 20201120          Changed syntax for sequence nextval.
    **     IAS 20201204          Added id() method and support stub for word tables
    **     IAS 20210104          Added trigger's for the population/update word tables
    **     IAS 20210219          Word tables support for custom extents
    **     IAS 20210303          Added containsUdfName() function
    **     CA  20210305          The object and handle fields have a Long representation at the SQL table field.
    **     IAS 20210310          Added implicit ordering
    **     IAS 20210315          Refactored for working with word tables for _temp database
    **     OM  20210412          Replace Hibernate-specific casts with dialect-specific casts.
    **     OM  20210908          Used SchemaDictionary.TEMP_TABLE_DB instead of hardcoded constant.
    **     IAS 20211223          Special processing of errors raised by UDFs.
    **     IAS 20220112          Resolve dialect by JDBC database type.
    **     IAS 20220602          Added isNativeUDFsSupported() method.
    **     IAS 20220712          Added getJdbcType() method.
    **     OM  20220721          Added partial conversion-time MariaDb dialect support.
    **     IAS 20220816          Added getScriptRunner() method.
    **     DDF 20220823          Replaced .nextvalue with next value for.
    **     IAS 20230109          Add 'currval' to reserved keywords' list
    **     IAS 20220913          Re-work processing of UDFs errors/warnings.
    **     OM  20220914          Use MAX-WIDTH to generate custom sized varchar columns.
    **     OM  20221012          Fixed regression (invalid sort criteria) in previous commit.
    **     OM  20221026          Excluded table name from index name by default. It will be added as needed by
    **                           dialects.
    **     RAA 20230109          Overrided methods getSequenceSetValString() and getSequenceCurrValString().
    ** 032 RAA 20230221          Added supportsLazyQueryResultsMode function.
    **     AL2 20230210          Added getInvisibleSpecificator.
    **     RAA 20230208          Overrided method for returning the postfix of a no-undo temp-table.
    **     AL2 20230303          Added a space to the invisible specificator to avoid appending it later.
    ** 033 AL2 20230316          Allow allowSelectWildcard.
    ** 034 IAS 20230405          Added fix for H2 bug with bind parameters in recursive CTEs.
    **     DDF 20230306          Removed usage of computed columns. Set isAutoRtrimAndCi to true. Included
    **                           token for getting types varchar_casesensitive/varchar_ignorecase/varchar
    **                           (refs: #7108).
    **     DDF 20230309          Removed unnecessary methods related to computed columns and checked for
    **                           CASE_TYPE_TOK before replacing it. Consider all columns to be case
    **                           insensitive in isCaseInsensitiveColumn because it is rarely used.
    ** 034 DDF 20230406          Removed isAutoRtrimAndCi. Added isAutoRtrim and isAutoCi methods.
    ** 035 GBB 20230512          Logging methods replaced by CentralLogger/ConversionStatus.
    ** 036 AL2 20231002          Added VALIDATE to the reserved keywords list. This is added in the FWD-H2 fork.
    ** 037 IAS 20230910          Fixed flags for word tables' generation. Added support for the ScriptRunner.
    ** 038 DDF 20230818          Created an additional getCreateSequenceString() method that generates
    ** 039 DDF 20231127          Added isUniqueConstraintViolation() method.
    ** 040 OM  20231218          Added implementation for DBCODEPAGE and DBCOLLATION 4GL functions.
    ** 041 OM  20240131          Fixed flaw in DBCODEPAGE / DBCOLLATION implementation.
    ** 042 RAA 20230601          Added supportsValidateMode function.
    **     RAA 20230619          Overrided getCreateIndexString function that deals with SOFT_UNIQUE indexes.
    **     RAA 20230704          Changed name of validateMode to allowDBUniqueCheck.
    ** 043 RAA 20240319          Added useNullEquality function.
    ** 044 AL2 20240322          Disable useNullEquality function due to regressions on validation.
    ** 045 DDF 20240612          Added getAddForeignKeyConstraintString(). ALTER TABLE should be transactional
    **                           as to not commit the session and release all H2 savepoints internally.
    **     DDF 20240620          Add the referred column besides the table name for REFERENCES.
    ** 046 OM  20240718          Fixed the list of columns in foreign key when creating FK constraints.
    ** 047 AS  20240903          Increased the precision for decimal fields to 60.
    ** 048 OM  20240909          Improved Database API. Concurrent multitenancy functionality implementation.
    ** 049 AL2 20240812          Fixed infinite loop and ClassCastException in isUniqueConstraintViolation.
    ** 050 OM  20250117          Added support for multiple dialects for multi-tenant landlord database.
    ** 051 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 java.io.*;
    import java.net.InetAddress;
    import java.net.URI;
    import java.sql.*;
    import java.text.NumberFormat;
    import java.util.*;
    import java.util.function.*;
    import java.util.logging.*;
    import java.util.regex.*;
    import java.util.stream.*;
    import org.apache.commons.lang.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.deploy.*;
    import com.goldencode.p2j.persist.h2.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.sequence.*;
    import com.goldencode.p2j.schema.*;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.ErrorManager.*;
    import com.goldencode.p2j.util.logging.*;

    import static com.goldencode.p2j.persist.dialect.DialectHelper.*;

    /**
     * Extends abstract {@link Dialect} for an H2 backend.
     */
    // TODO: implement base dialect features we need
    public class P2JH2Dialect
    extends Dialect
    {
       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(P2JH2Dialect.class.getName());

       /** Name of the primary key column. */
       private static final String PK = Configuration.getSchemaConfig().getPrimaryKeyName();

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

       /** Weights' aggregator */
       private static final WeightAggregator WEIGHT_AGGREGATOR = new SumAggregator();

       /** Regex Pattern for parsing message cased by error raised by UDF. */
       public static final Pattern UDF_ERROR_MESSAGE = Pattern.compile(
                "^Exception calling user-defined function: .*: (\\*\\* )?(.*)(\\. *)?\\(([0-9]*)\\).*$",
                Pattern.DOTALL
       );

       /* Initialization of static data. */
       static
       {
          fwd2sql.put("integer",    "integer");
          fwd2sql.put("Integer",    "integer");
          fwd2sql.put("recid",      "integer");
          fwd2sql.put("int64",      "bigint");
          fwd2sql.put("Long",       "bigint");
          fwd2sql.put("rowid",      "bigint");
          fwd2sql.put("decimal",    "numeric(60," + SCALE_TOK + ")"); // TODO: set precision?
          fwd2sql.put("logical",    "boolean");
          fwd2sql.put("character",  "varchar" + CASE_TYPE_TOK);
          fwd2sql.put("String",     "varchar");
          fwd2sql.put("handle",     "bigint");
          fwd2sql.put("comhandle",  "varchar");
          fwd2sql.put("date",       "date");
          fwd2sql.put("datetime",   "timestamp");
          fwd2sql.put("datetimetz", "timestamp with time zone");
          fwd2sql.put("blob",       "blob");
          fwd2sql.put("clob",       "clob");
          fwd2sql.put("object",     "bigint");
          fwd2sql.put("raw",        "binary");
       }

       /** AFTER INSERT trigger for words tables' support template */
       private static final List<String> AFTER_INSERT_TRIGGER = Collections.unmodifiableList(
             Arrays.asList(
                   "CREATE TRIGGER %s AFTER INSERT ON %s FOR EACH ROW AS",
                   "$$org.h2.api.Trigger create() { return new %s(\"%s;%s;%s;%s;%d\"); } $$;"
             )
       );

       /** AFTER UPDATE trigger for words tables' support template */
       private static final List<String> AFTER_UPDATE_TRIGGER = Collections.unmodifiableList(
             Arrays.asList(
                   "CREATE TRIGGER %s AFTER UPDATE ON %s FOR EACH ROW AS",
                   "$$org.h2.api.Trigger create() { return new %s(\"%s;%s;%s;%s;%d\"); } $$;"
             )
       );
       /** DROP TRIGGER statement */
       private static final String DROP_TRIGGER = "drop trigger if exists %s;";

    //   /** Actual minimum interval in milliseconds between analyze commands */
    //   private static final long analyzeInterval = 60000;

    //   /** Debug level logging enabled? */
    //   private static final boolean debug = LOG.isDebugEnabled();

       /**
        * Default constructor.  This constructor is for Hibernate's use.  Use code
        * should not construct instances of this class.
        * <p>
        * We provide this constructor to override the superclass' mapping of
        * <code>Types.VARCHAR</code>, in order to force this type to map to
        * PostgreSQL's <code>text</code> data type.
        */
       public P2JH2Dialect()
       {
          super();

          // Override superclass' VARCHAR and VARBINARY mappings, since we always
          // use the unbounded variants of these data types.
    //      registerColumnType(Types.VARCHAR, "varchar");
    //      registerColumnType(Types.VARBINARY, "binary");

          // Override superclass' NUMERIC mapping, since the Hibernate H2 dialect
          // doesn't honor precision and scale.
    //      registerColumnType(Types.NUMERIC, "numeric(50,$s)");
       }

       /**
        * Get a collection of reserved keywords specific to this dialect.
        *
        * 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 H2 1.3.176 reserved keywords.
        *
        * @see     <a href="http://www.h2database.com/html/advanced.html#compatibility">
        *          http://www.h2database.com/html/advanced.html#compatibility</a>
        */
       public static List<String> getReservedKeywords()
       {
          String[] keywords =
          {
             "all",
             "array",
             "case",
             "check",
             "constraint",
             "cross",
             "current_catalog",
             "current_date",
             "current_schema",
             "current_time",
             "current_timestamp",
             "current_user",
             "currval",
             "distinct",
             "except",
             "exists",
             "false",
             "fetch",
             "for",
             "foreign",
             "from",
             "full",
             "group",
             "having",
             "if",
             "inner",
             "intersect",
             "intersects",
             "interval",
             "is",
             "join",
             "left",
             "like",
             "limit",
             "localtime",
             "localtimestamp",
             "minus",
             "natural",
             "not",
             "null",
             "offset",
             "on",
             "order",
             "primary",
             "qualify",
             "right",
             "row",
             "rownum",
             "select",
             "sysdate",
             "systime",
             "systimestamp",
             "table",
             "today",
             "true",
             "union",
             "unique",
             "unknown",
             "using",
             "values",
             "validate",
             "where",
             "window",
             "with",
             "_rowid_"
          };

          return Arrays.asList(keywords);
       }

       /**
        * Get the name of the JDBC driver class associated with this dialect.
        *
        * @return  Fully qualified driver class name.
        */
       @Override
       public String getDriverClassName()
       {
          return "org.h2.Driver";
       }

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

       /**
        * Indicate whether this dialect requires computed columns to participate
        * within index definitions, instead of embedded expressions.
        *
        * @return  <code>true</code> to indicate computed columns are required.
        */
       @Override
       public boolean needsComputedColumns()
       {
          return false;
       }

       /**
        * Indicate whether this dialect natively supports the overloading of user
        * defined functions by parameter number and data type.
        *
        * @return  <code>false</code>.
        */
       @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  Always {@code true}, all the above mentioned items are supported.
        */
       @Override
       public boolean supportsBooleanDatatype()
       {
          return true;
       }

       /**
        * Create a data object, the purpose of which is specific to a particular
        * dialect implementation, to be stored as a context-local object.  This
        * allows a dialect to establish context-local data which is stored by the
        * persistence runtime framework, without the overhead of a separate,
        * context-local variable.  This object will be handed back to the dialect
        * in callback methods when the services of certain hooks are required.
        * <p>
        * Since the context-local data is specific to a particular dialect
        * implementation, it is optional.  Dialect implementations which do not
        * have a need for this data should simply return <code>null</code>.
        *
        * @param   database
        *          The database of the persistence services.
        *
        * @return  Dialect-specific, context-local data, or <code>null</code>.
        */
       @Override
       public Object createContext(Database database)
       {
          //return (analyzeInterval > 0 && database.isTemporary()) ? new Context() : null;

          return null;
       }

       /**
        * A dialect-specific callback hook which is invoked by the persistence
        * runtime framework after a transaction is complete.  The Hibernate
        * session will have been closed by the time this method is invoked.
        *
        * @param   persistence
        *          Provider of persistence services.
        * @param   context
        *          Optional, context-local data created previously by {@link
        *          #createContext}.
        * @param   rollback
        *          <code>true</code> if transaction ended with a rollback;
        *          <code>false</code> if transaction ended with a commit.
        *
        * @throws  PersistenceException
        *          if any persistence-related error occurs.
        */
       @Override
       public void postTransaction(Persistence persistence, Object context, boolean rollback)
       throws PersistenceException
       {
          /*
          if (context != null)
          {
             Context local = (Context) context;
             long now = System.currentTimeMillis();
             long diff = now - local.lastAnalyze;
             if (diff >= analyzeInterval)
             {
                int count = 0;
                try
                {
                   Iterator<String> iter = TemporaryBuffer.activeTables();
                   while (iter.hasNext())
                   {
                      iter.next();
                      count++;
                   }
                   if (count > 0)
                   {
                      persistence.executeSQL("analyze");
                   }
                }
                finally
                {
                   long end = System.currentTimeMillis();
                   long elapsed =  end - now;
                   local.analyzeTime += elapsed;
                   if (debug && count > 0)
                   {
                      StringBuilder buf = new StringBuilder("Analyze ");
                      buf.append(count);
                      buf.append(" temp table(s) [");
                      buf.append(diff);
                      buf.append(":");
                      buf.append(elapsed);
                      buf.append(":");
                      buf.append(local.analyzeTime);
                      buf.append("]");
                      LOG.debug(persistence.message(buf.toString()));
                   }
                   local.lastAnalyze = end;
                }
             }
          }
          */
       }

       /**
        * Format a date object as a string which is appropriate for use in an SQL
        * statement.
        *
        * @param   d
        *          Date to be formatted as a string.
        *
        * @return  Formatted representation of the given date.
        */
       @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)));
          buf.append('-');

          nf.setMinimumIntegerDigits(2);
          buf.append(nf.format(d.getMonth()));
          buf.append('-');
          buf.append(nf.format(d.getDay()));

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

          return buf.toString();
       }

       /**
        * Given a native column name as queried from database metadata, extract the root name of a
        * column. For example, a column name as found within an index in metadata might be represented
        * as:
        * <pre>
        *    __my_column
        * </pre>
        * We are interested only in the {@code my_column} root name, so we strip away the leading
        * {@code __} prefix.
        *
        * @param   expr
        *          Column name as natively represented by the database dialect.
        *
        * @return  Root column name.
        */
       @Override
       public String extractColumnName(String expr)
       {
          if (expr.startsWith(INSENSITIVE_CHAR_FIELD))
          {
             return expr.substring(INSENSITIVE_CHAR_FIELD.length());
          }
          else if (expr.startsWith(SENSITIVE_CHAR_FIELD))
          {
             return expr.substring(SENSITIVE_CHAR_FIELD.length());
          }
          else if (expr.startsWith(CC_PREFIX))
          {
             // has the computed column marker but not case-sensitivity
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.log(Level.WARNING, "Invalid column name: [" + expr + "]." +
                            "Failed to extract the root name of the column.");
             }
          }
          return expr;
       }

       /**
        * Adjust a character column name appropriate to this dialect, such that it
        * can be inserted into an index.  This involves prepending a special
        * prefix to designate a computed column.
        *
        * @param   name
        *          Base column name.
        * @param   ignoreCase
        *          <code>true</code> to cause case to be ignored, else <code>false</code>.
        *
        * @return  Processed column name.
        */
       @Override
       public String getProcessedCharacterColumnName(String name, boolean ignoreCase)
       {
          if (!needsComputedColumns())
          {
             return name;
          }

          return DBUtils.getPrefixedParameter(
             name,
             ignoreCase ? INSENSITIVE_CHAR_FIELD : SENSITIVE_CHAR_FIELD);
       }

       /**
        * Report whether the given column name represents an alias or an expression which indicates
        * the column is case insensitive.  Assume 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</code> if <code>name</code> represents case-insensitive data.
        */
       @Override
       public boolean isCaseInsensitiveColumn(String name)
       {
          return true;
       }

       /**
        * Generate a DDL string which will create a global sequence.
        *
        * @param   name
        *          Sequence name.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        */
       @Override
       public String getCreateSequenceString(String name)
       {
          return "create sequence " + name;
       }

       /**
        * Generate a DDL string which will create a global sequence.
        *
        * @param   name
        *          Sequence name.
        * @param   isTemporary
        *          If the sequence should be temporary or not.
        *
        * @return  Dialect-specific string to create the sequence, or {@code null} if not supported.
        */
       @Override
       public String getCreateSequenceString(String name, boolean isTemporary)
       {
          if (isTemporary)
          {
             return getCreateSequenceString(name) + " temporary";
          }
          return getCreateSequenceString(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.
        */
       @Override
       public String getCreateSequenceString(String name, long start)
       {
          return getCreateSequenceString(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</code> if
        *          not supported.
        */
       @Override
       public String getCreateSequenceString(String name,
                                             Long init,
                                             Long increment,
                                             Long minVal,
                                             Long maxVal,
                                             Boolean cycle)
       {
          // H2 create sequence syntax:
          // CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName
          //       [ START WITH long ]
          //       [ INCREMENT BY long ]
          //       [ CACHE long ]
          // H2 (at least in version 1.3.169) does not support min/max values
          //    nor cycle sequences, those features will be emulated by p2j

          return getCreateSequenceString(name, init) + " increment by " + increment + " cache 1";
       }

       /**
        * 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.
        */
       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 query the sequence, or
        *          <code>null</code> if not supported.
        */
       @Override
       public String getSequenceSetValString(String sequenceName, long newVal)
       {
          return "alter sequence " + sequenceName + " restart with " + newVal;
       }

       /**
        * Generate the appropriate select statement to reset the value of a
        * sequence.
        *
        * @param   sequenceName
        *          The name of the sequence.
        *
        * @return  Dialect-specific string to query the sequence, or
        *          <code>null</code> if not supported.
        */
       @Override
       public String getSequenceSetValString(String sequenceName)
       {
          return "alter sequence " + sequenceName + " restart with ?";
       }

       /**
        * Generate the appropriate select statement to reset the value of a
        * sequence.
        *
        * @return  Dialect-specific string to query the sequence, or
        *          <code>null</code> if not supported.
        */
       @Override
       public String getSequenceSetValString()
       {
          throw new IllegalStateException("Can't reset value of a sequence with prepared statement in H2");
       }

       /**
        * 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</code> if not supported.
        */
       @Override
       public String getSequenceCurrValString(String sequenceName)
       {
          // this feature is not supported by H2 directly, use a workaround
          return "select current_value from information_schema.sequences " +
                 "where upper(sequence_name) = '" + sequenceName.toUpperCase() + "'";
       }

       /**
        * Generate the appropriate select statement to retrieve the current
        * value of a sequence.
        *
        * @return  Dialect-specific string to query the sequence, or
        *          <code>null</code> if not supported.
        */
       @Override
       public String getSequenceCurrValString()
       {
          // this feature is not supported by H2 directly, use a workaround
          return "select current_value from information_schema.sequences " +
                 "where upper(sequence_name) = ?";
       }

       /**
        * Returns <code>true</code> 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 false as H2 dialect does not fully support 4GL sequences
        */
       @Override
       public boolean hasFullSequenceSupport()
       {
          return false;
       }

       /**
        * 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  Dialect-specific SQL expression, or {@code null} if this feature is not supported
        *          by the dialect.
        */
       @Override
       public String getSequencePrefetchString(String sequenceName)
       {
          return "select next value for " + sequenceName + " from system_range(?, ?)";
       }

       /**
        * 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.
        */
       @Override
       public String getSequenceNextValString(String sequenceName)
       {
          return "call next value for " + sequenceName;
       }

       /**
        * Given an exception, this method will check if it indicates a database
        * connectivity error.
        * <p>
        * Currently, as the H2 database is used only for in-memory temp-tables,
        * this method returns false.
        *
        * @param   exc
        *          The exception to be checked.
        *
        * @return  <code>true</code> if the exception indicates a database
        *          connectivity error.
        */
       @Override
       public boolean isConnectionError(Exception exc)
       {
          // TODO:  implement.
          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 false;
       }

       /**
        * 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.  This implementation
        * does nothing.
        *
        * @param   on
        *          <code>true</code> to enable inlining;  <code>false</code> to
        *          disable it.
        */
       @Override
       public void inlineLimit(boolean on)
       {
       }

       /**
        * Determines whether substitution parameters should be explicitly casted
        * to their datatypes inside "then ? else ?" statement.
        *
        * @return <code>true</code>.
        */
       @Override
       public boolean requiresExplicitCastInsideTernary()
       {
          return true;
       }

       /**
        * Check if bind parameters in recursive CTEs are supported.
        *
        * @return <code>true</code> if bind parameters in recursive CTEs are supported.
        */
       public boolean allowBindParamsInRecursiveCTE()
       {
          return false;
       }

    //   /**
    //    * Context local data needed by this object to perform dialect-specific
    //    * processing.  In this case, we track the last time a temp table analyze
    //    * was performed and how much accumulated time we have spent performing
    //    * such analyze commands in this context.
    //    */
    //   private static class Context
    //   {
    //      /** Time of last temp table analyze */
    //      private long lastAnalyze = System.currentTimeMillis();
    //
    //      /** Accumulated time spent analyzing temp tables */
    //      private long analyzeTime = 0L;
    //   }

       /**
        * 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.
       */
       @Override
       public String getDropIndexString(P2JIndex index)
       {
          return "drop index if exists " + quote(getSqlIndexName(index));
       }

       /**
        * Return a string which marks that a column is computed, so it should be marked as invisible
        * to allow implicit skipping of it when using wildcard selection (*) or direct access.
        *
        * @return   An SQL string specificator for invisible columns. {@code null} means that no such
        *           specificator is supported.
        */
       public String getInvisibleSpecificator()
       {
          return " invisible";
       }

       /**
        * Prepare the given metadata query parameter by making it uppercase.
        *
        * @param   param
        *          The metadata query parameter.
        *
        * @return  the uppercased parameter.
        */
       @Override
       public String prepareMetadataParameter(String param)
       {
          return param == null ? null : param.toUpperCase();
       }

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

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

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

       /**
        * Get a list of database prepare DDL statements.
        *
        * @param    database
        *           The database which needs to be prepared.
        *
        * @return   The statement list.
        */
       @Override
       public List<String> getDatabasePrepareStatements(Database database)
       {
          return H2Helper.getPrepareStatements(database);
       }

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

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

       /**
        * Set any database parameters before, to be available when the database
        * driver is loaded.
        * <p>
        * The "h2.sortNullsHigh" setting was removed, as it was determined that
        * by forcing the H2 to sort nulls high, it causes a regression in a test
        * where 4GL seems to sort the unknown value at the end of the result set,
        * when an descending sort clause was used (instead of sorting it at the
        * begining of the result set).
        * <p>
        * TODO: more testing needs to be done to determine exactly how 4GL sorts
        * the unknown value in the temporary database (and permanent DB too). At
        * the time of this comment, H2 has a bug which doesn't enforce the "sort
        * nulls high" setting in cases when the query's result order is determined
        * by the index.
        */
       @Override
       public void beforeDriverLoad()
       {
           // System.setProperty("h2.sortNullsHigh", "true");
       }

       /**
        * 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. If this indicates an embedded
        *           database, a mixed mode URL which will allow secure (i.e., SSL), remote connections
        *           to that database is generated. The mixed mode port is fixed.
        * @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
       {
          // save the original URL
          String origUrl = url;

          url = url.substring("jdbc:".length());

          try
          {
             // save the connection mode
             String mode = url.substring(0, "h2:tcp:".length());

             // if mode is neither TCP nor SSL, we have an embedded database running in mixed mode
             boolean mixedMode = !("h2:tcp:".equals(mode) || "h2:ssl:".equals(mode));

             // handle network and embedded URLs differently, but in both cases, temporarily drop
             // mode so that we can operate on a valid URI below
             if (mixedMode)
             {
                // embedded URL; temporarily insert localhost address after mode, which will force
                // replacement with remote host name below
                url = url.substring("h2:".length());
                url = "h2://localhost/" + url;
                mode = "h2:ssl:";
             }
             else
             {
                // network URL; get rid of the "h2:tcp:" or "h2:ssl:" text
                url = url.substring("h2:tcp:".length());
                url = "h2:" + url;
             }

             URI uri = new URI(url).normalize();

             // Check for localhost references.
             String host = uri.getHost().toLowerCase();
             if (InetAddress.getByName(host).isLoopbackAddress())
             {
                // Use hostname stored in database object.
                host = database.getSocketAddress().getHostName();

                // Generate new URI.
                uri = new URI(uri.getScheme(),
                              uri.getUserInfo(),
                              host,
                              mixedMode ? DatabaseManager.MIXED_MODE_SSL_PORT : uri.getPort(),
                              uri.getPath(),
                              uri.getQuery(),
                              uri.getFragment());

                // Prepend 'jdbc:' prefix.
                url = "jdbc:" + mode + uri.toString().substring("h2:".length());
             }
             else
             {
                // no preparation is needed
                url = origUrl;
             }
          }
          catch (Exception exc)
          {
             throw new PersistenceException("Invalid database connection URL", exc);
          }

          return url;
       }

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

       /**
        * Report whether the computed columns should be injected on table creating.
        *
        * @return  <code>true</code> for H2 database.
        */
       @Override
       public boolean injectComputedColumns()
       {
          return false;
       }

       /**
        * Checks if the SQLState of the SQLException matches the dialect specific UNIQUE_VIOLATION
        * code for H2 (23505). It unwraps the SQLException until an SQLState matching the code is
        * found, or there is no exception to unwrap anymore.
        *
        * @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)
       {
          Throwable ex = sqle;
          String sqleState;
          while (ex != null)
          {
             if (ex instanceof SQLException)
             {
                sqleState = ((SQLException) ex).getSQLState();
                if ("23505".equals(sqleState))
                {
                   return true;
                }
             }

             ex = ex.getCause();

             // avoid infinite loop by continious unwrapping of the cause
             if (ex == ex.getCause())
             {
                return false;
             }
          }
          return false;
       }

       /**
        * Some dialects do have constraints about the maximum size of fields that compose an index.
        * This method reports the dialect-specific index length limit, if any.
        *
        * @return  always 0 as this dialect do not have any known constraints about the maximum size
        *          of the fields of an index.
        */
       @Override
       public int getIndexLengthLimit()
       {
          return 0;
       }

       /**
        * Returns the size of the synthetic part of the index. At this moment only the <code>id</code>
        * 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</code>. The synthetic components may differ
        * between unique and non-unique indexes.
        * <p>
        * See {@link #getCreateIndexString(P2JIndex, boolean, boolean)}
        *
        * @param   unique
        *          <code>true</code> if the index is unique
        *
        * @return  0 if index is unique, 8 = size of an pkid (bigint)
        */
       public int getSyntheticIndexSize(boolean unique)
       {
          return unique ? 0 : 8;
       }

       /**
        * Composes a rtrim equivalent call for a specified expression. This is dialect specific.
        * The returned value looks like:
        * <pre>
        *    rtrim(expr)
        * </pre>
        * The rtrimmed expression is appended to the string builder.
        *
        * @param   expr
        *          The expression to be 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 filed to.
        */
       @Override
       public void addRtrimmedExpression(String expr, StringBuilder sb)
       {
          sb.append("rtrim(").append(expr).append(")");
       }

       /**
        * Obtain the number of parameters a query of this dialect is allowed by the SQL server.
        *
        * @return  always 0 because H2 does no have such limitation.
        */
       @Override
       public int getMaxParameterCount()
       {
          return 0;
       }

       /**
        * Return the beginning of the DDL statement that creates a temporary table.
        *
        * @param   ifNotExist
        *          If {@code true} an 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.
        */
       @Override
       public String getCreateTemporaryTableString(boolean ifNotExist) {
          return ifNotExist ? "create local temporary table if not exists "
                            : "create local temporary table ";
       }

       /**
        * Return the ending of the DDL statement that creates a temporary table.
        *
        * @param   noUndo
        *          Parameter that indicates what keyword should be returned. The choices are "transactional"
        *          and "noundo".
        *
        * @return  A string representing the ending of the DDL statement that creates a temporary
        *          table.
        */
       @Override
       public String getCreateTemporaryTablePostfix(boolean noUndo)
       {
          if (!noUndo)
          {
             return " transactional";
          }

          return " noundo";
       }

       /**
        * 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.
        */
       @Override
       public String getDropIndexString(boolean ifExists, String idxName, String tableName)
       {
          return (ifExists ? "drop index if exists " : "drop index ") + idxName;
       }

       /**
        * 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.
        */
       @Override
       public String getDropTableString(boolean ifExists, String tableName)
       {
          return (ifExists ? "drop table if exists " : "drop table ") + tableName + " cascade";
       }

       /**
        * 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 "long";
             case Types.NUMERIC: return "numeric";
             case Types.DOUBLE: return "double";
             case Types.DATE: return "date";
             case Types.VARBINARY: return "binary";
             case Types.TIMESTAMP: return "timestamp";
             case Types.TIMESTAMP_WITH_TIMEZONE: return "timestamp with time zone";
             case Types.ARRAY: return "array";
             case Types.BLOB: return "blob";
             case Types.VARCHAR: return "varchar";
             case Types.CLOB: return "clob";
             default: return type.toString(); // maybe log this?
          }
       }

       /**
        * 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)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.log(Level.WARNING, "Unknown '" + fwdType + "' type for H2 dialect.");
             }
          }
          else
          {
             if (caseSensitive != null)
             {
                String tmp = caseSensitive ? "_casesensitive" : "_ignorecase";
                s = s.replace(CASE_TYPE_TOK, tmp);
             }
             else
             {
                int i = s.indexOf(CASE_TYPE_TOK);
                if (i > 0)
                {
                   s = s.substring(0, i);
                }
             }

             int k = s.indexOf(SCALE_TOK);
             if (k > 0)
             {
                s = s.substring(0, k) + scale + s.substring(k + SCALE_TOK.length());
             }
          }
          return s;
       }

       /**
        * 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.
        */
       @Override
       public String getAnalyzeString(String table)
       {
          return table == null ? "analyze" : "analyze table " + table;
       }

       /**
        * Determine if the sort component or index needs to be augmented with a NULLS FIRST/LAST option and
        * add the required expression.
        *
        * @param   sb
        *          The {@code StringBuilder} which contains the SQL statement to be built.
        * @param   expression
        *          The field name (or expression) which is the current criterion to be augmented.
        * @param   field
        *          The field name which is the base of the previous parameter (unused here).
        * @param   asc
        *          The sort direction.
        * @param   forceAsc
        *          If {@code true}, force the insertion of the default {@code asc} direction.
        *
        * @return  1, representing the number of expressions which were added to the string builder.
        */
       @Override
       public int orderByNulls(StringBuilder sb, String expression, String field, boolean asc, boolean forceAsc)
       {
          super.orderByNulls(sb, expression, null, asc, forceAsc);
          sb.append(asc ? " nulls last" : " nulls first");
          return 1;
       }

       /**
        * Get the splitter for DDL scripts.
        * @return the splitter for DDL scripts
        */
       @Override
       public ScriptSplitter scriptSplitter()
       {
          throw new UnsupportedOperationException();
       }

       /**
        * Get a ScriptRunner instance for the dialect.
        *
        * @return a ScriptRunner instance for the dialect.
        *
        */
       @Override
       public ScriptRunner getScriptRunner()
       {
          return new H2ScriptRunner(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);
          }
       }

       /**
        * Check if UDF should be used for converted CONTAINS operator
        *
        * @return  <code>true</code> if UDF should be used for converted CONTAINS operator.
        */
       @Override
       public boolean useUdf4Contains()
       {
          return true;
       }

       /**
        * Check if word tables should be used for converted CONTAINS operator
        *
        * @return <code>true</code> 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</code> if native UDFs are supported for the dialect
        */
       public boolean isNativeUDFsSupported()
       {
          return false;
       }

       /**
        * Check if UDF should be used for converted CONTAINS operator
        *
        * @return <code>true</code> if UDF should be used for converted CONTAINS operator
        */
       @Override
       public boolean isUdfOriginatedError(SQLException e)
       {
          return "90105".equals(e.getSQLState());
       }

       /**
        * Check if the exception was caused by a warning in UDF.
        * @param e
        *        the exception in question
        * @return <code>true</code> if the exception was caused by error in UDF.
        */
       @Override
       public boolean isUdfOriginatedWarning(SQLException e)
       {
          return "90105".equals(e.getSQLState());
       }
       /**
        * Create the error descriptor from the SQLException message
        *
        * @param message
        *        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)), // was 4
                         matcher.group(2), !StringUtils.isBlank(matcher.group(1)));
             }
             catch (NumberFormatException e)
             {
                // no-op
             }
          }

          return new ErrorEntry(-1, message, false);
       }
       /**
        * Check if Common Table Expressions should be used for converted CONTAINS operator
        *
        * @return <code>true</code> if Common Table Expressions should be used for converted CONTAINS operator
        */
       public boolean useCTE4Contains()
       {
          return true;
       }

       /**
        * Check if mixed mode re-write should be used for converted CONTAINS operator
        *
        * @return <code>true</code> if mixed mode re-write should be used
        */
       public boolean useMixedNode4Contains()
       {
          return true;
       }

       /**
        * Check if the provided name is the name of the CONTAINS UDF
        * @param name
        *        name to be checked
        * @return <code>true</code> if the provided name is the name of the CONTAINS UDF
        */
       public boolean isUdfContains(String name)
       {
          return "contains_1".equals(name) || "contains_2".equals(name);
       }

       /**
        * Return the name of the CONTAINS UDF
        * @return the name of the CONTAINS UDF
        */
       public String containsUdfName()
       {
          return "contains_1";
       }

       /**
        * Get the WeightAggregator instance for the dialect if defined
        * @return the WeightAggregator instance for the dialect if defined
        */
       @Override
       public Optional<WeightAggregator> weightAggregator()
       {
          return Optional.ofNullable(WEIGHT_AGGREGATOR);
       }

       /**
        * 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
        *          word tables by name
        */
       @Override
       public void generateWordTablesDDLImpl(String dbName,
                                             PrintStream out,
                                             String eoln,
                                             Collection<WordTable> wordTables)
       {
          boolean isTemp = SchemaDictionary.TEMP_TABLE_DB.equals(dbName);
          String afterInsertTrigger = String.join(eoln, AFTER_INSERT_TRIGGER);
          String afterUpdateTrigger = String.join(eoln, AFTER_UPDATE_TRIGGER);
          wordTables.forEach(wt -> {
             if (!isTemp)
             {
                out.printf(DROP_TRIGGER, wt.afterInsertTriggerName);
                out.print(eoln);
                out.print(eoln);
             }
             if (wt.customExtentFields == null)
             {
                out.printf(afterInsertTrigger,
                      wt.afterInsertTriggerName,  wt.parentTableName,
                      (wt.extent == 0 ? OnInsertWords.class : OnInsertWordsExt.class).getName(),
                      PK, wt.fieldName, wt.tableName, wt.caseSensitive, 0);
             }
             else
             {
                String flist = wt.customExtentFields.stream().
                      map(f -> f.fieldName).collect(Collectors.joining(","));
                out.printf(afterInsertTrigger,
                      wt.afterInsertTriggerName,  wt.parentTableName,
                      OnInsertWordsCExt.class.getName(),
                      PK, flist, wt.tableName, wt.caseSensitive, 0);
             }
             out.print(eoln);
             out.print(eoln);
             if (wt.customExtentFields == null)
             {
                if (!isTemp)
                {
                   out.printf(DROP_TRIGGER, wt.afterUpdateTriggerName);
                   out.print(eoln);
                   out.print(eoln);
                }
                out.printf(afterUpdateTrigger,
                      wt.afterUpdateTriggerName,  wt.parentTableName,
                      (wt.extent == 0 ? OnUpdateWords.class : OnUpdateWordsExt.class).getName(),
                      PK, wt.fieldName, wt.tableName, wt.caseSensitive, 0);
                out.print(eoln);
                out.print(eoln);
             }
             else
             {
                for (CustomExtentField f : wt.customExtentFields)
                {
                   if (!isTemp)
                   {
                      out.printf(DROP_TRIGGER, wt.afterUpdateTriggerName);
                      out.print(eoln);
                      out.print(eoln);
                   }
                   out.printf(afterUpdateTrigger,
                         f.afterUpdateTriggerName,  wt.parentTableName,
                         OnUpdateWordsCExt.class.getName(),
                         PK, f.fieldName, wt.tableName, wt.caseSensitive, f.pos);
                   out.print(eoln);
                   out.print(eoln);
                }
             }
          });
       }

       /**
        * Determine if this {@code Dialect} supports queries executed in a lazy manner.
        *
        * @return  {@code true}
        *          H2 does support lazy queries.
        */
       @Override
       public boolean supportsLazyQueryResultsMode()
       {
          return true;
       }

       /**
        * Determine if the {@code Dialect} supports index validation (with the VALIDATE keyword).
        *
        * @return  {@code true}
        *          H2 supports this type of validation.
        */
       @Override
       public boolean allowDBUniqueCheck()
       {
          return true;
       }

       /**
        * Returns the beginning of the DDL statement for creating an index.
        *
        * @param   unique
        *          {@code true} for unique indexes.
        * @param   softUnique
        *          {@code true} for soft-unique indexes.
        *
        * @return  the beginning of the DDL statement for creating an index.
        */
       @Override
       public String getCreateIndexString(boolean unique, boolean softUnique)
       {
          if (unique)
          {
             return "create unique index ";
          }

          if (softUnique)
          {
             return "create soft_unique index ";
          }

          return "create index ";
       }

       /**
        * Get dialect id.
        *
        * @return  Dialect id.
        */
       @Override
       public String id()
       {
          return "h2";
       }

       /**
        * 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(32767) NOT NULL,\n" +
                       "       tenant_description VARCHAR(32767),\n" +
                       "       tenant_info        VARCHAR(32767),\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)";
             }
          };
       }

       /**
        * Helper interface for weights' aggregation
        */
       public static class SumAggregator
       implements WeightAggregator
       {
          /**
           * Get an aggregate wrapper for converted converted CONTAINS operator.
           *
           * @return an aggregate wrapper for converted converted CONTAINS operator
           */
          public BiConsumer<StringBuilder, Runnable> aggregate()
          {
             return (sb, r) -> {sb.append("sum(cast("); r.run(); sb.append(" as int))");};
          }

          /**
           * Get the minimal value of the aggregator.
           *
           * @return  the minimal value of the aggregator.
           */
          public String minAggregatedValue()
          {
             return "-1";
          }
       }

       /**
        * 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 null equality.
        * This is visible when doing {@code null == null}, which logically should return {@code true},
        * but not all dialects support this condition.
        *
        * @return  {@code true} if the dialect permits null equality.
        */
       public boolean useNullEquality()
       {
          return false;
       }

       /**
        * 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;
       }

       /**
        * 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
       {
          H2Helper.preparePermanentDatabase(db);
       }

       /**
        * Obtain the name of an index. This dialect overrides this method in order to make sure the name is unique
        * among tables because in H2 the index names must be unique.
        *
        * @param   index
        *          The index structure.
        *
        * @return  the unique SQL name of the index.
        */
       @Override
       public String getSqlIndexName(P2JIndex index)
       {
          return "idx__" + index.getTable() + "_" + index.getName();
       }

       /**
        * 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 transactional";
       }

       /**
        * Check if this dialect allows the selection of fields using * wildcard. Also, quantification should
        * also be supported if this is allowed (alias.*).
        *
        * @return   {@code true} if we can use * as selection wildcard.
        */
       public boolean allowSelectWildcard()
       {
          return true;
       }

       /**
        * 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
          {
             ResultSet rs = conn.prepareStatement(
                   "SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME " +
                   "FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='PUBLIC'"
             ).executeQuery();

             if (rs.next())
             {
                String collName = rs.getString(2); // DEFAULT_COLLATION_NAME
                if ("en_US_P2J".equals(collName))
                {
                   return "ISO8859-1"; // FWD 3.0 legacy default
                }
                int i = collName.indexOf("_fwd_");
                if (i > 0)
                {
                   collName = collName.substring(0, i);
                   i = collName.lastIndexOf('_');
                   if (i > 0)
                   {
                      return mapCP(collName.substring(i + 1));
                   }
                }
                return mapCP(rs.getString(1)); // DEFAULT_CHARACTER_SET_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
          {
             ResultSet rs = conn.prepareStatement(
                   "SELECT DEFAULT_COLLATION_NAME " +
                   "FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='PUBLIC'"
             ).executeQuery();

             if (rs.next())
             {
                String collName = rs.getString(1); // DEFAULT_COLLATION_NAME
                if ("en_US_P2J".equals(collName))
                {
                   return "basic"; // FWD 3.0 legacy default
                }
                int i = collName.indexOf("_fwd_");
                if (i > 0)
                {
                   return collName.substring(i + 5 /* "_fwd_".length */);
                }
                return collName;
             }
          }
          catch (SQLException e)
          {
             if (LOG.isLoggable(Level.WARNING))
             {
                LOG.warning("Failed to retrieve COLLATION from database.", e);
             }
          }

          // fallback for all errors
          return null;
       }

       /**
        * Maps the H2 specific charset names to 4GL codepage names.
        *
        * @param   h2Charset
        *          A H2 specific charset name.
        *
        * @return  The 4GL codepage name of {@code h2Charset}.
        */
       private static String mapCP(String h2Charset)
       {
          if (h2Charset == null)
          {
             return null;
          }

          switch (h2Charset.toUpperCase())
          {
             case "CP1251":    return "1251";
             case "CP1252":    return "1252";
             case "CP1257":    return "1257";
             case "ISO88591":  return "ISO8859-1";
             case "ISO885915": return "ISO8859-15";
          // case "IBM850":    return "IBM850";
             default:          return h2Charset;
          }
       }
    }