Example #1
0
  public void runTest() throws SQLException {

    db.start(this, "Transactions");
    db.openConnection();
    processTransactions();
    db.closeConnection();
    db.end();

    db.openConnection();
    processTransactions();
    db.logMemory(this, "Memory Usage");
    db.closeConnection();
  }
  @Override
  public void runTest() throws SQLException {

    database.start(this, "Transactions");
    database.openConnection();
    processTransactions();
    database.closeConnection();
    database.end();

    database.openConnection();
    processTransactions();
    database.logMemory(this, "Memory Usage");
    database.closeConnection();
  }
Example #3
0
  /**
   * Oppdaterer den totale vente-tiden i ett f*g. Metoden legger til waitingtime til det som lå der
   * fra før.
   *
   * @param subjectcode
   * @param waitingtime
   * @return
   */
  public static boolean updateTotalWaitingTime(String subjectcode, int waitingtime) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;

    try {
      ps =
          db.connection.prepareStatement(
              "UPDATE queue SET total_helped = total_helped + 1,total_waitingtime = total_waitingtime + ? WHERE subject_code = ?");
      ps.setInt(1, waitingtime);
      ps.setString(2, subjectcode);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }
    return true;
  }
Example #4
0
  /**
   * Endrer en entry(kø-element) til aktiv tilstand(får hjelp av en lærer).
   *
   * @param teacher
   * @param user
   * @param active
   */
  public static void setActive(String teacher, String user, boolean active) {
    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return;
    }

    PreparedStatement ps = null;

    try {
      ps =
          db.connection.prepareStatement(
              "UPDATE queue_list SET status = ?,teacher_username = ? WHERE person_username = ?");
      ps.setBoolean(1, active);
      ps.setString(2, teacher);
      ps.setString(3, user);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }
  }
Example #5
0
  /**
   * Endrer kø info til ett bestemt f*g
   *
   * @param code
   * @param info
   * @param username
   * @return
   */
  public static boolean endreKoInfo(String code, String info, String username) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;

    try {
      ps =
          db.connection.prepareStatement(
              "UPDATE queue SET info = ?,info_username = ? WHERE subject_code = ?");
      ps.setString(1, info);
      ps.setString(2, username);
      ps.setString(3, code);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }
    return true;
  }
Example #6
0
  /**
   * Fjerner en entry
   *
   * @param username
   * @param subjectcode
   * @return
   */
  public static boolean removeQueueEntry(String username) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;

    /* sletter din entrys */
    try {
      ps = db.connection.prepareStatement("DELETE FROM queue_list WHERE person_username = ?");
      ps.setString(1, username);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }

    return true;
  }
Example #7
0
  /**
   * Henter ut hvilken rolle en bruker har i ett bestemt f*g.
   *
   * @param subjectcode
   * @param username
   * @return
   */
  public static String getSubject_access(String subjectcode, String username) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
    }

    PreparedStatement ps = null;
    ResultSet rs = null;
    String role = "student";

    /* Henter hvilken access brukeren har i faget */
    try {
      ps =
          db.connection.prepareStatement(
              "SELECT r.role_name FROM roles r INNER JOIN person_sub_role psr ON psr.role_id = r.id WHERE psr.username = ? AND psr.subjectcode = ? ");
      ps.setString(1, username);
      ps.setString(2, subjectcode);
      rs = ps.executeQuery();

      if (rs.next()) {
        role = rs.getString("role_name");
      }
    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }
    return role;
  }
Example #8
0
  /**
   * oppretter en kø dersom det ikke finnes fra før.
   *
   * @param subjectcode
   * @return
   */
  public static boolean startQueue(String subjectcode) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;

    try {
      ps = db.connection.prepareStatement("UPDATE queue SET status = true WHERE subject_code = ?");
      ps.setString(1, subjectcode);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }

    return true;
  }
Example #9
0
  /**
   * Sletter alle kø-elementene fra ett f*g slik at fagets kø er tom.
   *
   * @param subjectcode
   * @return
   */
  public static boolean emptyQueue(String subjectcode) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;
    ResultSet rs = null;

    /* henter queue_id */

    int queue_id = 0;

    try {
      ps = db.connection.prepareStatement("SELECT id FROM queue WHERE subject_code = ?");
      ps.setString(1, subjectcode);
      rs = ps.executeQuery();

      if (rs.next()) {
        queue_id = rs.getInt("id");
      }
    } catch (Exception e) {
      System.out.println(e);
    }

    /* sletter alle entrys */
    try {
      ps = db.connection.prepareStatement("DELETE FROM queue_list WHERE queue_id = ?");
      ps.setInt(1, queue_id);
      ps.executeUpdate();
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeConnection(db.connection);
    }

    return true;
  }
