Dépôt officiel du code source de l'ERP OpenConcerto

/*

 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.

 *

 * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.

 *

 * The contents of this file are subject to the terms of the GNU General Public License Version 3

 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a

 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific

 * language governing permissions and limitations under the License.

 *

 * When distributing the software, include this License Header Notice in each file.

 */

 package org.openconcerto.sql.model;

import org.openconcerto.sql.changer.correct.FixSerial;

import org.openconcerto.sql.model.SQLField.Properties;

import org.openconcerto.sql.model.SQLTable.SQLIndex;

import org.openconcerto.sql.model.graph.TablesMap;

import org.openconcerto.sql.utils.ChangeTable.ClauseType;

import org.openconcerto.sql.utils.SQLUtils;

import org.openconcerto.utils.CollectionUtils;

import org.openconcerto.utils.CompareUtils;

import org.openconcerto.utils.FileUtils;

import org.openconcerto.utils.ListMap;

import org.openconcerto.utils.StringUtils;

import org.openconcerto.utils.Tuple2;

import org.openconcerto.utils.cc.ITransformer;

import org.openconcerto.xml.XMLCodecUtils;

import java.beans.DefaultPersistenceDelegate;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.math.BigDecimal;

import java.sql.Array;

import java.sql.Blob;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.Collections;

import java.util.Comparator;

import java.util.Date;

import java.util.IdentityHashMap;

import java.util.List;

import java.util.Map;

import java.util.Set;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.commons.dbcp.DelegatingConnection;

import org.postgresql.PGConnection;

/**

 * To require SSL, set the "ssl" connection property to "true" (note: for now any value, including

 * "false" enables it), to disable server validation set "sslfactory" to

 * "org.postgresql.ssl.NonValidatingFactory". To check the connection status, install the

 * contrib/sslinfo extension and execute "select ssl_is_used();". SSL Compression might be supported

 * if we can find a good sslfactory : see this

 * <a href="http://archives.postgresql.org/pgsql-general/2010-08/thrd5.php#00003">thread</a>.

 * <p>

 * To enable SSL on the server see http://www.postgresql.org/docs/current/static/ssl-tcp.html

 * (already set up on Ubuntu).

 * <p>

 *

 * @author Sylvain CUAZ

 */

public class SQLSyntaxPG extends SQLSyntax {

    // From http://www.postgresql.org/docs/9.0/interactive/multibyte.html

    static final short MAX_BYTES_PER_CHAR = 4;

    // http://www.postgresql.org/docs/9.0/interactive/datatype-character.html

    private static final short MAX_LENGTH_BYTES = 4;

    // https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F

    private static final int MAX_FIELD_SIZE = 1024 * 1024 * 1024;

    private static final int MAX_VARCHAR_L = (MAX_FIELD_SIZE - MAX_LENGTH_BYTES) / MAX_BYTES_PER_CHAR;

    static private final IdentityHashMap<String, String> DATE_SPECS;

    static {

        DATE_SPECS = new IdentityHashMap<String, String>();

        DATE_SPECS.put(DateProp.YEAR, "YYYY");

        DATE_SPECS.put(DateProp.MONTH_NAME, "TMmonth");

        DATE_SPECS.put(DateProp.MONTH_NUMBER, "MM");

        DATE_SPECS.put(DateProp.DAY_IN_MONTH, "DD");

        DATE_SPECS.put(DateProp.DAY_NAME_IN_WEEK, "TMday");

        DATE_SPECS.put(DateProp.HOUR, "HH24");

        DATE_SPECS.put(DateProp.MINUTE, "MI");

        DATE_SPECS.put(DateProp.SECOND, "SS");

        DATE_SPECS.put(DateProp.MICROSECOND, "US");

        XMLCodecUtils.register(SQLSyntaxPG.class, new DefaultPersistenceDelegate(new String[] {}));

    }

