com.continuent.tungsten.replicator.database
Class MySQLDatabase

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

All Implemented Interfaces:

Database

Direct Known Subclasses:

MySQLDrizzleDatabase

public class MySQLDatabase
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

MySQLDatabase()

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 MySQL database, which includes setting the wait_timeout to a very high value so we don't lose our connection.
void	connect(boolean binlog) Connect to a MySQL database, which includes setting the wait_timeout to a very high value so we don't lose our connection.
void	controlSessionLevelLogging(boolean suppressed) Sets session-level logging of updates.
void	createSchema(java.lang.String schema) Creates the named schema.
void	createTable(Table t, boolean replace) This should not be called for MySQL but we have a version of it anyway because it's better not to have broken code.
void	createTable(Table t, boolean replace, java.lang.String tableType) Stub routine that ignores table type.
void	createUser(User user) Creates a user that can connect from any location.
void	dropSchema(java.lang.String schema) Drops the named schema.
void	dropUser(User user, boolean ignore) Drops user, ignoring errors if desired by caller.
java.sql.ResultSet	getColumnsResultSet(java.sql.DatabaseMetaData md, java.lang.String schemaName, java.lang.String tableName) This function should be implemented in concrete class.
java.lang.String	getControlTimestampQuery(java.lang.Long timestamp) MySQL supports the 'set timestamp' command, which is what we return.
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 a list of schemas available on the server.
java.lang.String	getSessionVariable(java.lang.String name) Gets a variable on the current session.
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.
void	kill(Session session) Issue a KILL command to remove a particular session.
java.util.List<Session>	listSessions() Issue SHOW PROCESSLIST command to get a list of all currently available sessions.
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.
java.lang.String	prepareOptionSetStatement(java.lang.String optionName, java.lang.String optionValue) prepareOptionSetStatement generates the sql statement that is to be used to set an option (or a session variable) at the database connection level.
void	setSessionVariable(java.lang.String name, java.lang.String value) Sets a variable on the current session using MySQL SET command.
protected boolean	supportsCharset(java.lang.String tableType)
boolean	supportsControlSessionLevelLogging() Returns true if this implementation allow clients to turn logging of SQL updates on and off at the session level.
boolean	supportsControlTimestamp() By default we do not support controlling the timestamp.
boolean	supportsCreateDropSchema() Returns false by default as only some database types allow schema to be created dynamically.
boolean	supportsNativeSlaveSync() Returns true if this implementation supports synchronization of native slave replication with Tungsten position.
protected boolean	supportsNotNull(java.lang.String tableType)
protected boolean	supportsPrimaryKeys(java.lang.String tableType)
boolean	supportsReplace() Returns true if the implementation supports a SQL REPLACE command.
boolean	supportsSessionVariables() MySQL supports session variables.
boolean	supportsUseDefaultSchema() Returns false by default as only some database types allow schema to change.
boolean	supportsUserManagement() Returns true if this implementation supports user management commands.
void	syncNativeSlave(java.lang.String eventId) Synchronize the native slave position with Tungsten so that native replication can start properly after Tungsten goes offline.
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, createStatement, createTable, createTable, delete, disconnect, dropTable, dropTungstenCatalog, execute, executeUpdate, findTable, findTable, findTable, getBlobAsBytes, getConnection, getDatabaseMetaData, getReservedWords, getTables, getType, insert, isPrivileged, javaSQLTypeToNativeType, nativeTypeToJavaSQLType, prepareStatement, replace, rollback, setAutoCommit, setPassword, setPrivileged, setUrl, setUser, update

Methods inherited from class java.lang.Object

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

Constructor Detail

MySQLDatabase

public MySQLDatabase()
              throws java.sql.SQLException

Throws:

java.sql.SQLException

Method Detail

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 MySQL database, which includes setting the wait_timeout to a very high value so we don't lose our connection. 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 MySQL database, which includes setting the wait_timeout to a very high value so we don't lose our connection. 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)

createTable

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

This should not be called for MySQL but we have a version of it anyway because it's better not to have broken code. This will default to the default engine type.

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:

