Package net.sf.basedb.core.dbengine

Interface DbEngine

All Known Implementing Classes:
AbstractDbEngine, DefaultDbEngine, MySQLEngine, PostgresDbEngine
public interface DbEngine
Information about database-specific options that are not covered by the Hibernate Dialect objects.
Version:
2.0
Author:
nicklas
See Also:
Last modified
$Date: 2021-02-18 08:02:06 +0100 (Thu, 18 Feb 2021) $

  • Method Details

    • getQuotedName

      String getQuotedName(String name)
      Quote the name with the database specific quote character.
      Parameters:
      name - The name of an object in the database
      Returns:
      A quoted name
      Since:
      3.1

    • useThetaJoin

      boolean useThetaJoin()
      If the database uses theta join or ansi join.
      Returns:
      TRUE for theta join, FALSE for ansi join

    • getCatalogName

      String getCatalogName(String catalog, String schema)
      Get the catalog name to use for referencing the given catalog/schema pair. A database that fully supports schemas within catalogs should simply return the catalog name. Others (eg. MySQL should generate a catalog name that is a combination of the two).
      Parameters:
      catalog - The catalog name (always not null)
      schema - The schema name (always not null)
      Returns:
      Should return a non-null value
      Since:
      3.4

    • getSchemaName

      String getSchemaName(String catalog, String schema)
      Get the schema name to use for referencing the given catalog/schema pair. A database that fully supports schemas within catalogs should simply return the schema name. Others (eg. MySQL should return null).
      Parameters:
      catalog - The catalog name (always not null)
      schema - The schema name (always not null)
      Returns:
      The schema name or null to indicate that schemas are not used
      Since:
      3.4

    • getCreateSchemaSql

      String getCreateSchemaSql(String catalog, String schema)
      Generate an SQL statement to create the given schema. Note that the catalog and schema passed to this method should be the names returned from getCatalogName(String, String) and getSchemaName(String, String).
      Since:
      3.4

    • getCreateIndexSql

      String getCreateIndexSql(String catalog, String schema, String table, String name, Set<String> columns, boolean unique)
      Generate SQL to create an index. Note! When calling this method, ensure that the column set have a predictable iteration order!
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the index to be created
      columns - The name of the columns in the index
      unique - If the index must contain unique values or not
      Returns:
      The SQL to execute

    • getCreatePartialIndexSql

      String getCreatePartialIndexSql(String catalog, String schema, String table, String name, Set<String> columns, String condition)
      Generate SQL to create a partial index. A partial index is an index which only include rows meeting a certain condition. Unique partial indexes are not supported. Note! When calling this method, ensure that the column set have a predictable iteration order!
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the index to be created
      columns - The name of the columns in the index
      condition - The condition that specifies which rows to include in the index. Use '[' and ']' as placeholders for the open/close quote of the current dialect.
      Returns:
      The SQL to execute
      Since:
      3.10

    • getDropIndexSql

      String getDropIndexSql(String catalog, String schema, String table, String name, boolean unique)
      Generate SQL to drop an index.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the index
      unique - If the index contains unique values or not
      Returns:
      The SQL to execute

    • dropForeignKeysUsedInIndex

      boolean dropForeignKeysUsedInIndex()
      When dropping an index, must foreign keys that uses the same columns also be dropped (before dropping the index)? Default is FALSE, but MySQL need TRUE.
      Since:
      3.6

    • getCreateForeignKeySql

      String getCreateForeignKeySql(String catalog, String schema, String table, String name, Set<String> columns, String refTable, Set<String> refColumns)
      Generates SQL that creates a foreign key constraint between two tables. Note! When calling this method, ensure that the Set:s with columns have a predictable iteration order!
      Parameters:
      catalog - The name of the catalog (database) where the tables are located, or null if it is located in the current catalog
      schema - The name of the schema where the tables are located, or null if is located in the current schema
      table - The name of the foreign key table
      name - The name of the foreign key
      columns - The columns that make up the foreign key
      refTable - The table that is referenced by the foreign key
      refColumns - The columns that are referenced by the foreign key
      Returns:
      The SQL to create the foreign key
      Since:
      3.1

    • getDropForeignKeySql

      String getDropForeignKeySql(String catalog, String schema, String table, String name)
      Generate SQL to drop a foreign key constraint.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the foreign key
      Returns:
      The SQL to execute
      Since:
      3.0

    • getCreatePrimaryKeySql

      String getCreatePrimaryKeySql(String catalog, String schema, String table, String name, Set<String> columns)
      Generate SQL to create a primary key for a table. Note! When calling this method, ensure that the column set have a predictable iteration order!
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the primary key
      columns - The name of the columns in the primary key
      Returns:
      The SQL to execute
      Since:
      3.1

    • getDropPrimaryKey

      String getDropPrimaryKey(String catalog, String schema, String table, String name)
      Generate SQL that drops the primary key from a table.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      name - The name of the primary key
      Returns:
      The SQL to execute
      Since:
      3.1

    • getInsertAutoIncrementSql

      String getInsertAutoIncrementSql(PersistentClass pc)
      Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.
      Parameters:
      pc - The persistent class
      Since:
      3.0

    • analyzeAfterBatchOperation

      boolean analyzeAfterBatchOperation()
      If TRUE, a batcher that inserts, updates or delets a lot of rows in a table should execute the SQL returned by getAnalyzeTableSql(String, String, String) for that table.

      Postgres, for example, almost always require that an analysis is run since subsequent queries otherwise may take a long time to execute if indexes can't be used properly.

    • getOptimizeTableSql

      String getOptimizeTableSql(String catalog, String schema, String table)
      Generate SQL to optimize a table. The meaning of this may depend on the actual database used. Typical operations include updating index statistics, removing unused records, etc. Here are some example: 
      MySQL: OPTIMIZE TABLE <table>