Example #10
0
  /**
   * Legger inn en spesifikk øving i person_ass tabellen.
   *
   * @param username
   * @param subjectcode
   * @param assignment
   */
  public static void godkjennOving(String username, String subjectcode, int assignment) {
    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
    }

    PreparedStatement ps = null;
    ResultSet rs = null;
    int assignment_id = -1;
    try {
      /* henter ut assignemnt_id */
      ps =
          db.connection.prepareStatement(
              "SELECT id FROM assignments WHERE subjectcode = ? AND assignmentnumber = ?");
      ps.setString(1, subjectcode);
      ps.setInt(2, assignment);
      rs = ps.executeQuery();

      if (rs.next()) {
        assignment_id = rs.getInt("id");
      }

      /* Legger inn en øving til en person */

      ps =
          db.connection.prepareStatement(
              "INSERT INTO person_ass(username,assignment_id) VALUES(?,?)");
      ps.setString(1, username);
      ps.setInt(2, assignment_id);
      ps.executeUpdate();

    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }
  }
Example #11
0
  /**
   * Henter ut alle personer i ett f*g som personSimple objekter. Henter kun ut brukernavn, fornavn
   * og etternavn.
   *
   * @param code
   * @return
   */
  public static ArrayList<PersonSimple> getPersonerSimple(String code) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
    }

    PreparedStatement ps = null;
    ResultSet rs = null;
    ArrayList<PersonSimple> personer = new ArrayList();

    /* Henter hvilken access brukeren har i faget */
    try {
      ps =
          db.connection.prepareStatement(
              "SELECT p.username,p.firstname,p.lastname FROM person p INNER JOIN person_sub_role ps ON p.username = ps.username WHERE ps.subjectcode = ? AND role_id = ?");
      ps.setString(1, code);
      ps.setInt(2, 1);
      rs = ps.executeQuery();

      while (rs.next()) {
        personer.add(
            new PersonSimple(
                rs.getString("username"), rs.getString("firstname"), rs.getString("lastname")));
      }
    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }

    return personer;
  }
Example #12
0
  @Override
  public void init(Database db, int size) throws SQLException {
    this.database = db;
    transactions = size * 6;

    int scale = 2;
    accounts = size * 30;
    tellers = Math.max(accounts / 10, 1);
    branches = Math.max(tellers / 10, 1);

    db.start(this, "Init");

    db.openConnection();

    db.dropTable("BRANCHES");
    db.dropTable("TELLERS");
    db.dropTable("ACCOUNTS");
    db.dropTable("HISTORY");

    String[] create = {
      "CREATE TABLE BRANCHES(BID INT NOT NULL PRIMARY KEY, "
          + "BBALANCE DECIMAL(15,2), FILLER VARCHAR(88))",
      "CREATE TABLE TELLERS(TID INT NOT NULL PRIMARY KEY, "
          + "BID INT, TBALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
      "CREATE TABLE ACCOUNTS(AID INT NOT NULL PRIMARY KEY, "
          + "BID INT, ABALANCE DECIMAL(15,2), FILLER VARCHAR(84))",
      "CREATE TABLE HISTORY(TID INT, "
          + "BID INT, AID INT, DELTA DECIMAL(15,2), HTIME DATETIME, "
          + "FILLER VARCHAR(40))"
    };

    for (String sql : create) {
      db.update(sql);
    }

    PreparedStatement prep;
    db.setAutoCommit(false);
    int commitEvery = 1000;
    prep =
        db.prepare(
            "INSERT INTO BRANCHES(BID, BBALANCE, FILLER) "
                + "VALUES(?, 10000.00, '"
                + FILLER
                + "')");
    for (int i = 0; i < branches * scale; i++) {
      prep.setInt(1, i);
      db.update(prep, "insertBranches");
      if (i % commitEvery == 0) {
        db.commit();
      }
    }
    db.commit();
    prep =
        db.prepare(
            "INSERT INTO TELLERS(TID, BID, TBALANCE, FILLER) "
                + "VALUES(?, ?, 10000.00, '"
                + FILLER
                + "')");
    for (int i = 0; i < tellers * scale; i++) {
      prep.setInt(1, i);
      prep.setInt(2, i / tellers);
      db.update(prep, "insertTellers");
      if (i % commitEvery == 0) {
        db.commit();
      }
    }
    db.commit();
    int len = accounts * scale;
    prep =
        db.prepare(
            "INSERT INTO ACCOUNTS(AID, BID, ABALANCE, FILLER) "
                + "VALUES(?, ?, 10000.00, '"
                + FILLER
                + "')");
    for (int i = 0; i < len; i++) {
      prep.setInt(1, i);
      prep.setInt(2, i / accounts);
      db.update(prep, "insertAccounts");
      if (i % commitEvery == 0) {
        db.commit();
      }
    }
    db.commit();
    db.closeConnection();
    db.end();

    //        db.start(this, "Open/Close");
    //        db.openConnection();
    //        db.closeConnection();
    //        db.end();
  }
