/*
 * MiningMart Version 1.0
 * 
 * Copyright (C) 2006 Martin Scholz, Timm Euler, 
 *                    Daniel Hakenjos, Katharina Morik
 *
 * Contact: miningmart@ls8.cs.uni-dortmund.de
 *
 * A list of contributing developers (other than the copyright 
 * holders) can be found at
 * http://mmart.cs.uni-dortmund.de/downloads/download.html
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program, see the file MM_HOME/LICENSE; if not, write
 * to the Free Software Foundation, Inc., 51 Franklin Street, Fifth
 * Floor, Boston, MA 02110-1301, USA.
 */
package edu.udo.cs.miningmart.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.logging.Level;

import edu.udo.cs.miningmart.exception.DbConnectionClosed;
import edu.udo.cs.miningmart.exception.M4CompilerError;
import edu.udo.cs.miningmart.exception.M4Exception;
import edu.udo.cs.miningmart.m4.Columnset;
import edu.udo.cs.miningmart.m4.M4InterfaceContext;
import edu.udo.cs.miningmart.m4.utils.Print;

/**
 * Abstract superclass class to encapsulate most of the directly database related information.
 * Subclasses realise the access to different DBMS. 
 * The only class referencing subclasses of <code>DbCore</code> is <code>DB</code>. However, 
 * the class is public because it provides static methods to deal with different DBMS systems.
 * 
 * @author Martin Scholz
 * @version $Id: DbCore.java,v 1.6 2006/04/11 14:10:16 euler Exp $
 */
abstract public class DbCore 
{		
	/**
	 * Public constant to refer to the Database Management System (DBMS) Oracle.
	 */
	public static String DBMS_ORACLE   = "Oracle";
	
	/**
	 * Public constant to refer to the Database Management System (DBMS) PostgreSQL.
	 */
	public static String DBMS_POSTGRES = "Postgres";
	
	/**
	 * @return a DbCore object for the DMBS used
	 */
	static DbCore getDbCore( String url,
							 String dbName,
							 String user,
							 String passwd,
							 M4InterfaceContext cal,
							 boolean isM4Schema)
		throws SQLException
	{
		String dbms = findDBMS(url);		
		
		if (dbms == null)
		{   return null;   }
		
		if (dbms.equals(DBMS_ORACLE)) {
			return new DbCoreOracle(url, dbName, user, passwd, cal, isM4Schema);
		}
		else if (dbms.equals(DBMS_POSTGRES)) {
			return new DbCorePostgres(url, dbName, user, passwd, cal, isM4Schema);
		}
		else throw new SQLException("DbCore.getDbCore(): Could not recognize DBMS system!");
	}

	/**
	 * Use this method to decide about the Database Management System (DBMS)
	 * which is to be used. This method is given the DB connection URL (consisting of driver and location)
	 * and returns one of the public constants of this class whose name starts with 
	 * &quot;DBMS_&quot;.
	 * 
	 * Currently, this method uses the driver name to decide about the DBMS.
	 * 
	 * @author Timm Euler
	 * @param url The DB connection URL
	 * @return One of the public String constants of this class.
	 */
	public static String findDBMS(String url)
	{
		if (url != null) 
		{
			String lowUrl = url.toLowerCase();

			if (lowUrl.indexOf("oracle") >= 0) {
				return DBMS_ORACLE;				
			}
			else if (lowUrl.indexOf("postgres") >= 0) {
				return DBMS_POSTGRES;
			}
		}	
		return null;	
	}

	public Print getCasePrintObject() {
		return this.cal.getPrintObject();	
	}

	
    /* Instances of class Connection. It is used during the whole database session. */
    private Connection con = null;
    
    /* This HashMaps contains a Statement to ExtendedResultSet mapping.
     * It is used for read accesses to the database schema, only.
     * Statements with closed ExtendedResultSets are re-used for further
     * read accesses. During a commit of changes to the schema all affected
     * Statements are closed.
     */
    private final HashMap readResultSets = new HashMap();

	/*
	 * This field contains a Statement used for writing to the
	 * database schema. It helps to support automatic batching of
	 * queries that write, delete or change the database contents.
	 */
    private Statement statementWrite;
    
