Пример #1
0
 /**
  * Get the results of a user's attempt on a test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param testID the deploy file ID.
  * @param pi the PI of the user.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryUserReportTest(DatabaseAccess.Transaction dat, String testID, String pi)
     throws SQLException {
   return dat.query(
       "SELECT t.attempt,t.finished,q.question,q.attempt,q.clock,r.questionline,r.answerline,r.actions,r.attempts,s.axis,s.score,tq.questionnumber,t.finishedclock,"
           + "(SELECT MIN(a2.clock) FROM "
           + getPrefix()
           + "actions a2 WHERE a2.qi=q.qi) AS minaction,"
           + "(SELECT MAX(a2.clock) FROM "
           + getPrefix()
           + "actions a2 WHERE a2.qi=q.qi) AS maxaction,"
           + "q.finished as qfinished "
           + "FROM "
           + getPrefix()
           + "tests t "
           + "LEFT JOIN "
           + getPrefix()
           + "questions q ON t.ti=q.ti "
           + "LEFT JOIN "
           + getPrefix()
           + "testquestions tq ON t.ti=tq.ti AND q.question=tq.question "
           + "LEFT JOIN "
           + getPrefix()
           + "results r ON q.qi=r.qi "
           + "LEFT JOIN "
           + getPrefix()
           + "scores s ON q.qi=s.qi "
           + "WHERE t.deploy="
           + Strings.sqlQuote(testID)
           + " AND t.pi="
           + Strings.sqlQuote(pi)
           + " AND (q.finished>0 OR q.finished IS NULL OR tq.questionnumber = 1)"
           + "ORDER BY t.attempt,tq.questionnumber,q.clock");
 }
Пример #2
0
 /**
  * Get the current state of all questions in a test attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param ti test instance id.
  * @param questionID question id.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryQuestionActions(DatabaseAccess.Transaction dat, int ti, String questionID)
     throws SQLException {
   return dat.query(
       "SELECT q.qi, MAX(a.seq), q.finished,q.attempt,q.majorversion,q.minorversion "
           + "FROM "
           + getPrefix()
           + "questions q "
           + "LEFT JOIN "
           + getPrefix()
           + "actions a ON q.qi=a.qi "
           + "WHERE q.ti="
           + ti
           + " AND q.question="
           + Strings.sqlQuote(questionID)
           + " "
           + "AND attempt=("
           + "SELECT MAX(attempt) FROM "
           + getPrefix()
           + "questions q2 "
           + "WHERE q2.ti="
           + ti
           + " AND q2.question="
           + Strings.sqlQuote(questionID)
           + ") "
           + "GROUP BY q.qi,q.finished,q.attempt,q.majorversion,q.minorversion");
 }
Пример #3
0
 /**
  * Create a new test attempt within the database.
  *
  * @param dat the transaction within which the query should be executed.
  * @param oucu the user's username.
  * @param testID the deploy file ID.
  * @param rseed the random seed the user was assigned.
  * @param attempt attempt number for this user of this quiz, starting at 1.
  * @param admin 1 if this is an admin attempt, otherwise 0.
  * @param pi the PI of the user.
  * @param fixedVariant for testing, fixes the variant of each question.
  * @param navigatorVersion the version of the test navigator that started this attempt.
  * @throws SQLException
  */
 public void insertTest(
     DatabaseAccess.Transaction dat,
     String oucu,
     String testID,
     long rseed,
     int attempt,
     boolean admin,
     String pi,
     int fixedVariant,
     String navigatorVersion)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "tests (oucu,deploy,rseed,attempt,finished,clock,admin,pi,variant,testposition,navigatorversion) VALUES ("
           + Strings.sqlQuote(oucu)
           + ","
           + Strings.sqlQuote(testID)
           + ","
           + rseed
           + ","
           + attempt
           + ",0,DEFAULT,"
           + (admin ? "1" : "0")
           + ","
           + Strings.sqlQuote(pi)
           + ","
           + (fixedVariant == -1 ? "NULL" : fixedVariant + "")
           + ",0,'"
           + navigatorVersion
           + "');");
 }
Пример #4
0
 protected boolean columnExistsInTable(DatabaseAccess.Transaction dat, String table, String column)
     throws SQLException {
   ResultSet rs =
       dat.query(
           "SELECT COUNT(*) FROM information_schema.columns WHERE table_name="
               + Strings.sqlQuote(getPrefix() + table)
               + " AND column_name="
               + Strings.sqlQuote(column));
   rs.next();
   return rs.getInt(1) != 0;
 }
Пример #5
0
 /**
  * Add a new session to the sessioninfo table.
  *
  * @param dat the transaction within which the query should be executed.
  * @param ti test instance id.
  * @param ip the user's IP address.
  * @param agent the user's browser user agent.
  * @throws SQLException
  */
 public void insertSessionInfo(DatabaseAccess.Transaction dat, int ti, String ip, String agent)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "sessioninfo(ti,ip,useragent) VALUES("
           + ti
           + ","
           + Strings.sqlQuote(ip)
           + ","
           + Strings.sqlQuote(agent)
           + ");");
 }
