/*
 * 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.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import edu.udo.cs.miningmart.exception.DbConnectionClosed;
import edu.udo.cs.miningmart.exception.M4Exception;
import edu.udo.cs.miningmart.m4.M4InterfaceContext;
import edu.udo.cs.miningmart.m4.RelationalDatatypes;

/**
 * This class provides an access to an Oracle DBMS.
 * It has only been tested with Oracle version 8.1.6.
 *  
 * To compile this class, the Oracle JDBC driver package must
 * be added to the classpath.
 * 
 * @author Martin Scholz
 * @version $Id: DbCoreOracle.java,v 1.5 2006/04/11 14:10:16 euler Exp $
 */
public class DbCoreOracle extends DbCore
{
	/** The public constant indicating the Oracle Datatype NUMBER */
	public static final String ORACLE_TYPE_NUMBER = "NUMBER";
	
	/** The public constant indicating the Oracle Datatype VARCHAR2 */
	public static final String ORACLE_TYPE_STRING = "VARCHAR2";
	
	/** The public constant indicating the Oracle Datatype DATE */
	public static final String ORACLE_TYPE_DATE = "DATE";
	
	/** The public constant indicating the Oracle Datatype CHAR */
	public static final String ORACLE_TYPE_CHAR = "CHAR";
	
	/** The public constant indicating the Oracle Datatype LONG */
	public static final String ORACLE_TYPE_LONG = "LONG";
	
    /**
     * 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 isM4Schema is <code>true</code> iff the connection refers to the M4 schema. Useful
     * 		  to decide whether triggers need to be switched off etc.
     * 
     * @see edu.udo.cs.miningmart.m4.core.utils.DbCore#DbCore(String, String, String, String, CompilerAccessLogic, boolean)
     * @throws SQLException
     */
    public DbCoreOracle(
    			   String url,
               	   String dbName,
	               String user,
    	           String passwd,
        	       M4InterfaceContext cal,
        	       boolean isM4Schema)
		throws SQLException
    {
    	super(url, dbName, user, passwd, cal, isM4Schema);
    }
    
	/**
	 * @see DbCore#getDbms()
	 */
	public short getDbms() {
		return DB.ORACLE;
	}

	/**
	 * @see DbCore#registerJDBC_Driver()
	 */
	protected void registerJDBC_Driver() throws SQLException {
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
	}
	
	/**
	 * @see DbCore#switchAutocommitOff()
	 */
	protected void switchAutocommitOff(Connection con) throws SQLException {
		con.setAutoCommit(false);
	}	
	
