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($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->flagged
= $qa->is_flagged();
110 $record->questionsummary
= $qa->get_question_summary();
111 if (textlib_get_instance()->strlen($record->questionsummary
) > question_bank
::MAX_SUMMARY_LENGTH
) {
112 // It seems some people write very long quesions! MDL-30760
113 $record->questionsummary
= textlib_get_instance()->substr($record->questionsummary
,
114 0, question_bank
::MAX_SUMMARY_LENGTH
- 3) . '...';
116 $record->rightanswer
= $qa->get_right_answer_summary();
117 $record->responsesummary
= $qa->get_response_summary();
118 $record->timemodified
= time();
119 $record->id
= $this->db
->insert_record('question_attempts', $record);
121 foreach ($qa->get_step_iterator() as $seq => $step) {
122 $this->insert_question_attempt_step($step, $record->id
, $seq, $context);
127 * Helper method used by insert_question_attempt_step and update_question_attempt_step
128 * @param question_attempt_step $step the step to store.
129 * @param int $questionattemptid the question attept id this step belongs to.
130 * @param int $seq the sequence number of this stop.
131 * @return stdClass data to insert into the database.
133 protected function make_step_record(question_attempt_step
$step, $questionattemptid, $seq) {
134 $record = new stdClass();
135 $record->questionattemptid
= $questionattemptid;
136 $record->sequencenumber
= $seq;
137 $record->state
= (string) $step->get_state();
138 $record->fraction
= $step->get_fraction();
139 $record->timecreated
= $step->get_timecreated();
140 $record->userid
= $step->get_user_id();
145 * Helper method used by insert_question_attempt_step and update_question_attempt_step
146 * @param question_attempt_step $step the step to store.
147 * @param int $stepid the id of the step.
148 * @param context $context the context of the owning question_usage_by_activity.
150 protected function insert_step_data(question_attempt_step
$step, $stepid, $context) {
151 foreach ($step->get_all_data() as $name => $value) {
152 if ($value instanceof question_file_saver
) {
153 $value->save_files($stepid, $context);
156 $data = new stdClass();
157 $data->attemptstepid
= $stepid;
159 $data->value
= $value;
160 $this->db
->insert_record('question_attempt_step_data', $data, false);
165 * Store a {@link question_attempt_step} in the database.
166 * @param question_attempt_step $step the step to store.
167 * @param int $questionattemptid the question attept id this step belongs to.
168 * @param int $seq the sequence number of this stop.
169 * @param context $context the context of the owning question_usage_by_activity.
171 public function insert_question_attempt_step(question_attempt_step
$step,
172 $questionattemptid, $seq, $context) {
174 $record = $this->make_step_record($step, $questionattemptid, $seq);
175 $record->id
= $this->db
->insert_record('question_attempt_steps', $record);
177 $this->insert_step_data($step, $record->id
, $context);
181 * Update a {@link question_attempt_step} in the database.
182 * @param question_attempt_step $qa the step to store.
183 * @param int $questionattemptid the question attept id this step belongs to.
184 * @param int $seq the sequence number of this stop.
185 * @param context $context the context of the owning question_usage_by_activity.
187 public function update_question_attempt_step(question_attempt_step
$step,
188 $questionattemptid, $seq, $context) {
190 $record = $this->make_step_record($step, $questionattemptid, $seq);
191 $record->id
= $step->get_id();
192 $this->db
->update_record('question_attempt_steps', $record);
194 $this->db
->delete_records('question_attempt_step_data',
195 array('attemptstepid' => $record->id
));
196 $this->insert_step_data($step, $record->id
, $context);
200 * Load a {@link question_attempt_step} from the database.
201 * @param int $stepid the id of the step to load.
202 * @param question_attempt_step the step that was loaded.
204 public function load_question_attempt_step($stepid) {
205 $records = $this->db
->get_recordset_sql("
207 qas.id AS attemptstepid,
208 qas.questionattemptid,
217 FROM {question_attempt_steps} qas
218 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
222 ", array('stepid' => $stepid));
224 if (!$records->valid()) {
225 throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
228 $step = question_attempt_step
::load_from_records($records, $stepid);
235 * Load a {@link question_attempt} from the database, including all its
237 * @param int $questionattemptid the id of the question attempt to load.
238 * @param question_attempt the question attempt that was loaded.
240 public function load_question_attempt($questionattemptid) {
241 $records = $this->db
->get_recordset_sql("
244 quba.preferredbehaviour,
245 qa.id AS questionattemptid,
258 qas.id AS attemptstepid,
267 FROM {question_attempts qa
268 JOIN {question_usages} quba ON quba.id = qa.questionusageid
269 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
270 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
273 qa.id = :questionattemptid
277 ", array('questionattemptid' => $questionattemptid));
279 if (!$records->valid()) {
280 throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
283 $record = current($records);
284 $qa = question_attempt
::load_from_records($records, $questionattemptid,
285 new question_usage_null_observer(), $record->preferredbehaviour
);
292 * Load a {@link question_usage_by_activity} from the database, including
293 * all its {@link question_attempt}s and all their steps.
294 * @param int $qubaid the id of the usage to load.
295 * @param question_usage_by_activity the usage that was loaded.
297 public function load_questions_usage_by_activity($qubaid) {
298 $records = $this->db
->get_recordset_sql("
303 quba.preferredbehaviour,
304 qa.id AS questionattemptid,
317 qas.id AS attemptstepid,
326 FROM {question_usages} quba
327 LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
328 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
329 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
337 ", array('qubaid' => $qubaid));
339 if (!$records->valid()) {
340 throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
343 $quba = question_usage_by_activity
::load_from_records($records, $qubaid);
350 * Load information about the latest state of each question from the database.
352 * @param qubaid_condition $qubaids used to restrict which usages are included
353 * in the query. See {@link qubaid_condition}.
354 * @param array $slots A list of slots for the questions you want to konw about.
355 * @return array of records. See the SQL in this function to see the fields available.
357 public function load_questions_usages_latest_steps(qubaid_condition
$qubaids, $slots) {
358 list($slottest, $params) = $this->db
->get_in_or_equal($slots, SQL_PARAMS_NAMED
, 'slot');
360 $records = $this->db
->get_records_sql("
363 qa.id AS questionattemptid,
376 qas.id AS attemptstepid,
383 FROM {$qubaids->from_question_attempts('qa')}
384 JOIN {question_attempt_steps} qas ON
385 qas.id = {$this->latest_step_for_qa_subquery()}
388 {$qubaids->where()} AND
390 ", $params +
$qubaids->from_where_params());
396 * Load summary information about the state of each question in a group of
397 * attempts. This is used, for example, by the quiz manual grading report,
398 * to show how many attempts at each question need to be graded.
400 * @param qubaid_condition $qubaids used to restrict which usages are included
401 * in the query. See {@link qubaid_condition}.
402 * @param array $slots A list of slots for the questions you want to konw about.
403 * @return array The array keys are slot,qestionid. The values are objects with
404 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
405 * $manuallygraded and $all.
407 public function load_questions_usages_question_state_summary(
408 qubaid_condition
$qubaids, $slots) {
409 list($slottest, $params) = $this->db
->get_in_or_equal($slots, SQL_PARAMS_NAMED
, 'slot');
411 $rs = $this->db
->get_recordset_sql("
417 {$this->full_states_to_summary_state_sql()}
419 COUNT(1) AS numattempts
421 FROM {$qubaids->from_question_attempts('qa')}
422 JOIN {question_attempt_steps} qas ON
423 qas.id = {$this->latest_step_for_qa_subquery()}
424 JOIN {question} q ON q.id = qa.questionid
427 {$qubaids->where()} AND
436 {$this->full_states_to_summary_state_sql()}
444 ", $params +
$qubaids->from_where_params());
447 foreach ($rs as $row) {
448 $index = $row->slot
. ',' . $row->questionid
;
450 if (!array_key_exists($index, $results)) {
451 $res = new stdClass();
452 $res->slot
= $row->slot
;
453 $res->questionid
= $row->questionid
;
454 $res->name
= $row->name
;
455 $res->inprogress
= 0;
456 $res->needsgrading
= 0;
457 $res->autograded
= 0;
458 $res->manuallygraded
= 0;
460 $results[$index] = $res;
463 $results[$index]->{$row->summarystate
} = $row->numattempts
;
464 $results[$index]->all +
= $row->numattempts
;
472 * Get a list of usage ids where the question with slot $slot, and optionally
473 * also with question id $questionid, is in summary state $summarystate. Also
474 * return the total count of such states.
476 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
477 * $limitnum. A special value 'random' can be passed as $orderby, in which case
478 * $limitfrom is ignored.
480 * @param qubaid_condition $qubaids used to restrict which usages are included
481 * in the query. See {@link qubaid_condition}.
482 * @param int $slot The slot for the questions you want to konw about.
483 * @param int $questionid (optional) Only return attempts that were of this specific question.
484 * @param string $summarystate the summary state of interest, or 'all'.
485 * @param string $orderby the column to order by.
486 * @param array $params any params required by any of the SQL fragments.
487 * @param int $limitfrom implements paging of the results.
488 * Ignored if $orderby = random or $limitnum is null.
489 * @param int $limitnum implements paging of the results. null = all.
490 * @return array with two elements, an array of usage ids, and a count of the total number.
492 public function load_questions_usages_where_question_in_state(
493 qubaid_condition
$qubaids, $summarystate, $slot, $questionid = null,
494 $orderby = 'random', $params, $limitfrom = 0, $limitnum = null) {
498 $extrawhere .= ' AND qa.questionid = :questionid';
499 $params['questionid'] = $questionid;
501 if ($summarystate != 'all') {
502 list($test, $sparams) = $this->in_summary_state_test($summarystate);
503 $extrawhere .= ' AND qas.state ' . $test;
507 if ($orderby == 'random') {
509 } else if ($orderby) {
510 $sqlorderby = 'ORDER BY ' . $orderby;
515 // We always want the total count, as well as the partcular list of ids,
516 // based on the paging and sort order. Becuase the list of ids is never
517 // going to be too rediculously long. My worst-case scenario is
518 // 10,000 students in the coures, each doing 5 quiz attempts. That
519 // is a 50,000 element int => int array, which PHP seems to use 5MB
520 // memeory to store on a 64 bit server.
521 $params +
= $qubaids->from_where_params();
522 $params['slot'] = $slot;
523 $qubaids = $this->db
->get_records_sql_menu("
528 FROM {$qubaids->from_question_attempts('qa')}
529 JOIN {question_attempt_steps} qas ON
530 qas.id = {$this->latest_step_for_qa_subquery()}
531 JOIN {question} q ON q.id = qa.questionid
534 {$qubaids->where()} AND
541 $qubaids = array_keys($qubaids);
542 $count = count($qubaids);
544 if ($orderby == 'random') {
549 if (!is_null($limitnum)) {
550 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
553 return array($qubaids, $count);
557 * Load a {@link question_usage_by_activity} from the database, including
558 * all its {@link question_attempt}s and all their steps.
559 * @param qubaid_condition $qubaids used to restrict which usages are included
560 * in the query. See {@link qubaid_condition}.
561 * @param array $slots if null, load info for all quesitions, otherwise only
562 * load the averages for the specified questions.
564 public function load_average_marks(qubaid_condition
$qubaids, $slots = null) {
565 if (!empty($slots)) {
566 list($slottest, $slotsparams) = $this->db
->get_in_or_equal(
567 $slots, SQL_PARAMS_NAMED
, 'slot');
568 $slotwhere = " AND qa.slot $slottest";
574 list($statetest, $stateparams) = $this->db
->get_in_or_equal(array(
575 (string) question_state
::$gaveup,
576 (string) question_state
::$gradedwrong,
577 (string) question_state
::$gradedpartial,
578 (string) question_state
::$gradedright,
579 (string) question_state
::$mangaveup,
580 (string) question_state
::$mangrwrong,
581 (string) question_state
::$mangrpartial,
582 (string) question_state
::$mangrright), SQL_PARAMS_NAMED
, 'st');
584 return $this->db
->get_records_sql("
587 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
588 COUNT(1) AS numaveraged
590 FROM {$qubaids->from_question_attempts('qa')}
591 JOIN {question_attempt_steps} qas ON
592 qas.id = {$this->latest_step_for_qa_subquery()}
597 AND qas.state $statetest
602 ", $slotsparams +
$stateparams +
$qubaids->from_where_params());
606 * Load a {@link question_attempt} from the database, including all its
608 * @param int $questionid the question to load all the attempts fors.
609 * @param qubaid_condition $qubaids used to restrict which usages are included
610 * in the query. See {@link qubaid_condition}.
611 * @return array of question_attempts.
613 public function load_attempts_at_question($questionid, qubaid_condition
$qubaids) {
616 $params = $qubaids->from_where_params();
617 $params['questionid'] = $questionid;
619 $records = $DB->get_recordset_sql("
622 quba.preferredbehaviour,
623 qa.id AS questionattemptid,
636 qas.id AS attemptstepid,
645 FROM {$qubaids->from_question_attempts('qa')}
646 JOIN {question_usages} quba ON quba.id = qa.questionusageid
647 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
648 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
651 {$qubaids->where()} AND
652 qa.questionid = :questionid
660 $questionattempts = array();
661 while ($records->valid()) {
662 $record = $records->current();
663 $questionattempts[$record->questionattemptid
] =
664 question_attempt
::load_from_records($records,
665 $record->questionattemptid
, new question_usage_null_observer(),
666 $record->preferredbehaviour
);
670 return $questionattempts;
674 * Update a question_usages row to refect any changes in a usage (but not
675 * any of its question_attempts.
676 * @param question_usage_by_activity $quba the usage that has changed.
678 public function update_questions_usage_by_activity(question_usage_by_activity
$quba) {
679 $record = new stdClass();
680 $record->id
= $quba->get_id();
681 $record->contextid
= $quba->get_owning_context()->id
;
682 $record->component
= $quba->get_owning_component();
683 $record->preferredbehaviour
= $quba->get_preferred_behaviour();
685 $this->db
->update_record('question_usages', $record);
689 * Update a question_attempts row to refect any changes in a question_attempt
690 * (but not any of its steps).
691 * @param question_attempt $qa the question attempt that has changed.
693 public function update_question_attempt(question_attempt
$qa) {
694 $record = new stdClass();
695 $record->id
= $qa->get_database_id();
696 $record->maxmark
= $qa->get_max_mark();
697 $record->minfraction
= $qa->get_min_fraction();
698 $record->flagged
= $qa->is_flagged();
699 $record->questionsummary
= $qa->get_question_summary();
700 $record->rightanswer
= $qa->get_right_answer_summary();
701 $record->responsesummary
= $qa->get_response_summary();
702 $record->timemodified
= time();
704 $this->db
->update_record('question_attempts', $record);
708 * Delete a question_usage_by_activity and all its associated
709 * {@link question_attempts} and {@link question_attempt_steps} from the
711 * @param qubaid_condition $qubaids identifies which question useages to delete.
713 public function delete_questions_usage_by_activities(qubaid_condition
$qubaids) {
714 $where = "qa.questionusageid {$qubaids->usage_id_in()}";
715 $params = $qubaids->usage_id_in_params();
717 $contextids = $this->db
->get_records_sql_menu("
718 SELECT DISTINCT contextid, 1
719 FROM {question_usages}
720 WHERE id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
721 foreach ($contextids as $contextid => $notused) {
722 $this->delete_response_files($contextid, "IN (
724 FROM {question_attempts} qa
725 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
726 WHERE $where)", $params);
729 if ($this->db
->get_dbfamily() == 'mysql') {
730 $this->delete_usage_records_for_mysql($qubaids);
734 $this->db
->delete_records_select('question_attempt_step_data', "attemptstepid IN (
736 FROM {question_attempts} qa
737 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
738 WHERE $where)", $params);
740 $this->db
->delete_records_select('question_attempt_steps', "questionattemptid IN (
742 FROM {question_attempts} qa
743 WHERE $where)", $params);
745 $this->db
->delete_records_select('question_attempts',
746 "{question_attempts}.questionusageid {$qubaids->usage_id_in()}",
747 $qubaids->usage_id_in_params());
749 $this->db
->delete_records_select('question_usages',
750 "{question_usages}.id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
754 * This function is a work-around for poor MySQL performance with
755 * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
756 * syntax to get good performance. See MDL-29520.
757 * @param qubaid_condition $qubaids identifies which question useages to delete.
759 protected function delete_usage_records_for_mysql(qubaid_condition
$qubaids) {
760 // TODO once MDL-29589 is fixed, eliminate this method, and instead use the new $DB API.
762 DELETE qu, qa, qas, qasd
763 FROM {question_usages} qu
764 JOIN {question_attempts} qa ON qa.questionusageid = qu.id
765 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
766 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
767 WHERE qu.id ' . $qubaids->usage_id_in(),
768 $qubaids->usage_id_in_params());
772 * Delete all the steps for a question attempt.
773 * @param int $qaids question_attempt id.
774 * @param context $context the context that the $quba belongs to.
776 public function delete_steps($stepids, $context) {
777 if (empty($stepids)) {
780 list($test, $params) = $this->db
->get_in_or_equal($stepids, SQL_PARAMS_NAMED
);
782 $this->delete_response_files($context->id
, $test, $params);
784 $this->db
->delete_records_select('question_attempt_step_data',
785 "attemptstepid $test", $params);
786 $this->db
->delete_records_select('question_attempt_steps',
787 "id $test", $params);
791 * Delete all the files belonging to the response variables in the gives
792 * question attempt steps.
793 * @param int $contextid the context these attempts belong to.
794 * @param string $itemidstest a bit of SQL that can be used in a
795 * WHERE itemid $itemidstest clause. Must use named params.
796 * @param array $params any query parameters used in $itemidstest.
798 protected function delete_response_files($contextid, $itemidstest, $params) {
799 $fs = get_file_storage();
800 foreach (question_engine
::get_all_response_file_areas() as $filearea) {
801 $fs->delete_area_files_select($contextid, 'question', $filearea,
802 $itemidstest, $params);
807 * Delete all the previews for a given question.
808 * @param int $questionid question id.
810 public function delete_previews($questionid) {
811 $previews = $this->db
->get_records_sql_menu("
812 SELECT DISTINCT quba.id, 1
813 FROM {question_usages} quba
814 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
815 WHERE quba.component = 'core_question_preview' AND
816 qa.questionid = ?", array($questionid));
817 if (empty($previews)) {
820 $this->delete_questions_usage_by_activities(new qubaid_list($previews));
824 * Update the flagged state of a question in the database.
825 * @param int $qubaid the question usage id.
826 * @param int $questionid the question id.
827 * @param int $sessionid the question_attempt id.
828 * @param bool $newstate the new state of the flag. true = flagged.
830 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
831 if (!$this->db
->record_exists('question_attempts', array('id' => $qaid,
832 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
833 throw new moodle_exception('errorsavingflags', 'question');
836 $this->db
->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
840 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
841 * column to a summary state. Use this like
842 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
843 * @param string SQL fragment.
845 protected function full_states_to_summary_state_sql() {
847 foreach (question_state
::get_all() as $state) {
848 $sql .= "WHEN '$state' THEN '{$state->get_summary_state()}'\n";
854 * Get the SQL needed to test that question_attempt_steps.state is in a
855 * state corresponding to $summarystate.
856 * @param string $summarystate one of
857 * inprogress, needsgrading, manuallygraded or autograded
858 * @param bool $equal if false, do a NOT IN test. Default true.
859 * @return string SQL fragment.
861 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
862 $states = question_state
::get_all_for_summary_state($summarystate);
863 return $this->db
->get_in_or_equal(array_map('strval', $states),
864 SQL_PARAMS_NAMED
, $prefix, $equal);
868 * Change the maxmark for the question_attempt with number in usage $slot
869 * for all the specified question_attempts.
870 * @param qubaid_condition $qubaids Selects which usages are updated.
871 * @param int $slot the number is usage to affect.
872 * @param number $newmaxmark the new max mark to set.
874 public function set_max_mark_in_attempts(qubaid_condition
$qubaids, $slot, $newmaxmark) {
875 $this->db
->set_field_select('question_attempts', 'maxmark', $newmaxmark,
876 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
877 $qubaids->usage_id_in_params() +
array('slot' => $slot));
881 * Return a subquery that computes the sum of the marks for all the questions
882 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
885 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
888 * @param string $qubaid SQL fragment that controls which usage is summed.
889 * This will normally be the name of a column in the outer query. Not that this
890 * SQL fragment must not contain any placeholders.
891 * @return string SQL code for the subquery.
893 public function sum_usage_marks_subquery($qubaid) {
894 // To explain the COALESCE in the following SQL: SUM(lots of NULLs) gives
895 // NULL, while SUM(one 0.0 and lots of NULLS) gives 0.0. We don't want that.
896 // We always want to return a number, so the COALESCE is there to turn the
897 // NULL total into a 0.
898 return "SELECT COALESCE(SUM(qa.maxmark * qas.fraction), 0)
899 FROM {question_attempts} qa
900 JOIN {question_attempt_steps} qas ON qas.id = (
901 SELECT MAX(summarks_qas.id)
902 FROM {question_attempt_steps} summarks_qas
903 WHERE summarks_qas.questionattemptid = qa.id
905 WHERE qa.questionusageid = $qubaid
907 WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1
913 * Get a subquery that returns the latest step of every qa in some qubas.
914 * Currently, this is only used by the quiz reports. See
915 * {@link quiz_attempt_report_table::add_latest_state_join()}.
916 * @param string $alias alias to use for this inline-view.
917 * @param qubaid_condition $qubaids restriction on which question_usages we
918 * are interested in. This is important for performance.
919 * @return array with two elements, the SQL fragment and any params requried.
921 public function question_attempt_latest_state_view($alias, qubaid_condition
$qubaids) {
923 SELECT {$alias}qa.id AS questionattemptid,
924 {$alias}qa.questionusageid,
926 {$alias}qa.behaviour,
927 {$alias}qa.questionid,
930 {$alias}qa.minfraction,
932 {$alias}qa.questionsummary,
933 {$alias}qa.rightanswer,
934 {$alias}qa.responsesummary,
935 {$alias}qa.timemodified,
936 {$alias}qas.id AS attemptstepid,
937 {$alias}qas.sequencenumber,
939 {$alias}qas.fraction,
940 {$alias}qas.timecreated,
943 FROM {$qubaids->from_question_attempts($alias . 'qa')}
944 JOIN {question_attempt_steps} {$alias}qas ON
945 {$alias}qas.id = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
946 WHERE {$qubaids->where()}
947 ) $alias", $qubaids->from_where_params());
950 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
953 FROM {question_attempt_steps}
954 WHERE questionattemptid = $questionattemptid
959 * @param array $questionids of question ids.
960 * @param qubaid_condition $qubaids ids of the usages to consider.
961 * @return boolean whether any of these questions are being used by any of
964 public function questions_in_use(array $questionids, qubaid_condition
$qubaids) {
965 list($test, $params) = $this->db
->get_in_or_equal($questionids);
966 return $this->db
->record_exists_select('question_attempts',
967 'questionid ' . $test . ' AND questionusageid ' .
968 $qubaids->usage_id_in(), $params +
$qubaids->usage_id_in_params());
974 * Implementation of the unit of work pattern for the question engine.
976 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
977 * changes to a {@link question_usage_by_activity}, and its constituent parts,
978 * so that the changes can be saved to the database when {@link save()} is called.
980 * @copyright 2009 The Open University
981 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
983 class question_engine_unit_of_work
implements question_usage_observer
{
984 /** @var question_usage_by_activity the usage being tracked. */
987 /** @var boolean whether any of the fields of the usage have been changed. */
988 protected $modified = false;
991 * @var array list of slot => {@link question_attempt}s that
992 * were already in the usage, and which have been modified.
994 protected $attemptsmodified = array();
997 * @var array list of slot => {@link question_attempt}s that
998 * have been added to the usage.
1000 protected $attemptsadded = array();
1003 * @var array of array(question_attempt_step, question_attempt id, seq number)
1004 * of steps that have been added to question attempts in this usage.
1006 protected $stepsadded = array();
1009 * @var array of array(question_attempt_step, question_attempt id, seq number)
1010 * of steps that have been modified in their attempt.
1012 protected $stepsmodified = array();
1015 * @var array list of question_attempt_step.id => question_attempt_step of steps
1016 * that were previously stored in the database, but which are no longer required.
1018 protected $stepsdeleted = array();
1022 * @param question_usage_by_activity $quba the usage to track.
1024 public function __construct(question_usage_by_activity
$quba) {
1025 $this->quba
= $quba;
1028 public function notify_modified() {
1029 $this->modified
= true;
1032 public function notify_attempt_modified(question_attempt
$qa) {
1033 $slot = $qa->get_slot();
1034 if (!array_key_exists($slot, $this->attemptsadded
)) {
1035 $this->attemptsmodified
[$slot] = $qa;
1039 public function notify_attempt_added(question_attempt
$qa) {
1040 $this->attemptsadded
[$qa->get_slot()] = $qa;
1043 public function notify_step_added(question_attempt_step
$step, question_attempt
$qa, $seq) {
1044 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1048 if (($key = $this->is_step_added($step)) !== false) {
1052 if (($key = $this->is_step_modified($step)) !== false) {
1053 throw new coding_exception('Cannot add a step that has already been modified.');
1056 if (($key = $this->is_step_deleted($step)) !== false) {
1057 unset($this->stepsdeleted
[$step->get_id()]);
1058 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1062 $stepid = $step->get_id();
1064 if (array_key_exists($stepid, $this->stepsdeleted
)) {
1065 unset($this->stepsdeleted
[$stepid]);
1067 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1070 $this->stepsadded
[] = array($step, $qa->get_database_id(), $seq);
1074 public function notify_step_modified(question_attempt_step
$step, question_attempt
$qa, $seq) {
1075 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1079 if (($key = $this->is_step_added($step)) !== false) {
1083 if (($key = $this->is_step_deleted($step)) !== false) {
1084 throw new coding_exception('Cannot modify a step after it has been deleted.');
1087 $stepid = $step->get_id();
1088 if (empty($stepid)) {
1089 throw new coding_exception('Cannot modify a step that has never been stored in the database.');
1092 $this->stepsmodified
[] = array($step, $qa->get_database_id(), $seq);
1095 public function notify_step_deleted(question_attempt_step
$step, question_attempt
$qa) {
1096 if (array_key_exists($qa->get_slot(), $this->attemptsadded
)) {
1100 if (($key = $this->is_step_added($step)) !== false) {
1101 unset($this->stepsadded
[$key]);
1105 if (($key = $this->is_step_modified($step)) !== false) {
1106 unset($this->stepsmodified
[$key]);
1109 $stepid = $step->get_id();
1110 if (empty($stepid)) {
1111 return; // Was never in the database.
1114 $this->stepsdeleted
[$stepid] = $step;
1118 * @param question_attempt_step $step a step
1119 * @return int|false if the step is in the list of steps to be added, return
1120 * the key, otherwise return false.
1122 protected function is_step_added(question_attempt_step
$step) {
1123 foreach ($this->stepsadded
as $key => $data) {
1124 list($addedstep, $qaid, $seq) = $data;
1125 if ($addedstep === $step) {
1133 * @param question_attempt_step $step a step
1134 * @return int|false if the step is in the list of steps to be modified, return
1135 * the key, otherwise return false.
1137 protected function is_step_modified(question_attempt_step
$step) {
1138 foreach ($this->stepsmodified
as $key => $data) {
1139 list($modifiedstep, $qaid, $seq) = $data;
1140 if ($modifiedstep === $step) {
1148 * @param question_attempt_step $step a step
1149 * @return bool whether the step is in the list of steps to be deleted.
1151 protected function is_step_deleted(question_attempt_step
$step) {
1152 foreach ($this->stepsdeleted
as $deletedstep) {
1153 if ($deletedstep === $step) {
1161 * Write all the changes we have recorded to the database.
1162 * @param question_engine_data_mapper $dm the mapper to use to update the database.
1164 public function save(question_engine_data_mapper
$dm) {
1165 $dm->delete_steps(array_keys($this->stepsdeleted
), $this->quba
->get_owning_context());
1167 foreach ($this->stepsmodified
as $stepinfo) {
1168 list($step, $questionattemptid, $seq) = $stepinfo;
1169 $dm->update_question_attempt_step($step, $questionattemptid, $seq,
1170 $this->quba
->get_owning_context());
1173 foreach ($this->stepsadded
as $stepinfo) {
1174 list($step, $questionattemptid, $seq) = $stepinfo;
1175 $dm->insert_question_attempt_step($step, $questionattemptid, $seq,
1176 $this->quba
->get_owning_context());
1179 foreach ($this->attemptsadded
as $qa) {
1180 $dm->insert_question_attempt($qa, $this->quba
->get_owning_context());
1183 foreach ($this->attemptsmodified
as $qa) {
1184 $dm->update_question_attempt($qa);
1187 if ($this->modified
) {
1188 $dm->update_questions_usage_by_activity($this->quba
);
1195 * This class represents the promise to save some files from a particular draft
1196 * file area into a particular file area. It is used beause the necessary
1197 * information about what to save is to hand in the
1198 * {@link question_attempt::process_response_files()} method, but we don't know
1199 * if this question attempt will actually be saved in the database until later,
1200 * when the {@link question_engine_unit_of_work} is saved, if it is.
1202 * @copyright 2011 The Open University
1203 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1205 class question_file_saver
{
1206 /** @var int the id of the draft file area to save files from. */
1207 protected $draftitemid;
1208 /** @var string the owning component name. */
1209 protected $component;
1210 /** @var string the file area name. */
1211 protected $filearea;
1214 * @var string the value to store in the question_attempt_step_data to
1215 * represent these files.
1217 protected $value = null;
1221 * @param int $draftitemid the draft area to save the files from.
1222 * @param string $component the component for the file area to save into.
1223 * @param string $filearea the name of the file area to save into.
1225 public function __construct($draftitemid, $component, $filearea, $text = null) {
1226 $this->draftitemid
= $draftitemid;
1227 $this->component
= $component;
1228 $this->filearea
= $filearea;
1229 $this->value
= $this->compute_value($draftitemid, $text);
1233 * Compute the value that should be stored in the question_attempt_step_data
1234 * table. Contains a hash that (almost) uniquely encodes all the files.
1235 * @param int $draftitemid the draft file area itemid.
1236 * @param string $text optional content containing file links.
1238 protected function compute_value($draftitemid, $text) {
1241 $fs = get_file_storage();
1242 $usercontext = get_context_instance(CONTEXT_USER
, $USER->id
);
1244 $files = $fs->get_area_files($usercontext->id
, 'user', 'draft',
1245 $draftitemid, 'sortorder, filepath, filename', false);
1248 foreach ($files as $file) {
1249 $string .= $file->get_filepath() . $file->get_filename() . '|' .
1250 $file->get_contenthash() . '|';
1254 $hash = md5($string);
1259 if (is_null($text)) {
1263 // We add the file hash so a simple string comparison will say if the
1264 // files have been changed. First strip off any existing file hash.
1265 $text = preg_replace('/\s*<!-- File hash: \w+ -->\s*$/', '', $text);
1266 $text = file_rewrite_urls_to_pluginfile($text, $draftitemid);
1268 $text .= '<!-- File hash: ' . $hash . ' -->';
1273 public function __toString() {
1274 return $this->value
;
1278 * Actually save the files.
1279 * @param integer $itemid the item id for the file area to save into.
1281 public function save_files($itemid, $context) {
1282 file_save_draft_area_files($this->draftitemid
, $context->id
,
1283 $this->component
, $this->filearea
, $itemid);
1289 * This class represents a restriction on the set of question_usage ids to include
1290 * in a larger database query. Depending of the how you are going to restrict the
1291 * list of usages, construct an appropriate subclass.
1293 * If $qubaids is an instance of this class, example usage might be
1295 * SELECT qa.id, qa.maxmark
1296 * FROM $qubaids->from_question_attempts('qa')
1297 * WHERE $qubaids->where() AND qa.slot = 1
1299 * @copyright 2010 The Open University
1300 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1302 abstract class qubaid_condition
{
1305 * @return string the SQL that needs to go in the FROM clause when trying
1306 * to select records from the 'question_attempts' table based on the
1309 public abstract function from_question_attempts($alias);
1311 /** @return string the SQL that needs to go in the where clause. */
1312 public abstract function where();
1315 * @return the params needed by a query that uses
1316 * {@link from_question_attempts()} and {@link where()}.
1318 public abstract function from_where_params();
1321 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
1322 * This method returns the "IN (...)" part.
1324 public abstract function usage_id_in();
1327 * @return the params needed by a query that uses {@link usage_id_in()}.
1329 public abstract function usage_id_in_params();
1334 * This class represents a restriction on the set of question_usage ids to include
1335 * in a larger database query based on an explicit list of ids.
1337 * @copyright 2010 The Open University
1338 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1340 class qubaid_list
extends qubaid_condition
{
1341 /** @var array of ids. */
1343 protected $columntotest = null;
1348 * @param array $qubaids of question usage ids.
1350 public function __construct(array $qubaids) {
1351 $this->qubaids
= $qubaids;
1354 public function from_question_attempts($alias) {
1355 $this->columntotest
= $alias . '.questionusageid';
1356 return '{question_attempts} ' . $alias;
1359 public function where() {
1362 if (is_null($this->columntotest
)) {
1363 throw new coding_exception('Must call from_question_attempts before where().');
1365 if (empty($this->qubaids
)) {
1366 $this->params
= array();
1370 return $this->columntotest
. ' ' . $this->usage_id_in();
1373 public function from_where_params() {
1374 return $this->params
;
1377 public function usage_id_in() {
1380 if (empty($this->qubaids
)) {
1381 $this->params
= array();
1384 list($where, $this->params
) = $DB->get_in_or_equal(
1385 $this->qubaids
, SQL_PARAMS_NAMED
, 'qubaid');
1389 public function usage_id_in_params() {
1390 return $this->params
;
1396 * This class represents a restriction on the set of question_usage ids to include
1397 * in a larger database query based on JOINing to some other tables.
1399 * The general form of the query is something like
1401 * SELECT qa.id, qa.maxmark
1403 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
1404 * WHERE $where AND qa.slot = 1
1406 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1408 * @copyright 2010 The Open University
1409 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1411 class qubaid_join
extends qubaid_condition
{
1413 public $usageidcolumn;
1418 * Constructor. The meaning of the arguments is explained in the class comment.
1419 * @param string $from SQL fragemnt to go in the FROM clause.
1420 * @param string $usageidcolumn the column in $from that should be
1421 * made equal to the usageid column in the JOIN clause.
1422 * @param string $where SQL fragment to go in the where clause.
1423 * @param array $params required by the SQL. You must use named parameters.
1425 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1426 $this->from
= $from;
1427 $this->usageidcolumn
= $usageidcolumn;
1428 $this->params
= $params;
1429 if (empty($where)) {
1432 $this->where
= $where;
1435 public function from_question_attempts($alias) {
1437 JOIN {question_attempts} {$alias} ON " .
1438 "{$alias}.questionusageid = $this->usageidcolumn";
1441 public function where() {
1442 return $this->where
;
1445 public function from_where_params() {
1446 return $this->params
;
1449 public function usage_id_in() {
1450 return "IN (SELECT $this->usageidcolumn FROM $this->from WHERE $this->where)";
1453 public function usage_id_in_params() {
1454 return $this->params
;