/*
 * 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.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.Vector;

import edu.udo.cs.miningmart.compiler.utils.DrawSample;
import edu.udo.cs.miningmart.storedProcedures.BusinessDbConnectionSource;

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.M4InterfaceContext;
import edu.udo.cs.miningmart.m4.Column;
import edu.udo.cs.miningmart.m4.Columnset;
import edu.udo.cs.miningmart.m4.Concept;
import edu.udo.cs.miningmart.m4.Feature;
import edu.udo.cs.miningmart.m4.Parameter;
import edu.udo.cs.miningmart.m4.Step;
import edu.udo.cs.miningmart.m4.Value;
import edu.udo.cs.miningmart.m4.utils.Print;

/**
 * This class extends the class <code>DB</code> to serve as a collection of service and
 * convenience methods for the M4 compiler.
 * 
 * @see edu.udo.cs.miningmart.m4.core.utils.DB
 * 
 * @author Timm Euler
 * @version $Id: CompilerDatabaseService.java,v 1.7 2006/04/11 14:10:16 euler Exp $
 */
public class CompilerDatabaseService extends DB implements BusinessDbConnectionSource
{  	
	/**
	 * Constructor for CompilerDatabaseService.
	 * @param m4Url
	 * @param m4DbName
	 * @param m4User
	 * @param m4Passwd
	 * @param dataUrl
	 * @param dataDbName
	 * @param dataUser
	 * @param dataPasswd
	 * @param computeStatistics
	 * @param cal
	 * @throws SQLException
	 * 
     * @see edu.udo.cs.miningmart.db.DB
	 */
	public CompilerDatabaseService(
		String m4Url,
		String m4DbName,
		String m4User,
		String m4Passwd,
		String dataUrl,
		String dataDbName,
		String dataUser,
		String dataPasswd,
		boolean computeStatistics,
		M4InterfaceContext cal)
		throws SQLException
	{
			super(
				m4Url,
				m4DbName,
				m4User,
				m4Passwd,
				dataUrl,
				dataDbName,
				dataUser,
				dataPasswd,
				computeStatistics,
				cal);
	}

	/**
	 * @see edu.udo.cs.miningmart.db.DB
	 */
	public CompilerDatabaseService( ConfigReader cr, 
			                        boolean computeStatistics, 
									M4InterfaceContext cal) throws SQLException {
		super (cr, computeStatistics, cal);
	}
	
	/**
	 * @param db an existing <code>DB</code> connection to copy the
	 * connection data from and to share the cache with.
	 * @param m4i the <code>M4InterfaceContext</code> to use
	 */
	public CompilerDatabaseService(DB db, M4InterfaceContext m4i) {
		super(db, m4i);	
	}	

    /** 
     * Overwrites the superclass method to add the created view
     * or table to the trash.
     *
     * @param Columnset to be tested
     * @param step the step in which this Columnset was created
     * @return TRUE if a table was created, FALSE if a view was created
     */
    public boolean createSQLView(Columnset cs, Step step) throws M4CompilerError
    {
    	try {
    		boolean materialized = super.createSQLView(cs);
    		final long stepId = step.getId();
			if (materialized) {
		        this.addTableToTrash(cs.getName(), cs.getSchema(), stepId);
			}
			else {
		        this.addViewToTrash(cs.getName(), cs.getSchema(), stepId);
			}
			return materialized;
    	}
    	catch (DbConnectionClosed dbe)
    	{   throw new M4CompilerError("DB Connection closed when creating view for Columnset " + cs.getName() + ": " + dbe.getMessage());   }
    	catch (M4Exception m4e)
    	{   throw new M4CompilerError("M4Exception caught when creating view for Columnset " + cs.getName() + ": " + m4e.getMessage());   }
    }
    
    /**
     * If an operator creates an actual table in the Database, it <b>must</b>
     * call this method so that this information can be kept. The table will
     * be deleted once all metadata created in the given step is deleted!
     *
     * @param tableName Name of the table.
     * @param schemaName Name of the db schema in which the table lives.
     * @param stepId M4 Id of the step in which the table was created.
     */
    public void addTableToTrash(String tableName, String schemaName, long stepId) 
           throws M4CompilerError {  	
    	this.addDatabaseObjectToTrash( tableName, 
    			                       schemaName, 
									   edu.udo.cs.miningmart.m4.core.Step.DBT_TYPE_TABLE, 
									   stepId);
    }

