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