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