    public SQLSyntaxPG() {

        super(SQLSystem.POSTGRESQL, DATE_SPECS);

        this.typeNames.addAll(Boolean.class, "boolean", "bool", "bit");

        this.typeNames.addAll(Short.class, "smallint", "int2");

        this.typeNames.addAll(Integer.class, "integer", "int", "int4");

        this.typeNames.addAll(Long.class, "bigint", "int8");

        this.typeNames.addAll(BigDecimal.class, "decimal", "numeric");

        this.typeNames.addAll(Float.class, "real", "float4");

        this.typeNames.addAll(Double.class, "double precision", "float8");

        // since 7.3 default is without timezone

        this.typeNames.addAll(Timestamp.class, "timestamp", "timestamp without time zone");

        this.typeNames.addAll(java.sql.Date.class, "date");

        this.typeNames.addAll(java.sql.Time.class, "time", "time without time zone");

        this.typeNames.addAll(Blob.class, "bytea");

        // even though PG treats all Character Types equally, unbounded varchar is not standard, so

        // prefer "text" which is supported by all systems

        this.typeNames.addAll(Clob.class, "text", "varchar", "char", "character varying", "character");

        this.typeNames.addAll(String.class, "varchar", "char", "character varying", "character", "text");

    }

    static final Pattern BACKSLASH_PATTERN = Pattern.compile("\\", Pattern.LITERAL);

    static final String TWO_BACKSLASH_REPLACEMENT = Matcher.quoteReplacement("\\\\");

    @Override

    public final String quoteString(String s) {

        final String res = super.quoteString(s);

        if (s == null)

            return res;

        // see PostgreSQL Documentation 4.1.2.1 String Constants

        // escape \ by replacing them with \\

        final Matcher matcher = BACKSLASH_PATTERN.matcher(res);

        // only use escape form if needed (=> equals with other systems most of the time)

        return matcher.find() ? "E" + matcher.replaceAll(TWO_BACKSLASH_REPLACEMENT) : res;

    }

    @Override

    public int getMaximumIdentifierLength() {

        // http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html

        return 63;

    }

    public String getInitRoot(final String name) {

        final String sql;

        try {

            final String fileContent = FileUtils.readUTF8(SQLSyntaxPG.class.getResourceAsStream("pgsql-functions.sql"));

            sql = fileContent.replace("${rootName}", SQLBase.quoteIdentifier(name));

        } catch (IOException e) {

            throw new IllegalStateException("cannot read functions", e);

        }

        return sql;

    }

    @Override

    protected Tuple2<Boolean, String> getCast() {

        return Tuple2.create(false, "::");

    }

    public String getIDType() {

        return " int";

    }

    @Override

    public boolean isAuto(SQLField f) {

        return "YES".equals(f.getMetadata("IS_AUTOINCREMENT"));

    }

    public String getAuto() {

        return " serial";

    }

    @Override

    public int getMaximumVarCharLength() {

        return MAX_VARCHAR_L;

    }

    private String changeFKChecks(DBRoot r, final String action) {

        String res = r.getBase().quote("select %i.getTables(%s, '.*', 'tables_changeFKChecks');", r.getName(), r.getName());

        res += r.getBase().quote("select %i.setTrigger('" + action + "', 'tables_changeFKChecks');", r.getName());

        res += "close \"tables_changeFKChecks\";";

        return res;

    }

    @Override

    public String disableFKChecks(DBRoot b) {

        // MAYBE return "SET CONSTRAINTS ALL DEFERRED";

        // NOTE: CASCADE, SET NULL, SET DEFAULT cannot be deferred (as of 9.1)

        return this.changeFKChecks(b, "DISABLE");

    }

    @Override

    public String enableFKChecks(DBRoot b) {

        // MAYBE return "SET CONSTRAINTS ALL IMMEDIATE";

        return this.changeFKChecks(b, "ENABLE");

    }

    @SuppressWarnings("unchecked")

    @Override

    // override since pg driver do not return FILTER_CONDITION

