Note:

If you want to create a new page for developers, you should create it on the Moodle Developer Resource site.

Question bank consistency check

From MoodleDocs
Revision as of 15:50, 18 June 2008 by Tim Hunt (talk | contribs)

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.

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.