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