org.bitbucket.bradleysmithllc.etlunit.feature.database.BaseDatabaseImplemenation Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of etlunit-database Show documentation
There is a newer version: 4.6.0-eu
package org.bitbucket.bradleysmithllc.etlunit.feature.database;

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

public abstract class BaseDatabaseImplemenation implements DatabaseImplementation {
	protected RuntimeSupport runtimeSupport;

	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:
				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 = connection.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) {
		// use one of the tables to query and see if all is well
		Database database = databaseConnection.getDatabase();

		// 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;
		Catalog catalog = database.getCatalogs().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, DATABASE);

				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 Connection getConnection(DatabaseConnection dc, String mode, int id) throws TestExecutionError {
		String key = dc.getId() + "." + mode + "." + id;
		if (!connectionMap.containsKey(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 {
				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);
			}
		}

		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;

		if (connectionMap.containsKey(key))
		{
			try {
				conn.close();
			} catch (SQLException e) {
				applicationLog.severe("Error disposing connection", 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;
	}
}