com.continuent.tungsten.replicator.database
Class PostgreSQLDatabase

java.lang.Object
  com.continuent.tungsten.replicator.database.AbstractDatabase
      com.continuent.tungsten.replicator.database.PostgreSQLDatabase

All Implemented Interfaces:

Database

Direct Known Subclasses:

VerticaDatabase

public class PostgreSQLDatabase
extends AbstractDatabase

Implements DBMS-specific operations for MySQL.

Author:

Scott Martin

Field Summary

Fields inherited from class com.continuent.tungsten.replicator.database.AbstractDatabase

autoCommit, connected, dbConn, dbDriver, dbms, dbPassword, dbUri, dbUser, defaultSchema, drivers, privileged

Fields inherited from interface com.continuent.tungsten.replicator.database.Database

MYSQL, ORACLE, POSTGRESQL, UNKNOWN

Constructor Summary

PostgreSQLDatabase()

Method Summary

protected java.lang.String	columnToTypeString(Column c, java.lang.String tableType) Return a properly constructed type specification for the column.
void	connect() Connect to a PostgreSQL database.
void	connect(boolean binlog) Connect to a PostgreSQL database.
void	createSchema(java.lang.String schema) Checks whether the schema exists and, if not, creates it.
void	createTable(Table t, boolean replace) Creates a table using the supplied table definition.
void	dropSchema(java.lang.String schema) Checks whether the schema exists and, if it does, drops it.
void	dropTable(Table table) Drops an existing table.
byte[]	getBlobAsBytes(java.sql.ResultSet resultSet, int column) Fetches the given column as a byte[] array.
java.sql.ResultSet	getColumnsResultSet(java.sql.DatabaseMetaData md, java.lang.String schemaName, java.lang.String tableName) This function should be implemented in concrete class.
CsvWriter	getCsvWriter(java.io.BufferedWriter writer) Returns a properly configured CsvWriter to generate CSV according to the preferred conventions of this DBMS type.
java.lang.String	getDatabaseObjectName(java.lang.String name) Returns the eventually quoted database object name.
java.lang.String	getNowFunction() getNowFunction returns the database-specific way to get current date and time from the database.
java.lang.String	getPlaceHolder(OneRowChange.ColumnSpec col, java.lang.Object colValue, java.lang.String typeDesc) Return a place holder in a prepared statement for a column of type ColumnSpec.
protected java.sql.ResultSet	getPrimaryKeyResultSet(java.sql.DatabaseMetaData md, java.lang.String schemaName, java.lang.String tableName) This function should be implemented in concrete class.
java.util.ArrayList<java.lang.String>	getSchemas() Returns schemas in the currently connected database.
SqlOperationMatcher	getSqlNameMatcher() Returns a SQL name matcher for this database type.
protected java.sql.ResultSet	getTablesResultSet(java.sql.DatabaseMetaData md, java.lang.String schemaName, boolean baseTablesOnly) This function should be implemented in concrete class.
java.lang.String	getTimeDiff(java.lang.String string1, java.lang.String string2) getTimeDiff returns the database-specific way of subtracting two "dates" and return the result in seconds complete with space for the two bind variables.
java.lang.String	getUseSchemaQuery(java.lang.String schema) Returns a query that can be used to set the schema.
boolean	nullsBoundDifferently(OneRowChange.ColumnSpec col) Return TRUE IFF NULL values are bound differently in SQL statement from non null values for the given column type.
boolean	nullsEverBoundDifferently() return true IFF nulls are sometimes treated differently in nullsBoundDifferently() as non nulls.
boolean	supportsCreateDropSchema() Returns false by default as only some database types allow schema to be created dynamically.
boolean	supportsReplace() Returns true if the implementation supports a SQL REPLACE command.
boolean	supportsSessionVariables() Before using session variables in PostgreSQL one needs to define them in postgresql.conf
boolean	supportsUseDefaultSchema() Returns false by default as only some database types allow schema to change.
protected boolean	tableExists(Table t) Checks whether the given table exists in the currently connected database.
void	useDefaultSchema(java.lang.String schema) Changes the default schema to the named schema.

