CS145 - Fall 2013
Introduction to Databases
Assignment #6
Due Wednesday November 20

This assignment consists of two automated quizzes and the third (and final) part of your programming project. The final project is time-consuming at a minimum, and all-consuming if you choose to enter the optional contest. Some very important notes:

Automated Quizzes for Basic Material

Log into the CS145 Class2Go Site. Click on the Quizzes tab. Complete two quizzes:
As always, there are several versions of each quiz, and you are permitted to take each version up to three times. All of the versions contain the same set of questions, however the correct and incorrect multiple-choice options are different in each version. For each quiz, we will use the highest score achieved on any version of the quiz as of the due date. And remember: Automated quizzes receive no credit if submitted late.

Project Part 3: AuctionBase Web Site


As a baseline, you will design a set of queries and updates for your AuctionBase system and create a simple web interface for them using either PHP or the web.py framework for Python. Ambitious students may turn the simple front-end into a user-friendly web interface that looks like a real auction site, and may exploit other features of SQLite, PHP, or web.py for additional functionality. Use your creativity. A few exceptional projects will be selected to be demonstrated on the last day in class (for glory, not grades).

Required functionality

The functionality of your final AuctionBase system is quite flexible and open-ended, however you must implement at least the following basic capabilities in order to receive full credit on the project.

Your AuctionBase system should support realistic bidding behavior. For example, it should not accept bids that are less than or equal to the current highest bid, bids on closed auctions, or bids from users that don't exist. Also, as specified above with "Automatic auction closing," a bid at the buy price should close the auction. Some of these restrictions may already be checked by your constraints and triggers from Part 2 of the project; others may require additional triggers or code.

Full credit also requires general error- and constraint-checking as specified under Transactions, errors, and constraint-checking below. For starters, all of the constraints you implemented in Part 2 should be checked in your "live" AuctionBase system.

Extra functionality

Here are some examples of additional capabilities beyond our basic expectations that you might consider adding to your system if you're feeling ambitious. Use your imagination to come up with even more.

Note that you can receive full credit on the project by implementing just the basic capabilities specified earlier, along with constraint-checking, error-checking, and a simple web interface. That is the standard against which projects will be graded. Many of you will realize that it is not difficult to add functionality, or to enhance the user interface significantly. CS145 is not a user interface class and, again, you can receive full credit for a solid system with simple input boxes, menus, and simple HTML output tables. (However, under no circumstances should you be expecting the end-user to write SQL.) Of course we welcome much snazzier interfaces, with the zenith being a near-replica of eBay itself.

You will not receive additional points for extra functionality or a fancy interface, although there's the possibility of winning the AuctionBase contest (described below), and simply the personal satisfaction of building a full-featured and attractive database-backed website.

Transactions, errors, and constraint-checking

Commands that modify the database need to be handled carefully, and you should group them into transactions whenever it makes sense for them to be executed as a unit. Using transactional behavior, each unit should either complete in its entirety or, due to failed constraints or other errors, should not modify the database at all. Constraint violations, and other errors due to bad input values or data entry, should be managed gracefully: It must be possible for users to continue interacting with the system after a constraint violation or error is detected, and the database should not be corrupt. You should inform users when errors occur, but your error message need not indicate the exact violation that caused the error.

If it helps, you may assume that AuctionBase has only one user operating on it at a time. Although transactions may be useful for database modifications and constraint-checking, you do not need to worry about transactions as a concurrency-control mechanism. That said, even without special effort your system may turn out to be fairly robust for multiple users.

Miscellaneous details

Using other languages, tools, and browsers

You are not required to implement your AuctionBase web site using PHP or web.py. Any web programming environment with a means of connecting to SQLite3 databases is fine with us. However, only PHP and web.py have guaranteed support from the course staff in terms of system problems, knowledge, and general help. If you choose to use alternate languages or tools, you may be on your own, and you are still required to meet the project specifications.

