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 */
012
013package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
014
015import java.io.Serializable;
016import java.sql.DatabaseMetaData;
017import java.sql.PreparedStatement;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020import java.sql.Types;
021import java.util.ArrayList;
022import java.util.HashMap;
023import java.util.List;
024import java.util.Map;
025
026import org.nuxeo.ecm.core.storage.sql.ColumnType;
027import org.nuxeo.ecm.core.storage.sql.Model;
028import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
029import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
030import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
031import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join;
032import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
033
034/**
035 * Derby-specific dialect.
036 *
037 * @author Florent Guillaume
038 */
039public class DialectDerby extends Dialect {
040
041    public DialectDerby(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
042        super(metadata, repositoryDescriptor);
043    }
044
045    @Override
046    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
047        switch (type.spec) {
048        case STRING:
049            if (type.isUnconstrained()) {
050                return jdbcInfo("VARCHAR(32672)", Types.VARCHAR);
051            } else if (type.isClob()) {
052                return jdbcInfo("CLOB", Types.CLOB);
053            } else {
054                return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR);
055            }
056        case BOOLEAN:
057            return jdbcInfo("SMALLINT", Types.SMALLINT);
058        case LONG:
059            return jdbcInfo("BIGINT", Types.BIGINT);
060        case DOUBLE:
061            return jdbcInfo("DOUBLE", Types.DOUBLE);
062        case TIMESTAMP:
063            return jdbcInfo("TIMESTAMP", Types.TIMESTAMP);
064        case BLOBID:
065            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
066            // -----
067        case NODEID:
068        case NODEIDFK:
069        case NODEIDFKNP:
070        case NODEIDFKMUL:
071        case NODEIDFKNULL:
072        case NODEIDPK:
073        case NODEVAL:
074            return jdbcInfo("VARCHAR(36)", Types.VARCHAR);
075        case SYSNAME:
076        case SYSNAMEARRAY:
077            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
078        case TINYINT:
079            return jdbcInfo("SMALLINT", Types.TINYINT);
080        case INTEGER:
081            return jdbcInfo("INTEGER", Types.INTEGER);
082        case AUTOINC:
083            return jdbcInfo("INTEGER GENERATED BY DEFAULT AS IDENTITY", Types.INTEGER);
084        case FTINDEXED:
085            return jdbcInfo("CLOB", Types.CLOB);
086        case FTSTORED:
087            return jdbcInfo("CLOB", Types.CLOB);
088        case CLUSTERNODE:
089            return jdbcInfo("INTEGER", Types.INTEGER);
090        case CLUSTERFRAGS:
091            return jdbcInfo("VARCHAR(4000)", Types.VARCHAR);
092        }
093        throw new AssertionError(type);
094    }
095
096    @Override
097    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
098        // CLOB vs VARCHAR compatibility
099        if (expected == Types.VARCHAR && actual == Types.CLOB) {
100            return true;
101        }
102        if (expected == Types.CLOB && actual == Types.VARCHAR) {
103            return true;
104        }
105        // INTEGER vs BIGINT compatibility
106        if (expected == Types.BIGINT && actual == Types.INTEGER) {
107            return true;
108        }
109        if (expected == Types.INTEGER && actual == Types.BIGINT) {
110            return true;
111        }
112        return false;
113    }
114
115    @Override
116    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
117            throws SQLException {
118        switch (column.getJdbcType()) {
119        case Types.VARCHAR:
120        case Types.CLOB:
121            setToPreparedStatementString(ps, index, value, column);
122            return;
123        case Types.SMALLINT:
124            ps.setBoolean(index, ((Boolean) value).booleanValue());
125            return;
126        case Types.INTEGER:
127        case Types.BIGINT:
128            ps.setLong(index, ((Number) value).longValue());
129            return;
130        case Types.DOUBLE:
131            ps.setDouble(index, ((Double) value).doubleValue());
132            return;
133        case Types.TIMESTAMP:
134            setToPreparedStatementTimestamp(ps, index, value, column);
135            return;
136        default:
137            throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
138        }
139    }
140
141    @Override
142    @SuppressWarnings("boxing")
143    public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException {
144        switch (column.getJdbcType()) {
145        case Types.VARCHAR:
146        case Types.CLOB:
147            return getFromResultSetString(rs, index, column);
148        case Types.SMALLINT:
149            return rs.getBoolean(index);
150        case Types.INTEGER:
151        case Types.BIGINT:
152            return rs.getLong(index);
153        case Types.DOUBLE:
154            return rs.getDouble(index);
155        case Types.TIMESTAMP:
156            return getFromResultSetTimestamp(rs, index, column);
157        }
158        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
159    }
160
161    @Override
162    public int getFulltextIndexedColumns() {
163        return 0;
164    }
165
166    @Override
167    public boolean getMaterializeFulltextSyntheticColumn() {
168        return true;
169    }
170
171    @Override
172    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table,
173            List<Column> columns, Model model) {
174        throw new UnsupportedOperationException();
175    }
176
177    @Override
178    public String getDialectFulltextQuery(String query) {
179        return query; // TODO
180    }
181
182    // SELECT ..., 1 as nxscore
183    // FROM ... LEFT JOIN NXFT_SEARCH('default', ?) nxfttbl
184    // .................. ON hierarchy.id = nxfttbl.KEY
185    // WHERE ... AND nxfttbl.KEY IS NOT NULL
186    // ORDER BY nxscore DESC
187    @Override
188    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
189            Column mainColumn, Model model, Database database) {
190        // TODO multiple indexes
191        Table ft = database.getTable(model.FULLTEXT_TABLE_NAME);
192        Column ftMain = ft.getColumn(model.MAIN_KEY);
193        Column ftColumn = ft.getColumn(model.FULLTEXT_FULLTEXT_KEY);
194        String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch);
195        String ftColumnName = ftColumn.getFullQuotedName();
196        if (ftColumn.getJdbcType() == Types.CLOB) {
197            String colFmt = getClobCast(false);
198            if (colFmt != null) {
199                ftColumnName = String.format(colFmt, ftColumnName, Integer.valueOf(255));
200            }
201        }
202        FulltextMatchInfo info = new FulltextMatchInfo();
203        info.joins = new ArrayList<Join>(1);
204        if (nthMatch == 1) {
205            // Need only one JOIN involving the fulltext table
206            info.joins.add(new Join(Join.INNER, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(),
207                    mainColumn.getFullQuotedName()));
208        }
209        info.whereExpr = String.format("NX_CONTAINS(%s, ?) = 1", ftColumnName);
210        info.whereExprParam = fulltextQuery;
211        info.scoreExpr = "1";
212        info.scoreAlias = "NXSCORE" + nthSuffix;
213        info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null);
214        return info;
215    }
216
217    @Override
218    public boolean supportsUpdateFrom() {
219        return false;
220    }
221
222    @Override
223    public boolean doesUpdateFromRepeatSelf() {
224        throw new UnsupportedOperationException();
225    }
226
227    @Override
228    public boolean needsAliasForDerivedTable() {
229        return true;
230    }
231
232    @Override
233    public String getClobCast(boolean inOrderBy) {
234        return "CAST(%s AS VARCHAR(%d))";
235    }
236
237    @Override
238    public String getSecurityCheckSql(String idColumnName) {
239        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName);
240    }
241
242    @Override
243    public String getInTreeSql(String idColumnName, String id) {
244        return String.format("NX_IN_TREE(%s, ?) = 1", idColumnName);
245    }
246
247    private final String derbyFunctions = "org.nuxeo.ecm.core.storage.sql.db.DerbyFunctions";
248
249    @Override
250    public String getSQLStatementsFilename() {
251        return "nuxeovcs/derby.sql.txt";
252    }
253
254    @Override
255    public String getTestSQLStatementsFilename() {
256        return "nuxeovcs/derby.test.sql.txt";
257    }
258
259    @Override
260    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
261        Map<String, Serializable> properties = new HashMap<String, Serializable>();
262        properties.put("idType", "VARCHAR(36)");
263        properties.put("fulltextEnabled", Boolean.valueOf(!fulltextSearchDisabled));
264        properties.put("derbyFunctions", derbyFunctions);
265        properties.put(SQLStatement.DIALECT_WITH_NO_SEMICOLON, Boolean.TRUE);
266        return properties;
267    }
268
269    @Override
270    public String getValidationQuery() {
271        return "VALUES 1";
272    }
273
274    @Override
275    public boolean supportsPaging() {
276        return true;
277    }
278
279    @Override
280    public String addPagingClause(String sql, long limit, long offset) {
281        return sql + String.format(" OFFSET %d ROWS FETCH FIRST %d ROWS ONLY", offset, limit); // available from 10.5
282    }
283
284    @Override
285    public boolean isConcurrentUpdateException(Throwable t) {
286        for (; t != null; t = t.getCause()) {
287            if (t instanceof SQLException) {
288                String sqlState = ((SQLException) t).getSQLState();
289                if ("23503".equals(sqlState)) {
290                    // INSERT on table ... caused a violation of foreign key
291                    // constraint ... for key ...
292                    return true;
293                }
294                if ("40001".equals(sqlState)) {
295                    // A lock could not be obtained due to a deadlock
296                    return true;
297                }
298            }
299        }
300        return false;
301    }
302
303}