CS636 Homework 3 Music DB, Page Flow for pizza, API design, Java Exceptions


1)
a) For each page you reach starting from the "Home”, do the following. Start from the top link, so that the result is easy to grade.
  i) Record the URL displayed by your browser. Note that they all start with http://topcat.cs.umb.edu:11600/pizza3, so you can just record the part after this start.
  ii) Record how you got the next page to show from this page: click on a link?  click on a button? Anything else? Note: For this exercise, don't use the "back" button of  your browser.

welcome.jsp;jsessionid=E86616E23D6F4F5CD8FAF8A532429615

-- clicked link labeled Home

 

adminController/adminWelcome.html

--clicked link labeled Admin Service

 

adminController/initializeDB.html

--clicked button labeled Initialize Database

 

adminController/<c:url value = 'adminWelcome.html'/>

--clicked link labeled Back to Admin page


adminController/toppings

--clicked link labeled Manage Toppings

 

adminController/toppings    (stayed on same page)

--typed chocolate in text box, clicked button labeled add

 

adminController/toppings (stayed on same page)

-- clicked button labeled remove

 

adminController/adminWelcome.html

--clicked link labeled Back to Admin page

 

adminController/sizes

--clicked link labeled Manage Pizza Sizes

 

adminController/sizes (stayed on same page)

--typed medium in text box, clicked button labeled add

 

adminController/sizes (stayed on same page)

--clicked button labeled remove

 

adminController/adminWelcome.html

--clicked link labeled Back to Admin page

 

adminController/orders

--clicked link labeled Manage Orders

 

adminController/adminWelcome.html

--clicked link labeled Back to Admin page

 

adminController/days

--clicked link labeled Manage days

 

adminController/days (stayed on same page)

--clicked button labeled advance day to day 27, then 28 with same result

 

adminController/adminWelcome.html

--clicked link labeled Back to Admin page

 

studentWelcome.html

--clicked link labeled Student Service

 

orderForm.html

--clicked link labeled Order a pizza now!

 

orderForm.html (stayed on same page)

--clicked button labeled reset

 

orderPizza.html?room=4

--clicked button labeled Place Your Order, after selecting room 4

 

adminController/listVariables.html

--clicked link labeled List Variables

 

adminController/logout.html

--clicked link labeled logout

 


b) Draw a graph of the pages you navigated, with arrows showing use of links or buttons to get from one page to another. There are 9 pages in this pizza app: Home, Manage Toppings, Manage Sizes, Manage Orders, Manage Days, Student Welcome, Pizza Order Form, List Variables, and Logout, so there should be 9 nodes in your graph, with arrows between them as appropriate.

 

 

pizza page flow

---------------------------------

2) API Design. The service API defines the actions that the application can do. See class 8 for discussion of how we want each method in the service API to be self-contained in the sense that all the domain data needed for the action of the method is provided in the method's arguments.

a. Design a method header (no implementation) to provide a new action for the pizza project to report on (i.e. provide data on) all toppings currently in use in PREPARING orders (i.e., in the oven). If a topping is in use by more than one order, that should be clear from the result, either by a count or by repeated items returned. What source file should have this method?

Answer:   public List<String> getToppingsOfPreparedOrders() throws ServiceException  in AdminService.java (StudentService is also OK here)

or public Map<String, Integer> getToppingNamesForPreparedOrders() throws ServiceException in ...

Discussion (not needed in answer): Set<String> is not good here, because each string name would only be able to show up once. The method name can be different from above but should have a verb about retrieving, and toppings and preparing and orders in the name.  As a service API method, it must throw ServiceException.  List<PizzaTopping> is acceptable but note that no other service method provides PizzaTopping objects to the presentation layer, although, as immutable objects, it is OK to provide them.

b. Design a method header (no implementation) to provide a new action for the pizza project allowing a student to change the room number on all their orders that are currently in PREPARING or BAKED status.

Answer: public void changeRoomForCurrentOrders(int oldRoom, int newRoom) throws ServiceException