    /* 
     * If the number of open read access Statements for the schema exceeds
     * the number below, a warning is given to the user. This should help
     * to find ResultSets not closed properly after usage.
     */
	private static final int MAX_OPEN_STATEMENTS = 20;

	// connection details:
    private final String url;
    private final String user;
    private final String passwd;
	private final boolean refersToM4Schema;

	/*
	 * The CompilerAccessLogic, used to write debug messages to the
	 * corresponding Print object. This field should not be accessed
	 * directly, but only by the doPrint methods of class DbCore.
	 */
	private final transient M4InterfaceContext cal;

    /**
     * The constructor of this class establishes a new database connection.
     * Autocommit is set to false, so transaction management must be done
     * individually by the database functions. 
     * 
     * @param url Prefix of the database url
     * @param dbName name of the database
     * @param user name of the database user
     * @param passwd password for the database user
     * @param cal the <code>CompilerAccessLogic</code> of the current thread. It is just needed
     * 		  in order to reach the appropriate <code>Print</code>-object for writing debug messages
     * @param refersToM4Schema is <code>true</code> iff the connection refers to the M4 schema.
     * 		  Useful to decide whether triggers need to be switched off etc.
     * @throws SQLException
     */
    public DbCore( String url,
               	   String dbName,
	               String user,
    	           String passwd,
        	       M4InterfaceContext cal,
        	       boolean refersToM4Schema)
		throws SQLException
    {
        this.url    = url + dbName;
		this.user   = user;
		this.passwd = passwd;
		this.refersToM4Schema = refersToM4Schema;
		this.cal = cal;
		this.getFreshConnection();
    }

    /** 
     * Closes the database connections.
     * If the database is no longer used this Finalization method
     * closes the database connection.
     * Currently, I guess a java exit is an acceptable disconnect as well.
     * If not we have to register a addShutdownHook in Class Runtime
     */
	public void finalize() throws SQLException
	{
		/* Triggers are no longer used!
		
		try {
			if (this.isM4Schema()) {
				// Triggers must be switched on when exiting.
				// Just to prevent the strange effect that the flag is
				// set without a commit...	
				this.switchTriggersOn();
			}
		}
		catch (SQLException e) {}
		
		*/
		
		this.closeDbStatements();
		
		// DB close
		if (con != null)
		{			
			con.close();
			con = null;
			this.doPrint(Print.COMPILER_CASE_CONTROL, "DB disconnected");
		}
	}
	
	/** 
	 * Indicates the DBMS used. 
	 */
	abstract public short getDbms();

	/**
	 * This method must be implemented by subclasses in order
	 * to use other JDBC drivers (for other DBMS, for example).
	 */
	abstract protected void registerJDBC_Driver() throws SQLException;
	
	/**
	 * Since the transaction mechanism may be different for different DBMS,
	 * use this method to implement the Autocommit handling.
	 */
	abstract protected void switchAutocommitOff(Connection con) throws SQLException;
	

	/* *** Triggers are no longer used! ***
	 * Abstract method to be implemented by subclasses. Must disable
	 * the triggers for the next transaction.
	 */
	// abstract protected void switchTriggersOff() throws SQLException; 

	/* *** Triggers are no longer used! ***
	 * Abstract method to be implemented by subclasses. Must enable
	 * the triggers for the next transactions.
	 */
	// abstract protected void switchTriggersOn() throws SQLException;
	
