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