Source code

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