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. It is also possible to download any submission from any individual student for a particular project and run the Marmoset test scripts on their code. Previously all these tasks were rather tedious and required use of the web interface. This script is used by the PublicMarksUploader in CS 241 to automatically update the public marks page on the CS 241 website.

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. 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.


The script is intended to be placed in the ~/bin directory of the course account. This allows it to be run from any directory by anyone who is using 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 on-time marks for a given project or assignment. For each student in the classlist, the script finds the on-time submission with the highest 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). Courses will often have a script that can combine these .csv files for individual projects into a single .csv file for the whole assignment (for CS 241, there is a script called which does this).

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.


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.

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. Unlike -m, it cannot be used to download submissions for an entire assignment. 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.


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 test a particular submission by a particular student for a particular problem. Currently it only works with courses that use "dynamic" Marmoset rather than "static" Marmoset. The usage of this feature is best illustrated by a detailed example.


Suppose a student sends you the following email:

Subject: A1P2 help

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


You can test their most recent submission to A1P2 with 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 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" file to figure out the directory where the actual test setup is stored
  • Copy everything from this directory to localtest/j222rule.a1p2 using scp
  • Look at the actual file (which replaces the "dynamic" one when the actual test setup is copied over) to extract the list of test scripts to run
  • Run each test script and display the results
The user of marm_sql will just see a list of test results like the following gradually appear on the screen:

Testing submission 61 for a1p2 by user j222rule.
Results will be stored in localtest/j222rule.a1p2.
Type "cd .lt" for quick access to the results.
Running test TrivialTest... failed
Running test ReallyEasyTest... error
Running test BigTest... timeout
Running test HardTest... passed

If you cancel the command with Ctrl+C in the middle of running a test, the test scripts and the student's code will still be downloaded and available in localtest/j222rule.a1p2 for your perusal. However, if you cancel before the first "Running test TestName..." message appears on screen, the test scripts and student's code may not be finished downloading yet.

To view more detailed results or examine the student's submission, type 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 recent set of test results from marm_sql (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.

The marm_sql script attempts to save copies of files that correspond to student output so that you can examine them after the test is over. For example, suppose the four test scripts "TrivialTest", "ReallyEasyTest", "BigTest" and "HardTest" all save the student's standard output in a file called "stdout" and standard error in a file called "stderr". When the "TrivialTest" script runs, marm_sql will try to detect that TrivialTest modified or created two files called "stdout" and "stderr". After the test finishes, and before executing the next test script (which may overwrite "stdout" and "stderr") marm_sql will save copies of these files called "TrivialTest.x.stdout" and "TrivialTest.x.stderr". The same process repeats for the other test scripts. When all the tests are finished, the folder will contain the following "test result" files:

TrivialTest.x.stdout, TrivialTest.x.stderr, ReallyEasyTest.x.stdout, ReallyEasyTest.x.stderr, BigTest.x.stdout, BigTest.x.stderr, HardTest.x.stdout, HardTest.x.stderr

You can then compare these output files with the expected outputs (e.g. by doing diff TrivialTest.x.stdout TrivialTest.expected). Depending on the design of your test scripts, this method of saving the test results may not produce ideal results in all cases, but for simple tests it often works nicely. You may have to manually run the student's code again with some of the test inputs to figure out the issue. Still, using marm_sql will simplify the process of downloading the student's code, downloading all the test scripts, and putting them together in the same folder so you can run manual tests.

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.

The original marm_sql by Huma had a -l option that worked the same as -m, but accounted for late submissions as well as on-time submissions. If a student's late mark (after multiplying by 0.5 for the late penalty) was higher than the on-time mark, this option would return their late mark instead. Since CS 241 does not accept late submissions, this option was not tested and the code for it was not modified. This option is disabled in the version of marm_sql discussed on this page (the code is commented out); if you think it will be useful for your course, you can try re-enabling it and gettting it to work.

Download the Script

The marm_sql script is attached below.

Also attached is, a script for combining the .csv files containing marks for individual projects into a single .csv file containing marks for an entire assignment. (It is a Perl script; I don't know why the TWiki added a .txt extension.) To use this script, type ./ N > outN.csv, where "N" is an assignment number (e.g. ./ 7 > out7.csv for Assignment 7). The script will look in the current directory for files called "project-ANP*-grades.csv" (where * stands for any string of characters) or "project-ANBonus-grades.csv" (this is the same naming convention that marm_sql uses for the .csv files it outputs). It will then create a new .csv file containing the sum of the marks for each project for each student. This script will only be suitable for courses that use the same naming scheme for assignments as CS 241; if your course uses a different scheme then you will have to write your own script (or modify this script).

Topic attachments
I Attachment History Action Size Date Who Comment
Texttxt r1 manage 0.5 K 2019-08-28 - 11:44 SylvieDavies script
Texttxt marm_sql.txt r1 manage 31.7 K 2019-08-28 - 11:45 SylvieDavies  
Edit | Attach | Watch | Print version | History: r7 < r6 < r5 < r4 < r3 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r7 - 2019-08-28 - SylvieDavies
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback