/*
 * 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.StringTokenizer;
import java.util.Vector;

import edu.udo.cs.miningmart.exception.DbConnectionClosed;
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.RelationalDatatypes;


/**
 * This class provides an access to a PostgreSQL DBMS.
 * It has only been tested with PostgreSQL version 7.3.2.
 * See www.postgresgl.org and jdbc.postgresql.org.
 * 
 * Please note that for using PostgreSQL, the db.config file
 * must have a slightly different format: the line that contains
 * the database server name and the port must begin with "//" instead
 * of "@" and must <b>not</b> be ended by a colon.
 * 
 * To compile this class, the PostgreSQL JDBC driver package must
 * be added to the classpath.
 * 
 * @author Timm Euler
 * @version $Id: DbCorePostgres.java,v 1.9 2006/05/22 20:11:52 euler Exp $
 */
public class DbCorePostgres extends DbCore
{
	/** The public constant indicating the Postgres Datatype NUMERIC */
	public static final String POSTGRES_TYPE_NUMBER = "NUMERIC";
	
	/** The public constant indicating the Postgres Datatype VARCHAR */
	public static final String POSTGRES_TYPE_STRING = "VARCHAR";
	
	/** The public constant indicating the Postgres Datatype CHAR */
	public static final String POSTGRES_TYPE_CHAR = "CHAR";
	
	/** The public constant indicating the Postgres Datatype DATE */
	public static final String POSTGRES_TYPE_DATE = "DATE";
	
	/** The public constant indicating the Postgres Datatype TIME */
	public static final String POSTGRES_TYPE_TIME = "TIME";
	
	/** The public constant indicating the Postgres Datatype TIMESTAMP */
	public static final String POSTGRES_TYPE_TIMESTAMP = "TIMESTAMP";
	
	/** The public constant indicating the Postgres Datatype TEXT */
	public static final String POSTGRES_TYPE_TEXT = "TEXT";
	
	// Name of the M4 sequence as used by the M4 installation script
	private final String nameOfM4Sequence = "all_sq";
	
	// The postgres SQL command to open a block of transactions 
	// (such a block is closed and committed by the command "COMMIT").
	// A rollback command rolls back every SQL command that was issued 
	// after this command (if no commit was done).
	// private final String POSTGRES_COMMAND_INIT_TRANSACTION = "BEGIN";
	
    /**
     * The constructor of this class calls the constructor of the
     * superclass DbCore.
     * 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 DbCorePostgres( String url,
						   String dbName,
						   String user,
						   String passwd,
		                   M4InterfaceContext cal,
		                   boolean isM4Schema)
		throws SQLException
	{
		// For PostgreSQL, the URL for the database has a different format
		// than for Oracle. The format needed here is:
		
		// "jdbc:postgresql://<server>/<dbname>"
		// OR 
		// "jdbc:postgresql://<server>:<port>/<dbname>"
		
		// Therefore, the last line in db.config must start with "//" instead of "@" for PostgreSQL,
		// and it must NOT end with a colon.
		
		// The database name is preceded by "/" in this call to the constructor of the superclass.
		
		super( url,
			   "/" + dbName,
			   user,
			   passwd,
			   cal,
			   isM4Schema);
	}

	/**
	 * Implements the superclass method so that the correct
	 * JDBC driver for PostgreSQL is used.
	 */
	protected void registerJDBC_Driver() throws SQLException {
		DriverManager.registerDriver(new org.postgresql.Driver());
	}

	/** 
	 * Indicates the DBMS used. 
	 * 
	 * @return The public static constant DB.POSTGRES
	 */
	public short getDbms() {
		return DB.POSTGRES;
	}
	
	/**
	 * @see DbCore#switchAutocommitOff()
	 */
	protected void switchAutocommitOff(Connection con) throws SQLException {
		// Postgres does not support "Autocommit Off" any longer!
		// con.setAutoCommit(false);
		
		// Now start the first session of the new connection by
		// opening a new transaction block:
		this.openTransactionBlock();
	}
	
	/**
	 * Commit the last transactions in the M4 database.
	 * This method overrides the superclass method because POSTGRES
	 * uses a slightly modified transaction mechanism.
	 */
	public void commitTransactions() throws SQLException {
		// do everything the superclass method does:
		super.commitTransactions();
		
		// ... but add the initialization of the next transaction:
		this.openTransactionBlock();
	}
	
