org.bitbucket.bradleysmithllc.etlunit.feature.database.BaseDatabaseImplementation 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 com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;
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.*;
import org.bitbucket.bradleysmithllc.etlunit.parser.ETLTestValueObject;
import org.bitbucket.bradleysmithllc.etlunit.parser.ETLTestValueObjectBuilder;
import org.bitbucket.bradleysmithllc.etlunit.util.*;
import org.bitbucket.bradleysmithllc.etlunit.util.jdbc.ExceptionalConsumer;
import org.bitbucket.bradleysmithllc.etlunit.util.jdbc.JdbcVisitors;
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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicReference;
import java.util.function.Consumer;
import java.util.regex.Pattern;
public abstract class BaseDatabaseImplementation implements DatabaseImplementation {
protected RuntimeSupport runtimeSupport;
@Override
public boolean isUserSchema(DatabaseConnection dc, String schemaName) {
return !schemaName.equalsIgnoreCase("information_schema");
}
private final Map connectionMap = new HashMap<>();
protected JDBCClient jdbcClient;
protected Log applicationLog;
private DatabaseFeatureModule databaseFeatureModule;
private Pattern sqlEscapableCharacters;
public BaseDatabaseImplementation() {
sqlEscapableCharacters = Pattern.compile("^[A-Za-z_][A-Za-z_0-9]*$");
}
@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;
}
static class SqlTable {
private String catalogName;
private String schemaName;
private String tableName;
public static SqlTable of(String cat, String sch, String tbl) {
SqlTable s = new SqlTable();
s.catalogName = cat;
s.schemaName = sch;
s.tableName = tbl;
return s;
}
}
@Override
public final Object processOperation(operation op, OperationRequest request) throws UnsupportedOperationException {
switch (op) {
case dropConstraints:
final InitializeRequest initializeRequest = request.getInitializeRequest();
Database db = initializeRequest.getDatabase();
Catalog catalog = db.getCatalog(null);
try {
AtomicReference> commandList = new AtomicReference<>(new HashMap<>());
if (useInformationSchemaConstraints()) {
// use a jdbc meta data query to find all foreign keys in every table and drop
applicationLog.info("Discovering constraints with information schema");
JdbcVisitors.withPersistentConnection(getConnection(initializeRequest.getConnection(), initializeRequest.getMode())).scanQuery("select * from information_schema.constraints", (resultSet) -> {
String FKTABLE_CAT = resultSet.getString("TABLE_CATALOG");
String FKTABLE_SCHEM = resultSet.getString("TABLE_SCHEMA");
String FKTABLE_NAME = resultSet.getString("TABLE_NAME");
String FK_NAME = resultSet.getString("CONSTRAINT_NAME");
String type = resultSet.getString("CONSTRAINT_TYPE");
if (!type.equals("PRIMARY KEY")) {
applicationLog.info("Discovered constraint " + FK_NAME);
commandList.get().put(FK_NAME, SqlTable.of(FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME));
}
}).dispose();
} else {
// do the same thing, but with the imported keys
applicationLog.info("Discovering constraints with jdbc database metadata");
connectionVisitor(initializeRequest.getConnection(), initializeRequest.getMode(), (commands) -> {
commands.withDatabaseMetadata((databaseMetadata) -> {
// imported keys have to be discovered actively, not passively.
for (Schema schema : catalog.getSchemas()) {
for (Table table : schema.getTables()) {
String tablePhysicalName = table.getPhysicalName();
String schemaPhysicalName = schema.getPhysicalName();
ResultSet keysRS = databaseMetadata.getImportedKeys(null, schemaPhysicalName, tablePhysicalName);
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);
applicationLog.info("Discovered constraint " + FK_NAME);
commandList.get().put(FK_NAME, SqlTable.of(FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME));
}
} finally {
keysRS.close();
}
}
}
});
});
}
connectionVisitor(initializeRequest.getConnection(), initializeRequest.getMode(), (commands) -> {
commands.withStatement((statement) -> {
applicationLog.info("Processing constraints");
for (Map.Entry command : commandList.get().entrySet()) {
String fkid = command.getKey();
SqlTable ref = command.getValue();
Schema schema = catalog.getSchema(ref.schemaName);
Table table = schema != null ? schema.getTable(ref.tableName) : null;
if (table != null) {
applicationLog.info("Dropping constraint " + fkid);
String sql = "ALTER TABLE " + escapeQualifiedIdentifier(table) + " DROP CONSTRAINT " + fkid;
applicationLog.info("Dropping constraint with '" + sql + "'");
statement.addBatch(sql);
}
}
statement.executeBatch();
});
});
return null;
} catch (Exception testExecutionError) {
throw new RuntimeException(testExecutionError);
}
case materializeViews:
try {
materializeViews(request.getInitializeRequest());
} catch (TestExecutionError | IOException tee) {
throw new RuntimeException(tee);
}
}
return processOperationSub(op, request);
}
/* Handle materializing views in as uniform way as possible. Defer non-standard tasks like
* renaming views to the sub implementation.
*/
private void materializeViews(InitializeRequest initializeRequest) throws TestExecutionError, IOException {
DatabaseConnection databaseConnection = initializeRequest.getConnection();
String mode = initializeRequest.getMode();
// grab all views initially
final MapList schemaViewMap = new HashMapArrayList<>();
connectionVisitor(databaseConnection, mode, (commands) -> {
commands.scanQuery("select table_schema, table_name from information_schema.views", (resultSet) -> {
String schema = resultSet.getString(1);
String table = resultSet.getString(2);
if (isUserSchema(databaseConnection, schema)) {
schemaViewMap.add(schema, table);
}
});
});
String tag = "_867";
// process each view into a table
for (Map.Entry> schema : schemaViewMap.entrySet()) {
for (String view : schema.getValue()) {
// make a plain table copy
String script = createScriptToMaterializeViewToTable(
escapeIdentifierIfNeeded(schema.getKey()), escapeIdentifierIfNeeded(view), escapeIdentifierIfNeeded(schema.getKey()), escapeIdentifierIfNeeded(view + tag)
);
applicationLog.info("Materializing view with " + script);
executeScript(script, databaseConnection, mode);
}
}
for (Map.Entry> schema : schemaViewMap.entrySet()) {
for (String view : schema.getValue()) {
// rename original view to ._LV
Pair realViewName = mapToRealViewName(schema.getKey(), view);
String script = createScriptToRenameViewTo(
escapeIdentifierIfNeeded(schema.getKey()), escapeIdentifierIfNeeded(view), escapeIdentifierIfNeeded(realViewName.getLeft()), escapeIdentifierIfNeeded(realViewName.getRight()));
executeScript(script, databaseConnection, mode);
applicationLog.info("Renaming view with " + script);
// now rename each table to the original view name
script = createScriptToRenameTableTo(
escapeIdentifierIfNeeded(schema.getKey()), escapeIdentifierIfNeeded(view + tag), escapeIdentifierIfNeeded(schema.getKey()), escapeIdentifierIfNeeded(view));
applicationLog.info("Renaming table with " + script);
executeScript(script, databaseConnection, mode);
}
}
}
public void connectionVisitor(DatabaseConnection databaseConnection, String mode, ExceptionalConsumer consumer) throws TestExecutionError {
try {
JdbcVisitors.JdbcVisitorCommands commands = JdbcVisitors.withPersistentConnection(getConnection(databaseConnection, mode));
try {
consumer.accept(commands);
} catch (Exception e) {
throw new TestExecutionError("Visitor", e);
} finally {
commands.dispose();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/* This is also ANSI SQL. */
protected String createScriptToRenameTableTo(String sourceSchema, String sourceView, String targetSchema, String targetName) {
return "ALTER TABLE " + sourceSchema + "." + sourceView + " RENAME TO " + targetSchema + "." + targetName + ";";
}
/* This is not standard SQL, so this probably needs to be changed by the implementation. */
protected String createScriptToRenameViewTo(String sourceSchema, String sourceView, String targetSchema, String targetName) {
return "ALTER VIEW " + sourceSchema + "." + sourceView + " RENAME TO " + targetSchema + "." + targetName + ";";
}
/* This uses ANSI SQL, so unless the DB implementation doesn't support it it can be left alone. */
protected String createScriptToMaterializeViewToTable(String sourceSchema, String sourceView, String targetSchema, String targetName) {
return "CREATE TABLE " + targetSchema + "." + targetName + " as SELECT * FROM " + sourceSchema + "." + sourceView + " WHERE 1 = 0";
}
protected boolean useInformationSchemaConstraints() {
return true;
}
public abstract Object processOperationSub(operation op, OperationRequest request) throws UnsupportedOperationException;
@Override
public void prepareConnectionForInsert(Connection connection, Table target, DatabaseConnection dc, String mode) throws Exception {
}
@Override
public Pair mapToRealViewName(String aschema, String source) {
return ImmutablePair.of(aschema, source + "_5309");
}
@Override
public String strings(Pair tbl) {
return (tbl.getLeft() == null ? (tbl.getLeft() + ".") : ("")) + tbl.getRight();
}
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, database_role.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_role.database);
}
public final synchronized Connection getConnection(DatabaseConnection dc, String mode, database_role id) throws TestExecutionError {
String key = dc.getId() + "." + mode + "." + id + "." + runtimeSupport.getExecutorId();
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);
applicationLog.info("Username '" + loginName + "', password = '" + password + "'");
HikariConfig config = new HikariConfig();
config.setPoolName(key);
config.setJdbcUrl(jdbcUrl);
config.setUsername(loginName);
config.setPassword(password);
config.setDriverClassName(getJdbcDriverClass().getName());
HikariDataSource hikariDataSource = new HikariDataSource(config);
connectionMap.put(key, hikariDataSource);
}
try {
Connection connection = connectionMap.get(key).getConnection();
prepareConnection(connection);
return connection;
} catch (Exception throwables) {
throw new TestExecutionError("", throwables);
}
}
public final synchronized void disposePool(DatabaseConnection dc, String mode, database_role id) throws TestExecutionError {
String key = dc.getId() + "." + mode + "." + id + "." + runtimeSupport.getExecutorId();
if (connectionMap.containsKey(key)) {
HikariDataSource hds = connectionMap.get(key);
hds.close();
connectionMap.remove(key);
}
}
public void returnConnection(Connection conn, DatabaseConnection dc, String mode, database_role id, boolean normalState) throws TestExecutionError {
// always close. Uses connection repooling...
try {
conn.close();
} catch (SQLException throwables) {
throw new TestExecutionError("", throwables);
}
}
protected void prepareConnection(Connection connection) throws Exception {
}
@Override
public String getPassword(DatabaseConnection dc, String mode, database_role id) {
if (id == database_role.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() +
"_" + EtlUnitStringUtils.sanitize(dc.getId(), '_') +
(mode == null ? "" : ("_" + EtlUnitStringUtils.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, database_role id) {
if (id == database_role.sysadmin) return dc.getAdminUserName();
else return getLoginNameImpl(dc, mode);
}
@Override
public String getLoginName(DatabaseConnection dc, String mode) {
return getLoginName(dc, mode, database_role.database);
}
@Override
public String getPassword(DatabaseConnection dc, String mode) {
return getPassword(dc, mode, database_role.database);
}
public final void dispose() {
applicationLog.info("Closing connections");
for (Map.Entry conn : connectionMap.entrySet()) {
applicationLog.info("Closing connections for database mode " + conn.getKey());
HikariDataSource connection = conn.getValue();
connection.close();
}
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, DatabaseImplementation.database_role id) throws Exception {
StringBuilder stb = new StringBuilder();
stb.append("TRUNCATE TABLE ");
if (restrictToOwnedSchema(connection)) {
stb.append(escapeIdentifierIfNeeded(table.getPhysicalName()));
} else {
stb.append(escapeQualifiedIdentifier(table));
}
String fullTruncate = adjustTruncateTable(stb.toString());
st.execute(fullTruncate);
}
});
} catch (Exception exc) {
jdbcClient.useStatement(dc, mode, new JDBCClient.StatementClient() {
@Override
public void connection(Connection conn, Statement st, DatabaseConnection connection, String mode, DatabaseImplementation.database_role id) throws Exception {
StringBuilder stb = new StringBuilder();
stb.append("DELETE FROM ");
if (restrictToOwnedSchema(connection)) {
stb.append(escapeIdentifierIfNeeded(table.getPhysicalName()));
} else {
stb.append(escapeQualifiedIdentifier(table));
}
st.execute(stb.toString());
}
});
}
}
}
@Override
public String adjustTruncateTable(String toString) {
return 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 final String escapeQualifiedIdentifier(Table table) {
StringBuilder stb = new StringBuilder();
String catalogName = table.getCatalog().getPhysicalName();
if (catalogName != null) {
stb.append(escapeIdentifierIfNeeded(catalogName));
stb.append('.');
}
Schema schema = table.getSchema();
if (!schema.isVirtual()) {
String schemaName = schema.getPhysicalName();
if (schemaName != null) {
stb.append(escapeIdentifierIfNeeded(schemaName));
stb.append('.');
}
}
stb.append(escapeIdentifierIfNeeded(table.getPhysicalName()));
return stb.toString();
}
@Override
public String escapeIdentifierIfNeeded(String name) {
// scan chars. If anything other than a letter or number or _
// is encountered then it is escaped. Furthermore, if all letters
// are not of the same case, it is also escaped.
// look for and and , escape if present
boolean needsEscaping = false;
boolean uppercaseObserved = false;
boolean lowercaseObserved = false;
// check first char as a special case. Must be a letter
char[] charArray = name.toCharArray();
// allow first char to be either a letter or an '_'. Never a number or anything else
if (!Character.isLetter(charArray[0]) && charArray[0] != '_') {
needsEscaping = true;
} else {
for (char ch : charArray) {
if (!Character.isLetterOrDigit(ch)) {
// only allowed exception
if (ch != '_') {
needsEscaping = true;
break;
}
} else {
if (Character.isUpperCase(ch)) {
uppercaseObserved = true;
} else if (Character.isLowerCase(ch)) {
lowercaseObserved = true;
}
}
}
}
if (!needsEscaping) {
needsEscaping = uppercaseObserved & lowercaseObserved;
}
if (needsEscaping) {
return openEscapeChar() + name + closeEscapeChar();
}
return name;
}
protected String escapeChar() {
return "\"";
}
protected String openEscapeChar() {
return escapeChar();
}
protected String closeEscapeChar() {
return escapeChar();
}
@Override
public String getJdbcUrl(DatabaseConnection dc, String mode) {
return getJdbcUrl(dc, mode, database_role.database);
}
@Override
public boolean restrictToOwnedSchema(DatabaseConnection dc) {
return false;
}
protected void executeScript(String script, DatabaseConnection databaseConnection, String mode) throws IOException, TestExecutionError {
executeScript(script, databaseConnection, mode, database_role.database);
}
protected void executeScripts(String[] scripts, DatabaseConnection databaseConnection, String mode) throws IOException, TestExecutionError {
executeScripts(scripts, databaseConnection, mode, database_role.database);
}
protected void executeScripts(String[] scripts, DatabaseConnection databaseConnection, String mode, database_role target) throws IOException, TestExecutionError {
for (String script : scripts) {
executeScript(script, databaseConnection, mode, target);
}
}
protected void executeScript(String script, DatabaseConnection databaseConnection, String mode, database_role 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_role.database);
}
protected Exception[] executeScripts(DatabaseConnection databaseConnection, String mode, String[] scripts, database_role id) {
String loginName = getLoginName(databaseConnection, mode, database_role.database);
String password = getPassword(databaseConnection, mode, database_role.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, database_role.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