University of Virginia, Department of Computer Science
cs150: Computer Science — Spring 2007
cs150  Spring 2007
Problem Set 8:
HoosHungry.com?		 Out: 6 April
Due: Friday, 13 April

Collaboration Policy - Read Carefully

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. If you use resources other than the class materials, indicate what you used along with your answer.

Purpose

The primary goal of this assignment is to give you some experience with a dynamic web application before you build your own web application for ps9. In addition, this assignment should help you:

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 113.6 million sites in April 2007 and many fortunes were won and lost.

For this assignment you will understand and complete the implementation of a web application that aims to provide a restaurant guide for hungry Hoos. When you are done, you will have produced a site like this: http://www.cs.virginia.edu/cs150/hooshungry.

To build this, we used several languages:

Getting Started

Create a directory hooshungry inside your public_html directory. Download ps8.zip and unzip it into your public_html\hooshungry\ 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/hooshungry/. You should see a welcome page including links to Register New User and Display Restaurants.

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.cs.virginia.edu/cs150/hooshungry. Click on Register New User to create an account for yourself. You should receive an email message with your password. Once you have logged in, you will see an additional option to Add Restaurant. Also try clicking on the markers on the map, and the links that pop-up after you click on a marker. Try adding a restaurant, reviewing a restaurant, and looking at the restaurant reviews.

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/hooshungry/ for the web page that will host the map. (If you expect to get more than 50,000 page views per day for your PS8, you need to contact Google to get permission first. This will probably not be a problem for you unless you add some extra features to your site!)

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

Users

Since we want to only allow legitimate users to add restaurants and post reviews, 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). We will explain more about why the way it does things is semi-secure, and more obvious ways are not recommended 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.

Restaurants

We also need a database table to keep track of the restaurants. We will use the restaurants table for this, and create it using this SQL command. You should issue this SQL command for your database in phpMyAdmin: 
CREATE TABLE restaurants (
   id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
   user VARCHAR(80),
   name VARCHAR(80),
   cuisine VARCHAR(40),
   lat DECIMAL(54, 30) NOT NULL,
   lng DECIMAL(54, 30) NOT NULL,
   notes TEXT
)
The restaurants table has fields for storing information about restaurants, including their locations (lat and long represent the latitude and longitude of the restaurant) so they can be drawn on the map.

Once you have created the users and restaurants tables in your database, your site should work (except for the missing parts you will finish in the questions below).

Managing Passwords

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.

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/hooshungry) and click on the Reset Password link. This links to the Python file reset-password.cgi, 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 Python file is requested, the webserver will run the file in the Python interpreter and send the printed response back to the visitor. It is not necessary to understand this code in detail, and you probably won't want to change it, but take some time to look at the code in reset-password.cgi and reset-password-action.cgi and users.py and see if you can understand what it is doing.

Restaurant Information

If you click on a marker on the map, and then click on the restaurant name, you should get a page with more information on the restaurant. With the provided implementation, however, you instead get an error since the code for lookup in restaurants.py is not complete.
Question 2: The provided lookup(restaurantno) method in restaurants.py is missing the SQL query needed to find the restaurant. Complete the definition of lookup by filling in the missing SQL query. If your definition is correct, you should be able to click on the map markers, and then the restaurant name in the pop-up, to see a page with information on the selected restaurant.

Reviews

We want to be able to collect reviews for the restaurants. This requires a new table, reviews, which will store the restaurant reviews. We want to be able to associate reviews with the restaurant they are describing, so include a field in the reviews table that is a restaurant identifier. It is the number of the id field of the corresponding restaurant in the restaurants table.

Create your reviews table by issuing this SQL command in phpMyAdmin: 

CREATE TABLE reviews (
   restaurantid INT NOT NULL,
   user VARCHAR(80),
   stars INT,
   comments TEXT
)
You should now be able to add some reviews and see them on the restaurant pages. The index page has a link to Display Reviews which links to the show-reviews.cgi file. This file is not complete in your implementation.
Question 3: Complete the show-reviews.cgi file so that clicking on Display Reviews displays a page showing all the restaurant reviews, sorted from highest star rating to worst. To accomplish this, you should examine and understand the code in show-restaurants.cgi. To display the reviews in the right order, you will also need to modify the reviews.getAll() method defined in reviews.py.

Optional Extensions

Question 4: If you want to earn better than a green star on this assignment, think of an interesting extension or improvement to the web application and implement it.

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