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 Summary
Modifier and TypeMethodDescriptionGet the function call that takes the absolute of a value.boolean
If TRUE, a batcher that inserts, updates or delets a lot of rows in a table should execute the SQL returned bygetAnalyzeTableSql(String, String, String)
for that table.boolean
If the database does case sensitive or case insensitive string comparison in expressions.castToDate
(String value) Get a function that casts a date/timestamp to a date (eg. no time should be included in the result).boolean
If we need to check for invalid arguments to numerical functions to avoid exceptions from the database.boolean
When dropping an index, must foreign keys that uses the same columns also be dropped (before dropping the index)?Get the function call that calculates the exponential of a value.getAnalyzeTableSql
(String catalog, String schema, String table) Generate SQL to update the index statistics for a table.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.getCaseSensitiveVarchar
(int length) Generate a column declaration that creates a case-sensitive variable-length string column type.getCatalogName
(String catalog, String schema) Get the catalog name to use for referencing the given catalog/schema pair.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.getCreateIndexSql
(String catalog, String schema, String table, String name, Set<String> columns, boolean unique) Generate SQL to create an index.getCreatePartialIndexSql
(String catalog, String schema, String table, String name, Set<String> columns, String condition) Generate SQL to create a partial index.getCreatePrimaryKeySql
(String catalog, String schema, String table, String name, Set<String> columns) Generate SQL to create a primary key for a table.getCreateSchemaSql
(String catalog, String schema) Generate an SQL statement to create the given schema.default String
getCreateTemporaryIdTable
(String table) Generate SQL for creating a temporary table with a single 'id' column that is the primary key.getDropForeignKeySql
(String catalog, String schema, String table, String name) Generate SQL to drop a foreign key constraint.getDropIndexSql
(String catalog, String schema, String table, String name, boolean unique) Generate SQL to drop an index.getDropPrimaryKey
(String catalog, String schema, String table, String name) Generate SQL that drops the primary key from a table.Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.int
Get the maximum number of parameters that can be used in a prepared statement for a query.getOptimizeTableSql
(String catalog, String schema, String table) Generate SQL to optimize a table.getQuotedName
(String name) Quote the name with the database specific quote character.getSchemaName
(String catalog, String schema) Get the schema name to use for referencing the given catalog/schema pair.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.boolean
isValidColumnName
(String columnName) Check if a given string is valid to be used as a column name in the current database.boolean
isValidTableName
(String tableName) Check if a given string is valid to be used as a table name in the current database.Get the function call that takes the natural logarithm of a value.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.Get the function call that calculatesbase
raised to the power ofexponent
For example:POW(base, value)
Get a function/expression that matches the left value against the right value which should be a regular expression.boolean
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.boolean
If the current database supports column aliases in the group by part of a query or not.boolean
If the current database supports column aliases in the order by part of a query or not.boolean
If the current database supports column aliases in the order by part of a query or not.boolean
If the current database supports column aliases in the where part of a query or not.boolean
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.boolean
If the database uses theta join or ansi join.
-
Method Details
-
getQuotedName
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
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
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
Generate an SQL statement to create the given schema. Note that the catalog and schema passed to this method should be the names returned fromgetCatalogName(String, String)
andgetSchemaName(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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- The name of the index to be createdcolumns
- The name of the columns in the indexunique
- 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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- The name of the index to be createdcolumns
- The name of the columns in the indexcondition
- 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
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- The name of the indexunique
- 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 catalogschema
- The name of the schema where the tables are located, or null if is located in the current schematable
- The name of the foreign key tablename
- The name of the foreign keycolumns
- The columns that make up the foreign keyrefTable
- The table that is referenced by the foreign keyrefColumns
- The columns that are referenced by the foreign key- Returns:
- The SQL to create the foreign key
- Since:
- 3.1
-
getDropForeignKeySql
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- 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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- The name of the primary keycolumns
- The name of the columns in the primary key- Returns:
- The SQL to execute
- Since:
- 3.1
-
getDropPrimaryKey
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the tablename
- The name of the primary key- Returns:
- The SQL to execute
- Since:
- 3.1
-
getInsertAutoIncrementSql
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 bygetAnalyzeTableSql(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
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the table to optimize- Returns:
- The SQL to execute
- See Also:
-
getAnalyzeTableSql
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the table to analyze- Returns:
- The SQL to execute, or null if not supported
- See Also:
-
makeSafeCreateTable
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 SQLcatalog
- The name of the catalog (database) where the table is being create, or null if it is located in the current catalogschema
- The name of the schema where the table is being created, or null if is located in the current schematable
- The name of the table that is being created- Returns:
- The modified or unmodified SQL statement
- Since:
- 2.6
-
getCreateTemporaryIdTable
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 aCASE...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
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
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
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
Get the function call that calculatesbase
raised to the power ofexponent
For example:POW(base, value)
- Parameters:
base
- The base in the expressionexponent
- The exponent in the expression- Returns:
- The function call that calculates the power
- Throws:
UnsupportedOperationException
- If the database doesn't support this function
-
rlike
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 valueregexp
- The regular expression as a string- Returns:
- The function call/expression that matches the value agains the regular expression
- Since:
- 2.8
-
castToDate
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
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
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
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
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 theSchemaGenerator
class when creating or updating a database.- Parameters:
sql
- The original SQL statment as generated by Hibernatedialect
- The Hibernate dialect currently in usemode
- 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
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 catalogschema
- The name of the schema where the table is located, or null if is located in the current schematable
- The name of the table- Returns:
- An SQL query or null
- Since:
- 3.1
-