Package net.sf.basedb.core.dbengine

Class PostgresDbEngine

java.lang.Object

net.sf.basedb.core.dbengine.AbstractDbEngine

net.sf.basedb.core.dbengine.PostgresDbEngine

All Implemented Interfaces:

DbEngine

public class PostgresDbEngine
extends AbstractDbEngine
implements DbEngine

Database engine for Postgres.

Version:

2.0

Author:

nicklas

Last modified

$Date: 2021-02-18 08:02:06 +0100 (Thu, 18 Feb 2021) $

Constructor Summary

Constructors

Constructor	Description
PostgresDbEngine()	Create PostgresDbEngine.

Method Summary

Modifier and Type	Method	Description
boolean	analyzeAfterBatchOperation()	Return true.
private void	appendList(StringBuilder sb, Collection<String> items)
boolean	caseInsensitiveComparison()	Returns FALSE.
boolean	checkForInvalidNumberOperation()	Returns TRUE.
String	getAnalyzeTableSql(String catalog, String schema, String table)	Generate ANALYZE <schema>.<table>.
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.
String	getCreateForeignKeySql(String catalog, String schema, String table, String name, Set<String> columns, String refTable, Set<String> refColumns)	Generate alter table schema.table add constraint name foreign key (columns) references refTable (refColumns)
String	getCreateIndexSql(String catalog, String schema, String table, String name, Set<String> columns, boolean unique)	Generate alter table <schema>.<table> add constraint <name> unique (<columns>) for a unique index, create index <name> on <schema>.<table> (<columns>) for other indexes.
String	getCreatePartialIndexSql(String catalog, String schema, String table, String name, Set<String> columns, String condition)	Creates a partial index which is the same as a regular index + 'WHERE [condition]';
String	getCreatePrimaryKeySql(String catalog, String schema, String table, String name, Set<String> columns)	Generate alter table schema.table add constraint name primary key (columns).
String	getCreateSchemaSql(String catalog, String schema)	Generate create schema <schema>.
String	getCreateTemporaryIdTable(String table)	CREATE TEMPORARY TABLE <table> (id integer PRIMARY KEY) ON COMMIT DROP
String	getDropForeignKeySql(String catalog, String schema, String table, String name)	Generate alter table <schema>.<table> DROP CONSTRAINT <name>
String	getDropIndexSql(String catalog, String schema, String table, String name, boolean unique)	Generate alter table <schema>.<table> drop constraint <name> for unique indexes, drop index <schema>.<name> for other indexes.
String	getDropPrimaryKey(String catalog, String schema, String table, String name)	Generate alter table schema.table drop constraint name
String	getInsertAutoIncrementSql(PersistentClass pc)	Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.
String	getOptimizeTableSql(String catalog, String schema, String table)	Generate VACUUM FULL <schema>.<table>.
String	rlike(String value, String regexp)	Return <value> ~ <regexp>.
boolean	selectOrderByColumnsIfDistinct()	Returns TRUE.
boolean	supportColumnAliasInGroupBy()	Returns TRUE.
boolean	supportColumnAliasInHaving()	Returns FALSE.
boolean	supportColumnAliasInOrderBy()	Returns TRUE.
boolean	supportsColumnAliasInWhere()	Returns FALSE.
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.
boolean	useThetaJoin()	Always false.

Methods inherited from class net.sf.basedb.core.dbengine.AbstractDbEngine

abs, castToDate, dropForeignKeysUsedInIndex, exp, getCaseSensitiveVarchar, getCatalogName, getMaxParametersInQuery, getQuotedName, getSchemaName, inspectSchemaGenerationSQL, isValidColumnName, isValidName, isValidTableName, ln, makeSafeCreateTable, power

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Methods inherited from interface net.sf.basedb.core.dbengine.DbEngine

abs, castToDate, dropForeignKeysUsedInIndex, exp, getCaseSensitiveVarchar, getCatalogName, getMaxParametersInQuery, getQuotedName, getSchemaName, inspectSchemaGenerationSQL, isValidColumnName, isValidTableName, ln, makeSafeCreateTable, power

Constructor Details

PostgresDbEngine

public PostgresDbEngine()

Create PostgresDbEngine.

Method Details

useThetaJoin

public boolean useThetaJoin()

Always false.

Specified by:

useThetaJoin in interface DbEngine

Overrides:

useThetaJoin in class AbstractDbEngine

Returns:

TRUE for theta join, FALSE for ansi join

getCreateSchemaSql

public String getCreateSchemaSql(String catalog,
 String schema)

Generate create schema <schema>. The catalog parameter is not supported.

Specified by:

getCreateSchemaSql in interface DbEngine

Since:

3.4

getCreateIndexSql

public String getCreateIndexSql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns,
 boolean unique)

Generate alter table <schema>.<table> add constraint <name> unique (<columns>) for a unique index, create index <name> on <schema>.<table> (<columns>) for other indexes. The catalog parameter is not supported.

Specified by:

getCreateIndexSql in interface DbEngine

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

public String getCreatePartialIndexSql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns,
 String condition)

Creates a partial index which is the same as a regular index + 'WHERE [condition]';

Specified by:

getCreatePartialIndexSql in interface DbEngine

Overrides:

getCreatePartialIndexSql in class AbstractDbEngine

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

getDropIndexSql

public String getDropIndexSql(String catalog,
 String schema,
 String table,
 String name,
 boolean unique)

Generate alter table <schema>.<table> drop constraint <name> for unique indexes, drop index <schema>.<name> for other indexes. The catalog parameter is not supported.

Specified by:

