db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r595387 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe: direct/SimpleNonStandardOperations.java direct/Standard.java direct/StatementHelper.java test/OETest.java
Date Thu, 15 Nov 2007 18:25:07 GMT
Author: djd
Date: Thu Nov 15 10:24:47 2007
New Revision: 595387

URL: http://svn.apache.org/viewvc?rev=595387&view=rev
Log:
Add a couple of simple primary key tests that work in the oe (tpc-c) framework. These transactions
are not part of the specification but allow testing of basic functionality against an order-entry
database

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/SimpleNonStandardOperations.java
  (with props)
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/StatementHelper.java
  (with props)
Modified:
    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/test/OETest.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/SimpleNonStandardOperations.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/SimpleNonStandardOperations.java?rev=595387&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/SimpleNonStandardOperations.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/SimpleNonStandardOperations.java
Thu Nov 15 10:24:47 2007
@@ -0,0 +1,190 @@
+/*
+ *
+ * Derby - Class org.apache.derbyTesting.system.oe.direct.SimpleNonStandardOperations
+ *
+ * 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.
+ */
+package org.apache.derbyTesting.system.oe.direct;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
+import org.apache.derbyTesting.system.oe.client.Operations;
+import org.apache.derbyTesting.system.oe.client.Submitter;
+import org.apache.derbyTesting.system.oe.model.Customer;
+import org.apache.derbyTesting.system.oe.util.OERandom;
+
+/**
+ * Collection of simple transactions that can be executed against
+ * an order-entry database. These are not part of any standard
+ * TPC-C specification but are useful for running specific
+ * performance tests against Derby.
+ * 
+ * Since they are not standard operations there is no
+ * ability to display the information. Any data selected
+ * by a query is always fetched by processing all the
+ * rows and all the columns using getXXX.
+ *
+ */
+public class SimpleNonStandardOperations extends StatementHelper {
+    
+    /*
+     * Reusable objects
+     */
+    private final Customer customer = new Customer();
+    
+    private final OERandom rand;
+
+    public SimpleNonStandardOperations(Connection conn,
+            int isolation) throws SQLException
+    {
+        super(conn, false, isolation);
+        rand = Submitter.getRuntimeRandom(conn);
+    }
+    
+    /**
+     * Return an SimpleNonStandardOperations implementation based upon
+     * SimpleNonStandardOperations with a single difference.
+     * In this implementation the reset() executed after each
+     * PreparedStatement execute
+     * does nothing. Sees if there is any performance impact
+     * of explicitly closing each ResultSet and clearing the
+     * parameters.
+     * <P>
+     * Each ResultSet will be closed implicitly either at commit
+     * time or at the next execution of the same PreparedStatement object.
+     */
+    public static SimpleNonStandardOperations noReset(final Connection conn,
+            final int isolation)
+        throws SQLException
+    {
+        return new SimpleNonStandardOperations(conn, isolation) {
+            protected void reset(PreparedStatement ps) {}
+        };
+    }
+    
+    /**
+     * Execute customerInquiry() with random parameters.
+     * @throws SQLException 
+     *
+     */
+    public void customerInquiry(int scale) throws SQLException
+    {
+        customerInquiry((short) rand.randomInt(1, scale),
+                (short) rand.district(), rand.NURand1023());
+    }
+    
+    /**
+     * Lookup a customer's information (name, address, balance)
+     * fetching it by the identifier.
+     * <BR>
+     * Primary key lookup against the CUSTOMER table (which
+     * of course can be arbitrarily large depending on the
+     * scale of the database. The cardinality of the CUSTOMER
+     * is 30,000 rows per warehouse, for example with a 20
+     * warehouse system this test would perform a primary
+     * key lookup against 600,000 rows.
+     * 
+     * @param w Warehouse for customer
+     * @param d District for customer
+     * @param c Customer identifier
+     */
+    public void customerInquiry(short w, short d, int c)
+       throws SQLException
+    {
+        
+        PreparedStatement customerInquiry = prepareStatement(
+                "SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST, " +
+                "C_STREET_1, C_STREET_2, C_CITY, C_STATE, C_ZIP, " +
+                "C_PHONE " +
+                "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_ID = ?");
+        
+            customerInquiry.setShort(1, w);
+            customerInquiry.setShort(2, d);
+            customerInquiry.setInt(3, c);
+            ResultSet rs = customerInquiry.executeQuery();
+            rs.next();
+            
+            customer.clear();
+            customer.setBalance(rs.getString("C_BALANCE"));
+            customer.setFirst(rs.getString("C_FIRST"));
+            customer.setMiddle(rs.getString("C_MIDDLE"));
+            customer.setLast(rs.getString("C_LAST"));
+            
+            customer.setAddress(getAddress(rs, "C_STREET_1"));
+            
+            customer.setPhone(rs.getString("C_PHONE"));
+            
+            reset(customerInquiry);
+            conn.commit();
+    }
+    
+    /**
+     * Execute customerAddressChange() with random parameters.
+     * @throws SQLException 
+     *
+     */
+    public void customerAddressChange(int scale) throws SQLException
+    {
+        customerAddressChange((short) rand.randomInt(1, scale),
+                (short) rand.district(), rand.NURand1023());
+    }
+    
+    /**
+     * Update a customers address with a new random value.
+     * Update of a single row through a primary key.
+     * <BR>
+     * Primary key update against the CUSTOMER table (which
+     * of course can be arbitrarily large depending on the
+     * scale of the database. The cardinality of the CUSTOMER
+     * is 30,000 rows per warehouse, for example with a 20
+     * warehouse system this test would perform a primary
+     * key lookup against 600,000 rows.
+     * 
+     * @param w Warehouse for customer
+     * @param d District for customer
+     * @param c Customer identifier
+ 
+     */
+    public void customerAddressChange(short w, short d, int c)
+    throws SQLException
+    {
+        PreparedStatement customerAddressChange = prepareStatement(
+                "UPDATE CUSTOMER " +
+                "SET C_STREET_1 = ?, C_STREET_2 = ?, " +
+                "C_CITY = ?, C_STATE = ?, C_ZIP = ? " +
+                "WHERE C_W_ID = ? AND C_D_ID = ? AND C_ID = ?");
+        
+        customerAddressChange.setString(1, rand.randomAString10_20()); // c_street_1
+        customerAddressChange.setString(2, rand.randomAString10_20()); // c_street_2
+        customerAddressChange.setString(3, rand.randomAString10_20()); // c_city
+        customerAddressChange.setString(4, rand.randomState()); // c_state
+        customerAddressChange.setString(5, rand.randomZIP()); // c_zip
+        
+        customerAddressChange.setShort(6, w);
+        customerAddressChange.setShort(7, d);
+        customerAddressChange.setInt(8, c);
+        
+        customerAddressChange.executeUpdate();
+        
+        reset(customerAddressChange);
+        
+        conn.commit();
+          
+    }
+}

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

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?rev=595387&r1=595386&r2=595387&view=diff
==============================================================================
--- 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
Thu Nov 15 10:24:47 2007
@@ -26,14 +26,10 @@
 import java.sql.SQLException;
 import java.sql.Types;
 import java.util.ArrayList;