	/**
 	 * @see DbCore#getNextM4SequenceValue(Statement)
	 */
	protected long getNextM4SequenceValue(Statement stmt)
		throws M4Exception
	{
	        String query = "select nextval('" + this.nameOfM4Sequence + "')";
			Long nextval = null;
			String sqle  = "";
	        try {
				nextval = this.executeSingleValueSqlReadL(query, stmt);
	        }
	        catch (SQLException e) {
	        	sqle = "\nAn SQLException occured during invocation:\n" + e.getMessage();
	        }
			if (nextval == null) {
				String msg = "Error in method DbCorePostgres.getNextM4SequenceValue(Statement):\n"
						   + "Sequence '" + this.nameOfM4Sequence + "' did not return a value!"
						   + sqle;
				throw new M4Exception(msg);
			}
			return nextval.longValue();
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return An SQL String
	 */
	public String getSelectStringAllTables() {
		// In Postgres the System tables start with "pg_"... so exclude them:
		String ret = "SELECT UPPER(relname) FROM pg_class, pg_user WHERE relkind='r'" +
		  	         "AND SUBSTRING(relname FROM 1 FOR 3) != 'pg_'";
		if (this.getUser() != null) {
			ret += " AND relowner = pg_user.usesysid AND pg_user.usename = '" + this.getUser() + "'";
		}
		return ret;		
	}

	/**
	 * @see DbCore
	 * 
	 * @return An SQL String
	 */
	public String getSelectStringAllViews() {
		// In Postgres the System views start with "pg_"... so exclude them:
		String ret = "SELECT UPPER(relname) FROM pg_class, pg_user WHERE relkind='v'" +
		  	         "AND SUBSTRING(relname FROM 1 FOR 3) != 'pg_'";
		if (this.getUser() != null) {
			ret += " AND relowner = pg_user.usesysid AND pg_user.usename = '" + this.getUser() + "'";
		}
		return ret;		
	}
	
	/**
	 * @see DbCore
	 * 
	 * @param dbObjectName Name of a table OR view in the business data schema
	 * @return An SQL String
	 */
	public String getSelectStringAllColumnsForDbObject(String dbObjectName) {
		// There are some standard columns that every table has in Postgres;
		// these are excluded here. (This works for views as well.)
	    return "SELECT attname, typname " +
	           "FROM pg_attribute, pg_type, pg_class " +
	           "WHERE attrelid = pg_class.oid " + // link to table of tables/views
	           "AND atttypid = pg_type.oid " +    // link to table of data types
	           "AND attname != 'tableoid' " +
	           "AND attname != 'cmax' " +
	           "AND attname != 'xmax' " +
	           "AND attname != 'cmin' " +
	           "AND attname != 'xmin' " +
	           "AND attname != 'oid' " +
	           "AND attname != 'ctid' " +
	           "AND relname = '" + dbObjectName.toLowerCase() + "'";
	}

	public boolean tableExists(String tableName) throws M4Exception {
		String query = "SELECT oid FROM pg_class, pg_user WHERE relkind='r' AND relname='" +
						tableName.toLowerCase() + "'";
		if (this.getUser() != null) {
			query += " AND relowner = pg_user.usesysid AND pg_user.usename = '" + this.getUser() + "'";
		}
		Long l = null;
		try {
			l = this.executeSingleValueSqlReadL(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 (l != null);
	}
	
	/**
	 * @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 typname " +
    		   "FROM pg_attribute, pg_type, pg_class, pg_user " +
	           "WHERE attrelid = pg_class.oid " + // link to table of tables/views
	           "AND atttypid = pg_type.oid " +    // link to table of data types
	           "AND relowner = pg_user.usesysid " + // link to table of users
	           "AND attname != 'tableoid' " +
	           "AND attname != 'cmax' " +
	           "AND attname != 'xmax' " +
	           "AND attname != 'cmin' " +
	           "AND attname != 'xmin' " +
	           "AND attname != 'oid' " +
	           "AND attname != 'ctid' " +
	           "AND relname = '" + dbObjectName.toLowerCase() + 
	           "' AND attname = '" + columnName.toLowerCase() + "'";
	    if (owner != null) {
	    	ret += " AND usename = '" + owner.toLowerCase() + "'";
	    }
	    return ret;
	}

	/**
	 * @see DbCore
	 */
	public Collection getPrimaryKeyColumnNames(String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		
		// This method may have to be adapted to Postgres versions 8.x,
		// it works for 7.2 where this is ugly stuff.
		
		// this query gives a single String with the column numbers
		// of the primary key columns:
		String query = "SELECT indkey FROM pg_index, pg_class "+
						"WHERE pg_class.oid =indrelid AND relname = '" +
						dbObjectName.toLowerCase() + "' AND indisprimary='t'";
		ResultSet rs = this.executeSqlRead(query);
		String colNos = null;
		if (rs.next()) {
			colNos = rs.getString(1);
		}
		rs.close();
		// if no primary keys are defined there's nothing we can do:
		if (colNos == null) {
			return new Vector();
		}
		// read the column numbers:
		StringTokenizer st = new StringTokenizer(colNos);
		int nextPrimaryKeyColNumber = -1;
		if (st.hasMoreTokens()) {
			nextPrimaryKeyColNumber = Integer.parseInt(st.nextToken());
		}
		query = this.getSelectStringAllColumnsForDbObject(dbObjectName);
		rs = this.executeSqlRead(query);
		Vector thePrimaryKeyColumns = new Vector();
		int colNumberRead = 1;
		while (rs.next()) {
			// we assume here that the columns are sorted, as 
			// well as the column numbers! At least the numbering
			// starts with 1 everywhere.
			String colName = rs.getString(1);
			if (colNumberRead == nextPrimaryKeyColNumber) {
				thePrimaryKeyColumns.add(colName);
			}
			if (st.hasMoreTokens()) {
				nextPrimaryKeyColNumber = Integer.parseInt(st.nextToken());				
			}
			else {
				break;
			}
			colNumberRead++;
		}
		rs.close();
		return thePrimaryKeyColumns;
	}

	public boolean isColumnUsedAsPrimaryKey(String columnName, String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		Collection primColumnsForTable = this.getPrimaryKeyColumnNames(dbObjectName);
		if (primColumnsForTable == null) {
			return false;
		}
		return primColumnsForTable.contains(columnName);
	}
	
	/**
	 * @see DbCore
	 */
	public Map getTablesReferencedBy(String dbObjectName) 
	throws SQLException, DbConnectionClosed {
		
		// Probably this method must be updated for Postgres version 8.X!
		// It works for 7.2 where this is ugly stuff.
		
		String query = this.getSelectStringAllColumnsForDbObject(dbObjectName);
		ResultSet rs = this.executeSqlRead(query);
		Map myMap = new HashMap();
		while (rs.next()) {
			myMap.put(rs.getString(1), null);
		}
		rs.close();
		query = "SELECT tgargs, tgconstrrelid FROM pg_trigger, pg_class " +
		        "WHERE pg_class.oid = pg_trigger.tgrelid and pg_class.relname = '" +
				dbObjectName + "'";
		rs = this.executeSqlRead(query);
		while (rs.next()) {
			String argumentsOfTrigger = rs.getString(1);
			String[] args = this.getArguments(argumentsOfTrigger);
			// the second argument of the constraint tells us the table:
			if (args.length >= 2 && args[1].equalsIgnoreCase(dbObjectName)) {
				// so get the referenced table now:
				String referencedTable = args[2];
				// now get the columns that are used as foreign keys:
				// (they start at position 4)
				if (args.length > 4) {
					int noOfKeyCols = (args.length - 4) / 2;
					for (int i = 0; i < noOfKeyCols; i++) {
						myMap.put(args[i+4], referencedTable);
					}					
				}
			}
		}
		rs.close();
		return myMap;
	}

	private String[] getArguments(String postgresTriggerArgs) {
		int i = 0;
		Vector allArgs = new Vector();
		String currentArg = "";
		while (i < postgresTriggerArgs.length()) {
			if (postgresTriggerArgs.charAt(i) == '\\') {
				i = i + 4;
				allArgs.add(currentArg);
				currentArg = "";
			}
			else {
				currentArg += postgresTriggerArgs.charAt(i);
				i++;
			}
		}
		return (String[]) allArgs.toArray();
	}

	/**
	 * 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 relkind FROM pg_class WHERE relname = '" +
				dbObjectName.toLowerCase() + "'";
		String type = this.executeSingleValueSqlRead(query);
		if (type.equalsIgnoreCase("v")) {
			return Columnset.TYPE_VIEW;
		}
		if (type.equalsIgnoreCase("r")) {
			return Columnset.TYPE_TABLE;
		}
		return null;
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return the name of the column that contains the column names
	 */
	public String getAttributeForColumnNames() {
	    return "attname";
	}
	
	/**
	 * @see DbCore
	 * 
	 * @return the name of the column that contains the column types
	 */
	public String getAttributeForColumnTypes() {
	    return "typname";
	}
	
	/**
	 * WARNING: the unique row identifier works only for tables in Postgres!
	 * @see DbCore
	 */
	public String getUniqueRowIdentifier() {
		return "oid";
	}
	
	/**
	 * @see DbCore
	 */
	public String getDatatypeName(String m4RelDatatypeName, int size) {
		String ret = null;
		if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER)) {
			ret = POSTGRES_TYPE_NUMBER;
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_DATE)) {
			ret = POSTGRES_TYPE_DATE;
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_KEY)) {
			ret = POSTGRES_TYPE_NUMBER; //??
		}
		else if (m4RelDatatypeName.equals(RelationalDatatypes.RELATIONAL_DATATYPE_STRING)) {
			ret = POSTGRES_TYPE_STRING;
		}
		
