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 bp...@apache.org on 2008/10/18 16:29:40 UTC
svn commit: r705887 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java
Author: bpendleton
Date: Sat Oct 18 07:29:39 2008
New Revision: 705887
URL: http://svn.apache.org/viewvc?rev=705887&view=rev
Log:
DERBY-3043: Schema does not exist error when table uses check constraint
In Derby, the CREATE SCHEMA statement is optional. When a table is created,
if the schema which contains it does not yet exist, the schema is
automatically created as well. If the CREATE TABLE statement contained a
CHECK constraint, the statement was being rejected with the error
Schema 'XXX' does not exist
This error occurred during compilation and bind processing for the check
constraint, when the criteria used in the constraint were bound to the
table definition. At that point in query processing, the schema name for
the table had not yet been filled in, so the bind processing was searching
the DataDictionary for a schema which did not yet exist (the schema will
exist after the statement is executed, but we're still compiling the
statement at this point).
This change modifies the bind processing for the CHECK constraint so that
the FromBaseTable object used to bind the constraint has the schema name
fully filled in, ensuring that the bind processing will be able to
manipulate the table without encountering no-such-schema errors.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java?rev=705887&r1=705886&r2=705887&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java Sat Oct 18 07:29:39 2008
@@ -432,10 +432,18 @@
C_NodeTypes.FROM_LIST,
getNodeFactory().doJoinOrderOptimization(),
getContextManager());
+ // DERBY-3043: To avoid a no-such-schema error when
+ // binding the check constraint, ensure that the
+ // table we bind against has a schema name specified.
+ // If it doesn't, fill in the schema name now.
+ //
+ TableName newTN = getObjectName();
+ if (newTN.getSchemaName() == null)
+ newTN.setSchemaName(getSchemaDescriptor().getSchemaName());
FromBaseTable table = (FromBaseTable)
getNodeFactory().getNode(
C_NodeTypes.FROM_BASE_TABLE,
- getObjectName(),
+ newTN,
null,
null,
null,
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java?rev=705887&r1=705886&r2=705887&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/LazyDefaultSchemaCreationTest.java Sat Oct 18 07:29:39 2008
@@ -32,6 +32,7 @@
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.TestConfiguration;
import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
/**
* Tests the lazy creation functionality of default schema: the schema
@@ -239,6 +240,82 @@
c1.close();
}
+ public void testDerby3043CheckConstraint() throws SQLException
+ {
+ // Demonstrate the DERBY-3043 workaround: if the table name is
+ // schema-qualified, check constraints do not cause a problem,
+ // and the named schema is automatically created if it does
+ // not yet exist:
+ Connection c0 = openUserConnection("frogs");
+ Statement s0 = c0.createStatement();
+
+ JDBC.assertEmpty( s0.executeQuery
+ ("select * from sys.sysschemas where schemaname='FROGS'"));
+ JDBC.assertEmpty( s0.executeQuery
+ ("select * from sys.sysschemas where schemaname='NOSUCH'"));
+
+ // A simple example, which should work whether or not the
+ // DERBY-3043 fix is in place
+
+ s0.executeUpdate("create table frogs.users2(username varchar(16) " +
+ "CHECK(LENGTH(username)>7))");
+
+ // Demonstrate that any schema is lazy-created, not just the
+ // default schema which matches the username:
+
+ s0.executeUpdate("create table nosuch.users(username varchar(16) " +
+ "CHECK(LENGTH(username)>7))");
+
+ // Schemas FROGS and NOSUCH have been lazy-created:
+
+ JDBC.assertSingleValueResultSet( s0.executeQuery(
+ "select schemaname from sys.sysschemas " +
+ "where schemaname='FROGS'"),
+ "FROGS");
+ JDBC.assertSingleValueResultSet( s0.executeQuery(
+ "select schemaname from sys.sysschemas " +
+ "where schemaname='NOSUCH'"),
+ "NOSUCH");
+ c0.close();
+
+ // Now verify that the test cases from DERBY-3043 pass:
+
+ Connection c1 = openUserConnection("blogs");
+
+ Statement s1 = c1.createStatement();
+
+ // At the beginning, the schema 'blogs' does not exist.
+
+ JDBC.assertEmpty( s1.executeQuery
+ ("select * from sys.sysschemas where schemaname='BLOGS'"));
+
+ // Should work, but without the DERBY-3043 fix will get a
+ // "Schema blogs does not exist" error
+
+ s1.executeUpdate("create table users(username varchar(16) " +
+ "CHECK(LENGTH(username)>7))");
+
+ // Another slightly more complicated example, which requires
+ // the DERBY-3043 fix again to work.
+
+ s1.executeUpdate("CREATE TABLE BLOGSCOM__BLOGS__USERS(" +
+ "PK INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY," +
+ "username VARCHAR(16) NOT NULL " +
+ " CONSTRAINT BLOGSCOM__BLOGS__USERS_UNIQUE_username UNIQUE " +
+ " CONSTRAINT BLOGSCOM__BLOGS__USERS_PASSWORD_username " +
+ " CHECK(LENGTH(username)>7)," +
+ "password VARCHAR (32672) NOT NULL , " +
+ "PRIMARY KEY(PK))");
+
+ // Schema BLOGS should have been lazy-created:
+
+ JDBC.assertSingleValueResultSet( s1.executeQuery(
+ "select schemaname from sys.sysschemas " +
+ "where schemaname='BLOGS'"),
+ "BLOGS");
+
+ c1.close();
+ }
protected void tearDown() throws Exception {
@@ -289,6 +366,10 @@
2, // deadlock timeout
1)); // wait timeout
+ suites[i].addTest(new CleanDatabaseTestSetup(
+ new LazyDefaultSchemaCreationTest(
+ "testDerby3043CheckConstraint")));
+
if (i == 0) {
suite.addTest(suites[i]);
} else {