XapiActorSQLReader.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.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import com.claresco.tinman.lrs.XapiAccount;
import com.claresco.tinman.lrs.XapiActor;
import com.claresco.tinman.lrs.XapiAgent;
import com.claresco.tinman.lrs.XapiGroup;
import com.claresco.tinman.lrs.XapiInverseFunctionalIdentifier;
import com.hp.hpl.jena.sdb.core.sqlnode.SqlUnion;

/**
 * XapiActorSQLReader.java
 *
 *
 *
 *
 *
 * @author rheza
 * on Feb 27, 2014
 * 
 */

class XapiActorSQLReader extends SQLReader {
	
	private String myTableName = "actor";
	private String myMemberTableName = "groupactors";
	
	private PreparedStatement myRetrievalByIDStatement;
	private PreparedStatement myMemberRetrievalStatement;
	private PreparedStatement myRetrievalByAccountStatement;
	private PreparedStatement myRetrievalByMboxStatement;
	private PreparedStatement myRetrievalBySha1sumStatement;
	private PreparedStatement myRetrievalByOpenIDStatement;
	private PreparedStatement myRetrievalOfGroupByAgentStatement;
	
	private XapiAccountSQLReader myAccountReader;
	
	private String myIDField = "actorid";
	
	/**
	 * 
	 * Description:
	 *	Constructor
	 *
	 * Params:
	 *
	 */
	public XapiActorSQLReader(Connection conn, XapiAccountSQLReader theAccountReader) throws SQLException{
		this.myConn = conn;
		this.myRetrievalByIDStatement = SQLUtility.createRetrievalStatement(super.myConn, this.myTableName, "actorid");
		this.myMemberRetrievalStatement = SQLUtility.createRetrievalStatement(super.myConn, this.myMemberTableName, "groupid");
		this.myRetrievalByAccountStatement = SQLUtility.createRetrievalStatement(super.myConn, myTableName, new String[]{"accountid"});
		this.myRetrievalByMboxStatement = SQLUtility.createRetrievalStatement(myConn, myTableName, "actmbox");
		this.myRetrievalBySha1sumStatement = SQLUtility.createRetrievalStatement(myConn, myTableName, "atcmbox_sha1");
		this.myRetrievalByOpenIDStatement = SQLUtility.createRetrievalStatement(myConn, myTableName, "openid");
		this.myRetrievalOfGroupByAgentStatement = SQLUtility.createRetrievalStatement(myConn, "groupactors", "agentid");
		this.myAccountReader = theAccountReader;
	}
	
	/**
	 * 
	 * Description:
	 *	Retrieve XapiActor based on its ID
	 *
	 * Params:
	 *
	 */
	protected XapiActor retrieveByID(int theID) throws SQLException{
		this.myRetrievalByIDStatement.setInt(1, theID);
		
		myResult = this.myRetrievalByIDStatement.executeQuery();
				
		// How to make sure there is no duplicate
		
		handleNoData();
		
		int theGroupID;
		String theType;
		String theName;
		String theMBox;
		String theMBoxSHA1;
		String theOpenID;
		int theAccountID;
		XapiAccount theAccount = null;
		
		// Open result set
		myResult.next();
		
		theGroupID = myResult.getInt(myIDField);
		theType = myResult.getString("actortypecode");
		theName = myResult.getString("actname");
		theMBox = myResult.getString("actmbox");
		theMBoxSHA1 = myResult.getString("atcmbox_sha1");
		theOpenID = myResult.getString("openid");
		theAccountID = myResult.getInt("accountid");		
		ArrayList<XapiAgent> theMember = new ArrayList<XapiAgent>();
		
		// Retrieve account if account exists, the accountID is -1 if its value in the database is null
		if(theAccountID > 0){
			theAccount = myAccountReader.retrieveByID(theAccountID);
		}
		
		XapiInverseFunctionalIdentifier theIdentifier = new XapiInverseFunctionalIdentifier(theMBox, theMBoxSHA1, theOpenID, theAccount);
		
		// If actor is an agent, then this function is complete
		if(theType.equals("AGT")){
			return new XapiAgent(theName, theIdentifier);
		}
		
		// Unless, it is a group, then we need to retrieve the member
		this.myMemberRetrievalStatement.setInt(1, theGroupID);
		ResultSet theRSet = this.myMemberRetrievalStatement.executeQuery();
		
		while(theRSet.next()){
			// Recursion, for every member, we will return an XapiActor by calling this function
			theMember.add((XapiAgent) this.retrieveByID(theRSet.getInt("agentid")));
		}
		
		theRSet.close();
		
		return new XapiGroup(theName, theMember, theIdentifier);
	}
	
	
	
	protected int retrieveActorID(XapiActor theActor) throws SQLException{
		XapiInverseFunctionalIdentifier theActorID = theActor.getInverseFuncId();
		
		int theDatabaseID = -1;
		
		// Check if the actor already exists in the database
		switch(theActorID.getIdentifierIndex()){
			case 1: theDatabaseID = retrieveByMbox(theActorID.getMbox().toString());
					break;
			case 2: theDatabaseID = retrieveBySha1Sum(theActorID.getMboxSha1Sum());
					break;
			case 3: theDatabaseID = retrieveByOpenID(theActorID.getOpenId().toString());
					break;
			case 4: theDatabaseID = retrieveIDByAccount(theActorID.getAccount());
					break;
		}
		
		return theDatabaseID;
	}
	
