Due: Monday, 5 November 2007, 11:00pm
The goal of this assignment is to ensure that you are comfortable writing applications that use a DBMS as a back-end. This assignment is a short Java programming assignment.
We are going to build an application that will provide us with a web-based front-end for accessing and updating our inventory database from assignment 1. This application will also allow us to place orders for products.
You may find the following documentation helpful when completing this assignment:
Download the file: hw2.tar.gz. and extract all the files using the following command:
> tar zxvf hw2.tar.gz
You should now see a directory called hw2. If you see it, you are ready to start the assignment. If this procedure did not work for you, please contact the TA or talk to another student in the class.
To use JDBC, you need to have the jar file with the Postgres JDBC driver in you classpath. Set your classpath as follows if you're using tcsh:
> setenv CLASSPATH /usr/share/java/postgresql-jdbc4.jar:.
or as follows for bash:
> export CLASSPATH=/usr/share/java/postgresql-jdbc4.jar:.
As a first step, we will create an application with a command-line interface.
Create a class called ToyDB. Executing:
> java ToyDB
on cubist should produce the following menu:
*** Please enter one of the following commands *** > list > list <age> > describe <pid> > inventory <pid> > suppliers <pid> > manufacturer <pid> > place_order <pid> <client> <quantity> > list_orders <pid> > quit >
These commands should perform the following actions:
list: lists all the products in the Product relation.
list <age>: shows all the toys where the min_age is less than or equal to <age>.
describe <pid>: shows the detailed description of a product given its <pid>.
inventory <pid>: shows the number of items left in stock for product <pid>.
suppliers <pid>: shows the id and name of all suppliers for product <pid>.
manufacturer <pid>: shows the id and name of the manufacturer for product <pid>.
place_order <pid>: see next problem
list_orders <pid>: see next problem
quit: quits the application.
Your application should perform some rudimentary error checking, but we will not test it on erroneous inputs. Please do not spend time making things bullet-proof.
To ensure you work directly on the interesting parts of this assignment, we provide you a starter file ToyDBMenu.java that includes the code to print the menu and read user commands. The ToyDBMenu class also shows samples of how the results should be printed. You only need to rename the file and add the code to connect to the database, execute the appropriate SQL statements, and display the results.
Please write your program such that it reads the database name, login, and password necessary to connect to the database from a file. The format of the file should be:
jdbc:postgresql:cse544-07au-yourlogin
cse544-07au-yourlogin
password
This will help us when we test your assignment.
Inside hw2, we provide you a few test files called toydbX.test. Executing
> java ToyDB < toydbX.test
Should produce the same output as the one shown in toydbX.out.
We will test your application on inputs similar to toydbX.test.
For this problem, you should test your application on toydb1.test, toydb2.test and toydb3.test.
Executing queries is nice, but we also want our application to be able to modify the content of the database. In this question, we ask you to implement the remaining commands place_order and list_orders.
The first command: place_order <pid> <client> <quantity> takes as argument a product id (<pid>), the id of a client (<client>) and a quantity (<quantity>). It enables the given client to place an order to purchase the given quantity of the given product. The place_order command should process the order as follows:
If stock >= quantity for the given product, the application inserts a tuple into the Orders table and decreases stock by quantity for product identified with pid. All operations execute as part of a single transaction.
If stock < quantity for the given product, the application prints the following error message: "Not enough items left in stock" and aborts the transaction.
The second command: list_orders <pid> shows all the orders placed for product <pid>.
To support the above commands, you need to add the following relation to your database:
Orders(pid: integer, cid: integer, quantity: integer)
where pid is a foreign key that references Product, cid is a client identifier, and quantity is the quantity of the ordered item.
To test the additional features, we provide you a test file toydb4.test and toydb4.out.
As a last step, we would like to add a web-based interface to our database. There are many ways to implement this part of the assignment. We will show you the simplest technique possible and will point you to documentation presenting more sophisticated approaches. You should feel free to try these other techniques now or keep them in mind for the next time when you will be faced with a similar problem. You should at least think about the different possible system architectures and their benefits and drawbacks.
First, we need to modify our application to accept command-line arguments directly (note that ToyDBMenu already does this for you). Modify your application such that executing:
java ToyDB describe <pid>
outputs the description of the product identified with <pid> and exits. And executing:
java ToyDB place_order <pid> <client> <quantity>
places the appropriate order or produces an error message and exits.
To create the web-based front-end, we provide you the following start-up files: toydb.html, order.html, lookup.html, and toydb.cgi. Place these files in your home directory on cubist, under a subdirectory called www.
Modify the files order.html and lookup.html to invoke the toydb.cgi script in your home account. Replace "yourlogin" with your login in the line:
<form method=post action="http://cubist.cs.washington.edu/~yourlogin/toydb.cgi">
We also provide you a file called .htaccess. By placing this file in the www directory, you will restrict access to the forms to only those people who have a CSENetID.
Point your browser to the following page: http://cubist.cs.washington.edu/~yourlogin/toydb.html
You should see the page toydb.html. (Remeber to set the access rights of the html pages to ensure that you can actually see the pages.)
If these instructions did not work for you, please contact the TA or talk to another student in the class.
Using the provided forms, try to look-up an item in the inventory. This operation should work.
Extend the forms, the cgi script, and your application to perform the following two additional operations: place an order and lookup the list of orders (feel free to look up the list of orders by client or by product id).
The cgi script is written in python. If you do not know python, do not worry, python scripts are pretty much like pseudocode. Just be careful with the indentation. Here is a pointer to the Python documentation and the Python CGI documentation. If you want, you can also rewrite this script in Perl.
The technique we used above to add a web-based interface to our application is not very efficient. It works well for our simple example, but it is not scalable. Every time a user submits a request, the toydb.cgi script gets invoked. This script in turn invokes our application, which then opens a new connection to the database to execute the requested operations. All these extra processes and connections add serious overhead. There are more effective techniques for communicating with a back-end database from a front-end web interface. Chapter 7 in the Third Edition of the book by R&G presents a nice discussion of this topic. The following are some of the possible alternatives:
You should work on this assignment by yourself. Make sure to turn-in all the files that you create.
To hand-in your assignment, issue the following command under the directory where you saved all your files:
tar zcvf cse544-07au-youraccountname.tar.gz *
After executing this command, you should see a file named cse544-07au-youraccountname.tar.gz. Please send this file as an email attachment to your TA.
For the web-based interface, please leave all the forms in your www directory. The TA will play test them directly there.