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