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