db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1485938 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 24 May 2013 06:03:17 GMT
Author: dag
Date: Fri May 24 06:03:17 2013
New Revision: 1485938

URL: http://svn.apache.org/r1485938
Log:
DERBY-6148 Wrong sort elimination when using permuted sort order

Patch derby-6148-3. Adds lacking use of join order index map into
optimizableList in method OrderByList#sortRequired. Also adds
regression tests for previously failing (and a some good) queries.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java?rev=1485938&r1=1485937&r2=1485938&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
Fri May 24 06:03:17 2013
@@ -38,54 +38,62 @@ public interface RequiredRowOrdering
 	static final int ELIMINATE_DUPS = 2;
 	static final int NOTHING_REQUIRED = 3;
 
-	/**
-	 * Tell whether sorting is required for this RequiredRowOrdering,
-	 * given a RowOrdering.
-	 *
-	 * @param rowOrdering	The order of rows in question
-	 * @param optimizableList	The current join order being considered by 
-	 *    the optimizer. We need to look into this to determine if the outer
-	 *    optimizables are single row resultset if the order by column is
-	 *    on an inner optimizable and that inner optimizable is not a one
-	 *    row resultset. DERBY-3926
-	 *
-	 * @return	SORT_REQUIRED if sorting is required,
-	 *			ELIMINATE_DUPS if no sorting is required but duplicates
-	 *							must be eliminated (i.e. the rows are in
-	 *							the right order but there may be duplicates),
-	 *			NOTHING_REQUIRED is no operation is required
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	int sortRequired(RowOrdering rowOrdering, OptimizableList optimizableList) 
-	throws StandardException;
-
-	/**
-	 * Tell whether sorting is required for this RequiredRowOrdering,
-	 * given a RowOrdering representing a partial join order, and
-	 * a bit map telling what tables are represented in the join order.
-	 * This is useful for reducing the number of cases the optimizer
-	 * has to consider.
-	 *
-	 * @param rowOrdering	The order of rows in the partial join order
-	 * @param tableMap		A bit map of the tables in the partial join order
-	 * @param optimizableList	The current join order being considered by 
-	 *    the optimizer. We need to look into this to determine if the outer
-	 *    optimizables are single row resultset if the order by column is
-	 *    on an inner optimizable and that inner optimizable is not a one
-	 *    row resultset. DERBY-3926
-	 *
-	 * @return	SORT_REQUIRED if sorting is required,
-	 *			ELIMINATE_DUPS if no sorting is required by duplicates
-	 *							must be eliminated (i.e. the rows are in
-	 *							the right order but there may be duplicates),
-	 *			NOTHING_REQUIRED is no operation is required
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	int sortRequired(RowOrdering rowOrdering, JBitSet tableMap, 
-			OptimizableList optimizableList)
-			throws StandardException;
+    /**
+     * Tell whether sorting is required for this RequiredRowOrdering,
+     * given a RowOrdering.
+     *
+     * @param rowOrdering   The order of rows in question
+     * @param optimizableList The list of join participants
+     * @param proposedJoinOrder The current join order being considered by
+     *    the optimizer. We need to look into this to determine if the outer
+     *    optimizables are single row result set if the order by column is
+     *    on an inner optimizable and that inner optimizable is not a one.
+     *    {@code proposedJoinOrder} is a map onto {@code optimizableList}:
+     *    it contains indexes of optimizables in that list in the join order
+     *    proposed, cf. {@code OptimizerImpl#proposedJoinOrder}.
+     * @return  SORT_REQUIRED if sorting is required,
+     *          ELIMINATE_DUPS if no sorting is required but duplicates
+     *                          must be eliminated (i.e. the rows are in
+     *                          the right order but there may be duplicates),
+     *          NOTHING_REQUIRED is no operation is required
+     *
+     * @exception StandardException     Thrown on error
+     */
+    int sortRequired(RowOrdering rowOrdering,
+                     OptimizableList optimizableList,
+                     int[] proposedJoinOrder) throws StandardException;
+
+   /**
+    * Tell whether sorting is required for this RequiredRowOrdering,
+    * given a RowOrdering representing a partial join order, and
+    * a bit map telling what tables are represented in the join order.
+    * This is useful for reducing the number of cases the optimizer
+    * has to consider.
+    *
+    * @param rowOrdering   The order of rows in the partial join order
+    * @param tableMap      A bit map of the tables in the partial join order
+    * @param optimizableList The list of join participants
+    * @param proposedJoinOrder The current join order being considered by
+    *    the optimizer. We need to look into this to determine if the outer
+    *    optimizables are single row result set if the order by column is
+    *    on an inner optimizable and that inner optimizable is not a one.
+    *    {@code proposedJoinOrder} is a map onto {@code optimizableList}:
+    *    it contains indexes of optimizables in that list in the join order
+    *    proposed, cf. {@code OptimizerImpl#proposedJoinOrder}.
+    *    DERBY-3926 and DERBY-6148
+    *
+    * @return  SORT_REQUIRED if sorting is required,
+    *          ELIMINATE_DUPS if no sorting is required by duplicates
+    *                          must be eliminated (i.e. the rows are in
+    *                          the right order but there may be duplicates),
+    *          NOTHING_REQUIRED is no operation is required
+    *
+    * @exception StandardException     Thrown on error
+    */
+    int sortRequired(RowOrdering rowOrdering,
+                     JBitSet tableMap,
+                     OptimizableList optimizableList,
+                     int[] proposedJoinOrder) throws StandardException;
 
 	/**
 	 * Estimate the cost of doing a sort for this row ordering, given

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java?rev=1485938&r1=1485937&r2=1485938&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ConstraintDescriptor.java
Fri May 24 06:03:17 2013
@@ -21,6 +21,7 @@
 
 package org.apache.derby.iapi.sql.dictionary;
 
+import java.util.Arrays;
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.iapi.sql.depend.Provider;
@@ -456,7 +457,8 @@ public abstract class ConstraintDescript
 				"constraintId: " + constraintId + "\n" +
 				"deferrable: " + deferrable + "\n" +
 				"initiallyDeferred: " + initiallyDeferred + "\n" +
-				"referencedColumns: " + referencedColumns + "\n" +
+               "referencedColumns: " +
+                    Arrays.toString(referencedColumns) + "\n" +
 				"schemaDesc: " + schemaDesc + "\n"
 				;
 		}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java?rev=1485938&r1=1485937&r2=1485938&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java Fri
May 24 06:03:17 2013
@@ -82,7 +82,9 @@ public class OptimizerImpl implements Op
 
 	/* Bit map of tables that have already been assigned to slots.
 	 * Useful for pushing join clauses as slots are assigned.
-	 */
+     * Enforcement of ordering dependencies is done through
+     * assignedTableMap.
+     */
 	protected JBitSet		 assignedTableMap;
 	protected OptimizableList optimizableList;
 	OptimizablePredicateList predicateList;
