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