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