MySQL- or SQLite-DB as source in Moodle ACE Coderunner filter

MySQL- or SQLite-DB as source in Moodle ACE Coderunner filter

by Jörg Linke -
Number of replies: 3

I have managed to make some SQL-based questions running in a quiz by uploading the requested database  as an SQLite file as attachment.

But I don't understand, how I can provide the database in a interactive ACE inline element.

My source code at the moment: 

<pre class="language-sql" data-ace-interactive-code="">
<code>
select *
from customers
</code></pre>

  • First problem: the interpreter tells me, that it don't know the language sql.
  • Second problem: of cource, the source customers is unknown. 

Any help is highly welcome.

Best regards from Germany

Jörg




In reply to Jörg Linke

Re: MySQL- or SQLite-DB as source in Moodle ACE Coderunner filter

by Richard Lobb -
The ace-in-line filter sends code directly (more or less) CodeRunner's Jobe sandbox for execution. Jobe runs code in a number of languages, but SQL isn't one of them. The SQL question type in CodeRunner is actually a Python3 question that sends the queries to sqlite3. It might be instructive to click Customise on an SQL question and inspect the template.

To run SQL questions with the Ace-in-line filter you need to do the same sort of thing. But it's not trivial and uploading the database file is a bit tricky.

Firstly, to upload an sqlite3 database file for the code to operate on, you need to use one of the two file-upload methods supported by ace-in-line. However, neither method supports binary files, and sqlite3 databases are binary. So you have to first encode the database in a text format such as base64 and insert it into a hidden textarea (assuming use of the data-file-taids method).

Next, you need to insert a <script> element that defines a JavaScript function for use as a code-mapper, to wrap the SQL code from the Try it! box into a Python3 program that decode the database file back to binary and then run the sql code using sqlite3. 

Lastly you need to configure a <pre> element that uses the data-ace-lang attribute to set the Ace language to sql, the data-file-taids attribute to get the file from the (hidden) textarea and the data-code-mapper to reference the wrapper function you wrote in the <script> element.

If, after reading all that, you're still interested, I've added a demo of an SQL Try it! box to the Ace-in-line filter demo on this site. Scroll down to the section SQL: another example of using a code-mapper. Play with it a bit to see if it does what you want. If so, view the source of page to see what's going on under the hood. You should be able to mostly just copy-and-paste the raw HTML out of that page for your own use.
In reply to Richard Lobb

Re: MySQL- or SQLite-DB as source in Moodle ACE Coderunner filter

by Jörg Linke -
Thank you very much for your advises and your big work. With copy and paste i was able to make your demo run on my moodle site.
I understood, that i can import any sqlite db, after encoding in base64 and uploading in the textarea field. But i don't know how to encode the hole sqlite database. Of course i did research, but haven't found any answer.
In reply to Jörg Linke

Re: MySQL- or SQLite-DB as source in Moodle ACE Coderunner filter

by Richard Lobb -
On Linux on Mac there's a command-line program called base64. On Windows, certutil can do it. But if you're command-line-averse there are websites that do it too, e.g. https://www.base64encode.org/

If you don't like base64, you could instead load the textarea with the output of the sqlite3 .dump command, which generates the sql commands to reconstruct the database. That's a text file, so no encoding is required. But you'd have to change the wrapper to start with an empty database and run the SQL commands first, before running the user's SQL.