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 dj...@apache.org on 2007/01/16 18:12:15 UTC
svn commit: r496773 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting:
functionTests/tests/jdbcapi/DatabaseMetaDataTest.java junit/JDBC.java
Author: djd
Date: Tue Jan 16 09:12:12 2007
New Revision: 496773
URL: http://svn.apache.org/viewvc?view=rev&rev=496773
Log:
DERBY-2242 (partial) Add more fixtures to DataabaseMetaDataTest and change
some JDBC.assert methods that check the full ResultSet to
return the number of rows seen in the ResultSet.
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java?view=diff&rev=496773&r1=496772&r2=496773
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/DatabaseMetaDataTest.java Tue Jan 16 09:12:12 2007
@@ -25,17 +25,32 @@
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.sql.Types;
import java.util.StringTokenizer;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
/**
* Test the DatabaseMetaData api.
+ * <P>
+ * For the methods that return a ResultSet to determine the
+ * attributes of SQL objects (e.g. getTables) two methods
+ * are provided. A non-modify and a modify one.
+ *
+ * <BR>
+ * The non-modify method tests that the getXXX method call works.
+ * This can be used by other tests where issues have been seen
+ * with database meta data, such as upgrade and read-only databases.
+ * The non-modify means that the test method does not change the database
+ * in order to test the return of the getXXX method is correct.
+ * <BR>
+ * The modify versions test
* Work in progress.
* Methods left to test from JDBC 3
*
@@ -49,10 +64,8 @@
* getPrimaryKeys
* getProcedureColumns
* getProcedures
- * getSchemas
* getTablePrivileges
- * getTables
- * getTableTypes
+ * getTables (modify)
* getTypeInfo
*/
public class DatabaseMetaDataTest extends BaseJDBCTestCase {
@@ -140,11 +153,27 @@
{ "SUBSTRING", "'Ruby the Rubicon Jeep'", "10", "7", },
{ "UCASE", "'Fernando Alonso'" }
};
+
+ /**
+ * Did the test modifiy the database.
+ */
+ private boolean modifiedDatabase;
public DatabaseMetaDataTest(String name) {
super(name);
}
+ protected void tearDown() throws Exception
+ {
+ if (modifiedDatabase)
+ {
+ Connection conn = getConnection();
+ conn.setAutoCommit(false);
+ CleanDatabaseTestSetup.cleanDatabase(conn);
+ }
+ super.tearDown();
+ }
+
public static Test suite() {
return TestConfiguration.defaultSuite(DatabaseMetaDataTest.class);
}
@@ -563,46 +592,284 @@
rs = dmd.getAttributes(null,null,null,null);
assertMetaDataResultSet(rs, null, null);
- assertFalse(rs.next());
- rs.close();
+ JDBC.assertEmpty(rs);
rs = dmd.getCatalogs();
- assertMetaDataResultSet(rs, null, null);
- assertFalse(rs.next());
- rs.close();
+ checkCatalogsShape(rs);
+ JDBC.assertEmpty(rs);
rs = dmd.getSuperTables(null,null,null);
assertMetaDataResultSet(rs, null, null);
- assertFalse(rs.next());
- rs.close();
+ JDBC.assertEmpty(rs);
rs = dmd.getSuperTypes(null,null,null);
assertMetaDataResultSet(rs, null, null);
- assertFalse(rs.next());
- rs.close();
+ JDBC.assertEmpty(rs);
rs = dmd.getUDTs(null,null,null,null);
assertMetaDataResultSet(rs, null, null);
- assertFalse(rs.next());
- rs.close();
+ JDBC.assertEmpty(rs);
rs = dmd.getVersionColumns(null,null,
usingDerbyNetClient() ? "%" : null);
+ checkVersionColumnsShape(rs);
+ JDBC.assertEmpty(rs);
+ }
+
+ /**
+ * Six cominations of valid identifiers with mixed
+ * case, to see how the various pattern matching
+ * and returned values handle them.
+ */
+ public final String[] IDS =
+ {
+ "one",
+ "TWO",
+ "ThReE",
+ "\"four\"",
+ "\"FIVE\"",
+ "\"sIx\""
+ };
+
+ /**
+ * Test getSchemas() without modifying the database.
+ *
+ * @throws SQLException
+ */
+ public void testGetSchemasNoModify() throws SQLException {
+
+ DatabaseMetaData dmd = getDMD();
+
+ ResultSet rs = dmd.getSchemas();
+ checkSchemasShape(rs);
+ int schemaCount = JDBC.assertDrainResults(rs);
+ // There are 11 builtin schemas including APP
+ assertTrue("too few schemas", schemaCount >= 11);
+ }
+
+ /**
+ * Test getSchemas().
+ *
+ * @throws SQLException
+ */
+ public void testGetSchemasModify() throws SQLException {
+ // Set to cleanup on teardown.
+ modifiedDatabase = true;
+
+ DatabaseMetaData dmd = getDMD();
+
+ Statement s = createStatement();
+ for (int i = 0; i < IDS.length; i++)
+ s.executeUpdate("CREATE SCHEMA " + IDS[i]);
+ s.close();
+
+ ResultSet rs = dmd.getSchemas();
+ checkSchemas(rs, new String[]
+ { "APP", "FIVE", "NULLID", "ONE",
+ "SQLJ", "SYS", "SYSCAT", "SYSCS_DIAG",
+ "SYSCS_UTIL", "SYSFUN", "SYSIBM", "SYSPROC", "SYSSTAT",
+ "THREE", "TWO", "four", "sIx"});
+ }
+ /**
+ * Check the returned information from a getSchemas().
+ * The passed in String[] expected is a list of the
+ * schemas expected to be returned in order.
+ */
+ public static void checkSchemas(ResultSet rs,
+ String[] expected) throws SQLException
+ {
+ // Since catalog is always NULL expand into the
+ // two value set expected by assertFullResultSet.
+ String[][] expected2cols = new String[expected.length][];
+ for (int i = 0; i < expected.length; i++)
+ {
+ expected2cols[i] = new String[] {expected[i], null};
+ }
+ checkSchemasShape(rs);
+ JDBC.assertFullResultSet(rs, expected2cols, true);
+ rs.close();
+ }
+
+ /**
+ * Check the shape of the ResultSet from any
+ * getSchemas call.
+ */
+ private static void checkSchemasShape(ResultSet rs) throws SQLException
+ {
assertMetaDataResultSet(rs,
- new String[] {
- "SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
- "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"
- },
- new int[] {
- Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.VARCHAR,
- Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT
- }
- );
+ new String[] {
+ "TABLE_SCHEM", "TABLE_CATALOG"
+ },
+ new int[] {
+ Types.VARCHAR, Types.VARCHAR
+ }
+ );
+ }
+
+ /**
+ * Test getTables() without modifying the database.
+ *
+ * @throws SQLException
+ */
+ public void testGetTablesNoModify() throws SQLException {
+
+ DatabaseMetaData dmd = getDMD();
+
+ ResultSet rs;
+
+ rs = dmd.getTables(null, null, null, null);
+ checkTablesShape(rs);
+ int allTableCount = JDBC.assertDrainResults(rs);
+ assertTrue("getTables() on all was empty!", allTableCount > 0);
+
+ rs = dmd.getTables("%", "%", "%", null);
+ checkTablesShape(rs);
+ assertEquals("Different counts from getTables",
+ allTableCount, JDBC.assertDrainResults(rs));
+
+ rs = dmd.getTables(null, "NO_such_schema", null, null);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ rs = dmd.getTables(null, "SQLJ", null, null);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ rs = dmd.getTables(null, "SQLJ", "%", null);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ rs = dmd.getTables(null, "SYS", "No_such_table", null);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ String[] userTableOnly = new String[] {"TABLE"};
+
+ // no user tables in SYS
+ rs = dmd.getTables(null, "SYS", null, userTableOnly);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ rs = dmd.getTables(null, "SYS", "%", userTableOnly);
+ checkTablesShape(rs);
+ JDBC.assertEmpty(rs);
+
+ String[] systemTableOnly = new String[] {"SYSTEM_TABLE"};
+
+ rs = dmd.getTables(null, "SYS", null, systemTableOnly);
+ checkTablesShape(rs);
+ int systemTableCount = JDBC.assertDrainResults(rs);
+ assertTrue("getTables() on system tables was empty!", systemTableCount > 0);
+
+ rs = dmd.getTables(null, "SYS", "%", systemTableOnly);
+ checkTablesShape(rs);
+ assertEquals(systemTableCount, JDBC.assertDrainResults(rs));
+
+ String[] viewOnly = new String[] {"VIEW"};
+ rs = dmd.getTables(null, "SYS", null, viewOnly);
+ JDBC.assertEmpty(rs);
+
+ rs = dmd.getTables(null, "SYS", "%", viewOnly);
+ JDBC.assertEmpty(rs);
+
+ String[] allTables = {"SYNONYM","SYSTEM TABLE","TABLE","VIEW"};
+ rs = dmd.getTables(null, null, null, allTables);
+ checkTablesShape(rs);
+ assertEquals("Different counts from getTables",
+ allTableCount, JDBC.assertDrainResults(rs));
+ rs = dmd.getTables("%", "%", "%", allTables);
+ checkTablesShape(rs);
+ assertEquals("Different counts from getTables",
+ allTableCount, JDBC.assertDrainResults(rs));
- assertFalse(rs.next());
+ }
+
+ /**
+ * Test getTableTypes()
+ */
+ public void testTableTypes() throws SQLException
+ {
+ DatabaseMetaData dmd = getDMD();
+
+ ResultSet rs = dmd.getTableTypes();
+ assertMetaDataResultSet(rs,
+ new String[] {
+ "TABLE_TYPE"
+ },
+ new int[] {
+ Types.VARCHAR
+ }
+ );
+
+ JDBC.assertFullResultSet(rs, new String[][]
+ {
+ {"SYNONYM"},{"SYSTEM TABLE"},{"TABLE"},{"VIEW"},
+ }, true);
rs.close();
-
+ }
+ /**
+ * Check the shape of the ResultSet from any getTables call.
+ */
+ private void checkTablesShape(ResultSet rs) throws SQLException
+ {
+ assertMetaDataResultSet(rs,
+ new String[] {
+ "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE",
+ "REMARKS", "TYPE_CAT", "TYPE_SCHEM", "TYPE_NAME",
+ "SELF_REFERENCING_COL_NAME", "REF_GENERATION"
+ },
+ new int[] {
+ Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
+ Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
+ Types.VARCHAR, Types.VARCHAR
+ }
+ );
+ }
+
+ /*
+ # TABLE_CAT String => table catalog (may be null)
+ # TABLE_SCHEM String => table schema (may be null)
+ # TABLE_NAME String => table name
+ # TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
+ # REMARKS String => explanatory comment on the table
+ # TYPE_CAT String => the types catalog (may be null)
+ # TYPE_SCHEM String => the types schema (may be null)
+ # TYPE_NAME String => type name (may be null)
+ # SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
+ # REF_GENERATION
+*/
+ /**
+ * Check the shape of the ResultSet from any getCatlogs call.
+ */
+ private void checkCatalogsShape(ResultSet rs) throws SQLException
+ {
+ assertMetaDataResultSet(rs,
+ new String[] {
+ "TABLE_CAT"
+ },
+ new int[] {
+ Types.CHAR
+ }
+ );
+ }
+
+ /**
+ * Check the shape of the ResultSet from any
+ * getVersionColumns call.
+ */
+ private static void checkVersionColumnsShape(ResultSet rs) throws SQLException
+ {
+ assertMetaDataResultSet(rs,
+ new String[] {
+ "SCOPE", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
+ "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "PSEUDO_COLUMN"
+ },
+ new int[] {
+ Types.SMALLINT, Types.VARCHAR, Types.INTEGER, Types.VARCHAR,
+ Types.INTEGER, Types.INTEGER, Types.SMALLINT, Types.SMALLINT
+ }
+ );
}
public static void assertMetaDataResultSet(ResultSet rs,
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java?view=diff&rev=496773&r1=496772&r2=496773
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/JDBC.java Tue Jan 16 09:12:12 2007
@@ -395,6 +395,20 @@
column.close();
}
}
+
+ /**
+ * Assert a result set is empty.
+ * If the result set is not empty it will
+ * be drained before the check to see if
+ * it is empty.
+ * The ResultSet is closed by this method.
+
+ */
+ public static void assertEmpty(ResultSet rs)
+ throws SQLException
+ {
+ assertDrainResults(rs, 0);
+ }
/**
* Drain a single ResultSet by reading all of its
@@ -404,14 +418,18 @@
*
* Provides simple testing of the ResultSet when the
* contents are not important.
+ *
+ * The ResultSet is closed by this method.
*
* @param rs Result set to drain.
+ * @return the number of rows seen.
+
* @throws SQLException
*/
- public static void assertDrainResults(ResultSet rs)
+ public static int assertDrainResults(ResultSet rs)
throws SQLException
{
- assertDrainResults(rs, -1);
+ return assertDrainResults(rs, -1);
}
/**
@@ -419,13 +437,16 @@
* above. If the received row count is non-negative,
* this method also asserts that the number of rows
* in the result set matches the received row count.
+ *
+ * The ResultSet is closed by this method.
*
* @param rs Result set to drain.
* @param expectedRows If non-negative, indicates how
* many rows we expected to see in the result set.
+ * @return the number of rows seen.
* @throws SQLException
*/
- public static void assertDrainResults(ResultSet rs,
+ public static int assertDrainResults(ResultSet rs,
int expectedRows) throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
@@ -442,7 +463,9 @@
rs.close();
if (expectedRows >= 0)
- Assert.assertEquals("Unexpected row count:", expectedRows, rows);
+ Assert.assertEquals("Unexpected row count:", expectedRows, rows);
+
+ return rows;
}
/**