Example #13
0
  /**
   * Metoden som kalles ved hver "polling" all info som trengs når du ser på køen. blir metoden for
   * "tung" kan eventuelt øvingsoversikt delen fjernes..(2 spørringer)
   *
   * @param subjectcode
   * @return
   */
  public static Queue getQueueBean(String username, String subjectcode) {

    Queue q = new Queue();

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
    }

    PreparedStatement ps = null;
    PreparedStatement ps2 = null;
    ResultSet rs = null;
    ResultSet rs2 = null;

    int queue_id = 0;
    String info_username = "";
    /* Henter Queue info */
    try {
      ps = db.connection.prepareStatement("SELECT * FROM queue WHERE subject_code = ?");
      ps.setString(1, subjectcode);
      rs = ps.executeQuery();

      if (rs.next()) {
        queue_id = rs.getInt("id");
        q.setSubjectName(subjectcode);
        q.setQueue_info(rs.getString("info"));
        q.setStatus(rs.getBoolean("status"));
        q.setTotalWaitingTime(rs.getInt("total_waitingtime"));
        q.setTotalHelped(rs.getInt("total_helped"));
        info_username = rs.getString("info_username");
      }

      ps =
          db.connection.prepareStatement(
              "SELECT firstname,lastname FROM person WHERE username = ?");
      ps.setString(1, info_username);
      rs = ps.executeQuery();

      if (rs.next()) {
        q.setQueue_info_firstname(rs.getString("firstname"));
        q.setQueue_info_lastname(rs.getString("lastname"));
      }
    } catch (Exception e) {
      System.out.println(e);
    }

    /* henter alle entrys i koen */

    ArrayList<queue_entry> entrys = new ArrayList();
    try {
      ps = db.connection.prepareStatement("SELECT * FROM queue_list WHERE queue_id = ?");
      ps.setInt(1, queue_id);
      rs = ps.executeQuery();

      while (rs.next()) {
        int queue_list_id = rs.getInt("id");
        ArrayList<String> queue_list_persons = new ArrayList();
        ps2 =
            db.connection.prepareStatement(
                "SELECT person_username FROM queue_list_person WHERE queue_list_id = ?");
        ps2.setInt(1, queue_list_id);
        rs2 = ps2.executeQuery();

        while (rs2.next()) {
          queue_list_persons.add(rs2.getString("person_username"));
        }

        entrys.add(
            new queue_entry(
                rs.getString("info"),
                rs.getString("room"),
                rs.getInt("table_nr"),
                rs.getBoolean("status"),
                rs.getString("person_username"),
                rs.getString("teacher_username"),
                queue_list_persons,
                rs.getTimestamp("submit_time")));
      }

      q.setQueue_entrys(entrys);
      q.setEstimatedTime();

    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }

    return q;
  }
