|
|
| (43 intermediate revisions by 10 users not shown) |
| Line 1: |
Line 1: |
| The quiz data model has a fairly large pool of database tables, so the first step in explaining them is to provide some order. Conceptually it is possible to distinguish between the tables holding the teacher-supplied data, defining quizzes and questions, and the tables storing all the data that is generated when students interact with the quizzes and questions.
| | {{Quiz developer docs}} |
|
| |
|
| A further simplification is possible by separating out the questiontype specific tables. They are logical extensions to other tables and therefore are not necessary for understanding the general basic model. However, some information is provided for each questiontype specific table, namely which questiontype it belongs to (although that should be clear from the name), which table it extends and what the additional data is needed for.
| | This page documents the database tables used by the quiz module. |
|
| |
|
| The diagram below shows how the most important tables are linked to one another.
| | ==Quiz settings and runtime overview== |
|
| |
|
| [[image:Quiz_database_tables.gif]]
| | It is helpful to distinguish between quiz settings, which is where we store information about how the teacher has set up the quiz, and 'runtime' (not a great name) where we store information about people's attempts at the quiz. |
|
| |
|
| ==Teacher-supplied data==
| | Note that information about the attempter's interaction with each question is [[Overview_of_the_Moodle_question_engine#Database_tables|stored by the question engine]]. |
|
| |
|
| ===quiz===
| | Note: the diagram below is a bit out of date. The significant change is that quiz_question_instances has been renamed quiz_slots. |
| | [[Image:Quiz_database.png|560px]] |
|
| |
|
| The quiz table contains the definition for all the quizzes. Each quiz belongs to a course, reflected by the course id, has a name and a short descriptive text (intro), an opening and a closing time and several fields that store the settings of various quiz options, each of which is explained in the quiz help that is linked to from the quiz settings page.
| | [[Image:Quiz_database.dia]] [http://projects.gnome.org/dia/ Dia ] file, should you wish to have a copy of this diagram in an editable format. |
|
| |
|
| The only field that requires additional information is the optionsflag, which... I will put an explanation here eventually --[[User:Gustav|Gustav]] 06:02, 5 February 2006 (WST)
| | ==Common field types== |
|
| |
|
| ===quiz_questions===
| | * Fields that hold an overall score, like quiz.grade, should be NUMBER(10,5). |
| | * Fields that hold an individual question score, like quiz_question_instances.grade, should be NUMBER(12,7). |
|
| |
|
| This table constitutes the item or question bank, i.e. the repository of defined questions. The quiz_questions table defines the data that is common to questions of all types.
| | ==Detailed table descriptions== |
|
| |
|
| ;id
| | In Moodle 2.x 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. |
| :int(10) NOT NULL auto_increment,
| |
| :Primary key. This is used as a foreign key in many other tables. for example the quiz_answers table allows to define an arbitrary number of answers that are part of the question. And many questiontypes have their own tables that hold more information about the question.
| |
|
| |
|
| ;category
| | (Wouldn't it be nice if that documentation was automatically built and available online.) |
| :int(10) NOT NULL default '0',
| |
| :Foreign key: refers to an id in the table [[#quiz_categories|quiz_categories]]
| |
|
| |
|
| ;parent
| | ==Rough change-log== |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key: refers to an id in the table [[#quiz_questions|quiz_questions]]. This field is set to zero except in the case of wrapped questions as they are used for example in the multianswer questiontype. When a question wraps around any number of subquestions the subquestions will have their parent id field set to the id of the main question, thus allowing the question to find all its sub-questions (or wrapped questions). If parent is not '0' the question is never shown in a list of questions, because it is not a valid question by itself. This is also used for hiding random questions from the question list. Their parent field is simply set to their own id.
| |
|
| |
|
| ;name
| | ===Moodle 2.3=== |
| :varchar(255) NOT NULL default '',
| |
| :Question name that is used in question lists on the quiz editing pages
| |
|
| |
|
| ;questiontext
| | * New column quiz_attempts.currentpage for MDL-3054. |
| :text NOT NULL,
| | * New column quiz.navmethod for MDL-11047. |
| :Main text of the question
| | * New columns quiz.gradeperiod, quiz.overduehandling and quiz_attempts.state for MDL-3030. |
| | * Old columns quiz_reports.cron and quiz_reports.lastcron dropped (MDL-30635). (config_plugins is now used, as for other plugin types.) |
|
| |
|
| ;questiontextformat
| | ===Moodle 2.2=== |
| :tinyint(2) NOT NULL default '0',
| |
|
| |
|
| ;image
| | * Old quiz.popup column replaced by quiz.browsersecurity |
| :varchar(255) NOT NULL default '',
| |
|
| |
|
| ;defaultgrade
| | ===Moodle 2.1 (new question engine)=== |
| :int(10) unsigned NOT NULL default '1',
| |
| :Default grade for a question, usually '1'. In the wrapped parts of cloze questions defaultgrade represents the weight of the part.
| |
|
| |
|
| ;penalty
| | * Old quiz.optionflags and quiz.penaltyscheme columns replaced by quiz.preferredbehaviour. |
| :float NOT NULL default '0.1',
| | * New quiz.showblocks column. |
| :The penalty that is applied (if the respective quiz option is set) for an incorrect attempt at the question.
| | * Old quiz.review column split into seven new quiz.review* columns. |
|
| |
|
| ;qtype
| | ===Moodle 2.0=== |
| :smallint(6) NOT NULL default '0',
| |
| :The questiontype of the question. (Constants are defined in locallib.php)
| |
|
| |
|
| ;length
| | * New field quiz.introformat. |
| :int(10) unsigned NOT NULL default '1',
| | * New field quiz.questiondecimalpoints. |
| :This defines how many question numbers are required for this question. It is generally set to "1", but the description questiontype, for example, sets it to "0", reflecting the fact that it doesn't have a question number.
| | * New field quiz.showuserpicture. See MDL-3156. |
| | * All the quiz report tables are new. See [[Quiz_report_enhancements]] |
| | * All fields that store grades were reviewed and set to the recommended types mentioned above. |
| | * Never used quiz_question_versions table was removed. |
| | * New table quiz_overrides for MDL-16478. |
|
| |
|
| ;stamp
| | ===Moodle 1.9=== |
| :varchar(255) NOT NULL default '',
| |
| :Unique identifier
| |
|
| |
|
| ;version
| | * Time limit field changed to int(10). |
| :int(10) NOT NULL default '1',
| |
| :A number representing how often the question was edited.
| |
|
| |
|
| ;hidden
| | ===Moodle 1.7=== |
| :int(1) unsigned NOT NULL default '0',
| |
| :Controls whether to display a question in the question bank list in edit.php. Hiding questions is a mechanism to "delete" questions without removing them from the database and thus to allow them to be restored or "unhidden" at a later stage. Also the (unfinished and disabled) [[Quiz_developer_docs#Question_versioning|versioning feature]] uses the hidden field to prevent older versions of a question from cluttering the user interface.
| |
|
| |
|
| In addition to these static fields, which are part of the generic question definitions, there are some additional fields that are only added to the object at runtime. There are two different kinds of fields: On the one hand there are questiontype specific fields, which are also part of the static question definition, but only apply to some questions. On the other hand there are fields that refer to the question instance, i.e. the question in the context of a particular quiz. The values for these later fields can differ when a question is used in different quizzes.
| | * New table quiz_feedback. |
|
| |
|
| The runtime fields are added to question objects with the function <code>quiz_get_questions_options()</code> is called. This in turn calls the questiontype specific <code>get_question_options()</code> method for to add the options field.
| | ==See also== |
|
| |
|
| ;maxgrade
| | * [[Question_database_structure| Question bank/engine database structure]] |
| :The maximum grade available for this question in this quiz, i.e. the grade for a correct response.
| | * [[Database_schema_introduction|Database schema introduction]] |
| | |
| ;instance
| |
| :Foreign key: refers to an id in the table [[#quiz_instances|quiz_instances]]
| |
| | |
| ;options
| |
| :Optional field. It provides a namespace for any questiontype specific information that may be stored in this field. It is generally set by the <code>get_question_options</code> method.
| |
| | |
| ;name_prefix
| |
| :The prefix to be used on all interactions printed as part of the question.
| |
| | |
| We now deal in alphabetical order with the remaining tables for the teacher-provided data.
| |
| | |
| ===quiz_answers===
| |
| | |
| This table allows a common way to store one or more teacher-defined answers for each question. It is not mandatory for a questiontype to make use of this table however. A questiontype may choose to store it's teacher-defined answers in an entirely different way, or even to do away with teacher-defined answers and use some other method to mark the student-supplied answer. For example it could calculate the correct answer on the fly.
| |
| | |
| ;id
| |
| :int(10) unsigned NOT NULL auto_increment,
| |
| :Primary key
| |
| | |
| ;question
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key to [[#quiz_questions|quiz_questions]] table.
| |
| | |
| ;answer
| |
| :text NOT NULL,
| |
| :The string that represents the teacher-defined answer that will be compared to the student responses for grading and feedback purposes. The format of this field is entirely up to the question type.
| |
| | |
| ;fraction
| |
| :varchar(10) NOT NULL default '0.0',
| |
| :The fraction of the total mark that the student should earn for giving this particular answer. This could be a negative number for wrong answers. Note that it is stored in a varchar(10) field.
| |
| | |
| ;feedback
| |
| :text NOT NULL,
| |
| :Text that can be displayed to student as feedback when the student's responses match this particular answer.
| |
| | |
| In the past there was also a sequence number field was sometimes used to store the order of the different answers and was set to '0' if the order was of no importance.
| |
| | |
| ===quiz_categories===
| |
| | |
| Categories are provided as a way to organize questions. Each category has a name and a descriptive text (info) and the sortorder as metadata. Categories allow hierarchical nesting via the parent id and can be private or published, i.e. they can be made available to teachers in other courses.
| |
| | |
| Since categories are simply a means for organising questions they are not vital for understanding how the quiz module works.
| |
| | |
| ;id
| |
| :int(10) unsigned NOT NULL auto_increment,
| |
| :Primary key
| |
| | |
| ;course
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key to the course table
| |
| | |
| ;name
| |
| varchar(255) NOT NULL default '',
| |
| | |
| ;info
| |
| :text NOT NULL,
| |
| | |
| ;publish
| |
| :tinyint(4) NOT NULL default '0',
| |
| | |
| ;stamp
| |
| :varchar(255) NOT NULL default '',
| |
| | |
| ;parent int(10)
| |
| :unsigned NOT NULL default '0',
| |
| | |
| ;sortorder int(10)
| |
| :unsigned NOT NULL default '999',
| |
| | |
| ===quiz_question_instances===
| |
| | |
| Questions can be assigned different grades in different quizzes. These are stored in this table.
| |
| | |
| While, after a small extension, this table could also fulfill the purpose of storing the order of the questions in a quiz, this is currently still done in the questions field in the [[#quiz|quiz]] table.
| |
| | |
| ;id
| |
| :int(10) unsigned NOT NULL auto_increment,
| |
| :Primary key
| |
| | |
| ;quiz
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key to the id of the [[#quiz|quiz]] table.
| |
| | |
| ;question
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key to the id of the [[#quiz_questions|quiz_questions]] table.
| |
| | |
| ;grade
| |
| :smallint(6) NOT NULL default '0',
| |
| :The maximum grade assigned to this question in this quiz. This grade was set by the teacher on the [[mod/quiz/edit|quiz editing page]].
| |
| | |
| ===quiz_question_versions===
| |
| | |
| This feature is not finished and disabled. The table structure may still change. See [[Quiz developer docs#Question versioning]] for a discussion.
| |
| | |
| ;id
| |
| :int(10) unsigned NOT NULL auto_increment,
| |
| | |
| ;quiz
| |
| :int(10) unsigned NOT NULL default '0',
| |
| | |
| ;oldquestion
| |
| :int(10) unsigned NOT NULL default '0',
| |
| | |
| ;newquestion
| |
| :int(10) unsigned NOT NULL default '0',
| |
| | |
| ;userid
| |
| :int(10) unsigned NOT NULL default '0',
| |
| | |
| ;timestamp
| |
| :int(10) unsigned NOT NULL default '0',
| |
| | |
| ==Student-created data==
| |
| | |
| ===quiz_attempts===
| |
| | |
| In the quiz_attempts table a record is created each time a user starts an attempt at a quiz. This is one of the important tables and the corresponding $attempt object is passed between many of the quiz module functions and methods.
| |
| | |
| ;quiz
| |
| :int(10) unsigned NOT NULL default '0',<br />The id of the quiz that is attempted
| |
| ;userid
| |
| :int(10) unsigned NOT NULL default '0',<br />The id of the user who is attempting the quiz
| |
| ;attempt
| |
| :smallint(6) NOT NULL default '0',<br>It is possible for a user to attempt a quiz several times, therefore the number of the attempt is stored in this field.
| |
| ;sumgrades
| |
| :varchar(10) NOT NULL default '0.0',<br>The (unscaled) grade for the attempt, i.e. if the grades assigned to the questions add up to 8, but the maximum grade for the quiz is set to 10, then the sumgrades field can contain 8 at maximum.
| |
| ;timestart
| |
| :int(10) unsigned NOT NULL default '0',<br>The timestart field is set to the current time when an attempt is started and is never changed afterwards.
| |
| ;timefinish
| |
| :int(10) unsigned NOT NULL default '0',<br>The timefinish field is set to "0" initially and to the current time when the attempt is closed. This is exploited at several places in the code to determine whether an attempt has been closed or not (i.e. closed = timefinish > 0). For all other modifications of an attempt record the timemodified field should be changed as well.
| |
| ;timemodified
| |
| :int(10) unsigned NOT NULL default '0',<br>This should be changed each time data in the record is modified.
| |
| ;layout
| |
| :text NOT NULL,<br>a comma separated list of question ids, with a "0" denoting a page break. Usually the comma separated list ends with ",0".
| |
| ;preview
| |
| :tinyint(3) unsigned NOT NULL default '0',<br>A flag that marks a teacher preview (i.e. an attempt by a user with teacher privileges) that may be automatically deleted when the quiz is previewed again, and which is not taken into account when viewing statistics.
| |
| | |
| ===quiz_states===
| |
| | |
| States are saved for each interaction with a question. This allows a review of the complete history of a user's interactions with individual questions.
| |
| | |
| ;attempt
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key: refers to an id in the table quiz_attempts
| |
| | |
| ;question
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key: refers to an id in the table quiz_questions. The attempt id (or the quiz id) and the question id together sufficiently identify a question instance, which a state logically belongs to.
| |
| | |
| ;originalquestion
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :Foreign key: refers to an id in the table quiz_questions. It identifies which question was in use when the student attempted the question. This is part of the [[Quiz_developer_docs#Question_versioning|question versioning]] code.
| |
| | |
| ;seq_number
| |
| :int(6) unsigned NOT NULL default '0',
| |
| :A counter that provides information about the sequence in which the states were created.
| |
| | |
| ;answer
| |
| :text NOT NULL,
| |
| :This field is deleted during runtime and replaced with the responses field. For legacy reasons it is still called answer in the database. Questiontypes can store students' responses (usually in a serialized format) in this field using the method save_session_and_responses. Questiontypes are allowed to deviate from this and handle their response storage in any desired way. Some questiontypes do not use this field but instead store the student response in their own table extending this table.
| |
| | |
| ;timestamp
| |
| :int(10) unsigned NOT NULL default '0',
| |
| :A timestamp to record when the state was created. This could mainly be useful for audit purposes.
| |
| | |
| ;event
| |
| :int(4) unsigned NOT NULL default '0',
| |
| :Records the event or interaction type that lead from the previouse state to the current one. The field can take the value of any of the following constants (defined in locallib.php):
| |
| :*EVENTOPEN: The attempt has just been opened and this is the initial state of a question, i.e. the user has seen the question did not interact with it yet.
| |
| :*EVENTSAVE: The responses are just being saved, either because the student requested this explicitly or because the student navigated to another quiz page.
| |
| :*EVENTVALIDATE: The student requested a validation of the responses. (This is not supported by all questiontypes.)
| |
| :*EVENTGRADE: The responses are being graded but the question session is not closed. This is generally the case for adaptive questions.
| |
| :*EVENTCLOSE: The responses are being graded and the question session is closed. Usually this happens because the whole attempt closes, either because the student requests it or because the time is up or because we are beyond the due date.
| |
| :*EVENTDUPLICATEGRADE: This is a strange one. It indicates that the responses would have been graded had they not been found to be identical to previous responses.
| |
| | |
| ;grade
| |
| :varchar(10) NOT NULL default '0.0',
| |
| :Calculated from the raw grade by deducting the penalty and rescaling to the defaultgrade value of the question.
| |
| | |
| ;raw_grade
| |
| :varchar(10) NOT NULL default '',
| |
| :The grade that was achieved for the question scaled to the question's weight or grade as assigned in the quiz_question_instances table
| |
| | |
| ;penalty
| |
| :varchar(10) NOT NULL default '0.0',
| |
| :The penalty incurred during the transition from the previous state to this one. This is different to the cumulative penalty, which can be calculated by adding up all the penalties from previous marking events.
| |
| In addition to the database fields a few fields are added to the states at runtime. They are dealt with by the questiontypes with the methods <code>create_session_and_responses</code>, <code>restore_session_and_responses</code> and <code>save_session_and_responses</code> and are defined as follows.
| |
| | |
| ;sumpenalty
| |
| :The cumulative penalty, which can be calculated by adding up all the penalties from previous marking events. For efficiency these cumulative penalties are stored in the table [[#quiz_newest_states|quiz_newest_states]] so that they do not need to be re-calculated each time.
| |
| | |
| ;changed
| |
| :This records whether a change has taken place to the runtime state. This is set to false when the state is restored from the database. Any code which changes the question state must set this field to true amd must increment seq_number. The <code>quiz_save_question_session()</code> function will save the new state object to the database if the field is set to true.
| |
| | |
| ;responses
| |
| :This is automatically set to the value of the database field <code>answer</code> before that one is removed. The responses field contains an array of responses. In the default case of a single response the value can be found in ->responses['']. For questiontypes using several HTML form elements for their responses the array contains a value for each of the interaction elements. The indicies are determined by the name of the elements after the name_prefix is stripped.
| |
| | |
| ;last_graded
| |
| :This field contains another state object representing the most recent graded state in the history of this question attempt. This is necessary, because if a state merely represents a save interaction, it should not affect the session in any way. Therefore another grade interaction has to proceed from the last graded state.
| |
| | |
| ;options
| |
| :Optional field. Similarly to the options field in the question object, this field provides a namespace for any questiontype specific information. The difference is that the information in the state->options field should be state specific, whereas the question->options field should be static.
| |
| | |
| We now deal in alphabetical order with the remaining tables holding the student-created data.
| |
| | |
| ===quiz_grades===
| |
| | |
| The quiz_grades table merely stores a student's awarded grade for a quiz. Since it is possible to allow several attempts on a quiz, the grade stored is calculated depending on the quiz setting grademethod. This table exists mainly for convenience, because the values of its fields can be recalculated.
| |
| | |
| ===quiz_newest_states===
| |
| | |
| This table exists only for efficiency reasons:
| |
| #Via its 'newest' and 'newgraded' fields it gives attempt.php a way to quickly find the newest state and the newest graded state for an attempt. It allows the construction of SQL to select all the states that need to be loaded on attempt.php or review.php.
| |
| #Via its 'sumpenalty' field it gives quiz_apply_penalty() a quick way for getting at the accumulated penalty that needs to be applied. Without this field the penalties from all previous graded states would have to be added up each time. Not a big deal actually because this could be achieved with a single SQL query (using SUM) but this field was introduced when we still had the multiplicative penalty scheme around which would have been more difficult to recompute.
| |
| | |
| This table was introduced in Moodle 1.5 and is not populated for all states during the upgrade because on sites with a lot of existing states that could take too long. Rather it is done whenever needed by quiz_upgrade_states().
| |
| | |
| ==Questiontype specific tables==
| |
| | |
| ===quiz_calculated===
| |
| | |
| The quiz_calculated table is an extension to the quiz_questions table by the calculated questiontype. However, it would be more suitable to change that to be an extension of the quiz_answers table, which, from a data perspective, is already possible, since an answer id is stored in the answer field. The questiontype code would need some changes to take this into account, however.
| |
| | |
| ===quiz_dataset_definitions===
| |
| | |
| The quiz_dataset_definitions table belongs to the abstract datasetdependent questiontype, which is currently only used by the calculated questiontype. It is an indirect extension to the quiz_questions table, because the quiz_question_datasets table can link a question to one or more datasets. Each dataset represents a variable, that is used either in the questiontext or in the answer to a dataset dependent question.
| |
| | |
| ===quiz_dataset_items===
| |
| | |
| Dataset items can be created for each dataset. The quiz_dataset_items table stores these possible values for the variables defined in the quiz_dataset_definitions table.
| |
| | |
| ===quiz_match===
| |
| | |
| The quiz_match table belongs to the match questiontype and extends the quiz_questions table. It is only used in the code for saving matching questions and can therefore be considered redundant.
| |
| | |
| ===quiz_match_sub===
| |
| | |
| The quiz_match_sub table belongs to the match questiontype and extends the quiz_questions table. It stores the pairs of questions and answers (as strings) that need to be matched for a correct solution.
| |
| | |
| ===quiz_multianswers===
| |
| | |
| The quiz_multianswers table belongs to the multianswer questiontype and is an extension of the quiz_questions table. It merely stores a comma separated list of question ids in the sequence field, which is important, because that's the only way to know which sub question belongs to which position in the questiontext.
| |
| | |
| ===quiz_multichoice===
| |
| | |
| The quiz_multichoice table belongs to the multichoice questiontype and is an extension of the quiz_questions table. The layout field does not seem to be used, the answers field stores the order of the answers (should be superseded by the seq_number field in the quiz_answers table) and the single field is a flag signaling, whether only one option or multiple options can be chosen.
| |
| | |
| ===quiz_numerical===
| |
| | |
| The quiz_numerical table belongs to the numerical questiontype and is an extension of the quiz_answers table, defining a tolerance value for each answer.
| |
| | |
| ===quiz_numerical_units===
| |
| | |
| The quiz_numerical_units table is used by the numerical questiontype and the calculated questionype. It extends the quiz_questions table, defining an arbitrary number of units that can be used in the responses.
| |
| | |
| ===quiz_question_datasets===
| |
| | |
| The quiz_question_datasets table is used by dataset dependent questionypes (i.e. calculated) to link datasets to questions.
| |
| | |
| ===quiz_randomsamatch===
| |
| | |
| This extension to the quiz_questions table simply stores how many shortanswer questions should be randomly chosen to build this randomsamatch question.
| |
| | |
| ===quiz_rqp===
| |
| | |
| No information.
| |
| | |
| ===quiz_rqp_states===
| |
| | |
| No information.
| |
| | |
| ===quiz_rqp_types===
| |
| | |
| No information.
| |
| | |
| ===quiz_shortanswer===
| |
| | |
| The quiz_shortanswer table belongs to the shortanswer questiontype and is an extension of the quiz_questions table. The answers field stores a comma separated list of answer ids, which is redundant. The only valuable piece of information contained in this table is the usecase field, which is used to decide whether to do a case sensitive or case insensitive comparison for grading.
| |
| | |
| ===quiz_truefalse===
| |
| | |
| An extension of the quiz_questions table the quiz_truefalse table stores the answer ids for the true and for the false answers.
| |
|
| |
|
|
| |
|
| [[Category:Quiz]] | | [[Category:Quiz]] |
