db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r499618 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe: client/ direct/ load/ model/ test/ util/
Date Wed, 24 Jan 2007 23:44:10 GMT
Author: djd
Date: Wed Jan 24 15:44:09 2007
New Revision: 499618

URL: http://svn.apache.org/viewvc?view=rev&rev=499618
Log:
DERBY-2094 (partial) Add the order status business transaction implementation for oe.direct.Standard.
This implements the transaction using client side JBDC. Now that populating the database is
supported
run some test orders status and Stock level transactions in the OETest. Need more validation
on the
results of the transactions.

Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Display.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Load.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Operations.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/Standard.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/model/Order.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OETest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OperationsTester.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/util/OERandom.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Display.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Display.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Display.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Display.java
Wed Jan 24 15:44:09 2007
@@ -22,6 +22,7 @@
 import org.apache.derbyTesting.system.oe.model.Customer;
 import org.apache.derbyTesting.system.oe.model.District;
 import org.apache.derbyTesting.system.oe.model.Order;
+import org.apache.derbyTesting.system.oe.model.OrderLine;
 import org.apache.derbyTesting.system.oe.model.Warehouse;
 
 /**
@@ -74,10 +75,11 @@
      *            Customer for order
      * @param order
      *            Order fetched.
+     * @param lineItems Items for the order
      * @throws Exception
      */
     public void displayOrderStatus(Object displayData, boolean byName,
-            Customer customer, Order order) throws Exception;
+            Customer customer, Order order, OrderLine[] lineItems) throws Exception;
               
     /**
      * Display the result of a payment. Payment terminal i/o

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Load.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Load.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Load.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Load.java Wed
Jan 24 15:44:09 2007
@@ -64,6 +64,16 @@
 
     public static final int NEWORDERS_BREAKPOINT = 
         (ORDERS_COUNT_W - NEWORDERS_COUNT_W)/ DISTRICT_COUNT_W;
+    
+    
+    /**
+     * Set the seed for the random number generator used to
+     * populate the data. Useful for testing to ensure consistent
+     * repeatable runs. If not set, defaults a value based upon current time.
+     * Must be called before setupLoad to have an effect.
+     * @param seed
+     */
+    public void setSeed(long seed);
 
     /**
      * Perform the necessary setup before database population.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Operations.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Operations.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Operations.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/client/Operations.java
Wed Jan 24 15:44:09 2007
@@ -274,5 +274,11 @@
      * @throws Exception
      */
     public int delivery() throws Exception;
+    
+    /**
+     * Release any resources.
+     * 
+     */
+    public void close() throws Exception;
 
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/Standard.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/Standard.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/Standard.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/Standard.java
Wed Jan 24 15:44:09 2007
@@ -23,9 +23,14 @@
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.List;
 
 import org.apache.derbyTesting.system.oe.client.Display;
 import org.apache.derbyTesting.system.oe.client.Operations;
+import org.apache.derbyTesting.system.oe.model.Customer;
+import org.apache.derbyTesting.system.oe.model.Order;
+import org.apache.derbyTesting.system.oe.model.OrderLine;
 
 /**
  * Implement the transactions following the TPC-C specification
@@ -81,7 +86,7 @@
             "SELECT D_NEXT_O_ID FROM DISTRICT WHERE D_W_ID = ? AND D_ID = ?");
         
         sl2 = prepare(
-            "SELECT COUNT(DISTINCT(S_I_ID)) FROM ORDERLINE, STOCK " +
+            "SELECT COUNT(DISTINCT(S_I_ID)) AS LOW_STOCK FROM ORDERLINE, STOCK " +
             "WHERE OL_W_ID = ? AND OL_D_ID = ? " +
             "AND OL_O_ID < ? AND OL_O_ID >= ? " +
             "AND S_W_ID = ? AND S_I_ID = OL_I_ID AND S_QUANTITY < ?");
@@ -111,7 +116,7 @@
                 ResultSet rs = sl1.executeQuery();
 
                 rs.next();
-                int nextOrder = rs.getInt(1);
+                int nextOrder = rs.getInt("D_NEXT_O_ID");
                 rs.close();
 
                 sl2.setInt(3, nextOrder);
@@ -119,7 +124,7 @@
 
                 rs = sl2.executeQuery();
                 rs.next();
-                level = rs.getInt(1);
+                level = rs.getInt("LOW_STOCK");
                 rs.close();
 
                 conn.commit();
@@ -140,21 +145,173 @@
     /*
      * Order Status transaction.
      */
