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

• Understand how a web application is designed and implemented.
• Gain confidence that you can learn new languages on your own.
• Gain experience with some useful languages (Python, SQL, HTML, and JavaScript) for making web applications.
• Build your own web community.
• (Maybe) Save gas expenses by sharing rides home for Thanksgiving.

Background

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:

• HTML (Hypertext Markup Language) — the language used to describe web pages. See the Schemer's Guide to HTML.
• Python — a universal programming language that can be incorporated into web pages and is evaluated by the web server when a page is requested. See the Schemer's Guide to Python.
• SQL (Structured Query Language) — a language for manipulating and extracting information from a database similar to the procedures you used in PS5. See the Schemer's Guide to SQL.
• JavaScript — a client-side programming language that is incorporated into generated web pages and evaluated by the web browser. We use JavaScript to interface with the Google Maps client application. (There is no guide provided to JavaScript, since you are not expected to modify any of the JavaScript code.)

Getting Started

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

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

...

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
)

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. Insert a user into your users table with user name alyssa, email aph@cs.virginia.edu, last name Hacker and firstnames Alyssa P.. Insert a user into your users table with user name ben, email bb@cs.virginia.edu, last name Bitdiddle and firstnames Ben. 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 Select the lastname and firstnames of all users in your table in alphabetical order by firstnames. The response should be a table like: firstnames lastname Alyssa P. Hacker Ben Bitdlddle David Evans 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 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

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 ()

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

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

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

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

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.