/*
* Copyright 2008, Unitils.org
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.unitils.core.dbsupport;
import org.unitils.core.UnitilsException;
import org.unitils.core.util.StoredIdentifierCase;
import static org.unitils.core.util.StoredIdentifierCase.*;
import static org.unitils.thirdparty.org.apache.commons.dbutils.DbUtils.closeQuietly;
import static org.unitils.util.PropertyUtils.getString;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Set;
import org.apache.commons.lang.StringUtils;
/**
* Helper class that implements a number of common operations on a database schema. Operations that can be implemented
* using general JDBC or ANSI SQL constructs, are impelemented in this base abstract class. Operations that are DBMS
* specific are abstract, and their implementation is left to DBMS specific subclasses.
*
* @author Filip Neven
* @author Tim Ducheyne
* @author Frederick Beernaert
*/
abstract public class DbSupport {
/**
* Property key for the default identifier casing (lower_case, upper_case, mixed_case, auto)
*/
public static final String PROPKEY_STORED_IDENTIFIER_CASE = "database.storedIndentifierCase";
/**
* Property key for the default identifier quote string (empty value for not supported, auto)
*/
public static final String PROPKEY_IDENTIFIER_QUOTE_STRING = "database.identifierQuoteString";
/* The name of the DBMS implementation that is supported by this implementation */
private String databaseDialect;
/* The name of the database schema */
private String schemaName;
/* Gives access to the database */
private SQLHandler sqlHandler;
/* Indicates whether database identifiers are stored in lowercase, uppercase or mixed case */
private StoredIdentifierCase storedIdentifierCase;
/* The string that is used to quote identifiers to make them case sensitive, e.g. ", null means quoting not supported*/
private String identifierQuoteString;
/**
* Creates a new, unconfigured instance. To have a instance that can be used, the {@link #init} method must be
* called first.
*
* @param databaseDialect The name of the DBMS implementation that is supported by this implementation, not null
*/
protected DbSupport(String databaseDialect) {
this.databaseDialect = databaseDialect;
}
/**
* Initializes this DbSupport object with the given schemaName and dataSource.
* If the storedIdentifierCase or identifierQuoteString is set to null, the metadata of the connection will be used to determine the
* correct value.
*
* @param configuration The config, not null
* @param sqlHandler The sql handler, not null
* @param schemaName The name of the database schema
*/
public void init(Properties configuration, SQLHandler sqlHandler, String schemaName) {
this.sqlHandler = sqlHandler;
String identifierQuoteStringProperty = getString(PROPKEY_IDENTIFIER_QUOTE_STRING + "." + getDatabaseDialect(), configuration);
String storedIdentifierCaseValue = getString(PROPKEY_STORED_IDENTIFIER_CASE + "." + getDatabaseDialect(), configuration);
this.identifierQuoteString = determineIdentifierQuoteString(identifierQuoteStringProperty);
this.storedIdentifierCase = determineStoredIdentifierCase(storedIdentifierCaseValue);
this.schemaName = toCorrectCaseIdentifier(schemaName);
}
/**
* Gets the database dialect.
*
* @return the supported dialect, not null
*/
public String getDatabaseDialect() {
return databaseDialect;
}
/**
* Gets the schema name.
*
* @return the schema name, not null
*/
public String getSchemaName() {
return schemaName;
}
/**
* Gets the identifier quote string.
*
* @return the quote string, null if not supported
*/
public String getIdentifierQuoteString() {
return identifierQuoteString;
}
/**
* Gets the stored identifier case.
*
* @return the case, not null
*/
public StoredIdentifierCase getStoredIdentifierCase() {
return storedIdentifierCase;
}
/**
* Gets the sql handler.
*
* @return the data source, not null
*/
public SQLHandler getSQLHandler() {
return sqlHandler;
}
/**
* Returns the names of all tables in the database.
*
* @return The names of all tables in the database
*/
public abstract Set<String> getTableNames();
/**
* Gets the names of all columns of the given table.
*
* @param tableName The table, not null
* @return The names of the columns of the table with the given name
*/
public abstract Set<String> getColumnNames(String tableName);
/**
* Retrieves the names of all the views in the database schema.
*
* @return The names of all views in the database
*/
public abstract Set<String> getViewNames();
/**
* Retrieves the names of all materialized views in the database schema.
*
* @return The names of all materialized views in the database
*/
public Set<String> getMaterializedViewNames() {
throw new UnsupportedOperationException("Materialized views not supported for " + getDatabaseDialect());
}
/**
* Retrieves the names of all synonyms in the database schema.
*
* @return The names of all synonyms in the database
*/
public Set<String> getSynonymNames() {
throw new UnsupportedOperationException("Synonyms not supported for " + getDatabaseDialect());
}
/**
* Retrieves the names of all sequences in the database schema.
*
* @return The names of all sequences in the database, not null
*/
public Set<String> getSequenceNames() {
throw new UnsupportedOperationException("Sequences not supported for " + getDatabaseDialect());
}
/**
* Retrieves the names of all triggers in the database schema.
*
* @return The names of all triggers in the database, not null
*/
public Set<String> getTriggerNames() {
throw new UnsupportedOperationException("Triggers not supported for " + getDatabaseDialect());
}
/**
* Retrieves the names of all types in the database schema.
*
* @return The names of all types in the database, not null
*/
public Set<String> getTypeNames() {
throw new UnsupportedOperationException("Types are not supported for " + getDatabaseDialect());
}
/**
* Removes the table with the given name from the database.
* Note: the table name is surrounded with quotes, making it case-sensitive.
*
* @param tableName The table to drop (case-sensitive), not null
*/
public void dropTable(String tableName) {
getSQLHandler().executeUpdate("drop table " + qualified(tableName) + (supportsCascade() ? " cascade" : ""));
}
/**
* Removes the view with the given name from the database
* Note: the view name is surrounded with quotes, making it case-sensitive.
*
* @param viewName The view to drop (case-sensitive), not null
*/
public void dropView(String viewName) {
getSQLHandler().executeUpdate("drop view " + qualified(viewName) + (supportsCascade() ? " cascade" : ""));
}
/**
* Removes the materialized view with the given name from the database
* Note: the view name is surrounded with quotes, making it case-sensitive.
*
* @param viewName The view to drop (case-sensitive), not null
*/
public void dropMaterializedView(String viewName) {
throw new UnsupportedOperationException("Materialized views are not supported for " + getDatabaseDialect());
}
/**
* Removes the synonym with the given name from the database
* Note: the synonym name is surrounded with quotes, making it case-sensitive.
*
* @param synonymName The synonym to drop (case-sensitive), not null
*/
public void dropSynonym(String synonymName) {
getSQLHandler().executeUpdate("drop synonym " + qualified(synonymName));
}
/**
* Drops the sequence with the given name from the database
* Note: the sequence name is surrounded with quotes, making it case-sensitive.
*
* @param sequenceName The sequence to drop (case-sensitive), not null
*/
public void dropSequence(String sequenceName) {
getSQLHandler().executeUpdate("drop sequence " + qualified(sequenceName));
}
/**
* Drops the trigger with the given name from the database
* Note: the trigger name is surrounded with quotes, making it case-sensitive.
*
* @param triggerName The trigger to drop (case-sensitive), not null
*/
public void dropTrigger(String triggerName) {
getSQLHandler().executeUpdate("drop trigger " + qualified(triggerName));
}
/**
* Drops the type with the given name from the database
* Note: the type name is surrounded with quotes, making it case-sensitive.
*
* @param typeName The type to drop (case-sensitive), not null
*/
public void dropType(String typeName) {
getSQLHandler().executeUpdate("drop type " + qualified(typeName) + (supportsCascade() ? " cascade" : ""));
}
/**
* Disables all referential constraints (e.g. foreign keys) on all table in the schema
*/
public abstract void disableReferentialConstraints();
/**
* Disables all value constraints (e.g. not null) on all tables in the schema
*/
public abstract void disableValueConstraints();
/**
* Returns the value of the sequence with the given name.
* <p/>
* Note: this can have the side-effect of increasing the sequence value.
*
* @param sequenceName The sequence, not null
* @return The value of the sequence with the given name
*/
public long getSequenceValue(String sequenceName) {
throw new UnsupportedOperationException("Sequences not supported for " + getDatabaseDialect());
}
/**
* Sets the next value of the sequence with the given sequence name to the given sequence value.
*
* @param sequenceName The sequence, not null
* @param newSequenceValue The value to set
*/
public void incrementSequenceToValue(String sequenceName, long newSequenceValue) {
throw new UnsupportedOperationException("Sequences not supported for " + getDatabaseDialect());
}
/**
* Gets the names of all identity columns of the given table.
*
* @param tableName The table, not null
* @return The names of the identity columns of the table with the given name
*/
public Set<String> getIdentityColumnNames(String tableName) {
throw new UnsupportedOperationException("Identity columns not supported for " + getDatabaseDialect());
}
/**
* Increments the identity value for the specified identity column on the specified table to the given value. If there
* is no identity specified on the given primary key, the method silently finishes without effect.
*
* @param tableName The table with the identity column, not null
* @param identityColumnName The column, not null
* @param identityValue The new value
*/
public void incrementIdentityColumnToValue(String tableName, String identityColumnName, long identityValue) {
throw new UnsupportedOperationException("Identity columns not supported for " + getDatabaseDialect());
}
/**
* Gets the column type suitable to store values of the Java <code>java.lang.Long</code> type.
*
* @return The column type
*/
public String getLongDataType() {
return "BIGINT";
}
/**
* Gets the column type suitable to store text values.
*
* @param length The nr of characters.
* @return The column type, not null
*/
public String getTextDataType(int length) {
return "VARCHAR(" + length + ")";
}
/**
* Qualifies the given database object name with the name of the database schema. Quotes are put around both
* schemaname and object name. If the schemaName is not supplied, the database object is returned surrounded with
* quotes. If the DBMS doesn't support quoted database object names, no quotes are put around neither schema name
* nor database object name.
*
* @param databaseObjectName The database object name to be qualified
* @return The qualified database object name
*/
public String qualified(String databaseObjectName) {
if (!StringUtils.isEmpty(schemaName)) {
return quoted(schemaName) + "." + quoted(databaseObjectName);
}
return databaseObjectName;
}
/**
* Put quotes around the given databaseObjectName, if the underlying DBMS supports quoted database object names.
* If not, the databaseObjectName is returned unchanged.
*
* @param databaseObjectName The name, not null
* @return Quoted version of the given databaseObjectName, if supported by the underlying DBMS
*/
public String quoted(String databaseObjectName) {
if (identifierQuoteString == null) {
return databaseObjectName;
}
return identifierQuoteString + databaseObjectName + identifierQuoteString;
}
/**
* Converts the given identifier to uppercase/lowercase depending on the DBMS. If a value is surrounded with double
* quotes (") and the DBMS supports quoted database object names, the case is left untouched and the double quotes
* are stripped. These values are treated as case sensitive names.
* <p/>
* Identifiers can be prefixed with schema names. These schema names will be converted in the same way as described
* above. Quoting the schema name will make it case sensitive.
* Examples:
* <p/>
* mySchema.myTable -> MYSCHEMA.MYTABLE
* "mySchema".myTable -> mySchema.MYTABLE
* "mySchema"."myTable" -> mySchema.myTable
*
* @param identifier The identifier, not null
* @return The name converted to correct case if needed, not null
*/
public String toCorrectCaseIdentifier(String identifier) {
identifier = identifier.trim();
int index = identifier.indexOf('.');
if (index != -1) {
String schemaNamePart = identifier.substring(0, index);
String identifierPart = identifier.substring(index + 1);
return toCorrectCaseIdentifier(schemaNamePart) + "." + toCorrectCaseIdentifier(identifierPart);
}
if (identifierQuoteString != null && identifier.startsWith(identifierQuoteString) && identifier.endsWith(identifierQuoteString)) {
return identifier.substring(1, identifier.length() - 1);
}
if (storedIdentifierCase == UPPER_CASE) {
return identifier.toUpperCase();
} else if (storedIdentifierCase == LOWER_CASE) {
return identifier.toLowerCase();
} else {
return identifier;
}
}
/**
* Determines the case the database uses to store non-quoted identifiers. This will use the connections
* database metadata to determine the correct case.
*
* @param storedIdentifierCase The stored case: possible values 'lower_case', 'upper_case', 'mixed_case' and 'auto'
* @return The stored case, not null
*/
private StoredIdentifierCase determineStoredIdentifierCase(String storedIdentifierCase) {
if ("lower_case".equals(storedIdentifierCase)) {
return LOWER_CASE;
} else if ("upper_case".equals(storedIdentifierCase)) {
return UPPER_CASE;
} else if ("mixed_case".equals(storedIdentifierCase)) {
return MIXED_CASE;
} else if (!"auto".equals(storedIdentifierCase)) {
throw new UnitilsException("Unknown value " + storedIdentifierCase + " for property " + PROPKEY_STORED_IDENTIFIER_CASE + ". It should be one of lower_case, upper_case, mixed_case or auto.");
}
Connection connection = null;
try {
connection = getSQLHandler().getDataSource().getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
if (databaseMetaData.storesUpperCaseIdentifiers()) {
return UPPER_CASE;
} else if (databaseMetaData.storesLowerCaseIdentifiers()) {
return LOWER_CASE;
} else {
return MIXED_CASE;
}
} catch (SQLException e) {
throw new UnitilsException("Unable to determine stored identifier case.", e);
} finally {
closeQuietly(connection, null, null);
}
}
/**
* Determines the string used to quote identifiers to make them case-sensitive. This will use the connections
* database metadata to determine the quote string.
*
* @param identifierQuoteStringProperty The string to quote identifiers, 'none' if quoting is not supported, 'auto' for auto detection
* @return The quote string, null if quoting is not supported
*/
private String determineIdentifierQuoteString(String identifierQuoteStringProperty) {
if ("none".equals(identifierQuoteStringProperty)) {
return null;
} else if (!"auto".equals(identifierQuoteStringProperty)) {
return identifierQuoteStringProperty;
}
Connection connection = null;
try {
connection = getSQLHandler().getDataSource().getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();
String quoteString = databaseMetaData.getIdentifierQuoteString();
if (quoteString == null || "".equals(quoteString.trim())) {
return null;
}
return quoteString;
} catch (SQLException e) {
throw new UnitilsException("Unable to determine identifier quote string.", e);
} finally {
closeQuietly(connection, null, null);
}
}
/**
* Indicates whether the underlying DBMS supports synonyms
*
* @return True if synonyms are supported, false otherwise
*/
public boolean supportsSynonyms() {
return false;
}
/**
* Indicates whether the underlying DBMS supports sequences
*
* @return True if sequences are supported, false otherwise
*/
public boolean supportsSequences() {
return false;
}
/**
* Indicates whether the underlying DBMS supports triggers
*
* @return True if triggers are supported, false otherwise
*/
public boolean supportsTriggers() {
return false;
}
/**
* Indicates whether the underlying DBMS supports database types
*
* @return True if types are supported, false otherwise
*/
public boolean supportsTypes() {
return false;
}
/**
* Indicates whether the underlying DBMS supports identity columns
*
* @return True if identity is supported, false otherwise
*/
public boolean supportsIdentityColumns() {
return false;
}
/**
* Indicates whether the underlying DBMS supports materialized views
*
* @return True if materialized views are supported, false otherwise
*/
public boolean supportsMaterializedViews() {
return false;
}
/**
* Indicates whether the underlying DBMS supports the cascade option for dropping tables and views.
*
* @return True if cascade is supported, false otherwise
*/
public boolean supportsCascade() {
return false;
}
}