/**
 * Copyright (c) 2011, Martin Foster <martin@ethereal-realms.org>
 * All rights reserved.
 * 
 * Redistribution and use in source and binary forms, with or without 
 * modification, are permitted provided that the following conditions are met:
 * 
 *     * Redistributions of source code must retain the above copyright notice, 
 *       this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright 
 *       notice, this list of conditions and the following disclaimer in the 
 *       documentation and/or other materials provided with the distribution.
 *     * Neither the name of the Ethereal-Realms.org nor the names of its
 *       contributors may be used to endorse or promote products derived
 *       from this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package org.lyra.Database;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;

import org.lyra.Ledger;
import org.sqlite.SQLiteConfig;

/**
 * The database interface exists to implement additional functionality and to
 * provide a certain speed increase by caching the music database.
 * <p>
 * @author Martin Foster
 * @version 1.0.4, %Revision, 134%, %LastChangedDate, 24/09/11 1:47AM%
 */
public class Interface {

	// Logger class wrapper
	private Ledger Logger;
	
	// Database elements
	private Connection Conn;
	
	// Cached update handle
	private PreparedStatement myUpdates;
	
	// Cached query
	private String myQuery;
	
	
	/*
	 * CONSTANTS
	 */
	
	// Carriage return for platform independence
	private static final String CR = System.getProperty("line.separator");
	
	// Class generating updates
	private static final String CLASS = "org.lyra.Database.Interface";
	
	//  Host Database Elements
	private static final String HOST = "HOST";
	