+    
+    private PreparedStatement osCustomerById;
+    private PreparedStatement osLastOrderNumber;
+    private PreparedStatement osOrderDetails;
+    private PreparedStatement osOrderLineItems;
+    
+    private PreparedStatement osCustomerByName;
 
     public void setupOrderStatus() throws Exception {
-        // TODO Auto-generated method stub
+        osCustomerById = prepare(
+                "SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST " +
+                "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_ID = ?");
+        osLastOrderNumber = prepare(
+                "SELECT MAX(O_ID) AS LAST_ORDER FROM ORDERS " +
+                "WHERE O_W_ID = ? AND O_D_ID = ? AND O_C_ID = ?");
+        osOrderDetails = prepare(
+                "SELECT O_ENTRY_D, O_CARRIER_ID, O_OL_CNT " +
+                "FROM ORDERS WHERE O_W_ID = ? AND O_D_ID = ? AND O_ID = ?");
+        osOrderLineItems = prepare(
+                "SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, " +
+                "OL_DELIVERY_D FROM ORDERLINE " +
+                "WHERE OL_W_ID = ? AND OL_D_ID = ? AND OL_O_ID = ?");
+
+        osCustomerByName = prepare(
+                "SELECT C_ID, C_BALANCE, C_FIRST, C_MIDDLE " +
+                "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_LAST = ? " +
+                "ORDER BY C_FIRST");
 
     }
+    
+    /**
+     * Order status by customer last name.
+     * Based up the example SQL queries in appendix A.3
+     */
     public void orderStatus(Display display, Object displayData, short w,
             short d, String customerLast) throws Exception {
-        // TODO Auto-generated method stub
-
+        
+       
+        try {
+            osCustomerByName.setShort(1, w);
+            osCustomerByName.setShort(2, d);
+            osCustomerByName.setString(3, customerLast);
+            ResultSet rs = osCustomerByName.executeQuery();
+            int n = 0;
+            List list = new ArrayList();
+            while (rs.next())
+            {
+                Customer customer = new Customer();
+                customer.setWarehouse(w);
+                customer.setDistrict(d);
+                customer.setLast(customerLast);
+                
+                customer.setId(rs.getInt("C_ID"));
+                customer.setBalance(rs.getString("C_BALANCE"));
+                customer.setFirst(rs.getString("C_FIRST"));
+                customer.setMiddle(rs.getString("C_MIDDLE"));
+                
+                list.add(customer);
+            }
+            rs.close();
+            if (list.isEmpty())
+                throw new SQLException("Order Status by name - no matching customer "
+                        + customerLast);
+            
+            // Customer to use is midpoint (with round up) (see 2.6.2.2)
+            int mid = n/2;
+            if (n%2 == 1)
+                mid++;
+
+
+            Customer customer = (Customer) list.get(mid);
+            list = null;
+            
+            getOrderStatusForCustomer(display, displayData, true, customer);
+        } catch (SQLException e) {
+            conn.rollback();
+            throw e;
+        }
     }
 
