package com.oracle.determinations.interview.engine.userplugins;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;
import com.oracle.determinations.engine.Attribute;
import com.oracle.determinations.engine.Entity;
import com.oracle.determinations.engine.EntityInstance;
import com.oracle.determinations.engine.Relationship;
import com.oracle.determinations.engine.Rulebase;
import com.oracle.determinations.engine.Session;
import com.oracle.determinations.engine.Uncertain;
import com.oracle.determinations.interview.engine.InterviewRulebase;
import com.oracle.determinations.interview.engine.InterviewSession;
import com.oracle.determinations.interview.engine.SecurityToken;
import com.oracle.determinations.interview.engine.data.model.InstanceStatus;
import com.oracle.determinations.interview.engine.data.model.InterviewEntityInstance;
import com.oracle.determinations.interview.engine.data.model.InterviewUserData;
import com.oracle.determinations.interview.engine.plugins.InterviewSessionPlugin;
import com.oracle.determinations.interview.engine.plugins.InterviewSessionRegisterArgs;
import com.oracle.determinations.interview.engine.plugins.data.DataAdaptorPlugin;
/**
* Tutorials and Examples - Data Adaptor - Sample Code (DerbyDataAdaptor)
*/
public class DerbyDataAdaptor implements DataAdaptorPlugin {
private String driver = "org.apache.derby.jdbc.ClientDriver";
private String dbName = "derbydataadaptor";
private String connectionURL = "jdbc:derby://localhost:1527/" + dbName
+ ";";
private Connection connection = null;
private Statement statement = null;
/** ********** */
/* Constants */
/** ********** */
// uncertain and unknown
// for numbers we are assuming, we have positive numbers only
private final static int INT_UNKNOWN = -2;
private final static int INT_UNCERTAIN = -1;
private final static String STRING_UNKNOWN = "unknown";
private final static String STRING_UNCERTAIN = "uncertain";
// types
private enum AdaptorDataType {
TYPE_STRING, TYPE_INT, TYPE_BOOLEAN
}
/**
* Constructor
*/
public DerbyDataAdaptor() {
try {
Class.forName(driver);
System.out.println(driver + " loaded.");
} catch (ClassNotFoundException e) {
System.err.println("Class not found - " + e.getMessage());
}
}
/**
* Connects to the database and creates a statement
*
* @throws SQLException
*/
private void connectDBObjects() throws SQLException {
connection = DriverManager.getConnection(connectionURL);
System.out.println("Connected to database " + dbName);
statement = connection.createStatement();
}
/**
* Closes the statement and terminates the connection
*
* @throws SQLException
*/
private void closeDBObjects() throws SQLException {
statement.close();
connection.close();
System.out.println("Closed connection");
}
/**
* Authenticates a user. For the example, we always returns true.
*
* @param token
* the security token
* @return true if the user is authenticated, otherwise, false
*/
private boolean authenticateUser(SecurityToken token) {
// implement user authentication here
return true;
}
/**
* Generates a unique case ID
*
* @return the unique case ID
*/
private String generateCaseID() {
return UUID.randomUUID().toString();
}
/**
* Helper function for transforming the attribute value to a string for saving
* it to the database
*
* @param attribute
* the attribute
* @param instance
* the instance
* @param type
* the type of the attribute
* @return the writable value
*/
private String convertToTableAttrValue(Attribute attribute,
EntityInstance instance, AdaptorDataType type) {
String result = null;
if (attribute.isUnknown(instance)) {
switch (type) {
case TYPE_STRING:
result = "'" + STRING_UNKNOWN + "'";
break;
case TYPE_INT:
case TYPE_BOOLEAN:
result = new Integer(INT_UNKNOWN).toString();
break;
}
} else if (attribute.isUncertain(instance)) {
switch (type) {
case TYPE_STRING:
result = "'" + STRING_UNCERTAIN + "'";
break;
case TYPE_INT:
case TYPE_BOOLEAN:
result = new Integer(INT_UNCERTAIN).toString();
break;
}
} else {
Object value = attribute.getValue(instance);
switch (type) {
case TYPE_STRING:
result = (String) "'" + value + "'";
break;
case TYPE_INT:
result = ((Double) value).toString();
break;
case TYPE_BOOLEAN:
// 0 for false, 1 for true
result = (((Boolean) value).booleanValue() ? "1" : "0");
break;
}
}
return result;
}
/**
* Helper function for transforming an int value from a table to its
* equivalent interview attribute value
*
* @param value
* the integer value
* @param type
* the type of value, either boolean or int
* @return the attribute value
* @throws Exception
*/
private Object convertToInterviewAttrValue(int value, AdaptorDataType type)
throws Exception {
Object result = null;
switch (type) {
case TYPE_INT:
case TYPE_BOOLEAN:
if (value == INT_UNKNOWN) {
result = null;
} else if (value == INT_UNCERTAIN) {
result = Uncertain.INSTANCE;
} else {
if (type == AdaptorDataType.TYPE_INT) {
result = new Double(value);
} else {
result = ((value == 1) ? Boolean.TRUE : Boolean.FALSE);
}
}
break;
default:
throw new Exception("Interview type conversion is not supported.");
}
return result;
}
/**
* Helper function for converting a string value from a table to its
* corresponding interview attribute value
*
* @param value the int value
* @return the attribute value
*/
private Object convertToInterviewAttrValue(String value) {
Object result = null;
if (value.equals(STRING_UNKNOWN)) {
result = null;
} else if (value.equals(STRING_UNCERTAIN)) {
result = Uncertain.INSTANCE;
} else {
result = value;
}
return result;
}
/**
* Saves the session into the database. Only base attributes are saved.
*
* @param session
* the interview session
* @param caseID
* the case ID
*
* @throws Exception
*/
private void saveSession(InterviewSession session, String caseID)
throws Exception {
Session engineSession = session.getRuleSession();
Rulebase engineRulebase = engineSession.getRulebase();
EntityInstance globalInstance = engineSession.getGlobalEntityInstance();
Attribute attrSunShining = globalInstance.getEntity().getAttribute(
"sun_shining");
// used for writing down records
String row = null;
// 1. write the global record
row = "'"
+ caseID
+ "'"
+ ","
+ convertToTableAttrValue(attrSunShining, globalInstance,
AdaptorDataType.TYPE_BOOLEAN);
statement.execute("INSERT INTO WD_CASE VALUES (" + row + ")");
// 2. write child instances
System.out.println("Writing entity 'child' instances....");
Entity childEntity = engineRulebase.getEntity("child");
Attribute attrChildName = childEntity.getAttribute("child_name");
Attribute attrEatingIceCream = childEntity
.getAttribute("eating_icecream");
List<EntityInstance> children = globalInstance.getChildren(childEntity);
HashMap<EntityInstance, Integer> entityInstanceChildIDMap = new HashMap<EntityInstance, Integer>();
for (EntityInstance childInstance : children) {
row = "'"
+ caseID
+ "'"
+ ","
+ convertToTableAttrValue(attrChildName, childInstance,
AdaptorDataType.TYPE_STRING)
+ ","
+ convertToTableAttrValue(attrEatingIceCream,
childInstance, AdaptorDataType.TYPE_BOOLEAN);
statement
.execute("INSERT INTO CHILD(WD_CASE_ID,CHILD_NAME,EATING_ICECREAM) VALUES ("
+ row + ")", statement.RETURN_GENERATED_KEYS);
ResultSet generatedResult = statement.getGeneratedKeys();
int childID = -1;
if (generatedResult.next())
{
childID = generatedResult.getInt(1);
}
System.out.println("Child ID: " + childID);
entityInstanceChildIDMap.put(childInstance, new Integer(childID));
generatedResult.close();
}
// 3. write person instances and relationship
System.out.println("Writing entity 'person' instances....");
Entity personEntity = engineRulebase.getEntity("person");
Attribute attrPersonName = personEntity.getAttribute("person_name");
List<EntityInstance> people = globalInstance.getChildren(personEntity);
Relationship relChildren = personEntity
.getRelationship("personschildren");
for (EntityInstance personInstance : people) {
String personName = convertToTableAttrValue(attrPersonName,
personInstance, AdaptorDataType.TYPE_STRING);
row = "'" + caseID + "'" + "," + personName;
statement
.execute("INSERT INTO PERSON(WD_CASE_ID,PERSON_NAME) VALUES ("
+ row + ")", statement.RETURN_GENERATED_KEYS);
ResultSet generatedResult = statement.getGeneratedKeys();
int personID = -1;
if (generatedResult.next())
{
personID = generatedResult.getInt(1);
}
System.out.println("Person ID: " + personID);
ArrayList<Integer> personChildrenIDList = getPersonChildrenIDList(
personInstance, relChildren, caseID, personID,
entityInstanceChildIDMap);
// write relationship
for (Integer id : personChildrenIDList) {
statement.execute("INSERT INTO CHILD_PARENT VALUES (" + id
+ "," + personID + ")");
}
}
}
/*
* Helper method for retrieving the list of child ids related to a parent id
*/
private ArrayList<Integer> getPersonChildrenIDList(
EntityInstance personInstance, Relationship relChildren,
String caseID, int personID, HashMap<EntityInstance, Integer> map)
throws Exception {
ArrayList<Integer> IDlist = new ArrayList<Integer>();
List<EntityInstance> personChildren = relChildren
.getKnownTargets(personInstance);
for (EntityInstance personChildInstance : personChildren) {
IDlist.add(map.get(personChildInstance));
}
return IDlist;
}
/** ******************************** */
/* Web-determinations method calls */
/** ******************************** */
/**
* Returns true, for the data adaptor generates case IDs coming from the
* 'WD_Case' table
*/
public boolean dataAdaptorProvidesCaseID() {
return true;
}
/**
* Returns the saved cases
*/
public String[] listCases(SecurityToken token, InterviewRulebase rulebase) {
String[] cases = new String[] {};
if (authenticateUser(token)) {
try {
connectDBObjects();
if (statement.execute("SELECT * FROM WD_CASE")) {
ResultSet result = statement.getResultSet();
ArrayList<String> casesList = new ArrayList<String>();
while (result.next()) {
casesList
.add(new String(result.getString("WD_CASE_ID")));
}
result.close();
if (casesList.size() > 0) {
cases = casesList.toArray(new String[casesList.size()]);
}
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
} finally {
try {
closeDBObjects();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
}
return cases;
}
/**
* Loads a case by returning the interview user data
*/
public InterviewUserData load(SecurityToken token, String caseID,
InterviewRulebase rulebase) {
InterviewUserData userData = new InterviewUserData();
if (authenticateUser(token)) {
try {
connectDBObjects();
// 1. retrieve and set global record
if (statement
.execute("SELECT * FROM WD_CASE WHERE WD_CASE_ID = '"
+ caseID + "'")) {
InterviewEntityInstance iGlobalInstance = null;
ResultSet result = statement.getResultSet();
if (result.next()) {
iGlobalInstance = userData.getGlobalInstance();
int sun_shining = result.getInt("SUN_SHINING");
iGlobalInstance.setValue("sun_shining",
convertToInterviewAttrValue(sun_shining,
AdaptorDataType.TYPE_BOOLEAN));
}
result.close();
if (iGlobalInstance != null) {
String sql = null;
// 2. retrieve and set children
HashMap<String, InterviewEntityInstance> childIDInstanceMap = new HashMap<String, InterviewEntityInstance>();
sql = "SELECT * FROM CHILD WHERE WD_CASE_ID='" + caseID
+ "'";
if (statement.execute(sql)) {
ResultSet queryResult = statement.getResultSet();
while (queryResult.next()) {
int child_id = queryResult.getInt("CHILD_ID");
int eating_icecream = queryResult
.getInt("EATING_ICECREAM");
String child_name = queryResult
.getString("CHILD_NAME");
// 1. create an interview instance
InterviewEntityInstance iChildInstance = new InterviewEntityInstance(
"child", child_name + "(child_id: "
+ child_id + ")");
iChildInstance.setStatus(InstanceStatus.ADD);
iChildInstance
.setValue(
"child_name",
convertToInterviewAttrValue(child_name));
iChildInstance.setValue("eating_icecream",
convertToInterviewAttrValue(
eating_icecream,
AdaptorDataType.TYPE_BOOLEAN));
// 2. set containment
iChildInstance
.setContainmentParent(iGlobalInstance);
userData.addInstance(iChildInstance);
// 3. setup a map for setting the relationship
// (many to many)
childIDInstanceMap.put("" + child_id,
iChildInstance);
}
// complete the containment\
iGlobalInstance.setContainmentComplete("child",true);
queryResult.close();
}
// 3. retrieve and set people
sql = "SELECT * FROM PERSON WHERE WD_CASE_ID='"
+ caseID + "'";
HashMap<String, InterviewEntityInstance> personIDInstanceMap = new HashMap<String, InterviewEntityInstance>();
if (statement.execute(sql)) {
ResultSet queryResult = statement.getResultSet();
while (queryResult.next()) {
int person_id = queryResult.getInt("PERSON_ID");
String person_name = queryResult
.getString("PERSON_NAME");
// 1. create an interview instance
InterviewEntityInstance iPersonInstance = new InterviewEntityInstance(
"person", person_name + "(person_id: "
+ person_id + ")");
iPersonInstance.setStatus(InstanceStatus.ADD);
iPersonInstance
.setValue(
"person_name",
convertToInterviewAttrValue(person_name));
// 2. set containment
iPersonInstance
.setContainmentParent(iGlobalInstance);
userData.addInstance(iPersonInstance);
// 3. add to map for use with setting the
// relationship
personIDInstanceMap.put("" + person_id,
iPersonInstance);
}
// complete the containment
iGlobalInstance.setContainmentComplete("person", true);
queryResult.close();
}
// 4. set up the relationship personschildren (many to
// many) from person to child
for (String person_id : personIDInstanceMap.keySet()) {
sql = "SELECT * FROM CHILD_PARENT WHERE PARENT_ID="
+ person_id;
InterviewEntityInstance iPersonInstance = personIDInstanceMap
.get(person_id);
if (statement.execute(sql)) {
ResultSet queryResult = statement
.getResultSet();
while (queryResult.next()) {
int child_id = queryResult
.getInt("CHILD_ID");
InterviewEntityInstance iChildInstance = childIDInstanceMap
.get("" + child_id);
iPersonInstance.addRelationship(
"personschildren", iChildInstance,
InstanceStatus.ADD);
}
queryResult.close();
}
}
}
}
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
} finally {
try {
closeDBObjects();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
}
return userData;
}
/**
* Saves the interview session by mapping entities, instances and attributes
* to fields of tables
*/
public String save(SecurityToken token, String caseID,
InterviewSession session) {
String generatedCaseID = "";
if (authenticateUser(token)) {
try {
connectDBObjects();
generatedCaseID = generateCaseID();
saveSession(session, generatedCaseID);
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
} finally {
try {
closeDBObjects();
} catch (SQLException e) {
throw new RuntimeException(e.getMessage());
}
}
}
return generatedCaseID;
}
/**
* Registers the plugin using a specific rulebase
*/
public InterviewSessionPlugin getInstance(InterviewSessionRegisterArgs args) {
if (args.getSession().getRulebase().getIdentifier().equals(
"Parents and Children")) {
return new DerbyDataAdaptor();
}
return null;
}
}