XapiStatementSQLReader.java

/**
 * ClarescoExperienceAPI
 * Copyright 
 *
 * This code is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License version 2 only, as
 * published by the Free Software Foundation.
 *
 * Please contact Claresco, www.claresco.com, if you have any questions.
 **/

package com.claresco.tinman.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.TimeZone;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.swing.text.html.HTMLDocument.HTMLReader.IsindexAction;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormatter;
import org.joda.time.format.ISODateTimeFormat;

import com.claresco.tinman.json.JsonUtility;
import com.claresco.tinman.lrs.XapiAgent;
import com.claresco.tinman.lrs.XapiContext;
import com.claresco.tinman.lrs.XapiGroup;
import com.claresco.tinman.lrs.XapiInverseFunctionalIdentifier;
import com.claresco.tinman.lrs.XapiObject;
import com.claresco.tinman.lrs.XapiResult;
import com.claresco.tinman.lrs.XapiStatement;
import com.claresco.tinman.lrs.XapiVerb;
import com.claresco.tinman.lrs.XapiActor;
import com.claresco.tinman.servlet.XapiBadParamException;
import com.google.gson.Gson;

/**
 * XapiStatementSqlReader.java
 *
 * Read from database
 *
 *
 *
 * @author rheza
 * on Feb 26, 2014
 * 
 */

class XapiStatementSQLReader extends SQLReader {

	protected PreparedStatement myUUIDRetrievalStatement;
	protected PreparedStatement myIDRetrievalStatement;
	protected PreparedStatement myRetrievalByActorStatement;
	protected PreparedStatement myRetrievalByActorAsObjectStatement;
	protected PreparedStatement myRetrievalByVerbStatement;
	protected PreparedStatement myVoidedRetrievalStatement;
	
	private String myTableName = "statement";
	
	private XapiActorSQLReader myActorReader;
	private XapiVerbSQLReader myVerbReader;
	private XapiObjectSQLReader myObjectReader;
	private XapiContextSQLReader myContextReader;
	private XapiResultSQLReader myResultReader;
	
	 /**
	 * Description:
	 *	Constructor
	 *
	 * Params:
	 *	
	 */
	public XapiStatementSQLReader(Connection conn, XapiActorSQLReader theActorReader, 
			XapiVerbSQLReader theVerbReader, XapiObjectSQLReader theObjectReader, XapiContextSQLReader 
			theContextReader, XapiResultSQLReader theResultReader) throws SQLException{
		this.myConn = conn;
		this.myUUIDRetrievalStatement = SQLUtility.createRetrievalStatement(super.myConn, 
				myTableName, new String[]{"statementuuid", "isvoided"});
		this.myIDRetrievalStatement = SQLUtility.createRetrievalStatement(super.myConn, 
				myTableName, "statementid");
		this.myRetrievalByActorStatement = SQLUtility.createRetrievalStatement(myConn, 
				myTableName, "actorid");
		this.myRetrievalByVerbStatement = SQLUtility.createRetrievalStatement(myConn, 
				myTableName, "verbid");
		this.myRetrievalByActorAsObjectStatement = createActorAsObjectStatement();
		this.myVoidedRetrievalStatement = SQLUtility.createRetrievalStatement(myConn, myTableName, 
				new String[]{"statementid", "isvoided"});
		
		this.myActorReader = theActorReader;
		this.myVerbReader = theVerbReader;
		this.myObjectReader = theObjectReader;
		this.myContextReader = theContextReader;
		this.myResultReader = theResultReader;
	}

	
	
	/**
	 * 
	 * Description:
	 *	Return a statement object from the database based on its uuid
	 *
	 * Params:
	 *	theUUID
	 */
	protected HashMap<Integer, XapiStatement> retrieveByUUID(String theUUID) throws SQLException{
		this.myUUIDRetrievalStatement.setString(1, theUUID);
		this.myUUIDRetrievalStatement.setInt(2, 0);
		myResult = this.myUUIDRetrievalStatement.executeQuery();
		
		HashMap<Integer, XapiStatement> statementArray = getStatementFromResult();
		
		assert statementArray.size() <= 1;
		
		return statementArray;
	}

	
	
	protected HashMap<Integer, XapiStatement> retrieveByUUID(UUID theUUID) throws SQLException{
		return this.retrieveByUUID(theUUID.toString());
	}

	
	
