public void setInstances(List<Integer> instanceIds, String collectionPid, String coder) {
StringBuilder query = new StringBuilder();
query.append(
"INSERT INTO Annotation (pid, size, last_visited_index, coder, collection_pid, status) VALUES (");
query.append("'").append(getPid()).append("',");
query.append("'").append(instanceIds.size()).append("',");
query.append("'").append(0).append("',");
query.append("'").append(coder).append("',");
query.append("'").append(collectionPid).append("',");
query.append("'").append(Status.CREATED.toString()).append("')");
DbManager.executeUpdate(query.toString());
int index = 0;
for (int instanceId : instanceIds) {
query = new StringBuilder();
query.append("INSERT INTO AnnotationInstance (pid,instance_id,`index`,time_spent) VALUES (");
query.append("'").append(getPid()).append("',");
query.append("'").append(instanceId).append("',");
query.append("'").append(index).append("',");
query.append("'").append(0).append("')");
DbManager.executeUpdate(query.toString());
index++;
}
}
public String getCodes(int instanceId) {
return DbManager.getString(
"SELECT codes FROM AnnotationInstance WHERE pid='"
+ getPid()
+ "' AND instance_id="
+ instanceId);
}
public Instance getDbInstance(int index) {
Instance instance = new Instance(InstanceType.GOLD);
Connection connection = null;
try {
connection = DbManager.getConnection();
String query =
"SELECT I.id AS id, I.text AS text, AI.codes AS codes, AI.time_spent AS time_spent "
+ "FROM AnnotationInstance AI "
+ "JOIN Instance I ON AI.instance_id = I.id "
+ "WHERE AI.pid=? AND AI.`index`=?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, getPid());
statement.setInt(2, index);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
instance.setId(resultSet.getInt("id"));
instance.setText(resultSet.getString("text"));
instance.setCodes(toInstanceCodes(resultSet.getString("codes")));
instance.setTimeSpent(resultSet.getInt("time_spent"));
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != connection) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return instance;
}
public void updateStatus(Status status) {
StringBuilder query = new StringBuilder();
query.append("UPDATE Annotation SET status='").append(status.toString()).append("' ");
query.append("WHERE pid='").append(getPid()).append("' ");
DbManager.executeUpdate(query.toString());
}
public List<Instance> getInstances() {
List<Instance> instances = new ArrayList<Instance>();
Connection connection = null;
try {
connection = DbManager.getConnection();
String query =
"SELECT I.id AS id, I.message_id AS message_id, AI.codes AS codes "
+ "FROM AnnotationInstance AI "
+ "JOIN Instance I ON AI.instance_id = I.id "
+ "WHERE AI.pid=? ORDER BY AI.`index` ASC";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, getPid());
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
Instance instance = new Instance(InstanceType.GOLD);
instance.setId(resultSet.getInt("id"));
instance.setMessageId(resultSet.getString("message_id"));
instance.setCodes(toInstanceCodes(resultSet.getString("codes")));
instances.add(instance);
}
resultSet.close();
statement.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (null != connection) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return instances;
}
public void updateTimeSpent(int index, int seconds) {
StringBuilder query = new StringBuilder();
query.append("UPDATE AnnotationInstance SET time_spent='").append(seconds).append("' ");
query.append("WHERE pid='").append(getPid()).append("' ");
query.append("AND `index`='").append(index).append("'");
DbManager.executeUpdate(query.toString());
}
public int getTimeSpent(int index) {
return DbManager.getInt(
"SELECT time_spent FROM AnnotationInstance WHERE pid='"
+ getPid()
+ "' AND `index`='"
+ index
+ "'");
}
public int getTotalPages() {
return DbManager.getInt(
"SELECT COUNT(DISTINCT M.page_view_id) "
+ "FROM AnnotationInstance AI JOIN Instance I ON I.id = AI.instance_id "
+ "JOIN Message M ON M.id = I.message_id "
+ "WHERE AI.pid='"
+ getPid()
+ "'");
}
public void updateDbLastVisitedIndex(int lastVisitedIndex) {
StringBuilder query = new StringBuilder();
query
.append("UPDATE Annotation SET last_visited_index='")
.append(lastVisitedIndex)
.append("' ");
query.append("WHERE pid='").append(getPid()).append("'");
DbManager.executeUpdate(query.toString());
}
public List<String> getPageViewIds() {
return DbManager.getStringValues(
"SELECT DISTINCT M.page_view_id AS page_view_id "
+ "FROM AnnotationInstance AI JOIN Instance I ON I.id = AI.instance_id "
+ "JOIN Message M ON M.id = I.message_id "
+ "WHERE AI.pid='"
+ getPid()
+ "' "
+ "ORDER BY AI.`index` ASC");
}
public String getPageViewId(int index) {
return DbManager.getString(
"SELECT M.page_view_id AS page_view_id "
+ "FROM AnnotationInstance AI JOIN Instance I ON I.id = AI.instance_id "
+ "JOIN Message M ON M.id = I.message_id "
+ "WHERE AI.pid='"
+ getPid()
+ "' "
+ "AND AI.`index`="
+ index);
}
public void updateDbInstance(int index, String[] codes) {
StringBuilder query = new StringBuilder();
query
.append("UPDATE AnnotationInstance SET codes='")
.append(toDbCodesValue(codes))
.append("' ");
query.append("WHERE pid='").append(getPid()).append("' ");
query.append("AND `index`='").append(index).append("' ");
DbManager.executeUpdate(query.toString());
}
public int getPageViewEndIndex(String pageViewId) {
return DbManager.getInt(
"SELECT MAX(AI.`index`) "
+ "FROM AnnotationInstance AI JOIN Instance I ON I.id = AI.instance_id "
+ "JOIN Message M ON M.id = I.message_id "
+ "WHERE AI.pid='"
+ getPid()
+ "' "
+ "AND M.page_view_id='"
+ pageViewId
+ "' ");
}
public List<Integer> getInstanceIds(String pageViewId) {
return DbManager.getIntValues(
"SELECT DISTINCT AI.instance_id AS instance_id "
+ "FROM AnnotationInstance AI JOIN Instance I ON I.id = AI.instance_id "
+ "JOIN Message M ON M.id = I.message_id "
+ "WHERE AI.pid='"
+ getPid()
+ "' "
+ "AND M.page_view_id='"
+ pageViewId
+ "' "
+ "ORDER BY AI.`index` ASC");
}
public int getSize() {
return DbManager.getInt("SELECT size FROM Annotation WHERE pid='" + getPid() + "'");
}
public int getDbLastVisitedIndex() {
return DbManager.getInt(
"SELECT last_visited_index FROM Annotation WHERE pid='" + getPid() + "'");
}
public String getCoder() {
return DbManager.getString("SELECT coder FROM Annotation WHERE pid='" + getPid() + "'");
}
public String getCollectionPid() {
return DbManager.getString(
"SELECT collection_pid FROM Annotation WHERE pid='" + getPid() + "'");
}
public int getTimeSpent() {
return DbManager.getInt(
"SELECT SUM(time_spent) AS time_spent FROM AnnotationInstance WHERE pid='"
+ getPid()
+ "' GROUP BY pid");
}
public String getStatus() {
return DbManager.getString("SELECT status FROM Annotation WHERE pid='" + getPid() + "'");
}