Before turning in your final project please ensure that it operates correctly using the Firefox browser on the Corn cluster. If there is a compelling reason you cannot make your system work in the Corn browser environment (e.g., you really want to exploit certain features in a different browser), you must get preapproval from the course staff to use a different browser environment. Send an email message to cs145@cs.stanford.edu telling us precisely what browser environment you wish to use for your project. The message must be sent by Friday November 15 so that we have time to work things out if your browser environment poses a problem for us. You will receive a reply within 24 hours of your message, and you must receive a positive confirmation message before assuming that your alternate environment is okay.

When the preapproval process is not followed, projects that have problems on the Corn Firefox browser may lose points, possibly all points if we cannot run your project at all.

Most importantly: It is imperative that we can run your project with a minimum of effort on the Corn cluster. Due to the size of the class we will not be able to set up separate environments for individual projects, or conduct private demos.

Choosing between PHP and web.py

This year we're providing sample code and TA support for two different web frameworks: PHP and web.py. We have no preference between them, so the decision is entirely up to you. (If you decide to use something else altogether, then you're on your own, and fully responsible to ensure you can meet the Project Part 3 specification and we can run your system on the Corn cluster; see Using other languages and tools above.) As you make the decision between PHP and web.py, here are a few things to consider:

Having trouble?

If you run into difficulties, before contacting the course staff please consult the Project Part 3 FAQ Page. We will keep it up-to-date with answers to common questions.

Project Details: web.py Option

(If you're using PHP, click here)

Your first task is to become familiar with the web.py framework and the Jinja2 templating engine. You'll be using web.py for request handling and database querying, while Jinja2 will be used for generating HTML responses. Before jumping in, we recommend at least skimming through the Web.py tutorial, the Web.py API docs, and the Jinja2 Template Designer Documentation, to familiarize yourself with the tools you'll be using, particularly if you've never used web.py or Jinja2 before. These references, and others you may find useful, are linked to the class Support Materials page.

Once you're ready, here are the steps to follow to get started on your project:

Performing transactions in web.py

Here is an example of how you might structure your code in auctionbase.py to use database transactions:

    t = sqlitedb.transaction()
        sqlitedb.query('[FIRST QUERY STATEMENT]')
        sqlitedb.query('[SECOND QUERY STATEMENT]')
The first line is responsible for initiating the transaction. Then, we begin our queries on the database, but we do so within a try/except block. That way, if our query violates a constraint in the database, we can catch the resulting error thrown by web.py and handle it appropriately. Here, we call t.rollback() to abort the transaction, and then call raise to throw the original exception once again. (Note: you’ll want to avoid calling raise in your own implementation, since you’re required to handle all interactions with the database gracefully.) If no errors occur, we enter into our else branch and commit our transaction.

Debugging web.py

If you start to come across errors in your application -- and you undoubtedly will -- it may be helpful to run your application locally rather than through Stanford's Personal CGI service. You can run the following from your shell:

    python auctionbase.py
This command will start the web application, and will loop indefinitely printing out a thorough debug log of the activities your application is performing, including all SQL queries it executes. Any additional print statements you add to your code will also appear here, enabling you to diagnose problems. There is, however, an important caveat: your application will only be accessible from the local machine. For example, if you have logged in via ssh to the corn24 machine, then you will only be able to access the web application from a browser that also runs on corn24 as well. You will not be able to access the web application from your own browser. So, to access the application, you must open Firefox from the shell, by executing:
    firefox &
(Note: X-forwarding must be enabled for this to work. Since, you’re running python auctionbase.py in your shell already, you may need to open a new window and ssh into the same Corn machine.) Then, once a browser window has appeared, you can access the application by typing localhost:8080/currtime (or any other URL that’s part of your application, e.g. localhost:8080/selecttime) into the browser’s URL address bar. Notice that auctionbase.py is no longer part of the URL as it was with CGI -- when testing the server on a local machine, auctionbase.py isn't needed in the URL.

Now, whenever you visit a page via localhost, you’ll get a steady stream of debugging information in your shell. Once you’re done debugging, you can quit the local web application by typing Ctrl-C.

One final note: You can use the above procedure to create a working environment on your own local machine. In fact, as long as your computer runs the same version of Python as the Corn cluster machines (version 2.7.4), we encourage you to develop locally, as it will be easier and faster for you to debug your web application and correct any errors. However, you are still required to ensure that your web application works on the Corn cluster where we will be grading it. Be sure to test your web application on the Corn cluster before submission!

Project Details: PHP Option

(If you're using web.py, click here)

Your first task is to become familiar with PHP by setting up your web directory and experimenting with the provided sample PHP pages. You will be connecting to your existing SQLite database via the PHP PDO (PHP Data Objects) interface. Even if you haven't used PHP before (and we're assuming you haven't), it may be possible to complete the basic project with minimal help from additional PHP materials. However, if you like to read tutorial materials before jumping in, we recommend this PHP Tutorial and Reference, this PHP PDO Tutorial, and the PHP PDO Documentation. These references, and others you may find useful, are linked to the class Support Materials page.

Performing transactions in PHP

Here is an example of how you might structure your PHP code to perform a transaction on your database:


    // Step 1: Establish a connection to your "mydatabase.db" SQLite database
    try {
      // Get a PDO handle to your database
      $dbname = "mydatabase.db";
      $db = new PDO("sqlite:" . $dbname);

      // Set error handling so that errors throw an exception

// Enable foreign key constraints on your database
$db->exec("PRAGMA foreign_keys = ON;"); } catch (PDOException $e) { echo "SQLite connection failed: " . $e->getMessage(); exit(); } // Step 2: Perform action try { // Start a transaction $db->beginTransaction(); // Run command 1 (using query) $com1 = "select/insert/delete/update ... "; $result1 = $db->query($com1); if ( ... ) { // Let's say you now want to roll back the transaction throw new Exception("description of what was wrong"); } // Run command 2 (using prepare) $com2 = "select/insert/delete/update ... where id = :id ... "; $result2 = $db->prepare($com2); $result2->execute(array(":id" => $id)); // Step 3: Commit the transaction $db->commit(); echo "Success!"; } catch (Exception $e) { try { $db->rollBack(); } catch (PDOException $pe) {} echo "Transaction failed: " . $e->getMessage(); } // Step 4: Close the connection $db = null; ?>

The code above works as follows:

One final note: It's no problem if you choose to develop in an environment other than the Corn cluster. However, you are required to ensure that your web application works on the Corn cluster where we will be grading it. Be sure to test your web application on the Corn cluster before submission!

The (Optional) CS145 AuctionBase Contest

We will select a small number of AuctionBase systems as winners of our annual CS145 AuctionBase Contest. Winners of the contest will:

The criteria for selection will be some combination of beyond-the-basics functionality and a good user interface.

Important: If you want your project to be considered for the contest, you must:

  1. Submit it by the on-time deadline of 11:59:00 PM on Wednesday November 20
  2. Clearly indicate at the top of your README file that you are entering the contest, as described in the Submission Instructions.

Submission Instructions

Prepare a submission directory containing a text file called README.txt, along with all of the files necessary to make your website run correctly when copied into the grader's cgi-bin directory. This includes your SQLite database file.

Your README.txt file should include at least the following, in this order:

  1. The line "I WOULD LIKE TO ENTER THE AUCTIONBASE CONTEST" if you want your project to be considered for the contest described below. Otherwise leave blank.

  2. A description of how the user gets to each of the basic capabilities required by the assignment.

  3. A short description of the input parameters a user can provide when browsing auctions.

  4. A list of any capabilities in your system beyond the basic requirements, how the user gets to them, and a short description of any relevant input parameters.

If you did not use PHP or web.py: It's imperative that we can run your project with a minimum of effort on the Corn cluster. You must submit detailed instructions and all of your source code.

As usual, from your submission directory execute the script:

You may resubmit as many times as you like, however only the latest submission and timestamp are saved, and those are what we will use for grading your work and determining late penalties. Submissions via email will not be accepted.

Remember that points will be deducted if you do not follow the submission procedures exactly as specified.

Reminders: Late Policy and Honor Code