cs1120: Problem Set 8: HoosHungry.com

d a database.

To build this, we used several languages:

Required Reading:

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 PS7, the Course Book Chapters on Interpreters and the Web, 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.
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

Some amount of setup work will be required before your website is up and running.

Setup Step 1: (nothing to turn in for this but all team members should do all Setup steps)

To get a feel for the web application, open a web browser to https://church.cs.virginia.edu/cs1120/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.

Setup Step 2: (nothing to turn in for this but all team members should do all Setup steps)

You will be making a website similar to HoosHungry. We will storing your files on a special department webserver, named socrates, so that users can access them with a normal web browser. (We have already made you an account there.)

This special departmental server that will hold your files is sometimes called a network drive. You will have to map the network drive on to your lab computer before you can access the files. (Mac and Linux users: read all the instructions — specific info for you is at the end.)

If you are working from on grounds, skip this step. If you want to work from off grounds, you will have to install and run the virtual private network (VPN) client to access these UVA resources securely from outside. If relevant, be sure to click "make always available online" when you're using the VPN.
Right click on the My Computer icon and select Map Network Drive from the dropdown menu, as shown in this screenshot:
Normal files on your hard drive live on C:. We will use Z: for these networked files for PS8 and PS9. Make sure that Z: is selected next to the Drive: prompt. Then, at the Folder: line, write in \\socrates\mst3k where mst3k is your UVA ID. It should look like this screenshot:
- If just socrates doesn't work, try \\socrates.cs.virginia.edu\mst3k.
- Use the same password you use in the OLS 001 Labs.
- You must have logged on to the OLS 001 Lab computers at least once before you can authenticate here.
- If you are working on an OLS 001 Lab Computer, you will be authenticated automatically. If not, use CSLAB\mst3k as your username, where mst3k is your UVA ID. You may have to click on Connect as a difference user for this.
  Note the CSLAB\ prefix in the username. This is essential; without it, you will not be able to authenticate.
- A set of screenshots showing the process for Windows XP is also available.
Explore the network drive Z: and click through the public_html and cgi-bin folders until you reach Z:\public_html\cgi-bin.
Note that only the Windows machines in the lab are officially supported.
- Click here for unofficial instructions for doing this using Linux.
- Click here for unofficial instructions for doing this on a Mac.

Setup Step 3: (nothing to turn in for this but all team members should do all Setup steps)

Do you have a directory hooshungry inside your cgi-bin directoy? And does hooshungry have have index.py and other files inside of it?
- If so, do nothing for this step! We've already installed ps8.zip for you.
If you do not already have a full hooshungry directory inside your cgi-bin directory, then do this:
1. Create a directory hooshungry inside your cgi-bin directory. Using lowercase letters matters! If you're at the lab machines in OLS 001, you will be creating Z:\public_html\cgi-bin\hooshungry, but the drive letter may be different on different machines.
2. Download ps8.zip and unzip it into your public_html\cgi-bin\hooshungry\ directory.
3. The webserver that will be responsible for making your files available to the outside world is called plato. Point your web browser at http://plato.cs.virginia.edu/cgi-bin/cgiwrap/wrw6y/hooshungry/index.py but replace wrw6y with your UVA ID. You should see something like the HoosHungry website. You will not be able to try out your website fully until you have set up the database, however.
  If you mistakenly extracted your the ps8.zip files to another location, such as public_html\cgi-bin\HoosHungry\ps8\, then you'll need to point your web browser at the appropriate location (e.g., http://plato.cs.virginia.edu/cgi-bin/cgiwrap/wrw6y/HoosHungry/ps8/index.py). Uppercase and lowercase matter!

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.

The University of Virginia will provide you with your own personal MySQL database, hosted on dbm2.itc.virginia.edu. You will have to sign up and activate your database, and then create and manage the appropriate tables on it.

Setup Step 4: (nothing to turn in for this but all team members should do all Setup steps)

First, you need to create a MySQL account. Follow the directions in the SQL Guide to create your own MySQL account, being sure to select the new password security style (it's the default, so that's easy to do):

Second, you need to create a database. Follow the directions in the SQL Guide to create your own MySQL database. If your UVA id is mst3k, create the mst3k_cs1120 database, as shown below (click to enlarge):

If you haven't yet read the SQL Guide, scroll back up to where it was listed as part of the reading for this problem set and then read it. :-)

Setup Step 5: (nothing to turn in for this but all team members should do all Setup steps)

After creating your database, edit the db.py file. You can find it in hooshungry\db.py. Change the values assigned to userName, password, server and dbName to match those for the database you created. Notably, it should look something like this:

server = "dbm2.itc.virginia.edu"
username = "mst3k"
password = "XYZ123"
dbName = "mst3k_cs1120"

