HSQLStatisticDatabase.java

package de.tud.kom.socom.web.server.database.statistics;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import de.tud.kom.socom.web.server.database.HSQLAccess;
import de.tud.kom.socom.web.server.util.Logger;
import de.tud.kom.socom.web.server.util.LoggerFactory;

public class HSQLStatisticDatabase implements StatisticDatabase {

	private static StatisticDatabase instance = new HSQLStatisticDatabase();
	private Logger logger = LoggerFactory.getLogger();
	private static HSQLAccess db;

	private HSQLStatisticDatabase() {
		db = HSQLAccess.getInstance();
	}

	public static StatisticDatabase getInstance() {
		return instance;
	}

	@Override
	public JSONObject getGameGraphJSON(long instanceid) throws SQLException, JSONException {
		PreparedStatement statement = db.getPreparedStatement("SELECT * FROM gameinstances WHERE id = ?;");
		statement.setLong(1, instanceid);
		ResultSet rs = statement.executeQuery();
		if(!rs.next()){ // instance doesnt exist
			logger.Debug("Instance " + instanceid + " not found, abort.");
			return null;
		}
		
		JSONObject json = new JSONObject();
		json.put("instanceid", instanceid);
		JSONArray contextarray = new JSONArray();
		
		statement = db.getPreparedStatement("SELECT " +
				"gamecontexts.name, " +
				"gamecontexts.externalid, " +
				"gamecontexts.autogenerated, " +
				"gamecontexts.id, " +
				"COUNT(DISTINCT gamecontent.id) AS contentcount, " +
				"SUM(DISTINCT gamecontent.hits) AS contenthitstotal, " +
				"COUNT(DISTINCT influence.id) AS influencecount, " +
				"COUNT(DISTINCT userprogress.uid) AS usersseen, " +
				"SUM(userprogress.playtime) AS timespenttotal " +
			"FROM " +
				"((gamecontexts LEFT JOIN gamecontent ON gamecontent.contextid = gamecontexts.id) " +
					"LEFT JOIN influence ON influence.contextid = gamecontexts.id) " +
						"LEFT JOIN userprogress ON userprogress.scnid = gamecontexts.id " +
			"WHERE " +
				"gamecontexts.gameinstid = ? " +
			"GROUP BY " +
				"name, " +
				"externalid," +
				"autogenerated, " +
				"id;");
		statement.setLong(1, instanceid);
		rs = statement.executeQuery();
		
		JSONArray endNodes = new JSONArray(); 
		long startNode = -1;
		Set<Long> gotRelations = new HashSet<Long>();
		Set<Long> nodes = new HashSet<Long>();
		long timeSpentAvgMin = Long.MAX_VALUE, timeSpentAvgMax = 0;
		
		while(rs.next()) {
			long id = rs.getLong("id");
			nodes.add(id);
			boolean autogenerated = rs.getBoolean("autogenerated");
			String name = rs.getString("name");
			String externalid = rs.getString("externalid");
			long timeSpentTotal = rs.getLong("timespenttotal");
			long usersSeen = rs.getLong("usersseen");
			long timeSpentAvg = usersSeen > 0 ? timeSpentTotal / usersSeen : 0;
			if(timeSpentAvg > timeSpentAvgMax) timeSpentAvgMax = timeSpentAvg;
			if(timeSpentAvg < timeSpentAvgMin) timeSpentAvgMin = timeSpentAvg;
			long contentCount = rs.getLong("contentcount");
			long influenceCount = rs.getLong("influenceCount");
			long contentHits = rs.getLong("contenthitstotal");
			JSONArray relations = getRelations(id, gotRelations);
			contextarray.put(new JSONObject().put("id", id).put("data", new JSONObject().put("id", id).put("autogenerated", autogenerated)
					.put("name", name).put("timeSpentTotal", timeSpentTotal).put("timeSpentAvg", timeSpentAvg).put("externalid",externalid)
					.put("usersSeen", usersSeen).put("contentCount", contentCount).put("influenceCount", influenceCount)
					.put("contentHits", contentHits).put("relationsTo", relations)));
			
			if(relations.length() == 0) // no relations anywhere -> endnodes.. (dead-end)
				endNodes.put(id);
		}
		for(long node : nodes){
			if(!gotRelations.contains(node))
				startNode = node;
		}
		if(startNode == -1) {
			startNode = findStartNodeFromFromStartingTimes(instanceid);
		}
		
		json.put("contexts", contextarray);
		json.put("timeSpentAvgMin", timeSpentAvgMin);
		json.put("timeSpentAvgMax", timeSpentAvgMax);
		json.put("startnode", startNode);
		json.put("endnodes", endNodes);
		return json;
	}
	
	private long findStartNodeFromFromStartingTimes(long instanceid) throws SQLException {
		PreparedStatement statement = db.getPreparedStatement("SELECT COUNT(uid), scnid " +
				"FROM (userprogress " +
					"LEFT JOIN " +
						"(SELECT uid AS newuid, MIN(time) AS mintime FROM userprogress GROUP BY uid) AS t1 " +
							"ON userprogress.uid = t1.newuid) " +
							"LEFT JOIN gamecontexts ON gamecontexts.id = userprogress.scnid " +
				"WHERE time = mintime AND gameinstid = ? " +
				"GROUP BY scnid " +
				"LIMIT 1");
		statement.setLong(1, instanceid);
		ResultSet rs = statement.executeQuery();
		long startId = -1;
		if(rs.next())
			startId = rs.getLong("scnid");
		return startId;
	}

	private JSONArray getRelations(long contextid, Set<Long> gotRelations) throws SQLException, JSONException {
		JSONArray array = new JSONArray();
		PreparedStatement statement = db.getPreparedStatement("SELECT * FROM gamecontextrelations WHERE parentid = ?;");
		statement.setLong(1, contextid);
		ResultSet rs = statement.executeQuery();
		while(rs.next()) {
			long source = contextid;
			long dest = rs.getLong("childid");
			long timesUsed = rs.getLong("timesused");
			boolean autogenerated = rs.getBoolean("autogenerated");
			array.put(new JSONObject().put("destination", dest).put("source", source).put("timesUsed", timesUsed)
					.put("autogenerated", autogenerated));
			gotRelations.add(dest);
		}
		return array;
	}
}