/**
* 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");
}
/**
* 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");
}
/**
* 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
+ "');");
}
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;
}
/**
* 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)
+ ");");
}
/**
* 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))
+ ");");
}
/**
* 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)
+ ";");
}
/**
* 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");
}
/**
* 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))
+ ");");
}
/**
* 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");
}
/**
* 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");
}
/**
* 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");
}
/**
* 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
+ ");");
}
/**
* 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)
+ ";");
}
/**
* 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
+ ");");
}
/**
* 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);");
}
/**
* 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");
}
/**
* 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))
+ ");");
}