Example #14
0
  /**
   * Henter ut alle øvingene til alle personene som ligger i en queue entry.
   *
   * @param person
   * @return
   */
  public static Queue_godkjenning getListObjects(String person, String subjectcode) {
    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
    }

    PreparedStatement ps = null;
    ResultSet rs = null;
    Queue_godkjenning Qg = new Queue_godkjenning();
    ArrayList<String> personer = new ArrayList();
    personer.add(person);
    int queue_list_id = -1;
    int queue_id = -1;

    /* Henter ut info om entryen */
    try {
      ps =
          db.connection.prepareStatement(
              "SELECT ql.submit_time,ql.info,ql.room,ql.table_nr,ql.id,ql.queue_id,ql.person_username FROM queue_list ql INNER JOIN queue q ON q.id = ql.queue_id WHERE ql.person_username = ? AND q.subject_code = ?");

      ps.setString(1, person);
      ps.setString(2, subjectcode);
      rs = ps.executeQuery();

      if (rs.next()) {
        Qg.setInfo(rs.getString("info"));
        Qg.setRoom(rs.getString("room"));
        Qg.setTable_nr(rs.getInt("table_nr"));
        Qg.setTime(rs.getTimestamp("submit_time"));
        queue_list_id = rs.getInt("id");
        queue_id = rs.getInt("queue_id");
      }

      /* Henter ut antall øvinger i faget */
      ps =
          db.connection.prepareStatement(
              "SELECT sub.assignments FROM subject sub INNER JOIN queue q ON q.subject_code = sub.subjectcode WHERE q.id = ?");
      ps.setInt(1, queue_id);
      rs = ps.executeQuery();

      if (rs.next()) {
        Qg.setNumberofassignments(rs.getInt("assignments"));
      }

      /* Henter ut alle personer som ligger i entryen utenom påmelder */
      ps =
          db.connection.prepareStatement(
              "SELECT person_username FROM queue_list_person WHERE queue_list_id = ?");
      ps.setInt(1, queue_list_id);
      rs = ps.executeQuery();

      while (rs.next()) {
        personer.add(rs.getString("person_username"));
      }

      /* Henter ut alle øvinger til alle i entryen */
      ArrayList<Assignment> ass;
      Assignment ny;
      for (String p : personer) {
        ps =
            db.connection.prepareStatement(
                "SELECT ass.assignmentnumber FROM assignments ass INNER JOIN person_ass pas ON ass.id = pas.assignment_id WHERE pas.username = ? AND ass.subjectcode = ?");
        ps.setString(1, p);
        ps.setString(2, subjectcode);
        rs = ps.executeQuery();
        ass = new ArrayList();
        while (rs.next()) {
          ny = new Assignment();
          ny.setAssignmentNumber(rs.getInt("assignmentnumber"));
          ass.add(ny);
        }
        Qg.personer.add(new ListObject(p, ass));
      }
    } catch (Exception e) {
      System.out.println(e);
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }
    return Qg;
  }
Example #15
0
  /**
   * Legger til en person i køen til ett f*g.
   *
   * @param username
   * @param subjectcode
   * @param info
   * @return
   */
  public static boolean addQueueEntry(
      String username,
      String subjectcode,
      String info,
      String room,
      int table,
      ArrayList<String> personer) {

    Database db = new Database();
    try {
      db.openConnection();
    } catch (Exception ex) {
      System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex);
      return false;
    }

    PreparedStatement ps = null;
    ResultSet rs = null;

    int queue_id = 0;

    try {
      ps = db.connection.prepareStatement("SELECT id FROM queue WHERE subject_code = ?");
      ps.setString(1, subjectcode);
      rs = ps.executeQuery();

      if (rs.next()) {
        queue_id = rs.getInt("ID");
      }
    } catch (Exception e) {
      System.out.println(e);
    }

    try {
      ps =
          db.connection.prepareStatement(
              "INSERT INTO queue_list (queue_id,info,room,table_nr,status,person_username) VALUES (?,?,?,?,?,?)");
      ps.setInt(1, queue_id);
      ps.setString(2, info);
      ps.setString(3, room);
      ps.setInt(4, table);
      ps.setBoolean(5, false);
      ps.setString(6, username);
      ps.executeUpdate();

      ps = db.connection.prepareStatement("SELECT id FROM queue_list WHERE person_username = ?");
      ps.setString(1, username);
      rs = ps.executeQuery();

      int queue_list_id = -1;
      if (rs.next()) {
        queue_list_id = rs.getInt("id");
      }

      for (String pers : personer) {
        ps =
            db.connection.prepareStatement(
                "INSERT INTO queue_list_person (person_username, queue_list_id) VALUES (?,?)");
        ps.setString(1, pers);
        ps.setInt(2, queue_list_id);
        ps.executeUpdate();
      }
    } catch (Exception e) {
      System.out.println(e);
      return false;
    } finally {
      Cleanup.closeSentence(ps);
      Cleanup.closeResSet(rs);
      Cleanup.closeConnection(db.connection);
    }

    return true;
  }