Running SQL in CodeRunner

Running SQL in CodeRunner

by Patrick Wang -
Number of replies: 8

Dear all,

Previously I taught Java and Python in my school, and CodeRunner was of great help to my teaching. Thanks to Richard and your team!

This semester I am teaching database, so I wonder, Is it possible to integrate SQL in CodeRunner?

Thanks.



Regards,

Patrick

In reply to Patrick Wang

Re: Running SQL in CodeRunner

by Richard Lobb -

If you're happy to use sqlite3, SQL questions are straightforward (thanks to Trent Lewis for this suggestion). Using a server like mysql is way harder.

I set up a demo just last week of a simple sqlite3-based SQL question type  - see this quiz. I attach the prototype and the two sample questions from that quiz. These are experimental and have had negligible testing - I haven't even checked the download. Please let me know if it does/doesn't work for you. If it's useful I'll include it in the next CodeRunner release.

Richard


In reply to Richard Lobb

Re: Running SQL in CodeRunner

by Patrick Wang -

Hi Richard,

Thanks for your advice. Let me try it out.



Regards,

Patrick.

In reply to Patrick Wang

Re: Running SQL in CodeRunner

by Sergi García -
Hi Richard.
I'm very interested in use things like MySQL. I know it is harder to configure but I want to collaborate.

I was thinking in something like an script that imitates SQLite interface and that script runs a query in a mysql server or something like that.

What is your opinion about that?

Regards, Sergi.
 
In reply to Sergi García

Re: Running SQL in CodeRunner

by Sergi García -

I have audited the sample and I watched it is a hacked Python :) Very good job. Maybe I will try to do the same using a mySQL server customizing code.


Thanks for everything :)

In reply to Sergi García

Re: Running SQL in CodeRunner

by Richard Lobb -

The problem with running SQL questions on a mysql server or similar is that each question, or in fact each separate test case, needs to be run in a database context set up by the question author. It should be possible to grade multiple questions concurrently. The tests should be "stateless"': a question might ask students to insert data, and the test might use a select to verify the insertion, but once the test is fully complete there should be no residual database change. Lastly the tests must be secure in the sense that one user cannot see another user's database.

It's difficult to achieve all these requirements with (say) mysql. You could ask the question author to provide a mysql dump of a (temporary) database, which could be loaded for each test, then purged on completion, but it's not trivial to set this up and nor is it a very nice user interface for the author.

If you make any progress on this, please post back with your findings. But if it were me teaching SQL, I'd be using sqlite3 :)

Richard

In reply to Richard Lobb

Re: Running SQL in CodeRunner

by Sergi García -
I think you are right.

I was only thinking in Select exercises. With SQLite you can only simple copy the file (as you do in the script) and modify that.

My main aim of using mySQL is because teachers askt me (they are teching using that database,  but in mi opinion differences are minor https://stackoverflow.com/questions/751160/mysql-and-sqlite-differences-in-sql and I think is a better idea focus on SQLite.

I want to ask a question. I'm reading JOBE documentation and I have a doubt. In wich directory should be put the database.db file in order to test SQLite questions?

Thanks :)


In reply to Sergi García

Re: Running SQL in CodeRunner

by Richard Lobb -

You don't upload the sqlite database file to jobe - you simply include it as a support file in the CodeRunner question itself. That way every question has its own database.

Richard

In reply to Richard Lobb

Re: Running SQL in CodeRunner

by Trent Lewis -

As Richard states, you do need to copy the database file to the jobe server, but specify it as a support. These are my settings



editing a coderunner question for a database file


coderunner sql support file