		if ((ret != null) && (size > 0))
		{   ret += "(" + size + ")";   }
		
		return ret;
	}		
	
	/**
	 * @see DbCore
	 */
	public String getM4DatatypeName(String dbmsDatatypeName) {
		dbmsDatatypeName = dbmsDatatypeName.toUpperCase();
		if (dbmsDatatypeName.equals(POSTGRES_TYPE_NUMBER)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER;
		}
		else if (dbmsDatatypeName.startsWith("FLOAT")) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER;
		}
		else if (dbmsDatatypeName.startsWith("INT")) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_NUMBER;
		}
		else if (dbmsDatatypeName.equals(POSTGRES_TYPE_DATE) ||
				 dbmsDatatypeName.equals(POSTGRES_TYPE_TIME) ||
				 dbmsDatatypeName.equals(POSTGRES_TYPE_TIMESTAMP)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_DATE;
		}
		else if (dbmsDatatypeName.equals(POSTGRES_TYPE_STRING)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_STRING;
		}
		else if (dbmsDatatypeName.equals(POSTGRES_TYPE_TEXT)) {
			return RelationalDatatypes.RELATIONAL_DATATYPE_STRING;
		}
		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;
	}
	
	/*
	 * This method opens a new transaction block. Such a block is closed 
	 * and committed by a COMMIT (SQL command). A rollback will roll back all 
	 * effects of SQL commands in the current transaction block.
	 */
	private void openTransactionBlock() // throws SQLException 
	{
	/*		
		try {
			this.executeSqlWrite(POSTGRES_COMMAND_INIT_TRANSACTION);
		}
		catch (DbConnectionClosed d) {
			throw new SQLException("Problem in DbCorePostgres.commitTransactions: DbConnectionClosed exception caught: " + d.getMessage());
		}	
		*/	
	} 
	
	/** @return a <code>String</code> that can be used as a query to test the DB connection. */
	public String getTestQuery() {
		return getSelectStringAllTables();	
	}
	
}
/*
 * Historie
 * --------
 *
 * $Log: DbCorePostgres.java,v $
 * Revision 1.9  2006/05/22 20:11:52  euler
 * *** empty log message ***
 *
 * Revision 1.8  2006/04/11 14:10:16  euler
 * Updated license text.
 *
 * Revision 1.7  2006/04/06 16:31:15  euler
 * Prepended license remark.
 *
 * Revision 1.6  2006/03/29 11:16:44  euler
 * Still more robust when installing.
 *
 * Revision 1.5  2006/03/29 09:50:47  euler
 * Added installation robustness.
 *
 * Revision 1.4  2006/03/04 12:13:44  euler
 * *** empty log message ***
 *
 * Revision 1.3  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.2  2006/01/03 15:21:44  euler
 * Bugfixes
 *
 * Revision 1.1  2006/01/03 09:54:23  hakenjos
 * Initial version!
 *
 */
