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 ab...@apache.org on 2007/03/02 01:47:48 UTC
svn commit: r513562 [1/2] - in /db/derby/code/trunk/java/testing: ./
org/apache/derbyTesting/system/optimizer/
org/apache/derbyTesting/system/optimizer/query/
org/apache/derbyTesting/system/optimizer/utils/
Author: abrown
Date: Thu Mar 1 16:47:47 2007
New Revision: 513562
URL: http://svn.apache.org/viewvc?view=rev&rev=513562
Log:
DERBY-2249 (partial): Initial commit of files for a new test "toolkit" with
the following potential uses:
- run a lot of optimizations to look for memory leaks, etc. in the optimizer
- run queries that take a long time to optimize, to try to understand why
- run queries that are quite complex, to determine whether we select the
"best" query plan
- run queries that take a long time to execute
- run queries on different versions to see the performance gain/loss
Contributed by Manjula Kutty (mkutty@remulak.net)
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestViews.java (with props)
Modified:
db/derby/code/trunk/java/testing/build.xml
Modified: db/derby/code/trunk/java/testing/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/build.xml?view=diff&rev=513562&r1=513561&r2=513562
==============================================================================
--- db/derby/code/trunk/java/testing/build.xml (original)
+++ db/derby/code/trunk/java/testing/build.xml Thu Mar 1 16:47:47 2007
@@ -81,6 +81,7 @@
<ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/master"/>
<ant dir="${derby.testing.src.dir}/${derby.testing.suites.dir}"/>
<ant dir="${derby.testing.src.dir}/${derby.testing.system.dir}/oe"/>
+ <ant dir="${derby.testing.src.dir}/${derby.testing.system.dir}/optimizer"/>
<ant dir="${derby.testing.src.dir}/${derby.testing.perf.dir}"/>
</target>
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,151 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.RunLangTest
+
+ 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.system.optimizer;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+
+import org.apache.derbyTesting.system.optimizer.query.GenericQuery;
+import org.apache.derbyTesting.system.optimizer.query.QueryList;
+import org.apache.derbyTesting.system.optimizer.utils.DataUtils;
+import org.apache.derbyTesting.system.optimizer.utils.TestViews;
+/**
+ *
+ * Class RunLangTest: The main class that runs this test.
+ *
+ * To run the test use:
+ *
+ * java org.apache.derbyTesting.system.langtest.RunLangTest -reset|-qlist
+ * -reset = Reset the database and begin run
+ * -qlist = Run only test queries from the 'query.list' file provided
+ * No arguments will run all the default test queries, provided via classes
+ * Query1 - Query6 in this test case.
+ *
+ * Set the 'derby.langtest.mode' to 'client' to run this test using the
+ * DerbyClient against a Derby Network Server running on port 1527
+ */
+
+public class RunLangTest {
+
+ public static void main(String[] args) {
+ Connection conn = null;
+ String driverClass=StaticValues.embedClass;
+ String jdbcurl=StaticValues.embedURL;
+ boolean reset=false;
+ try {
+ String mode=System.getProperty("derby.langtest.mode");
+ if(mode!=null){
+ if(mode.equalsIgnoreCase("client")){
+ driverClass=StaticValues.clientClass;
+ jdbcurl=StaticValues.clientURL;
+ }else{
+ driverClass=StaticValues.embedClass;
+ jdbcurl=StaticValues.embedURL;
+ }
+ }else{
+
+ File dir = new File("testdb");
+ if((!dir.exists())){
+ reset=true;
+ }
+ }
+ System.out.println("Running test with url "+jdbcurl);
+ if(args.length>0){
+ if(args[0].equalsIgnoreCase("-reset"))
+ reset=true;
+ else if(args[0].equalsIgnoreCase("-qlist")){
+ QueryList.queryListOnly=true;
+ }else{
+ printUsage();
+ return;
+ }
+ }
+ Class.forName(driverClass);
+ if (reset) {
+ System.out.println("Initializing...");
+ conn = DriverManager.getConnection(jdbcurl);
+ TestViews.init();
+ DataUtils.dropObjects(conn);
+ DataUtils.createObjects(conn);
+ }else{
+ conn = DriverManager.getConnection(jdbcurl);
+ }
+ DataUtils.insertData(conn);
+ QueryList.init(conn);
+ System.out.println(" List of query scenarios to run: "+QueryList.getQList().size());
+ for(int i=0;i<QueryList.getQList().size();i++){
+ System.out.println("\n______________________________________________________________________\n");
+ GenericQuery gq=(GenericQuery)QueryList.getQList().get(i);
+ System.out.println("*** Running query: "+gq.getDescription()+" ***");
+ conn=null; //conn.close() throws "Invalid transaction state" exception
+ conn = DriverManager.getConnection(jdbcurl);
+ gq.setConnection(conn);
+ gq.executeQueries(false); //using regular STATEMENTS
+ conn.close();
+ conn=null; //conn.close() throws "Invalid transaction state" exception
+ conn = DriverManager.getConnection(jdbcurl);
+ gq.setConnection(conn);
+ gq.executeQueries(true); //using prepared STATEMENTS
+
+ }
+ } catch (ClassNotFoundException cne) {
+ System.out.println("Class not found Exception: " + cne.getMessage());
+ } catch (SQLException sqe) {
+ System.out.println("SQL Exception :" + sqe);
+
+ sqe.printStackTrace();
+ }catch (Exception e){
+ System.out.println("Unexpected Exception "+e);
+ e.printStackTrace();
+ }
+ printResults();
+ }
+ private static void printUsage(){
+ System.out.println("Usage:");
+ System.out.println("\njava org.apache.derbyTesting.system.langtest.RunLangTest -reset|-qlist\n");
+ System.out.println("-reset = Reset the database and begin run");
+ System.out.println("-qlist = Run only test queries from the 'query.list' file provided");
+ System.out.println("\nNo arguments will run all the default test queries available in this test case.\n");
+ }
+ private static void printResults(){
+ System.out.println("\n\n========================= R E S U L T S =========================\n");
+ for(int i=0;i<QueryList.getQList().size();i++){
+ System.out.println("\n________________________________________________________________________________________________");
+ GenericQuery gq=(GenericQuery)QueryList.getQList().get(i);
+ System.out.println("Timings for Query type: "+gq.getDescription()+"\n");
+ System.out.println("QueryName\tUsing PreparedStatment\tUsing Statement\tRows Expected");
+ System.out.println("------------------------------------------------------------------------------");
+
+ for(int k=0;k<gq.getQueries().size();k++){
+ String queryName="QUERY # "+(k+1);
+ String [] prepStmtTimes=(String [])gq.getPrepStmtRunResults().get(k);
+ String [] stmtTimes=(String [])gq.getStmtRunResults().get(k);
+ for(int j=0; j<StaticValues.ITER;j++){
+ System.out.println(queryName+"\t"+prepStmtTimes[j]+"\t\t"+stmtTimes[j]+"\t"+gq.getRowsExpected(k));
+ }
+ System.out.println("*************************************************************************");
+ }
+ System.out.println("\n________________________________________________________________________________________________");
+ }
+ }
+}
\ No newline at end of file
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/RunLangTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,56 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.StaticValues
+
+ 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.system.optimizer;
+/**
+ *
+ * Class StaticValues: A location to store all the common static values used in
+ * this test
+ *
+ */
+public class StaticValues {
+ public static String clientURL="jdbc:derby://localhost:1527/testdb;create=true";
+ public static String clientClass="org.apache.derby.jdbc.ClientDriver";
+
+ public static String embedURL="jdbc:derby:testdb;create=true";
+ public static String embedClass="org.apache.derby.jdbc.EmbeddedDriver";
+
+ public static int NUM_OF_ROWS=1000; //Total number of rows expected in each table
+ public static int NUM_OF_TABLES=64; //Total number of tables to be created
+ public static int ITER=2; //Number of iterations of each query
+
+ public static String queryFile="query.list"; //File name that contains the custom queries
+ //SCHEMA OBJECTS
+ public static String DROP_TABLE="DROP TABLE ";
+ public static String CREATE_TABLE="CREATE TABLE ";
+ public static String TABLE_NAME="MYTABLE";
+ public static String TABLE_COLS="(col1 INT primary key, col2 VARCHAR(100),col3 VARCHAR(100),col4 VARCHAR(30),col5 VARCHAR(30),col6 varchar(30),col7 VARCHAR(40), col8 INT, col9 timestamp)";
+ public static String CREATE_VIEW="CREATE VIEW ";
+ public static String VIEW1_COLS="col1, col2, col3, col4, col5, col6, col7 from ";
+ public static String VIEW2_COLS="col1, col2, col3, col4, col5, col6, col7, col8, col9 from ";
+
+ //INSERT
+ public static String INSERT_TABLE="INSERT INTO ";
+ public static String INSERT_VALUES=" VALUES(?,?,?,?,?,?,?, ?, ?) ";
+
+ public static void init(){
+ //TODO Load from property file
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/StaticValues.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml Thu Mar 1 16:47:47 2007
@@ -0,0 +1,85 @@
+<?xml version="1.0"?>
+<!--
+ 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.
+-->
+
+<!-- ==================================================================== -->
+<!-- Derby build file -->
+<!-- ==================================================================== -->
+
+<project default="optimizer" basedir="../../../../../..">
+
+<!-- ==================================================================== -->
+<!-- Set properties -->
+<!-- ==================================================================== -->
+
+ <property name="properties.dir" value="tools/ant/properties"/>
+
+ <!-- User settings -->
+ <property file="${user.home}/ant.properties"/>
+
+ <!-- Significant dirs -->
+ <property file="${properties.dir}/dirs.properties"/>
+ <property file="${properties.dir}/derbytesting.properties"/>
+
+ <!-- Compiler settings -->
+ <property file="${properties.dir}/defaultcompiler.properties"/>
+ <property file="${properties.dir}/${build.compiler}.properties"/>
+
+ <!-- Parser properties -->
+ <property file="${properties.dir}/parser.properties"/>
+
+ <!-- Compile-time classpath properties files -->
+ <property file="${properties.dir}/extrapath.properties"/>
+ <property file="${properties.dir}/compilepath.properties"/>
+ <property file="${user.home}/properties/derbytesting.properties"/>
+ <property file="${ant.home}/properties/derbytesting.properties"/>
+
+ <!-- Release and Version info -->
+ <property file="${properties.dir}/release.properties"/>
+
+<!-- ============ Begin Targets ============== -->
+
+ <target name="optimizer"
+ description="test for optimizer">
+ <javac
+ source="1.4"
+ target="1.4"
+ bootclasspath="${empty}"
+ nowarn="on"
+ debug="true"
+ depend="${depend}"
+ deprecation="${deprecation}"
+ optimize="${optimize}"
+ proceed="${proceed}"
+ verbose="${verbose}"
+ srcdir="${derby.testing.src.dir}"
+ destdir="${out.dir}">
+ <classpath>
+ <pathelement path="${java14compile.classpath}"/>
+ <pathelement path="${junit}"/>
+ </classpath>
+ <include name="${derby.testing.system.dir}/optimizer/**/*.java"/>
+ </javac>
+
+
+ </target>
+
+<!-- ============= End Targets ============== -->
+
+<!-- ============= End Project ============== -->
+
+</project>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/build.xml
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,151 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.GenericQuery;
+
+ 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.system.optimizer.query;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Enumeration;
+import java.util.Properties;
+
+import org.apache.derbyTesting.system.optimizer.StaticValues;
+import org.apache.derbyTesting.system.optimizer.utils.TestUtils;
+/**
+ *
+ * Class GenericQuery: The generic class that is extended by the Query classes or instantiated
+ * when the 'query.list' of custom queries is provided
+ *
+ */
+
+
+
+public class GenericQuery {
+ protected String description="Custom Test Query";
+ protected Connection conn=null;
+ protected ArrayList queries=new ArrayList();
+ protected ArrayList prepStmtRunResults=new ArrayList(); //times using PreparedStatement
+ protected ArrayList stmtRunResults=new ArrayList(); //times using Statement
+ protected int[] rowsExpected=null; //add rows expected
+
+ public void setConnection(Connection con){
+ conn=con;
+ }
+ public void generateQueries(){
+
+ }
+ public void generateQueries(Properties prop){
+ Enumeration qenum=prop.keys();
+ while(qenum.hasMoreElements()){
+ String queryName=(String)qenum.nextElement();
+ queries.add(prop.get(queryName));
+ }
+ }
+
+ public String getDescription(){
+ return description;
+ }
+ public void executeQueries(boolean prepare) throws SQLException{
+ rowsExpected=new int[queries.size()]; //initialize the array with correct size
+ String query="";
+ if(prepare){
+ System.out.println("=====================> Using java.sql.PreparedStatement <====================");
+ }else{
+ System.out.println("=====================> Using java.sql.Statement <====================");
+
+ }
+ try{
+ for(int k=0;k<queries.size();k++){
+
+ query=(String)queries.get(k);
+ String [] times=new String [StaticValues.ITER];
+ int rowsReturned=0;
+ for (int i=0;i<StaticValues.ITER;i++){
+
+ Statement stmt=null;
+ ResultSet rs=null;
+ PreparedStatement pstmt=null;
+ if(prepare){
+ pstmt=conn.prepareStatement(query);
+ }else{
+ stmt=conn.createStatement();
+
+ }
+ long start=System.currentTimeMillis();
+ if(prepare)
+ rs=pstmt.executeQuery();
+ else
+ rs=stmt.executeQuery(query);
+ ResultSetMetaData rsmd=rs.getMetaData();
+ int totalCols=rsmd.getColumnCount();
+
+ while(rs.next()){
+ String row="";
+ for(int j=1;j<=totalCols;j++){
+ row+=rs.getString(j)+" | ";
+ }
+ rowsReturned++;
+ }
+ long time_taken=(System.currentTimeMillis() - start);
+ System.out.println("Time required to execute:");
+ System.out.println(query);
+ System.out.println("Total Rows returned = "+rowsReturned);
+
+ System.out.println("==> "+time_taken+" milliseconds "+" OR "+TestUtils.getTime(time_taken));
+ times[i]=TestUtils.getTime(time_taken);
+ rs.close();
+ if(prepare){
+ pstmt.close();
+ }else{
+ stmt.close();
+ }
+ rowsExpected[k]=rowsReturned;//add expected rows for respective queries
+ rowsReturned=0;
+ }//end for loop to run StaticValues.ITER times
+
+ if(prepare){
+ prepStmtRunResults.add(times);
+ }else{
+ stmtRunResults.add(times);
+ }
+
+ }
+ }catch(SQLException sqe){
+ throw new SQLException("Failed query:\n "+query+"\n SQLState= "+sqe.getSQLState()+"\n ErrorCode= "+sqe.getErrorCode()+"\n Message= "+sqe.getMessage());
+ }
+ }
+ public ArrayList getPrepStmtRunResults() {
+ return prepStmtRunResults;
+ }
+ public ArrayList getStmtRunResults() {
+ return stmtRunResults;
+ }
+ public int getRowsExpected(int index) {
+ return rowsExpected[index];
+ }
+ public ArrayList getQueries() {
+ return queries;
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/GenericQuery.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,51 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.Query1
+
+ 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.system.optimizer.query;
+
+/**
+ *
+ * Class Query1: Returns a list of queries that Selects from a single view
+ *
+ */
+
+public class Query1 extends GenericQuery {
+
+ public Query1() {
+ description = "Select from single view";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select col1,col2,col3,col5,col6 from v8 where col4 in ('MYTABLE32_COL4:4122','MYTABLE32_COL4:3419','MYTABLE1_COL4:2197','MYTABLE1_COL4:1243','MYTABLE2_COL4:3684','MYTABLE2_COL4:4264','MYTABLE3_COL4:450','MYTABLE3_COL4:2150','MYTABLE4_COL4:966','MYTABLE4_COL4:2812','MYTABLE5_COL4:4897','MYTABLE5_COL4:2748','MYTABLE6_COL4:245','MYTABLE6_COL4:2341','MYTABLE7_COL4:1603','MYTABLE7_COL4:1150','MYTABLE8_COL4:4759','MYTABLE8_COL4:1535','MYTABLE9_COL4:1227','MYTABLE9_COL4:229','MYTABLE10_COL4:549','MYTABLE10_COL4:4043','MYTABLE11_COL4:1400','MYTABLE11_COL4:3964','MYTABLE12_COL4:3141','MYTABLE12_COL4:2808','MYTABLE13_COL4:2008','MYTABLE13_COL4:3835','MYTABLE14_COL4:3897','MYTABLE14_COL4:246','MYTABLE15_COL4:1284','MYTABLE15_COL4:3715','MYTABLE16_COL4:2583','MYTABLE16_COL4:4507','MYTABLE17_COL4:2899','MYTABLE17_COL4:1670','MYTABLE18_COL4:2187','MYTABLE18_COL4:175','MYTABLE19_COL4:3783','MYTABLE19_COL4:1525','MYTABLE20_COL4:3398','MYTABLE20_COL4:1568','MYTABLE21_COL4:3148','MY
TABLE21_COL4:2262','MYTABLE22_COL4:2815','MYTABLE22_COL4:2413','MYTABLE23_COL4:746','MYTABLE23_COL4:4357','MYTABLE24_COL4:1361','MYTABLE24_COL4:564','MYTABLE25_COL4:1427','MYTABLE25_COL4:1568','MYTABLE26_COL4:3707','MYTABLE26_COL4:1986','MYTABLE27_COL4:2771','MYTABLE27_COL4:3322','MYTABLE28_COL4:4485','MYTABLE28_COL4:3905','MYTABLE29_COL4:4142','MYTABLE29_COL4:3812','MYTABLE30_COL4:2724','MYTABLE30_COL4:1380','MYTABLE31_COL4:3702','MYTABLE31_COL4:803' )");
+ queries
+ .add("select col1,col2,col3,col5,col6 from v16 where col4 in ('MYTABLE32_COL4:4122','MYTABLE32_COL4:3419','MYTABLE1_COL4:2197','MYTABLE1_COL4:1243','MYTABLE2_COL4:3684','MYTABLE2_COL4:4264','MYTABLE3_COL4:450','MYTABLE3_COL4:2150','MYTABLE4_COL4:966','MYTABLE4_COL4:2812','MYTABLE5_COL4:4897','MYTABLE5_COL4:2748','MYTABLE6_COL4:245','MYTABLE6_COL4:2341','MYTABLE7_COL4:1603','MYTABLE7_COL4:1150','MYTABLE8_COL4:4759','MYTABLE8_COL4:1535','MYTABLE9_COL4:1227','MYTABLE9_COL4:229','MYTABLE10_COL4:549','MYTABLE10_COL4:4043','MYTABLE11_COL4:1400','MYTABLE11_COL4:3964','MYTABLE12_COL4:3141','MYTABLE12_COL4:2808','MYTABLE13_COL4:2008','MYTABLE13_COL4:3835','MYTABLE14_COL4:3897','MYTABLE14_COL4:246','MYTABLE15_COL4:1284','MYTABLE15_COL4:3715','MYTABLE16_COL4:2583','MYTABLE16_COL4:4507','MYTABLE17_COL4:2899','MYTABLE17_COL4:1670','MYTABLE18_COL4:2187','MYTABLE18_COL4:175','MYTABLE19_COL4:3783','MYTABLE19_COL4:1525','MYTABLE20_COL4:3398','MYTABLE20_COL4:1568','MYTABLE21_COL4:3148','M
YTABLE21_COL4:2262','MYTABLE22_COL4:2815','MYTABLE22_COL4:2413','MYTABLE23_COL4:746','MYTABLE23_COL4:4357','MYTABLE24_COL4:1361','MYTABLE24_COL4:564','MYTABLE25_COL4:1427','MYTABLE25_COL4:1568','MYTABLE26_COL4:3707','MYTABLE26_COL4:1986','MYTABLE27_COL4:2771','MYTABLE27_COL4:3322','MYTABLE28_COL4:4485','MYTABLE28_COL4:3905','MYTABLE29_COL4:4142','MYTABLE29_COL4:3812','MYTABLE30_COL4:2724','MYTABLE30_COL4:1380','MYTABLE31_COL4:3702','MYTABLE31_COL4:803' )");
+ queries
+ .add("select col1,col2,col3,col5,col6 from v32 where col4 in ('MYTABLE32_COL4:4122','MYTABLE32_COL4:3419','MYTABLE1_COL4:2197','MYTABLE1_COL4:1243','MYTABLE2_COL4:3684','MYTABLE2_COL4:4264','MYTABLE3_COL4:450','MYTABLE3_COL4:2150','MYTABLE4_COL4:966','MYTABLE4_COL4:2812','MYTABLE5_COL4:4897','MYTABLE5_COL4:2748','MYTABLE6_COL4:245','MYTABLE6_COL4:2341','MYTABLE7_COL4:1603','MYTABLE7_COL4:1150','MYTABLE8_COL4:4759','MYTABLE8_COL4:1535','MYTABLE9_COL4:1227','MYTABLE9_COL4:229','MYTABLE10_COL4:549','MYTABLE10_COL4:4043','MYTABLE11_COL4:1400','MYTABLE11_COL4:3964','MYTABLE12_COL4:3141','MYTABLE12_COL4:2808','MYTABLE13_COL4:2008','MYTABLE13_COL4:3835','MYTABLE14_COL4:3897','MYTABLE14_COL4:246','MYTABLE15_COL4:1284','MYTABLE15_COL4:3715','MYTABLE16_COL4:2583','MYTABLE16_COL4:4507','MYTABLE17_COL4:2899','MYTABLE17_COL4:1670','MYTABLE18_COL4:2187','MYTABLE18_COL4:175','MYTABLE19_COL4:3783','MYTABLE19_COL4:1525','MYTABLE20_COL4:3398','MYTABLE20_COL4:1568','MYTABLE21_COL4:3148','M
YTABLE21_COL4:2262','MYTABLE22_COL4:2815','MYTABLE22_COL4:2413','MYTABLE23_COL4:746','MYTABLE23_COL4:4357','MYTABLE24_COL4:1361','MYTABLE24_COL4:564','MYTABLE25_COL4:1427','MYTABLE25_COL4:1568','MYTABLE26_COL4:3707','MYTABLE26_COL4:1986','MYTABLE27_COL4:2771','MYTABLE27_COL4:3322','MYTABLE28_COL4:4485','MYTABLE28_COL4:3905','MYTABLE29_COL4:4142','MYTABLE29_COL4:3812','MYTABLE30_COL4:2724','MYTABLE30_COL4:1380','MYTABLE31_COL4:3702','MYTABLE31_COL4:803' )");
+ queries
+ .add("select col1,col2,col3,col5,col6 from v42 where col4 in ('MYTABLE32_COL4:4122','MYTABLE32_COL4:3419','MYTABLE1_COL4:2197','MYTABLE1_COL4:1243','MYTABLE2_COL4:3684','MYTABLE2_COL4:4264','MYTABLE3_COL4:450','MYTABLE3_COL4:2150','MYTABLE4_COL4:966','MYTABLE4_COL4:2812','MYTABLE5_COL4:4897','MYTABLE5_COL4:2748','MYTABLE6_COL4:245','MYTABLE6_COL4:2341','MYTABLE7_COL4:1603','MYTABLE7_COL4:1150','MYTABLE8_COL4:4759','MYTABLE8_COL4:1535','MYTABLE9_COL4:1227','MYTABLE9_COL4:229','MYTABLE10_COL4:549','MYTABLE10_COL4:4043','MYTABLE11_COL4:1400','MYTABLE11_COL4:3964','MYTABLE12_COL4:3141','MYTABLE12_COL4:2808','MYTABLE13_COL4:2008','MYTABLE13_COL4:3835','MYTABLE14_COL4:3897','MYTABLE14_COL4:246','MYTABLE15_COL4:1284','MYTABLE15_COL4:3715','MYTABLE16_COL4:2583','MYTABLE16_COL4:4507','MYTABLE17_COL4:2899','MYTABLE17_COL4:1670','MYTABLE18_COL4:2187','MYTABLE18_COL4:175','MYTABLE19_COL4:3783','MYTABLE19_COL4:1525','MYTABLE20_COL4:3398','MYTABLE20_COL4:1568','MYTABLE21_COL4:3148','M
YTABLE21_COL4:2262','MYTABLE22_COL4:2815','MYTABLE22_COL4:2413','MYTABLE23_COL4:746','MYTABLE23_COL4:4357','MYTABLE24_COL4:1361','MYTABLE24_COL4:564','MYTABLE25_COL4:1427','MYTABLE25_COL4:1568','MYTABLE26_COL4:3707','MYTABLE26_COL4:1986','MYTABLE27_COL4:2771','MYTABLE27_COL4:3322','MYTABLE28_COL4:4485','MYTABLE28_COL4:3905','MYTABLE29_COL4:4142','MYTABLE29_COL4:3812','MYTABLE30_COL4:2724','MYTABLE30_COL4:1380','MYTABLE31_COL4:3702','MYTABLE31_COL4:803' )");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query1.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,52 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.Query2
+
+ 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.system.optimizer.query;
+
+/**
+ *
+ * Class Query2: Returns a list of queries that Selects from multiple views
+ * using joins
+ *
+ */
+
+public class Query2 extends GenericQuery {
+
+ public Query2() {
+ description = "Select from multiple views using joins";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select xx.col1,xx.col2,xx.col3,xx.col5,xx.col6 from v8 xx, v8_2 xy where xx.col4=xy.col4 and xy.col7 in ('MYTABLE32_COL7:4122','MYTABLE32_COL7:3419','MYTABLE1_COL7:2197','MYTABLE1_COL7:1243','MYTABLE2_COL7:3684','MYTABLE2_COL7:4264','MYTABLE3_COL7:450','MYTABLE3_COL7:2150','MYTABLE4_COL7:966','MYTABLE4_COL7:2812','MYTABLE5_COL7:4897','MYTABLE5_COL7:2748','MYTABLE6_COL7:245','MYTABLE6_COL7:2341','MYTABLE7_COL7:1603','MYTABLE7_COL7:1150','MYTABLE8_COL7:4759','MYTABLE8_COL7:1535','MYTABLE9_COL7:1227','MYTABLE9_COL7:229','MYTABLE10_COL7:549','MYTABLE10_COL7:4043','MYTABLE11_COL7:1400','MYTABLE11_COL7:3964','MYTABLE12_COL7:3141','MYTABLE12_COL7:2808','MYTABLE13_COL7:2008','MYTABLE13_COL7:3835','MYTABLE14_COL7:3897','MYTABLE14_COL7:246','MYTABLE15_COL7:1284','MYTABLE15_COL7:3715','MYTABLE16_COL7:2583','MYTABLE16_COL7:4507','MYTABLE17_COL7:2899','MYTABLE17_COL7:1670','MYTABLE18_COL7:2187','MYTABLE18_COL7:175','MYTABLE19_COL7:3783','MYTABLE19_COL7:1525','MYTABLE20_COL7:339
8','MYTABLE20_COL7:1568','MYTABLE21_COL7:3148','MYTABLE21_COL7:2262','MYTABLE22_COL7:2815','MYTABLE22_COL7:2413','MYTABLE23_COL7:746','MYTABLE23_COL7:4357','MYTABLE24_COL7:1361','MYTABLE24_COL7:564','MYTABLE25_COL7:1427','MYTABLE25_COL7:1568','MYTABLE26_COL7:3707','MYTABLE26_COL7:1986','MYTABLE27_COL7:2771','MYTABLE27_COL7:3322','MYTABLE28_COL7:4485','MYTABLE28_COL7:3905','MYTABLE29_COL7:4142','MYTABLE29_COL7:3812','MYTABLE30_COL7:2724','MYTABLE30_COL7:1380','MYTABLE31_COL7:3702','MYTABLE31_COL7:803' )");
+ queries
+ .add("select xx.col1,xx.col2,xx.col3,xx.col5,xx.col6 from v16 xx, v16_2 xy where xx.col4=xy.col4 and xy.col7 in ('MYTABLE32_COL7:4122','MYTABLE32_COL7:3419','MYTABLE1_COL7:2197','MYTABLE1_COL7:1243','MYTABLE2_COL7:3684','MYTABLE2_COL7:4264','MYTABLE3_COL7:450','MYTABLE3_COL7:2150','MYTABLE4_COL7:966','MYTABLE4_COL7:2812','MYTABLE5_COL7:4897','MYTABLE5_COL7:2748','MYTABLE6_COL7:245','MYTABLE6_COL7:2341','MYTABLE7_COL7:1603','MYTABLE7_COL7:1150','MYTABLE8_COL7:4759','MYTABLE8_COL7:1535','MYTABLE9_COL7:1227','MYTABLE9_COL7:229','MYTABLE10_COL7:549','MYTABLE10_COL7:4043','MYTABLE11_COL7:1400','MYTABLE11_COL7:3964','MYTABLE12_COL7:3141','MYTABLE12_COL7:2808','MYTABLE13_COL7:2008','MYTABLE13_COL7:3835','MYTABLE14_COL7:3897','MYTABLE14_COL7:246','MYTABLE15_COL7:1284','MYTABLE15_COL7:3715','MYTABLE16_COL7:2583','MYTABLE16_COL7:4507','MYTABLE17_COL7:2899','MYTABLE17_COL7:1670','MYTABLE18_COL7:2187','MYTABLE18_COL7:175','MYTABLE19_COL7:3783','MYTABLE19_COL7:1525','MYTABLE20_COL7:3
398','MYTABLE20_COL7:1568','MYTABLE21_COL7:3148','MYTABLE21_COL7:2262','MYTABLE22_COL7:2815','MYTABLE22_COL7:2413','MYTABLE23_COL7:746','MYTABLE23_COL7:4357','MYTABLE24_COL7:1361','MYTABLE24_COL7:564','MYTABLE25_COL7:1427','MYTABLE25_COL7:1568','MYTABLE26_COL7:3707','MYTABLE26_COL7:1986','MYTABLE27_COL7:2771','MYTABLE27_COL7:3322','MYTABLE28_COL7:4485','MYTABLE28_COL7:3905','MYTABLE29_COL7:4142','MYTABLE29_COL7:3812','MYTABLE30_COL7:2724','MYTABLE30_COL7:1380','MYTABLE31_COL7:3702','MYTABLE31_COL7:803' )");
+ queries
+ .add("select xx.col1,xx.col2,xx.col3,xx.col5,xx.col6 from v32 xx, v32_2 xy where xx.col4=xy.col4 and xy.col7 in ('MYTABLE32_COL7:4122','MYTABLE32_COL7:3419','MYTABLE1_COL7:2197','MYTABLE1_COL7:1243','MYTABLE2_COL7:3684','MYTABLE2_COL7:4264','MYTABLE3_COL7:450','MYTABLE3_COL7:2150','MYTABLE4_COL7:966','MYTABLE4_COL7:2812','MYTABLE5_COL7:4897','MYTABLE5_COL7:2748','MYTABLE6_COL7:245','MYTABLE6_COL7:2341','MYTABLE7_COL7:1603','MYTABLE7_COL7:1150','MYTABLE8_COL7:4759','MYTABLE8_COL7:1535','MYTABLE9_COL7:1227','MYTABLE9_COL7:229','MYTABLE10_COL7:549','MYTABLE10_COL7:4043','MYTABLE11_COL7:1400','MYTABLE11_COL7:3964','MYTABLE12_COL7:3141','MYTABLE12_COL7:2808','MYTABLE13_COL7:2008','MYTABLE13_COL7:3835','MYTABLE14_COL7:3897','MYTABLE14_COL7:246','MYTABLE15_COL7:1284','MYTABLE15_COL7:3715','MYTABLE16_COL7:2583','MYTABLE16_COL7:4507','MYTABLE17_COL7:2899','MYTABLE17_COL7:1670','MYTABLE18_COL7:2187','MYTABLE18_COL7:175','MYTABLE19_COL7:3783','MYTABLE19_COL7:1525','MYTABLE20_COL7:3
398','MYTABLE20_COL7:1568','MYTABLE21_COL7:3148','MYTABLE21_COL7:2262','MYTABLE22_COL7:2815','MYTABLE22_COL7:2413','MYTABLE23_COL7:746','MYTABLE23_COL7:4357','MYTABLE24_COL7:1361','MYTABLE24_COL7:564','MYTABLE25_COL7:1427','MYTABLE25_COL7:1568','MYTABLE26_COL7:3707','MYTABLE26_COL7:1986','MYTABLE27_COL7:2771','MYTABLE27_COL7:3322','MYTABLE28_COL7:4485','MYTABLE28_COL7:3905','MYTABLE29_COL7:4142','MYTABLE29_COL7:3812','MYTABLE30_COL7:2724','MYTABLE30_COL7:1380','MYTABLE31_COL7:3702','MYTABLE31_COL7:803' )");
+ queries
+ .add("select xx.col1,xx.col2,xx.col3,xx.col5,xx.col6 from v42 xx, v42_2 xy where xx.col4=xy.col4 and xy.col7 in ('MYTABLE32_COL7:4122','MYTABLE32_COL7:3419','MYTABLE1_COL7:2197','MYTABLE1_COL7:1243','MYTABLE2_COL7:3684','MYTABLE2_COL7:4264','MYTABLE3_COL7:450','MYTABLE3_COL7:2150','MYTABLE4_COL7:966','MYTABLE4_COL7:2812','MYTABLE5_COL7:4897','MYTABLE5_COL7:2748','MYTABLE6_COL7:245','MYTABLE6_COL7:2341','MYTABLE7_COL7:1603','MYTABLE7_COL7:1150','MYTABLE8_COL7:4759','MYTABLE8_COL7:1535','MYTABLE9_COL7:1227','MYTABLE9_COL7:229','MYTABLE10_COL7:549','MYTABLE10_COL7:4043','MYTABLE11_COL7:1400','MYTABLE11_COL7:3964','MYTABLE12_COL7:3141','MYTABLE12_COL7:2808','MYTABLE13_COL7:2008','MYTABLE13_COL7:3835','MYTABLE14_COL7:3897','MYTABLE14_COL7:246','MYTABLE15_COL7:1284','MYTABLE15_COL7:3715','MYTABLE16_COL7:2583','MYTABLE16_COL7:4507','MYTABLE17_COL7:2899','MYTABLE17_COL7:1670','MYTABLE18_COL7:2187','MYTABLE18_COL7:175','MYTABLE19_COL7:3783','MYTABLE19_COL7:1525','MYTABLE20_COL7:3
398','MYTABLE20_COL7:1568','MYTABLE21_COL7:3148','MYTABLE21_COL7:2262','MYTABLE22_COL7:2815','MYTABLE22_COL7:2413','MYTABLE23_COL7:746','MYTABLE23_COL7:4357','MYTABLE24_COL7:1361','MYTABLE24_COL7:564','MYTABLE25_COL7:1427','MYTABLE25_COL7:1568','MYTABLE26_COL7:3707','MYTABLE26_COL7:1986','MYTABLE27_COL7:2771','MYTABLE27_COL7:3322','MYTABLE28_COL7:4485','MYTABLE28_COL7:3905','MYTABLE29_COL7:4142','MYTABLE29_COL7:3812','MYTABLE30_COL7:2724','MYTABLE30_COL7:1380','MYTABLE31_COL7:3702','MYTABLE31_COL7:803' )");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query2.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,52 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.Query3
+
+ 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.system.optimizer.query;
+
+/**
+ *
+ * Class Query3: Returns a list of queries that Selects from multiple views with
+ * joins on columns having indexes
+ *
+ */
+
+public class Query3 extends GenericQuery {
+
+ public Query3() {
+ description = "Select from multiple views with joins on columns having indexes";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select v8.col5, v8.col2 , v8.col3 from v8 inner join v8_2 on v8.col4=v8_2.col4 where (v8.col1>100 and v8.col1<110) union all select v8.col5, v8.col6 , v8_2.col7 from v8 inner join v8_2 on v8.col7=v8_2.col7 where (v8.col1>100 and v8.col1<110)");
+ queries
+ .add("select v16.col5, v16.col2 , v16.col3 from v16 inner join v16_2 on v16.col4=v16_2.col4 where (v16.col1>100 and v16.col1<110) union all select v16.col5, v16.col6 , v16_2.col7 from v16 inner join v16_2 on v16.col7=v16_2.col7 where (v16.col1>100 and v16.col1<110)");
+ queries
+ .add("select v32.col5, v32.col2 , v32.col3 from v32 inner join v32_2 on v32.col4=v32_2.col4 where (v32.col1>100 and v32.col1<110) union all select v32.col5, v32.col6 , v32_2.col7 from v32 inner join v32_2 on v32.col7=v32_2.col7 where (v32.col1>100 and v32.col1<110)");
+ queries
+ .add("select v42.col5, v42.col2 , v42.col3 from v42 inner join v42_2 on v42.col4=v42_2.col4 where (v42.col1>100 and v42.col1<110) union all select v42.col5, v42.col6 , v42_2.col7 from v42 inner join v42_2 on v42.col7=v42_2.col7 where (v42.col1>100 and v42.col1<110)");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query3.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,52 @@
+/*
+
+ Derby - Class Class org.apache.derbyTesting.system.langtest.query.Query4
+
+ 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.system.optimizer.query;
+
+/**
+ *
+ * Class Query4: Returns a list of queries that Selects from multiple views with
+ * joins on columns having no indexes
+ *
+ */
+
+public class Query4 extends GenericQuery {
+
+ public Query4() {
+ description = "Select from multiple views with joins on columns having no indexes";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select v8.col5, v8.col2 , v8_2.col3 from v8 inner join v8_2 on v8.col5=v8_2.col5 where (v8.col1>100 and v8.col1<110) union all select v8.col4, v8.col7 , v8_2.col7 from v8 inner join v8_2 on v8.col6=v8_2.col6 where (v8.col1>100 and v8.col1<110)");
+ queries
+ .add("select v16.col5, v16.col2 , v16_2.col3 from v16 inner join v16_2 on v16.col5=v16_2.col5 where (v16.col1>100 and v16.col1<110) union all select v16.col4, v16.col7 , v16_2.col7 from v16 inner join v16_2 on v16.col6=v16_2.col6 where (v16.col1>100 and v16.col1<110)");
+ queries
+ .add("select v32.col5, v32.col2 , v32_2.col3 from v32 inner join v32_2 on v32.col5=v32_2.col5 where (v32.col1>100 and v32.col1<110) union all select v32.col4, v32.col7 , v32_2.col7 from v32 inner join v32_2 on v32.col6=v32_2.col6 where (v32.col1>100 and v32.col1<110)");
+ queries
+ .add("select v42.col5, v42.col2 , v42_2.col3 from v42 inner join v42_2 on v42.col5=v42_2.col5 where (v42.col1>100 and v42.col1<110) union all select v42.col4, v42.col7 , v42_2.col7 from v42 inner join v42_2 on v42.col6=v42_2.col6 where (v42.col1>100 and v42.col1<110)");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query4.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,50 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.Query5
+
+ 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.system.optimizer.query;
+
+/**
+ * Class Query5: Returns a list of queries that Selects from multiple
+ * views with joins on columns, one with index and one without index
+ */
+
+public class Query5 extends GenericQuery {
+
+ public Query5() {
+ description = "Select from multiple views with joins on columns, one with index and one without index";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select v8.col5, v8.col2 , v8_2.col3 from v8 inner join v8_2 on v8.col4=v8_2.col4 where (v8.col1>100 and v8.col1<110) union all select v8.col5, v8.col7 , v8_2.col7 from v8 inner join v8_2 on v8.col6=v8_2.col6 where (v8.col1>100 and v8.col1<110)");
+ queries
+ .add("select v16.col5, v16.col2 , v16_2.col3 from v16 inner join v16_2 on v16.col4=v16_2.col4 where (v16.col1>100 and v16.col1<110) union all select v16.col5, v16.col7 , v16_2.col7 from v16 inner join v16_2 on v16.col6=v16_2.col6 where (v16.col1>100 and v16.col1<110)");
+ queries
+ .add("select v32.col5, v32.col2 , v32_2.col3 from v32 inner join v32_2 on v32.col4=v32_2.col4 where (v32.col1>100 and v32.col1<110) union all select v32.col5, v32.col7 , v32_2.col7 from v32 inner join v32_2 on v32.col6=v32_2.col6 where (v32.col1>100 and v32.col1<110)");
+ queries
+ .add("select v42.col5, v42.col2 , v42_2.col3 from v42 inner join v42_2 on v42.col4=v42_2.col4 where (v42.col1>100 and v42.col1<110) union all select v42.col5, v42.col7 , v42_2.col7 from v42 inner join v42_2 on v42.col6=v42_2.col6 where (v42.col1>100 and v42.col1<110)");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query5.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,90 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.Query6
+
+ 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.system.optimizer.query;
+
+/*
+ * Class Query6: Returns a list of queries that Selects from multiple
+ * views with combination of nested views and aggregate views
+ */
+
+public class Query6 extends GenericQuery {
+
+ public Query6() {
+ description = "Select from multiple views with combination of nested views and aggregate views";
+ generateQueries();
+ }
+
+ /**
+ * @param args
+ */
+ public void generateQueries() {
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a right join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a right join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a inner join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a inner join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a right join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a right join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a inner join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a inner join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from sum_view_8a join max_view_8b on col1=max_view_8bc1 where col1 <100 union all select col1, min_view_8bc1 from sum_view_8a join min_view_8b on col1=min_view_8bc1 where col1 <100");
+ queries
+ .add("select col1, max_view_8bc1 from avg_view_8a join max_view_8b on col1=1 where (col1 >0 and col1 <10) union all select col1, min_view_8bc1 from avg_view_8a join min_view_8b on col1=2 where (col1 >0 and col1 <10)");
+ queries
+ .add("select col1, max_view_8bc1 from avg_view_8a join max_view_8b on col1=101 where (col1 >100 and col1 <200) union all select col1, min_view_8bc1 from avg_view_8a join min_view_8b on col1=min_view_8bc1 where (col1 >100 and col1 <200)");
+ queries
+ .add("select col1, count_view_8bc1 from avg_view_8a join count_view_8b on col1=23 where col1 <100 union all select col1, min_view_8bc1 from avg_view_8a join min_view_8b on col1=20 where col1 <100");
+ queries
+ .add("select col1, count_view_8bc1 from max_view_8a join count_view_8b on col1=145 where (col1 >50 and col1<300) union all select col1, max_view_8bc1 from avg_view_8a join max_view_8b on col1=123 where (col1 >50 and col1<510)");
+ queries
+ .add("select col1,sum_view_8bc1 from avg_view_8a join sum_view_8b on col1<10 where col1 is not null union all select col1, count_view_8bc1 from avg_view_8a join count_view_8b on col1<10 where col1 is null");
+ queries
+ .add("select sum_view_8bc1,avg_view_8bc1,max_view_8bc1 from sum_view_8b,avg_view_8b,max_view_8b where (sum_view_8bc1>avg_view_8bc1 and avg_view_8bc1<max_view_8bc1) union all select count_view_8bc1,min_view_8bc1,max_view_8bc1 from count_view_8b,min_view_8b,max_view_8b where (count_view_8bc1=min_view_8bc1=max_view_8bc1)");
+ queries
+ .add("select sum_view_8bc1,avg_view_8bc1,max_view_8bc1 from sum_view_8b,avg_view_8b,max_view_8b where (sum_view_8bc1>avg_view_8bc1 and avg_view_8bc1< max_view_8bc1) union select min_view_8bc1,avg_view_8bc1,max_view_8bc1 from min_view_8b,avg_view_8b,max_view_8b where (min_view_8bc1>avg_view_8bc1 and avg_view_8bc1<max_view_8bc1)");
+ queries
+ .add("select sum_view_8bc1,avg_view_8bc1,max_view_8bc1 from sum_view_8b,avg_view_8b,max_view_8b where (sum_view_8bc1=40200 and avg_view_8bc1<1255 and max_view_8bc1=400) union all select count_view_8bc1,min_view_8bc1,max_view_8bc1 from count_view_8b,min_view_8b,max_view_8b where (count_view_8bc1=40200 and min_view_8bc1<1255 and max_view_8bc1=400)");
+ queries
+ .add("select sum_view_8bc1,avg_view_8bc1,min_view_8bc1 from sum_view_8b,avg_view_8b,min_view_8b where (min_view_8bc1=(select col8 from mytable8 where col8=2)) union all select count_view_8bc1,min_view_8bc1,max_view_8bc1 from count_view_8b,min_view_8b,max_view_8b where (count_view_8bc1=200 and (min_view_8bc1=2) or (max_view_8bc1=1245))");
+ queries
+ .add("select v_level7c2, v_level7c4, sum_view_8bc1 from v_level7 join sum_view_8b on sum_view_8bc1 is not null union all select v_level7c2, v_level7c3 , sum_view_8bc1 from v_level7 join sum_view_8b on v_level7c1=sum_view_8bc1 where ((v_level7c1>2308 and v_level7c1<2310) and v_level7c4 is not null and sum_view_8bc1 is not null)");
+ queries
+ .add("select v_level8c2, v_level8c4, count_view_8bc1 from v_level8 join count_view_8b on count_view_8bc1=v_level8c1 where ((v_level8c1>108 and v_level8c1<201) and v_level8c4 is not null and v_level8c7 is not null) union all select v_level8c2, v_level8c3 , avg_view_8bc1 from v_level8 join avg_view_8b on v_level8c1=avg_view_8bc1 where ((v_level8c1=201 ) and v_level8c4 = 'String value for the varchar column in Table MYTABLE2: 197' and avg_view_8bc1 =97)");
+ queries
+ .add("select v_level6c2, v_level6c4, min_view_8bc1 from v_level6 join min_view_8b on min_view_8bc1=v_level6c1 where (v_level6c2='String value for the varchar column in Table MYTABLE9: 2') union all select v_level5c2, v_level5c3 , min_view_8bc1 from v_level5 join min_view_8b on v_level5c1=min_view_8bc1 where ((v_level5c1>2308 and v_level5c1<2310) and v_level5c4 is not null and min_view_8bc1 is not null)");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 union all select col1,col7 from mytable2) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2 union all select a3,a4 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a3,a4),mytable1 where mytable1.col1=A.a3 and mytable1.col7=A.a4");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2 union all select a3,a4 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a3,a4),mytable1 where mytable1.col1=A.a3 and mytable1.col7=A.a4 union all select a5,a6 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 100 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a5,a6),mytable8 where mytable8.col1=A.a5 and mytable8.col7=A.a6 union all select a7,a8 from (select col1,col7 from mytable55 where ( col1< 1000 ) union all select col1,col7 from mytable10 where (col1 < 1000 )) as A(a7,a8),mytable1 where mytable1.col1=A.a7 and mytable1.col7=A.a8");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2 union all select a3,a4 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a3,a4),mytable1 where mytable1.col1=A.a3 and mytable1.col7=A.a4 union all select a5,a6 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 100 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a5,a6),mytable8 where mytable8.col1=A.a5 and mytable8.col7=A.a6 union all select a7,a8 from (select col1,col7 from mytable55 where ( col1< 1000 ) union all select col1,col7 from mytable10 where (col1 < 1000 )) as A(a7,a8),mytable1 where mytable1.col1=A.a7 and mytable1.col7=A.a8 union all select a5,a6 from (select v_level8c1,v_level8c7 from v_level8 where (v_l
evel8c1 < 100 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a5,a6),mytable8 where mytable8.col1=A.a5 and mytable8.col7=A.a6 union all select a7,a8 from (select col1,col7 from mytable55 where ( col1< 1000 ) union all select col1,col7 from mytable10 where (col1 < 1000 )) as A(a7,a8),mytable1 where mytable1.col1=A.a7 and mytable1.col7=A.a8");
+ queries
+ .add("select a1,a2 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 > 1000 and v_level8c1 < 1010 ) union all select col1,col7 from mytable2 where (col1 > 1000 and col1 <1010 )) as A(a1,a2),mytable1 where mytable1.col1=A.a1 and mytable1.col7=A.a2 union all select a3,a4 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 1000 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a3,a4),mytable1 where mytable1.col1=A.a3 and mytable1.col7=A.a4 union all select a5,a6 from (select v_level8c1,v_level8c7 from v_level8 where (v_level8c1 < 100 ) union all select col1,col7 from mytable2 where (col1 < 1000 )) as A(a5,a6),mytable8 where mytable8.col1=A.a5 and mytable8.col7=A.a6 union all select a7,a8 from (select col1,col7 from mytable55 where ( col1< 1000 ) union all select col1,col7 from mytable10 where (col1 < 1000 )) as A(a7,a8),mytable1 where mytable1.col1=A.a7 and mytable1.col7=A.a8");
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/Query6.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,75 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.query.QueryList
+
+ 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.system.optimizer.query;
+/**
+ * Class QueryList: Returns the list of queries to be run as a part of the test. If the
+ * 'query.list' file is provided, this class will read the file and
+ * return a GenericQuery object
+ */
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.IOException;
+import java.sql.Connection;
+import java.util.ArrayList;
+import java.util.Properties;
+
+import org.apache.derbyTesting.system.optimizer.StaticValues;
+
+public class QueryList {
+ private static ArrayList qList=new ArrayList();
+ public static boolean queryListOnly=false;
+ public static void init(Connection conn){
+ GenericQuery q=new GenericQuery();
+ File queryFile = new File(StaticValues.queryFile);
+ if(queryFile.exists()){
+ System.out.println("External query list found, adding to the run...");
+ Properties p=new Properties();
+ queryListOnly=true;
+ try{
+ p.load(new FileInputStream(queryFile));
+ q.generateQueries(p);
+ getQList().add(q);
+ if(queryListOnly){
+ return;
+ }
+ }catch(IOException ioe){
+ ioe.printStackTrace();
+ }
+ }
+
+ q=new Query1();
+ getQList().add(q);
+ q=new Query2();
+ getQList().add(q);
+ q=new Query3();
+ getQList().add(q);
+ q=new Query4();
+ getQList().add(q);
+ q=new Query5();
+ getQList().add(q);
+ q=new Query6();
+ getQList().add(q);
+
+ }
+ public static ArrayList getQList() {
+ return qList;
+ }
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/query/QueryList.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,201 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.utils.DataUtils
+
+ 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.system.optimizer.utils;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.Timestamp;
+
+import org.apache.derbyTesting.system.optimizer.StaticValues;
+/**
+ *
+ * Class DataUtils: Utility class to drop/create database objects and populate data
+ *
+ */
+
+
+public class DataUtils {
+ public static void dropObjects(Connection conn) throws SQLException {
+ Statement stmt = null;
+ System.out.println("Dropping existing Tables and Views...");
+ for (int i=0;i<TestViews.dropViews.size();i++){
+ try{
+ stmt = conn.createStatement();
+ stmt.executeUpdate((String)TestViews.dropViews.get(i));
+ }catch(SQLException sqe){
+ if(!sqe.getSQLState().equalsIgnoreCase("X0X05")){
+ throw sqe;
+ }
+ }
+ }
+ for (int i = 1; i <= StaticValues.NUM_OF_TABLES; i++) {
+ try {
+ String tableName = StaticValues.TABLE_NAME + i;
+ stmt = conn.createStatement();
+ stmt.execute(StaticValues.DROP_TABLE+ tableName);
+ stmt.close();
+ } catch (SQLException sqe) {
+ if (!sqe.getSQLState().equalsIgnoreCase("42Y55")) {
+ throw sqe;
+ }
+ }
+ }// end for
+ }
+ public static void createObjects(Connection conn) throws SQLException {
+ Statement stmt = null;
+ System.out.println("Creating Tables...");
+ for (int i = 1; i <= StaticValues.NUM_OF_TABLES; i++) {
+ try {
+ String tableName = StaticValues.TABLE_NAME + i;
+ System.out.println(" Creating Table - "+tableName);
+ stmt = conn.createStatement();
+ stmt.execute(StaticValues.CREATE_TABLE+ tableName+ StaticValues.TABLE_COLS);
+
+ stmt.close();
+ } catch (SQLException sqe) {
+ if (!sqe.getSQLState().equalsIgnoreCase("X0Y32")) {
+ throw sqe;
+ } else {
+ System.out.println("Table " + StaticValues.TABLE_NAME + i
+ + " exists");
+ }
+
+ }
+ }// end for
+ System.out.println("Creating Views...");
+ for (int i=0;i<TestViews.createViews.size();i++){
+ try{
+ stmt = conn.createStatement();
+ stmt.executeUpdate((String)TestViews.createViews.get(i));
+ }catch(SQLException sqe){
+ System.out.println("SQLState = "+sqe.getSQLState()+", "+sqe);
+ System.out.println("View statement ==> "+(String)TestViews.createViews.get(i)+" failed");
+ }
+ }
+ }
+
+ public static void insertData(Connection conn){
+ try{
+ String commonString = "String value for the ";
+ String valueForString = commonString + "varchar column ";
+ String valueForBitData = commonString + "bit data column ";
+ conn.setAutoCommit(false);
+ Statement stmt = conn.createStatement();
+ ResultSet rs = null;
+ int totalRows = 0;
+ for (int i = 1; i <= StaticValues.NUM_OF_TABLES; i++) {
+ String tableName = StaticValues.TABLE_NAME + i;
+ rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
+ while (rs.next()) {
+ totalRows = rs.getInt(1);
+ }
+ if (totalRows >= StaticValues.NUM_OF_ROWS) {
+ System.out.println(" InsertData.insert_data() => "
+ + totalRows + " exists in table " + tableName
+ + "...");
+
+ }else{
+ if(totalRows>0){
+ System.out.println("Dropping existing indexes from table: "
+ + tableName);
+ try {
+ stmt.executeUpdate("DROP INDEX " + tableName
+ + "_col4_idx");
+ } catch (SQLException sqe) {
+ if (!sqe.getSQLState().equalsIgnoreCase("42X65")) {
+ throw sqe;
+ }
+ }
+ try {
+ stmt.executeUpdate("DROP INDEX " + tableName
+ + "_col7_idx");
+ } catch (SQLException sqe) {
+ if (!sqe.getSQLState().equalsIgnoreCase("42X65")) {
+ throw sqe;
+ }
+ }
+ System.out.println("Rows deleted from " + tableName + "= "
+ + stmt.executeUpdate("DELETE FROM " + tableName));
+ }
+ PreparedStatement ps = conn
+ .prepareStatement(StaticValues.INSERT_TABLE
+ + tableName + StaticValues.INSERT_VALUES);
+ long start = System.currentTimeMillis();
+ int k = 1;
+ while (k <= StaticValues.NUM_OF_ROWS) {
+
+ ps.setInt(1, k);
+ ps.setString(2, valueForString + "in Table "
+ + StaticValues.TABLE_NAME + i + ": " + k);
+ ps.setString(3, valueForBitData + "in Table "
+ + StaticValues.TABLE_NAME + i + ": " + k);
+ ps.setString(4, StaticValues.TABLE_NAME + i + "_COL4:"
+ + k);
+ ps.setString(5, StaticValues.TABLE_NAME + i + "_COL5:"
+ + k);
+ ps.setString(6, StaticValues.TABLE_NAME + i + "_COL6:"
+ + k);
+ ps.setString(7, StaticValues.TABLE_NAME + i + "_COL7:"
+ + k);
+ ps.setInt(8, k);
+ /*
+ * ps.setString(8, StaticValues.TABLE_NAME + i +
+ * "_COL8:" + k);
+ */
+ ps.setTimestamp(9, new Timestamp(System
+ .currentTimeMillis()));
+ ps.executeUpdate();
+ if ((k % 10000) == 0) {
+ conn.commit();
+ }
+ k++;
+ }
+ ps.close();
+ conn.commit();
+ System.out.println("Inserted " + (k - 1) + " rows into "
+ + tableName + " in "
+ + (System.currentTimeMillis() - start)
+ + " milliseconds");
+ conn.setAutoCommit(true);
+
+ System.out.println("Creating indexes for table: "
+ + tableName);
+
+ stmt.executeUpdate("CREATE INDEX " + tableName
+ + "_col4_idx on " + tableName + "(col4)");
+ stmt.executeUpdate("CREATE INDEX " + tableName
+ + "_col7_idx on " + tableName + "(col7)");
+ }//end else
+ }// end for
+ }catch (Exception se){
+ System.out.println(" EXCEPTION:" + se.getMessage());
+ System.out.println("Stack Trace : \n" );
+ se.printStackTrace();
+ return;
+ }
+ }
+}
+
+
+
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/DataUtils.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java?view=auto&rev=513562
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java Thu Mar 1 16:47:47 2007
@@ -0,0 +1,68 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.system.langtest.utils.TestUtils
+
+ 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.system.optimizer.utils;
+/**
+ *
+ * Class TestUtils: Utility class for measuring query times
+ *
+ */
+public class TestUtils {
+ static int MILLISECONDS_IN_SEC=1000;
+ static int SECONDS_IN_MIN=60;
+ static int MINUTES_IN_HR=60;
+
+ public static String getTime(long timeInMs)
+ {
+ StringBuffer stringBuff = new StringBuffer(32);
+ //get Hours
+ int hours = (int)timeInMs /( MINUTES_IN_HR * SECONDS_IN_MIN * MILLISECONDS_IN_SEC);
+ if (hours > 0) {
+ stringBuff.append(hours);
+ stringBuff.append(" hr");
+ }
+ //get Minutes
+ int remainHours = (int)timeInMs % (MINUTES_IN_HR * SECONDS_IN_MIN * MILLISECONDS_IN_SEC);
+ int minutes = remainHours / (SECONDS_IN_MIN * MILLISECONDS_IN_SEC);
+ if (minutes > 0) {
+ stringBuff.append(minutes);
+ stringBuff.append(" min ");
+ }
+ //get Seconds
+ int remainMinutes = remainHours % (SECONDS_IN_MIN * MILLISECONDS_IN_SEC);
+ int seconds = remainMinutes / MILLISECONDS_IN_SEC;
+ int milliseconds = remainMinutes % MILLISECONDS_IN_SEC;
+
+ stringBuff.append(seconds);
+ if (hours == 0 && minutes < 5)
+ {
+ stringBuff.append('.');
+ if (milliseconds < 10)
+ stringBuff.append('0');
+ if (milliseconds < 100)
+ stringBuff.append('0');
+ stringBuff.append(milliseconds);
+ }
+ stringBuff.append(" secs ");
+ return stringBuff.toString();
+
+ }
+
+}
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/system/optimizer/utils/TestUtils.java
------------------------------------------------------------------------------
svn:eol-style = native