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            String indexName = makeName(name, "", "_unique_pos", getMaxIndexNameSize());
502            sqls.add(String.format("CREATE UNIQUE INDEX \"%s\" ON \"%s\" (\"%s\", \"%s\")", indexName, name,
503                    Model.MAIN_KEY, Model.COLL_TABLE_POS_KEY));
504        }
505        return sqls;
506    }
507
508    @Override
509    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
510            Model model) {
511        String sql;
512        if (compatibilityFulltextTable) {
513            sql = "CREATE INDEX %s ON %s USING GIN(%s)";
514        } else {
515            sql = "CREATE INDEX %s ON %s USING GIN(NX_TO_TSVECTOR(%s))";
516        }
517        return String.format(sql, quotedIndexName.toLowerCase(), table.getQuotedName(), columns.get(0).getQuotedName());
518    }
519
520    // must not be interpreted as a regexp, we split on it
521    protected static final String FT_LIKE_SEP = " @#AND#@ ";
522
523    protected static final String FT_LIKE_COL = "??";
524
525    /**
526     * {@inheritDoc}
527     * <p>
528     * The result of this is passed to {@link #getFulltextScoredMatchInfo}.
529     */
530    @Override
531    public String getDialectFulltextQuery(String query) {
532        query = query.replace(" & ", " "); // PostgreSQL compatibility BBB
533        query = PREFIX_PATTERN.matcher(query).replaceAll(PREFIX_REPL);
534        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
535        if (ft == null) {
536            return ""; // won't match anything
537        }
538        if (!FulltextQueryAnalyzer.hasPhrase(ft)) {
539            return FulltextQueryAnalyzer.translateFulltext(ft, "|", "&", "& !", "");
540        }
541        if (compatibilityFulltextTable) {
542            throw new QueryParseException("Cannot use phrase search in fulltext compatibilty mode. "
543                    + "Please upgrade the fulltext table: " + query);
544        }
545        /*
546         * Phrase search. We have to do the phrase query using a LIKE, but for performance we pre-filter using as many
547         * fulltext matches as possible by breaking some of the phrases into words. We do an AND of the two. 1.
548         * pre-filter using fulltext on a query that is a superset of the original query,
549         */
550        FulltextQuery broken = breakPhrases(ft);
551        String ftsql = FulltextQueryAnalyzer.translateFulltext(broken, "|", "&", "& !", "");
552        /*
553         * 2. AND with a LIKE-based search for all terms, except those that are already exactly matched by the first
554         * part, i.e., toplevel ANDed non-phrases.
555         */
556        FulltextQuery noand = removeToplevelAndedWords(ft);
557        if (noand != null) {
558            StringBuilder buf = new StringBuilder();
559            generateLikeSql(noand, buf);
560            ftsql += FT_LIKE_SEP + buf.toString();
561
562        }
563        return ftsql;
564    }
565
566    /**
567     * Returns a fulltext query that is a superset of the original one and does not have phrase searches.
568     * <p>
569     * Negative phrases (which are at AND level) are removed, positive phrases are split into ANDed words.
570     */
571    protected static FulltextQuery breakPhrases(FulltextQuery ft) {
572        FulltextQuery newFt = new FulltextQuery();
573        if (ft.op == Op.AND || ft.op == Op.OR) {
574            List<FulltextQuery> newTerms = new LinkedList<>();
575            for (FulltextQuery term : ft.terms) {
576                FulltextQuery broken = breakPhrases(term);
577                if (broken == null) {
578                    // remove negative phrase
579                } else if (ft.op == Op.AND && broken.op == Op.AND) {
580                    // associativity (sub-AND hoisting)
581                    newTerms.addAll(broken.terms);
582                } else {
583                    newTerms.add(broken);
584                }
585            }
586            if (newTerms.size() == 1) {
587                // single-term parenthesis elimination
588                newFt = newTerms.get(0);
589            } else {
590                newFt.op = ft.op;
591                newFt.terms = newTerms;
592            }
593        } else {
594            boolean isPhrase = ft.isPhrase();
595            if (!isPhrase) {
596                newFt = ft;
597            } else if (ft.op == Op.WORD) {
598                // positive phrase
599                // split it
600                List<FulltextQuery> newTerms = new LinkedList<>();
601                for (String subword : ft.word.split(" ")) {
602                    FulltextQuery sft = new FulltextQuery();
603                    sft.op = Op.WORD;
604                    sft.word = subword;
605                    newTerms.add(sft);
606                }
607                newFt.op = Op.AND;
608                newFt.terms = newTerms;
609            } else {
610                // negative phrase
611                // removed
612                newFt = null;
613            }
614        }
615        return newFt;
616    }
617
618    /**
619     * Removes toplevel ANDed simple words from the query.
620     */
621    protected static FulltextQuery removeToplevelAndedWords(FulltextQuery ft) {
622        if (ft.op == Op.OR || ft.op == Op.NOTWORD) {
623            return ft;
624        }
625        if (ft.op == Op.WORD) {
626            if (ft.isPhrase()) {
627                return ft;
628            }
629            return null;
630        }
631        List<FulltextQuery> newTerms = new LinkedList<>();
632        for (FulltextQuery term : ft.terms) {
633            if (term.op == Op.NOTWORD) {
634                newTerms.add(term);
635            } else { // Op.WORD
636                if (term.isPhrase()) {
637                    newTerms.add(term);
638                }
639            }
640        }
641        if (newTerms.isEmpty()) {
642            return null;
643        } else if (newTerms.size() == 1) {
644            // single-term parenthesis elimination
645            return newTerms.get(0);
646        } else {
647            FulltextQuery newFt = new FulltextQuery();
648            newFt.op = Op.AND;
649            newFt.terms = newTerms;
650            return newFt;
651        }
652    }
653
654    // turn non-toplevel ANDed single words into SQL
655    // abc "foo bar" -"gee man"
656    // -> ?? LIKE '% foo bar %' AND ?? NOT LIKE '% gee man %'
657    // ?? is a pseudo-parameter for the col
658    protected static void generateLikeSql(FulltextQuery ft, StringBuilder buf) {
659        if (ft.op == Op.AND || ft.op == Op.OR) {
660            buf.append('(');
661            boolean first = true;
662            for (FulltextQuery term : ft.terms) {
663                if (!first) {
664                    if (ft.op == Op.AND) {
665                        buf.append(" AND ");
666                    } else { // Op.OR
667                        buf.append(" OR ");
668                    }
669                }
670                first = false;
671                generateLikeSql(term, buf);
672            }
673            buf.append(')');
674        } else {
675            buf.append(FT_LIKE_COL);
676            if (ft.op == Op.NOTWORD) {
677                buf.append(" NOT");
678            }
679            buf.append(" LIKE '% ");
680            String word = ft.word.toLowerCase();
681            // SQL escaping
682            word = word.replace("'", "''");
683            word = word.replace("\\", ""); // don't take chances
684            word = word.replace(PREFIX_SEARCH, "%");
685            buf.append(word);
686            if (!word.endsWith("%")) {
687                buf.append(" %");
688            }
689            buf.append("'");
690        }
691    }
692
693    // OLD having problems in pre-9.2 (NXP-9228)
694    // SELECT ...,
695    // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), nxquery, 32) as nxscore
696    // FROM ...
697    // LEFT JOIN fulltext ON fulltext.id = hierarchy.id,
698    // TO_TSQUERY('french', ?) nxquery
699    // WHERE ...
700    // AND nxquery @@ NX_TO_TSVECTOR(fulltext)
701    // AND fulltext LIKE '% foo bar %' -- when phrase search
702    // ORDER BY nxscore DESC
703
704    // NEW
705    // SELECT ...,
706    // TS_RANK_CD(NX_TO_TSVECTOR(fulltext), TO_TSQUERY('french', ?), 32) as
707    // nxscore
708    // FROM ...
709    // LEFT JOIN fulltext ON fulltext.id = hierarchy.id
710    // WHERE ...
711    // AND TO_TSQUERY('french', ?) @@ NX_TO_TSVECTOR(fulltext)
712    // AND fulltext LIKE '% foo bar %' -- when phrase search
713    // ORDER BY nxscore DESC
714    @Override
715    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
716            Column mainColumn, Model model, Database database) {
717        String indexSuffix = model.getFulltextIndexSuffix(indexName);
718        Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
719        Column ftMain = ft.getColumn(Model.MAIN_KEY);
720        Column ftColumn = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + indexSuffix);
721        String ftColumnName = ftColumn.getFullQuotedName();
722        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
723        FulltextMatchInfo info = new FulltextMatchInfo();
724        info.joins = new ArrayList<>();
725        if (nthMatch == 1) {
726            // Need only one JOIN involving the fulltext table
727            info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(),
728                    mainColumn.getFullQuotedName()));
729        }
730        /*
731         * for phrase search, fulltextQuery may contain a LIKE part
732         */
733        String like;
734        if (fulltextQuery.contains(FT_LIKE_SEP)) {
735            String[] tmp = fulltextQuery.split(FT_LIKE_SEP, 2);
736            fulltextQuery = tmp[0];
737            like = tmp[1].replace(FT_LIKE_COL, ftColumnName);
738        } else {
739            like = null;
740        }
741        String tsquery = String.format("TO_TSQUERY('%s', ?)", fulltextAnalyzer);
742        String tsvector;
743        if (compatibilityFulltextTable) {
744            tsvector = ftColumnName;
745        } else {
746            tsvector = String.format("NX_TO_TSVECTOR(%s)", ftColumnName);
747        }
748        String where = String.format("(%s @@ %s)", tsquery, tsvector);
749        if (like != null) {
750            where += " AND (" + like + ")";
751        }
752        info.whereExpr = where;
753        info.whereExprParam = fulltextQuery;
754        info.scoreExpr = String.format("TS_RANK_CD(%s, %s, 32)", tsvector, tsquery);
755        info.scoreExprParam = fulltextQuery;
756        info.scoreAlias = "_nxscore" + nthSuffix;
757        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
758        return info;
759    }
760
761    @Override
762    public boolean getMaterializeFulltextSyntheticColumn() {
763        return true;
764    }
765
766    @Override
767    public int getFulltextIndexedColumns() {
768        return 1;
769    }
770
771    @Override
772    public String getFreeVariableSetterForType(ColumnType type) {
773        if (type == ColumnType.FTSTORED && compatibilityFulltextTable) {
774            return "NX_TO_TSVECTOR(?)";
775        }
776        return "?";
777    }
778
779    @Override
780    public boolean supportsUpdateFrom() {
781        return true;
782    }
783
784    @Override
785    public boolean doesUpdateFromRepeatSelf() {
786        return false;
787    }
788
789    @Override
790    public boolean needsAliasForDerivedTable() {
791        return true;
792    }
793
794    @Override
795    public boolean supportsIlike() {
796        return true;
797    }
798
799    @Override
800    public boolean supportsReadAcl() {
801        return aclOptimizationsEnabled;
802    }
803
804    @Override
805    public String getPrepareUserReadAclsSql() {
806        return "SELECT nx_prepare_user_read_acls(?)";
807    }
808
809    @Override
810    public String getReadAclsCheckSql(String userIdCol) {
811        return String.format("%s = md5(array_to_string(?, '%s'))", userIdCol, getUsersSeparator());
812    }
813
814    @Override
815    public String getUpdateReadAclsSql() {
816        return "SELECT nx_update_read_acls();";
817    }
818
819    @Override
820    public String getRebuildReadAclsSql() {
821        return "SELECT nx_rebuild_read_acls();";
822    }
823
824    @Override
825    public String getSecurityCheckSql(String idColumnName) {
826        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
827    }
828
829    @Override
830    public boolean supportsAncestorsTable() {
831        return true;
832    }
833
834    @Override
835    public boolean supportsFastDescendants() {
836        return pathOptimizationsEnabled;
837    }
838
839    @Override
840    public String getInTreeSql(String idColumnName, String id) {
841        String cast;
842        try {
843            cast = getCastForId(id);
844        } catch (IllegalArgumentException e) {
845            // discard query with invalid id
846            return null;
847        }
848        if (pathOptimizationsEnabled) {
849            return String.format("EXISTS(SELECT 1 FROM ancestors WHERE id = %s AND ARRAY[?]%s <@ ancestors)",
850                    idColumnName, getCastForArray(cast));
851        }
852        return String.format("%s IN (SELECT * FROM nx_children(?%s))", idColumnName, cast);
853    }
854
855    protected String getCastForArray(String cast) {
856        if (cast.isEmpty()) {
857            return cast;
858        }
859        return cast + "[]";
860    }
861
862    protected String getCastForId(String id) {
863        String ret;
864        switch (idType) {
865        case VARCHAR:
866            return "";
867        case UUID:
868            // check that it's really a uuid
869            if (id != null) {
870                UUID.fromString(id);
871            }
872            ret = "::uuid";
873            break;
874        case SEQUENCE:
875            // check that it's really an integer
876            if (id != null && !StringUtils.isNumeric(id)) {
877                throw new IllegalArgumentException("Invalid sequence id: " + id);
878            }
879            ret = "::bigint";
880            break;
881        default:
882            throw new AssertionError("Unknown id type: " + idType);
883        }
884        return ret;
885    }
886
887    @Override
888    public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) {
889        returnParam[0] = mixin;
890        String sql = "ARRAY[?]::varchar[] <@ " + mixinsColumn.getFullQuotedName();
891        return positive ? sql : "NOT(" + sql + ")";
892    }
893
894    @Override
895    public boolean supportsSysNameArray() {
896        return true;
897    }
898
899    @Override
900    public boolean supportsArrays() {
901        return true;
902    }
903
904    @Override
905    public boolean supportsArrayColumns() {
906        return true;
907    }
908
909    public static class ArraySubQueryPostgreSQL extends ArraySubQuery {
910
911        protected Dialect dialect = null;
912
913        protected Table fakeSubqueryTableAlias = null;
914
915        public ArraySubQueryPostgreSQL(Column arrayColumn, String alias) {
916            super(arrayColumn, alias);
917            dialect = arrayColumn.getTable().getDialect();
918            fakeSubqueryTableAlias = new TableAlias(arrayColumn.getTable(), alias);
919        }
920
921        @Override
922        public Column getSubQueryIdColumn() {
923            Column column = fakeSubqueryTableAlias.getColumn(Model.MAIN_KEY);
924            return new ArraySubQueryPostgreSQLColumn(column.getPhysicalName(), column.getType());
925        }
926
927        @Override
928        public Column getSubQueryValueColumn() {
929            return new ArraySubQueryPostgreSQLColumn(Model.COLL_TABLE_VALUE_KEY, arrayColumn.getBaseType());
930        }
931
932        public class ArraySubQueryPostgreSQLColumn extends Column {
933            private static final long serialVersionUID = 1L;
934
935            ArraySubQueryPostgreSQLColumn(String columnName, ColumnType columnType) {
936                super(fakeSubqueryTableAlias, columnName, columnType, columnName);
937            }
938
939            @Override
940            public String getFullQuotedName() {
941                return dialect.openQuote() + subQueryAlias + dialect.closeQuote() + '.' + getQuotedName();
942            }
943        }
944
945        @Override
946        public String toSql() {
947            Table table = arrayColumn.getTable();
948            return String.format("(SELECT %s, UNNEST(%s) AS %s, generate_subscripts(%s, 1) AS %s FROM %s) ",
949                    table.getColumn(Model.MAIN_KEY).getQuotedName(), arrayColumn.getQuotedName(),
950                    Model.COLL_TABLE_VALUE_KEY, arrayColumn.getQuotedName(), Model.COLL_TABLE_POS_KEY,
951                    table.getRealTable().getQuotedName());
952        }
953    }
954
955    @Override
956    public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) {
957        return new ArraySubQueryPostgreSQL(arrayColumn, subQueryAlias);
958    }
959
960    @Override
961    public String getArrayElementString(String arrayColumnName, int arrayElementIndex) {
962        // PostgreSQL arrays index start at 1
963        return arrayColumnName + "[" + (arrayElementIndex + 1) + "]";
964    }
965
966    @Override
967    public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) {
968        StringBuilder sql = new StringBuilder();
969        if (!positive) {
970            sql.append("(NOT(");
971        }
972        if (params.size() == 1) {
973            // ? = ANY(arrayColumn)
974            sql.append("? = ANY(");
975            sql.append(arrayColumn.getFullQuotedName());
976            if (cast != null) {
977                // DATE cast
978                sql.append("::");
979                sql.append(cast);
980                sql.append("[]");
981            }
982            sql.append(")");
983        } else {
984            // arrayColumn && ARRAY[?, ?, ?]
985            sql.append(arrayColumn.getFullQuotedName());
986            sql.append(" && ");
987            sql.append("ARRAY[");
988            for (int i = 0; i < params.size(); i++) {
989                if (i != 0) {
990                    sql.append(", ");
991                }
992                sql.append('?');
993            }
994            sql.append("]::");
995            sql.append(arrayColumn.getSqlTypeString());
996        }
997        if (!positive) {
998            sql.append(") OR ");
999            sql.append(arrayColumn.getFullQuotedName());
1000            sql.append(" IS NULL)");
1001        }
1002        return sql.toString();
1003    }
1004
1005    @Override
1006    public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
1007        return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "LIKE");
1008    }
1009
1010    @Override
1011    public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) {
1012        return getArrayOpSql(arrayColumn, refName, positive, dataHierTable, "ILIKE");
1013    }
1014
1015    protected String getArrayOpSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable,
1016            String op) {
1017        Table table = arrayColumn.getTable();
1018        String tableAliasName = openQuote() + getTableName(refName) + closeQuote();
1019        String sql = String.format("EXISTS (SELECT 1 FROM %s AS %s WHERE %s = %s AND %s %s ?)",
1020                getArraySubQuery(arrayColumn, tableAliasName).toSql(), tableAliasName,
1021                dataHierTable.getColumn(Model.MAIN_KEY).getFullQuotedName(),
1022                tableAliasName + '.' + table.getColumn(Model.MAIN_KEY).getQuotedName(),
1023                tableAliasName + '.' + Model.COLL_TABLE_VALUE_KEY, op);
1024        if (!positive) {
1025            sql = "NOT(" + sql + ")";
1026        }
1027        return sql;
1028    }
1029
1030    @Override
1031    public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException {
1032        if (elements == null || elements.length == 0) {
1033            return null;
1034        }
1035        String typeName;
1036        switch (type) {
1037        case Types.VARCHAR:
1038            typeName = "varchar";
1039            break;
1040        case Types.CLOB:
1041            typeName = "text";
1042            break;
1043        case Types.BIT:
1044            typeName = "bool";
1045            break;
1046        case Types.BIGINT:
1047            typeName = "int8";
1048            break;
1049        case Types.DOUBLE:
1050            typeName = "float8";
1051            break;
1052        case Types.TIMESTAMP:
1053            typeName = "timestamp";
1054            break;
1055        case Types.SMALLINT:
1056            typeName = "int2";
1057            break;
1058        case Types.INTEGER:
1059            typeName = "int4";
1060            break;
1061        case Types.OTHER: // id
1062            switch (idType) {
1063            case VARCHAR:
1064                typeName = "varchar";
1065                break;
1066            case UUID:
1067                typeName = "uuid";
1068                break;
1069            case SEQUENCE:
1070                typeName = "int8";
1071                break;
1072            default:
1073                throw new AssertionError("Unknown id type: " + idType);
1074            }
1075            break;
1076        default:
1077            throw new AssertionError("Unknown type: " + type);
1078        }
1079        return connection.createArrayOf(typeName, elements);
1080    }
1081
1082    @Override
1083    public String getSQLStatementsFilename() {
1084        return "nuxeovcs/postgresql.sql.txt";
1085    }
1086
1087    @Override
1088    public String getTestSQLStatementsFilename() {
1089        return "nuxeovcs/postgresql.test.sql.txt";
1090    }
1091
1092    @Override
1093    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
1094        Map<String, Serializable> properties = new HashMap<>();
1095        switch (idType) {
1096        case VARCHAR:
1097            properties.put("idType", "varchar(36)");
1098            properties.put("idTypeParam", "varchar");
1099            properties.put("idNotPresent", "'-'");
1100            properties.put("sequenceEnabled", Boolean.FALSE);
1101            break;
1102        case UUID:
1103            properties.put("idType", "uuid");
1104            properties.put("idTypeParam", "uuid");
1105            properties.put("idNotPresent", "'00000000-FFFF-FFFF-FFFF-FFFF00000000'");
1106            properties.put("sequenceEnabled", Boolean.FALSE);
1107            break;
1108        case SEQUENCE:
1109            properties.put("idType", "int8");
1110            properties.put("idTypeParam", "int8");
1111            properties.put("idNotPresent", "-1");
1112            properties.put("sequenceEnabled", Boolean.TRUE);
1113            properties.put("idSequenceName", idSequenceName);
1114        }
1115        properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled));
1116        properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled));
1117        properties.put("fulltextAnalyzer", fulltextAnalyzer);
1118        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
1119        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
1120        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
1121        properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled));
1122        properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled));
1123        properties.put("arrayColumnsEnabled", Boolean.valueOf(arrayColumnsEnabled));
1124        if (!fulltextSearchDisabled) {
1125            Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME);
1126            FulltextConfiguration fti = model.getFulltextConfiguration();
1127            List<String> lines = new ArrayList<>(fti.indexNames.size());
1128            for (String indexName : fti.indexNames) {
1129                String suffix = model.getFulltextIndexSuffix(indexName);
1130                Column ftft = ft.getColumn(Model.FULLTEXT_FULLTEXT_KEY + suffix);
1131                Column ftst = ft.getColumn(Model.FULLTEXT_SIMPLETEXT_KEY + suffix);
1132                Column ftbt = ft.getColumn(Model.FULLTEXT_BINARYTEXT_KEY + suffix);
1133                String concat;
1134                if (compatibilityFulltextTable) {
1135                    // tsvector
1136                    concat = "  NEW.%s := COALESCE(NEW.%s, ''::TSVECTOR) || COALESCE(NEW.%s, ''::TSVECTOR);";
1137                } else {
1138                    // text with space at beginning and end
1139                    concat = "  NEW.%s := ' ' || COALESCE(NEW.%s, '') || ' ' || COALESCE(NEW.%s, '') || ' ';";
1140                }
1141                String line = String.format(concat, ftft.getQuotedName(), ftst.getQuotedName(), ftbt.getQuotedName());
1142                lines.add(line);
1143            }
1144            properties.put("fulltextTriggerStatements", String.join("\n", lines));
1145        }
1146        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
1147        List<String> permsList = new LinkedList<>();
1148        for (String perm : permissions) {
1149            permsList.add("('" + perm + "')");
1150        }
1151        properties.put("readPermissions", String.join(", ", permsList));
1152        properties.put("usersSeparator", getUsersSeparator());
1153        properties.put("everyone", SecurityConstants.EVERYONE);
1154        properties.put("readAclMaxSize", Integer.toString(readAclMaxSize));
1155        properties.put("unlogged", unloggedKeyword);
1156        return properties;
1157    }
1158
1159    @Override
1160    public List<String> getStartupSqls(Model model, Database database) {
1161        if (aclOptimizationsEnabled) {
1162            log.info("Vacuuming tables used by optimized acls");
1163            return Collections.singletonList("SELECT nx_vacuum_read_acls()");
1164        }
1165        return Collections.emptyList();
1166    }
1167
1168    @Override
1169    public boolean isClusteringSupported() {
1170        return true;
1171    }
1172
1173    @Override
1174    public String getClusterInsertInvalidations() {
1175        return "SELECT NX_CLUSTER_INVAL(?, ?, ?, ?)";
1176    }
1177
1178    @Override
1179    public String getClusterGetInvalidations() {
1180        return "DELETE FROM cluster_invals WHERE nodeid = ? RETURNING id, fragments, kind";
1181    }
1182
1183    @Override
1184    public boolean isConcurrentUpdateException(Throwable t) {
1185        while (t.getCause() != null) {
1186            t = t.getCause();
1187        }
1188        if (t instanceof SQLException) {
1189            String sqlState = ((SQLException) t).getSQLState();
1190            if ("23503".equals(sqlState)) {
1191                // insert or update on table ... violates foreign key constraint
1192                return true;
1193            }
1194            if ("23505".equals(sqlState)) {
1195                // duplicate key value violates unique constraint
1196                return true;
1197            }
1198            if ("40P01".equals(sqlState)) {
1199                // deadlock detected
1200                return true;
1201            }
1202        }
1203        return false;
1204    }
1205
1206    @Override
1207    public boolean supportsPaging() {
1208        return true;
1209    }
1210
1211    @Override
1212    public String addPagingClause(String sql, long limit, long offset) {
1213        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
1214    }
1215
1216    @Override
1217    public boolean supportsWith() {
1218        return false; // don't activate until proven useful
1219        // return supportsWith;
1220    }
1221
1222    @Override
1223    public void performAdditionalStatements(Connection connection) throws SQLException {
1224        // Check if there is a pre-existing aclr_permission table
1225        boolean createAclrPermission;
1226        try (Statement s = connection.createStatement()) {
1227            String sql = "SELECT 1 FROM pg_tables WHERE tablename = 'aclr_permission'";
1228            try (ResultSet rs = s.executeQuery(sql)) {
1229                createAclrPermission = !rs.next();
1230            }
1231        }
1232        // If no table, it will be created and filled at DDL execution time
1233        if (createAclrPermission) {
1234            return;
1235        }
1236        // Warn user if BROWSE permissions has changed
1237        Set<String> dbPermissions = new HashSet<>();
1238        try (Statement s = connection.createStatement()) {
1239            String sql = "SELECT * FROM aclr_permission";
1240            try (ResultSet rs = s.executeQuery(sql)) {
1241                while (rs.next()) {
1242                    dbPermissions.add(rs.getString(1));
1243                }
1244            }
1245        }
1246        SecurityService securityService = NXCore.getSecurityService();
1247        Set<String> confPermissions = new HashSet<>(
1248                Arrays.asList(securityService.getPermissionsToCheck(SecurityConstants.BROWSE)));
1249        if (!dbPermissions.equals(confPermissions)) {
1250            log.error("Security permission for BROWSE has changed, you need to rebuild the optimized read acls:"
1251                    + "DROP TABLE aclr_permission; DROP TABLE aclr; then restart.");
1252        }
1253    }
1254
1255    public String getUsersSeparator() {
1256        if (usersSeparator == null) {
1257            return DEFAULT_USERS_SEPARATOR;
1258        }
1259        return usersSeparator;
1260    }
1261
1262    @Override
1263    public String getValidationQuery() {
1264        return "";
1265    }
1266
1267    @Override
1268    public String getAncestorsIdsSql() {
1269        return "SELECT NX_ANCESTORS(?)";
1270    }
1271
1272    @Override
1273    public boolean needsNullsLastOnDescSort() {
1274        return true;
1275    }
1276
1277    @Override
1278    public String getDateCast() {
1279        // this is more amenable to being indexed than a CAST
1280        return "DATE(%s)";
1281    }
1282
1283    @Override
1284    public String castIdToVarchar(String expr) {
1285        switch (idType) {
1286        case VARCHAR:
1287            return expr;
1288        case UUID:
1289            return expr + "::varchar";
1290        case SEQUENCE:
1291            return expr + "::varchar";
1292        default:
1293            throw new AssertionError("Unknown id type: " + idType);
1294        }
1295    }
1296
1297    @Override
1298    public DialectIdType getIdType() {
1299        return idType;
1300    }
1301
1302    @Override
1303    public String getSoftDeleteSql() {
1304        return "SELECT NX_DELETE(?, ?)";
1305    }
1306
1307    @Override
1308    public String getSoftDeleteCleanupSql() {
1309        return "SELECT NX_DELETE_PURGE(?, ?)";
1310    }
1311
1312    @Override
1313    public String getBinaryFulltextSql(List<String> columns) {
1314        if (compatibilityFulltextTable) {
1315            // extract tokens from tsvector
1316            String columnsAs = columns.stream()
1317                                      .map(col -> "regexp_replace(" + col + "::text, $$'|'\\:[^']*'?$$, ' ', 'g')")
1318                                      .collect(Collectors.joining(", "));
1319            return "SELECT " + columnsAs + " FROM fulltext WHERE id=?";
1320        }
1321        return super.getBinaryFulltextSql(columns);
1322    }
1323
1324    // parenthesizes parameter part, with optional nested parentheses
1325    private static final Pattern SIG_MATCH = Pattern.compile("[^(]*\\((([^()]*|\\([^()]*\\))*)\\).*", Pattern.DOTALL);
1326
1327    @Override
1328    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
1329            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
1330        boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT);
1331        if (compatCheck) {
1332            procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
1333            return Collections.singletonList(procCreate);
1334        }
1335        // extract signature from create statement
1336        Matcher m = SIG_MATCH.matcher(procCreate);
1337        if (!m.matches()) {
1338            throw new NuxeoException("Cannot parse arguments: " + procCreate);
1339        }
1340        String procArgs = normalizeArgs(m.group(1));
1341        try (Statement st = connection.createStatement()) {
1342            // check if the stored procedure exists and its content
1343            String getBody = "SELECT prosrc, pg_get_function_identity_arguments(oid) FROM pg_proc WHERE proname = '"
1344                    + procName + "'";
1345            logger.log(getBody);
1346            try (ResultSet rs = st.executeQuery(getBody)) {
1347                while (rs.next()) {
1348                    String body = rs.getString(1);
1349                    String args = rs.getString(2);
1350                    if (!args.equals(procArgs)) {
1351                        // different signature
1352                        continue;
1353                    }
1354                    // stored proc already exists
1355                    if (normalizeString(procCreate).contains(normalizeString(body))) {
1356                        logger.log("  -> exists, unchanged");
1357                        return Collections.emptyList();
1358                    } else {
1359                        logger.log("  -> exists, old");
1360                        // we can't drop then recreate as for instance a function used by a trigger
1361                        // would say "cannot drop function ... because other objects depend on it"
1362                        // so we hack and do an do a replace
1363                        if (!procCreate.toLowerCase().startsWith("create ")) {
1364                            throw new NuxeoException("Should start with CREATE: " + procCreate);
1365                        }
1366                        procCreate = "CREATE OR REPLACE " + procCreate.substring("create ".length());
1367                        return Collections.singletonList(procCreate);
1368                    }
1369                }
1370            }
1371            logger.log("  -> missing");
1372            return Collections.singletonList(procCreate);
1373        }
1374    }
1375
1376    protected static String normalizeString(String string) {
1377        return string.replaceAll("[ \n\r\t]+", " ").trim();
1378    }
1379
1380    /** The type aliases that we use for our stored procedure argument definitions. */
1381    private static final Map<String, String> TYPE_ALIASES = new HashMap<>();
1382
1383    static {
1384        TYPE_ALIASES.put("bool", "boolean");
1385        TYPE_ALIASES.put("varchar", "character varying");
1386        TYPE_ALIASES.put("int", "integer");
1387        TYPE_ALIASES.put("int4", "integer");
1388        TYPE_ALIASES.put("int8", "bigint");
1389        TYPE_ALIASES.put("timestamp", "timestamp without time zone");
1390    }
1391
1392    /** Normalize PostgreSQL type aliases. */
1393    protected static String normalizeArgs(String args) {
1394        if (args.isEmpty()) {
1395            return args;
1396        }
1397        args = args.toLowerCase();
1398        List<String> argList = Arrays.asList(args.split(",[ ]*"));
1399        List<String> newArgList = new ArrayList<>(argList.size());
1400        for (String arg : argList) {
1401            // array or size spec
1402            int i = arg.indexOf('(');
1403            if (i == -1) {
1404                i = arg.indexOf('[');
1405            }
1406            String suffix = "";
1407            if (i > 0) {
1408                suffix = arg.substring(i);
1409                arg = arg.substring(0, i);
1410            }
1411            for (Entry<String, String> es : TYPE_ALIASES.entrySet()) {
1412                String type = es.getKey();
1413                if (arg.equals(type) || arg.endsWith(" " + type)) {
1414                    arg = arg.substring(0, arg.length() - type.length()) + es.getValue();
1415                    break;
1416                }
1417            }
1418            newArgList.add(arg + suffix);
1419        }
1420        return String.join(", ", newArgList);
1421    }
1422
1423}