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