Problem Set 8:
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.
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
- 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
- Build your own web community.
- Satisfy late night cravings with yummy Python Burgers and Lambda Cakes.
In 1990, Tim
, wrote a program called WorlDwidEweb
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
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:
- 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
web pages and is evaluated by the web server when a page is
requested. See the PS7, Chapter 12 and 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.
incorporated into generated web pages and evaluated by the web browser.
Create a directory hooshungry
inside your public_html
directory. Download ps8.zip
and unzip it into your public_html\hooshungry\
this, you should be able to see the web application by opening a web
browser to http://www.people.virginia.edu/your
. You should see a welcome page including
links to Register New User
and Display Restaurants
(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
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.
Our web application uses the Google Maps API
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
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
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
Thank you for signing up for a Google Maps
API key. Your key is:
Copy the key into the file gmap.py
, replacing the provided key.
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
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 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,
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
on the left side of the page to see the table you
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', 'email@example.com', '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.
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 firstname.lastname@example.org, last name
Hacker and firstnames Alyssa P..
- Insert a user into your users table with user name
ben, email email@example.com, 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):
- Select the lastname and firstnames of all users
in your table in alphabetical order by firstnames. The
response should be a table like:
- Select the email address of all users in your table with
lastname matching Hacker. The response should be the
- 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.
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
CREATE TABLE restaurants (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
lat DECIMAL(54, 30) NOT NULL,
lng DECIMAL(54, 30) NOT NULL,
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).
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
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.
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
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.
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
that is a restaurant identifier. It is the number of the id
field of the corresponding restaurant in the restaurants
Create your reviews table by issuing this SQL command in phpMyAdmin:
CREATE TABLE reviews (
restaurantid INT NOT NULL,
You should now be able to add some reviews and see them on the
restaurant pages. The index page has a link to Display
which links to the show-reviews.cgi
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.
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.