package com.parallels.pba.autotests.lib.database;
import com.google.common.base.Charsets;
import com.google.common.io.Files;
import com.google.common.io.Resources;
import com.parallels.autotests.framework.logger.Logger;
import com.parallels.pba.autotests.general.testNameAppender.PBATestListener;
import com.parallels.pba.autotests.lib.constants.Constants;
import org.apache.commons.io.FileUtils;
import org.apache.derby.jdbc.EmbeddedDriver;
import java.io.*;
import java.net.URL;
import java.sql.*;
/**
* Defines object "Database" and all respective operations.
*/
public class Database implements Constants {
private final static String DATABASE_PATH = "./db";
private final static String SQL_RESOURCE_NAME = "pba-test.sql";
private final static String LAST_MODIFIED_FILE_PATH = "./last-modified.txt";
private static Database testDataStore = new Database();
/**
* Database connection
*/
private Connection connection;
/**
* SQL-statement for database management
*/
private PreparedStatement preparedStatement;
/**
* SQL-query results
*/
private ResultSet resultSet;
/**
* @return the testDataStore
*/
private static Database getTestDataStore() {
if (!testDataStore.isConnected()) {
testDataStore.establishConnection();
}
return testDataStore;
}
public static Result executeQuery(String query) {
if (!Database.getTestDataStore().isConnected()) {
throw new RuntimeException("No connection with test data store.");
}
try {
getTestDataStore().setPreparedStatement(getTestDataStore().getConnection().prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
getTestDataStore().setResultSet(getTestDataStore().getPreparedStatement().executeQuery());
getTestDataStore().getResultSet().first();
} catch (SQLException sqlException) {
throw new RuntimeException(sqlException);
}
return new Result(getTestDataStore().getResultSet());
}
/**
* Executes SQL-query on specified connection return result in result set.
*/
public static Result executeQuery(String query, Integer id) {
executeQuery(query, id, null);
try {
getTestDataStore().getResultSet().first();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
return new Result(getTestDataStore().getResultSet());
}
public static Result executeQuery(String query, String mark) {
executeQuery(query, null, mark);
return new Result(getTestDataStore().getResultSet());
}
private static void executeQuery(String query, Integer id, String mark) {
if (!Database.getTestDataStore().isConnected()) {
throw new RuntimeException("No connection with test data store.");
}
try {
query += " AND LOWER(test_name) = LOWER(?)";
getTestDataStore().setPreparedStatement(getTestDataStore().getConnection().prepareStatement(
query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
if (mark != null) {
getTestDataStore().getPreparedStatement().setString(1, mark);
getTestDataStore().getPreparedStatement().setString(2, PBATestListener.getCurrentTestName());
} else if (id == null) {
getTestDataStore().getPreparedStatement().setInt(1, 1);
getTestDataStore().getPreparedStatement().setString(2, DEFAULT_TEST_NAME);
} else {
getTestDataStore().getPreparedStatement().setInt(1, id);
getTestDataStore().getPreparedStatement().setString(2, PBATestListener.getCurrentTestName());
}
getTestDataStore().setResultSet(getTestDataStore().getPreparedStatement().executeQuery());
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
public static Result getObjectById(String tableName, Integer id) {
if (!Database.getTestDataStore().isConnected()) {
throw new RuntimeException("No connection with test data store.");
}
executeQuery(String.format("SELECT * FROM %s WHERE id = ?", tableName), id);
return new Result(getTestDataStore().getResultSet());
}
public void establishConnection() {
if (!isConnected()) {
try {
EmbeddedDriver.class.newInstance();
} catch (Exception e) {
throw new RuntimeException("Can't register Derby JDBC driver. Reason: " + e.getMessage());
}
String currentRequest = "";
try {
Logger.debug("Creating connection to tests database");
if (!isDatabaseResetRequired()) {
connection = DriverManager.getConnection(String.format("jdbc:derby:%s;create=true", DATABASE_PATH));
setConnection(connection);
return;
}
if (new File(DATABASE_PATH).exists()) {
FileUtils.deleteDirectory(new File(DATABASE_PATH));
}
connection = DriverManager.getConnection(String.format("jdbc:derby:%s;create=true", DATABASE_PATH));
setConnection(connection);
InputStream bis = (InputStream) Resources.getResource(SQL_RESOURCE_NAME).getContent();
InputStreamReader isr = new InputStreamReader(bis);
BufferedReader reader = new BufferedReader(isr);
String line;
StringBuilder sb = new StringBuilder();
Statement statement = connection.createStatement();
Logger.debug("Database creation started");
while ((line = reader.readLine()) != null) {
if (!line.isEmpty()) {
sb.append(line);
if (line.trim().endsWith(";")) {
currentRequest = sb.toString().replaceAll("\\\\r\\\\n", "\r\n");
statement.execute(currentRequest.subSequence(0, currentRequest.length() - 1).toString());
sb = new StringBuilder();
}
}
}
Logger.debug("Database successfully created");
statement.close();
} catch (Exception e) {
throw new RuntimeException(String.format("Error while executing SQL query: '%s': '%s'", currentRequest, e.getMessage()));
}
}
}
private boolean isDatabaseResetRequired() {
long sqlSourceLastModified = 0;
try {
URL sqlSourceUrl = Resources.getResource(SQL_RESOURCE_NAME);
sqlSourceLastModified = sqlSourceUrl.openConnection().getLastModified();
if (new File(DATABASE_PATH).exists() && new File(LAST_MODIFIED_FILE_PATH).exists()) {
long lastModified = Long.parseLong(Files.toString(new File(LAST_MODIFIED_FILE_PATH), Charsets.UTF_8));
if (sqlSourceLastModified == lastModified) {
return false;
}
}
} catch (Exception e) {
Logger.debug(String.format("Test database should be recreated since '%s'", e.getMessage()));
}
// writing last modified date into temporary file
try {
Writer writer = new BufferedWriter(new FileWriter(LAST_MODIFIED_FILE_PATH, false));
writer.write(String.valueOf(sqlSourceLastModified));
writer.close();
return true;
} catch (Exception exception) {
throw new RuntimeException(exception);
}
}
/**
* Defines if connection to database is established or not.
*
* @return true - connection with Derby database is established, false -
* connection with Derby database is not established.
*/
public Boolean isConnected() {
return getConnection() != null;
}
/**
* @return the connection
*/
public Connection getConnection() {
return connection;
}
/**
* @param connection the connection to set
*/
public void setConnection(Connection connection) {
this.connection = connection;
}
/**
* @return the preparedStatement
*/
public PreparedStatement getPreparedStatement() {
return preparedStatement;
}
/**
* @param preparedStatement the preparedStatement to set
*/
public void setPreparedStatement(PreparedStatement preparedStatement) {
this.preparedStatement = preparedStatement;
}
/**
* @return the resultSet
*/
public ResultSet getResultSet() {
return resultSet;
}
/**
* @param resultSet the resultSet to set
*/
public void setResultSet(ResultSet resultSet) {
this.resultSet = resultSet;
}
}