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}