Methods inherited from class com.continuent.tungsten.replicator.database.AbstractDatabase

close, commit, consistencyCheck, consistencyCheck, controlSessionLevelLogging, createStatement, createTable, createTable, createTable, createUser, delete, disconnect, dropTungstenCatalog, dropUser, execute, executeUpdate, findTable, findTable, findTable, getConnection, getControlTimestampQuery, getDatabaseMetaData, getReservedWords, getSessionVariable, getTables, getType, insert, isPrivileged, javaSQLTypeToNativeType, kill, listSessions, nativeTypeToJavaSQLType, prepareOptionSetStatement, prepareStatement, replace, rollback, setAutoCommit, setPassword, setPrivileged, setSessionVariable, setUrl, setUser, supportsControlSessionLevelLogging, supportsControlTimestamp, supportsNativeSlaveSync, supportsUserManagement, syncNativeSlave, update

Methods inherited from class java.lang.Object

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

Constructor Detail

PostgreSQLDatabase

public PostgreSQLDatabase()
                   throws java.sql.SQLException

Throws:

java.sql.SQLException

Method Detail

getSqlNameMatcher

public SqlOperationMatcher getSqlNameMatcher()
                                      throws ReplicatorException

Returns a SQL name matcher for this database type. You can get a matcher without calling connect() first.

Specified by:

getSqlNameMatcher in interface Database

Specified by:

getSqlNameMatcher in class AbstractDatabase

Throws:

ReplicatorException

See Also:

AbstractDatabase.getSqlNameMatcher()

columnToTypeString

protected java.lang.String columnToTypeString(Column c,
                                              java.lang.String tableType)

Description copied from class: AbstractDatabase

Return a properly constructed type specification for the column. Concrete Database subclasses must implement at least this method if no others.

Specified by:

columnToTypeString in class AbstractDatabase

Parameters:

c - Column for which specification is required

Returns:

String containing specification

connect

public void connect()
             throws java.sql.SQLException

Connect to a PostgreSQL database. Connects to the database. You must set the url, user, and password then do this. Connection does not log queries by default.

Specified by:

connect in interface Database

Overrides:

connect in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.connect()

connect

public void connect(boolean binlog)
             throws java.sql.SQLException

Connect to a PostgreSQL database. Connects to the database. You must set the url, user, and password then do this.

Specified by:

connect in interface Database

Overrides:

connect in class AbstractDatabase

Parameters:

binlog - log connection updates.

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.connect(boolean)

supportsReplace

public boolean supportsReplace()

Description copied from interface: Database

Returns true if the implementation supports a SQL REPLACE command.

Specified by:

supportsReplace in interface Database

Overrides:

supportsReplace in class AbstractDatabase

supportsUseDefaultSchema

public boolean supportsUseDefaultSchema()

Description copied from class: AbstractDatabase

Returns false by default as only some database types allow schema to change.

Specified by:

supportsUseDefaultSchema in interface Database

Overrides:

supportsUseDefaultSchema in class AbstractDatabase

See Also:

Database.supportsUseDefaultSchema()

useDefaultSchema

public void useDefaultSchema(java.lang.String schema)
                      throws java.sql.SQLException

Description copied from class: AbstractDatabase

Changes the default schema to the named schema.

Specified by:

useDefaultSchema in interface Database

Overrides:

useDefaultSchema in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

Database.useDefaultSchema(java.lang.String)

getUseSchemaQuery

public java.lang.String getUseSchemaQuery(java.lang.String schema)

Description copied from class: AbstractDatabase

Returns a query that can be used to set the schema.

Specified by:

getUseSchemaQuery in interface Database

Overrides:

getUseSchemaQuery in class AbstractDatabase

See Also:

Database.getUseSchemaQuery(java.lang.String)

supportsCreateDropSchema

public boolean supportsCreateDropSchema()

Description copied from class: AbstractDatabase

