db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r993374 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 07 Sep 2010 14:17:59 GMT
Author: rhillegas
Date: Tue Sep  7 14:17:58 2010
New Revision: 993374

URL: http://svn.apache.org/viewvc?rev=993374&view=rev
Log:
DERBY-4789: Attempt bulk-insert optimization when inserting from a table function.

Added:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
  (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java?rev=993374&r1=993373&r2=993374&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java Tue Sep
 7 14:17:58 2010
@@ -325,6 +325,12 @@ public class FromVTI extends FromTable i
 		return supportsMultipleInstantiations;
 	}
 
+    /** Return true if this is a user-defined table function */
+    public boolean isDerbyStyleTableFunction()
+    {
+        return isDerbyStyleTableFunction;
+    }
+
 	/**
 	 * @see ResultSetNode#adjustForSortElimination()
 	 */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java?rev=993374&r1=993373&r2=993374&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasNodeVisitor.java
Tue Sep  7 14:17:58 2010
@@ -38,7 +38,7 @@ import org.apache.derby.iapi.error.Stand
  */
 public class HasNodeVisitor implements Visitor
 {
-	private boolean hasNode;
+	protected boolean hasNode;
 	private Class 	nodeClass;
 	private Class	skipOverClass;
 	/**

Added: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java?rev=993374&view=auto
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
(added)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
Tue Sep  7 14:17:58 2010
@@ -0,0 +1,64 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.HasTableFunctionVisitor
+
+   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.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.services.sanity.SanityManager;
+import org.apache.derby.iapi.sql.compile.Visitable; 
+import org.apache.derby.iapi.sql.compile.Visitor;
+
+import org.apache.derby.iapi.error.StandardException;
+
+/**
+ * Find out if we have a user-defined table function anywhere in the
+ * tree.  Stop traversal as soon as we find one.
+ */
+public class HasTableFunctionVisitor extends HasNodeVisitor
+{
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // CONSTRUCTORS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    public HasTableFunctionVisitor()
+    {
+        super( FromVTI.class );
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // OVERRIDES
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+	public Visitable visit(Visitable node)
+	{
+		if ( node instanceof FromVTI )
+		{
+            FromVTI vti = (FromVTI) node;
+
+            if ( vti.isDerbyStyleTableFunction() ) { hasNode = true; }
+		}
+		return node;
+	}
+}
+

Propchange: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/HasTableFunctionVisitor.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java?rev=993374&r1=993373&r2=993374&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/InsertNode.java Tue
Sep  7 14:17:58 2010
@@ -853,7 +853,33 @@ public final class InsertNode extends DM
         resultSet.pushOffsetFetchFirst(offset, fetchFirst);
 
 		super.optimizeStatement();
-	}
+        
+        //
+        // If the insert stream involves a table function, attempt the bulk-insert
+        // optimization. See https://issues.apache.org/jira/browse/DERBY-4789
+        // We perform this check after optimization because the table function may be
+        // wrapped in a view, which is only expanded at optimization time.
+        //
+        HasTableFunctionVisitor tableFunctionVisitor = new HasTableFunctionVisitor();
+        this.accept( tableFunctionVisitor );
+        if ( tableFunctionVisitor.hasNode() ) { requestBulkInsert(); }
+	}
+
+    /**
+     * Request bulk insert optimization at run time.
+     */
+    private void requestBulkInsert()
+    {
+        if ( targetProperties == null ) { targetProperties = new Properties(); }
+
+        // Set bulkInsert if insertMode not already set. For the import procedures,
+        // the insertMode property may be set already
+        String key = "insertMode";
+        String value = "bulkInsert";
+
+        if ( targetProperties.getProperty( key ) == null )
+        { targetProperties.put( key, value ); }
+    }
 
 	/**
 	 * Code generation for insert

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java?rev=993374&r1=993373&r2=993374&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Tue Sep  7 14:17:58 2010
@@ -91,6 +91,20 @@ public class TableFunctionTest extends B
         { "fAlSe", "false" },
     };
     
+    private static  final   String[][]  BULK_INSERT_ROWS =
+    {
+        { "1", "red" },
+        { "2", "blue" },
+    };
+    
+    private static  final   String[][]  DOUBLY_INSERTED_ROWS =
+    {
+        { "1", "red" },
+        { "1", "red" },
+        { "2", "blue" },
+        { "2", "blue" },
+    };
+    
     private static  final   String[][]  ALL_TYPES_ROWS =
     {
         {
@@ -957,6 +971,8 @@ public class TableFunctionTest extends B
 
         coercionTest();
 
+        bulkInsert();
+        
         miscBugs();
     }
     
@@ -1184,6 +1200,117 @@ public class TableFunctionTest extends B
     }
     
     /**
+     * Verify bulk insert using a VTI
+     */
+    private void  bulkInsert()
+        throws Exception
+    {
+        Connection conn = getConnection();
+        
+        goodStatement
+            (
+             "create table bulkInsertTable\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n"
+             );
+        goodStatement
+            (
+             "create table biSourceTable\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n"
+             );
+        goodStatement
+            (
+             "create function bulkInsertVTI()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     column0 int,\n" +
+             "     column1 varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name '" + getClass().getName() + ".bulkInsertVTI'\n"
+             );
+        goodStatement
+            (
+             "create view bulkInsertView( column0, column1 ) as select column0, column1\n"
+
+             "from table( bulkInsertVTI() ) s\n"
+             );
+        goodStatement
+            (
+             "insert into biSourceTable select * from bulkInsertView\n"
+             );
+
+        //
+        // Inserting from a table function into an empty table should trigger
+        // the bulk-insert optimization, resulting in a new conglomerate for
+        // the target table
+        //
+        // Inserting from a table function into a non-empty table should NOT trigger
+        // the bulk-insert optimization. The conglomerate number of the target table
+        // should not change.
+        //
+
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from table( bulkInsertVTI() ) s",
+             true
+             );
+
+        // You still get bulk-insert if you wrap the table function in a view
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from bulkInsertView",
+             true
+             );
+
+        // You don't get bulk-insert if you insert from an ordinary table
+        vetBulkInsert
+            (
+             conn,
+             "insert into bulkInsertTable select * from biSourceTable",
+             false
+             );
+
+    }
+    private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected
)
+        throws Exception
+    {
+        goodStatement( "delete from bulkInsertTable" );
+
+        vetBulkInsert( conn, insert, bulkInsertExpected, BULK_INSERT_ROWS );
+
+        //
+        // Inserting from a table function into a non-empty table should NOT trigger
+        // the bulk-insert optimization. The conglomerate number of the target table
+        // should not change.
+        //
+        vetBulkInsert( conn, insert, false, DOUBLY_INSERTED_ROWS );
+    }
+    private void vetBulkInsert( Connection conn, String insert, boolean bulkInsertExpected,
String[][] expectedRows )
+        throws Exception
+    {
+        long originalConglomerateID = getConglomerateID( conn, "BULKINSERTTABLE" );
+        goodStatement( insert );
+        long conglomerateIDAfterInsert = getConglomerateID( conn, "BULKINSERTTABLE" );
+        
+        assertEquals( bulkInsertExpected, originalConglomerateID != conglomerateIDAfterInsert
);
+        assertResults
+            (
+             "select * from bulkInsertTable order by column0",
+             expectedRows,
+             new int[] { Types.INTEGER, Types.VARCHAR }
+             );
+    }
+    
+    /**
      * Verify that Derby handles VTI columns of all known datatypes.
      */
     private void  allLegalDatatypesVTIResults()
