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 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)