| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.XMLOptimizerTraceTest |
| |
| 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.functionTests.tests.lang; |
| |
| import java.io.File; |
| import java.net.URL; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import junit.framework.Test; |
| |
| import org.apache.derby.iapi.services.info.JVMInfo; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.SupportFilesSetup; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * <p> |
| * Test xml-based optimizer tracing, introduced by DERBY-6211. |
| * </p> |
| */ |
| public class XMLOptimizerTraceTest extends GeneratedColumnsHelper |
| { |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTANTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| private static final String TRACE_FILE_NAME = "xott.xml"; |
| private static final String SAVED_TRACE_NAME = "xmlOptimizer.trace"; |
| private static final String FILE_EXISTS = "XIE0S"; |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // STATE |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // CONSTRUCTOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * Create a new instance. |
| */ |
| |
| public XMLOptimizerTraceTest(String name) |
| { |
| super(name); |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // JUnit BEHAVIOR |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() |
| { |
| String[] testFiles = new String[] { "functionTests/tests/lang/" + SAVED_TRACE_NAME }; |
| |
| BaseTestSuite suite = new BaseTestSuite("XMLOptimizerTraceTest"); |
| |
| suite.addTest( TestConfiguration.defaultSuite( XMLOptimizerTraceTest.class ) ); |
| |
| return new SupportFilesSetup( TestConfiguration.singleUseDatabaseDecorator( suite ), testFiles ); |
| } |
| |
| protected void setUp() |
| throws Exception |
| { |
| super.setUp(); |
| |
| Connection conn = getConnection(); |
| |
| if ( !routineExists( conn, "INTEGERLIST" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create function integerList()\n" + |
| "returns table( a int, b int, c int, d int )\n" + |
| "language java parameter style derby_jdbc_result_set no sql\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.RestrictedVTITest.integerList'\n" |
| ); |
| } |
| |
| if ( !routineExists( conn, "GETRESULTSETMETADATA" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create function getResultSetMetaData( query varchar( 32672 ) )\n" + |
| "returns table\n" + |
| "(\n" + |
| " getCatalogName varchar( 32672 ),\n" + |
| " getColumnClassName varchar( 32672 ),\n" + |
| " getColumnDisplaySize int,\n" + |
| " getColumnLabel varchar( 32672 ),\n" + |
| " getColumnName varchar( 32672 ),\n" + |
| " getColumnType int,\n" + |
| " getColumnTypeName varchar( 32672 ),\n" + |
| " getPrecision int,\n" + |
| " getScale int,\n" + |
| " getSchemaName varchar( 32672 ),\n" + |
| " getTableName varchar( 32672 ),\n" + |
| " isAutoIncrement boolean,\n" + |
| " isCaseSensitive boolean,\n" + |
| " isCurrency boolean,\n" + |
| " isDefinitelyWritable boolean,\n" + |
| " isNullable int,\n" + |
| " isReadOnly boolean,\n" + |
| " isSearchable boolean,\n" + |
| " isSigned boolean,\n" + |
| " isWritable boolean\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set reads sql data\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.RSMDWrapper.getResultSetMetaData'\n" |
| ); |
| } |
| |
| if ( !tableExists( conn, "T" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table t( a int, b varchar( 100 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create index t_a on t( a )" |
| ); |
| } |
| |
| if ( !tableExists( conn, "S" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table s( a int, b varchar( 100 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create index s_a on s( a )" |
| ); |
| } |
| |
| if ( !tableExists( conn, "R" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table r( a int, b varchar( 100 ) )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create index r_a on r( a )" |
| ); |
| } |
| |
| if ( !tableExists( conn, "T1" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table t1( c1 int, c2 int, c3 int )" |
| ); |
| } |
| |
| if ( !tableExists( conn, "T2" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table t2( c1 int, c2 int, c3 int )" |
| ); |
| } |
| |
| if ( !tableExists( conn, "T3" ) ) |
| { |
| goodStatement |
| ( |
| conn, |
| "create table t3( c1 int, c2 int, c3 int )" |
| ); |
| } |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // TESTS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** |
| * <p> |
| * Test the planCost table function. |
| * </p> |
| */ |
| public void test_01_planCost() throws Exception |
| { |
| Connection conn = getConnection(); |
| File traceFile = SupportFilesSetup.getReadWrite( TRACE_FILE_NAME ); |
| |
| SupportFilesSetup.deleteFile( traceFile ); |
| |
| // turn on xml-based optimizer tracing and run some queries |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' )" |
| ); |
| |
| // 2-table query |
| goodStatement |
| ( |
| conn, |
| "select s.a from t, s where t.a = s.a" |
| ); |
| // 3-table query |
| goodStatement |
| ( |
| conn, |
| "select s.a from t, s, r where t.a = s.a and s.a = r.a" |
| ); |
| // query involving a table function |
| goodStatement |
| ( |
| conn, |
| "select s.a from s, table( integerList() ) i where s.a = i.a" |
| ); |
| |
| // turn off optimizer tracing and dump the xml trace to a file |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', false, '" + traceFile.getPath() + "' )" |
| ); |
| |
| // install the planCost table function and view |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracingViews', true, '" + traceFile.getPath() + "' )" |
| ); |
| |
| // verify the full signature of the planCost table function |
| assertResults |
| ( |
| conn, |
| "select getColumnName, getColumnTypeName, getPrecision from table( getResultSetMetaData( 'select * from planCost where 1=2' ) ) g", |
| new String[][] |
| { |
| { "TEXT", "VARCHAR", "32672" }, |
| { "STMTID", "INTEGER", "10" }, |
| { "QBID", "INTEGER", "10" }, |
| { "COMPLETE", "BOOLEAN", "1" }, |
| { "SUMMARY", "VARCHAR", "32672" }, |
| { "TYPE", "VARCHAR", "50" }, |
| { "ESTIMATEDCOST", "DOUBLE", "15" }, |
| { "ESTIMATEDROWCOUNT", "BIGINT", "19" }, |
| }, |
| false |
| ); |
| |
| // verify some contents of the xml output which we hope will remain stable |
| // across test platforms |
| assertResults |
| ( |
| conn, |
| "select distinct stmtID, summary from planCost where complete order by stmtID, summary", |
| new String[][] |
| { |
| { "1", "( \"APP\".\"S_A\" # \"APP\".\"T_A\" )" }, |
| { "1", "( \"APP\".\"T_A\" # \"APP\".\"S_A\" )" }, |
| { "2", "( ( \"APP\".\"R_A\" # \"APP\".\"S_A\" ) * \"APP\".\"T_A\" )" }, |
| { "2", "( ( \"APP\".\"R_A\" # \"APP\".\"T_A\" ) * \"APP\".\"S_A\" )" }, |
| { "2", "( ( \"APP\".\"S_A\" # \"APP\".\"R_A\" ) * \"APP\".\"T_A\" )" }, |
| { "2", "( ( \"APP\".\"S_A\" # \"APP\".\"T_A\" ) * \"APP\".\"R_A\" )" }, |
| { "2", "( ( \"APP\".\"T_A\" # \"APP\".\"R_A\" ) * \"APP\".\"S_A\" )" }, |
| { "2", "( ( \"APP\".\"T_A\" # \"APP\".\"S_A\" ) * \"APP\".\"R_A\" )" }, |
| { "3", "( \"APP\".\"INTEGERLIST\"() # \"APP\".\"S_A\" )" }, |
| { "3", "( \"APP\".\"S_A\" # \"APP\".\"INTEGERLIST\"() )" }, |
| }, |
| false |
| ); |
| |
| // uninstall the planCost table function and view |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracingViews', false )" |
| ); |
| |
| // use planCost to examine an outer join |
| vetOuterJoin( conn ); |
| |
| // verify that you can't overwrite an existing file with xml output (DERBY-6635) |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' )" |
| ); |
| expectExecutionError |
| ( |
| conn, |
| FILE_EXISTS, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', false, '" + traceFile.getPath() + "' )" |
| ); |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', false )" |
| ); |
| |
| } |
| |
| /** |
| * <p> |
| * Some general tests for XmlVTI. |
| * </p> |
| */ |
| public void test_02_xmlVTI() throws Exception |
| { |
| Connection conn = getConnection(); |
| File traceFile = SupportFilesSetup.getReadOnly( SAVED_TRACE_NAME ); |
| URL traceURL = SupportFilesSetup.getReadOnlyURL( SAVED_TRACE_NAME ); |
| String[][] resultsParentAndChild = new String[][] |
| { |
| { "1", "R_A", "HASH", "20.1395", "6" }, |
| { "1", "R_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| { "1", "S_A", "HASH", "20.1395", "6" }, |
| { "1", "S_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| { "1", "T_A", "HASH", "20.1395", "6" }, |
| { "1", "T_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| }; |
| String[][] resultsChildOnly = new String[][] |
| { |
| { "R_A", "HASH", "20.1395", "6" }, |
| { "R_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| { "S_A", "HASH", "20.1395", "6" }, |
| { "S_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| { "T_A", "HASH", "20.1395", "6" }, |
| { "T_A", "NESTEDLOOP", "20.039500000000004", "6" }, |
| }; |
| |
| // create the type and factory function needed by the XmlVTI |
| goodStatement |
| ( |
| conn, |
| "create type ArrayList external name 'java.util.ArrayList' language java" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create function asList( cell varchar( 32672 ) ... ) returns ArrayList\n" + |
| "language java parameter style derby no sql\n" + |
| "external name 'org.apache.derby.vti.XmlVTI.asList'\n" |
| ); |
| |
| // create an XmlVTI which reads from a file and incorporates parent tags |
| goodStatement |
| ( |
| conn, |
| "create function decorationWithParentInfo\n" + |
| "(\n" + |
| " fileName varchar( 32672 ),\n" + |
| " rowTag varchar( 32672 ),\n" + |
| " parentTags ArrayList,\n" + |
| " childTags ArrayList\n" + |
| ")\n" + |
| "returns table\n" + |
| "(\n" + |
| " qbID int,\n" + |
| " conglomerateName varchar( 36 ),\n" + |
| " joinStrategy varchar( 20 ),\n" + |
| " estimatedCost double,\n" + |
| " estimatedRowCount int\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set no sql\n" + |
| "external name 'org.apache.derby.vti.XmlVTI.xmlVTI'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create view decorationWithParentInfo as\n" + |
| "select * from table\n" + |
| "(\n" + |
| " decorationWithParentInfo\n" + |
| " (\n" + |
| " '" + traceFile.getPath() + "',\n" + |
| " 'decoration',\n" + |
| " asList( 'qbID' ),\n" + |
| " asList( 'decConglomerateName', 'decJoinStrategy', 'ceEstimatedCost', 'ceEstimatedRowCount' )\n" + |
| " )\n" + |
| ") v\n" |
| ); |
| |
| // create an XmlVTI which reads from a file and only used child tags |
| goodStatement |
| ( |
| conn, |
| "create function decorationChildOnly\n" + |
| "(\n" + |
| " fileName varchar( 32672 ),\n" + |
| " rowTag varchar( 32672 ),\n" + |
| " childTags varchar( 32672 )...\n" + |
| ")\n" + |
| "returns table\n" + |
| "(\n" + |
| " conglomerateName varchar( 36 ),\n" + |
| " joinStrategy varchar( 20 ),\n" + |
| " estimatedCost double,\n" + |
| " estimatedRowCount int\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set no sql\n" + |
| "external name 'org.apache.derby.vti.XmlVTI.xmlVTI'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create view decorationChildOnly as\n" + |
| "select * from table\n" + |
| "(\n" + |
| " decorationChildOnly\n" + |
| " (\n" + |
| " '" + traceFile.getPath() + "',\n" + |
| " 'decoration',\n" + |
| " 'decConglomerateName', 'decJoinStrategy', 'ceEstimatedCost', 'ceEstimatedRowCount'\n" + |
| " )\n" + |
| ") v\n" |
| ); |
| |
| // create an XmlVTI which reads from an url file and uses parent tags |
| goodStatement |
| ( |
| conn, |
| "create function decorationURLParentInfo\n" + |
| "(\n" + |
| " urlString varchar( 32672 ),\n" + |
| " rowTag varchar( 32672 ),\n" + |
| " parentTags ArrayList,\n" + |
| " childTags ArrayList\n" + |
| ")\n" + |
| "returns table\n" + |
| "(\n" + |
| " qbID int,\n" + |
| " conglomerateName varchar( 36 ),\n" + |
| " joinStrategy varchar( 20 ),\n" + |
| " estimatedCost double,\n" + |
| " estimatedRowCount int\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set no sql\n" + |
| "external name 'org.apache.derby.vti.XmlVTI.xmlVTIFromURL'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create view decorationURLParentInfo as\n" + |
| "select * from table\n" + |
| "(\n" + |
| " decorationURLParentInfo\n" + |
| " (\n" + |
| " '" + traceURL.toString() + "',\n" + |
| " 'decoration',\n" + |
| " asList( 'qbID' ),\n" + |
| " asList( 'decConglomerateName', 'decJoinStrategy', 'ceEstimatedCost', 'ceEstimatedRowCount' )\n" + |
| " )\n" + |
| ") v\n" |
| ); |
| |
| // create an XmlVTI which reads from an url file and uses only child tags |
| goodStatement |
| ( |
| conn, |
| "create function decorationURLChildOnly\n" + |
| "(\n" + |
| " urlString varchar( 32672 ),\n" + |
| " rowTag varchar( 32672 ),\n" + |
| " childTags varchar( 32672 )...\n" + |
| ")\n" + |
| "returns table\n" + |
| "(\n" + |
| " conglomerateName varchar( 36 ),\n" + |
| " joinStrategy varchar( 20 ),\n" + |
| " estimatedCost double,\n" + |
| " estimatedRowCount int\n" + |
| ")\n" + |
| "language java parameter style derby_jdbc_result_set no sql\n" + |
| "external name 'org.apache.derby.vti.XmlVTI.xmlVTIFromURL'\n" |
| ); |
| goodStatement |
| ( |
| conn, |
| "create view decorationURLChildOnly as\n" + |
| "select * from table\n" + |
| "(\n" + |
| " decorationURLChildOnly\n" + |
| " (\n" + |
| " '" + traceURL.toString() + "',\n" + |
| " 'decoration',\n" + |
| " 'decConglomerateName', 'decJoinStrategy', 'ceEstimatedCost', 'ceEstimatedRowCount'\n" + |
| " )\n" + |
| ") v\n" |
| ); |
| |
| // verify that the XmlVTIs work |
| assertResults |
| ( |
| conn, |
| "select distinct qbID, conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n" + |
| "from decorationWithParentInfo\n" + |
| "where conglomerateName like '%_A' and estimatedCost is not null\n" + |
| "order by qbID, conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n", |
| resultsParentAndChild, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select distinct conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n" + |
| "from decorationChildOnly\n" + |
| "where conglomerateName like '%_A' and estimatedCost is not null\n" + |
| "order by conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n", |
| resultsChildOnly, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select distinct qbID, conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n" + |
| "from decorationURLParentInfo\n" + |
| "where conglomerateName like '%_A' and estimatedCost is not null\n" + |
| "order by qbID, conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n", |
| resultsParentAndChild, |
| false |
| ); |
| |
| assertResults |
| ( |
| conn, |
| "select distinct conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n" + |
| "from decorationURLChildOnly\n" + |
| "where conglomerateName like '%_A' and estimatedCost is not null\n" + |
| "order by conglomerateName, joinStrategy, estimatedCost, estimatedRowCount\n", |
| resultsChildOnly, |
| false |
| ); |
| |
| // clean up after ourselves |
| goodStatement( conn, "drop view decorationURLChildOnly" ); |
| goodStatement( conn, "drop function decorationURLChildOnly" ); |
| goodStatement( conn, "drop view decorationURLParentInfo" ); |
| goodStatement( conn, "drop function decorationURLParentInfo" ); |
| goodStatement( conn, "drop view decorationChildOnly" ); |
| goodStatement( conn, "drop function decorationChildOnly" ); |
| goodStatement( conn, "drop view decorationWithParentInfo" ); |
| goodStatement( conn, "drop function decorationWithParentInfo" ); |
| goodStatement( conn, "drop function asList" ); |
| goodStatement( conn, "drop type ArrayList restrict" ); |
| } |
| |
| |
| /** |
| * <p> |
| * Test xml optimizer tracing of outer joins. |
| * </p> |
| */ |
| private void vetOuterJoin( Connection conn ) throws Exception |
| { |
| File traceFile = SupportFilesSetup.getReadWrite( TRACE_FILE_NAME ); |
| SupportFilesSetup.deleteFile( traceFile ); |
| |
| // turn on xml-based optimizer tracing |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' )" |
| ); |
| |
| // run an outer join |
| goodStatement |
| ( |
| conn, |
| "select * from t3, (t1 left outer join t2 on t1.c1 = t2.c1) where t3.c1 = t1.c1" |
| ); |
| |
| // turn off optimizer tracing |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracing', false, '" + traceFile.getPath() + "' )" |
| ); |
| |
| // load the trace viewer |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracingViews', true, '" + traceFile.getPath() + "' )" |
| ); |
| |
| // verify the plan shapes which were considered |
| PreparedStatement ps = chattyPrepare |
| ( |
| conn, |
| "select distinct summary from planCost\n" + |
| "where complete and qbID = 1\n" + |
| "order by summary\n" |
| ); |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| String summary1 = rs.getString( 1 ).trim(); |
| rs.next(); |
| String summary2 = rs.getString( 1 ).trim(); |
| assertTrue( summary1.startsWith( "( \"APP\"." ) ); |
| assertTrue( summary1.endsWith( " * ProjectRestrictNode )" ) ); |
| assertTrue( summary2.startsWith( "( ProjectRestrictNode # \"APP\"." ) ); |
| rs.close(); |
| ps.close(); |
| |
| // unload the trace viewer |
| goodStatement |
| ( |
| conn, |
| "call syscs_util.syscs_register_tool( 'optimizerTracingViews', false )" |
| ); |
| |
| } |
| |
| /////////////////////////////////////////////////////////////////////////////////// |
| // |
| // MINIONS |
| // |
| /////////////////////////////////////////////////////////////////////////////////// |
| |
| /** Return true if the SQL routine exists */ |
| private boolean routineExists( Connection conn, String functionName ) throws Exception |
| { |
| PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.sysaliases where alias = ?" ); |
| ps.setString( 1, functionName ); |
| |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| |
| boolean retval = rs.getInt( 1 ) > 0 ? true : false; |
| |
| rs.close(); |
| ps.close(); |
| |
| return retval; |
| } |
| |
| /** Return true if the table exists */ |
| private boolean tableExists( Connection conn, String tableName ) throws Exception |
| { |
| PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.systables where tablename = ?" ); |
| ps.setString( 1, tableName ); |
| |
| ResultSet rs = ps.executeQuery(); |
| rs.next(); |
| |
| boolean retval = rs.getInt( 1 ) > 0 ? true : false; |
| |
| rs.close(); |
| ps.close(); |
| |
| return retval; |
| } |
| |
| } |
