db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r356562 [6/6] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/reference/ engine/org/apache/derby/iapi/sql/ engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/jdbc/ e...
Date Tue, 13 Dec 2005 18:24:11 GMT
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/phaseTester.out
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
Tue Dec 13 10:23:42 2005
@@ -89,6 +89,7 @@
 lang/nonreserved.sql
 lang/nulls.sql
 lang/openScans.sql
+lang/optimizerOverrides.sql
 lang/orderby.sql
 lang/orderbyElimination.sql
 lang/outerjoin.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
Tue Dec 13 10:23:42 2005
@@ -24,6 +24,7 @@
 jdbcapi/metadata.java
 jdbcapi/metadataMultiConn.java
 jdbcapi/odbc_metadata.java
+lang/optimizerOverrides.sql
 jdbcapi/parameterMetaDataJdbc30.java
 jdbcapi/savepointJdbc30.java
 lang/big.sql

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/jdbc20.runall
Tue Dec 13 10:23:42 2005
@@ -2,7 +2,6 @@
 lang/scrollCursors2.java
 lang/scrollCursors3.sql
 jdbcapi/batchUpdate.java
-jdbcapi/metadataJdbc20.java
 jdbcapi/getCurConnJdbc20.sql
 jdbcapi/statementJdbc20.java
 jdbcapi/connectionJdbc20.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata.java
