db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r500297 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe: client/Operations.java direct/Standard.java run/Schema.java schema/delivery.sql test/OperationsTester.java util/OERandom.java
Date Fri, 26 Jan 2007 18:26:31 GMT
Author: djd
Date: Fri Jan 26 10:26:30 2007
New Revision: 500297

URL: http://svn.apache.org/viewvc?view=rev&rev=500297
Log:
DERBY-2094 (partial) Add the delivery transactions and a database based queuing system and
results file for
those transactions.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql
  (with props)
Modified:
    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/run/Schema.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/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=500297&r1=500296&r2=500297
==============================================================================
--- 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
Fri Jan 26 10:26:30 2007
@@ -269,11 +269,9 @@
      * Execute a single delivery from the FIFO queue. Processing a delivery
      * request is described in clause 2.7.4.
      * 
-     * @return Number of seconds the delivery request was queued for. If no
-     *         request was queued then -1 is returned.
-     * @throws Exception
+      * @throws Exception
      */
-    public int delivery() throws Exception;
+    public void delivery() throws Exception;
     
     /**
      * Release any resources.

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=500297&r1=500296&r2=500297
==============================================================================
--- 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
Fri Jan 26 10:26:30 2007
@@ -19,10 +19,13 @@
  */
 package org.apache.derbyTesting.system.oe.direct;
 
+import java.lang.reflect.Field;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Types;
 import java.util.ArrayList;
 import java.util.List;
 
@@ -100,7 +103,7 @@
         
         int isolation = conn.getTransactionIsolation();
 
-        int level;
+        int lowStock;
         try {
 
             try {
@@ -127,7 +130,7 @@
 
                 rs = sl2.executeQuery();
                 rs.next();
-                level = rs.getInt("LOW_STOCK");
+                lowStock = rs.getInt("LOW_STOCK");
                 reset(sl2);
 
                 conn.commit();
@@ -142,7 +145,7 @@
         }
 
         if (display != null)
-            display.displayStockLevel(displayData, w, d, threshold, level);
+            display.displayStockLevel(displayData, w, d, threshold, lowStock);
     }
     
     /*
@@ -567,45 +570,200 @@
         // TODO Auto-generated method stub
 
     }
+    
+    private PreparedStatement sdSchedule;
+    
     public void setupScheduleDelivery() throws Exception {
-        // TODO Auto-generated method stub
-
+        sdSchedule = prepare(
+           "INSERT INTO DELIVERY_REQUEST(DR_W_ID, DR_CARRIER_ID, DR_STATE) " +
+           "VALUES(?, ?, 'Q')");
     }
+    
+    /**
+     * Schedule a delivery using the database as the queuing
+     * mechanism and the results file.
+     * See delivery.sql.
+     */
     public void scheduleDelivery(Display display, Object displayData, short w,
             short carrier) throws Exception {
-        // TODO Auto-generated method stub
+        int isolation = conn.getTransactionIsolation(); 
+        try {
 
+            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
+            
+            sdSchedule.setShort(1, w);
+            sdSchedule.setShort(2, carrier);
+            sdSchedule.executeUpdate();
+            reset(sdSchedule);
+            conn.commit();
+        } finally {
+            conn.setTransactionIsolation(isolation);
+        }
+        
+        if (display != null)
+            display.displayScheduleDelivery(displayData, w, carrier);
     }
     
+    private PreparedStatement dlFindOldestRequest;
+    private PreparedStatement dlSetRequestState;
+    private PreparedStatement dlFindOrderToDeliver;
+    private PreparedStatement dlDeleteNewOrder;
+    private PreparedStatement dlSetOrderCarrier;
+    private PreparedStatement dlSetOrderlineDate;
+    private PreparedStatement dlUpdateCustomer;
+    private PreparedStatement dlRecordDelivery;
+    private PreparedStatement dlCompleteDelivery;
+    
+    
     public void setupDelivery() throws Exception {
-        // TODO Auto-generated method stub
+
+        dlFindOldestRequest = prepare(
+            "SELECT DR_ID, DR_W_ID, DR_CARRIER_ID FROM DELIVERY_REQUEST " +
+            "WHERE DR_STATE = 'Q' ORDER BY DR_QUEUED");
+        dlFindOldestRequest.setMaxRows(1);
+        
+        dlSetRequestState = prepare(
+            "UPDATE DELIVERY_REQUEST SET DR_STATE = ? " +
+            "WHERE DR_ID = ?");
+        dlCompleteDelivery = prepare(
+            "UPDATE DELIVERY_REQUEST SET DR_STATE = 'C', DR_COMPLETED = CURRENT TIMESTAMP
" +
+            "WHERE DR_ID = ?");
+        
+        dlFindOrderToDeliver = prepare(
+            "SELECT MIN(NO_O_ID) AS ORDER_TO_DELIVER FROM NEWORDERS " +
+            "WHERE NO_W_ID = ? AND NO_D_ID = ?");
+        
+        dlDeleteNewOrder = prepare(
+            "DELETE FROM NEWORDERS WHERE NO_W_ID = ? AND NO_D_ID = ? AND NO_O_ID = ?");
+        
+        dlSetOrderCarrier = prepare(
+            "UPDATE ORDERS SET O_CARRIER_ID = ? " +
+            "WHERE O_W_ID = ? AND O_D_ID = ? AND O_ID = ?");
+        
+        dlSetOrderlineDate = prepare(
+            "UPDATE ORDERLINE SET OL_DELIVERY_D = CURRENT TIMESTAMP " +
+            "WHERE OL_W_ID = ? AND OL_D_ID = ? AND OL_O_ID = ?");
+        
+        
+        dlUpdateCustomer = prepare(
+            "UPDATE CUSTOMER SET " +
+            "C_BALANCE = (SELECT SUM(OL_AMOUNT) FROM ORDERLINE " +
+                          "WHERE OL_W_ID = ? AND OL_D_ID = ? AND OL_O_ID = ?), " +
+            "C_DELIVERY_CNT = C_DELIVERY_CNT + 1 " +
+            "WHERE C_W_ID = ? AND C_D_ID = ? AND " +
+            "C_ID = (SELECT O_C_ID FROM ORDERS " +
+                    "WHERE O_W_ID = ? AND O_D_ID = ? AND O_ID = ?)");
+        
+        dlRecordDelivery = prepare(
+            "INSERT INTO DELIVERY_ORDERS(DO_DR_ID, DO_D_ID, DO_O_ID) " +
+            "VALUES (?, ?, ?)");
 
     }