@@ -139,11 +141,29 @@ public class OptimizerImpl implements Op
 	protected int maxMemoryPerTable;
 
 	// Whether or not we need to reload the best plan for an Optimizable
-	// when we "pull" it.  If the latest complete join order was the
+    // when we "pull" [1] it.  If the latest complete join order was the
 	// best one so far, then the Optimizable will already have the correct
 	// best plan loaded so we don't need to do the extra work.  But if
 	// the most recent join order was _not_ the best, then this flag tells
 	// us that we need to reload the best plan when pulling.
+    //
+    // [1]: As part of the iteration through the join orders, the optimizer has
+    // to "pull" Optimizables from the the join order before re-placing them in
+    // a different order.  As an example, in order to get from:
+    //
+    //   { HOJ, TAB_V, TAB_D }    to
+    //
+    //   { HOJ, TAB_D, TAB_V}
+    //
+    // the optimizer will first pull TAB_D from the join order, then
+    // it will pull TAB_V, then it will place TAB_D, and finally place
+    // TAB_V.  I.e.:
+    //
+    //   { HOJ, TAB_V, - }
+    //   { HOJ, -, - }
+    //   { HOJ, TAB_D, - }
+    //   { HOJ, TAB_D, TAB_V }
+
 	private boolean reloadBestPlan;
 
 	// Set of optimizer->bestJoinOrder mappings used to keep track of which
