/* The class contains a 'execQuery' method that connects to the underlying database
 * and executes a query which has been given to the method as a string variable.
 * Having executed the query, the column information and each row are extracted 
 * and stored in a Table instance (defined by the 'Table' class).
 *
 * Assumptions:
 * - the query to be executed must return the temporal/time compontents 
 *   of each row that has been extracted.
 * - the start and the end time columns in the table that resides in the 
 *   underlying database are not given any particular column positions.
 */
import java.io.*;
import java.sql.*;

class  Query
{
  public static String driver="org.postgresql.Driver";
  // public static String url="jdbc:postgresql://db.doc.ic.ac.uk/jp99db";
  // public static String username="jp99";
  // public static String password="cantagesro";
  public static String url="jdbc:postgresql://localhost/football";
  public static String username="pjm";
  public static String password="Again11";

	/* variables to hold the start and end time column position to aid the 
	 * extraction of the time components in order to be placed in a Row instance. 
	 */ 
	public static int st_pos=99;
	public static int et_pos=99;

	public static Table execQuery(String qry)
	{
		Table table = new Table();
		try 
		{
			/* establish connection with the database */
			Class.forName(driver);
			Connection con=
			  DriverManager.getConnection(url,username,password);

			/* execute the query */
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(qry);
			ResultSetMetaData meta = rs.getMetaData();	
			
			/* set the column information extracted from the database */
			getColInfo(table, meta);
			/* add each row extracted from the database */
			getRowVals(table, rs);

			/* destroy the connection with the database */
			con.close();

		}

		catch (java.lang.ClassNotFoundException e1)
		{
			System.out.println("Could not load driver " + e1);
		}

		catch (java.sql.SQLException e2) 
		{
			System.out.println("SQL error " + e2.getMessage());
		}

	 return table;

	}


	/* Extracts the column information with regards to the query that has been executed */
	public static void getColInfo(Table table, ResultSetMetaData meta)
	{
		/* the number of columns returned by result of query */
		int colNumber=(getColNumber(meta)-2); // exclude the time component columns.

		String[] colNames = new String[colNumber];
		int[] colTypes = new int[colNumber];

		/* get all the column names and types */
		colNames=getColNames(meta, colNumber+2);
		colTypes=getColTypes(meta, colNumber+2);

		/* put the column information into the table */
		table.putCol(colNumber, colNames, colTypes);
	}

	/* Extracts each row and its corresponding start and end time values from the executed query */
	public static void getRowVals(Table table, ResultSet rs)
	{
		int colNumber=table.getNoCols();
		Object[] rowVals = new Object[colNumber];
		int st_val=99;
		int et_val=99;
		
		try
		{
			while(rs.next())
			{
				/* get all the objects in a row excluding the time components */
				rowVals=getRowValues(rs, colNumber+2);
				/* get the start and end time values */
				st_val=getRowTime(rs, st_pos);
				et_val=getRowTime(rs, et_pos);
				/* place the row in the table data structure */
				table.putRow(rowVals, st_val, et_val);
			}
		}

		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}
	}
	

	/************************************************************************/
	/* Methods to handling COLUMN information extraction					*/
	/************************************************************************/

	/* Extracts and returns the number of columns in the result of the executing query */
	public static int getColNumber(ResultSetMetaData meta)
	{
		int number = 0;

		try
		{
			number=meta.getColumnCount();
		}

		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}

		return number;
	}


	/* Returns the column titles in the result of executing query 
	 * When the start and end time columns are found, set the global 
	 * variables to hold the column position of the time componenets
	 */
	public static String[] getColNames(ResultSetMetaData meta, int count)
	{
		String s = new String();
		String[] colNames = new String[count-2];
		
		try
		{
			/* copy all column names execpt the time component columns
			 * locate the column position of the time components 
			 */
			for(int i=0; i<count; i++)
			{
				s=meta.getColumnLabel(i+1);

				if(s.compareTo("st")==0)
				{
					st_pos=i;
				}
				else if(s.compareTo("et")==0)
				{
					et_pos=i;
				}
				else
				{
					colNames[i] = s;
				}
			}
			
		}
		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}

		return colNames;
	}

	/* Extract all the column types asscoiated with each column excluding the time component columns */
	public static int[] getColTypes(ResultSetMetaData meta, int count)
	{
		int[] colTypes = new int[count-2];
		
		try
		{
			for(int i=0; i<count; i++) /* go through each column */
			{
				/* ignore the start and end time columns */ 
				if (i!=st_pos && i!=et_pos)
				{
					colTypes[i]=meta.getColumnType(i+1);
				}
				
			}
			
		}	
		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}

		return colTypes;
	}


	/************************************************************************/
	/* Methods to handling ROW information extraction						*/
	/************************************************************************/

	/* Extract all the objects excluding the time values associated with a row */
	public static Object[] getRowValues(ResultSet rs, int count)
	{
		Object[] rowVals = new Object[count-2]; 
		try
		{
			for(int i=0; i<count; i++) /* go through each column of the row */
			{
				/* ignore the start and end time row values */ 
				if (i!=st_pos && i!=et_pos)
				{
					rowVals[i]=rs.getObject(i+1);

					/* replace null values from the database with the String value 'null' */
					try
					{
						rowVals[i].equals("null");
					}
					catch (NullPointerException e)
					{
						rowVals[i]=(Object) "null";
					}
				}
			}
			
		}	
		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}

		return rowVals;
	}

	/* Extract the specific (start or end) time value based on its column position */
	public static int getRowTime(ResultSet rs, int pos)
	{
		int time=99;

		try
		{
			time=rs.getInt(pos+1);
		}
		
		catch (java.sql.SQLException e)
		{
			System.out.println("SQL error " + e.getMessage());
		}

		return time;
	}

}