	/**
	 * Closes and re-opens the connection to the database. This may
	 * be necessary to reduce the number of open database cursors.
	 */
	void getFreshConnection() throws SQLException
    {
		if (this.con != null) {
			/* Triggers are no longer used!
			  if (this.isM4Schema()) {
				this.switchTriggersOn();
			  }
			*/
			this.con.commit();
			this.closeDbStatements();
			this.con.close();
			 // once should be sufficient:
		}
		else this.registerJDBC_Driver();

	    this.con = DriverManager.getConnection (url, user, passwd);

	    //switch autocommit off 
	    this.switchAutocommitOff(con);
	    
	    // print info about database connection
	    DatabaseMetaData md = con.getMetaData();
	    doPrint(Print.MAX, "\n--- Refreshing database connection ---");
	    doPrint(Print.MAX, "JDBC-Driver-Version: " + md.getDriverVersion() + "  " +  md.getDatabaseProductName());
		doPrint(Print.MAX, "Database: " + this.url + "\tUser:  " +  user);
	    // Everyone in the package m4compiler should be able to use this
	    // DB instance, so store me as the open connection in the class-field:

	    doPrint(Print.MAX, "DB-Connection ok");
	    
		/* Triggers are no longer used!
		  if (this.isM4Schema()) {
		    this.switchTriggersOff();
		  }
		*/
		
		// --- not necessary for current random number implementation ---
		// Statement stmt = m4Con.createStatement();
	    // String query = "SELECT M4_Sampleinit() FROM dual";
	    // this.doPrint(Print.DB_READ, "Initialising Random numbers: " + query);
	    // stmt.executeQuery(query);
	    // stmt.close();
	}

	String getUrl() { return this.url; }
	
	String getUser() { return this.user; }
	
	String getPasswd() { return this.passwd; }

	boolean isM4Schema() { return this.refersToM4Schema; }

	/**
	 * Commit the last transactions in the M4 database.
	 */
	public void commitTransactions() throws SQLException
	{
		this.closeDbStatements();
		if (this.isM4Schema()) {
			// this.switchTriggersOn(); // remove transaction internal trigger-off flag
			this.getDatabaseConnection().commit();
			// this.switchTriggersOff(); // set the trigger-off flag again
		}
		else {
			this.getDatabaseConnection().commit();	
		}
		this.doPrint(Print.DB_WRITE, "DB: Batch executed, updates committed!");
	}
	
	/**
	 * Roll back the last transactions (since the last commit)
	 * in the database.
	 */
	public void rollback() throws SQLException
	{
		this.closeDbStatements();
		this.getDatabaseConnection().rollback();
		this.doPrint(Print.DB_WRITE, "DB: Updates rolled back!");
		
		/*
		  if (this.isM4Schema()) {
			this.switchTriggersOff();
		  }
		*/
	}

    /**
     * This method may be needed to provide wrappers with a database connection.
     * @return An object implementing the interface <code>java.sql.Connection</code>.
     */
    private Connection getDatabaseConnection() {
	   	return this.con;
    }

	/**
	 * @return a <code>Statement</code> to read the schema.
	 * If possible, an old <code>Statement</code> with closed <code>ResultSet</code>
	 * is returned. The new <code>ResultSet</code> of the <code>Statement</code> has
	 * to be registered. this is done by the <code>executeSqlRead</code> method.
	 *  */
	private Statement getDbReadStatement()
		throws SQLException, DbConnectionClosed
	{
		Statement stmt = findFreeStatement(this.readResultSets);
		if (stmt == null) {
			stmt = this.getDatabaseConnection().createStatement();	
			this.readResultSets.put(stmt, new ExtendedResultSet(stmt));
		}
		if (this.readResultSets.size() > MAX_OPEN_STATEMENTS) {
			this.openStatementsWarning(this.readResultSets.size());
		}
		
		return stmt;
	}

	/** @return a <code>Statement</code> to write to the database.*/
	private Statement getDbWriteStatement()
		throws SQLException, DbConnectionClosed
	{
		if (this.statementWrite == null) {
			Statement stmt = this.getDatabaseConnection().createStatement();
			stmt.clearBatch();
			this.statementWrite = stmt;
		}
		return this.statementWrite;
	}

	/**
	 * Closes all <code>Statement</code>s that belong to the database schema,
	 * after executing all remaining batches.
	 * */
	private void closeDbStatements() {
		if (this.statementWrite != null) {
			try {
				DbCore.commitBatch(this.statementWrite);
				this.statementWrite.close();
			}
			catch (SQLException e) {} // Should not be problematic at this point!
			this.statementWrite = null;
		}
		DbCore.clearStatementMap(this.readResultSets);
	}

