001package io.ebean.config.dbplatform; 002 003import io.ebean.BackgroundExecutor; 004import io.ebean.Query; 005import io.ebean.annotation.PartitionMode; 006import io.ebean.annotation.PersistBatch; 007import io.ebean.annotation.Platform; 008import io.ebean.config.CustomDbTypeMapping; 009import io.ebean.config.PlatformConfig; 010import io.ebean.util.JdbcClose; 011import org.slf4j.Logger; 012import org.slf4j.LoggerFactory; 013 014import javax.persistence.PersistenceException; 015import javax.sql.DataSource; 016import java.sql.Connection; 017import java.sql.DatabaseMetaData; 018import java.sql.ResultSet; 019import java.sql.SQLException; 020import java.sql.Statement; 021import java.sql.Types; 022 023/** 024 * Database platform specific settings. 025 */ 026public class DatabasePlatform { 027 028 private static final Logger logger = LoggerFactory.getLogger(DatabasePlatform.class); 029 030 /** 031 * Behavior used when ending a query only transaction (at read committed isolation level). 032 */ 033 public enum OnQueryOnly { 034 035 /** 036 * Rollback the transaction. 037 */ 038 ROLLBACK, 039 040 /** 041 * Commit the transaction 042 */ 043 COMMIT 044 } 045 046 047 /** 048 * Set to true for MySql, no other jdbc drivers need this workaround. 049 */ 050 protected boolean useExtraTransactionOnIterateSecondaryQueries; 051 052 /** 053 * The behaviour used when ending a read only transaction at read committed isolation level. 054 */ 055 protected OnQueryOnly onQueryOnly = OnQueryOnly.COMMIT; 056 057 /** 058 * The open quote used by quoted identifiers. 059 */ 060 protected String openQuote = "\""; 061 062 /** 063 * The close quote used by quoted identifiers. 064 */ 065 protected String closeQuote = "\""; 066 067 /** 068 * When set to true all db column names and table names use quoted identifiers. 069 */ 070 protected boolean allQuotedIdentifiers; 071 072 /** 073 * For limit/offset, row_number etc limiting of SQL queries. 074 */ 075 protected SqlLimiter sqlLimiter = new LimitOffsetSqlLimiter(); 076 077 /** 078 * Limit/offset support for SqlQuery only. 079 */ 080 protected BasicSqlLimiter basicSqlLimiter = new BasicSqlLimitOffset(); 081 082 /** 083 * Mapping of JDBC to Database types. 084 */ 085 protected DbPlatformTypeMapping dbTypeMap = new DbPlatformTypeMapping(); 086 087 /** 088 * Default values for DB columns. 089 */ 090 protected DbDefaultValue dbDefaultValue = new DbDefaultValue(); 091 092 /** 093 * Set to true if the DB has native UUID type support. 094 */ 095 protected boolean nativeUuidType; 096 097 /** 098 * Defines DB identity/sequence features. 099 */ 100 protected DbIdentity dbIdentity = new DbIdentity(); 101 102 protected boolean sequenceBatchMode = true; 103 104 protected int sequenceBatchSize = 20; 105 106 /** 107 * The history support for this database platform. 108 */ 109 protected DbHistorySupport historySupport; 110 111 /** 112 * The JDBC type to map booleans to (by default). 113 */ 114 protected int booleanDbType = Types.BOOLEAN; 115 116 /** 117 * The JDBC type to map Blob to. 118 */ 119 protected int blobDbType = Types.BLOB; 120 121 /** 122 * The JDBC type to map Clob to. 123 */ 124 protected int clobDbType = Types.CLOB; 125 126 /** 127 * For Oracle treat empty strings as null. 128 */ 129 protected boolean treatEmptyStringsAsNull; 130 131 /** 132 * The database platform name. 133 */ 134 protected Platform platform = Platform.GENERIC; 135 136 protected String columnAliasPrefix = "c"; 137 138 protected String tableAliasPlaceHolder = "${ta}"; 139 140 /** 141 * Use a BackTick ` at the beginning and end of table or column names that you 142 * want to use quoted identifiers for. The backticks get converted to the 143 * appropriate characters in convertQuotedIdentifiers 144 */ 145 private static final char BACK_TICK = '`'; 146 147 /** 148 * The non-escaped like clause (to stop slash being escaped on some platforms). 149 * Used for the 'raw like' expression but not for startsWith, endsWith and contains expressions. 150 */ 151 protected String likeClauseRaw = "like ? escape''"; 152 153 /** 154 * Escaped like clause for startsWith, endsWith and contains. 155 */ 156 protected String likeClauseEscaped = "like ? escape'|'"; 157 158 /** 159 * Escape character used for startsWith, endsWith and contains. 160 */ 161 protected char likeEscapeChar = '|'; 162 163 /** 164 * Characters escaped for startsWith, endsWith and contains. 165 */ 166 protected char[] likeSpecialCharacters = { '%', '_', '|' }; 167 168 protected DbEncrypt dbEncrypt; 169 170 protected boolean idInExpandedForm; 171 172 protected boolean selectCountWithAlias; 173 174 /** 175 * If set then use the FORWARD ONLY hint when creating ResultSets for 176 * findIterate() and findVisit(). 177 */ 178 protected boolean forwardOnlyHintOnFindIterate; 179 180 /** 181 * If set then use the CONCUR_UPDATABLE hint when creating ResultSets. 182 * 183 * This is {@code false} for HANA 184 */ 185 protected boolean supportsResultSetConcurrencyModeUpdatable = true; 186 187 188 /** 189 * By default we use JDBC batch when cascading (except for SQL Server and HANA). 190 */ 191 protected PersistBatch persistBatchOnCascade = PersistBatch.ALL; 192 193 /** 194 * The maximum length of table names - used specifically when derived 195 * default table names for intersection tables. 196 */ 197 protected int maxTableNameLength = 60; 198 199 /** 200 * A value of 60 is a reasonable default for all databases except 201 * Oracle (limited to 30) and DB2 (limited to 18). 202 */ 203 protected int maxConstraintNameLength = 60; 204 205 protected boolean supportsNativeIlike; 206 207 protected SqlExceptionTranslator exceptionTranslator = new SqlCodeTranslator(); 208 209 /** 210 * Instantiates a new database platform. 211 */ 212 public DatabasePlatform() { 213 } 214 215 /** 216 * Translate the SQLException into a specific persistence exception if possible. 217 */ 218 public PersistenceException translate(String message, SQLException e) { 219 return exceptionTranslator.translate(message, e); 220 } 221 222 /** 223 * Configure the platform given the server configuration. 224 */ 225 public void configure(PlatformConfig config) { 226 this.sequenceBatchSize = config.getDatabaseSequenceBatchSize(); 227 configureIdType(config.getIdType()); 228 configure(config, config.isAllQuotedIdentifiers()); 229 } 230 231 /** 232 * Configure UUID Storage etc based on ServerConfig settings. 233 */ 234 protected void configure(PlatformConfig config, boolean allQuotedIdentifiers) { 235 this.allQuotedIdentifiers = allQuotedIdentifiers; 236 addGeoTypes(config.getGeometrySRID()); 237 configureIdType(config.getIdType()); 238 dbTypeMap.config(nativeUuidType, config.getDbUuid()); 239 for (CustomDbTypeMapping mapping : config.getCustomTypeMappings()) { 240 if (platformMatch(mapping.getPlatform())) { 241 dbTypeMap.put(mapping.getType(), parse(mapping.getColumnDefinition())); 242 } 243 } 244 } 245 246 protected void configureIdType(IdType idType) { 247 if (idType != null) { 248 this.dbIdentity.setIdType(idType); 249 } 250 } 251 252 protected void addGeoTypes(int srid) { 253 // default has no geo type support 254 } 255 256 private DbPlatformType parse(String columnDefinition) { 257 return DbPlatformType.parse(columnDefinition); 258 } 259 260 private boolean platformMatch(Platform platform) { 261 return platform == null || isPlatform(platform); 262 } 263 264 /** 265 * Return true if this matches the given platform. 266 */ 267 public boolean isPlatform(Platform platform) { 268 return this.platform == platform; 269 } 270 271 /** 272 * Return the platform key. 273 */ 274 public Platform getPlatform() { 275 return platform; 276 } 277 278 /** 279 * Return the name of the underlying Platform in lowercase. 280 * <p> 281 * "generic" is returned when no specific database platform has been set or found. 282 * </p> 283 */ 284 public String getName() { 285 return platform.name().toLowerCase(); 286 } 287 288 /** 289 * Return true if we are using Sequence batch mode rather than STEP. 290 */ 291 public boolean isSequenceBatchMode() { 292 return sequenceBatchMode; 293 } 294 295 /** 296 * Set to false to not use sequence batch mode but instead STEP mode. 297 */ 298 public void setSequenceBatchMode(boolean sequenceBatchMode) { 299 this.sequenceBatchMode = sequenceBatchMode; 300 } 301 302 /** 303 * Return true if this database platform supports native ILIKE expression. 304 */ 305 public boolean isSupportsNativeIlike() { 306 return supportsNativeIlike; 307 } 308 309 /** 310 * Return the maximum table name length. 311 * <p> 312 * This is used when deriving names of intersection tables. 313 * </p> 314 */ 315 public int getMaxTableNameLength() { 316 return maxTableNameLength; 317 } 318 319 /** 320 * Return the maximum constraint name allowed for the platform. 321 */ 322 public int getMaxConstraintNameLength() { 323 return maxConstraintNameLength; 324 } 325 326 /** 327 * Return true if the JDBC driver does not allow additional queries to execute 328 * when a resultSet is being 'streamed' as is the case with findEach() etc. 329 * <p> 330 * Honestly, this is a workaround for a stupid MySql JDBC driver limitation. 331 * </p> 332 */ 333 public boolean useExtraTransactionOnIterateSecondaryQueries() { 334 return useExtraTransactionOnIterateSecondaryQueries; 335 } 336 337 /** 338 * Return a DB Sequence based IdGenerator. 339 * 340 * @param be the BackgroundExecutor that can be used to load the sequence if 341 * desired 342 * @param ds the DataSource 343 * @param stepSize the sequence allocation size as defined by mapping (defaults to 50) 344 * @param seqName the name of the sequence 345 */ 346 public PlatformIdGenerator createSequenceIdGenerator(BackgroundExecutor be, DataSource ds, int stepSize, String seqName) { 347 return null; 348 } 349 350 /** 351 * Return the behaviour to use when ending a read only transaction. 352 */ 353 public OnQueryOnly getOnQueryOnly() { 354 return onQueryOnly; 355 } 356 357 /** 358 * Set the behaviour to use when ending a read only transaction. 359 */ 360 public void setOnQueryOnly(OnQueryOnly onQueryOnly) { 361 this.onQueryOnly = onQueryOnly; 362 } 363 364 /** 365 * Return the DbEncrypt handler for this DB platform. 366 */ 367 public DbEncrypt getDbEncrypt() { 368 return dbEncrypt; 369 } 370 371 /** 372 * Set the DbEncrypt handler for this DB platform. 373 */ 374 public void setDbEncrypt(DbEncrypt dbEncrypt) { 375 this.dbEncrypt = dbEncrypt; 376 } 377 378 /** 379 * Return the history support for this database platform. 380 */ 381 public DbHistorySupport getHistorySupport() { 382 return historySupport; 383 } 384 385 /** 386 * Set the history support for this database platform. 387 */ 388 public void setHistorySupport(DbHistorySupport historySupport) { 389 this.historySupport = historySupport; 390 } 391 392 /** 393 * So no except for Postgres and CockroachDB. 394 */ 395 public boolean isNativeArrayType() { 396 return false; 397 } 398 399 /** 400 * Return true if the DB supports native UUID. 401 */ 402 public boolean isNativeUuidType() { 403 return nativeUuidType; 404 } 405 406 /** 407 * Return the mapping of JDBC to DB types. 408 * 409 * @return the db type map 410 */ 411 public DbPlatformTypeMapping getDbTypeMap() { 412 return dbTypeMap; 413 } 414 415 /** 416 * Return the mapping for DB column default values. 417 */ 418 public DbDefaultValue getDbDefaultValue() { 419 return dbDefaultValue; 420 } 421 422 /** 423 * Return the column alias prefix. 424 */ 425 public String getColumnAliasPrefix() { 426 return columnAliasPrefix; 427 } 428 429 /** 430 * Set the column alias prefix. 431 */ 432 public void setColumnAliasPrefix(String columnAliasPrefix) { 433 this.columnAliasPrefix = columnAliasPrefix; 434 } 435 436 /** 437 * Return the table alias placeholder. 438 */ 439 public String getTableAliasPlaceHolder() { 440 return tableAliasPlaceHolder; 441 } 442 443 /** 444 * Set the table alias placeholder. 445 */ 446 public void setTableAliasPlaceHolder(String tableAliasPlaceHolder) { 447 this.tableAliasPlaceHolder = tableAliasPlaceHolder; 448 } 449 450 /** 451 * Return the close quote for quoted identifiers. 452 */ 453 public String getCloseQuote() { 454 return closeQuote; 455 } 456 457 /** 458 * Return the open quote for quoted identifiers. 459 */ 460 public String getOpenQuote() { 461 return openQuote; 462 } 463 464 /** 465 * Return the JDBC type used to store booleans. 466 */ 467 public int getBooleanDbType() { 468 return booleanDbType; 469 } 470 471 /** 472 * Return the data type that should be used for Blob. 473 * <p> 474 * This is typically Types.BLOB but for Postgres is Types.LONGVARBINARY for 475 * example. 476 * </p> 477 */ 478 public int getBlobDbType() { 479 return blobDbType; 480 } 481 482 /** 483 * Return the data type that should be used for Clob. 484 * <p> 485 * This is typically Types.CLOB but for Postgres is Types.VARCHAR. 486 * </p> 487 */ 488 public int getClobDbType() { 489 return clobDbType; 490 } 491 492 /** 493 * Return true if empty strings should be treated as null. 494 * 495 * @return true, if checks if is treat empty strings as null 496 */ 497 public boolean isTreatEmptyStringsAsNull() { 498 return treatEmptyStringsAsNull; 499 } 500 501 /** 502 * Return true if a compound ID in (...) type expression needs to be in 503 * expanded form of (a=? and b=?) or (a=? and b=?) or ... rather than (a,b) in 504 * ((?,?),(?,?),...); 505 */ 506 public boolean isIdInExpandedForm() { 507 return idInExpandedForm; 508 } 509 510 /** 511 * Return true if the ResultSet TYPE_FORWARD_ONLY Hint should be used on 512 * findIterate() and findVisit() PreparedStatements. 513 * <p> 514 * This specifically is required for MySql when processing large results. 515 * </p> 516 */ 517 public boolean isForwardOnlyHintOnFindIterate() { 518 return forwardOnlyHintOnFindIterate; 519 } 520 521 /** 522 * Set to true if the ResultSet TYPE_FORWARD_ONLY Hint should be used by default on findIterate PreparedStatements. 523 */ 524 public void setForwardOnlyHintOnFindIterate(boolean forwardOnlyHintOnFindIterate) { 525 this.forwardOnlyHintOnFindIterate = forwardOnlyHintOnFindIterate; 526 } 527 528 /** 529 * Return true if the ResultSet CONCUR_UPDATABLE Hint should be used on 530 * createNativeSqlTree() PreparedStatements. 531 * <p> 532 * This specifically is required for Hana which doesn't support CONCUR_UPDATABLE 533 * </p> 534 */ 535 public boolean isSupportsResultSetConcurrencyModeUpdatable() { 536 return supportsResultSetConcurrencyModeUpdatable; 537 } 538 539 /** 540 * Set to true if the ResultSet CONCUR_UPDATABLE Hint should be used by default on createNativeSqlTree() PreparedStatements. 541 */ 542 public void setSupportsResultSetConcurrencyModeUpdatable(boolean supportsResultSetConcurrencyModeUpdatable) { 543 this.supportsResultSetConcurrencyModeUpdatable = supportsResultSetConcurrencyModeUpdatable; 544 } 545 546 /** 547 * Normally not needed - overridden in CockroachPlatform. 548 */ 549 public boolean isDdlCommitOnCreateIndex() { 550 return false; 551 } 552 553 /** 554 * Return the DB identity/sequence features for this platform. 555 * 556 * @return the db identity 557 */ 558 public DbIdentity getDbIdentity() { 559 return dbIdentity; 560 } 561 562 /** 563 * Return the SqlLimiter used to apply additional sql around a query to limit 564 * its results. 565 * <p> 566 * Basically add the clauses for limit/offset, rownum, row_number(). 567 * </p> 568 * 569 * @return the sql limiter 570 */ 571 public SqlLimiter getSqlLimiter() { 572 return sqlLimiter; 573 } 574 575 /** 576 * Return the BasicSqlLimiter for limit/offset of SqlQuery queries. 577 */ 578 public BasicSqlLimiter getBasicSqlLimiter() { 579 return basicSqlLimiter; 580 } 581 582 /** 583 * Set the DB TRUE literal (from the registered boolean ScalarType) 584 */ 585 public void setDbTrueLiteral(String dbTrueLiteral) { 586 this.dbDefaultValue.setTrue(dbTrueLiteral); 587 } 588 589 /** 590 * Set the DB FALSE literal (from the registered boolean ScalarType) 591 */ 592 public void setDbFalseLiteral(String dbFalseLiteral) { 593 this.dbDefaultValue.setFalse(dbFalseLiteral); 594 } 595 596 /** 597 * Convert backticks to the platform specific open quote and close quote 598 * <p> 599 * Specific plugins may implement this method to cater for platform specific 600 * naming rules. 601 * </p> 602 * 603 * @param dbName the db table or column name 604 * @return the db table or column name with potentially platform specific quoted identifiers 605 */ 606 public String convertQuotedIdentifiers(String dbName) { 607 // Ignore null values e.g. schema name or catalog 608 if (dbName != null && !dbName.isEmpty()) { 609 if (dbName.charAt(0) == BACK_TICK) { 610 if (dbName.charAt(dbName.length() - 1) == BACK_TICK) { 611 return openQuote + dbName.substring(1, dbName.length() - 1) + closeQuote; 612 } else { 613 logger.error("Missing backquote on [" + dbName + "]"); 614 } 615 } else if (allQuotedIdentifiers) { 616 return openQuote + dbName + closeQuote; 617 } 618 } 619 return dbName; 620 } 621 622 /** 623 * Remove quoted identifier quotes from the table or column name if present. 624 */ 625 public String unQuote(String dbName) { 626 if (dbName != null && !dbName.isEmpty()) { 627 if (dbName.startsWith(openQuote)) { 628 // trim off the open and close quotes 629 return dbName.substring(1, dbName.length()-1); 630 } 631 } 632 return dbName; 633 } 634 635 /** 636 * Set to true if select count against anonymous view requires an alias. 637 */ 638 public boolean isSelectCountWithAlias() { 639 return selectCountWithAlias; 640 } 641 642 public String completeSql(String sql, Query<?> query) { 643 if (query.isForUpdate()) { 644 sql = withForUpdate(sql, query.getForUpdateMode()); 645 } 646 647 return sql; 648 } 649 650 /** 651 * For update hint on the FROM clause (SQL server only). 652 */ 653 public String fromForUpdate(Query.ForUpdate forUpdateMode) { 654 // return null except for sql server 655 return null; 656 } 657 658 protected String withForUpdate(String sql, Query.ForUpdate forUpdateMode) { 659 // silently assume the database does not support the "for update" clause. 660 logger.info("it seems your database does not support the 'for update' clause"); 661 return sql; 662 } 663 664 /** 665 * Returns the like clause used by this database platform. 666 * <p> 667 * This may include an escape clause to disable a default escape character. 668 */ 669 public String getLikeClause(boolean rawLikeExpression) { 670 return rawLikeExpression ? likeClauseRaw : likeClauseEscaped; 671 } 672 673 /** 674 * Return the platform default JDBC batch mode for persist cascade. 675 */ 676 public PersistBatch getPersistBatchOnCascade() { 677 return persistBatchOnCascade; 678 } 679 680 /** 681 * Create the DB schema if it does not exist. 682 */ 683 public void createSchemaIfNotExists(String dbSchema, Connection connection) throws SQLException { 684 if (!schemaExists(dbSchema, connection)) { 685 Statement query = connection.createStatement(); 686 try { 687 logger.info("create schema:{}", dbSchema); 688 query.executeUpdate("create schema " + dbSchema); 689 } finally { 690 JdbcClose.close(query); 691 } 692 } 693 } 694 695 /** 696 * Return true if the schema exists. 697 */ 698 public boolean schemaExists(String dbSchema, Connection connection) throws SQLException { 699 ResultSet schemas = connection.getMetaData().getSchemas(); 700 try { 701 while (schemas.next()) { 702 String schema = schemas.getString(1); 703 if (schema.equalsIgnoreCase(dbSchema)) { 704 return true; 705 } 706 } 707 } finally { 708 JdbcClose.close(schemas); 709 } 710 return false; 711 } 712 713 /** 714 * Return true if the table exists. 715 */ 716 public boolean tableExists(Connection connection, String catalog, String schema, String table) throws SQLException { 717 718 DatabaseMetaData metaData = connection.getMetaData(); 719 ResultSet tables = metaData.getTables(catalog, schema, table, null); 720 try { 721 return tables.next(); 722 } finally { 723 JdbcClose.close(tables); 724 } 725 } 726 727 /** 728 * Return true if partitions exist for the given table. 729 */ 730 public boolean tablePartitionsExist(Connection connection, String table) throws SQLException { 731 return true; 732 } 733 734 /** 735 * Return the SQL to create an initial partition for the given table. 736 */ 737 public String tablePartitionInit(String tableName, PartitionMode mode, String property, String singlePrimaryKey) { 738 return null; 739 } 740 741 /** 742 * Escapes the like string for this DB-Platform 743 */ 744 public String escapeLikeString(String value) { 745 StringBuilder sb = null; 746 for (int i = 0; i < value.length(); i++) { 747 char ch = value.charAt(i); 748 boolean escaped = false; 749 for (char escapeChar: likeSpecialCharacters) { 750 if (ch == escapeChar) { 751 if (sb == null) { 752 sb = new StringBuilder(value.substring(0, i)); 753 } 754 escapeLikeCharacter(escapeChar, sb); 755 escaped = true; 756 break; 757 } 758 } 759 if (!escaped && sb != null) { 760 sb.append(ch); 761 } 762 } 763 if (sb == null) { 764 return value; 765 } else { 766 return sb.toString(); 767 } 768 } 769 770 protected void escapeLikeCharacter(char ch, StringBuilder sb) { 771 sb.append(likeEscapeChar).append(ch); 772 } 773}