/*
* Copyright (c) MuleSoft, Inc. All rights reserved. http://www.mulesoft.com
* The software in this package is published under the terms of the CPAL v1.0
* license, a copy of which has been included with this distribution in the
* LICENSE.txt file.
*/
package org.mule.munit;
import static junit.framework.Assert.assertEquals;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.StringWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import au.com.bytecode.opencsv.CSVWriter;
import org.apache.log4j.Logger;
import org.h2.tools.RunScript;
public class DatabaseServer
{
private static Logger logger = Logger.getLogger(DatabaseServer.class);
/**
* <p>H2 Database name</p>
*/
private String database;
/**
* <p>Name of (or path to) the SQL file whose statements will be executed when the database is started</p>
*/
private String sqlFile;
/**
* <p>CSV files (separated by semicolon) that creates tables in the database using the file name (without the
* termination, ".csv") as the table name and its columns as the table columns</p>
*/
private String csv;
/**
* <p>The database connection</p>
*/
private Connection connection;
public DatabaseServer(String database, String sqlFile, String csv)
{
this.database = database;
this.sqlFile = sqlFile;
this.csv = csv;
}
/**
* <p>Starts the server</p>
* <p>Executes the correspondent queries if an SQL file has been included in the dbserver configuration</p>
* <p>Creates the correspondent tables in the database if a CSV file has been included in the dbserver
* configuration</p>
*/
public void start()
{
try
{
addJdbcToClassLoader();
connection = DriverManager.getConnection("jdbc:h2:mem:" + database);
executeQueriesFromSQLFile(connection);
Statement stmt = connection.createStatement();
createTablesFromCsv(stmt);
}
catch (Exception e)
{
throw new DatabaseServerException("Could not start the database server", e);
}
}
/**
* <p>Executes the SQL query received as parameter</p>
*
* @param sql query to be executed
* @return result of the SQL query received
*/
public Object execute(String sql)
{
Statement statement = null;
try
{
statement = connection.createStatement();
return statement.execute(sql);
}
catch (SQLException e)
{
logger.error("There has been a problem while executing the SQL statement", e);
throw new DatabaseServerException("There has been a problem while executing the SQL statement",e);
}
}
/**
* <p>Executes a SQL query</p>
*
* @param sql query to be executed
* @return result of the SQL query in a JSON format.
*/
public Object executeQuery(String sql)
{
try
{
return getMap(sql);
}
catch (SQLException e)
{
logger.error("There has been a problem while executing the SQL statement", e);
throw new DatabaseServerException("There has been a problem while executing the SQL statement",e);
}
}
/**
* <p>Executes a SQL query</p>
*
* @param query query to be executed
* @param returns Expected value
*/
public void validateThat(String query, String returns)
{
try
{
Writer writerQueryResult = getResults(query);
assertEquals(writerQueryResult.toString().trim(), returns.replace("\\n", "\n"));
}
catch (ClassCastException ccException)
{
throw new RuntimeException("The JSON String must always be an array");
}
catch (SQLException e)
{
throw new RuntimeException("Invalid Query");
}
catch (IOException e)
{
throw new RuntimeException("Could no access to query results");
}
}
/**
* <p>Stops the server.</p>
*/
public void stop()
{
try
{
if (connection != null)
{
connection.close();
}
}
catch (SQLException e)
{
throw new RuntimeException("Could not stop the database server", e);
}
}
private void addJdbcToClassLoader() throws InstantiationException,
IllegalAccessException, ClassNotFoundException
{
Class.forName("org.h2.Driver").newInstance();
}
private void executeQueriesFromSQLFile(Connection conn) throws SQLException, FileNotFoundException
{
if (sqlFile != null)
{
InputStream streamImput = getClass().getClassLoader().getResourceAsStream(sqlFile);
RunScript.execute(conn, new InputStreamReader(streamImput));
}
}
private void createTablesFromCsv(Statement stmt)
{
if (csv != null)
{
String[] tables = csv.split(";");
for (String table : tables)
{
String tableName = table.replaceAll(".csv", "");
try
{
stmt.execute("CREATE TABLE " + tableName + " AS SELECT * FROM CSVREAD(\'classpath:" + table + "\');");
}
catch (SQLException e)
{
throw new RuntimeException("Invalid SQL, could not create table " + tableName + " from " + table);
}
}
}
}
private List<Map<String, String>> getMap(String sql) throws SQLException
{
Statement statement;
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
List<Map<String, String>> jsonArray = new ArrayList<Map<String, String>>();
ResultSetMetaData metaData = resultSet.getMetaData();
while (resultSet.next())
{
HashMap<String, String> jsonObject = new HashMap<String, String>();
for (int i = 1; i <= metaData.getColumnCount(); i++)
{
String columnName = metaData.getColumnName(i);
jsonObject.put(columnName, String.valueOf(resultSet.getObject(columnName)));
}
jsonArray.add(jsonObject);
}
return jsonArray;
}
private Writer getResults(String sql) throws SQLException, IOException
{
Statement statement;
statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
Writer writer = new StringWriter();
CSVWriter csvwriter = new CSVWriter(writer);
csvwriter.writeAll(resultSet, true);
return writer;
}
}