Returns false by default as only some database types allow schema to be created dynamically.

Specified by:

supportsCreateDropSchema in interface Database

Overrides:

supportsCreateDropSchema in class AbstractDatabase

See Also:

Database.supportsCreateDropSchema()

createSchema

public void createSchema(java.lang.String schema)
                  throws java.sql.SQLException

Checks whether the schema exists and, if not, creates it. This mimics MySQLDatabase's behavior "CREATE DATABASE IF NOT EXISTS". Creates the named schema.

Specified by:

createSchema in interface Database

Overrides:

createSchema in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.createSchema(java.lang.String)

dropSchema

public void dropSchema(java.lang.String schema)
                throws java.sql.SQLException

Checks whether the schema exists and, if it does, drops it. This mimics MySQLDatabase's behavior "DROP DATABASE IF EXISTS". Drops the named schema.

Specified by:

dropSchema in interface Database

Overrides:

dropSchema in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.dropSchema(java.lang.String)

tableExists

protected boolean tableExists(Table t)
                       throws java.sql.SQLException

Checks whether the given table exists in the currently connected database. Check is made against "pg_tables".

Returns:

true, if table exists, false, if not.

Throws:

java.sql.SQLException

dropTable

public void dropTable(Table table)

Drops an existing table.

Specified by:

dropTable in interface Database

Overrides:

dropTable in class AbstractDatabase

See Also:

Database.dropTable(com.continuent.tungsten.replicator.database.Table)

createTable

public void createTable(Table t,
                        boolean replace)
                 throws java.sql.SQLException

Creates a table using the supplied table definition.

Specified by:

createTable in interface Database

Overrides:

createTable in class AbstractDatabase

Parameters:

t - Table specification

replace - If true, replace an existing table

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.createTable(com.continuent.tungsten.replicator.database.Table, boolean, java.lang.String)

supportsSessionVariables

public boolean supportsSessionVariables()

Before using session variables in PostgreSQL one needs to define them in postgresql.conf

Specified by:

supportsSessionVariables in interface Database

Overrides:

supportsSessionVariables in class AbstractDatabase

See Also:

Database.supportsSessionVariables()

getSchemas

public java.util.ArrayList<java.lang.String> getSchemas()
                                                 throws java.sql.SQLException

Returns schemas in the currently connected database. Returns a list of schemas available on the server.

Returns:

list of available schemas

Throws:

java.sql.SQLException

See Also:

Database.getSchemas()

getColumnsResultSet

public java.sql.ResultSet getColumnsResultSet(java.sql.DatabaseMetaData md,
                                              java.lang.String schemaName,
                                              java.lang.String tableName)
                                       throws java.sql.SQLException

Description copied from class: AbstractDatabase

This function should be implemented in concrete class.

Specified by:

getColumnsResultSet in interface Database

Specified by:

getColumnsResultSet in class AbstractDatabase

Parameters:

md - DatabaseMetaData object

schemaName - schema name

tableName - table name

Returns:

ResultSet as produced by DatabaseMetaData.getColumns() for a given schema and table

Throws:

java.sql.SQLException

getPrimaryKeyResultSet

protected java.sql.ResultSet getPrimaryKeyResultSet(java.sql.DatabaseMetaData md,
                                                    java.lang.String schemaName,
                                                    java.lang.String tableName)
                                             throws java.sql.SQLException

Description copied from class: AbstractDatabase

This function should be implemented in concrete class.

Specified by:

getPrimaryKeyResultSet in class AbstractDatabase

Parameters:

md - DatabaseMetaData object

schemaName - schema name

tableName - table name

Returns:

ResultSet as produced by DatabaseMetaData.getPrimaryKeys() for a given schema and table

Throws:

java.sql.SQLException

getTablesResultSet

protected java.sql.ResultSet getTablesResultSet(java.sql.DatabaseMetaData md,
                                                java.lang.String schemaName,
                                                boolean baseTablesOnly)
                                         throws java.sql.SQLException