	/**
	 * 
	 * Description:
	 *	Retrieve a statement object from the database based on its uuid
	 *
	 * Params:
	 *
	 */
	protected HashMap<Integer, XapiStatement> retrieveByID(int theID) throws SQLException
			, XapiDataIntegrityException{
		this.myIDRetrievalStatement.setInt(1, theID);
		
		myResult = this.myIDRetrievalStatement.executeQuery();
		
		HashMap<Integer, XapiStatement> statementArray = getStatementFromResult();
		
		if(statementArray.size() > 1){
			throw new XapiDuplicateStatementIDException("something went wrong");
		}
		
		return statementArray;
	}
	
	
	
	protected boolean doesStatementExists(String theUUID) throws SQLException{
		this.myUUIDRetrievalStatement.setString(1, theUUID);
		this.myUUIDRetrievalStatement.setInt(2, 0);
		myResult = this.myUUIDRetrievalStatement.executeQuery();
		
		if (!myResult.isBeforeFirst() ) {    
			 return false; 
		} 
		return true;
	}
	
	
	
	/**
	 * 
	 * Definition:
	 *	Retrieve Statement based on its UUID
	 *
	 * Params:
	 *
	 *
	 */
	protected int findIDByUUID(String theUUID) throws SQLException{
		this.myUUIDRetrievalStatement.setString(1, theUUID);
		this.myUUIDRetrievalStatement.setInt(2, 0);
		myResult = this.myUUIDRetrievalStatement.executeQuery();
		
		if(!myResult.isBeforeFirst()){
			return -1;
		}
		
		myResult.next();
		return myResult.getInt(1);
	}
	
	
	
	/**
	 * 
	 * Definition:
	 *	Retrieve statements based on its actor id
	 *
	 * Params:
	 *
	 *
	 */
	protected HashMap<Integer, XapiStatement> retrieveStatementByActor(int actorID) throws SQLException{
		// Find statements where actor is a subject
		myRetrievalByActorStatement.setInt(1, actorID);
		myResult = myRetrievalByActorStatement.executeQuery();
		HashMap<Integer, XapiStatement> statementMap = getStatementFromResult();
		
		// Find statements where actor is an object
		myRetrievalByActorAsObjectStatement.setInt(1, actorID);
		myResult = myRetrievalByActorAsObjectStatement.executeQuery();
		statementMap.putAll(getStatementFromResult());
		
		// Find groups which the actor belongs to and then find statement which those groups are in
		ArrayList<Integer> groupIDArray = myActorReader.retrieveGroupsOfAgent(actorID);
		for(Integer i : groupIDArray){
			statementMap.putAll(retrieveStatementByActor(i));
		}
		
		return statementMap;
	}
	
	
	
	/**
	 * 
	 * Definition:
	 *	Return statements whose verbs match the IRI
	 *
	 * Params:
	 *
	 *
	 */
	protected HashMap<Integer, XapiStatement> retrieveStatementByVerb(String theVerbIRI) throws SQLException{
		int theVerbID = myVerbReader.retrieveIDByValue(theVerbIRI);
		
		myRetrievalByVerbStatement.setInt(1, theVerbID);
		myResult = myRetrievalByVerbStatement.executeQuery();
		HashMap<Integer, XapiStatement> statementMap = getStatementFromResult();
		
		return statementMap;
	}
	
	
	
	private HashMap<Integer, XapiStatement> retrieveVoidedStatementByID(String theUUID) throws SQLException{
		myVoidedRetrievalStatement.setString(1, theUUID);
		myVoidedRetrievalStatement.setInt(2, 1);
		
		myResult = myVoidedRetrievalStatement.executeQuery();
		
		HashMap<Integer, XapiStatement> myVoidedStatementMap = getStatementFromResult();
		
		return myVoidedStatementMap;
	}
	
	
	
