Hi Sara
CodeRunner provides a general framework for setting computer-graded questions. The simple supplied sql question type is just a sample to get people going, so it is indeed important to understand that as a first step.
I'll assume you've read the general documentation on how CodeRunner works (starting with The Architecture of CodeRunner) and focus just on the supplied sql
question type.
Firstly, I suggest you import the two exported questions in the quiz you refer to into your question bank using the standard Moodle question import functionality (they're both Moodle XML files). Then, if you open them for editing, you'll be able
to see how those particular questions are set up.
If you open the Question type details section, you'll see the following:
CodeRunner question type: sql
A SQL question type, using sqlite3, run from Python3. sqlite3 must be installed on the Jobe server for this question type.
The working directory is searched for files with an extension '.db'. If there is only one such file, it is used as the sqlite3 database for all tests. Multiple .db files currently issues an error message; a possible extension is to use different
db files for each test, e.g. in sorted order.
For each test, an sqlite3 command script of the form
.mode column
.headers on
<code in extra>
<student answer>
<testcode>
is run.
A fresh copy of the db file is used for each test case.
A template parameter columnwidths can be used to set the report column widths. By default sqlite3 sets each column width to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data.
A template string like
{"columnwidths": [10, 50, 10, 5]}
will instead use column widths of 10, 50, 10 and 5 for the first four columns.
If you also open the Support files section, you'll see there's a single file q1.db. This is an sqlite3 database file; you need to prepare one of these in advance to load into the question. [You do realise you're restricted to sqlite3, do you? If your heart is set on Postgresql, you're going to have compatibility problems.] This file is used (created afresh for each test) as the operational database.
In the first Simple select question, there are two test cases. The first test case has no extra template data, and the test code (the first field in the the test case) is simply a comment to the student that their code is being run with the original db. So in this case the sqlite3 script reduces to the first two sqlite3 control statements followed by the student's answer. This is just a select statement, and the output of that is then displayed in the result table, for comparison with the Expected output. If they match exactly the student gets the marks, otherwise they have to try again.
The second test case has a non-empty Extra template data field, so that code is run before the student's code. In this case it just inserts an extra row into the customers table.
The second question is similar but the supplied database file has two tables and the question requires students to do a join.
These questions are just intended as a proof of concept. I don't teach SQL myself, but I would almost certainly be wanting more complex question types if I did. To write your own question types, though, you need to be more familiar with the CodeRunner framework; I suggest you get a bit more familiar with the supplied question type first. But if you want to inspect the underlying question type template, you can click the Customise check box in the question authoring form. There , in the Template field, you'll see the Python code that looks in the current working directory for any suitable .db files (exactly one is expected at present - you can extend the question type to use a different db file for each test case once you have a deeper understanding of it). Then there's a block of code that uses the Twig template engine to essentially iterate through the test cases, building an sqlite3 program for each test as outlined above, then executing sqlite3 in a subprocess to run that program.
Hope that's enough to get you going.
