001/*
002 * Copyright (c) 2006-2013 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 */
012package org.nuxeo.ecm.core.storage.sql.jdbc.dialect;
013
014import java.io.Serializable;
015import java.sql.DatabaseMetaData;
016import java.sql.PreparedStatement;
017import java.sql.ResultSet;
018import java.sql.SQLException;
019import java.sql.Types;
020import java.util.HashMap;
021import java.util.List;
022import java.util.Map;
023
024import org.nuxeo.ecm.core.storage.sql.ColumnType;
025import org.nuxeo.ecm.core.storage.sql.Model;
026import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor;
027import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column;
028import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database;
029import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table;
030
031/**
032 * DB2-specific dialect.
033 */
034public class DialectDB2 extends Dialect {
035
036    protected final String fulltextParameters;
037
038    private static final String DEFAULT_USERS_SEPARATOR = "|";
039
040    protected String usersSeparator;
041
042    public DialectDB2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) {
043        super(metadata, repositoryDescriptor);
044        fulltextParameters = repositoryDescriptor == null ? null : repositoryDescriptor.fulltextAnalyzer == null ? ""
045                : repositoryDescriptor.fulltextAnalyzer;
046        usersSeparator = repositoryDescriptor == null ? null
047                : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR
048                        : repositoryDescriptor.usersSeparatorKey;
049        fulltextDisabled = true;
050        if (repositoryDescriptor != null) {
051            repositoryDescriptor.setFulltextDisabled(true);
052        }
053    }
054
055    @Override
056    public String getCascadeDropConstraintsString() {
057        return " CASCADE";
058    }
059
060    @Override
061    public JDBCInfo getJDBCTypeAndString(ColumnType type) {
062        switch (type.spec) {
063        case STRING:
064            if (type.isUnconstrained()) {
065                return jdbcInfo("VARCHAR(255)", Types.VARCHAR);
066            } else if (type.isClob() || type.length > 2000) {
067                return jdbcInfo("CLOB", Types.CLOB);
068            } else {
069                return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR);
070            }
071        case BOOLEAN:
072            return jdbcInfo("SMALLINT", Types.BIT);
073        case LONG:
074            return jdbcInfo("BIGINT", Types.BIGINT);
075        case DOUBLE:
076            return jdbcInfo("DOUBLE", Types.DOUBLE);
077        case TIMESTAMP:
078            return jdbcInfo("TIMESTAMP", Types.TIMESTAMP);
079        case BLOBID:
080            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
081            // -----
082        case NODEID:
083        case NODEIDFK:
084        case NODEIDFKNP:
085        case NODEIDFKMUL:
086        case NODEIDFKNULL:
087        case NODEIDPK:
088        case NODEVAL:
089            return jdbcInfo("VARCHAR(36)", Types.VARCHAR);
090        case SYSNAME:
091        case SYSNAMEARRAY:
092            return jdbcInfo("VARCHAR(250)", Types.VARCHAR);
093        case TINYINT:
094            return jdbcInfo("SMALLINT", Types.TINYINT);
095        case INTEGER:
096            return jdbcInfo("INTEGER", Types.INTEGER);
097        case AUTOINC:
098            return jdbcInfo("INTEGER", Types.INTEGER); // TODO
099        case FTINDEXED:
100            return jdbcInfo("CLOB", Types.CLOB);
101        case FTSTORED:
102            return jdbcInfo("CLOB", Types.CLOB);
103        case CLUSTERNODE:
104            return jdbcInfo("VARCHAR(25)", Types.VARCHAR);
105        case CLUSTERFRAGS:
106            return jdbcInfo("VARCHAR(4000)", Types.VARCHAR);
107        default:
108            throw new AssertionError(type);
109        }
110    }
111
112    @Override
113    public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) {
114        if (expected == Types.BIT && actual == Types.SMALLINT) {
115            return true;
116        }
117        return false;
118    }
119
120    @Override
121    public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column)
122            throws SQLException {
123        switch (column.getJdbcType()) {
124        case Types.VARCHAR:
125        case Types.CLOB:
126            setToPreparedStatementString(ps, index, value, column);
127            return;
128        case Types.BIT:
129            ps.setInt(index, ((Boolean) value).booleanValue() ? 1 : 0);
130            return;
131        case Types.TINYINT:
132        case Types.SMALLINT:
133            ps.setInt(index, ((Long) value).intValue());
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.BIT:
158            return rs.getBoolean(index);
159        case Types.TINYINT:
160        case Types.SMALLINT:
161        case Types.INTEGER:
162        case Types.BIGINT:
163            return rs.getLong(index);
164        case Types.DOUBLE:
165            return rs.getDouble(index);
166        case Types.TIMESTAMP:
167            return getFromResultSetTimestamp(rs, index, column);
168        }
169        throw new SQLException("Unhandled JDBC type: " + column.getJdbcType());
170    }
171
172    @Override
173    protected int getMaxNameSize() {
174        // since DB2 9.5
175        // http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.wn.doc%2Fdoc%2Fc0051391.html
176        return 128;
177    }
178
179    @Override
180    public boolean supportsReadAcl() {
181        return false; // TODO
182    }
183
184    @Override
185    public boolean isClusteringSupported() {
186        return false;
187    }
188
189    @Override
190    public boolean supportsPaging() {
191        return false;
192    }
193
194    // check
195    // http://www.channeldb2.com/profiles/blogs/porting-limit-and-offset
196    // http://programmingzen.com/2010/06/02/enabling-limit-and-offset-in-db2-9-7-2/
197    // https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/limit_offset?lang=en
198    @Override
199    public String addPagingClause(String sql, long limit, long offset) {
200        return null;
201    }
202
203    @Override
204    public String getSQLStatementsFilename() {
205        return "nuxeovcs/db2.sql.txt";
206    }
207
208    @Override
209    public String getTestSQLStatementsFilename() {
210        return "nuxeovcs/db2.test.sql.txt";
211    }
212
213    @Override
214    public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) {
215        Map<String, Serializable> properties = new HashMap<String, Serializable>();
216        properties.put("idType", "VARCHAR(36)");
217        properties.put("argIdType", "VARCHAR(36)"); // in function args
218        return properties;
219    }
220
221    @Override
222    public String getValidationQuery() {
223        return "VALUES 1";
224    }
225
226    public String getUsersSeparator() {
227        if (usersSeparator == null) {
228            return DEFAULT_USERS_SEPARATOR;
229        }
230        return usersSeparator;
231    }
232
233    @Override
234    public int getFulltextIndexedColumns() {
235        return 2;
236    }
237
238    @Override
239    public boolean getMaterializeFulltextSyntheticColumn() {
240        return true;
241    }
242
243    @Override
244    public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table,
245            List<Column> columns, Model model) {
246        throw new UnsupportedOperationException();
247    }
248
249    @Override
250    public String getDialectFulltextQuery(String query) {
251        throw new UnsupportedOperationException();
252    }
253
254    @Override
255    public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch,
256            Column mainColumn, Model model, Database database) {
257        throw new UnsupportedOperationException();
258    }
259
260    @Override
261    public boolean supportsUpdateFrom() {
262        throw new UnsupportedOperationException();
263    }
264
265    @Override
266    public boolean doesUpdateFromRepeatSelf() {
267        throw new UnsupportedOperationException();
268    }
269
270    @Override
271    public String getSecurityCheckSql(String idColumnName) {
272        return String.format("NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName);
273    }
274
275    @Override
276    public String getInTreeSql(String idColumnName, String id) {
277        return String.format("NX_IN_TREE(%s, ?) = 1", idColumnName);
278    }
279
280}