예제 #1
0
  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++;
    }
  }
예제 #2
0
 public String getCodes(int instanceId) {
   return DbManager.getString(
       "SELECT codes FROM AnnotationInstance WHERE pid='"
           + getPid()
           + "' AND instance_id="
           + instanceId);
 }
예제 #3
0
 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;
 }
예제 #4
0
  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());
  }
예제 #5
0
 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;
 }
예제 #6
0
  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());
  }
예제 #7
0
 public int getTimeSpent(int index) {
   return DbManager.getInt(
       "SELECT time_spent FROM AnnotationInstance WHERE pid='"
           + getPid()
           + "' AND `index`='"
           + index
           + "'");
 }
예제 #8
0
 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()
           + "'");
 }
예제 #9
0
  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());
  }
예제 #10
0
 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");
 }
예제 #11
0
 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);
 }
예제 #12
0
  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());
  }
예제 #13
0
 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
           + "' ");
 }
예제 #14
0
 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");
 }
예제 #15
0
 public int getSize() {
   return DbManager.getInt("SELECT size FROM Annotation WHERE pid='" + getPid() + "'");
 }
예제 #16
0
 public int getDbLastVisitedIndex() {
   return DbManager.getInt(
       "SELECT last_visited_index FROM Annotation WHERE pid='" + getPid() + "'");
 }
예제 #17
0
 public String getCoder() {
   return DbManager.getString("SELECT coder FROM Annotation WHERE pid='" + getPid() + "'");
 }
예제 #18
0
 public String getCollectionPid() {
   return DbManager.getString(
       "SELECT collection_pid FROM Annotation WHERE pid='" + getPid() + "'");
 }
예제 #19
0
 public int getTimeSpent() {
   return DbManager.getInt(
       "SELECT SUM(time_spent) AS time_spent FROM AnnotationInstance WHERE pid='"
           + getPid()
           + "' GROUP BY pid");
 }
예제 #20
0
 public String getStatus() {
   return DbManager.getString("SELECT status FROM Annotation WHERE pid='" + getPid() + "'");
 }