🐬
MySQL

Overview
An AutoTest V2 configuration for a MySQL assignment has two stages:
The Setup stage: this is where you setup the auto grading environment. You can install packages and upload files. This builds into an image on which all tests are executed. All the configuration here only runs once for all submissions;
The Tests stage: this is what students will see and where you can configure all your tests that you want to run on every student’s code. Everything that you specify here is executed in the working directory of the student.
Steps are created in a “Scratch-like” block layout and can be dragged and dropped, reordered and nested to create an AutoTest program. We are continuously adding new blocks to AutoTest V2 to make it more powerful and easier to use.
AutoTest V2 is still available next to our original AutoTest, to start setting up an AutoTest V2 select AutoTest V2 when creating a new AutoTest.

Already have an original AutoTest? You can switch to AutoTest V2 by deleting it and pressing "Select another version of AutoTest" to finally select AutoTest V2. Please note that your original AutoTest will be deleted when pressing the "Delete" button!
Developing, snapshots and publishing to students
When developing your AutoTest V2 configuration, you can continuously test your configuration on the "Test Submission".
After configuring something, you press the “Build Snapshot” button in the bottom right corner of the screen. This will Build your AutoTest into a Snapshot (a history of your snapshots are available via the Snapshots tab).
After pressing "Build Snapshot", you can:
Test the configuration by seeing the results in seconds.
If you are ready to publish your AutoTest to your students press the big green Publish button.
If you make any changes, you build again and if you are satisfied, you can republish them to your students.
If you want to unpublish your snapshot, you can simply go to it in the green bar and press the red “Unpublish” button.
Step 1: Setup
CodeGrade AutoTest V2 runs on Ubuntu (20.04 LTS) machines which you can configure in any way that you want. Common software is pre-installed, and you can very easily install any version of MySQL yourself.
In the setup section of your AutoTest, you can install software or packages and upload any files you might need for testing. The setup section will build an image and is only run once for all submissions. 
You can upload files using the "Upload Files" block, this is where you can upload your SQL database file to initialize the database later. These files will be placed in the $UPLOADED_FILES directory on the Virtual Server.
You can install software and packages (or configure the build image in any other way) using the "Script" block. You have network access by default in the Setup tab, so you may also download libraries you want to use.
Installing MySQL and configuring the database
1.In the setup step, create a new "Script" block. In this script block, use the following commands to install MySQL:
sudo apt update
sudo apt install -y mysql-server mysql-client
2.You can now initialize your database, this also means that it is set up for all students. Upload your SQL file using a "Upload Files" block and use the following command to run it:
sudo mysql < fixtures/<DATABASE>.sql
3.Finally, create a user called codegrade that has all privileges on the database you just set up. Feel free to change the permissions granted depending on the needs in your autograder.
cat <<EOF | sudo mysql
    CREATE USER 'codegrade'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON <DATABASE>.* TO 'codegrade'@'localhost';
EOF
Script to run queries
We can test our students' sql queries using IO (Input and Output) tests. Using another simple shell script, run-mysql.sh, we can initialize MySQL and run it on our data model. Upload the script blow using a "Upload Files" block:
#!/bin/sh
​
# Wait for the mysql service to start up
while ! mysqladmin ping --password=password >/dev/null 2>&1; do
    sleep 1
done
​
# File containing SQL commands/queries
sql=$1
shift 1
​
# We cd into test_db here because the database example we use
# refers to other files in its directory with relative paths.
# Then we run mysql with the password we have set up in setup.sh
# with the sql file redirected to its stdin.
MYSQL_PWD='password' mysql --database=<DATABASE> -t "[email protected]" <"$sql"
In this script, we first wait for the MySQL service to start up. After it has started up, MySQL is run with our data model and the password we have set up.
Make sure to update line 16 and update the password if you changed that from the default password in the script above.
Basic setup of a MySQL assignment and the Chinook database.
Step 2: Create your tests
Now that you have created the setup, it's time to create the actual tests. This is done on the Tests tab. Select one of the many test-blocks to configure a AutoTest V2 procedure in a "scratch"-like format. Some tests can be nested and you can chose to connect tests to rubric categories with a certain weight, you can also hide aspects of tests and enable internet access for specific tests.
Set up an IO Test
To run queries and check if they are correct, you can use the "IO test" block in CodeGrade.
1.Run the uploaded shell script to easily run a query, use the following command in your "IO Test" block to do so: bash $UPLOADED_FILES/run-mysql.sh <STUDENT_QUERY.SQL>. Use the -N flag (see screenshot below) to remove table names from the output.
2.Provide the expected output, in a "Full Match" or "Substring Match" block inside the "IO Test" block, you can set the expected output of the query. Want to separately test multiple parts of the output? Add multiple "Substring Match" blocks to achieve this!
3.Optionally add a "Connect to Rubric" block to connect your test to a rubric and use to grade the student.
An example IO Test for an SQL query in CodeGrade AutoTest.
Note: as you can see in our expected output, we start with the name of the column (“Name”). You can disable the output of column names by passing the -N flag!
Step 3: Start the AutoTest
After you have created a configuration, create a Snapshot to test it on a "Test Submission". In the General Settings, you should have already uploaded a Test Submission, so you will see the results of this straight away.
Once everything works as expected, press the green "Publish to students" button to publish your AutoTest V2 to students. If they use the editor they can run it instantly, and if they hand in in any other way they will get their AutoTest results instantly after any submission.
Automatic Grading Guides - Previous
SQL
Autograding MySQL in AutoTest V1
Last modified 17d ago