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