db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r574276 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Mon, 10 Sep 2007 15:09:36 GMT
Author: rhillegas
Date: Mon Sep 10 08:09:35 2007
New Revision: 574276

URL: http://svn.apache.org/viewvc?rev=574276&view=rev
Log:
DERBY-716: Add optimizer support for Derby-style Table Functions.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.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=574276&r1=574275&r2=574276&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 Mon Sep
10 08:09:35 2007
@@ -78,6 +78,7 @@
 
 import java.lang.reflect.Constructor;
 import java.lang.reflect.InvocationTargetException;
+import java.lang.reflect.Modifier;
 
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -231,7 +232,7 @@
 		{
 			try
 			{
-				VTICosting vtic = (version2) ? (VTICosting) ps : (VTICosting) rs;
+				VTICosting vtic = getVTICosting();
 				estimatedCost = vtic.getEstimatedCostPerInstantiation(this);
 				estimatedRowCount = vtic.getEstimatedRowCount(this);
 				supportsMultipleInstantiations = vtic.supportsMultipleInstantiations(this);
@@ -543,6 +544,19 @@
 									 subqueryList,
 									 aggregateVector);
 
+		// Is the parameter list to the constructor valid for a VTI?
+		methodParms = methodCall.getMethodParms();
+
+        RoutineAliasInfo    routineInfo = methodCall.getRoutineInfo();
+
+        if (
+            (routineInfo !=null) &&
+            routineInfo.getReturnType().isRowMultiSet() &&
+            (routineInfo.getParameterStyle() == RoutineAliasInfo.PS_DERBY_JDBC_RESULT_SET)
+            )			{
+            isDerbyStyleTableFunction = true;
+        }
+        
 		/* If we have a valid constructor, does class implement the correct interface? 
 		 * If version2 is true, then it must implement PreparedStatement, otherwise
 		 * it can implement either PreparedStatement or ResultSet.  (We check for
@@ -583,10 +597,11 @@
 		    implementsVTICosting = constructor.assignableTo(ClassName.VTICosting);
 		}
 
-
-
-		// Is the parameter list to the constructor valid for a VTI?
-		methodParms = methodCall.getMethodParms();
+        if ( isDerbyStyleTableFunction )
+        {
+            implementsVTICosting = implementsDerbyStyleVTICosting( methodCall.getJavaClassName()
);
+        }
+            
 
 		/* Build the RCL for this VTI.  We instantiate an object in order
 		 * to get the ResultSetMetaData.
@@ -615,14 +630,8 @@
 
 			// if this is a Derby-style Table Function, then build the result
 			// column list from the RowMultiSetImpl return datatype
-			RoutineAliasInfo    routineInfo = methodCall.getRoutineInfo();
 
-			if (
-			     (routineInfo !=null) &&
-			     routineInfo.getReturnType().isRowMultiSet() &&
-			     (routineInfo.getParameterStyle() == RoutineAliasInfo.PS_DERBY_JDBC_RESULT_SET)
-			    )			{
-			    isDerbyStyleTableFunction = true;
+			if ( isDerbyStyleTableFunction ) {
 			    createResultColumnsForTableFunction( routineInfo.getReturnType() );
 			}
 			else
@@ -1599,4 +1608,69 @@
         }
 
     }
+
+    /**
+     * Return true if this Derby Style Table Function implements the VTICosting
+     * interface. The class must satisfy the following conditions:
+     *
+     * <ul>
+     * <li>Implements VTICosting</li>
+     * <li>Has a public, no-arg constructor</li>
+     * </ul>
+     */
+    private boolean implementsDerbyStyleVTICosting( String className )
+        throws StandardException
+    {
+        Constructor     constructor = null;
+        Class           vtiClass = null;
+        
+        try {
+            vtiClass = Class.forName( className );
+
+            if ( !VTICosting.class.isAssignableFrom( vtiClass ) ) { return false; }
+        }
+        catch (Throwable t)
+        {
+            throw StandardException.unexpectedUserException( t );
+        }
+
+        try {
+            constructor = vtiClass.getConstructor( new Class[] {} );
+        }
+        catch (Throwable t)
+        {
+            throw StandardException.newException
+                ( SQLState.LANG_NO_COSTING_CONSTRUCTOR, t, className );
+        }
+        
+        if ( Modifier.isPublic( constructor.getModifiers() ) ) { return true; }
+
+        // Bad class. It thinks it implements VTICosting, but it doesn't
+        // have a public no-arg constructor
+        throw StandardException.newException
+            ( SQLState.LANG_NO_COSTING_CONSTRUCTOR, className );
+    }
+
+    /**
+     * Get the VTICosting implementation for this optimizable VTI.
+     */
+    private VTICosting  getVTICosting()
+        throws StandardException
+    {
+        if ( !isDerbyStyleTableFunction ) { return (version2) ? (VTICosting) ps : (VTICosting)
rs; }
+        
+        try {
+            String              className = methodCall.getJavaClassName();
+            Class               vtiClass = Class.forName( className );
+            Constructor         constructor = vtiClass.getConstructor( new Class[] {} );
+            VTICosting          result = (VTICosting) constructor.newInstance( null );
+
+            return result;
+        }
+        catch (Throwable t)
+        {
+            throw StandardException.unexpectedUserException( t );
+        }
+    }
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=574276&r1=574275&r2=574276&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Sep 10 08:09:35
2007
@@ -2517,6 +2517,12 @@
                 <arg>functionName</arg>
             </msg>
 
+            <msg>
+                <name>42ZB5</name>
+                <text>Class '{0}' implements VTICosting but does not provide a public,
no-arg constructor.</text>
+                <arg>className</arg>
+            </msg>
+
         </family>
 
 

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=574276&r1=574275&r2=574276&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Mon Sep 10 08:09:35 2007
@@ -1056,10 +1056,11 @@
     String LANG_LIKE_COLLATION_MISMATCH                               = "42ZA2";
     String LANG_CAN_NOT_CREATE_TABLE                               = "42ZA3";
 
-    String LANG_NO_DJRS                                                 = "42ZB1";
+    String LANG_NO_DJRS                                             = "42ZB1";
     String LANG_MUST_BE_DJRS                                        = "42ZB2";
-    String LANG_XML_NOT_ALLOWED_DJRS                    = "42ZB3";
-    String LANG_NOT_TABLE_FUNCTION                    = "42ZB4";
+    String LANG_XML_NOT_ALLOWED_DJRS                                = "42ZB3";
+    String LANG_NOT_TABLE_FUNCTION                                  = "42ZB4";
+    String LANG_NO_COSTING_CONSTRUCTOR                              = "42ZB5";
 
 	//following 3 matches the DB2 sql states
 	String LANG_DECLARED_GLOBAL_TEMP_TABLE_ONLY_IN_SESSION_SCHEMA = "428EK";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java?rev=574276&r1=574275&r2=574276&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
Mon Sep 10 08:09:35 2007
@@ -23,6 +23,9 @@
 
 import  java.sql.*;
 
+import org.apache.derby.vti.VTICosting;
+import org.apache.derby.vti.VTIEnvironment;
+
 /**
  * <p>
  * This is a concrete VTI which is prepopulated with rows which are just
@@ -37,12 +40,70 @@
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    public  static  final   double  FAKE_ROW_COUNT = 13.0;
+    public  static  final   double  FAKE_INSTANTIATION_COST = 3149.0;
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // INNER CLASSES
     //
     ///////////////////////////////////////////////////////////////////////////////////
 
+    //
+    // Inner classes for testing VTICosting api.
+    //
+    public  static  class   MissingConstructor  extends StringArrayVTI  implements VTICosting
+    {
+        private  MissingConstructor( String[] columnNames, String[][] rows ) { super( columnNames,
rows ); }
+
+        public  static  ResultSet   dummyVTI()
+        {
+            return new StringArrayVTI( new String[] { "foo" }, new String[][] { { "bar" }
} );
+        }
+        
+        public  double  getEstimatedRowCount( VTIEnvironment env ) throws SQLException
+        {
+            return FAKE_ROW_COUNT;
+        }
+        
+        public  double  getEstimatedCostPerInstantiation( VTIEnvironment env ) throws SQLException
+        {
+            return FAKE_INSTANTIATION_COST;
+        }
+        
+        public  boolean supportsMultipleInstantiations( VTIEnvironment env ) throws SQLException
+        {
+            return false;
+        }        
+    }
+    
+    public  static  class   ZeroArgConstructorNotPublic    extends MissingConstructor
+    {
+        ZeroArgConstructorNotPublic()
+        { super( new String[] { "foo" }, new String[][] { { "bar" } } ); }
+    }
+    
+    public  static  class   ConstructorException    extends ZeroArgConstructorNotPublic
+    {
+        public  ConstructorException()
+        {
+            super();
+
+            Object      shameOnYou = null;
+
+            // trip over a null pointer exception
+            shameOnYou.hashCode();
+        }
+    }
+    
+    public  static  class   GoodVTICosting    extends ZeroArgConstructorNotPublic
+    {
+        public  GoodVTICosting()
+        {
+            super();
+        }
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // STATE

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=574276&r1=574275&r2=574276&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
Mon Sep 10 08:09:35 2007
@@ -22,6 +22,7 @@
 package org.apache.derbyTesting.functionTests.tests.lang;
 
 import java.lang.reflect.*;
+import java.io.*;
 import java.sql.*;
 import java.util.ArrayList;
 
@@ -53,6 +54,10 @@
         "invert",
         "returnsACoupleRows",
         "returnsAllLegalDatatypes",
+        "missingConstructor",
+        "zeroArgConstructorNotPublic",
+        "constructorException",
+        "goodVTICosting",
     };
     
     private static  final   String[][]  SIMPLE_ROWS =
@@ -702,6 +707,9 @@
         },
     };
 
+    private static  final   String  ESTIMATED_ROW_COUNT = "optimizer estimated row count:";
+    private static  final   String  ESTIMATED_COST = "optimizer estimated cost:";
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // INNER CLASSES
@@ -784,6 +792,7 @@
         notTableFunction();
         simpleVTIResults();
         allLegalDatatypesVTIResults();
+        vtiCosting();
     }
     
     /**
@@ -863,7 +872,7 @@
     private void simpleDDL()
         throws Exception
     {
-        goodDDL
+        goodStatement
             (
              "create function simpleFunctionTable()\n" +
              "returns TABLE\n" +
@@ -894,7 +903,7 @@
     private void notTableFunction()
         throws Exception
     {
-        goodDDL
+        goodStatement
             (
              "create function invert( intValue int )\n" +
              "returns int\n" +
@@ -921,7 +930,7 @@
     private void  simpleVTIResults()
         throws Exception
     {
-        goodDDL
+        goodStatement
             (
              "create function returnsACoupleRows()\n" +
              "returns TABLE\n" +
@@ -950,7 +959,7 @@
     private void  allLegalDatatypesVTIResults()
         throws Exception
     {
-        goodDDL
+        goodStatement
             (
              "create function returnsAllLegalDatatypes( intArgument int, varcharArgument
varchar( 10 ) )\n" +
              "returns TABLE\n" +
@@ -1017,6 +1026,103 @@
         assertFunctionDBMD( "RETURNSALLLEGALDATATYPES", GF_RADT , GFC_RADT );
     }
     
+    /**
+     * Verify the VTICosting optimizer api.
+     */
+    private void vtiCosting()
+        throws Exception
+    {
+        //
+        // Doesn't have a public no-arg constructor.
+        //
+        goodStatement
+            (
+             "create function missingConstructor()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     varcharCol varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$MissingConstructor.dummyVTI'\n"
+             );
+        expectError
+            (
+             "42ZB5",
+             "select s.*\n" +
+             "    from TABLE( missingConstructor() ) s\n"
+             );
+
+        //
+        // Has a no-arg constructor but it isn't public.
+        //
+        goodStatement
+            (
+             "create function zeroArgConstructorNotPublic()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     varcharCol varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$ZeroArgConstructorNotPublic.dummyVTI'\n"
+             );
+        expectError
+            (
+             "42ZB5",
+             "select s.*\n" +
+             "    from TABLE( missingConstructor() ) s\n"
+             );
+
+        //
+        // Has a public, no-arg constructor but it raises an exception.
+        //
+        goodStatement
+            (
+             "create function constructorException()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     varcharCol varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$ConstructorException.dummyVTI'\n"
+             );
+        expectError
+            (
+             "38000",
+             "select s.*\n" +
+             "    from TABLE( constructorException() ) s\n"
+             );
+
+        //
+        // Good implementation of VTICosting. Verify that the optimizer costs
+        // are overridden.
+        //
+        goodStatement
+            (
+             "create function goodVTICosting()\n" +
+             "returns TABLE\n" +
+             "  (\n" +
+             "     varcharCol varchar( 10 )\n" +
+             "  )\n" +
+             "language java\n" +
+             "parameter style DERBY_JDBC_RESULT_SET\n" +
+             "no sql\n" +
+             "external name 'org.apache.derbyTesting.functionTests.tests.lang.StringArrayVTI$GoodVTICosting.dummyVTI'\n"
+             );
+        String      optimizerStats = getOptimizerStats
+            (
+             "select s.*\n" +
+             "    from TABLE( goodVTICosting() ) s\n"
+             );
+        assertEquals( StringArrayVTI.FAKE_ROW_COUNT, readDoubleTag( optimizerStats, ESTIMATED_ROW_COUNT
), 0.0 );
+        assertEquals( StringArrayVTI.FAKE_INSTANTIATION_COST, readDoubleTag( optimizerStats,
ESTIMATED_COST ), 0.0 );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // Derby FUNCTIONS
@@ -1091,9 +1197,9 @@
     /**
      * Run good DDL.
      */
-    private void    goodDDL( String ddl )
+    private void    goodStatement( String ddl )
     {
-        println( "Running good DDL:\n\t" + ddl );
+        println( "Running good statement:\n\t" + ddl );
         
         try {
             PreparedStatement    ps = prepareStatement( ddl );
@@ -1479,4 +1585,71 @@
         org.apache.derby.tools.JDBCDisplayUtil.DisplayResults
             ( System.out, rs, conn );
     }
+
+    //////////////////
+    //
+    // OPTIMIZER STATS
+    //
+    //////////////////
+
+    /**
+     * <p>
+     * Get the optimizer stats for a query.
+     * </p>
+     */
+    private String  getOptimizerStats( String query )
+        throws Exception
+    {
+        goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)" );
+        goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1)" );
+        goodStatement( query );
+
+        PreparedStatement   ps = prepareStatement( "values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()"
);
+        ResultSet           rs = ps.executeQuery();
+
+        rs.next();
+
+        String  retval = rs.getString( 1 );
+
+        rs.close();
+        ps.close();
+
+        goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(0)" );
+        goodStatement( "CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)" );
+
+        return retval;
+    }
+
+    
+    /**
+     * <p>
+     * Read the value of a tag in some optimizer output.
+     * </p>
+     */
+    private	double  readDoubleTag( String optimizerOutput, String tag )
+        throws Exception
+    {
+        StringReader        stringReader = new StringReader( optimizerOutput );
+        LineNumberReader    lineNumberReader = new LineNumberReader( stringReader );
+
+        while ( true )
+        {
+            String  line = lineNumberReader.readLine();
+            if ( line == null ) { break; }
+
+            int     idx = line.indexOf( tag );
+
+            if ( idx < 0 ) { continue; }
+
+            String  remnant = line.substring( idx + tag.length() );
+            double  result = Double.parseDouble( remnant );
+            
+            println( "Read " + result + " from optimizer output." );
+            return result;
+        }
+
+        return 0.0;
+    }
+
+
 }



Mime
View raw message