package com.webstersmalley.mediacollection.database;
   
   import java.io.IOException;
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   
  import org.apache.log4j.Logger;
  import org.apache.log4j.PropertyConfigurator;
  
  public class DatabaseConnection
  {
  	private static Logger log = Logger.getLogger(DatabaseConnection.class);
  	static
  	{
  		PropertyConfigurator.configure("log4j.properties");
  	}
  	
  	public DatabaseConnection(String databaseName, boolean create) throws ClassNotFoundException, SQLException
  	{
  		String connectionStringStub = "jdbc:derby:" + databaseName + ";";
  		this.databaseName = databaseName;
          Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
  		if (create)
  		{
  			this.connection = DriverManager.getConnection(connectionStringStub + "create=true");
  		}
  		else
  		{
  			this.connection = DriverManager.getConnection(connectionStringStub);
  		}
  	}
  	
  	private String databaseName;
  	private Connection connection;
  	
      public static void drop(String databaseName) throws IOException
      {
      	shutdown(databaseName);
      	Utilities.purgeDirectory(databaseName);
      }
      
  	public void execute(String sql) throws SQLException
  	{
  		if (sql == null || (sql.trim().equals("")))
  		{
  			log.debug("Skipping null command");
  			return;
  		}
  		log.debug("Running statement: " + sql);
  		connection.prepareStatement(sql).execute();
  	}
      
  	/**
  	 * @return Returns the connection.
  	 */
  	public Connection getConnection() {
  		return connection;
  	}
  
  	/**
  	 * @param connection The connection to set.
  	 */
  	public void setConnection(Connection connection) {
  		this.connection = connection;
  	}
  
  	public ResultSet executeQuery(String sql) throws SQLException
  	{
  		if (sql == null || (sql.trim().equals("")))
  		{
  			log.debug("Skipping null command");
  			return null;
  		}
  		log.debug("Executing query: " + sql);
  		PreparedStatement ps = connection.prepareStatement(sql);
  		return ps.executeQuery();
  	}
  	
      public void runScript(String filename) throws IOException, SQLException
      {
      	log.info("Running script: " + filename);
  		ScriptReader sr = new ScriptReader(filename);
  		String[] commands = sr.splitScript();
  		for (int i = 0 ; i < commands.length ; i++)
  		{
  			execute(commands[i]);
  		}    	
      }
      
      public static void shutdown(String databaseName)
      {
      	try
  		{
      		DriverManager.getConnection("jdbc:derby:" + databaseName + "shutdown=true");
  		}
      	catch (SQLException se)
 		{
     		// Do nothing - we expect this!
 		}
     }
     
     public static String escapeString(String input) {
     	return input.replaceAll("'", "''");
     }
     
 }