CSE 344 Homework 5

Objectives:
To get experience with database application development and transaction management.
Reading assignments:
Chapter 6, and Section 9.6
Assignment tools:
PostgreSQL, IMDB dataset ascii version, starter code files
Due date:
May 17, 2011, at 11:59 pm in the dropbox
What to turn in:
Customer database schema in setup.sql, completed version of Query.java starter code file

Read this whole document before starting this project. There is a lot of valuable information here, including the Final Comments section at the bottom.

Congratulations, you've decided to start your very own video rental store!

You've just signed a contract with a content provider that has videos of all the movies in the IMDB database, and you will resell this content to your customers. Once you open for business, your customers will access your service online to search the IMDB movie database for movies they are interested in, and then rent movies (which we assume are delivered by the content provider; we don't do this part in the project). Once a customer rents a movie, she can watch it as many times as she wants, until she decides to "return" it to your store. You need to keep track of which customers are currently renting which movies.

There are two important restrictions:

  1. Because your store is brand-new, your contract with the content provider will only allow you to rent each movie to at most one customer at any one time. The movie needs to be first returned before you may rent it again to another customer (or the same customer).
  2. Your own business model imposes a second important restriction: your store is based on subscription (much like Netflix), allowing customers to rent up to a maximum number of movies for as long as they want. Once they reach that number you will deny them more rentals, until they return a movie. You offer a few different rental plans, each with its own monthly fee and maximum number of movies.

In this homework, you have two main tasks. The first is to design a database of your customers; this database will live in the same Postgres database as your existing IMDB data from homework 3.

The second task is to complete a working prototype of your video store's customer interface. This system consists of a temporary command-line interface to your back end system (to be replaced by a Web interface before you open for actual business). The back end connects to your database to update the customer database to rent and return movies. We have already provided code for a complete UI and partial back end; you will implement the rest of the back end.

Task 0: Running the starter code (0 points)

Your customer interface will be a Java application. Download the starter code files; you will see 4 files:

To run the starter code, you will need the the Java JDK, if you don't already have it. You also need your imdb database in Postgres, as created in homework 3. If you deleted this database, you should recreate it. Once you do this, you will need to modify dbconn.properties to point to the imdb database, and to enter your Postgres username and password. (If you are running Postgres on a Windows lab machine using the instructions from homework 3, then you didn't have to enter a password. In that case, you can enter any password you like.)

Now that you have configured the database, you can run the starter code. If needed, start up the Postgres server using the instructions from homework 3. Then run the following commands, depending on your platform:

Windows Linux or Mac 
cd \where\you\unzipped\the\starter\code
[replace the directory below with your JDK's bin\ directory]
path C:\Program Files\Java\jdk1.6.0_25\bin;%path%
set CLASSPATH=.;postgresql-8.4-701.jdbc4.jar
javac -g VideoStore.java Query.java
java VideoStore user password

cd /where/you/unzipped/the/starter/code


export CLASSPATH=.:postgresql-8.4-701.jdbc4.jar
javac -g VideoStore.java Query.java
java VideoStore user password

The last command launches the starter code. Normally, you run it like this: java VideoStore Joe jopsswd , that is, you provide the username and password of the video store user. Your project should check that Joe is a valid user in the database, and that his password is correct; the starter code does not authenticate the user, so user and password are ignored.

Now you should see the command-line prompt for your video store: 

 *** Please enter one of the following commands ***
> search <movie title>
> plan [<plan id>]
> rent <movie id>
> return <movie id>
> fastsearch <movie title>
> quit
>

The search command works (sort of). Try typing: 

search Nixon

After a few seconds, you should start getting movie titles containing the word 'Nixon', and their directors. (You don't yet get the actors: one of your jobs is to list the actors.)

Task 1: Customer database design (20 points)

Your first task is to design and create your customer database in Postgres. You should create a different database from the imdb database, but will be hosted by the same Postgres server as the imdb database. That is, you will have two databases: imdb and customer. You will also need to add a line to the dbconn.properties file to tell Java how to connect to customer. (Note: it is important for you to understand that customer and imdb are two different databases, and that in practice they are likely to be hosted on different database servers, running on two different machines, perhaps in two different geographical locations. In fact, if you have access to a second Postgres server, try hosting the two databases on the two different servers, as an experiment (this is not required in the assignment).) Once you design the database, create in customer all the tables needed by your video store application.

What to turn in: a single text file called setup.sql with CREATE TABLE and INSERT statements for this database, assuming the imdb database from homeworks 2 and 3 is already present (turn in the setup files for imdb if it has changed too).

Customer information

Your customer information database has the following entity sets:

E1. Customer: a customer has an id (integer), a login, a password, a first name and a last name.

E2. Plan. Each plan has a plan id (integer), a name (say: "Basic", "Rental Plus", "Super Access" -- you can invent your own), the maximum number of rentals allowed (e.g. "basic" allows one movie; "rental plus" allows three; "super access" allows five; again, these are your choices), and the monthly fee. For this project, you are asked to insert four different rental plans.

E3. Rental: a "rental" entity represents the fact that a movie was rented by a customer with a customer id. The movie is identified by a movie id (from the IMDB database). The rental has a status that can be open, or closed, and the date and time the movie was checked out, to distinguish multiple rentals of the same movie by the same customer. When a customer first rents a movie, then you create an open entry in Rentals; when he returns it you update it to closed (you don't delete it). Keeping the rental history helps you improve your business by doing data mining (but we don't do this in this class.)

In addition there are the following relationships:

R1. Each customer has exactly one rental plan.

R2. Each rental refers to exactly one customer. (It also refers to a single movie, but that's in a different database, so we don't model that as a relationship.)

Create a text file called setup.sql with CREATE TABLE statements and INSERT statements that populate each table with a few tuples (minimum 8 tuples): you will turn in this file. This file should load into Postgres with the psql "\i" command, so put anything that is not runnable SQL in comments. Write a separate script file with DROP TABLE statements; it's useful to run it whenever you find a bug in your schema or data (don't turn in this file).

Task 2: Java customer application (80 points)

Your second task is to write the Java application that your customers will use, by completing the starter code. You need to modify only Query.java. Do not modify VideoStore.java, because we will test your homework using the current version of VideoStore.java.

What to turn in: the Java file Query.java.

The application is a simple command-line Java program. A "real" application will have a Web interface instead, but such an interface is not the topic of this class. Your Java application needs to connect to both the imdb and the customer databases, hosted by your computer's Postgres server.

When your application starts, it reads a customer username and password from the command line. It validates them against the database, then retains the customer id throughout the session. All rentals/returns are on behalf of this single customer: to change the customer you must quit the application and restart it with another customer. The authentication logic is not yet wired up in the starter code; as mentioned above, one of your tasks will be to make it work.

Once the application is started, the customer can select one of the following commands:

To complete your application, you need to do the following:

  1. Complete the provided IMDB movie search function, fixing a security flaw in it along the way.
  2. Write a new, faster version of the search function.
  3. Implement the remaining functions in Query.java to read and write customer data from your database, taking care to ensure atomic transaction semantics.

Task 2A: Completing the search function (20 points)

In the search command, the user types in a string, and you return:

The starter code already returns the movies and directors. Your task to return all actors, and also to indicate whether the movie is available for rental.

Task 2B: A faster version of search (20 points)

The search function in task 2A uses dependent joins, and can be slow sometimes. (Note: the speed depends dramatically on whether you are running with a cold cache, or a hot cache.) Your task here is to write a faster version of search, called fastsearch, by using joins (or outer joins? you need to determine that!) instead of dependent joins. Your search should return only (1) the movie information (id, title, year) and (2) its actors. For extra credit you can also return (3) directors and availability information.

How much better should you expect fastsearch to be? With a hot cache, fastsearch typically increases the speed very little: perhaps from 2-3 seconds to 1 second or so; with a cold cache the performance increase may be larger, from minutes, to several seconds.

Task 2C: Customer database transactions (30 points)

Now, complete the application by implementing each of the following transactions. (We call each action a transaction. You will need to write some of them as SQL transactions. Others are interactions with the database that do not require transactions.)

  1. The "login" transaction, which is run implicitly when you start your command line program, authenticating the user by his/her username and password. Much of the authentication logic is already provided in the starter code. For the most part, all you need to do is uncomment the code that performs the authentication and modify it to match your CUSTOMER schema.

  2. The "print customer info" transaction: To provide a minimum amount of user-friendliness, at each iteration of the program's main loop, you need to print the current customer's name, and tell them how many additional movies they can rent (given their current plan and the number of movies that they have already rented).

  3. The "plan" transaction. Here, the customer types the command plan PLAN_ID and you set her new plan to that plan id. How does the customer know which plan id's are available? They type in plan without any plan id, and then you will list all available plans, their names, and their terms (maximum number of movies available for rental and monthly fees).

  4. The "rent" transaction. The user types in rent MOVIE_ID, and you will "rent" that movie to the customer.

  5. The "return" transaction. The user types in return MOVIE_ID. You update your records to mark the return of that movie.

Be sure to use SQL transactions when appropriate to implement these "transactions". See more on this below.

Task 2D: Stop SQL Injection (10 points)

The search function in this simple application (and similarly fastsearch), provides only search by title keyword. Now type this at the prompt: 

search ' and year=1899 --

You get all movies in 1899! Perhaps try this? search '; drop movie; drop casts; drop actor; -- Actually, don't try it, you get the idea...

This is SQL injection: hackers like to do it on Website interfaces to databases. Your task here is to fix the search and fastsearch function to prevent SQL injection attacks. Fix the security issue by changing the code in Query.java (only). Hint: when fixing the issue, look at other parts of the starter code that execute SQL to see what they do differently from the broken search code.

Transaction management (for Task 2C)

You must use SQL transactions in order to guarantee ACID properties: you must define begin- and end-transaction statements, and insert them in appropriate places in Query.java. In particular, you must ensure that the following two constraints are always satisfied, even if multiple instances of your application talk to the database.

C1. at any time a movie can be rented to at most one customer.

C2. at any time a customer can have at most as many movies rented as his/her plan allows.

Concretely: (a) when a customer requests to rent a movie, you may need to deny this request and (b) when a customer selects a "lower" plan (with fewer allowed movies), you may also need to deny this request (why?). You can implement denying in many ways, but we strongly recommend the SQL ROLLBACK statement.

You must use transactions correctly such that users cannot cheat, nor can race conditions introduced by concurrent execution lead to an inconsistent state of the database. For example, a user may try to cheat and coerce your application to violate the constraint C2 above by running two instances of your application in parallel, with the same user id: depending on how you write your application and on race conditions, the malicious user may succeed in renting more movies than he/she is allowed. Your properly designed transactions should prevent that.

Design transactions correctly. Avoid including user interaction inside a SQL transaction: that is, don't begin a transaction then wait for the user to decide what she wants to do (why?). The rule of thumb is that transactions need to be as short as possible, but not shorter.

You can execute the SQL code for START TRANSACTION and friends directly, using the SQL we have provided in the starter code: 

// In each operation that is to be a SQL transaction:
_begin_transaction_read_write_statement.executeUpdate();

[... execute updates and queries.]

_commit_transaction_statement.executeUpdate();
[OR]
_rollback_transaction_statement.executeUpdate();

You can also feel free to use an alternative method provided by Java: 

// When you start the database up
Connection conn = [...]
conn.setAutoCommit(false);
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

// In each operation that is to be a SQL transaction:
[... execute updates and queries.]
conn.commit();
[OR]
conn.rollback();
 
// Whenever you DON'T want a SQL transaction:
conn.setAutoCommit(true);
[... execute queries.]
conn.setAutoCommit(false);

Besides syntax, the main difference between the two ways is that if you execute the SQL code for transactions directly, then by default your SQL code is not in a transaction. (More precisely, each SQL statement is in a separate transaction, a mode sometimes called "auto-commit mode.") If you use the Java Connection class methods to turn off auto-commit mode, then by default everything is in a transaction (which will reduce the concurrency of your system).

To test that your transactions work correctly, we recommend the following (and this is how we will test your homework). Place a break in the middle of your transaction, by reading and throwing away a line of the user's input. Run two (or more?) instances of VideoStore.java, say A and B. Let both reach the point when they read from the standard input; then you decide which one you allow to proceed, and thus control the order in which the transactions are interleaved.

IMPORTANT

FINAL COMMENTS: