Princeton University
COS 333: Advanced Programming Techniques

Assignment 1: A Registrar Application: Command-Line Version

Purpose

The purpose of this assignment is to help you learn or review the Python programming language and database programming in Python. If done properly, the assignment also will give you practice at composing modular code.

Rules

Make sure you study the COS 333 Policies web page before doing this assignment or any of the course's assignments.

You may work with one teammate on this assignment, and we prefer that you do so.

It must be the case that either you submit all of your team's files or your teammate submits all of your team's files. (It must not be the case that you submit some of your team's files and your teammate submits some of your team's files.) Your readme file and your source code files must contain your name and your teammate's name.

Your Task

Any university registrar's office must maintain data about courses and classes offered during the upcoming semester. Those data typically are kept in a database. The registrar's office must provide an interface that allows students and other interested parties to query the database.

Assume that you are working for Princeton's Registrar's Office. You are given a database containing data about courses and classes offered during an upcoming Princeton semester. (Actually the data are for the Spring 2010 semester.) Your task is to compose Python programs that allow Princeton students and other interested parties to query the database.

For this assignment your programs must have simple textual interfaces. The next assignment will ask you to enhance your programs in a few ways, perhaps most notably such that they have graphical user interfaces. So it will be to your advantage to modularize your code so you easily can replace the textual interfaces with graphical ones.

The Database

The database is a SQLite database that is stored in a file named reg.sqlite. That file is provided in the courselab /u/cos333/Asgt1Solution directory. The database consists of these five tables:

classes
Fields: classid, courseid, days, starttime, endtime, bldg, roomnum
courses
Fields: courseid, area, title, descrip, prereqs
crosslistings
Fields: courseid, dept, coursenum
coursesprofs
Fields: courseid, profid
profs
Fields: profid, profname

These are the relationships among the tables:

courses and classes:

