University of Virginia Computer Science
CS150: Computer Science, Fall 2005

Problem Set 7:
HooRides.net
Out: 31 October
Due: Monday, 7 November

Collaboration Policy - Read Carefully

Note: If you have a project idea and team for PS8 and send this to me by Wednesday, 2 November, you may be able to arrange a different collaboration policy for PS7 than the standard policy stated below.

For this problem set, you may work alone or with a partner of your choice. If you work with a partner, you and your partner should turn in one assignment with both of your names on it and both people must participate fully in all of the work. You should read the whole problem set yourself and think about the questions before beginning to work on them with your partner.

You may discuss this assignment with other students in the class and ask and provide help in useful ways. You may consult any outside resources you wish including books, papers, web sites and people except for materials from previous CS200 courses.

If you use resources other than the class materials, indicate what you used along with your answer.

Purpose

Background

In 1990, Tim Berners-Lee, wrote a program called WorlDwidEweb for editing hypertext (text with embedded links to other documents) and developed the HyperText Transfer Protocol (HTTP) for allowing clients (browsers) and servers to talk to each other, Universal Resource Locators (URLs) for naming objects, and the HyperText Markup Language (HTML) for describing hybertext documents. With these pieces in place, anyone could set up a web server and start publishing their own hypertext documents. The web grew exponentially throughout the 1990s, with the number of web sites increasing from a few hundred in 1991 to 48 million sites in March 2004 and many fortunes were won and lost.

For this assignment you will understand and complete the implementation of a web application that provides a "ride board" for UVa students. When you are done, you will have produced a site like this: www.HooRides.net. The ride board here is basic and lacks some important features for a useful ride board. We hope one group will select improving this web application for your PS8 project. The group that does this will get the HooRides.net domain name.

To build this, we used several languages:

Getting Started

Create a directory hoorides inside your public_html directory. Download hoorides.zip and unzip it into your public_html\hoorides\ directory. After this, you should be able to see the web application by opening a web browser to http://www.people.virginia.edu/your userid/hoorides/. You should see a welcome page including links for Offer a ride and Look for a ride. (These won't work on your website yet; you will need to first set up your Google Maps key as described below.)

Question 0: (nothing to turn in for this but all team members should do it) To get a feel for the web application, open a web browser to http://www.HooRides.net. Click on Register New User to create an account for yourself. If you are driving home for Thanksgiving, try offering a ride. If you need a ride somewhere, try requesting a ride. Try clicking on the map flags and other links.

Google Maps

Our web application uses the Google Maps API to display maps. An "API" is an Application Program Interface, a set of definitions that allow programs to interact with each other. In this case, the Google Maps API is a set of procedures you can use to interact with the Google Maps application that Google provides for displaying and manipulating maps. It provides procedures for controlling the map, putting flags and lines on the map, and obtaining user clicks on the map.

To use the Google Maps API, you need to obtain a Google Maps API key. Follow the directions here: http://www.google.com/apis/maps/signup.html. Note that keys are associated with the URL of the web page where the map is hosted, so you will need to enter your URL as http://www.people.virginia.edu/~your user id/hoorides/ for the web page that will host the map. (If you expect to get more than 50,000 page views per day for your PS7, you need to contact Google to get permission first. This will probably not be a problem for most of you.)

After you sign up for a Google Maps API key, you will see a page like this:

Thank you for signing up for a Google Maps API key. Your key is:

ABQIAAAA2kcJjN_en3sWPySRiYQfaRQfZCebFK1Jdo5rele1j7cYODPsohTttyMK5pkDuJP3SW94RIpDRwPBSw
...
Copy the key into the file key.py, replacing the provided key.

Database

Because HTTP is a stateless protocol, all information that needs to persist between web requests must be stored somewhere. We use a database to store everything (except for user login information with is stored in a cookie, see below). The Schemer's Guide to Structured Query Language gives a brief introduction to the SQL language we will use to manipulate the database.

First, you need to create a database (follow the directions in the SQL Guide to create your own MySQL database). After creating your database, edit the db.py file. Change the values assigned to userName, password and dbName to match those for the database you created.

Since we want to keep some information about rides requested and offered private (such as the email address of the offerer), we need a table for managing users. Create a table in your database named users with fields for storing the name, email address, last name, first names (all names except the last name), encrypted password and cookiecounter for each user (managing cookies and passwords is tricky and error prone. For now, you should just use the code we provide for this and we will explain more about why the way it does things is semi-secure, and more obvious ways are not in a later lecture).

You can create the users table by issuing this SQL command (you can issue a SQL command using phpMyAdmin by click the SQL tab):

CREATE TABLE users (
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user VARCHAR(80) UNIQUE,
   email VARCHAR(255) UNIQUE NOT NULL,
   lastname VARCHAR(255) NOT NULL,
   firstnames VARCHAR(255),
   password CHAR(80),
   cookiecounter INT
)
Enter the above command in the text entry area under "Run SQL query/queries on database name" and then click Go. You can also create tables using the MySQL web interface. After you submit the command, you should see a page with Your SQL-query has been executed successfully, and then be able to click on users on the left side of the page to see the table you created.

