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        // -----
102        case NODEID:
103        case NODEIDFK:
104        case NODEIDFKNP:
105        case NODEIDFKMUL:
106        case NODEIDFKNULL:
107        case NODEIDPK:
108        case NODEVAL:
109            return jdbcInfo("VARCHAR(36)", Types.VARCHAR);
110        case SYSNAME:
111        case SYSNAMEARRAY:
112            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
113        case TINYINT:
114            return jdbcInfo("TINYINT", Types.TINYINT);
115        case INTEGER:
116            return jdbcInfo("INTEGER", Types.INTEGER);
117        case AUTOINC:
118            return jdbcInfo("INTEGER AUTO_INCREMENT", Types.INTEGER);
119        case FTINDEXED:
120            throw new AssertionError(type);
121        case FTSTORED:
122            return jdbcInfo("CLOB", Types.CLOB);
123        case CLUSTERNODE:
124            return jdbcInfo("INTEGER", Types.INTEGER);
125        case CLUSTERFRAGS:
126            return jdbcInfo("VARCHAR", Types.VARCHAR);
127        }
128        throw new AssertionError(type);
129    }
130
131    @Override
132    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
133        // CLOB vs VARCHAR compatibility
134        if (expected == Types.VARCHAR && actual == Types.CLOB) {
135            return true;
136        }
137        if (expected == Types.CLOB && actual == Types.VARCHAR) {
138            return true;
139        }
140        // INTEGER vs BIGINT compatibility
141        if (expected == Types.BIGINT && actual == Types.INTEGER) {
142            return true;
143        }
144        if (expected == Types.INTEGER && actual == Types.BIGINT) {
145            return true;
146        }
147        return false;
148    }
149
150    @Override
151    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
152            throws SQLException {
153        switch (column.getJdbcType()) {
154        case Types.VARCHAR:
155        case Types.CLOB:
156            setToPreparedStatementString(ps, index, value, column);
157            return;
158        case Types.BOOLEAN:
159            ps.setBoolean(index, ((Boolean) value).booleanValue());
160            return;
161        case Types.TINYINT:
162        case Types.INTEGER:
163        case Types.BIGINT:
164            ps.setLong(index, ((Number) value).longValue());
165            return;
166        case Types.DOUBLE:
167            ps.setDouble(index, ((Double) value).doubleValue());
168            return;
169        case Types.TIMESTAMP:
170            setToPreparedStatementTimestamp(ps, index, value, column);
171            return;
172        default:
173            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
174        }
175    }
176
177    @Override
178    @SuppressWarnings("boxing")
179    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
180        switch (column.getJdbcType()) {
181        case Types.VARCHAR:
182        case Types.CLOB:
183            return getFromResultSetString(rs, index, column);
184        case Types.BOOLEAN:
185            return rs.getBoolean(index);
186        case Types.TINYINT:
187        case Types.INTEGER:
188        case Types.BIGINT:
189            return rs.getLong(index);
190        case Types.DOUBLE:
191            return rs.getDouble(index);
192        case Types.TIMESTAMP:
193            return getFromResultSetTimestamp(rs, index, column);
194        }
195        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
196    }
197
198    @Override
199    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns,
200            Model model) {
201        String columnNames = columns.stream()
202                                    .map(column -> "'" + column.getPhysicalName() + "'")
203                                    .collect(Collectors.joining(", "));
204        String fullIndexName = String.format("PUBLIC_%s_%s", table.getPhysicalName(), indexName);
205        return String.format("CALL NXFT_CREATE_INDEX('%s', 'PUBLIC', '%s', (%s), '%s')", fullIndexName,
206                table.getPhysicalName(), columnNames, fulltextAnalyzer);
207    }
208
209    @Override
210    public String getDialectFulltextQuery(String query) {
211        query = query.replace("%", "*");
212        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
213        if (ft == null) {
214            return "DONTMATCHANYTHINGFOREMPTYQUERY";
215        }
216        return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "\"");
217    }
218
219    // SELECT ..., 1 as nxscore
220    // FROM ... LEFT JOIN NXFT_SEARCH('default', ?) nxfttbl
221    // .................. ON hierarchy.id = nxfttbl.KEY
222    // WHERE ... AND nxfttbl.KEY IS NOT NULL
223    // ORDER BY nxscore DESC
224    @Override
225    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
226            Column mainColumn, Model model, Database database) {
227        String phftname = database.getTable(Model.FULLTEXT_TABLE_NAME).getPhysicalName();
228        String fullIndexName = "PUBLIC_" + phftname + "_" + indexName;
229        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
230        String tableAlias = "_NXFTTBL" + nthSuffix;
231        String quotedTableAlias = openQuote() + tableAlias + closeQuote();
232        FulltextMatchInfo info = new FulltextMatchInfo();
233        info.joins = Collections.singletonList( //
234                new Join(Join.LEFT, //
235                        String.format("NXFT_SEARCH('%s', ?)", fullIndexName), tableAlias, // alias
236                        fulltextQuery, // param
237                        String.format("%s.KEY", quotedTableAlias), // on1
238                        mainColumn.getFullQuotedName() // on2
239        ));
240        info.whereExpr = String.format("%s.KEY IS NOT NULL", quotedTableAlias);
241        info.scoreExpr = "1";
242        info.scoreAlias = "_NXSCORE" + nthSuffix;
243        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
244        return info;
245    }
246
247    @Override
248    public boolean getMaterializeFulltextSyntheticColumn() {
249        return false;
250    }
251
252    @Override
253    public int getFulltextIndexedColumns() {
254        return 2;
255    }
256
257    @Override
258    public boolean supportsUpdateFrom() {
259        return false; // check this, unused
260    }
261
262    @Override
263    public boolean doesUpdateFromRepeatSelf() {
264        return true;
265    }
266
267    @Override
268    public String getClobCast(boolean inOrderBy) {
269        if (!inOrderBy) {
270            return "CAST(%s AS VARCHAR)";
271        }
272        return null;
273    }
274
275    @Override
276    public String getSecurityCheckSql(String idColumnName) {
277        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
278    }
279
280    @Override
281    public String getInTreeSql(String idColumnName, String id) {
282        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
283    }
284
285    @Override
286    public boolean supportsArrays() {
287        return false;
288    }
289
290    @Override
291    public boolean isConcurrentUpdateException(Throwable t) {
292        while (t.getCause() != null) {
293            t = t.getCause();
294        }
295        if (t instanceof SQLException) {
296            String sqlState = ((SQLException) t).getSQLState();
297            if ("23503".equals(sqlState)) {
298                // Referential integrity violated child exists
299                return true;
300            }
301            if ("23505".equals(sqlState)) {
302                // Duplicate key
303                return true;
304            }
305            if ("23506".equals(sqlState)) {
306                // Referential integrity violated parent exists
307                return true;
308            }
309            if ("40001".equals(sqlState)) {
310                // Deadlock detected
311                return true;
312            }
313            if ("HYT00".equals(sqlState)) {
314                // Lock timeout
315                return true;
316            }
317            if ("90131".equals(sqlState)) {
318                // Concurrent update in table ...: another transaction has
319                // updated or deleted the same row
320                return true;
321            }
322        }
323        return false;
324    }
325
326    @Override
327    public String getSQLStatementsFilename() {
328        return "nuxeovcs/h2.sql.txt";
329    }
330
331    @Override
332    public String getTestSQLStatementsFilename() {
333        return "nuxeovcs/h2.test.sql.txt";
334    }
335
336    @Override
337    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
338        Map<String, Serializable> properties = new HashMap<>();
339        properties.put("idType", "VARCHAR(36)");
340        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
341        List<String> permsList = new LinkedList<>();
342        for (String perm : permissions) {
343            permsList.add("('" + perm + "')");
344        }
345        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled));
346        properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled));
347        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
348        properties.put("readPermissions", String.join(", ", permsList));
349        properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions");
350        properties.put("h2Fulltext", "org.nuxeo.ecm.core.storage.sql.db.H2Fulltext");
351        properties.put("usersSeparator", getUsersSeparator());
352        return properties;
353    }
354
355    @Override
356    public boolean isClusteringSupported() {
357        return true;
358    }
359
360    @Override
361    public String getClusterInsertInvalidations() {
362        return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)";
363    }
364
365    @Override
366    public String getClusterGetInvalidations() {
367        return "SELECT * FROM NX_CLUSTER_GET_INVALS(?)";
368    }
369
370    @Override
371    public boolean supportsPaging() {
372        return true;
373    }
374
375    @Override
376    public String addPagingClause(String sql, long limit, long offset) {
377        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
378    }
379
380    public String getUsersSeparator() {
381        if (usersSeparator == null) {
382            return DEFAULT_USERS_SEPARATOR;
383        }
384        return usersSeparator;
385    }
386
387    @Override
388    public String getBlobLengthFunction() {
389        return "LENGTH";
390    }
391
392    @Override
393    public String getAncestorsIdsSql() {
394        return "CALL NX_ANCESTORS(?)";
395    }
396
397    @Override
398    public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection,
399            JDBCLogger logger, Map<String, Serializable> properties) throws SQLException {
400        throw new UnsupportedOperationException();
401    }
402
403}