Project 3

Due Monday, Nov. 30th, 2009 by 4:00pm

Partners

Please remember the ongoing rule about partnerships. The CS143 project may be completed individually or in teams of two, the choice is up to each student. Partnership rules consist of the following:

An identical amount of work is expected and the same grading scale is used for individual and team projects.
If you work in a team, choose your partner carefully. Teams are permitted to "divorce" at any time during the course, and individual students may choose to team up as the project progresses, however students from divorced teams may not form new teams or join other teams.
Both partners in a team will receive exactly the same grade for each project part submitted jointly.

Please make sure to submit only one copy of the assignment if you work in a team.

Part 1

The primary purpose of this first "PHP warm-up" part is for us to provide you with a whole bunch of basic information, and to get everyone up-to-speed on our computing systems and the languages and tools we will be using. Those of you who have done some Web programming before, especially in PHP, may find this project part nearly trivial. Those of you who haven't will find it merely straightforward. With all that said, please don't start at the last minute -- as with all programming and systems work, everything takes a bit of time, and unforeseen snafus do crop up.

Step 0: Getting started with PHP on SEASNet

Please note that the Apache httpd is listening on port 9143. httpd is running on lnxsrv03, but there is also the alias cs143.seas.ucla.edu. Specifying either lnxsrv03 or cs143 will get you to the same server. User's web directory is ~/public_html. In order to allow access to that directory you must do the following:

chmod o-rw ~ (DO NOT ALLOW anyone into your home directory)
chmod o+x ~ (open your home directory for web access only)
mkdir -m 701 ~/public_html (your html files and PHP scripts go in here)

Creating a simple HTML:

create index.html in public_html
chmod o+r index.html
access index.html with http://cs143.seas.ucla.edu:9143/~cs143xxx/index.html (replace cs143xxx with your actual username)

Creating a simple PHP script:

Create script1.cgi in public_html.
Example:
#!/usr/bin/php
<?php
print 'Content-type: text/html' . "\n\n";
print '<html><head><title>Title of the document</title></head>';
print '<body>';
print 'Hello from SEASnet. Good luck! <br />';
print '</body>';
print '</html>';
?>
chmod u+x script1.cgi
test the script on the command line, i.e., ./script1.cgi
access from a browser as http://cs143.seas.ucla.edu:9143/~cs143xxx/script1.cgi

