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