import java.sql.*;

public class Estimator
{


/**
*  Input : Two sql strings    .
*  we have to decide which query is comparatively better to be executed
*  use the oralce explain plan command and plan_table to find out the cost of individual queries
*  Output : SQL squery that is beetter
*/


private ResultSet result;
private Statement stmt;
private Connection conn;
private EstabConn remotedb;

public Estimator()
{
	try {
			// specifiy the database name
			remotedb = new EstabConn();
			conn = remotedb.connectdb(new String("orcl"));
			stmt = conn.createStatement ();
	} catch(SQLException ex)  {
			ex.printStackTrace();
			System.out.println("Error in establishing connection with the database: in class Estimator");
			System.exit(0);
	}

}


public String estimate(String sqlQuery1,String sqlQuery2)
{
	if((sqlQuery1 == null) || (sqlQuery2 == null))
		{
			System.out.println("One of the query is null: in class Estimator");
			return null;
		}
	else   		/* get results from the database */
		{
			try {

		    ResultSet rs;

			String str1 = new String("EXPLAIN PLAN set STATEMENT_ID = 'X' FOR " + sqlQuery1);
			stmt.executeQuery (str1);
			String str2 = new String("EXPLAIN PLAN set STATEMENT_ID = 'Y' FOR " + sqlQuery2);
			stmt.executeQuery (str2);

			rs = stmt.executeQuery ("select cardinality, cost from plan_table where statement_id = 'X' ");
			rs.next();
			int cardinality1 = rs.getInt(1);
			int cost1 = rs.getInt(2);

			rs = stmt.executeQuery ("select cardinality, cost from plan_table where statement_id = 'Y' ");
			rs.next();
			int cardinality2 = rs.getInt(1);
			int cost2 = rs.getInt(2);

			System.out.println("Cost1:" + cost1 );
			System.out.println("Cardinality1:" + cardinality1 );
			System.out.println("Cost2:" + cost2 );
			System.out.println("Cardinality2:" + cardinality2 );

			if(cost1 == cost2)
				{
					if (cardinality1<=cardinality2)
						return sqlQuery1;
					else
						return sqlQuery2;
				}

			if(cost1 < cost2)
				return sqlQuery1;
			else
				return sqlQuery2;

			} catch(SQLException ex)  {
				ex.printStackTrace();
				System.out.println("Error in executing the sqlQuery: in Estimator");
			}

			return null;
		}

}


public void close()
{
	try {
	result.close();
	stmt.close();
	conn.close();
	remotedb.close();
	} catch(SQLException ex)  {
		ex.printStackTrace();
		System.out.println("Error in closing connection to the database: in class Estimator");
		System.exit(0);
	}

}





}