Yes, you really have to change the server line from dbm1 to dmb2. At this point your HoosHungry page will not work, because your new database does not have the right tables! Let's fix that up now.

Users

Since we want to only allow legitimate users to add restaurants and post reviews, we need a table for managing users.

You will 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). In later lectures we will explain more about why the way it does things is semi-secure.

Setup Step 6: (nothing to turn in for this but all team members should do all Setup steps)

You can create the users table by issuing this SQL command:

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
)

To enter SQL commands using phpMyAdmin, first click on the Databases link near the center of the page.

Next, click on the mst3k_cs1120 database line.

We could use their handy table creation interface, but we'll do it with raw SQL instead. So click on the SQL tab in the top center:

A big empty text field window should appear:

Look for the words "on database mst3k_cs1120" in the upper right hand corner (circled in green in the screenshot). If you don't see those, you're not operating on the right database.

Now copy and paste the query above into the text box, and click Go:

If it works, you will see the text "Your SQL-query has been executed successfully":

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.

Setup Step 7: (nothing to turn in for this but all team members should do all Setup steps)

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 ('weimer', 'weimer@cs.virginia.edu', 'Weimer', 'Wes')

(Don't forget the quote (') marks.) If you do it correctly, it will look something like this, but with your name instead of "Wes Weimer":

At this point, your users table should contain one entry corresponding to yourself. We'll use the Browse command to check this is the case. If you see a Brownse option in the upper left, click it. If not, click on the Structure tab in the upper left, and then click on the Browse icon to the right of your table, as shown below:

In the screenshot, the Browse icon is circled. When you click on Browse, you should see something like this:

Issue the necessary SQL commands to repair the table if it is not correct. Note that now that we've run the INSERT command (at the top of this step), we can see the one entry in teh table. 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 very similar to the table-select procedure you defined in Problem Set 5, but there are a few differences: see the SQL Guide for details on SELECT.

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.

Setup Step 8: (nothing to turn in for this but all team members should do all Setup steps)

You absolutely must have switched over to your own database by now. If you are still using the default wrw6y database in db.py, you should return to Step 5.
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.

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

Setup Step 9: (nothing to turn in for this but all team members should do all Setup steps)

You absolutely must have switched over to your own database by now. If you are still using the default wrw6y database in db.py, you should return to Step 5.
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
)

When you have created all three tables, click on the Database: mst3k_cs1120 link in the upper center for a summary. You should see all three tables listed:

Google Maps

Google maps is the best!
True dat.
Double true!
— Chris Parnell and Andy Samberg, Lazy Sunday

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.

Setup Step 10: (nothing to turn in for this but all team members should do all Setup steps)

To use the Google Maps API, you need to obtain a Google Maps API key. We have already obtained a Google Maps API key for you. There is nothing for you to do in this step.