Note that each field has a name and a type. The id field is a unique identifier for each user. Since it is labeled AUTO_INCREMENT, the database will give it a value automatically that is one more than the previous entry. The user and email fields have type VARCHAR(num) which means they are a string of up to num characters. They use the UNIQUE and NOT NULL modifiers to indicate that all table entries must have different user names and emails, and that every entry must have a value for these fields.

Now that you have created a table, insert an entry for yourself in the table. For example, I would do this by running the SQL query,

INSERT INTO users (user,  email, lastname, firstnames)
           VALUES ('evans', 'evans@cs.virginia.edu', 'Evans', 'David')
(Don't forget the quote (') marks.)

After running the insert command, you should be able to see one entry in your table. You can view the whole table by clocking on Browse. Note that the id field has been automatically assigned a value, but the password and cookiecounter field values are blank (NULL). The Browse link shows you the result of the query, SELECT * FROM users which means to select all fields for all entries in the users table. Note that SQL's SELECT command is different in some ways from the table-select procedure you defined in Problem Set 5. See the SQL Guide for details on SELECT.

Question 1: For each question, provide the SQL command that performs the requested action and run your command on your database. Note that the commands modify the state of the database, so you need to do them in order.
  1. Insert a user into your users table with user name alyssa, email aph@cs.virginia.edu, last name Hacker and firstnames Alyssa P..
  2. Insert a user into your users table with user name ben, email bb@cs.virginia.edu, last name Bitdiddle and firstnames Ben.
  3. Select the lastname of all users in your table. The response should be a table like this (of course, your result will be different because you put yourself in the table instead of me):
    lastname
    Evans
    Hacker
    Bitdiddle
  4. Select the lastname and firstnames of all users in your table in alphabetical order by firstnames. The response should be a table like:
    firstnameslastname
    Alyssa P.Hacker
    BenBitdlddle
    DavidEvans
  5. Select the email address of all users in your table with lastname matching Hacker. The response should be the table:
    email
    aph@cs.virginia.edu
  6. Delete all entries from your table whose id does not equal the id for your entry. (Note that the MySQL interface will give you a confirmation on DELETE commands, since a mistake could remove all the records you want from the table. It is a good idea with DELETE commands to use a LIMIT n as part of the query to make sure only the right number of entries are deleted. For example, for this question you would do DELETE FROM users ... LIMIT 2 to ensure that no more than 2 entries are deleted.

At this point, your users table should contain one entry corresponding to yourself. Use Browse to check this is the case, and issue the necessary SQL commands to repair it if it is not.

Managing Users

When you created your user entry in the table, you did not provide a value for the password field. This is because we don't want to store actual passwords in the database. This would be dangerous since anyone who breaks into the database (or just steals the disk it is stored on and starts looking at bits on the disk) would be able to learn everyone's password. Even though you would be foolish to put anything highly confidential on this site, people often use the same password for security-critical and non-security critical websites, so it is important to never store passwords in cleartext.

So, instead of storing actual passwords in the database we will store encrypted passwords. There are some tricky issues in how to do this that we will discuss in a later lecture, but the basic idea is to store Encrypt(password) in the database, and then when a user logs in check that the value calculated by encrypting the entered password matches the stored password. To activate your account, you will need to reset the password. Reload your main page (http://www.people.virginia.edu/~your username/hoorides) and click on the Reset Password link. This links to the HTML file reset-password.cgi. This file contains:

 1    #!/uva/bin/python
 2
 3    import util
 4
 5    util.printHeader ("Reset Password")
 6
 7    print """
 8    <h1>Reset Password</h1>
 9
10    Enter your email address to resent your password.  
11    A new password will be sent to your email address.
12
13    <blockquote>
14       <form method="post" action="reset-password-process.cgi">
15          Email: <input type="text" size="30" name="email"><br>
16          <input type="submit" value="Reset Password">
17       </form>
18    </blockquote>
19   """
20  
21   util.printFooter ()
This is a cgi (common gateway inferface file) that will be executed on the webserver in response to the client request for the reset-password.cgi page. Unlike normal HTML pages where the webserver just retrieves a static text file, when a cgi page is requested, the webserver will run a program to generate the output response.

The first line of this file tells the webserver which program to run. In this case (and for all the cgi files you will use for this problem set), #!/uva/bin/python tells the webserver to run the python interpreter on this file, and send the output back to the client.

Hence, the rest of this file is interpreted as python code. Line 3, import util is similar to the Scheme expression (load "util.ss"). It imports the util.py python code file into this file. The util.py file defines some procedures that are useful in producing web pages, including the printHeader procedure that takes the title of the web page as its parameter and prints out the HTML header for the output page. Line 5 applies util.printHeader to "Reset Password" to print out the HTML header for this page.

Next, line 7-19 prints out a fragment of HTML that will display the "Reset Password" title and a form for entering an email address. The action parameter of the form is the URL of the file that will receive the values entered in the form when the visitor clicks the submit button. In this case reset-password-process.cgi will receive the form input.

Enter your email address in the form and click Reset Password. (Remember to change the values in opendb.php to match your database before doing this).

The reset-password-process.cgi code receives the input. Look at the code for reset-password-process.cgi and try to understand what it is doing.

Question 2: Explain in clear English how the login processing works. You answer should explain how the login information entered on the index.cgi page is processed and how the password is checked, relating fragements of the code to what we covered in Class 29 and Class 30. There is no code to write for this question, but you should demonstrate adequate understanding of the provided code in your answer.

Managing Rides

We need a database table to keep track of the ride requests and offers. We will use the rides table for this, and create it using this SQL command. You should issue this SQL command for your database in phpMyAdmin:
CREATE TABLE rides (
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user VARCHAR(80),
   leave DATE NOT NULL,
   ret DATE,
   fromLat DECIMAL(54, 30) NOT NULL,
   fromLong DECIMAL(54, 30) NOT NULL,
   toLat DECIMAL(54, 30) NOT NULL,
   toLong DECIMAL(54, 30) NOT NULL,
   notes TEXT,
   offer BOOL
)
The rides table has fields for recording the user offering or requesting a ride, the date for leaving and returning, and the origin and destination locations as latitude and longitude. The offer field is TRUE if the entry corresponds to a ride offered and FALSE if the entry corresponds to a ride requested.

The rides.py file provides a Python interface to the rides table. It provides methods for adding new ride offers and requests, and getting the rides from the table.

The lookupRide method in rides.py takes as input the number of the ride to lookup and returns a Ride object representing that ride. When a visitor clicks on the Details link for a map flag on the Look for a Ride page, it requests the page rideinfo.cgi?ride=<ride number>. The rideinfo.cgi code uses rides.lookupRide to find the corresponding ride and print out its details.
Question 3: The provided lookupRide method is missing the SQL query needed to find the ride. Complete the definition of lookupRide by filling in the missing SQL query.

Looking for a Ride

The page look.cgi creates a map that displays the ride offers and requests.

The code for look.cgi is excerpted below:

#!/uva/bin/python

import util
import cookies
import users
import rides
import gmap

util.printHeader ("Look for Rides")

print """

<h1>Look for a Ride</h1>
<p>
<center>
<div id="map" style="width: 600px; height: 600px"></div>
<script type="text/javascript">
   //<![CDATA[

   var map = new GMap(document.getElementById("map"));
   map.addControl(new GSmallMapControl());
   map.centerAndZoom(new GPoint(-78.4995, 38.0340), 10);
   ...
   function createMarker(user, date, rideno, point) {
      var marker = new GMarker(point, ricon);
     // Show this marker's index in the info window when it is clicked
     var html = "Offered by: <b>" + user + "</b><br>Date: " + date
        + "<br><a href=\\\"rideinfo.cgi?ride=" + rideno + "\\\">Details</a>";

     GEvent.addListener(marker, "click", function() {
       marker.openInfoWindowHtml(html);
     });

    return marker;
   }

"""

r = rides.Rides ()
all = r.getAll ()

for ride in all:
   gmap.outputRideMarker (ride)

print """
   //]]>
</script>
</center>
<p>
"""

util.printFooter ()
This code is a bit complex, it contains fragments in three different languages: HTML (in blue), JavaScript (in green), and Python (in black).

The first section imports several Python modules and calls util.printHeader to print the web page header. The next blue section is HTML code for printing the title and then setting up the JavaScript to contain the map. The JavaScript uses the Google Maps API to display a map. Within the JavaScipt section we return to Python code (after the """) and generate the map overlay markers and lines to show the rides. The call to r.getAll() returns a tuple containing all the rides in the rides table, each as a Ride object (defined it ride.py). The for ride in all: is similar to map! in Scheme. It iterated through each Ride object, calling gmap.outputRideMarker (ride) (defined in gmap.py). This procedure prints out some JavaScript code that uses the Google Maps API to place overlays on the map corresponding to the ride passed in as its parameter.

You are not expected to modify the JavaScript code, but feel free to experiment with it (just keep a copy of the original code so you can repair it if anything breaks).

Browsing the Ride List

The browse.cgi file provides a way to see all the offered rides in a table (click on Browse the ride list).

You should be able to figure out its code yourself enough to answer this question.

Question 4: The provided browse.cgi only displays the rides offered. Add code to make it also print out a table containing the same information for the rides requested.

Optional Extensions

Our ride board isn't yet quite good enough to be useful. For example, there is no way to remove rides from the board. It would be good if after the leave date has passed, the ride no longer appears on the map. The Ride Details page does not yet show the ride on the map. There is no way to search for rides with particular origins, destinations, or dates.

If you want to earn better than a green star on this assignment, think of an interesting extension or improvement to the ride board and implement it.

If you would like to develop an improved ride board for your PS8 project, contact the course staff with your ideas.

This assignment and the HooRides web application was developed by
David Faulkner, Dan Upton, and David Evans for CS150 Spring 2005.

"); print ( $res[$first] ) ; print (""); ?>
CS 150: Computer Science
University of Virginia
evans@cs.virginia.edu
Using these Materials