Discussion (not needed in answer): Note that the oldRoom is needed, because the service layer (stateless service layer) does not have knowledge of a "current room". This method, like most mutators, returns void, and signals errors via the ServiceException. Of course the name of the method can be different, but should have a verb about changing,  "room", "orders".

c. The method you designed in b. clearly requires new support in the DAO API, since the "real" orders reside in the database. Design a new method (no implementation) for PizzaOrderDAO to do this.

public void updateOrderRoom(int ordNo, int newRoom) throws SQLException

or public void updateOrder(PizzaOrder order) throws SQLException

Discussion (not needed in answer): The first is modeled on updateStatus. The second one assumes that the service layer has already updated the room in the argued order. This would allow any update in the order except the identifying order number. Note that we don't need a new finder to do this action because we have findOrdersByRoom which can be called for today (all PREPARING and BAKED pizzas are today's) and then the results checked for desired status values.

3) ---------------------------------

a) If the SQL in AdminDAO.advanceDay throws a SQLException, where is it caught?   

            In AdminService.advanceDay().

What happens to that exception next? 

            AdminService.advanceDay() throws a ServiceException.

Where is the ensuing thrown exception caught? 

            ShopAdmin.main(), TakeOrder.main(), or SystemTest.main()

What is printed out in this case?

            Error in run of <TakeOrder or ShopAdmin or SystemTest>:

            cs636.pizza.service.ServiceException: Unsuccessful advance day

              cause: <whatever the cause of the SQL error was>

            Stack Trace: <stack trace of all methods gone through>

           

b) Make the scenario of part a happen by changing current_day to current_dayx in the SQL update statement.  Run SystemTest on all three databases (ant oraSysTest, etc.) and record the exception report from each of the three. 

For oraSysTest:

Error in run of SystemTest:

cs636.pizza.service.ServiceException: Unsuccessful advance day

  cause: java.sql.SQLSyntaxErrorException: ORA-00904: "CURRENT_DAYX": invalid identifier

 

 Stack Trace: cs636.pizza.service.ServiceException: Unsuccessful advance day

    at cs636.pizza.service.AdminService.advanceDay(AdminService.java:108)

    at cs636.pizza.presentation.SystemTest.run(SystemTest.java:81)

    at cs636.pizza.presentation.SystemTest.main(SystemTest.java:60)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "CURRENT_DAYX": invalid identifier

 

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

for mysqlSysTest:

Error in run of SystemTest:

cs636.pizza.service.ServiceException: Unsuccessful advance day

  cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'current_dayx' in 'field list'

 Stack Trace: cs636.pizza.service.ServiceException: Unsuccessful advance day

    at cs636.pizza.service.AdminService.advanceDay(AdminService.java:108)

    at cs636.pizza.presentation.SystemTest.run(SystemTest.java:81)

    at cs636.pizza.presentation.SystemTest.main(SystemTest.java:60)

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'current_dayx' in 'field list'

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

for hsqlSysTest:

Error in run of SystemTest:

cs636.pizza.service.ServiceException: Unsuccessful advance day

  cause: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CURRENT_DAYX

 Stack Trace: cs636.pizza.service.ServiceException: Unsuccessful advance day

    at cs636.pizza.service.AdminService.advanceDay(AdminService.java:108)

    at cs636.pizza.presentation.SystemTest.run(SystemTest.java:81)

    at cs636.pizza.presentation.SystemTest.main(SystemTest.java:60)

Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: CURRENT_DAYX

    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)

           

4) Music1-setup. ---------------------------------

a) Install the supplied music1-setup project and copy it to music1, for further development.

            Done, on school Unix account and home PC.

b) Use music1-setup’s database/build.xml with ant to start hsqldb and load all three databases. Report your ant commands and success or problems. 

In database: ant start-hsqldb, ant load-hsqldb, ant show-hsqldb, ant drop-oradb, ant load-oradb, ant
show-oradb, ant load-mysqldb, ant show-mysqldb. All worked on UNIX and home PC.

 

c)  From base directory: ant oraSysTest, ant mysqlSysTest, ant hsqlSysTest. All worked on UNIX and home PC.

 

