MDL-47122 use insert_records to improve question engine performance.
[moodle.git] / question / engine / datalib.php
CommitLineData
d1b7e03d 1<?php
d1b7e03d
TH
2// This file is part of Moodle - http://moodle.org/
3//
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.
8//
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.
13//
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/>.
16
d1b7e03d 17/**
5e8a85aa 18 * Code for loading and saving question attempts to and from the database.
d1b7e03d 19 *
4040e2dd 20 * A note for future reference. This code is pretty efficient but there are some
94815ccf
TH
21 * potential optimisations that could be contemplated, at the cost of making the
22 * code more complex:
23 *
4040e2dd
TH
24 * 1. (This is probably not worth doing.) In the unit-of-work save method, we
25 * could get all the ids for steps due to be deleted or modified,
94815ccf
TH
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.
30 *
4040e2dd 31 * @package core_question
017bc1d9
TH
32 * @copyright 2009 The Open University
33 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
34 */
35
36
a17b297d
TH
37defined('MOODLE_INTERNAL') || die();
38
39
d1b7e03d
TH
40/**
41 * This class controls the loading and saving of question engine data to and from
42 * the database.
43 *
017bc1d9
TH
44 * @copyright 2009 The Open University
45 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
46 */
47class question_engine_data_mapper {
06f8ed54
TH
48 /**
49 * @var moodle_database normally points to global $DB, but I prefer not to
50 * use globals if I can help it.
51 */
52 protected $db;
53
54 /**
55 * @param moodle_database $db a database connectoin. Defaults to global $DB.
56 */
0a606a2b 57 public function __construct(moodle_database $db = null) {
06f8ed54
TH
58 if (is_null($db)) {
59 global $DB;
06f8ed54
TH
60 $this->db = $DB;
61 } else {
62 $this->db = $db;
63 }
64 }
65
d1b7e03d
TH
66 /**
67 * Store an entire {@link question_usage_by_activity} in the database,
68 * including all the question_attempts that comprise it.
69 * @param question_usage_by_activity $quba the usage to store.
70 */
71 public function insert_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 72 $record = new stdClass();
d1b7e03d 73 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
74 $record->component = $quba->get_owning_component();
75 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 76
06f8ed54 77 $newid = $this->db->insert_record('question_usages', $record);
d1b7e03d
TH
78 $quba->set_id_from_database($newid);
79
4040e2dd
TH
80 // Initially an array of array of question_attempt_step_objects.
81 // Built as a nested array for efficiency, then flattened.
82 $stepdata = array();
83
d1b7e03d 84 foreach ($quba->get_attempt_iterator() as $qa) {
4040e2dd
TH
85 $stepdata[] = $this->insert_question_attempt($qa, $quba->get_owning_context());
86 }
87
88 $stepdata = call_user_func_array('array_merge', $stepdata);
89 if ($stepdata) {
90 $this->insert_all_step_data($stepdata);
d1b7e03d
TH
91 }
92 }
93
94 /**
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.
94815ccf 98 * @param context $context the context of the owning question_usage_by_activity.
4040e2dd 99 * @return array of question_attempt_step_data rows, that still need to be inserted.
d1b7e03d 100 */
217f9a61 101 public function insert_question_attempt(question_attempt $qa, $context) {
0ff4bd08 102 $record = new stdClass();
d1b7e03d
TH
103 $record->questionusageid = $qa->get_usage_id();
104 $record->slot = $qa->get_slot();
56b0df7e 105 $record->behaviour = $qa->get_behaviour_name();
d1b7e03d 106 $record->questionid = $qa->get_question()->id;
1da821bb 107 $record->variant = $qa->get_variant();
d1b7e03d
TH
108 $record->maxmark = $qa->get_max_mark();
109 $record->minfraction = $qa->get_min_fraction();
4e3d8293 110 $record->maxfraction = $qa->get_max_fraction();
d1b7e03d 111 $record->flagged = $qa->is_flagged();
56b0df7e 112 $record->questionsummary = $qa->get_question_summary();
2f1e464a 113 if (core_text::strlen($record->questionsummary) > question_bank::MAX_SUMMARY_LENGTH) {
c83ed025 114 // It seems some people write very long quesions! MDL-30760
2f1e464a 115 $record->questionsummary = core_text::substr($record->questionsummary,
c83ed025
TH
116 0, question_bank::MAX_SUMMARY_LENGTH - 3) . '...';
117 }
56b0df7e
TH
118 $record->rightanswer = $qa->get_right_answer_summary();
119 $record->responsesummary = $qa->get_response_summary();
d1b7e03d 120 $record->timemodified = time();
06f8ed54 121 $record->id = $this->db->insert_record('question_attempts', $record);
1c80e79a 122 $qa->set_database_id($record->id);
d1b7e03d 123
4040e2dd
TH
124 // Initially an array of array of question_attempt_step_objects.
125 // Built as a nested array for efficiency, then flattened.
126 $stepdata = array();
127
d1b7e03d 128 foreach ($qa->get_step_iterator() as $seq => $step) {
4040e2dd 129 $stepdata[] = $this->insert_question_attempt_step($step, $record->id, $seq, $context);
d1b7e03d 130 }
4040e2dd
TH
131
132 return call_user_func_array('array_merge', $stepdata);
d1b7e03d
TH
133 }
134
135 /**
94815ccf
TH
136 * Helper method used by insert_question_attempt_step and update_question_attempt_step
137 * @param question_attempt_step $step the step to store.
217f9a61
TH
138 * @param int $questionattemptid the question attept id this step belongs to.
139 * @param int $seq the sequence number of this stop.
94815ccf 140 * @return stdClass data to insert into the database.
d1b7e03d 141 */
94815ccf 142 protected function make_step_record(question_attempt_step $step, $questionattemptid, $seq) {
0ff4bd08 143 $record = new stdClass();
d1b7e03d
TH
144 $record->questionattemptid = $questionattemptid;
145 $record->sequencenumber = $seq;
deef04a4 146 $record->state = (string) $step->get_state();
d1b7e03d
TH
147 $record->fraction = $step->get_fraction();
148 $record->timecreated = $step->get_timecreated();
149 $record->userid = $step->get_user_id();
94815ccf
TH
150 return $record;
151 }
d1b7e03d 152
94815ccf
TH
153 /**
154 * Helper method used by insert_question_attempt_step and update_question_attempt_step
155 * @param question_attempt_step $step the step to store.
156 * @param int $stepid the id of the step.
157 * @param context $context the context of the owning question_usage_by_activity.
4040e2dd 158 * @return array of question_attempt_step_data rows, that still need to be inserted.
94815ccf 159 */
4040e2dd
TH
160 protected function prepare_step_data(question_attempt_step $step, $stepid, $context) {
161 $rows = array();
d1b7e03d 162 foreach ($step->get_all_data() as $name => $value) {
48d9c17d 163 if ($value instanceof question_file_saver) {
94815ccf 164 $value->save_files($stepid, $context);
ea07b2d6
TH
165 }
166 if ($value instanceof question_response_files) {
7a26403f 167 $value = (string) $value;
48d9c17d
TH
168 }
169
0ff4bd08 170 $data = new stdClass();
94815ccf 171 $data->attemptstepid = $stepid;
56b0df7e
TH
172 $data->name = $name;
173 $data->value = $value;
4040e2dd
TH
174 $rows[] = $data;
175 }
176 return $rows;
177 }
178
179 /**
180 * Insert a lot of records into question_attempt_step_data in one go.
181 * @param array $rows the rows to insert.
182 */
183 public function insert_all_step_data(array $rows) {
184 if (!$rows) {
185 return;
d1b7e03d 186 }
4040e2dd 187 $this->db->insert_records('question_attempt_step_data', $rows);
d1b7e03d
TH
188 }
189
94815ccf
TH
190 /**
191 * Store a {@link question_attempt_step} in the database.
192 * @param question_attempt_step $step the step to store.
193 * @param int $questionattemptid the question attept id this step belongs to.
194 * @param int $seq the sequence number of this stop.
195 * @param context $context the context of the owning question_usage_by_activity.
4040e2dd 196 * @return array of question_attempt_step_data rows, that still need to be inserted.
94815ccf
TH
197 */
198 public function insert_question_attempt_step(question_attempt_step $step,
199 $questionattemptid, $seq, $context) {
200
201 $record = $this->make_step_record($step, $questionattemptid, $seq);
202 $record->id = $this->db->insert_record('question_attempt_steps', $record);
203
4040e2dd 204 return $this->prepare_step_data($step, $record->id, $context);
94815ccf
TH
205 }
206
207 /**
208 * Update a {@link question_attempt_step} in the database.
209 * @param question_attempt_step $qa the step to store.
210 * @param int $questionattemptid the question attept id this step belongs to.
211 * @param int $seq the sequence number of this stop.
212 * @param context $context the context of the owning question_usage_by_activity.
4040e2dd 213 * @return array of question_attempt_step_data rows, that still need to be inserted.
94815ccf
TH
214 */
215 public function update_question_attempt_step(question_attempt_step $step,
216 $questionattemptid, $seq, $context) {
217
218 $record = $this->make_step_record($step, $questionattemptid, $seq);
219 $record->id = $step->get_id();
220 $this->db->update_record('question_attempt_steps', $record);
221
222 $this->db->delete_records('question_attempt_step_data',
223 array('attemptstepid' => $record->id));
4040e2dd 224 return $this->prepare_step_data($step, $record->id, $context);
94815ccf
TH
225 }
226
d1b7e03d
TH
227 /**
228 * Load a {@link question_attempt_step} from the database.
f7970e3c 229 * @param int $stepid the id of the step to load.
d1b7e03d
TH
230 * @param question_attempt_step the step that was loaded.
231 */
232 public function load_question_attempt_step($stepid) {
35d5f1c2 233 $records = $this->db->get_recordset_sql("
d1b7e03d 234SELECT
dc1ee5cb
TH
235 quba.contextid,
236 COALLESCE(q.qtype, 'missingtype') AS qtype,
d1b7e03d
TH
237 qas.id AS attemptstepid,
238 qas.questionattemptid,
239 qas.sequencenumber,
240 qas.state,
241 qas.fraction,
242 qas.timecreated,
243 qas.userid,
244 qasd.name,
245 qasd.value
246
dc1ee5cb
TH
247FROM {question_attempt_steps} qas
248JOIN {question_attempts} qa ON qa.id = qas.questionattemptid
249JOIN {question_usages} quba ON quba.id = qa.questionusageid
250LEFT JOIN {question} q ON q.id = qa.questionid
06f8ed54 251LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
252
253WHERE
06f8ed54
TH
254 qas.id = :stepid
255 ", array('stepid' => $stepid));
d1b7e03d 256
35d5f1c2 257 if (!$records->valid()) {
88f0eb15 258 throw new coding_exception('Failed to load question_attempt_step ' . $stepid);
d1b7e03d
TH
259 }
260
35d5f1c2
TH
261 $step = question_attempt_step::load_from_records($records, $stepid);
262 $records->close();
263
264 return $step;
d1b7e03d
TH
265 }
266
267 /**
268 * Load a {@link question_attempt} from the database, including all its
269 * steps.
f7970e3c 270 * @param int $questionattemptid the id of the question attempt to load.
d1b7e03d
TH
271 * @param question_attempt the question attempt that was loaded.
272 */
273 public function load_question_attempt($questionattemptid) {
35d5f1c2 274 $records = $this->db->get_recordset_sql("
d1b7e03d 275SELECT
7a719748 276 quba.contextid,
d1b7e03d
TH
277 quba.preferredbehaviour,
278 qa.id AS questionattemptid,
279 qa.questionusageid,
280 qa.slot,
281 qa.behaviour,
282 qa.questionid,
1da821bb 283 qa.variant,
d1b7e03d
TH
284 qa.maxmark,
285 qa.minfraction,
4e3d8293 286 qa.maxfraction,
d1b7e03d
TH
287 qa.flagged,
288 qa.questionsummary,
289 qa.rightanswer,
290 qa.responsesummary,
291 qa.timemodified,
292 qas.id AS attemptstepid,
293 qas.sequencenumber,
294 qas.state,
295 qas.fraction,
296 qas.timecreated,
297 qas.userid,
298 qasd.name,
299 qasd.value
300
d50e8a1b 301FROM {question_attempts} qa
06f8ed54
TH
302JOIN {question_usages} quba ON quba.id = qa.questionusageid
303LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
304LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
305
306WHERE
06f8ed54 307 qa.id = :questionattemptid
d1b7e03d
TH
308
309ORDER BY
310 qas.sequencenumber
06f8ed54 311 ", array('questionattemptid' => $questionattemptid));
d1b7e03d 312
35d5f1c2 313 if (!$records->valid()) {
88f0eb15 314 throw new coding_exception('Failed to load question_attempt ' . $questionattemptid);
d1b7e03d
TH
315 }
316
d50e8a1b 317 $record = $records->current();
35d5f1c2 318 $qa = question_attempt::load_from_records($records, $questionattemptid,
d1b7e03d 319 new question_usage_null_observer(), $record->preferredbehaviour);
35d5f1c2
TH
320 $records->close();
321
322 return $qa;
d1b7e03d
TH
323 }
324
325 /**
326 * Load a {@link question_usage_by_activity} from the database, including
327 * all its {@link question_attempt}s and all their steps.
f7970e3c 328 * @param int $qubaid the id of the usage to load.
d1b7e03d
TH
329 * @param question_usage_by_activity the usage that was loaded.
330 */
331 public function load_questions_usage_by_activity($qubaid) {
35d5f1c2 332 $records = $this->db->get_recordset_sql("
d1b7e03d 333SELECT
d1b7e03d
TH
334 quba.id AS qubaid,
335 quba.contextid,
336 quba.component,
337 quba.preferredbehaviour,
338 qa.id AS questionattemptid,
339 qa.questionusageid,
340 qa.slot,
341 qa.behaviour,
342 qa.questionid,
1da821bb 343 qa.variant,
d1b7e03d
TH
344 qa.maxmark,
345 qa.minfraction,
4e3d8293 346 qa.maxfraction,
d1b7e03d
TH
347 qa.flagged,
348 qa.questionsummary,
349 qa.rightanswer,
350 qa.responsesummary,
351 qa.timemodified,
352 qas.id AS attemptstepid,
353 qas.sequencenumber,
354 qas.state,
355 qas.fraction,
356 qas.timecreated,
357 qas.userid,
358 qasd.name,
359 qasd.value
360
06f8ed54
TH
361FROM {question_usages} quba
362LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
363LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
364LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
365
366WHERE
06f8ed54 367 quba.id = :qubaid
d1b7e03d
TH
368
369ORDER BY
370 qa.slot,
371 qas.sequencenumber
c76145d3 372 ", array('qubaid' => $qubaid));
d1b7e03d 373
35d5f1c2 374 if (!$records->valid()) {
88f0eb15 375 throw new coding_exception('Failed to load questions_usage_by_activity ' . $qubaid);
d1b7e03d
TH
376 }
377
35d5f1c2
TH
378 $quba = question_usage_by_activity::load_from_records($records, $qubaid);
379 $records->close();
380
381 return $quba;
d1b7e03d
TH
382 }
383
99caa248
JP
384 /**
385 * Load all {@link question_usage_by_activity} from the database for one qubaid_condition
386 * Include all its {@link question_attempt}s and all their steps.
387 * @param qubaid_condition $qubaids the condition that tells us which usages to load.
388 * @return question_usage_by_activity[] the usages that were loaded.
389 */
390 public function load_questions_usages_by_activity($qubaids) {
391 $records = $this->db->get_recordset_sql("
392SELECT
393 quba.id AS qubaid,
394 quba.contextid,
395 quba.component,
396 quba.preferredbehaviour,
397 qa.id AS questionattemptid,
398 qa.questionusageid,
399 qa.slot,
400 qa.behaviour,
401 qa.questionid,
402 qa.variant,
403 qa.maxmark,
404 qa.minfraction,
405 qa.maxfraction,
406 qa.flagged,
407 qa.questionsummary,
408 qa.rightanswer,
409 qa.responsesummary,
410 qa.timemodified,
411 qas.id AS attemptstepid,
412 qas.sequencenumber,
413 qas.state,
414 qas.fraction,
415 qas.timecreated,
416 qas.userid,
417 qasd.name,
418 qasd.value
419
420FROM {question_usages} quba
421LEFT JOIN {question_attempts} qa ON qa.questionusageid = quba.id
422LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
423LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
424
425WHERE
426 quba.id {$qubaids->usage_id_in()}
427
428ORDER BY
429 quba.id,
430 qa.slot,
431 qas.sequencenumber
432 ", $qubaids->usage_id_in_params());
433
434 if (!$records->valid()) {
435 throw new coding_exception('Failed to load questions_usages_by_activity for qubaid_condition :' . $qubaids);
436 }
437
438 $qubas = array();
439 do {
440 $record = $records->current();
441 $qubas[$record->qubaid] = question_usage_by_activity::load_from_records($records, $record->qubaid);
442 } while ($records->valid());
443
444 $records->close();
445
446 return $qubas;
447 }
448
d1b7e03d
TH
449 /**
450 * Load information about the latest state of each question from the database.
451 *
452 * @param qubaid_condition $qubaids used to restrict which usages are included
e68e4ccf 453 * in the query. See {@link qubaid_condition}.
5b0a31bf 454 * @param array $slots A list of slots for the questions you want to know about.
e68e4ccf 455 * @param string|null $fields
d1b7e03d
TH
456 * @return array of records. See the SQL in this function to see the fields available.
457 */
e68e4ccf 458 public function load_questions_usages_latest_steps(qubaid_condition $qubaids, $slots, $fields = null) {
a2ac2349 459 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
d1b7e03d 460
e68e4ccf 461 if ($fields === null) {
99caa248 462 $fields = "qas.id,
d1b7e03d
TH
463 qa.id AS questionattemptid,
464 qa.questionusageid,
465 qa.slot,
466 qa.behaviour,
467 qa.questionid,
1da821bb 468 qa.variant,
d1b7e03d
TH
469 qa.maxmark,
470 qa.minfraction,
4e3d8293 471 qa.maxfraction,
d1b7e03d
TH
472 qa.flagged,
473 qa.questionsummary,
474 qa.rightanswer,
475 qa.responsesummary,
476 qa.timemodified,
477 qas.id AS attemptstepid,
478 qas.sequencenumber,
479 qas.state,
480 qas.fraction,
481 qas.timecreated,
e68e4ccf
JP
482 qas.userid";
483
484 }
485
486 $records = $this->db->get_records_sql("
487SELECT
488 {$fields}
d1b7e03d
TH
489
490FROM {$qubaids->from_question_attempts('qa')}
84b37108
TH
491JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
492 AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
d1b7e03d
TH
493
494WHERE
495 {$qubaids->where()} AND
496 qa.slot $slottest
06f8ed54 497 ", $params + $qubaids->from_where_params());
d1b7e03d
TH
498
499 return $records;
500 }
501
502 /**
5e8a85aa
TH
503 * Load summary information about the state of each question in a group of
504 * attempts. This is used, for example, by the quiz manual grading report,
505 * to show how many attempts at each question need to be graded.
d1b7e03d
TH
506 *
507 * @param qubaid_condition $qubaids used to restrict which usages are included
508 * in the query. See {@link qubaid_condition}.
509 * @param array $slots A list of slots for the questions you want to konw about.
510 * @return array The array keys are slot,qestionid. The values are objects with
511 * fields $slot, $questionid, $inprogress, $name, $needsgrading, $autograded,
512 * $manuallygraded and $all.
513 */
9c197f44
TH
514 public function load_questions_usages_question_state_summary(
515 qubaid_condition $qubaids, $slots) {
a2ac2349 516 list($slottest, $params) = $this->db->get_in_or_equal($slots, SQL_PARAMS_NAMED, 'slot');
d1b7e03d 517
06f8ed54 518 $rs = $this->db->get_recordset_sql("
d1b7e03d
TH
519SELECT
520 qa.slot,
521 qa.questionid,
522 q.name,
523 CASE qas.state
524 {$this->full_states_to_summary_state_sql()}
525 END AS summarystate,
526 COUNT(1) AS numattempts
527
528FROM {$qubaids->from_question_attempts('qa')}
84b37108
TH
529JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
530 AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
06f8ed54 531JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
532
533WHERE
534 {$qubaids->where()} AND
535 qa.slot $slottest
536
537GROUP BY
538 qa.slot,
539 qa.questionid,
540 q.name,
541 q.id,
304f0d85
TH
542 CASE qas.state
543 {$this->full_states_to_summary_state_sql()}
544 END
d1b7e03d 545
f9b0500f 546ORDER BY
d1b7e03d
TH
547 qa.slot,
548 qa.questionid,
549 q.name,
550 q.id
06f8ed54 551 ", $params + $qubaids->from_where_params());
d1b7e03d 552
d1b7e03d 553 $results = array();
cf3b6568 554 foreach ($rs as $row) {
d1b7e03d
TH
555 $index = $row->slot . ',' . $row->questionid;
556
557 if (!array_key_exists($index, $results)) {
0ff4bd08 558 $res = new stdClass();
d1b7e03d
TH
559 $res->slot = $row->slot;
560 $res->questionid = $row->questionid;
561 $res->name = $row->name;
562 $res->inprogress = 0;
563 $res->needsgrading = 0;
564 $res->autograded = 0;
565 $res->manuallygraded = 0;
566 $res->all = 0;
567 $results[$index] = $res;
568 }
569
570 $results[$index]->{$row->summarystate} = $row->numattempts;
571 $results[$index]->all += $row->numattempts;
572 }
cf3b6568 573 $rs->close();
d1b7e03d
TH
574
575 return $results;
576 }
577
578 /**
579 * Get a list of usage ids where the question with slot $slot, and optionally
580 * also with question id $questionid, is in summary state $summarystate. Also
581 * return the total count of such states.
582 *
583 * Only a subset of the ids can be returned by using $orderby, $limitfrom and
584 * $limitnum. A special value 'random' can be passed as $orderby, in which case
585 * $limitfrom is ignored.
586 *
587 * @param qubaid_condition $qubaids used to restrict which usages are included
588 * in the query. See {@link qubaid_condition}.
f7970e3c
TH
589 * @param int $slot The slot for the questions you want to konw about.
590 * @param int $questionid (optional) Only return attempts that were of this specific question.
d1b7e03d
TH
591 * @param string $summarystate the summary state of interest, or 'all'.
592 * @param string $orderby the column to order by.
cf3b6568 593 * @param array $params any params required by any of the SQL fragments.
f7970e3c 594 * @param int $limitfrom implements paging of the results.
d1b7e03d 595 * Ignored if $orderby = random or $limitnum is null.
f7970e3c 596 * @param int $limitnum implements paging of the results. null = all.
d1b7e03d
TH
597 * @return array with two elements, an array of usage ids, and a count of the total number.
598 */
599 public function load_questions_usages_where_question_in_state(
600 qubaid_condition $qubaids, $summarystate, $slot, $questionid = null,
5b0a31bf 601 $orderby = 'random', $params = array(), $limitfrom = 0, $limitnum = null) {
d1b7e03d
TH
602
603 $extrawhere = '';
604 if ($questionid) {
cf3b6568
TH
605 $extrawhere .= ' AND qa.questionid = :questionid';
606 $params['questionid'] = $questionid;
d1b7e03d
TH
607 }
608 if ($summarystate != 'all') {
cf3b6568 609 list($test, $sparams) = $this->in_summary_state_test($summarystate);
d1b7e03d 610 $extrawhere .= ' AND qas.state ' . $test;
cf3b6568 611 $params += $sparams;
d1b7e03d
TH
612 }
613
614 if ($orderby == 'random') {
615 $sqlorderby = '';
616 } else if ($orderby) {
617 $sqlorderby = 'ORDER BY ' . $orderby;
618 } else {
619 $sqlorderby = '';
620 }
621
5b0a31bf
TH
622 // We always want the total count, as well as the partcular list of ids
623 // based on the paging and sort order. Because the list of ids is never
624 // going to be too ridiculously long. My worst-case scenario is
625 // 10,000 students in the course, each doing 5 quiz attempts. That
d1b7e03d 626 // is a 50,000 element int => int array, which PHP seems to use 5MB
5b0a31bf
TH
627 // memory to store on a 64 bit server.
628 $qubaidswhere = $qubaids->where(); // Must call this before params.
cf3b6568
TH
629 $params += $qubaids->from_where_params();
630 $params['slot'] = $slot;
5b0a31bf 631
cf3b6568 632 $qubaids = $this->db->get_records_sql_menu("
d1b7e03d
TH
633SELECT
634 qa.questionusageid,
635 1
636
637FROM {$qubaids->from_question_attempts('qa')}
84b37108
TH
638JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
639 AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
cf3b6568 640JOIN {question} q ON q.id = qa.questionid
d1b7e03d
TH
641
642WHERE
5b0a31bf 643 {$qubaidswhere} AND
cf3b6568 644 qa.slot = :slot
d1b7e03d
TH
645 $extrawhere
646
647$sqlorderby
cf3b6568 648 ", $params);
d1b7e03d
TH
649
650 $qubaids = array_keys($qubaids);
651 $count = count($qubaids);
652
653 if ($orderby == 'random') {
654 shuffle($qubaids);
655 $limitfrom = 0;
656 }
657
658 if (!is_null($limitnum)) {
659 $qubaids = array_slice($qubaids, $limitfrom, $limitnum);
660 }
661
662 return array($qubaids, $count);
663 }
664
665 /**
666 * Load a {@link question_usage_by_activity} from the database, including
667 * all its {@link question_attempt}s and all their steps.
668 * @param qubaid_condition $qubaids used to restrict which usages are included
669 * in the query. See {@link qubaid_condition}.
670 * @param array $slots if null, load info for all quesitions, otherwise only
671 * load the averages for the specified questions.
672 */
673 public function load_average_marks(qubaid_condition $qubaids, $slots = null) {
d1b7e03d 674 if (!empty($slots)) {
9c197f44
TH
675 list($slottest, $slotsparams) = $this->db->get_in_or_equal(
676 $slots, SQL_PARAMS_NAMED, 'slot');
f4fe3968 677 $slotwhere = " AND qa.slot {$slottest}";
d1b7e03d
TH
678 } else {
679 $slotwhere = '';
5b0a31bf 680 $slotsparams = array();
d1b7e03d
TH
681 }
682
e24ee794 683 list($statetest, $stateparams) = $this->db->get_in_or_equal(array(
deef04a4
TH
684 (string) question_state::$gaveup,
685 (string) question_state::$gradedwrong,
686 (string) question_state::$gradedpartial,
687 (string) question_state::$gradedright,
688 (string) question_state::$mangaveup,
689 (string) question_state::$mangrwrong,
690 (string) question_state::$mangrpartial,
691 (string) question_state::$mangrright), SQL_PARAMS_NAMED, 'st');
d1b7e03d 692
e24ee794 693 return $this->db->get_records_sql("
d1b7e03d
TH
694SELECT
695 qa.slot,
696 AVG(COALESCE(qas.fraction, 0)) AS averagefraction,
697 COUNT(1) AS numaveraged
698
699FROM {$qubaids->from_question_attempts('qa')}
84b37108
TH
700JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
701 AND qas.sequencenumber = {$this->latest_step_for_qa_subquery()}
d1b7e03d
TH
702
703WHERE
704 {$qubaids->where()}
705 $slotwhere
706 AND qas.state $statetest
707
708GROUP BY qa.slot
709
710ORDER BY qa.slot
e24ee794 711 ", $slotsparams + $stateparams + $qubaids->from_where_params());
d1b7e03d
TH
712 }
713
714 /**
715 * Load a {@link question_attempt} from the database, including all its
716 * steps.
038014c4 717 *
f7970e3c 718 * @param int $questionid the question to load all the attempts fors.
d1b7e03d
TH
719 * @param qubaid_condition $qubaids used to restrict which usages are included
720 * in the query. See {@link qubaid_condition}.
038014c4 721 * @return question_attempt[] array of question_attempts that were loaded.
d1b7e03d
TH
722 */
723 public function load_attempts_at_question($questionid, qubaid_condition $qubaids) {
99caa248 724 $sql = "
d1b7e03d 725SELECT
04853f27 726 quba.contextid,
d1b7e03d
TH
727 quba.preferredbehaviour,
728 qa.id AS questionattemptid,
729 qa.questionusageid,
730 qa.slot,
731 qa.behaviour,
732 qa.questionid,
1da821bb 733 qa.variant,
d1b7e03d
TH
734 qa.maxmark,
735 qa.minfraction,
4e3d8293 736 qa.maxfraction,
d1b7e03d
TH
737 qa.flagged,
738 qa.questionsummary,
739 qa.rightanswer,
740 qa.responsesummary,
741 qa.timemodified,
742 qas.id AS attemptstepid,
743 qas.sequencenumber,
744 qas.state,
745 qas.fraction,
746 qas.timecreated,
747 qas.userid,
748 qasd.name,
749 qasd.value
750
751FROM {$qubaids->from_question_attempts('qa')}
04853f27
TH
752JOIN {question_usages} quba ON quba.id = qa.questionusageid
753LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
754LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
d1b7e03d
TH
755
756WHERE
757 {$qubaids->where()} AND
04853f27 758 qa.questionid = :questionid
d1b7e03d
TH
759
760ORDER BY
761 quba.id,
762 qa.id,
99caa248
JP
763 qas.sequencenumber";
764
765 // For qubaid_list must call this after calling methods that generate sql.
766 $params = $qubaids->from_where_params();
767 $params['questionid'] = $questionid;
768
769 $records = $this->db->get_recordset_sql($sql, $params);
d1b7e03d 770
d1b7e03d 771 $questionattempts = array();
35d5f1c2
TH
772 while ($records->valid()) {
773 $record = $records->current();
d1b7e03d
TH
774 $questionattempts[$record->questionattemptid] =
775 question_attempt::load_from_records($records,
776 $record->questionattemptid, new question_usage_null_observer(),
777 $record->preferredbehaviour);
d1b7e03d 778 }
35d5f1c2
TH
779 $records->close();
780
d1b7e03d
TH
781 return $questionattempts;
782 }
783
784 /**
785 * Update a question_usages row to refect any changes in a usage (but not
786 * any of its question_attempts.
787 * @param question_usage_by_activity $quba the usage that has changed.
788 */
789 public function update_questions_usage_by_activity(question_usage_by_activity $quba) {
0ff4bd08 790 $record = new stdClass();
d1b7e03d
TH
791 $record->id = $quba->get_id();
792 $record->contextid = $quba->get_owning_context()->id;
56b0df7e
TH
793 $record->component = $quba->get_owning_component();
794 $record->preferredbehaviour = $quba->get_preferred_behaviour();
d1b7e03d 795
88f0eb15 796 $this->db->update_record('question_usages', $record);
d1b7e03d
TH
797 }
798
799 /**
800 * Update a question_attempts row to refect any changes in a question_attempt
801 * (but not any of its steps).
802 * @param question_attempt $qa the question attempt that has changed.
803 */
804 public function update_question_attempt(question_attempt $qa) {
0ff4bd08 805 $record = new stdClass();
d1b7e03d
TH
806 $record->id = $qa->get_database_id();
807 $record->maxmark = $qa->get_max_mark();
808 $record->minfraction = $qa->get_min_fraction();
4e3d8293 809 $record->maxfraction = $qa->get_max_fraction();
d1b7e03d 810 $record->flagged = $qa->is_flagged();
56b0df7e
TH
811 $record->questionsummary = $qa->get_question_summary();
812 $record->rightanswer = $qa->get_right_answer_summary();
813 $record->responsesummary = $qa->get_response_summary();
d1b7e03d
TH
814 $record->timemodified = time();
815
88f0eb15 816 $this->db->update_record('question_attempts', $record);
d1b7e03d
TH
817 }
818
819 /**
820 * Delete a question_usage_by_activity and all its associated
821 * {@link question_attempts} and {@link question_attempt_steps} from the
822 * database.
6b5f24d3 823 * @param qubaid_condition $qubaids identifies which question useages to delete.
d1b7e03d 824 */
6b5f24d3
TH
825 public function delete_questions_usage_by_activities(qubaid_condition $qubaids) {
826 $where = "qa.questionusageid {$qubaids->usage_id_in()}";
827 $params = $qubaids->usage_id_in_params();
828
829 $contextids = $this->db->get_records_sql_menu("
830 SELECT DISTINCT contextid, 1
831 FROM {question_usages}
a2ac2349 832 WHERE id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
6b5f24d3
TH
833 foreach ($contextids as $contextid => $notused) {
834 $this->delete_response_files($contextid, "IN (
835 SELECT qas.id
836 FROM {question_attempts} qa
837 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
838 WHERE $where)", $params);
839 }
840
f0bfd964
TH
841 if ($this->db->get_dbfamily() == 'mysql') {
842 $this->delete_usage_records_for_mysql($qubaids);
843 return;
844 }
845
c76145d3 846 $this->db->delete_records_select('question_attempt_step_data', "attemptstepid IN (
d1b7e03d 847 SELECT qas.id
c76145d3
TH
848 FROM {question_attempts} qa
849 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
c76145d3 850 WHERE $where)", $params);
6b5f24d3 851
c76145d3 852 $this->db->delete_records_select('question_attempt_steps', "questionattemptid IN (
d1b7e03d 853 SELECT qa.id
c76145d3 854 FROM {question_attempts} qa
c76145d3 855 WHERE $where)", $params);
6b5f24d3
TH
856
857 $this->db->delete_records_select('question_attempts',
a2ac2349
TH
858 "{question_attempts}.questionusageid {$qubaids->usage_id_in()}",
859 $qubaids->usage_id_in_params());
6b5f24d3
TH
860
861 $this->db->delete_records_select('question_usages',
a2ac2349 862 "{question_usages}.id {$qubaids->usage_id_in()}", $qubaids->usage_id_in_params());
d1b7e03d
TH
863 }
864
f0bfd964
TH
865 /**
866 * This function is a work-around for poor MySQL performance with
867 * DELETE FROM x WHERE id IN (SELECT ...). We have to use a non-standard
868 * syntax to get good performance. See MDL-29520.
869 * @param qubaid_condition $qubaids identifies which question useages to delete.
870 */
871 protected function delete_usage_records_for_mysql(qubaid_condition $qubaids) {
2ec325c2
TH
872 $qubaidtest = $qubaids->usage_id_in();
873 if (strpos($qubaidtest, 'question_usages') !== false &&
874 strpos($qubaidtest, 'IN (SELECT') === 0) {
875 // This horrible hack is required by MDL-29847. It comes from
876 // http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/
877 $qubaidtest = 'IN (SELECT * FROM ' . substr($qubaidtest, 3) . ' AS hack_subquery_alias)';
878 }
879
f0bfd964
TH
880 // TODO once MDL-29589 is fixed, eliminate this method, and instead use the new $DB API.
881 $this->db->execute('
882 DELETE qu, qa, qas, qasd
883 FROM {question_usages} qu
884 JOIN {question_attempts} qa ON qa.questionusageid = qu.id
033d6566
EL
885 LEFT JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
886 LEFT JOIN {question_attempt_step_data} qasd ON qasd.attemptstepid = qas.id
2ec325c2 887 WHERE qu.id ' . $qubaidtest,
f0bfd964
TH
888 $qubaids->usage_id_in_params());
889 }
890
d1b7e03d
TH
891 /**
892 * Delete all the steps for a question attempt.
f7970e3c 893 * @param int $qaids question_attempt id.
94815ccf 894 * @param context $context the context that the $quba belongs to.
d1b7e03d 895 */
94815ccf
TH
896 public function delete_steps($stepids, $context) {
897 if (empty($stepids)) {
d1b7e03d
TH
898 return;
899 }
94815ccf 900 list($test, $params) = $this->db->get_in_or_equal($stepids, SQL_PARAMS_NAMED);
6b5f24d3 901
296e1e97 902 $this->delete_response_files($context->id, $test, $params);
f0bfd964 903
94815ccf 904 $this->db->delete_records_select('question_attempt_step_data',
f4fe3968 905 "attemptstepid {$test}", $params);
9c197f44 906 $this->db->delete_records_select('question_attempt_steps',
f4fe3968 907 "id {$test}", $params);
d1b7e03d
TH
908 }
909
6b5f24d3
TH
910 /**
911 * Delete all the files belonging to the response variables in the gives
912 * question attempt steps.
913 * @param int $contextid the context these attempts belong to.
914 * @param string $itemidstest a bit of SQL that can be used in a
915 * WHERE itemid $itemidstest clause. Must use named params.
916 * @param array $params any query parameters used in $itemidstest.
917 */
918 protected function delete_response_files($contextid, $itemidstest, $params) {
919 $fs = get_file_storage();
c749527b 920 foreach (question_engine::get_all_response_file_areas() as $filearea) {
6b5f24d3
TH
921 $fs->delete_area_files_select($contextid, 'question', $filearea,
922 $itemidstest, $params);
923 }
924 }
925
d1b7e03d
TH
926 /**
927 * Delete all the previews for a given question.
f7970e3c 928 * @param int $questionid question id.
d1b7e03d
TH
929 */
930 public function delete_previews($questionid) {
c76145d3 931 $previews = $this->db->get_records_sql_menu("
d1b7e03d 932 SELECT DISTINCT quba.id, 1
c76145d3
TH
933 FROM {question_usages} quba
934 JOIN {question_attempts} qa ON qa.questionusageid = quba.id
d1b7e03d 935 WHERE quba.component = 'core_question_preview' AND
c76145d3 936 qa.questionid = ?", array($questionid));
d1b7e03d
TH
937 if (empty($previews)) {
938 return;
939 }
6b5f24d3 940 $this->delete_questions_usage_by_activities(new qubaid_list($previews));
d1b7e03d
TH
941 }
942
943 /**
944 * Update the flagged state of a question in the database.
f7970e3c
TH
945 * @param int $qubaid the question usage id.
946 * @param int $questionid the question id.
947 * @param int $sessionid the question_attempt id.
948 * @param bool $newstate the new state of the flag. true = flagged.
d1b7e03d 949 */
06f8ed54 950 public function update_question_attempt_flag($qubaid, $questionid, $qaid, $slot, $newstate) {
f9b0500f 951 if (!$this->db->record_exists('question_attempts', array('id' => $qaid,
06f8ed54 952 'questionusageid' => $qubaid, 'questionid' => $questionid, 'slot' => $slot))) {
88f0eb15 953 throw new moodle_exception('errorsavingflags', 'question');
d1b7e03d
TH
954 }
955
06f8ed54 956 $this->db->set_field('question_attempts', 'flagged', $newstate, array('id' => $qaid));
d1b7e03d
TH
957 }
958
959 /**
960 * Get all the WHEN 'x' THEN 'y' terms needed to convert the question_attempt_steps.state
961 * column to a summary state. Use this like
962 * CASE qas.state {$this->full_states_to_summary_state_sql()} END AS summarystate,
963 * @param string SQL fragment.
964 */
965 protected function full_states_to_summary_state_sql() {
966 $sql = '';
967 foreach (question_state::get_all() as $state) {
f4fe3968 968 $sql .= "WHEN '{$state}' THEN '{$state->get_summary_state()}'\n";
d1b7e03d
TH
969 }
970 return $sql;
971 }
972
973 /**
974 * Get the SQL needed to test that question_attempt_steps.state is in a
975 * state corresponding to $summarystate.
976 * @param string $summarystate one of
977 * inprogress, needsgrading, manuallygraded or autograded
f7970e3c 978 * @param bool $equal if false, do a NOT IN test. Default true.
d1b7e03d
TH
979 * @return string SQL fragment.
980 */
cf3b6568 981 public function in_summary_state_test($summarystate, $equal = true, $prefix = 'summarystates') {
d1b7e03d 982 $states = question_state::get_all_for_summary_state($summarystate);
deef04a4
TH
983 return $this->db->get_in_or_equal(array_map('strval', $states),
984 SQL_PARAMS_NAMED, $prefix, $equal);
d1b7e03d
TH
985 }
986
987 /**
988 * Change the maxmark for the question_attempt with number in usage $slot
989 * for all the specified question_attempts.
990 * @param qubaid_condition $qubaids Selects which usages are updated.
f7970e3c 991 * @param int $slot the number is usage to affect.
d1b7e03d
TH
992 * @param number $newmaxmark the new max mark to set.
993 */
994 public function set_max_mark_in_attempts(qubaid_condition $qubaids, $slot, $newmaxmark) {
c76145d3
TH
995 $this->db->set_field_select('question_attempts', 'maxmark', $newmaxmark,
996 "questionusageid {$qubaids->usage_id_in()} AND slot = :slot",
997 $qubaids->usage_id_in_params() + array('slot' => $slot));
d1b7e03d
TH
998 }
999
1000 /**
1001 * Return a subquery that computes the sum of the marks for all the questions
1002 * in a usage. Which useage to compute the sum for is controlled bu the $qubaid
1003 * parameter.
1004 *
1005 * See {@link quiz_update_all_attempt_sumgrades()} for an example of the usage of
1006 * this method.
1007 *
1008 * @param string $qubaid SQL fragment that controls which usage is summed.
2daffca5
TH
1009 * This will normally be the name of a column in the outer query. Not that this
1010 * SQL fragment must not contain any placeholders.
d1b7e03d
TH
1011 * @return string SQL code for the subquery.
1012 */
1013 public function sum_usage_marks_subquery($qubaid) {
ad73a2cb
TH
1014 // To explain the COALESCE in the following SQL: SUM(lots of NULLs) gives
1015 // NULL, while SUM(one 0.0 and lots of NULLS) gives 0.0. We don't want that.
1016 // We always want to return a number, so the COALESCE is there to turn the
1017 // NULL total into a 0.
1018 return "SELECT COALESCE(SUM(qa.maxmark * qas.fraction), 0)
c76145d3 1019 FROM {question_attempts} qa
84b37108
TH
1020 JOIN {question_attempt_steps} qas ON qas.questionattemptid = qa.id
1021 AND qas.sequencenumber = (
1022 SELECT MAX(summarks_qas.sequencenumber)
1023 FROM {question_attempt_steps} summarks_qas
1024 WHERE summarks_qas.questionattemptid = qa.id
d2c69d93 1025 )
d1b7e03d 1026 WHERE qa.questionusageid = $qubaid
9c197f44
TH
1027 HAVING COUNT(CASE
1028 WHEN qas.state = 'needsgrading' AND qa.maxmark > 0 THEN 1
1029 ELSE NULL
1030 END) = 0";
d1b7e03d
TH
1031 }
1032
94c0ec21
TH
1033 /**
1034 * Get a subquery that returns the latest step of every qa in some qubas.
1035 * Currently, this is only used by the quiz reports. See
ac4d9157 1036 * {@link quiz_attempts_report_table::add_latest_state_join()}.
94c0ec21
TH
1037 * @param string $alias alias to use for this inline-view.
1038 * @param qubaid_condition $qubaids restriction on which question_usages we
1039 * are interested in. This is important for performance.
1040 * @return array with two elements, the SQL fragment and any params requried.
1041 */
1042 public function question_attempt_latest_state_view($alias, qubaid_condition $qubaids) {
1043 return array("(
1044 SELECT {$alias}qa.id AS questionattemptid,
1045 {$alias}qa.questionusageid,
1046 {$alias}qa.slot,
1047 {$alias}qa.behaviour,
1048 {$alias}qa.questionid,
1049 {$alias}qa.variant,
1050 {$alias}qa.maxmark,
1051 {$alias}qa.minfraction,
4e3d8293 1052 {$alias}qa.maxfraction,
94c0ec21
TH
1053 {$alias}qa.flagged,
1054 {$alias}qa.questionsummary,
1055 {$alias}qa.rightanswer,
1056 {$alias}qa.responsesummary,
1057 {$alias}qa.timemodified,
1058 {$alias}qas.id AS attemptstepid,
1059 {$alias}qas.sequencenumber,
1060 {$alias}qas.state,
1061 {$alias}qas.fraction,
1062 {$alias}qas.timecreated,
1063 {$alias}qas.userid
1064
1065 FROM {$qubaids->from_question_attempts($alias . 'qa')}
84b37108
TH
1066 JOIN {question_attempt_steps} {$alias}qas ON {$alias}qas.questionattemptid = {$alias}qa.id
1067 AND {$alias}qas.sequencenumber = {$this->latest_step_for_qa_subquery($alias . 'qa.id')}
94c0ec21 1068 WHERE {$qubaids->where()}
f4fe3968 1069 ) {$alias}", $qubaids->from_where_params());
d1b7e03d
TH
1070 }
1071
1072 protected function latest_step_for_qa_subquery($questionattemptid = 'qa.id') {
d1b7e03d 1073 return "(
84b37108 1074 SELECT MAX(sequencenumber)
c76145d3 1075 FROM {question_attempt_steps}
d1b7e03d
TH
1076 WHERE questionattemptid = $questionattemptid
1077 )";
1078 }
1079
1080 /**
1081 * @param array $questionids of question ids.
07f88584
TH
1082 * @param qubaid_condition $qubaids ids of the usages to consider.
1083 * @return boolean whether any of these questions are being used by any of
1084 * those usages.
d1b7e03d 1085 */
07f88584 1086 public function questions_in_use(array $questionids, qubaid_condition $qubaids) {
c76145d3
TH
1087 list($test, $params) = $this->db->get_in_or_equal($questionids);
1088 return $this->db->record_exists_select('question_attempts',
07f88584
TH
1089 'questionid ' . $test . ' AND questionusageid ' .
1090 $qubaids->usage_id_in(), $params + $qubaids->usage_id_in_params());
d1b7e03d
TH
1091 }
1092}
1093
6b5f24d3 1094
d1b7e03d
TH
1095/**
1096 * Implementation of the unit of work pattern for the question engine.
1097 *
1098 * See http://martinfowler.com/eaaCatalog/unitOfWork.html. This tracks all the
1099 * changes to a {@link question_usage_by_activity}, and its constituent parts,
1100 * so that the changes can be saved to the database when {@link save()} is called.
1101 *
017bc1d9
TH
1102 * @copyright 2009 The Open University
1103 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1104 */
1105class question_engine_unit_of_work implements question_usage_observer {
1106 /** @var question_usage_by_activity the usage being tracked. */
1107 protected $quba;
1108
1109 /** @var boolean whether any of the fields of the usage have been changed. */
1110 protected $modified = false;
1111
1112 /**
94815ccf 1113 * @var array list of slot => {@link question_attempt}s that
d1b7e03d
TH
1114 * were already in the usage, and which have been modified.
1115 */
1116 protected $attemptsmodified = array();
1117
1118 /**
94815ccf 1119 * @var array list of slot => {@link question_attempt}s that
d1b7e03d
TH
1120 * have been added to the usage.
1121 */
1122 protected $attemptsadded = array();
1123
1124 /**
94815ccf
TH
1125 * @var array of array(question_attempt_step, question_attempt id, seq number)
1126 * of steps that have been added to question attempts in this usage.
d1b7e03d 1127 */
94815ccf 1128 protected $stepsadded = array();
d1b7e03d
TH
1129
1130 /**
94815ccf
TH
1131 * @var array of array(question_attempt_step, question_attempt id, seq number)
1132 * of steps that have been modified in their attempt.
d1b7e03d 1133 */
94815ccf
TH
1134 protected $stepsmodified = array();
1135
1136 /**
1137 * @var array list of question_attempt_step.id => question_attempt_step of steps
1138 * that were previously stored in the database, but which are no longer required.
1139 */
1140 protected $stepsdeleted = array();
d1b7e03d
TH
1141
1142 /**
1143 * Constructor.
1144 * @param question_usage_by_activity $quba the usage to track.
1145 */
1146 public function __construct(question_usage_by_activity $quba) {
1147 $this->quba = $quba;
1148 }
1149
1150 public function notify_modified() {
1151 $this->modified = true;
1152 }
1153
1154 public function notify_attempt_modified(question_attempt $qa) {
94815ccf
TH
1155 $slot = $qa->get_slot();
1156 if (!array_key_exists($slot, $this->attemptsadded)) {
1157 $this->attemptsmodified[$slot] = $qa;
d1b7e03d
TH
1158 }
1159 }
1160
1161 public function notify_attempt_added(question_attempt $qa) {
1162 $this->attemptsadded[$qa->get_slot()] = $qa;
1163 }
1164
94815ccf 1165 public function notify_step_added(question_attempt_step $step, question_attempt $qa, $seq) {
d1b7e03d
TH
1166 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1167 return;
1168 }
1169
94815ccf
TH
1170 if (($key = $this->is_step_added($step)) !== false) {
1171 return;
1172 }
1173
1174 if (($key = $this->is_step_modified($step)) !== false) {
1175 throw new coding_exception('Cannot add a step that has already been modified.');
1176 }
1177
1178 if (($key = $this->is_step_deleted($step)) !== false) {
1179 unset($this->stepsdeleted[$step->get_id()]);
1180 $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
1181 return;
1182 }
1183
1184 $stepid = $step->get_id();
1185 if ($stepid) {
1186 if (array_key_exists($stepid, $this->stepsdeleted)) {
1187 unset($this->stepsdeleted[$stepid]);
d1b7e03d 1188 }
94815ccf
TH
1189 $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
1190
1191 } else {
1192 $this->stepsadded[] = array($step, $qa->get_database_id(), $seq);
1193 }
1194 }
1195
1196 public function notify_step_modified(question_attempt_step $step, question_attempt $qa, $seq) {
1197 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1198 return;
d1b7e03d
TH
1199 }
1200
94815ccf
TH
1201 if (($key = $this->is_step_added($step)) !== false) {
1202 return;
1203 }
1204
1205 if (($key = $this->is_step_deleted($step)) !== false) {
1206 throw new coding_exception('Cannot modify a step after it has been deleted.');
1207 }
1208
1209 $stepid = $step->get_id();
1210 if (empty($stepid)) {
1211 throw new coding_exception('Cannot modify a step that has never been stored in the database.');
1212 }
1213
1214 $this->stepsmodified[] = array($step, $qa->get_database_id(), $seq);
d1b7e03d
TH
1215 }
1216
94815ccf 1217 public function notify_step_deleted(question_attempt_step $step, question_attempt $qa) {
d1b7e03d
TH
1218 if (array_key_exists($qa->get_slot(), $this->attemptsadded)) {
1219 return;
1220 }
94815ccf
TH
1221
1222 if (($key = $this->is_step_added($step)) !== false) {
1223 unset($this->stepsadded[$key]);
1224 return;
1225 }
1226
1227 if (($key = $this->is_step_modified($step)) !== false) {
1228 unset($this->stepsmodified[$key]);
1229 }
1230
1231 $stepid = $step->get_id();
1232 if (empty($stepid)) {
1233 return; // Was never in the database.
1234 }
1235
1236 $this->stepsdeleted[$stepid] = $step;
1237 }
1238
1239 /**
1240 * @param question_attempt_step $step a step
1241 * @return int|false if the step is in the list of steps to be added, return
1242 * the key, otherwise return false.
1243 */
1244 protected function is_step_added(question_attempt_step $step) {
1245 foreach ($this->stepsadded as $key => $data) {
1246 list($addedstep, $qaid, $seq) = $data;
1247 if ($addedstep === $step) {
1248 return $key;
1249 }
1250 }
1251 return false;
1252 }
1253
1254 /**
1255 * @param question_attempt_step $step a step
1256 * @return int|false if the step is in the list of steps to be modified, return
1257 * the key, otherwise return false.
1258 */
1259 protected function is_step_modified(question_attempt_step $step) {
1260 foreach ($this->stepsmodified as $key => $data) {
1261 list($modifiedstep, $qaid, $seq) = $data;
1262 if ($modifiedstep === $step) {
1263 return $key;
1264 }
1265 }
1266 return false;
1267 }
1268
1269 /**
1270 * @param question_attempt_step $step a step
1271 * @return bool whether the step is in the list of steps to be deleted.
1272 */
1273 protected function is_step_deleted(question_attempt_step $step) {
1274 foreach ($this->stepsdeleted as $deletedstep) {
1275 if ($deletedstep === $step) {
1276 return true;
1277 }
1278 }
1279 return false;
d1b7e03d
TH
1280 }
1281
1282 /**
1283 * Write all the changes we have recorded to the database.
1284 * @param question_engine_data_mapper $dm the mapper to use to update the database.
1285 */
1286 public function save(question_engine_data_mapper $dm) {
94815ccf
TH
1287 $dm->delete_steps(array_keys($this->stepsdeleted), $this->quba->get_owning_context());
1288
4040e2dd
TH
1289 // Initially an array of array of question_attempt_step_objects.
1290 // Built as a nested array for efficiency, then flattened.
1291 $stepdata = array();
1292
94815ccf
TH
1293 foreach ($this->stepsmodified as $stepinfo) {
1294 list($step, $questionattemptid, $seq) = $stepinfo;
4040e2dd
TH
1295 $stepdata[] = $dm->update_question_attempt_step(
1296 $step, $questionattemptid, $seq, $this->quba->get_owning_context());
94815ccf 1297 }
6b5f24d3 1298
d1b7e03d
TH
1299 foreach ($this->stepsadded as $stepinfo) {
1300 list($step, $questionattemptid, $seq) = $stepinfo;
4040e2dd
TH
1301 $stepdata[] = $dm->insert_question_attempt_step(
1302 $step, $questionattemptid, $seq, $this->quba->get_owning_context());
d1b7e03d 1303 }
6b5f24d3 1304
d1b7e03d 1305 foreach ($this->attemptsadded as $qa) {
4040e2dd
TH
1306 $stepdata[] = $dm->insert_question_attempt(
1307 $qa, $this->quba->get_owning_context());
d1b7e03d 1308 }
6b5f24d3 1309
d1b7e03d
TH
1310 foreach ($this->attemptsmodified as $qa) {
1311 $dm->update_question_attempt($qa);
1312 }
6b5f24d3 1313
d1b7e03d
TH
1314 if ($this->modified) {
1315 $dm->update_questions_usage_by_activity($this->quba);
1316 }
4040e2dd
TH
1317
1318 if (!$stepdata) {
1319 return;
1320 }
1321 $dm->insert_all_step_data(call_user_func_array('array_merge', $stepdata));
d1b7e03d
TH
1322 }
1323}
1324
1325
8a1e7b77
TH
1326/**
1327 * The interface implemented by {@link question_file_saver} and {@link question_file_loader}.
1328 *
1329 * @copyright 2012 The Open University
1330 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1331 */
1332interface question_response_files {
1333 /**
1334 * Get the files that were submitted.
1335 * @return array of stored_files objects.
1336 */
1337 public function get_files();
1338}
1339
1340
217f9a61
TH
1341/**
1342 * This class represents the promise to save some files from a particular draft
1343 * file area into a particular file area. It is used beause the necessary
1344 * information about what to save is to hand in the
1345 * {@link question_attempt::process_response_files()} method, but we don't know
1346 * if this question attempt will actually be saved in the database until later,
1347 * when the {@link question_engine_unit_of_work} is saved, if it is.
1348 *
1349 * @copyright 2011 The Open University
1350 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1351 */
8a1e7b77 1352class question_file_saver implements question_response_files {
217f9a61
TH
1353 /** @var int the id of the draft file area to save files from. */
1354 protected $draftitemid;
1355 /** @var string the owning component name. */
1356 protected $component;
1357 /** @var string the file area name. */
1358 protected $filearea;
1359
cd3557e6
TH
1360 /**
1361 * @var string the value to store in the question_attempt_step_data to
1362 * represent these files.
1363 */
1364 protected $value = null;
1365
217f9a61
TH
1366 /**
1367 * Constuctor.
1368 * @param int $draftitemid the draft area to save the files from.
1369 * @param string $component the component for the file area to save into.
1370 * @param string $filearea the name of the file area to save into.
1371 */
48d9c17d 1372 public function __construct($draftitemid, $component, $filearea, $text = null) {
217f9a61
TH
1373 $this->draftitemid = $draftitemid;
1374 $this->component = $component;
1375 $this->filearea = $filearea;
48d9c17d 1376 $this->value = $this->compute_value($draftitemid, $text);
217f9a61
TH
1377 }
1378
48d9c17d
TH
1379 /**
1380 * Compute the value that should be stored in the question_attempt_step_data
1381 * table. Contains a hash that (almost) uniquely encodes all the files.
1382 * @param int $draftitemid the draft file area itemid.
1383 * @param string $text optional content containing file links.
1384 */
1385 protected function compute_value($draftitemid, $text) {
cd3557e6
TH
1386 global $USER;
1387
cd3557e6 1388 $fs = get_file_storage();
21c08c63 1389 $usercontext = context_user::instance($USER->id);
cd3557e6
TH
1390
1391 $files = $fs->get_area_files($usercontext->id, 'user', 'draft',
48d9c17d 1392 $draftitemid, 'sortorder, filepath, filename', false);
cd3557e6
TH
1393
1394 $string = '';
1395 foreach ($files as $file) {
1396 $string .= $file->get_filepath() . $file->get_filename() . '|' .
1397 $file->get_contenthash() . '|';
1398 }
d629327a 1399 $hash = md5($string);
cd3557e6 1400
48d9c17d 1401 if (is_null($text)) {
d629327a
TH
1402 if ($string) {
1403 return $hash;
1404 } else {
1405 return '';
1406 }
48d9c17d
TH
1407 }
1408
1409 // We add the file hash so a simple string comparison will say if the
1410 // files have been changed. First strip off any existing file hash.
d629327a
TH
1411 if ($text !== '') {
1412 $text = preg_replace('/\s*<!-- File hash: \w+ -->\s*$/', '', $text);
1413 $text = file_rewrite_urls_to_pluginfile($text, $draftitemid);
3fc9410f
TH
1414 if ($string) {
1415 $text .= '<!-- File hash: ' . $hash . ' -->';
1416 }
48d9c17d
TH
1417 }
1418 return $text;
cd3557e6
TH
1419 }
1420
217f9a61 1421 public function __toString() {
48d9c17d 1422 return $this->value;
217f9a61
TH
1423 }
1424
1425 /**
1426 * Actually save the files.
1427 * @param integer $itemid the item id for the file area to save into.
1428 */
1429 public function save_files($itemid, $context) {
1430 file_save_draft_area_files($this->draftitemid, $context->id,
1431 $this->component, $this->filearea, $itemid);
1432 }
8a1e7b77
TH
1433
1434 /**
1435 * Get the files that were submitted.
1436 * @return array of stored_files objects.
1437 */
1438 public function get_files() {
1439 global $USER;
1440
1441 $fs = get_file_storage();
1442 $usercontext = context_user::instance($USER->id);
1443
1444 return $fs->get_area_files($usercontext->id, 'user', 'draft',
1445 $this->draftitemid, 'sortorder, filepath, filename', false);
1446 }
1447}
1448
1449
1450/**
1451 * This class is the mirror image of {@link question_file_saver}. It allows
1452 * files to be accessed again later (e.g. when re-grading) using that same
1453 * API as when doing the original grading.
1454 *
1455 * @copyright 2012 The Open University
1456 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
1457 */
1458class question_file_loader implements question_response_files {
1459 /** @var question_attempt_step the step that these files belong to. */
1460 protected $step;
1461
1462 /** @var string the field name for these files - which is used to construct the file area name. */
1463 protected $name;
1464
1465 /**
1466 * @var string the value to stored in the question_attempt_step_data to
1467 * represent these files.
1468 */
1469 protected $value;
1470
1471 /** @var int the context id that the files belong to. */
1472 protected $contextid;
1473
1474 /**
1475 * Constuctor.
1476 * @param question_attempt_step $step the step that these files belong to.
1477 * @param string $name string the field name for these files - which is used to construct the file area name.
1478 * @param string $value the value to stored in the question_attempt_step_data to
1479 * represent these files.
1480 * @param int $contextid the context id that the files belong to.
1481 */
1482 public function __construct(question_attempt_step $step, $name, $value, $contextid) {
d0782585
MPC
1483 $this->step = $step;
1484 $this->name = $name;
1485 $this->value = $value;
1486 $this->contextid = $contextid;
8a1e7b77
TH
1487 }
1488
1489 public function __toString() {
1490 return $this->value;
1491 }
1492
1493 /**
1494 * Get the files that were submitted.
1495 * @return array of stored_files objects.
1496 */
1497 public function get_files() {
1498 return $this->step->get_qt_files($this->name, $this->contextid);
1499 }
afb1b3d0
TH
1500
1501 /**
1502 * Copy these files into a draft area, and return the corresponding
1503 * {@link question_file_saver} that can save them again.
1504 *
1505 * This is used by {@link question_attempt::start_based_on()}, which is used
1506 * (for example) by the quizzes 'Each attempt builds on last' feature.
1507 *
1508 * @return question_file_saver that can re-save these files again.
1509 */
1510 public function get_question_file_saver() {
1511
d629327a
TH
1512 // There are three possibilities here for what $value will look like:
1513 // 1) some HTML content followed by an MD5 hash in a HTML comment;
1514 // 2) a plain MD5 hash;
1515 // 3) or some real content, without any hash.
1516 // The problem is that 3) is ambiguous in the case where a student writes
1517 // a response that looks exactly like an MD5 hash. For attempts made now,
1518 // we avoid case 3) by always going for case 1) or 2) (except when the
1519 // response is blank. However, there may be case 3) data in the database
1520 // so we need to handle it as best we can.
afb1b3d0
TH
1521 if (preg_match('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', $this->value)) {
1522 $value = preg_replace('/\s*<!-- File hash: [0-9a-zA-Z]{32} -->\s*$/', '', $this->value);
1523
1524 } else if (preg_match('/^[0-9a-zA-Z]{32}$/', $this->value)) {
1525 $value = null;
1526
1527 } else {
d629327a 1528 $value = $this->value;
afb1b3d0
TH
1529 }
1530
1531 list($draftid, $text) = $this->step->prepare_response_files_draft_itemid_with_text(
1532 $this->name, $this->contextid, $value);
1533 return new question_file_saver($draftid, 'question', 'response_' . $this->name, $text);
1534 }
217f9a61
TH
1535}
1536
1537
d1b7e03d
TH
1538/**
1539 * This class represents a restriction on the set of question_usage ids to include
1540 * in a larger database query. Depending of the how you are going to restrict the
1541 * list of usages, construct an appropriate subclass.
1542 *
1543 * If $qubaids is an instance of this class, example usage might be
1544 *
1545 * SELECT qa.id, qa.maxmark
1546 * FROM $qubaids->from_question_attempts('qa')
1547 * WHERE $qubaids->where() AND qa.slot = 1
1548 *
f7970e3c 1549 * @copyright 2010 The Open University
017bc1d9 1550 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1551 */
1552abstract class qubaid_condition {
1553
1554 /**
1555 * @return string the SQL that needs to go in the FROM clause when trying
1556 * to select records from the 'question_attempts' table based on the
1557 * qubaid_condition.
1558 */
1559 public abstract function from_question_attempts($alias);
1560
1561 /** @return string the SQL that needs to go in the where clause. */
1562 public abstract function where();
1563
1564 /**
1565 * @return the params needed by a query that uses
1566 * {@link from_question_attempts()} and {@link where()}.
1567 */
1568 public abstract function from_where_params();
1569
1570 /**
1571 * @return string SQL that can use used in a WHERE qubaid IN (...) query.
1572 * This method returns the "IN (...)" part.
1573 */
1574 public abstract function usage_id_in();
1575
1576 /**
1577 * @return the params needed by a query that uses {@link usage_id_in()}.
1578 */
1579 public abstract function usage_id_in_params();
e68e4ccf
JP
1580
1581 /**
1582 * @return string 40-character hash code that uniquely identifies the combination of properties and class name of this qubaid
1583 * condition.
1584 */
1585 public function get_hash_code() {
1586 return sha1(serialize($this));
1587 }
d1b7e03d
TH
1588}
1589
1590
1591/**
1592 * This class represents a restriction on the set of question_usage ids to include
1593 * in a larger database query based on an explicit list of ids.
1594 *
f7970e3c 1595 * @copyright 2010 The Open University
017bc1d9 1596 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1597 */
1598class qubaid_list extends qubaid_condition {
1599 /** @var array of ids. */
1600 protected $qubaids;
1601 protected $columntotest = null;
1602 protected $params;
1603
1604 /**
1605 * Constructor.
1606 * @param array $qubaids of question usage ids.
1607 */
1608 public function __construct(array $qubaids) {
1609 $this->qubaids = $qubaids;
1610 }
1611
1612 public function from_question_attempts($alias) {
d1b7e03d 1613 $this->columntotest = $alias . '.questionusageid';
9b40c540 1614 return '{question_attempts} ' . $alias;
d1b7e03d
TH
1615 }
1616
1617 public function where() {
1618 global $DB;
1619
1620 if (is_null($this->columntotest)) {
88f0eb15 1621 throw new coding_exception('Must call from_question_attempts before where().');
d1b7e03d
TH
1622 }
1623 if (empty($this->qubaids)) {
9b40c540 1624 $this->params = array();
d1b7e03d
TH
1625 return '1 = 0';
1626 }
d1b7e03d 1627
9b40c540 1628 return $this->columntotest . ' ' . $this->usage_id_in();
d1b7e03d
TH
1629 }
1630
1631 public function from_where_params() {
1632 return $this->params;
1633 }
1634
1635 public function usage_id_in() {
1636 global $DB;
1637
1638 if (empty($this->qubaids)) {
5f79a9bc 1639 $this->params = array();
d1b7e03d
TH
1640 return '= 0';
1641 }
9c197f44
TH
1642 list($where, $this->params) = $DB->get_in_or_equal(
1643 $this->qubaids, SQL_PARAMS_NAMED, 'qubaid');
d1b7e03d
TH
1644 return $where;
1645 }
1646
1647 public function usage_id_in_params() {
1648 return $this->params;
1649 }
1650}
1651
1652
1653/**
1654 * This class represents a restriction on the set of question_usage ids to include
1655 * in a larger database query based on JOINing to some other tables.
1656 *
1657 * The general form of the query is something like
1658 *
1659 * SELECT qa.id, qa.maxmark
1660 * FROM $from
2a3bdbf9 1661 * JOIN {question_attempts} qa ON qa.questionusageid = $usageidcolumn
d1b7e03d
TH
1662 * WHERE $where AND qa.slot = 1
1663 *
1664 * where $from, $usageidcolumn and $where are the arguments to the constructor.
1665 *
f7970e3c 1666 * @copyright 2010 The Open University
017bc1d9 1667 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
d1b7e03d
TH
1668 */
1669class qubaid_join extends qubaid_condition {
1670 public $from;
1671 public $usageidcolumn;
1672 public $where;
1673 public $params;
1674
1675 /**
1676 * Constructor. The meaning of the arguments is explained in the class comment.
1677 * @param string $from SQL fragemnt to go in the FROM clause.
1678 * @param string $usageidcolumn the column in $from that should be
1679 * made equal to the usageid column in the JOIN clause.
1680 * @param string $where SQL fragment to go in the where clause.
e24ee794 1681 * @param array $params required by the SQL. You must use named parameters.
d1b7e03d
TH
1682 */
1683 public function __construct($from, $usageidcolumn, $where = '', $params = array()) {
1684 $this->from = $from;
1685 $this->usageidcolumn = $usageidcolumn;
1686 $this->params = $params;
1687 if (empty($where)) {
1688 $where = '1 = 1';
1689 }
1690 $this->where = $where;
1691 }
1692
1693 public function from_question_attempts($alias) {
f4fe3968 1694 return "{$this->from}
0f33deaf 1695 JOIN {question_attempts} {$alias} ON " .
d1b7e03d
TH
1696 "{$alias}.questionusageid = $this->usageidcolumn";
1697 }
1698
1699 public function where() {
1700 return $this->where;
1701 }
1702
1703 public function from_where_params() {
1704 return $this->params;
1705 }
1706
1707 public function usage_id_in() {
f4fe3968 1708 return "IN (SELECT {$this->usageidcolumn} FROM {$this->from} WHERE {$this->where})";
d1b7e03d
TH
1709 }
1710
1711 public function usage_id_in_params() {
1712 return $this->params;
1713 }
1714}