Question database structure - Revision history

Mudrd8mz: Text replacement - "`" to ""`


2021-07-15T08:26:06Z
Text replacement - "<code (.*)>" to "<syntaxhighlight lang="$1">"

				
				
				
				
				
				← Older revision
				Revision as of 08:26, 15 July 2021
				
Line 39:
Line 39:
When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening: When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:



<code sql> <syntaxhighlight lang="sql">
SELECT SELECT
     st.id,      st.id,

`Mudrd8mz: Text replacement - "`" to ""

2021-07-14T20:26:59Z

Text replacement - "</code>" to "</syntaxhighlight>"

← Older revision		Revision as of 20:26, 14 July 2021
Line 65:		Line 65:

	ORDER BY st.attempt, st.question, st.seq_number;		ORDER BY st.attempt, st.question, st.seq_number;
	</~~code~~>		</syntaxhighlight>

	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.)		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.)

Deraadt: /* See also */

2011-06-15T06:28:44Z

TimHunt: /* Following what happens when questions are attempted */

2011-05-13T15:25:00Z

Following what happens when questions are attempted

← Older revision		Revision as of 15:25, 13 May 2011
Line 40:		Line 40:

	<code sql>		<code sql>
	SELECT st.id, st.attempt, st.question, st.seq_number,		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.newest THEN 'newest' END AS isnewest,
	CASE WHEN st.id = sess.newgraded THEN 'newgraded' END AS isnewgraded,		CASE WHEN st.id = sess.newgraded THEN 'newgraded' END AS isnewgraded,
Line 46:		Line 50:
	WHEN 5 THEN '5 (validate)' WHEN 6 THEN '6 (close&grade)' WHEN 7 THEN '7 (submit)'		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,		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		st.answer,
			st.raw_grade,
			st.grade,
			st.penalty,
			sess.sumpenalty,
			sess.manualcomment,
			st.timestamp

	FROM mdl_question_states st		FROM mdl_question_states st
	JOIN mdl_question_sessions sess ON st.question = sess.questionid AND st.attempt = sess.attemptid		JOIN mdl_question_sessions sess ON st.question = sess.questionid AND st.attempt = sess.attemptid
	WHERE st.~~attempt~~ > 0		JOIN mdl_quiz_attempts quiza ON quiza.uniqueid = sess.attemptid

			WHERE quiza.id > 0

	ORDER BY st.attempt, st.question, st.seq_number;		ORDER BY st.attempt, st.question, st.seq_number;
	</code>		</code>

TimHunt: /* Following what happens when questions are attempted */

2009-04-01T12:00:59Z

Following what happens when questions are attempted

← Older revision		Revision as of 12:00, 1 April 2009
Line 53:		Line 53:
	</code>		</code>

	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.		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.		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.

TimHunt: /* Following what happens when a question is attempted */

2009-03-24T05:46:58Z

Following what happens when a question is attempted

← Older revision		Revision as of 05:46, 24 March 2009
Line 35:		Line 35:
	* [[True/False_question_type#Database_tables\|True/false]]		* [[True/False_question_type#Database_tables\|True/false]]

	==Following what happens when ~~a question is~~ attempted==		==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:		When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:

TimHunt at 05:46, 24 March 2009

2009-03-24T05:46:03Z

@@ Line 34: / Line 34: @@
 * [[Short_Answer_question_type#Database_tables|Short answer]]
 * [[True/False_question_type#Database_tables|True/false]]
 ==Rough change-log==

TimHunt at 12:33, 5 March 2009

2009-03-05T12:33:49Z

← Older revision		Revision as of 12:33, 5 March 2009
Line 1:		Line 1:
	{{~~Template:~~Question development}}		{{Question development}}

	This page documents the database tables use by the question bank and question engine.		This page documents the database tables use by the question bank and question engine.

TimHunt at 12:27, 5 March 2009

2009-03-05T12:27:19Z

← Older revision		Revision as of 12:27, 5 March 2009
Line 1:		Line 1:
			{{Template:Question development}}

	This page documents the database tables use by the question bank and question engine.		This page documents the database tables use by the question bank and question engine.

TimHunt: New page: 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 th...

2009-03-05T12:11:16Z

New page: 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 th...

New page

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.

==Overview==

[[Image:Question_database.png]]

[[Image:Question_database.dia]] [http://projects.gnome.org/dia/ Dia ] file, should you wish to have a copy of this diagram in an editable format.

==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:

* [[Calculated_question_type#Database_tables|Calculated]]
* [[Matching_question_type#Database_tables|Matching]]
* [[Multiple_Choice_question_type#quiz_multichoice_table|Multiple choice]]
* [[Cloze_question_type#Data_structure|Multi-answer (Cloze)]]
* [[Numerical_question_type#Database_tables|Numerical]]
* [[Random_Short-Answer_Matching_question_type#Database_tables|Random short-answer matching]]
* [[Short_Answer_question_type#Database_tables|Short answer]]
* [[True/False_question_type#Database_tables|True/false]]

==Rough change-log==

===Moodle 2.0===

* New field question_sessions.flagged.
* All fields that store grades were reviewed and set to the recommended types mentioned above.

===Moodle 1.9===

* New question bank:
** New fields question.timecreated, timemodified, createdby and modifiedby.
** question_category.course changed to question_category.contextid.
** Removed field question_category.published.

===Moodle 1.7===

* question_sessions.comment renamed to manualcomment (reserved word).
* new column question.generalfeedback.

TODO, check CVS history for more.

==See also==

* [[Quiz_database_structure|Quiz database structure]]
* [[Database_schema_introduction|Database schema introduction]]

[[Category:Questions]]

← Older revision		Revision as of 06:28, 15 June 2011
Line 98:		Line 98:


	[[Category:~~Questions~~]]		[[Category:Question engine]]

← Older revision		Revision as of 08:26, 15 July 2021
Line 39:		Line 39:
	When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:		When a user is attempting questions (for example by attempting a quiz), the following query gives the best view of what is happening:

	<~~code~~ sql>		<syntaxhighlight lang="sql">
	SELECT		SELECT
	st.id,		st.id,

Question database structure - Revision history

Mudrd8mz: Text replacement - "" to ""

Mudrd8mz: Text replacement - "" to ""

Deraadt: /* See also */

TimHunt: /* Following what happens when questions are attempted */

TimHunt: /* Following what happens when questions are attempted */

TimHunt: /* Following what happens when a question is attempted */

TimHunt at 05:46, 24 March 2009

TimHunt at 12:33, 5 March 2009

TimHunt at 12:27, 5 March 2009

TimHunt: New page: 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 th...

Mudrd8mz: Text replacement - "`" to ""`

`Mudrd8mz: Text replacement - "`" to ""