## Question Authors' Forum

### SQL CodeRunner Newbie

by Sara Cohen -
Number of replies: 11

I am a long time database professor, and I typically use Postgresql for my students SQL practice. I am VERY much interested in including CodeRunner questions on SQL in my Moodle course, now that we are all online. Unfortunately, I can't seem to make heads or tails of the form in which I define an SQL question. I have absolutely no idea what does where, or what the meaning is of most fields in the form. I searched for a video (or a page with screenshots) which would take me step by step through defining a "Hello World" SQL question, but could not find one.

Can someone possibly walk me through the template and the process of defining a simple question of this type? Or point me in the direction of explanations? (I saw https://coderunner.org.nz/mod/quiz/view.php?id=194I but dont know how to define such questions myself). I would be very very grateful!

Sara

### Re: SQL CodeRunner Newbie

by Richard Lobb -

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
<code in extra>
<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.

### Re: SQL CodeRunner Newbie

by Sara Cohen -
Thank you for your detailed reply. It is working great for me now and I am sure will be a huge help in my online teaching!

All the best,
Sara

### Re: SQL CodeRunner Newbie

by Fatma Bouali -

Hi Richard,

I have imported your the two SQL questions demos questions. I have Installed Sqilte3.

I don't unterstand why I have this error ?

Thank you

### Re: SQL CodeRunner Newbie

by Fatma Bouali -

The error

### Re: SQL CodeRunner Newbie

by Richard Lobb -

It looks like sqlite3 is not installed correctly on your Jobe server. sqlite3 is part of the standard Jobe install nowadays but how old is your Jobe server? What happens if you log in to it and type the command sqlite3?

### Re: SQL CodeRunner Newbie

by Fatma Bouali -

Hi Richard,

My University has installed SQLITE3 on JOBE SERVER and it works.

However the results of SQLITE 3 (the tables or the error) are not displayed

I have attached the file comparing  the outputs I had with those of the demo test on your website.

Best regards

Fatma

### Re: SQL CodeRunner Newbie

by Richard Lobb -

Hi Fatma

You have an error in your answer, which finishes with the line

order by

Order by what? :)

That generated the syntax error shown at the top of your Got output:

Error: near line 6: near ";": syntax error.

But I have to admit that the additional traceback information does tend to drown out the key lines at the top. That's not very user-friendly. It didn't used to behave like that - I suspect sqlite3 has been changed to return a non-zero exit status when there's a syntax error, rather than just printing an error to the standard-error stream.

To improve the quality of the feedback you need to edit the question's template, either by customising your questions or by editing the master prototype BUILT_IN_PROTOTYPE_sql (for which you need to be a sys admin). A fix that works in most cases is to replace the line 41

    raise Exception("sqlite3 error: " + str(e))

with just

    pass

The output should then be just

***Run error***
Error: near line 6: near ";": syntax error
However, I'm not totally happy with that fix as it will then mask a more-serious error, such as sqlite3 not being installed properly. A proper fix that works for all likely versions of python3 needs a bit more thought and I'm rather busy getting a new course with 1000 students enrolled going in a week's time!

Or ... you could just train your students to read only the first two lines of the error message and ignore the rest :-)

### Re: SQL CodeRunner Newbie

by Fatma Bouali -

Hi Richard,

Best regards

Fatma

### Re: SQL CodeRunner Newbie

by Atanas Nikolov -
Hi Richard,

I imported two .xml file and receive the next error:

"qtype_coderunner/Prototype sql is unavailable in this context, or does not exist."

We have working jobe server and sqlite3 is installed.
We use old version of Moodle - 3.3 and CodeRunner - 3.7.9.

May you send me the code for Customization of sql_question?

Thanks a lot
Atanas

### Re: SQL CodeRunner Newbie

by Richard Lobb -

I attach the standard SQL prototype. But please realise that with older versions of CodeRunner like yours the same error message would be issued if you had multiple copies of a prototype as if you had zero copies of it. If, after importing this prototype, you still get that error, it's probably because you've somehow finished up with multiple copies of an SQL prototype question. You should try to hunt down the duplicates.

Of course, you will need sqlite3 on your Jobe server for this question type to work.

Richard