001/* 002 * (C) Copyright 2006-2017 Nuxeo (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.Statement; 029import java.sql.Types; 030import java.util.ArrayList; 031import java.util.Arrays; 032import java.util.Collections; 033import java.util.HashMap; 034import java.util.Iterator; 035import java.util.LinkedList; 036import java.util.List; 037import java.util.Map; 038 039import org.apache.commons.logging.Log; 040import org.apache.commons.logging.LogFactory; 041import org.nuxeo.ecm.core.NXCore; 042import org.nuxeo.ecm.core.api.NuxeoException; 043import org.nuxeo.ecm.core.api.security.SecurityConstants; 044import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer; 045import org.nuxeo.ecm.core.storage.FulltextQueryAnalyzer.FulltextQuery; 046import org.nuxeo.ecm.core.storage.sql.ColumnType; 047import org.nuxeo.ecm.core.storage.sql.Model; 048import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 049import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 050import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 051import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 052import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 053import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 054 055/** 056 * Microsoft SQL Server-specific dialect. 057 * 058 * @author Florent Guillaume 059 */ 060public class DialectSQLServer extends Dialect { 061 062 private static final Log log = LogFactory.getLog(DialectSQLServer.class); 063 064 private static final String DEFAULT_FULLTEXT_ANALYZER = "english"; 065 066 private static final String DEFAULT_FULLTEXT_CATALOG = "nuxeo"; 067 068 /** 069 * Column containing an IDENTITY used to create a clustered index. 070 */ 071 public static final String CLUSTER_INDEX_COL = "_oid"; 072 073 protected final String fulltextAnalyzer; 074 075 protected final String fulltextCatalog; 076 077 private static final String DEFAULT_USERS_SEPARATOR = "|"; 078 079 protected final String usersSeparator; 080 081 protected final DialectIdType idType; 082 083 protected String idSequenceName; 084 085 protected boolean pathOptimizationsEnabled; 086 087 /** 9 = SQL Server 2005, 10 = SQL Server 2008, 11 = SQL Server 2012 / Azure */ 088 protected int majorVersion; 089 090 // http://msdn.microsoft.com/en-us/library/ms174396.aspx 091 /** 5 = Azure */ 092 protected int engineEdition; 093 094 protected boolean azure; 095 096 public DialectSQLServer(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 097 super(metadata, repositoryDescriptor); 098 try { 099 checkDatabaseConfiguration(metadata.getConnection()); 100 majorVersion = metadata.getDatabaseMajorVersion(); 101 engineEdition = getEngineEdition(metadata.getConnection()); 102 103 } catch (SQLException e) { 104 throw new NuxeoException(e); 105 } 106 if (engineEdition == 5) { // 5 = SQL Azure 107 azure = true; 108 fulltextDisabled = true; 109 fulltextSearchDisabled = true; 110 if (repositoryDescriptor != null) { 111 repositoryDescriptor.setFulltextDisabled(true); 112 } 113 } 114 fulltextAnalyzer = repositoryDescriptor == null ? null 115 : repositoryDescriptor.getFulltextAnalyzer() == null ? DEFAULT_FULLTEXT_ANALYZER 116 : repositoryDescriptor.getFulltextAnalyzer(); 117 fulltextCatalog = repositoryDescriptor == null ? null 118 : repositoryDescriptor.getFulltextCatalog() == null ? DEFAULT_FULLTEXT_CATALOG 119 : repositoryDescriptor.getFulltextCatalog(); 120 usersSeparator = repositoryDescriptor == null ? null 121 : repositoryDescriptor.usersSeparatorKey == null ? DEFAULT_USERS_SEPARATOR 122 : repositoryDescriptor.usersSeparatorKey; 123 pathOptimizationsEnabled = repositoryDescriptor != null && repositoryDescriptor.getPathOptimizationsEnabled(); 124 String idt = repositoryDescriptor == null ? null : repositoryDescriptor.idType; 125 if (idt == null || "".equals(idt) || "varchar".equalsIgnoreCase(idt)) { 126 idType = DialectIdType.VARCHAR; 127 } else if (idt.toLowerCase().startsWith("sequence")) { 128 idType = DialectIdType.SEQUENCE; 129 if (idt.toLowerCase().startsWith("sequence:")) { 130 String[] split = idt.split(":"); 131 idSequenceName = split[1]; 132 } else { 133 idSequenceName = "hierarchy_seq"; 134 } 135 } else { 136 throw new NuxeoException("Unknown id type: '" + idt + "'"); 137 } 138 139 } 140 141 @Override 142 public boolean supportsPaging() { 143 // available since SQL Server 2012 144 return (majorVersion >= 11); 145 } 146 147 @Override 148 public String addPagingClause(String sql, long limit, long offset) { 149 if (!sql.contains("ORDER")) { 150 // Order is required to use the offset operation 151 sql += " ORDER BY 1"; 152 } 153 return sql + String.format(" OFFSET %d ROWS FETCH NEXT %d ROWS ONLY", offset, limit); 154 } 155 156 protected int getEngineEdition(Connection connection) throws SQLException { 157 try (Statement st = connection.createStatement()) { 158 ResultSet rs = st.executeQuery("SELECT CONVERT(NVARCHAR(100), SERVERPROPERTY('EngineEdition'))"); 159 rs.next(); 160 return rs.getInt(1); 161 } 162 } 163 164 protected void checkDatabaseConfiguration(Connection connection) throws SQLException { 165 try (Statement stmt = connection.createStatement()) { 166 String sql = "SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = db_name()"; 167 if (log.isTraceEnabled()) { 168 log.trace("SQL: " + sql); 169 } 170 ResultSet rs = stmt.executeQuery(sql); 171 if (!rs.next()) { 172 throw new SQLException("Cannot detect whether READ_COMMITTED_SNAPSHOT is on"); 173 } 174 int on = rs.getInt(1); 175 if (on != 1) { 176 throw new SQLException("Incorrect database configuration, you must enable READ_COMMITTED_SNAPSHOT"); 177 } 178 rs.close(); 179 } 180 } 181 182 @Override 183 public char openQuote() { 184 return '['; 185 } 186 187 @Override 188 public char closeQuote() { 189 return ']'; 190 } 191 192 @Override 193 public String getNoColumnsInsertString(Column idColumn) { 194 return "DEFAULT VALUES"; 195 } 196 197 @Override 198 public String getNullColumnString() { 199 return " NULL"; 200 } 201 202 @Override 203 public boolean qualifyIndexName() { 204 return false; 205 } 206 207 @Override 208 public String getAddColumnString() { 209 return "ADD"; 210 } 211 212 @Override 213 public JDBCInfo getJDBCTypeAndString(ColumnType type) { 214 switch (type.spec) { 215 case STRING: 216 if (type.isUnconstrained()) { 217 return jdbcInfo("NVARCHAR(4000)", Types.VARCHAR); 218 } else if (type.isClob() || type.length > 4000) { 219 return jdbcInfo("NVARCHAR(MAX)", Types.CLOB); 220 } else { 221 return jdbcInfo("NVARCHAR(%d)", type.length, Types.VARCHAR); 222 } 223 case BOOLEAN: 224 return jdbcInfo("BIT", Types.BIT); 225 case LONG: 226 return jdbcInfo("BIGINT", Types.BIGINT); 227 case DOUBLE: 228 return jdbcInfo("DOUBLE PRECISION", Types.DOUBLE); 229 case TIMESTAMP: 230 return jdbcInfo("DATETIME2(3)", Types.TIMESTAMP); 231 case BLOBID: 232 return jdbcInfo("NVARCHAR(250)", Types.VARCHAR); 233 // ----- 234 case NODEID: 235 case NODEIDFK: 236 case NODEIDFKNP: 237 case NODEIDFKMUL: 238 case NODEIDFKNULL: 239 case NODEIDPK: 240 case NODEVAL: 241 switch (idType) { 242 case VARCHAR: 243 return jdbcInfo("NVARCHAR(36)", Types.VARCHAR); 244 case SEQUENCE: 245 return jdbcInfo("BIGINT", Types.BIGINT); 246 } 247 case SYSNAME: 248 case SYSNAMEARRAY: 249 return jdbcInfo("NVARCHAR(256)", Types.VARCHAR); 250 case TINYINT: 251 return jdbcInfo("TINYINT", Types.TINYINT); 252 case INTEGER: 253 return jdbcInfo("INT", Types.INTEGER); 254 case AUTOINC: 255 return jdbcInfo("INT IDENTITY", Types.INTEGER); 256 case FTINDEXED: 257 throw new AssertionError(type); 258 case FTSTORED: 259 return jdbcInfo("NVARCHAR(MAX)", Types.CLOB); 260 case CLUSTERNODE: 261 return jdbcInfo("SMALLINT", Types.SMALLINT); 262 case CLUSTERFRAGS: 263 return jdbcInfo("NVARCHAR(4000)", Types.VARCHAR); 264 } 265 throw new AssertionError(type); 266 } 267 268 @Override 269 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 270 // The jTDS JDBC driver uses VARCHAR / CLOB 271 // The Microsoft JDBC driver uses NVARCHAR / LONGNVARCHAR 272 if (expected == Types.VARCHAR && actual == Types.CLOB) { 273 return true; 274 } 275 if (expected == Types.VARCHAR && actual == Types.NVARCHAR) { 276 return true; 277 } 278 if (expected == Types.VARCHAR && actual == Types.LONGNVARCHAR) { 279 return true; 280 } 281 if (expected == Types.CLOB && actual == Types.VARCHAR) { 282 return true; 283 } 284 if (expected == Types.CLOB && actual == Types.NVARCHAR) { 285 return true; 286 } 287 if (expected == Types.CLOB && actual == Types.LONGNVARCHAR) { 288 return true; 289 } 290 if (expected == Types.BIGINT && actual == Types.INTEGER) { 291 return true; 292 } 293 if (expected == Types.INTEGER && actual == Types.BIGINT) { 294 return true; 295 } 296 return false; 297 } 298 299 @Override 300 public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException { 301 switch (idType) { 302 case VARCHAR: 303 ps.setObject(index, value, Types.VARCHAR); 304 break; 305 case SEQUENCE: 306 setIdLong(ps, index, value); 307 break; 308 } 309 } 310 311 @Override 312 public void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 313 throws SQLException { 314 switch (column.getJdbcType()) { 315 case Types.VARCHAR: 316 case Types.CLOB: 317 setToPreparedStatementString(ps, index, value, column); 318 return; 319 case Types.BIT: 320 ps.setBoolean(index, ((Boolean) value).booleanValue()); 321 return; 322 case Types.TINYINT: 323 case Types.SMALLINT: 324 case Types.INTEGER: 325 case Types.BIGINT: 326 ps.setLong(index, ((Number) value).longValue()); 327 return; 328 case Types.DOUBLE: 329 ps.setDouble(index, ((Double) value).doubleValue()); 330 return; 331 case Types.TIMESTAMP: 332 setToPreparedStatementTimestamp(ps, index, value, column); 333 return; 334 default: 335 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 336 } 337 } 338 339 @Override 340 @SuppressWarnings("boxing") 341 public Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException { 342 switch (column.getJdbcType()) { 343 case Types.VARCHAR: 344 case Types.CLOB: 345 return getFromResultSetString(rs, index, column); 346 case Types.BIT: 347 return rs.getBoolean(index); 348 case Types.TINYINT: 349 case Types.INTEGER: 350 case Types.BIGINT: 351 return rs.getLong(index); 352 case Types.DOUBLE: 353 return rs.getDouble(index); 354 case Types.TIMESTAMP: 355 return getFromResultSetTimestamp(rs, index, column); 356 } 357 throw new SQLException("Unhandled JDBC type: " + column.getJdbcType()); 358 } 359 360 @Override 361 protected int getMaxNameSize() { 362 return 128; 363 } 364 365 @Override 366 public boolean getMaterializeFulltextSyntheticColumn() { 367 return false; 368 } 369 370 @Override 371 public int getFulltextIndexedColumns() { 372 return 2; 373 } 374 375 @Override 376 public boolean supportsMultipleFulltextIndexes() { 377 // With SQL Server, only one full-text index is allowed per table... 378 return false; 379 } 380 381 @Override 382 public String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, List<Column> columns, 383 Model model) { 384 StringBuilder buf = new StringBuilder(); 385 buf.append(String.format("CREATE FULLTEXT INDEX ON %s (", table.getQuotedName())); 386 Iterator<Column> it = columns.iterator(); 387 while (it.hasNext()) { 388 buf.append(String.format("%s LANGUAGE %s", it.next().getQuotedName(), getQuotedFulltextAnalyzer())); 389 if (it.hasNext()) { 390 buf.append(", "); 391 } 392 } 393 String fulltextUniqueIndex = "[fulltext_pk]"; 394 buf.append(String.format(") KEY INDEX %s ON [%s]", fulltextUniqueIndex, fulltextCatalog)); 395 return buf.toString(); 396 } 397 398 @Override 399 public String getDialectFulltextQuery(String query) { 400 query = query.replace("%", "*"); 401 FulltextQuery ft = FulltextQueryAnalyzer.analyzeFulltextQuery(query); 402 if (ft == null) { 403 return "DONTMATCHANYTHINGFOREMPTYQUERY"; 404 } 405 return FulltextQueryAnalyzer.translateFulltext(ft, "OR", "AND", "AND NOT", "\"", "\"", 406 Collections.<Character> emptySet(), "\"", "\"", false); 407 } 408 409 // SELECT ..., FTTBL.RANK / 1000.0 410 // FROM ... LEFT JOIN [fulltext] ON [fulltext].[id] = [hierarchy].[id] 411 // ........ LEFT JOIN CONTAINSTABLE([fulltext], *, ?, LANGUAGE 'english') 412 // .................. AS FTTBL 413 // .................. ON [fulltext].[id] = FTTBL.[KEY] 414 // WHERE ... AND FTTBL.[KEY] IS NOT NULL 415 // ORDER BY FTTBL.RANK DESC 416 @Override 417 public FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 418 Column mainColumn, Model model, Database database) { 419 // TODO multiple indexes 420 Table ft = database.getTable(Model.FULLTEXT_TABLE_NAME); 421 Column ftMain = ft.getColumn(Model.MAIN_KEY); 422 String nthSuffix = nthMatch == 1 ? "" : String.valueOf(nthMatch); 423 String tableAlias = "_nxfttbl" + nthSuffix; 424 FulltextMatchInfo info = new FulltextMatchInfo(); 425 // there are two left joins here 426 info.joins = new ArrayList<>(); 427 if (nthMatch == 1) { 428 // Need only one JOIN involving the fulltext table 429 info.joins.add(new Join(Join.LEFT, ft.getQuotedName(), null, null, ftMain.getFullQuotedName(), 430 mainColumn.getFullQuotedName())); 431 } 432 info.joins.add( 433 new Join(Join.LEFT, // 434 String.format("CONTAINSTABLE(%s, *, ?, LANGUAGE %s)", ft.getQuotedName(), 435 getQuotedFulltextAnalyzer()), 436 tableAlias, // alias 437 fulltextQuery, // param 438 ftMain.getFullQuotedName(), // on1 439 String.format("%s.[KEY]", tableAlias) // on2 440 )); 441 info.whereExpr = String.format("%s.[KEY] IS NOT NULL", tableAlias); 442 info.scoreExpr = String.format("(%s.RANK / 1000.0)", tableAlias); 443 info.scoreAlias = "_nxscore" + nthSuffix; 444 info.scoreCol = new Column(mainColumn.getTable(), null, ColumnType.DOUBLE, null); 445 return info; 446 } 447 448 protected String getQuotedFulltextAnalyzer() { 449 if (!Character.isDigit(fulltextAnalyzer.charAt(0))) { 450 return String.format("'%s'", fulltextAnalyzer); 451 } 452 return fulltextAnalyzer; 453 } 454 455 @Override 456 public String getLikeEscaping() { 457 return " ESCAPE '\\'"; 458 } 459 460 @Override 461 public boolean supportsCircularCascadeDeleteConstraints() { 462 // See http://support.microsoft.com/kb/321843 463 // Msg 1785 Introducing FOREIGN KEY constraint 464 // 'hierarchy_parentid_hierarchy_fk' on table 'hierarchy' may cause 465 // cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON 466 // UPDATE NO ACTION, or modify other FOREIGN KEY constraints. 467 // Instead we use a trigger "INSTEAD OF DELETE" to do the recursion. 468 return false; 469 } 470 471 @Override 472 public boolean supportsUpdateFrom() { 473 return true; 474 } 475 476 @Override 477 public boolean doesUpdateFromRepeatSelf() { 478 return true; 479 } 480 481 @Override 482 public boolean needsAliasForDerivedTable() { 483 return true; 484 } 485 486 @Override 487 public boolean needsOriginalColumnInGroupBy() { 488 // http://msdn.microsoft.com/en-us/library/ms177673.aspx 489 // A column alias that is defined in the SELECT list cannot be used to 490 // specify a grouping column. 491 return true; 492 } 493 494 @Override 495 public String getSecurityCheckSql(String idColumnName) { 496 return String.format("dbo.NX_ACCESS_ALLOWED(%s, ?, ?) = 1", idColumnName); 497 } 498 499 @Override 500 public boolean supportsFastDescendants() { 501 return pathOptimizationsEnabled; 502 } 503 504 @Override 505 public String getInTreeSql(String idColumnName, String id) { 506 String idParam; 507 switch (idType) { 508 case VARCHAR: 509 idParam = "?"; 510 break; 511 case SEQUENCE: 512 // check that it's really an integer 513 if (id != null && !org.apache.commons.lang.StringUtils.isNumeric(id)) { 514 return null; 515 } 516 idParam = "CONVERT(BIGINT, ?)"; 517 break; 518 default: 519 throw new AssertionError("Unknown id type: " + idType); 520 } 521 522 if (pathOptimizationsEnabled) { 523 return String.format("EXISTS(SELECT 1 FROM ancestors WHERE hierarchy_id = %s AND ancestor = %s)", 524 idColumnName, idParam); 525 } 526 return String.format("%s IN (SELECT * FROM dbo.nx_children(%s))", idColumnName, idParam); 527 } 528 529 @Override 530 public String getSQLStatementsFilename() { 531 return "nuxeovcs/sqlserver.sql.txt"; 532 } 533 534 @Override 535 public String getTestSQLStatementsFilename() { 536 return "nuxeovcs/sqlserver.test.sql.txt"; 537 } 538 539 @Override 540 public Map<String, Serializable> getSQLStatementsProperties(Model model, Database database) { 541 Map<String, Serializable> properties = new HashMap<>(); 542 switch (idType) { 543 case VARCHAR: 544 properties.put("idType", "NVARCHAR(36)"); 545 properties.put("idTypeParam", "NVARCHAR"); 546 properties.put("idNotPresent", "'-'"); 547 properties.put("sequenceEnabled", Boolean.FALSE); 548 break; 549 case SEQUENCE: 550 properties.put("idType", "BIGINT"); 551 properties.put("idTypeParam", "BIGINT"); 552 properties.put("idNotPresent", "-1"); 553 properties.put("sequenceEnabled", Boolean.TRUE); 554 properties.put("idSequenceName", idSequenceName); 555 } 556 properties.put("lockEscalationDisabled", Boolean.valueOf(supportsLockEscalationDisable())); 557 properties.put("md5HashString", getMd5HashString()); 558 properties.put("reseedAclrModified", azure ? "" : "DBCC CHECKIDENT('aclr_modified', RESEED, 0);"); 559 properties.put("fulltextEnabled", Boolean.valueOf(!fulltextDisabled)); 560 properties.put("fulltextSearchEnabled", Boolean.valueOf(!fulltextSearchDisabled)); 561 properties.put("fulltextCatalog", fulltextCatalog); 562 properties.put("aclOptimizationsEnabled", Boolean.valueOf(aclOptimizationsEnabled)); 563 properties.put("pathOptimizationsEnabled", Boolean.valueOf(pathOptimizationsEnabled)); 564 properties.put("clusteringEnabled", Boolean.valueOf(clusteringEnabled)); 565 properties.put("proxiesEnabled", Boolean.valueOf(proxiesEnabled)); 566 properties.put("softDeleteEnabled", Boolean.valueOf(softDeleteEnabled)); 567 String[] permissions = NXCore.getSecurityService().getPermissionsToCheck(SecurityConstants.BROWSE); 568 List<String> permsList = new LinkedList<>(); 569 for (String perm : permissions) { 570 permsList.add(String.format(" SELECT '%s' ", perm)); 571 } 572 properties.put("readPermissions", String.join(" UNION ALL ", permsList)); 573 properties.put("usersSeparator", getUsersSeparator()); 574 return properties; 575 } 576 577 protected String getMd5HashString() { 578 if (majorVersion <= 9) { 579 // this is an internal function and doesn't work on Azure 580 return "SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', @string)), 3, 32)"; 581 } else { 582 // this doesn't work on SQL Server 2005 583 return "SUBSTRING(CONVERT(VARCHAR(34), HashBytes('MD5', @string), 1), 3, 32)"; 584 } 585 } 586 587 protected boolean supportsLockEscalationDisable() { 588 // not supported on SQL Server 2005 589 return majorVersion > 9; 590 } 591 592 @Override 593 public boolean supportsReadAcl() { 594 return aclOptimizationsEnabled; 595 } 596 597 @Override 598 public String getPrepareUserReadAclsSql() { 599 return "EXEC nx_prepare_user_read_acls ?"; 600 } 601 602 @Override 603 public String getReadAclsCheckSql(String userIdCol) { 604 return String.format("%s = dbo.nx_md5(?)", userIdCol); 605 } 606 607 @Override 608 public String getUpdateReadAclsSql() { 609 return "EXEC dbo.nx_update_read_acls"; 610 } 611 612 @Override 613 public String getRebuildReadAclsSql() { 614 return "EXEC dbo.nx_rebuild_read_acls"; 615 } 616 617 @Override 618 public List<String> getStartupSqls(Model model, Database database) { 619 if (aclOptimizationsEnabled) { 620 log.info("Vacuuming tables used by optimized acls"); 621 return Collections.singletonList("EXEC nx_vacuum_read_acls"); 622 } 623 return Collections.emptyList(); 624 } 625 626 @Override 627 public boolean isClusteringSupported() { 628 return true; 629 } 630 631 @Override 632 public String getClusterInsertInvalidations() { 633 return "EXEC dbo.NX_CLUSTER_INVAL ?, ?, ?, ?"; 634 } 635 636 @Override 637 public String getClusterGetInvalidations() { 638 return "SELECT [id], [fragments], [kind] FROM [cluster_invals] WHERE [nodeid] = ?"; 639 } 640 641 @Override 642 public boolean isConcurrentUpdateException(Throwable t) { 643 while (t.getCause() != null) { 644 t = t.getCause(); 645 } 646 if (t instanceof SQLException) { 647 switch (((SQLException) t).getErrorCode()) { 648 case 547: // The INSERT statement conflicted with the FOREIGN KEY 649 // constraint ... 650 case 1205: // Transaction (Process ID ...) was deadlocked on ... 651 // resources with another process and has been chosen as 652 // the deadlock victim. Rerun the transaction 653 case 2627: // Violation of UNIQUE KEY constraint 654 // Violation of PRIMARY KEY constraint 655 return true; 656 } 657 } 658 return false; 659 } 660 661 @Override 662 public String getBlobLengthFunction() { 663 return "DATALENGTH"; 664 } 665 666 public String getUsersSeparator() { 667 if (usersSeparator == null) { 668 return DEFAULT_USERS_SEPARATOR; 669 } 670 return usersSeparator; 671 } 672 673 @Override 674 public Serializable getGeneratedId(Connection connection) throws SQLException { 675 if (idType != DialectIdType.SEQUENCE) { 676 return super.getGeneratedId(connection); 677 } 678 String sql = String.format("SELECT NEXT VALUE FOR [%s]", idSequenceName); 679 try (Statement s = connection.createStatement()) { 680 ResultSet rs = s.executeQuery(sql); 681 rs.next(); 682 return Long.valueOf(rs.getLong(1)); 683 } 684 } 685 686 /** 687 * Set transaction isolation level to snapshot 688 */ 689 @Override 690 public void performPostOpenStatements(Connection connection) throws SQLException { 691 try (Statement stmt = connection.createStatement()) { 692 stmt.execute("SET TRANSACTION ISOLATION LEVEL READ COMMITTED"); 693 } 694 } 695 696 @Override 697 public String getAncestorsIdsSql() { 698 return "SELECT id FROM dbo.NX_ANCESTORS(?)"; 699 } 700 701 @Override 702 public String getDateCast() { 703 if (majorVersion <= 9) { 704 // SQL Server 2005 doesn't have a DATE type. At all. Sigh. 705 // Style 112 is YYYYMMDD 706 return "CONVERT(DATETIME, CONVERT(VARCHAR, %s, 112), 112)"; 707 } 708 return super.getDateCast(); 709 } 710 711 @Override 712 public String castIdToVarchar(String expr) { 713 switch (idType) { 714 case VARCHAR: 715 return expr; 716 case SEQUENCE: 717 return "CONVERT(VARCHAR, " + expr + ")"; 718 default: 719 throw new AssertionError("Unknown id type: " + idType); 720 } 721 } 722 723 @Override 724 public DialectIdType getIdType() { 725 return idType; 726 } 727 728 @Override 729 public List<String> getIgnoredColumns(Table table) { 730 return Collections.singletonList(CLUSTER_INDEX_COL); 731 } 732 733 /** 734 * Tables created for directories don't need a clustered column automatically defined. 735 */ 736 protected boolean needsClusteredColumn(Table table) { 737 if (idType == DialectIdType.SEQUENCE) { 738 // good enough for a clustered index 739 // no need to add another column 740 return false; 741 } 742 for (Column col : table.getColumns()) { 743 if (col.getType().isId()) { 744 return true; 745 } 746 } 747 return false; 748 } 749 750 @Override 751 public String getCustomColumnDefinition(Table table) { 752 if (!needsClusteredColumn(table)) { 753 return null; 754 } 755 return String.format("[%s] BIGINT NOT NULL IDENTITY", CLUSTER_INDEX_COL); 756 } 757 758 @Override 759 public List<String> getCustomPostCreateSqls(Table table, Model model) { 760 if (!needsClusteredColumn(table)) { 761 return Collections.emptyList(); 762 } 763 String quotedIndexName = getIndexName(table.getKey(), Collections.singletonList(CLUSTER_INDEX_COL)); 764 String sql = String.format("CREATE UNIQUE CLUSTERED INDEX [%s] ON %s ([%s])", quotedIndexName, 765 table.getQuotedName(), CLUSTER_INDEX_COL); 766 return Collections.singletonList(sql); 767 } 768 769 @Override 770 public String getSoftDeleteSql() { 771 return "EXEC dbo.NX_DELETE ?, ?"; 772 } 773 774 @Override 775 public String getSoftDeleteCleanupSql() { 776 return "{?= call dbo.NX_DELETE_PURGE(?, ?)}"; 777 } 778 779 @Override 780 public List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, Connection connection, 781 JDBCLogger logger, Map<String, Serializable> properties) throws SQLException { 782 boolean compatCheck = ddlMode.contains(RepositoryDescriptor.DDL_MODE_COMPAT); 783 String procCreateLower = procCreate.toLowerCase(); 784 String procDrop; 785 if (procCreateLower.startsWith("create function ")) { 786 procDrop = "DROP FUNCTION " + procName; 787 } else if (procCreateLower.startsWith("create procedure ")) { 788 procDrop = "DROP PROCEDURE " + procName; 789 } else { 790 procDrop = "DROP TRIGGER " + procName; 791 } 792 if (compatCheck) { 793 procDrop = "IF OBJECT_ID('" + procName + "') IS NOT NULL " + procDrop; 794 return Arrays.asList(procDrop, procCreate); 795 } 796 try (Statement st = connection.createStatement()) { 797 String getBody = "SELECT OBJECT_DEFINITION(OBJECT_ID('" + procName + "'))"; 798 logger.log(getBody); 799 try (ResultSet rs = st.executeQuery(getBody)) { 800 rs.next(); 801 String body = rs.getString(1); 802 if (body == null) { 803 logger.log(" -> missing"); 804 return Collections.singletonList(procCreate); 805 } else if (normalizeString(procCreate).contains(normalizeString(body))) { 806 logger.log(" -> exists, unchanged"); 807 return Collections.emptyList(); 808 } else { 809 logger.log(" -> exists, old"); 810 return Arrays.asList(procDrop, procCreate); 811 } 812 } 813 } 814 } 815 816 protected static String normalizeString(String string) { 817 return string.replaceAll("[ \n\r\t]+", " ").trim(); 818 } 819 820 @Override 821 public String getSQLForDump(String sql) { 822 return sql + "\nGO"; 823 } 824 825}