2 // This file is part of Moodle - http://moodle.org/
4 // Moodle is free software: you can redistribute it and/or modify
5 // it under the terms of the GNU General Public License as published by
6 // the Free Software Foundation, either version 3 of the License, or
7 // (at your option) any later version.
9 // Moodle is distributed in the hope that it will be useful,
10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 // GNU General Public License for more details.
14 // You should have received a copy of the GNU General Public License
15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>.
18 * Code for loading and saving question attempts to and from the database.
20 * A note for future reference. This code is pretty efficient but there are two
21 * potential optimisations that could be contemplated, at the cost of making the
24 * 1. (This is the easier one, but probably not worth doing.) In the unit-of-work
25 * save method, we could get all the ids for steps due to be deleted or modified,
26 * and delete all the question_attempt_step_data for all of those steps in one
27 * query. That would save one DB query for each ->stepsupdated. However that number
28 * is 0 except when re-grading, and when regrading, there are many more inserts
29 * into question_attempt_step_data than deletes, so it is really hardly worth it.
31 * 2. A more significant optimisation would be to write an efficient
32 * $DB->insert_records($arrayofrecords) method (for example using functions
33 * like pg_copy_from) and then whenever we save stuff (unit_of_work->save and
34 * insert_questions_usage_by_activity) collect together all the records that
35 * need to be inserted into question_attempt_step_data, and insert them with
36 * a single call to $DB->insert_records. This is likely to be the biggest win.
37 * We do a lot of separate inserts into question_attempt_step_data.
40 * @subpackage questionengine
41 * @copyright 2009 The Open University
42 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
46 defined('MOODLE_INTERNAL') ||
die();
50 * This class controls the loading and saving of question engine data to and from
53 * @copyright 2009 The Open University
54 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
56 class question_engine_data_mapper
{
58 * @var moodle_database normally points to global $DB, but I prefer not to
59 * use globals if I can help it.
64 * @param moodle_database $db a database connectoin. Defaults to global $DB.
66 public function __construct(moodle_database
$db = null) {
76 * Store an entire {@link question_usage_by_activity} in the database,
77 * including all the question_attempts that comprise it.
78 * @param question_usage_by_activity $quba the usage to store.
80 public function insert_questions_usage_by_activity(question_usage_by_activity
$quba) {
81 $record = new stdClass();
82 $record->contextid
= $quba->get_owning_context()->id
;
83 $record->component
= $quba->get_owning_component();
84 $record->preferredbehaviour
= $quba->get_preferred_behaviour();
86 $newid = $this->db
->insert_record('question_usages', $record);
87 $quba->set_id_from_database($newid);
89 foreach ($quba->get_attempt_iterator() as $qa) {
90 $this->insert_question_attempt($qa, $quba->get_owning_context());
95 * Store an entire {@link question_attempt} in the database,
96 * including all the question_attempt_steps that comprise it.
97 * @param question_attempt $qa the question attempt to store.
98 * @param context $context the context of the owning question_usage_by_activity.
100 public function insert_question_attempt(question_attempt
$qa, $context) {
101 $record = new stdClass();
102 $record->questionusageid
= $qa->get_usage_id();
103 $record->slot
= $qa->get_slot();
104 $record->behaviour
= $qa->get_behaviour_name();
105 $record->questionid
= $qa->get_question()->id
;
106 $record->variant
= $qa->get_variant();
107 $record->maxmark
= $qa->get_max_mark();
108 $record->minfraction
= $qa->get_min_fraction();
109 $record->maxfraction
= $qa->get_max_fraction();
110 $record->flagged
= $qa->is_flagged();
111 $record->questionsummary
= $qa->get_question_summary();
112 if (core_text
::strlen($record->questionsummary
) > question_bank
::MAX_SUMMARY_LENGTH
) {
113 // It seems some people write very long quesions! MDL-30760
114 $record->questionsummary
= core_text
::substr($record->questionsummary
,
115 0, question_bank
::MAX_SUMMARY_LENGTH
- 3) . '...';
117 $record->rightanswer
= $qa->get_right_answer_summary();
118 $record->responsesummary
= $qa->get_response_summary();
119 $record->timemodified
= time();
120 $record->id
= $this->db
->insert_record('question_attempts', $record);
121 $qa->set_database_id($record->id
);
123 foreach ($qa->get_step_iterator() as $seq => $step) {
124 $this->insert_question_attempt_step($step, $record->id
, $seq, $context);
129 * Helper method used by insert_question_attempt_step and update_question_attempt_step
130 * @param question_attempt_step $step the step to store.
131 * @param int $questionattemptid the question attept id this step belongs to.
132 * @param int $seq the sequence number of this stop.
133 * @return stdClass data to insert into the database.
135 protected function make_step_record(question_attempt_step
$step, $questionattemptid, $seq) {
136 $record = new stdClass();
137 $record->questionattemptid
= $questionattemptid;
138 $record->sequencenumber
= $seq;
139 $record->state
= (string) $step->get_state();
140 $record->fraction
= $step->get_fraction();
141 $record->timecreated
= $step->get_timecreated();
142 $record->userid
= $step->get_user_id();
147 * Helper method used by insert_question_attempt_step and update_question_attempt_step
148 * @param question_attempt_step $step the step to store.
149 * @param int $stepid the id of the step.
150 * @param context $context the context of the owning question_usage_by_activity.
152 protected function insert_step_data(question_attempt_step
$step, $stepid, $context) {
153 foreach ($step->get_all_data() as $name => $value) {
154 if ($value instanceof question_file_saver
) {
155 $value->save_files($stepid, $context);
157 if ($value instanceof question_response_files
) {
158 $value = (string) $value;
161 $data = new stdClass();
162 $data->attemptstepid
= $stepid;
164 $data->value
= $value;
165 $this->db
->insert_record('question_attempt_step_data', $data, false);
170 * Store a {@link question_attempt_step} in the database.
171 * @param question_attempt_step $step the step to store.
172 * @param int $questionattemptid the question attept id this step belongs to.
173 * @param int $seq the sequence number of this stop.
174 * @param context $context the context of the owning question_usage_by_activity.
176 public function insert_question_attempt_step(question_attempt_step
$step,
177 $questionattemptid, $seq, $context) {
179 $record = $this->make_step_record($step, $questionattemptid, $seq);
180 $record->id
= $this->db
->insert_record('question_attempt_steps', $record);
182 $this->insert_step_data($step, $record->id
, $context);
186 * Update a {@link question_attempt_step} in the database.
187 * @param question_attempt_step $qa the step to store.
188 * @param int $questionattemptid the question attept id this step belongs to.
189 * @param int $seq the sequence number of this stop.
190 * @param context $context the context of the owning question_usage_by_activity.
192 public function update_question_attempt_step(question_attempt_step
$step,
193 $questionattemptid, $seq, $context) {
195 $record = $this->make_step_record($step, $questionattemptid, $seq);
196 $record->id
= $step->get_id();
197 $this->db
->update_record('question_attempt_steps', $record);
199 $this->db
->delete_records('question_attempt_step_data',
200 array('attemptstepid' => $record->id
));
201 $this->insert_step_data($step, $record->id
, $context);
205 * Load a {@link question_attempt_step} from the database.
206 * @param int $stepid the id of the step to load.
207 * @param question_attempt_step the step that was loaded.
209 public function load_question_attempt_step($stepid) {
210 $records = $this->db
->get_recordset_sql("
213 COALLESCE(q.qtype, 'missingtype') AS qtype,
214 qas.id AS attemptstepid,
215 qas.questionattemptid,
224 FROM {question_attempt_steps} qas
225 JOIN {question_attempts} qa ON qa.id = qas.questionattemptid
226 JOIN {question_usages} quba ON quba.id = qa.questionusageid
227 LEFT JOIN {question} q ON q.id = qa.questionid
228 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
232 ", array('stepid' => $stepid));
234 if (!$records->valid()) {
235 throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
238 $step = question_attempt_step
::load_from_records($records, $stepid);
245 * Load a {@link question_attempt} from the database, including all its
247 * @param int $questionattemptid the id of the question attempt to load.
248 * @param question_attempt the question attempt that was loaded.
250 public function load_question_attempt($questionattemptid) {
251 $records = $this->db
->get_recordset_sql("
254 quba.preferredbehaviour,
255 qa.id AS questionattemptid,
269 qas.id AS attemptstepid,
278 FROM {question_attempts} qa
279 JOIN {question_usages} quba ON quba.id = qa.questionusageid
280 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
281 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
284 qa.id = :questionattemptid
288 ", array('questionattemptid' => $questionattemptid));
290 if (!$records->valid()) {
291 throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
294 $record = $records->current();
295 $qa = question_attempt
::load_from_records($records, $questionattemptid,
296 new question_usage_null_observer(), $record->preferredbehaviour
);
303 * Load a {@link question_usage_by_activity} from the database, including
304 * all its {@link question_attempt}s and all their steps.
305 * @param int $qubaid the id of the usage to load.
306 * @param question_usage_by_activity the usage that was loaded.
308 public function load_questions_usage_by_activity($qubaid) {
309 $records = $this->db
->get_recordset_sql("
314 quba.preferredbehaviour,
315 qa.id AS questionattemptid,
329 qas.id AS attemptstepid,
338 FROM {question_usages} quba
339 LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
340 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
341 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
349 ", array('qubaid' => $qubaid));
351 if (!$records->valid()) {
352 throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
355 $quba = question_usage_by_activity
::load_from_records($records, $qubaid);
362 * Load information about the latest state of each question from the database.
364 * @param qubaid_condition $qubaids used to restrict which usages are included
365 * in the query. See {@link qubaid_condition}.
366 * @param array $slots A list of slots for the questions you want to konw about.
367 * @param string|null $fields
368 * @return array of records. See the SQL in this function to see the fields available.
370 public function load_questions_usages_latest_steps(qubaid_condition
$qubaids, $slots, $fields = null) {
371 list($slottest, $params) = $this->db
->get_in_or_equal($slots, SQL_PARAMS_NAMED
, 'slot');
373 if ($fields === null) {
375 qa.id AS questionattemptid,
389 qas.id AS attemptstepid,
398 $records = $this->db
->get_records_sql("
402 FROM {$qubaids->from_question_attempts('qa')}
403 JOIN {question_attempt_steps} qas ON
404 qas.id = {$this->latest_step_for_qa_subquery()}
407 {$qubaids->where()} AND
409 ", $params +
$qubaids->from_where_params());
415 * Load summary information about the state of each question in a group of
416 * attempts. This is used, for example, by the quiz manual grading report,
417 * to show how many attempts at each question need to be graded.
419 * @param qubaid_condition $qubaids used to restrict which usages are included
420 * in the query. See {@link qubaid_condition}.
421 * @param array $slots A list of slots for the questions you want to konw about.
422 * @return array The array keys are slot,qestionid. The values are objects with
423 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
424 * $manuallygraded and $all.
426 public function load_questions_usages_question_state_summary(
427 qubaid_condition
$qubaids, $slots) {
428 list($slottest, $params) = $this->db
->get_in_or_equal($slots, SQL_PARAMS_NAMED
, 'slot');
430 $rs = $this->db
->get_recordset_sql("
436 {$this->full_states_to_summary_state_sql()}
438 COUNT(1) AS numattempts
440 FROM {$qubaids->from_question_attempts('qa')}
441 JOIN {question_attempt_steps} qas ON
442 qas.id = {$this->latest_step_for_qa_subquery()}
443 JOIN {question} q ON q.id = qa.questionid
446 {$qubaids->where()} AND
455 {$this->full_states_to_summary_state_sql()}
463 ", $params +
$qubaids->from_where_params());
466 foreach ($rs as $row) {
467 $index = $row->slot
. ',' . $row->questionid
;
469 if (!array_key_exists($index, $results)) {
470 $res = new stdClass();
471 $res->slot
= $row->slot
;
472 $res->questionid
= $row->questionid
;
473 $res->name
= $row->name
;
474 $res->inprogress
= 0;
475 $res->needsgrading
= 0;
476 $res->autograded
= 0;
477 $res->manuallygraded
= 0;
479 $results[$index] = $res;
482 $results[$index]->{$row->summarystate
} = $row->numattempts
;
483 $results[$index]->all +
= $row->numattempts
;
491 * Get a list of usage ids where the question with slot $slot, and optionally
492 * also with question id $questionid, is in summary state $summarystate. Also
493 * return the total count of such states.
495 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
496 * $limitnum. A special value 'random' can be passed as $orderby, in which case
497 * $limitfrom is ignored.
499 * @param qubaid_condition $qubaids used to restrict which usages are included
500 * in the query. See {@link qubaid_condition}.
501 * @param int $slot The slot for the questions you want to konw about.
502 * @param int $questionid (optional) Only return attempts that were of this specific question.
503 * @param string $summarystate the summary state of interest, or 'all'.
504 * @param string $orderby the column to order by.
505 * @param array $params any params required by any of the SQL fragments.
506 * @param int $limitfrom implements paging of the results.
507 * Ignored if $orderby = random or $limitnum is null.
508 * @param int $limitnum implements paging of the results. null = all.
509 * @return array with two elements, an array of usage ids, and a count of the total number.
511 public function load_questions_usages_where_question_in_state(
512 qubaid_condition
$qubaids, $summarystate, $slot, $questionid = null,
513 $orderby = 'random', $params, $limitfrom = 0, $limitnum = null) {
517 $extrawhere .= ' AND qa.questionid = :questionid';
518 $params['questionid'] = $questionid;
520 if ($summarystate != 'all') {
521 list($test, $sparams) = $this->in_summary_state_test($summarystate);
522 $extrawhere .= ' AND qas.state ' . $test;
526 if ($orderby == 'random') {
528 } else if ($orderby) {
529 $sqlorderby = 'ORDER BY ' . $orderby;
534 // We always want the total count, as well as the partcular list of ids,
535 // based on the paging and sort order. Becuase the list of ids is never
536 // going to be too rediculously long. My worst-case scenario is
537 // 10,000 students in the coures, each doing 5 quiz attempts. That
538 // is a 50,000 element int => int array, which PHP seems to use 5MB
539 // memeory to store on a 64 bit server.
540 $params +
= $qubaids->from_where_params();
541 $params['slot'] = $slot;
542 $qubaids = $this->db
->get_records_sql_menu("
547 FROM {$qubaids->from_question_attempts('qa')}
548 JOIN {question_attempt_steps} qas ON
549 qas.id = {$this->latest_step_for_qa_subquery()}
550 JOIN {question} q ON q.id = qa.questionid
553 {$qubaids->where()} AND
560 $qubaids = array_keys($qubaids);
561 $count = count($qubaids);
563 if ($orderby == 'random') {
568 if (!is_null($limitnum)) {
569 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
572 return array($qubaids, $count);
576 * Load a {@link question_usage_by_activity} from the database, including
577 * all its {@link question_attempt}s and all their steps.
578 * @param qubaid_condition $qubaids used to restrict which usages are included
579 * in the query. See {@link qubaid_condition}.
580 * @param array $slots if null, load info for all quesitions, otherwise only
581 * load the averages for the specified questions.
583 public function load_average_marks(qubaid_condition
$qubaids, $slots = null) {
584 if (!empty($slots)) {
585 list($slottest, $slotsparams) = $this->db
->get_in_or_equal(
586 $slots, SQL_PARAMS_NAMED
, 'slot');
587 $slotwhere = " AND qa.slot $slottest";
593 list($statetest, $stateparams) = $this->db
->get_in_or_equal(array(
594 (string) question_state
::$gaveup,
595 (string) question_state
::$gradedwrong,
596 (string) question_state
::$gradedpartial,
597 (string) question_state
::$gradedright,
598 (string) question_state
::$mangaveup,
599 (string) question_state
::$mangrwrong,
600 (string) question_state
::$mangrpartial,
601 (string) question_state
::$mangrright), SQL_PARAMS_NAMED
, 'st');
603 return $this->db
->get_records_sql("
606 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
607 COUNT(1) AS numaveraged
609 FROM {$qubaids->from_question_attempts('qa')}
610 JOIN {question_attempt_steps} qas ON
611 qas.id = {$this->latest_step_for_qa_subquery()}
616 AND qas.state $statetest
621 ", $slotsparams +
$stateparams +
$qubaids->from_where_params());
625 * Load a {@link question_attempt} from the database, including all its
627 * @param int $questionid the question to load all the attempts fors.
628 * @param qubaid_condition $qubaids used to restrict which usages are included
629 * in the query. See {@link qubaid_condition}.
630 * @return array of question_attempts.
632 public function load_attempts_at_question($questionid, qubaid_condition
$qubaids) {
633 $params = $qubaids->from_where_params();
634 $params['questionid'] = $questionid;
636 $records = $this->db
->get_recordset_sql("
639 quba.preferredbehaviour,
640 qa.id AS questionattemptid,
654 qas.id AS attemptstepid,
663 FROM {$qubaids->from_question_attempts('qa')}
664 JOIN {question_usages} quba ON quba.id = qa.questionusageid
665 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
666 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
669 {$qubaids->where()} AND
670 qa.questionid = :questionid
678 $questionattempts = array();
679 while ($records->valid()) {
680 $record = $records->current();
681 $questionattempts[$record->questionattemptid
] =
682 question_attempt
::load_from_records($records,
683 $record->questionattemptid
, new question_usage_null_observer(),
684 $record->preferredbehaviour
);
688 return $questionattempts;
692 * Update a question_usages row to refect any changes in a usage (but not
693 * any of its question_attempts.
694 * @param question_usage_by_activity $quba the usage that has changed.
696 public function update_questions_usage_by_activity(question_usage_by_activity
$quba) {
697 $record = new stdClass();
698 $record->id
= $quba->get_id();
699 $record->contextid
= $quba->get_owning_context()->id
;
700 $record->component
= $quba->get_owning_component();
701 $record->preferredbehaviour
= $quba->get_preferred_behaviour();
703 $this->db
->update_record('question_usages', $record);
707 * Update a question_attempts row to refect any changes in a question_attempt
708 * (but not any of its steps).
709 * @param question_attempt $qa the question attempt that has changed.
711 public function update_question_attempt(question_attempt
$qa) {
712 $record = new stdClass();
713 $record->id
= $qa->get_database_id();
714 $record->maxmark
= $qa->get_max_mark();
715 $record->minfraction
= $qa->get_min_fraction();
716 $record->maxfraction
= $qa->get_max_fraction();
717 $record->flagged
= $qa->is_flagged();
718 $record->questionsummary
= $qa->get_question_summary();
719 $record->rightanswer
= $qa->get_right_answer_summary();
720 $record->responsesummary
= $qa->get_response_summary();
721 $record->timemodified
= time();
723 $this->db
->update_record('question_attempts', $record);
727 * Delete a question_usage_by_activity and all its associated
728 * {@link question_attempts} and {@link question_attempt_steps} from the
730 * @param qubaid_condition $qubaids identifies which question useages to delete.
732 public function delete_questions_usage_by_activities(qubaid_condition
$qubaids) {
733 $where = "qa.questionusageid {$qubaids->usage_id_in()}";
734 $params = $qubaids->usage_id_in_params();
736 $contextids = $this->db
->get_records_sql_menu("
737 SELECT DISTINCT contextid, 1
738 FROM {question_usages}
739 WHERE id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
740 foreach ($contextids as $contextid => $notused) {
741 $this->delete_response_files($contextid, "IN (
743 FROM {question_attempts} qa
744 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
745 WHERE $where)", $params);
748 if ($this->db
->get_dbfamily() == 'mysql') {
749 $this->delete_usage_records_for_mysql($qubaids);
753 $this->db
->delete_records_select('question_attempt_step_data', "attemptstepid IN (
755 FROM {question_attempts} qa
756 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
757 WHERE $where)", $params);
759 $this->db
->delete_records_select('question_attempt_steps', "questionattemptid IN (
761 FROM {question_attempts} qa
762 WHERE $where)", $params);
764 $this->db
->delete_records_select('question_attempts',
765 "{question_attempts}.questionusageid {$qubaids->usage_id_in()}",
766 $qubaids->usage_id_in_params());
768 $this->db
->delete_records_select('question_usages',
769 "{question_usages}.id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
773 * This function is a work-around for poor MySQL performance with
774 * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
775 * syntax to get good performance. See MDL-29520.
776 * @param qubaid_condition $qubaids identifies which question useages to delete.
778 protected function delete_usage_records_for_mysql(qubaid_condition
$qubaids) {
779 $qubaidtest = $qubaids->usage_id_in();
780 if (strpos($qubaidtest, 'question_usages') !== false &&
781 strpos($qubaidtest, 'IN (SELECT') === 0) {
782 // This horrible hack is required by MDL-29847. It comes from
783 // http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
784 $qubaidtest = 'IN (SELECT * FROM ' . substr($qubaidtest, 3) . ' AS hack_subquery_alias)';
787 // TODO once MDL-29589 is fixed, eliminate this method, and instead use the new $DB API.
789 DELETE qu, qa, qas, qasd
790 FROM {question_usages} qu
791 JOIN {question_attempts} qa ON qa.questionusageid = qu.id
792 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
793 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
794 WHERE qu.id ' . $qubaidtest,
795 $qubaids->usage_id_in_params());
799 * Delete all the steps for a question attempt.
800 * @param int $qaids question_attempt id.
801 * @param context $context the context that the $quba belongs to.
803 public function delete_steps($stepids, $context) {
804 if (empty($stepids)) {
807 list($test, $params) = $this->db
->get_in_or_equal($stepids, SQL_PARAMS_NAMED
);
809 $this->delete_response_files($context->id
, $test, $params);
811 $this->db
->delete_records_select('question_attempt_step_data',
812 "attemptstepid $test", $params);
813 $this->db
->delete_records_select('question_attempt_steps',
814 "id $test", $params);
818 * Delete all the files belonging to the response variables in the gives
819 * question attempt steps.
820 * @param int $contextid the context these attempts belong to.
821 * @param string $itemidstest a bit of SQL that can be used in a
822 * WHERE itemid $itemidstest clause. Must use named params.
823 * @param array $params any query parameters used in $itemidstest.
825 protected function delete_response_files($contextid, $itemidstest, $params) {
826 $fs = get_file_storage();
827 foreach (question_engine
::get_all_response_file_areas() as $filearea) {
828 $fs->delete_area_files_select($contextid, 'question', $filearea,
829 $itemidstest, $params);
834 * Delete all the previews for a given question.
835 * @param int $questionid question id.
837 public function delete_previews($questionid) {
838 $previews = $this->db
->get_records_sql_menu("
839 SELECT DISTINCT quba.id, 1
840 FROM {question_usages} quba
841 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
842 WHERE quba.component = 'core_question_preview' AND
843 qa.questionid = ?", array($questionid));
844 if (empty($previews)) {
847 $this->delete_questions_usage_by_activities(new qubaid_list($previews));
851 * Update the flagged state of a question in the database.
852 * @param int $qubaid the question usage id.
853 * @param int $questionid the question id.
854 * @param int $sessionid the question_attempt id.
855 * @param bool $newstate the new state of the flag. true = flagged.
857 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
858 if (!$this->db
->record_exists('question_attempts', array('id' => $qaid,
859 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
860 throw new moodle_exception('errorsavingflags', 'question');
863 $this->db
->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
867 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
868 * column to a summary state. Use this like
869 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
870 * @param string SQL fragment.
872 protected function full_states_to_summary_state_sql() {
874 foreach (question_state
::get_all() as $state) {
875 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
881 * Get the SQL needed to test that question_attempt_steps.state is in a
882 * state corresponding to $summarystate.
883 * @param string $summarystate one of
884 * inprogress, needsgrading, manuallygraded or autograded
885 * @param bool $equal if false, do a NOT IN test. Default true.
886 * @return string SQL fragment.
888 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
889 $states = question_state
::get_all_for_summary_state($summarystate);
890 return $this->db
->get_in_or_equal(array_map('strval', $states),
891 SQL_PARAMS_NAMED
, $prefix, $equal);
895 * Change the maxmark for the question_attempt with number in usage $slot
896 * for all the specified question_attempts.
897 * @param qubaid_condition $qubaids Selects which usages are updated.
898 * @param int $slot the number is usage to affect.
899 * @param number $newmaxmark the new max mark to set.
901 public function set_max_mark_in_attempts(qubaid_condition
$qubaids, $slot, $newmaxmark) {
902 $this->db
->set_field_select('question_attempts', 'maxmark', $newmaxmark,
903 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
904 $qubaids->usage_id_in_params() +
array('slot' => $slot));
908 * Return a subquery that computes the sum of the marks for all the questions
909 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
912 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
915 * @param string $qubaid SQL fragment that controls which usage is summed.
916 * This will normally be the name of a column in the outer query. Not that this
917 * SQL fragment must not contain any placeholders.
918 * @return string SQL code for the subquery.
920 public function sum_usage_marks_subquery($qubaid) {
921 // To explain the COALESCE in the following SQL: SUM(lots of NULLs) gives
922 // NULL, while SUM(one 0.0 and lots of NULLS) gives 0.0. We don't want that.
923 // We always want to return a number, so the COALESCE is there to turn the
924 // NULL total into a 0.
925 return "SELECT COALESCE(SUM(qa.maxmark * qas.fraction), 0)
926 FROM {question_attempts} qa
927 JOIN {question_attempt_steps} qas ON qas.id = (
928 SELECT MAX(summarks_qas.id)
929 FROM {question_attempt_steps} summarks_qas
930 WHERE summarks_qas.questionattemptid = qa.id
932 WHERE qa.questionusageid = $qubaid
934 WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1
940 * Get a subquery that returns the latest step of every qa in some qubas.
941 * Currently, this is only used by the quiz reports. See
942 * {@link quiz_attempts_report_table::add_latest_state_join()}.
943 * @param string $alias alias to use for this inline-view.
944 * @param qubaid_condition $qubaids restriction on which question_usages we
945 * are interested in. This is important for performance.
946 * @return array with two elements, the SQL fragment and any params requried.
948 public function question_attempt_latest_state_view($alias, qubaid_condition
$qubaids) {
950 SELECT {$alias}qa.id AS questionattemptid,
951 {$alias}qa.questionusageid,
953 {$alias}qa.behaviour,
954 {$alias}qa.questionid,
957 {$alias}qa.minfraction,
958 {$alias}qa.maxfraction,
960 {$alias}qa.questionsummary,
961 {$alias}qa.rightanswer,
962 {$alias}qa.responsesummary,
963 {$alias}qa.timemodified,
964 {$alias}qas.id AS attemptstepid,
965 {$alias}qas.sequencenumber,
967 {$alias}qas.fraction,
968 {$alias}qas.timecreated,
971 FROM {$qubaids->from_question_attempts($alias . 'qa')}
972 JOIN {question_attempt_steps} {$alias}qas ON
973 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
974 WHERE {$qubaids->where()}
975 ) $alias", $qubaids->from_where_params());
978 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
981 FROM {question_attempt_steps}
982 WHERE questionattemptid = $questionattemptid
987 * @param array $questionids of question ids.
988 * @param qubaid_condition $qubaids ids of the usages to consider.
989 * @return boolean whether any of these questions are being used by any of
992 public function questions_in_use(array $questionids, qubaid_condition
$qubaids) {
993 list($test, $params) = $this->db
->get_in_or_equal($questionids);
994 return $this->db
->record_exists_select('question_attempts',
995 'questionid ' . $test . ' AND questionusageid ' .
996 $qubaids->usage_id_in(), $params +
$qubaids->usage_id_in_params());
1002 * Implementation of the unit of work pattern for the question engine.
1004 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
1005 * changes to a {@link question_usage_by_activity}, and its constituent parts,
1006 * so that the changes can be saved to the database when {@link save()} is called.
1008 * @copyright 2009 The Open University
1009 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1011 class question_engine_unit_of_work
implements question_usage_observer
{
1012 /** @var question_usage_by_activity the usage being tracked. */
1015 /** @var boolean whether any of the fields of the usage have been changed. */
1016 protected $modified = false;
1019 * @var array list of slot => {@link question_attempt}s that
1020 * were already in the usage, and which have been modified.
1022 protected $attemptsmodified = array();
1025 * @var array list of slot => {@link question_attempt}s that
1026 * have been added to the usage.
1028 protected $attemptsadded = array();
1031 * @var array of array(question_attempt_step, question_attempt id, seq number)
1032 * of steps that have been added to question attempts in this usage.
1034 protected $stepsadded = array();
1037 * @var array of array(question_attempt_step, question_attempt id, seq number)
1038 * of steps that have been modified in their attempt.
1040 protected $stepsmodified = array();
1043 * @var array list of question_attempt_step.id => question_attempt_step of steps
1044 * that were previously stored in the database, but which are no longer required.
1046 protected $stepsdeleted = array();
1050 * @param question_usage_by_activity $quba the usage to track.
1052 public function __construct(question_usage_by_activity
$quba) {
1053 $this->quba
= $quba;
1056 public function notify_modified() {
1057 $this->modified
= true;
1060 public function notify_attempt_modified(question_attempt
$qa) {
1061 $slot = $qa->get_slot();
1062 if (!array_key_exists($slot, $this->attemptsadded
)) {
1063 $this->attemptsmodified
[$slot] = $qa;
1067 public function notify_attempt_added(question_attempt
$qa) {
1068 $this->attemptsadded
[$qa->get_slot()] = $qa;
1071 public function notify_step_added(question_attempt_step
$step, question_attempt
$qa, $seq) {
1072 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1076 if (($key = $this->is_step_added($step)) !== false) {
1080 if (($key = $this->is_step_modified($step)) !== false) {
1081 throw new coding_exception('Cannot add a step that has already been modified.');
1084 if (($key = $this->is_step_deleted($step)) !== false) {
1085 unset($this->stepsdeleted
[$step->get_id()]);
1086 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1090 $stepid = $step->get_id();
1092 if (array_key_exists($stepid, $this->stepsdeleted
)) {
1093 unset($this->stepsdeleted
[$stepid]);
1095 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1098 $this->stepsadded
[] = array($step, $qa->get_database_id(), $seq);
1102 public function notify_step_modified(question_attempt_step
$step, question_attempt
$qa, $seq) {
1103 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1107 if (($key = $this->is_step_added($step)) !== false) {
1111 if (($key = $this->is_step_deleted($step)) !== false) {
1112 throw new coding_exception('Cannot modify a step after it has been deleted.');
1115 $stepid = $step->get_id();
1116 if (empty($stepid)) {
1117 throw new coding_exception('Cannot modify a step that has never been stored in the database.');
1120 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1123 public function notify_step_deleted(question_attempt_step
$step, question_attempt
$qa) {
1124 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1128 if (($key = $this->is_step_added($step)) !== false) {
1129 unset($this->stepsadded
[$key]);
1133 if (($key = $this->is_step_modified($step)) !== false) {
1134 unset($this->stepsmodified
[$key]);
1137 $stepid = $step->get_id();
1138 if (empty($stepid)) {
1139 return; // Was never in the database.
1142 $this->stepsdeleted
[$stepid] = $step;
1146 * @param question_attempt_step $step a step
1147 * @return int|false if the step is in the list of steps to be added, return
1148 * the key, otherwise return false.
1150 protected function is_step_added(question_attempt_step
$step) {
1151 foreach ($this->stepsadded
as $key => $data) {
1152 list($addedstep, $qaid, $seq) = $data;
1153 if ($addedstep === $step) {
1161 * @param question_attempt_step $step a step
1162 * @return int|false if the step is in the list of steps to be modified, return
1163 * the key, otherwise return false.
1165 protected function is_step_modified(question_attempt_step
$step) {
1166 foreach ($this->stepsmodified
as $key => $data) {
1167 list($modifiedstep, $qaid, $seq) = $data;
1168 if ($modifiedstep === $step) {
1176 * @param question_attempt_step $step a step
1177 * @return bool whether the step is in the list of steps to be deleted.
1179 protected function is_step_deleted(question_attempt_step
$step) {
1180 foreach ($this->stepsdeleted
as $deletedstep) {
1181 if ($deletedstep === $step) {
1189 * Write all the changes we have recorded to the database.
1190 * @param question_engine_data_mapper $dm the mapper to use to update the database.
1192 public function save(question_engine_data_mapper
$dm) {
1193 $dm->delete_steps(array_keys($this->stepsdeleted
), $this->quba
->get_owning_context());
1195 foreach ($this->stepsmodified
as $stepinfo) {
1196 list($step, $questionattemptid, $seq) = $stepinfo;
1197 $dm->update_question_attempt_step($step, $questionattemptid, $seq,
1198 $this->quba
->get_owning_context());
1201 foreach ($this->stepsadded
as $stepinfo) {
1202 list($step, $questionattemptid, $seq) = $stepinfo;
1203 $dm->insert_question_attempt_step($step, $questionattemptid, $seq,
1204 $this->quba
->get_owning_context());
1207 foreach ($this->attemptsadded
as $qa) {
1208 $dm->insert_question_attempt($qa, $this->quba
->get_owning_context());
1211 foreach ($this->attemptsmodified
as $qa) {
1212 $dm->update_question_attempt($qa);
1215 if ($this->modified
) {
1216 $dm->update_questions_usage_by_activity($this->quba
);
1223 * The interface implemented by {@link question_file_saver} and {@link question_file_loader}.
1225 * @copyright 2012 The Open University
1226 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1228 interface question_response_files
{
1230 * Get the files that were submitted.
1231 * @return array of stored_files objects.
1233 public function get_files();
1238 * This class represents the promise to save some files from a particular draft
1239 * file area into a particular file area. It is used beause the necessary
1240 * information about what to save is to hand in the
1241 * {@link question_attempt::process_response_files()} method, but we don't know
1242 * if this question attempt will actually be saved in the database until later,
1243 * when the {@link question_engine_unit_of_work} is saved, if it is.
1245 * @copyright 2011 The Open University
1246 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1248 class question_file_saver
implements question_response_files
{
1249 /** @var int the id of the draft file area to save files from. */
1250 protected $draftitemid;
1251 /** @var string the owning component name. */
1252 protected $component;
1253 /** @var string the file area name. */
1254 protected $filearea;
1257 * @var string the value to store in the question_attempt_step_data to
1258 * represent these files.
1260 protected $value = null;
1264 * @param int $draftitemid the draft area to save the files from.
1265 * @param string $component the component for the file area to save into.
1266 * @param string $filearea the name of the file area to save into.
1268 public function __construct($draftitemid, $component, $filearea, $text = null) {
1269 $this->draftitemid
= $draftitemid;
1270 $this->component
= $component;
1271 $this->filearea
= $filearea;
1272 $this->value
= $this->compute_value($draftitemid, $text);
1276 * Compute the value that should be stored in the question_attempt_step_data
1277 * table. Contains a hash that (almost) uniquely encodes all the files.
1278 * @param int $draftitemid the draft file area itemid.
1279 * @param string $text optional content containing file links.
1281 protected function compute_value($draftitemid, $text) {
1284 $fs = get_file_storage();
1285 $usercontext = context_user
::instance($USER->id
);
1287 $files = $fs->get_area_files($usercontext->id
, 'user', 'draft',
1288 $draftitemid, 'sortorder, filepath, filename', false);
1291 foreach ($files as $file) {
1292 $string .= $file->get_filepath() . $file->get_filename() . '|' .
1293 $file->get_contenthash() . '|';
1295 $hash = md5($string);
1297 if (is_null($text)) {
1305 // We add the file hash so a simple string comparison will say if the
1306 // files have been changed. First strip off any existing file hash.
1308 $text = preg_replace('/\s*<!-- File hash: \w+ -->\s*$/', '', $text);
1309 $text = file_rewrite_urls_to_pluginfile($text, $draftitemid);
1311 $text .= '<!-- File hash: ' . $hash . ' -->';
1317 public function __toString() {
1318 return $this->value
;
1322 * Actually save the files.
1323 * @param integer $itemid the item id for the file area to save into.
1325 public function save_files($itemid, $context) {
1326 file_save_draft_area_files($this->draftitemid
, $context->id
,
1327 $this->component
, $this->filearea
, $itemid);
1331 * Get the files that were submitted.
1332 * @return array of stored_files objects.
1334 public function get_files() {
1337 $fs = get_file_storage();
1338 $usercontext = context_user
::instance($USER->id
);
1340 return $fs->get_area_files($usercontext->id
, 'user', 'draft',
1341 $this->draftitemid
, 'sortorder, filepath, filename', false);
1347 * This class is the mirror image of {@link question_file_saver}. It allows
1348 * files to be accessed again later (e.g. when re-grading) using that same
1349 * API as when doing the original grading.
1351 * @copyright 2012 The Open University
1352 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1354 class question_file_loader
implements question_response_files
{
1355 /** @var question_attempt_step the step that these files belong to. */
1358 /** @var string the field name for these files - which is used to construct the file area name. */
1362 * @var string the value to stored in the question_attempt_step_data to
1363 * represent these files.
1367 /** @var int the context id that the files belong to. */
1368 protected $contextid;
1372 * @param question_attempt_step $step the step that these files belong to.
1373 * @param string $name string the field name for these files - which is used to construct the file area name.
1374 * @param string $value the value to stored in the question_attempt_step_data to
1375 * represent these files.
1376 * @param int $contextid the context id that the files belong to.
1378 public function __construct(question_attempt_step
$step, $name, $value, $contextid) {
1379 $this->step
= $step;
1380 $this->name
= $name;
1381 $this->value
= $value;
1382 $this->contextid
= $contextid;
1385 public function __toString() {
1386 return $this->value
;
1390 * Get the files that were submitted.
1391 * @return array of stored_files objects.
1393 public function get_files() {
1394 return $this->step
->get_qt_files($this->name
, $this->contextid
);
1398 * Copy these files into a draft area, and return the corresponding
1399 * {@link question_file_saver} that can save them again.
1401 * This is used by {@link question_attempt::start_based_on()}, which is used
1402 * (for example) by the quizzes 'Each attempt builds on last' feature.
1404 * @return question_file_saver that can re-save these files again.
1406 public function get_question_file_saver() {
1408 // There are three possibilities here for what $value will look like:
1409 // 1) some HTML content followed by an MD5 hash in a HTML comment;
1410 // 2) a plain MD5 hash;
1411 // 3) or some real content, without any hash.
1412 // The problem is that 3) is ambiguous in the case where a student writes
1413 // a response that looks exactly like an MD5 hash. For attempts made now,
1414 // we avoid case 3) by always going for case 1) or 2) (except when the
1415 // response is blank. However, there may be case 3) data in the database
1416 // so we need to handle it as best we can.
1417 if (preg_match('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', $this->value
)) {
1418 $value = preg_replace('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', '', $this->value
);
1420 } else if (preg_match('/^[0-9a-zA-Z]{32}$/', $this->value
)) {
1424 $value = $this->value
;
1427 list($draftid, $text) = $this->step
->prepare_response_files_draft_itemid_with_text(
1428 $this->name
, $this->contextid
, $value);
1429 return new question_file_saver($draftid, 'question', 'response_' . $this->name
, $text);
1435 * This class represents a restriction on the set of question_usage ids to include
1436 * in a larger database query. Depending of the how you are going to restrict the
1437 * list of usages, construct an appropriate subclass.
1439 * If $qubaids is an instance of this class, example usage might be
1441 * SELECT qa.id, qa.maxmark
1442 * FROM $qubaids->from_question_attempts('qa')
1443 * WHERE $qubaids->where() AND qa.slot = 1
1445 * @copyright 2010 The Open University
1446 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1448 abstract class qubaid_condition
{
1451 * @return string the SQL that needs to go in the FROM clause when trying
1452 * to select records from the 'question_attempts' table based on the
1455 public abstract function from_question_attempts($alias);
1457 /** @return string the SQL that needs to go in the where clause. */
1458 public abstract function where();
1461 * @return the params needed by a query that uses
1462 * {@link from_question_attempts()} and {@link where()}.
1464 public abstract function from_where_params();
1467 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
1468 * This method returns the "IN (...)" part.
1470 public abstract function usage_id_in();
1473 * @return the params needed by a query that uses {@link usage_id_in()}.
1475 public abstract function usage_id_in_params();
1478 * @return string 40-character hash code that uniquely identifies the combination of properties and class name of this qubaid
1481 public function get_hash_code() {
1482 return sha1(serialize($this));
1488 * This class represents a restriction on the set of question_usage ids to include
1489 * in a larger database query based on an explicit list of ids.
1491 * @copyright 2010 The Open University
1492 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1494 class qubaid_list
extends qubaid_condition
{
1495 /** @var array of ids. */
1497 protected $columntotest = null;
1502 * @param array $qubaids of question usage ids.
1504 public function __construct(array $qubaids) {
1505 $this->qubaids
= $qubaids;
1508 public function from_question_attempts($alias) {
1509 $this->columntotest
= $alias . '.questionusageid';
1510 return '{question_attempts} ' . $alias;
1513 public function where() {
1516 if (is_null($this->columntotest
)) {
1517 throw new coding_exception('Must call from_question_attempts before where().');
1519 if (empty($this->qubaids
)) {
1520 $this->params
= array();
1524 return $this->columntotest
. ' ' . $this->usage_id_in();
1527 public function from_where_params() {
1528 return $this->params
;
1531 public function usage_id_in() {
1534 if (empty($this->qubaids
)) {
1535 $this->params
= array();
1538 list($where, $this->params
) = $DB->get_in_or_equal(
1539 $this->qubaids
, SQL_PARAMS_NAMED
, 'qubaid');
1543 public function usage_id_in_params() {
1544 return $this->params
;
1550 * This class represents a restriction on the set of question_usage ids to include
1551 * in a larger database query based on JOINing to some other tables.
1553 * The general form of the query is something like
1555 * SELECT qa.id, qa.maxmark
1557 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
1558 * WHERE $where AND qa.slot = 1
1560 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1562 * @copyright 2010 The Open University
1563 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1565 class qubaid_join
extends qubaid_condition
{
1567 public $usageidcolumn;
1572 * Constructor. The meaning of the arguments is explained in the class comment.
1573 * @param string $from SQL fragemnt to go in the FROM clause.
1574 * @param string $usageidcolumn the column in $from that should be
1575 * made equal to the usageid column in the JOIN clause.
1576 * @param string $where SQL fragment to go in the where clause.
1577 * @param array $params required by the SQL. You must use named parameters.
1579 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1580 $this->from
= $from;
1581 $this->usageidcolumn
= $usageidcolumn;
1582 $this->params
= $params;
1583 if (empty($where)) {
1586 $this->where
= $where;
1589 public function from_question_attempts($alias) {
1591 JOIN {question_attempts} {$alias} ON " .
1592 "{$alias}.questionusageid = $this->usageidcolumn";
1595 public function where() {
1596 return $this->where
;
1599 public function from_where_params() {
1600 return $this->params
;
1603 public function usage_id_in() {
1604 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1607 public function usage_id_in_params() {
1608 return $this->params
;