	/**
	 * 
	 * Definition:
	 *	Helper method that will create a hashmap from result
	 *
	 * Params:
	 *
	 *
	 */
	private HashMap<Integer, XapiStatement> getStatementFromResult() throws SQLException{
		HashMap<Integer, XapiStatement> statementArray = new HashMap<Integer, XapiStatement>();
		
		while(myResult.next()){
			UUID theId = UUID.fromString(myResult.getString("statementuuid"));
			XapiActor theActor = myActorReader.retrieveByID(myResult.getInt("actorid"));
			XapiVerb theVerb = myVerbReader.retrieveByID(myResult.getInt("verbid"));
			XapiObject theObject = myObjectReader.retrieveByID(myResult.getInt("objectid"));
			XapiResult theResult = null;
			int theResultID = myResult.getInt("resultid");
			if(!myResult.wasNull()){
				theResult = myResultReader.retrieveByID(theResultID);
			}
			
			XapiContext theContext = null;
			int theContextID = myResult.getInt("contextid");
			if(!myResult.wasNull()){
				theContext = myContextReader.retrieveByID(theContextID);
			}
			
			String theTSString = null;
			
			Timestamp theTS = myResult.getTimestamp("sttime");
			if(!myResult.wasNull()){
				DateTime theTimestamp = SQLUtility.getDatetime(theTS);
				theTSString = theTimestamp.withZoneRetainFields(DateTimeZone.UTC).toString();
			}
			
			// Voided statement should not be returned
			int isVoided = myResult.getInt("isVoided");
			if(isVoided == 0){
				statementArray.put(myResult.getInt("statementid") ,new XapiStatement(theId, theActor,
						theVerb, theObject, theResult, theContext, theTSString));
			}
		}
		
		return statementArray;
	}
	
	
	
	protected HashMap<Integer, XapiStatement> handleConjuctionQuery(HashMap<String, String> myParamMap) throws SQLException,
			XapiDataIntegrityException, XapiSQLOperationProblemException{
		if(myParamMap.size() == 0){
			throw new XapiConflictingParamException("There is no parameter");
		}
		
		if(myParamMap.containsKey("statementId") && myParamMap.size() > 1){
			throw new XapiConflictingParamException("Other parameters are not suitable with statementId");
		}else if(myParamMap.containsKey("voidedStatementId") && myParamMap.size() > 1){
			throw new XapiConflictingParamException("Other parameters are not suitable with voidedStatementId");
		}else if(myParamMap.containsKey("statementId") && myParamMap.containsKey("voidedStatementId")){
			throw new XapiConflictingParamException("Can't have both statementId and voidedStatementId");
		}else if(myParamMap.containsKey("statementId") && myParamMap.size() == 1){
			return retrieveByUUID(myParamMap.get("statementId"));
		}else if(myParamMap.containsKey("voidedStatementId") && myParamMap.size() == 1){
			return retrieveVoidedStatementByID(myParamMap.get("voidedStatementId"));
		}else{
			ArrayList<Integer> myStatementIDs = retrieveStatementsByParameter(myParamMap);
			
			HashMap<Integer, XapiStatement> myResultMap = new HashMap<Integer, XapiStatement>();
			
			if(myStatementIDs == null){
				return null;
			}
			
			for(Integer i : myStatementIDs){
				myResultMap.put(i, retrieveByID(i.intValue()).get(i));
			}
			
			return myResultMap;
		}
	}
	
	
	
	private ArrayList<Integer> retrieveStatementsByParameter(HashMap<String, String> paramMaps)
			throws SQLException, XapiDataIntegrityException, XapiSQLOperationProblemException{
		String topHalf = "select st.statementid from statement st";
		String bottomHalf = "where";

		// This is the conjuction query
		int i = 1;
		HashMap<String, Integer> positionMap = new HashMap<String, Integer>();
		for(String s : paramMaps.keySet()){
			if(s.equals("statementId")){
				bottomHalf += "st.statementid = ?";
				positionMap.put(s, new Integer(i));
				i++;
			}else if(s.equals("agent")){
				topHalf += createTopHalfString("actor", "a", "actorid");
				bottomHalf += createBottomHalfString("a", "actorid");
				positionMap.put(s, new Integer(i));
				i++;
			}else if(s.equals("verb")){
				topHalf += createTopHalfString("verb", "v", "verbid");
				bottomHalf += createBottomHalfString("v", "verbiri");
				positionMap.put(s, new Integer(i));
				i++;
			}else if(s.equals("activity")){
				topHalf += createTopHalfString("object", "obj", "objectid");
				topHalf += createTopHalfString("activity", "actv", "activityid");
				bottomHalf += createBottomHalfString("actv", "actviri");
				positionMap.put(s, new Integer(i));
				i++;
			}else if(s.equals("since")){
				bottomHalf += createBottomHalfString("st", "ststored", ">");
				positionMap.put(s, new Integer(i));
				i++;
			}else if(s.equals("until")){
				bottomHalf += createBottomHalfString("st", "ststored", "<");
				positionMap.put(s, new Integer(i));
				i++;
			}
		}
		
		bottomHalf += createBottomHalfString("st", "isvoided");
		positionMap.put("isvoided", i);
		
		bottomHalf = bottomHalf.replace("where and", "where");
		
		// Always order by stored time in desceding onder
		String ending = " order by ststored desc;";

		String fullString = topHalf + " " + bottomHalf + ending;

		return executeConjQueryStatement(paramMaps, positionMap, fullString);
	}
	
	
	