-    public int delivery() throws Exception {
-        // TODO Auto-generated method stub
-        return 0;
-    }
+    
 
-    public void close() throws SQLException {
+    public void delivery() throws Exception {
         
-        close(sl1);
-        close(sl2);
+        // Find the most oldest queued order (FIFO)
+        ResultSet rs = dlFindOldestRequest.executeQuery();
+        rs.next();
+        int request = rs.getInt("DR_ID");
+        short w = rs.getShort("DR_W_ID");
+        short carrier = rs.getShort("DR_CARRIER_ID");
+        reset(dlFindOldestRequest);
+        
+        // Mark it as in progress
+        dlSetRequestState.setString(1, "I");
+        dlSetRequestState.setInt(2, request);
+        dlSetRequestState.executeUpdate();
+        reset(dlSetRequestState);
         
-        close(osCustomerById);
-        close(osCustomerByName);
-        close(osLastOrderNumber);
-        close(osOrderDetails);
-        close(osOrderLineItems);
-        
-        close(pyCustomerPayment);
-        close(pyCustomerInfoId);
-        close(pyCustomerUpdateBadCredit);
-        close(pyDistrictUpdate);
-        close(pyDistrictInfo);
-        close(pyWarehouseUpdate);
-        close(pyWarehouseInfo);
-        close(pyHistory);
+        conn.commit();
+        
+        // This parameter remains invariant over
+        // the batch we will insert.
+        dlRecordDelivery.setInt(1, request);
         
+        // Process one row per-district for this warehouse
+        for (short d = 1; d <= 10; d++)
+        {
+            dlRecordDelivery.setShort(2, d);
+
+            // Get the oldest NEWORDERS for this district
+            dlFindOrderToDeliver.setShort(1, w);
+            dlFindOrderToDeliver.setShort(2, d);
+            rs = dlFindOrderToDeliver.executeQuery();
+            rs.next();
+            int order = rs.getInt("ORDER_TO_DELIVER");
+            if (rs.wasNull()) {
+                // No orders to deliver
+                dlRecordDelivery.setNull(3, Types.INTEGER);
+                dlRecordDelivery.addBatch();
+            }
+            reset(dlFindOrderToDeliver);
+            
+            // Delete the NEWORDERS row
+            dlDeleteNewOrder.setShort(1, w);
+            dlDeleteNewOrder.setShort(2, d);
+            dlDeleteNewOrder.setInt(3, order);
+            dlDeleteNewOrder.executeUpdate();
+            reset(dlDeleteNewOrder);
+            
+            // Set the carrier in ORDERS
+            dlSetOrderCarrier.setShort(1, carrier);
+            dlSetOrderCarrier.setShort(2, w);
+            dlSetOrderCarrier.setShort(3, d);
+            dlSetOrderCarrier.setInt(4, order);
+            dlSetOrderCarrier.executeUpdate();
+            reset(dlSetOrderCarrier);
+            
+            // Update ORDERLINE with the delivery date
+            dlSetOrderlineDate.setShort(1, w);
+            dlSetOrderlineDate.setShort(2, d);
+            dlSetOrderlineDate.setInt(3, order);
+            dlSetOrderlineDate.executeUpdate();
+            reset(dlSetOrderlineDate);
+            
+            dlUpdateCustomer.setShort(1, w);
+            dlUpdateCustomer.setShort(2, d);
+            dlUpdateCustomer.setInt(3, order);
+            dlUpdateCustomer.setShort(4, w);
+            dlUpdateCustomer.setShort(5, d);
+            dlUpdateCustomer.setShort(6, w);
+            dlUpdateCustomer.setShort(7, d);
+            dlUpdateCustomer.setInt(8, order);
+            dlUpdateCustomer.executeUpdate();
+            reset(dlUpdateCustomer);
+                      
+            conn.commit();
+            
+            dlRecordDelivery.setInt(3, order);
+            dlRecordDelivery.addBatch();
+        }
+        
+        // Record the delivery including the timestamp
+        // 90% are meant to complete within 80 seconds
+        // of them being queued.
+        dlRecordDelivery.executeBatch();
+        reset(dlRecordDelivery);
+        dlCompleteDelivery.setInt(1, request);
+        dlCompleteDelivery.executeUpdate();
+        reset(dlCompleteDelivery);
+        conn.commit();
+        
+    }
+
+    public void close() throws Exception {
+        
+        // Close any instance field that is a PreparedStatement
+        Field[] fields = getClass().getDeclaredFields();
+        for (int i = 0; i < fields.length; i++) {
+            Field f = fields[i];
+                       
+            if (PreparedStatement.class.isAssignableFrom(f.getType()))
+            {
+                close((PreparedStatement) f.get(this));
+            }
+        }
     }
     
     /**

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/run/Schema.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/run/Schema.java?view=diff&rev=500297&r1=500296&r2=500297
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/run/Schema.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/run/Schema.java Fri
Jan 26 10:26:30 2007
@@ -63,6 +63,7 @@
         // Create Schema
         suite.addTest(new Schema("schema.sql"));
         suite.addTest(new Schema("dataroutines.sql"));
+        suite.addTest(new Schema("delivery.sql"));
     }
 
     /**

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql?view=auto&rev=500297
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql
Fri Jan 26 10:26:30 2007
@@ -0,0 +1,58 @@
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements.  See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License.  You may obtain a copy of the License at
+--
+--    http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+;
+
+-- Routines related to the delivery transaction
+-- Section 2.7
+--
+-- OrderEntry (OE) uses the database as the queueing
+-- mechanism and the "results file"
+-- described by the TPC-C specification in section 2.7.
+-- This exceeds the requirements of the specification
+-- (no ACID required) but since the focus of OE is to
+-- test Derby, it seems additional work against the
+-- database is a good thing. It also provides a consistent
+-- and standard way to access the data, including from clients.
+;
+
+-- Requests are queued in this table.
+-- DR_STATE Q-queued, Pin progress, C-complete, E-error
+;
+CREATE TABLE DELIVERY_REQUEST
+(
+   DR_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+   DR_W_ID SMALLINT NOT NULL,
+   DR_CARRIER_ID SMALLINT NOT NULL,
+   DR_QUEUED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+   DR_COMPLETED TIMESTAMP,
+   DR_STATE  CHAR(1)
+     CONSTRAINT DR_STATE_CHECK CHECK (DR_STATE IN ('Q', 'I', 'C', 'E'))
+);
+
+-- Index to allow lookup of the oldest queued order.
+;
+CREATE INDEX DR_LOOKUP ON DELIVERY_REQUEST(DR_STATE, DR_QUEUED);
+
+-- Record of orders delivered.
+-- A NULL DL_O_ID means that no orders for that district could be delivered.
+;
+CREATE TABLE DELIVERY_ORDERS
+(
+   DO_DR_ID INTEGER NOT NULL CONSTRAINT DO_FK REFERENCES DELIVERY_REQUEST(DR_ID),
+   DO_D_ID SMALLINT NOT NULL,
+   DO_O_ID INTEGER
+);
+
+    
\ No newline at end of file

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/schema/delivery.sql
------------------------------------------------------------------------------
    svn:eol-style = native

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=500297&r1=500296&r2=500297
==============================================================================
--- 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
Fri Jan 26 10:26:30 2007
@@ -153,10 +153,16 @@
     public void testScheduleDelivery() throws Exception
     {
         ops.setupScheduleDelivery();
+        for (int i = 0; i < 50; i++)
+            ops.scheduleDelivery(this, null, w, rand.carrier());
     }
     public void testDelivery() throws Exception
     {
         ops.setupDelivery();
+        // Ensure there are some schedule deliveries
+        testScheduleDelivery();
+        for (int i = 0; i < 50; i++)
+            ops.delivery();
     }
 
     public void displayStockLevel(Object displayData, short w, short d, int threshold, int
lowStock) throws Exception {

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=500297&r1=500296&r2=500297
==============================================================================
--- 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
Fri Jan 26 10:26:30 2007
@@ -115,6 +115,15 @@
     {
         return (short) randomInt(1, 10);
     }
+    
+    /**
+     * Return a random carrier [1..10]
+     * @return
+     */
+    public short carrier()
+    {
+        return (short) randomInt(1, 10);
+    }
 
     /**
      * Return a random threshold for the stock level [10..20]



Mime
View raw message