Пример #6
0
 /**
  * Store a custom result for a question attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param qi question instance id.
  * @param name custom result name.
  * @param value custom result value.
  * @throws SQLException
  */
 public void insertCustomResult(DatabaseAccess.Transaction dat, int qi, String name, String value)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "customresults VALUES("
           + qi
           + ","
           + Strings.sqlQuote(name)
           + ","
           + unicode(Strings.sqlQuote(value))
           + ");");
 }
Пример #7
0
 /**
  * Get the number of attempts a user has made at a test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param oucu the user's username.
  * @param testID the deploy file ID.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryMaxTestAttempt(DatabaseAccess.Transaction dat, String oucu, String testID)
     throws SQLException {
   return dat.query(
       "SELECT MAX(attempt) "
           + "FROM "
           + getPrefix()
           + "tests "
           + "WHERE oucu="
           + Strings.sqlQuote(oucu)
           + " AND deploy="
           + Strings.sqlQuote(testID)
           + ";");
 }
Пример #8
0
 /**
  * Get a user's most recent attempt at a test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param oucu the user's username.
  * @param testID the deploy file ID.
  * @return the requestedt data.
  * @throws SQLException
  */
 public ResultSet queryUnfinishedSessions(
     DatabaseAccess.Transaction dat, String oucu, String testID) throws SQLException {
   return dat.query(
       "SELECT ti,rseed,finished,variant,testposition,navigatorversion "
           + "FROM "
           + getPrefix()
           + "tests "
           + "WHERE oucu="
           + Strings.sqlQuote(oucu)
           + " AND deploy="
           + Strings.sqlQuote(testID)
           + " "
           + "ORDER BY attempt DESC LIMIT 1");
 }
Пример #9
0
 /**
  * Store a parameter of an action.
  *
  * @param dat the transaction within which the query should be executed.
  * @param qi question instance id.
  * @param seq action sequence number.
  * @param name parameter name.
  * @param value parameter value.
  * @throws SQLException
  */
 public void insertParam(
     DatabaseAccess.Transaction dat, int qi, int seq, String name, String value)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "params VALUES ("
           + qi
           + ","
           + seq
           + ","
           + Strings.sqlQuote(name)
           + ","
           + unicode(Strings.sqlQuote(value))
           + ");");
 }
Пример #10
0
 /**
  * Get a list of all the questions attempts for all questions in a test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param testID the deploy file ID.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryQuestionList(DatabaseAccess.Transaction dat, String testID)
     throws SQLException {
   return dat.query(
       "SELECT q.question,sum(s.score) as scoretot,count(s.score) as numscores,"
           + "max(q.majorversion) as maxversion,tq.questionnumber,count(tq.ti) as numattempts "
           + "FROM "
           + getPrefix()
           + "tests t "
           + "JOIN "
           + getPrefix()
           + "testquestions tq ON t.ti=tq.ti "
           + "JOIN "
           + getPrefix()
           + "questions q ON t.ti=q.ti AND q.question=tq.question "
           + "LEFT JOIN "
           + getPrefix()
           + "scores s ON q.qi=s.qi "
           + "WHERE t.deploy="
           + Strings.sqlQuote(testID)
           + " "
           + "AND q.finished <> 0"
           + "AND ((s.axis='' AND s.score IS NOT NULL) OR s.axis IS NULL)"
           + "GROUP BY q.question,tq.questionnumber "
           + "ORDER BY tq.questionnumber");
 }
Пример #11
0
 /**
  * Get a list of a user's sessions where they interacted with a particular test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param testID the deploy file ID.
  * @param pi the PI of the user.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryUserReportSessions(DatabaseAccess.Transaction dat, String testID, String pi)
     throws SQLException {
   return dat.query(
       "SELECT t.attempt,si.clock,si.ip,si.useragent "
           + "FROM "
           + getPrefix()
           + "tests t "
           + "INNER JOIN "
           + getPrefix()
           + "sessioninfo si ON t.ti=si.ti "
           + "WHERE t.deploy="
           + Strings.sqlQuote(testID)
           + " AND t.pi="
           + Strings.sqlQuote(pi)
           + " "
           + "ORDER BY t.attempt,si.clock");
 }
Пример #12
0
 /**
  * Get a list of all the people who have attempted a particular test, ordered by PI
  *
  * @param dat the transaction within which the query should be executed.
  * @param testID the deploy file ID.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryTestAttemptersByPIandFinishedASC(
     DatabaseAccess.Transaction dat, String testID) throws SQLException {
   return dat.query(
       "SELECT oucu,pi,clock,finished,admin,finishedclock,rseed,variant,ti "
           + "FROM "
           + getPrefix()
           + "tests t "
           + "WHERE deploy="
           + Strings.sqlQuote(testID)
           + " "
           + "ORDER BY pi,finished DESC,clock ASC");
 }
Пример #13
0
 /**
  * Store a score for a question attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param qi question instance id.
  * @param axis the axis the score should be recorded against.
  * @param marks the score.
  * @throws SQLException
  */
 public void insertScore(DatabaseAccess.Transaction dat, int qi, String axis, int marks)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "scores VALUES("
           + qi
           + ","
           + Strings.sqlQuote(axis)
           + ","
           + marks
           + ");");
 }