    /**
     * If an operator creates a function in the Database, it <b>must</b>
     * call this method so that this information can be kept. The function will
     * be deleted once all metadata created in the given step is deleted!
     *
     * @param functionName Name of the function.
     * @param schemaName Name of the db schema in which the function lives.
     * @param stepId M4 Id of the step in which the function was created.
     */
    public void addFunctionToTrash(String functionName, String schemaName, long stepId) 
           throws M4CompilerError {  	
    	this.addDatabaseObjectToTrash( functionName, 
    			                       schemaName, 
									   edu.udo.cs.miningmart.m4.core.Step.DBT_TYPE_FUNCTION, 
									   stepId);
    }

    /**
     * If an operator creates an index in the Database, it <b>must</b>
     * call this method so that this information can be kept. The index will
     * be deleted once all metadata created in the given step is deleted!
     *
     * @param indexName Name of the index.
     * @param schemaName Name of the db schema in which the index lives.
     * @param stepId M4 Id of the step in which the index was created.
     */
    public void addIndexToTrash(String indexName, String schemaName, long stepId) 
           throws M4CompilerError {  	
    	this.addDatabaseObjectToTrash( indexName, 
    			                       schemaName, 
									   edu.udo.cs.miningmart.m4.core.Step.DBT_TYPE_INDEX, 
									   stepId);
    }

    /**
     * If an operator creates an actual view in the Database, it <b>must</b>
     * call this method so that this information can be kept. The view will
     * be deleted once all metadata created in the given step is deleted!
     *
     * @param viewName Name of the view.
     * @param schemaName Name of the db schema in which the view lives.
     * @param stepId M4 Id of the step in which the view was created.
     */
    public void addViewToTrash(String viewName, String schemaName, long stepId) 
           throws M4CompilerError {  	
    	this.addDatabaseObjectToTrash( viewName, 
    			                       schemaName, 
									   edu.udo.cs.miningmart.m4.core.Step.DBT_TYPE_VIEW, 
									   stepId);
    }

	private void addDatabaseObjectToTrash(String objectName, String schemaName, String objectType, long stepId)
		throws M4CompilerError
	{
		Step theStep = null;
		String msg = null;
		try {
			theStep = (Step) this.getM4Object(stepId, Step.class);
		}
		catch (M4Exception e) {
			msg = e.getMessage();
		}
		
		if (theStep == null) {
			throw new M4CompilerError(
				"Could not add database object to trash of step with ID"
				+ stepId + "!\n" + (msg == null ? " Step not found!" : "Exception: " + msg));
		}
		
		try {
			theStep.addDatabaseObjectToTrash(objectName, schemaName, objectType);
		}
		catch (M4Exception e) {
			throw new M4CompilerError(e.getMessage());	
		}
	}
	
    /**
     * This method may be needed to provide wrappers with a database connection
     * to the business database.
     * Using this method should be avoided, because the database accesses are not
     * controlled by the control structure, if it is used. For these external
     * accesses a separate business data Connection is set up, <b>which has to be
     * closed by the calling operator!</b>
	 *
	 * @return a <code>java.sql.Connection</code> to the business database
     */
    public Connection getDatabaseConnectionForData()
    	throws M4CompilerError
    {
	   try {
	   		return this.getBusinessDbCore().getExternalDatabaseConnection();
	   }
	   catch (SQLException e) {
	   		throw new M4CompilerError("DB.getDatabaseConnectionForData(): Could not create new "
	   								 + "Connection for external usage:\n" + e.getMessage());
	   }
       catch (DbConnectionClosed dbe)
       {   throw new M4CompilerError("DB Connection closed: " + dbe.getMessage());   }    
    }
	
	/**
	 * Return the DBMS-dependent string which can be used in a SELECT-Statement in SQL
	 * to uniquely identify table rows by an integer.
	 * 
	 * @param useBusinessDBMS Set this parameter to TRUE if the unique row identifier will be used 
	 *        in the business data schema. (This is probably the case.) Set it to FALSE if the 
	 *        identifier will be used in the M4 data schema.
	 * @return A DBMS-dependent string which identifies table rows by integers in SQL-Statements.
	 */
	public String getUniqueRowIdentifier(boolean useBusinessDBMS) throws M4CompilerError
	{   try {
			if (useBusinessDBMS)
			{   return this.getBusinessDbCore().getUniqueRowIdentifier();   }
			else
			{   return this.getM4DbCore().getUniqueRowIdentifier();   }
	    }
	    catch (DbConnectionClosed dbcc)
	    {   throw new M4CompilerError("CompilerDatabaseService.getUniqueRowIdentifier: " + dbcc.getMessage());  }
	}
	
