Documentation for the marm_sql Script

The marm_sql script allows common Marmoset tasks to be performed from the command line. Users can download all the best on-time submissions and marks for a project directly to the course account. Previously it also allowed course staff to run Marmoset tests on student submissions from the command line; however, this feature is currently broken due to changes in the setup of the course accounts and Marmoset scripts.

The original version of this script was created by Huma Zafar for CS 136. (I'm not sure if that version is publicly available anywhere.) The version discussed on this page is a modified version created by Sylvie Davies in Fall 2012 for use with CS 241, and updated by several ISAs over the years. The script might work with other courses, but this can't be guaranteed as every course has subtle differences in configuration. If you think the script will be useful for your course, read through this page and the script source code carefully to see if there is anything that might need to be changed to make it work with your course.

Installation

The script is intended to be placed in the ~/bin directory of the course account.

The script will not work unless your course account has access to the Marmoset database. To check this, first make sure the environment variable HOME is set to the home directory for the course account rather than your personal home directory. That is, echo $HOME should display something like /u/cs241 instead of /u/cs241/u/userid. The reason for this is that MySQL looks under HOME for the MySQL login information, and normally that would be stored in the main home directory instead of your personal home directory. If it's not set correctly, you can set it with export HOME=/u/cs241 (in bash) or setenv HOME /u/cs241 (in tcsh). Then try typing mysql to start MySQL, and then type show tables in marmoset;. If it shows you a list of tables in the Marmoset database, that should mean you have access. If you get an error, your account probably does not have access and you should talk to your ISC or someone from CSCF.

Note that you shouldn't need to set HOME correctly to use marm_sql; the script sets HOME itself. You just need to ensure that if HOME is set correctly, then the course account can access the Marmoset database.

If you want to be able use the submission-testing feature with members of the course staff, ensure you have created a .coursestaff.XXXX file in your home directory for the current term. XXXX should be a number representing the current term, where the first two digits represent the year and the last two digits represent the month when the term started (01 = January, 05 = May, 09 = September). For example, 1205 stands for the Spring 2012 term. The command termcode -m should give you the term number in this format if you are unsure.

For CS 241, this script has already been placed in the ~/bin directory (if it's not there, someone has moved it since this was written) and Marmoset database access should work as long as HOME is set to /u/cs241. However, you will need to set up the .coursestaff.XXXX file yourself at the start of the term (details on how to do so are in CompSci241ISADuties).

Downloading Marks

The -m option can be used to download marks for a given project or assignment. In CS 241, the script doesn't just download "raw" marks from Marmoset; it calculates the correct grade based on the course's late policy. If the late policy changes, the script will need to be updated.

For each student in the classlist, the script determines the correct mark and creates a .csv file in the current directory containing each student's user ID and mark. If the name of a single project is given (e.g. A1P1) then the marks will be written to a file called project-PROJECT_NAME-grades.csv (e.g. project-A1P1-grades.csv). If the name or number of an assignment is given (e.g. A3, 7) then the script will try to find all the projects matching the assignment name and download marks for each one. This will result in .csv files being created for each project it finds (e.g. marm_sql -m 7 will create .csv files called project-A7P1-grades.csv, project-A7P2-grades.csv, and so on).

The -m option can be combined with other options:

• The -s option can be used to specify a file containing a list of student IDs to download marks for if you don't want to download marks for the whole classlist.
• The -v option ("verbose mode") causes the script to display more information about what it is doing. When used with -m it displays a download progress counter.

Note that if the -s option is not used, marks will be downloaded for every student in the classlist; this means students who dropped the course will not be included, and instructors and ISAs will not be included. If you want to download all marks, including marks for users that are no longer (or never were) in the classlist, the only way to do this is to create a custom student IDs file containing those users and pass it in with the -s option. A possible improvement for the future would be to add a -a option that downloads marks for all submissions to a particular project, rather than only looking at the students who are in the classlist.

Examples

marm_sql -m a7p4

This downloads marks for Assignment 7 Problem 4. More specifically, the marm_sql script will do a case-insensitive search through the project list for the current course offering, looking for projects that match the string "a7p4". Supposing it found one called "A7P4", it would download the marks for each student's best on-time submission and save them to a file called project-A7P4-grades.csv. Note that the project name from Marmoset is used in the name of the .csv file, not the project name you passed as an option to -m.

marm_sql -m A2

marm_sql -m 2

These commands are equivalent, and they both download marks for all the projects on Assignment 2. The script assumes that all the names of all projects on Assignment 2 begin with "A2P" or "A2B" (case insensitive); the rest of the characters do not matter. This will find project names like "A2P1", "a2p2", "A2P3a", and "A2Bonus". It will not find names like "A20P1", "A12P7", or "A2Q3". For each PROJECT_NAME found, a file called project-PROJECT_NAME-grades.csv will be created in the current directory containing the marks for that project.

If you have a project name that's not of the form "AxP..." or "AxB..." for some number "x", simply enter the name and it should work:

marm_sql -m FinalProject

Some other name formats are allowed which behave like regular expressions, such as "A4P[1-3]" to only download projects A4P1, A4P2 and A4P3. You should wrap regular expressions like these in quotes in case there is some interference between this syntax and shell globbing patterns.

The following example shows how to specify a student ID file:

marm_sql -s section_001_students -v -m a5bonus

Assuming section_001_students is a file that contains student IDs for all the students in section 001, this will download the marks for the Assignment 5 Bonus problem for only students in section 001. Because the -v option is specified, a download progress counter is displayed while the script runs. It is recommended that you use the -v option when downloading marks manually from the command line, but not when calling marm_sql from a script.

Downloading Submissions

The -d option can be used to download the best on-time submissions for a given project. It accepts arguments in the same format as -m. The submissions are stored a folder under the current directory with the same name as the specified project. As with -m, the -s and -v options can be used with -d and they have the effect you would expect them to.

Similarly to the -m option, the default (when the -s option is not used) is to download submissions for every student in the classlist, so submissions by instructors, ISAs, or students who dropped will be missed.

Examples

marm_sql -v -d a3p5

This downloads the best on-time submissions for all students in the classlist for Assignment 3 Problem 5. As with the -m option, the marm_sql script will do a case-insensitive search for projects matching this name. Supposing it finds one called "A3P5", it will create a new folder called "A3P5" in the current directory and store the student submissions in that folder. Because the -v option is included, a download progress counter will be displayed.

marm_sql -s section_001_students -d a4p4

This downloads the best on-time submissions for only the students listed in the section_001_students file (presumably students from section 001).

Testing Submissions

The -t option allows you to download a student's submission, together with the test scripts for the corresponding problem, so that you can manually run the Marmoset tests against the student's submission. Formerly the -t option would run the tests automatically and show you the results, but this feature no longer works. Fixing it would be a good project for a future ISA! An old version of MarmSql with the automatic testing is stored under ~/bin/marm_sql_broken_testing if you want to play around with it.

This feature assumes your course uses "dynamic" Marmoset tests rather than "static". It may not work correctly with static test setups.

Example

Suppose a student sends you the following email:

From: johnny_rules222@coolemail.com
To: cs241@student.cs.uwaterloo.ca
Subject: A1P2 help

Hello, my code isn't working and I don't know why.

Johnny

You can download their submission to A1P2, together with the A1P2 test scripts, using the following command:

marm_sql -t Johnny,a1p2

If a student sends you an email like this from their personal email account, you might not remember or know their Quest user ID. However, if you just type their first or last name, the marm_sql script will attempt to help you identify them by looking through the classlist for matching names. A message like this will be displayed:

'Johnny' not found in student user ID file.
Did you mean one of the following students?
j222rules Rules, Johnny
jsmith Smith, Johnny
m3johnny Johnny, Maria 
Enter the corrected user ID (first 8 characters):

You can then enter "j222rule" to proceed. Note that the user IDs from the classlist are not truncated to 8 characters, but the ones on Marmoset are, so only enter the first 8 characters of the displayed user ID if it is longer. If "j222rule" turned out to be the only Johnny in the classlist, marm_sql would automatically select this user ID and continue rather than prompting you.

If you happened to remember this student's user ID, you could have skipped this step by typing:

marm_sql -t j222rule,a1p2

Either way, marm_sql will download and test the most recent submission for A1P2 by "j222rule". Internally, this consists of a number of steps. It is not essential you understand the purpose of all these steps to use the script, but they are described here nonetheless.

• Figure out which submission from j222rule is the most recent one.
• Create a directory called localtest/j222rule.a1p2 under the current directory and a hidden symbolic link called .lt pointing to this directory.
• Download the submission as a .zip file and extract it to localtest/j222rule.a1p2.
• Download the test setup for A1P2 as a .zip file and extract it to localtest/j22rule.a1p2.

To understand the next steps, a bit of background on how "dynamic" Marmoset test setups work is required. Essentially, these setups consist solely of a file called test.properties that points to another directory, usually one that is stored somewhere on the course account or the associated "t" account (e.g. cs241t). This other directory contains the actual test setup (i.e. all the scripts that Marmoset runs to determine if a submission is correct). When Marmoset tries to run a "dynamic" test setup, it triggers a command that copies the actual test setup into the current directory and then executes the actual test scripts. This means changes to the actual test setup are immediately reflected on Marmoset without having to reupload the new tests.

The local testing script attempts to duplicate this behaviour, which leads us to the remaining steps:

• Look at the "dynamic" test.properties file to figure out the directory where the actual test setup is stored.
• Copy everything from this directory to localtest/j222rule.a1p2 using scp.

The user of marm_sql will see a message like this:

Downloading submission 61 for a1p2 by user j222rule.
Submission and test scripts will be stored in localtest/j222rule.a1p2.
Type "cd .lt" for quick access to the data.

To test the submission, do cd localtest/j222rule.a1p2 to change into the local testing directory. Alternatively, as a shortcut, you can just type cd .lt and it will take you to the most recently downloaded submission from marm_sql -t (as mentioned, .lt is a symbolic link that gets updated each time you test a submission with the script). It is recommended you always use marm_sql -t from your personal home directory (e.g. /u/cs241/u/userid) so you do not leave "localtest" directories and hidden ".lt" symbolic links all over the course account.

Once in the submission folder, you can compile the submission if necessary, and run it against the test inputs. Trying to use the Marmoset makefile for compilation or Marmoset scripts for testing will usually result in errors, since the cs241 account has a different setup from the cs241t account. (Running this script on the cs241t account won't work either, because cs241t doesn't have access to the Marmoset database.) Instead, just do things manually. For example, if the submission is a C++ program you can just compile it like this:

g++ -g -o filename *.cc

Then run it with the test input file directly (usually it is called something like "testname.in", although this varies for each problem):

./filename < testname.in

The expected output is usually stored in a file like "testname.stdout" (or "testname.stderr" for error output) if you want to compare with the expected results using diff.

Other Test Examples

marm_sql -t m3johnny,a9p3,5

You can optionally include a "submission number" after the project name if you want to test a submission other than the most recent one. The command above would download and test submission #5 by user m3johnny for A9P3.

marm_sql -v -t jsmith,a4bonus

The -v option works with the -t option as well, though it is not very useful. It displays a bit more information about what the script is doing. Also, in the step where the test scripts are copied to the local test folder via scp, the output of scp (showing which files are being copied over) is normally suppressed; if the -v option is used then the scp output will be displayed. The -v option may help with debugging if marm_sql -t is not working for some reason.

Other Features

The -q option allows you to run an arbitrary SQL query on the Marmoset database. Simply specify your query as a string argument to the -q option.

The -c option returns the current "course PK" for the course, which is a number that Marmoset uses to uniquely identify a course in its database. This can be useful when combined with -q, since you almost always need to include the course PK in your queries unless you want to run your query for every course.

For example:

marm_sql -q "select project_pk from projects where title='A1P1', course_pk=$(marm_sql -c);"

This command would return the "project PK" (a unique identifier for projects) for A1P1.

If marm_sql is run without options or with invalid options, a short usage message will be displayed. If marm_sql is run with the -h ("help") option, a much longer and more detailed usage message will be displayed.

Download the Script

The marm_sql script is attached below.