org.pentaho.di.core.database
Class MSSQLServerDatabaseMeta

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

public class MSSQLServerDatabaseMeta
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
MSSQLServerDatabaseMeta()
           
 
Method Summary
 boolean checkIndexExists(Database database, String schemaName, String tableName, String[] idx_fields)
          Verifies on the specified database connection if an index exists on the fields with the specified name.
 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 getDropColumnStatement(String tablename, ValueMetaInterface v, String tk, boolean use_autoinc, String pk, boolean semicolon)
          Generates the SQL statement to drop a column from the specified table
 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 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
 Long getNextBatchIdUsingLockTables(DatabaseMeta dbm, Database ldb, String schemaName, String tableName, String fieldName)
           
 String[] getReservedWords()
           
 String getSchemaTableCombination(String schema_name, String table_part)
          Get the schema-table combination to query the right table.
 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 getSQLListOfProcedures(String schemaName)
           
 String getSQLListOfSchemas()
           
 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 getURL(String hostname, String port, String databaseName)
           
 String[] getUsedLibraries()
           
 String quoteSQLString(String string)
           
 boolean supportsBitmapIndex()
           
 boolean supportsCatalogs()
           
 boolean supportsErrorHandlingOnBatchUpdates()
           
 boolean supportsSchemas()
           
 boolean supportsSynonyms()
           
 boolean useSafePoints()
           
 
Methods inherited from class org.pentaho.di.core.database.BaseDatabaseMeta
addExtraOption, clone, generateColumnAlias, getAccessType, getAttributes, getBackwardsCompatibleSchemaTableCombination, getBackwardsCompatibleTable, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getLimitClause, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getNextBatchIdUsingAutoIncSQL, getNextBatchIdUsingSequence, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSequences, getSQLNextSequenceValue, getSQLSequenceExists, getSQLUnlockTables, getStartQuote, getSynonymTypes, getTablespaceDDL, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, needsToLockAllTables, parseStatements, quoteReservedWords, releaseSavepoint, 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, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, 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, clone, generateColumnAlias, getAccessType, getAttributes, getConnectionPoolingProperties, getConnectSQL, getDatabaseFactoryName, getDatabaseName, getDatabasePortNumberString, getDataTablespace, getDataTablespaceDDL, getEndQuote, getExtraOptionIndicator, getExtraOptions, getExtraOptionSeparator, getExtraOptionValueSeparator, getFunctionAverage, getFunctionCount, getFunctionMaximum, getFunctionMinimum, getFunctionSum, getHostname, getIndexTablespace, getIndexTablespaceDDL, getInitialPoolSize, getLimitClause, getMaxColumnsInIndex, getMaximumPoolSize, getMaxTextFieldLength, getMaxVARCHARLength, getName, getNextBatchId, getNotFoundTK, getObjectId, getPartitioningInformation, getPassword, getPluginId, getPluginName, getPreferredSchemaName, getSelectCountStatement, getServername, getSQLCurrentSequenceValue, getSQLListOfProcedures, getSQLListOfSequences, getSQLNextSequenceValue, getSQLSequenceExists, getSQLUnlockTables, getStartQuote, getSynonymTypes, getTableTypes, getTruncateTableStatement, getUsername, getValueFromResultSet, getViewTypes, getXulOverlayFile, isChanged, isDefaultingToUppercase, isDisplaySizeTwiceThePrecision, isExplorable, isFetchSizeSupported, isForcingIdentifiersToLowerCase, isForcingIdentifiersToUpperCase, isMySQLVariant, isPartitioned, isQuoteAllFields, isRequiringTransactionsOnQueries, isStreamingResults, isSystemTable, isUsingConnectionPool, isUsingDoubleDecimalAsSchemaTableSeparator, needsPlaceHolder, needsToLockAllTables, parseStatements, quoteReservedWords, releaseSavepoint, 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, supportsEmptyTransactions, supportsErrorHandling, supportsFloatRoundingOnUpdate, supportsGetBlob, supportsNewLinesInSQL, supportsOptionsInURL, supportsPreparedStatementMetadataRetrieval, supportsRepository, supportsResultSetMetadataRetrievalOnly, supportsSequenceNoMaxValueOption, supportsSequences, supportsSetCharacterStream, supportsSetLong, supportsSetMaxRows, supportsTimeStampToDateConversion, supportsTransactions, supportsViews, useSchemaNameForTableList
 

Constructor Detail

MSSQLServerDatabaseMeta

public MSSQLServerDatabaseMeta()
Method Detail

supportsCatalogs

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

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

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.

getSchemaTableCombination

public String getSchemaTableCombination(String schema_name,
                                        String table_part)
Description copied from class: BaseDatabaseMeta
Get the schema-table combination to query the right table. Usually that is SCHEMA.TABLENAME, however there are exceptions to this rule...

Specified by:
getSchemaTableCombination in interface DatabaseInterface
Overrides:
getSchemaTableCombination in class BaseDatabaseMeta
Parameters:
schema_name - The schema name
table_part - The tablename
Returns:
the schema-table combination to query the right table.

supportsBitmapIndex

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

supportsSynonyms

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

getSQLQueryFields

public String getSQLQueryFields(String tableName)
Description copied from class: BaseDatabaseMeta
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)

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. null is returned in case locking is not supported on the target database. null is the default value

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

getDropColumnStatement

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

Specified by:
getDropColumnStatement in interface DatabaseInterface
Overrides:
getDropColumnStatement in class BaseDatabaseMeta
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 drop a column from 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.

getSQLListOfProcedures

public String getSQLListOfProcedures(String schemaName)
Parameters:
the - schema name to search in or null if you want to search the whole DB
Returns:
The SQL on this database to get a list of stored procedures.

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

getUsedLibraries

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

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.

checkIndexExists

public boolean checkIndexExists(Database database,
                                String schemaName,
                                String tableName,
                                String[] idx_fields)
                         throws KettleDatabaseException
Verifies on the specified database connection if an index exists on the fields with the specified name.

Specified by:
checkIndexExists in interface DatabaseInterface
Overrides:
checkIndexExists in class BaseDatabaseMeta
Parameters:
database - a connected database
schemaName -
tableName -
idxFields -
Returns:
true if the index exists, false if it doesn't.
Throws:
KettleException
KettleDatabaseException

getSQLListOfSchemas

public String getSQLListOfSchemas()
Specified by:
getSQLListOfSchemas in interface DatabaseInterface
Overrides:
getSQLListOfSchemas in class BaseDatabaseMeta
Returns:
the SQL to retrieve the list of schemas or null if the JDBC metadata needs to be used.

supportsSchemas

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

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 an Oracle SQL statement (insert, update, delete, etc)

getNextBatchIdUsingLockTables

public Long getNextBatchIdUsingLockTables(DatabaseMeta dbm,
                                          Database ldb,
                                          String schemaName,
                                          String tableName,
                                          String fieldName)
                                   throws KettleDatabaseException
Overrides:
getNextBatchIdUsingLockTables in class BaseDatabaseMeta
Throws:
KettleDatabaseException

useSafePoints

public boolean useSafePoints()
Specified by:
useSafePoints in interface DatabaseInterface
Overrides:
useSafePoints in class BaseDatabaseMeta
Returns:
true if the database supports the use of safe-points and if it is appropriate to ever use it (default to false)

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.