org.bitbucket.bradleysmithllc.etlunit.feature.database.BaseDatabaseImplemenation Maven / Gradle / Ivy
package org.bitbucket.bradleysmithllc.etlunit.feature.database;
/*
* #%L
* etlunit-database
* %%
* Copyright (C) 2010 - 2014 bradleysmithllc
* %%
* 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.
* #L%
*/
import org.bitbucket.bradleysmithllc.etlunit.Log;
import org.bitbucket.bradleysmithllc.etlunit.RuntimeSupport;
import org.bitbucket.bradleysmithllc.etlunit.TestExecutionError;
import org.bitbucket.bradleysmithllc.etlunit.feature.database.db.Catalog;
import org.bitbucket.bradleysmithllc.etlunit.feature.database.db.Database;
import org.bitbucket.bradleysmithllc.etlunit.feature.database.db.Schema;
import org.bitbucket.bradleysmithllc.etlunit.feature.database.db.Table;
import org.bitbucket.bradleysmithllc.etlunit.parser.ETLTestValueObjectBuilder;
import org.bitbucket.bradleysmithllc.etlunit.util.IOUtils;
import org.bitbucket.bradleysmithllc.etlunit.util.StringUtils;
import org.bitbucket.bradleysmithllc.etlunit.util.VelocityUtil;
import javax.inject.Inject;
import javax.inject.Named;
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public abstract class BaseDatabaseImplemenation implements DatabaseImplementation {
protected RuntimeSupport runtimeSupport;
public static int DB_PING = 1000;
private final Map connectionMap = new HashMap();
protected JDBCClient jdbcClient;
protected Log applicationLog;
private DatabaseFeatureModule databaseFeatureModule;
@Inject
public void setApplicationLog(@Named("applicationLog") Log log) {
applicationLog = log;
}
@Inject
public void setDatabaseFeatureModule(DatabaseFeatureModule databaseFeatureModule) {
this.databaseFeatureModule = databaseFeatureModule;
}
@Inject
public void receiveRuntimeSupport(RuntimeSupport runtimeSupport) {
this.runtimeSupport = runtimeSupport;
}
@Override
public final Object processOperation(operation op, OperationRequest request) throws UnsupportedOperationException {
switch (op) {
case dropConstraints:
final InitializeRequest initializeRequest = request.getInitializeRequest();
// use a jdbc meta data query to find all foreign keys in every table and drop
try {
jdbcClient.useStatement(initializeRequest.getConnection(), initializeRequest.getMode(), new JDBCClient.StatementClient() {
@Override
public void connection(Connection conn, Statement st, DatabaseConnection connection, String mode, int id) throws Exception {
Database db = initializeRequest.getDatabase();
DatabaseMetaData md = conn.getMetaData();
for (Catalog catalog : db.getCatalogs()) {
for (Schema schema : catalog.getSchemas()) {
for (final Table table : schema.getTables()) {
// grab all exported foreign keys and drop for tables
if (table.getType() == Table.type.table || table.getType() == Table.type.temp_table) {
ResultSet keysRS = md.getImportedKeys(table.getCatalog().getName(), table.getSchema().getName(), table.getName());
// track constraints so we don't drop one twice. This behavior has been observed in jtds
Map cons = new HashMap();
try {
while (keysRS.next()) {
String FKTABLE_CAT = keysRS.getString(5);
String FKTABLE_SCHEM = keysRS.getString(6);
String FKTABLE_NAME = keysRS.getString(7);
String FK_NAME = keysRS.getString(12);
String fktab = FKTABLE_CAT + "." + FKTABLE_SCHEM + "." + FKTABLE_NAME;
// issue a drop for this constraint
String fkId = escapeIdentifier(FK_NAME);
String conKey = fktab + "." + fkId;
if (!cons.containsKey(conKey)) {
cons.put(conKey, "");
String SQL = "ALTER TABLE " + escapeQualifiedIdentifier(table) + " DROP CONSTRAINT " + fkId;
applicationLog.debug("Removing constraint from table " + fktab + " named " + fkId + " using sql '" + SQL + "'");
st.addBatch(SQL);
}
}
st.executeBatch();
} finally {
keysRS.close();
}
}
}
}
}
}
});
return null;
} catch (TestExecutionError testExecutionError) {
throw new RuntimeException(testExecutionError);
}
}
return processOperationSub(op, request);
}
public abstract Object processOperationSub(operation op, OperationRequest request) throws UnsupportedOperationException;
@Override
public void prepareConnectionForInsert(Connection connection, Table target, DatabaseConnection dc, String mode) throws Exception {
}
public database_state getDatabaseState(DatabaseConnection databaseConnection, String mode, Database database) {
// use one of the tables to query and see if all is well
// grab the first table in the first schema in the first catalog . . .
// Make sure the table has at least one column available - an unused table will have
// not loaded any columns
Table table = null;
List catalogs = database.getCatalogs();
if (catalogs.size() != 0)
{
Catalog catalog = catalogs.get(0);
outer:
for (Schema schema : catalog.getSchemas()) {
for (Table t_table : schema.getTables()) {
if (t_table.getTableColumns().size() > 0) {
table = t_table;
break outer;
}
}
}
if (table != null) {
applicationLog.info("Pinging table [" + table.getQualifiedName() + "] to verify database state for mode [" + mode + "]");
String sqlSelect = table.createSQLSelect(this);
applicationLog.info("Using query[" + sqlSelect + "] to verify database state for mode [" + mode + "]");
// open a connection to the database but don't use it
try {
jdbcClient.useResultSet(databaseConnection, mode, new NullResultSetClient(), sqlSelect, DB_PING);
applicationLog.info("Ping successful");
return database_state.pass;
} catch (TestExecutionError testExecutionError) {
applicationLog.severe("Failure querying table", testExecutionError);
}
applicationLog.info("Ping failed");
}
}
return database_state.fail;
}
@Override
public void setJdbcClient(JDBCClient client) {
jdbcClient = client;
jdbcClient.worksFor(this);
}
public final Connection getConnection(DatabaseConnection dc, String mode) throws TestExecutionError {
return getConnection(dc, mode, DATABASE);
}
public final synchronized Connection getConnection(DatabaseConnection dc, String mode, int id) throws TestExecutionError {
String key = dc.getId() + "." + mode + "." + id + "." + runtimeSupport.getExecutorId();
//applicationLog.info("Requesting connection: [" + key + "]");
if (!connectionMap.containsKey(key)) {
//applicationLog.info("Creating connection: [" + key + "]");
String jdbcUrl = getJdbcUrl(dc, mode, id);
applicationLog.info("Using JDBC url '" + jdbcUrl + "'");
String loginName = getLoginName(dc, mode, id);
String password = getPassword(dc, mode, id);
try {
// check whether the driver is already registered
try
{
DriverManager.getDriver(jdbcUrl);
}
catch(SQLException exc)
{
// register the driver
Class hsqldbDriver = getJdbcDriverClass();
// do this manually because the automatic way does not work in maven
DriverManager.registerDriver((Driver) hsqldbDriver.newInstance());
}
Connection connection = DriverManager.getConnection(jdbcUrl, loginName, password);
prepareConnection(connection);
return connection;
} catch (Exception exc) {
throw new IllegalArgumentException("Could not connect to URL [" + jdbcUrl + "] using login name '" + loginName + "'", exc);
}
}
else
{
//applicationLog.info("Using cached connection: [" + key + "]");
}
return connectionMap.remove(key);
}
public void returnConnection(Connection conn, DatabaseConnection dc, String mode, int id, boolean normalState) throws TestExecutionError {
String key = dc.getId() + "." + mode + "." + id + "." + runtimeSupport.getExecutorId();
//applicationLog.info("Connection returned: [" + key + "] " + System.identityHashCode(conn));
if (id == DB_PING || connectionMap.containsKey(key) || !normalState)
{
try {
applicationLog.info("Disposing connection: [" + key + "] " + System.identityHashCode(conn));
conn.close();
} catch (SQLException e) {
applicationLog.severe("Error disposing connection: [" + key + "] " + System.identityHashCode(conn), e);
}
}
else
{
connectionMap.put(key, conn);
}
}
protected void prepareConnection(Connection connection) throws Exception {
}
@Override
public String getPassword(DatabaseConnection dc, String mode, int id) {
if (id == SYSADMIN) return dc.getAdminPassword();
else return getPasswordImpl(dc, mode);
}
protected String getPasswordImpl(DatabaseConnection dc, String mode) {
return getDatabaseName(dc, mode);
}
@Override
public String getDatabaseName(DatabaseConnection dc, String mode) {
String databaseName = "__" + runtimeSupport.getProjectUser() +
"_" + runtimeSupport.getProjectName() +
"_" + runtimeSupport.getProjectVersion() +
"_" + StringUtils.sanitize(dc.getId(), '_') +
(mode == null ? "" : ("_" + StringUtils.sanitize(mode, '_'))) +
"_" + runtimeSupport.getProjectUID();
return databaseName;
}
protected String getLoginNameImpl(DatabaseConnection dc, String mode) {
return getDatabaseName(dc, mode);
}
@Override
public String getLoginName(DatabaseConnection dc, String mode, int id) {
if (id == SYSADMIN) return dc.getAdminUserName();
else return getLoginNameImpl(dc, mode);
}
@Override
public String getLoginName(DatabaseConnection dc, String mode) {
return getLoginName(dc, mode, DATABASE);
}
@Override
public String getPassword(DatabaseConnection dc, String mode) {
return getPassword(dc, mode, DATABASE);
}
public final void dispose() {
for (Map.Entry conn : connectionMap.entrySet()) {
try {
Connection connection = conn.getValue();
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
connectionMap.clear();
}
/**
* Default is all system tables are excluded
*
* @param table
* @return
*/
@Override
public boolean isTableTestVisible(DatabaseConnection dc, String mode, Table table) {
return table.getType() != Table.type.system_table;
}
/**
* Default issues a truncate table statement, followed by a 'delete from' if that throws an error
*
* @param table
*/
@Override
public void purgeTableForTest(DatabaseConnection dc, String mode, final Table table) throws Exception {
// ignore views and system tables
if (table.getType() != Table.type.system_table && table.getType() != Table.type.view && table.getType() != Table.type.synthetic && table.getType() != Table.type.sql) {
// try with truncate, then try delete from
try {
jdbcClient.useStatement(dc, mode, new JDBCClient.StatementClient() {
@Override
public void connection(Connection conn, Statement st, DatabaseConnection connection, String mode, int id) throws Exception {
StringBuilder stb = new StringBuilder();
stb.append("TRUNCATE TABLE ");
if (restrictToOwnedSchema(connection)) {
stb.append(escapeIdentifier(table.getName()));
} else {
stb.append(escapeQualifiedIdentifier(table));
}
st.execute(stb.toString());
}
});
} catch (Exception exc) {
jdbcClient.useStatement(dc, mode, new JDBCClient.StatementClient() {
@Override
public void connection(Connection conn, Statement st, DatabaseConnection connection, String mode, int id) throws Exception {
StringBuilder stb = new StringBuilder();
stb.append("DELETE FROM ");
if (restrictToOwnedSchema(connection)) {
stb.append(escapeIdentifier(table.getName()));
} else {
stb.append(escapeQualifiedIdentifier(table));
}
st.execute(stb.toString());
}
});
}
}
}
@Override
public Table.type translateTableType(String JDBCMetaTableTypeName) {
if (JDBCMetaTableTypeName.equals("TABLE")) {
return Table.type.table;
} else if (JDBCMetaTableTypeName.equals("SYSTEM TABLE")) {
return Table.type.system_table;
} else if (JDBCMetaTableTypeName.equals("VIEW")) {
return Table.type.view;
} else if (JDBCMetaTableTypeName.equals("GLOBAL TEMPORARY")) {
return Table.type.temp_table;
} else if (JDBCMetaTableTypeName.equals("LOCAL TEMPORARY")) {
return Table.type.temp_table;
} else if (JDBCMetaTableTypeName.equals("ALIAS")) {
return Table.type.table;
} else if (JDBCMetaTableTypeName.equals("SYNONYM")) {
return Table.type.table;
}
return null;
}
/**
* Use the common quote character for escaping.
*
* @param table
* @return
* @throws Exception
*/
@Override
public String escapeQualifiedIdentifier(Table table) {
StringBuilder stb = new StringBuilder();
String catalogName = table.getCatalog().getName();
if (catalogName != null) {
stb.append(escapeIdentifier(catalogName));
stb.append('.');
}
Schema schema = table.getSchema();
if (!schema.isVirtual()) {
String schemaName = schema.getName();
if (schemaName != null) {
stb.append(escapeIdentifier(schemaName));
stb.append('.');
}
}
stb.append(escapeIdentifier(table.getName()));
return stb.toString();
}
@Override
public String escapeIdentifier(String name) {
return '"' + name + '"';
}
@Override
public String getJdbcUrl(DatabaseConnection dc, String mode) {
return getJdbcUrl(dc, mode, DATABASE);
}
@Override
public boolean restrictToOwnedSchema(DatabaseConnection dc) {
return true;
}
protected void executeScript(String script, DatabaseConnection databaseConnection, String mode) throws IOException, TestExecutionError {
executeScript(script, databaseConnection, mode, DATABASE);
}
protected void executeScripts(String[] scripts, DatabaseConnection databaseConnection, String mode) throws IOException, TestExecutionError {
executeScripts(scripts, databaseConnection, mode, DATABASE);
}
protected void executeScripts(String[] scripts, DatabaseConnection databaseConnection, String mode, int target) throws IOException, TestExecutionError {
for (String script : scripts) {
executeScript(script, databaseConnection, mode, target);
}
}
protected void executeScript(String script, DatabaseConnection databaseConnection, String mode, int target) throws IOException, TestExecutionError {
jdbcClient.useResultSetScript(databaseConnection, mode, null, script, target);
}
private Exception[] executeScripts(DatabaseConnection databaseConnection, String mode, String[] scripts) {
return executeScripts(databaseConnection, mode, scripts, DATABASE);
}
protected Exception[] executeScripts(DatabaseConnection databaseConnection, String mode, String[] scripts, int id) {
String loginName = getLoginName(databaseConnection, mode, DATABASE);
String password = getPassword(databaseConnection, mode, DATABASE);
Map databaseProperties = databaseConnection.getDatabaseProperties();
String tablespace = loginName + "_FILE";
String tempTablespace = "T_" + loginName + "_FILE";
Map map = new HashMap();
map.put("databaseName", loginName);
map.put("databasePassword", password);
if (databaseProperties != null) {
String otableName = databaseProperties.get("tablespace");
if (otableName != null) {
tablespace = otableName;
map.put("tablespaceSpecified", "true");
}
otableName = databaseProperties.get("temp-tablespace");
if (otableName != null) {
tempTablespace = otableName;
map.put("tempTablespaceSpecified", "true");
}
}
map.put("tablespace", tablespace);
map.put("tempTablespace", tempTablespace);
Exception[] exc_arr = new Exception[scripts.length];
for (int index = 0; index < scripts.length; index++) {
String script = scripts[index];
URL url_script = getClass().getResource("/" + script + ".vm");
try {
String killText = IOUtils.readURLToString(url_script);
String fscript = VelocityUtil.writeTemplate(killText, map);
String scriptName = script + "_" + getDatabaseName(databaseConnection, mode) + ".sql";
File scriptFile = runtimeSupport.createGeneratedSourceFile(getImplementationId(), scriptName);
IOUtils.writeBufferToFile(scriptFile, new StringBuffer(fscript));
executeScript(fscript, databaseConnection, mode, SYSADMIN);
} catch (Exception e) {
applicationLog.severe("Error executing script: " + e.toString(), e);
exc_arr[index] = e;
}
}
return exc_arr;
}
/**
* By default do nothing.
* @param databaseConnection
* @param mode
* @param builder
*/
@Override
public void propagateImplementationProperties(DatabaseConnection databaseConnection, String mode, ETLTestValueObjectBuilder builder) {
}
} © 2015 - 2025 Weber Informatics LLC | Privacy Policy