	private String createTopHalfString(String tableName, String tableNickname, String fieldName){
		String theString = " left join %s %s using (%s)";
		
		return String.format(theString, tableName, tableNickname, fieldName);
	}
	
	
	
	private String createBottomHalfString(String tableNickname, String fieldName){
		String theS = " and %s.%s = ?";
		
		return String.format(theS, tableNickname, fieldName);
	}
	
	
	
	private String createBottomHalfString(String tableNickname, String fieldName, String operator){
		String theS = " and %s.%s %s ?";
		
		return String.format(theS, tableNickname, fieldName, operator);
	}
	
	
	
	private ArrayList<Integer> executeConjQueryStatement(HashMap<String, String> paramMaps, 
			HashMap<String, Integer> locationMaps, String conjQueryStatement) throws SQLException,
			XapiSQLOperationProblemException{
		PreparedStatement myConjQueryStatement = myConn.prepareStatement(conjQueryStatement);
		DateTimeFormatter theFormatter = ISODateTimeFormat.dateTimeParser();
		for(String paramName : paramMaps.keySet()){
			String paramValue = paramMaps.get(paramName);
			int locationIndex = locationMaps.get(paramName).intValue();
			if(paramName.equals("statementId")){
				myConjQueryStatement.setString(locationIndex, paramValue);
			}else if(paramName.equals("agent")){
				
				myConjQueryStatement.setInt(locationIndex, Integer.parseInt(paramValue));
			}else if(paramName.equals("verb")){
				myConjQueryStatement.setString(locationIndex, paramValue);
			}else if(paramName.equals("activity")){
				myConjQueryStatement.setString(locationIndex, paramValue);
			}else if(paramName.equals("since")){
				try{
					DateTime myTimeStamp = theFormatter.parseDateTime(paramValue);
					Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
					myConjQueryStatement.setTimestamp(locationIndex, SQLUtility.getTimestamp(myTimeStamp), cal);
				}catch(IllegalArgumentException exc){
					throw new XapiSQLOperationProblemException("Having trouble reading the timestamp");
				}
			}else if(paramName.equals("until")){
				try{
					DateTime myTimeStamp = theFormatter.parseDateTime(paramValue);
					Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
					myConjQueryStatement.setTimestamp(locationIndex, SQLUtility.getTimestamp(myTimeStamp), cal);
				}catch(IllegalArgumentException exc){
					throw new XapiSQLOperationProblemException("Having trouble parsing the timestamp");
				}
			}
		}
		
		myConjQueryStatement.setInt(locationMaps.get("isvoided").intValue(), 0);
		
		myResult = myConjQueryStatement.executeQuery();
		
		ArrayList<Integer> arrayIDs = new ArrayList<Integer>();
		
		while(myResult.next()){
			arrayIDs.add(new Integer(myResult.getInt("statementid")));
		}
		
		if(arrayIDs.isEmpty()){
			return null;
		}
		
		return arrayIDs;
	}
	

	
	/**
	 * 
	 * Definition:
	 *	Helper method to create statement to retrieve actor as object
	 *
	 * Params:
	 *
	 *
	 */
	private PreparedStatement createActorAsObjectStatement() throws SQLException{
		String theString = "select * from statement where objectid in (select objectid from object where " +
				"actorid = ?)";
		
		return super.myConn.prepareStatement(theString);
	}
		
		
	
	/**
	 * Description:
	 * 	Close everything makes everything safe
	 */
	protected void close() throws SQLException{
		super.close();
		SQLUtility.closeStatement(this.myIDRetrievalStatement);
		SQLUtility.closeStatement(this.myUUIDRetrievalStatement);
		SQLUtility.closeStatement(myRetrievalByActorAsObjectStatement);
		SQLUtility.closeStatement(myRetrievalByActorStatement);
		SQLUtility.closeStatement(myRetrievalByVerbStatement);
	}
	
	
	
	public static void main(String[] args) {
		try {
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}