@@ -1821,6 +1948,14 @@ public class TableFunctionTest extends B
     }
 
     /**
+     * A VTI for use in bulk insert
+     */
+    public  static  ResultSet bulkInsertVTI()
+    {
+        return makeVTI( BULK_INSERT_ROWS );
+    }
+
+    /**
      * A VTI which returns rows having columns of all legal datatypes.
      */
     public  static  ResultSet returnsAllLegalDatatypes( int intArg, String varcharArg )
@@ -2159,7 +2294,7 @@ public class TableFunctionTest extends B
                     fail( "Can't handle jdbc type " + actualJdbcType );
                 }
 
-                println( "Comparing " + expectedValue + " to " + actualValue + " and " +
actualValueByName );
+                //println( "Comparing " + expectedValue + " to " + actualValue + " and "
+ actualValueByName );
 
                 if ( actualValue == null ) { assertNull( actualValueByName ); }
                 else { assertTrue( actualValue.equals( actualValueByName ) ); }
@@ -2353,4 +2488,35 @@ public class TableFunctionTest extends B
         return 0.0;
     }
 
+    /** Get the conglomerate id of a table */
+    private long getConglomerateID( Connection conn, String tableName ) throws Exception
+    {
+        PreparedStatement ps = conn.prepareStatement
+            (
+             "select c.conglomeratenumber\n" +
+             "from sys.sysconglomerates c, sys.systables t\n" +
+             "where t.tablename = ? and t.tableid = c.tableid"
+             );
+        ps.setString( 1, tableName );
+
+        long result = getScalarLong( ps );
+
+        ps.close();
+
+        return result;
+    }
+
+    /** Get a scalar long result from a query */
+    private long getScalarLong( PreparedStatement ps ) throws Exception
+    {
+        ResultSet rs = ps.executeQuery();
+        rs.next();
+        long retval = rs.getLong( 1 );
+
+        rs.close();
+        ps.close();
+
+        return retval;
+    }
+
 }



Mime
View raw message