Links
🪶

SQLite

Step 1: Setup

CodeGrade AutoTest runs on Ubuntu (18.04.2 LTS) machines which you can configure in any way that you want.
In the setup section of your AutoTest, you can upload any files you might need for testing, this is where you will update your database file. These files are called Fixtures and will be placed in the $FIXTURES directory on the Virtual Server.
The file structure of the server is like this:
$FIXTURES/
All of your uploaded fixtures will be here.
$STUDENT/
This is where the submission of student is placed.
Tests are executed from this directory.
After uploading any files you might need, you can run some setup to install any packages you might need.
  • Global setup script: this is where you install your additional pip packages you want to be available on the server, this is where we will install the database. The global setup only runs once and is then cached, so the student submission won't be available here yet.
  • Per-student setup script: this will run once when running the autograding for a student and will run before all tests. This is a good place to move $FIXTURES to the $STUDENT directory in case the student solution needs some input files to run correctly.
Use any command in the Global Setup Script field to install software or run your setup script

Installing the database

  1. 1.
    Add the following to the Global setup script: sudo apt install -y sqlite3. This will install SQLite.
  2. 2.
    Upload the SQLite file with the database as a fixture.

Step 2: Create your tests

Now that you have created the setup, it's time to create the actual tests. Do this by pressing the "Add Level" button and then the "Add Category" button.
All tests in AutoTest fill in a specific rubric category that you select. After selecting the category, you can start creating tests that will fill it in. Multiple test types are available in CodeGrade, which can be used together depending on your needs and wishes.

Set up an IO Test

To run queries and check if they are correct, you can use the IO test in CodeGrade.
  1. 1.
    Program to test: sqlite3 $FIXTURES/Chinook_Sqlite.sqlite
  2. 2.
    We can specify the query file in the Input arguments, by redirecting it to mysql’s stdin, for example with < songs_from_hendrix.sql. This will be the file where the students will write their query in.
  3. 3.
    Provide the expected output Manually write or copy and paste the expected output into the "Expected output" field.
  4. 4.
    Select the IO test options For this assignment, we can turn on the Ignore All Whitespace option and turn off the Substring match option. As a SQL query with superfluous output means the query is incorrect.
An example I/O Test for an SQL query.

Step 3: Start the AutoTest

Once you have created a configuration, press start and your AutoTest will start running. In the General Settings, you should have already uploaded a Test Submission, so you will see the results of this straight away.
Once it's started and your assignment is set to Open or Done, students can hand in and get immediate feedback!