001/*
002 * (C) Copyright 2006-2016 Nuxeo SA (http://nuxeo.com/) and others.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *     http://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 *
016 * Contributors:
017 *     Florent Guillaume
018 *     Benoit Delbosc
019 */
020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
021
022import java.io.Serializable;
023import java.sql.Connection;
024import java.sql.DatabaseMetaData;
025import java.sql.PreparedStatement;
026import java.sql.ResultSet;
027import java.sql.SQLException;
028import java.sql.Types;
029import java.util.Collections;
030import java.util.HashMap;
031import java.util.LinkedList;
032import java.util.List;
033import java.util.Map;
034import java.util.stream.Collectors;
035
036import org.nuxeo.common.utils.StringUtils;
037import org.nuxeo.ecm.core.NXCore;
038import org.nuxeo.ecm.core.api.security.SecurityConstants;
039import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
040import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
041import org.nuxeo.ecm.core.storage.sql.ColumnType;
042import org.nuxeo.ecm.core.storage.sql.Model;
043import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
044import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
045import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
046import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
047import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
048import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
049
050/**
051 * H2-specific dialect.
052 *
053 * @author Florent Guillaume
054 */
055public class DialectH2 extends Dialect {
056
057    protected static final String DEFAULT_USERS_SEPARATOR = ",";
058
059    private static final String DEFAULT_FULLTEXT_ANALYZER = "org.apache.lucene.analysis.standard.StandardAnalyzer";
060
061    protected final String fulltextAnalyzer;
062
063    protected final String usersSeparator;
064
065    public DialectH2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
066        super(metadata, repositoryDescriptor);
067        fulltextAnalyzer = repositoryDescriptor == null ? null
068                : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER
069                        : repositoryDescriptor.getFulltextAnalyzer();
070        usersSeparator = repositoryDescriptor == null ? null
071                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
072                        : repositoryDescriptor.usersSeparatorKey;
073    }
074
075    @Override
076    public boolean supportsIfExistsAfterTableName() {
077        return true;
078    }
079
080    @Override
081    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
082        switch (type.spec) {
083        case STRING:
084            if (type.isUnconstrained()) {
085                return jdbcInfo("VARCHAR", Types.VARCHAR);
086            } else if (type.isClob()) {
087                return jdbcInfo("CLOB", Types.CLOB);
088            } else {
089                return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR);
090            }
091        case BOOLEAN:
092            return jdbcInfo("BOOLEAN", Types.BOOLEAN);
093        case LONG:
094            return jdbcInfo("BIGINT", Types.BIGINT);
095        case DOUBLE:
096            return jdbcInfo("DOUBLE", Types.DOUBLE);
097        case TIMESTAMP:
098            return jdbcInfo("TIMESTAMP", Types.TIMESTAMP);
099        case BLOBID:
100            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
101        case BLOB:
102            return jdbcInfo("BLOB", Types.BLOB);
103        // -----
104        case NODEID:
105        case NODEIDFK:
106        case NODEIDFKNP:
107        case NODEIDFKMUL:
108        case NODEIDFKNULL:
109        case NODEIDPK:
110        case NODEVAL:
111            return jdbcInfo("VARCHAR(36)", Types.VARCHAR);
112        case SYSNAME:
113        case SYSNAMEARRAY:
114            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
115        case TINYINT:
116            return jdbcInfo("TINYINT", Types.TINYINT);
117        case INTEGER:
118            return jdbcInfo("INTEGER", Types.INTEGER);
119        case AUTOINC:
120            return jdbcInfo("INTEGER AUTO_INCREMENT", Types.INTEGER);
121        case FTINDEXED:
122            throw new AssertionError(type);
123        case FTSTORED:
124            return jdbcInfo("CLOB", Types.CLOB);
125        case CLUSTERNODE:
126            return jdbcInfo("INTEGER", Types.INTEGER);
127        case CLUSTERFRAGS:
128            return jdbcInfo("VARCHAR", Types.VARCHAR);
129        }
130        throw new AssertionError(type);
131    }
132
133    @Override
134    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
135        // CLOB vs VARCHAR compatibility
136        if (expected == Types.VARCHAR && actual == Types.CLOB) {
137            return true;
138        }
139        if (expected == Types.CLOB && actual == Types.VARCHAR) {
140            return true;
141        }
142        // INTEGER vs BIGINT compatibility
143        if (expected == Types.BIGINT && actual == Types.INTEGER) {
144            return true;
145        }
146        if (expected == Types.INTEGER && actual == Types.BIGINT) {
147            return true;
148        }
149        return false;
150    }
151
152    @Override
153    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
154            throws SQLException {
155        switch (column.getJdbcType()) {
156        case Types.VARCHAR:
157        case Types.CLOB:
158            setToPreparedStatementString(ps, index, value, column);
159            return;
160        case Types.BOOLEAN:
161            ps.setBoolean(index, ((Boolean) value).booleanValue());
162            return;
163        case Types.TINYINT:
164        case Types.INTEGER:
165        case Types.BIGINT:
166            ps.setLong(index, ((Number) value).longValue());
167            return;
168        case Types.DOUBLE:
169            ps.setDouble(index, ((Double) value).doubleValue());
170            return;
171        case Types.TIMESTAMP:
172            setToPreparedStatementTimestamp(ps, index, value, column);
173            return;
174        case Types.BLOB:
175            ps.setBytes(index, (byte[]) value);
176            return;
177        default:
178            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
179        }
180    }
181
182    @Override
183    @SuppressWarnings("boxing")
184    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
185        switch (column.getJdbcType()) {
186        case Types.VARCHAR:
187        case Types.CLOB:
188            return getFromResultSetString(rs, index, column);
189        case Types.BOOLEAN:
190            return rs.getBoolean(index);
191        case Types.TINYINT:
192        case Types.INTEGER:
193        case Types.BIGINT:
194            return rs.getLong(index);
195        case Types.DOUBLE:
196            return rs.getDouble(index);
197        case Types.TIMESTAMP:
198            return getFromResultSetTimestamp(rs, index, column);
199        case Types.BLOB:
200            return rs.getBytes(index);
201        }
202        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
203    }
204
205    @Override
206    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
207            Model model) {
208        String columnNames = columns.stream()
209                                    .map(column -> "'" + column.getPhysicalName() + "'")
210                                    .collect(Collectors.joining(", "));
211        String fullIndexName = String.format("PUBLIC_%s_%s", table.getPhysicalName(), indexName);
212        return String.format("CALL NXFT_CREATE_INDEX('%s', 'PUBLIC', '%s', (%s), '%s')", fullIndexName,
213                table.getPhysicalName(), columnNames, fulltextAnalyzer);
214    }
215
216    @Override
217    public String getDialectFulltextQuery(String query) {
218        query = query.replace("%", "*");
219        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
220        if (ft == null) {
221            return "DONTMATCHANYTHINGFOREMPTYQUERY";
222        }
223        return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "\"");
224    }
225
226    // SELECT ..., 1 as nxscore
227    // FROM ... LEFT JOIN NXFT_SEARCH('default', ?) nxfttbl
228    // .................. ON hierarchy.id = nxfttbl.KEY
229    // WHERE ... AND nxfttbl.KEY IS NOT NULL
230    // ORDER BY nxscore DESC
231    @Override
232    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
233            Column mainColumn, Model model, Database database) {
234        String phftname = database.getTable(Model.FULLTEXT_TABLE_NAME).getPhysicalName();
235        String fullIndexName = "PUBLIC_" + phftname + "_" + indexName;
236        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
237        String tableAlias = "_NXFTTBL" + nthSuffix;
238        String quotedTableAlias = openQuote() + tableAlias + closeQuote();
239        FulltextMatchInfo info = new FulltextMatchInfo();
240        info.joins = Collections.singletonList( //
241                new Join(Join.LEFT, //
242                        String.format("NXFT_SEARCH('%s', ?)", fullIndexName), tableAlias, // alias
243                        fulltextQuery, // param
244                        String.format("%s.KEY", quotedTableAlias), // on1
245                        mainColumn.getFullQuotedName() // on2
246        ));
247        info.whereExpr = String.format("%s.KEY IS NOT NULL", quotedTableAlias);
248        info.scoreExpr = "1";
249        info.scoreAlias = "_NXSCORE" + nthSuffix;
250        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
251        return info;
252    }
253
254    @Override
255    public boolean getMaterializeFulltextSyntheticColumn() {
256        return false;
257    }
258
259    @Override
260    public int getFulltextIndexedColumns() {
261        return 2;
262    }
263
264    @Override
265    public boolean supportsUpdateFrom() {
266        return false; // check this, unused
267    }
268
269    @Override
270    public boolean doesUpdateFromRepeatSelf() {
271        return true;
272    }
273
274    @Override
275    public String getClobCast(boolean inOrderBy) {
276        if (!inOrderBy) {
277            return "CAST(%s AS VARCHAR)";
278        }
279        return null;
280    }
281
282    @Override
283    public String getSecurityCheckSql(String idColumnName) {
284        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
285    }
286
287    @Override
288    public String getInTreeSql(String idColumnName, String id) {
289        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
290    }
291
292    @Override
293    public boolean supportsArrays() {
294        return false;
295    }
296
297    @Override
298    public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns,
299            List<Serializable> outValues) {
300        Column keyColumn = columns.get(0);
301        Table table = keyColumn.getTable();
302        StringBuilder sql = new StringBuilder();
303        sql.append("MERGE INTO ");
304        sql.append(table.getQuotedName());
305        sql.append(" KEY (");
306        sql.append(keyColumn.getQuotedName());
307        sql.append(") VALUES (");
308        for (int i = 0; i < columns.size(); i++) {
309            if (i != 0) {
310                sql.append(", ");
311            }
312            sql.append("?");
313            outColumns.add(columns.get(i));
314            outValues.add(values.get(i));
315        }
316        sql.append(")");
317        return sql.toString();
318    }
319
320    @Override
321    public boolean isConcurrentUpdateException(Throwable t) {
322        while (t.getCause() != null) {
323            t = t.getCause();
324        }
325        if (t instanceof SQLException) {
326            String sqlState = ((SQLException) t).getSQLState();
327            if ("23503".equals(sqlState)) {
328                // Referential integrity violated child exists
329                return true;
330            }
331            if ("23505".equals(sqlState)) {
332                // Duplicate key
333                return true;
334            }
335            if ("23506".equals(sqlState)) {
336                // Referential integrity violated parent exists
337                return true;
338            }
339            if ("40001".equals(sqlState)) {
340                // Deadlock detected
341                return true;
342            }
343            if ("HYT00".equals(sqlState)) {
344                // Lock timeout
345                return true;
346            }
347            if ("90131".equals(sqlState)) {
348                // Concurrent update in table ...: another transaction has
349                // updated or deleted the same row
350                return true;
351            }
352        }
353        return false;
354    }
355
356    @Override
357    public String getSQLStatementsFilename() {
358        return "nuxeovcs/h2.sql.txt";
359    }
360
361    @Override
362    public String getTestSQLStatementsFilename() {
363        return "nuxeovcs/h2.test.sql.txt";
364    }
365
366    @Override
367    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
368        Map<String, Serializable> properties = new HashMap<>();
369        properties.put("idType", "VARCHAR(36)");
370        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
371        List<String> permsList = new LinkedList<>();
372        for (String perm : permissions) {
373            permsList.add("('" + perm + "')");
374        }
375        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
376        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
377        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
378        properties.put("readPermissions", String.join(", ", permsList));
379        properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions");
380        properties.put("h2Fulltext", "org.nuxeo.ecm.core.storage.sql.db.H2Fulltext");
381        properties.put("usersSeparator", getUsersSeparator());
382        return properties;
383    }
384
385    @Override
386    public boolean isClusteringSupported() {
387        return true;
388    }
389
390    @Override
391    public String getClusterInsertInvalidations() {
392        return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)";
393    }
394
395    @Override
396    public String getClusterGetInvalidations() {
397        return "SELECT * FROM NX_CLUSTER_GET_INVALS(?)";
398    }
399
400    @Override
401    public boolean supportsPaging() {
402        return true;
403    }
404
405    @Override
406    public String addPagingClause(String sql, long limit, long offset) {
407        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
408    }
409
410    public String getUsersSeparator() {
411        if (usersSeparator == null) {
412            return DEFAULT_USERS_SEPARATOR;
413        }
414        return usersSeparator;
415    }
416
417    @Override
418    public String getBlobLengthFunction() {
419        return "LENGTH";
420    }
421
422    @Override
423    public String getAncestorsIdsSql() {
424        return "CALL NX_ANCESTORS(?)";
425    }
426
427    @Override
428    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
429            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
430        throw new UnsupportedOperationException();
431    }
432
433}