Database.createTable(com.continuent.tungsten.replicator.database.Table, 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

Description copied from class: AbstractDatabase

Creates the named schema.

Specified by:

createSchema in interface Database

Overrides:

createSchema in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

Database.createSchema(java.lang.String)

dropSchema

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

Description copied from class: AbstractDatabase

Drops the named schema.

Specified by:

dropSchema in interface Database

Overrides:

dropSchema in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

Database.dropSchema(java.lang.String)

supportsControlSessionLevelLogging

public boolean supportsControlSessionLevelLogging()

Returns true if this implementation allow clients to turn logging of SQL updates on and off at the session level. (Currently only MySQL supports this feature.)

Specified by:

supportsControlSessionLevelLogging in interface Database

Overrides:

supportsControlSessionLevelLogging in class AbstractDatabase

See Also:

Database.supportsControlSessionLevelLogging()

controlSessionLevelLogging

public void controlSessionLevelLogging(boolean suppressed)
                                throws java.sql.SQLException

Sets session-level logging of updates.

Specified by:

controlSessionLevelLogging in interface Database

Overrides:

controlSessionLevelLogging in class AbstractDatabase

Parameters:

suppressed - If true, updates are not logged; otherwise logging is turned on

Throws:

java.sql.SQLException

See Also:

Database.controlSessionLevelLogging(boolean)

supportsNativeSlaveSync

public boolean supportsNativeSlaveSync()

Returns true if this implementation supports synchronization of native slave replication with Tungsten position. (Currently only MySQL supports this feature.)

Specified by:

supportsNativeSlaveSync in interface Database

Overrides:

supportsNativeSlaveSync in class AbstractDatabase

See Also:

AbstractDatabase.supportsNativeSlaveSync()

syncNativeSlave

public void syncNativeSlave(java.lang.String eventId)
                     throws java.sql.SQLException

Synchronize the native slave position with Tungsten so that native replication can start properly after Tungsten goes offline.

Specified by:

syncNativeSlave in interface Database

Overrides:

syncNativeSlave in class AbstractDatabase

Parameters:

eventId - Tungsten event ID containing native log coordinates

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.syncNativeSlave(java.lang.String)

supportsControlTimestamp

public boolean supportsControlTimestamp()

Description copied from class: AbstractDatabase

By default we do not support controlling the timestamp. Returns true if this implementation supports changing the timestamp value used by functions that return the current time.

Specified by:

supportsControlTimestamp in interface Database

Overrides:

supportsControlTimestamp in class AbstractDatabase

See Also:

Database.supportsControlTimestamp()

getControlTimestampQuery

public java.lang.String getControlTimestampQuery(java.lang.Long timestamp)

MySQL supports the 'set timestamp' command, which is what we return.

Specified by:

getControlTimestampQuery in interface Database

Overrides:

getControlTimestampQuery in class AbstractDatabase

Parameters:

timestamp - Time in milliseconds according to Java standard

See Also:

AbstractDatabase.supportsControlTimestamp()

supportsSessionVariables

public boolean supportsSessionVariables()

MySQL supports session variables.

Specified by:

supportsSessionVariables in interface Database

Overrides:

supportsSessionVariables in class AbstractDatabase

See Also:

Database.supportsSessionVariables()

setSessionVariable

public void setSessionVariable(java.lang.String name,
                               java.lang.String value)
                        throws java.sql.SQLException

Sets a variable on the current session using MySQL SET command. Sets a variable on the current session. Sets a session variable. This works only if the database type supports session variables.

Specified by:

setSessionVariable in interface Database

Overrides:

setSessionVariable in class AbstractDatabase

Parameters:

name - Name of the variable to set

value - Value to set

Throws:

java.sql.SQLException - Thrown if setting variable is unsuccessful

See Also:

Database.setSessionVariable(java.lang.String, java.lang.String)

getSessionVariable

public java.lang.String getSessionVariable(java.lang.String name)
                                    throws java.sql.SQLException

Gets a variable on the current session.

Specified by:

getSessionVariable in interface Database

Overrides:

getSessionVariable in class AbstractDatabase

Parameters:

name - Name of the variable to get

Returns:

Value of variable or null if unset

Throws:

java.sql.SQLException - Thrown if getting variable is unsuccessful

See Also:

Database.getSessionVariable(java.lang.String)

getSchemas

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

Description copied from interface: Database

Returns a list of schemas available on the server.

Returns:

list of available schemas

Throws:

java.sql.SQLException

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

prepareOptionSetStatement

public java.lang.String prepareOptionSetStatement(java.lang.String optionName,
                                                  java.lang.String optionValue)

prepareOptionSetStatement generates the sql statement that is to be used to set an option (or a session variable) at the database connection level.

Specified by:

prepareOptionSetStatement in interface Database

Overrides:

prepareOptionSetStatement in class AbstractDatabase

Parameters:

optionName - the option to be set

optionValue - the value to be used

Returns:

a string that contains the statement that should be executed, or null if this does not exist for a database

See Also:

AbstractDatabase.prepareOptionSetStatement(java.lang.String, java.lang.String)

createTable

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

Stub routine that ignores table type. MySQL databases must override this.

Specified by:

createTable in interface Database

Overrides:

createTable in class AbstractDatabase

Parameters:

t - Table specification

replace - If true, replace an existing table

tableType - table type to be used for tungsten catalog tables

Throws:

java.sql.SQLException

See Also:

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

supportsPrimaryKeys

protected boolean supportsPrimaryKeys(java.lang.String tableType)

supportsCharset

protected boolean supportsCharset(java.lang.String tableType)

supportsNotNull

protected boolean supportsNotNull(java.lang.String tableType)

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)

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

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)

supportsUserManagement

public boolean supportsUserManagement()

Returns true if this implementation supports user management commands.

Specified by:

supportsUserManagement in interface Database

Overrides:

supportsUserManagement in class AbstractDatabase

See Also:

AbstractDatabase.supportsUserManagement()

createUser

public void createUser(User user)
                throws java.sql.SQLException

Creates a user that can connect from any location. If the user is a superuser, grant all on *.*, otherwise just grant select.

Specified by:

createUser in interface Database

Overrides:

createUser in class AbstractDatabase

Parameters:

user - User definition

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.createUser(com.continuent.tungsten.replicator.database.User)

dropUser

public void dropUser(User user,
                     boolean ignore)
              throws java.sql.SQLException

Drops user, ignoring errors if desired by caller.

Specified by:

dropUser in interface Database

Overrides:

dropUser in class AbstractDatabase

Parameters:

user - User definition

ignore - If true, ignore error

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.dropUser(com.continuent.tungsten.replicator.database.User, boolean)

listSessions

public java.util.List<Session> listSessions()
                                     throws java.sql.SQLException

Issue SHOW PROCESSLIST command to get a list of all currently available sessions.

Specified by:

listSessions in interface Database

Overrides:

listSessions in class AbstractDatabase

Throws:

java.sql.SQLException

See Also:

AbstractDatabase.listSessions()

kill

public void kill(Session session)
          throws java.sql.SQLException,
                 ReplicatorException

Issue a KILL command to remove a particular session.

Specified by:

kill in interface Database

Overrides:

kill in class AbstractDatabase

Throws:

java.sql.SQLException

ReplicatorException

See Also:

AbstractDatabase.kill(com.continuent.tungsten.replicator.database.Session)