	/**
	 * For external use a separate (business data) Connection is set up, which has to be closed by
	 * the calling operator.
	 * @return a <code>Connection</code> to the database
	 * */
	Connection getExternalDatabaseConnection()
		throws SQLException
	{
		this.registerJDBC_Driver();
	    Connection newDataCon = DriverManager.getConnection (this.getUrl(), this.getUser(), this.getPasswd());

	    //switch autocommit off 
	    this.switchAutocommitOff(newDataCon);
	    
	    DatabaseMetaData dmd = newDataCon.getMetaData();
	    this.doPrint(Print.COMPILER_CASE_CONTROL, "\n--- Setting up new database connection for external use ---");
	    this.doPrint(Print.COMPILER_CASE_CONTROL, "JDBC-Driver-Version: " + dmd.getDriverVersion() + "  " +  dmd.getDatabaseProductName());
	    this.doPrint(Print.COMPILER_CASE_CONTROL, "DB-Connection ok");
		
	    return newDataCon;
    }

	/** 
	 * Method to comfortably write to the database. If possible automatic batching is applied.
	 * @param query an SQL query to be executed. This has to be a write operation to the database,
	 * or an SQL string to execute a procedure in the according schema.
	 * */
	public void executeSqlWrite(String query)
		throws SQLException, DbConnectionClosed
	{
        Statement stmt = this.getDbWriteStatement();
		this.executeSqlWrite(query, stmt);
	}

    /** 
     * Create the generated sql definition for a new view within the database,
     * or materialize the view as a table, if this is preferrable.
     *
     * @param Columnset to be tested
     * @param stepId the Id of the step in which this Columnset was created
     */
    public void createSQLView(Columnset cs, boolean materialize)
    throws SQLException, DbConnectionClosed {
        String sqlTemp = cs.getSQLDefinition();
        sqlTemp = sqlTemp.substring(1, sqlTemp.length() - 1);
	    String query = "CREATE " + (materialize ? "TABLE " : "OR REPLACE VIEW ")
	    			   + cs.getName() + " AS " + sqlTemp;
	    this.executeSqlWrite(query);
    }

	/**
	 * Create a function in the database by using the given function definition.
	 * 
	 * @param myFunction a function definition for a SQL function
	 */
    public void createSQLFunction(String myFunction)
    	throws SQLException, DbConnectionClosed
    {
	    String query = "CREATE OR REPLACE " + myFunction;
        this.executeSqlWrite(query);
    }

	/**
	 * Creates a database index for the given table and set of attributes.
	 * @param tableName the table to create an index on
	 * @param attributes an array of attribute names, specifying for which attributes an index should be created
	 * @return the name of the index or <code>null</code>.
	 */
	public String createSqlIndex(String tableName, String[] attributes)
		throws SQLException, DbConnectionClosed
	{
		if (attributes == null || attributes.length == 0)
			return null;
		final String indexName = tableName + "_IDX";
	    String query = "CREATE INDEX " + indexName + " ON " + tableName + " (";
	    for (int i = 0; i < attributes.length; i++) {
			query += attributes[i] + ", ";
		}
		query = query.subSequence(0, query.length() - 2) + ")";
        this.executeSqlWrite(query);
        return indexName;
	}

	/** 
	 * Method to comfortably read from the database.
	 * @param query an SQL query to be executed. This has to be a read operation on the database.
	 * @return the corresponding <code>ResultSet</code>
	 * */
	public ResultSet executeSqlRead(String query)
		throws SQLException, DbConnectionClosed
	{
        Statement stmt = this.getDbReadStatement();
		return this.executeSqlRead(query, stmt);
	}

	/** 
	 * @param query an SQL query to be executed. This has to be a write operation or an SQL string
	 * to execute a stored procedure.
	 * @param stmt the <code>Statement</code> to be used to execute the query
	 * */
	private void executeSqlWrite(String query, Statement stmt)
		throws SQLException
	{
		if (isBatchableWriteQuery(query))  {
	        stmt.addBatch(query);
    	    this.doPrint(Print.DB_WRITE, "DB write (to batch): " + query);
		}
		else {
     		commitBatch(stmt);
			stmt.executeUpdate(query);
	        this.doPrint(Print.DB_WRITE, "DB execute/update: " + query);
        }
	}
	