Note on PHP: All CGI scripts, including PHP, must have the extension .cgi because this is enforced in httpd.conf. PHP is used as CGI (not as httpd's module) to enhance security by using suEXEC, http://httpd.apache.org/docs/2.2/suexec.html Although running PHP as an Apache's module provides for more convenient coding style, it is not allowed because we cannot use suEXEC in this case.

Step 1: Review the W3CSchools PHP tutorial to learn basic PHP. Please read at least up to "PHP $_POST" page of the tutorial. You can test the examples in the tutorial by creating a php page in the ~/public_html directory. All files in ~/public_html are served by the guest Apache server and are accessible at http://cs143.seas.ucla.edu:9143/~cs143xxx/ from your host browser. Note that cs143xxx should be replaced by your username.

Step 2: Connecting to the database

Connecting to a database in PHP is simple. For this lab, the database set up is MySQL, so you will need specific syntax for that library. To connect to the database, at the beginning of your php script you should have the following lines:

#!/usr/bin/php
<?php
print 'Content-type: text/html' . "\n\n";
$username="USERNAME";
$password="PASSWORD";
$database="DATABASE";
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
?>

Clearly you must replace USERNAME, PASSWORD and DATABASE with your class username, password and database. Open up your PHP script in a web browser. For example if you named your script script2.cgi, you would enter http://cs143.seas.ucla.edu:9143/~cs143xxx/script2.cgi. The script should show nothing at all, most importantly it shouldn’t say “Unable to select database”.

Step 3: Forms

You will need to write several forms in this project. A form is similar to a function call. Each of the inputs is a parameter and the function that’s called is the action. This is an example of a simple form echoed out in PHP:

echo " <form method='post' action='script3.cgi'>
<input type='hidden' name='arg' value='begin'>
<input type='submit' value='Go'>
</form> ";

This form will load the script script3.cgi with a parameter of ’arg’ set to ’begin’. To retrieve this parameter in your php script you can do the following:

NOTE: This is a work-around, because due to security reason the global variable $_POST is disabled on SEASNet.

// POST data come to "stdin" with running php as cgi, not "input"
// $_POST is not set when running PHP as CGI
$post_string = file_get_contents('php://stdin');
print 'Post string = ' . $post_string;
$post_array = split ('&', $post_string);
// Now create our own $_POST array
foreach ( $post_array as $parameter ) {
$param_array = split ('=', $parameter);
$_POST["$param_array[0]"] = $param_array[1];
}
$param1 = $_POST['arg'];

This will put the value from ’arg’ into the new variable param1. You can use this to check different task values. For example if ($param1 == ’begin’) {}... will check the value of param1. You will have to write several of these forms throughout this project.

Step 4: Queries in PHP

Queries in PHP are the same as the command line queries you executed in Project 1. Only difference is, the actual query will be stored in a string variable, and then executed using a specific php function. Here’s an example of how to execute a query:

$query = "SELECT first FROM actor";
$result = mysql_query($query) or die("Error");

Retrieving results from queries is also simple. The resulting table is returned as a series of associative arrays. Here’s how to go about retrieving data from your query. Assuming the query has already been executed, and its result stored in $result...

while ($row = mysql_fetch_row($result))
{ $actor = $row[0]; print $actor."<br/>";
}

This will echo (or display) all the fields associated with actor's first name in each row in your actor table. In this part you are to write several forms, that will call the same function in your PHP script, with different variables. Once eveyrthing is written, this is the database-oriented functionality expected from your script:

Some kind of a simple search through your database, with ability to display the results (For example a form that allows you to search for actors by DOB)

Ability to remove results that have been searched for (For examples delete actors with specified DOB)

Ability to add a new row to your table (Any table you choose)

Part 2

Web functuanality

The primary purpose of this part is to create a functional webpage where a user can login as well as rate movies and administrator can manager user accounts. All of this is to be done using PHP.

Step 0: Logging in and rating movies

As mentioned before, in this project users are expected to both be able to login using the accounts that they create and also be able to rate movies. The database stores average rating of a movie based on up-to-date rating information. For users to rate movies, they need to register for the site and create logins for themselves. After they login to the site with the correct user name and password, they can rate movies. A user can rate a movie in the range of 1-10. Every time a new rating is generated, the average rating for the movie is updated in the database. (Hint: you may want to store the average rating of a movie and the count of users who have rated this movie so far for such updates ). It is up to you, the owner of the database, to decide if you allow a user to update/delete his rating of a movie.

Step 1: Interface for administrator

The database administrator is a special user with the user name “admin”. He has unrestricted access to all the data in the database. Besides the search interface for regular users, you web site should also support the direct input of SQL queries for use by the administrator. Information about the movies, actors, actresses, directors, and mpaa ratings in the movie database can only be updated with an administrator login. (Hint: you can use the same SQL interface for both searches and updates by the administrator.)

Step 2: Constraints

Updating the database

After the initial loading, only the administrator is allowed to add tuples representing new movies, new actors, new actresses, or new directors to the relevant tables, and to update or delete existing tuples about movies, actors, actresses, and directors.

The database can also be updated with the information of user login or rating when a user registers to the site or rates a movie.

Need of user login

A user can search the database without having to login. But login is needed to be able to rate movies.

Rating movies

To ensure fair rating, each user is allowed to rate a movie only once. In addition, a user under the age of 18 is only allowed to rate certain movies based on the mpaa-ratings of a movie and the user’s age. Use this chart as a guideline of mpaa-ratings.

Part 3

Queries

The primary purpose of this exercise is to make sure that your web application can express various types of queries.

Step 0: Query support

A. Simple searches based on properties of movies. For this category, your application should support the following queries:
- Find all movies that have ratings in the input rating range.
- Find all movies that were released in the input year range.
- Find movies with title given as input. Users should be able to search for both exact matches matches and substring matches (using the LIKE construct in SQL).
B. Complex searches involving multiple entities. The following queries involve the correlation of two or more entities.
- Find movies with a particular actor, actress, or director given as input. Users should be able to search for both exact matches and substring matches (using the LIKE construct).
- Find movies that have all the input actors (and/or actresses) in it.
- Find all actors and/or actresses who have worked with a given director.
C. Top K searches
- Show top k (k≥1) movies as sorted by rating. In case of a tie, show any top k movies.

Step 1: Express the following queries using your interface

1. List all the directors who directed a 'Documentary' movie. Your query should return director name, the movie name, and the year.
2. List the first and last names of all the actors who played in the movie 'Office Space'.
3. List all the actors who acted in most number of movies.
4. List all directors who directed 5 movies or more, in descending order of the number of movies they directed. Return the directors' names and the number of movies each of them directed.
5. Find the films with more women actors than men.
6. This query consists of two parts:
- (a) For each year, count the number of movies in that year that had only female actors.
- (b) Now make a small change: for each year, report the percentage of movies with only female actors made that year, and also the total number of movies made that year. For example, tuple 1990 28.76 6098 would represent that in 1990 there were 6098 movies, and 28.76% had only female actors.
7. Find the film(s) with the largest cast. Return the movie title and the size of the cast. By "cast size" we mean the number of distinct actors that played in that movie: if an actor played multiple roles, or if the actor is simply listed more than once in casts we still count her/him only once.
8. For every pair of male and female actors that appear together in some film, find the total number of films in which they appear together. Sort the answers in decreasing order of the total number of films.
9. Let's define the DiCaprio number of an actor as the length of the shortest path between the actor and DiCaprio, Leonardo in the "co-acting" graph. That is, Leonardo DiCaprio has DiCaprio number 0; all actors who acted in the same film as LD have DiCaprio number 1; all actors who acted in the same film as some actor with DiCaprio number 1 have DiCaprio number 2, etc. Return all actors whose DiCaprio number is 2.

Late Submission Policy

To accommodate the emergencies that students may encounter, each team has 3-day grace period for late submission. The grace period can be used for any part of the project in the unit of one day. For example, a student may use 1-day grace period for Project 1 and 2-day grace period for Project 2. Any single project part may not be more than 2 days late. Note that even if a team submits a project 12 hours late, they would need to use a full day grace period to avoid late penalty. If your project is submitted late, we will automatically use the available days in your grace period unless you specifically mention otherwise in the README file.

What to Submit

Please submit the following files electronically (no hard copy):

Part 1:

Submit all scripts created in Step 4 in P1.tar. In your README (described below) provide a URL for testing your implementation of step 4.

Part 2:

Submit all scripts you created for this part in P2.tar. As in Part 1, in your README provide a sample user login and admin login for testing your implementation.

Part 3:

Submit queries.txt - A file containing all your queries from step 1 and URL in README that demonstrates all functionality from step 0.

General

A README file that includes any information you think is useful. At a minimum, your README must contain your name, SID, email address and information requested above. If you are working as a team, make sure both team member's information is included, and discuss briefly how the work was divided (For example, did you divide up the project and work separately? did you use pair programming? etc.). Your README file must be in PLAIN TEXT format. No Word documents, PDF, RTF, or any other format.

Acknowledgements: http://www.cs.washington.edu/education/courses/cse444/08au/