org.pentaho.di.core.database
Class MySQLDatabaseMeta

java.lang.Object
  extended by org.pentaho.di.core.database.BaseDatabaseMeta
      extended by org.pentaho.di.core.database.MySQLDatabaseMeta
All Implemented Interfaces:
Cloneable, DatabaseInterface
Direct Known Subclasses:
InfiniDbDatabaseMeta, InfobrightDatabaseMeta

public class MySQLDatabaseMeta
extends BaseDatabaseMeta
implements DatabaseInterface

Contains MySQL specific information through static final members

Since:
11-mrt-2005
Author:
Matt

Field Summary
 
Fields inherited from class org.pentaho.di.core.database.BaseDatabaseMeta
ATTRIBUTE_CLUSTER_DBNAME_PREFIX, ATTRIBUTE_CLUSTER_HOSTNAME_PREFIX, ATTRIBUTE_CLUSTER_PASSWORD_PREFIX, ATTRIBUTE_CLUSTER_PORT_PREFIX, ATTRIBUTE_CLUSTER_USERNAME_PREFIX, ATTRIBUTE_FORCE_IDENTIFIERS_TO_LOWERCASE, ATTRIBUTE_FORCE_IDENTIFIERS_TO_UPPERCASE, ATTRIBUTE_INITIAL_POOL_SIZE, ATTRIBUTE_IS_CLUSTERED, ATTRIBUTE_MAXIMUM_POOL_SIZE, ATTRIBUTE_MSSQL_DOUBLE_DECIMAL_SEPARATOR, ATTRIBUTE_POOLING_PARAMETER_PREFIX, ATTRIBUTE_PORT_NUMBER, ATTRIBUTE_PREFERRED_SCHEMA_NAME, ATTRIBUTE_PREFIX_EXTRA_OPTION, ATTRIBUTE_QUOTE_ALL_FIELDS, ATTRIBUTE_SQL_CONNECT, ATTRIBUTE_SUPPORTS_BOOLEAN_DATA_TYPE, ATTRIBUTE_USE_POOLING, ATTRIBUTE_USE_RESULT_STREAMING, AUTOINCREMENT_SQL_FOR_BATCH_ID, poolingParameters, SELECT_COUNT_STATEMENT, SEQUENCE_FOR_BATCH_ID
 
Constructor Summary
MySQLDatabaseMeta()
           
 
Method Summary
 int[] getAccessTypeList()
          Get the list of possible access types for a database.
 String getAddColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon)
          Generates the SQL statement to add a column to the specified table
 int getDefaultDatabasePort()
           
 String getDriverClass()
          Obtain the name of the JDBC driver class that we need to use!
 String getEndQuote()
          Simply add an underscore in the case of MySQL!
 String getExtraOptionIndicator()
           
 String getExtraOptionSeparator()
           
 String getExtraOptionsHelpText()
           
 String getFieldDefinition(ValueMetaInterface v, String tk, String pk, boolean use_autoinc, boolean add_fieldname, boolean add_cr)
          Describe a Value as a field in the database.
 String getLimitClause(int nrRows)
           
 String getModifyColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon)
          Generates the SQL statement to modify a column in the specified table
 int getNotFoundTK(boolean use_autoinc)
          Get the not found technical key.
 String[] getReservedWords()
           
 String getSQLColumnExists(String columnname, String tablename)
           
 String getSQLInsertAutoIncUnknownDimensionRow(String schemaTable, String keyField, String versionField)
          Get the SQL to insert a new empty unknown record in a dimension.
 String getSQLLockTables(String[] tableNames)
           
 String getSQLQueryColumnFields(String columnname, String tableName)
           
 String getSQLQueryFields(String tableName)
          Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table
 String getSQLTableExists(String tablename)
           
 String getSQLUnlockTables(String[] tableName)
           
 String getStartQuote()
           
 String getURL(String hostname, String port, String databaseName)
           
 String[] getUsedLibraries()
           
 boolean isMySQLVariant()
           
 boolean isSystemTable(String tableName)
           
 boolean needsToLockAllTables()
           
 String quoteSQLString(String string)
           
 boolean releaseSavepoint()
          Returns a false as Oracle does not allow for the releasing of savepoints.
 boolean supportsBitmapIndex()
           
 boolean supportsErrorHandlingOnBatchUpdates()
           
 boolean supportsSynonyms()
           
 boolean supportsTransactions()
           
 boolean supportsViews()
           
 