(If you expect to get more than about 1,000 page views per day for your PS8, you need to contact Google to get separate permission. Follow the directions here: http://www.google.com/apis/maps/signup.html. This will probably not be a problem for you unless you add some extra features to your site!)

Setup Step 11: (nothing to turn in for this but all team members should do all Setup steps)

At this point, you should be able to see the web application by opening a web browser to http://plato.cs.virginia.edu/cgi-bin/cgiwrap/your_uva_id/hooshungry/index.py You should see a welcome page including links to Register New User and Display Restaurants, plus a map of Charlottesville.

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.

Setup Step 12: (nothing to turn in for this but all team members should do all Setup steps)

To activate your account, you will need to reset the password.

Reload your main page ( http://plato.cs.virginia.edu/cgi-bin/cgiwrap/your_uva_id/hooshungry/index.py ) and click on the Reset Password link.

This links to the Python file reset-password.py, that will be executed on the webserver in response to the client request for the reset-password.py 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.py and reset-password-action.py and users.py and see if you can understand what it is doing.

Understanding SQL

Question 1: For each question, provide the SQL command that performs the requested action and run your command on your database (i.e., using phpMyAdmin, as above). 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

Weimer

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 Bitdiddle

Westley Weimer
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.

lastname
Weimer
Hacker
Bitdiddle

firstnames	lastname
Alyssa P.	Hacker
Ben	Bitdiddle
Westley	Weimer

email
aph@cs.virginia.edu

Turn in: Write down (or print out) and turn in the correct SQL commands.

Debugging Web Applications

Debugging a Python web application is slightly trickier than debugging a normal Python application because your Python code is actually being run on the server, plato, on not on your dekstop machine. We will practice debugging together.

Question 2: Edit index.py, the main HoosHungry file. Notice that the first line says #!/usr/bin/python. This tells the web server that this file is a Python script. Don't touch that line! If you ever make a new Python script for a web application, it will need that line first as well.

Anyway, change index.py so that you add the text

if true: then false

to the second line. Note that the line you were just told to add is invalid Python syntax. It represents a mistake.

Direct your browser to http://plato.cs.virginia.edu/cgi-bin/cgiwrap/your_uva_id/hooshungry/index.py . You should see the words Internal Server Error, which are not very helpful.

Now direct your browser to this new URL, which is just like the old one but with a d added after cgiwrap ("d" for "debug"):

http://plato.cs.virginia.edu/cgi-bin/cgiwrapd/your_uva_id/hooshungry/index.py

Scroll down to the absolute bottom of the page. You should find an error message explaining the error on Line 2. Copy-and-paste that error message and submit it via automatic adjudication.

Now go back and fix up index.py by removing the erroneous line!

Whenever you receive a "blank page" or an Internal Server Error message, use the cgiwrapd power to help you debug the problem.

Turn in: A copy of the error message via automatic adjudication.

Simple Web Applications

We have included two simple web forms with the HoosHungry distribution. One is simple-scheme.ss and the other is simple-python.py.

Question 3: Direct your browser to http://plato.cs.virginia.edu/cgi-bin/cgiwrap/your_uva_id/hooshungry/simple-python.py and then to http://plato.cs.virginia.edu/cgi-bin/cgiwrap/your_uva_id/hooshungry/simple-scheme.ss . In each case, enter your name, pick a color, and use the quick brown fox as your list of words.

Notice that when you submit the web form, control is transfered to either simple-python2.py or simple-scheme2.ss.

Read all four of simple-scheme.ss, simple-scheme2.ss, simple-python.py and simple-python2.py carefully. Choose either Scheme or Python. Edit the corresponding file and add a form field requesting the year of the user's birth. Edit the corresponding second file and print out the user's year of birth on the other side.

Do not go too quickly through this step — take some time to understand these files, because this is PS9 (the Final Project) in a nutshell.

Take a screenshot of your input form webpage showing the near "year" question. Then take a second screenshot of the second webpage showing the chosen "year" being displayed.

Turn in: The two screenshots demonstrating your working modified simple web application.

Restaurant and Review Information

If would be nice if you could 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.py file. This file is not complete in your implementation.

If you click on a marker on the map, and then click on the restaurant name, it would be nice to see 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 4: 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.

Turn in: Use the automatic adjudicator. The deliverables are:

The URL for the restaurant information screen (this must be your website and not church.cs.virginia.edu). For full credit, you must have at least two separate reviews by two different reviewers for the given restaurant.
Copy and paste the new c.execute("") line from restaurants.py into the adjudicator.

In addition, the Display Reviews functionality could be improved:

Question 5: Complete the show-reviews.py file so that clicking on Display Reviews displays a page showing all the restaurant reviews (that is, all reviews for all restaurants), sorted from highest star rating to worst. To accomplish this, you should examine and understand the code in show-restaurants.py. To display the reviews in the right order, you will also need to modify the reviews.getAll() method defined in reviews.py.

You absolutely must have switched over to your own database by now. If you are still using the default wrw6y database in db.py, you should return to Step 5.

When displaying the reviews, do your best to follow the format provided in the sample website. Additionally, ensure that the ratings are represented using stars (* * * * *) and not numbers (5).

Turn in: Use the automatic adjudicator. The deliverables are:

The URL for your Display Reviews page. For full credit, you must show at least two separate restaurants, each of which must have at least two separate reviews. That is, you must have added the restaurants and reviews to your database — either via phpMyAdmin or via your HoosHungry website.
Copy and paste your updated c.execute("SELECT * FROM reviews") line from reviews.py.

Question 6: Print out all of show-reviews.py and submit it as part of your written turn-in.

Free Time: If you want to get a head start on PS9, check out our video tutorials on how to make this website prettier (e.g., with cascading style sheets or embedded animation and slideshows).

This assignment was updated by Wes Weimer for cs150 Spring 2009, developed by David Evans for cs150 Spring 2007, and built from the HooRides web application that was developed by David Faulkner, Dan Upton, and David Evans for cs150 Spring 2005.

cs1120: Problem Set 8: HoosHungry.com

cs1120 Fall 2010

classes book syllabus ps exams guides forum

cs1120: Computer Science
from Ada and Euclid to Quantum Computing and the World Wide Web

Instructor
Westley Weimer

Email Address
cs1120-staff@cs.virginia.edu

Class Meetings
Mondays and Wednesdays, 3:30-4:45pm in CHEM 005

Office & Lab Hours
Mo 14:30-15:00 Wes
Mo 17:00-19:00 Evan
Fr 15:00-16:00 Nathan
Su 13:00-14:00 Evan Yang
Su 14:00-15:00 Evan
Su 15:00-17:00 Jon

Located in Thornton Stacks
or OLS 001 (We 11:00-14:00 + Su)
or OLS 219 (Mo/We 14:30-15:00)