Class DB_PostgreSQL

java.lang.Object
org.compiere.db.DB_PostgreSQL
All Implemented Interfaces:
AdempiereDatabase

public class DB_PostgreSQL extends Object implements AdempiereDatabase
PostgreSQL Database Port
Version:
$Id: DB_PostgreSQL.java,v 1.23 2005/03/11 20:29:01 jjanke Exp $ --- Modifications: removed static references to database connection and instead always get a new connection from database pool manager which manages all connections set rw/ro properties for the connection accordingly.
Author:
, Jorg Janke, Victor Perez, Ashley Ramdass (Posterita)
  • Field Details

  • Constructor Details

    • DB_PostgreSQL

      public DB_PostgreSQL()
      PostgreSQL Database
  • Method Details

    • getConvert

      public Convert getConvert()
      Description copied from interface: AdempiereDatabase
      Get Convert implementation for this DB adapter
      Specified by:
      getConvert in interface AdempiereDatabase
      Returns:
      Convert instance
    • getName

      public String getName()
      Get Database Name
      Specified by:
      getName in interface AdempiereDatabase
      Returns:
      database short name
    • getDescription

      public String getDescription()
      Get Database Description
      Specified by:
      getDescription in interface AdempiereDatabase
      Returns:
      database long name and version
    • getStandardPort

      public int getStandardPort()
      Get Standard JDBC Port
      Specified by:
      getStandardPort in interface AdempiereDatabase
      Returns:
      standard port
    • getDriver

      public Driver getDriver() throws SQLException
      Get and register Database Driver
      Specified by:
      getDriver in interface AdempiereDatabase
      Returns:
      Driver
      Throws:
      SQLException
    • getConnectionURL

      public String getConnectionURL(CConnection connection)
      Get Database Connection String. Requirements: - createdb -E UNICODE compiere
      Specified by:
      getConnectionURL in interface AdempiereDatabase
      Parameters:
      connection - Connection Descriptor
      Returns:
      connection String
    • getConnectionURL

      public String getConnectionURL(String dbHost, int dbPort, String dbName, String userName)
      Get Connection URL
      Specified by:
      getConnectionURL in interface AdempiereDatabase
      Parameters:
      dbHost - db Host
      dbPort - db Port
      dbName - sb Name
      userName - user name
      Returns:
      connection url
    • getConnectionURL

      public String getConnectionURL(String connectionURL, String userName)
      Get Database Connection String
      Specified by:
      getConnectionURL in interface AdempiereDatabase
      Parameters:
      connectionURL - Connection URL
      userName - user name
      Returns:
      connection String
    • getCatalog

      public String getCatalog()
      Get JDBC Catalog
      Specified by:
      getCatalog in interface AdempiereDatabase
      Returns:
      catalog (database name)
    • getSchema

      public String getSchema()
      Get JDBC Schema
      Specified by:
      getSchema in interface AdempiereDatabase
      Returns:
      schema (dbo)
    • supportsBLOB

      public boolean supportsBLOB()
      Supports BLOB
      Specified by:
      supportsBLOB in interface AdempiereDatabase
      Returns:
      true if BLOB is supported
    • toString

      public String toString()
      String Representation
      Specified by:
      toString in interface AdempiereDatabase
      Overrides:
      toString in class Object
      Returns:
      info
    • getStatus

      public String getStatus()
      Get Status
      Specified by:
      getStatus in interface AdempiereDatabase
      Returns:
      status info
    • convertStatement

      public String convertStatement(String oraStatement)
      Convert an individual Oracle Style statements to target database statement syntax
      Specified by:
      convertStatement in interface AdempiereDatabase
      Parameters:
      oraStatement -
      Returns:
      converted Statement
      Throws:
      Exception
    • getSystemUser

      public String getSystemUser()
      Get Name of System User
      Specified by:
      getSystemUser in interface AdempiereDatabase
      Returns:
      e.g. sa, system
    • getSystemDatabase

      public String getSystemDatabase(String databaseName)
      Get Name of System Database
      Specified by:
      getSystemDatabase in interface AdempiereDatabase
      Parameters:
      databaseName - database Name
      Returns:
      e.g. master or database Name
    • TO_DATE

      public String TO_DATE(Timestamp time, boolean dayOnly)
      Create SQL TO Date String from Timestamp
      Specified by:
      TO_DATE in interface AdempiereDatabase
      Parameters:
      time - Date to be converted
      dayOnly - true if time set to 00:00:00
      Returns:
      TO_TIMESTAMP('2001-01-30 18:10:20',''YYYY-MM-DD HH24:MI:SS') or TO_TIMESTAMP('2001-01-30',''YYYY-MM-DD')
    • TO_CHAR

      public String TO_CHAR(String columnName, int displayType, String AD_Language)
      Create SQL for formatted Date, Number
      Specified by:
      TO_CHAR in interface AdempiereDatabase
      Parameters:
      columnName - the column name in the SQL
      displayType - Display Type
      AD_Language - 6 character language setting (from Env.LANG_*)
      Returns:
      TRIM(TO_CHAR(columnName,'999G999G999G990D00','NLS_NUMERIC_CHARACTERS='',.''')) or TRIM(TO_CHAR(columnName,'TM9')) depending on DisplayType and Language
      See Also:
    • TO_NUMBER

      public String TO_NUMBER(BigDecimal number, int displayType)
      Return number as string for INSERT statements with correct precision
      Specified by:
      TO_NUMBER in interface AdempiereDatabase
      Parameters:
      number - number
      displayType - display Type
      Returns:
      number as string
    • getJSONCast

      public String getJSONCast()
      Specified by:
      getJSONCast in interface AdempiereDatabase
      Returns:
      string with right casting for JSON inserts
    • TO_JSON

      public String TO_JSON(String value)
      Return string as JSON object for INSERT statements
      Specified by:
      TO_JSON in interface AdempiereDatabase
      Parameters:
      value -
      Returns:
      value as json
    • getCommands

      public String[] getCommands(int cmdType)
      Get SQL Commands
      Specified by:
      getCommands in interface AdempiereDatabase
      Parameters:
      cmdType - CMD_*
      Returns:
      array of commands to be executed
    • getRowSet

      public RowSet getRowSet(ResultSet rs) throws SQLException
      Get RowSet
      Parameters:
      rs - ResultSet
      Returns:
      RowSet
      Throws:
      SQLException
    • getCachedConnection

      public Connection getCachedConnection(CConnection connection, boolean autoCommit, int transactionIsolation) throws Exception
      Get Cached Connection
      Specified by:
      getCachedConnection in interface AdempiereDatabase
      Parameters:
      connection - connection
      autoCommit - auto commit
      transactionIsolation - trx isolation
      Returns:
      Connection
      Throws:
      Exception
    • getDataSource

      public DataSource getDataSource(CConnection connection)
      Create DataSource (Client)
      Specified by:
      getDataSource in interface AdempiereDatabase
      Parameters:
      connection - connection
      Returns:
      data dource
    • createPoolDataSource

      public ConnectionPoolDataSource createPoolDataSource(CConnection connection)
      Create Pooled DataSource (Server)
      Parameters:
      connection - connection
      Returns:
      data source
    • getDriverConnection

      public Connection getDriverConnection(CConnection connection) throws SQLException
      Get Connection from Driver
      Specified by:
      getDriverConnection in interface AdempiereDatabase
      Parameters:
      connection - info
      Returns:
      connection or null
      Throws:
      SQLException
    • getDriverConnection

      public Connection getDriverConnection(String dbUrl, String dbUid, String dbPwd) throws SQLException
      Get Driver Connection
      Specified by:
      getDriverConnection in interface AdempiereDatabase
      Parameters:
      dbUrl - URL
      dbUid - user
      dbPwd - password
      Returns:
      connection
      Throws:
      SQLException
    • close

      public void close()
      Close
      Specified by:
      close in interface AdempiereDatabase
    • getAlternativeSQL

      public String getAlternativeSQL(int reExNo, String msg, String sql)
      Check and generate an alternative SQL
      Specified by:
      getAlternativeSQL in interface AdempiereDatabase
      Parameters:
      reExNo - number of re-execution
      msg - previous execution error message
      sql - previous executed SQL
      Returns:
      String, the alternative SQL, null if no alternative
    • getConstraintType

      public String getConstraintType(Connection conn, String tableName, String IXName)
      Get constraint type associated with the index
      Specified by:
      getConstraintType in interface AdempiereDatabase
      Parameters:
      conn - connection
      tableName - table name
      IXName - Index name
      Returns:
      String[0] = 0: do not know, 1: Primary Key 2: Foreign Key String[1] - String[n] = Constraint Name
    • isSupported

      public boolean isSupported(String sql)
      Check if DBMS support the sql statement
      Specified by:
      isSupported in interface AdempiereDatabase
      Parameters:
      sql - SQL statement
      Returns:
      true: yes
    • dumpLocks

      public static void dumpLocks(Connection conn)
      Dump table lock info to console for current transaction
      Parameters:
      conn -
    • getNextID

      public int getNextID(String name)
      Description copied from interface: AdempiereDatabase
      Get next sequence number in this Sequence
      Specified by:
      getNextID in interface AdempiereDatabase
      Parameters:
      name - Sequence name
    • getNextID

      public int getNextID(String name, String trxName)
      Description copied from interface: AdempiereDatabase
      Get next sequence number in this Sequence
      Specified by:
      getNextID in interface AdempiereDatabase
      Parameters:
      name - Sequence name
      trxName - Transaction name
    • createSequence

      public boolean createSequence(String name, int increment, int minvalue, int maxvalue, int start, String trxName)
      Description copied from interface: AdempiereDatabase
      Create Native Sequence
      Specified by:
      createSequence in interface AdempiereDatabase
      Parameters:
      name - Sequence Name
    • isQueryTimeoutSupported

      public boolean isQueryTimeoutSupported()
      Postgresql jdbc driver doesn't support setQueryTimeout, query/statement timeout is implemented using the "SET LOCAL statement_timeout TO" statement.
      Specified by:
      isQueryTimeoutSupported in interface AdempiereDatabase
      Returns:
      true
    • addPagingSQL

      public String addPagingSQL(String sql, int start, int end)
      Implemented using the fetch first and offset feature. use 1 base index for start and end parameter
      Specified by:
      addPagingSQL in interface AdempiereDatabase
      Parameters:
      sql -
      start -
      end -
      Returns:
      SQL with added paging clause
    • isPagingSupported

      public boolean isPagingSupported()
      Description copied from interface: AdempiereDatabase
      Is the database have sql extension that return a subset of the query result
      Specified by:
      isPagingSupported in interface AdempiereDatabase
      Returns:
      true if DB support paging SQL
    • forUpdate

      public boolean forUpdate(PO po, int timeout)
      Description copied from interface: AdempiereDatabase
      Lock PO for update
      Specified by:
      forUpdate in interface AdempiereDatabase
      Returns:
      true if lock is granted
    • getNameOfUniqueConstraintError

      public String getNameOfUniqueConstraintError(Exception e)
      Get the name of the unique constraint name based on a postgresql message This method works for English, Spanish and German The foreign key constraint name is expected to be found in the second quoted string English quotes -> "constraint" Spanish quotes -> «constraint» German quotes -> »constraint«
      Specified by:
      getNameOfUniqueConstraintError in interface AdempiereDatabase
      Returns:
      unique constraint name
    • getForeignKeyConstraint

      public String getForeignKeyConstraint(Exception e)
      Get the foreign key constraint name based on a postgresql message This method works for English, Spanish and German The foreign key constraint name is expected to be found in the second quoted string English quotes -> "constraint" Spanish quotes -> «constraint» German quotes -> »constraint«
      Specified by:
      getForeignKeyConstraint in interface AdempiereDatabase
      Returns:
      constraint name
    • subsetClauseForCSV

      public String subsetClauseForCSV(String columnName, String csv)
      Specified by:
      subsetClauseForCSV in interface AdempiereDatabase
      csv - comma separated value
      Returns:
      subset sql clause
    • quoteColumnName

      public String quoteColumnName(String columnName)
      Description copied from interface: AdempiereDatabase
      Quote column name if necessary (usually to avoid conflict with reserved keywords)
      Specified by:
      quoteColumnName in interface AdempiereDatabase
      Returns:
      columnName or quoted columnName
    • intersectClauseForCSV

      public String intersectClauseForCSV(String columnName, String csv)
      Specified by:
      intersectClauseForCSV in interface AdempiereDatabase
      csv - comma separated value
      Returns:
      intersect sql clause
    • intersectClauseForCSV

      public String intersectClauseForCSV(String columnName, String csv, boolean isNotClause)
      Specified by:
      intersectClauseForCSV in interface AdempiereDatabase
      csv - comma separated value
      Returns:
      intersect sql clause
    • isNativeMode

      public boolean isNativeMode()
      Specified by:
      isNativeMode in interface AdempiereDatabase
      Returns:
      true if using native dialect, false if using oracle dialect
    • isUseNativeDialect

      public static final boolean isUseNativeDialect()
      Returns:
      true if it is using native dialect
    • markNativeKeyword

      public static final String markNativeKeyword(String keyword)
      Parameters:
      keyword -
      Returns:
      if not using native dialect, return native_marker + keyword
    • removeNativeKeyworkMarker

      public static final String removeNativeKeyworkMarker(String statement)
      Parameters:
      statement -
      Returns:
      statement after the removal of native keyword marker
    • getNumericDataType

      public String getNumericDataType()
      Specified by:
      getNumericDataType in interface AdempiereDatabase
      Returns:
      numeric data type name
    • getCharacterDataType

      public String getCharacterDataType()
      Specified by:
      getCharacterDataType in interface AdempiereDatabase
      Returns:
      fixed length character data type name
    • getVarcharDataType

      public String getVarcharDataType()
      Specified by:
      getVarcharDataType in interface AdempiereDatabase
      Returns:
      variable length character data type name
    • getBlobDataType

      public String getBlobDataType()
      Specified by:
      getBlobDataType in interface AdempiereDatabase
      Returns:
      binary large object data type name
    • getClobDataType

      public String getClobDataType()
      Specified by:
      getClobDataType in interface AdempiereDatabase
      Returns:
      character large object data type name
    • getJsonDataType

      public String getJsonDataType()
      Specified by:
      getJsonDataType in interface AdempiereDatabase
      Returns:
      json object data type name
    • getTimestampDataType

      public String getTimestampDataType()
      Specified by:
      getTimestampDataType in interface AdempiereDatabase
      Returns:
      time stamp data type name
    • getTimestampWithTimezoneDataType

      public String getTimestampWithTimezoneDataType()
      Specified by:
      getTimestampWithTimezoneDataType in interface AdempiereDatabase
      Returns:
      timestamp with time zone type name
    • getSQLDDL

      public String getSQLDDL(MColumn column)
      Specified by:
      getSQLDDL in interface AdempiereDatabase
      Returns:
      DDL SQL statement for column
    • getSQLAdd

      public String getSQLAdd(MTable table, MColumn column)
      Get SQL Add command
      Specified by:
      getSQLAdd in interface AdempiereDatabase
      Parameters:
      table - table
      Returns:
      sql
    • getSQLModify

      public String getSQLModify(MTable table, MColumn column, boolean setNullOption)
      Get SQL Modify command
      Specified by:
      getSQLModify in interface AdempiereDatabase
      Parameters:
      table - table
      setNullOption - generate null / not null statement
      Returns:
      sql separated by ;
    • isQueryTimeout

      public boolean isQueryTimeout(SQLException ex)
      Specified by:
      isQueryTimeout in interface AdempiereDatabase
      Returns:
      true if ex is caused by query timeout
    • getTablePartitionService

      public ITablePartitionService getTablePartitionService()
      Description copied from interface: AdempiereDatabase
      Get DB specific table partition support
      Specified by:
      getTablePartitionService in interface AdempiereDatabase
      Returns:
      ITablePartitionService instance
    • TO_Blob

      public String TO_Blob(byte[] blob)
      Description copied from interface: AdempiereDatabase
      Convert blob to hex encoded string and return SQL function that will convert the hex encoded string back to blob
      Specified by:
      TO_Blob in interface AdempiereDatabase
      Returns:
      SQL statement