	// Language Database Elements
	private static final String LANG = "LANG";
	
	
	/**
	 * Standard constructor for this class.   This will create a database
	 * element if possible.  If this fails during the Connect phase then
	 * the Class calling this store should not continue with execution.
	 * <p>
	 * @param logger Logging capability.
	 */
	public Interface(Ledger logger) {
		// Pass up
		this.Logger = logger;
	}
	// End of Constructor Method

	
	/**
	 * Will conduct the initial connection and logging to the SQLite database
	 * store.   If there are problems this component will return false and the
	 * program can terminate gracefully.
	 * <p>
	 * @param path File containing path to the database.
	 * @return boolean True/False statement about successful connection.
	 */
	public boolean Connect(File path) {
		
		/*
		 * Attempt to connect to the database store
		 */
	    try {
	    	// Load the JDBC driver
			Class.forName("org.sqlite.JDBC");

	 
			// Connect and establish information
		    try {
		    	// Point driver to the database store
				Conn = DriverManager.getConnection("jdbc:sqlite:" + path.getAbsolutePath());

				/*
				 * This section aims to setup additional components of the
				 * SQLite database connection.
				 */
				SQLiteConfig conf = new SQLiteConfig();
				
				// Foreign Key Constraints Enabled
				conf.enforceForeignKeys(true);
				
				// Additional trigger handling
				conf.enableRecursiveTriggers(true);
				
				// Apply the settings
				conf.apply(Conn);
		
				// Every transaction should take place one after another
				Conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
				
				// Return true
				return true;
			} 
		    
		    // Log as severe due to the fact that execution will not continue
		    catch (SQLException e) {
				Logger.Severe(CLASS, "Connect", e);
			}

		} 

	    // Log as severe due to the fact that execution will not continue
	    catch (ClassNotFoundException e) {
	    	Logger.Severe(CLASS, "Connect", e);			
		}

	    // Connection to database failed fail method
	    return false;
	}
	// End of Connect Method

	
	/**
	 * Will cleanly close the database connection in order to avoid any
	 * potential file locks that could be introduced otherwise.
	 */
	public void Disconnect() {
		
		// Close the connection
		try {
			// Attempt
			Conn.close();
		} 
		
		// Internal database error
		catch (SQLException e) {
			
			// Can't even disconnect properly
			Logger.Severe(CLASS, "Disconnect", e);
		}
	} 
	// End Disconnect
	
	
	/**
	 * When logging levels are set to CONFIG a significant amount of
	 * information will be handed to the user about the database and how it
	 * operates.  
	 * <p>
	 * The first round of information pulled from the database will be SQLite
	 * Meta Data.   This can be especially useful to determine exactly what
	 * you are connected to.
	 * <table>
	 *  <tr>
	 *   <td><b>getURL</b></td>
	 *   <td>The connection string used by the JDBC driver.   Useful to
	 *       find out if the paths are correct.</td>
	 *  </tr>
	 *  <tr>
	 *   <td><b>getUserName</b></td>
	 *   <td>The user name of the database connection.  Normally this will never
	 *       apply to a SQLite database.</td>
	 *  </tr>  
	 *  <tr>
	 *   <td><b>getDatabaseProductName</b></td>
	 *   <td>Database name.   This will normally always appear as SQLite</td>
	 *  </tr>
	 *  <tr>
	 *   <td><b>getDatabaseProductVersion</b></td>
	 *   <td>The version of the database file which is supported.  Normally 3.6.4.</td>
	 *  </tr>
	 *  <tr>
	 *   <td><b>getDriverName</b></td>
	 *   <td>This should indicate SQLiteJDBC as the code only employs this driver.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>getDriverVersion</b></td>
	 *   <td>Normally you would get a version number.  However with the JDBC 
	 *       driver used you will either get <i>native</i> to indicate native
	 *       drivers are used or <i>java</i> to indicate the driver is using
	 *       slower drivers for compatibility</td>
	 *  </tr> 
	 * </table>
	 * <p>
	 * Information about the SQLite PRAGMA system is also included here.  Some
	 * of the features may not be supported due to version issues.  If that
	 * is the case the NOT SUPPORTED descriptor will be appended instead of the
	 * proper database value.  The current PRAGMA types searched for are as
	 * follows:
	 * <p>
	 * <table>
	 *  <tr>
	 *   <td><b>PRAGMA auto_vacuum</b></td>
	 *   <td>Query or set the auto-vacuum status in the database.</td>
	 *  </tr>
	 *  <tr>
	 *   <td><b>PRAGMA cache_size</b></td>
	 *   <td>Query or change the suggested maximum number of database disk
     *       pages that SQLite will hold in memory at once per open database
     *       file.</td>
	 *  </tr>  
	 *  <tr>
	 *   <td><b>PRAGMA case_sensitive_like</b></td>
	 *   <td>The default behaviour of the LIKE operator is to ignore case for
     *       ASCII characters. Hence, by default 'a' LIKE 'A' is true.</td>
	 *  </tr>
	 *    <tr>
	 *   <td><b>PRAGMA count_changes</b></td>
	 *   <td>Query or change the count-changes flag.</td>
	 *  </tr>
	 *  <tr>
	 *   <td><b>PRAGMA default_cache_size</b></td>
	 *   <td>This pragma queries or sets the suggested maximum number of
     *       pages of disk cache that will be allocated per open database
     *       file.</td>
	 *  </tr>  
	 *  <tr>
	 *   <td><b>PRAGMA empty_result_callbacks</b></td>
	 *   <td>Query or change the empty-result-callbacks flag.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA encoding</b></td>
	 *   <td>In first form, if the main database has already been created,
	 *       then this pragma returns the text encoding used by the
	 *       main database, one of "UTF-8", "UTF-16le" (little-endian
	 *       UTF-16 encoding) or "UTF-16be" (big-endian UTF-16
	 *       encoding).</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA foreign_keys</b></td>
	 *   <td>Query, set, or clear the enforcement of foreign key 
         *       constraints. </td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA full_column_names</b></td>
	 *   <td>Query or change the full_column_names flag.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA fullfsync</b></td>
	 *   <td>Query or change the fullfsync flag.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA journal_mode</b></td>
	 *   <td>This pragma queries or sets the journal mode for databases
         *       associated with the current database connection.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA journal_size_limit</b></td>
	 *   <td>If a database connection is operating in either "exclusive
	 *       mode" (PRAGMA locking_mode=exclusive) or "persistent journal
	 *       mode" (PRAGMA journal_mode=persist) then under certain
	 *       circumstances after committing a transaction the journal
	 *       file may remain in the file-system.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA legacy_file_format</b></td>
	 *   <td>This pragma sets or queries the value of the legacy_file_format
	 *       flag. When this flag is on, new SQLite databases are created
	 *       in a file format that is readable and writable by all versions
	 *       of SQLite going back to 3.0.0. When the flag is off, new
	 *       databases are created using the latest file format which
	 *       might not be readable or writable by versions of SQLite
	 *       prior to 3.3.0.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA locking_mode</b></td>
	 *   <td>This pragma sets or queries the database connection 
	 *       locking-mode. The locking-mode is either NORMAL
	 *       or EXCLUSIVE. </td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA page_size</b></td>
	 *   <td>Query or set the page size of the database.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA max_page_count</b></td>
	 *   <td>Query or set the maximum number of pages in the database file.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA read_uncommitted</b></td>
	 *   <td>Query, set, or clear READ UNCOMMITTED isolation.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA recursive_triggers</b></td>
	 *   <td>Query, set, or clear the recursive trigger capability. </td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA reverse_unordered_selects</b></td>
	 *   <td>When enabled, this PRAGMA causes SELECT statements without
	 *       a an ORDER BY clause to emit their results in the reverse
 	 *       order of what they normally would.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA short_column_names</b></td>
	 *   <td>Query or change the short-column-names flag.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA synchronous</b></td>
	 *   <td>Query or change the setting of the "synchronous" flag.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA temp_store</b></td>
	 *   <td>Query or change the setting of the "temp_store" parameter.</td>
	 *  </tr> 
	 *  <tr>
	 *   <td><b>PRAGMA temp_store_directory</b></td>
	 *   <td>Query or change the setting of the "temp_store_directory" - the
	 *       directory where files used for storing temporary tables
	 *       and indices are kept.</td>
     *  </tr>
	 * </table>
	 * <p>
	 * Additionally a complete list of all tables will also be displayed.  
	 * This can be a great asset when determining if tables have been added
	 * or removed.
	 */
	public void debugDatabase() {
		
		// Should we continue with MetaData information
		if (Logger.isLoggable("CONFIG")) {
			// Capture invalid queries
			try {
				
				/*
				 * Meta Data information contains a lot of information on what
				 * the database can support and how it will operate.  You can
				 * even use it to fetch information on view and table
				 * information which we cover only at name level.
				 */
				
				// Meta Data handle
				DatabaseMetaData mtdt;
				mtdt = Conn.getMetaData();

				// String Buffer
				StringBuffer sb = new StringBuffer();
			
				/*
				 * This section will pull key pieces of information about
				 * the database connection itself.
				 */
				sb.append("-- Database Settings --" + CR);
			
				// Individual elements
				sb.append("getURL: " 						+ mtdt.getURL() + CR);
				sb.append("tgetDatabaseProductName: " 		+ mtdt.getDatabaseProductName() + CR);
				sb.append("tgetDatabaseProductVersion: " 	+ mtdt.getDatabaseProductVersion() + CR);
				sb.append("tgetDriverName: " 				+ mtdt.getDriverName() + CR);
				sb.append("tgetDriverVersion: " 			+ mtdt.getDriverVersion() + CR);
			
				// Log this output
				Logger.Config(sb.toString());
			
				// Clear buffer
				sb = new StringBuffer();
			
			
				/*
				 * SQLite has a lot of specific information that are stored
				 * under PRAGMAS and can be used to tweak the database
				 * response if necessary.   
				 */
			
				// Title
				sb.append("-- Database PRAGMA Information --" + CR);
			
				// Create statement
				Statement spragma = Conn.createStatement();
			
				// List of PRAGMAS to display]
				String [] elements = {"auto_vacuum", "cache_size", "case_sensitive_like",
									  "count_changes", "default_cache_size", "fullfsync",
									  "empty_result_callbacks", "encoding", "foreign_keys",
									  "full_column_names", "journal_mode", "page_size",
									  "journal_size_limit", "legacy_file_format", 
									  "locking_mode", "max_page_count", "read_uncommitted",
									  "recursive_triggers", "reverse_unordered_selects",
									  "short_column_names", "synchronous", "temp_store",
									  "temp_store_directory"};
			
				// Loop through
				for (String single: elements) {
					// Query for this value
					try {
						ResultSet pragma = spragma.executeQuery("PRAGMA " + single);
				
						// Should only be one record
						while (pragma.next()) {
							// Append to string buffer
							sb.append(single + ": " + pragma.getString(1) + CR);
						}
					}
				
					// Execution failed
					catch (SQLException e) {
						// Display as such
						sb.append(single + ": NOT SUPPORTED" + CR);
					}
				}
			
				// Log this output
				Logger.Config(sb.toString());
			
				// Clear buffer
				sb = new StringBuffer();
			
				/*
				 * This section will pull table and view information from
				 * the database.  Very useful for debugging especially if
				 * you suspect the table has been removed.
				 */
			
				// Title
				sb.append("-- Database Tables and Views --" + CR);
			
				// What we are looking for
				String [] view = {"TABLE","VIEW"};
			
				// Produce a Result Set
				ResultSet tables = mtdt.getTables(null, null, null, view);
			
				// Loop and place into buffer
				while (tables.next()) {
					// Schema location
					sb.append(tables.getString("TABLE_SCHEM"));
		    	
					// Table name
					sb.append("." 	+ tables.getString("TABLE_NAME") + CR);
				}
		    
				// Close Result Set
				tables.close();
		    
				// Log this output
				Logger.Config(sb.toString());
			}
			
			// One of the events above have failed
			catch (SQLException e) {
				// Log and move on
				Logger.Warn(CLASS, "Debug", e);
			}
		}
	}
	// End of Debug Method

	
	/**
	 * One of the more critical components of this Class is the ability to
	 * abstract the SQL Query process.
	 * 
	 * @param query A string defining the queries
	 * @param parameters Elements of string which contain the elements
	 * @return myConnections
	 */
	public myConnections Query(String query, String... parameters) {

		// Connection handle
		myConnections opened = new myConnections();
		
		// Debugging information
		if (Logger.isLoggable("FINE")) QueryDebug("Query", query, parameters);
		
		// Capture exceptions
		try {
			// Prepare query
			PreparedStatement ps = Conn.prepareStatement(query);
			
			// Keep track of handle
			opened.setPreparedStatement(ps);
			
			
			// Counter to keep track of position
			int count = 1;
		
			// Loop through and append arguments to query
			for (String arg : parameters) {
				
				// Include elements
				ps.setString(count, arg);
			
				// Increment
				count++;
			}
    
			// Execute above query with no parameters
			ResultSet rs = ps.executeQuery();
			
			// Keep track of handle
			opened.setResultSet(rs);
		} 
		
		// Generate failure of query
		catch (SQLException e) {
			Logger.Warn(CLASS, "Query", e);
		}
		
		return opened;
	} 
	// End Query

	
	/**
	 * Debugging capability for the query string handler.  Used to format the
	 * query text as an ability to run the query independently if need be.
	 * 
	 * @param query A string defining the queries
	 * @param parameters Elements of string which contain the elements
	 */
	private void QueryDebug(String method, String query, String... parameters) {
		
		// Loop through and append arguments to query
		for (String arg : parameters) {
			
			// Make safe
			arg = Matcher.quoteReplacement(arg);
			
			// Replace the elements properly escaped for the query
			query = query.replaceFirst("\\?", "'" + arg + "'");
		}
			
		// Log to logger
		Logger.Fine(CLASS, "queryDebug", query);
	} 
	// End Query Debug

