001/* 002 * (C) Copyright 2006-2014 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 */ 019 020package org.nuxeo.ecm.core.storage.sql.jdbc.dialect; 021 022import java.io.Serializable; 023import java.lang.reflect.Constructor; 024import java.lang.reflect.InvocationTargetException; 025import java.security.MessageDigest; 026import java.security.NoSuchAlgorithmException; 027import java.sql.Array; 028import java.sql.Connection; 029import java.sql.DatabaseMetaData; 030import java.sql.PreparedStatement; 031import java.sql.ResultSet; 032import java.sql.SQLException; 033import java.sql.Timestamp; 034import java.sql.Types; 035import java.util.ArrayList; 036import java.util.Calendar; 037import java.util.Collection; 038import java.util.Collections; 039import java.util.GregorianCalendar; 040import java.util.HashMap; 041import java.util.List; 042import java.util.Map; 043import java.util.UUID; 044import java.util.concurrent.atomic.AtomicLong; 045import java.util.regex.Pattern; 046 047import org.nuxeo.common.utils.StringUtils; 048import org.nuxeo.ecm.core.api.NuxeoException; 049import org.nuxeo.ecm.core.query.QueryParseException; 050import org.nuxeo.ecm.core.storage.FulltextDescriptor; 051import org.nuxeo.ecm.core.storage.sql.ColumnType; 052import org.nuxeo.ecm.core.storage.sql.Model; 053import org.nuxeo.ecm.core.storage.sql.RepositoryDescriptor; 054import org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger; 055import org.nuxeo.ecm.core.storage.sql.jdbc.db.Column; 056import org.nuxeo.ecm.core.storage.sql.jdbc.db.Database; 057import org.nuxeo.ecm.core.storage.sql.jdbc.db.Join; 058import org.nuxeo.ecm.core.storage.sql.jdbc.db.Table; 059import org.nuxeo.runtime.api.Framework; 060 061/** 062 * A Dialect encapsulates knowledge about database-specific behavior. 063 * 064 * @author Florent Guillaume 065 */ 066public abstract class Dialect { 067 068 // change to have deterministic pseudo-UUID generation for debugging 069 public static final boolean DEBUG_UUIDS = false; 070 071 // if true then debug UUIDs (above) are actual UUIDs, not short strings 072 public static final boolean DEBUG_REAL_UUIDS = false; 073 074 // for debug 075 private final AtomicLong temporaryIdCounter = new AtomicLong(0); 076 077 /** 078 * Property used to disable NULLS LAST usage when sorting DESC. This increase performance for some dialects because 079 * they can use an index for sorting when there are no NULL value. 080 * 081 * @Since 5.9 082 */ 083 public static final String NULLS_LAST_ON_DESC_PROP = "nuxeo.vcs.use-nulls-last-on-desc"; 084 085 /** 086 * Store the SQL for descending order 087 * 088 * @since 5.9 089 */ 090 protected String descending; 091 092 /** 093 * System property to override the dialect to use globally instead of the one auto-detected. It can be suffixed by 094 * "." and the database name (without spaces and as returned by the database itself) to override only for a specific 095 * database. 096 * 097 * @since 5.6 098 */ 099 public static final String DIALECT_CLASS = "nuxeo.vcs.dialect"; 100 101 public static final Map<String, Class<? extends Dialect>> DIALECTS = new HashMap<String, Class<? extends Dialect>>(); 102 static { 103 DIALECTS.put("H2", DialectH2.class); 104 DIALECTS.put("MySQL", DialectMySQL.class); 105 DIALECTS.put("Oracle", DialectOracle.class); 106 DIALECTS.put("PostgreSQL", DialectPostgreSQL.class); 107 DIALECTS.put("Microsoft SQL Server", DialectSQLServer.class); 108 DIALECTS.put("HSQL Database Engine", DialectHSQLDB.class); 109 DIALECTS.put("Apache Derby", DialectDerby.class); 110 DIALECTS.put("DB2", DialectDB2.class); 111 } 112 113 public static final class JDBCInfo { 114 public final String string; 115 116 public final int jdbcType; 117 118 public final String jdbcBaseTypeString; 119 120 public final int jdbcBaseType; 121 122 public JDBCInfo(String string, int jdbcType) { 123 this(string, jdbcType, null, 0); 124 } 125 126 public JDBCInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 127 this.string = string; 128 this.jdbcType = jdbcType; 129 this.jdbcBaseTypeString = jdbcBaseTypeString; 130 this.jdbcBaseType = jdbcBaseType; 131 } 132 } 133 134 /** Type of id when stored in the database. */ 135 public enum DialectIdType { 136 /** VARCHAR storing a UUID as a string. */ 137 VARCHAR, 138 /** Native UUID. */ 139 UUID, 140 /** Long from sequence generated by database. */ 141 SEQUENCE, 142 } 143 144 public static JDBCInfo jdbcInfo(String string, int jdbcType) { 145 return new JDBCInfo(string, jdbcType); 146 } 147 148 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType) { 149 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType); 150 } 151 152 public static JDBCInfo jdbcInfo(String string, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 153 return new JDBCInfo(string, jdbcType, jdbcBaseTypeString, jdbcBaseType); 154 } 155 156 public static JDBCInfo jdbcInfo(String string, int length, int jdbcType, String jdbcBaseTypeString, int jdbcBaseType) { 157 return new JDBCInfo(String.format(string, Integer.valueOf(length)), jdbcType, String.format(jdbcBaseTypeString, 158 Integer.valueOf(length)), jdbcBaseType); 159 } 160 161 protected final boolean storesUpperCaseIdentifiers; 162 163 protected boolean fulltextDisabled; 164 165 protected boolean fulltextSearchDisabled; 166 167 protected final boolean aclOptimizationsEnabled; 168 169 /** 170 * @since 5.7 171 */ 172 protected boolean clusteringEnabled; 173 174 /** 175 * @since 5.7 176 */ 177 protected boolean softDeleteEnabled; 178 179 protected boolean proxiesEnabled; 180 181 protected final int readAclMaxSize; 182 183 /** 184 * Creates a {@code Dialect} by connecting to the datasource to check what database is used. 185 */ 186 public static Dialect createDialect(Connection connection, RepositoryDescriptor repositoryDescriptor) { 187 DatabaseMetaData metadata; 188 String databaseName; 189 try { 190 metadata = connection.getMetaData(); 191 databaseName = metadata.getDatabaseProductName(); 192 } catch (SQLException e) { 193 throw new NuxeoException(e); 194 } 195 if (databaseName.contains("/")) { 196 // DB2/LINUX, DB2/DARWIN, etc. 197 databaseName = databaseName.substring(0, databaseName.indexOf('/')); 198 } 199 String dialectClassName = Framework.getProperty(DIALECT_CLASS); 200 if (dialectClassName == null) { 201 dialectClassName = Framework.getProperty(DIALECT_CLASS + '.' + databaseName.replace(" ", "")); 202 } 203 Class<? extends Dialect> dialectClass; 204 if (dialectClassName == null) { 205 dialectClass = DIALECTS.get(databaseName); 206 if (dialectClass == null) { 207 throw new NuxeoException("Unsupported database: " + databaseName); 208 } 209 } else { 210 Class<?> klass; 211 try { 212 ClassLoader cl = Thread.currentThread().getContextClassLoader(); 213 klass = cl.loadClass(dialectClassName); 214 } catch (ClassNotFoundException e) { 215 throw new NuxeoException(e); 216 } 217 if (!Dialect.class.isAssignableFrom(klass)) { 218 throw new NuxeoException("Not a Dialect: " + dialectClassName); 219 } 220 dialectClass = (Class<? extends Dialect>) klass; 221 } 222 Constructor<? extends Dialect> ctor; 223 try { 224 ctor = dialectClass.getConstructor(DatabaseMetaData.class, RepositoryDescriptor.class); 225 } catch (ReflectiveOperationException e) { 226 throw new NuxeoException("Bad constructor signature for: " + dialectClassName, e); 227 } 228 Dialect dialect; 229 try { 230 dialect = ctor.newInstance(metadata, repositoryDescriptor); 231 } catch (InvocationTargetException e) { 232 Throwable t = e.getTargetException(); 233 if (t instanceof NuxeoException) { 234 throw (NuxeoException) t; 235 } else { 236 throw new NuxeoException(t); 237 } 238 } catch (ReflectiveOperationException e) { 239 throw new NuxeoException("Cannot construct dialect: " + dialectClassName, e); 240 } 241 return dialect; 242 } 243 244 public Dialect(DatabaseMetaData metadata, RepositoryDescriptor repositoryDescriptor) { 245 try { 246 storesUpperCaseIdentifiers = metadata.storesUpperCaseIdentifiers(); 247 } catch (SQLException e) { 248 throw new NuxeoException(e); 249 } 250 if (repositoryDescriptor == null) { 251 fulltextDisabled = true; 252 fulltextSearchDisabled = true; 253 aclOptimizationsEnabled = false; 254 readAclMaxSize = 0; 255 clusteringEnabled = false; 256 softDeleteEnabled = false; 257 proxiesEnabled = true; 258 } else { 259 FulltextDescriptor fulltextDescriptor = repositoryDescriptor.getFulltextDescriptor(); 260 fulltextDisabled = fulltextDescriptor.getFulltextDisabled(); 261 fulltextSearchDisabled = fulltextDescriptor.getFulltextSearchDisabled(); 262 aclOptimizationsEnabled = repositoryDescriptor.getAclOptimizationsEnabled(); 263 readAclMaxSize = repositoryDescriptor.getReadAclMaxSize(); 264 clusteringEnabled = repositoryDescriptor.getClusteringEnabled(); 265 softDeleteEnabled = repositoryDescriptor.getSoftDeleteEnabled(); 266 proxiesEnabled = repositoryDescriptor.getProxiesEnabled(); 267 } 268 } 269 270 /** 271 * Gets the schema to use to query metadata about existing tables. 272 */ 273 public String getConnectionSchema(Connection connection) throws SQLException { 274 return null; 275 } 276 277 /** 278 * Gets the JDBC type and string from Nuxeo's type abstraction. 279 */ 280 public abstract JDBCInfo getJDBCTypeAndString(ColumnType type); 281 282 /** 283 * Check mismatches between expected and actual JDBC types read from database introspection. 284 */ 285 public boolean isAllowedConversion(int expected, int actual, String actualName, int actualSize) { 286 return false; 287 } 288 289 /** 290 * Gets a generated id if so configured, otherwise returns null. 291 */ 292 public Serializable getGeneratedId(Connection connection) throws SQLException { 293 if (DEBUG_UUIDS) { 294 if (DEBUG_REAL_UUIDS) { 295 return String.format("00000000-0000-0000-0000-%012x", 296 Long.valueOf(temporaryIdCounter.incrementAndGet())); 297 } else { 298 return "UUID_" + temporaryIdCounter.incrementAndGet(); 299 } 300 } else { 301 return UUID.randomUUID().toString(); 302 } 303 } 304 305 /** 306 * Sets a prepared statement value that is a Nuxeo main id (usually UUID). 307 * 308 * @param ps the prepared statement 309 * @param index the parameter index in the prepared statement 310 * @param value the value to set 311 */ 312 public void setId(PreparedStatement ps, int index, Serializable value) throws SQLException { 313 ps.setObject(index, value); 314 } 315 316 /** 317 * Sets a long id (sequence) from a value that may be a String or already a Long. 318 */ 319 public void setIdLong(PreparedStatement ps, int index, Serializable value) throws SQLException { 320 long l; 321 if (value instanceof String) { 322 try { 323 l = Long.parseLong((String) value); 324 } catch (NumberFormatException e) { 325 throw new SQLException("Invalid long id: " + value); 326 } 327 } else if (value instanceof Long) { 328 l = ((Long) value).longValue(); 329 } else { 330 throw new SQLException("Unsupported class for long id, class: " + value.getClass() + " value: " + value); 331 } 332 ps.setLong(index, l); 333 } 334 335 public abstract void setToPreparedStatement(PreparedStatement ps, int index, Serializable value, Column column) 336 throws SQLException; 337 338 public static final String ARRAY_SEP = "|"; 339 340 protected void setToPreparedStatementString(PreparedStatement ps, int index, Serializable value, Column column) 341 throws SQLException { 342 String v; 343 ColumnType type = column.getType(); 344 if (type == ColumnType.SYSNAMEARRAY) { 345 // implementation when arrays aren't supported 346 String[] strings = (String[]) value; 347 if (strings == null) { 348 v = null; 349 } else { 350 // use initial and final separator as terminator 351 StringBuilder buf = new StringBuilder(ARRAY_SEP); 352 for (String string : strings) { 353 buf.append(string); 354 buf.append(ARRAY_SEP); 355 } 356 v = buf.toString(); 357 } 358 } else { 359 v = (String) value; 360 } 361 ps.setString(index, v); 362 } 363 364 public void setToPreparedStatementTimestamp(PreparedStatement ps, int index, Serializable value, Column column) 365 throws SQLException { 366 Calendar cal = (Calendar) value; 367 Timestamp ts = cal == null ? null : new Timestamp(cal.getTimeInMillis()); 368 ps.setTimestamp(index, ts, cal); // cal passed for timezone 369 } 370 371 public Timestamp getTimestampFromCalendar(Calendar value) { 372 return new Timestamp(value.getTimeInMillis()); 373 } 374 375 public Timestamp[] getTimestampFromCalendar(Serializable[] value) { 376 if (value == null) { 377 return null; 378 } 379 Timestamp[] ts = new Timestamp[value.length]; 380 for (int i = 0; i < value.length; i++) { 381 ts[i] = getTimestampFromCalendar((Calendar) value[i]); 382 } 383 return ts; 384 } 385 386 public Calendar getCalendarFromTimestamp(Timestamp value) { 387 if (value == null) { 388 return null; 389 } 390 Calendar cal = new GregorianCalendar(); // XXX timezone 391 cal.setTimeInMillis(value.getTime()); 392 return cal; 393 } 394 395 public Calendar[] getCalendarFromTimestamp(Timestamp[] value) { 396 if (value == null) { 397 return null; 398 } 399 Calendar[] cal = new GregorianCalendar[value.length]; 400 for (int i = 0; i < value.length; i++) { 401 cal[i] = getCalendarFromTimestamp(value[i]); 402 } 403 return cal; 404 } 405 406 public abstract Serializable getFromResultSet(ResultSet rs, int index, Column column) throws SQLException; 407 408 protected Serializable getFromResultSetString(ResultSet rs, int index, Column column) throws SQLException { 409 String string = rs.getString(index); 410 if (string == null) { 411 return null; 412 } 413 ColumnType type = column.getType(); 414 if (type == ColumnType.SYSNAMEARRAY) { 415 // implementation when arrays aren't supported 416 // an initial separator is expected 417 if (string.startsWith(ARRAY_SEP)) { 418 string = string.substring(ARRAY_SEP.length()); 419 } 420 // the final separator is dropped as split does not return final 421 // empty strings 422 return string.split(Pattern.quote(ARRAY_SEP)); 423 } else { 424 return string; 425 } 426 } 427 428 protected Serializable getFromResultSetTimestamp(ResultSet rs, int index, Column column) throws SQLException { 429 Timestamp ts = rs.getTimestamp(index); 430 if (ts == null) { 431 return null; 432 } else { 433 Serializable cal = new GregorianCalendar(); // XXX timezone 434 ((Calendar) cal).setTimeInMillis(ts.getTime()); 435 return cal; 436 } 437 } 438 439 public boolean storesUpperCaseIdentifiers() { 440 return storesUpperCaseIdentifiers; 441 } 442 443 public char openQuote() { 444 return '"'; 445 } 446 447 public char closeQuote() { 448 return '"'; 449 } 450 451 public String toBooleanValueString(boolean bool) { 452 return bool ? "1" : "0"; 453 } 454 455 protected int getMaxNameSize() { 456 return 999; 457 } 458 459 protected int getMaxIndexNameSize() { 460 return getMaxNameSize(); 461 } 462 463 /* 464 * Needs to be deterministic and not change between Nuxeo EP releases. Turns "field_with_too_many_chars_for_oracle" 465 * into "FIELD_WITH_TOO_MANY_C_58557BA3". 466 */ 467 protected String makeName(String name, int maxNameSize) { 468 if (name.length() > maxNameSize) { 469 MessageDigest digest; 470 try { 471 digest = MessageDigest.getInstance("MD5"); 472 } catch (NoSuchAlgorithmException e) { 473 throw new RuntimeException(e.toString(), e); 474 } 475 byte[] bytes = name.getBytes(); 476 digest.update(bytes, 0, bytes.length); 477 name = name.substring(0, maxNameSize - 1 - 8); 478 name += '_' + toHexString(digest.digest()).substring(0, 8); 479 } 480 name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase(); 481 name = name.replace(':', '_'); 482 return name; 483 } 484 485 /* 486 * Used for one-time names (IDX, FK, PK), ok if algorithm changes. If too long, keeps 4 chars of the prefix and the 487 * full suffix. 488 */ 489 protected String makeName(String prefix, String string, String suffix, int maxNameSize) { 490 String name = prefix + string + suffix; 491 if (name.length() > maxNameSize) { 492 MessageDigest digest; 493 try { 494 digest = MessageDigest.getInstance("MD5"); 495 } catch (NoSuchAlgorithmException e) { 496 throw new RuntimeException(e.toString(), e); 497 } 498 byte[] bytes = (prefix + string).getBytes(); 499 digest.update(bytes, 0, bytes.length); 500 name = prefix.substring(0, 4); 501 name += '_' + toHexString(digest.digest()).substring(0, 8); 502 name += suffix; 503 } 504 name = storesUpperCaseIdentifiers() ? name.toUpperCase() : name.toLowerCase(); 505 name = name.replace(':', '_'); 506 return name; 507 } 508 509 protected static final char[] HEX_DIGITS = "0123456789ABCDEF".toCharArray(); 510 511 public static String toHexString(byte[] bytes) { 512 StringBuilder buf = new StringBuilder(2 * bytes.length); 513 for (byte b : bytes) { 514 buf.append(HEX_DIGITS[(0xF0 & b) >> 4]); 515 buf.append(HEX_DIGITS[0x0F & b]); 516 } 517 return buf.toString(); 518 } 519 520 public String getTableName(String name) { 521 return makeName(name, getMaxNameSize()); 522 } 523 524 public String getColumnName(String name) { 525 return makeName(name, getMaxNameSize()); 526 } 527 528 public String getPrimaryKeyConstraintName(String tableName) { 529 return makeName(tableName, "", "_PK", getMaxNameSize()); 530 } 531 532 public String getForeignKeyConstraintName(String tableName, String foreignColumnName, String foreignTableName) { 533 return makeName(tableName + '_', foreignColumnName + '_' + foreignTableName, "_FK", getMaxNameSize()); 534 } 535 536 public String getIndexName(String tableName, List<String> columnNames) { 537 return makeName(qualifyIndexName() ? tableName + '_' : "", StringUtils.join(columnNames, '_'), "_IDX", 538 getMaxIndexNameSize()); 539 } 540 541 /** 542 * Gets a CREATE INDEX statement for an index. 543 * 544 * @param indexName the index name (for fulltext) 545 * @param indexType the index type 546 * @param table the table 547 * @param columns the columns to index 548 * @param model the model 549 */ 550 public String getCreateIndexSql(String indexName, Table.IndexType indexType, Table table, List<Column> columns, 551 Model model) { 552 List<String> qcols = new ArrayList<String>(columns.size()); 553 List<String> pcols = new ArrayList<String>(columns.size()); 554 for (Column col : columns) { 555 qcols.add(col.getQuotedName()); 556 pcols.add(col.getPhysicalName()); 557 } 558 String quotedIndexName = openQuote() + getIndexName(table.getKey(), pcols) + closeQuote(); 559 if (indexType == Table.IndexType.FULLTEXT) { 560 return getCreateFulltextIndexSql(indexName, quotedIndexName, table, columns, model); 561 } else { 562 return String.format("CREATE INDEX %s ON %s (%s)", quotedIndexName, table.getQuotedName(), 563 StringUtils.join(qcols, ", ")); 564 } 565 } 566 567 /** 568 * Specifies what columns of the fulltext table have to be indexed. 569 * 570 * @return 0 for none, 1 for the synthetic one, 2 for the individual ones 571 */ 572 public abstract int getFulltextIndexedColumns(); 573 574 /** 575 * SQL Server supports only one fulltext index. 576 */ 577 public boolean supportsMultipleFulltextIndexes() { 578 return true; 579 } 580 581 /** 582 * Does the fulltext synthetic column have to be materialized. 583 */ 584 public abstract boolean getMaterializeFulltextSyntheticColumn(); 585 586 /** 587 * Gets a CREATE INDEX statement for a fulltext index. 588 */ 589 public abstract String getCreateFulltextIndexSql(String indexName, String quotedIndexName, Table table, 590 List<Column> columns, Model model); 591 592 /** 593 * Get the dialect-specific version of a fulltext query. 594 * 595 * @param query the CMIS-syntax-based fulltext query string 596 * @return the dialect native fulltext query string 597 */ 598 public abstract String getDialectFulltextQuery(String query); 599 600 /** 601 * Information needed to express fulltext search with scoring. 602 */ 603 public static class FulltextMatchInfo { 604 605 public List<Join> joins; 606 607 public String whereExpr; 608 609 public String whereExprParam; 610 611 public String scoreExpr; 612 613 public String scoreExprParam; 614 615 public String scoreAlias; 616 617 public Column scoreCol; 618 } 619 620 /** 621 * Gets the SQL information needed to do a a fulltext match, either with a direct expression in the WHERE clause, or 622 * using a join with an additional table. 623 */ 624 public abstract FulltextMatchInfo getFulltextScoredMatchInfo(String fulltextQuery, String indexName, int nthMatch, 625 Column mainColumn, Model model, Database database); 626 627 /** 628 * Gets the SQL fragment to add after a LIKE match to specify the escaping character. 629 * 630 * @since 7.4 631 */ 632 public String getLikeEscaping() { 633 return null; 634 } 635 636 /** 637 * Gets the SQL fragment to match a mixin type. 638 */ 639 public String getMatchMixinType(Column mixinsColumn, String mixin, boolean positive, String[] returnParam) { 640 returnParam[0] = "%" + ARRAY_SEP + mixin + ARRAY_SEP + "%"; 641 return String.format("%s %s ?", mixinsColumn.getFullQuotedName(), positive ? "LIKE" : "NOT LIKE"); 642 } 643 644 /** 645 * Indicates if dialect supports paging 646 * 647 * @return true if the dialect supports paging 648 */ 649 public boolean supportsPaging() { 650 return false; 651 } 652 653 /** 654 * Returns the SQL query with a paging clause 655 * 656 * @since 5.7 (replacing getPagingClause) 657 */ 658 public String addPagingClause(String sql, long limit, long offset) { 659 throw new UnsupportedOperationException("paging is not supported"); 660 } 661 662 /** 663 * Gets the type of a fulltext column has known by JDBC. 664 * <p> 665 * This is used for setNull. 666 */ 667 public int getFulltextType() { 668 return Types.CLOB; 669 } 670 671 /** 672 * Gets the JDBC expression setting a free value for this column type. 673 * <p> 674 * Needed for columns that need an expression around the value being set, usually for conversion (this is the case 675 * for PostgreSQL fulltext {@code TSVECTOR} columns for instance). 676 * 677 * @param type the column type 678 * @return the expression containing a free variable 679 */ 680 public String getFreeVariableSetterForType(ColumnType type) { 681 return "?"; 682 } 683 684 public String getNoColumnsInsertString() { 685 return "VALUES ( )"; 686 } 687 688 public String getNullColumnString() { 689 return ""; 690 } 691 692 public String getTableTypeString(Table table) { 693 return ""; 694 } 695 696 public String getAddPrimaryKeyConstraintString(String constraintName) { 697 return String.format(" ADD CONSTRAINT %s PRIMARY KEY ", constraintName); 698 } 699 700 public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKeys, String referencedTable, 701 String[] primaryKeys, boolean referencesPrimaryKey) { 702 String sql = String.format(" ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s", constraintName, 703 StringUtils.join(foreignKeys, ", "), referencedTable); 704 if (!referencesPrimaryKey) { 705 sql += " (" + StringUtils.join(primaryKeys, ", ") + ')'; 706 } 707 return sql; 708 } 709 710 public boolean qualifyIndexName() { 711 return true; 712 } 713 714 public boolean supportsIfExistsBeforeTableName() { 715 return false; 716 } 717 718 public boolean supportsIfExistsAfterTableName() { 719 return false; 720 } 721 722 public String getCascadeDropConstraintsString() { 723 return ""; 724 } 725 726 public boolean supportsCircularCascadeDeleteConstraints() { 727 // false for MS SQL Server 728 return true; 729 } 730 731 public String getAddColumnString() { 732 return "ADD COLUMN"; 733 } 734 735 /** 736 * Does the dialect support UPDATE t SET ... FROM t, u WHERE ... ? 737 */ 738 public abstract boolean supportsUpdateFrom(); 739 740 /** 741 * When doing an UPDATE t SET ... FROM t, u WHERE ..., does the FROM clause need to repeate the updated table (t). 742 */ 743 public abstract boolean doesUpdateFromRepeatSelf(); 744 745 /** 746 * When doing a SELECT DISTINCT that uses a ORDER BY, do the keys along which we order have to be mentioned in the 747 * DISTINCT clause? 748 */ 749 public boolean needsOrderByKeysAfterDistinct() { 750 return true; 751 } 752 753 /** 754 * Whether a derived table (subselect in a FROM statement) needs an alias. 755 */ 756 public boolean needsAliasForDerivedTable() { 757 return false; 758 } 759 760 /** 761 * Whether a GROUP BY can only be used with the original column name and not an alias. 762 */ 763 public boolean needsOriginalColumnInGroupBy() { 764 return false; 765 } 766 767 /** 768 * Whether implicit Oracle joins (instead of explicit ANSI joins) are needed. 769 */ 770 public boolean needsOracleJoins() { 771 return false; 772 } 773 774 /** 775 * The dialect need an extra SQL statement to populate a user read acl cache before running the query. 776 * 777 * @since 5.5 778 */ 779 public boolean needsPrepareUserReadAcls() { 780 return supportsReadAcl(); 781 } 782 783 /** 784 * True if the dialect need an extra NULLS LAST on DESC sort. 785 * 786 * @since 5.9 787 */ 788 public boolean needsNullsLastOnDescSort() { 789 return false; 790 } 791 792 /** 793 * When using a CLOB field in an expression, is some casting required and with what pattern? 794 * <p> 795 * Needed for Derby and H2. 796 * 797 * @param inOrderBy {@code true} if the expression is for an ORDER BY column 798 * @return a pattern for String.format with one parameter for the column name and one for the width, or {@code null} 799 * if no cast is required 800 */ 801 public String getClobCast(boolean inOrderBy) { 802 return null; 803 } 804 805 /** 806 * Get the expression to use to cast a column to a DATE type. 807 * 808 * @return a pattern for String.format with one parameter for the column name 809 * @since 5.6 810 */ 811 public String getDateCast() { 812 return "CAST(%s AS DATE)"; 813 } 814 815 /** 816 * Casts an id column to a VARCHAR type. 817 * <p> 818 * Used for uuid/varchar joins. 819 * 820 * @return the casted expression 821 * @since 5.7 822 */ 823 public String castIdToVarchar(String expr) { 824 return expr; 825 } 826 827 /** 828 * Gets the type of id when stored in the database. 829 * 830 * @since 5.7 831 */ 832 public DialectIdType getIdType() { 833 return DialectIdType.VARCHAR; 834 } 835 836 /** 837 * Gets the expression to use to check security. 838 * 839 * @param idColumnName the quoted name of the id column to use 840 * @return an SQL expression with two parameters (principals and permissions) that is true if access is allowed 841 */ 842 public abstract String getSecurityCheckSql(String idColumnName); 843 844 /** 845 * Checks if the dialect supports an ancestors table. 846 */ 847 public boolean supportsAncestorsTable() { 848 return false; 849 } 850 851 /** 852 * Checks whether {@link #getInTreeSQL} is optimized for fast results (using an ancestors or descendants table). 853 * 854 * @since 7.10, 6.0-HF21 855 */ 856 public boolean supportsFastDescendants() { 857 return false; 858 } 859 860 /** 861 * Gets the expression to use to check tree membership. 862 * 863 * @param idColumnName the quoted name of the id column to use 864 * @param id the id, to check syntax with respect to specialized id column types 865 * @return an SQL expression with one parameters for the based id that is true if the document is under base id, or 866 * {@code null} if the query cannot match 867 */ 868 public abstract String getInTreeSql(String idColumnName, String id); 869 870 /** 871 * Does the dialect support passing ARRAY values (to stored procedures mostly). 872 * <p> 873 * If not, we'll simulate them using a string and a separator. 874 * 875 * @return true if ARRAY values are supported 876 */ 877 public boolean supportsArrays() { 878 return false; 879 } 880 881 /** 882 * Does a stored function returning an result set need to access it as a single array instead of iterating over a 883 * normal result set's rows. 884 * <p> 885 * Oracle needs this. 886 */ 887 public boolean supportsArraysReturnInsteadOfRows() { 888 return false; 889 } 890 891 /** 892 * Gets the array result as a converted array of Serializable. 893 * 894 * @since 5.9.3 895 */ 896 public Serializable[] getArrayResult(Array array) throws SQLException { 897 throw new UnsupportedOperationException(); 898 } 899 900 /** 901 * Checks if the dialect supports storing arrays of system names (for mixins for instance). 902 */ 903 public boolean supportsSysNameArray() { 904 return false; 905 } 906 907 /** 908 * Does the dialect support storing arrays in table columns. 909 * <p> 910 * 911 * @return true if ARRAY columns are supported 912 */ 913 public boolean supportsArrayColumns() { 914 return false; 915 } 916 917 /** 918 * Structured Array Subquery Abstract Class. 919 */ 920 public static abstract class ArraySubQuery { 921 protected Column arrayColumn; 922 923 protected String subQueryAlias; 924 925 public ArraySubQuery(Column arrayColumn, String subqueryAlias) { 926 this.arrayColumn = arrayColumn; 927 this.subQueryAlias = subqueryAlias; 928 } 929 930 public abstract Column getSubQueryIdColumn(); 931 932 public abstract Column getSubQueryValueColumn(); 933 934 public abstract String toSql(); 935 } 936 937 /** 938 * Gets the dialect-specific subquery for an array column. 939 */ 940 public ArraySubQuery getArraySubQuery(Column arrayColumn, String subQueryAlias) { 941 throw new QueryParseException("Array sub-query not supported"); 942 } 943 944 /** 945 * Get SQL Array Element Subscripted string. 946 */ 947 public String getArrayElementString(String arrayColumnName, int arrayElementIndex) { 948 throw new QueryParseException("Array element not supported"); 949 } 950 951 /** 952 * Gets the SQL string for an array column IN expression. 953 */ 954 public String getArrayInSql(Column arrayColumn, String cast, boolean positive, List<Serializable> params) { 955 throw new QueryParseException("Array IN not supported"); 956 } 957 958 /** 959 * Gets the SQL string for an array column LIKE expression. 960 */ 961 public String getArrayLikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 962 throw new QueryParseException("Array LIKE not supported"); 963 } 964 965 /** 966 * Gets the SQL string for an array column ILIKE expression. 967 */ 968 public String getArrayIlikeSql(Column arrayColumn, String refName, boolean positive, Table dataHierTable) { 969 throw new QueryParseException("Array ILIKE not supported"); 970 } 971 972 /** 973 * Factory method for creating Array objects, suitable for passing to {@link PreparedStatement#setArray}. 974 * <p> 975 * (An equivalent method is defined by JDBC4 on the {@link Connection} class.) 976 * 977 * @param type the SQL type of the elements 978 * @param elements the elements of the array 979 * @param connection the connection 980 * @return an Array holding the elements 981 */ 982 public Array createArrayOf(int type, Object[] elements, Connection connection) throws SQLException { 983 throw new SQLException("Not supported"); 984 } 985 986 /** 987 * Gets the name of the file containing the SQL statements. 988 */ 989 public abstract String getSQLStatementsFilename(); 990 991 public abstract String getTestSQLStatementsFilename(); 992 993 /** 994 * Gets the properties to use with the SQL statements. 995 */ 996 public abstract Map<String, Serializable> getSQLStatementsProperties(Model model, Database database); 997 998 /** 999 * Checks that clustering is supported. 1000 */ 1001 public boolean isClusteringSupported() { 1002 return false; 1003 } 1004 1005 /** 1006 * Does clustering fetch of invalidations ( {@link #getClusterGetInvalidations}) need a separate delete for them. 1007 */ 1008 public boolean isClusteringDeleteNeeded() { 1009 return false; 1010 } 1011 1012 /** 1013 * Gets the SQL to send an invalidation to the cluster. 1014 * 1015 * @return an SQL statement with parameters for: nodeId, id, fragments, kind 1016 */ 1017 public String getClusterInsertInvalidations() { 1018 return null; 1019 } 1020 1021 /** 1022 * Gets the SQL to query invalidations for this cluster node. 1023 * 1024 * @return an SQL statement returning a result set 1025 */ 1026 public String getClusterGetInvalidations() { 1027 return null; 1028 } 1029 1030 /** 1031 * Does the dialect support ILIKE operator 1032 */ 1033 public boolean supportsIlike() { 1034 return false; 1035 } 1036 1037 /** 1038 * Does the dialect support an optimized read security checks 1039 */ 1040 public boolean supportsReadAcl() { 1041 return false; 1042 } 1043 1044 /** 1045 * Does the dialect support SQL-99 WITH common table expressions. 1046 */ 1047 public boolean supportsWith() { 1048 return false; 1049 } 1050 1051 /** 1052 * Does the dialect have an empty string identical to NULL (Oracle). 1053 */ 1054 public boolean hasNullEmptyString() { 1055 return false; 1056 } 1057 1058 /** 1059 * Maximum number of values in a IN (?, ?, ...) statement. 1060 * <p> 1061 * Beyond this size we'll do the query in several chunks. 1062 * <p> 1063 * PostgreSQL is limited to 65535 values in a prepared statement. 1064 * <p> 1065 * Oracle is limited to 1000 expressions in a list (ORA-01795). 1066 */ 1067 public int getMaximumArgsForIn() { 1068 return 400; 1069 } 1070 1071 /** 1072 * Gets the statement to update the read acls 1073 */ 1074 public String getUpdateReadAclsSql() { 1075 return null; 1076 } 1077 1078 /** 1079 * Gets the statement to rebuild the wall read acls 1080 */ 1081 public String getRebuildReadAclsSql() { 1082 return null; 1083 } 1084 1085 /** 1086 * Gets the expression to check if access is allowed using read acls. The dialect must suppportsReadAcl. 1087 * 1088 * @param userIdCol the quoted name of the aclr_user_map user_id column to use 1089 * @return an SQL expression with one parameter (principals) that is true if access is allowed 1090 */ 1091 public String getReadAclsCheckSql(String userIdCol) { 1092 return null; 1093 } 1094 1095 /** 1096 * Gets the SQL expression to prepare the user read acls cache. This can be used to populate a table cache. 1097 * 1098 * @since 5.5 1099 * @return and SQL expression with one parameter (principals) 1100 */ 1101 public String getPrepareUserReadAclsSql() { 1102 return null; 1103 } 1104 1105 /** 1106 * Gets the sql statements to execute after the repository init (at startup). 1107 * <p> 1108 * Used for vacuum-like operations. 1109 * 1110 * @since 6.0-HF24, 7.10-HF01, 8.1 1111 */ 1112 public List<String> getStartupSqls(Model model, Database database) { 1113 return Collections.emptyList(); 1114 } 1115 1116 /** 1117 * Checks if an exception received means that a concurrent update was detected. 1118 * 1119 * @since 5.8 1120 */ 1121 public boolean isConcurrentUpdateException(Throwable t) { 1122 return false; 1123 } 1124 1125 /** 1126 * Let the dialect processes additional statements after tables creation and conditional statements. Can be used for 1127 * specific upgrade procedure. 1128 * 1129 * @param connection 1130 */ 1131 public void performAdditionalStatements(Connection connection) throws SQLException { 1132 } 1133 1134 /** 1135 * A query that, when executed, will make at least a round-trip to the server to check that the connection is alive. 1136 * <p> 1137 * The query should throw an error if the connection is dead. 1138 */ 1139 public String getValidationQuery() { 1140 return "SELECT 1"; 1141 } 1142 1143 /** 1144 * Gets the SQL function that returns the length of a blob, in bytes. 1145 */ 1146 public String getBlobLengthFunction() { 1147 // the SQL-standard function (PostgreSQL, MySQL) 1148 return "OCTET_LENGTH"; 1149 } 1150 1151 /** 1152 * Let the dialect perform additional statements just after the connection is opened. 1153 */ 1154 public void performPostOpenStatements(Connection connection) throws SQLException { 1155 } 1156 1157 /** 1158 * Gets additional SQL statements to execute after the CREATE TABLE when creating an identity column. 1159 * <p> 1160 * Oracle needs both a sequence and a trigger. 1161 */ 1162 public List<String> getPostCreateIdentityColumnSql(Column column) { 1163 return Collections.emptyList(); 1164 } 1165 1166 /** 1167 * Checks if an identity column is already defined as a primary key and does not need a separate index added. 1168 * <p> 1169 * MySQL defines the identity column directly as primary key. 1170 */ 1171 public boolean isIdentityAlreadyPrimary() { 1172 return false; 1173 } 1174 1175 /** 1176 * True if the dialect returns the generated key for the identity from the insert statement. 1177 * <p> 1178 * Oracle needs a separate call to CURRVAL. 1179 */ 1180 public boolean hasIdentityGeneratedKey() { 1181 return true; 1182 } 1183 1184 /** 1185 * Gets the SQL query to execute to retrieve the last generated identity key. 1186 * <p> 1187 * Oracle needs a separate call to CURRVAL. 1188 */ 1189 public String getIdentityGeneratedKeySql(Column column) { 1190 return null; 1191 } 1192 1193 /** 1194 * Gets the SQL query to get the ancestors of a set of ids. 1195 * 1196 * @return null if not available 1197 */ 1198 public String getAncestorsIdsSql() { 1199 return null; 1200 } 1201 1202 /** 1203 * Gets the SQL descending sort direction with option to sort nulls last. Use to unify database behavior. 1204 * 1205 * @return DESC or DESC NULLS LAST depending on dialects. 1206 */ 1207 public String getDescending() { 1208 if (descending == null) { 1209 if (needsNullsLastOnDescSort() 1210 && Boolean.parseBoolean(Framework.getProperty(NULLS_LAST_ON_DESC_PROP, "true"))) { 1211 descending = " DESC NULLS LAST"; 1212 } else { 1213 descending = " DESC"; 1214 } 1215 } 1216 return descending; 1217 } 1218 1219 /** 1220 * Columns ignored if we see them in existing tables. 1221 */ 1222 public List<String> getIgnoredColumns(Table table) { 1223 return Collections.emptyList(); 1224 } 1225 1226 /** 1227 * Additional column definitions for CREATE TABLE. 1228 */ 1229 public String getCustomColumnDefinition(Table table) { 1230 return null; 1231 } 1232 1233 /** 1234 * Additional things to execute after CREATE TABLE. 1235 */ 1236 public List<String> getCustomPostCreateSqls(Table table) { 1237 return Collections.emptyList(); 1238 } 1239 1240 /** 1241 * SQL to soft delete documents. SQL returned has free parameters for the array of ids and time. 1242 */ 1243 public String getSoftDeleteSql() { 1244 throw new UnsupportedOperationException("Soft deletes not supported"); 1245 } 1246 1247 /** 1248 * SQL to clean soft-delete documents. SQL returned has free parameters max and beforeTime. 1249 */ 1250 public String getSoftDeleteCleanupSql() { 1251 throw new UnsupportedOperationException("Soft deletes not supported"); 1252 } 1253 1254 /** 1255 * Return the SQL to get the columns fulltext fields 1256 * 1257 * @param columns 1258 * @since 5.9.3 1259 */ 1260 public String getBinaryFulltextSql(List<String> columns) { 1261 return "SELECT " + StringUtils.join(columns, ", ") + " FROM fulltext WHERE id=?"; 1262 } 1263 1264 /** 1265 * Checks if a given stored procedure exists and is identical to the passed creation SQL. 1266 * <p> 1267 * There are 3 cases to deal with, and actions to perform: 1268 * <ul> 1269 * <li>the stored procedure doesn't exist, and must be created (create the stored procedure); 1270 * <li>the stored procedure exists but is not up to date (drop the old stored procedure and re-create it); 1271 * <li>the stored procedure exists and is up to date (nothing to do). 1272 * </ul> 1273 * <p> 1274 * When there is nothing to do, {@code null} is returned. Otherwise the returned value is a list of SQL statements 1275 * to execute. Note that the SQL statements will include also INSERT statements to be executed to remember the 1276 * creation SQL itself. 1277 * 1278 * @param procName the stored procedure name 1279 * @param procCreate the creation SQL for the stored procedure 1280 * @param ddlMode the DDL mode 1281 * @param connection the connection 1282 * @param logger the logger 1283 * @param properties the statement execution properties 1284 * @return a list of SQL statements 1285 * @since 6.0-HF24, 7.10-HF01, 8.1 1286 */ 1287 public abstract List<String> checkStoredProcedure(String procName, String procCreate, String ddlMode, 1288 Connection connection, JDBCLogger logger, Map<String, Serializable> properties) throws SQLException; 1289 1290 /** 1291 * Returns the initial DDL statements to add to a DDL dump. 1292 * 1293 * @return a list of SQL statements, usually empty 1294 * @since 6.0-HF24, 7.10-HF01, 8.1 1295 */ 1296 public Collection<? extends String> getDumpStart() { 1297 return Collections.emptyList(); 1298 } 1299 1300 /** 1301 * Returns the final DDL statements to add to a DDL dump. 1302 * 1303 * @return a list of SQL statements, usually empty 1304 * @since 6.0-HF24, 7.10-HF01, 8.1 1305 */ 1306 public Collection<? extends String> getDumpStop() { 1307 return Collections.emptyList(); 1308 } 1309 1310 /** 1311 * Returns the SQL statement with proper terminator to use in a dump. 1312 * 1313 * @return the SQL statement 1314 * @since 6.0-HF24, 7.10-HF01, 8.1 1315 */ 1316 public String getSQLForDump(String sql) { 1317 return sql + ";"; 1318 } 1319 1320}