import Zql.ZQuery;
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.ROWID;


public class PopulateCache
{

private String sqlQuery=null;
private ZQuery zquery=null;

private ResultSet result=null;
private Connection conn=null;
private EstabConn localdb=null;
private	PreparedStatement pstmt=null;
private Statement stmt=null;
private Statement stmt2=null;

private static int TIDStart=0;
private static int TIDCount=0;
public static int QIDStart=0;


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

}


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

}

// NEW FUNCTIONALITY used in class Sept24Cache
// set the variables and call appropriate functions
public void populate(ResultSet result, String sqlQuery) throws SQLException
{
	this.result = result;
	this.sqlQuery = sqlQuery;


	if( result==null || result.next()==false ) { System.out.println("Empty resultset : nothing to be stored : in class PopulateCache "); return; }
	else
	{
	saveSequenceNumbers();
	ResultSet rsetCache = null;

	do
	{

	// first column has to be rowid
		String rowid = (( (OracleResultSet) result).getROWID(1)).stringValue();

		//String rowid = rset.getString(1);
		System.out.println(rowid  + " rowid string");


		String str = "select rowid from CT where RID = '" + rowid + "'";

		rsetCache = stmt2.executeQuery(str);

		if (rsetCache.next()  == false )
			{
				// data is not in cache
	    		System.out.println ("inserting into cachetable....");
	    		// call populate... something
	    		str = "insert into CT (RID,QID) values('"  + rowid + "'," + QIDStart + ")" ;
	    		System.out.println(str);
	    		stmt2.executeUpdate(str);
			 }
	    else
	    	{
				System.out.println ("Data already present in cache ");
				// save some info about the query id and tid....
				str = "insert into DT (RID,QID) values('"  + rowid + "'," + QIDStart + ")" ;
	    		stmt2.executeUpdate(str);


		 	}

	 } while (result.next ());

	} // end of else
}






// set the variables and call appropriate functions
public void populate(ResultSet result, String sqlQuery, ZQuery zquery)
{
	this.result = result;
	this.sqlQuery = sqlQuery;
	this.zquery = zquery;


	if(result==null) { System.out.println("Empty resultset : nothing to be stored : in class PopulateCache "); return; }

	saveSequenceNumbers();
	populateCache();
	populateQuery();
	populateAssociation();
}

public void saveSequenceNumbers()
{
	try {

	// Execute the query
	stmt.executeQuery ("select TID.nextval, QID.nextval from dual");
	ResultSet t1 = stmt.executeQuery ("select TID.currval, QID.currval from dual");
	t1.next();
	TIDStart = t1.getInt(1);
	QIDStart = t1.getInt(2);
	t1.close();
		} catch (SQLException se) {
				System.out.println("oops! a problem here : in class PopulateCache");
				se.printStackTrace();
	}

}




public void populateCache()
{
	StringBuffer sb1=null;
	ResultSetMetaData meta=null;
	String[] colName;
	int numberOfColumns=0;


try {

	// we need to create a SQL query string to inster into the cachetable
	sb1 = new StringBuffer("INSERT INTO CACHETABLE(");

	// get the column names of the local table and store them in a String array
	meta = result.getMetaData();
	numberOfColumns = meta.getColumnCount();
	colName = new String[numberOfColumns + 1];
	for(int i=1;i<=numberOfColumns;i++)
		{
			colName[i] = meta.getColumnName(i);
		}

	// we create a query of the form  "INSERT INTO CACHETABLE(NAME, QID, TID) VALUES(?, ?, ?)
	// we then create a prepared statement and then insert the missing values from the ResultSet

	//  NOTE: IF the resultSet contains TID, it will lead to duplicate column names, and hence error.

	sb1.append(colName[1]);
	for(int i=2;i<=numberOfColumns;i++)
		{
			sb1.append(", ").append(colName[i]);
		}
	sb1.append(", TID"); // UNIQUE TUPLE IDENTIFIER
	sb1.append(") VALUES(?");
	for(int i=2;i<=numberOfColumns;i++)
		{
			sb1.append(", ?");
		}
	sb1.append(", TID.nextval");  // next value in the sequence
	sb1.append(")");
	String sb = sb1.toString();

	// Create a Statement
	pstmt = conn.prepareStatement(sb);

/**
 *  we need to extract individual rows from the ResultSet
 *  Also each cell of the row has to be matched to the attribute in the local table
 *  hence we create a prepared statement, and iterate over the rows present in the ResultSet
 */

 // Iterate through the ResultSet
	while (result.next ())
	{
		for(int i=1;i<=numberOfColumns;i++)
			{
				pstmt.setObject(i,result.getObject(i));
				//"INSERT INTO CACHETABLE(NAME, QID, TID,......) VALUES(?, ?, ?,.....)
				// fill in the missing values
			}
	      pstmt.addBatch();
	      pstmt.executeBatch();
	      TIDCount++;  // count the number of rows : to be used in populateAssociation
	}


	 } catch (SQLException se) {
		System.out.println("oops! a problem here : in class PopulateCache:populateCache");
		se.printStackTrace();
	 }


}


public void populateQuery()
{

	try {

	  pstmt = conn.prepareStatement("INSERT INTO QUERYTABLE(QID,SQLSTMT,ZQUERY,COUNT) values (QID.NEXTVAL, ?, ?, ?)");

	  pstmt.setString(1, sqlQuery);
      pstmt.setString(2, "zquery1");  // currently zquey is a string in oracle table definition
      pstmt.setInt(3, 1); // in reality COUNT should be "?" to be obtained dynamically.....

      pstmt.addBatch();
      pstmt.executeBatch();

      TIDStart++;  // due to the twice usage of QID.nextval
	  QIDStart++;

		} catch (SQLException se) {
			System.out.println("oops! a problem here in prepareStatement: in class PopulateCache:populateQuery");
			se.printStackTrace();
		}

}


public void populateAssociation()
{
	try {

		while(TIDCount>0)
			{
			stmt.executeUpdate ("INSERT INTO ASSOCIATION(QID,TID) VALUES(" + QIDStart + "," + TIDStart + ")");
			TIDStart++;
			TIDCount--;
			}

		} catch (SQLException se) {
				System.out.println("oops! a problem here in Statement: in class PopulateCache:populateAssociation");
				se.printStackTrace();
		}

}





}