Question Authors' Forum

Running SQL in CodeRunner

 
Picture of Patrick Wang
Running SQL in CodeRunner
by Patrick Wang - Sunday, 22 October 2017, 5:28 PM
 

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

Picture of Richard Lobb
Re: Running SQL in CodeRunner
by Richard Lobb - Monday, 23 October 2017, 10:23 PM
 

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


Picture of Patrick Wang
Re: Running SQL in CodeRunner
by Patrick Wang - Tuesday, 24 October 2017, 1:44 AM
 

Hi Richard,

Thanks for your advice. Let me try it out.



Regards,

Patrick.

Picture of Sergi García
Re: Running SQL in CodeRunner
by Sergi García - Wednesday, 25 October 2017, 10:56 PM
 
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.
 
Picture of Sergi García
Re: Running SQL in CodeRunner
by Sergi García - Thursday, 26 October 2017, 7:29 AM
 

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

Picture of Richard Lobb
Re: Running SQL in CodeRunner
by Richard Lobb - Thursday, 26 October 2017, 2:36 PM
 

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

Picture of Sergi García
Re: Running SQL in CodeRunner
by Sergi García - Thursday, 26 October 2017, 10:11 PM
 
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 :)


Picture of Richard Lobb
Re: Running SQL in CodeRunner
by Richard Lobb - Thursday, 26 October 2017, 11:03 PM
 

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

Picture of Trent Lewis
Re: Running SQL in CodeRunner
by Trent Lewis - Thursday, 9 November 2017, 3:05 PM
 

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