	/** 
	 * @param query an SQL query to be executed. This has to be a read operation.
	 * @param stmt the <code>Statement</code> to be used to execute the query
	 * @return the corresponding <code>ResultSet</code>
	 * */
	private ResultSet executeSqlRead(String query, Statement stmt)
		throws SQLException
	{
		this.doPrint(Print.DB_READ, "DB Query (Read): " + query);
		ExtendedResultSet rs = new ExtendedResultSet(stmt.executeQuery(query));
		this.readResultSets.put(stmt, rs);
		return rs;
	}

	/** 
	 * @param query an SQL query to be executed. This has to be a read operation yielding a
	 * single (numeric) value, e.g. a &quot;<i>SELECT COUNT(*) ...</i>&quot;, or a
	 * &quot;<i>SELECT AVG(Column)...</i>&quot;
	 * @param stmt the <code>Statement</code> to be used to execute the query
	 * @return the result as a <code>String</code>, or <code>null</code>, if no value was returned.
	 * */
	private String executeSingleValueSqlRead(String query, Statement stmt)
		throws SQLException
	{
		ResultSet rs = null;
		try {
			rs = this.executeSqlRead(query, stmt);
			if (rs.next()) {
				String s = rs.getString(1);
				return s;
			}
			else {
				return null;	
			}
		}
		finally {
			DB.closeResultSet(rs);
		}
	}

	/** 
	 * @param query an SQL query to be executed. This has to be a read operation yielding a
	 * single (numeric) value, which is convertable to datatype long.
	 * @param stmt the <code>Statement</code> to be used to execute the query
	 * @return the result as a <code>String</code>, or <code>null</code>, if no value was returned.
	 * */
	protected Long executeSingleValueSqlReadL(String query, Statement stmt)
		throws SQLException
	{
		ResultSet rs = null;
		try{
			rs = this.executeSqlRead(query, stmt);
			if (rs.next()) {
				Long l = new Long(rs.getLong(1));
				rs.close();
				return l;
			}
			else {
				return null;	
			}
		}
		finally {
			DB.closeResultSet(rs);
		}
	}

	/** 
	 * @see DB#executeSingleValueSqlReadL(String, Statement)
	 * In contrast to that method, this method creates and then closes its own
	 * <code>Statement</code>.
	 * */
	public Long executeSingleValueSqlReadL(String query)
		throws SQLException, DbConnectionClosed
	{
		Statement stmt = this.getDbReadStatement();
		return this.executeSingleValueSqlReadL(query, stmt);
	}

	/** 
	 * @see DB#executeSingleValueSqlRead(String, Statement)
	 * In contrast to that method, this method creates and then closes its own
	 * <code>Statement</code>.
	 * */
	public String executeSingleValueSqlRead(String query)
		throws SQLException, DbConnectionClosed
	{
		Statement stmt = this.getDbReadStatement();
		return this.executeSingleValueSqlRead(query, stmt);
	}

	/**
	 * @param stmt the <code>Statement</code> to be used to execute the query
	 * @return the value returned by the sequence (installed in the M4 schema).
	 * @throws M4Exception if for some reason the sequence does not return a value
	 * */
	abstract protected long getNextM4SequenceValue(Statement stmt) throws M4Exception;

	/**
	 * @return the value returned by the sequence (installed in the M4 schema).
	 * @throws M4Exception if for some reason the sequence does not return a value
	 * */
	long getNextM4SequenceValue()
		throws DbConnectionClosed, M4Exception
	{
			try { 
				Statement stmt = this.getDbReadStatement();
		        return this.getNextM4SequenceValue(stmt);
			}
			catch (SQLException e) { // Only possible origin is "this.getM4DbStatement()"!
				String msg = "Error in method DbCore.getNextM4SequenceValue():\n"
						   + "Could not create Statement:\n"
						   + e.getMessage();
				throw new M4Exception(msg);			
			}
	}

	/** 
	 * Method to execute a stored procedure residing in the according schema.
	 * @param procedureName the name of the stored procedure in the database
	 * @param parameters the parameters to be passed to the stored procedure.
	 *                   In SQL code these parameters are comma separated.
	 */
    public void executeDBProcedure(String procedureName, String[] parameters)
		throws SQLException, DbConnectionClosed, M4CompilerError
    {
		final Statement stmt = this.getDbWriteStatement();
		DbCore.commitBatch(stmt); // better execute previous write queries before running the procedure!
		
		String queryStart = "BEGIN " + procedureName + "(";
		String queryEnd   = "); END; ";
		String queryParameters = "";
		for (int i=0; i<parameters.length; i++) {
		    queryParameters += parameters[i];
	    	if (i < parameters.length - 1)
				queryParameters += ", ";
		}
		String query = queryStart + queryParameters + queryEnd;
		this.doPrint(Print.DB_READ, "DB Query: " + query);

	/*
		if (this.getDatabaseConnection() == this.getDatabaseConnectionForM4())
		{ // The business schema stored procedure might perform a 'commit',
		  // which would switch off the triggers for all other threads accessing the DB!
			this.switchTriggersOn(); // Remove the "trigger-off" flag.
			stmt.executeUpdate(query); // Execute the Stored Procedure, which is allowed to 'commit'.
			this.switchTriggersOff(); // Set the "trigger-off" flag again.
		}
		else { 
			// In this case, a 'commit' should not do any harm, because the Stored Procedure
			// has no access to the M4schema!
	*/
			stmt.executeUpdate(query);
	//	}
    }

	/**
	 * Displays a warning, if too many read <code>Statements</code> are open in parallel. 
	 * @param number the number of open <code>Statements</code> for the specified schema.
	 * */
	private void openStatementsWarning(int number) {
		this.doPrint(Print.MAX, "( Warning: The number of open statements is " + number + ".");
		this.doPrint(Print.MAX, "  Probably some of the ResultSets are not closed after usage! )");
	}

	/**
	 * This method is invoked by class Db, if a set flag to kill the current thread has been
	 * detected. A rollback on the according schema is performed, in order to reach a consistent
	 * state. Then the <code>Statement</code>s are closed.
	 * */
	void stopDbThread()
	{
		this.doPrint(
			Print.MAX,
			"Thread was requested to stop. Closing database resources.");
		try {
			if (this.statementWrite != null) {
				this.statementWrite.clearBatch();
			}
			DbCore.clearStatementMap(this.readResultSets);
			if (this.con != null) {
				this.con.rollback();
				this.con.close();
				this.con = null;
			}
			this.doPrint(
				Print.DB_WRITE,
				"DB: Rollback and closing of connection successful!");
		}
		catch (SQLException e) {}
	}

	/**
	 * generates warning messages
	 * @param verbosity the verbosity level of the warning as specified in the class
	 * 					<code>edu.udo.cs.miningmart.m4.core.utils.Print</code>
	 * @param message the warning message
	 * */
	private void doPrint(Level verbosity, String message) {
		this.getCasePrintObject().doPrint(verbosity, message);
	}

	/**
	 * prints an <code>Exception</code> to the debug output
	 * @param ex the <code>Exception</code> to be printed
	 * */
	private void doPrint(Exception ex) {
		this.getCasePrintObject().doPrint(Print.ERROR,ex.getMessage(),ex);
	}

	// ------------------------------------------------------------------------------

	/** commits and clears a batch */
	private static void commitBatch(Statement stmt)
		throws SQLException
	{
		stmt.executeBatch();
		stmt.clearBatch();		
	}

	/** 
	 * @param query an SQL query of type write
	 * @return <code>true</code> iff the query is suited to be batched
	 * */
	private static boolean isBatchableWriteQuery(String query) {
		final String tmp = query.trim().toLowerCase();
		
        return (tmp.startsWith("update ") || tmp.startsWith("insert "));
        //  || ( (tmp.startsWith("insert ")) && (tmp.indexOf("trigger_flag_t") == -1)) )
	}
	
	/**
	 * Closed the stored read statements in the given <code>HashMap</code> and clears
	 * the <code>HashMap</code> afterwards.
	 * @param theMap the <code>HashMap</code> to be cleared
	 *  */
	private static void clearStatementMap(HashMap theMap) {
		if (theMap == null || theMap.isEmpty())
			return;
		Iterator it = theMap.keySet().iterator();
		while (it.hasNext()) {
			Statement stmt = (Statement) it.next();
			if (stmt != null) {
				try {
					stmt.close();
				}
				catch (SQLException e) {} // not considered dangerous
			}
		}
		theMap.clear();
	}
	
