Question database structure
This page documents the database tables use by the question bank and question engine.
The distinction between the question bank and the question engine is that the question bank stores the question definitions, organised into categories, while the question engine stores the information a user's attempts at a question or questions.
Common field types
- Fields that hold a question score, like question.defaultgrade or question_answers.fraction, should be NUMBER(12,7).
- Question text, feedback, etc. should be TEXT(short).
Detailed table descriptions
In Moodle 2.0 dev, you can get these by going to Administration -> Development -> XMLDB and clicking on the [Doc] link next in any of the relevant rows (mod/quiz/db, mod/quiz/report/xxx/db). Looking directly there is much more likely to be up-to-date than relying on information that has been copied here.
(Wouldn't it be nice if that documentation was automatically build and available online.)
Individual question types
The following core question types have their own database tables:
- Multiple choice
- Multi-answer (Cloze)
- Random short-answer matching
- Short answer
Following what happens when questions are attempted
When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:
SELECT st.id, st.attempt, st.question, st.seq_number, CASE WHEN st.id = sess.newest THEN 'newest' END AS isnewest, CASE WHEN st.id = sess.newgraded THEN 'newgraded' END AS isnewgraded, CASE st.event WHEN 0 THEN '0 (open)' WHEN 2 THEN '2 (save)' WHEN 3 THEN '3 (grade)' WHEN 5 THEN '5 (validate)' WHEN 6 THEN '6 (close&grade)' WHEN 7 THEN '7 (submit)' WHEN 8 THEN '8 (close)' WHEN 9 THEN '9 (manualgrade)' ELSE CAST(st.event AS VARCHAR) END AS event, st.answer, st.raw_grade, st.grade, st.penalty, sess.sumpenalty, sess.manualcomment, st.timestamp FROM mdl_question_states st JOIN mdl_question_sessions sess ON st.question = sess.questionid AND st.attempt = sess.attemptid JOIN mdl_quiz_attempts quiza ON quiza.uniqueid = sess.attemptid WHERE quiza.id > 0 ORDER BY st.attempt, st.question, st.seq_number;
I believe that works in both Postgres and MySQL. You probably need to adjust the WHERE clause so that only returns the rows you are interested in. (Actually, does not quite work in MySQL. You need to change 'CAST(st.event AS varchar)' to 'st.event' in the middle.)
Watching the results of that query as you work through a quiz attempt with a test user account is a very good way to work out what is going on in the code and find bugs.
- New field question_sessions.flagged.
- All fields that store grades were reviewed and set to the recommended types mentioned above.
- New question bank:
- New fields question.timecreated, timemodified, createdby and modifiedby.
- question_category.course changed to question_category.contextid.
- Removed field question_category.published.
- question_sessions.comment renamed to manualcomment (reserved word).
- new column question.generalfeedback.
TODO, check CVS history for more.