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