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