Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 26427 invoked from network); 24 Jan 2007 23:44:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 24 Jan 2007 23:44:33 -0000 Received: (qmail 5278 invoked by uid 500); 24 Jan 2007 23:44:39 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 5254 invoked by uid 500); 24 Jan 2007 23:44:39 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 5235 invoked by uid 99); 24 Jan 2007 23:44:39 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 24 Jan 2007 15:44:39 -0800 X-ASF-Spam-Status: No, hits=-9.4 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from [140.211.11.3] (HELO eris.apache.org) (140.211.11.3) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 24 Jan 2007 15:44:31 -0800 Received: by eris.apache.org (Postfix, from userid 65534) id 49E6D1A981A; Wed, 24 Jan 2007 15:44:11 -0800 (PST) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit 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 -0000 To: derby-commits@db.apache.org From: djd@apache.org X-Mailer: svnmailer-1.1.0 Message-Id: <20070124234411.49E6D1A981A@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org 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) */