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