	/**
	 * 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 length Gives a size for the datatype. 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.
	 * @param useBusinessDBMS Set this parameter to TRUE if the datatype should be looked up
	 *        in the DB that holds the business data schema. Set it to FALSE if it should
	 *        be looked up in the M4 schema DB.
	 * @return A DBMS-dependent string that contains a datatype name.
	 */
	public String getDbNameOfM4Datatype(String m4RelDatatypeName, int length, boolean useBusinessDBMS) throws M4CompilerError
	{   try {
			if (useBusinessDBMS)
			{   return this.getBusinessDbCore().getDatatypeName(m4RelDatatypeName, length);   }
			else
			{   return this.getM4DbCore().getDatatypeName(m4RelDatatypeName, length);   }
	    }
	    catch (DbConnectionClosed dbcc)
	    {   throw new M4CompilerError("CompilerDatabaseService.getDbNameOfM4Datatype: Db connection closed: " + dbcc.getMessage());  }
	}
	
	/**
	 * Returns the case id for the given step.
	 * 
	 * @param stepId An M4 step Id.
	 * @return The case Id that is entered in the database for the 
	 * 		   step with the given Id.
	 */
	public long readCaseIdForStepId(long stepId)
		throws M4CompilerError
    {
    	try {
		    String query =
		           "SELECT st_caid FROM step_t WHERE st_id = " + stepId;
	        Long caseId = this.executeM4SingleValueSqlReadL(query);
		    if (caseId == null) {
		        throw new M4CompilerError("M4 Database: Case not found");
		    }
			return caseId.longValue();
    	}
		catch (SQLException sqle) {
			throw new M4CompilerError(
				"SQL error when reading case info for step " + stepId
				+ ":\n" + sqle.getMessage());
		}    		
		catch (DbConnectionClosed dbe) {
			throw new M4CompilerError(
				"DB Connection closed when reading case info for step "
				+ stepId + ":\n" + dbe.getMessage());
		}
	}
    
