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