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 @Override 257 public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 258 List<Serializable> outValues) { 259 throw new UnsupportedOperationException(); 260 } 261 262 private final String derbyFunctions = "org.nuxeo.ecm.core.storage.sql.db.DerbyFunctions"; 263 264 @Override 265 public String getSQLStatementsFilename() { 266 return "nuxeovcs/derby.sql.txt"; 267 } 268 269 @Override 270 public String getTestSQLStatementsFilename() { 271 return "nuxeovcs/derby.test.sql.txt"; 272 } 273 274 @Override 275 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 276 Map<String, Serializable> properties = new HashMap<String, Serializable>(); 277 properties.put("idType", "VARCHAR(36)"); 278 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled)); 279 properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 280 properties.put("derbyFunctions", derbyFunctions); 281 properties.put(SQLStatement.DIALECT_WITH_NO_SEMICOLON, Boolean.TRUE); 282 return properties; 283 } 284 285 @Override 286 public String getValidationQuery() { 287 return "VALUES 1"; 288 } 289 290 @Override 291 public boolean supportsPaging() { 292 return true; 293 } 294 295 @Override 296 public String addPagingClause(String sql, long limit, long offset) { 297 return sql + String.format(" OFFSET %d ROWS FETCH FIRST %d ROWS ONLY", offset, limit); // available from 10.5 298 } 299 300 @Override 301 public boolean isConcurrentUpdateException(Throwable t) { 302 for (; t != null; t = t.getCause()) { 303 if (t instanceof SQLException) { 304 String sqlState = ((SQLException) t).getSQLState(); 305 if ("23503".equals(sqlState)) { 306 // INSERT on table ... caused a violation of foreign key 307 // constraint ... for key ... 308 return true; 309 } 310 if ("40001".equals(sqlState)) { 311 // A lock could not be obtained due to a deadlock 312 return true; 313 } 314 } 315 } 316 return false; 317 } 318 319 @Override 320 public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, 321 JDBCLogger logger, Map<String, Serializable> properties) throws SQLException { 322 throw new UnsupportedOperationException(); 323 } 324 325}