+    /**
+     * Order status by customer identifier.
+     * Based up the example SQL queries in appendix A.3
+     */
     public void orderStatus(Display display, Object displayData, short w,
             short d, int c) throws Exception {
-        // TODO Auto-generated method stub
+        
+        Customer customer = new Customer();
+        customer.setWarehouse(w);
+        customer.setDistrict(d);
+        customer.setId(c);
+        
+        try {
+            // Get the customer information
+            osCustomerById.setShort(1, w);
+            osCustomerById.setShort(2, d);
+            osCustomerById.setInt(3, c);
+            ResultSet rs = osCustomerById.executeQuery();
+            rs.next();
+            customer.setBalance(rs.getString("C_BALANCE"));
+            customer.setFirst(rs.getString("C_FIRST"));
+            customer.setMiddle(rs.getString("C_MIDDLE"));
+            customer.setLast(rs.getString("C_LAST"));    
+            rs.close();
+
+            getOrderStatusForCustomer(display, displayData, false, customer);
+        } catch (SQLException e) {
+            conn.rollback();
+            throw e;
+        }
+    }
+    
+    /**
+     * Fetch the order details having obtained the customer information
+     * and display it.
+     */
+    private void getOrderStatusForCustomer(Display display, Object displayData,
+            boolean byName, Customer customer) throws Exception
+    {
+        Order order = new Order();
+        order.setWarehouse(customer.getWarehouse());
+        order.setDistrict(customer.getDistrict());
+        
+        // Find the most recent order number for this customer
+        osLastOrderNumber.setShort(1, customer.getWarehouse());
+        osLastOrderNumber.setShort(2, customer.getDistrict());
+        osLastOrderNumber.setInt(3, customer.getId());
+        ResultSet rs = osLastOrderNumber.executeQuery();
+        rs.next();
+        order.setId(rs.getInt("LAST_ORDER"));
+        rs.close();
+        
+        // Details for the order.
+        osOrderDetails.setShort(1, customer.getWarehouse());
+        osOrderDetails.setShort(2, customer.getDistrict());
+        osOrderDetails.setInt(3, order.getId());
+        rs = osOrderDetails.executeQuery();
+        rs.next();
+        order.setEntry_d(rs.getTimestamp("O_ENTRY_D"));
+        order.setCarrier_id((Integer) rs.getObject("O_CARRIER_ID"));
+        order.setOl_cnt(rs.getInt("O_OL_CNT"));
+        rs.close();
 
+        OrderLine[] lineItems = new OrderLine[order.getOl_cnt()];
+        
+        osOrderLineItems.setShort(1, order.getWarehouse());
+        osOrderLineItems.setShort(2, order.getDistrict());
+        osOrderLineItems.setInt(3, order.getId());
+        rs = osOrderLineItems.executeQuery();
+        int oli = 0;
+        while (rs.next())
+        {
+            OrderLine ol = new OrderLine();
+            ol.setI_id(rs.getInt("OL_I_ID"));
+            ol.setSupply_w_id(rs.getShort("OL_SUPPLY_W_ID"));
+            ol.setQuantity(rs.getShort("OL_QUANTITY"));
+            ol.setAmount(rs.getString("OL_AMOUNT"));
+            ol.setDelivery_d( rs.getTimestamp("OL_DELIVERY_D"));
+            
+            lineItems[oli++] = ol;
+        }
+        rs.close();
+        conn.commit();
+        
+        if (display != null)
+            display.displayOrderStatus(displayData,
+                    byName, customer, order, lineItems);
     }
 
     public void setupPayment() throws Exception {
@@ -202,5 +359,24 @@
     public int delivery() throws Exception {
         // TODO Auto-generated method stub
         return 0;
+    }
+
+    public void close() throws SQLException {
+        
+        close(sl1);
+        close(sl2);
+        
+        close(osCustomerById);
+        close(osCustomerByName);
+        close(osLastOrderNumber);
+        close(osOrderDetails);
+        close(osOrderLineItems);
+        
+    }
+    private static void close(PreparedStatement ps)
+       throws SQLException
+    {
+        if (ps != null)
+            ps.close();
     }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/load/SimpleInsert.java
Wed Jan 24 15:44:09 2007
@@ -39,6 +39,11 @@
      * warehouse scale factor, default to 1.
      */
     short scale = 1;
+    
+    /**
+     * Seed value for random number generator.
+     */
+    private long seed = System.currentTimeMillis();
 
     /**
      * Utility to generate random data per the TPC-C requirements
@@ -71,12 +76,12 @@
         s.execute("CREATE TABLE C(CLOAD INT, CLAST INT, CID INT, CITEM INT)");
         conn.commit();
 
-        random = new OERandom(-1, -1, -1);
+        random = new OERandom(-1, -1, -1, seed);
 
         // Section 2.1.6.1 of TPC-C spec
         int loadRandomFactor = random.randomInt(0, 255);
         s.execute("INSERT INTO C VALUES(" + loadRandomFactor + ", -1, -1, -1)");
-        random = new OERandom(loadRandomFactor, -1, -1);
+        random = new OERandom(loadRandomFactor, -1, -1, seed);
         conn.commit();
 
         this.scale = scale;
@@ -401,6 +406,10 @@
         psOL.close();
         psO.close();
         psNO.close();
+    }
+
+    public void setSeed(long seed) {
+        this.seed = seed;
     }
 
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/model/Order.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/model/Order.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/model/Order.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/model/Order.java Wed
Jan 24 15:44:09 2007
@@ -51,7 +51,7 @@
     private short warehouse;
     private int customer;
     private Timestamp entry_d;
-    private Short carrier_id;
+    private Integer carrier_id; // JDBC maps SMALLINT to java.lang.Integer
     private int ol_cnt;
     private boolean all_local;
 
@@ -61,10 +61,10 @@
     public void setAll_local(boolean all_local) {
         this.all_local = all_local;
     }
-    public Short getCarrier_id() {
+    public Integer getCarrier_id() {
         return carrier_id;
     }
-    public void setCarrier_id(Short carrier_id) {
+    public void setCarrier_id(Integer carrier_id) {
         this.carrier_id = carrier_id;
     }
     public int getCustomer() {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OETest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OETest.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OETest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OETest.java Wed
Jan 24 15:44:09 2007
@@ -78,6 +78,7 @@
     {
         OperationsTester tester = new OperationsTester(ops);
         tester.test();
+        ops.close();
     }
     
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OperationsTester.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OperationsTester.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OperationsTester.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/test/OperationsTester.java
Wed Jan 24 15:44:09 2007
@@ -19,7 +19,14 @@
  */
 package org.apache.derbyTesting.system.oe.test;
 
+import org.apache.derbyTesting.system.oe.client.Display;
 import org.apache.derbyTesting.system.oe.client.Operations;
+import org.apache.derbyTesting.system.oe.model.Customer;
+import org.apache.derbyTesting.system.oe.model.District;
+import org.apache.derbyTesting.system.oe.model.Order;
+import org.apache.derbyTesting.system.oe.model.OrderLine;
+import org.apache.derbyTesting.system.oe.model.Warehouse;
+import org.apache.derbyTesting.system.oe.util.OERandom;
 
 /**
  * Test an implementation of Operations.
@@ -27,12 +34,15 @@
  * code is added the implemetations of the transactions
  * will be added.
  */
-class OperationsTester  {
+class OperationsTester implements Display {
 
     private final Operations ops;
+    private final OERandom rand;
+    private final short w = 1;
     
     OperationsTester(Operations ops) {
         this.ops = ops;
+        this.rand = new OERandom(-1, -1, -1, 3458567);
     }
     
     void test() throws Exception
@@ -48,10 +58,44 @@
     private void testStockLevel() throws Exception
     {
         ops.setupStockLevel();
+        
+        // Check a null display is handled
+        ops.stockLevel(null, null,
+                w, rand.district(), rand.threshold());
+        
+        for (int i = 0; i < 20; i++)
+            ops.stockLevel(this, null,
+                    w, rand.district(), rand.threshold());
     }
+    
+    /**
+     * Execute a number of order-status transactions
+     * by name and identifier. Also check the implementation
+     * accepts a null display.
+     * @throws Exception
+     */
     private void testOrderStatus() throws Exception
     {
         ops.setupOrderStatus();
+        
+        // By identifier
+        ops.orderStatus(null, null,
+                w, rand.district(), rand.NURand1023());
+        for (int i = 0; i < 50; i++) {
+            ops.orderStatus(this, null,
+                    w, rand.district(), rand.NURand1023());
+        }
+        
+        // By name 
+        ops.orderStatus(null, null,
+                w, rand.district(), rand.randomCLast());
+        for (int i = 0; i < 50; i++)
+        {
+            ops.orderStatus(this, null,
+                    w, rand.district(), rand.randomCLast());
+            
+        }
+        //
     }
     private void testPayment() throws Exception
     {
@@ -68,5 +112,29 @@
     private void testDelivery() throws Exception
     {
         ops.setupDelivery();
+    }
+
+    public void displayStockLevel(Object displayData, short w, short d, int threshold, int
level) throws Exception {
+        // TODO: Check expected data is set.  
+    }
+
+    public void displayOrderStatus(Object displayData, boolean byName, Customer customer,
Order order, OrderLine[] lineItems) throws Exception {
+        // TODO: Check expected data is set.   
+        
+    }
+
+    public void displayPayment(Object displayData, String amount, boolean byName, Warehouse
warehouse, District district, Customer customer) throws Exception {
+        // TODO Auto-generated method stub
+        
+    }
+
+    public void displayNewOrder(Object displayData, Warehouse warehouse, District district,
Customer customer, Order order) throws Exception {
+        // TODO Auto-generated method stub
+        
+    }
+
+    public void displayScheduleDelivery(Object displayData, short w, short carrier) throws
Exception {
+        // TODO Auto-generated method stub
+        
     }
 }

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/util/OERandom.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/util/OERandom.java?view=diff&rev=499618&r1=499617&r2=499618
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/util/OERandom.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/util/OERandom.java
Wed Jan 24 15:44:09 2007
@@ -97,7 +97,25 @@
 
         return ((int) rrand) + start;
     }
+    
+    /**
+     * Return a random district [1..10]
+     * @return
+     */
+    public short district()
+    {
+        return (short) randomInt(1, 10);
+    }
 
+    /**
+     * Return a random threshold for the stock level [10..20]
+     * @return
+     */
+    public int threshold()
+    {
+        return randomInt(10, 20);
+    }
+    
     /**
      * tpcc 4.3.2.2 (random a string)
      */



Mime
View raw message