getDropIndexSql in interface DbEngine

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

getCreateForeignKeySql

public String getCreateForeignKeySql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns,
 String refTable,
 Set<String> refColumns)

Generate alter table schema.table add constraint name foreign key (columns) references refTable (refColumns)

Specified by:

getCreateForeignKeySql in interface DbEngine

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

public String getDropForeignKeySql(String catalog,
 String schema,
 String table,
 String name)

Generate alter table <schema>.<table> DROP CONSTRAINT <name>

Specified by:

getDropForeignKeySql in interface DbEngine

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

public String getCreatePrimaryKeySql(String catalog,
 String schema,
 String table,
 String name,
 Set<String> columns)

Generate alter table schema.table add constraint name primary key (columns).

Specified by:

getCreatePrimaryKeySql in interface DbEngine

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

public String getDropPrimaryKey(String catalog,
 String schema,
 String table,
 String name)

Generate alter table schema.table drop constraint name

Specified by:

getDropPrimaryKey in interface DbEngine

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

public String getInsertAutoIncrementSql(PersistentClass pc)

Description copied from interface: DbEngine

Return a SQL fragment that can be used in an INSERT INTO statement to generate an ID for new rows.

Specified by:

getInsertAutoIncrementSql in interface DbEngine

Parameters:

pc - The persistent class

Returns:

Always "nextval('sequenceName')" (assuming that the ID column is generated by a sequence)

Since:

3.0

analyzeAfterBatchOperation

public boolean analyzeAfterBatchOperation()

Return true. Otherwise queries may take a very long time since indexes may not be used properly.

Specified by:

analyzeAfterBatchOperation in interface DbEngine

getOptimizeTableSql

public String getOptimizeTableSql(String catalog,
 String schema,
 String table)

Generate VACUUM FULL <schema>.<table>. The catalog parameter is not supported.

Specified by:

getOptimizeTableSql in interface DbEngine

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:

• DbEngine.getAnalyzeTableSql(String, String, String)

getAnalyzeTableSql

public String getAnalyzeTableSql(String catalog,
 String schema,
 String table)

Generate ANALYZE <schema>.<table>. The catalog parameter is not supported.

Specified by:

getAnalyzeTableSql in interface DbEngine

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:

• DbEngine.getOptimizeTableSql(String, String, String)

getCreateTemporaryIdTable

public String getCreateTemporaryIdTable(String table)

CREATE TEMPORARY TABLE <table> (id integer PRIMARY KEY) ON COMMIT DROP

Specified by:

getCreateTemporaryIdTable in interface DbEngine

Parameters:

table - The name of the temporary table

Returns:

SQL statement or null

Since:

3.18

caseInsensitiveComparison

public boolean caseInsensitiveComparison()

Returns FALSE.

Specified by:

caseInsensitiveComparison in interface DbEngine

Returns:

FALSE if comparisons are case sensitive (ABC != abc), TRUE if they are case insensitive (ABC = abc)

Since:

2.4

supportsColumnAliasInWhere

public boolean supportsColumnAliasInWhere()

Returns FALSE.

Specified by:

supportsColumnAliasInWhere in interface DbEngine

Overrides:

supportsColumnAliasInWhere in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInOrderBy

public boolean supportColumnAliasInOrderBy()

Returns TRUE.

Specified by:

supportColumnAliasInOrderBy in interface DbEngine

Overrides:

supportColumnAliasInOrderBy in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInGroupBy

public boolean supportColumnAliasInGroupBy()

Returns TRUE.

Specified by:

supportColumnAliasInGroupBy in interface DbEngine

Overrides:

supportColumnAliasInGroupBy in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

supportColumnAliasInHaving

public boolean supportColumnAliasInHaving()

Returns FALSE.

Specified by:

supportColumnAliasInHaving in interface DbEngine

Overrides:

supportColumnAliasInHaving in class AbstractDbEngine

Returns:

TRUE if column aliases are supported, FALSE otherwise

checkForInvalidNumberOperation

public boolean checkForInvalidNumberOperation()

Returns TRUE.

Specified by:

checkForInvalidNumberOperation in interface DbEngine

Overrides:

checkForInvalidNumberOperation in class AbstractDbEngine

Returns:

TRUE if we need to check the numbers, FALSE otherwise

selectOrderByColumnsIfDistinct

public boolean selectOrderByColumnsIfDistinct()

Returns TRUE.

Specified by:

selectOrderByColumnsIfDistinct in interface DbEngine

Overrides:

selectOrderByColumnsIfDistinct in class AbstractDbEngine

Returns:

TRUE if order by columns must be selected, FALSE otherwise

rlike

public String rlike(String value,
 String regexp)

Return <value> ~ <regexp>.

Specified by:

rlike in interface DbEngine

Overrides:

rlike in class AbstractDbEngine

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

useSavePointToContinueTransactionFromSqlFailure

public boolean useSavePointToContinueTransactionFromSqlFailure()

Description copied from interface: DbEngine

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.

Specified by:

useSavePointToContinueTransactionFromSqlFailure in interface DbEngine

Overrides:

useSavePointToContinueTransactionFromSqlFailure in class AbstractDbEngine

Returns:

Always TRUE

getApproximateRowCountSql

public String getApproximateRowCountSql(String catalog,
 String schema,
 String table)

Description copied from interface: DbEngine

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.

Specified by:

getApproximateRowCountSql in interface DbEngine

Overrides:

getApproximateRowCountSql in class AbstractDbEngine

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:

null, since this depends on capabilities of underlying database

appendList

private void appendList(StringBuilder sb,
 Collection<String> items)