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.HashMap;
030import java.util.LinkedList;
031import java.util.List;
032import java.util.Map;
033
034import org.nuxeo.ecm.core.api.NuxeoException;
035import org.nuxeo.ecm.core.api.security.SecurityConstants;
036import org.nuxeo.ecm.core.model.BaseSession;
037import org.nuxeo.ecm.core.model.BaseSession.VersionAclMode;
038import org.nuxeo.ecm.core.security.SecurityService;
039import org.nuxeo.ecm.core.storage.sql.ColumnType;
040import org.nuxeo.ecm.core.storage.sql.Model;
041import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
042import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger;
043import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
044import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
045import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
046import org.nuxeo.runtime.api.Framework;
047
048/**
049 * H2-specific dialect.
050 *
051 * @author Florent Guillaume
052 */
053public class DialectH2 extends Dialect {
054
055    protected static final String DEFAULT_USERS_SEPARATOR = ",";
056
057    protected final String usersSeparator;
058
059    protected final boolean disableVersionACL;
060
061    protected final boolean disableReadVersionPermission;
062
063    public DialectH2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
064        super(metadata, repositoryDescriptor);
065        if (!fulltextSearchDisabled) {
066            throw new NuxeoException("Fulltext search cannot be enabled with H2");
067        }
068        usersSeparator = repositoryDescriptor == null ? null
069                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
070                        : repositoryDescriptor.usersSeparatorKey;
071        disableVersionACL = VersionAclMode.getConfiguration() == VersionAclMode.DISABLED;
072        disableReadVersionPermission = BaseSession.isReadVersionPermissionDisabled();
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        throw new UnsupportedOperationException();
209    }
210
211    @Override
212    public String getDialectFulltextQuery(String query) {
213        throw new UnsupportedOperationException();
214    }
215
216    @Override
217    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
218            Column mainColumn, Model model, Database database) {
219        throw new UnsupportedOperationException();
220    }
221
222    @Override
223    public boolean getMaterializeFulltextSyntheticColumn() {
224        return false;
225    }
226
227    @Override
228    public int getFulltextIndexedColumns() {
229        return 0;
230    }
231
232    @Override
233    public boolean supportsUpdateFrom() {
234        return false; // check this, unused
235    }
236
237    @Override
238    public boolean doesUpdateFromRepeatSelf() {
239        return true;
240    }
241
242    @Override
243    public String getClobCast(boolean inOrderBy) {
244        if (!inOrderBy) {
245            return "CAST(%s AS VARCHAR)";
246        }
247        return null;
248    }
249
250    @Override
251    public String getSecurityCheckSql(String idColumnName) {
252        return String.format("NX_ACCESS_ALLOWED2(%s, ?, ?, %s, %s)", idColumnName, disableVersionACL,
253                disableReadVersionPermission);
254    }
255
256    @Override
257    public String getInTreeSql(String idColumnName, String id) {
258        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
259    }
260
261    @Override
262    public boolean supportsArrays() {
263        return false;
264    }
265
266    @Override
267    public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns,
268            List<Serializable> outValues) {
269        Column keyColumn = columns.get(0);
270        Table table = keyColumn.getTable();
271        StringBuilder sql = new StringBuilder();
272        sql.append("MERGE INTO ");
273        sql.append(table.getQuotedName());
274        sql.append(" KEY (");
275        sql.append(keyColumn.getQuotedName());
276        sql.append(") VALUES (");
277        for (int i = 0; i < columns.size(); i++) {
278            if (i != 0) {
279                sql.append(", ");
280            }
281            sql.append("?");
282            outColumns.add(columns.get(i));
283            outValues.add(values.get(i));
284        }
285        sql.append(")");
286        return sql.toString();
287    }
288
289    @Override
290    public boolean isConcurrentUpdateException(Throwable t) {
291        // recent versions of H2 throw a SQLException whose cause,
292        // an IllegalStateException, is not itself a SQLException
293        Throwable cause;
294        while ((cause = t.getCause()) != null && cause instanceof SQLException) {
295            t = cause;
296        }
297        if (t instanceof SQLException) {
298            String sqlState = ((SQLException) t).getSQLState();
299            if ("23503".equals(sqlState)) {
300                // Referential integrity violated child exists
301                return true;
302            }
303            if ("23505".equals(sqlState)) {
304                // Duplicate key
305                return true;
306            }
307            if ("23506".equals(sqlState)) {
308                // Referential integrity violated parent exists
309                return true;
310            }
311            if ("40001".equals(sqlState)) {
312                // Deadlock detected
313                return true;
314            }
315            if ("HYT00".equals(sqlState)) {
316                // Lock timeout
317                return true;
318            }
319            if ("90131".equals(sqlState)) {
320                // Concurrent update in table ...: another transaction has
321                // updated or deleted the same row
322                return true;
323            }
324        }
325        return false;
326    }
327
328    @Override
329    public String getSQLStatementsFilename() {
330        return "nuxeovcs/h2.sql.txt";
331    }
332
333    @Override
334    public String getTestSQLStatementsFilename() {
335        return "nuxeovcs/h2.test.sql.txt";
336    }
337
338    @Override
339    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
340        Map<String, Serializable> properties = new HashMap<>();
341        properties.put("idType", "VARCHAR(36)");
342        String[] permissions = Framework.getService(SecurityService.class)
343                                        .getPermissionsToCheck(SecurityConstants.BROWSE);
344        List<String> permsList = new LinkedList<>();
345        for (String perm : permissions) {
346            permsList.add("('" + perm + "')");
347        }
348        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
349        properties.put("readPermissions", String.join(", ", permsList));
350        properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions");
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}