Пример #14
0
 /**
  * Get the number of attempts that a user has made at a question in a test attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param ti test instance id.
  * @param questionID question id.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryMaxQuestionAttempt(
     DatabaseAccess.Transaction dat, int ti, String questionID) throws SQLException {
   return dat.query(
       "SELECT MAX(attempt) "
           + "FROM "
           + getPrefix()
           + "questions "
           + "WHERE ti="
           + ti
           + " AND question="
           + Strings.sqlQuote(questionID)
           + ";");
 }
Пример #15
0
 /**
  * Store the results of a question attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param qi question instance id.
  * @param questionLine Text summary of question.
  * @param answerLine Text summary of user's answer.
  * @param actionSummary Text summary of all the user's actions.
  * @param attempts number of attempts at the question. 1 = right first time, 2 = right second
  *     time, etc.; 0 = pass, -1 = wrong.
  * @throws SQLException
  */
 public void insertResult(
     DatabaseAccess.Transaction dat,
     int qi,
     String questionLine,
     String answerLine,
     String actionSummary,
     int attempts)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "results (qi,questionline,answerline,actions,attempts) VALUES("
           + qi
           + ","
           + unicode(Strings.sqlQuote(questionLine))
           + ","
           + unicode(Strings.sqlQuote(answerLine))
           + ","
           + unicode(Strings.sqlQuote(actionSummary))
           + ","
           + attempts
           + ");");
 }
Пример #16
0
 /**
  * Create a new attempt at a question within a test attempt.
  *
  * @param dat the transaction within which the query should be executed.
  * @param ti test instance id.
  * @param questionID question id.
  * @param attempt attempt number.
  * @throws SQLException
  */
 public void insertQuestion(DatabaseAccess.Transaction dat, int ti, String questionID, int attempt)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "questions (ti,question,attempt,finished,clock,majorversion,minorversion) "
           + "VALUES ("
           + ti
           + ","
           + Strings.sqlQuote(questionID)
           + ","
           + attempt
           + ",0,DEFAULT,0,0);");
 }
Пример #17
0
 /**
  * Get a report of all users' interactions with a particular question in a test.
  *
  * @param dat the transaction within which the query should be executed.
  * @param testID the deploy file ID.
  * @param questionID question id.
  * @return the requested data.
  * @throws SQLException
  */
 public ResultSet queryQuestionReport(
     DatabaseAccess.Transaction dat, String testID, String questionID) throws SQLException {
   return dat.query(
       "SELECT t.pi,t.oucu,q.attempt,q.clock,"
           + "r.questionline,r.answerline,r.actions,r.attempts,s.axis,s.score "
           + "FROM "
           + getPrefix()
           + "tests t "
           + "INNER JOIN "
           + getPrefix()
           + "questions q ON t.ti=q.ti "
           + "INNER JOIN "
           + getPrefix()
           + "results r ON q.qi=r.qi "
           + "LEFT JOIN "
           + getPrefix()
           + "scores s ON q.qi=s.qi "
           + "WHERE t.deploy="
           + Strings.sqlQuote(testID)
           + " AND q.question="
           + Strings.sqlQuote(questionID)
           + " AND q.finished>0 "
           + "ORDER BY t.pi,q.attempt");
 }
Пример #18
0
 /**
  * Add a new row to the testquestions table.
  *
  * @param dat the transaction within which the query should be executed.
  * @param ti test instance id.
  * @param number sequence number of the question within the test.
  * @param questionID question id.
  * @param requiredVersion required major version of the question, from the test definition file.
  * @param sectionName Which section of the test the question belongs to.
  * @throws SQLException
  */
 public void insertTestQuestion(
     DatabaseAccess.Transaction dat,
     int ti,
     int number,
     String questionID,
     int requiredVersion,
     String sectionName)
     throws SQLException {
   dat.update(
       "INSERT INTO "
           + getPrefix()
           + "testquestions (ti,questionnumber,question,requiredversion,sectionname) "
           + "VALUES("
           + ti
           + ","
           + number
           + ","
           + Strings.sqlQuote(questionID)
           + ","
           + (requiredVersion == -1 ? "NULL" : "" + requiredVersion)
           + ","
           + (sectionName == null ? "NULL" : Strings.sqlQuote(sectionName))
           + ");");
 }