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.HashMap; 030import java.util.LinkedList; 031import java.util.List; 032import java.util.Map; 033 034import org.nuxeo.ecm.core.api.NuxeoException; 035import org.nuxeo.ecm.core.api.security.SecurityConstants; 036import org.nuxeo.ecm.core.model.BaseSession; 037import org.nuxeo.ecm.core.model.BaseSession.VersionAclMode; 038import org.nuxeo.ecm.core.security.SecurityService; 039import org.nuxeo.ecm.core.storage.sql.ColumnType; 040import org.nuxeo.ecm.core.storage.sql.Model; 041import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 042import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 043import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 044import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 045import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 046import org.nuxeo.runtime.api.Framework; 047 048/** 049 * H2-specific dialect. 050 * 051 * @author Florent Guillaume 052 */ 053public class DialectH2 extends Dialect { 054 055 protected static final String DEFAULT_USERS_SEPARATOR = ","; 056 057 protected final String usersSeparator; 058 059 protected final boolean disableVersionACL; 060 061 protected final boolean disableReadVersionPermission; 062 063 public DialectH2(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 064 super(metadata, repositoryDescriptor); 065 if (!fulltextSearchDisabled) { 066 throw new NuxeoException("Fulltext search cannot be enabled with H2"); 067 } 068 usersSeparator = repositoryDescriptor == null ? null 069 : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR 070 : repositoryDescriptor.usersSeparatorKey; 071 disableVersionACL = VersionAclMode.getConfiguration() == VersionAclMode.DISABLED; 072 disableReadVersionPermission = BaseSession.isReadVersionPermissionDisabled(); 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 throw new UnsupportedOperationException(); 209 } 210 211 @Override 212 public String getDialectFulltextQuery(String query) { 213 throw new UnsupportedOperationException(); 214 } 215 216 @Override 217 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 218 Column mainColumn, Model model, Database database) { 219 throw new UnsupportedOperationException(); 220 } 221 222 @Override 223 public boolean getMaterializeFulltextSyntheticColumn() { 224 return false; 225 } 226 227 @Override 228 public int getFulltextIndexedColumns() { 229 return 0; 230 } 231 232 @Override 233 public boolean supportsUpdateFrom() { 234 return false; // check this, unused 235 } 236 237 @Override 238 public boolean doesUpdateFromRepeatSelf() { 239 return true; 240 } 241 242 @Override 243 public String getClobCast(boolean inOrderBy) { 244 if (!inOrderBy) { 245 return "CAST(%s AS VARCHAR)"; 246 } 247 return null; 248 } 249 250 @Override 251 public String getSecurityCheckSql(String idColumnName) { 252 return String.format("NX_ACCESS_ALLOWED2(%s, ?, ?, %s, %s)", idColumnName, disableVersionACL, 253 disableReadVersionPermission); 254 } 255 256 @Override 257 public String getInTreeSql(String idColumnName, String id) { 258 return String.format("NX_IN_TREE(%s, ?)", idColumnName); 259 } 260 261 @Override 262 public boolean supportsArrays() { 263 return false; 264 } 265 266 @Override 267 public String getUpsertSql(List<Column> columns, List<Serializable> values, List<Column> outColumns, 268 List<Serializable> outValues) { 269 Column keyColumn = columns.get(0); 270 Table table = keyColumn.getTable(); 271 StringBuilder sql = new StringBuilder(); 272 sql.append("MERGE INTO "); 273 sql.append(table.getQuotedName()); 274 sql.append(" KEY ("); 275 sql.append(keyColumn.getQuotedName()); 276 sql.append(") VALUES ("); 277 for (int i = 0; i < columns.size(); i++) { 278 if (i != 0) { 279 sql.append(", "); 280 } 281 sql.append("?"); 282 outColumns.add(columns.get(i)); 283 outValues.add(values.get(i)); 284 } 285 sql.append(")"); 286 return sql.toString(); 287 } 288 289 @Override 290 public boolean isConcurrentUpdateException(Throwable t) { 291 // recent versions of H2 throw a SQLException whose cause, 292 // an IllegalStateException, is not itself a SQLException 293 Throwable cause; 294 while ((cause = t.getCause()) != null && cause instanceof SQLException) { 295 t = cause; 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<>(); 341 properties.put("idType", "VARCHAR(36)"); 342 String[] permissions = Framework.getService(SecurityService.class) 343 .getPermissionsToCheck(SecurityConstants.BROWSE); 344 List<String> permsList = new LinkedList<>(); 345 for (String perm : permissions) { 346 permsList.add("('" + perm + "')"); 347 } 348 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 349 properties.put("readPermissions", String.join(", ", permsList)); 350 properties.put("h2Functions", "org.nuxeo.ecm.core.storage.sql.db.H2Functions"); 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}