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:
In this homework, you have two main tasks. The first is to design a database of your customers; you will use this Postgres database along with your existing IMDB database from homework 1.
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.
Your customer interface will be a Java application. Download the starter code files; you will see 4 files:
VideoStore.java: the command-line interface to your video store;
calls into Query.java to run customer transactions
Query.java: code to run customer transactions against
your database, such as renting and returning movies
dbconn.properties: a file containing settings to connect
to the customer and IMDB databases
postgresql-8.4-701.jdbc4.jar: the JDBC to Postgres driver.
This tells Java how to connect to a Postgres database server, and needs to be
in your CLASSPATH (see below)
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 1. 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 1, 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 1. 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.)
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 previous homeworks is already present
(turn in the setup files for imdb if it has changed too).
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. (Note: in real life, you would NEVER store an actual password in the database, only a hash of the password, but for the purposes of this assignment, we can pretend not to worry about it.)
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.
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).
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:
Query.java to
read and write customer data from your database, taking care to
ensure atomic transaction semantics.
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.
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 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.
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.)
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.
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).
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).
The "rent" transaction. The user types in rent MOVIE_ID,
and you will "rent" that movie to the customer.
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.
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.
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:
Postgres does not actually support fully serializable transactions - in many cases, Postgres allows data to be read and written in a way that is not allowed in a serial transaction schedule. We will discuss these issues in class, along with some workarounds you can use for this homework.
Don't try to start a transaction inside another transaction! Postgres doesn't support nesting transactions, and will ignore attempts to run START TRANSACTION while already in a transaction. It will not ignore COMMIT or ROLLBACK, however, which means that if you (accidentally or purposefully) nest transactions, you could end up committing or aborting a transaction earlier than you intended.
FINAL COMMENTS:
The starter code is designed to give you a gentle introduction to embedding SQL into Java. Start by running the starter code, examine it and make sure you understand the part that works. Then create your customer database, insert 1-2 customers, and uncomment the few lines of code in the starter code that do the user authentication and that greet the user with her/his name and plan details. This should all work flawlessly (if not, talk to the instructor or TA). Then, complete the first (the "search") transaction, i.e. return the actors. Then continue with the other functions.
The completed project has about 20 quite simple SQL queries embedded in the Java code. Some queries are parameterized: a parameter is a constant that is known only at runtime, and therefore appears as a '?' in the SQL code in Java: you already have examples in the started code.