# P2JPostgreSQLDialect.java

    /*
    ** Module   : P2JPostgreSQLDialect.java
    ** Abstract : Concrete implementation of Dialect for a PostgreSQL backend
    **
    ** Copyright (c) 2004-2025, Golden Code Development Corporation.
    **
    ** -#- -I- --Date-- --JPRM-- -----------------------------------Description-----------------------------------
    ** 001 ECF 20060929   @30034 Created initial version. Adds P2JDialect
    **                           method implementations to extend Hibernate's
    **                           PostgreSQLDialect.
    ** 002 ECF 20070514   @33483 Added extractColumnName(). Extracts a root
    **                           column name from its native representation in
    **                           a particular dialect.
    ** 003 ECF 20070923   @35221 Override superclass' VARCHAR mapping to point
    **                           to PG's 'text' data type. This is desirable
    **                           because we always want to use 'text' instead
    **                           of 'varchar(n)' for string data on the
    **                           backend.
    ** 004 ECF 20071002   @35334 Implemented new P2JDialect methods. Added
    **                           createContext() and postTransaction() methods
    **                           to enable temp table periodic vacuums.
    ** 005 ECF 20080311   @37518 Implemented methods for computed columns and
    **                           user defined function overloading, which are
    **                           not needed by this dialect but required by
    **                           the P2JDialect interface.
    ** 006 ECF 20080430   @38179 Fix to permit schema generation. Defer lookup
    **                           of vacuumInterval in directory until first
    **                           use. Performing the directory bind during
    **                           class initialization fails when generating
    **                           schema via Ant task.
    ** 007 ECF 20080611   @38705 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.
    ** 008 ECF 20080805   @39316 Added getCreateSequenceString(). Generates
    **                           DDL to create a sequence starting at a
    **                           particular value.
    ** 009 CA  20080815   @39444 Added isConnectionError method - it checks if
    **                           a given exception represents a connection
    **                           error.
    ** 010 ECF 20080930   @40001 Added isQueryRangeParameterInlined().
    **                           Indicates whether query substitution
    **                           parameters within range checks should be
    **                           inlined.
    ** 011 ECF 20081015   @40096 Added formatDate(). Formats a date into a
    **                           string suitable for use in an SQL statement.
    ** 012 ECF 20090310   @41456 Fixed performance problem with variable limit
    **                           clause in a SELECT statement. In cases of
    **                           simple and very small (2 or less) record
    **                           limits (with no offset) in a SELECT, we now
    **                           inline the number directly in the SQL. In the
    **                           default implementation, Hibernate would use a
    **                           bound parameter, which could result in a poor
    **                           query plan (sequential table scan instead of
    **                           an index scan).
    ** 013 GES 20090424   @41944 Import change.
    ** 014 ECF 20090705   @43225 Appended 'analyze' to 'vacuum' command.
    ** 015 SVL 20090810   @43575 Added requiresExplicitCastInsideTernary().
    **                           Determines whether substitution parameters should
    **                           be explicitly casted to their datatypes inside
    **                           "then ? else ?" statement.
    ** 016 GES 20100701          Added a type name override to allow honoring of
    **                           precision and scale in numeric types.
    ** 017 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.
    ** 018 CA  20101202          Added method to build the URL for a remote
    **                           connection.
    ** 019 SVL 20110622          Added usesSingleBackslash.
    ** 020 OM  20121120          Implemented DDL/SQL generation for sequences.
    ** 021 SVL 20130331          Upgraded to Hibernate 4.
    ** 022 ECF 20130510          Modified sequence-related methods to not use quoted names. Replace
    **                           deprecated superclass.
    ** 023 OM  20130605          Added if exists clause to drop index statement.
    ** 024 OM  20130712          Overrided default (invalid) min/max values for sequences.
    **                           Implemented createSequenceHandler().
    ** 025 VMN 20131005          Added injectComputedColumns. Replaced $p to 50 in
    *                            registerColumnType for Types.NUMERIC.
    ** 026 OM  20130925          Replaced apache.commons logging with p2j.util.LogHelper.
    ** 027 OM  20130820          Added support for mixed asc/desc indexes.
    ** 028 OM  20140410          Fixed support case-sensitive fields in indexes.
    **                           Added Override annotations. Simplified string concatenations.
    ** 029 VMN 20140502          Added enhance schema name conversion support for hint "escape"
    **                           attribute.
    ** 030 OM  20140508          Added support for computing the index-key size.
    ** 031 OM  20140623          Added rtrim() dialect specific implementation. Added query max
    **                           parameters count support.
    ** 032 OM  20140924          Removed usesSingleBackslash.
    ** 033 OM  20150522          Added getReservedKeywords() static method.
    ** 034 OM  20151130          rtrim() dialect specific implementation returns the number of
    **                           occurrences of the expression processed. Implemented
    **                           supportsBooleanDatatype().
    ** 035 EVL 20160223          Javadoc fixes to make compatible with Oracle Java 8 for Solaris 10.
    ** 036 OM  20160603          Fixed rtrimming to space character only.
    ** 037 ECF 20160912          Added getSequencePrefetchString.
    ** 038 ECF 20190327          Replaced ContextLocal for inlineLimit variable with more efficient
    **                           ThreadLocal. It is always accessed on the same thread.
    ** 039 OM  20200610          Removed Hibernate dependecies.
    ** 040 IAS 20201204          Added id() method and support for word tables
    **     IAS 20210219          Word tables support for custom extents
    **     IAS 20210303          Added useCTE4Contains() 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
    **     IAS 20210803          Generate 'words' UDFs which can switch between using Java and SQL
    **                           low-level UDFs
    **     IAS 20211223          Special processing of errors raised by UDFs.
    **     IAS 20220112          Resolve dialect by JDBC database type.
    **     TJD 20220331          Removed reference to IOUtils.toInputStream
    **     IAS 20220601          Do not emit 'words' UDFs definitions
    **     IAS 20220602          Added isNativeUDFsSupported() method.
    **     IAS 20220712          Added getJdbcType() method.
    **     OM  20220721          Added partial conversion-time MariaDb dialect support.
    **     IAS 20220816          Added getScriptRunner() method.
    **     IAS 20220913          Re-work processing of UDFs errors/warnings.
    **     IAS 20220921          Dialect-specific UDF schema.
    **     OM  20221026          Excluded table name from index name by default. It will be added as needed by
    **                           dialects.
    **     RAA 20230109          Overrided method getSequenceSetValString().
    ** 041 RAA 20230208          Added parameter for getCreateTemporaryTablePostfix.
    ** 042 BS  20230331          Added support for sequences.
    ** 043 DDF 20230306          Added a parameter to getSqlMappedType() (refs: #7108).
    ** 044 GBB 20230512          Logging methods replaced by CentralLogger/ConversionStatus.
    ** 045 AB  20230828          Overrided getMaxEntriesInSelect().
    ** 046 DDF 20231127          Added isUniqueConstraintViolation() method.
    ** 047 OM  20231218          Added implementation for DBCODEPAGE and DBCOLLATION 4GL functions.
    ** 048 IAS 20230208          Dialect-specific error handler support.
    ** 049 OM  20240131          Fixed flaw in DBCODEPAGE / DBCOLLATION implementation.
    ** 050 DDF 20240320          Added preferDisjunctiveForm() method.
    ** 051 OM  20240909          Concurrent multitenancy functionality implementation.
    ** 052 AL2 20241105          INCLUDE the recid into the secondary indexes.
    ** 053 OM  20241128          Multi-tenant runtime support: selected the proper persistence context, eventually
    **                           based on [sharedDb] parameter.
    ** 054 OM  20250117          Added support for multiple dialects for multi-tenant landlord database.
    ** 055 RNC 20250224          Overrode isRecoverableError().
    ** 056 DDF 20240209          Added an additional parameter to the getComputedColumnString method.
    ** 057 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.nio.charset.Charset;
    import java.sql.*;
    import java.sql.Date;
    import java.text.*;
    import java.util.*;
    import java.util.function.*;
    import java.util.logging.*;
    import java.util.regex.*;
    import java.util.stream.*;
    import org.apache.commons.io.input.*;
    import org.apache.commons.lang.StringUtils;
    import com.goldencode.util.*;
    import com.goldencode.p2j.util.logging.*;
    import com.goldencode.p2j.util.ErrorManager.*;
    import com.mchange.v2.c3p0.*;
    import com.goldencode.p2j.cfg.*;
    import com.goldencode.p2j.convert.*;
    import com.goldencode.p2j.directory.DirectoryService;
    import com.goldencode.p2j.persist.*;
    import com.goldencode.p2j.persist.deploy.*;
    import com.goldencode.p2j.persist.orm.*;
    import com.goldencode.p2j.persist.sequence.*;
    import com.goldencode.p2j.util.*;

    /**
     * Extends Hibernate's <code>PostgreSQLDialect</code> class to add method implementations
     * required by the abstract {@link Dialect} base class.
     * <p>
     * Note:  this implementation expects a directory to be available at the time
     * the class is loaded.
     */
    // TODO: implement base dialect features we need
    public class P2JPostgreSQLDialect
    extends Dialect
    {
       /** Logger */
       private static final CentralLogger LOG = CentralLogger.get(P2JPostgreSQLDialect.class.getName());

       /** Allow runtime switch between word tables and UDF */
       private static final boolean useUdf4Contains = Boolean.parseBoolean(
          System.getProperty("P2JPostgreSQLDialect.useUdf4Contains", "false"));

       /** Allow runtime switch between using CTE for word tables and IN (SELECT ...) */
       private static final boolean useCte4Contains = Boolean.parseBoolean(
          System.getProperty("P2JPostgreSQLDialect.useCte4Contains", "true"));

       /** Allow runtime switch between 'pure' CTE mode and a 'mixed' one */
       private static final boolean useMixedMode4Contains = Boolean.parseBoolean(
          System.getProperty("P2JPostgreSQLDialect.useMixedMode4Contains", "true"));

       /** Regex Pattern for parsing message cased by error raised by UDF. */
       private static final Pattern UDF_ERROR_MESSAGE = Pattern.compile(
          "^(ERROR|WARNING): (\\*\\* )?(.*)(\\.)? *\\((-?[0-9]*)\\).*$",
          Pattern.DOTALL);

       /** Weights' aggregator */
       private static final WeightAggregator WEIGHT_AGGREGATOR;

       static
       {
          String aggregatorName = System.getProperty("P2JPostgreSQLDialect.weightAggregator", "SUM");
          WeightAggregator weightAggregator;
          try
          {
             weightAggregator = StringUtils.isEmpty(aggregatorName) ? null :
                                     Aggregator.valueOf(aggregatorName.toUpperCase());
          }
          catch(IllegalArgumentException e)
          {
             LOG.warning("Unknown aggregator name: [" + aggregatorName + "]");
             weightAggregator = null;
          }
          WEIGHT_AGGREGATOR = weightAggregator;
       }

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

       /** trigger function for words tables' support template */
       private static final List<String> TRIGGER_FUNC = Collections.unmodifiableList(
          Arrays.asList(
             "create or replace function %s()",
             "returns trigger",
             "language plpgsql",
             "as",
             "$$",
             "begin",
             "   delete from %s where %s.parent__id = new.%s;",
             "   insert into %s select * from words(new.%s, new.%s, %s);",
             "   return new;",
             "end;",
             "$$;"
          )
       );

       /** trigger function for words tables' support template (extent field) */
       private static final List<String> TRIGGER_FUNC_EXT = Collections.unmodifiableList(
          Arrays.asList(
             "create or replace function %s()",
             "returns trigger",
             "language plpgsql",
             "as",
             "$$",
             "begin",
             "   delete from %s where %s.parent__id = new.parent__id and %s.list__index = new.list__index;",
             "   insert into %s select * from words(new.parent__id, new.list__index, new.%s, %s);",
             "   return new;",
             "end;",
             "$$;"
          )
       );

       /** trigger function for words tables' support template (custom extent field) */
       private static final List<String> TRIGGER_FUNC_CEXT = Collections.unmodifiableList(
          Arrays.asList(
             "create or replace function %s()",
             "returns trigger",
             "language plpgsql",
             "as",
             "$$",
             "begin",
             "   delete from %s where %s.parent__id = new.recid and %s.list__index = %d;",
             "   insert into %s select * from words(new.recid, %d, new.%s, %s);",
             "   return new;",
             "end;",
             "$$;"
          )
       );

       /** ON INSERT trigger function for words tables' support template (custom extent field) */
       private static final List<String> INS_TRIGGER_FUNC_CEXT = Collections.unmodifiableList(
          Arrays.asList(
             "create or replace function %s()",
             "returns trigger",
             "language plpgsql",
             "as",
             "$$",
             "begin",
             "   delete from %s where %s.parent__id = new.recid;",
             "%s",
             "   return new;",
             "end;",
             "$$;"
          )
       );

       /** Repeatable line template for the ON INSERT trigger function for words tables'
        *  support template (custom extent field)
        */
       private static final String INS_TRIGGER_LINE =
          "   insert into %s select * from words(new.recid, %d, new.%s, %s);";

       /** 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 execute procedure %s();"
          )
       );

       /** 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 of %s",
             "   on",
             "   %s for each row execute procedure %s();"
          )
       );

       /** DROP TRIGGER statement */
       private static final String DROP_TRIGGER = "drop trigger if exists %s ON %s;";

       /** Default minimum interval in seconds between temp table vacuums */
       private static final int DEFAULT_VACUUM_INTERVAL = 30;

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

       /** Flag indicating whether to inline a record limit in an SQL SELECT */
       private static final ThreadLocal<Boolean> inlineLimit =
       new ThreadLocal<Boolean>()
       {
          protected Boolean initialValue() { return Boolean.FALSE; }
       };

       /** Lowest date supported by PostgreSQL (01/01/4713 BC) */
       private static final Date lowDate;

       /** Highest date supported by PostgreSQL (12/31/32767 AD) */
       private static final Date highDate;

       /** Actual minimum interval in milliseconds between temp table vacuums */
       private static long vacuumInterval = -1L;

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

       /* Initialization of static data. */
       static
       {
          fwd2sql.put("integer",    "int4");
          fwd2sql.put("Integer",    "int4");
          fwd2sql.put("recid",      "int4");
          fwd2sql.put("int64",      "int8");
          fwd2sql.put("Long",       "int8");
          fwd2sql.put("rowid",      "int8");
          fwd2sql.put("decimal",    "numeric(50," + SCALE_TOK + ")");
          fwd2sql.put("logical",    "bool");
          fwd2sql.put("character",  "text");
          fwd2sql.put("String",     "text");
          fwd2sql.put("handle",     "int8");
          fwd2sql.put("comhandle",  "text");
          fwd2sql.put("date",       "date");
          fwd2sql.put("datetime",   "timestamp");
          fwd2sql.put("datetimetz", "timestamp with time zone");
          fwd2sql.put("blob",       "oid");   // "bytea");
          fwd2sql.put("clob",       "text");
          fwd2sql.put("object",     "int8");
          fwd2sql.put("raw",        "bytea");

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

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

          cal.set(Calendar.ERA, GregorianCalendar.BC);
          cal.set(Calendar.YEAR, 4713);
          cal.set(Calendar.MONTH, Calendar.JANUARY);
          cal.set(Calendar.DATE, 1);
          lowDate = new Date(cal.getTime().getTime());

          cal.clear();

          cal.set(Calendar.ERA, GregorianCalendar.AD);
          cal.set(Calendar.YEAR, 32767);
          cal.set(Calendar.MONTH, Calendar.DECEMBER);
          cal.set(Calendar.DATE, 31);
          highDate = new Date(cal.getTime().getTime());
       }

       /**
        * 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 P2JPostgreSQLDialect()
       {
          super();

          // Override superclass' VARCHAR mapping, since we always use text for
          // string data.
    //      registerColumnType(Types.VARCHAR, "text");

          // Override superclass' NUMERIC mapping, since the Hibernate PostgreSQL
          // 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 PostgreSQL 9.3 reserved keywords in lowercase.
        *
        * @see     <a href="http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html">
        *          http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html</a>
        */
       public static List<String> getReservedKeywords()
       {
          String[] keywords = {
             "all",
             "analyse",
             "analyze",
             "and",
             "any",
             "array",
             "as",
             "asc",
             "asymmetric",
             "authorization",
             "binary",
             "both",
             "case",
             "cast",
             "check",
             "collate",
             "collation",
             "column",
             "concurrently",
             "constraint",
             "create",
             "cross",
             "current_catalog",
             "current_date",
             "current_role",
             "current_schema",
             "current_time",
             "current_timestamp",
             "current_user",
             "default",
             "deferrable",
             "desc",
             "distinct",
             "do",
             "else",
             "end",
             "except",
             "false",
             "fetch",
             "for",
             "foreign",
             "freeze",
             "from",
             "full",
             "grant",
             "group",
             "having",
             "ilike",
             "in",
             "initially",
             "inner",
             "intersect",
             "interval",
             "into",
             "is",
             "isnull",
             "join",
             "lateral",
             "leading",
             "left",
             "like",
             "limit",
             "localtime",
             "localtimestamp",
             "natural",
             "not",
             "notnull",
             "null",
             "offset",
             "on",
             "only",
             "or",
             "order",
             "outer",
             "over",
             "overlaps",
             "placing",
             "primary",
             "references",
             "returning",
             "right",
             "select",
             "session_user",
             "similar",
             "some",
             "symmetric",
             "table",
             "then",
             "to",
             "trailing",
             "true",
             "union",
             "unique",
             "user",
             "using",
             "variadic",
             "verbose",
             "when",
             "where",
             "window",
             "with",
          };

          return Arrays.asList(keywords);
       }

       /**
        * Report the minimum number of milliseconds between explicit vacuums.
        * This number is retrieved from the directory, or if not configured there,
        * is defaulted to a standard interval.
        *
        * @return  Minimum number of milliseconds between vacuums.
        */
       private static long getVacuumInterval()
       {
          if (vacuumInterval < 0)
          {
             // Attempt to read temp-table vacuum interval from temp-table section
             // of directory.  Missing value uses default interval, 0 disables,
             // negative number is an error.
             DirectoryService ds = DirectoryService.getInstance();
             if (!ds.bind())
             {
                throw new RuntimeException("Directory bind failed");
             }

             try
             {
                String nodeID = Utils.findDirectoryNodePath(ds, "database", "container", false);
                Integer value = null;
                if (nodeID != null)
                {
                   value = ds.getNodeInteger(nodeID + "/" + DatabaseManager.TEMP_TABLE_SCHEMA +
                                                 "/p2j/vacuum_interval",
                                             "value");
                }

                int seconds = DEFAULT_VACUUM_INTERVAL;
                if (value == null)
                {
                   if (LOG.isLoggable(Level.INFO))
                   {
                      LOG.log(Level.INFO, "Temp table vacuum interval not specified;  using default");
                   }
                }
                else
                {
                   seconds = value;
                }

                if (seconds <= 0)
                {
                   if (LOG.isLoggable(Level.WARNING))
                   {
                      LOG.log(Level.WARNING, "Temp table periodic vacuuming disabled!");
                   }
                }
                else if (LOG.isLoggable(Level.INFO))
                {
                   LOG.log(Level.INFO, "Temp table vacuum interval set to " + seconds + " seconds");
                }

                vacuumInterval = seconds * 1000;
             }
             finally
             {
                ds.unbind();
             }
          }

          return vacuumInterval;
       }

       /**
        * Get the name of the JDBC driver class associated with this dialect.
        *
        * @return  Fully qualified driver class name.
        */
       @Override
       public String getDriverClassName()
       {
          return "org.postgresql.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 "postgresql";
       }

       /**
        * Indicate whether this dialect requires computed columns to participate
        * within index definitions, instead of embedded expressions.
        *
        * @return  <code>false</code> to indicate the backing database can embed
        *          an expression within an index definition.
        */
       @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>true</code>.
        */
       @Override
       public boolean supportsFunctionOverloading()
       {
          return true;
       }

       /**
        * 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 (database.isTemporary() && getVacuumInterval() > 0) ? new Context() : 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.lastVacuum;
             if (diff >= getVacuumInterval())
             {
                int count = 0;
                try
                {
                   StringBuilder buf = new StringBuilder();
                   Iterator<String> iter = TemporaryBuffer.activeTables();
                   for (count = 0; iter.hasNext(); count++, buf.setLength(0))
                   {
                      buf.append("vacuum analyze ");
                      buf.append(iter.next());
                      persistence.executeSQL(buf.toString(), Persistence.PRIVATE_CTX);
                   }
                }
                finally
                {
                   long end = System.currentTimeMillis();
                   long elapsed =  end - now;
                   local.vacuumTime += elapsed;
                   if (debug)
                   {
                      LOG.log(Level.FINE,
                              persistence.message("Vacuum " + count + " temp table(s) [" + diff +
                                                  ":" + elapsed + ":" + local.vacuumTime + "]"));
                   }
                   local.lastVacuum = end;
                }
             }
          }
       }

       /**
        * Retrieve the earliest possible date value which is valid for the
        * backing database.
        *
        * @return  Earliest date supported by the backing database.
        */
       @Override
       public Date getLowDate()
       {
          return lowDate;
       }

       /**
        * Retrieve the latest possible date value which is valid for the backing
        * database.
        *
        * @return  Latest date supported by the backing database.
        */
       @Override
       public Date getHighDate()
       {
          return highDate;
       }

       /**
        * 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 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.
        */
       @Override
       public String extractColumnName(String expr)
       {
          final String rtrim = "rtrim(";
          final String upper = "upper(";

          String root = expr.toLowerCase();

          // Remove "upper" function if present (only applicable to text fields).
          if (root.startsWith(upper))
          {
             int len = root.length();
             root = root.substring(upper.length(), len - 1);
          }

          // Remove "rtrim" function if present (only applicable to text fields).
          if (root.startsWith(rtrim))
          {
             int commaPos = root.lastIndexOf(",");
             if (commaPos > 0)
             {
                root = root.substring(rtrim.length(), commaPos);
                if (LOG.isLoggable(Level.INFO))
                {
                   LOG.log(Level.INFO, "Old rtrim: [" + expr + "].");
                }
             }
             else
             {
                root = root.substring(rtrim.length(), root.length() - 1);
             }
          }

          // Remove data type suffix.
          int delim = root.indexOf("::");

          root = (delim >= 0 ? root.substring(0, delim) : root);

          // Strip enclosing parentheses.
          while (root.startsWith("(") && root.endsWith(")"))
          {
             root = root.substring(1, root.length() - 1);
          }

          // Strip enclosing double quotes or backticks.
          root = StringHelper.stripEnclosing(root, '"');
          root = StringHelper.stripEnclosing(root, '`');

          return root;
       }

       /**
        * Adjust a character column name appropriate to this dialect, such that it
        * can be inserted into an index.  This involves wrapping the name within
        * an rtrim function, and if <code>ignoreCase</code> is <code>true</code>,
        * an upper function.
        *
        * @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)
       {
          StringBuilder buf = new StringBuilder();
          if (ignoreCase)
          {
             buf.append("upper(");
          }
          buf.append("rtrim(").append(name).append(")");
          if (ignoreCase)
          {
             buf.append(")");
          }

          return buf.toString();
       }

       /**
        * Report whether the given column name represents an alias or an
        * expression which indicates the column is case insensitive.  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 name.toLowerCase().contains("upper(");
       }

       /**
        * 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</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 a computed column.
        */
       @Override
       public boolean isComputedColumn(String name)
       {
          return false; // PostgreSQL dialect does not use computed columns
       }

       /**
        * 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 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 "create sequence " + name + " start " + 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)
       {
          StringBuilder buf = new StringBuilder(getCreateSequenceString(name));
          buf.append(" increment ").append(increment);

          if (minVal != null)
          {
             buf.append(" minvalue ").append(minVal);
          }
          else
          {
             // default MINVALUE in PSQL is 1, must take care to avoid
             // START value (n) cannot be less than MINVALUE (1) error
             // if (increment > 0 /* && init < 1*/)
             {

                //default minvalue is: -9223372036854775807 !!
                minVal = Long.MIN_VALUE;
                buf.append(" minvalue ").append(minVal);
                LOG.log(Level.WARNING, "Fixed MINVALUE = " + minVal + " for PSQL sequence " + name);
             }
          }

          if (maxVal != null)
          {
             buf.append(" maxvalue ").append(maxVal);
          }
          else
          {
             // default MAXVALUE in PSQL is -1, must take care to avoid
             // START value (n) cannot be greater than MAXVALUE (-1) error
             // if (increment < 0 /* && init > -1 */)
             {
                maxVal = Long.MAX_VALUE;
                buf.append(" maxvalue ").append(maxVal);
                LOG.log(Level.WARNING, "Fixed MAXVALUE = " + maxVal + " for PSQL sequence " + name);
             }
          }

          buf.append(" start ").append(init);
          if (cycle != null && cycle)
          {
             buf.append(" cycle");
          }
          buf.append(" cache 1");

          return buf.toString();
       }

       /**
        * Generate a DDL string which will drop a sequence.
        *
        * @param   name
        *          The name of the sequence to be dropped.
        *
        * @return  Dialect-specific string to drop the sequence, or {@code null} if not supported.
        */
       public String getDropSequenceString(String name)
       {
          return "drop sequence if exists " + name;
       }

       /**
        * Generate the appropriate select statement to retrieve the next 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 "select setval('" + sequenceName + "', " + newVal + ")";
       }

       /**
        * Generate the appropriate select statement to retrieve the next 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 "select setval('" + sequenceName + "', ?)";
       }

       /**
        * Generate the appropriate select statement to retrieve the next value of a sequence.
        *
        * @return  Dialect-specific string to query the sequence, or
        *          <code>null</code> if not supported.
        */
       @Override
       public String getSequenceSetValString()
       {
          return "select setval(?, ?)";
       }

       /**
        * Generate the appropriate select statement to retrieve the next value of a sequence.
        *
        * @param   sequenceName
        *          The name of the sequence to be queried.
        *
        * @return  Dialect-specific string to query the sequence, or {@code null} if not supported.
        */
       @Override
       public String getSequenceNextValString(String sequenceName)
       {
          return "select nextval ('" + sequenceName + "')";
       }

       /**
        * Generate the appropriate select statement to reset the 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)
       {
          // The CURRVAL is relative to current session and will fail if called before a call to NEXTVAL or SETVAL
          return "select last_value from " + sequenceName;
       }

       /**
        * 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 true because PostgreSQL dialect fully support 4GL sequences
        */
       @Override
       public boolean hasFullSequenceSupport()
       {
          return true;
       }

       /**
        * Generate the appropriate select statement to pre-fetch multiple, ascending values from a
        * sequence. The values need not be contiguous.
        *
        * @param   sequenceName
        *          The name of the sequence from which the values are to be retrieved.
        *
        * @return  Dialect-specific SQL expression, or {@code null} if this feature is not supported
        *          by the dialect.
        */
       @Override
       public String getSequencePrefetchString(String sequenceName)
       {
          return "select nextval('" + sequenceName + "') from generate_series(?, ?)";
       }

       /**
        * Given an exception, this method will check if it indicates a database
        * connectivity error.
        * <p>
        * For the PostgreSQL dialect, if the state indicates a 08xxx error code
        * or 57P01 or 57P02 codes, the method will return <code>true</code>.
        * <p>
        * A complete list of defined error codes can be found here:
        * https://www.postgresql.org/docs/current/errcodes-appendix.html
        *
        * @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)
       {
          boolean error = false;

          Throwable next = exc;
          while (next != null && !error)
          {
             if (next instanceof SQLException)
             {
                String state = ((SQLException) next).getSQLState();

                error = state != null &&
                        (state.startsWith("08") ||
                         state.equals("57P01")  ||
                         state.equals("57P02"));
             }
             next = next.getCause();
          }

          return error;
       }

       /**
        * Given an exception, this method will check if it indicates a recoverable error.
        * <p>
        * For the PostgreSQL dialect, if the state indicates a 22P05 error code,
        * the method will return <code>true</code>.
        * <p>
        * A complete list of defined error codes can be found
        * <a href="https://www.postgresql.org/docs/current/errcodes-appendix.html">here</a>.
        *
        * @param   exc
        *          The exception to be checked.
        *
        * @return  {@code true} if the exception indicates a recoverable error, {@code false} otherwise.
        */
       @Override
       public boolean isRecoverableError(Exception exc)
       {
          boolean recoverable = false;

          Throwable next = exc;
          while (next != null && !recoverable)
          {
             if (next instanceof SQLException)
             {
                String state = ((SQLException) next).getSQLState();

                recoverable = state != null && state.equals("22P05");
             }
             next = next.getCause();
          }

          return recoverable;
       }

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

       /**
        * 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.
        */
       @Override
       public void inlineLimit(boolean on)
       {
          inlineLimit.set(on);
       }

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

       /**
        * Override the parent's implementation to inline small (2 or less) limit
        * values when there is no accompanying offset.  In other cases, we fall
        * back to the original implementation.
        * <p>
        * WARNING:  the implementation of this override has an ugly dependency on
        * the order of the logic in Hibernate's <code>Loader</code> class.
        * Specifically, we rely on the fact that this method is invoked when a
        * query is being prepared, which must happen before parameters are bound,
        * at which point {@link #supportsVariableLimit()} is invoked.  This
        * method sets state in a context local variable which is later read when
        * the latter method is invoked.
        *
        * @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.
        *
        * @see     #supportsVariableLimit()
        */
       @Override
       public String getLimitString(String sql, int offset, int limit)
       {
          String stmt;
          boolean inlinePermitted = inlineLimit.get();

          if (inlinePermitted && limit <= 2 && offset == 0)
          {
             stmt = sql + " limit " + limit;
          }
          else
          {
             if (inlinePermitted)
             {
                // Override inline request.
                inlineLimit.set(Boolean.FALSE);
             }

             stmt = super.getLimitString(sql, offset, limit);
          }

          return stmt;
       }

       /**
        * Indicate whether a variable limit is supported by this dialect.  The
        * answer is dependent upon context local state set within {@link
        * #getLimitString(String, int, int)}.  See the applicable warning in that
        * method's description.  This method is consulted during parameter binding
        * in Hibernate's <code>Loader</code> class.
        *
        * @return  <code>true</code> if a bound parameter should be used in a
        *          SELECT statement's limit clause;  <code>false</code> if the
        *          limit was inlined by {@link #getLimitString(String, int, int)}.
        */
       @Override
       public boolean supportsVariableLimit()
       {
          return inlineLimit.get() ? false : super.supportsVariableLimit();
       }

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

       /**
        * Build the DDL to create an index based upon the specified definition.
        * The SQL is generated by using a database-specific dialect.
        * <p>
        * Use the INCLUDE syntax to also embed the PK into the secondary index; this is especially
        * useful for unique indexes that do not have a trailing recid. Queries that are sorting
        * on recid, query on recid or require the recid as a SELECT expression will be able to pick it up
        * from the index entry directly, without an extra look-up into the index table
        * <p>
        * This addition is for the sake of performance and SHOULDN'T have any functional impact.
        *
        * @param   index
        *          Definition of the index to be created.
        * @param   unique
        *          If <code>true</code>, create the index as a unique index,
        *          <code>false</code> to omit the unique constraint.
        * @param   toFile
        *          <code>true</code> if the string will be output to a file or
        *          <code>false</code> if it will be directly submitted via JDBC.
        *
        * @return  The DDL which was created.
        */
       @Override
       public String getCreateIndexString(P2JIndex index, boolean unique, boolean toFile)
       {
          String fullIndex = super.getCreateIndexString(index, unique, toFile);

          if (unique || index.isUnique())
          {
             fullIndex += " include (" +  Configuration.getSchemaConfig().getPrimaryKeyName() + ")";
          }

          return fullIndex;
       }

       /**
        * 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  Always {@code null} because this dialect does not use computed columns.
        */
       @Override
       public String getComputedColumnFormula(String colName, boolean ignoreCase)
       {
          return null;
       }

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

       /**
        * 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 descending 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 ascending 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   an empty list.
        */
       @Override
       public List<String> getDatabasePrepareStatements(Database database)
       {
          return Collections.emptyList();
       }

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

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

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

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

       /**
        * Report whether the computed columns should be injected on table creating.
        *
        * @return   <code>false</code> because PostgreSQL does not support computed columns.
        */
       @Override
       public boolean injectComputedColumns()
       {
          return false;
       }

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

          return false;
       }

       /**
        * 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  always {@code null}. This dialect does not use computed columns.
        */
       @Override
       public String getComputedColumnString(String name, String type, int scale, boolean cs, boolean mandatory)
       {
          return null;
       }

       /**
        * Provides SQL to load sequences.
        *
        * @param   database
        *          database object
        * @return  sequence SQL
        */
       @Override
       public String getSequenceSQL(Database database)
       {
          return "SELECT sequence_name " +
             "FROM information_schema.sequences " +
             "WHERE sequence_catalog = '" + database.getName() + "'";
       }

       /**
        * 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 (int8)
        */
       public int getSyntheticIndexSize(boolean unique)
       {
          return unique ? 0 : 8;
       }

       /**
        * Composes a rtrim equivalent call for a specified expr. This is dialect specific.
        * The returned value looks like:
        * <pre>
        *    rtrim(expr)
        * </pre>
        * The rtrim'd 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 database server.
        *
        * @return  Always 0 because PostgreSQL has no such limitation.
        */
       @Override
       public int getMaxParameterCount()
       {
          return 0;
       }

       private enum Aggregator implements WeightAggregator
       {
          SUM
          {
             @Override
             public BiConsumer<StringBuilder, Runnable> aggregate()
             {
                return (sb, r) -> {sb.append("sum(cast("); r.run(); sb.append(" as int))");};
             }

             @Override
             public String minAggregatedValue()
             {
                return "-1";
             }

          },
          MAX
          {
             @Override
             public BiConsumer<StringBuilder, Runnable> aggregate()
             {
                return (sb, r) -> {sb.append("max(cast("); r.run(); sb.append(" as int))");};
             }

             @Override
             public String minAggregatedValue()
             {
                return "-1";
             }

          },

          OR
          {
             @Override
             public BiConsumer<StringBuilder, Runnable> aggregate()
             {
                return (sb, r) -> {sb.append("bool_or("); r.run(); sb.append(")");};
             }

             @Override
             public String minAggregatedValue()
             {
                return "false";
             }

          }
       }

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

       /**
        * Return the ending of the DDL statement that creates a temporary table.
        *
        * @param   noUndo
        *          This parameter is not used at the moment. The intention was to generate a keyword for
        *          no-undo tables. Only {@link P2JH2Dialect} benefits from this parameter.
        *
        * @return  A string representing the ending of the DDL statement that creates a temporary
        *          table.
        */
       @Override
       public String getCreateTemporaryTablePostfix(boolean noUndo)
       {
          return " on commit drop";
       }

       /**
        * 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 "bool";
             case Types.INTEGER: return "int4";
             case Types.BIGINT: return "int8";
             case Types.NUMERIC: return "numeric";
             case Types.DATE: return "date";
             case Types.VARBINARY: return "bytea";
             case Types.TIMESTAMP: return "timestamp";
             case Types.TIMESTAMP_WITH_TIMEZONE: return "timestamp with time zone";
             case Types.ARRAY: return "array";
             case Types.BLOB: return "oid";
             case Types.VARCHAR: // fall through
             case Types.CLOB: return "text";
             default: return type.toString(); // maybe log this?
          }
       }

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

          switch (type.toUpperCase())
          {
             case "BOOL": return ParmType.LOG;
             case "INT4": return ParmType.INT;
             case "INT8": return ParmType.INT64;
             case "NUMERIC": return ParmType.DEC;
             case "TEXT": return ParmType.CHAR;
             case "DATE": return ParmType.DATE;
             case "TIMESTAMP": return ParmType.DT;
             case "TIMESTAMPTZ": return ParmType.DTTZ;
             case "OID": return ParmType.BLOB;
             case "BYTEA": return ParmType.RAW;
             default:
                throw new IllegalArgumentException("DB column type " + type + " is not supported");
          }
       }

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

       /**
        * Get a new instance of a splitter for DDL scripts.
        *
        * @return  the splitter for DDL scripts.
        */
       @Override
       public ScriptSplitter scriptSplitter()
       {
          return new PgScriptSplitter();
       }

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

       /**
        * 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)
       {
          return new FWDClob(value);
       }

       /**
        * 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 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 boolean useWordTables()
       {
          return !useUdf4Contains;
       }

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

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

       /**
        * Check if the exception was caused by error in UDF.
        * @param e
        *        the exception in question
        * @return <code>true</code> if the exception was caused by error in UDF.
        */
       @Override
       public boolean isUdfOriginatedError(SQLException e)
       {
          return "FWD00".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.
        */
       public boolean isUdfOriginatedWarning(SQLException e)
       {
          return "FWD00".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
        */
       @Override
       public boolean useCTE4Contains()
       {
          return useCte4Contains || useMixedMode4Contains;
       }

       /**
        * 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
        */
       @Override
       public boolean useMixedNode4Contains()
       {
          return useMixedMode4Contains;
       }

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

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

       /**
        * Get c3p0 ConnectionCustomizer class for the dialect.
        * @param <T>
        *        Customizer class
        * @param cfg
        *        Database configuration.
        * @return c3p0 ConnectionCustomizer class for the dialect.
        */
       @Override
       public Class<? extends AbstractConnectionCustomizer> connectionCustomizer(DatabaseConfig cfg)
       {
          return cfg.isUseJavaUDFs() ? null : FWDPostgreSQLConnectionCustomizer.class;
       }
       /**
        * 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)
       {
          String triggerFunc = TRIGGER_FUNC.stream().collect(Collectors.joining(eoln));
          String triggerFuncExt = TRIGGER_FUNC_EXT.stream().collect(Collectors.joining(eoln));
          String triggerFuncCExt = TRIGGER_FUNC_CEXT.stream().collect(Collectors.joining(eoln));
          String insTriggerFuncCExt = INS_TRIGGER_FUNC_CEXT.stream().collect(Collectors.joining(eoln));
          String afterInsertTrigger = AFTER_INSERT_TRIGGER.stream().collect(Collectors.joining(eoln));
          String afterUpdateTrigger = AFTER_UPDATE_TRIGGER.stream().collect(Collectors.joining(eoln));
          wordTables.stream().forEach(wt -> {
             if (wt.customExtentFields == null)
             {
                if (wt.extent == 0)
                {
                   out.printf(triggerFunc,
                         wt.triggerFunctionName, wt.tableName, wt.tableName, PK,
                         wt.tableName, PK, wt.fieldName, !wt.caseSensitive);
                }
                else
                {
                   out.printf(triggerFuncExt,
                         wt.triggerFunctionName,
                         wt.tableName, wt.tableName, wt.tableName, wt.tableName,
                         wt.fieldName, !wt.caseSensitive);
                }
                out.print(eoln);
                out.print(eoln);
                out.printf(DROP_TRIGGER, wt.afterUpdateTriggerName, wt.parentTableName);
                out.print(eoln);
                out.print(eoln);
                out.printf(afterUpdateTrigger,
                      wt.afterUpdateTriggerName, wt.fieldName, wt.parentTableName, wt.triggerFunctionName);
                out.print(eoln);
                out.print(eoln);
                out.printf(DROP_TRIGGER, wt.afterInsertTriggerName, wt.parentTableName);
                out.print(eoln);
                out.print(eoln);
                out.printf(afterInsertTrigger,
                      wt.afterInsertTriggerName, wt.parentTableName, wt.triggerFunctionName);
                out.print(eoln);
                out.print(eoln);
             }
             else
             {
                for (CustomExtentField f: wt.customExtentFields)
                {
                   out.printf(triggerFuncCExt,
                         f.triggerFunctionName,
                         wt.tableName, wt.tableName, wt.tableName, f.pos,
                         wt.tableName, f.pos, f.fieldName, !wt.caseSensitive
                   );
                   out.print(eoln);
                   out.print(eoln);
                   out.printf(DROP_TRIGGER, f.afterUpdateTriggerName, wt.parentTableName);
                   out.print(eoln);
                   out.print(eoln);
                   out.printf(afterUpdateTrigger,
                         f.afterUpdateTriggerName, f.fieldName, wt.parentTableName,
                         f.triggerFunctionName
                   );
                   out.print(eoln);
                   out.print(eoln);
                }
                out.printf(DROP_TRIGGER, wt.afterInsertTriggerName, wt.parentTableName);
                out.print(eoln);
                out.print(eoln);
                String lines = wt.customExtentFields.stream().map(f -> String.format(
                                        INS_TRIGGER_LINE,
                                        wt.tableName, f.pos, f.fieldName, !wt.caseSensitive )).
                                  collect(Collectors.joining(eoln));
                out.printf(insTriggerFuncCExt,
                      wt.triggerFunctionName,
                      wt.tableName, wt.tableName,
                      lines);
                out.print(eoln);
                out.print(eoln);
                out.printf(afterInsertTrigger,
                      wt.afterInsertTriggerName, wt.parentTableName, wt.triggerFunctionName);
                out.print(eoln);
                out.print(eoln);
             }
          });
       }

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

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

       /**
        * Obtain the name of an index. This dialect overrides this method in order to make sure the name is unique
        * among tables because in PSQL 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();
       }

       /** Obtains an object responsible with providing dialect specific SQL statements for tenant management. */
       @Override
       public 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        TEXT NOT NULL,\n" +
                       "       tenant_description TEXT,\n" +
                       "       tenant_info        TEXT,\n" +
                       "       status             BOOL,\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 TEXT NOT NULL,\n" +
                       "       pdb_name    TEXT NOT NULL,\n" +
                       "       ldb_name    TEXT NOT NULL,\n" +
                       "       url         TEXT,\n" +
                       "       username    TEXT,\n" +
                       "       password    TEXT,\n" +
                       "       " + C3P0_MAX_STMTS + " INT4,\n" +
                       "       " + C3P0_MIN_POOL  + " INT4,\n" +
                       "       " + C3P0_MAX_POOL  + " INT4,\n" +
                       "       " + C3P0_ACQ_INC   + " INT4,\n" +
                       "       " + C3P0_MAX_IDLE  + " INT4,\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)";
             }
          };
       }

       /*
        * Returns the maximum admitted number of columns selected. For PSQL the number is 1664.
        *
        * @return limitation of the PSQL
        */
       @Override
       public int getMaxEntriesInSelect()
       {
          return 1664;
       }

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

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

          // fallback for all errors
          return null;
       }

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

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

          // fallback for all errors
          return null;
       }

       /**
        * 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.
        */
       @Override
       public boolean preferDisjunctiveForm()
       {
          return true;
       }

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

          if ("en_US@p2j_basic".equals(collation) || "C".equals(collation) || "POSIX".equals(collation))
          {
             return "basic"; // FWD 3.0 legacy
          }

          int i = collation.indexOf("_fwd_");
          if (i > 0)
          {
             collation = collation.substring(0, i);
             i = collation.indexOf('@');
             if (i < 0)
             {
                i = collation.lastIndexOf('_');
             }
             if (i > 0)
             {
                collation = collation.substring(i + 1);

                switch (collation.toUpperCase())
                {
                   case "ISO88591":  return "ISO8859-1";
                   case "ISO885915": return "ISO8859-15";
                   case "UTF8":      return "UTF-8";
                   case "CP1252":    return "1252";
                // case "IBM850":    return "IBM850";
                // case "IBM852":    return "IBM852";
                   default:          return collation;
                }
             }
          }
          else
          {
             i = collation.lastIndexOf('.');
             if (i > 0)
             {
                collation = collation.substring(i + 1);

                switch (collation.toUpperCase())
                {
                   case "ISO88591":  return "ISO8859-1";
                   case "ISO885915": return "ISO8859-15";
                   case "UTF8":      return "UTF-8";
                   case "CP1252":    return "1252";
                   // case "IBM850":    return "IBM850";
                   // case "IBM852":    return "IBM852";
                   default:          return collation;
                }
             }
          }
          return collation;
       }

       /**
        * Maps the PostgreSQL specific collation names to 4GL collation names.
        *
        * @param   collation
        *          A PostgreSQL specific collation name.
        *          (Ex: "en_US@p2j_basic", "en_US.utf8", "C", "en_US@iso88591_fwd_basic" (default))
        *
        * @return  The 4GL codepage name of {@code h2Charset}.
        */
       private static String mapCollation(String collation)
       {
          if (collation == null)
          {
             return null;
          }

          if ("en_US@p2j_basic".equals(collation) || "C".equals(collation) || "POSIX".equals(collation))
          {
             return "basic"; // FWD 3.0 legacy
          }

          int i = collation.indexOf("_fwd_");
          if (i > 0)
          {
             return collation.substring(i + 5 /* "_fwd_".length */);
          }
          return collation;
       }

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

          /** Accumulated time spent vacuuming temp tables */
          private long vacuumTime = 0L;
       }

       /**
        * Read-only implementation of a BLOB instance, to be used by the JDBC driver.
        */
       private static class FWDBlob
       implements Blob
       {
          /** The data. */
          private byte[] data;

          /**
           * Create a new CLOB instance.
           *
           * @param    data
           *           The data.
           */
          public FWDBlob(byte[] data)
          {
             this.data = data;
          }

          /**
           * Get the {@link #data} length.
           *
           * @return   See above.
           */
          @Override
          public long length()
          throws SQLException
          {
             return data.length;
          }

          /**
           * Get the sub-array starting from the given position, and with the specified length.
           *
           * @param    pos
           *           The 1-based position.
           * @param    length
           *           The length.
           *
           * @return   See above.
           */
          @Override
          public byte[] getBytes(long pos, int length)
          throws SQLException
          {
             return Arrays.copyOfRange(data, (int) pos - 1, length);
          }

          /**
           * Get the binary stream for the data.
           *
           * @return   See above.
           */
          @Override
          public InputStream getBinaryStream()
          throws SQLException
          {
             return new ByteArrayInputStream(data);
          }

          /**
           * Get the binary stream starting from the given position, and with the specified length.
           *
           * @param    pos
           *           The 1-based position.
           * @param    length
           *           The length.
           *
           * @return   See above.
           */
          @Override
          public InputStream getBinaryStream(long pos, long length)
          throws SQLException
          {
             return new ByteArrayInputStream(getBytes(pos, (int) length));
          }

          /**
           * Release the {@link #data}.
           */
          @Override
          public void free() throws SQLException
          {
             this.data = null;
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public long position(byte[] pattern, long start)
          throws SQLException
          {
             throw new SQLException("FWDBlob:position");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public long position(Blob pattern, long start)
          throws SQLException
          {
             throw new SQLException("FWDBlob:position");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public int setBytes(long pos, byte[] bytes)
          throws SQLException
          {
             throw new SQLException("FWDBlob:setBytes");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public int setBytes(long pos, byte[] bytes, int offset, int len)
          throws SQLException
          {
             throw new SQLException("FWDBlob:setBytes");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public OutputStream setBinaryStream(long pos)
          throws SQLException
          {
             throw new SQLException("FWDBlob:setBinaryStream");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public void truncate(long len)
          throws SQLException
          {
             throw new SQLException("FWDBlob:truncate");
          }
       }

       /**
        * Read-only implementation of a CLOB instance, to be used by the JDBC driver.
        */
       private static class FWDClob
       implements Clob
       {
          /** The data. */
          private String data;

          /**
           * Create a new CLOB instance.
           *
           * @param    data
           *           The data.
           */
          public FWDClob(String data)
          {
             this.data = data;
          }

          /**
           * Get the {@link #data} length.
           *
           * @return   See above.
           */
          @Override
          public long length()
          throws SQLException
          {
             return data.length();
          }

          /**
           * Get the substring starting from the given position, and with the specified length.
           *
           * @param    pos
           *           The 1-based position.
           * @param    length
           *           The length.
           *
           * @return   See above.
           */
          @Override
          public String getSubString(long pos, int length)
          throws SQLException
          {
             return data.substring((int) pos, length);
          }

          /**
           * Get the character stream.
           *
           * @return   See above.
           */
          @Override
          public Reader getCharacterStream()
          throws SQLException
          {
             return new CharSequenceReader(data);
          }

          /**
           * Get the ASCII stream.
           *
           * @return   See above.
           */
          @Override
          public InputStream getAsciiStream()
          throws SQLException
          {
             return new ByteArrayInputStream(data.getBytes(Charset.defaultCharset()));
          }

          /**
           * Get the character stream starting from the given position, and with the specified length.
           *
           * @param    pos
           *           The 1-based position.
           * @param    length
           *           The length.
           *
           * @return   See above.
           */
          @Override
          public Reader getCharacterStream(long pos, long length)
          throws SQLException
          {
             return new CharSequenceReader(getSubString(pos, (int) length));
          }

          /**
           * Release the {@link #data}.
           */
          @Override
          public void free()
          throws SQLException
          {
             this.data = null;
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public long position(String searchstr, long start)
          throws SQLException
          {
             throw new SQLException("FWDClob:position");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public long position(Clob searchstr, long start)
          throws SQLException
          {
             throw new SQLException("FWDClob:position");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public int setString(long pos, String str)
          throws SQLException
          {
             throw new SQLException("FWDClob:setString");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public int setString(long pos, String str, int offset, int len)
          throws SQLException
          {
             throw new SQLException("FWDClob:setString");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public OutputStream setAsciiStream(long pos)
          throws SQLException
          {
             throw new SQLException("FWDClob:setAsciiStream");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public Writer setCharacterStream(long pos)
          throws SQLException
          {
             throw new SQLException("FWDClob:setCharacterStream");
          }

          /**
           * Operation not allowed.
           *
           * @throws  SQLException
           *          Always
           */
          @Override
          public void truncate(long len)
          throws SQLException
          {
             throw new SQLException("FWDClob:truncate");
          }
       }

       /**
        * UDF types
        */
       public static enum UDF_TYPE
       {
          /** Java UDFs */
          JAVA,

          /** SQL UDFs */
          SQL;

          /**
           * Get configured UDF types.
           * @param dbName
           *        database name
           * @return list of configured UDF types.
           */
          public static Set<UDF_TYPE> config(String dbName)
          {
             String udfTypes = Configuration.getSchemaConfig().
                   getNamespaceParameter(dbName, "postgresql/udf");
             if (udfTypes == null)
             {
                return EnumSet.of(JAVA);
             }
             return Arrays.stream(udfTypes.split(",")).
                      map(String::toUpperCase).map(String::trim).map(UDF_TYPE::valueOf).
                      collect(Collectors.toSet());
          }

          /**
           * Check if SQL UDFs should be used with the database.
           * @param dbName
           *        database name
           * @return <code>true</code>
           */
          public static boolean useSqlUdfs(String dbName)
          {
             return config(dbName).contains(SQL);
          }
       }

       /** Splitter for DDL scripts. */
       public static class PgScriptSplitter
       implements ScriptSplitter
       {
          /** The current state of the automaton. */
          protected State currentState = State.GAP;

          /**
           * Forcibly set the splitter state
           * @param state
           *        new state;
           */
          public void state(State state)
          {
             this.currentState = state;
          }

          /**
           * Process next line and return a new state
           * @param line
           *        next sline to be processed
           * @return new state
           */
          public State state(String line)
          {
             if (line == null)
             {
                return (currentState = State.GAP);
             }

             String s = line.trim();
             if (currentState == State.COMMENT)
             {
                if (s.endsWith("*/"))
                {
                   return (currentState = State.GAP);
                }
                else
                {
                   return currentState;
                }
             }

             if (s.startsWith("/*"))
             {
                if (currentState == State.DDL)
                {
                   return currentState;
                }
                return currentState = s.endsWith("*/") ? State.GAP : State.COMMENT;
             }

             if (s.isEmpty())
             {
                return currentState;
             }

             currentState = line.startsWith(";") ? State.GAP : State.DDL;
             return currentState;
          }
       }
    }