	/**
	 * @see DbCore#getNextM4SequenceValue(Statement)
	 */
	protected long getNextM4SequenceValue(Statement stmt)
		throws M4Exception
	{
	        String query = "select all_sq.nextval from dual";
			Long nextval = null;
			String sqle  = "";
	        try {
				nextval = this.executeSingleValueSqlReadL(query, stmt);
	        }
	        catch (SQLException e) {
	        	sqle = "\nAn SQLException occured during invokation:\n" + e.getMessage();
	        }
			if (nextval == null) {
				String msg = "Error in method DbCore.getNextM4SequenceValue(Statement):\n"
						   + "Sequence 'all_sq.nextval' did not return a value!"
						   + sqle;
				throw new M4Exception(msg);
			}
			return nextval.longValue();
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return An SQL String
	 */
	public String getSelectStringAllTables() {
		return "SELECT UPPER(TABLE_NAME) FROM USER_TABLES";
	}

	public boolean tableExists(String tableName) throws M4Exception {
		String query = "SELECT table_name FROM user_tables WHERE table_name='" +
						tableName.toUpperCase() + "'";
		String s = null;
		try {
			s = this.executeSingleValueSqlRead(query);
		}
		catch (SQLException sqle) {
			throw new M4Exception("SQL error testing if table '" + tableName + "' exists: " + sqle.getMessage());
		}
		catch (DbConnectionClosed d) {
			throw new M4Exception("DB connection error when testing if table '" + tableName + "' exists: " + d.getMessage());
		}
		return (s != null);
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return An SQL String
	 */
	public String getSelectStringAllViews() {
		return "SELECT UPPER(VIEW_NAME) FROM USER_VIEWS";
	}
	
	/**
	 * @see DbCore
	 * 
	 * @param dbObjectName Name of a table or view in the business data schema
	 * @return An SQL String
	 */
	public String getSelectStringAllColumnsForDbObject(String dbObjectName) {
	    return "SELECT column_name, data_type " +
    		   "FROM USER_TAB_COLUMNS " + 
    		   "WHERE table_name = '" + 
    		   dbObjectName.toUpperCase() + "'";
	}
	
	/**
	 * @see DbCore
	 * 
	 * @param dbObjectName Name of a table or view in the business data schema
	 * @param owner Name of the owner of the table or view (can be null)
	 * @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 getSelectStringColumnDataTypes(String dbObjectName, String owner, String columnName) {
	    String ret = "SELECT data_type " +
	    		   "FROM ALL_TAB_COLUMNS " + 
	    		   "WHERE table_name = '" + 
	    		   dbObjectName.toUpperCase() + 
	    		   "' AND column_name = '" +
	    		   columnName.toUpperCase() + "'";
	    if (owner != null) {
	    	ret += " AND owner = '" + owner.toUpperCase() + "'";
	    }
        return ret;
	}
	
	/**
	 * 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 Collection getPrimaryKeyColumnNames(String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		String query = "SELECT column_name FROM user_cons_columns, user_constraints " +
			"WHERE user_constraints.constraint_name = user_cons_columns.constraint_name " +
			"AND user_constraints.constraint_type = 'P' " + 
			"AND user_cons_columns.table_name = '" +
			dbObjectName.toUpperCase() + "'";
		ResultSet rs = this.executeSqlRead(query);
		Vector ret = new Vector();
		while (rs.next()) {
			ret.add(rs.getString(1));
		}
		rs.close();
		if (ret.isEmpty()) {
			return null;
		}
		else {
			return ret;
		}
	}
	
	/**
	 * Returns a Map that maps every column of the given table that acts as
	 * a foreign key to the name of the table that it references. 
	 * The Map uses only Strings.
	 * 
	 * @param dbObjectName
	 * @return
	 * @throws SQLException
	 * @throws DbConnectionClosed
	 */
	public Map getTablesReferencedBy(String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		String query = "SELECT constraint_name, r_constraint_name " +
			"FROM user_constraints " +
			"WHERE table_name = '" + dbObjectName + 
			"' AND constraint_type = 'R'";
		ResultSet rs = this.executeSqlRead(query);
		Map myMap = new HashMap();
		while (rs.next()) {
			String constr = rs.getString(1);
			String ref = rs.getString(2);
			String innerQuery = "SELECT table_name FROM user_constraints " +
				"WHERE constraint_name = '" + ref + "'";
			String foreignTableName = this.executeSingleValueSqlRead(innerQuery);
			if (foreignTableName != null) {
				innerQuery = "SELECT column_name FROM user_cons_columns " +
					"WHERE user_cons_columns.constraint_name = '" + constr + 
					"'";
				ResultSet rs2 = this.executeSqlRead(innerQuery);
				while (rs2.next()) {
					String columnName = rs2.getString(1);
					myMap.put(columnName, foreignTableName);
				}
				rs2.close();
			}
		}
		rs.close();
		return myMap;
	}	
	
	/**
	 * 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 String getTableOrViewType(String dbObjectName) throws SQLException, DbConnectionClosed {
		String query = "SELECT table_name FROM user_tables WHERE table_name = '" + dbObjectName + "'";
		String result = this.executeSingleValueSqlRead(query);
		if (result == null) {
			query = "SELECT view_name FROM user_views WHERE view_name = '" + dbObjectName + "'";
			result = this.executeSingleValueSqlRead(query);
			if (result == null) {
				return null;
			}
			else {
				return edu.udo.cs.miningmart.m4.Columnset.CS_TYPE_VIEW;
			}
		}
		else {
			return edu.udo.cs.miningmart.m4.Columnset.CS_TYPE_TABLE;
		}		
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return the name of the column that contains the column names
	 */
	public String getAttributeForColumnNames() {
	    return "COLUMN_NAME";
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return the name of the column that contains the column types
	 */
	public String getAttributeForColumnTypes() {
	    return "DATA_TYPE";
	}
	
	/**
	 * @see DbCore
	 */
	public String getUniqueRowIdentifier() {
		return "ROWNUM";
	}
	
	/**
	 * @see DbCore
	 */
	public String getDatatypeName(String m4RelDatatypeName, int size) {
		String ret = null;
		if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER)) {
			ret = ORACLE_TYPE_NUMBER;
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_DATE)) {
			ret = ORACLE_TYPE_DATE;
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_KEY)) {
			ret = ORACLE_TYPE_NUMBER; //??
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_STRING)) {
			ret = ORACLE_TYPE_STRING;
		}
		
		if ((ret != null) && (size > 0))
		{   ret += "(" + size + ")";   }
		
		return ret;
	}			
	
	/**
	 * @see DbCore
	 */
	public String getM4DatatypeName(String dbmsDatatypeName) {
		dbmsDatatypeName = dbmsDatatypeName.toUpperCase();
		if (dbmsDatatypeName.equals(ORACLE_TYPE_NUMBER)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER;
		}
		else if (dbmsDatatypeName.equals(ORACLE_TYPE_DATE)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_DATE;
		}
		else if (dbmsDatatypeName.equals(ORACLE_TYPE_STRING)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_STRING;
		}
		else if (dbmsDatatypeName.equals(ORACLE_TYPE_CHAR)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_STRING;
		}
		else if (dbmsDatatypeName.equals(ORACLE_TYPE_LONG)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER;
		}
		return null;
	}	
	
	/**
	 * 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 boolean dropRelation(String tableName) 
	throws M4Exception {
		boolean tableExists = this.tableExists(tableName);
		
		if (tableExists) {
			String sql_drop = "DROP TABLE " + tableName;
				
			try
			{	this.executeSqlWrite(sql_drop);  }
			catch (SQLException sqle) {
				throw new M4Exception(
					"Error trying to remove the table '"
					+ tableName + "':\n" + sqle.getMessage());
			}
			catch (DbConnectionClosed dbe) {
				throw new M4Exception(
					"DB Connection closed when deleting table '"
					+ tableName + "':\n" + dbe.getMessage());
			}    
		}		          
		               
		return tableExists;
	}
	
	/** @return a <code>String</code> that can be used as a query to test the DB connection. */
	public String getTestQuery() {
		return "SELECT 0 FROM DUAL";	
	}

}
/*
 * Historie
 * --------
 *
 * $Log: DbCoreOracle.java,v $
 * Revision 1.5  2006/04/11 14:10:16  euler
 * Updated license text.
 *
 * Revision 1.4  2006/04/06 16:31:15  euler
 * Prepended license remark.
 *
 * Revision 1.3  2006/03/29 09:50:47  euler
 * Added installation robustness.
 *
 * Revision 1.2  2006/01/24 12:31:45  euler
 * Added recognition of key type for new columns.
 * Removed EstimatedStatistics from context menu
 * because they are in too basic status for the release.
 *
 * Revision 1.1  2006/01/03 09:54:23  hakenjos
 * Initial version!
 *
 */