	/**
	 * 
	 * Description:
	 *	Retrieve the ID of the actor based on its account
	 *
	 * Params:
	 *	theHomepage :
	 *	theName :	
	 *	
	 */
	protected int retrieveIDByAccount(String theHomepage, String theName) throws SQLException{
		this.myRetrievalByAccountStatement.setInt(1, this.myAccountReader.retrieveIDByValue(theHomepage, theName));
		
		myResult = this.myRetrievalByAccountStatement.executeQuery();
		
		if(!myResult.isBeforeFirst()){
			return -1;
		}
		
		myResult.next();
		return myResult.getInt(myIDField);
	}
	
	
	
	protected int retrieveIDByAccount(XapiAccount theAccount) throws SQLException{
		if(theAccount == null){
			return -1;
		}
		return retrieveIDByAccount(theAccount.getHomePage().toString(), theAccount.getName());
	}
	
	
	
	protected boolean doesActorExists(int theID) throws SQLException{
		myResult = SQLUtility.executeRetrievalByIDQuery(myRetrievalByIDStatement, theID);
		
		if(SQLUtility.isResultEmpty(myResult)){
			return false;
		}
		return true;
	}
	
	
	
	protected int retrieveByInverseFuncID(XapiInverseFunctionalIdentifier theInvFuncID) throws SQLException{
		int theDatabaseID = -1;
		
		// Check if the actor already exists in the database
		switch(theInvFuncID.getIdentifierIndex()){
			case 1: theDatabaseID = this.retrieveByMbox(theInvFuncID.getMbox().toString());
					break;
			case 2: theDatabaseID = this.retrieveBySha1Sum(theInvFuncID.getMboxSha1Sum());
					break;
			case 3: theDatabaseID = this.retrieveByOpenID(theInvFuncID.getOpenId().toString());
					break;
			case 4: theDatabaseID = this.retrieveIDByAccount(theInvFuncID.getAccount());
					break;
		}
		
		return theDatabaseID;
	}
	
	
	
	protected int retrieveByMbox(String theMbox) throws SQLException{
		myRetrievalByMboxStatement.setString(1, theMbox);
		myResult = myRetrievalByMboxStatement.executeQuery();
		
		if(SQLUtility.isResultEmpty(myResult)){
			return -1;
		}
		
		myResult.next();
		return myResult.getInt(myIDField);
	}
	
	
	
	protected int retrieveBySha1Sum(String theSha1Sum) throws SQLException{
		myRetrievalBySha1sumStatement.setString(1, theSha1Sum);
		myResult = myRetrievalBySha1sumStatement.executeQuery();
		
		if(SQLUtility.isResultEmpty(myResult)){
			return -1;
		}
		
		myResult.next();
		return myResult.getInt(myIDField);
	}
	
	
	
	protected int retrieveByOpenID(String theOpenID) throws SQLException{
		myRetrievalByOpenIDStatement.setString(1, theOpenID);
		myResult = myRetrievalByOpenIDStatement.executeQuery();
		
		if(SQLUtility.isResultEmpty(myResult)){
			return -1;
		}
		
		myResult.next();
		return myResult.getInt(myIDField);
	}
	
	
	
	protected ArrayList<Integer> retrieveGroupsOfAgent(int theAgentID) throws SQLException{
		ArrayList<Integer> groupIDArray = new ArrayList<Integer>();
		
		myRetrievalOfGroupByAgentStatement.setInt(1, theAgentID);
		myResult = myRetrievalOfGroupByAgentStatement.executeQuery();
		
		handleNoData();
		
		while(myResult.next()){
			groupIDArray.add(myResult.getInt(1));
		}
		
		return groupIDArray;
	}
	
	
	
	protected boolean isActorAgent(int theID) throws SQLException, XapiDataIntegrityException{
		myResult = SQLUtility.executeRetrievalByIDQuery(myRetrievalByIDStatement, theID);
		
		if(SQLUtility.isResultEmpty(myResult)){
			throw new XapiDataIntegrityException("Actor with that ID does not exists");
		}
		
		myResult.next();
		String theType = myResult.getString("actortypecode");
		if (theType.equals("AGT")) {
			return true;
		}
		return false;
	}
	
	
	
	protected boolean isActorGroup(int theID) throws SQLException, XapiDataIntegrityException{
		return !isActorAgent(theID);
	}
	
	
	
	/**
	 * Description:
	 * 	Close everything
	 * 
	 */
	protected void close() throws SQLException{
		super.close();
		SQLUtility.closeStatement(myMemberRetrievalStatement);
		SQLUtility.closeStatement(myRetrievalByAccountStatement);
		SQLUtility.closeStatement(myRetrievalByAccountStatement);
		SQLUtility.closeStatement(myRetrievalByMboxStatement);
		SQLUtility.closeStatement(myRetrievalByOpenIDStatement);
		SQLUtility.closeStatement(myRetrievalBySha1sumStatement);
		SQLUtility.closeStatement(myRetrievalOfGroupByAgentStatement);
	}
		
	
	
	public static void main(String[] args) {
		try {
			Connection conn = SQLUtility.establishDefaultConnection();
			XapiAccountSQLReader theAccountReader = new XapiAccountSQLReader(conn);
			XapiActorSQLReader theReader = new XapiActorSQLReader(conn, theAccountReader);
			
			/**
			XapiGroup g = (XapiGroup) theReader.retrieveByID(10099);
			System.out.println(g);
			System.out.println(theReader.retrieveIDByAccount("http://example.com/homePage", "GroupAccount"));
			
			if(theReader.doesActorExists(11111)){
				System.out.println(true);
			}else{
				System.out.println(false);
			}
			**/
			
			System.out.println(theReader.retrieveByMbox("mailto:xapi@adlnet.gov"));
			System.out.println(theReader.retrieveByOpenID("aaron.openid.example.org"));
			
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
}