Postgres: VACCUUM <table>
      NOTE! The SQL returned by this method may impact the performance on the database. It is only intended to be executed when there is a need to clean up the database. How often this is needed depends on the database.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table to optimize
      Returns:
      The SQL to execute
      See Also:

    • getAnalyzeTableSql

      String getAnalyzeTableSql(String catalog, String schema, String table)
      Generate SQL to update the index statistics for a table. Here are some example: 
      MySQL: ANALYZE TABLE <table>
Postgres: ANALYZE <table>
      NOTE! The SQL returned by this method must be able to run within a transaction and from multiple transactions on the same table at the same time. If no such SQL statement exists null should be returned.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table to analyze
      Returns:
      The SQL to execute, or null if not supported
      See Also:

    • makeSafeCreateTable

      String makeSafeCreateTable(String sql, String catalog, String schema, String table)
      Create a "safe" CREATE TABLE query string that will not fail if the table already exists. Implementors should inject proper SQL code in a way that is supported in the underlying database. The MySQL engine, for example, replaces CREATE TABLE with CREATE TABLE IF NOT EXISTS. If the underlying database doesn't support this kind of check the original SQL should be returned umodified.
      Parameters:
      sql - The original SQL
      catalog - The name of the catalog (database) where the table is being create, or null if it is located in the current catalog
      schema - The name of the schema where the table is being created, or null if is located in the current schema
      table - The name of the table that is being created
      Returns:
      The modified or unmodified SQL statement
      Since:
      2.6

    • getCreateTemporaryIdTable

      default String getCreateTemporaryIdTable(String table)
      Generate SQL for creating a temporary table with a single 'id' column that is the primary key. The table need to be automatically deleted when the transaction ends. If the database doesn't support this null should be returned. Note! This method is experiment and may change in a future API. A default implementation that return null is provided.
      Parameters:
      table - The name of the temporary table
      Returns:
      SQL statement or null
      Since:
      3.18

    • caseInsensitiveComparison

      boolean caseInsensitiveComparison()
      If the database does case sensitive or case insensitive string comparison in expressions.
      Returns:
      FALSE if comparisons are case sensitive (ABC != abc), TRUE if they are case insensitive (ABC = abc)
      Since:
      2.4

    • supportsColumnAliasInWhere

      boolean supportsColumnAliasInWhere()
      If the current database supports column aliases in the where part of a query or not. For example: SELECT tbl.blah as foo, ... FROM Table tbl WHERE foo = 2

      If not supported the entire expression will be used again.

      Returns:
      TRUE if column aliases are supported, FALSE otherwise

    • supportColumnAliasInOrderBy

      boolean supportColumnAliasInOrderBy()
      If the current database supports column aliases in the order by part of a query or not. For example: SELECT tbl.blah as foo, ... FROM Table tbl ORDER BY foo

      If not supported the entire expression will be used again.

      Returns:
      TRUE if column aliases are supported, FALSE otherwise

    • supportColumnAliasInGroupBy

      boolean supportColumnAliasInGroupBy()
      If the current database supports column aliases in the group by part of a query or not. For example: SELECT tbl.blah as foo, ... FROM Table tbl GROUP BY foo

      If not supported the entire expression will be used again.

      Returns:
      TRUE if column aliases are supported, FALSE otherwise

    • supportColumnAliasInHaving

      boolean supportColumnAliasInHaving()
      If the current database supports column aliases in the order by part of a query or not. For example: SELECT count(tbl.blah) as foo, ... FROM Table tbl GROUP BY ... HAVING foo = 2

      If not supported the entire expression will be used again.

      Returns:
      TRUE if column aliases are supported, FALSE otherwise

    • selectOrderByColumnsIfDistinct

      boolean selectOrderByColumnsIfDistinct()
      If the database requires that columns appearing in the ORDER BY part of the query must also be part of the SELECT list when using the DISTINCT keyword.
      Returns:
      TRUE if order by columns must be selected, FALSE otherwise

    • checkForInvalidNumberOperation

      boolean checkForInvalidNumberOperation()
      If we need to check for invalid arguments to numerical functions to avoid exceptions from the database. Postgres, for example, throws exceptions if trying to divide by zero, while MySQL returns null. To get the same behavior we need to add a CASE...WHEN statement to the query for Postgres. Example: 
      CASE WHEN denominator = 0 THEN null ELSE numerator / denominator
      Returns:
      TRUE if we need to check the numbers, FALSE otherwise

    • getMaxParametersInQuery

      int getMaxParametersInQuery()
      Get the maximum number of parameters that can be used in a prepared statement for a query.
      Since:
      3.4.1

    • ln

      String ln(String value)
      Get the function call that takes the natural logarithm of a value. For example: LN(value)
      Parameters:
      value - The value to take the logarithm of
      Returns:
      The function call that calculates the logartihm
      Throws:
      UnsupportedOperationException - If the database doesn't support logarithms

    • abs

      String abs(String value)
      Get the function call that takes the absolute of a value. For example: ABS(value)
      Parameters:
      value - The value to use in the calculation
      Returns:
      The function call that calculates the absolute value
      Throws:
      UnsupportedOperationException - If the database doesn't support this function

    • exp

      String exp(String value)
      Get the function call that calculates the exponential of a value. For example: EXP(value)
      Parameters:
      value - The value to use in the calculation
      Returns:
      The function call that calculates the exponential
      Throws:
      UnsupportedOperationException - If the database doesn't support this function

    • power

      String power(String base, String exponent)
      Get the function call that calculates base raised to the power of exponent For example: POW(base, value)
      Parameters:
      base - The base in the expression
      exponent - The exponent in the expression
      Returns:
      The function call that calculates the power
      Throws:
      UnsupportedOperationException - If the database doesn't support this function

    • rlike

      String rlike(String value, String regexp)
      Get a function/expression that matches the left value against the right value which should be a regular expression. If the database doesn't support regular expressions, the returned expression should use equality testing.
      Parameters:
      value - The left value
      regexp - The regular expression as a string
      Returns:
      The function call/expression that matches the value agains the regular expression
      Since:
      2.8

    • castToDate

      String castToDate(String value)
      Get a function that casts a date/timestamp to a date (eg. no time should be included in the result).
      Parameters:
      value - The value to cast
      Returns:
      An expression that casts the value to a date
      Since:
      2.16

    • isValidTableName

      boolean isValidTableName(String tableName)
      Check if a given string is valid to be used as a table name in the current database.
      Parameters:
      tableName - The string to check
      Returns:
      TRUE if the name is valid, FALSE if not
      Since:
      2.4

    • isValidColumnName

      boolean isValidColumnName(String columnName)
      Check if a given string is valid to be used as a column name in the current database.
      Parameters:
      columnName - The string to check
      Returns:
      TRUE if the name is valid, FALSE if not
      Since:
      2.4

    • getCaseSensitiveVarchar

      String getCaseSensitiveVarchar(int length)
      Generate a column declaration that creates a case-sensitive variable-length string column type. If the database engine already treats strings in a case-sensitive way, it is safe to return null from this method. MySQL is case-insensitive by default. To get a case-sensitive column we can use: varchar(length) CHARACTER SET utf8 COLLATE utf8_bin
      Parameters:
      length - The maximum length that needs to be stored in the column
      Returns:
      The SQL snippet that creates the column, or null to let Hibernate use the default SQL
      Since:
      2.9

    • inspectSchemaGenerationSQL

      String inspectSchemaGenerationSQL(String sql, Dialect dialect, SchemaGenerator.Mode mode)
      Let the DbEninge inspect a schema generation sql statment and possible modify it before it is sent to the database. This method is called from the SchemaGenerator class when creating or updating a database.
      Parameters:
      sql - The original SQL statment as generated by Hibernate
      dialect - The Hibernate dialect currently in use
      mode - The installation mode
      Returns:
      The SQL statement which may have been modified or null to skip executing the statment
      Since:
      3.1

    • useSavePointToContinueTransactionFromSqlFailure

      boolean useSavePointToContinueTransactionFromSqlFailure()
      If the underlying database need to create a savepoint before executing an SQL statement that results in an error in order to be able to continue using the same transaction for other SQL queries. This is, for example, needed by PostgreSQL which will otherwise ignore all SQL statements executed after the one that caused an error.
      Returns:
      TRUE if a SAVEPOINT is needed, FALSE if not

    • getApproximateRowCountSql

      String getApproximateRowCountSql(String catalog, String schema, String table)
      Get an SQL statement that returns an approximate count for the number of rows in the given table. The SQL query should return a single row with a single value.
      Parameters:
      catalog - The name of the catalog (database) where the table is located, or null if it is located in the current catalog
      schema - The name of the schema where the table is located, or null if is located in the current schema
      table - The name of the table
      Returns:
      An SQL query or null
      Since:
      3.1