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.DatabaseMetaData;
018import java.sql.PreparedStatement;
019import java.sql.ResultSet;
020import java.sql.SQLException;
021import java.sql.Types;
022import java.util.ArrayList;
023import java.util.Collections;
024import java.util.HashMap;
025import java.util.LinkedList;
026import java.util.List;
027import java.util.Map;
028
029import org.nuxeo.common.utils.StringUtils;
030import org.nuxeo.ecm.core.NXCore;
031import org.nuxeo.ecm.core.api.security.SecurityConstants;
032import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer;
033import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery;
034import org.nuxeo.ecm.core.storage.sql.ColumnType;
035import org.nuxeo.ecm.core.storage.sql.Model;
036import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
037import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
038import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
039import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
040import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
041
042/**
043 * H2-specific dialect.
044 *
045 * @author Florent Guillaume
046 */
047public class DialectH2 extends Dialect {
048
049    protected static final String DEFAULT_USERS_SEPARATOR = ",";
050
051    private static final String DEFAULT_FULLTEXT_ANALYZER = "org.apache.lucene.analysis.standard.StandardAnalyzer";
052
053    protected final String usersSeparator;
054
055    public DialectH2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
056        super(metadata, repositoryDescriptor);
057        usersSeparator = repositoryDescriptor == null ? null
058                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
059                        : repositoryDescriptor.usersSeparatorKey;
060    }
061
062    @Override
063    public boolean supportsIfExistsAfterTableName() {
064        return true;
065    }
066
067    @Override
068    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
069        switch (type.spec) {
070        case STRING:
071            if (type.isUnconstrained()) {
072                return jdbcInfo("VARCHAR", Types.VARCHAR);
073            } else if (type.isClob()) {
074                return jdbcInfo("CLOB", Types.CLOB);
075            } else {
076                return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR);
077            }
078        case BOOLEAN:
079            return jdbcInfo("BOOLEAN", Types.BOOLEAN);
080        case LONG:
081            return jdbcInfo("BIGINT", Types.BIGINT);
082        case DOUBLE:
083            return jdbcInfo("DOUBLE", Types.DOUBLE);
084        case TIMESTAMP:
085            return jdbcInfo("TIMESTAMP", Types.TIMESTAMP);
086        case BLOBID:
087            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
088            // -----
089        case NODEID:
090        case NODEIDFK:
091        case NODEIDFKNP:
092        case NODEIDFKMUL:
093        case NODEIDFKNULL:
094        case NODEIDPK:
095        case NODEVAL:
096            return jdbcInfo("VARCHAR(36)", Types.VARCHAR);
097        case SYSNAME:
098        case SYSNAMEARRAY:
099            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
100        case TINYINT:
101            return jdbcInfo("TINYINT", Types.TINYINT);
102        case INTEGER:
103            return jdbcInfo("INTEGER", Types.INTEGER);
104        case AUTOINC:
105            return jdbcInfo("INTEGER AUTO_INCREMENT", Types.INTEGER);
106        case FTINDEXED:
107            throw new AssertionError(type);
108        case FTSTORED:
109            return jdbcInfo("CLOB", Types.CLOB);
110        case CLUSTERNODE:
111            return jdbcInfo("INTEGER", Types.INTEGER);
112        case CLUSTERFRAGS:
113            return jdbcInfo("VARCHAR", Types.VARCHAR);
114        }
115        throw new AssertionError(type);
116    }
117
118    @Override
119    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
120        // CLOB vs VARCHAR compatibility
121        if (expected == Types.VARCHAR && actual == Types.CLOB) {
122            return true;
123        }
124        if (expected == Types.CLOB && actual == Types.VARCHAR) {
125            return true;
126        }
127        // INTEGER vs BIGINT compatibility
128        if (expected == Types.BIGINT && actual == Types.INTEGER) {
129            return true;
130        }
131        if (expected == Types.INTEGER && actual == Types.BIGINT) {
132            return true;
133        }
134        return false;
135    }
136
137    @Override
138    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
139            throws SQLException {
140        switch (column.getJdbcType()) {
141        case Types.VARCHAR:
142        case Types.CLOB:
143            setToPreparedStatementString(ps, index, value, column);
144            return;
145        case Types.BOOLEAN:
146            ps.setBoolean(index, ((Boolean) value).booleanValue());
147            return;
148        case Types.TINYINT:
149        case Types.INTEGER:
150        case Types.BIGINT:
151            ps.setLong(index, ((Number) value).longValue());
152            return;
153        case Types.DOUBLE:
154            ps.setDouble(index, ((Double) value).doubleValue());
155            return;
156        case Types.TIMESTAMP:
157            setToPreparedStatementTimestamp(ps, index, value, column);
158            return;
159        default:
160            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
161        }
162    }
163
164    @Override
165    @SuppressWarnings("boxing")
166    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
167        switch (column.getJdbcType()) {
168        case Types.VARCHAR:
169        case Types.CLOB:
170            return getFromResultSetString(rs, index, column);
171        case Types.BOOLEAN:
172            return rs.getBoolean(index);
173        case Types.TINYINT:
174        case Types.INTEGER:
175        case Types.BIGINT:
176            return rs.getLong(index);
177        case Types.DOUBLE:
178            return rs.getDouble(index);
179        case Types.TIMESTAMP:
180            return getFromResultSetTimestamp(rs, index, column);
181        }
182        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
183    }
184
185    @Override
186    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table,
187            List<Column> columns, Model model) {
188        List<String> columnNames = new ArrayList<String>(columns.size());
189        for (Column col : columns) {
190            columnNames.add("'" + col.getPhysicalName() + "'");
191        }
192        String fullIndexName = String.format("PUBLIC_%s_%s", table.getPhysicalName(), indexName);
193        String analyzer = model.getFulltextConfiguration().indexAnalyzer.get(indexName);
194        if (analyzer == null) {
195            analyzer = DEFAULT_FULLTEXT_ANALYZER;
196        }
197        return String.format("CALL NXFT_CREATE_INDEX('%s', 'PUBLIC', '%s', (%s), '%s')", fullIndexName,
198                table.getPhysicalName(), StringUtils.join(columnNames, ", "), analyzer);
199    }
200
201    @Override
202    public String getDialectFulltextQuery(String query) {
203        query = query.replace("%", "*");
204        FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query);
205        if (ft == null) {
206            return "DONTMATCHANYTHINGFOREMPTYQUERY";
207        }
208        return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "\"");
209    }
210
211    // SELECT ..., 1 as nxscore
212    // FROM ... LEFT JOIN NXFT_SEARCH('default', ?) nxfttbl
213    // .................. ON hierarchy.id = nxfttbl.KEY
214    // WHERE ... AND nxfttbl.KEY IS NOT NULL
215    // ORDER BY nxscore DESC
216    @Override
217    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
218            Column mainColumn, Model model, Database database) {
219        String phftname = database.getTable(Model.FULLTEXT_TABLE_NAME).getPhysicalName();
220        String fullIndexName = "PUBLIC_" + phftname + "_" + indexName;
221        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
222        String tableAlias = "_NXFTTBL" + nthSuffix;
223        String quotedTableAlias = openQuote() + tableAlias + closeQuote();
224        FulltextMatchInfo info = new FulltextMatchInfo();
225        info.joins = Collections.singletonList( //
226        new Join(Join.LEFT, //
227                String.format("NXFT_SEARCH('%s', ?)", fullIndexName), tableAlias, // alias
228                fulltextQuery, // param
229                String.format("%s.KEY", quotedTableAlias), // on1
230                mainColumn.getFullQuotedName() // on2
231        ));
232        info.whereExpr = String.format("%s.KEY IS NOT NULL", quotedTableAlias);
233        info.scoreExpr = "1";
234        info.scoreAlias = "_NXSCORE" + nthSuffix;
235        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
236        return info;
237    }
238
239    @Override
240    public boolean getMaterializeFulltextSyntheticColumn() {
241        return false;
242    }
243
244    @Override
245    public int getFulltextIndexedColumns() {
246        return 2;
247    }
248
249    @Override
250    public boolean supportsUpdateFrom() {
251        return false; // check this, unused
252    }
253
254    @Override
255    public boolean doesUpdateFromRepeatSelf() {
256        return true;
257    }
258
259    @Override
260    public String getClobCast(boolean inOrderBy) {
261        if (!inOrderBy) {
262            return "CAST(%s AS VARCHAR)";
263        }
264        return null;
265    }
266
267    @Override
268    public String getSecurityCheckSql(String idColumnName) {
269        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName);
270    }
271
272    @Override
273    public String getInTreeSql(String idColumnName, String id) {
274        return String.format("NX_IN_TREE(%s, ?)", idColumnName);
275    }
276
277    @Override
278    public boolean supportsArrays() {
279        return false;
280    }
281
282    @Override
283    public boolean isConcurrentUpdateException(Throwable t) {
284        while (t.getCause() != null) {
285            t = t.getCause();
286        }
287        if (t instanceof SQLException) {
288            String sqlState = ((SQLException) t).getSQLState();
289            if ("23001".equals(sqlState)) {
290                // Unique index or primary key violation
291                return true;
292            }
293            if ("23002".equals(sqlState)) {
294                // Referential integrity constraint violation
295                return true;
296            }
297            if ("23506".equals(sqlState)) {
298                // Referential integrity constraint violation
299                return true;
300            }
301            if ("40001".equals(sqlState)) {
302                // Deadlock detected
303                return true;
304            }
305            if ("HYT00".equals(sqlState)) {
306                // Timeout trying to lock table
307                return true;
308            }
309            if ("90131".equals(sqlState)) {
310                // Concurrent update in table ...: another transaction has
311                // updated or deleted the same row
312                return true;
313            }
314        }
315        return false;
316    }
317
318    @Override
319    public String getSQLStatementsFilename() {
320        return "nuxeovcs/h2.sql.txt";
321    }
322
323    @Override
324    public String getTestSQLStatementsFilename() {
325        return "nuxeovcs/h2.test.sql.txt";
326    }
327
328    @Override
329    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
330        Map<String, Serializable> properties = new HashMap<String, Serializable>();
331        properties.put("idType", "VARCHAR(36)");
332        String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE);
333        List<String> permsList = new LinkedList<String>();
334        for (String perm : permissions) {
335            permsList.add("('" + perm + "')");
336        }
337        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextSearchDisabled));
338        properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled));
339        properties.put("readPermissions", StringUtils.join(permsList, ", "));
340        properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions");
341        properties.put("h2Fulltext", "org.nuxeo.ecm.core.storage.sql.db.H2Fulltext");
342        properties.put("usersSeparator", getUsersSeparator());
343        return properties;
344    }
345
346    @Override
347    public boolean isClusteringSupported() {
348        return true;
349    }
350
351    @Override
352    public String getClusterInsertInvalidations() {
353        return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)";
354    }
355
356    @Override
357    public String getClusterGetInvalidations() {
358        return "SELECT * FROM NX_CLUSTER_GET_INVALS(?)";
359    }
360
361    @Override
362    public boolean supportsPaging() {
363        return true;
364    }
365
366    @Override
367    public String addPagingClause(String sql, long limit, long offset) {
368        return sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
369    }
370
371    public String getUsersSeparator() {
372        if (usersSeparator == null) {
373            return DEFAULT_USERS_SEPARATOR;
374        }
375        return usersSeparator;
376    }
377
378    @Override
379    public String getBlobLengthFunction() {
380        return "LENGTH";
381    }
382
383    @Override
384    public String getAncestorsIdsSql() {
385        return "CALL NX_ANCESTORS(?)";
386    }
387
388}