# Dialect.java

    /*
    ** Module   : Dialect.java
    ** Abstract : API to handle database dialect differences
    **
    ** Copyright (c) 2004-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
    ** 001 ECF 20060929   @30033 Created initial version. Extends Hibernate's
    **                           Dialect API to add methods required
    **                           specifically for P2J issues.
    ** 002 ECF 20070514   @33484 Added extractColumnName(). Extracts a root
    **                           column name from its native representation in
    **                           a particular dialect.
    ** 003 ECF 20071002   @35333 Added hooks to enable post-transaction
    **                           callback processing.
    ** 004 ECF 20080310   @37517 Added support for computed columns and user
    **                           defined function overloading.
    ** 005 ECF 20080611   @38700 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.
    ** 006 ECF 20080805   @39314 Added getCreateSequenceString(). Generates
    **                           DDL to create a sequence starting at a
    **                           particular value.
    ** 007 CA  20080815   @39442 Added isConnectionError method - it checks if
    **                           a given exception represents a connection
    **                           error.
    ** 008 ECF 20080930   @40000 Added isQueryRangeParameterInlined().
    **                           Indicates whether query substitution
    **                           parameters within range checks should be
    **                           inlined.
    ** 009 ECF 20081015   @40095 Added formatDate(). Formats a date into a
    **                           string suitable for use in an SQL statement.
    ** 010 ECF 20090310   @41499 Added inlineLimit(). Enables inlining the
    **                           limit/max for records returned by a query
    **                           into the SELECT statement.
    ** 011 SVL 20090810   @43573 Added requiresExplicitCastInsideTernary().
    **                           Determines whether substitution parameters should
    **                           be explicitly casted to their datatypes inside
    **                           "then ? else ?" statement.
    ** 012 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.
    ** 013 CA  20101202          Added method to build the URL for a remote
    **                           connection.
    ** 014 SVL 20110622          Added usesSingleBackslash.
    ** 015 OM  20121120          Added DDL/SQL generation support for sequences.
    ** 016 OM  20130712          Declared createSequenceHandler().
    ** 017 VMN 20131005          Added injectComputedColumns.
    ** 018 OM  20140410          Fixed support case-sensitive fields in indexes.
    ** 019 VMN 20140427          Added quote() method and changed getAlterComputedColumnString
    **                           signature.
    ** 020 OM  20140508          Added support for computing the index-key size.
    ** 021 OM  20140623          Added rtrim() dialect specific implementation. Added query max
    **                           parameters count support.
    ** 022 OM  20140924          Removed usesSingleBackslash.
    ** 023 OM  20151130          Added supportsBooleanDatatype(). rtrim() dialect specific
    **                           implementation returns the number of occurrences of the expression processed.
    ** 024 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 025 OM  20160603          Fixed rtrimming to space character only.
    ** 026 ECF 20160912          Added getSequencePrefetchString.
    ** 027 ECF 20190924          Refactored abstract class from P2JDialect interface; removed
    **                           Hibernate dependencies.
    **     OM  20200610          Made abstract the sequence-related method to force implementation in subclasses.
    **                           Dropped unused/obsolete methods.
    **     CA  20200624          Augment the index with the NULLS FIRST/LAST depending on the direction and dialect.
    ** 028 OM  20200924          Index components carry multiple information to avoid map lookups for them.
    ** 029 IAS 20201204          Added id() method and support for word tables
    **     IAS 20210105          Added isUdfContains() function
    **     IAS 20210303          Added useCTE4Contains() and containsUdfName() functions
    **     IAS 20210310          Added implicit ordering
    **     OM  20210412          Replace Hibernate-specific casts with dialect-specific casts
    **     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.
    **     TJD 20220504          Upgrade do Java 11 minor changes
    **     BS  20221111          Added ParmType translations for common types
    **     IAS 20220913          Re-work processing of UDFs errors/warnings.
    **     OM  20220914          Use MAX-WIDTH to generate custom sized varchar columns.
    **     IAS 20220921          Dialect-specific UDF schema
    **     OM  20221012          Fixed regression in previous commit.
    **     OM  20221018          Added support for MariaDb lenient dialect.
    **     OM  20221026          Excluded table name from index name by default. It will be added as needed by
    **                           dialects.
    **     BS  20221111          Added ParmType translations for common types
    **     RAA 20230109          Added method getSequenceSetValString().
    **     SVL 20230110          P2JIndex.components() provides direct access to the components array in order to
    **                           improve performance.
    **     ECF 20230127          Added resolveBooleanConstant method.
    ** 030 RAA 20230221          Added supportsLazyQueryResultsMode function.
    **     AL2 20230210          Added getInvisibleSpecificator.
    **     RAA 20230208          Modified function for creating a temp table postfix. Depending on the parameter,
    **                           it can resume its initial purpose or it can return the keyword specific to
    **                           no-undo temp-tables.
    **     AL2 20230303          Made getInvisibleSpecificator return by default an empty string instead of null.
    ** 031 AL2 20230316          Consider selection using wildcard with allowSelectWildcard.
    ** 032 BS  20230331          Added support for sequences.
    ** 033 IAS 20230405          Added fix for H2 bug with bind parameters in recursive CTEs.
    ** 034 DDF 20230306          Created token for selecting between varchar_casesensitive and varchar_ignorecase
    **                           in P2JH2Dialect. Added a parameter to getSqlMappedType() (refs: #7108).
    ** 031 DDF 20230330          Changed return value of getComputedColumnPrefix from null to empty string.
    ** 032 DDF 20230406          Removed isAutoRtrimAndCi. Added isAutoRtrim and isAutoCi methods.
    ** 033 GBB 20230512          Logging methods replaced by CentralLogger/ConversionStatus.
    ** 034 OM  20230615          Converting the MAX-WIDTH from size of UTF-16 encoding to number of characters.
    ** 035 AB  20230828          Added getMaxEntriesInSelect() method.
    ** 036 OM  20230908          Added checkIndexConstraints() method.
    ** 037 DDF 20230818          Created an additional getCreateSequenceString() method.
    ** 038 DDF 20231127          Added isUniqueConstraintViolation() method.
    ** 039 OM  20231218          Added implementation for DBCODEPAGE and DBCOLLATION 4GL functions.
    ** 040 IAS 20230208          Dialect-specific error handler support.
    ** 041 OM  20240131          Fixed flaw in DBCODEPAGE / DBCOLLATION implementation.
    ** 042 RAA 20230802          Added support for SOFT_UNIQUE keyword.
    **     RAA 20230802          There are two getCreateIndexString functions now, the second one being
    **                           responsible with handling SOFT_UNIQUE indexes.
    **     RAA 20230704          Changed name of validateMode to allowDBUniqueCheck.
    **     RAA 20231114          Replaced PersistenceException with RuntimeException in getCreateIndexString.
    ** 043 RAA 20240319          Added useNullEquality function which defaults to false.
    ** 044 DDF 20240320          Added preferDisjunctiveForm() method.
    ** 045 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 OM  20240909          Concurrent multitenancy functionality implementation.
    ** 048 OM  20250117          Added support for multiple dialects for multi-tenant landlord database.
    ** 049 RNC 20250224          Added isRecoverableError() method.
    ** 050 DDF 20240205          Added mandatory and isChar parameters to orderByNulls.
    **     DDF 20240209          Added an additional parameter to getComputedColumnString method and removed
    **                           isChar from orderByNulls.
    ** 051 OM  20250303          The tenant_name is fetched with the other columns of the domain.
    ** 052 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.sql.*;
    import java.sql.Date;
    import java.util.*;
    import java.util.concurrent.*;
    import java.util.function.*;
    import java.util.logging.*;

    import com.goldencode.p2j.cfg.*;
    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.SequenceHandler;
    import com.goldencode.p2j.util.*;
    import com.goldencode.p2j.util.ErrorManager.*;
    import com.goldencode.p2j.util.logging.*;
    import com.mchange.v2.c3p0.*;

    /**
     * Extends the API defined by Hibernate's abstract <code>Dialect</code> class
     * to add methods required by the P2J persistence framework.  The
     * implementations of these methods are necessarily specific to the backing
     * database and may be different for different database vendors.
     * <p>
     * NOTE:  a longer term goal is to eliminate this interface by merging these
     * methods back into the mainstream Hibernate <code>Dialect</code> class.
     */
    // TODO: implement base dialect features we need
    // TODO: refactor/deduplicate common method implementations in subclasses
    public abstract class Dialect
    {
       /**
        * Token used to mark a placeholder for the scale when generating the DDL for creating/altering a decimal
        * column in a table.
        */
       static final String SCALE_TOK = "%scale%";

       /**
        * Token used to mark placeholder for varchar type when generating the DDL for creating a
        * a varchar_casesensitive/varchar_ignorecase. This is strictly for H2.
        */
       static final String CASE_TYPE_TOK = "%ctype%";

       /** The default MAX-WIDTH for character fields if the attribute is not specified. */
       static final int DEFAULT_MAX_WIDTH = 16;

       /** Logger */
       private static final CentralLogger log = CentralLogger.get(Dialect.class.getName());

       /** Map of known/loaded dialect instances */
       private static final Map<String, Dialect> knownDialects = new ConcurrentHashMap<>();

       private static final Map<String, Dialect> dialectByJdbcType = Collections.unmodifiableMap(
          new HashMap<String, Dialect>()
          {{
             put("postgresql", new P2JPostgreSQLDialect());
             put("h2", new P2JH2Dialect());
             put("sqlserver", new P2JSQLServer2012Dialect());
             put("mariadb", new MariaDbDialect());
             // put("mariadb", new MariaDbLenientDialect()); -- MariaDbDialect will have handle both
          }}
       );

       /**
        * Get the instance of a concrete subclass of this class which is associated with the given
        * JDBC database type.
        *
        * @param   jdbcType
        *          Database jdbc type.
        *
        * @return  Instance associated with the given jdbcType
        */
       public static Dialect getDialect(String jdbcType)
       {
          return dialectByJdbcType.get(jdbcType);
       }

       /**
        * Get the instance of a concrete subclass of this class which is associated with the given
        * database.
        * <p>
        * There is one instance per subclass type; they are stateless and shared.
        *
        * @param   database
        *          Database identifier.
        *
        * @return  Instance associated with the given database, or {@code null} if no dialect has
        *          been configured for the database.
        */
       public static Dialect getDialect(Database database) // this method apparently seems broken
       {
          Dialect dialect = null;

          try
          {
             Settings settings = Settings.get(database);
             dialect = getDialect(settings);
          }
          catch (PersistenceException exc)
          {
             if (log.isLoggable(Level.WARNING))
             {
                String msg = "No settings found for database '" + database.getName() + "'";
                if (log.isLoggable(Level.FINE))
                {
                   log.log(Level.FINE, msg, exc);
                }
                else
                {
                   log.log(Level.WARNING, msg);
                }
             }
          }

          return dialect;
       }

       /**
        * Analyses a JDBC connection URL and returns the {@code Dialect} to handle it. The URL is parsed and the
        * dialect sub-protocol is used for looking up the right dialect which will be returned.
        * For this, the URL should have the following format:<p>
        * {@code jdbc:<dialect-sub-protocol>:<path-and-eventual-parameters-are-ignored>}
        * <p>
        * If {@code jdbcURL} does not meet this minimum requirement, {@code null} is returned
        *
        * @param   jdbcURL
        *          The JDBC URL string which should be analysed. Or {@code null} if the string cannot be parsed.
        *
        * @return  the JDBC dialect identifier
        */
       public static Dialect getDialectForURL(String jdbcURL)
       {
          int c1 = jdbcURL.indexOf(':');
          if (c1 == -1)
          {
             return null;
          }

          int c2 = jdbcURL.indexOf(':', c1 + 1);
          if (c2 == -1)
          {
             return null;
          }

          return getDialect(jdbcURL.substring(c1 + 1, c2));
       }

       /**
        * Get known dialects by name
        *
        * @return known dialects by name
        */
       public static Map<String, Dialect> dialects()
       {
          return Collections.unmodifiableMap(knownDialects);
       }

       /**
        * Get the instance of a concrete subclass of this class, as specified in the given settings.
        * If the settings do not specify a dialect class, {@code null} is returned.
        * <p>
        * There is one instance per subclass type; they are stateless and shared.
        *
        * @param   settings
        *          Settings object which should contain a dialect entry.
        *
        * @return  Instance of a concrete dialect subclass, if specified in the given settings;
        *          otherwise {@code null}.
        */
       public static Dialect getDialect(Settings settings)
       {
          String name = settings.getString(Settings.DIALECT, null);
          if (name == null || name.trim().isEmpty())
          {
             if (log.isLoggable(Level.WARNING))
             {
                log.log(Level.WARNING, "No database dialect configured");
             }

             return null;
          }

          Dialect dialect = knownDialects.get(name);

          if (dialect != null)
          {
             return dialect;
          }

          try
          {
             @SuppressWarnings("unchecked")
             Class<? extends Dialect> clazz = (Class<? extends Dialect>) Class.forName(name);

             dialect = clazz.getDeclaredConstructor().newInstance();

             knownDialects.putIfAbsent(name, dialect);
          }
          catch (ClassCastException exc)
          {
             if (log.isLoggable(Level.SEVERE))
             {
                log.log(Level.SEVERE, "Configured dialect '" + name + "' is not recognized", exc);
             }
          }
          catch (ReflectiveOperationException exc)
          {
             if (log.isLoggable(Level.SEVERE))
             {
                log.log(Level.SEVERE, "Dialect '" + name + "' could not be loaded", exc);
             }
          }

          return dialect;
       }

       /**
        * Get the splitter for DDL scripts.
        *
        * @return the splitter for DDL scripts
        */
       public abstract ScriptSplitter scriptSplitter();

       /**
        * Get a ScriptRunner instance for the dialect.
        *
        * @return a ScriptRunner instance for the dialect.
        *
        */
       public abstract ScriptRunner getScriptRunner();

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

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

       /**
        * Get the name of the JDBC driver class associated with this dialect.
        *
        * @return  Fully qualified driver class name.
        */
       public abstract String getDriverClassName();

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

       /**
        * Get dialect id.
        *
        * @return dialect id
        */
       public abstract String id();

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

       /**
        * 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 abstract boolean useWordTables();

       /**
        * Check if native UDFs are supported for the dialect
        *
        * @return <code>true</code> if native UDFs are supported for the dialect
        */
       public abstract boolean isNativeUDFsSupported();

       /**
        * Get UDF schema name (if applicable).
        *
        * @return UDF schema name (if applicable).
        */
       public String udfSchema()
       {
          return "";
       }

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

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

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

       /**
        * 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.
        */
       public boolean isUdfOriginatedWarning(SQLException e)
       {
          return false;
       }

       /**
        * Create the error descriptor from the SQLException message.
        *
        * @param   message
        *          The SQLException message.
        *
        * @return  the error descriptor.
        */
       public ErrorEntry errorEntry(String message)
       {
          throw new UnsupportedOperationException();
       }
       /**
        * 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 false;
       }

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

       /**
        * Check if the provided name is the name of the CONTAINS UDF.
        *
        * @param   name
        *          The 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".equals(name);
       }

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

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

       /**
        * Activate error handler at the database side.
        *
        * @param   conn
        *          Database connection.
        *
        * @throws  SQLException
        *          On error.
        */
       public void activateErrorHandler(Connection conn)
       throws SQLException
       {

       }

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

       /**
        * 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
        */
       public abstract void generateWordTablesDDLImpl(String dbName,
                                                      PrintStream out,
                                                      String eoln,
                                                      Collection<WordTable>  wordTables);

       /**
        * 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>.
        */
       public Object createContext(Database database)
       {
          return null;
       }

       /**
        * A dialect-specific callback hook which is invoked by the persistence runtime framework
        * after a transaction is complete.
        * <p>
        * Default implementation does nothing.
        *
        * @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.
        */
       public void postTransaction(Persistence persistence, Object context, boolean rollback)
       throws PersistenceException
       {
       }

       /**
        * Retrieve the earliest possible date value which is valid for the backing database.
        *
        * @return  Earliest date supported by the backing database or {@code null} to indicate no
        *          particular restriction.
        */
       public Date getLowDate()
       {
          return null;
       }

       /**
        * Retrieve the latest possible date value which is valid for the backing database.
        *
        * @return  Latest date supported by the backing database or {@code null} to indicate no
        *          particular restriction.
        */
       public Date getHighDate()
       {
          return null;
       }

       /**
        * 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.
        */
       public abstract String formatDate(date d);

       /**
        * Given a column expression 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>
        *    upper(rtrim("key"::text))
        * </pre>
        * We are interested only in the {@code key} root name, so we strip away the {@code upper()}
        * and {@code rtrim()} functions, the double quotes, and the {@code ::text} suffix.
        *
        * @param   expr
        *          Expression containing column name as natively represented by
        *          the database dialect.
        *
        * @return  Root column name.
        */
       public abstract String extractColumnName(String 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.
        *
        * @param   name
        *          Base column name.
        * @param   ignoreCase
        *          <code>true</code> to cause case to be ignored, else <code>false</code>.
        *
        * @return  Processed column name.
        */
       public abstract String getProcessedCharacterColumnName(String name, boolean ignoreCase);

       /**
        * 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.
        */
       public abstract boolean isCaseInsensitiveColumn(String name);

       /**
        * 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  {@code true} if {@code name} represents a computed column. Default implementation
        *          returns {@code false}.
        */
       public boolean isComputedColumn(String name)
       {
          return false;
       }

       /**
        * 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;
        *          <code>false</code> if the backing database can embed an
        *          expression within an index definition.
        */
       public abstract boolean needsComputedColumns();

       /**
        * Get the prefix, if any, which should be prepended to computed column and/or property names.
        *
        * @param   caseSensitive
        *          <code>true</code> if this is a character field was declared with
        *          <code>CASE-SENSITIVE</code> attribute. In each case the column name will be
        *          name-decorated differently.
        *
        * @return  Computed column/property prefix, or an empty string if the
        *          backing database does not use computed columns.
        *
        * @see     #needsComputedColumns()
        */
       public String getComputedColumnPrefix(boolean caseSensitive)
       {
          return "";
       }

       /**
        * Indicate whether this dialect natively supports the overloading of user
        * defined functions by parameter number and data type.
        *
        * @return  <code>true</code> if the backing database natively supports
        *          overloading, else <code>false</code>.
        */
       public abstract boolean supportsFunctionOverloading();

       /**
        * 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).
        */
       public abstract boolean supportsBooleanDatatype();

       /**
        * 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.
        */
       public abstract String getCreateSequenceString(String 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.
        */
       public String getCreateSequenceString(String name, boolean isTemporary)
       {
          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.
        */
       public abstract String getCreateSequenceString(String name, long 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.
        * @param   maxVal
        *          Sequence maximum value. Optional.
        * @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.
        */
       public abstract String getCreateSequenceString(String name,
                                                      Long init,
                                                      Long increment,
                                                      Long minVal,
                                                      Long maxVal,
                                                      Boolean cycle);

       /**
        * 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 abstract String getDropSequenceString(String 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.
        */
       public abstract String getSequenceSetValString(String sequenceName, long 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 set the current value of the sequence, or {@code null}
        *          if not supported.
        */
       public abstract String getSequenceSetValString(String 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.
        */
       public abstract String getSequenceSetValString();

       /**
        *
        * 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.
        */
       public abstract String getSequenceNextValString(String 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.
        */
       public abstract String getSequenceCurrValString(String sequenceName);

       /**
        * Generate the appropriate select statement to retrieve the current value of a sequence.
        *
        * @return  Dialect-specific string to query the sequence, or {@code null} if not supported.
        */
       public String getSequenceCurrValString()
       {
          throw new IllegalStateException(
                   "Current value of a sequence cannot be retrieved using prepared statements.");
       }

       /**
        * 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
        */
       public abstract boolean hasFullSequenceSupport();

       /**
        * 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.
        */
       public abstract String getSequencePrefetchString(String sequenceName);

       /**
        * 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.
        */
       public abstract boolean isConnectionError(Exception exc);

       /**
        * 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.
        */
       public abstract boolean isQueryRangeParameterInlined();

       /**
        * 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</code> to enable inlining;  <code>false</code> to
        *          disable it.
        */
       public abstract void inlineLimit(boolean on);

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

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

       /**
        * Get the limit string for the given limit and offset.
        *
        * @param   sql
        *          Base select statement SQL without the limit/offset clause.
        * @param   offset
        *          Starting offset.
        * @param   limit
        *          Number of records to return.
        *
        * @return  SQL with limit/offset clause appended.
        */
       public String getLimitString(String sql, int offset, int limit)
       {
          String stmt = sql + " limit " + limit;

          if (offset > 0)
          {
             stmt = stmt + " offset " + offset;
          }

          return stmt;
       }

       /**
        * Indicate whether a variable limit is supported by this dialect.
        *
        * @return  {@code false}; should be overridden by dialects which support a variable (i.e.,
        *          not inlined) limit string.
        */
       public boolean supportsVariableLimit()
       {
          return false;
       }

       /**
        * Resolve the given text to a boolean value. By default, if {@code text} matches the strings "true" or
        * "1" case-insensitively, the result is {@code true}. Subclasses with more esoteric mappings may override
        * this method.
        *
        * @param   text
        *          A potential representation of a boolean constant.
        *
        * @return  {@code true} if the given constant string represents boolean true, else {@code false}.
        */
       public boolean resolveBooleanConstant(String text)
       {
          return "true".equalsIgnoreCase(text) || "1".equals(text);
       }

       /**
        * 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.
       */
       public abstract String getDropIndexString(P2JIndex index);

       /**
        * Build the DDL to create an index based upon the specified definition. The SQL is generated
        * by using a database-specific dialect.
        * <p>
        * It is expected that this method is NOT used at runtime, as it has a dependency on
        * conversion project configuration being available.
        *
        * @param   index
        *          Definition of the index to be created.
        * @param   unique
        *          If {@code true}, create the index as a unique index, otherwise omit the unique
        *          constraint.
        * @param   toFile
        *          {@code true} if the string will be output to a file. Otherwise it will be directly
        *          submitted via JDBC.
        *          TODO: this parameter seems to be unused. Drop it?
        *
        * @return  The DDL which was created.
       */
       public String getCreateIndexString(P2JIndex index, boolean unique, boolean toFile)
       {
          StringBuilder sb = new StringBuilder();
          sb.append(getCreateIndexString(unique)).append(getSqlIndexName(index));
          sb.append(" on ").append(index.getTable()).append(" (");
          boolean lastDesc = false;
          ArrayList<P2JIndexComponent> comps = index.components();
          for (int i = 0; i < comps.size(); i++)
          {
             P2JIndexComponent comp = comps.get(i);
             String sortCrit;
             boolean isChar = comp.isCharType();
             if (isChar/* && comp.isIgnoreCase()*/)
             {
                sortCrit = getProcessedCharacterColumnName(comp.getColumnName(), comp.isIgnoreCase());
             }
             else
             {
                sortCrit = comp.getColumnName();
             }

             orderByNulls(sb, sortCrit, null, !comp.isDescending(), false, comp.isMandatory());

             if (i != comps.size() - 1)
             {
                sb.append(", ");
             }
             lastDesc = comp.isDescending();
          }

          // Note 1: the direction of the pk column name is the same with the last component before it. this make
          //         this index much easier to fit using the sort clause of the FindQuery, especially in reverse
          //         sorting mode (at runtime I cannot tell is the sorting direction was already reversed during
          //         conversion for FIND LAST/FIND PREV structures so I cannot assume the direction of pk id is
          //         always ascending; however, if the direction is linked to one of the components of the index,
          //         reversing it's sorting direction will also reverse the sort order of the pk id so the query
          //         planner will be able to identify the correct index at runtime).
          // Note 2: add this pseudo-component only if the index was originally NOT unique. If it was found to be
          //         a derived unique index, it is still implicitly unique (even if the attribute was not
          //         specified above) and do not need the surrogate component PK added
          if (!unique && !index.isUnique())
          {
             sb.append(", ").append(Configuration.getSchemaConfig().getPrimaryKeyName());
             if (lastDesc)
             {
                sb.append(" desc");
             }
          }
          sb.append(")");

          return sb.toString();
       }

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

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

       /**
        * Returns {@code true} if the dialect handles the optimization of an augmented construct
        * which is resulted from comparing exactly two character fields.
        *
        * @return  as described above.
        */
       public boolean preferDisjunctiveForm()
       {
          return false;
       }

       /**
        * Returns the formula for character computed columns. This will be called in two cases: when
        * generating the ddl for permanent databases schema (this will be written to a .sql file) and
        * when creating temp-tables (the code will be sent directly to SQL).
        *
        * @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 formula for computed columns or {@code null} if the dialect does not support
        *          computed columns.
        */
       public String getComputedColumnFormula(String colName, boolean ignoreCase)
       {
          return null;
       }

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

       /**
        * Given an exception, this method will check if it indicates a recoverable error.
        * The method was made to overcome the limitations imposed by this dialect which are not
        * present in the OE database due to not being so restrictive while handling the data.
        *
        * @param   exc
        *          The exception to be checked.
        *
        * @return  {@code true} if the exception indicates a recoverable error, {@code false} otherwise.
        */
       public boolean isRecoverableError(Exception exc)
       {
          return false;
       }

       /**
        * Prepare the given metadata query parameter.
        *
        * @param   param
        *          The metadata query parameter.
        *
        * @return  the prepared parameter.
        */
       public abstract String prepareMetadataParameter(String param);

       /**
        * Process the given metadata query result.
        *
        * @param   value
        *          The metadata query result.
        *
        * @return  the processed result.
        */
       public abstract String processMetadataResult(String value);

       /**
        * 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.
        */
       public abstract boolean isMetadataSortDesc(String 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.
        */
       public abstract boolean isMetadataSortAsc(String value);

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

       /**
        * 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.
        */
       public abstract boolean explicitSetCollation();

       /**
        * Get the statement delimiter for this dialect.
        *
        * @return  the statement delimiter.
        */
       public abstract String getDelimiter();

       /**
        * Set any database parameters before, to be available when the database
        * driver is loaded.
        */
       public void beforeDriverLoad()
       {
       }

       /**
        * 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.
        */
       public abstract String buildRemoteURL(String url, Database database)
       throws PersistenceException;

       /**
        * 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.
        */
       public abstract SequenceHandler createSequenceHandler(String ldbName);

       /**
        * Report whether the computed columns should be injected on table creating.
        *
        * @return  <code>true</code> if the computed columns should be injected on table creating
        *          else <code>false</code>.
        */
       public abstract boolean injectComputedColumns();

       /**
        * Checks if the SQLState of the SQLException matches the dialect specific UNIQUE_VIOLATION
        * code.
        *
        * @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.
        */
       public abstract boolean isUniqueConstraintViolation(SQLException sqle);

       /**
        * Constructs and returns the declaration of a computed column based on its name, type, scale, and
        * case-sensitiveness.
        *
        * @param   name
        *          The original column name.
        * @param   type
        *          The original column type.
        * @param   scale
        *          The optional scale, if needed.
        * @param   cs
        *          The case-sensitiveness.
        * @param   mandatory
        *          If the column is non-null.
        *
        * @return  The declaration of the computed column(s) or {@code null} if not needed.
        */
       public String getComputedColumnString(String name, String type, int scale, boolean cs, boolean mandatory)
       {
          if (!"character".equals(type))
          {
             return null;
          }

          // the computed columns is generally used only for char type columns
          return getComputedColumnPrefix(cs) + name + " " + getSqlMappedType(type, scale, cs) +
                 " as " + getComputedColumnFormula(name, !cs);
       }

       /**
        * Apply dialect-specific quoting.
        *
        * @param   expr
        *          String expression.
        *
        * @return  The quoted value.
        */
       public String quote(String expr)
       {
          // TODO: implement
          return expr;
       }

       /**
        * Some dialects have constraints about the maximum size of fields that compose an index.
        * This method reports the dialect-specific index length limit, if any.
        *
        * @return  Dialect-specific index length limit. If there are no such limits for this dialog,
        *          the method returns 0.
        */
       public int getIndexLengthLimit()
       {
          return 0;
       }

       /**
        * Computes the size (in bytes) occupied by this value when stored as index-key in an index.
        * It only make sense to call this method if the {@code getIndexLengthLimit()} returns
        * positive values.
        * If this dialect does not have index-size constraints then this method does not need to be
        * overridden.
        *
        * @param   type
        *          The type to be analyzed.
        * @param   val
        *          The actual value. Except for the char datatype, only the type of value
        *          is used, the actual value is not needed to determine the size occupied by the
        *          value as index-key.
        *
        * @return  the size (in bytes) occupied by this value when stored as index-key in an index.
        */
       public int computeIndexKeySize(Class<? extends BaseDataType> type, Object val)
       {
          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.
        *
        * @param   unique
        *          <code>true</code> if the index is unique
        *
        * @return  The size of synthetic part of the index, in bytes.
        */
       public abstract int getSyntheticIndexSize(boolean unique);

       /**
        * Composes a rtrim equivalent call for a specified expression. This is dialect specific.
        * Usually the returned value looks like:
        * <pre>
        *    rtrim(expr)
        * </pre>
        * The rtrim-med expression is appended to the string builder.
        * The return value (the number of occurrences of the initial expr added to sb)
        * should be use by caller to count the number of possible parameters from a query.
        *
        * @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 field to.
        */
       public abstract void addRtrimmedExpression(String expr, StringBuilder sb);

       /**
        * Obtain the number of parameters a query of this dialect is allowed by the SQL server.
        * If the query need more parameter it have to be rewritten or split and the result
        * reassembled in memory.
        *
        * @return  the maximum number of parameters that a query may have or 0 if there is no
        *          such limitation.
        */
       public int getMaxParameterCount()
       {
          return 0;
       }

       /**
        * Returns the beginning of the DDL statement for creating a permanent table.
        *
        * @return  the beginning of the DDL statement for creating a permanent table.
        */
       public String getCreateTableString()
       {
          return "create table ";
       }

       /**
        * 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.
        */
       public abstract String getCreateTemporaryTableString(boolean ifNotExist);

       /**
        * Return the ending of the DDL statement that creates a temporary table.
        *
        * @param   noUndo
        *          If {@code true}, this function will return the keyword specific to no-undo temp-tables.
        *
        * @return  A string representing the ending of the DDL statement that creates a temporary
        *          table, if any, or empty string otherwise.
        */
       public abstract String getCreateTemporaryTablePostfix(boolean noUndo);

       /**
        * Returns the beginning of the DDL statement for creating an index.
        *
        * @param   unique
        *          {@code true} for unique indexes.
        *
        * @return  the beginning of the DDL statement for creating an index.
        */
       public String getCreateIndexString(boolean unique)
       {
          return "create " + (unique ? "unique " : "") + "index ";
       }

       /**
        * 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.
        */
       public String getCreateIndexString(boolean unique, boolean softUnique)
       {
          throw new RuntimeException("soft_unique is not accessible from this dialect");
       }

       /**
        * 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.
        */
       public abstract String getDropIndexString(boolean ifExists, String idxName, String 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.
        */
       public abstract String getDropTableString(boolean ifExists, String tableName);

       /**
        * 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.
        */
       public String getSpecificTypeAsString(FqlType type)
       {
          return type.toString();
       }

       /**
        * 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}.
        */
       public String getSqlMappedType(Class<?> fwdType, int scale, Boolean caseSensitive)
       {
          return fwdType == null ? null : getSqlMappedType(fwdType.getSimpleName(), scale, caseSensitive);
       }

       /**
        * Get the SQL mapping for a specific FWD data type.
        *
        * @param   fwdType
        *          The class name of the FWD datatype.
        * @param   scale
        *          Optional scale parameter (for {@code decimal} data type).
        *
        * @return  The string representation of the SQL datatype mapped by {@code fwdType}.
        */
       public String getSqlMappedType(String fwdType, int scale, Boolean caseSensitive)
       {
          throw new RuntimeException(
                "SQL datatype not defined for '" + fwdType + "' for '" +
                this.getClass().getSimpleName() + "' dialect.");
       }

       /**
        * 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.
        */
       public abstract String getAnalyzeString(String table);

       /**
        * Determine if the sort component or index needs to be augmented with a NULLS FIRST/LAST option and
        * add the required expression(s) as needed by this dialect. The default implementation does not augment
        * the expression in any way.
        *
        * @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 (possible {@code null}, ignored here).
        * @param   asc
        *          The sort direction.
        * @param   forceAsc
        *          If {@code true}, force the insertion of the default {@code asc} direction.
        *
        * @return  the number of expressions which were added to the string builder.
        */
       public int orderByNulls(StringBuilder sb, String expression, String field, boolean asc, boolean forceAsc)
       {
          return orderByNulls(sb, expression, field, asc, forceAsc, false);
       }

       /**
        * Determine if the sort component or index needs to be augmented with a NULLS FIRST/LAST option and
        * add the required expression(s) as needed by this dialect. The default implementation does not augment
        * the expression in any way.
        *
        * @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 (possible {@code null}, ignored here).
        * @param   asc
        *          The sort direction.
        * @param   forceAsc
        *          If {@code true}, force the insertion of the default {@code asc} direction.
        * @param   mandatory
        *          The non-null property of the field.
        *
        * @return  the number of expressions which were added to the string builder.
        */
       public int orderByNulls(StringBuilder sb,
                               String expression,
                               String field,
                               boolean asc,
                               boolean forceAsc,
                               boolean mandatory)
       {
          sb.append(expression);
          if (!asc)
          {
             sb.append(" desc");
          }
          else if (forceAsc)
          {
             sb.append(" asc");
          }
          return 1;
       }

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

       /**
        * Translation from database column type to <code>ParmType</code>. This is a general implementation that will work
        * for common types used in FWD. For fine-tuning, you can override it in specific dialects.
        *
        * TODO: should be abstract
        *
        * @param   type
        *          column type
        * @param   comment
        *          comment with optional specification of type
        * @return  ParmType
        */
       public ParmType translateToParmType(String type, String comment)
       {
          throw new UnsupportedOperationException("Translation of column types is not yet implemented for this dialect");
       }

       /**
        * Get decimals value for types that support it. This is a general implementation that will work
        * for common types used in FWD. For fine-tuning, you can override it in specific dialects.
        *
        * @param   type
        *          column type
        * @param   decimals
        *          decimals value as provided by the database
        * @param   comment
        *          comment with optional metadata
        * @return  decimals value or null (for types that don't support decimals)
        */
       public Long getDecimals(String type, String decimals, String comment)
       {
          switch (type.toUpperCase())
          {
             case "DECIMAL":
             case "NUMERIC":
                return Long.valueOf(decimals);
             default:
                return null;
          }
       }

       /**
        * 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.
        */
       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
        */
       public String getCatalogPattern(Database database)
       {
          throw new UnsupportedOperationException("Catalog matching is not yet implemented for this dialect");
       }

       /**
        * Provides schema matching pattern to load data correctly from JDBC.
        *
        * @param   database
        *          database object
        * @return  schema pattern
        */
       public String getSchemaPattern(Database database)
       {
          throw new UnsupportedOperationException("Schema matching is not yet implemented for this dialect");
       }

       /**
        * Determine if the {@code Dialect} supports queries executed in a lazy manner.
        * Unless implemented, lazy queries are not supported.
        *
        * @return  {@code false}
        *          By default, lazy queries are not supported.
        */
       public boolean supportsLazyQueryResultsMode()
       {
          return false;
       }

       /**
        * Determine if the {@code Dialect} supports index validation (with the VALIDATE keyword).
        *
        * @return  {@code false}
        *          By default, VALIDATE is not supported.
        */
       public boolean allowDBUniqueCheck()
       {
          return false;
       }

       /**
        * Provides SQL to load sequences.
        *
        * @param   database
        *          database object
        * @return  sequence SQL
        */
       public String getSequenceSQL(Database database)
       {
          throw new UnsupportedOperationException("SQL for sequences is not yet implemented for this dialect");
       }

       /**
        * If the dialect has some constraints about the index size, do the calculations and report any possible
        * error/warning when the database schema is set.
        * <p>
        * The default implementation does nothing.
        *
        * @param   p2JIndex
        *          The index to be processed.
        * @param   fields
        *          All table fields, mapped by their legacy name.
        */
       public void checkIndexConstraints(P2JIndex p2JIndex, Map<String, P2JField> fields)
       {
       }

       /**
        * Helper interface for weights' aggregation
        */
       public static interface WeightAggregator
       {
          /**
           * Get an aggregate wrapper for converted converted CONTAINS operator.
           *
           * @return an aggregate wrapper for converted converted CONTAINS operator
           */
          public BiConsumer<StringBuilder, Runnable> aggregate();

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

       /**
        * Prepare the specified database by creating all necessary function aliases and setting global parameters.
        * <p>
        * The default implementation from {@code Dialect} does nothing.
        *
        * @param   db
        *          Database to prepare.
        *
        * @throws  PersistenceException
        *          if there is any error executing DDL.
        */
       public void preparePermanentDatabase(Database db)
       throws PersistenceException
       {
          // NOP
       }

       /**
        * Obtain the name of an index. The implementing dialect may override this method in order to make sure
        * the name is unique among tables.
        *
        * @param   index
        *          The index structure.
        *
        * @return  the SQL name of the index.
        */
       public String getSqlIndexName(P2JIndex index)
       {
          return index.getName();
       }

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

       /**
        * Returns the max numbers of fields selected that the dialect can allow without throwing an error.
        *
        * @return Maximum of selected fields allowed
        */
       public int getMaxEntriesInSelect()
       {
          return Integer.MAX_VALUE;
       }

       /**
        * 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)
       {
          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.
        */
       public String getCollation(Connection conn)
       {
          return null;
       }

       /** Obtains an object responsible with providing dialect specific SQL statements for tenant management. */
       public abstract Dialect.TenantSQL getTenantSql();

       /**
        * Implement this to obtain an object responsible with providing dialect specific SQL statements for tenant
        * management.
        */
       public interface TenantSQL
       {
          /** The column name for {@code c3p0.maxStatementsPerConnection}. */
          String C3P0_MAX_STMTS = "c3p0_max_stmts";

          /** The column name for {@code c3p0.minPoolSize}. */
          String C3P0_MIN_POOL = "c3p0_min_pool";

          /** The column name for {@code c3p0.maxPoolSize}. */
          String C3P0_MAX_POOL = "c3p0_max_pool";

          /** The column name for {@code c3p0.acquireIncrement}. */
          String C3P0_ACQ_INC = "c3p0_acq_inc";

          /** The column name for {@code c3p0.maxIdleTime}. */
          String C3P0_MAX_IDLE = "c3p0_max_idle";

          /** SQL for creating the master table, if it doesn't already exist. */
          public String getCreateMasterTable();

          /**
           * 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.
           */
          public String getCreateDatabasesTable();

          /** SQL for creating an index for the master table on the {@code tenant_name} column. */
          public String getCreateMasterIndex();

          /**
           * 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.
           */
          public String getCreateTenantDbIndex();

          /** SQL for retrieving all the tenants. */
          public default String getSelectAllTenants()
          {
             return "SELECT * FROM master";
          }

          /**
           * Obtain the SELECT SQL statement for retrieving the information of a specific database, of a specific
           * tenant.
           * TODO: is this used?
           *
           * @return  the SELECT SQL statement for retrieving the information of a specific database, of a
           *          specific tenant.
           */
          public default String getSelectTenantDatabase()
          {
             return "SELECT * FROM tenant_databases WHERE tenant_name = ? and pdb_name = ?";
          }

          /**
           * Obtain the SELECT SQL statement retrieving the information of all databases of a specific tenant.
           *
           * @return  the SELECT SQL statement retrieving the information of all databases of a specific tenant.
           */
          public default String getSelectTenantDatabases()
          {
             return "SELECT * FROM tenant_databases WHERE tenant_name = ?";
          }

          /** SQL used for inserting a tenant into the master table. */
          public default String getInsertTenant()
          {
             return "INSERT INTO master VALUES (?, ?, ?, ?, ?)";
          }

          /**
           * Obtain the INSERT SQL statement used for inserting a database into the {@code tenant_databases}
           * table.
           *
           * @return  the INSERT SQL statement used for inserting a database into the {@code tenant_databases}
           *          table.
           */
          public default String getInsertTenantDatabases()
          {
             return "INSERT INTO tenant_databases VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
          }

          /** SQL used for updating a tenant's name in the master table. */
          public default String getUpdateTenantName()
          {
             return "UPDATE master SET tenant_name = ? WHERE tenant_name = ?";
          }

          /** SQL used for updating a tenant's description in the master table. */
          public default String getUpdateTenantDescription()
          {
             return "UPDATE master SET tenant_description = ? WHERE tenant_name = ?";
          }

          /** SQL used for updating a tenant's info in the master table. */
          public default String getUpdateTenantInfo()
          {
             return "UPDATE master SET tenant_info = ? WHERE tenant_name = ?";
          }

          /**
           * Obtain the SQL statement used for updating a tenant's name in the {@code tenant_databases} table.
           *
           * @return  the SQL statement used for updating a tenant's name in the {@code tenant_databases} table.
           */
          public default String getUpdateTenantDbName()
          {
             return "UPDATE tenant_databases SET tenant_name = ? WHERE tenant_name = ?";
          }

          /**
           * Obtain the SQL statement used for updating the physical database name of a tenant in the
           * {@code tenant_databases} table.
           *
           * @return  the SQL statement used for updating the physical database name of a tenant in the
           *          {@code tenant_databases} table.
           */
          public default String getUpdatePdbName()
          {
             return "UPDATE tenant_databases SET pdb_name = ? WHERE tenant_name = ? and pdb_name = ?";
          }

          /**
           * Obtain the SQL statement used for updating the logical database name of a tenant in the
           * {@code tenant_databases} table.
           *
           * @return the SQL statement used for updating the logical database name of a tenant in the
           *        {@code tenant_databases} table.
           */
          public default String getUpdateLdbName()
          {
             return "UPDATE tenant_databases SET ldb_name = ? WHERE tenant_name = ? and pdb_name = ?";
          }

          /**
           * Obtain the SQL statement used for updating the URL of the database of a tenant in the
           * {@code tenant_databases} table.
           *
           * @return  the SQL statement used for updating the URL of the database of a tenant in the
           *          {@code tenant_databases} table.
           */
          public default String getUpdateDbUrl()
          {
             return "UPDATE tenant_databases SET url = ? WHERE tenant_name = ? and pdb_name = ?";
          }

          /** SQL used for updating the database username for a tenant in the {@code tenant_databases} table. */
          public default String getUpdateUsername()
          {
             return "UPDATE tenant_databases SET username = ? WHERE tenant_name = ? and pdb_name = ?";
          }

          /** SQL used for updating the database password for a tenant in the {@code tenant_databases} table. */
          public default String getUpdatePassword()
          {
             return "UPDATE tenant_databases SET password = ? WHERE tenant_name = ? and pdb_name = ?";
          }

          /** SQL used for enabling/disabling a tenant. */
          public default String getEnableTenant()
          {
             return "UPDATE master SET status = ? WHERE tenant_name = ?";
          }

          /** SQL used to check if a tenant is enabled or not. */
          public default String getIsTenantEnabled()
          {
             return "SELECT status FROM master where tenant_name = ?";
          }

          /** SQL used for deleting a tenant based on the tenant name value. */
          public default String getDeleteTenant()
          {
             return "DELETE FROM master WHERE tenant_name = ?";
          }

          /** SQL used for deleting a database specific to a tenant from the tenant_databases table. */
          public default String getDeleteTenantDatabase()
          {
             return "DELETE FROM tenant_databases WHERE tenant_name = ? and pdb_name = ?";
          }

          /** SQL used for deleting all the databases specific to a tenant from the tenant_databases table. */
          public default String getDeleteTenantDatabases()
          {
             return "DELETE FROM tenant_databases WHERE tenant_name = ?";
          }

          /**
           * SQL used for selecting the domains from the {@code _sec_authentication_domain} table.
           * <p>
           * Unlike previous SQLs which are executed against {@code landlord} database, this is executed
           * on the primary database (the table was moved from meta to primary in order to remain persistent).
           */
          public default String getSelectDomains()
          {
             return "SELECT domain_name, domain_type, domain_description, domain_access_code, " +
                           "domain_enabled, tenant_name " +
                    "FROM meta_sec_authentication_domain";
          }

          /**
           * SQL used for selecting the domain types from the sec_authentication_system table.
           * <p>
           * Unlike the first SQLs which are executed against {@code landlord} database, this is executed
           * on the primary database (the table was moved from meta to primary in order to remain persistent).
           */
          public default String getSelectDomainTypes()
          {
             return "SELECT domain_type, domain_type_description FROM meta_sec_authentication_system";
          }
       }
    }