You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2013/05/24 08:03:17 UTC
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/
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();
+ }
+ }
}