@@ -1765,8 +1785,10 @@ public class OptimizerImpl implements Op
 					curOpt.considerSortAvoidancePath())
 				{
 					if (requiredRowOrdering.sortRequired(
-							bestRowOrdering, optimizableList) == 
-								RequiredRowOrdering.NOTHING_REQUIRED)
+                            bestRowOrdering,
+                            optimizableList,
+                            proposedJoinOrder) ==
+                        RequiredRowOrdering.NOTHING_REQUIRED)
 					{
 						if (tracingIsOn()) { tracer().traceCurrentPlanAvoidsSort( bestCost, currentSortAvoidanceCost
); }
 
@@ -2208,8 +2230,9 @@ public class OptimizerImpl implements Op
 				** path avoid a sort?
 				*/
 				if (requiredRowOrdering.sortRequired(currentRowOrdering,
-														assignedTableMap,
-														optimizableList)
+                                                     assignedTableMap,
+                                                     optimizableList,
+                                                     proposedJoinOrder)
 										==RequiredRowOrdering.NOTHING_REQUIRED)
 				{
 					ap = optimizable.getBestSortAvoidancePath();
@@ -2337,8 +2360,9 @@ public class OptimizerImpl implements Op
 				** path avoid a sort?
 				*/
 				if (requiredRowOrdering.sortRequired(currentRowOrdering,
-														assignedTableMap,
-														optimizableList)
+                                                    assignedTableMap,
+                                                     optimizableList,
+                                                     proposedJoinOrder)
 										== RequiredRowOrdering.NOTHING_REQUIRED)
 				{
 					ap = optimizable.getBestSortAvoidancePath();

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?rev=1485938&r1=1485937&r2=1485938&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Fri
May 24 06:03:17 2013
@@ -425,27 +425,33 @@ public class OrderByList extends Ordered
 
 	/* RequiredRowOrdering interface */
 
-	/**
-	 * @see RequiredRowOrdering#sortRequired
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public int sortRequired(RowOrdering rowOrdering,
-			OptimizableList optimizableList) throws StandardException
-	{
-		return sortRequired(rowOrdering, (JBitSet) null, optimizableList);
-	}
+    /**
+     * @see RequiredRowOrdering#sortRequired(RowOrdering, OptimizableList, int[])
+     *
+     * @exception StandardException     Thrown on error
+     */
+    public int sortRequired(
+        RowOrdering rowOrdering,
+        OptimizableList optimizableList,
+        int[] proposedJoinOrder) throws StandardException
+    {
+        return sortRequired(rowOrdering,
+                            (JBitSet)null,
+                            optimizableList,
+                            proposedJoinOrder);
+    }
 
-	/**
-	 * @see RequiredRowOrdering#sortRequired
-	 *
-	 * @exception StandardException		Thrown on error
-	 */
-	public int sortRequired(RowOrdering rowOrdering, 
-			JBitSet tableMap,
-			OptimizableList optimizableList)
-				throws StandardException
-	{
+    /**
+     * @see RequiredRowOrdering#sortRequired(RowOrdering, JBitSet, OptimizableList, int[])
+     *
+     * @exception StandardException     Thrown on error
+     */
+    public int sortRequired(
+        RowOrdering rowOrdering,
+        JBitSet tableMap,
+        OptimizableList optimizableList,
+        int[] proposedJoinOrder) throws StandardException
+    {
 		/*
 		** Currently, all indexes are ordered ascending, so a descending
 		** ORDER BY always requires a sort.
@@ -574,17 +580,24 @@ public class OrderByList extends Ordered
 					 * order by column's optimizable and the rows returned 
 					 * from those multiple scans may not be ordered correctly.
 					 */
-					for (int i=0; i < optimizableList.size(); i++)
+
+                   for (int i=0;
+                        i < proposedJoinOrder.length &&
+                            proposedJoinOrder[i] != -1; // -1: partial order
+                        i++)
 					{
-						//Get one outer optimizable at a time from the join
-						//order
-						Optimizable considerOptimizable = 
-							optimizableList.getOptimizable(i);
-						//If we have come across the optimizable for the order 
-						//by column in the join order, then we do not need to 
-						//look at the inner optimizables in the join order. As
-						//long as the outer optimizables are one row resultset,
-						//we are fine to consider sort avoidance.
+                       // Get one outer optimizable at a time from the join
+                       // order
+                        Optimizable considerOptimizable = optimizableList.
+                                getOptimizable(proposedJoinOrder[i]);
+
+                       // If we have come across the optimizable for the order
+                       // by column in the join order, then we do not need to
+                       // look at the inner optimizables in the join order. As
+                       // long as the outer optimizables are one row
+                       // resultset, or is ordered on the order by column (see
+                       // below check), we are fine to consider sort
+                       // avoidance.
 						if (considerOptimizable.getTableNumber() == 
 							cr.getTableNumber())
 							break;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java?rev=1485938&r1=1485937&r2=1485938&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
Fri May 24 06:03:17 2013
@@ -3601,4 +3601,290 @@ public class OrderByAndSortAvoidance ext
                 {"2147483654", "000003", "21857"}};
         JDBC.assertFullResultSet(rs, result);
     }
+
+
+    /*
+     * DERBY-6148. Verifying that permuted join order doesn't
+     * erroneously give sort avoidance under certain index access
+     * paths.
+     */
+    public void testDerby6148() throws SQLException {
+        Statement s = createStatement();
+
+        createTablesForDerby6148(s);
+        insertDataForDerby6148();
+        createIndexesForDerby6148(s);
+
+        // This query failed prior to fixing DERBY-6148
+        final String brokenQuery =
+            "SELECT t.id, t.item, title " +
+            "    FROM d6148_tests t" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP, " +
+            "                             constraint = d6148_tests_1\n" +
+            "       , d6148_item_usage u" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP," +
+            "                             constraint = d6148_item_usage_1\n" +
+            "    WHERE username = 'MICKEY' AND " +
+            "          u.item = t.item " +
+            "ORDER BY t.item, title";
+
+        // These queries worked prior to fixing DERBY-6148
+        final String goodQuery1 = // changed order of FROM tables here:
+            "SELECT t.id, t.item, title " +
+            "    FROM d6148_item_usage u" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP," +
+            "                             constraint = d6148_item_usage_1\n" +
+            "       , d6148_tests t" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP, " +
+            "                             constraint = d6148_tests_1\n" +
+            "    WHERE username = 'MICKEY' AND " +
+            "          u.item = t.item " +
+            "ORDER BY t.item, title";
+
+        final String goodQuery2 = // changed ORDER BY column to other equijoin
+                                  // predicate column
+            "SELECT t.id, t.item, title " +
+            "    FROM d6148_tests t" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP, " +
+            "                             constraint = d6148_tests_1\n" +
+            "       , d6148_item_usage u" +
+            "         -- DERBY-PROPERTIES joinStrategy = NESTEDLOOP," +
+            "                             constraint = d6148_item_usage_1\n" +
+            "    WHERE username = 'MICKEY' AND " +
+            "          u.item = t.item " +
+            "ORDER BY u.item, title";
+
+
+        final String[][] expectedRows = getExpectedRowsDerby6148();
+        JDBC.assertFullResultSet(s.executeQuery(brokenQuery), expectedRows);
+        JDBC.assertFullResultSet(s.executeQuery(goodQuery1), expectedRows);
+        JDBC.assertFullResultSet(s.executeQuery(goodQuery2), expectedRows);
+    }
+
+    private String[][] getExpectedRowsDerby6148() {
+        return new String[][]{
+            {"15", "60001", "Test 15         "},
+            {"19", "60001", "Test 19         "},
+            {"25", "60001", "Test 25         "},
+            {"27", "60001", "Test 27         "},
+            {"28", "60001", "Test 28         "},
+            {"10", "61303", "Test 10         "},
+            {"11", "61303", "Test 11         "},
+            {"13", "61303", "Test 13         "},
+            {"14", "61303", "Test 14         "},
+            {"21", "61303", "Test 21         "},
+            {"35", "61303", "Test 35         "},
+            {"9", "61303", "Test 9          "},
+            {"26", "7205731", "Test 26         "},
+            {"32", "7205731", "Test 32         "},
+            {"4", "7205731", "Test 4          "},
+            {"5", "7205731", "Test 5          "},
+            {"6", "7205731", "Test 6          "},
+            {"7", "7205731", "Test 7          "},
+            {"8", "7205731", "Test 8          "},
+            {"1", "XY101", "Test 1          "},
+            {"12", "XY101", "Test 12         "},
+            {"16", "XY101", "Test 16         "},
+            {"17", "XY101", "Test 17         "},
+            {"18", "XY101", "Test 18         "},
+            {"2", "XY101", "Test 2          "},
+            {"22", "XY101", "Test 22         "},
+            {"23", "XY101", "Test 23         "},
+            {"24", "XY101", "Test 24         "},
+            {"3", "XY101", "Test 3          "},
+            {"31", "XY101", "Test 31         "}};
+    }
+
+    private void createTablesForDerby6148(Statement s) throws SQLException {
+        s.executeUpdate(
+            "create table d6148_tests (" +
+            "    id integer not null generated always as identity " +
+            "        (start with 1, increment by 1), " +
+            "    item varchar(15) not null, " +
+            "    title varchar(255) not null)");
+
+        s.executeUpdate(
+            "create table d6148_item_usage (" +
+            "    username varchar(15) not null, " +
+            "    item varchar(15) not null, " +
+            "    value smallint default 0)");
+
+        s.executeUpdate(
+            "create table d6148_items (" +
+            "    item varchar(15) not null, " +
+            "    name varchar(255) not null, " +
+            "    special char(1) default null)");
+
+        s.executeUpdate(
+            "create table d6148_users (" +
+            "    username varchar(15) not null, " +
+            "    surname varchar(255) not null)");
+
+    }
+
+    private void createIndexesForDerby6148(Statement s) throws SQLException {
+        // Create primary/unique indexes
+        s.executeUpdate(
+            "alter table d6148_items add constraint " +
+            "    d6148_items_pk primary key (item)");
+
+        s.executeUpdate(
+            "alter table d6148_item_usage add constraint " +
+            "    d6148_item_usage_pk primary key (username, item)");
+
+        s.executeUpdate(
+            "alter table d6148_users add constraint " +
+            "    users_pk primary key (username)");
+
+        s.executeUpdate(
+            "alter table d6148_tests add constraint " +
+            "    d6148_tests_pk primary key (id)");
+
+        s.executeUpdate(
+            "alter table d6148_tests add constraint " +
+            "    d6148_tests_1 unique (item, title)");
+
+        // Add foreign key constraints
+        s.executeUpdate(
+            "alter table d6148_item_usage add constraint " +
+            "    d6148_item_usage_2 foreign key (item) references " +
+            "    d6148_items (item) on delete cascade on update no action");
+
+        s.executeUpdate(
+            "alter table d6148_item_usage add constraint " +
+            "    d6148_item_usage_1 foreign key (username) references " +
+            "    d6148_users (username) on delete cascade on update no action");
+
+        s.executeUpdate(
+            "alter table d6148_tests add constraint " +
+            "    d6148_tests_2 foreign key (item) references " +
+            "    d6148_items (item) on delete cascade on update no action");
+    }
+
+    private void insertDataForDerby6148() throws SQLException {
+        String[][] users = {
+            {"ADMIN","Administrator"},
+            {"MINNIE","MOUSE"},
+            {"MICKEY","MOUSE"},
+            {"TEST","Test"},
+            {"PIED","Piper"},
+            {"WINNIE","Pooh"},
+            {"DONALD","Duck"},
+            {"CLARK","Kent"},
+            {"VARG","Veum"},
+            {"TOMMY","Tiger"},
+            {"USER1","?????"},
+            {"DEMO","Demo"},
+            {"BRAM","Stoker"},
+            {"USER2","???????"},
+            {"USER3","?????"}};
+
+        PreparedStatement ps = prepareStatement(
+            "insert into d6148_users values (?,?)");
+
+        for (String[] u : users) {
+            ps.setString(1, u[0]);
+            ps.setString(2, u[1]);
+            ps.executeUpdate();
+        }
+
+        String[][] items = {
+            {"XY101","XY101", null},
+            {"61303","61303", null},
+            {"7205731","7205731", null},
+            {"60001","60001", null},
+            {"60001B","60001B", null},
+            {"61108","61108", null}};
+
+        ps = prepareStatement(
+            "insert into d6148_items values (?,?,?)");
+
+        for (String[] i : items) {
+            ps.setString(1, i[0]);
+            ps.setString(2, i[1]);
+            ps.setString(3, i[2]);
+            ps.executeUpdate();
+        }
+
+        String[][] tests = {
+            {"XY101","Test 1          "},
+            {"XY101","Test 2          "},
+            {"XY101","Test 3          "},
+            {"7205731","Test 4          "},
+            {"7205731","Test 5          "},
+            {"7205731","Test 6          "},
+            {"7205731","Test 7          "},
+            {"7205731","Test 8          "},
+            {"61303","Test 9          "},
+            {"61303","Test 10         "},
+            {"61303","Test 11         "},
+            {"XY101","Test 12         "},
+            {"61303","Test 13         "},
+            {"61303","Test 14         "},
+            {"60001","Test 15         "},
+            {"XY101","Test 16         "},
+            {"XY101","Test 17         "},
+            {"XY101","Test 18         "},
+            {"60001","Test 19         "},
+            {"60001B","Test 20         "},
+            {"61303","Test 21         "},
+            {"XY101","Test 22         "},
+            {"XY101","Test 23         "},
+            {"XY101","Test 24         "},
+            {"60001","Test 25         "},
+            {"7205731","Test 26         "},
+            {"60001","Test 27         "},
+            {"60001","Test 28         "},
+            {"60001B","Test 29         "},
+            {"60001B","Test 30         "},
+            {"XY101","Test 31         "},
+            {"7205731","Test 32         "},
+            {"60001B","Test 33         "},
+            {"60001B","Test 34         "},
+            {"61303","Test 35         "}};
+
+        ps = prepareStatement(
+            "insert into d6148_tests values (default,?,?)");
+
+        for (String[] t : tests) {
+            ps.setString(1, t[0]);
+            ps.setString(2, t[1]);
+            ps.executeUpdate();
+        }
+
+        String[][] item_usage = {
+            {"MINNIE","XY101","4"},
+            {"MICKEY","XY101","4"},
+            {"MICKEY","61303","4"},
+            {"MICKEY","7205731","4"},
+            {"PIED","61303","2"},
+            {"TOMMY","60001","1"},
+            {"USER1","60001","0"},
+            {"BRAM","60001","2"},
+            {"WINNIE","7205731","1"},
+            {"MICKEY","60001","4"},
+            {"DONALD","60001","2"},
+            {"PIED","60001","2"},
+            {"VARG","60001","2"},
+            {"CLARK","60001","2"},
+            {"TEST","60001B","0"},
+            {"DEMO","61303","0"},
+            {"DONALD","61303","2"},
+            {"DONALD","60001B","4"},
+            {"DEMO","XY101","0"},
+            {"USER2","61303","0"},
+            {"USER3","61303","0"},
+            {"MICKEY","61108","4"},
+            {"MINNIE","60001B","0"}};
+
+        ps = prepareStatement(
+            "insert into d6148_item_usage values (?,?,?)");
+
+        for (String[] iu : item_usage) {
+            ps.setString(1, iu[0]);
+            ps.setString(2, iu[1]);
+            ps.setString(3, iu[2]);
+            ps.executeUpdate();
+        }
+    }
 }



Mime
View raw message