Description copied from class: AbstractDatabase

This function should be implemented in concrete class.

Specified by:

getTablesResultSet in class AbstractDatabase

Parameters:

md - DatabaseMetaData object

schemaName - schema name

baseTablesOnly - If true, return only base tables, not catalogs or views

Returns:

ResultSet as produced by DatabaseMetaData.getTables() for a given schema

Throws:

java.sql.SQLException

getTimeDiff

public java.lang.String getTimeDiff(java.lang.String string1,
                                    java.lang.String string2)

getTimeDiff returns the database-specific way of subtracting two "dates" and return the result in seconds complete with space for the two bind variables. E.g. in MySQL it might be "time_to_sec(timediff(?, ?))". If either of the string variables are null, replace with the bind character (e.g. "?") else use the string given. For example getTimeDiff(null, "myTimeCol") -> time_to_sec(timediff(?, myTimeCol))

getNowFunction

public java.lang.String getNowFunction()

Description copied from interface: Database

getNowFunction returns the database-specific way to get current date and time from the database.

Returns:

the name of the function to be called at the database level to get the current date and time

getPlaceHolder

public java.lang.String getPlaceHolder(OneRowChange.ColumnSpec col,
                                       java.lang.Object colValue,
                                       java.lang.String typeDesc)

Description copied from interface: Database

Return a place holder in a prepared statement for a column of type ColumnSpec. Typically "?" as is INSERT INTO FOO VALUES(?)

Specified by:

getPlaceHolder in interface Database

Overrides:

getPlaceHolder in class AbstractDatabase

nullsBoundDifferently

public boolean nullsBoundDifferently(OneRowChange.ColumnSpec col)

Description copied from interface: Database

Return TRUE IFF NULL values are bound differently in SQL statement from non null values for the given column type. For example, in Oracle, the datatype XML must look like "XMLTYPE(?)" in most SQL statements, but in the case of a NULL value, it would look simply like "?".

Specified by:

nullsBoundDifferently in interface Database

Overrides:

nullsBoundDifferently in class AbstractDatabase

nullsEverBoundDifferently

public boolean nullsEverBoundDifferently()

Description copied from interface: Database

return true IFF nulls are sometimes treated differently in nullsBoundDifferently() as non nulls.

Specified by:

nullsEverBoundDifferently in interface Database

Overrides:

nullsEverBoundDifferently in class AbstractDatabase

getBlobAsBytes

public byte[] getBlobAsBytes(java.sql.ResultSet resultSet,
                             int column)
                      throws java.sql.SQLException

Fetches the given column as a byte[] array. For MySQL and Oracle this means converting BLOB field into byte[] array, while for PostgreSQL - returning the bytea type field's value.

Specified by:

getBlobAsBytes in interface Database

Overrides:

getBlobAsBytes in class AbstractDatabase

Parameters:

resultSet - ResultSet which has this blob column.

column - Index of the column to fetch value from.

Returns:

byte[] array containing underlying BLOB or bytea field value.

Throws:

java.sql.SQLException - As this method is operating on ResultSet, it might throw a SQLException.

See Also:

Database.getBlobAsBytes(ResultSet, int)

getDatabaseObjectName

public java.lang.String getDatabaseObjectName(java.lang.String name)

Returns the eventually quoted database object name. For example, with mysql, database object names should be backticked (`example`).

Specified by:

getDatabaseObjectName in interface Database

Overrides:

getDatabaseObjectName in class AbstractDatabase

Parameters:

name - unquoted database object name

Returns:

eventually quoted database object name

See Also:

AbstractDatabase.getDatabaseObjectName(java.lang.String)

getCsvWriter

public CsvWriter getCsvWriter(java.io.BufferedWriter writer)

Returns a properly configured CsvWriter to generate CSV according to the preferred conventions of this DBMS type.

Parameters:

writer - A buffered writer to receive CSV output

Returns:

A property configured CsvWriter instance

See Also:

Database.getCsvWriter(java.io.BufferedWriter)