For each row of the courses table with courseid x, there are 0 or more rows of the classes table with courseid x. (Some courses don't have a corresponding class; that is, a particular course might not be offered this semester. Some courses have 1 corresponding class. Some courses have more than 1 corresponding class.)

For each row of the classes table with courseid x, there is exactly 1 row of the courses table with courseid x. (Each class has exactly one corresponding course.)

courses and crosslistings:
For each row of the courses table with courseid x, there are 1 or more rows of the crosslistings table with courseid x. (Some courses have 1 department and course number. Some courses have more than 1 department and course number.)

For each row of the crosslistings table with courseid x, there is exactly one row of the courses table with courseid x.
courses and coursesprofs:
For each row of the courses table with courseid x, there are 0 or more rows of the coursesprofs table with courseid x. (Some courses don't have an assigned professor. Some courses have 1 professor. Some courses have more than one professor. Incidentally, I find it strange that Princeton maps professors to courses instead of mapping professors to classes.)

For each row of the coursesprofs table with courseid x, there is exactly one row of the courses table with courseid x.
coursesprofs and profs:
For each row of the coursesprofs table with profid x, there is exactly 1 row of the profs table with profid x. (Each professor has exactly one name!)

For each row of the profs table with profid x, there are 0 or more rows in the coursesprofs table with profid x. (Some professors are teaching 0 courses. Some are teaching 1 course. Some are teaching more than one course.)

Your first step must be to familiarize yourself with the database.

The `reg.py` Program

You must compose two programs. The first program must be named reg.py.

A reference program named ref_reg.pyc is available on courselab in the directory /u/cos333/Asgt1Solution. Your reg.py must have the same behavior as ref_reg.pyc. That is, when given the same command-line arguments and database, your reg.py and ref_reg.pyc must write exactly the same (character-for-character) output to stdout and stderr, and must have the same exit status. Run ref_reg.pyc to answer any questions that you might have about the desired behavior of your reg.py.

Please don't download ref_reg.pyc. Instead, please run it on courselab. To do that, issue these commands:

$ cd /u/cos333/Asgt1Solution
$ python ref_reg.pyc arguments

Note: An alternative approach is to run ref_reg.pyc using these commands:

$ cd yourProjectDirectory
$ python /u/cos333/Asgt1Solution/ref_reg.pyc arguments

Why use that alternative approach? The ref_reg.pyc program uses the reg.sqlite file that is in the working directory. Using the first approach, ref_reg.pyc uses the reg.sqlite file that is in the /u/cos333/Asgt1Solution directory. You don't control that file. Using the alternative approach, ref_reg.pyc uses the reg.sqlite file that is in your project directory. You control that file; you can delete it or corrupt it at will. Thereby you can observe the behavior of ref_reg.pyc when reg.sqlite is missing or has been corrupted.

You must run ref_reg.pyc to determine the desired behavior of your reg.py. Here are some highlights of that behavior...

When executed via a command that contains -h as a command-line argument, your reg.py must display a help message:

$ python reg.py -h
usage: reg.py [-h] [-d dept] [-n num] [-a area] [-t title]

Registrar application: show overviews of classes

options:
  -h, --help  show this help message and exit
  -d dept     show only those classes whose department contains dept
  -n num      show only those classes whose course number contains num
  -a area     show only those classes whose distrib area contains area
  -t title    show only those classes whose course title contains title

Hint: Use the standard Python argparse module.

These example commands describe how your reg.py must behave when given proper command-line arguments. Give them a try using ref_reg.pyc:

$ python reg.py
Display data for all classes.
$ python reg.py -d COS
Display data for all classes whose department, when converted to lowercase letters, contains "cos".
$ python reg.py -n 333
Display data for all classes whose course number, when converted to lowercase letters, contains "333".
$ python reg.py -n b
Display data for all classes whose course number, when converted to lowercase letters, contains "b".
$ python reg.py -a Qr
Display data for all classes whose distribution area, when converted to lowercase letters, contains "qr".
$ python reg.py -t intro
Display data for all classes whose title, when converted to lowercase letters, contains "intro".
$ python reg.py -t science
Display data for all classes whose title, when converted to lowercase letters, contains "science".
$ python reg.py -t C_S
Display data for all classes whose title, when converted to lowercase letters, contains "c_s". The point is that your program, unlike SQL, must not interpret the underscore character as a wildcard character.
$ python reg.py -t c%S
Display data for all classes whose title, when converted to lowercase letters, contains "c%s". The point is that your program, unlike SQL, must not interpret the percent character as a wildcard character.
$ python reg.py -d cos -n 3
Display data for all classes whose department when converted to lowercase letters contains "cos" and whose course number when converted to lowercase letters contains "3".
$ python reg.py -d cos -a qr -n 2 -t intro
Display data for all classes whose department when converted to lowercase letters contains "cos" and whose area when converted to lowercase letters contains "qr" and whose course number when converted to lowercase letters contains "2" and whose title when converted to lowercase letters contains "intro".
$ python reg.py -t "Independent Study"
Display data for all classes whose title, when converted to lowercase letters, contains "independent study".
$ python reg.py -t "Independent Study "
Display data for all classes whose title, when converted to lowercase letters, contains "independent study ".
$ python reg.py -t "Independent Study "
Display data for all classes whose title, when converted to lowercase letters, contains "independent study ".
$ python reg.py -t " Independent Study"
Display data for all classes whose title, when converted to lowercase letters, contains " independent study".
$ python reg.py -t " Independent Study"
Display data for all classes whose title, when converted to lowercase letters, contains " independent study".
$ python reg.py -t=-c
Display data for all classes whose title, when converted to lowercase letters, contains "-c".

In short, your reg.py must display the classid, dept, coursenum, area, and title of each class that matches the criteria specified by the user via command-line arguments.

Your reg.py must use SQL prepared statements to protect the database against SQL injection attacks.

Your reg.py must write its output in a human-readable format. These are some salient characteristics of the output:

The output must have have the appearance of a table.
Each column must have a title.
Each title must be underlined with hyphens.
The rows must sorted. The primary sort must be by dept in ascending order, the secondary sort must be by coursenum in ascending order, and tertiary sort must be by classid in ascending order.
Within each row, each line must consist of no more than 72 characters, not including the newline character.
Within each row, each line must end after a word, not within a word. That is, no newline characters may appear within words.

Normally your reg.py must terminate with exit status 0. If it detects a database-related error, then it must terminate with exit status 1. If it detects erroneous command-line arguments, then it must terminate with exit status 2 — as is the default behavior of argparse.

Hint: Use the Python percent (%) operator and C-style conversion specifications to format each row of output. An expression of this form works:

row = '%5s %4s %6s %4s %s' % (classid, dept, coursenum, area, title)

(There are other reasonable approaches; but given your knowledge from COS 217 of the C language and its printf() conversion specifications, that approach is likely to be the most straightforward for you.)

Then, having generated each row, use the standard Python textwrap module to wrap it to lines. Within each row, lines after the first must be indented using 5+1+4+1+6+1+4+1=23 spaces.

Hint: Consider the fact that a SQL statement of this form:

SELECT somefields FROM sometable WHERE somefield LIKE '%'

has the same semantics as a SQL statement of this form:

SELECT somefields FROM sometable

That fact could help you to express the logic in your reg.py more succinctly.

The `regdetails.py` Program

Your second program must be named regdetails.py.

A reference program named ref_regdetails.pyc is available on courselab in the directory /u/cos333/Asgt1Solution. Your regdetails.py must have the same behavior as ref_regdetails.pyc. That is, when given the same command-line arguments and database, your regdetails.py and ref_regdetails.pyc must write exactly the same (character-for-character) output to stdout and stderr, and must have the same exit status. Run ref_regdetails.pyc to answer any questions that you might have about the desired behavior of your regdetails.py.

Please don't download ref_regdetails.pyc. Instead, please run it on courselab. To do that, issue these commands:

$ cd /u/cos333/Asgt1Solution
$ python ref_regdetails.pyc arguments

Note: An alternative approach is to run ref_regdetails.pyc using these commands:

$ cd yourProjectDirectory
$ python /u/cos333/Asgt1Solution/ref_regdetails.pyc SOMEPORT

The ref_regdetails.pyc program uses the reg.sqlite file that is in the working directory. Using the first approach, ref_regdetails.pyc uses the reg.sqlite file that is in the /u/cos333/Asgt1Solution directory. You don't control that file. Using the alternative approach, ref_regdetails.pyc uses the reg.sqlite file that is in your project directory. You control that file; you can delete it or corrupt it at will. Thereby you can observe the behavior of ref_regdetails.pyc when reg.sqlite is missing or has been corrupted.

You must run ref_regdetails.pyc to determine the desired behavior of your regdetails.py. Here are some highlights of that behavior...

When executed via a command that contains -h as a command-line argument, your regdetails.py must display a help message:

$ python regdetails.py -h
usage: regdetails.py [-h] classid

Registrar application: show details about a class

positional arguments:
  classid     the id of the class whose details should be shown

options:
  -h, --help  show this help message and exit

Hint: Use the the standard Python argparse module.

When executed via a command that contains a valid classid, your regdetails.py must write to stdout the courseid, days, starttime, endtime, bldg, roomnum, dept(s), coursenum(s), area, title, descrip, prereqs, and profname(s) for the class with the given classid. These commands illustrate. Give them a try using ref_regdetails.pyc:

$ python regdetails.py 8321
$ python regdetails.py 9032
$ python regdetails.py 8293
$ python regdetails.py 9977
$ python regdetails.py 9012
$ python regdetails.py 10188

Your regdetails.py must use SQL prepared statements to protect the database against SQL injection attacks.

Your regdetails.py must write its data in a human-readable format. These are some salient characteristics of that format:

Titles and blank lines must appear where appropriate.
The data in the "depts and coursenums" rows must be sorted primarily by dept in ascending order and secondarily by coursenum in ascending order.
The data in the "profnames" rows must be sorted by profname in ascending order.
Within each row, each line must consist of no more than 72 characters, not including the newline character.
Within each row, each line must end after a word, not within a word. That is, no newline characters may appear within words.

Normally your regdetails.py must terminate with exit status 0. If it detects a database-related error, or if no class has the specified classid, then it must terminate with exit status 1. If it detects erroneous command-line arguments, then it must terminate with exit status 2 — as is the default behavior of argparse.

Hint: Use the standard Python textwrap module to wrap each row to lines.

Hint: Consider whether it's reasonable (or even possible!) to retrieve all required data from the database by executing a single SQL SELECT statement. Consider whether it's more reasonable to retrieve the required data by executing multiple SQL SELECT statements.

Error Handling

Your reg.py and regdetails.py must be robust.

Your reg.py must handle erroneous command-line arguments gracefully. These commands illustrate. Give them a try using ref_reg.pyc:

$ python reg.py a qr
$ python reg.py -A qr
$ python reg.py "-a " qr
$ python reg.py -a qr st
$ python reg.py -a
$ python reg.py -a qr -d
$ python reg.py -a -d cos
$ python reg.py -x

Your regdetails.py must handle erroneous command-line arguments gracefully. These commands illustrate. Give them a try using ref_regdetails.pyc:

$ python regdetails.py
$ python regdetails.py 8321 9032
$ python regdetails.py abc123
$ python regdetails.py 9034

Your reg.py and regdetails.py must handle "database cannot be opened" errors. If the database cannot be opened, then your programs must write a descriptive error message — the one contained within the thrown Exception object — to their stderr.

Your reg.py and regdetails.py also must handle "corrupted database" errors. In the context of this course, a corrupted database is one that causes the database driver, upon interaction with the database, to throw an exception. In the case of SQLite, a corrupted database might consist of an invalid SQLite file — for example, a file that's empty or contains simple text. More generally, a corrupted database might be a database that is missing a table that the interaction requires, or is missing a column/field that the interaction requires. If the database is corrupted such that the SQLite driver's execution of a SELECT statement throws an exception, then your programs must write a descriptive error message — the one contained within the thrown Exception object — to their stderr.

Good Question from Student During a Recent Semester

Suppose the user runs regdetails.py for a particular classid, the program queries the classes table to find the corresponding courseid, and the program then queries the database to fetch the row in the courses table with that courseid. Furthermore, suppose the database is corrupted such that no row with that courseid exists in the courses table. Should our regdetails.py handle that particular database corruption error?

Answer

No. Your grader won't test your regdetails.py program on that error. Generalizing...

Database management systems can enforce foreign key integrity constraints on the databases that they manage. So any DBMS would not allow its databases to contain the kind of violation that the student described. And so client programs would not need to check for such violations.

That's a very good thing. With a richer database, it would be very difficult for a client program systematically to check for foreign key integrity constraint violations. And it would be absurdly redundant for every client program to check for such violations.

So it's more realistic to compose your reg.py and regdetails.py such that they don't check for foreign key integrity constraint violations. And so it's fine to compose your programs such that they check for only the kinds of database-related errors that are described in this specification: file-level errors and schema-level errors. The first easily could happen if the reg.sqlite file is missing from the working directory. The second could happen if, for example, the reg.sqlite file is present but empty.

Each error message written by your reg.py must be prefixed with sys.argv[0] (which is normally but not necessarily "reg.py") followed by a colon and a space. Similarly, each error message written by your regdetails.py must be prefixed with sys.argv[0] (which is normally but not necessarily "regdetails.py") followed by a colon and a space.

Testing

We'll cover software testing techniques in lectures later in the semester. In the meantime, to test your programs it will be sufficient to rely upon (1) your knowledge of testing from the COS 217 course, and (2) this A Software Testing Taxonomy document.

Test your reg.py and regdetails.py by (1) reviewing this assignment specification thoroughly, making sure that your programs conform to every aspect of it, and (2) comparing the behavior of your programs with the behavior of the reference programs.

Boundary Testing

Focus on boundary (alias corner case) testing. Of course, make sure that your programs handle normal data. But also make sure that your programs handle unusual data: courses that have multiple cross-referenced departments/numbers, long titles, long descriptions, multiple professors, no professors, and so forth. Also make sure that your programs handle errors: erroneous command-line arguments, database cannot be opened, corrupted database, and so forth.

Statement Testing

Also focus on statement (alias coverage) testing. Your tests should cause every statement of your reg.py and regdetails.py to be executed.

You're encouraged, but not required, to use the Python coverage tool to generate a coverage report showing which lines of your programs have and have not been executed by your tests. These are the steps:

Repeatedly issue commands of the form python -m coverage run -p reg.py arguments. Each of those commands runs your reg.py with the specified arguments, and generates a coverage report in a file named .coverageX (for some X). The report indicates which lines of your reg.py were executed and which were not.
Repeatedly issue commands of the form python -m coverage run -p regdetails.py argument. Each of those commands runs your regdetails.py with the specified argument, and generates a coverage report in a file named .coverageX (for some X). The report indicates which lines of your regdetails.py were executed and which were not.
Issue the command python -m coverage combine to combine the coverage reports generated by steps 1 and 2 into one large coverage report in a file named .coverage.
Issue the command python -m coverage html to use the .coverage file to generate a human-readable report as a set of HTML documents in a directory named htmlcov.
Browse to htmlcov/index.html to check the report.
The files in your htmlcov directory should show that 100% of your programs' lines were executed. If the report doesn't show 100% coverage, then we recommend that you revise your testing plan accordingly, delete the .coverage* files and the htmlcov directory, and repeat steps 1 through 5.

Incidentally, if you're clever you could use your testreg.py and testregdetails.py programs, as described in the next section of this document, to generate your coverage report.

Test Automation

Use the given programs /u/cos333/Asgt1Solution/testreg.py and /u/cos333/Asgt1Solution/testregdetails.py to automate your testing.

Your first step should be to study those programs. Then issue these commands on courselab to make your own copies of them:

cd yourProjectDirectory
cp /u/cos333/Asgt1Solution/testreg.py .
cp /u/cos333/Asgt1Solution/testregdetails.py .

Issue these commands on courselab to automate testing of your reg.py:

# Run testreg.py on your reg.py, and save the output in a file named out1.
python testreg.py reg.py > out1 2>&1

# Run testreg.py on the given ref_reg.pyc, and save the output in a file named out2.
python testreg.py /u/cos333/Asgt1Solution/ref_reg.pyc > out2 2>&1

# Edit out2, deleting every occurrence of "/u/cos333/Asgt1Solution/".
sed -i 's\/u/cos333/Asgt1Solution/\\g' out2

# Edit out2, changing every occurrence of "ref_reg.pyc" to "reg.py".
sed -i 's\ref_reg.pyc\reg.py\g' out2

# Compare the contents of out1 and out2.
diff out1 out2

The diff command should generate no output, thus indicating that the out1 and out2 files are identical.

Thereafter, edit your copy of testreg.py to add additional tests, and repeat that command sequence

Similarly, issue these commands on courselab to automate testing of your regdetails.py:

# Run testregdetails.py on your regdetails.py, and save the output in a file named out1.
python testregdetails.py regdetails.py > out3 2>&1

# Run testregdetails.py on the given ref_regdetails.pyc, and save the output in a file named out4.
python testregdetails.py /u/cos333/Asgt1Solution/ref_regdetails.pyc > out4 2>&1

# Edit out4, deleting every occurrence of "/u/cos333/Asgt1Solution/".
sed -i 's\/u/cos333/Asgt1Solution/\\g' out4

# Edit out4, changing every occurrence of "ref_regdetails.pyc" to "regdetails.py".
sed -i 's\ref_regdetails.pyc\regdetails.py\g' out4

# Compare the contents of out3 and out4.
diff out3 out4

The diff command should generate no output, thus indicating that the out3 and out4 files are identical.

Thereafter, edit your copy of testregdetails.py to add additional tests, and repeat that command sequence.

Hint: If you have trouble understanding the output of the diff out1 out2 command, then widen your terminal window and issue the command diff -y out1 out2. The output will be a side-by-side display of the two files, with markers denoting lines that differ. Same for diff out3 out4.

Program Style

Your programs must be well styled. Generally, by consensus of the Python community, good Python style is defined by the PEP 8 -- Style Guide for Python Code website.

The Python community has developed a static code analysis tool named pylint. The pylint tool is similar to the splint tool that you used in COS 217: just as splint (configured with a .splintrc file) generates a report critiquing the style of given C code, so pylint (configured with a .pylintrc file) generates a report critiquing the style of given Python code. The pylint tool enforces many of the PEP 8 guidelines, and some additional guidelines too. In COS 333 "good style" is defined by the pylint tool, as configured with the given .pylintrc file. The lecture example programs (with a few intentional or unavoidable exceptions) cause pylint to generate perfect reports, and your assignment programs should do the same.

Using pylint is easy. You can critique one file comprising your programs at a time:

python -m pylint reg.py
python -m pylint regdetails.py
...

However, a better approach is to critique all files comprising your programs at the same time:

python -m pylint reg.py regdetails.py ...

When given multiple files, pylint performs some cross-file critiquing. So the "all files at the same time" approach may generate warnings in addition to those generated by the "one file at a time" approach. Make sure you use the "all files at the same time" approach, as your grader will.

Advice

The command-line format of reg.py is incomplete in the logical sense. Most notably the format doesn't allow the user to express OR relationships in queries. For example, the format doesn't allow the user to express queries of the form "Display data for all classes whose area is 'qr' OR 'st'." or "Display data for all classes whose dept is 'cos' OR whose area is 'qr'." Don't be concerned about that. Remember that the purpose of the assignment is not to develop a great registrar's office application per se. Instead its purpose is to give you experience with database programming — experience that, we hope, will help you when developing your project, and beyond.
Modularize your code as much as you can. Good modularity will help you to reuse your code from this assignment in the next assignment.

Modularize your code at the file level. In particular, isolate the database-related code in a distinct file.

Also modularize your code at the function level. In your reg.py program — which should consist of your reg.py file and other .py files — define distinct functions for accepting user input, accessing the database, and writing output. The way to do that effectively is:
- Define the user input function such that it returns a data structure that describes the user's query.
- Define the database access function such that it accepts the query data structure and returns its results in another data structure.
- Define the output writing function such that it accepts the results data structure and writes data.
Design your regdetails.py with similar function-level modularity.
Make sure that the output of your reg.py has the same format as does the output of ref_reg.pyc. If it doesn't, then (no doubt) your testing programs will detect many differences between the output of the two programs, and it will be difficult for you to determine how well your reg.py is working. Moreover, if it doesn't, then your reg.py will fail many of our automated checks, it will be difficult for your grader to determine if how well your reg.py is working, and your grade will suffer. Same for your regdetails.py.

Submission

Compose a readme file. If you're working on the assignment alone, then the first line of your readme file must contain your Princeton netid, and nothing else. If you're working on the assignment with a teammate, then the first line of your readme file must contain your Princeton netid and your teammate's Princeton netid with an underscore between the two, and nothing else. That information in that format will help us to automate the process of determining who worked with whom.

Thereafter your readme file must contain:

Your name and your teammate's name.
A paragraph describing your contribution, and another paragraph describing your teammate's contribution. Please be thorough; we're looking for two substantial paragraphs, not a sentence or two.
A description of whatever help (if any) you received from other people while doing the assignment, as prescribed by the course Policies web page.
A description of the sources of information that you used while doing the assignment.
An indication of how much time you spent doing the assignment.
Your assessment of the assignment: Did it help you to learn? What did it help you to learn? Do you have any suggestions for improvement? Etc.
(Optionally) Any information that will help us to grade your work in the most favorable light. In particular you must describe all known bugs.

Your readme file must be a plain text file. Don't create your readme file using Microsoft Word or any other word processor.

Submit your assignment files on courselab using these commands:

submit 1 readme
submit 1 reg.py anyFilesUsedByThatProgram
submit 1 regdetails.py anyFilesUsedByThatProgram

You're not required to submit your testreg.py or your testregdetails.py. But if you do submit them, then your grader will comment on them briefly.

As noted above in the Rules section, it must be the case that either you submit all of your team's files or your teammate submits all of your team's files. (It must not be the case that you submit some of your team's files and your teammate submits some of your team's files.) You may submit multiple times; we'll grade the latest files that you submit.

Please follow the rules on what to submit. It will be a big help to us if you get the filenames right and submit exactly what's asked for. Thanks.

Grading

Assume that your grader already has activated the cos333 virtual environment before he/she runs your programs. The document from the first lecture entitled A COS 333 Computing Environment describes the cos333 virtual environment.

Your grade will be based upon:

Correctness, that is, the correctness of your programs as specified by this document and the given reference programs.
Style, that is, the quality of your program style as reported by the pylint tool, when using the given .pylintrc file, and when executed via the command python -m pylint *.py.

Ten points (that is, ten percent) of your grade will be based upon the quality of your program style as reported by pylint. Your grader will start with the 10-point score reported by pylint. Your grader then will "round down" that score to the 0.5 level to compute your program style grade. For example, if your pylint score is 9.8, then your program style grade will be 9.5; if your pylint score is 7.4, then your program style grade will be 7.0.

If your code fails the tests on some particular functionality, then your grader won't be able to inspect your code manually to try to assign partial credit for that functionality. So please make sure your code behaves properly.

Princeton University COS 333: Advanced Programming Techniques