	/**
     * Computes the sum of the entries in the given column.
     * 
     * @param col the column
     * @return the sum of all values in the column as a String
     */
    public String computeSum(Column col)
    	throws M4CompilerError
    {
    	try {
	    	edu.udo.cs.miningmart.m4.Columnset cs = col.getColumnset();            
	    	String query, sum;
	        query = "SELECT SUM(" +
	        		col.getSQLDefinition() +
	        		") FROM " + cs.getSchemaPlusName();               
			sum = this.executeBusinessSingleValueSqlRead(query);
	        return sum;
    	}
    	catch (SQLException sqle) {
    		throw new M4CompilerError(
    			"SQL Error trying to compute sum for column " + col.getId()
    			+ ":\n" + sqle.getMessage());
    	}
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when computing sum in column " + col.getId()
    			+ ":\n" + dbe.getMessage());
    	}
    }

	/**
	 * Computes the number of distinct elements present in the column.
     * 
     * @param col the column
     * @return the number of different values in the column as a String
	 */
	public String computeNumberOfDistinctElements(Column col)
		throws M4CompilerError
	{
		try {
	    	edu.udo.cs.miningmart.m4.Columnset cs = col.getColumnset();            
	    	String query, sum;
	        query = "SELECT COUNT(DISTINCT(" +
	        		col.getSQLDefinition() +
	        		")) FROM " + cs.getSchemaPlusName();               
			sum = this.executeBusinessSingleValueSqlRead(query);
	        return sum;		
		}
    	catch (SQLException sqle) {
    		throw new M4CompilerError(
    			"SQL Error trying to compute no of elements for column " + col.getId()
    			+ ":\n" + sqle.getMessage());
    	}
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when computing element number in column " + col.getId()
    			+ ":\n" + dbe.getMessage());
    	}
	}
	
    /**  
     * This method checks if the generated sql-string for
     * a new column is executable on the database. It is
     * only a select-statement, no database object is created.
     * It returns the DB datatype of the column as provided by the query.
     *
     * @param Column to be tested
     * @return Return the number of the database type of the Column, as specified
     * 		   in table COL_DATATYPE_T in the M4.
     */
    public long testSQLColumn(Column col) throws M4CompilerError
    {
    	String dataType = null;
    	ResultSet rs = null;
    	try {
		    String query = "SELECT " + col.getSQLDefinition() + " FROM " +
	        	    col.getColumnset().getSchema() + "." + col.getColumnset().getName();
	        if (col.getColumnset().getType() == Columnset.CS_TYPE_TABLE) {
	        	query += " WHERE " + this.getUniqueRowIdentifier(false) + " < 1";
	        }
		    rs = this.executeBusinessSqlRead(query);
		    ResultSetMetaData md = rs.getMetaData();
		    dataType = md.getColumnTypeName(1);
    	}
    	catch (SQLException sqle)
    	{   throw new M4CompilerError("SQL Error testing SQL definition for column " + col.getId() + ": " + sqle.getMessage());   }    
    	catch (DbConnectionClosed dbe)
    	{   throw new M4CompilerError("DB Connection closed when testing definition for column " + col.getId() +": " + dbe.getMessage());   }    
		finally {
			DB.closeResultSet(rs);
		}
    	
	    if (dataType != null) {
        	String m4DataType = this.busiDbc.getM4DatatypeName(dataType);
        	if (m4DataType != null) {
    	    	try {
		        	return (new edu.udo.cs.miningmart.m4.core.RelationalDatatypes(this)).getIdForName(m4DataType);
	        	}
        		catch (M4Exception m4e) {
	        	    throw new M4CompilerError("M4 error when testing a new Column: " + m4e.getMessage());
	        	}
        	}
	    }
        // If we don't know:
	    throw new M4CompilerError("SQL-Datatype returned for column not known: " +
				  "Got " + dataType +
				  " but expected NUMBER/NUMERIC or VARCHAR or DATE or CHAR");
    } // end public int testSQLColumn
    
    /**
     * This method returns an array of Value objects whose
     * value is a String of the form 'count, element' for 
     * each element in the column.
     * 
     * @author Felix Koschin
     * @param col The column
     * @return An array of value objects whose value is a String
     */   
	public String[] getCountOfElements(Column col) throws M4CompilerError
	{
		Vector theElements = new Vector();
		ResultSet rs = null;
		try {
			String query = "SELECT " + col.getName() + ", COUNT("
					     + col.getName() + ") AS ItemsCount "
						 + "FROM " + col.getColumnset().getName()
						 + " GROUP BY " + col.getName()
						 + " ORDER BY " + col.getName();
			
			rs = this.executeBusinessSqlRead(query);

			while (rs.next()) {
				theElements.add(
						rs.getString("ItemsCount")
						+ ","
						+ rs.getString(col.getName()));
			}
		}
		catch (SQLException sqle) {
			throw new M4CompilerError(
				"Error trying to read distinct values in column " + col.getName()
				+ ":\n" + sqle.getMessage());
		}
    	catch (DbConnectionClosed dbe)	{
    		throw new M4CompilerError(
    			"DB Connection closed when counting elements in column "
    			+ col.getId() +": " + dbe.getMessage());
    	}
    	finally {
    		DB.closeResultSet(rs);
    	}
    	
		theElements.trimToSize();
		String[] El = new String[theElements.size()];
		for (int i = 0; i < theElements.size(); i++) {
			El[i] = (String) theElements.get(i);
		}

		return El;
	}
	
    /**
     * This method returns an array of Value objects whose
     * value is a String of the form 'count, elementTA, elementCA' for 
     * each element in the TA and CA column(s). TA means target attribute,
     * CA means class attribute.
     * 
     * @author Felix Koschin
     * @param colTA the target column
     * @param colCA the class column
     * @return An array of value objects whose value is a String
     */ 
    public String[] getCountOfElements(Column colCA, Column colTA) throws M4CompilerError
    {
        Vector theElements = new Vector();
        ResultSet rs = null;
        try {
            String query = "SELECT " + colTA.getName() + ", " + colCA.getName() + ", COUNT(" + colTA.getName() + ") AS ItemsCount " +
				"FROM " + colTA.getColumnset().getName() + " GROUP BY " + colTA.getName() + ", " + colCA.getName() + " ORDER BY " + colTA.getName();
//            Statement stmt = dataCon.createStatement();
	        this.doPrint(Print.DB_READ, "DB Query: " + query);
            rs = this.executeBusinessSqlRead(query);
            while (rs.next()) {
                theElements.add(rs.getString("ItemsCount") + "," + rs.getString(colCA.getName()) + "," + rs.getString(colTA.getName()));
            }
        }
        catch (SQLException sqle) {
            throw new M4CompilerError(
            	"Error trying to read distinct values in column " + colTA.getName()
            	+ "," + colCA.getName() + ": " + sqle.getMessage());
        }
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when counting elements in column "
    			+ colCA.getId() + ": " + dbe.getMessage());
    	}
    	finally {
    		DB.closeResultSet(rs);
    	}
    	
        theElements.trimToSize();
        String[] El = new String[theElements.size()];
        for (int i = 0; i < theElements.size(); i++) {
        	El[i] = (String) theElements.get(i);
        }

        return El;
    }
    
    /**
     * This method returns an array of String objects 
     * of the form 'count, elementTA, elementCA' for 
     * each element in the TA and CA column(s). TA means target attribute,
     * CA means class attribute.
     * 
     * @author Felix Koschin
     * @param colTA the target column
     * @param colCA the class column
     * @param sampleSize a maximum sample size
     * @return An array of String objects 
     */ 
    public String[] getCountOfElements(Column colCA, Column colTA, long sampleSize) throws DbConnectionClosed, M4CompilerError
    {
        Vector theElements = new Vector();
        ResultSet rs = null;
        
        try
        {   
        	final edu.udo.cs.miningmart.m4.Columnset cs = colTA.getColumnset();
			String tableName = cs.getSchemaPlusName();
			
        	String query = "SELECT COUNT(*) FROM " + tableName;
			String rowcount = this.executeBusinessSingleValueSqlRead(query);
			doPrint(Print.OPERATOR,"Sample " + sampleSize + " of " + rowcount);
			
        	double sampleRatio = (double)sampleSize / Long.parseLong(rowcount) * 100;
        	doPrint(Print.OPERATOR,"SampleRatio:" + sampleRatio + "%");
        	
            if (sampleRatio < 100) {
            	// query = query + " SAMPLE BLOCK ( " + sampleRatio + ") ";
            	
            	Vector selectedCols = new Vector();
            	selectedCols.add(colCA.getName().toUpperCase());
            	selectedCols.add(colTA.getName().toUpperCase());
            
       			tableName = "SAMPLE_" + cs.getId();
            	String tmpTableName = "TMP_" + cs.getId();
            	
            	new DrawSample(colCA.getColumnset(), selectedCols,
            			       tableName, tmpTableName,
            			       new Long(rowcount), sampleSize,
            			       null, this);
            
                query = "SELECT " + colTA.getName() + ", " + colCA.getName()
                		+ ", COUNT(" + colTA.getName() + ") AS ItemsCount "
		            	+ " FROM " + tableName
		            	+ " GROUP BY " + colTA.getName() + ", " + colCA.getName()
		            	+ " ORDER BY " + colTA.getName();
            }
        	else {
                query = "SELECT " + colTA.getSQLDefinition() + " " + colTA.getName() + ", "
                		+ colCA.getSQLDefinition() + " " + colCA.getName() 
                		+ ", COUNT(" + colTA.getSQLDefinition() + ") AS ItemsCount "
		            	+ " FROM " + tableName
		            	+ " GROUP BY " + colTA.getSQLDefinition()
		            	+ ", " + colCA.getSQLDefinition()
		            	+ " ORDER BY " + colTA.getSQLDefinition();
        	}        		

	        this.doPrint(Print.DB_READ, "DB Query: " + query);

            rs = this.executeBusinessSqlRead(query);
            while (rs.next()) {
                theElements.add(rs.getString("ItemsCount") + "," + rs.getString(colCA.getName()) + "," + rs.getString(colTA.getName()));
            }
            this.dropBusinessTable(tableName);
        }
        catch (SQLException sqle)
        {
            throw new M4CompilerError("Error trying to read distinct values in column " +
                                      colTA.getName() + "," + colCA.getName() + ": " + sqle.getMessage());
        }
        catch (M4Exception m4e) {
        	throw new M4CompilerError(m4e.getMessage());
        }
    	catch (DbConnectionClosed dbe)
    	{   throw new M4CompilerError("DB Connection closed when counting elements in column " + colCA.getId() +": " + dbe.getMessage());   }    
        finally {
        	DB.closeResultSet(rs);
        }

        theElements.trimToSize();
        String[] El = new String[theElements.size()];
        for (int i = 0; i < theElements.size(); i++)
        {   El[i] = (String) theElements.get(i);  }

        return El;
    } // end public String[] getCountOfElements

    /**
     * @author Felix Koschin
     */ 
 	public String[][] getFrquencyTable(Column theClassAttribute, Feature[] theAttributes, String[] classValues)
 	throws M4CompilerError {
		
		int i;
		ArrayList Attr = new ArrayList();
		ArrayList AVal = new ArrayList();
		
		String query = "SELECT * FROM ";
		String t = "";
		for (i = 0; i < theAttributes.length; i++)
		{
			if (i == 0)
				t = theAttributes[i].getName();
			else
				t = t + ", " + theAttributes[i].getName();
			String s = "SELECT DISTINCT " + theAttributes[i].getName() + " FROM " + theClassAttribute.getColumnset().getName() + " ORDER BY " + theAttributes[i].getName();
			if (i != 0) query = query + ", ";
			query = query + "(" + s + ") AS T" + i;
		}
		query = query + " ORDER BY " + t;
		
		ResultSet rs = null;
		try {
			rs = this.executeBusinessSqlRead(query);
			while (rs.next())
			{
				String s = rs.getString(theAttributes[0].getName());
				for (i = 1; i < theAttributes.length; i++)
					s = s + ", " + rs.getString(theAttributes[i].getName());
				Attr.add(s);
				AVal.add(new long[classValues.length + 1]);
			}
			Attr.add("Total");
			AVal.add(new long[classValues.length + 1]);
			rs.close();
		}
		catch (SQLException sqle) {
			String s = "Error trying to read distinct values in columns " + theAttributes[0].getName();
			for (i = 1; i < theAttributes.length; i++)
				s = s + ", " + theAttributes[i].getName();
			s = s + ": " + sqle.getMessage();
			throw new M4CompilerError(s);
		}
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when computing frequency table: "
    			+ dbe.getMessage());
    	}
		finally {
			DB.closeResultSet(rs);
		}

		try {
			for (i = 0; i < classValues.length; i++) {
				int n;
				String s = theAttributes[0].getName();
				for (n = 1; n < theAttributes.length; n++)
					s = s + " || ', ' || " + theAttributes[n].getName();
				query = "SELECT (" + s + ") AS Label, Count(*) AS Freq " +
					" FROM " + theClassAttribute.getColumnset().getName() +
					" WHERE " + theClassAttribute.getName() + " = '" + classValues[i] + "'" +
					" GROUP BY ";
				s = theAttributes[0].getName();
				for (n = 1; n < theAttributes.length; n++)
					s = s + ", " + theAttributes[n].getName();
				query = query + s;
				rs = this.executeBusinessSqlRead(query);
				
				n = 0;
				while (rs.next())
				{
					s = rs.getString("Label");
					while (!(s.equals(Attr.get(n)))) n++;
					((long[])(AVal.get(n)))[i] = Long.parseLong(rs.getString("Freq"));					
				}
				rs.close();
			}
		}
		catch (SQLException sqle) {
			throw new M4CompilerError("SQL Error when accessing frequency table: "
			+ sqle.getMessage());
		}
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when accessing frequency table: "
    			+ dbe.getMessage());
    	}    
		finally {
			DB.closeResultSet(rs);
		}

    	
		String[][] retVal = new String[Attr.size()][classValues.length + 2];

		long[] sumCA = new long[classValues.length + 1];
		for (i = 0; i < Attr.size() - 1; i++)
		{
			retVal[i][0] = (String)(Attr.get(i));
			long[] val = ((long[])(AVal.get(i)));
			long sum = 0;
			for (int n = 0; n < classValues.length; n++)
			{
				retVal[i][n + 1] = Long.toString(val[n]);
				sum = sum + val[n];
				sumCA[n] = sumCA[n] + val[n];
			}			
			retVal[i][classValues.length + 1] = Long.toString(sum);
		}

		i = Attr.size() - 1;
		retVal[i][0] = (String)(Attr.get(i));
		long sum = 0;
		for (int n = 0; n < classValues.length; n++)
		{
				retVal[i][n + 1] = Long.toString(sumCA[n]);
				sum = sum + sumCA[n];
		}
		retVal[i][classValues.length + 1] = Long.toString(sum);

		return retVal;
	}
  
    /**
     * Computes the number of entries in the given column which have the given value.
     * Optionally, a Group-By-Expression is used.
     * 
     * @param col the column 
     * @param value the value
     * @return the number of occurrences of the value in the column as a String
     */
    public String computeNumberOfElementsForValue(Column col, String value)
    	throws M4CompilerError
    {
    	try {
    		edu.udo.cs.miningmart.m4.Columnset cs = col.getColumnset();            
	    	String query, sum;
	    	String valueForQuery;
	    	if (col.getColumnDataType() == 13) // datatype STRING
	    	{  valueForQuery = DB.quote(value);   }
	    	else { valueForQuery = value;  }
	        
	        query = "SELECT COUNT(*) FROM " + cs.getSchemaPlusName() +
	        		" WHERE " + col.getSQLDefinition() + 
	        		" = " + valueForQuery;               
			sum = this.executeBusinessSingleValueSqlRead(query);
	        return sum;		    	
    	}
    	catch (SQLException sqle)
    	{   throw new M4CompilerError("SQL error when computing no of elements per Value for column " + col.getId() +": " + sqle.getMessage());   }    
    	catch (DbConnectionClosed dbe)
    	{   throw new M4CompilerError("DB Connection closed when computing no of elements per Value for column " + col.getId() +": " + dbe.getMessage());   }    
    }
    
    /**
     * This method executes a Stored Procedure in the database.
     * 
     * @param procedureName The name of the stored procedure.
     * @param parameters The parameters of the stored procedure.
     *        Note that table names and similar parameters should be
     *        quoted like this: 'tablename'. The static method
     *        <i>String quote(String)</i> of this class can be used to
     *        quote such strings.
     * @param businessDb <code>true</code> indicates that the
     *        stored procedure is located in the schema of the
     *        business data, <code>false</code> executes a stored
     *        procedure in the M4 schema.
    */
    public void executeDBProcedure(String procedureName, String[] parameters, boolean businessDb)
		throws M4CompilerError
    {
    	try {
	    	DbCore dbc = (businessDb ? this.getBusinessDbCore() : this.getM4DbCore());
	    	dbc.executeDBProcedure(procedureName, parameters);
    	}
    	catch (SQLException sqle)
    	{   throw new M4CompilerError("SQL error executing a stored procedure: " + sqle.getMessage());   }    
    	catch (DbConnectionClosed dbe)
    	{   throw new M4CompilerError("DB Connection closed when executing a stored procedure: " + dbe.getMessage());   }    
    }
    
	/**
	 * Return the different values that occur in this column in the
	 * database in a String array. If the Column contains NULL values,
	 * there will be a String object in the returned array which
	 * is <code>null</code>. If you want to avoid that, use the
	 * method <code>getDistinctElementsWithoutNull(Column)</code>.
	 * 
	 * @param col The Column
	 * @return A String array 
	 */
    public String[] getDistinctElements(Column col) throws M4CompilerError
    {
        Vector theElements = new Vector();
        ResultSet rs = null;
        try {
            String query = "SELECT distinct(" + col.getSQLDefinition() + ") " +
                           "FROM " + col.getColumnset().getSchemaPlusName();
		    rs = this.executeBusinessSqlRead(query);
            while (rs.next()) {
                theElements.add(rs.getString(1));
            }
			rs.close();
        }
        catch (SQLException sqle) {
            throw new M4CompilerError(
            	"Error trying to read distinct values in column "
                + col.getName() + ": " + sqle.getMessage());
        }
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when reading distinct values for column "
    			+ col.getId() +": " + dbe.getMessage());
    	}
    	finally {
    		DB.closeResultSet(rs);
    	}
    	
        theElements.trimToSize();
        String[] distEl = new String[theElements.size()];
        for (int i = 0; i < theElements.size(); i++) {
        	distEl[i] = (String) theElements.get(i);
        }

        return distEl;
    } // end public String[] getDistinctElements

	/**
	 * Return the different values that occur in this column in the
	 * database in a String array.
	 * 
	 * @param columnId The Column id
	 * @return A String array 
	 */
    public String[] getDistinctElements(long columnId) throws M4CompilerError
    {
    	try {
			return this.getDistinctElements((Column) this.getM4Object(columnId, Column.class));
    	}
        catch (M4Exception m4e)
        {   throw new M4CompilerError("M4 error when reading distinct elements for Column with Id "
        	                          + columnId + ": " + m4e.getMessage());   
        }
    }
    
	/**
	 * Return the different non-null values that occur in this column in the
	 * database in a String array. If the Column contains NULL values,
	 * this will not lead to an entry for null in the returned array.
	 * 
	 * @param col The Column
	 * @return A String array 
	 */
    public String[] getDistinctElementsWithoutNull(Column col) throws  M4CompilerError
    {
        Vector theElements = new Vector();
        ResultSet rs = null;
        try {
            String query = "SELECT distinct(" + col.getSQLDefinition() + ") " +
                           "FROM " + col.getColumnset().getSchemaPlusName();
		    rs = this.executeBusinessSqlRead(query);
            String s;
            while (rs.next()) {
            	s = rs.getString(col.getName());
            	theElements.add(s);
            }
			rs.close();
        }
        catch (SQLException sqle) {
            throw new M4CompilerError(
            	"Error trying to read distinct values in column "
            	+ col.getName() + ": " + sqle.getMessage());
        }
    	catch (DbConnectionClosed dbe) {
    		throw new M4CompilerError(
    			"DB Connection closed when reading distinct values for column "
    			+ col.getId() +": " + dbe.getMessage());
    	}
    	finally {
    		DB.closeResultSet(rs);
    	}
    	
        theElements.trimToSize();       
        String[] distEl = new String[theElements.size()];
        for (int i = 0; i < theElements.size(); i++) {
        	distEl[i] = (String) theElements.get(i);
        }

        return distEl;
    }
    
    /* Never used:
	private static String nullToNULL(String s) { return (s == null) ? "NULL" : s; }
	
	private static String nullToNULLquote(String s) {
		return (s == null) ? "NULL" : "'" + s.trim() + "'";
	} 
	*/   

    /**
     * This method should only be used by the compiler's control structure.
     * 
     * @param step a <code>Step</code> to find all input Concepts for
     * @return A <code>Collection</code> of <code>Concept</code>s
    */
	public Collection getInputConceptsFor(Step step)
		throws M4Exception
	{
		Vector ret = new Vector();
		Iterator it = step.getParameterTuples().iterator();
		while (it.hasNext()) {
			Parameter parameter = (Parameter) it.next();
			if (parameter.isInputParam()) {
				edu.udo.cs.miningmart.m4.ParameterObject parObj = parameter.getTheParameterObject();
				if (parObj instanceof Concept)
					ret.add(parObj);
			}
		}
		return ret;
	}

	private DbCore getM4DbCore() throws DbConnectionClosed
	{
		if (this.cal.getStopRequest()) {
			this.stopDbThreads(); // always throws an exception
		}
		// else:
		return this.m4Dbc;
	}

	public DbCore getBusinessDbCore() throws DbConnectionClosed
	{
		if (this.cal.getStopRequest()) {
			this.stopDbThreads(); // always throws an exception
		}
		// else:
		return this.busiDbc;
	}
	
	private void stopDbThreads() throws DbConnectionClosed
	{
		this.m4Dbc.stopDbThread();
		this.busiDbc.stopDbThread();
		throw new DbConnectionClosed("Requested to stop DB threads.");
	}
	
	/**
	 * 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 owner Name of the owner of the table or view
	 * @param tableName Name of a table or view in the business schema
	 * @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 String getSelectStringForColumnDataType(String owner, String tableName, String columnName) {
		return this.busiDbc.getSelectStringColumnDataTypes(tableName, owner, columnName);
	}
	
	public String getNameOfNumericDatatype() {
		return this.busiDbc.getDatatypeName(edu.udo.cs.miningmart.m4.core.RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER, -1);
	}
}
/*
 * Historie
 * --------
 *
 * $Log: CompilerDatabaseService.java,v $
 * Revision 1.7  2006/04/11 14:10:16  euler
 * Updated license text.
 *
 * Revision 1.6  2006/04/06 16:31:15  euler
 * Prepended license remark.
 *
 * Revision 1.5  2006/03/29 09:50:47  euler
 * Added installation robustness.
 *
 * Revision 1.4  2006/03/02 16:49:59  euler
 * Many bugfixes
 *
 * Revision 1.3  2006/01/09 09:22:40  euler
 * Bugfixes
 *
 * Revision 1.2  2006/01/06 16:24:47  euler
 * Updates and bugfixes in the delete-Mechanism for M4Data objects.
 *
 * Revision 1.1  2006/01/03 09:54:22  hakenjos
 * Initial version!
 *
 */