d) Set up an eclipse projects for music1-setup and music1, and compare music1-setup to pizza1, by selecting one in Project Explorer, then hold down control and select the other, then right-click and Compare With>Each Other.  Then display the differences for build.xml by selecting it in the resulting file display.  Describe the differences.  Note that this technique will be helpful to track your changes from music1-setup to music1.

            Most of the differences are just changing pizza to music.  A few comments changed.         pizza1 refers to tunnels and music1-setup refers to plink tunnels (it turns out plink = Putty link).  Targets testBL1 and testDAO1 have not yet been implemented in music but have     been implemented in pizza.

5) ---------------------------------

Note: using traditional joins here, but JOIN is great too. As usual, there are many alternative forms for these queries.

a. Find all users (user_id's) with email addresses ending with "gmail.com".

select user_id from SITE_USER where email_address like '%gmail.com';

 USER_ID
 -------
       1

--------------------------------------
b. Find all lineitems of invoice 2 and list their product id and quantity.
select product_id, quantity from lineitem
where invoice_id=2;

product_id quantity
 ---------- --------
          2        3
          4        1

c. List all the downloads (product id, filename, and date) for the user with email andi@murach.com.

select t.product_id, t.sample_filename, d.download_date from

download d, site_user u, track t

where u.email_address='andi@murach.com' and d.track_id = t.track_id

and d.user_id = u.user_id

product_id sample_filename download_date
 ---------- --------------- ---------------------
          4 filter.mp3      2009-08-30 09:34:00.0
 

d. There are no invoices in the tables in the original load, making it hard to test queries involving invoices.  Fix this problem by inserting an invoice 2 (dated 9/2/10, for user 2) with lineitems for products pf01 and jr01.  Show the three insert statements in your hw paper.

insert into invoice values (2, 2, timestamp '2010-09-02 00:00:00', 27.90, ‘n’);
insert into lineitem values (1, 2, 2, 1);
insert into lineitem values (2, 2, 4, 1);

e. List all invoice ids and invoice dates for the user with email andi@murach.com.

select i.invoice_id, i.invoice_date from invoice i, site_user u

  where i.user_id = u.user_id and u.email_address='andi@murach.com';


 INVOICE_ID INVOICE_DATE         
 ---------- ---------------------
          2 2010-09-02 00:00:00.0

--------------------------------------
f. Find all product ids purchased by the user with email andi@murach.com (i.e., find the lineitems)

select distinct l.product_id from lineitem l, invoice i, site_user u

  where u.email_address='andi@murach.com' and
        l.invoice_id = i.invoice_id and
        i.user_id = u.user_id;

 PRODUCT_ID
 ----------
          2
          4


--------------------------------------
g. Find all products (product codes) where a download preceded a purchase. Avoid duplicates in the result.
Old schema:
select distinct product_code from product p where
p.product_id in
(select d.product_id from download d, invoice i, lineitem l
      where d.product_id = l.product_id and
            l.invoice_id = i.invoice_id and
            d.download_date < i.invoice_date);

 

or with a single big join:

select distinct p.product_code from download d, invoice i, lineitem l, product p
      where d.product_id = l.product_id and p.product_id = l.product_id and
            l.invoice_id = i.invoice_id and
            d.download_date < i.invoice_date;


 PRODUCT_CODE
 ------------
 jr01   

 

with a single big join:

select distinct p.product_code from download d, track t, invoice i, lineitem l, product p
      where d.track_id = t.track_id and t.product_id = l.product_id and

p.product_id = l.product_id and
      l.invoice_id = i.invoice_id and
      d.download_date < i.invoice_date;


--------------------------------------
h. Find the downloaded file (.mp3 filename) that was downloaded the highest number of times.

This is probably the hardest one. One way: use the having clause to pick out the max of the count(*) values, but not using max(count(*)) since that is not in SQL92, the main standard in use. Note that “>= all” can find a max for us. Alternatively, use an outer query on the results of the basic query.


select t.sample_filename, count(*) as num_dls from download d, track t

   where t.track_id = d.track_id
group by t.track_id, t.sample_filename
having count(*) >= all  
  (select count(*) from download d, track t

   where t.track_id = d.track_id
   group by t.track_id);

 

SAMPLE_FILENAME

---------------------------

   NUM_DLS

----------

filter.mp3

         2

 

so_long.mp3

         2

--------------------------------------
i. Report on the number of downloads (total over all files for that CD) by CD product code, since August 1, 2009.

select p.product_code, count(d.download_id) as totdown from download d, product p, track t
where t.product_id=p.product_id and d.track_id = t.track_id and
d.download_date >= (timestamp '2009-08-01 00:00:00')
group by p.product_code;

 PRODUCT_CODE TOTDOWN
 ------------ -------
 jr01               3

 

Note: this result does not list CDs having 0 downloads. However, it is an acceptable answer.

 

To get the 0 counts, we could convert the above query into a subquery to evaluate

For each row in the products table:

select p.product_code, (select count(d.download_id) as totdown from download d, track t
where t.product_id=p.product_id and d.track_id = t.track_id and
d.download_date >= (timestamp '2009-08-01 00:00:00')) totdown from product p;

PRODUCT_CODE    TOTDOWN

------------ ----------

8601                0

jr01                3

pf01                0

pf02                0

 

---------------------------------

6 (Graded separately).  A Little start on music1 (pa1a). See the project 1 spec. Write a program Register.java (in src of your music1 project) to insert a new user, directly, no User object, no "DAO" yet.  This brute force starter program follows the idea of using all the pieces of the needed technology as soon as possible.  Elegance can wait a bit.  Show the source in your hw paper. Feel free to copy code from JdbcCheckup.java and SystemTest.java.

 

// All code basically copied from SystemTest.java, JDBCCheckup.java, and DBDAO.java

 

package cs636.music.presentation;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import cs636.music.config.MusicSystemConfig;

/**
 *
 *         This class inserts a new user into the database.
 */

public class Register {

       private static Connection connection;

       public static void main(String[] args) {
              String dbUrl = null;
              String usr = null;
              String pw = null;
              if (args.length == 0) {
                     // don't change defaults from null, use hsqldb with defaults
              } else if (args.length == 3) {
                     dbUrl = args[0];
                     usr = args[1];
                     pw = args[2];
              } else {
                     System.out
                                  .println("usage:java <dbURL> <user> <passwd>");
                     System.exit(1);
              }
              try {
                     System.out.println("opening DB");
                     connection = DriverManager.getConnection(dbUrl, usr, pw);         
              } catch(Exception e) {
                     System.err.println("Error in attempt to open database: " );
                     System.err.println(MusicSystemConfig.exceptionReport(e));
                     System.exit(2);
              }
              try {
                     System.out.println("registering user.");
                     registerUser(connection);
                     System.out.println("Run complete, exiting");
              } catch (Exception e) {
                     System.err.println("Error in attempt to register user: " );
                     System.err.println(MusicSystemConfig.exceptionReport(e));
              }
       }
      
      
       private static void registerUser(Connection connection) throws SQLException {
              // Create a statement
              Statement stmt = connection.createStatement();
              ResultSet rset = null;
              int userId = 4; // after standard scripts are run, the DB will often have three users, so start with 4.
                                         // in order not to cause an error by attempting to insert a record with a duplicate primary key.
              try {
                     // The following database actions are handled normally,
                     // i.e., if they fail they will throw a SQLException
                     // and terminate the execution of this method
                     // with execution of the finally clause
                     // Try to find out the next valid user id from the music system table.
                     rset = stmt.executeQuery("select user_id from music_sys_tab");
                     while (rset.next())
                           userId = rset.getInt(1);
             
                     stmt.execute("insert into site_user (user_id, firstname, lastname, email_address) values (" + userId + ", 'Jane', 'Dough',"
                                  + "'jdough@anonymous.net')");
                    
                     // ideally should change the user_id for the next user, need to add that to future code.
              } finally {   // Note: try without catch: let the caller handle
                                    // any exceptions of the "normal" db actions.
                     stmt.close(); // clean up statement resources, incl. rset
              }
   
       }
 
}