Methods inherited from class org.pentaho.di.core.database.BaseDatabaseMeta
addExtraOption, checkIndexExists, clone, generateColumnAlias, getAccessType, getAttributes, getBackwardsCompatibleSchemaTableCombination, getBackwardsCompatibleTable, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getDropColumnStatement, getExtraOptions, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getNextBatchIdUsingAutoIncSQL, getNextBatchIdUsingLockTables, getNextBatchIdUsingSequence, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSchemaTableCombination, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSchemas, getSQLListOfSequences, getSQLNextSequenceValue, getSQLSequenceExists, getSynonymTypes, getTablespaceDDL, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, parseStatements, quoteReservedWords, requiresCastToVariousForIsNull, requiresCreateTablePrimaryKeyAppend, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsAutoInc, supportsBatchUpdates, supportsBooleanDataType, supportsCatalogs, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSchemas, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, useSafePoints, useSchemaNameForTableList
 
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 
Methods inherited from interface org.pentaho.di.core.database.DatabaseInterface
addExtraOption, checkIndexExists, clone, generateColumnAlias, getAccessType, getAttributes, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getDropColumnStatement, getExtraOptions, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSchemaTableCombination, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSchemas, getSQLListOfSequences, getSQLNextSequenceValue, getSQLSequenceExists, getSynonymTypes, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, parseStatements, quoteReservedWords, requiresCastToVariousForIsNull, requiresCreateTablePrimaryKeyAppend, setAccessType, setAttributes, setChanged, setConnectionPoolingProperties, setConnectSQL, setDatabaseName, setDatabasePortNumberString, setDataTablespace, setForcingIdentifiersToLowerCase, setForcingIdentifiersToUpperCase, setHostname, setIndexTablespace, setInitialPoolSize, setMaximumPoolSize, setName, setObjectId, setPartitioned, setPartitioningInformation, setPassword, setPluginId, setPluginName, setPreferredSchemaName, setQuoteAllFields, setServername, setStreamingResults, setSupportsBooleanDataType, setUsername, setUsingConnectionPool, setUsingDoubleDecimalAsSchemaTableSeparator, supportsAutoInc, supportsBatchUpdates, supportsBooleanDataType, supportsCatalogs, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSchemas, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, useSafePoints, useSchemaNameForTableList
 

Constructor Detail

MySQLDatabaseMeta

public MySQLDatabaseMeta()
Method Detail

getAccessTypeList

public int[] getAccessTypeList()
Description copied from interface: DatabaseInterface
Get the list of possible access types for a database.

Specified by:
getAccessTypeList in interface DatabaseInterface
Specified by:
getAccessTypeList in class BaseDatabaseMeta
Returns:
the list of possible access types for a database.

getDefaultDatabasePort

public int getDefaultDatabasePort()
Specified by:
getDefaultDatabasePort in interface DatabaseInterface
Overrides:
getDefaultDatabasePort in class BaseDatabaseMeta
Returns:
the default database port number

getLimitClause

public String getLimitClause(int nrRows)
Specified by:
getLimitClause in interface DatabaseInterface
Overrides:
getLimitClause in class BaseDatabaseMeta
Parameters:
nrRows - The number of rows to which we want to limit the result of the query.
Returns:
the clause after a select statement to limit the number of rows

getSQLQueryFields

public String getSQLQueryFields(String tableName)
Returns the minimal SQL to launch in order to determine the layout of the resultset for a given database table

Specified by:
getSQLQueryFields in interface DatabaseInterface
Overrides:
getSQLQueryFields in class BaseDatabaseMeta
Parameters:
tableName - The name of the table to determine the layout for
Returns:
The SQL to launch.

getSQLTableExists

public String getSQLTableExists(String tablename)
Specified by:
getSQLTableExists in interface DatabaseInterface
Overrides:
getSQLTableExists in class BaseDatabaseMeta
Parameters:
tablename - The table to verify the existance for
Returns:
The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the table.

getSQLColumnExists

public String getSQLColumnExists(String columnname,
                                 String tablename)
Specified by:
getSQLColumnExists in interface DatabaseInterface
Overrides:
getSQLColumnExists in class BaseDatabaseMeta
tablename - The table to verify the existance for
Returns:
The SQL to execute to verify if the given table exists. If an Exception is thrown for this SQL, we don't have the column.

getSQLQueryColumnFields

public String getSQLQueryColumnFields(String columnname,
                                      String tableName)

getNotFoundTK

public int getNotFoundTK(boolean use_autoinc)
Description copied from interface: DatabaseInterface
Get the not found technical key.

Specified by:
getNotFoundTK in interface DatabaseInterface
Overrides:
getNotFoundTK in class BaseDatabaseMeta
Parameters:
use_autoinc - Whether or not we want to use an auto increment field
Returns:
the lowest possible technical key to be used as the NOT FOUND row in a slowly changing dimension.
See Also:
DatabaseInterface.getNotFoundTK(boolean)

getDriverClass

public String getDriverClass()
Description copied from interface: DatabaseInterface
Obtain the name of the JDBC driver class that we need to use!

Specified by:
getDriverClass in interface DatabaseInterface
Returns:
the name of the JDBC driver class for the specific database

getURL

public String getURL(String hostname,
                     String port,
                     String databaseName)
Specified by:
getURL in interface DatabaseInterface
Parameters:
hostname - the hostname
port - the port as a string
databaseName - the database name
Returns:
the URL to use for connecting to the database.

getExtraOptionSeparator

public String getExtraOptionSeparator()
Specified by:
getExtraOptionSeparator in interface DatabaseInterface
Overrides:
getExtraOptionSeparator in class BaseDatabaseMeta
Returns:
The extra option separator in database URL for this platform (usually this is semicolon ; )

getExtraOptionIndicator

public String getExtraOptionIndicator()
Specified by:
getExtraOptionIndicator in interface DatabaseInterface
Overrides:
getExtraOptionIndicator in class BaseDatabaseMeta
Returns:
This indicator separates the normal URL from the options

supportsTransactions

public boolean supportsTransactions()
Specified by:
supportsTransactions in interface DatabaseInterface
Overrides:
supportsTransactions in class BaseDatabaseMeta
Returns:
true if the database supports transactions.

supportsBitmapIndex

public boolean supportsBitmapIndex()
Specified by:
supportsBitmapIndex in interface DatabaseInterface
Overrides:
supportsBitmapIndex in class BaseDatabaseMeta
Returns:
true if the database supports bitmap indexes

supportsViews

public boolean supportsViews()
Specified by:
supportsViews in interface DatabaseInterface
Overrides:
supportsViews in class BaseDatabaseMeta
Returns:
true if the database supports views

supportsSynonyms

public boolean supportsSynonyms()
Specified by:
supportsSynonyms in interface DatabaseInterface
Overrides:
supportsSynonyms in class BaseDatabaseMeta
Returns:
true if the database supports synonyms

getAddColumnStatement

public String getAddColumnStatement(String tablename,
                                    ValueMetaInterface v,
                                    String tk,
                                    boolean use_autoinc,
                                    String pk,
                                    boolean semicolon)
Generates the SQL statement to add a column to the specified table

Specified by:
getAddColumnStatement in interface DatabaseInterface
Parameters:
tablename - The table to add
v - The column defined as a value
tk - the name of the technical key field
use_autoinc - whether or not this field uses auto increment
pk - the name of the primary key field
semicolon - whether or not to add a semi-colon behind the statement.
Returns:
the SQL statement to add a column to the specified table

getModifyColumnStatement

public String getModifyColumnStatement(String tablename,
                                       ValueMetaInterface v,
                                       String tk,
                                       boolean use_autoinc,
                                       String pk,
                                       boolean semicolon)
Generates the SQL statement to modify a column in the specified table

Specified by:
getModifyColumnStatement in interface DatabaseInterface
Parameters:
tablename - The table to add
v - The column defined as a value
tk - the name of the technical key field
use_autoinc - whether or not this field uses auto increment
pk - the name of the primary key field
semicolon - whether or not to add a semi-colon behind the statement.
Returns:
the SQL statement to modify a column in the specified table

getFieldDefinition

public String getFieldDefinition(ValueMetaInterface v,
                                 String tk,
                                 String pk,
                                 boolean use_autoinc,
                                 boolean add_fieldname,
                                 boolean add_cr)
Description copied from interface: DatabaseInterface
Describe a Value as a field in the database.

Specified by:
getFieldDefinition in interface DatabaseInterface
Parameters:
v - The value to describe
tk - The field that's going to be the technical key
pk - The field that's going to be the primary key
use_autoinc - Use autoincrement or not
add_fieldname - Add the fieldname to the definition or not
add_cr - Add a cariage return at the end of the definition or not.
Returns:
a value described as a field in this database.

