Source code

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