Question bank consistency check

There are certain internal consistency things that should be true in the database, but which may no longer be the case due to past (or present bugs). This page lists some checks you can run, and in some cases gives ways to fix any inconsistencies that are found.

I guess the checks on this page should be converted to Healtcentre checks, like in this screenshot: http://moodle.org/mod/forum/discuss.php?d=78622#p446361.

For random questions, question.parent should equal question.id

So the following query should return no records:

SELECT * FROM mdl_question WHERE qtype = 'random' and parent <> id

This will not be true for random questions restored from backup before MDL-5482 was fixed. It should be safe to fix it by running

UPDATE mdl_question SET parent = id WHERE qtype = 'random' and parent <> id

Indeed, this is done automatically on update to 1.9.1.

For multianswer questions, subquestions should have the right parent

For each question whose id is listed in question_multianswer.sequence, its question.parent field should equal question_multianswer.question. That is, the following query should return no records.

SELECT * FROM mdl_question q
    JOIN mdl_question_multianswer qma ON POSITION(',' || q.id || ',' IN ',' || qma.sequence || ',') > 0
WHERE qma.question <> q.parent

(This query uses SQL standard string concatenation and the position function. There are probably some database engines that don't support this properly. This work on Postgres.)

Now that MDL-14750 has been fixed, this should not happen any more, and there is a database upgrade that will be run when you upgrade to 1.9.1 that should fix any old instances of this problem.

For multianswer questions, subquestions should be in the same category as their parents

This test only makes sense if question.parent is correct for all your subquestions, that is, the the previous test passes.

If the previous test passes, then this query should return no records:

SELECT * FROM mdl_question q
    JOIN mdl_question parent_q ON parent_q.id = q.parent
WHERE q.category <> parent_q.category

Now that MDL-10899 has been fixed, this should not happen any more, and there is a database upgrade that will be run when you upgrade to 1.9.1 that should fix any old instances of this problem.

To fix this and the previous problem on older versions, execute the code in question_multianswer_fix_subquestion_parents_and_categories in /question/type/multianswer/db/upgrade.php from the 1.9 stable branch.

Only multianswer and random questions should appear as the parent of another question

(Unless someone invents an interesting new question type.) The following query should return no records:

SELECT * FROM mdl_question q
    JOIN mdl_question parent_q ON parent_q.id = q.parent
WHERE parent_q.qtype NOT IN ('random', 'multianswer')

Question categories should all belong to a valid context

This applies from 1.9 onwards. The following query should return no records.

SELECT qc.*, (SELECT COUNT(1) FROM mdl_question q WHERE q.category = qc.id) AS numquestions
FROM mdl_question_categories qc
    LEFT JOIN mdl_context con ON qc.contextid = con.id
WHERE con.id IS NULL

If you have any orphaned categories (we seem to, in our database) then if they don't contain any questions, they can just be deleted. If they do contain questions, then think about moving the questions to another category before deleting them.

Question categories should belong to the same context as their parent

The following query should return no records.

SELECT parent_qc.id AS parent, child_qc.id AS child, child_qc.contextid
FROM mdl_question_categories child_qc
    JOIN mdl_question_categories parent_qc ON child_qc.parent = parent_qc.id
WHERE child_qc.contextid <> parent_qc.contextid

Documentation