getReservedWords

public String[] getReservedWords()
Specified by:
getReservedWords in interface DatabaseInterface
Overrides:
getReservedWords in class BaseDatabaseMeta
Returns:
an array of reserved words for the database type...

getStartQuote

public String getStartQuote()
Specified by:
getStartQuote in interface DatabaseInterface
Overrides:
getStartQuote in class BaseDatabaseMeta
Returns:
The start quote sequence, mostly just double quote, but sometimes [, ...

getEndQuote

public String getEndQuote()
Simply add an underscore in the case of MySQL!

Specified by:
getEndQuote in interface DatabaseInterface
Overrides:
getEndQuote in class BaseDatabaseMeta
Returns:
The end quote sequence, mostly just double quote, but sometimes ], ...
See Also:
DatabaseInterface.getEndQuote()

getSQLLockTables

public String getSQLLockTables(String[] tableNames)
Specified by:
getSQLLockTables in interface DatabaseInterface
Overrides:
getSQLLockTables in class BaseDatabaseMeta
Parameters:
tableNames - The names of the tables to lock
Returns:
The SQL command to lock database tables for write purposes.

getSQLUnlockTables

public String getSQLUnlockTables(String[] tableName)
Specified by:
getSQLUnlockTables in interface DatabaseInterface
Overrides:
getSQLUnlockTables in class BaseDatabaseMeta
Parameters:
tableName - The name of the table to unlock
Returns:
The SQL command to unlock a database table.

needsToLockAllTables

public boolean needsToLockAllTables()
Specified by:
needsToLockAllTables in interface DatabaseInterface
Overrides:
needsToLockAllTables in class BaseDatabaseMeta
Returns:
true if the database needs all repository tables to be locked, not just one ref table (R_REPOSITORY_LOG)

getExtraOptionsHelpText

public String getExtraOptionsHelpText()
Specified by:
getExtraOptionsHelpText in interface DatabaseInterface
Overrides:
getExtraOptionsHelpText in class BaseDatabaseMeta
Returns:
extra help text on the supported options on the selected database platform.

getUsedLibraries

public String[] getUsedLibraries()
Specified by:
getUsedLibraries in interface DatabaseInterface
Returns:
the required libraries (in libext) for this database connection.

isSystemTable

public boolean isSystemTable(String tableName)
Specified by:
isSystemTable in interface DatabaseInterface
Overrides:
isSystemTable in class BaseDatabaseMeta
Parameters:
tableName -
Returns:
true if the specified table is a system table

getSQLInsertAutoIncUnknownDimensionRow

public String getSQLInsertAutoIncUnknownDimensionRow(String schemaTable,
                                                     String keyField,
                                                     String versionField)
Get the SQL to insert a new empty unknown record in a dimension.

Specified by:
getSQLInsertAutoIncUnknownDimensionRow in interface DatabaseInterface
Overrides:
getSQLInsertAutoIncUnknownDimensionRow in class BaseDatabaseMeta
Parameters:
schemaTable - the schema-table name to insert into
keyField - The key field
versionField - the version field
Returns:
the SQL to insert the unknown record into the SCD.

quoteSQLString

public String quoteSQLString(String string)
Specified by:
quoteSQLString in interface DatabaseInterface
Overrides:
quoteSQLString in class BaseDatabaseMeta
Parameters:
string -
Returns:
A string that is properly quoted for use in a SQL statement (insert, update, delete, etc)

isMySQLVariant

public boolean isMySQLVariant()
Specified by:
isMySQLVariant in interface DatabaseInterface
Overrides:
isMySQLVariant in class BaseDatabaseMeta
Returns:
true if the database is a MySQL variant, like MySQL 5.1, InfiniDB, InfoBright, and so on.

releaseSavepoint

public boolean releaseSavepoint()
Returns a false as Oracle does not allow for the releasing of savepoints.

Specified by:
releaseSavepoint in interface DatabaseInterface
Overrides:
releaseSavepoint in class BaseDatabaseMeta
Returns:

supportsErrorHandlingOnBatchUpdates

public boolean supportsErrorHandlingOnBatchUpdates()
Specified by:
supportsErrorHandlingOnBatchUpdates in interface DatabaseInterface
Overrides:
supportsErrorHandlingOnBatchUpdates in class BaseDatabaseMeta
Returns:
true if the database supports error handling (recovery of failure) while doing batch updates.