	/** 
	 * This method scans in a set of collected <code>Statement</code> - 
	 * <code>ExtendedResultSet</code> tuples for <code>Statement</code> which are no
	 * longer in use, and returns one of these, if possible.
	 * @param theMap a <code>HashMap</code> with <code>Statement</code>s and their
	 * <code>ExtendedResultSet</code>s.
	 * @return a <code>Statement</code> free for further read accesses to the database
	 * 		it is connected to, or <code>null</code>, if no such <code>Statement</code>
	 * 		exists.
	 * */
	private static Statement findFreeStatement(HashMap theMap) {
		Iterator it = theMap.keySet().iterator();
		while (it.hasNext()) {
			Statement stmt = (Statement) it.next();
			if (stmt != null) {
				ExtendedResultSet rs = (ExtendedResultSet) theMap.get(stmt);
				if (rs != null && rs.isClosed())
					return stmt;
			}
		}
		return null;
	}

	/**
	 * Abstract method to be implemented by the subclasses.
	 * Returns an SQL String that selects the names of all tables 
	 * from the current schema. This is DBMS-dependent.
	 * 
	 * @return An SQL String
	 */
	public abstract String getSelectStringAllTables();

	/**
	 * Abstract method to be implemented by the subclasses.
	 * Returns an SQL String that selects the names of all views 
	 * from the current schema. This is DBMS-dependent.
	 * 
	 * @return An SQL String
	 */
	public abstract String getSelectStringAllViews();

	/**
	 * Returns TRUE if a table (not a view) with the given name exists
	 * in the database.
	 * 
	 * @param tableName the table name
	 * @return TRUE or FALSE
	 * @throws M4Exception
	 */
	public abstract boolean tableExists(String tableName) throws M4Exception;
	
	/**
	 * Abstract method to be implemented by the subclasses.
	 * Returns the SQL String that selects all column names and types for
	 * the given Table or View name from the business data schema.
	 * This is DBMS-dependent.
	 * 
	 * @param dbObjectName Name of a table or view in the business data schema
	 * @return An SQL String
	 */
	public abstract String getSelectStringAllColumnsForDbObject(String dbObjectName);	
	
    /**
	 * Abstract method to be implemented by the subclasses.
     * Returns the name of the column that contains the column names.
	 * This is DBMS-dependent.
     * 
     * @return the name of the column that contains the column names.
     */
	public abstract String getAttributeForColumnNames();
	
    /**
	 * Abstract method to be implemented by the subclasses.
     * Returns the name of the column that contains the column types.
	 * This is DBMS-dependent.
     * 
     * @return the name of the column that contains the column types.
     */
	public abstract String getAttributeForColumnTypes();
		
    /**
	 * Abstract method to be implemented by the subclasses.
     * Returns a String that can be used to uniquely identify table rows
     * in SQL-Statements.
	 * This is DBMS-dependent.
     * 
     * @return the unique row identifier name
     */
	public abstract String getUniqueRowIdentifier();	

	/**
	 * Returns a Map that maps every column of the given table to the name
	 * of the table that it references as a foreign key, or to null if the
	 * column does not act as a foreign key. The Map uses only Strings.
	 * 
	 * @param dbObjectName the given table name
	 * @return a Map whose keys and values are Strings
	 * @throws SQLException
	 * @throws DbConnectionClosed
	 */
	public abstract Map getTablesReferencedBy(String dbObjectName) throws SQLException, DbConnectionClosed;

	/**
	 * Returns TRUE iff the given table/view has ONLY columns that 
	 * act as foreign keys to other tables. 
	 * 
	 * @param dbObjectName name of given table/view
	 * @return a boolean value
	 * @throws SQLException
	 * @throws DbConnectionClosed
	 */
	public boolean hasOnlyForeignKeyColumns(String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		Map referencedTables = this.getTablesReferencedBy(dbObjectName);
		ResultSet rs = this.executeSqlRead(this.getSelectStringAllColumnsForDbObject(dbObjectName));
		boolean allColumnsAreForeignKeys = true;
		while (rs.next()) {
			String columnName = rs.getString(1);
			if (referencedTables.get(columnName) == null) {
				allColumnsAreForeignKeys = false;
			}
		}
		rs.close();
		return allColumnsAreForeignKeys;
	}
	
