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