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