Tue Dec 13 10:23:42 2005
@@ -49,6 +49,9 @@
 
 public class metadata extends metadata_test {
 
+	public metadata() {
+	}
+
 	/**
 	 * Constructor:
 	 * Just intializes the Connection and Statement fields
@@ -77,7 +80,7 @@
 	 * which will in turn call back here for implementations of
 	 * the abstract methods.
 	 */
-	public static void main(String[] args) {
+	public static void main(String[] args) throws Exception {
 
 		new metadata(args).runTest();
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/metadata_test.java
Tue Dec 13 10:23:42 2005
@@ -23,6 +23,7 @@
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.DatabaseMetaData;
+import java.sql.PreparedStatement;
 import java.sql.ResultSetMetaData;
 import java.sql.Statement;
 import java.sql.CallableStatement;
@@ -35,6 +36,7 @@
 import java.math.BigDecimal;
 
 import java.util.Properties;
+import java.util.StringTokenizer;
 
 import org.apache.derby.tools.ij;
 
@@ -82,11 +84,96 @@
 	}
 
 	// We leave it up to the classes which extend this one to
-	// initialize the following fields at contruct time.
-	protected Connection con;
-	protected static Statement s;
+	// initialize the following fields at construct time.
+	public Connection con;
+	public static Statement s;
+	
+	/*
+	** Escaped function testing
+	*/
+	private static final String[][] NUMERIC_FUNCTIONS =
+	{
+		// Section C.1 JDBC 3.0 spec.
+		{ "ABS", "-25.67" },
+		{ "ACOS", "1.34" },
+		{ "ASIN", "1.21" },
+		{ "ATAN", "0.34" },
+		{ "ATAN2", "0.56", "1.2" },
+		{ "CEILING", "3.45" },
+		{ "COS", "1.2" },
+		{ "COT", "3.4" },
+		{ "DEGREES", "2.1" },
+		{ "EXP", "2.3" },
+		{ "FLOOR", "3.22" },
+		{ "LOG", "34.1" },
+		{ "LOG10", "18.7" },
+		{ "MOD", "124", "7" },
+		{ "PI" },
+		{ "POWER", "2", "3" },
+		{ "RADIANS", "54" },
+		{ "RAND", "17" }, 
+		{ "ROUND", "345.345", "1" }, 
+		{ "SIGN", "-34" },
+		{ "SIN", "0.32" },
+		{ "SQRT", "6.22" },
+		{ "TAN", "0.57", },
+		{ "TRUNCATE", "345.395", "1" }
+	};
+	
+	private static final String[][] TIMEDATE_FUNCTIONS =
+	{	
+		// Section C.3 JDBC 3.0 spec.
+		{ "CURDATE" },
+		{ "CURTIME" },
+		{ "DAYNAME", "{d '1995-12-19'h}" },
+		{ "DAYOFMONTH", "{d '1995-12-19'}" },
+		{ "DAYOFWEEK", "{d '1995-12-19'}" },
+		{ "DAYOFYEAR", "{d '1995-12-19'}" },
+		{ "HOUR", "{t '16:13:03'}" },
+		{ "MINUTE", "{t '16:13:03'}" },
+		{ "MONTH", "{d '1995-12-19'}" },
+		{ "MONTHNAME", "{d '1995-12-19'}" },
+		{ "NOW" },
+		{ "QUARTER", "{d '1995-12-19'}" },
+		{ "SECOND", "{t '16:13:03'}" },
+		{ "TIMESTAMPADD", "SQL_TSI_DAY", "7", "{ts '1995-12-19 12:15:54'}" },
+		{ "TIMESTAMPDIFF", "SQL_TSI_DAY", "{ts '1995-12-19 12:15:54'}", "{ts '1997-11-02 00:15:23'}"
},
+		{ "WEEK", "{d '1995-12-19'}" },
+		{ "YEAR", "{d '1995-12-19'}" },
+		
+	};
 
-	protected void runTest() {
+	private static final String[][] SYSTEM_FUNCTIONS =
+	{	
+		// Section C.4 JDBC 3.0 spec.
+		{ "DATABASE" },
+		{ "IFNULL", "'this'", "'that'" },
+		{ "USER"},
+		};	
+	
+	private static final String[][] STRING_FUNCTIONS =
+	{	
+		// Section C.2 JDBC 3.0 spec.
+		{ "ASCII" , "'Yellow'" },
+		{ "CHAR", "65" },
+		{ "CONCAT", "'hello'", "'there'" },
+		{ "DIFFERENCE", "'Pires'", "'Piers'" },
+		{ "INSERT", "'Bill Clinton'", "4", "'William'" },
+		{ "LCASE", "'Fernando Alonso'" },
+		{ "LEFT", "'Bonjour'", "3" },
+		{ "LENGTH", "'four    '" } ,
+		{ "LOCATE", "'jour'", "'Bonjour'" },
+		{ "LTRIM", "'   left trim   '"},
+		{ "REPEAT", "'echo'", "3" },
+		{ "REPLACE", "'to be or not to be'", "'be'", "'England'" },
+		{ "RTRIM", "'  right trim   '"},
+		{ "SOUNDEX", "'Derby'" },
+		{ "SPACE", "12"},
+		{ "SUBSTRING", "'Ruby the Rubicon Jeep'", "10", "7", },
+		{ "UCASE", "'Fernando Alonso'" }
+		};
+
+	public void runTest() {
 
 		DatabaseMetaData met;
 		ResultSet rs;
@@ -288,6 +375,27 @@
 				new String [] {null, "%", "GETPCTEST%"},
 				null, null, null));
 
+			System.out.println("getUDTs() with user-named types null :");
+ 			dumpRS(met.getUDTs(null, null, null, null));
+
+			System.out.println("getUDTs() with user-named types in ('JAVA_OBJECT') :");
+ 			int[] userNamedTypes = new int[1];
+ 			userNamedTypes[0] = java.sql.Types.JAVA_OBJECT;
+ 			dumpRS(met.getUDTs("a", null, null, userNamedTypes));      
+
+ 			System.out.println("getUDTs() with user-named types in ('STRUCT') :");
+ 			userNamedTypes[0] = java.sql.Types.STRUCT;
+ 			dumpRS(met.getUDTs("b", null, null, userNamedTypes));
+
+ 			System.out.println("getUDTs() with user-named types in ('DISTINCT') :");
+ 			userNamedTypes[0] = java.sql.Types.DISTINCT;
+ 			dumpRS(met.getUDTs("c", null, null, userNamedTypes));
+
+			System.out.println("getUDTs() with user-named types in ('JAVA_OBJECT', 'STRUCT') :");
+ 			userNamedTypes = new int[2];
+ 			userNamedTypes[0] = java.sql.Types.JAVA_OBJECT;
+ 			userNamedTypes[1] = java.sql.Types.STRUCT;
+ 			dumpRS(met.getUDTs("a", null, null, userNamedTypes));
 
 			/*
 			 * any methods that were not tested above using code written
@@ -523,6 +631,21 @@
 			System.out.println("dataDefinitionIgnoredInTransactions(): " +
 							   met.dataDefinitionIgnoredInTransactions());
 
+			System.out.println("Test the metadata calls related to visibility of changes made by others
for different resultset types");
+			System.out.println("Since Derby materializes a forward only ResultSet incrementally, it
is possible to see changes");
+			System.out.println("made by others and hence following 3 metadata calls will return true
for forward only ResultSets.");
+			System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + met.othersUpdatesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+			System.out.println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + met.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+			System.out.println("othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " + met.othersInsertsAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+			System.out.println("Scroll insensitive ResultSet by their definition do not see changes
made by others and hence following metadata calls return false");
+			System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + met.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+			System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + met.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+			System.out.println("othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? " + met.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+			System.out.println("Derby does not yet implement scroll sensitive resultsets and hence
following metadata calls return false");
+			System.out.println("othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? " + met.othersUpdatesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
+			System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? " + met.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
+			System.out.println("othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? " + met.othersInsertsAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
+
 			System.out.println("getConnection(): "+
 					   ((met.getConnection()==con)?"same connection":"different connection") );
 			System.out.println("getProcedureColumns():");
@@ -711,6 +834,19 @@
 				}
 			}
 			rs.close();
+			
+			System.out.println("Test escaped numeric functions - JDBC 3.0 C.1");
+			testEscapedFunctions(con, NUMERIC_FUNCTIONS, met.getNumericFunctions());
+			
+			System.out.println("Test escaped string functions - JDBC 3.0 C.2");
+			testEscapedFunctions(con, STRING_FUNCTIONS, met.getStringFunctions());
+
+			System.out.println("Test escaped date time functions - JDBC 3.0 C.3");
+			testEscapedFunctions(con, TIMEDATE_FUNCTIONS, met.getTimeDateFunctions());
+
+			System.out.println("Test escaped system functions - JDBC 3.0 C.4");
+			testEscapedFunctions(con, SYSTEM_FUNCTIONS, met.getSystemFunctions());
+
 			//
 			// Test referential actions on delete
 			//
@@ -880,6 +1016,7 @@
 			s.execute("drop table refactnoaction");
 			s.execute("drop table refactcascade");
 			s.execute("drop table refactsetnull");
+			s.execute("drop table inflight");
 			s.execute("drop table refaction1");
 
 			// test beetle 5195
@@ -925,7 +1062,20 @@
 				" parameter style java"); 
             	s.execute("call isReadO()");
             }
-			
+			s.execute("drop procedure isReadO");
+			s.execute("drop procedure GETPCTEST4Bx");
+			s.execute("drop procedure GETPCTEST4B");
+			s.execute("drop procedure GETPCTEST4A");
+			s.execute("drop procedure GETPCTEST3B");
+			s.execute("drop procedure GETPCTEST3A");
+			s.execute("drop procedure GETPCTEST2");
+			s.execute("drop procedure GETPCTEST1");
+			s.execute("drop table t");
+			s.execute("drop table reftab");
+			s.execute("drop table reftab2");
+			s.execute("drop view SCREWIE");
+			s.execute("drop table louie");
+			s.execute("drop table alltypes");
 			s.close();
 			if (con.getAutoCommit() == false)
 				con.commit();
@@ -944,6 +1094,141 @@
 		System.out.println("Test metadata finished");
     }
 
+	/**
+	 * Test escaped functions. Working from the list of escaped functions defined
+	 * by JDBC, compared to the list returned by the driver.
+	 * <OL>
+	 * <LI> See that all functions defined by the driver are in the spec list
+	 * and that they work.
+	 * <LI> See that only functions defined by the spec are in the driver's list.
+	 * <LI> See that any functions defined by the spec that work are in the driver's list.
+	 * </OL>
+	 * FAIL will be printed for any issues.
+	 * @param conn
+	 * @param specList
+	 * @param metaDataList
+	 * @throws SQLException
+	 */
+	private static void testEscapedFunctions(Connection conn, String[][] specList, String metaDataList)
+	throws SQLException
+	{
+		boolean[] seenFunction = new boolean[specList.length];
+		
+		System.out.println("TEST FUNCTIONS DECLARED IN DATABASEMETADATA LIST");
+		StringTokenizer st = new StringTokenizer(metaDataList, ",");
+		while (st.hasMoreTokens())
+		{
+			String function = st.nextToken();
+			
+			// find this function in the list
+			boolean isSpecFunction = false;
+			for (int f = 0; f < specList.length; f++)
+			{
+				String[] specDetails = specList[f];
+				if (function.equals(specDetails[0]))
+				{
+					// Matched spec.
+					if (seenFunction[f])
+						System.out.println("FAIL Function in list twice: " + function);
+					seenFunction[f] = true;
+					isSpecFunction = true;
+					
+					if (!executeEscaped(conn, specDetails))
+						System.out.println("FAIL Function failed to execute "+ function);
+					break;
+				}
+			}
+			
+			if (!isSpecFunction)
+			{
+				System.out.println("FAIL Non-JDBC spec function in list: " + function);
+			}
+		}
+		
+		// Now see if any speced functions are not in the metadata list
+		System.out.println("TEST FUNCTIONS NOT DECLARED IN DATABASEMETADATA LIST");
+		for (int f = 0; f < specList.length; f++)
+		{
+			if (seenFunction[f])
+				continue;
+			String[] specDetails = specList[f];
+			if (executeEscaped(conn, specDetails))
+				System.out.println("FAIL function works but not declared in list: " + specDetails[0]);
+			
+		}
+	}
+	
+	private static boolean executeEscaped(Connection conn, String[] specDetails)
+	{
+		
+		String sql = "VALUES { fn " + specDetails[0] + "(";
+		
+		for (int p = 0; p < specDetails.length - 1; p++)
+		{
+			if (p != 0)
+				sql = sql + ", ";
+			
+			sql = sql + specDetails[p + 1];
+		}
+		
+		sql = sql + ") }";
+		
+		// Special processing for functions that return
+		// current date, time or timestamp. This is to
+		// ensure we don't have output that depends on
+		// the time the test is run.
+		if ("CURDATE".equals(specDetails[0]))
+			sql = "VALUES CASE WHEN { fn CURDATE()} = CURRENT_DATE THEN 'OK' ELSE 'wrong' END";
+		else if ("CURTIME".equals(specDetails[0]))
+			sql = "VALUES CASE WHEN { fn CURTIME()} = CURRENT_TIME THEN 'OK' ELSE 'wrong' END";
+		else if ("NOW".equals(specDetails[0]))
+			sql = "VALUES CASE WHEN { fn NOW()} = CURRENT_TIMESTAMP THEN 'OK' ELSE 'wrong' END";
+		
+		
+		System.out.print("Executing " + sql + " -- ");
+			
+		try {
+			PreparedStatement ps = conn.prepareStatement(sql);
+			ResultSet rs = ps.executeQuery();
+			
+			while (rs.next())
+			{
+				// truncate numbers to avoid multiple master files
+				// with double values.
+				String res = rs.getString(1);
+				
+				switch (rs.getMetaData().getColumnType(1))
+				{
+				case Types.DOUBLE:
+				case Types.REAL:
+				case Types.FLOAT:
+					if (res.length() > 4)
+						res = res.substring(0, 4);
+					break;
+				default:
+					break;
+				}
+				System.out.print("  = >" + res + "< ");
+			}
+			rs.close();
+			ps.close();
+			System.out.println(" << ");
+			return true;
+		} catch (SQLException e) {
+			System.out.println("");
+			showSQLExceptions(e);
+			return false;
+		}
+		
+	}
+
+	static private void showSQLExceptions (SQLException se) {
+		while (se != null) {
+			System.out.println("SQLSTATE("+se.getSQLState()+"): " + se.getMessage());
+			se = se.getNextException();
+		}
+	}
+	
 	static protected void dumpSQLExceptions (SQLException se) {
 		System.out.println("FAIL -- unexpected exception");
 		while (se != null) {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/checkConstraint.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/checkConstraint.sql?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/checkConstraint.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/checkConstraint.sql
Tue Dec 13 10:23:42 2005
@@ -22,7 +22,8 @@
 
 -- verify that a check constraint can't be used as an optimizer override
 create table t1(c1 int constraint asdf check(c1 = 1));
-select * from t1 properties constraint = asdf;
+select * from t1 --derby-properties constraint = asdf
+;
 -- alter table t1 drop constraint asdf;
 rollback;
 -- alter table t1 drop constraint asdf;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/copyfiles.ant
Tue Dec 13 10:23:42 2005
@@ -147,6 +147,7 @@
 nulls.sql
 openScans.sql
 openScans_derby.properties
+optimizerOverrides.sql
 orderby.sql
 orderbyElimination.sql
 orderbyElimination_derby.properties

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
Tue Dec 13 10:23:42 2005
@@ -139,13 +139,8 @@
 DROP TABLE tb1;
 DROP TABLE testCS;
 
--- PROPERTIES in DB2 mode
-create table maps (country_ISO_code char(2)) PROPERTIES derby.storage.pageSize=262144;
-
--- PROPERTIES in DB2 mode
 -- beetle 5177
 create table maps2 (country_ISO_code char(2));
-create index map_idx1 on maps2(country_ISO_code) properties derby.storage.pageSize = 2048;
 -- BTREE not supported in both Cloudscape and DB2 mode and that is why rather than getting
feature not implemented, we will get syntax error in DB2 mode
 create btree index map_idx2 on maps2(country_ISO_code);
 create unique btree index map_idx2 on maps2(country_ISO_code);

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql?rev=356562&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
Tue Dec 13 10:23:42 2005
@@ -0,0 +1,221 @@
+-- test the optimizer overrides
+autocommit off;
+
+-- create the tables
+create table t1 (c1 int, c2 int, c3 int, constraint cons1 primary key(c1, c2));
+create table t2 (c1 int not null, c2 int not null, c3 int, constraint cons2 unique(c1, c2));
+
+-- populate the tables
+insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
+insert into t2 values (1, 1, 1), (2, 2, 2), (3, 3, 3), (4, 4, 4);
+
+-- create some indexes
+create index t1_c1c2c3 on t1(c1, c2, c3);
+create index t1_c3c2c1 on t1(c3, c2, c1);
+create index t1_c1 on t1(c1);
+create index t1_c2 on t1(c2);
+create index t1_c3 on t1(c3);
+create index "t1_c2c1" on t1(c2, c1);
+create index t2_c1c2c3 on t2(c1, c2, c3);
+create index t2_c3c2c1 on t2(c3, c2, c1);
+create index t2_c1 on t2(c1);
+create index t2_c2 on t2(c2);
+create index t2_c3 on t2(c3);
+
+-- create some views
+create view v1 as select * from t1 --derby-properties index = t1_c1
+;
+create view v2 as select t1.* from t1, t2;
+create view v3 as select * from v1;
+create view neg_v1 as select * from t1 --derby-properties asdf = fdsa
+;
+
+-- negative tests
+select 
+-- derby-properties index = t1_c1
+* from t1;
+select * -- derby-properties index = t1_c1
+from t1;
+select 
+-- derby-properties
+* from t1;
+-- optimizer override did not specify propertyname=value pairs
+select * from t1 --derby-properties
+;
+
+-- invalid property
+select * from t1 --derby-properties asdf = i1
+;
+select * from t1 exposedname --derby-properties asdf = i1
+;
+
+-- non-existent index
+select * from t1 --derby-properties index = t1_notexists
+;
+select * from t1 exposedname --derby-properties index = t1_notexists
+;
+
+-- non-existent constraint
+select * from t1 --derby-properties constraint = t1_notexists
+;
+select * from t1 exposedname --derby-properties constraint = t1_notexists
+;
+
+-- make sure following get treated as comments
+--d 
+-- de
+-- der
+--derb
+--derby comment
+-- derby another comment
+--derby-
+--derby-p
+--derby-pr
+--derby-pro
+--derby-prop
+--derby-prope
+--derby-proper
+-- derby-propert
+-- derby-properti
+-- derby-propertie
+-- derby-propertiex
+
+-- both index and constraint
+select * from t1 --derby-properties index = t1_c1, constraint = cons1
+;
+select * from t1 exposedname --derby-properties index = t1_c1, constraint = cons1
+;
+
+-- index which includes columns in for update of list
+select * from t1 --derby-properties index = t1_c1 
+for update;
+select * from t1 exposedname --derby-properties index = t1_c1 
+for update;
+select * from t1 --derby-properties index = t1_c1 
+for update of c2, c1;
+select * from t1 exposedname --derby-properties index = t1_c1 
+for update of c2, c1;
+
+-- constraint which includes columns in for update of list
+select * from t1 --derby-properties constraint = cons1 
+for update;
+select * from t1 exposedname --derby-properties constraint = cons1 
+for update;
+select * from t1 --derby-properties constraint = cons1 
+for update of c2, c1;
+select * from t1 exposedname --derby-properties constraint = cons1 
+for update of c2, c1;
+
+-- select from view with bad derby-properties list
+select * from neg_v1;
+
+-- bad derby-properties tests on outer joins
+select * from t1 --derby-properties i = a 
+left outer join t2 on 1=1;
+select * from t1 left outer join t2 --derby-properties i = t1_c1 
+on 1=1;
+select * from t1 left outer join t2 --derby-properties index = t1_c1 
+on 1=1;
+select * from t1 right outer join t2 --derby-properties index = t1_c1 
+on 1=1;
+
+-- invalid joinStrategy
+select * from t1 a, t1 b --derby-properties joinStrategy = asdf
+;
+
+-- positive tests
+
+-- verify that statements are dependent on specified index or constraint
+commit;
+
+-- dependent on index
+prepare p1 as 'select * from t1 --derby-properties index = t1_c1
+';
+execute p1;
+drop index t1_c1;
+execute p1;
+remove p1;
+rollback;
+
+-- dependent on constraint
+prepare p2 as 'select * from t1 --derby-properties constraint = cons1
+';
+execute p2;
+alter table t1 drop constraint cons1;
+execute p2;
+remove p2;
+rollback;
+
+-- change display width in anticipation of runtimestatistics
+maximumdisplaywidth 5000;
+
+call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
+
+-- the token derby-properties is case insensitive. Few tests for that
+select * from t1 --DeRbY-pRoPeRtIeS index = t1_c1 
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- misspell derby-properties and make sure that it gets treated as a regular comment rather
than optimizer override
+select * from t1 --DeRbY-pRoPeRtIeAAA index = t1_c1 
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- force index, delimited identifier
+select * from t1 --derby-properties index = "t1_c2c1"
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- force table scan
+select * from t1 --derby-properties index = null
+;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- force index in create view
+select * from v1;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- cursor updateability test
+select * from t1 --derby-properties index = t1_c1 
+for update of c2, c3;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- joins
+select 1 from t1 a --derby-properties index = t1_c1
+, t2 b --derby-properties index = t2_c2
+;values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- comparisons that can't get pushed down
+select * from t1 --derby-properties index = t1_c1 
+where c1 = c1;
+select * from t1 --derby-properties index = t1_c1 
+where c1 = c2;
+select * from t1 --derby-properties index = t1_c1 
+where c1 + 1 = 1 + c1;
+
+-- outer joins
+select * from t1 --derby-properties index = t1_c1 
+left outer join t2 --derby-properties index = t2_c2 
+on t1.c1 = t2.c1;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+-- verify nestedloop joinStrategy
+select * from t1 a, t1 b --derby-properties joinStrategy = nestedloop
+where a.c1 = b.c1;
+values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+
+--negative test. insertModeValue is not avaible to a user and hence will
+--give a syntax error. There are some undocumented properties which are
+--allowed within Derby engine only and insertModeValue is one of them.
+create table temp1 (c1 int, c2 int, c3 int, constraint temp1cons1 primary key(c1, c2));
+insert into temp1 (c1,c2,c3) -- derby-properties insertModeValue=replace
+select * from t1;
+
+-- clean up
+drop view neg_v1;
+drop view v3;
+drop view v2;
+drop view v1;
+drop table t2;
+drop table t1;
+drop table temp1;

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/optimizerOverrides.sql
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/store/access.sql
Tue Dec 13 10:23:42 2005
@@ -809,7 +809,7 @@
 
 commit;
 
-update t2778 properties index = a_idx 
+update t2778 --derby-properties index = a_idx 
     set col10 = 
         '0_col10llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll012340_col10lllllllllllxxxxxx'
     where col00 = '0_';

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/phaseTester.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/phaseTester.java?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/phaseTester.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/phaseTester.java
Tue Dec 13 10:23:42 2005
@@ -13,6 +13,7 @@
 
 import java.sql.*;
 
+import org.apache.derbyTesting.functionTests.tests.jdbcapi.metadata;
 
 /**
 	Tests upgrades including soft upgrade.
@@ -107,6 +108,7 @@
 		}
 
 		System.out.println("\n\nSTART - phase " + PHASES[phase] + " db version " + dbMajor + "."
+ dbMinor);
+		System.out.println("jdbc url is " + url);
 
 		Connection conn;
 		
@@ -167,6 +169,12 @@
 
 			setPhaseComplete(conn, phase, passed);
 
+			//test the metadata calls at this stages of the db. This is to make
+			//sure that they don't break between these forms of upgrades of a db
+			metadata metadataTest = new metadata();
+			metadataTest.con = conn;
+			metadata.s = conn.createStatement();
+			metadataTest.runTest();
 		}
 
 		System.out.println("END - " + (passed ? "PASS" : "FAIL") + " - phase " + PHASES[phase]
+ " db version " + dbMajor + "." + dbMinor);
@@ -267,7 +275,7 @@
 		switch (phase) {
 		case PH_CREATE:
 			conn.createStatement().executeUpdate("CREATE TABLE PHASE(id INT NOT NULL, ok INT)");
-			conn.createStatement().executeUpdate("CREATE TABLE T1(id INT NOT NULL PRIMARY KEY, name
varchar(200))");
+			conn.createStatement().executeUpdate("CREATE TABLE TABLE1(id INT NOT NULL PRIMARY KEY,
name varchar(200))");
 			break;
 		case PH_SOFT_UPGRADE:
 			break;
@@ -286,7 +294,7 @@
 		ps.close();
 		
 		// perform some transactions
-		ps = conn.prepareStatement("INSERT INTO T1 VALUES (?, ?)");
+		ps = conn.prepareStatement("INSERT INTO TABLE1 VALUES (?, ?)");
 		for (int i = 1; i < 20; i++)
 		{
 			ps.setInt(1, i + (phase * 100));
@@ -294,14 +302,14 @@
 			ps.executeUpdate();
 		}
 		ps.close();
-		ps = conn.prepareStatement("UPDATE T1 set name = name || 'U' where id = ?");
+		ps = conn.prepareStatement("UPDATE TABLE1 set name = name || 'U' where id = ?");
 		for (int i = 1; i < 20; i+=3)
 		{
 			ps.setInt(1, i + (phase * 100));
 			ps.executeUpdate();
 		}
 		ps.close();
-		ps = conn.prepareStatement("DELETE FROM T1 where id = ?");
+		ps = conn.prepareStatement("DELETE FROM TABLE1 where id = ?");
 		for (int i = 1; i < 20; i+=4)
 		{
 			ps.setInt(1, i + (phase * 100));

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/runphases.ksh
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/runphases.ksh?rev=356562&r1=356561&r2=356562&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/runphases.ksh (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/upgradeTests/runphases.ksh Tue
Dec 13 10:23:42 2005
@@ -1,6 +1,12 @@
 
 #
 #
+# Added a master for the current run of this script into 
+# java\testing\org\apache\derbyTesting\functionTests\master\phaseTester.out
+# Until this script becomes part of the derby test harness, the output of the manual run
+# of this test can be manually checked against the checked master. This also means that
+# if this test is modified, the master should be updated. 
+#
 # runphases old_major old_minor old_engine new_engine
 #
 # e.g.



Mime
View raw message