Source code

001/*
002 * (C) Copyright 2006-2018 Nuxeo (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 *     Benoit Delbosc
019 */
020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
021
022import java.io.Serializable;
023import java.sql.Array;
024import java.sql.Connection;
025import java.sql.DatabaseMetaData;
026import java.sql.PreparedStatement;
027import java.sql.ResultSet;
028import java.sql.SQLException;
029import java.sql.Statement;
030import java.sql.Timestamp;
031import java.sql.Types;
032import java.util.ArrayList;
033import java.util.Arrays;
034import java.util.Collections;
035import java.util.HashMap;
036import java.util.HashSet;
037import java.util.LinkedList;
038import java.util.List;
039import java.util.Map;
040import java.util.Map.Entry;
041import java.util.Set;
042import java.util.UUID;
043import java.util.regex.Matcher;
044import java.util.regex.Pattern;
045import java.util.stream.Collectors;
046
047import org.apache.commons.lang3.StringUtils;
048import org.apache.commons.logging.Log;
049import org.apache.commons.logging.LogFactory;
050import org.nuxeo.ecm.core.NXCore;
051import org.nuxeo.ecm.core.api.NuxeoException;
052import org.nuxeo.ecm.core.api.security.SecurityConstants;
053import org.nuxeo.ecm.core.query.QueryParseException;
054import org.nuxeo.ecm.core.security.SecurityService;
055import org.nuxeo.ecm.core.storage.FulltextConfiguration;
056import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
057import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
058import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.Op;
059import org.nuxeo.ecm.core.storage.sql.ColumnType;
060import org.nuxeo.ecm.core.storage.sql.Model;
061import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
062import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
063import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
064import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
065import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
066import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
067import org.nuxeo.ecm.core.storage.sql.jdbc.db.TableAlias;
068
069/**
070 * PostgreSQL-specific dialect.
071 *
072 * @author Florent Guillaume
073 */
074public class DialectPostgreSQL extends Dialect {
075
076    private static final Log log = LogFactory.getLog(DialectPostgreSQL.class);
077
078    private static final String DEFAULT_FULLTEXT_ANALYZER = "english";
079
080    private static final String DEFAULT_USERS_SEPARATOR = ",";
081
082    private static final String PREFIX_SEARCH = ":*";
083
084    // prefix search syntax foo* or foo% or foo:*-> foo:*
085    private static final Pattern PREFIX_PATTERN = Pattern.compile("(\\*|%|:\\*)( |\"|$)");
086
087    private static final String PREFIX_REPL = PREFIX_SEARCH + "$2";
088
089    private static final String[] RESERVED_COLUMN_NAMES = { "xmin", "xmax", "cmin", "cmax", "ctid", "oid", "tableoid" };
090
091    private static final String UNLOGGED_KEYWORD = "UNLOGGED";
092
093    protected final String fulltextAnalyzer;
094
095    protected final boolean supportsWith;
096
097    protected boolean hierarchyCreated;
098
099    protected boolean pathOptimizationsEnabled;
100
101    protected final boolean arrayColumnsEnabled;
102
103    protected final boolean childNameUniqueConstraintEnabled;
104
105    protected final boolean collectionUniqueConstraintEnabled;
106
107    protected String usersSeparator;
108
109    protected final DialectIdType idType;
110
111    protected boolean compatibilityFulltextTable;
112
113    protected final String unloggedKeyword;
114
115    protected String idSequenceName;
116
117    public DialectPostgreSQL(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
118        super(metadata, repositoryDescriptor);
119        fulltextAnalyzer = repositoryDescriptor == null ? null
120                : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER
121                        : repositoryDescriptor.getFulltextAnalyzer();
122        pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled();
123        if (repositoryDescriptor != null) {
124            log.info("Path optimizations " + (pathOptimizationsEnabled ? "enabled" : "disabled"));
125        }
126        arrayColumnsEnabled = repositoryDescriptor != null && repositoryDescriptor.getArrayColumns();
127        childNameUniqueConstraintEnabled = repositoryDescriptor != null
128                && repositoryDescriptor.getChildNameUniqueConstraintEnabled();
129        collectionUniqueConstraintEnabled = repositoryDescriptor != null
130                && repositoryDescriptor.getCollectionUniqueConstraintEnabled();
131        int major, minor;
132        try {
133            major = metadata.getDatabaseMajorVersion();
134            minor = metadata.getDatabaseMinorVersion();
135        } catch (SQLException e) {
136            throw new NuxeoException(e);
137        }
138        supportsWith = major > 8 || (major == 8 && minor >= 4);
139        if ((major == 9 && minor >= 1) || (major > 9)) {
140            unloggedKeyword = UNLOGGED_KEYWORD;
141        } else {
142            unloggedKeyword = "";
143        }
144        usersSeparator = repositoryDescriptor == null ? null
145                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
146                        : repositoryDescriptor.usersSeparatorKey;
147        String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType;
148        if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) {
149            idType = DialectIdType.VARCHAR;
150        } else if ("uuid".equalsIgnoreCase(idt)) {
151            idType = DialectIdType.UUID;
152        } else if (idt.toLowerCase().startsWith("sequence")) {
153            idType = DialectIdType.SEQUENCE;
154            if (idt.toLowerCase().startsWith("sequence:")) {
155                String[] split = idt.split(":");
156                idSequenceName = split[1];
157            } else {
158                idSequenceName = "hierarchy_seq";
159            }
160        } else {
161            throw new NuxeoException("Unknown id type: '" + idt + "'");
162        }
163        try {
164            compatibilityFulltextTable = getCompatibilityFulltextTable(metadata);
165        } catch (SQLException e) {
166            throw new NuxeoException(e);
167        }
168    }
169
170    protected boolean getCompatibilityFulltextTable(DatabaseMetaData metadata) throws SQLException {
171        ResultSet rs = metadata.getColumns(null, null, Model.FULLTEXT_TABLE_NAME, "%");
172        while (rs.next()) {
173            // COLUMN_NAME=fulltext DATA_TYPE=1111 TYPE_NAME=tsvector
174            String columnName = rs.getString("COLUMN_NAME");
175            if (Model.FULLTEXT_FULLTEXT_KEY.equals(columnName)) {
176                String typeName = rs.getString("TYPE_NAME");
177                return "tsvector".equals(typeName);
178            }
179        }
180        return false;
181    }
182
183    @Override
184    public String toBooleanValueString(boolean bool) {
185        return bool ? "true" : "false";
186    }
187
188    @Override
189    public String getNoColumnsInsertString(Column idColumn) {
190        return "DEFAULT VALUES";
191    }
192
193    @Override
194    public String getCascadeDropConstraintsString() {
195        return "CASCADE";
196    }
197
198    @Override
199    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
200        switch (type.spec) {
201        case STRING:
202            if (type.isUnconstrained()) {
203                return jdbcInfo("varchar", Types.VARCHAR);
204            } else if (type.isClob()) {
205                return jdbcInfo("text", Types.CLOB);
206            } else {
207                return jdbcInfo("varchar(%d)", type.length, Types.VARCHAR);
208            }
209        case ARRAY_STRING:
210            if (type.isUnconstrained()) {
211                return jdbcInfo("varchar[]", Types.ARRAY, "varchar", Types.VARCHAR);
212            } else if (type.isClob()) {
213                return jdbcInfo("text[]", Types.ARRAY, "text", Types.CLOB);
214            } else {
215                return jdbcInfo("varchar(%d)[]", type.length, Types.ARRAY, "varchar", Types.VARCHAR);
216            }
217        case BOOLEAN:
218            return jdbcInfo("bool", Types.BIT);
219        case ARRAY_BOOLEAN:
220            return jdbcInfo("bool[]", Types.ARRAY, "bool", Types.BOOLEAN);
221        case LONG:
222            return jdbcInfo("int8", Types.BIGINT);
223        case ARRAY_LONG:
224            return jdbcInfo("int8[]", Types.ARRAY, "int8", Types.BIGINT);
225        case DOUBLE:
226            return jdbcInfo("float8", Types.DOUBLE);
227        case ARRAY_DOUBLE:
228            return jdbcInfo("float8[]", Types.ARRAY, "float8", Types.DOUBLE);
229        case TIMESTAMP:
230            return jdbcInfo("timestamp", Types.TIMESTAMP);
231        case ARRAY_TIMESTAMP:
232            return jdbcInfo("timestamp[]", Types.ARRAY, "timestamp", Types.TIMESTAMP);
233        case BLOBID:
234            return jdbcInfo("varchar(250)", Types.VARCHAR);
235        case ARRAY_BLOBID:
236            return jdbcInfo("varchar(250)[]", Types.ARRAY, "varchar", Types.VARCHAR);
237        // -----
238        case NODEID:
239        case NODEIDFK:
240        case NODEIDFKNP:
241        case NODEIDFKMUL:
242        case NODEIDFKNULL:
243        case NODEIDPK:
244        case NODEVAL:
245            switch (idType) {
246            case VARCHAR:
247                return jdbcInfo("varchar(36)", Types.VARCHAR);
248            case UUID:
249                return jdbcInfo("uuid", Types.OTHER);
250            case SEQUENCE:
251                return jdbcInfo("int8", Types.BIGINT);
252            }
253            throw new AssertionError("Unknown id type: " + idType);
254        case NODEARRAY:
255            switch (idType) {
256            case VARCHAR:
257                return jdbcInfo("varchar(36)[]", Types.ARRAY, "varchar", Types.VARCHAR);
258            case UUID:
259                return jdbcInfo("uuid[]", Types.ARRAY, "uuid", Types.OTHER);
260            case SEQUENCE:
261                return jdbcInfo("int8[]", Types.ARRAY, "int8", Types.BIGINT);
262            }
263            throw new AssertionError("Unknown id type: " + idType);
264        case SYSNAME:
265            return jdbcInfo("varchar(250)", Types.VARCHAR);
266        case SYSNAMEARRAY:
267            return jdbcInfo("varchar(250)[]", Types.ARRAY, "varchar", Types.VARCHAR);
268        case TINYINT:
269            return jdbcInfo("int2", Types.SMALLINT);
270        case INTEGER:
271            return jdbcInfo("int4", Types.INTEGER);
272        case ARRAY_INTEGER:
273            return jdbcInfo("int4[]", Types.ARRAY, "int4", Types.INTEGER);
274        case AUTOINC:
275            return jdbcInfo("serial", Types.INTEGER);
276        case FTINDEXED:
277            if (compatibilityFulltextTable) {
278                return jdbcInfo("tsvector", Types.OTHER);
279            } else {
280                return jdbcInfo("text", Types.CLOB);
281            }
282        case FTSTORED:
283            if (compatibilityFulltextTable) {
284                return jdbcInfo("tsvector", Types.OTHER);
285            } else {
286                return jdbcInfo("text", Types.CLOB);
287            }
288        case CLUSTERNODE:
289            return jdbcInfo("int4", Types.INTEGER);
290        case CLUSTERFRAGS:
291            return jdbcInfo("varchar[]", Types.ARRAY, "varchar", Types.VARCHAR);
292        }
293        throw new AssertionError(type);
294    }
295
296    @Override
297    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
298        // CLOB vs VARCHAR compatibility
299        if (expected == Types.VARCHAR && actual == Types.CLOB) {
300            return true;
301        }
302        if (expected == Types.CLOB && actual == Types.VARCHAR) {
303            return true;
304        }
305        // INTEGER vs BIGINT compatibility
306        if (expected == Types.BIGINT && actual == Types.INTEGER) {
307            return true;
308        }
309        if (expected == Types.INTEGER && actual == Types.BIGINT) {
310            return true;
311        }
312        // TSVECTOR vs CLOB compatibility during upgrade tests
313        // where column detection is done before upgrade test setup
314        if (expected == Types.CLOB && (actual == Types.OTHER && actualName.equals("tsvector"))) {
315            return true;
316        }
317        return false;
318    }
319
320    @Override
321    public Serializable getGeneratedId(Connection connection) throws SQLException {
322        if (idType != DialectIdType.SEQUENCE) {
323            return super.getGeneratedId(connection);
324        }
325        String sql = String.format("SELECT NEXTVAL('%s')", idSequenceName);
326        try (Statement s = connection.createStatement()) {
327            try (ResultSet rs = s.executeQuery(sql)) {
328                rs.next();
329                return Long.valueOf(rs.getLong(1));
330            }
331        }
332    }
333
334    @Override
335    public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException {
336        switch (idType) {
337        case VARCHAR:
338            ps.setObject(index, value);
339            break;
340        case UUID:
341            ps.setObject(index, value, Types.OTHER);
342            break;
343        case SEQUENCE:
344            setIdLong(ps, index, value);
345            break;
346        default:
347            throw new AssertionError();
348        }
349    }
350
351    @SuppressWarnings("boxing")
352    public Serializable getId(ResultSet rs, int index) throws SQLException {
353        switch (idType) {
354        case VARCHAR:
355        case UUID:
356            return rs.getString(index);
357        case SEQUENCE:
358            return rs.getLong(index);
359        default:
360            throw new AssertionError();
361        }
362    }
363
364    @Override
365    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
366            throws SQLException {
367        switch (column.getJdbcType()) {
368        case Types.VARCHAR:
369        case Types.CLOB:
370            setToPreparedStatementString(ps, index, value, column);
371            return;
372        case Types.BIT:
373            ps.setBoolean(index, ((Boolean) value).booleanValue());
374            return;
375        case Types.SMALLINT:
376            ps.setInt(index, ((Long) value).intValue());
377            return;
378        case Types.INTEGER:
379        case Types.BIGINT:
380            ps.setLong(index, ((Number) value).longValue());
381            return;
382        case Types.DOUBLE:
383            ps.setDouble(index, ((Double) value).doubleValue());
384            return;
385        case Types.TIMESTAMP:
386            setToPreparedStatementTimestamp(ps, index, value, column);
387            return;
388        case Types.ARRAY:
389            int jdbcBaseType = column.getJdbcBaseType();
390            String jdbcBaseTypeName = column.getSqlBaseTypeString();
391            if (jdbcBaseType == Types.TIMESTAMP) {
392                value = getTimestampFromCalendar((Serializable[]) value);
393            }
394            Array array = ps.getConnection().createArrayOf(jdbcBaseTypeName, (Object[]) value);
395            ps.setArray(index, array);
396            return;
397        case Types.OTHER:
398            ColumnType type = column.getType();
399            if (type.isId()) {
400                setId(ps, index, value);
401                return;
402            } else if (type == ColumnType.FTSTORED) {
403                ps.setString(index, (String) value);
404                return;
405            }
406            throw new SQLException("Unhandled type: " + column.getType());
407        default:
408            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
409        }
410    }
411
412    @Override
413    @SuppressWarnings("boxing")
414    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
415        int jdbcType = rs.getMetaData().getColumnType(index);
416        if (column.getJdbcType() == Types.ARRAY && jdbcType != Types.ARRAY) {
417            jdbcType = column.getJdbcBaseType();
418        } else {
419            jdbcType = column.getJdbcType();
420        }
421        switch (jdbcType) {
422        case Types.VARCHAR:
423        case Types.CLOB:
424            return getFromResultSetString(rs, index, column);
425        case Types.BIT:
426            return rs.getBoolean(index);
427        case Types.SMALLINT:
428        case Types.INTEGER:
429        case Types.BIGINT:
430            return rs.getLong(index);
431        case Types.DOUBLE:
432            return rs.getDouble(index);
433        case Types.TIMESTAMP:
434            return getFromResultSetTimestamp(rs, index, column);
435        case Types.ARRAY:
436            Array array = rs.getArray(index);
437            if (array == null) {
438                return null;
439            }
440            if (array.getBaseType() == Types.TIMESTAMP) {
441                return getCalendarFromTimestamp((Timestamp[]) array.getArray());
442            } else {
443                return (Serializable) array.getArray();
444            }
445        case Types.OTHER:
446            ColumnType type = column.getType();
447            if (type.isId()) {
448                return getId(rs, index);
449            }
450            throw new SQLException("Unhandled type: " + column.getType());
451        }
452        throw new SQLException(
453                "Unhandled JDBC type: " + column.getJdbcType() + " for type " + column.getType().toString());
454    }
455
456    @Override
457    protected int getMaxNameSize() {
458        return 63;
459    }
460
461    @Override
462    public String getColumnName(String name) {
463        // ignore suffixed "_" when checking for reservedness
464        String n = name.replaceAll("_+$", "");
465        for (String reserved : RESERVED_COLUMN_NAMES) {
466            if (n.equals(reserved)) {
467                // reserved, add one more suffix "_"
468                name += "_";
469                break;
470            }
471        }
472        return super.getColumnName(name);
473    }
474
475    @Override
476    public List<String> getCustomPostCreateSqls(Table table, Model model) {
477        List<String> sqls = new ArrayList<>();
478        String key = table.getKey();
479        if (childNameUniqueConstraintEnabled && key.equals(Model.HIER_TABLE_NAME)) {
480            // CREATE UNIQUE INDEX hierarchy_unique_child ON hierarchy (parentid, name)
481            // WHERE isproperty = false
482            sqls.add(String.format(
483                    "CREATE UNIQUE INDEX \"hierarchy_unique_child\" ON \"%s\" (\"%s\", \"%s\") WHERE \"%s\" = false",
484                    Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY,
485                    Model.HIER_CHILD_ISPROPERTY_KEY));
486            // CREATE UNIQUE INDEX hierarchy_unique_child_complex ON hierarchy (parentid, name)
487            // WHERE isproperty = true AND pos IS NULL
488            sqls.add(String.format(
489                    "CREATE UNIQUE INDEX \"hierarchy_unique_child_complex\" ON \"%s\" (\"%s\", \"%s\") WHERE \"%s\" = true AND \"%s\" IS NULL",
490                    Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY,
491                    Model.HIER_CHILD_ISPROPERTY_KEY, Model.HIER_CHILD_POS_KEY));
492            // CREATE UNIQUE INDEX hierarchy_unique_child_complex_list ON hierarchy (parentid, name, pos)
493            // WHERE isproperty = true AND pos IS NOT NULL
494            sqls.add(String.format(
495                    "CREATE UNIQUE INDEX \"hierarchy_unique_child_complex_list\" ON \"%s\" (\"%s\", \"%s\", \"%s\") WHERE \"%s\" = true AND \"%s\" IS NOT NULL",
496                    Model.HIER_TABLE_NAME, Model.HIER_PARENT_KEY, Model.HIER_CHILD_NAME_KEY, Model.HIER_CHILD_POS_KEY,
497                    Model.HIER_CHILD_ISPROPERTY_KEY, Model.HIER_CHILD_POS_KEY));
498        } else if (collectionUniqueConstraintEnabled && model.isCollectionFragment(key)) {
499            // CREATE UNIQUE INDEX dc_contributors_unique_pos ON dc_contributors (id, pos)
500            String name = table.getPhysicalName();
501            sqls.add(String.format("CREATE UNIQUE INDEX \"%s_unique_pos\" ON \"%s\" (\"%s\", \"%s\")", name, name,
502                    Model.MAIN_KEY, Model.COLL_TABLE_POS_KEY));
503        }
504        return sqls;
505    }
506
507    @Override
508    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
509            Model model) {
510        String sql;
511        if (compatibilityFulltextTable) {
512            sql = "CREATE INDEX %s ON %s USING GIN(%s)";
513        } else {
514            sql = "CREATE INDEX %s ON %s USING GIN(NX_TO_TSVECTOR(%s))";
515        }
516        return String.format(sql, quotedIndexName.toLowerCase(), table.getQuotedName(), columns.get(0).getQuotedName());
517    }
518
519    // must not be interpreted as a regexp, we split on it
520    protected static final String FT_LIKE_SEP = " @#AND#@ ";
521
522    protected static final String FT_LIKE_COL = "??";
523
524    /**
525     * {@inheritDoc}
526     * <p>
527     * The result of this is passed to {@link #getFulltextScoredMatchInfo}.
528     */
529    @Override
530    public String getDialectFulltextQuery(String query) {
531        query = query.replace(" & ", " "); // PostgreSQL compatibility BBB
532        query = PREFIX_PATTERN.matcher(query).replaceAll(PREFIX_REPL);
533        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
534        if (ft == null) {
535            return ""; // won't match anything
536        }
537        if (!FulltextQueryAnalyzer.hasPhrase(ft)) {
538            return FulltextQueryAnalyzer.translateFulltext(ft, "|", "&", "& !", "");
539        }
540        if (compatibilityFulltextTable) {
541            throw new QueryParseException("Cannot use phrase search in fulltext compatibilty mode. "
542                    + "Please upgrade the fulltext table: " + query);
543        }
544        /*
545         * Phrase search. We have to do the phrase query using a LIKE, but for performance we pre-filter using as many
546         * fulltext matches as possible by breaking some of the phrases into words. We do an AND of the two. 1.
547         * pre-filter using fulltext on a query that is a superset of the original query,
548         */
549        FulltextQuery broken = breakPhrases(ft);
550        String ftsql = FulltextQueryAnalyzer.translateFulltext(broken, "|", "&", "& !", "");
551        /*
552         * 2. AND with a LIKE-based search for all terms, except those that are already exactly matched by the first
553         * part, i.e., toplevel ANDed non-phrases.
554         */
555        FulltextQuery noand = removeToplevelAndedWords(ft);
556        if (noand != null) {
557            StringBuilder buf = new StringBuilder();
558            generateLikeSql(noand, buf);
559            ftsql += FT_LIKE_SEP + buf.toString();
560
561        }
562        return ftsql;
563    }
564
565    /**
566     * Returns a fulltext query that is a superset of the original one and does not have phrase searches.
567     * <p>
568     * Negative phrases (which are at AND level) are removed, positive phrases are split into ANDed words.
569     */
570    protected static FulltextQuery breakPhrases(FulltextQuery ft) {
571        FulltextQuery newFt = new FulltextQuery();
572        if (ft.op == Op.AND || ft.op == Op.OR) {
573            List<FulltextQuery> newTerms = new LinkedList<>();
574            for (FulltextQuery term : ft.terms) {
575                FulltextQuery broken = breakPhrases(term);
576                if (broken == null) {
577                    // remove negative phrase
578                } else if (ft.op == Op.AND && broken.op == Op.AND) {
579                    // associativity (sub-AND hoisting)
580                    newTerms.addAll(broken.terms);
581                } else {
582                    newTerms.add(broken);
583                }
584            }
585            if (newTerms.size() == 1) {
586                // single-term parenthesis elimination
587                newFt = newTerms.get(0);
588            } else {
589                newFt.op = ft.op;
590                newFt.terms = newTerms;
591            }
592        } else {
593            boolean isPhrase = ft.isPhrase();
594            if (!isPhrase) {
595                newFt = ft;
596            } else if (ft.op == Op.WORD) {
597                // positive phrase
598                // split it
599                List<FulltextQuery> newTerms = new LinkedList<>();
600                for (String subword : ft.word.split(" ")) {
601                    FulltextQuery sft = new FulltextQuery();
602                    sft.op = Op.WORD;
603                    sft.word = subword;
604                    newTerms.add(sft);
605                }
606                newFt.op = Op.AND;
607                newFt.terms = newTerms;
608            } else {
609                // negative phrase
610                // removed
611                newFt = null;
612            }
613        }
614        return newFt;
615    }
616
617    /**
618     * Removes toplevel ANDed simple words from the query.
619     */
620    protected static FulltextQuery removeToplevelAndedWords(FulltextQuery ft) {
621        if (ft.op == Op.OR || ft.op == Op.NOTWORD) {
622            return ft;
623        }
624        if (ft.op == Op.WORD) {
625            if (ft.isPhrase()) {
626                return ft;
627            }
628            return null;
629        }
630        List<FulltextQuery> newTerms = new LinkedList<>();
631        for (FulltextQuery term : ft.terms) {
632            if (term.op == Op.NOTWORD) {
633                newTerms.add(term);
634            } else { // Op.WORD
635                if (term.isPhrase()) {
636                    newTerms.add(term);
637                }
638            }
639        }
640        if (newTerms.isEmpty()) {
641            return null;
642        } else if (newTerms.size() == 1) {
643            // single-term parenthesis elimination
644            return newTerms.get(0);
645        } else {
646            FulltextQuery newFt = new FulltextQuery();
647            newFt.op = Op.AND;
648            newFt.terms = newTerms;
649            return newFt;
650        }
651    }
652
653    // turn non-toplevel ANDed single words into SQL
654    // abc "foo bar" -"gee man"
655    // -> ?? LIKE '% foo bar %' AND ?? NOT LIKE '% gee man %'
656    // ?? is a pseudo-parameter for the col
657    protected static void generateLikeSql(FulltextQuery ft, StringBuilder buf) {
658        if (ft.op == Op.AND || ft.op == Op.OR) {
659            buf.append('(');
660            boolean first = true;
661            for (FulltextQuery term : ft.terms) {
662                if (!first) {
663                    if (ft.op == Op.AND) {
664                        buf.append(" AND ");
665                    } else { // Op.OR
666                        buf.append(" OR ");
667                    }
668                }
669                first = false;
670                generateLikeSql(term, buf);
671            }
672            buf.append(')');
673        } else {
674            buf.append(FT_LIKE_COL);
675            if (ft.op == Op.NOTWORD) {
676                buf.append(" NOT");
677            }
678            buf.append(" LIKE '% ");
679            String word = ft.word.toLowerCase();
680            // SQL escaping
681            word = word.replace("'", "''");
682            word = word.replace("\\", ""); // don't take chances
683            word = word.replace(PREFIX_SEARCH, "%");
684            buf.append(word);
685            if (!word.endsWith("%")) {
686                buf.append(" %");
687            }
688            buf.append("'");
689        }
690    }
691
692    // OLD having problems in pre-9.2 (NXP-9228)
693    // SELECT ...,
694    // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), nxquery, 32) as nxscore
695    // FROM ...
696    // LEFT JOIN fulltext ON fulltext.id = hierarchy.id,
697    // TO_TSQUERY('french', ?) nxquery
698    // WHERE ...
699    // AND nxquery @@ NX_TO_TSVECTOR(fulltext)
700    // AND fulltext LIKE '% foo bar %' -- when phrase search
701    // ORDER BY nxscore DESC
702
703    // NEW
704    // SELECT ...,
705    // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), TO_TSQUERY('french', ?), 32) as
706    // nxscore
707    // FROM ...
708    // LEFT JOIN fulltext ON fulltext.id = hierarchy.id
709    // WHERE ...
710    // AND TO_TSQUERY('french', ?) @@ NX_TO_TSVECTOR(fulltext)
711    // AND fulltext LIKE '% foo bar %' -- when phrase search
712    // ORDER BY nxscore DESC
713    @Override
714    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
715            Column mainColumn, Model model, Database database) {
716        String indexSuffix = model.getFulltextIndexSuffix(indexName);
717        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
718        Column ftMain = ft.getColumn(Model.MAIN_KEY);
719        Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix);
720        String ftColumnName = ftColumn.getFullQuotedName();
721        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
722        FulltextMatchInfo info = new FulltextMatchInfo();
723        info.joins = new ArrayList<>();
724        if (nthMatch == 1) {
725            // Need only one JOIN involving the fulltext table
726            info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(),
727                    mainColumn.getFullQuotedName()));
728        }
729        /*
730         * for phrase search, fulltextQuery may contain a LIKE part
731         */
732        String like;
733        if (fulltextQuery.contains(FT_LIKE_SEP)) {
734            String[] tmp = fulltextQuery.split(FT_LIKE_SEP, 2);
735            fulltextQuery = tmp[0];
736            like = tmp[1].replace(FT_LIKE_COL, ftColumnName);
737        } else {
738            like = null;
739        }
740        String tsquery = String.format("TO_TSQUERY('%s', ?)", fulltextAnalyzer);
741        String tsvector;
742        if (compatibilityFulltextTable) {
743            tsvector = ftColumnName;
744        } else {
745            tsvector = String.format("NX_TO_TSVECTOR(%s)", ftColumnName);
746        }
747        String where = String.format("(%s @@ %s)", tsquery, tsvector);
748        if (like != null) {
749            where += " AND (" + like + ")";
750        }
751        info.whereExpr = where;
752        info.whereExprParam = fulltextQuery;
753        info.scoreExpr = String.format("TS_RANK_CD(%s, %s, 32)", tsvector, tsquery);
754        info.scoreExprParam = fulltextQuery;
755        info.scoreAlias = "_nxscore" + nthSuffix;
756        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
757        return info;
758    }
759
760    @Override
761    public boolean getMaterializeFulltextSyntheticColumn() {
762        return true;
763    }
764
765    @Override
766    public int getFulltextIndexedColumns() {
767        return 1;
768    }
769
770    @Override
771    public String getFreeVariableSetterForType(ColumnType type) {
772        if (type == ColumnType.FTSTORED && compatibilityFulltextTable) {
773            return "NX_TO_TSVECTOR(?)";
774        }
775        return "?";
776    }
777
778    @Override
779    public boolean supportsUpdateFrom() {
780        return true;
781    }
782
783    @Override
784    public boolean doesUpdateFromRepeatSelf() {
785        return false;
786    }
787
788    @Override
789    public boolean needsAliasForDerivedTable() {
790        return true;
791    }
792
793    @Override
794    public boolean supportsIlike() {
795        return true;
796    }
797
798    @Override
799    public boolean supportsReadAcl() {
800        return aclOptimizationsEnabled;
801    }
802
803    @Override
804    public String getPrepareUserReadAclsSql() {
805        return "SELECT nx_prepare_user_read_acls(?)";
806    }
807
808    @Override
809    public String getReadAclsCheckSql(String userIdCol) {
810        return String.format("%s = md5(array_to_string(?, '%s'))", userIdCol, getUsersSeparator());
811    }
812
813    @Override
814    public String getUpdateReadAclsSql() {
815        return "SELECT nx_update_read_acls();";
816    }
817
818    @Override
819    public String getRebuildReadAclsSql() {
820        return "SELECT nx_rebuild_read_acls();";
821    }
822
823    @Override
824    public String getSecurityCheckSql(String idColumnName) {
825        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
826    }
827
828    @Override
829    public boolean supportsAncestorsTable() {
830        return true;
831    }
832
833    @Override
834    public boolean supportsFastDescendants() {
835        return pathOptimizationsEnabled;
836    }
837
838    @Override
839    public String getInTreeSql(String idColumnName, String id) {
840        String cast;
841        try {
842            cast = getCastForId(id);
843        } catch (IllegalArgumentException e) {
844            // discard query with invalid id
845            return null;
846        }
847        if (pathOptimizationsEnabled) {
848            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE id = %s AND ARRAY[?]%s <@ ancestors)",
849                    idColumnName, getCastForArray(cast));
850        }
851        return String.format("%s IN (SELECT * FROM nx_children(?%s))", idColumnName, cast);
852    }
853
854    protected String getCastForArray(String cast) {
855        if (cast.isEmpty()) {
856            return cast;
857        }
858        return cast + "[]";
859    }
860
861    protected String getCastForId(String id) {
862        String ret;
863        switch (idType) {
864        case VARCHAR:
865            return "";
866        case UUID:
867            // check that it's really a uuid
868            if (id != null) {
869                UUID.fromString(id);
870            }
871            ret = "::uuid";
872            break;
873        case SEQUENCE:
874            // check that it's really an integer
875            if (id != null && !StringUtils.isNumeric(id)) {
876                throw new IllegalArgumentException("Invalid sequence id: " + id);
877            }
878            ret = "::bigint";
879            break;
880        default:
881            throw new AssertionError("Unknown id type: " + idType);
882        }
883        return ret;
884    }
885
886    @Override
887    public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) {
888        returnParam[0] = mixin;
889        String sql = "ARRAY[?]::varchar[] <@ " + mixinsColumn.getFullQuotedName();
890        return positive ? sql : "NOT(" + sql + ")";
891    }
892
893    @Override
894    public boolean supportsSysNameArray() {
895        return true;
896    }
897
898    @Override
899    public boolean supportsArrays() {
900        return true;
901    }
902
903    @Override
904    public boolean supportsArrayColumns() {
905        return true;
906    }
907
908    public static class ArraySubQueryPostgreSQL extends ArraySubQuery {
909
910        protected Dialect dialect = null;
911
912        protected Table fakeSubqueryTableAlias = null;
913
914        public ArraySubQueryPostgreSQL(Column arrayColumn, String alias) {
915            super(arrayColumn, alias);
916            dialect = arrayColumn.getTable().getDialect();
917            fakeSubqueryTableAlias = new TableAlias(arrayColumn.getTable(), alias);
918        }
919
920        @Override
921        public Column getSubQueryIdColumn() {
922            Column column = fakeSubqueryTableAlias.getColumn(Model.MAIN_KEY);
923            return new ArraySubQueryPostgreSQLColumn(column.getPhysicalName(), column.getType());
924        }
925
926        @Override
927        public Column getSubQueryValueColumn() {
928            return new ArraySubQueryPostgreSQLColumn(Model.COLL_TABLE_VALUE_KEY, arrayColumn.getBaseType());
929        }
930
931        public class ArraySubQueryPostgreSQLColumn extends Column {
932            private static final long serialVersionUID = 1L;
933
934            ArraySubQueryPostgreSQLColumn(String columnName, ColumnType columnType) {
935                super(fakeSubqueryTableAlias, columnName, columnType, columnName);
936            }
937
938            @Override
939            public String getFullQuotedName() {
940                return dialect.openQuote() + subQueryAlias + dialect.closeQuote() + '.' + getQuotedName();
941            }
942        }
943
944        @Override
945        public String toSql() {
946            Table table = arrayColumn.getTable();
947            return String.format("(SELECT %s, UNNEST(%s) AS %s, generate_subscripts(%s, 1) AS %s FROM %s) ",
948                    table.getColumn(Model.MAIN_KEY).getQuotedName(), arrayColumn.getQuotedName(),
949                    Model.COLL_TABLE_VALUE_KEY, arrayColumn.getQuotedName(), Model.COLL_TABLE_POS_KEY,
950                    table.getRealTable().getQuotedName());
951        }
952    }
953
954    @Override
955    public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) {
956        return new ArraySubQueryPostgreSQL(arrayColumn, subQueryAlias);
957    }
958
959    @Override
960    public String getArrayElementString(String arrayColumnName, int arrayElementIndex) {
961        // PostgreSQL arrays index start at 1
962        return arrayColumnName + "[" + (arrayElementIndex + 1) + "]";
963    }
964
965    @Override
966    public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) {
967        StringBuilder sql = new StringBuilder();
968        if (!positive) {
969            sql.append("(NOT(");
970        }
971        if (params.size() == 1) {
972            // ? = ANY(arrayColumn)
973            sql.append("? = ANY(");
974            sql.append(arrayColumn.getFullQuotedName());
975            if (cast != null) {
976                // DATE cast
977                sql.append("::");
978                sql.append(cast);
979                sql.append("[]");
980            }
981            sql.append(")");
982        } else {
983            // arrayColumn && ARRAY[?, ?, ?]
984            sql.append(arrayColumn.getFullQuotedName());
985            sql.append(" && ");
986            sql.append("ARRAY[");
987            for (int i = 0; i < params.size(); i++) {
988                if (i != 0) {
989                    sql.append(", ");
990                }
991                sql.append('?');
992            }
993            sql.append("]::");
994            sql.append(arrayColumn.getSqlTypeString());
995        }
996        if (!positive) {
997            sql.append(") OR ");
998            sql.append(arrayColumn.getFullQuotedName());
999            sql.append(" IS NULL)");
1000        }
1001        return sql.toString();
1002    }
1003
1004    @Override
1005    public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
1006        return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "LIKE");
1007    }
1008
1009    @Override
1010    public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
1011        return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "ILIKE");
1012    }
1013
1014    protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable,
1015            String op) {
1016        Table table = arrayColumn.getTable();
1017        String tableAliasName = openQuote() + getTableName(refName) + closeQuote();
1018        String sql = String.format("EXISTS (SELECT 1 FROM %s AS %s WHERE %s = %s AND %s %s ?)",
1019                getArraySubQuery(arrayColumn, tableAliasName).toSql(), tableAliasName,
1020                dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(),
1021                tableAliasName + '.' + table.getColumn(Model.MAIN_KEY).getQuotedName(),
1022                tableAliasName + '.' + Model.COLL_TABLE_VALUE_KEY, op);
1023        if (!positive) {
1024            sql = "NOT(" + sql + ")";
1025        }
1026        return sql;
1027    }
1028
1029    @Override
1030    public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
1031        if (elements == null || elements.length == 0) {
1032            return null;
1033        }
1034        String typeName;
1035        switch (type) {
1036        case Types.VARCHAR:
1037            typeName = "varchar";
1038            break;
1039        case Types.CLOB:
1040            typeName = "text";
1041            break;
1042        case Types.BIT:
1043            typeName = "bool";
1044            break;
1045        case Types.BIGINT:
1046            typeName = "int8";
1047            break;
1048        case Types.DOUBLE:
1049            typeName = "float8";
1050            break;
1051        case Types.TIMESTAMP:
1052            typeName = "timestamp";
1053            break;
1054        case Types.SMALLINT:
1055            typeName = "int2";
1056            break;
1057        case Types.INTEGER:
1058            typeName = "int4";
1059            break;
1060        case Types.OTHER: // id
1061            switch (idType) {
1062            case VARCHAR:
1063                typeName = "varchar";
1064                break;
1065            case UUID:
1066                typeName = "uuid";
1067                break;
1068            case SEQUENCE:
1069                typeName = "int8";
1070                break;
1071            default:
1072                throw new AssertionError("Unknown id type: " + idType);
1073            }
1074            break;
1075        default:
1076            throw new AssertionError("Unknown type: " + type);
1077        }
1078        return connection.createArrayOf(typeName, elements);
1079    }
1080
1081    @Override
1082    public String getSQLStatementsFilename() {
1083        return "nuxeovcs/postgresql.sql.txt";
1084    }
1085
1086    @Override
1087    public String getTestSQLStatementsFilename() {
1088        return "nuxeovcs/postgresql.test.sql.txt";
1089    }
1090
1091    @Override
1092    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
1093        Map<String, Serializable> properties = new HashMap<>();
1094        switch (idType) {
1095        case VARCHAR:
1096            properties.put("idType", "varchar(36)");
1097            properties.put("idTypeParam", "varchar");
1098            properties.put("idNotPresent", "'-'");
1099            properties.put("sequenceEnabled", Boolean.FALSE);
1100            break;
1101        case UUID:
1102            properties.put("idType", "uuid");
1103            properties.put("idTypeParam", "uuid");
1104            properties.put("idNotPresent", "'00000000-FFFF-FFFF-FFFF-FFFF00000000'");
1105            properties.put("sequenceEnabled", Boolean.FALSE);
1106            break;
1107        case SEQUENCE:
1108            properties.put("idType", "int8");
1109            properties.put("idTypeParam", "int8");
1110            properties.put("idNotPresent", "-1");
1111            properties.put("sequenceEnabled", Boolean.TRUE);
1112            properties.put("idSequenceName", idSequenceName);
1113        }
1114        properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
1115        properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
1116        properties.put("fulltextAnalyzer", fulltextAnalyzer);
1117        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
1118        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
1119        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
1120        properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled));
1121        properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled));
1122        properties.put("arrayColumnsEnabled", Boolean.valueOf(arrayColumnsEnabled));
1123        if (!fulltextSearchDisabled) {
1124            Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
1125            FulltextConfiguration fti = model.getFulltextConfiguration();
1126            List<String> lines = new ArrayList<>(fti.indexNames.size());
1127            for (String indexName : fti.indexNames) {
1128                String suffix = model.getFulltextIndexSuffix(indexName);
1129                Column ftft = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + suffix);
1130                Column ftst = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + suffix);
1131                Column ftbt = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + suffix);
1132                String concat;
1133                if (compatibilityFulltextTable) {
1134                    // tsvector
1135                    concat = "  NEW.%s := COALESCE(NEW.%s, ''::TSVECTOR) || COALESCE(NEW.%s, ''::TSVECTOR);";
1136                } else {
1137                    // text with space at beginning and end
1138                    concat = "  NEW.%s := ' ' || COALESCE(NEW.%s, '') || ' ' || COALESCE(NEW.%s, '') || ' ';";
1139                }
1140                String line = String.format(concat, ftft.getQuotedName(), ftst.getQuotedName(), ftbt.getQuotedName());
1141                lines.add(line);
1142            }
1143            properties.put("fulltextTriggerStatements", String.join("\n", lines));
1144        }
1145        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
1146        List<String> permsList = new LinkedList<>();
1147        for (String perm : permissions) {
1148            permsList.add("('" + perm + "')");
1149        }
1150        properties.put("readPermissions", String.join(", ", permsList));
1151        properties.put("usersSeparator", getUsersSeparator());
1152        properties.put("everyone", SecurityConstants.EVERYONE);
1153        properties.put("readAclMaxSize", Integer.toString(readAclMaxSize));
1154        properties.put("unlogged", unloggedKeyword);
1155        return properties;
1156    }
1157
1158    @Override
1159    public List<String> getStartupSqls(Model model, Database database) {
1160        if (aclOptimizationsEnabled) {
1161            log.info("Vacuuming tables used by optimized acls");
1162            return Collections.singletonList("SELECT nx_vacuum_read_acls()");
1163        }
1164        return Collections.emptyList();
1165    }
1166
1167    @Override
1168    public boolean isClusteringSupported() {
1169        return true;
1170    }
1171
1172    @Override
1173    public String getClusterInsertInvalidations() {
1174        return "SELECT NX_CLUSTER_INVAL(?, ?, ?, ?)";
1175    }
1176
1177    @Override
1178    public String getClusterGetInvalidations() {
1179        return "DELETE FROM cluster_invals WHERE nodeid = ? RETURNING id, fragments, kind";
1180    }
1181
1182    @Override
1183    public boolean isConcurrentUpdateException(Throwable t) {
1184        while (t.getCause() != null) {
1185            t = t.getCause();
1186        }
1187        if (t instanceof SQLException) {
1188            String sqlState = ((SQLException) t).getSQLState();
1189            if ("23503".equals(sqlState)) {
1190                // insert or update on table ... violates foreign key constraint
1191                return true;
1192            }
1193            if ("23505".equals(sqlState)) {
1194                // duplicate key value violates unique constraint
1195                return true;
1196            }
1197            if ("40P01".equals(sqlState)) {
1198                // deadlock detected
1199                return true;
1200            }
1201        }
1202        return false;
1203    }
1204
1205    @Override
1206    public boolean supportsPaging() {
1207        return true;
1208    }
1209
1210    @Override
1211    public String addPagingClause(String sql, long limit, long offset) {
1212        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
1213    }
1214
1215    @Override
1216    public boolean supportsWith() {
1217        return false; // don't activate until proven useful
1218        // return supportsWith;
1219    }
1220
1221    @Override
1222    public void performAdditionalStatements(Connection connection) throws SQLException {
1223        // Check if there is a pre-existing aclr_permission table
1224        boolean createAclrPermission;
1225        try (Statement s = connection.createStatement()) {
1226            String sql = "SELECT 1 FROM pg_tables WHERE tablename = 'aclr_permission'";
1227            try (ResultSet rs = s.executeQuery(sql)) {
1228                createAclrPermission = !rs.next();
1229            }
1230        }
1231        // If no table, it will be created and filled at DDL execution time
1232        if (createAclrPermission) {
1233            return;
1234        }
1235        // Warn user if BROWSE permissions has changed
1236        Set<String> dbPermissions = new HashSet<>();
1237        try (Statement s = connection.createStatement()) {
1238            String sql = "SELECT * FROM aclr_permission";
1239            try (ResultSet rs = s.executeQuery(sql)) {
1240                while (rs.next()) {
1241                    dbPermissions.add(rs.getString(1));
1242                }
1243            }
1244        }
1245        SecurityService securityService = NXCore.getSecurityService();
1246        Set<String> confPermissions = new HashSet<>(
1247                Arrays.asList(securityService.getPermissionsToCheck(SecurityConstants.BROWSE)));
1248        if (!dbPermissions.equals(confPermissions)) {
1249            log.error("Security permission for BROWSE has changed, you need to rebuild the optimized read acls:"
1250                    + "DROP TABLE aclr_permission; DROP TABLE aclr; then restart.");
1251        }
1252    }
1253
1254    public String getUsersSeparator() {
1255        if (usersSeparator == null) {
1256            return DEFAULT_USERS_SEPARATOR;
1257        }
1258        return usersSeparator;
1259    }
1260
1261    @Override
1262    public String getValidationQuery() {
1263        return "";
1264    }
1265
1266    @Override
1267    public String getAncestorsIdsSql() {
1268        return "SELECT NX_ANCESTORS(?)";
1269    }
1270
1271    @Override
1272    public boolean needsNullsLastOnDescSort() {
1273        return true;
1274    }
1275
1276    @Override
1277    public String getDateCast() {
1278        // this is more amenable to being indexed than a CAST
1279        return "DATE(%s)";
1280    }
1281
1282    @Override
1283    public String castIdToVarchar(String expr) {
1284        switch (idType) {
1285        case VARCHAR:
1286            return expr;
1287        case UUID:
1288            return expr + "::varchar";
1289        case SEQUENCE:
1290            return expr + "::varchar";
1291        default:
1292            throw new AssertionError("Unknown id type: " + idType);
1293        }
1294    }
1295
1296    @Override
1297    public DialectIdType getIdType() {
1298        return idType;
1299    }
1300
1301    @Override
1302    public String getSoftDeleteSql() {
1303        return "SELECT NX_DELETE(?, ?)";
1304    }
1305
1306    @Override
1307    public String getSoftDeleteCleanupSql() {
1308        return "SELECT NX_DELETE_PURGE(?, ?)";
1309    }
1310
1311    @Override
1312    public String getBinaryFulltextSql(List<String> columns) {
1313        if (compatibilityFulltextTable) {
1314            // extract tokens from tsvector
1315            String columnsAs = columns.stream()
1316                                      .map(col -> "regexp_replace(" + col + "::text, $$'|'\\:[^']*'?$$, ' ', 'g')")
1317                                      .collect(Collectors.joining(", "));
1318            return "SELECT " + columnsAs + " FROM fulltext WHERE id=?";
1319        }
1320        return super.getBinaryFulltextSql(columns);
1321    }
1322
1323    // parenthesizes parameter part, with optional nested parentheses
1324    private static final Pattern SIG_MATCH = Pattern.compile("[^(]*\\((([^()]*|\\([^()]*\\))*)\\).*", Pattern.DOTALL);
1325
1326    @Override
1327    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
1328            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
1329        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
1330        if (compatCheck) {
1331            procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
1332            return Collections.singletonList(procCreate);
1333        }
1334        // extract signature from create statement
1335        Matcher m = SIG_MATCH.matcher(procCreate);
1336        if (!m.matches()) {
1337            throw new NuxeoException("Cannot parse arguments: " + procCreate);
1338        }
1339        String procArgs = normalizeArgs(m.group(1));
1340        try (Statement st = connection.createStatement()) {
1341            // check if the stored procedure exists and its content
1342            String getBody = "SELECT prosrc, pg_get_function_identity_arguments(oid) FROM pg_proc WHERE proname = '"
1343                    + procName + "'";
1344            logger.log(getBody);
1345            try (ResultSet rs = st.executeQuery(getBody)) {
1346                while (rs.next()) {
1347                    String body = rs.getString(1);
1348                    String args = rs.getString(2);
1349                    if (!args.equals(procArgs)) {
1350                        // different signature
1351                        continue;
1352                    }
1353                    // stored proc already exists
1354                    if (normalizeString(procCreate).contains(normalizeString(body))) {
1355                        logger.log("  -> exists, unchanged");
1356                        return Collections.emptyList();
1357                    } else {
1358                        logger.log("  -> exists, old");
1359                        // we can't drop then recreate as for instance a function used by a trigger
1360                        // would say "cannot drop function ... because other objects depend on it"
1361                        // so we hack and do an do a replace
1362                        if (!procCreate.toLowerCase().startsWith("create ")) {
1363                            throw new NuxeoException("Should start with CREATE: " + procCreate);
1364                        }
1365                        procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
1366                        return Collections.singletonList(procCreate);
1367                    }
1368                }
1369            }
1370            logger.log("  -> missing");
1371            return Collections.singletonList(procCreate);
1372        }
1373    }
1374
1375    protected static String normalizeString(String string) {
1376        return string.replaceAll("[ \n\r\t]+", " ").trim();
1377    }
1378
1379    /** The type aliases that we use for our stored procedure argument definitions. */
1380    private static final Map<String, String> TYPE_ALIASES = new HashMap<>();
1381
1382    static {
1383        TYPE_ALIASES.put("bool", "boolean");
1384        TYPE_ALIASES.put("varchar", "character varying");
1385        TYPE_ALIASES.put("int", "integer");
1386        TYPE_ALIASES.put("int4", "integer");
1387        TYPE_ALIASES.put("int8", "bigint");
1388        TYPE_ALIASES.put("timestamp", "timestamp without time zone");
1389    }
1390
1391    /** Normalize PostgreSQL type aliases. */
1392    protected static String normalizeArgs(String args) {
1393        if (args.isEmpty()) {
1394            return args;
1395        }
1396        args = args.toLowerCase();
1397        List<String> argList = Arrays.asList(args.split(",[ ]*"));
1398        List<String> newArgList = new ArrayList<>(argList.size());
1399        for (String arg : argList) {
1400            // array or size spec
1401            int i = arg.indexOf('(');
1402            if (i == -1) {
1403                i = arg.indexOf('[');
1404            }
1405            String suffix = "";
1406            if (i > 0) {
1407                suffix = arg.substring(i);
1408                arg = arg.substring(0, i);
1409            }
1410            for (Entry<String, String> es : TYPE_ALIASES.entrySet()) {
1411                String type = es.getKey();
1412                if (arg.equals(type) || arg.endsWith(" " + type)) {
1413                    arg = arg.substring(0, arg.length() - type.length()) + es.getValue();
1414                    break;
1415                }
1416            }
1417            newArgList.add(arg + suffix);
1418        }
1419        return String.join(", ", newArgList);
1420    }
1421
1422}