	/**
	 * Returns a Collection with the names of the DB columns that belong to the
	 * given table or view and form its primary key, if such a key is declared by
	 * SQL constraints in the DB. Otherwise null is returned.
	 *   
	 * @param dbObjectName the name of the given table or view
	 * @return a Collection of Strings
	 * @throws SQLException
	 */
	public abstract Collection getPrimaryKeyColumnNames(String dbObjectName) 
	throws SQLException, DbConnectionClosed;

	/**
	 * Returns one of the TYPE_... constants in the class
	 * edu.udo.cs.miningmart.m4.Columnset, or null if the given 
	 * String is neither a table or view.
	 * 
	 * @param dbObjectName name of a table or view
	 * @return a String with the type information
	 */
	public abstract String getTableOrViewType(String dbObjectName) throws SQLException, DbConnectionClosed;
	
	/**
	 * This method returns the DBMS-dependent name of the datatype which the current
	 * DBMS uses to realize the given M4-Relational Datatype.
	 * 
	 * @param m4RelDatatypeName name of the M4 Relational Datatype
	 * @param size A size to be returned with the datatype, if the DBMS allows that.
	 *        For example, if the M4 Relational Datatype is NUMBER and length is 10, 
	 *        then the return value will be the String &quot;NUMBER(10)&quot;
	 *        if the DBMS is Oracle. If this length is not positive, the datatype will be returned without
	 *        a size, in the example: &quot;NUMBER&quot;. The DBMS may not allow lengths at all, in which case
	 *        also the datatype without a size is returned.
	 * @return A DBMS-dependent string that contains a datatype name.
	 */
	public abstract String getDatatypeName(String m4RelDatatypeName, int size);	
	
	/**
	 * This method returns the M4-Relational Datatype that corresponds to the given
	 * DBMS-dependent name of a datatype.
	 * 
	 * @param dbmsDatatypeName the name of the datatype in the underlying DBMS
	 * @return the corresponding M4 Relational Datatype
	 */
	public abstract String getM4DatatypeName(String dbmsDatatypeName);
	
	/**
	 * This method returns the DBMS-dependent SQL command that returns the name of the datatype of the given
	 * column in the given table or view (which is owned by the given owner).
	 * 
	 * @param dbObjectName Name of a table or view in the business data schema
	 * @param owner Name of the owner of the table or view
	 * @param columnName Name of the column whose datatype is returned
	 * @return the DBMS-dependent name of the datatype of the column with the given name
	 */
	public abstract String getSelectStringColumnDataTypes(String dbObjectName, String owner, String columnName);
	
	/**
	 * Try to drop the table or view with the given name, if it exists. Returns TRUE iff the table
	 * had existed, FALSE otherwise.
	 * 
	 * @param tableName the name of the table to be dropped
	 * @return TRUE iff the table had existed before it was removed,
	 *         FALSE if nothing was done.
	 */
	public abstract boolean dropRelation(String tableName) throws M4Exception;
	
	/** @return a <code>String</code> that can be used as a query to test the DB connection. */
	public abstract String getTestQuery();
	
}
/*
 * Historie
 * --------
 *
 * $Log: DbCore.java,v $
 * Revision 1.6  2006/04/11 14:10:16  euler
 * Updated license text.
 *
 * Revision 1.5  2006/04/06 16:31:15  euler
 * Prepended license remark.
 *
 * Revision 1.4  2006/03/29 09:50:47  euler
 * Added installation robustness.
 *
 * Revision 1.3  2006/03/02 16:49:59  euler
 * Many bugfixes
 *
 * Revision 1.2  2006/01/03 10:43:50  euler
 * Bugfixes
 *
 * Revision 1.1  2006/01/03 09:54:23  hakenjos
 * Initial version!
 *
 */