-import java.util.IdentityHashMap;
-import java.util.Iterator;
 import java.util.List;
-import java.util.Map;
 
 import org.apache.derbyTesting.system.oe.client.Display;
 import org.apache.derbyTesting.system.oe.client.Operations;
-import org.apache.derbyTesting.system.oe.model.Address;
 import org.apache.derbyTesting.system.oe.model.Customer;
 import org.apache.derbyTesting.system.oe.model.District;
 import org.apache.derbyTesting.system.oe.model.Order;
@@ -59,14 +55,11 @@
  * too much since the purpose of the framework
  * is to test Derby's performance.
  */
-public class Standard implements Operations {
-    
-    private final Connection conn;
+public class Standard extends StatementHelper implements Operations {
     
     /*
      * Objects for re-use within the transactions
      */
-    
     private final Customer customer = new Customer();
     
     private final Warehouse warehouse = new Warehouse();
@@ -85,9 +78,7 @@
      */
     public Standard(Connection conn) throws SQLException
     {
-        this.conn = conn;
-        conn.setAutoCommit(false);
-        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
+        super(conn, false, Connection.TRANSACTION_SERIALIZABLE);
     }
     
     /**
@@ -110,41 +101,6 @@
     }
     
     /**
-     * Map of SQL text to its PreparedStatement.
-     * This allows the SQL text to be in-line with
-     * code that sets the parameters and looks at 
-     * the results. Map is on the identity of the SQL
-     * string which assumes they are all constants
-     * (and hence interned). Assumption is that this
-     * will provide for a quicker lookup than by text
-     * since the statements can be many characters.
-     * 
-     * May also allow easier sharing with other implementations
-     * such as a Java procedure which could have a different
-     * prepareStatement method.
-     */
-    private Map statements = new IdentityHashMap();
-    
-    /**
-     * Prepare a statement, looking in the map first.
-     * If the statement does not exist in the map then
-     * it is prepared and put into the map for future use.
-     */
-    PreparedStatement prepareStatement(String sql) throws SQLException {
-        PreparedStatement ps = (PreparedStatement) statements.get(sql);
-        if (ps != null)
-            return ps;
-        
-        // Prepare all statements as forward-only, read-only, close at commit.
-        ps = conn.prepareStatement(sql,
-                ResultSet.TYPE_FORWARD_ONLY,
-                ResultSet.CONCUR_READ_ONLY,
-                ResultSet.CLOSE_CURSORS_AT_COMMIT);
-        statements.put(sql, ps);
-        return ps;
-    }
-    
-    /**
      *  Stock Level transaction.
      *  Described in section 2.8.2.
      *  SQL based upon sample prgram in appendix A.5.
@@ -953,49 +909,6 @@
         
     }
 
-    public void close() throws Exception {
-              
-        for (Iterator i = statements.keySet().iterator(); i.hasNext(); )
-        {
-            String sql = (String) i.next();
-            PreparedStatement ps = (PreparedStatement) statements.get(sql);
-            ps.close();
-        }
-    }
-    
-    /**
-     * Reset a PreparedStatement. Closes its open ResultSet
-     * and clears the parameters. While clearing the parameters
-     * is not required since any future execution will override
-     * them, it is done here to reduce the chance of errors.
-     * E.g. using the wrong prepared statement for a operation
-     * or not setting all the parameters.
-     * It is assumed the prepared statement was just executed.
-     * @throws SQLException 
-     */
-    protected void reset(PreparedStatement ps) throws SQLException
-    {
-        ResultSet rs = ps.getResultSet();
-        if (rs != null)
-            rs.close();
-        ps.clearParameters();
-    }
-    
-    private Address getAddress(ResultSet rs, String firstColumnName) throws SQLException
-    {
-        Address address = new Address();
-        
-        int col = rs.findColumn(firstColumnName);
-        address.setStreet1(rs.getString(col++));
-        address.setStreet2(rs.getString(col++));
-        address.setCity(rs.getString(col++));
-        address.setState(rs.getString(col++));
-        address.setZip(rs.getString(col));
-        
-        return address;
-    }
-    
-    
     public void sortOrderItems(int[] items, short[] quantities, short[] supplyW) {
 
         OrderItem4Sort[] list = new OrderItem4Sort[items.length];

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/StatementHelper.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/StatementHelper.java?rev=595387&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/StatementHelper.java
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/oe/direct/StatementHelper.java
Thu Nov 15 10:24:47 2007
@@ -0,0 +1,124 @@
+/*
+ *
+ * Derby - Class org.apache.derbyTesting.system.oe.direct.StatementHelper
+ *
+ * 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.
+ */
+package org.apache.derbyTesting.system.oe.direct;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.IdentityHashMap;
+import java.util.Iterator;
+import java.util.Map;
+
+import org.apache.derbyTesting.system.oe.model.Address;
+
+/**
+ * Utility class for direct operations. Maintains the connection
+ * and a hash table of PreparedStatements to simplify management
+ * of PreparedStatement objects by the business transactions.
+ *
+ */
+class StatementHelper {
+
+    protected final Connection conn;
+    
+    StatementHelper(Connection conn, boolean autoCommit, int isolation) throws SQLException
+    {
+        this.conn = conn;
+        conn.setAutoCommit(autoCommit);
+        conn.setTransactionIsolation(isolation);
+    }
+    
+    /**
+     * Map of SQL text to its PreparedStatement.
+     * This allows the SQL text to be in-line with
+     * code that sets the parameters and looks at 
+     * the results. Map is on the identity of the SQL
+     * string which assumes they are all constants
+     * (and hence interned). Assumption is that this
+     * will provide for a quicker lookup than by text
+     * since the statements can be many characters.
+     * 
+     * May also allow easier sharing with other implementations
+     * such as a Java procedure which could have a different
+     * prepareStatement method.
+     */
+    private Map statements = new IdentityHashMap();
+
+    /**
+     * Prepare a statement, looking in the map first.
+     * If the statement does not exist in the map then
+     * it is prepared and put into the map for future use.
+     */
+    protected PreparedStatement prepareStatement(String sql) throws SQLException {
+        PreparedStatement ps = (PreparedStatement) statements.get(sql);
+        if (ps != null)
+            return ps;
+        
+        // Prepare all statements as forward-only, read-only, close at commit.
+        ps = conn.prepareStatement(sql,
+                ResultSet.TYPE_FORWARD_ONLY,
+                ResultSet.CONCUR_READ_ONLY,
+                ResultSet.CLOSE_CURSORS_AT_COMMIT);
+        statements.put(sql, ps);
+        return ps;
+    }
+
+    public void close() throws SQLException {
+              
+        for (Iterator i = statements.keySet().iterator(); i.hasNext(); )
+        {
+            String sql = (String) i.next();
+            PreparedStatement ps = (PreparedStatement) statements.get(sql);
+            ps.close();
+        }
+    }
+
+    /**
+     * Reset a PreparedStatement. Closes its open ResultSet
+     * and clears the parameters. While clearing the parameters
+     * is not required since any future execution will override
+     * them, it is done here to reduce the chance of errors.
+     * E.g. using the wrong prepared statement for a operation
+     * or not setting all the parameters.
+     * It is assumed the prepared statement was just executed.
+     * @throws SQLException 
+     */
+    protected void reset(PreparedStatement ps) throws SQLException {
+        ResultSet rs = ps.getResultSet();
+        if (rs != null)
+            rs.close();
+        ps.clearParameters();
+    }
+
+    protected Address getAddress(ResultSet rs, String firstColumnName) throws SQLException
{
+        Address address = new Address();
+        
+        int col = rs.findColumn(firstColumnName);
+        address.setStreet1(rs.getString(col++));
+        address.setStreet2(rs.getString(col++));
+        address.setCity(rs.getString(col++));
+        address.setState(rs.getString(col++));
+        address.setZip(rs.getString(col));
+        
+        return address;
+    }
+
+}

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

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?rev=595387&r1=595386&r2=595387&view=diff
==============================================================================
--- 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 Thu
Nov 15 10:24:47 2007
@@ -25,10 +25,14 @@
 
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.system.oe.direct.SimpleNonStandardOperations;
 import org.apache.derbyTesting.system.oe.run.Checks;
 import org.apache.derbyTesting.system.oe.run.Populate;
 import org.apache.derbyTesting.system.oe.run.Schema;
 
+import java.sql.Connection;
+import java.sql.SQLException;
+
 /**
  * Test the basic functionality of the Order Entry test
  * using scale 1 as a functional test, to ensure that changes to the
@@ -53,7 +57,40 @@
         suite.addTest(Checks.suite());
 
         suite.addTestSuite(OperationsTester.class);
+        suite.addTestSuite(OETest.class);
                 
         return new CleanDatabaseTestSetup(suite);
+    }
+    
+    private SimpleNonStandardOperations getNSOps() throws SQLException
+    {
+        return new SimpleNonStandardOperations(
+                getConnection(), Connection.TRANSACTION_SERIALIZABLE);
+    }
+    
+    /**
+     * Test the non-standard customer inquiry transaction
+     */
+    public void testCustomerInquiry() throws SQLException
+    {
+        SimpleNonStandardOperations nsops = getNSOps();
+                   
+        for (int i = 0; i < 20; i++)
+            nsops.customerInquiry(1);
+        
+        nsops.close();
+    }
+    
+    /**
+     * Test the non-standard customer address change transaction
+     */
+    public void testCustomerAddressChange() throws SQLException
+    {
+        SimpleNonStandardOperations nsops = getNSOps();
+                   
+        for (int i = 0; i < 20; i++)
+            nsops.customerAddressChange(1);
+        
+        nsops.close();
     }
 }



Mime
View raw message