nl.psek.fitnesse.fixtures.database.DatabaseFixture Maven / Gradle / Ivy
package nl.psek.fitnesse.fixtures.database;
import fitnesse.responders.editing.fixture.Command;
import fitnesse.responders.editing.fixture.Fixture;
import fitnesse.responders.editing.fixture.Start;
import nl.psek.fitnesse.ConditionalExceptionType;
import nl.psek.fitnesse.fixtures.general.properties.PropertiesFixture;
import nl.psek.fitnesse.fixtures.general.util.FileUtils;
import org.apache.log4j.Logger;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import static nl.psek.fitnesse.fixtures.util.FixtureOperations.matchOnPattern;
/**
* @author Ronald Mathies
*/
@Fixture(name = "Database Fixture")
public class DatabaseFixture {
private static final Logger LOG = Logger.getLogger(DatabaseFixture.class);
private static final String CONNECTION_DATABASE_DRIVER = "database.driver";
private static final String CONNECTION_DATABASE_URL = "database.url";
private static final String CONNECTION_DATABASE_USERNAME = "database.username";
private static final String CONNECTION_DATABASE_PASSWORD = "database.password";
private Map connections = new HashMap();
private Map resultSetManagers = new HashMap<>();
private List parameters = new ArrayList<>();
public ResultSetManager activeResultSetManager = null;
/**
* Constructs a new database fixture.
*/
@Start(name = "startDatabaseFixture", arguments = {}, example = "|start |Database Fixture|\n|createConnection; |name|database.properties |\n|$value= |performSelectSingleResult; |name|select column from table where 1 = 1|\n|closeConnection; |name|")
public DatabaseFixture() {
}
/**
* Creates a new database connection with the specified name and properties.
*
* @param name the name for the connection.
* @param properties the properties file containing the connection settings.
*/
@Command(name = "createConnection", arguments = {"name", "properties"}, example = "|createConnection; |name|database.properties |")
public void createConnection(String name, String properties) {
Connection connection = createConnection(properties);
connections.put(name, connection);
}
/**
* Performs a query on the database using the connection with the specified name.
*
* @param name the name of the connection to use.
* @param sql the sql statement to execute.
* @return the resultset.
*/
@Command(name = "performSelect", arguments = {"name", "sql"}, example = "|performSelect; |name|select * from table where name like 'bike%'|")
public void performSelect(String name, String sql) {
Connection connection = findConnectionByName(name);
try {
PreparedStatement statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
setPreparedStatementParameters(statement);
ResultSet resultSet = statement.executeQuery();
if (!resultSetManagers.containsKey(name)) {
resultSetManagers.put(name, new ResultSetManager());
}
ResultSetManager resultSetManager = resultSetManagers.get(name);
resultSetManager.setResultSet(resultSet);
this.activeResultSetManager = resultSetManager;
} catch (SQLException e) {
ConditionalExceptionType.fail("Failed to execute query '%s' due to the following '%s'.", sql, e.getMessage());
}
}
private void setPreparedStatementParameters(PreparedStatement statement) throws SQLException {
for (int i = 0; i < parameters.size(); i++) {
statement.setObject(i + 1, parameters.get(i));
}
parameters.clear();
}
/**
* Activates a result set, that was initiated by a previous query executed.
*
* @param name the name of the connection that initiated a result set.
*/
@Command(name = "activateResultSet", arguments = {"name"}, example = "|activateResultSet; |name|")
public void activateResultSet(String name) {
if (!resultSetManagers.containsKey(name)) {
ConditionalExceptionType.fail("No active result set found for connection '%s'.", name);
}
this.activeResultSetManager = resultSetManagers.get(name);
try {
LOG.info("===== BEGIN Resultset " + name + " =====");
printResultSet(activeResultSetManager.getResultSet());
LOG.info("======= END Resultset " + name + " =====");
} catch (Exception e) {}
}
public static void printResultSet(ResultSet rs) throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= columnsNumber; i++) {
if (i > 1) System.out.print(" | ");
System.out.print(rs.getString(i));
}
System.out.println("");
}
}
/**
* Retreives a value (the specified column) from the resultset at the given row..
*
*/
@Command(name = "getValue", arguments = {"row", "column"}, example = "|$value= |getValue; |10|mycolumn|")
public Object getValue(int index, String column) {
return this.activeResultSetManager.getValue(index,column);
}
@Command(name = "verifyValue", arguments = {"row", "column", "pattern"}, example = "|verifyValue; |10|mycolumn|pattern|")
public boolean verifyValue(int index, String column, String pattern) {
return this.activeResultSetManager.verifyValue(index, column, pattern);
}
/**
* Retreives a value (the specified column) from the resultset at the given row..
*
*/
@Command(name = "getNumberOfRows", arguments = {}, example = "|$count= |getNumberOfRows; |")
public int getNumberOfRows() {
return this.activeResultSetManager.getNumberOfRows();
}
@Command(name = "verifyNumberOfRows", arguments = {"expectedNumberOfRows"}, example = "|verifyNumberOfRows; |1|")
public boolean verifyNumberOfRows(int expectedNumberOfRows) {
return this.activeResultSetManager.verifyNumberOfRows(expectedNumberOfRows);
}
/**
* Performs a query on the database using the connection with the specified name.
*
* The query should result in a single row with a single column, in case of more
* columns then only the first column will be returned.
*
* @param name the name of the connection to use.
* @param sql the sql statement to execute.
* @return the resultset.
*/
@Command(name = "performSelectSingleResult", arguments = {"name", "sql"}, example = "|$value= |performSelectSingleResult; |name|select column from table where 1 = 1|")
public Object performSelectSingleResult(String name, String sql) {
Connection connection = findConnectionByName(name);
try {
PreparedStatement statement = connection.prepareStatement(sql);
setPreparedStatementParameters(statement);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
return resultSet.getObject(1);
}
return "#[NULL]#";
} catch (SQLException | NullPointerException e) {
ConditionalExceptionType.fail("Failed to execute query '%s' due to the following '%s'.", sql, e.getMessage());
}
return "#[NULL]#";
}
@Command(name = "performSelectSingleResultUntilResultPresent", arguments = {"name", "sql", "expectedResult", "maxAttempts"}, example = "|performSelectSingleResultUntilResultPresent;|name|select column from table where 1=1|resultsInThisString|10|")
public boolean performSelectSingleResultUntilResultPresent(String name, String sql, String expectedResult, int maxAttempts) throws InterruptedException {
int milliSecondsBetweenAttempt = 1000;
return performSelectSingleResultUntilResultPresent(name, sql, expectedResult, maxAttempts, milliSecondsBetweenAttempt);
}
@Command(name = "performSelectSingleResultUntilResultPresent", arguments = {"name", "sql", "pattern", "maxAttempts", "timeBetweenAttempts in millis"}, example = "|performSelectSingleResultUntilResultPresent;|name|select column from table where 1=1|resultsInThisString|10|5000|")
public boolean performSelectSingleResultUntilResultPresent(String name, String sql, String pattern, int maxAttempts, int milliSecondsBetweenAttempt) throws InterruptedException {
Boolean isPresent;
try {
isPresent = matchOnPattern(performSelectSingleResult(name, sql).toString(), pattern);
} catch (NullPointerException e) {//catch any null results, they are likely because we are polling
isPresent = false; }
int currentAttempt = 0;
while (isPresent == false && currentAttempt < maxAttempts) {
// als het er niet is doen we dit
Thread.sleep(milliSecondsBetweenAttempt); // wait milliSecondsBetweenAttempt milliseconds
try {
isPresent = matchOnPattern(performSelectSingleResult(name, sql).toString(), pattern); // execute the query again
} catch (NullPointerException e) { //catch any null results
}
currentAttempt++;
}
return isPresent;
}
@Command(name = "performSelectSaveBlobToDisk", arguments = {"name", "sql", "fileName"}, example = "|performSelectSaveBlobToDisk; |name|select column from table where 1 = 1|fileName|")
public Boolean performSelectSaveBlobToDisk(String name, String sql, String fileName) {
try {
saveInputStreamToDisk(getBlobAsStream(name, sql), fileName);
return true;
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
@Command(name = "verifyResultPerformSelectSingleResult", arguments = {"name", "sql", "pattern"}, example = "|verifyResultPerformSelectSingleResult; |name|select column from table where 1 = 1|expectedResult|")
public Boolean verifyResultPerformSelectSingleResult(String name, String sql, String pattern) {
if (pattern.length() == 0) {
pattern = null;
}
return matchOnPattern(performSelectSingleResult(name, sql).toString(), pattern);
}
public InputStream getBlobAsStream(String name, String sql) {
Connection connection = findConnectionByName(name);
try {
PreparedStatement statement = connection.prepareStatement(sql);
setPreparedStatementParameters(statement);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
if (Types.BLOB == resultSet.getMetaData().getColumnType(1)) {
return resultSet.getBlob(1).getBinaryStream();
} else {
ConditionalExceptionType.fail("The column that was found is not a BLOB");
}
} else {
ConditionalExceptionType.fail("No rows were found");
}
} catch (SQLException e) {
ConditionalExceptionType.fail("Failed to execute query '%s' due to the following '%s'.", sql, e.getMessage());
}
return null;
}
@Command(name = "getClobAsString", arguments = {"name", "sql"}, example = "|$result=|getClobAsString; |name|select column from table where 1 = 1|")
public String getClobAsString(String name, String sql) {
Connection connection = findConnectionByName(name);
try {
PreparedStatement statement = connection.prepareStatement(sql);
setPreparedStatementParameters(statement);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
if (Types.CLOB == resultSet.getMetaData().getColumnType(1)) {
Clob cXML = resultSet.getClob(1);
String sXML = cXML.getSubString(1, (int) cXML.length());
return sXML;
} else {
ConditionalExceptionType.fail("The column that was found is not a CLOB");
}
} else {
ConditionalExceptionType.fail("No rows were found");
}
} catch (SQLException e) {
ConditionalExceptionType.fail("Failed to execute query '%s' due to the following '%s'.", sql, e.getMessage());
}
return null;
}
private void saveInputStreamToDisk(InputStream inputStream, String targetFileName) throws IOException, SQLException {
File file = new File(targetFileName);
if (!file.exists()) {
file.createNewFile();
} else {
System.out.println("WARNING! File already exists, it will be overwritten: " + targetFileName);
}
FileUtils.writeStreamToFile(inputStream, file);
}
/**
* Executes the contents of an SQL file using the connection with the specified name.
*
* @param name the name of the connection to use.
* @param sqlFile the file containing the sql statements to execute.
* @return flag indicating if the execution was successful.
*/
@Command(name = "executeSqlFile", arguments = {"name", "sqlFile"}, example = "|executeSqlFile; |name|create_table.sql|")
public boolean executeSqlFile(String name, String sqlFile) {
Connection connection = findConnectionByName(name);
SqlExecutor sqlExecutor = new SqlExecutor();
sqlExecutor.execute(connection, sqlFile);
return true;
}
@Command(name = "executeSqlString", arguments = {"name", "sqlFile"}, example = "|executeSqlString; |name|DELETE FROM Table WHERE ID = '123457890'|")
public void executeSqlString(String name, String sqlString) {
Connection connection = findConnectionByName(name);
SqlExecutor sqlExecutor = new SqlExecutor();
try {
sqlExecutor.processString(connection, sqlString);
} catch (SQLException e) {
ConditionalExceptionType.fail("An exception occurred: '%s'.", e.getMessage());
}
}
@Command(name = "executeSqlStringWithReturnValue", arguments = {"name", "sqlFile"}, example = "|check|executeSqlStringWithReturnValue; |name|DELETE FROM Table WHERE ID = '123457890'|>0|")
public int executeSqlStringWithReturnValue(String name, String sqlString) {
String results = "";
Connection connection = findConnectionByName(name);
SqlExecutor sqlExecutor = new SqlExecutor();
return sqlExecutor.processStringWithResults(connection, sqlString);
}
/**
* Closes a database connection with the specified name.
*
* @param name the name of the database connection.
*/
@Command(name = "closeConnection", arguments = {"name"}, example = "|closeConnection; |name|")
public void closeConnection(String name) {
Connection connection = findConnectionByName(name);
try {
connection.close();
} catch (SQLException sqlException) {
// Ignore this message.
} finally {
connections.remove(name);
}
}
/**
* Close all open database connections.
*/
@Command(name = "closeAllConnection", arguments = {}, example = "|closeAllConnections; |")
public void closeAllConnections() {
for (String name : connections.keySet()) {
try {
Connection connection = connections.get(name);
connection.close();
} catch (SQLException sqlException) {
// Ignore this message.
} finally {
connections.remove(name);
}
}
}
/**
* Finds a database connection with the specified name.
*
* @param name the name of the database connection.
* @return the database connection.
* @throws ConditionalExceptionType when the database connection with the specified name could not be found.
*/
private Connection findConnectionByName(String name) {
if (connections.containsKey(name)) {
return connections.get(name);
}
ConditionalExceptionType.fail("No database connection found with name '%s'.", name);
return null;
}
/**
* Creates a new database connection with the specified properties file.
*
* @param properties the properties file containing the connection settings.
* @return the database connection.
* @throws ConditionalExceptionType when the driver class could not be found or when the connection could not be opened.
*/
private Connection createConnection(String properties) {
PropertiesFixture propertiesFixture = new PropertiesFixture(properties);
String databaseDriver = propertiesFixture.getStringProperty(CONNECTION_DATABASE_DRIVER);
try {
Class.forName(databaseDriver);
} catch (ClassNotFoundException classNotFoundException) {
ConditionalExceptionType.fail("Cannot find database driver class '%s'.", databaseDriver);
}
String databaseUrl = propertiesFixture.getStringProperty(CONNECTION_DATABASE_URL);
String databaseUsername = propertiesFixture.getStringProperty(CONNECTION_DATABASE_USERNAME);
String databasePassword = propertiesFixture.getStringProperty(CONNECTION_DATABASE_PASSWORD);
try {
return DriverManager.getConnection(databaseUrl, databaseUsername, databasePassword);
} catch (SQLException sqlException) {
ConditionalExceptionType.fail("Cannot open connection to '%s' because '%s'.", databaseUrl, sqlException.getMessage());
}
return null;
}
/**
* Sets a parameter that will be put in a "?" placeholder in the SQL statement.
* See prepared statement.
*/
@Command(name = "selectionParameter", arguments = {"parameter"}, example = "|selectionParameter; |12345|")
public boolean selectionParameter(String parameter) {
parameters.add(parameter);
return true;
}
}