	/**
	 * This method is useful for a multiple column result.  It will determine
	 * the column names and then use those to create a hash map. 
	 * <p>
	 * @param query String query to generate.
	 * @param parameters Array of parameters to append to the query.
	 * @return HashMap results.
	 */
	public Map<String,String> getRow(String query, String... parameters) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		// Storage container
		Map<String,String> rowstore = new HashMap<String,String>();
		
		try {
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();
			
			// What was returned
			ResultSetMetaData rowdata = rs.getMetaData();
			int colcount = rowdata.getColumnCount();
			
			// First available
			if (rs.next()) {
				
				// Loop through rows and create the HashMap
				for (int col=1; col <= colcount; col++) {
					rowstore.put(rowdata.getColumnName(col), rs.getString(col));
				}
			}
			
			// Close record set
			opened.close();
		}
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getRow", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
		
		// Returns the row store
		return rowstore;
	}
	// End of getRow Method
	
	
	/**
	 * This method is useful for a multiple column result.  It will determine
	 * the column names and then use those to create a hash map and additionally
	 * throw it into a list.
	 * <p>
	 * @param list List containing all of the results.
	 * @param query String query to generate.
	 * @param parameters Array of parameters to append to the query.
	 */
	public void getRows(List<Map<String,String>> list, String query, String... parameters) {

		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		try {
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();
			
			// What was returned
			ResultSetMetaData rowdata = rs.getMetaData();
			int colcount = rowdata.getColumnCount();
			
			// First available
			while (rs.next()) {
				
				// Storage container
				Map<String,String> rowstore = new HashMap<String,String>();
				
				// Loop through rows and create the HashMap
				for (int col=1; col <= colcount; col++) {
					rowstore.put(rowdata.getColumnName(col), rs.getString(col));
				}
				
				// Add into list
				list.add(rowstore);
			}
			
			// Close record set
			opened.close();
		}
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getRows", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
	}
	// End of getRows Method
	

	/**
	 * A query which will return multiple rows must make use of this mechanism
	 * since the getColumn variant only returns a singular column with one or
	 * more rows of data.
	 * <p>
	 * @param rowQuery A variant of the main query which will return a count
	 *                 of rows to return.
	 * @param mainQuery The main query which will return all rows requested.
	 * @param parameters Parameters necessary to run both the Row and Main query.
	 * @return A multi-level array with all of the results.
	 */
	public String[][] getRows(String rowQuery, String mainQuery, String... parameters) {
		
		// Get the amount of rows in this query
		int rows = getInt(rowQuery, parameters);
		
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
			
			
		try {
			// Generate query
			opened = Query(mainQuery, parameters);
			rs = opened.getResultSet();

			/*
			 * Meta Data information is a requirement for the multiple column
			 * retrieval since we need information on how deep the array will
			 * go.
			 */
			int depth = rs.getMetaData().getColumnCount();
			
			// Create the holding array
			String[][] holder = new String[rows][depth];
				
			// Row counter
			int rCount = 0;
				
				
			/*
			 * Loops through individual elements.  This TRY/CATCH statement is
			 * separate from the one above because not getting full row count
			 * will not affect operation, simply performance.
			 */
			while (rs.next()) {
					
				// Safety but should never apply
				if (rCount >= rows) break; 
					
				// Loop though the columns and extract
				for (int dCount=1; dCount <= depth; dCount++)
					holder[rCount][dCount-1] = rs.getString(dCount);
					
				// Increment for re-use
				rCount++;
			}
				
			// Close out
			opened.close();
				
			// Return generated type
			return holder;
		} 
			
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getRows", e);
				
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
					
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
		
		// Return an empty array
		return null;
	}
	// End of getRows Method
	
	
	/**
	 * A query which is meant to return a large data set back as a complex 
	 * data structure containing a Map within a Map.  Which is primarily used
	 * for quick lookups when confirming changes to the database.
	 * <p>
	 * <b>Note:</b> The first row must be unique else the map will override.
	 * <p>
	 * @param query String query to generate.
	 * @param parameters Array of parameters to append to the query.
	 * @return Map<String,Map<String,String> data type containing all results.
	 */
	public Map<String,Map<String,String>> getSet(String query, String... parameters) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		// Storage container
		Map<String,Map<String,String>> pass = new HashMap<String,Map<String,String>>();
		
		try {
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();
			
			// What was returned
			ResultSetMetaData rowdata = rs.getMetaData();
			int colcount = rowdata.getColumnCount();
			
			// First available
			while (rs.next()) {
				
				// Temporary Map
				Map<String,String> rowstore = new HashMap<String,String>();
				
				// Loop through rows and create the HashMap
				for (int col=2; col <= colcount; col++) {
					rowstore.put(rowdata.getColumnName(col), rs.getString(col));
				}
				
				// Create a new map entry the first row must be unique
				pass.put(rs.getString(1), rowstore);
			}
			
			// Close record set
			opened.close();
		}
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getSet", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
		
		// Returns the row store
		return pass;
	}
	// End of getSet Method
	
	
	/**
	 * An extension of the Query method.  This one will take a List as
	 * a parameter and fill it in accordingly based on the Query generated.
	 * <p>
	 * @param list ArrayList to populate.
	 * @param query Query to execute on the Database.
	 * @param parameters Array of strings to pass on as parameters.
	 */
	public void getColumn(List<String> list, String query, String... parameters) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		try {
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();

			/*
			 * Loops through individual elements.  This TRY/CATCH statement is
			 * separate from the one above because not getting full row count
			 * will not affect operation, simply performance.
			 */

			while (rs.next()) {
				/*
				 * Since we are only returning a single dimension ArrayList
				 * makes sense to assume it will only return one element.
				 */
				list.add(rs.getString(1));
			}
			
			// Close out
			opened.close();
		} 
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getColumn", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
	}
	// End of getColumn Method
	
	
	/**
	 * Will return a single element if so required.   Since it can always return
	 * a null set, this will also ensure that a value is always returned as
	 * necessary.
	 * <p>
	 * @param query Query to execute on Database.
	 * @param parameters Array of strings containing parameters to pass on.
	 * @return Integer value as requested.
	 */
	public Integer getInt(String query, String... parameters) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		try {
			
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();

			if (rs.next()) {
				/*
				 * Since we are only returning a single dimension ArrayList
				 * makes sense to assume it will only return one element.
				 */
				return rs.getInt(1);
			}
			
			// Close out
			opened.close();
		}
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getInt", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
		
		// Fall back
		return null;
	}
	// End of getInt Method
	
	
	/**
	 * Will return a single element if so required.   Since it can always return
	 * a null set, this will also ensure that a value is always returned as
	 * necessary.
	 * <p>
	 * @param query Query to execute on Database.
	 * @param parameters Array of strings containing parameters to pass on.
	 * @return Integer value as requested.
	 */
	public String getString(String query, String... parameters) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		try {
			// Generate query
			opened = Query(query, parameters);
			rs = opened.getResultSet();

			if (rs.next()) {
				/*
				 * Since we are only returning a single dimension ArrayList
				 * makes sense to assume it will only return one element.
				 */
				return rs.getString(1);
			}
			
			// Close out
			opened.close();
		}
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getString", e);
			
			// Close all handles	
			try {
				if (opened != null)
					opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
		
		// Fall back
		return null;
	}
	// End of getString Method
	
	
	/**
	 * Generates a Map<String,String> based on the rows returned.  As the name
	 * implies this is mostly used for properties such as language elements and
	 * global settings.
	 * <p>
	 * @param prop Property Map to store the results.
	 * @param query Query string containing elements to run.
	 */
	public void getProperties(Map<String,String> prop, String query) {
		// Holding handles
		ResultSet rs = null;
		myConnections opened = null;
		
		try {
			// Generate query
			opened = Query(query);
			
			// Extract results
			rs = opened.getResultSet();

			/*
			 * Loops through individual elements.  An additional check will be
			 * done in order to confirm that there are no rows already matching
			 * the criteria.  If this is the case they will be skipped over.
			 */
			while (rs.next()) {
				/*
				 * We extract the elements and assume that since this query
				 * is to return properties then there are only two columns to
				 * deal with.
				 */
				String key = rs.getString(1);

				// Create if the element does not already exist
				if (!prop.containsKey(key))
					prop.put(key, rs.getString(2));
			}
			
			// Close out
			opened.close();
		} 
		
		// Failed to draw results
		catch (SQLException e) {
			// Log accordingly
			Logger.Warn(CLASS, "getProperties", e);
			
			// Close all handles	
			try {
				if (opened != null) opened.close();
			} 
				
			// We honestly could care less about failures here
			catch (SQLException e1) {}
		}
	}
	// End of getProperties Method
	
	
	
	/**
	 * Direct pass through to retrieve the MetaData from the database
	 * elements themselves.
	 * <p>
	 * @return DatabaseMetaData instance.
	 * @throws SQLException
	 */
	public DatabaseMetaData getMetaDeta() throws SQLException {
		// Simple pass through
		return Conn.getMetaData();
	}
	// End of getMetaData Method
	

	/**
	 * Provides the ability to update and create elements in the database
	 * while providing an audit table.  This variant will remain active
	 * in order to speed up multiple row updates to the same table.
	 * <p>
	 * @param query A string defining the queries
	 * @param parameters Elements of string which contain the elements
	 * @return Boolean value which indicates success.
	 */
	public boolean updateMultipleRows(String query, String... parameters) {
		
		/*
		 * This method works great if your queries never change.  However in
		 * the case of mass updates it does happen that changes and insertions
		 * actually change based on values that we have currently.
		 * 
		 * We work around this by just closing the PreparedStatement and then
		 * creating a new element.  Its not necessarily pretty nor 100%
		 * efficient, but it is more so then single transactions.
		 */
		if ((myQuery != null) && (!myQuery.equals(query)))
			updateComplete();
		
		
		
		// Debugging information
		if (Logger.isLoggable("INFO")) QueryDebug("Update", query, parameters);
		
		
		// Capture exceptions
		try {			
			/*
			 *  Conduct the initial update as specified by the user
			 */
			
			// Prepare statement
			if (myUpdates == null) {
				
				// Always disable auto committing of changes
				Conn.setAutoCommit(false);
				
				// Send the appropriate update
				myUpdates = Conn.prepareStatement(query);
				
				// Cache for next pass
				myQuery = query;
			}
				
			
			// Counter to keep track of position
			int count = 1;
		
			// Loop through and append arguments to query
			for (String arg : parameters) {
				// Include elements
				myUpdates.setString(count, arg);
			
				// Increment
				count++;
			}
    
			// Execute above query
			myUpdates.executeUpdate();

			// Return true
			return true;
		} 
		
		// Generate failure of query
		catch (SQLException e) {
			Logger.Warn(CLASS, "updateMultipleRows", e);
		}
			
		// The above failed assume the worst	
		return false;
	}
	// End updateMultipleRows Method
	
	
	/**
	 * Provides the ability to update and create elements in the database
	 * while providing an audit table.  A single row will neatly clean up
	 * after itself.
	 * <p>
	 * @param query A string defining the queries
	 * @param parameters Elements of string which contain the elements
	 * @return Boolean value which indicates success in operation.
	 */
	public boolean updateSingleRows(String query, String... parameters) {
		
		// Debugging information
		if (Logger.isLoggable("INFO")) QueryDebug("Update", query, parameters);
		
		// Capture exceptions
		try {			
			/*
			 *  Conduct the initial update as specified by the user
			 */
			
			// Prepare statement
			PreparedStatement update = Conn.prepareStatement(query);
			
			// Counter to keep track of position
			int count = 1;
		
			// Loop through and append arguments to query
			for (String arg : parameters) {
				// Include elements
				update.setString(count, arg);
			
				// Increment
				count++;
			}
    
			// Execute above query
			update.executeUpdate();
			update.close();
			
			// Return true
			return true;
		} 
		
		// Generate failure of query
		catch (SQLException e) {
			Logger.Warn(CLASS, "updateSingleRows", e);
		}
			
		// The above failed assume the worst	
		return false;
	}
	// End updateSingleRow Method
	
	
	/**
	 * Certain elements of the system require that other elements be attached
	 * to the primary database.   This permits the language and site elements
	 * to be cached without duplicating settings and configuration elements
	 * at every startup.
	 * <p>
	 * @param db File element containing location of database to attach.
	 * @param name String containing the identifier to use for table lookup.
	 * @return Success of the connection.
	 */
	private boolean Attach(File db, String name) {
		
		// We need this to work
		if (!db.exists())
			return false;
		
		/* 
		 * The single word command will actually be used to clean up the
		 * entire database.
		 */
		try {
			// Prepare Statement
			PreparedStatement qmeta = Conn.prepareStatement("ATTACH DATABASE ? AS ?");
			
			// Insert elements
			qmeta.setString(1, db.getAbsolutePath());
			qmeta.setString(2, name);
			
			// Execute above query
			qmeta.executeUpdate();
			qmeta.close();
			
			// All is good
			return true;
		} 
		
		catch (SQLException e) {
			// Log and move on
			Logger.Warn(CLASS, "Attach", e);
		}
		
		// Something failed
		return false;
	}
	// End of Attach Method
	
	
	/**
	 * Attach a new host element to the database.  This is a pass-through for
	 * the main Attach method.
	 * <p>
	 * @param db Path to the database.
	 * @return Boolean indicating success of connection
	 */
	public boolean attachHost(File db) {
		// Direct Pass-Through
		return Attach(db, HOST);
	}
	// End of AttachHost Method
	
	/**
	 * Attach a new language element to the database.  This is a pass-through
	 * for the main Attach method.
	 * <p>
	 * @param db Path to the database.
	 * @return Boolean indicating success of attachment.
	 */
	public boolean attachLang(File db) {
		// Direct Pass-Through
		return Attach(db, LANG);
	}
	// End of AttachLang Method
	
	
	/**
	 * The DETACH command will permit the separation of a database from the
	 * main.   This can permit the changing of hosts without leaving the
	 * application.
	 * <p>
	 * @param name String containing name of database to detach from.
	 */
	private void Detach(String name) {
		/* 
		 * The single word command will actually be used to clean up the
		 * entire database.
		 */
		try {
			// Prepare Statement
			PreparedStatement qmeta = Conn.prepareStatement("DETACH name");
			
			// Insert Elements
			qmeta.setString(1, name);
			
			// Execute above query
			qmeta.executeUpdate();
			qmeta.close();
		} 
		
		catch (SQLException e) {
			// Log and move on
			Logger.Warn(CLASS, "Detach", e);
		}
	}
	// End of Detach Method
	
	
	/**
	 * Detaches the currently connected host database from the main entry.
	 * Permitting the addition of a new entry,
	 */
	public void detachHost() {
		// Straight pass-through
		Detach(HOST);
	}
	// End of detachHost Method
	
	
	/**
	 * Detaches the currently connected language databases from the main entry.
	 * Unlike host, this method will rarely be used.
	 */
	public void detachLang() {
		// Straight pass-through
		Detach(LANG);
	}
	// End of detachLang Method
	
	
	/**
	 * Rarely used method which allows for a myriad of queries to be added into
	 * a singular transaction in order to prevent out of dependency updates.
	 * <p>
	 * @return Boolean indicating success of the operation.
	 */
	public boolean updateCommence() {

		try {
									
			// Set the flag
			Conn.setAutoCommit(false);
					
			// Commit all the changes
			Conn.commit();
			
			// All is good
			return true;
		} 
		
		// Record if the process failed
		catch (SQLException e) {
			
			// Log and move on
			Logger.Warn(CLASS, "updateCommence", e);
		}
		
		// Things did not go as planned
		return false;
	}
	// End of updateCommence Method
	
	
	/**
	 * Cleans up the update process created by multiple row updates setting
	 * up the process for another pass if necessary.
	 * <p>
	 * @return Boolean indicating success of the operation.
	 */
	public boolean updateComplete() {
		try {
			
			/*
			 * Handle Specific
			 */
			if (myUpdates != null) {
				
				// Close the statement update handle
				myUpdates.close();
			
				// Clear out handle
				myUpdates = null;
			}
			
			// Commit all the changes
			Conn.commit();
			
			// Set the flag
			Conn.setAutoCommit(true);
					
			// All is good
			return true;
		} 
		
		// Record if the process failed
		catch (SQLException e) {
			
			// Log and move on
			Logger.Warn(CLASS, "updateComplete", e);
		}
		
		// Things did not go as planned
		return false;
	}
	// End of updateComplete Method
	
	
	/**
	 * The VACUUM command which cleans up the database structure and can
	 * shrink up used space considerably. 
	 */
	public void Vacuum() {
		/* 
		 * The single word command will actually be used to clean up the
		 * entire database.
		 */
		try {
			// Prepare Statement
			PreparedStatement qmeta = Conn.prepareStatement("VACUUM");
			
			// Execute above query
			qmeta.executeUpdate();
			qmeta.close();
		} 
		
		catch (SQLException e) {
			// Log and move on
			Logger.Warn(CLASS, "Vacuum", e);
		}
	}
	// End of Vacuum Method
	
	
	/**
	 * For some reason it is difficult to properly separate preparation and
	 * query results running without running into trouble.  So this class
	 * is essentially a holder class for the PreparedStatement and ResultSet
	 * handles.
	 * <p>
	 * @author Martin Foster
	 */
	private class myConnections {
		// Instances
		private PreparedStatement myPrepared = null;
		private ResultSet myResult = null;
		
		
		/**
		 * Default Constructor 
		 */
		public myConnections() {}
		
		
		/**
		 * Sets the PreparedStatement element.
		 * <p>
		 * @param ps PreparedStatement
		 */
		public void setPreparedStatement(PreparedStatement ps) {
			myPrepared = ps;
		}
		// End of setPreparedStatement Method
		
		
		/**
		 * Gets the PreparedStatement element.
		 * <p>
		 * @return PreparedStatement
		 */
		@SuppressWarnings("unused")
		public PreparedStatement getPreparedStatement() {
			return myPrepared;
		}
		// End of getPreparedStatement Method
		
		
		/**
		 * Sets the ResultSet element.
		 * <p>
		 * @param rs ResultSet.
		 */
		public void setResultSet(ResultSet rs) {
			myResult = rs;
		}
		// End of setResultSet Method
		
		
		/**
		 * Sets the ResultSet element.
		 * <p>
		 * @return ResultSet.
		 */
		public ResultSet getResultSet() {
			return myResult;
		}
		// End of getResultSet Method
		
		
		/**
		 * Closes and nulls out all of the handles in this class.
		 * <p>
		 * @throws SQLException 
		 */
		public void close() throws SQLException {
			// Close the result set
			if (myResult != null) {
				myResult.close();
				myResult = null;
			}
			
			// Close the prepared statement
			if (myPrepared != null) {
				myPrepared.close();
				myPrepared = null;
			}
		}
		// End of close Method
	}
	// End of myConnections Class

}
