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 fu...@apache.org on 2007/03/27 08:42:01 UTC
svn commit: r522772 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/ master/DerbyNet/ master/DerbyNetClient/
master/DerbyNetClient/jdk14/ suites/ tests/lang/
Author: fuzzylogic
Date: Mon Mar 26 23:41:59 2007
New Revision: 522772
URL: http://svn.apache.org/viewvc?view=rev&rev=522772
Log:
DERBY-2484: Convert syscat.sql to JUnit
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java (with props)
Removed:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNet/syscat.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/jdk14/syscat.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DerbyNetClient/syscat.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/syscat.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/syscat_app.properties
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=522772&r1=522771&r2=522772
==============================================================================
--- 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 Mon Mar 26 23:41:59 2007
@@ -74,7 +74,6 @@
lang/subquery2.sql
lang/subqueryFlattening.sql
lang/supersimple.sql
-lang/syscat.sql
lang/timestampArith.java
lang/triggerBeforeTrig.sql
lang/triggerGeneral.sql
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbynetmats.runall?view=diff&rev=522772&r1=522771&r2=522772
==============================================================================
--- 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 Mon Mar 26 23:41:59 2007
@@ -27,7 +27,6 @@
lang/simpleScroll.sql
lang/supersimple.sql
lang/wisconsin.java
-lang/syscat.sql
lang/ShutdownDatabase.java
store/holdCursorExternalSortJDBC30.sql
store/holdCursorJDBC30.sql
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java?view=auto&rev=522772
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java Mon Mar 26 23:41:59 2007
@@ -0,0 +1,640 @@
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Tests concerning the system catalogs.
+ *
+ * Retaining comment from previous .sql test:
+ * RESOLVE - add selects from sysdepends when simplified
+ *
+ */
+public class SystemCatalogTest extends BaseJDBCTestCase {
+
+ public SystemCatalogTest(String name) {
+ super(name);
+ }
+
+ public static Test suite() {
+ Test suite = TestConfiguration.defaultSuite(SystemCatalogTest.class);
+ return TestConfiguration.singleUseDatabaseDecorator(suite);
+ }
+
+ /**
+ * Test that the user cannot execute any DDL statements on the system tables.
+ * @throws SQLException
+ */
+ public void testNoUserDDLOnSystemTables() throws SQLException {
+ Statement s = createStatement();
+
+ try{
+ s.executeUpdate("drop table sys.systables");
+ } catch (SQLException e)
+ {
+ assertSQLState("X0Y56", e);
+ }
+
+ try{
+ s.executeUpdate("drop index sys.sysaliases_index2");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("create index trash on sys.systables(tableid)");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("create table sys.usertable(c1 int)");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("create view sys.userview as select * from sys.systables");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("alter table sys.systables drop column tablename");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("alter table sys.systables add column foo int");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("alter table sys.systables alter column tablename null");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+
+ try{
+ s.executeUpdate("alter table sys.systables drop primary key");
+ } catch (SQLException e)
+ {
+ assertSQLState("42X62", e);
+ }
+ }
+
+ /**
+ * Test that the system tables cannot be changed by various DML statements.
+ *
+ * @throws SQLException
+ */
+ public void testSystemCatalogsNotUpdatable() throws SQLException{
+ Connection c = getConnection();
+ Statement s = c.createStatement();
+
+ c.setAutoCommit(false);
+
+ try{
+ s.executeUpdate("delete from sys.systables");
+ } catch (SQLException e)
+ {
+ assertSQLState("42Y25", e);
+ }
+
+ try{
+ s.executeUpdate("update sys.systables set tablename = tablename || 'trash'");
+ } catch (SQLException e)
+ {
+ assertSQLState("42Y25", e);
+ }
+
+ try{
+ s.executeUpdate("insert into sys.systables select * from sys.systables");
+ } catch (SQLException e)
+ {
+ assertSQLState("42Y25", e);
+ }
+
+ try{
+ ResultSet rs = s.executeQuery("select tablename from sys.systables for update of tablename");
+ } catch (SQLException e)
+ {
+ assertSQLState("42Y90", e);
+ }
+
+ c.rollback();
+ c.setAutoCommit(true);
+
+ }
+
+ /**
+ * Test various default store properties for the system tables.
+ *
+ * @throws SQLException
+ */
+ public void testSystemCatalogStoreProperties() throws SQLException{
+ Statement s = createStatement();
+ s.execute("create function gatp(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllTableProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");
+ s.execute("create function gaip(SCH VARCHAR(128), TBL VARCHAR(128)) RETURNS VARCHAR(1000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.TestPropertyInfo.getAllIndexProperties' LANGUAGE JAVA PARAMETER STYLE JAVA");
+
+ // get the properties for the heaps
+ ResultSet rs = s.executeQuery("select tablename,gatp('SYS', tablename) from sys.systables order by tablename");
+ boolean nonEmptyResultSet = false;
+ String tablename = null;
+ String sysdummy = "SYSDUMMY1";
+ String heapResult = "{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=12, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=false }";
+ while(rs.next()) {
+ nonEmptyResultSet = true;
+ tablename = rs.getString(1);
+ if (tablename.equals(sysdummy)) {
+ assertTrue(rs.getString(2).startsWith("{ }"));
+ } else {
+ assertTrue(rs.getString(2).startsWith(heapResult));
+ }
+ }
+ assertTrue(nonEmptyResultSet);
+ rs.close();
+
+ // get the properties for the indexes
+ rs = s.executeQuery("select conglomeratename, gaip('SYS', conglomeratename) from sys.sysconglomerates where isindex order by conglomeratename");
+ nonEmptyResultSet = false;
+ String indexResult = "{ derby.storage.initialPages=1, derby.storage.minimumRecordSize=1, derby.storage.pageReservedSpace=0, derby.storage.pageSize=4096, derby.storage.reusableRecordId=true }";
+ while(rs.next()) {
+ nonEmptyResultSet = true;
+ assertTrue(rs.getString(2).startsWith(indexResult));
+ }
+ assertTrue(nonEmptyResultSet);
+ rs.close();
+ s.close();
+ }
+
+ /**
+ * Test that each system table has a table type of "S".
+ *
+ * @throws SQLException
+ */
+ public void testSystemCatalogTableTypes() throws SQLException {
+ Statement s = createStatement();
+ ResultSet rs = s.executeQuery("select TABLENAME, TABLETYPE from sys.systables order by tablename");
+
+ boolean nonEmptyResultSet = false;
+ while(rs.next()) {
+ nonEmptyResultSet = true;
+ assertEquals("S", rs.getString(2));
+ }
+ assertTrue(nonEmptyResultSet);
+ rs.close();
+ s.close();
+ }
+
+ /**
+ * Check that all the tables for their expected columns.
+ *
+ * @throws SQLException
+ */
+ public void testSystemCatalogColumns() throws SQLException {
+ String [][] expected = {
+ {"SYSALIASES", "ALIAS", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSALIASES", "ALIASID", "1", "CHAR(36) NOT NULL"},
+ {"SYSALIASES", "ALIASINFO", "8", "org.apache.derby.catalog.AliasInfo"},
+ {"SYSALIASES", "ALIASTYPE", "5", "CHAR(1) NOT NULL"},
+ {"SYSALIASES", "JAVACLASSNAME", "4", "LONG VARCHAR NOT NULL"},
+ {"SYSALIASES", "NAMESPACE", "6", "CHAR(1) NOT NULL"},
+ {"SYSALIASES", "SCHEMAID", "3", "CHAR(36)"},
+ {"SYSALIASES", "SPECIFICNAME", "9", "VARCHAR(128) NOT NULL"},
+ {"SYSALIASES", "SYSTEMALIAS", "7", "BOOLEAN NOT NULL"},
+ {"SYSCHECKS", "CHECKDEFINITION", "2", "LONG VARCHAR NOT NULL"},
+ {"SYSCHECKS", "CONSTRAINTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSCHECKS", "REFERENCEDCOLUMNS", "3", "org.apache.derby.catalog.ReferencedColumns NOT NULL"},
+ {"SYSCOLPERMS", "COLPERMSID", "1", "CHAR(36) NOT NULL"},
+ {"SYSCOLPERMS", "COLUMNS", "6", "org.apache.derby.iapi.services.io.FormatableBitSet NOT NULL"},
+ {"SYSCOLPERMS", "GRANTEE", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSCOLPERMS", "GRANTOR", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSCOLPERMS", "TABLEID", "4", "CHAR(36) NOT NULL"},
+ {"SYSCOLPERMS", "TYPE", "5", "CHAR(1) NOT NULL"},
+ {"SYSCOLUMNS", "AUTOINCREMENTINC", "9", "BIGINT"},
+ {"SYSCOLUMNS", "AUTOINCREMENTSTART", "8", "BIGINT"},
+ {"SYSCOLUMNS", "AUTOINCREMENTVALUE", "7", "BIGINT"},
+ {"SYSCOLUMNS", "COLUMNDATATYPE", "4", "org.apache.derby.catalog.TypeDescriptor NOT NULL"},
+ {"SYSCOLUMNS", "COLUMNDEFAULT", "5", "java.io.Serializable"},
+ {"SYSCOLUMNS", "COLUMNDEFAULTID", "6", "CHAR(36)"},
+ {"SYSCOLUMNS", "COLUMNNAME", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSCOLUMNS", "COLUMNNUMBER", "3", "INTEGER NOT NULL"},
+ {"SYSCOLUMNS", "REFERENCEID", "1", "CHAR(36) NOT NULL"},
+ {"SYSCONGLOMERATES", "CONGLOMERATEID", "8", "CHAR(36) NOT NULL"},
+ {"SYSCONGLOMERATES", "CONGLOMERATENAME", "4", "VARCHAR(128)"},
+ {"SYSCONGLOMERATES", "CONGLOMERATENUMBER", "3", "BIGINT NOT NULL"},
+ {"SYSCONGLOMERATES", "DESCRIPTOR", "6", "org.apache.derby.catalog.IndexDescriptor"},
+ {"SYSCONGLOMERATES", "ISCONSTRAINT", "7", "BOOLEAN"},
+ {"SYSCONGLOMERATES", "ISINDEX", "5", "BOOLEAN NOT NULL"},
+ {"SYSCONGLOMERATES", "SCHEMAID", "1", "CHAR(36) NOT NULL"},
+ {"SYSCONGLOMERATES", "TABLEID", "2", "CHAR(36) NOT NULL"},
+ {"SYSCONSTRAINTS", "CONSTRAINTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSCONSTRAINTS", "CONSTRAINTNAME", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSCONSTRAINTS", "REFERENCECOUNT", "7", "INTEGER NOT NULL"},
+ {"SYSCONSTRAINTS", "SCHEMAID", "5", "CHAR(36) NOT NULL"},
+ {"SYSCONSTRAINTS", "STATE", "6", "CHAR(1) NOT NULL"},
+ {"SYSCONSTRAINTS", "TABLEID", "2", "CHAR(36) NOT NULL"},
+ {"SYSCONSTRAINTS", "TYPE", "4", "CHAR(1) NOT NULL"},
+ {"SYSDEPENDS", "DEPENDENTFINDER", "2", "org.apache.derby.catalog.DependableFinder NOT NULL"},
+ {"SYSDEPENDS", "DEPENDENTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSDEPENDS", "PROVIDERFINDER", "4", "org.apache.derby.catalog.DependableFinder NOT NULL"},
+ {"SYSDEPENDS", "PROVIDERID", "3", "CHAR(36) NOT NULL"},
+ {"SYSDUMMY1", "IBMREQD", "1", "CHAR(1)"},
+ {"SYSFILES", "FILEID", "1", "CHAR(36) NOT NULL"},
+ {"SYSFILES", "FILENAME", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSFILES", "GENERATIONID", "4", "BIGINT NOT NULL"},
+ {"SYSFILES", "SCHEMAID", "2", "CHAR(36) NOT NULL"},
+ {"SYSFOREIGNKEYS", "CONGLOMERATEID", "2", "CHAR(36) NOT NULL"},
+ {"SYSFOREIGNKEYS", "CONSTRAINTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSFOREIGNKEYS", "DELETERULE", "4", "CHAR(1) NOT NULL"},
+ {"SYSFOREIGNKEYS", "KEYCONSTRAINTID", "3", "CHAR(36) NOT NULL"},
+ {"SYSFOREIGNKEYS", "UPDATERULE", "5", "CHAR(1) NOT NULL"},
+ {"SYSKEYS", "CONGLOMERATEID", "2", "CHAR(36) NOT NULL"},
+ {"SYSKEYS", "CONSTRAINTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSROUTINEPERMS", "ALIASID", "4", "CHAR(36) NOT NULL"},
+ {"SYSROUTINEPERMS", "GRANTEE", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSROUTINEPERMS", "GRANTOPTION", "5", "CHAR(1) NOT NULL"},
+ {"SYSROUTINEPERMS", "GRANTOR", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSROUTINEPERMS", "ROUTINEPERMSID", "1", "CHAR(36) NOT NULL"},
+ {"SYSSCHEMAS", "AUTHORIZATIONID", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSSCHEMAS", "SCHEMAID", "1", "CHAR(36) NOT NULL"},
+ {"SYSSCHEMAS", "SCHEMANAME", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSSTATEMENTS", "COMPILATIONSCHEMAID", "8", "CHAR(36)"},
+ {"SYSSTATEMENTS", "LASTCOMPILED", "7", "TIMESTAMP"},
+ {"SYSSTATEMENTS", "SCHEMAID", "3", "CHAR(36) NOT NULL"},
+ {"SYSSTATEMENTS", "STMTID", "1", "CHAR(36) NOT NULL"},
+ {"SYSSTATEMENTS", "STMTNAME", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSSTATEMENTS", "TEXT", "6", "LONG VARCHAR NOT NULL"},
+ {"SYSSTATEMENTS", "TYPE", "4", "CHAR(1) NOT NULL"},
+ {"SYSSTATEMENTS", "USINGTEXT", "9", "LONG VARCHAR"},
+ {"SYSSTATEMENTS", "VALID", "5", "BOOLEAN NOT NULL"},
+ {"SYSSTATISTICS", "COLCOUNT", "7", "INTEGER NOT NULL"},
+ {"SYSSTATISTICS", "CREATIONTIMESTAMP", "4", "TIMESTAMP NOT NULL"},
+ {"SYSSTATISTICS", "REFERENCEID", "2", "CHAR(36) NOT NULL"},
+ {"SYSSTATISTICS", "STATID", "1", "CHAR(36) NOT NULL"},
+ {"SYSSTATISTICS", "STATISTICS", "8", "org.apache.derby.catalog.Statistics NOT NULL"},
+ {"SYSSTATISTICS", "TABLEID", "3", "CHAR(36) NOT NULL"},
+ {"SYSSTATISTICS", "TYPE", "5", "CHAR(1) NOT NULL"},
+ {"SYSSTATISTICS", "VALID", "6", "BOOLEAN NOT NULL"},
+ {"SYSTABLEPERMS", "DELETEPRIV", "6", "CHAR(1) NOT NULL"},
+ {"SYSTABLEPERMS", "GRANTEE", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSTABLEPERMS", "GRANTOR", "3", "VARCHAR(128) NOT NULL"},
+ {"SYSTABLEPERMS", "INSERTPRIV", "7", "CHAR(1) NOT NULL"},
+ {"SYSTABLEPERMS", "REFERENCESPRIV", "9", "CHAR(1) NOT NULL"},
+ {"SYSTABLEPERMS", "SELECTPRIV", "5", "CHAR(1) NOT NULL"},
+ {"SYSTABLEPERMS", "TABLEID", "4", "CHAR(36) NOT NULL"},
+ {"SYSTABLEPERMS", "TABLEPERMSID", "1", "CHAR(36) NOT NULL"},
+ {"SYSTABLEPERMS", "TRIGGERPRIV", "10", "CHAR(1) NOT NULL"},
+ {"SYSTABLEPERMS", "UPDATEPRIV", "8", "CHAR(1) NOT NULL"},
+ {"SYSTABLES", "LOCKGRANULARITY", "5", "CHAR(1) NOT NULL"},
+ {"SYSTABLES", "SCHEMAID", "4", "CHAR(36) NOT NULL"},
+ {"SYSTABLES", "TABLEID", "1", "CHAR(36) NOT NULL"},
+ {"SYSTABLES", "TABLENAME", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSTABLES", "TABLETYPE", "3", "CHAR(1) NOT NULL"},
+ {"SYSTRIGGERS", "ACTIONSTMTID", "11", "CHAR(36)"},
+ {"SYSTRIGGERS", "CREATIONTIMESTAMP", "4", "TIMESTAMP NOT NULL"},
+ {"SYSTRIGGERS", "EVENT", "5", "CHAR(1) NOT NULL"},
+ {"SYSTRIGGERS", "FIRINGTIME", "6", "CHAR(1) NOT NULL"},
+ {"SYSTRIGGERS", "NEWREFERENCINGNAME", "17", "VARCHAR(128)"},
+ {"SYSTRIGGERS", "OLDREFERENCINGNAME", "16", "VARCHAR(128)"},
+ {"SYSTRIGGERS", "REFERENCEDCOLUMNS", "12", "org.apache.derby.catalog.ReferencedColumns"},
+ {"SYSTRIGGERS", "REFERENCINGNEW", "15", "BOOLEAN"},
+ {"SYSTRIGGERS", "REFERENCINGOLD", "14", "BOOLEAN"},
+ {"SYSTRIGGERS", "SCHEMAID", "3", "CHAR(36) NOT NULL"},
+ {"SYSTRIGGERS", "STATE", "8", "CHAR(1) NOT NULL"},
+ {"SYSTRIGGERS", "TABLEID", "9", "CHAR(36) NOT NULL"},
+ {"SYSTRIGGERS", "TRIGGERDEFINITION", "13", "LONG VARCHAR"},
+ {"SYSTRIGGERS", "TRIGGERID", "1", "CHAR(36) NOT NULL"},
+ {"SYSTRIGGERS", "TRIGGERNAME", "2", "VARCHAR(128) NOT NULL"},
+ {"SYSTRIGGERS", "TYPE", "7", "CHAR(1) NOT NULL"},
+ {"SYSTRIGGERS", "WHENSTMTID", "10", "CHAR(36)"},
+ {"SYSVIEWS", "CHECKOPTION", "3", "CHAR(1) NOT NULL"},
+ {"SYSVIEWS", "COMPILATIONSCHEMAID", "4", "CHAR(36)"},
+ {"SYSVIEWS", "TABLEID", "1", "CHAR(36) NOT NULL"},
+ {"SYSVIEWS", "VIEWDEFINITION", "2", "LONG VARCHAR NOT NULL"}
+ };
+
+ Statement s = createStatement();
+
+ ResultSet rs = s.executeQuery("select TABLENAME, COLUMNNAME, COLUMNNUMBER, COLUMNDATATYPE from sys.systables t, sys.syscolumns c" +
+ " where t.TABLEID=c.REFERENCEID order by TABLENAME, COLUMNNAME");
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.close();
+
+ }
+
+ public void testSystemCatalogIndexes() throws SQLException{
+ String [][] expected =
+ {
+ {"SYSALIASES", "SYSALIASES_HEAP", "false"},
+ {"SYSALIASES", "SYSALIASES_INDEX3", "true"},
+ {"SYSALIASES", "SYSALIASES_INDEX2", "true"},
+ {"SYSALIASES", "SYSALIASES_INDEX1", "true"},
+ {"SYSCHECKS", "SYSCHECKS_HEAP", "false"},
+ {"SYSCHECKS", "SYSCHECKS_INDEX1", "true"},
+ {"SYSCOLPERMS", "SYSCOLPERMS_HEAP", "false"},
+ {"SYSCOLPERMS", "SYSCOLPERMS_INDEX3", "true"},
+ {"SYSCOLPERMS", "SYSCOLPERMS_INDEX2", "true"},
+ {"SYSCOLPERMS", "SYSCOLPERMS_INDEX1", "true"},
+ {"SYSCOLUMNS", "SYSCOLUMNS_HEAP", "false"},
+ {"SYSCOLUMNS", "SYSCOLUMNS_INDEX2", "true"},
+ {"SYSCOLUMNS", "SYSCOLUMNS_INDEX1", "true"},
+ {"SYSCONGLOMERATES", "SYSCONGLOMERATES_HEAP", "false"},
+ {"SYSCONGLOMERATES", "SYSCONGLOMERATES_INDEX3", "true"},
+ {"SYSCONGLOMERATES", "SYSCONGLOMERATES_INDEX2", "true"},
+ {"SYSCONGLOMERATES", "SYSCONGLOMERATES_INDEX1", "true"},
+ {"SYSCONSTRAINTS", "SYSCONSTRAINTS_HEAP", "false"},
+ {"SYSCONSTRAINTS", "SYSCONSTRAINTS_INDEX3", "true"},
+ {"SYSCONSTRAINTS", "SYSCONSTRAINTS_INDEX2", "true"},
+ {"SYSCONSTRAINTS", "SYSCONSTRAINTS_INDEX1", "true"},
+ {"SYSDEPENDS", "SYSDEPENDS_HEAP", "false"},
+ {"SYSDEPENDS", "SYSDEPENDS_INDEX2", "true"},
+ {"SYSDEPENDS", "SYSDEPENDS_INDEX1", "true"},
+ {"SYSDUMMY1", "SYSDUMMY1_HEAP", "false"},
+ {"SYSFILES", "SYSFILES_HEAP", "false"},
+ {"SYSFILES", "SYSFILES_INDEX2", "true"},
+ {"SYSFILES", "SYSFILES_INDEX1", "true"},
+ {"SYSFOREIGNKEYS", "SYSFOREIGNKEYS_HEAP", "false"},
+ {"SYSFOREIGNKEYS", "SYSFOREIGNKEYS_INDEX2", "true"},
+ {"SYSFOREIGNKEYS", "SYSFOREIGNKEYS_INDEX1", "true"},
+ {"SYSKEYS", "SYSKEYS_HEAP", "false"},
+ {"SYSKEYS", "SYSKEYS_INDEX1", "true"},
+ {"SYSROUTINEPERMS", "SYSROUTINEPERMS_HEAP", "false"},
+ {"SYSROUTINEPERMS", "SYSROUTINEPERMS_INDEX3", "true"},
+ {"SYSROUTINEPERMS", "SYSROUTINEPERMS_INDEX2", "true"},
+ {"SYSROUTINEPERMS", "SYSROUTINEPERMS_INDEX1", "true"},
+ {"SYSSCHEMAS", "SYSSCHEMAS_HEAP", "false"},
+ {"SYSSCHEMAS", "SYSSCHEMAS_INDEX2", "true"},
+ {"SYSSCHEMAS", "SYSSCHEMAS_INDEX1", "true"},
+ {"SYSSTATEMENTS", "SYSSTATEMENTS_HEAP", "false"},
+ {"SYSSTATEMENTS", "SYSSTATEMENTS_INDEX2", "true"},
+ {"SYSSTATEMENTS", "SYSSTATEMENTS_INDEX1", "true"},
+ {"SYSSTATISTICS", "SYSSTATISTICS_HEAP", "false"},
+ {"SYSSTATISTICS", "SYSSTATISTICS_INDEX1", "true"},
+ {"SYSTABLEPERMS", "SYSTABLEPERMS_HEAP", "false"},
+ {"SYSTABLEPERMS", "SYSTABLEPERMS_INDEX3", "true"},
+ {"SYSTABLEPERMS", "SYSTABLEPERMS_INDEX2", "true"},
+ {"SYSTABLEPERMS", "SYSTABLEPERMS_INDEX1", "true"},
+ {"SYSTABLES", "SYSTABLES_HEAP", "false"},
+ {"SYSTABLES", "SYSTABLES_INDEX2", "true"},
+ {"SYSTABLES", "SYSTABLES_INDEX1", "true"},
+ {"SYSTRIGGERS", "SYSTRIGGERS_HEAP", "false"},
+ {"SYSTRIGGERS", "SYSTRIGGERS_INDEX3", "true"},
+ {"SYSTRIGGERS", "SYSTRIGGERS_INDEX2", "true"},
+ {"SYSTRIGGERS", "SYSTRIGGERS_INDEX1", "true"},
+ {"SYSVIEWS", "SYSVIEWS_HEAP", "false"},
+ {"SYSVIEWS", "SYSVIEWS_INDEX1", "true"},
+ };
+
+ Statement s = createStatement();
+
+ ResultSet rs = s.executeQuery("select TABLENAME, CONGLOMERATENAME, ISINDEX from sys.systables t, sys.sysconglomerates c"
+ + " where t.TABLEID=c.TABLEID order by TABLENAME, ISINDEX");
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.close();
+ }
+
+ /**
+ * Check that a newly created table and its columns appear in SYSTABLES and SYSCOLUMNS
+ * @throws SQLException
+ */
+ public void testNewTableInSystemCatalogs() throws SQLException {
+ Statement s = createStatement();
+
+ s.execute("create table t (i int, s smallint)");
+
+ ResultSet rs = s.executeQuery("select TABLETYPE from sys.systables where tablename = 'T'");
+ JDBC.assertSingleValueResultSet(rs, "T");
+ rs.close();
+
+ rs = s.executeQuery("select TABLENAME, COLUMNNAME, COLUMNNUMBER, columndatatype from sys.systables t, sys.syscolumns c" +
+ " where t.TABLEID=c.REFERENCEID and t.tablename = 'T' order by TABLENAME, COLUMNNAME");
+ String[][] expected = {{"T", "I", "1", "INTEGER"}, {"T", "S", "2", "SMALLINT"}};
+ JDBC.assertFullResultSet(rs,expected);
+ rs.close();
+
+ rs = s.executeQuery("select TABLENAME, ISINDEX from sys.systables t, sys.sysconglomerates c where t.TABLEID=c.TABLEID and t.TABLENAME = 'T' order by TABLENAME, ISINDEX");
+ expected = new String[][] {{"T", "false"},};
+ JDBC.assertFullResultSet(rs,expected);
+ rs.close();
+
+ s.execute("drop table t");
+
+ s.close();
+ }
+
+ /**
+ * Test that table and column names over thirty characters are recorded
+ * properly in the system tables.
+ *
+ * @throws SQLException
+ */
+ public void testOverThirtyCharsInTableName() throws SQLException {
+ Statement s = createStatement();
+
+ s.execute("create table t234567890123456789012345678901234567890 (c234567890123456789012345678901234567890 int)");
+
+ ResultSet rs = s.executeQuery("select TABLENAME from sys.systables where length(TABLENAME) > 30 order by tablename");
+ JDBC.assertSingleValueResultSet(rs, "T234567890123456789012345678901234567890");
+ rs.close();
+
+ rs = s.executeQuery("select COLUMNNAME from sys.syscolumns where {fn length(COLUMNNAME)} > 30 order by columnname");
+ JDBC.assertSingleValueResultSet(rs, "C234567890123456789012345678901234567890");
+ rs.close();
+
+ s.execute("drop table t234567890123456789012345678901234567890");
+ s.close();
+ }
+
+ /**
+ * Test that named constraints and unnamed constraints are recorded in the system tables properly.
+ *
+ * @throws SQLException
+ */
+ public void testPrimaryAndUniqueKeysInSystemCatalogs() throws SQLException {
+ Statement s = createStatement();
+ String getNamedConstraintsQuery = "select c.constraintname, c.type from sys.sysconstraints c, sys.systables t "
+ + "where c.tableid = t.tableid and not t.tablename like 'UNNAMED%' order by c.constraintname";
+
+ s.execute("create table primkey1 (c1 int not null constraint prim1 primary key)");
+ String [][] expected = new String[][] {{"PRIM1", "P"}};
+ ResultSet rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected, true);
+ rs.close();
+
+ s.execute("create table unnamed_primkey2 (c1 int not null primary key)");
+ rs = s.executeQuery("select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and t.tablename = 'UNNAMED_PRIMKEY2' order by c.constraintname");
+ assertTrue(rs.next());
+ assertEquals("P", rs.getString(2));
+ assertFalse(rs.next());
+ rs.close();
+ rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.execute("create table primkey3 (c1 int not null, c2 int not null, constraint prim3 primary key(c2, c1))");
+ expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}};
+ rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.execute("create table uniquekey1 (c1 int not null constraint uniq1 unique)");
+ expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}, {"UNIQ1", "U"}};
+ rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.execute("create table unnamed_uniquekey2 (c1 int not null unique)");
+ rs = s.executeQuery("select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where c.tableid = t.tableid and t.tablename = 'UNNAMED_UNIQUEKEY2' order by c.constraintname");
+ assertTrue(rs.next());
+ assertEquals("U", rs.getString(2));
+ assertFalse(rs.next());
+ rs.close();
+ rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.execute("create table uniquekey3 (c1 int not null, c2 int not null, constraint uniq3 unique(c2, c1))");
+ expected = new String[][] {{"PRIM1", "P"}, {"PRIM3", "P"}, {"UNIQ1", "U"}, {"UNIQ3", "U"}};
+ rs = s.executeQuery(getNamedConstraintsQuery);
+ JDBC.assertFullResultSet(rs, expected);
+ rs.close();
+
+ s.execute("drop table primkey1");
+ s.execute("drop table unnamed_primkey2");
+ s.execute("drop table primkey3");
+ s.execute("drop table uniquekey1");
+ s.execute("drop table unnamed_uniquekey2");
+ s.execute("drop table uniquekey3");
+
+ s.close();
+ }
+
+ /**
+ * Test that view creation is recorded in the system tables.
+ *
+ * @throws SQLException
+ */
+ public void testViewsOfSystemCatalogs() throws SQLException {
+ Statement s = createStatement();
+ s.execute("create table t (i int, s smallint)");
+ s.execute("create table uniquekey3 (c1 int not null, c2 int not null, constraint uniq3 unique(c2, c1))");
+ s.execute("create view dummyview as select * from t, uniquekey3");
+
+ ResultSet rs = s.executeQuery("select tablename from sys.systables t, sys.sysviews v where t.tableid = v.tableid order by tablename");
+ JDBC.assertSingleValueResultSet(rs, "DUMMYVIEW");
+ rs.close();
+
+ s.execute("drop view dummyview");
+ s.execute("drop table t");
+ s.execute("drop table uniquekey3");
+ s.close();
+ }
+
+ /**
+ * Check that column datatypes are reported correctly, both in
+ * embedded and client/server modes
+ *
+ * @throws SQLException
+ */
+ public void testColumnDatatypesInSystemCatalogs() throws SQLException {
+ Statement s = createStatement();
+
+ s.execute("create table decimal_tab (dcol decimal(5,2), ncol numeric(5,2) default 1.0)");
+ ResultSet rs = s.executeQuery("select columnname, columndatatype from sys.syscolumns where columnname IN ('DCOL', 'NCOL') order by columnname");
+ //DCOL
+ assertTrue(rs.next());
+ assertTrue(rs.getString(2).startsWith("DECIMAL(5,2)"));
+ //NCOL
+ assertTrue(rs.next());
+ assertTrue(rs.getString(2).startsWith("NUMERIC(5,2)"));
+ assertFalse(rs.next());
+ rs.close();
+
+ s.execute("create index decimal_tab_idx on decimal_tab(dcol)");
+ rs = s.executeQuery("select conglomeratename, descriptor from sys.sysconglomerates where conglomeratename = 'DECIMAL_TAB_IDX' order by conglomeratename");
+ assertTrue(rs.next());
+ assertTrue(rs.getString(2).startsWith("BTREE (1)"));
+ assertFalse(rs.next());
+ rs.close();
+
+ s.execute("create trigger t1 after update on decimal_tab for each row values 1");
+ rs = s.executeQuery("select triggername, referencedcolumns from sys.systriggers order by triggername");
+ assertTrue(rs.next());
+ assertNull(rs.getString(2));
+ assertFalse(rs.next());
+ rs.close();
+
+ s.execute("drop trigger t1");
+ s.execute("drop table decimal_tab");
+ s.close();
+ }
+
+ /**
+ * Test for fix of Derby-318, confirm that it is possible to select
+ * COLUMNDEFAULT from SYSCOLUMNS after a column that is generated by
+ * default has been added.
+ *
+ * @throws SQLException
+ */
+ public void testAutoincrementColumnUpdated() throws SQLException{
+ Statement s = createStatement();
+ s.executeUpdate("create table defaultAutoinc(autoinccol int generated by default as identity)");
+ ResultSet rs = s.executeQuery("select COLUMNDEFAULT from SYS.SYSCOLUMNS where COLUMNNAME = 'AUTOINCCOL'");
+ assertTrue(rs.next());
+ // Before Derby-318, this next call would have failed with an NPE
+ Object o = rs.getObject(1);
+ if (! (o instanceof java.io.Serializable)) {
+ fail("SystemCatalogTest: invalid Object type for SYSCOLUMNS.COLUMNDEFAULT");
+ }
+ assertFalse(rs.next());
+ rs.close();
+
+ s.executeUpdate("drop table defaultAutoinc");
+ s.close();
+
+ }
+
+ /**
+ * Run SYSCS_UTIL.SYSCS_CHECK_TABLE on each system table.
+ *
+ * @throws SQLException
+ */
+ public void testCheckConsistencyOfSystemCatalogs() throws SQLException {
+ Statement s = createStatement();
+ ResultSet rs = s.executeQuery("select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename)from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1' order by tablename");
+
+ boolean nonEmptyResultSet = false;
+ while(rs.next()) {
+ nonEmptyResultSet = true;
+ assertEquals(rs.getInt(2), 1);
+ }
+
+ assertTrue(nonEmptyResultSet);
+ rs.close();
+ s.close();
+ }
+}
+
+
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SystemCatalogTest.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?view=diff&rev=522772&r1=522771&r2=522772
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Mon Mar 26 23:41:59 2007
@@ -78,7 +78,7 @@
suite.addTest(SysDiagVTIMappingTest.suite());
suite.addTest(UpdatableResultSetTest.suite());
suite.addTest(CurrentOfTest.suite());
- suite.addTest(CursorTest.suite());
+ suite.addTest(CursorTest.suite());
suite.addTest(CastingTest.suite());
suite.addTest(ScrollCursors2Test.suite());
suite.addTest(NullIfTest.suite());
@@ -88,6 +88,7 @@
suite.addTest(UnaryArithmeticParameterTest.suite());
suite.addTest(HoldCursorTest.suite());
suite.addTest(ShutdownDatabaseTest.suite());
+ suite.addTest(SystemCatalogTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.