    public List<Map<String, Object>> getIndexInfo(SQLTable t) throws SQLException {

        final String query = "SELECT NULL AS \"TABLE_CAT\",  n.nspname as \"TABLE_SCHEM\",\n"

                //

                + "ct.relname as \"TABLE_NAME\", NOT i.indisunique AS \"NON_UNIQUE\",\n"

                //

                + "NULL AS \"INDEX_QUALIFIER\", ci.relname as \"INDEX_NAME\",\n"

                //

                + "NULL as \"TYPE\", col.attnum as \"ORDINAL_POSITION\",\n"

                //

                + "CASE WHEN i.indexprs IS NULL THEN col.attname ELSE pg_get_indexdef(ci.oid,col.attnum,false) END AS \"COLUMN_NAME\",\n"

                //

                + "NULL AS \"ASC_OR_DESC\", ci.reltuples as \"CARDINALITY\", ci.relpages as \"PAGES\",\n"

                //

                + "pg_get_expr(i.indpred,ct.oid) as \"FILTER_CONDITION\"\n"

                //

                + "FROM pg_catalog.pg_class ct\n"

                //

                + "     JOIN pg_catalog.pg_namespace n ON n.oid = ct.relnamespace\n"

                //

                + "     JOIN pg_catalog.pg_index i ON ct.oid=i.indrelid\n"

                //

                + "     JOIN pg_catalog.pg_class ci ON ci.oid=i.indexrelid\n"

                //

                + "     JOIN pg_catalog.pg_attribute col ON col.attrelid = ci.oid\n"

                //

                + "WHERE ci.relkind IN ('i','') AND n.nspname <> 'pg_catalog' AND n.nspname !~ '^pg_toast'\n"

                //

                + " AND n.nspname = " + quoteString(t.getSchema().getName()) + " AND ct.relname = " + quoteString(t.getName()) + "\n"

                //

                + "ORDER BY \"NON_UNIQUE\", \"TYPE\", \"INDEX_NAME\", \"ORDINAL_POSITION\";";

        // don't cache since we don't listen on system tables

        return (List<Map<String, Object>>) t.getDBSystemRoot().getDataSource().execute(query, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false));

    }

    protected String setNullable(SQLField f, boolean b) {

        return "ALTER COLUMN " + f.getQuotedName() + " " + (b ? "DROP" : "SET") + " NOT NULL";

    }

    @Override

    public Map<ClauseType, List<String>> getAlterField(SQLField f, Set<Properties> toAlter, String type, String defaultVal, Boolean nullable) {

        final List<String> res = new ArrayList<String>();

        if (toAlter.contains(Properties.NULLABLE))

            res.add(this.setNullable(f, nullable));

        final String newType;

        if (toAlter.contains(Properties.TYPE)) {

            newType = type;

            res.add("ALTER COLUMN " + f.getQuotedName() + " TYPE " + newType);

        } else

            newType = getType(f);

        if (toAlter.contains(Properties.DEFAULT))

            res.add(this.setDefault(f, defaultVal));

        return ListMap.singleton(ClauseType.ALTER_COL, res);

    }

    @Override

    public String getDropRoot(String name) {

        return "DROP SCHEMA IF EXISTS " + SQLBase.quoteIdentifier(name) + " CASCADE ;";

    }

    @Override

    public String getCreateRoot(String name) {

        return "CREATE SCHEMA " + SQLBase.quoteIdentifier(name) + " ;";

    }

    @Override

    public String getDropPrimaryKey(SQLTable t) {

        return getDropConstraint() + SQLBase.quoteIdentifier(t.getConstraint(ConstraintType.PRIMARY_KEY, t.getPKsNames()).getName());

    }

    @Override

    public String getDropIndex(String name, SQLName tableName) {

        return "DROP INDEX IF EXISTS " + new SQLName(tableName.getItemLenient(-2), name).quote() + " ;";

    }

    @Override

    protected String getCreateIndex(final String cols, final SQLName tableName, SQLIndex i) {

        final String method = i.getMethod() != null ? " USING " + i.getMethod() : "";

        return "ON " + tableName.quote() + " " + method + cols;

    }

    @SuppressWarnings("unused")

    private final String getIndexesReq(String schema, String tablePattern) {

        return "SELECT pg_catalog.pg_get_indexdef(i.indexrelid), c2.relname, i.indisunique, i.indisclustered, i.indisvalid" +

        // FROM

                " FROM pg_catalog.pg_class c" +

                //

                " LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace" +

                //

                " LEFT JOIN pg_catalog.pg_index i ON c.oid = i.indrelid" +

                //

                " LEFT JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid" +

                // WHERE

                " WHERE c.relname ~ '" + tablePattern + "' and n.nspname = '" + schema + "'" + " and i.indisprimary = FALSE;";

    }

    protected boolean supportsPGCast() {

        return true;

    }

    @Override

    public boolean isDeadLockException(SQLException exn) {

        return SQLUtils.findWithSQLState(exn).getSQLState().equals("40P01");

    }

    @Override

    public boolean isTableNotFoundException(Exception exn) {

        return SQLUtils.findWithSQLState(exn).getSQLState().equals("42P01");

    }

    @Override

    public String getSetLockTimeoutQuery(int millis) {

        return "SET lock_timeout to " + millis;

    }

    @Override

    public String getShowLockTimeoutQuery() {

        final String interval = cast("setting||unit", "INTERVAL");

        return "select  CAST( EXTRACT(milliseconds from " + interval + ") as int) from pg_settings where \"name\" = 'lock_timeout'";

    }

    private static final Pattern NOW_PTRN = Pattern.compile("\\(?'now'::text\\)?(::timestamp)");

    @Override

    public String transfDefaultJDBC2SQL(SQLField f) {

        if (f.getDefaultValue() != null && Date.class.isAssignableFrom(f.getType().getJavaType())) {

            // pg returns ('now'::text)::timestamp without time zone for CURRENT_TIMESTAMP

            // replace() handles complex defaults, e.g. now + '00:00:10'::interval

            return NOW_PTRN.matcher(f.getDefaultValue().toString()).replaceAll("CURRENT_TIMESTAMP$1");

        } else {

            return super.transfDefaultJDBC2SQL(f);

        }

    }

    @Override

    public void _loadData(final File f, final SQLTable t) throws IOException, SQLException {

        final String copy = "COPY " + t.getSQLName().quote() + " FROM STDIN " + getDataOptions() + ";";

        final Number count = t.getDBSystemRoot().getDataSource().useConnection(new ConnectionHandlerNoSetup<Number, IOException>() {

            @Override

            public Number handle(SQLDataSource ds) throws SQLException, IOException {

                FileInputStream in = null;

                try {

                    in = new FileInputStream(f);

                    final Connection conn = ((DelegatingConnection) ds.getConnection()).getInnermostDelegate();

                    return ((PGConnection) conn).getCopyAPI().copyIn(copy, in);

                } finally {

                    if (in != null)

                        in.close();

                }

            }

        });

        final SQLName seq = FixSerial.getPrimaryKeySeq(t);

        // no need to alter sequence if nothing was inserted (can be -1 in old pg)

        // also avoid NULL for empty tables and thus arbitrary start constant

        if (count.intValue() != 0 && seq != null) {

            t.getDBSystemRoot().getDataSource().execute(t.getBase().quote("select %n.\"alterSeq\"( %s, 'select max(%n)+1 from %f');", t.getDBRoot(), seq, t.getKey(), t));

        }

    }

    private String getDataOptions() {

        return " WITH NULL " + quoteString("\\N") + " CSV HEADER QUOTE " + quoteString("\"") + " ESCAPE AS " + quoteString("\\");

    }

    @Override

    protected void _storeData(final SQLTable t, final File f) throws IOException {

        // if there's no fields, there's no data

        if (t.getFields().size() == 0)

            return;

        final String cols = CollectionUtils.join(t.getOrderedFields(), ",", new ITransformer<SQLField, String>() {

            @Override

            public String transformChecked(SQLField f) {

                return SQLBase.quoteIdentifier(f.getName());

            }

        });

        // you can't specify line separator to pg, so use STDOUT as it always use \n

        try {

            final String sql = "COPY (" + selectAll(t).asString() + ") to STDOUT " + getDataOptions() + " FORCE QUOTE " + cols + " ;";

            t.getDBSystemRoot().getDataSource().useConnection(new ConnectionHandlerNoSetup<Number, IOException>() {

                @Override

                public Number handle(SQLDataSource ds) throws SQLException, IOException {

                    final Connection conn = ((DelegatingConnection) ds.getConnection()).getInnermostDelegate();

                    FileOutputStream out = null;

                    try {

                        out = new FileOutputStream(f);

                        return ((PGConnection) conn).getCopyAPI().copyOut(sql, out);

                    } finally {

                        if (out != null)

                            out.close();

                    }

                }

            });

        } catch (Exception e) {

            throw new IOException("unable to store " + t + " into " + f, e);

        }

    }

    static SQLSelect selectAll(final SQLTable t) {

        final SQLSelect sel = new SQLSelect(true);

        for (final SQLField field : t.getOrderedFields()) {

            // MySQL despite accepting 'boolean', 'true' and 'false' keywords doesn't really

            // support booleans

            if (field.getType().getJavaType() == Boolean.class)

                sel.addRawSelect("cast(" + field.getFieldRef() + " as integer)", field.getName());

            else

                sel.addSelect(field);

        }

        return sel;

    }

    @Override

    public String getChar(int asciiCode) {

        return "chr(" + asciiCode + ")";

    }

    @Override

    public String getRegexpOp(boolean negation) {

        return negation ? "!~" : "~";

    }

    @Override

    public String getDayOfWeek(String sqlTS) {

        return "EXTRACT(DOW from " + sqlTS + ") + 1";

    }

    @Override

    public String getMonth(String sqlTS) {

        return "EXTRACT(MONTH from " + sqlTS + ")";

    }

    @Override

    public String getFormatTimestamp(String sqlTS, boolean basic) {

        return this.getFormatTimestamp(sqlTS, SQLBase.quoteStringStd(basic ? "YYYYMMDD\"T\"HH24MISS.US" : "YYYY-MM-DD\"T\"HH24:MI:SS.US"));

    }

    @Override

    public String getFormatTimestamp(String sqlTS, String nativeFormat) {

        return "to_char(" + sqlTS + ", " + nativeFormat + ")";

    }

    @Override

    public String quoteForTimestampFormat(String text) {

        return StringUtils.doubleQuote(text, false);

    }

    @Override

    public final String getCreateSynonym(final SQLTable t, final SQLName newName) {

        String res = super.getCreateSynonym(t, newName);

        // in postgresql 8.3 views are not updatable, need to write rules

        final List<SQLField> fields = t.getOrderedFields();

        final List<String> setL = new ArrayList<String>(fields.size());

        final List<String> insFieldsL = new ArrayList<String>(fields.size());

        final List<String> insValuesL = new ArrayList<String>(fields.size());

        for (final SQLField f : fields) {

            final String name = t.getBase().quote("%n", f);

            final String newDotName = t.getBase().quote("NEW.%n", f, f);

            // don't add isAuto to ins

            if (!this.isAuto(f)) {

                insFieldsL.add(name);

                insValuesL.add(newDotName);

            }

            setL.add(name + " = " + newDotName);

        }

        final String set = "set " + CollectionUtils.join(setL, ", ");

        final String insFields = "(" + CollectionUtils.join(insFieldsL, ", ") + ") ";

        final String insValues = "VALUES(" + CollectionUtils.join(insValuesL, ", ") + ") ";

        // rule names are unique by table

        res += t.getBase().quote("CREATE or REPLACE RULE \"_updView_\" AS ON UPDATE TO %i\n" + "DO INSTEAD UPDATE %f \n" + set + "where %n=OLD.%n\n" + "RETURNING %f.*;", newName, t, t.getKey(),

                t.getKey(), t);

        res += t.getBase().quote("CREATE or REPLACE RULE \"_delView_\" AS ON DELETE TO %i\n" + "DO INSTEAD DELETE FROM %f \n where %n=OLD.%n\n" + "RETURNING %f.*;", newName, t, t.getKey(), t.getKey(),

                t);

        res += t.getBase().quote("CREATE or REPLACE RULE \"_insView_\" AS ON INSERT TO %i\n" + "DO INSTEAD INSERT INTO %f" + insFields + " " + insValues + "RETURNING %f.*;", newName, t, t);

        return res;

    }

    @Override

    public String getFunctionQuery(SQLBase b, Set<String> schemas) {

        return "SELECT ROUTINE_SCHEMA as \"schema\", ROUTINE_NAME as \"name\", ROUTINE_DEFINITION as \"src\" FROM \"information_schema\".ROUTINES where ROUTINE_CATALOG='" + b.getMDName()

                + "' and ROUTINE_SCHEMA in (" + quoteStrings(schemas) + ")";

    }

    @Override

    public String getTriggerQuery(SQLBase b, TablesMap tables) throws SQLException {

        return "SELECT tgname as \"TRIGGER_NAME\", n.nspname as \"TABLE_SCHEMA\", c.relname as \"TABLE_NAME\", tgfoid as \"ACTION\", pg_get_triggerdef(t.oid) as \"SQL\" \n" +

        // from

                "FROM pg_catalog.pg_trigger t\n" +

                // table

                "INNER JOIN pg_catalog.pg_class c on t.tgrelid = c.oid\n" +

                // schema

                "INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n" +

                // requested tables

                getTablesMapJoin(tables, "n.nspname", "c.relname") +

                // where

                "\nwhere not t." + (b.getVersion()[0] >= 9 ? "tgisinternal" : "tgisconstraint");

    }

    @Override

    public String getColumnsQuery(SQLBase b, TablesMap tables) {

        return "SELECT TABLE_SCHEMA as \"" + INFO_SCHEMA_NAMES_KEYS.get(0) + "\", TABLE_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(1) + "\", COLUMN_NAME as \"" + INFO_SCHEMA_NAMES_KEYS.get(2)

                + "\" , CHARACTER_SET_NAME as \"CHARACTER_SET_NAME\", COLLATION_NAME as \"COLLATION_NAME\" from INFORMATION_SCHEMA.COLUMNS\n" +

                // requested tables

                getTablesMapJoin(tables, "TABLE_SCHEMA", "TABLE_NAME");

    }

    @Override

    @SuppressWarnings("unchecked")

    public List<Map<String, Object>> getConstraints(SQLBase b, TablesMap tables) throws SQLException {

        final String sel = "select nsp.nspname as \"TABLE_SCHEMA\", rel.relname as \"TABLE_NAME\", c.conname as \"CONSTRAINT_NAME\", c.oid as cid, \n"

                + "case c.contype when 'u' then 'UNIQUE' when 'c' then 'CHECK' when 'f' then 'FOREIGN KEY' when 'p' then 'PRIMARY KEY' end as \"CONSTRAINT_TYPE\", att.attname as \"COLUMN_NAME\", pg_get_constraintdef(c.oid) as \"DEFINITION\","

                + "c.conkey as \"colsNum\", att.attnum as \"colNum\"\n"

                // from

                + "from pg_catalog.pg_constraint c\n" + "join pg_namespace nsp on nsp.oid = c.connamespace\n" + "left join pg_class rel on rel.oid = c.conrelid\n"

                + "left join pg_attribute att on  att.attrelid = c.conrelid and att.attnum = ANY(c.conkey)\n"

                // requested tables

                + getTablesMapJoin(tables, "nsp.nspname", "rel.relname")

                // order

                + "\norder by nsp.nspname, rel.relname, c.conname";

        // don't cache since we don't listen on system tables

        final List<Map<String, Object>> res = sort((List<Map<String, Object>>) b.getDBSystemRoot().getDataSource().execute(sel, new IResultSetHandler(SQLDataSource.MAP_LIST_HANDLER, false)));

        // ATTN c.conkey are not column indexes since dropped attribute are not deleted

        // so we must join pg_attribute to find out column names

        SQLSyntaxMySQL.mergeColumnNames(res);

        return res;

    }

    // pg has no ORDINAL_POSITION and no indexOf() function (except in contrib) so we can't ORDER

    // BY in SQL, we have to do it in java

    private List<Map<String, Object>> sort(final List<Map<String, Object>> sortedByConstraint) {

        final List<Map<String, Object>> res = new ArrayList<Map<String, Object>>(sortedByConstraint.size());

        final Comparator<Map<String, Object>> comp = new Comparator<Map<String, Object>>() {

            @Override

            public int compare(Map<String, Object> o1, Map<String, Object> o2) {

                return CompareUtils.compareInt(getIndex(o1), getIndex(o2));

            }

            // index of the passed column in the constraint

            private final int getIndex(Map<String, Object> o) {

                final int colNum = ((Number) o.get("colNum")).intValue();

                try {

                    // Integer for driver version 9, Short for version 42

                    final Number[] array = (Number[]) ((Array) o.get("colsNum")).getArray();

                    for (int i = 0; i < array.length; i++) {

                        if (array[i].intValue() == colNum)

                            return i;

                    }

                    throw new IllegalStateException(colNum + " was not found in " + Arrays.toString(array));

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        };

        // use the oid of pg to identify constraints (otherwise we'd have to compare the fully

        // qualified name of the constraint)

        int prevID = -1;

        final List<Map<String, Object>> currentConstr = new ArrayList<Map<String, Object>>();

        for (final Map<String, Object> m : sortedByConstraint) {

            final int currentID = ((Number) m.get("cid")).intValue();

            // at each change of constraint, sort its columns

            if (currentConstr.size() > 0 && currentID != prevID) {

                res.addAll(sort(currentConstr, comp));

                currentConstr.clear();

            }

            currentConstr.add(m);

            prevID = currentID;

        }

        res.addAll(sort(currentConstr, comp));

        return res;

    }

    private final List<Map<String, Object>> sort(List<Map<String, Object>> currentConstr, final Comparator<Map<String, Object>> comp) {

        Collections.sort(currentConstr, comp);

        for (int i = 0; i < currentConstr.size(); i++) {

            currentConstr.get(i).put("ORDINAL_POSITION", i + 1);

            // remove columns only needed to sort

            currentConstr.get(i).remove("cid");

            currentConstr.get(i).remove("colNum");

            currentConstr.get(i).remove("colsNum");

        }

        return currentConstr;

    }

    @Override

    public String getDropTrigger(Trigger t) {

        return "DROP TRIGGER " + SQLBase.quoteIdentifier(t.getName()) + " on " + t.getTable().getSQLName().quote();

    }

    @Override

    public String getSessionIDExpression() {

        return "pg_backend_pid()";

    }

    @Override

    public String getSessionsQuery(final DBSystemRoot sysRoot, final boolean includeSelf) {

        final String allRows = "SELECT pid as \"ID\", query as \"QUERY\", usename as \"USER_NAME\" FROM pg_stat_activity WHERE datname=" + quoteString(sysRoot.getName());

        if (includeSelf)

            return allRows;

        return allRows + " and pid != " + this.getSessionIDExpression();

    }

    @Override

    public String getVersionFunction() {

        // shorter than version() : "PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu

        // 9.6.9-2.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609,

        // 64-bit"

        return "current_setting('server_version')";

    }

    @Override

    public String getAllowConnectionsQuery(String sysRootName, boolean allow) {

        // REVOKE CONNECT ON DATABASE "ControleKD" TO public : superusers can still connect

        // to recover if all DB (even postgres and templates) run postgres --single

        // (single user / standalone mode ignores datallowconn)

        return "UPDATE pg_database SET datallowconn=" + SQLType.getBoolean(this).toString(allow) + " WHERE datname=" + quoteString(sysRootName);

        // "ALTER DATABASE WITH ALLOW_CONNECTIONS" isn't allowed on our own base

    }

    @Override

    public boolean isConnectionDisallowedException(SQLException exn) {

        return "55000".equals(exn.getSQLState());

    }

    @Override

    public String getSQLArray(final List<String> sqlExpressions, final String type) {

        if (type == null)

            return "ARRAY[" + CollectionUtils.join(sqlExpressions, ", ") + ']';

        else

            return this.quoteString('{' + CollectionUtils.join(sqlExpressions, ", ") + '}') + "::" + type + "[]";

    }

    @Override

    public String getSQLArrayContains(String arrayExpression, String itemExpression) {

        return itemExpression + " = ANY(" + arrayExpression + ")";

    }

    @Override

    public String getSQLArrayLength(final String arrayExpression) {

        return "ARRAY_LENGTH(" + arrayExpression + ", 1)";

    }

    @Override

    public String getSQLArrayConcat(final String arrayExpression, final String array2Expression) {

        return "array_cat(" + arrayExpression + ", " + array2Expression + ")";

    }

    @Override

    public String getSQLArrayAppend(final String arrayExpression, final String itemExpression) {

        // don't use || as it doesn't handle varchar literal

        return "array_append(" + arrayExpression + ", " + itemExpression + ")";

    }

    @Override

    public String getSQLArraySlice(final String arrayExpression, final String index1Expression, final String index2Expression) {

        return '(' + arrayExpression + ")[" + index1Expression + ":" + index2Expression + "]";

    }

}