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 * Benoit Delbosc 019 */ 020 021package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 022 023import java.io.Serializable; 024import java.sql.Connection; 025import java.sql.DatabaseMetaData; 026import java.sql.PreparedStatement; 027import java.sql.ResultSet; 028import java.sql.SQLException; 029import java.sql.Types; 030import java.util.ArrayList; 031import java.util.Collections; 032import java.util.HashMap; 033import java.util.LinkedList; 034import java.util.List; 035import java.util.Map; 036 037import org.nuxeo.common.utils.StringUtils; 038import org.nuxeo.ecm.core.NXCore; 039import org.nuxeo.ecm.core.api.security.SecurityConstants; 040import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer; 041import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery; 042import org.nuxeo.ecm.core.storage.sql.ColumnType; 043import org.nuxeo.ecm.core.storage.sql.Model; 044import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 045import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 046import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 047import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 048import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 049import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 050 051/** 052 * H2-specific dialect. 053 * 054 * @author Florent Guillaume 055 */ 056public class DialectH2 extends Dialect { 057 058 protected static final String DEFAULT_USERS_SEPARATOR = ","; 059 060 private static final String DEFAULT_FULLTEXT_ANALYZER = "org.apache.lucene.analysis.standard.StandardAnalyzer"; 061 062 protected final String fulltextAnalyzer; 063 064 protected final String usersSeparator; 065 066 public DialectH2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 067 super(metadata, repositoryDescriptor); 068 fulltextAnalyzer = repositoryDescriptor == null ? null 069 : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER 070 : repositoryDescriptor.getFulltextAnalyzer(); 071 usersSeparator = repositoryDescriptor == null ? null 072 : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR 073 : repositoryDescriptor.usersSeparatorKey; 074 } 075 076 @Override 077 public boolean supportsIfExistsAfterTableName() { 078 return true; 079 } 080 081 @Override 082 public JDBCInfo getJDBCTypeAndString(ColumnType type) { 083 switch (type.spec) { 084 case STRING: 085 if (type.isUnconstrained()) { 086 return jdbcInfo("VARCHAR", Types.VARCHAR); 087 } else if (type.isClob()) { 088 return jdbcInfo("CLOB", Types.CLOB); 089 } else { 090 return jdbcInfo("VARCHAR(%d)", type.length, Types.VARCHAR); 091 } 092 case BOOLEAN: 093 return jdbcInfo("BOOLEAN", Types.BOOLEAN); 094 case LONG: 095 return jdbcInfo("BIGINT", Types.BIGINT); 096 case DOUBLE: 097 return jdbcInfo("DOUBLE", Types.DOUBLE); 098 case TIMESTAMP: 099 return jdbcInfo("TIMESTAMP", Types.TIMESTAMP); 100 case BLOBID: 101 return jdbcInfo("VARCHAR(250)", Types.VARCHAR); 102 // ----- 103 case NODEID: 104 case NODEIDFK: 105 case NODEIDFKNP: 106 case NODEIDFKMUL: 107 case NODEIDFKNULL: 108 case NODEIDPK: 109 case NODEVAL: 110 return jdbcInfo("VARCHAR(36)", Types.VARCHAR); 111 case SYSNAME: 112 case SYSNAMEARRAY: 113 return jdbcInfo("VARCHAR(250)", Types.VARCHAR); 114 case TINYINT: 115 return jdbcInfo("TINYINT", Types.TINYINT); 116 case INTEGER: 117 return jdbcInfo("INTEGER", Types.INTEGER); 118 case AUTOINC: 119 return jdbcInfo("INTEGER AUTO_INCREMENT", Types.INTEGER); 120 case FTINDEXED: 121 throw new AssertionError(type); 122 case FTSTORED: 123 return jdbcInfo("CLOB", Types.CLOB); 124 case CLUSTERNODE: 125 return jdbcInfo("INTEGER", Types.INTEGER); 126 case CLUSTERFRAGS: 127 return jdbcInfo("VARCHAR", Types.VARCHAR); 128 } 129 throw new AssertionError(type); 130 } 131 132 @Override 133 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 134 // CLOB vs VARCHAR compatibility 135 if (expected == Types.VARCHAR && actual == Types.CLOB) { 136 return true; 137 } 138 if (expected == Types.CLOB && actual == Types.VARCHAR) { 139 return true; 140 } 141 // INTEGER vs BIGINT compatibility 142 if (expected == Types.BIGINT && actual == Types.INTEGER) { 143 return true; 144 } 145 if (expected == Types.INTEGER && actual == Types.BIGINT) { 146 return true; 147 } 148 return false; 149 } 150 151 @Override 152 public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 153 throws SQLException { 154 switch (column.getJdbcType()) { 155 case Types.VARCHAR: 156 case Types.CLOB: 157 setToPreparedStatementString(ps, index, value, column); 158 return; 159 case Types.BOOLEAN: 160 ps.setBoolean(index, ((Boolean) value).booleanValue()); 161 return; 162 case Types.TINYINT: 163 case Types.INTEGER: 164 case Types.BIGINT: 165 ps.setLong(index, ((Number) value).longValue()); 166 return; 167 case Types.DOUBLE: 168 ps.setDouble(index, ((Double) value).doubleValue()); 169 return; 170 case Types.TIMESTAMP: 171 setToPreparedStatementTimestamp(ps, index, value, column); 172 return; 173 default: 174 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 175 } 176 } 177 178 @Override 179 @SuppressWarnings("boxing") 180 public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { 181 switch (column.getJdbcType()) { 182 case Types.VARCHAR: 183 case Types.CLOB: 184 return getFromResultSetString(rs, index, column); 185 case Types.BOOLEAN: 186 return rs.getBoolean(index); 187 case Types.TINYINT: 188 case Types.INTEGER: 189 case Types.BIGINT: 190 return rs.getLong(index); 191 case Types.DOUBLE: 192 return rs.getDouble(index); 193 case Types.TIMESTAMP: 194 return getFromResultSetTimestamp(rs, index, column); 195 } 196 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 197 } 198 199 @Override 200 public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, 201 List<Column> columns, Model model) { 202 List<String> columnNames = new ArrayList<String>(columns.size()); 203 for (Column col : columns) { 204 columnNames.add("'" + col.getPhysicalName() + "'"); 205 } 206 String fullIndexName = String.format("PUBLIC_%s_%s", table.getPhysicalName(), indexName); 207 return String.format("CALL NXFT_CREATE_INDEX('%s', 'PUBLIC', '%s', (%s), '%s')", fullIndexName, 208 table.getPhysicalName(), StringUtils.join(columnNames, ", "), fulltextAnalyzer); 209 } 210 211 @Override 212 public String getDialectFulltextQuery(String query) { 213 query = query.replace("%", "*"); 214 FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query); 215 if (ft == null) { 216 return "DONTMATCHANYTHINGFOREMPTYQUERY"; 217 } 218 return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "NOT", "\""); 219 } 220 221 // SELECT ..., 1 as nxscore 222 // FROM ... LEFT JOIN NXFT_SEARCH('default', ?) nxfttbl 223 // .................. ON hierarchy.id = nxfttbl.KEY 224 // WHERE ... AND nxfttbl.KEY IS NOT NULL 225 // ORDER BY nxscore DESC 226 @Override 227 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 228 Column mainColumn, Model model, Database database) { 229 String phftname = database.getTable(Model.FULLTEXT_TABLE_NAME).getPhysicalName(); 230 String fullIndexName = "PUBLIC_" + phftname + "_" + indexName; 231 String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); 232 String tableAlias = "_NXFTTBL" + nthSuffix; 233 String quotedTableAlias = openQuote() + tableAlias + closeQuote(); 234 FulltextMatchInfo info = new FulltextMatchInfo(); 235 info.joins = Collections.singletonList( // 236 new Join(Join.LEFT, // 237 String.format("NXFT_SEARCH('%s', ?)", fullIndexName), tableAlias, // alias 238 fulltextQuery, // param 239 String.format("%s.KEY", quotedTableAlias), // on1 240 mainColumn.getFullQuotedName() // on2 241 )); 242 info.whereExpr = String.format("%s.KEY IS NOT NULL", quotedTableAlias); 243 info.scoreExpr = "1"; 244 info.scoreAlias = "_NXSCORE" + nthSuffix; 245 info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); 246 return info; 247 } 248 249 @Override 250 public boolean getMaterializeFulltextSyntheticColumn() { 251 return false; 252 } 253 254 @Override 255 public int getFulltextIndexedColumns() { 256 return 2; 257 } 258 259 @Override 260 public boolean supportsUpdateFrom() { 261 return false; // check this, unused 262 } 263 264 @Override 265 public boolean doesUpdateFromRepeatSelf() { 266 return true; 267 } 268 269 @Override 270 public String getClobCast(boolean inOrderBy) { 271 if (!inOrderBy) { 272 return "CAST(%s AS VARCHAR)"; 273 } 274 return null; 275 } 276 277 @Override 278 public String getSecurityCheckSql(String idColumnName) { 279 return String.format("NX_ACCESS_ALLOWED(%s, ?, ?)", idColumnName); 280 } 281 282 @Override 283 public String getInTreeSql(String idColumnName, String id) { 284 return String.format("NX_IN_TREE(%s, ?)", idColumnName); 285 } 286 287 @Override 288 public boolean supportsArrays() { 289 return false; 290 } 291 292 @Override 293 public boolean isConcurrentUpdateException(Throwable t) { 294 while (t.getCause() != null) { 295 t = t.getCause(); 296 } 297 if (t instanceof SQLException) { 298 String sqlState = ((SQLException) t).getSQLState(); 299 if ("23503".equals(sqlState)) { 300 // Referential integrity violated child exists 301 return true; 302 } 303 if ("23505".equals(sqlState)) { 304 // Duplicate key 305 return true; 306 } 307 if ("23506".equals(sqlState)) { 308 // Referential integrity violated parent exists 309 return true; 310 } 311 if ("40001".equals(sqlState)) { 312 // Deadlock detected 313 return true; 314 } 315 if ("HYT00".equals(sqlState)) { 316 // Lock timeout 317 return true; 318 } 319 if ("90131".equals(sqlState)) { 320 // Concurrent update in table ...: another transaction has 321 // updated or deleted the same row 322 return true; 323 } 324 } 325 return false; 326 } 327 328 @Override 329 public String getSQLStatementsFilename() { 330 return "nuxeovcs/h2.sql.txt"; 331 } 332 333 @Override 334 public String getTestSQLStatementsFilename() { 335 return "nuxeovcs/h2.test.sql.txt"; 336 } 337 338 @Override 339 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 340 Map<String, Serializable> properties = new HashMap<String, Serializable>(); 341 properties.put("idType", "VARCHAR(36)"); 342 String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE); 343 List<String> permsList = new LinkedList<String>(); 344 for (String perm : permissions) { 345 permsList.add("('" + perm + "')"); 346 } 347 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled)); 348 properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 349 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 350 properties.put("readPermissions", StringUtils.join(permsList, ", ")); 351 properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions"); 352 properties.put("h2Fulltext", "org.nuxeo.ecm.core.storage.sql.db.H2Fulltext"); 353 properties.put("usersSeparator", getUsersSeparator()); 354 return properties; 355 } 356 357 @Override 358 public boolean isClusteringSupported() { 359 return true; 360 } 361 362 @Override 363 public String getClusterInsertInvalidations() { 364 return "CALL NX_CLUSTER_INVAL(?, ?, ?, ?)"; 365 } 366 367 @Override 368 public String getClusterGetInvalidations() { 369 return "SELECT * FROM NX_CLUSTER_GET_INVALS(?)"; 370 } 371 372 @Override 373 public boolean supportsPaging() { 374 return true; 375 } 376 377 @Override 378 public String addPagingClause(String sql, long limit, long offset) { 379 return sql + String.format(" LIMIT %d OFFSET %d", limit, offset); 380 } 381 382 public String getUsersSeparator() { 383 if (usersSeparator == null) { 384 return DEFAULT_USERS_SEPARATOR; 385 } 386 return usersSeparator; 387 } 388 389 @Override 390 public String getBlobLengthFunction() { 391 return "LENGTH"; 392 } 393 394 @Override 395 public String getAncestorsIdsSql() { 396 return "CALL NX_ANCESTORS(?)"; 397 } 398 399 @Override 400 public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, 401 JDBCLogger logger, Map<String, Serializable> properties) throws SQLException { 402 throw new UnsupportedOperationException(); 403 } 404 405}