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