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 mi...@apache.org on 2008/03/07 18:25:58 UTC
svn commit: r634752 - in /db/derby/code/trunk/java:
engine/org/apache/derby/impl/sql/compile/
engine/org/apache/derby/impl/sql/execute/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/ test...
Author: mikem
Date: Fri Mar 7 09:25:56 2008
New Revision: 634752
URL: http://svn.apache.org/viewvc?rev=634752&view=rev
Log:
DERBY-3456
contributed by Anurag Shekhar
adds code and tests to enable alter table to change allow nulls on a column
which currently participates in a unique constraint.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java Fri Mar 7 09:25:56 2008
@@ -185,14 +185,22 @@
else
{
- // a column that is part of a primary key or unique constraint
+ //if a column is part of unique constraint it can't be
+ //made nullable in soft upgrade mode
+ if ((existingConstraint.getConstraintType() ==
+ DataDictionary.UNIQUE_CONSTRAINT)) {
+ if (!dd.checkVersion(
+ DataDictionary.DD_VERSION_DERBY_10_4, null))
+ throw StandardException.newException(
+ SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT,
+ name);
+ }
+ // a column that is part of a primary key
// is being made nullable; can't be done.
if ((getNodeType() ==
C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE) &&
((existingConstraint.getConstraintType() ==
- DataDictionary.PRIMARYKEY_CONSTRAINT) ||
- (existingConstraint.getConstraintType() ==
- DataDictionary.UNIQUE_CONSTRAINT)))
+ DataDictionary.PRIMARYKEY_CONSTRAINT)))
{
throw StandardException.newException(
SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT, name);
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Fri Mar 7 09:25:56 2008
@@ -26,6 +26,7 @@
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
+import org.apache.derby.catalog.Dependable;
import org.apache.derby.catalog.DependableFinder;
import org.apache.derby.catalog.IndexDescriptor;
@@ -1063,7 +1064,31 @@
DataTypeDescriptor dataType =
columnDescriptor.getType().getNullabilityType(nullability);
-
+ //check if there any unique constraint to update
+ ConstraintDescriptorList cdl = dd.getConstraintDescriptors(td);
+ int columnPostion = columnDescriptor.getPosition();
+ for (int i = 0; i < cdl.size(); i++) {
+ ConstraintDescriptor cd = cdl.elementAt(i);
+ if (cd.getConstraintType() == DataDictionary.UNIQUE_CONSTRAINT) {
+ ColumnDescriptorList columns = cd.getColumnDescriptors();
+ for (int count = 0; count < columns.size(); count++) {
+ if (columns.elementAt(count).getPosition()
+ != columnPostion)
+ break;
+ //get backing index
+ ConglomerateDescriptor desc
+ = td.getConglomerateDescriptor(
+ cd.getConglomerateId());
+ //check if the backing index was created when the column
+ //not null ie is backed by unique index
+ if (!desc.getIndexDescriptor().isUnique())
+ break;
+ //need to replace the index
+ recreateUniqueConstraintBackingIndex (desc, td,
+ activation, lcc);
+ }
+ }
+ }
newColumnDescriptor =
new ColumnDescriptor(colName,
columnDescriptor.getPosition(),
@@ -1077,11 +1102,11 @@
+
// Update the ColumnDescriptor with new default info
dd.dropColumnDescriptor(td.getUUID(), colName, tc);
dd.addDescriptor(newColumnDescriptor, td,
- DataDictionary.SYSCOLUMNS_CATALOG_NUM, false, tc);
-
+ DataDictionary.SYSCOLUMNS_CATALOG_NUM, false, tc);
}
/**
* Workhorse for modifying the default value of a column.
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DDLSingleTableConstantAction.java Fri Mar 7 09:25:56 2008
@@ -21,6 +21,7 @@
package org.apache.derby.impl.sql.execute;
+import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
@@ -253,6 +254,50 @@
return;
}
+
+ /**
+ * Recreate backing index of unique constraint.
+ * It first drops the existing index and creates it again with
+ * uniqueness set to false and uniqueWhenNotNull set to true. It reuses
+ * the uuid so there is no need to update ConstraintDescriptor.
+ * @param cd ConglomerateDescritor to recreate
+ * @param td TableDescriptor for the table on which congDesc exists
+ * @param activation Activation used when creating a new backing
+ * index (if a new backing index is needed)
+ * @param lcc LanguageConnectionContext used for dropping
+ * @throws StandardException
+ */
+ void recreateUniqueConstraintBackingIndex (
+ ConglomerateDescriptor cd,
+ TableDescriptor td,
+ Activation activation,
+ LanguageConnectionContext lcc) throws StandardException {
+ Properties prop = new Properties ();
+ //get index property
+ loadIndexProperties(lcc, cd, prop);
+ ArrayList list = new ArrayList();
+ dropConglomerate(cd, td, false, list, activation, lcc);
+ String [] cols = cd.getColumnNames();
+ if (cols == null) {
+ //column list wasn't stored in conglomerateDescritor
+ //fetch is from table descriptor
+ int [] pos = cd.getIndexDescriptor().baseColumnPositions();
+ cols = new String [pos.length];
+ for (int i = 0; i < cols.length; i++) {
+ cols [i] = td.getColumnDescriptor(pos [i]).getColumnName();
+ }
+ }
+
+ //create new index action
+ CreateIndexConstantAction action =
+ new CreateIndexConstantAction (false, false, true,
+ cd.getIndexDescriptor().indexType(), td.getSchemaName(),
+ cd.getConglomerateName(), td.getName(), td.getUUID(),
+ cols, cd.getIndexDescriptor().isAscending(),
+ true, cd.getUUID(), prop);
+ //create index
+ action.executeConstantAction(activation);
+ }
/**
* Get any table properties that exist for the received
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Fri Mar 7 09:25:56 2008
@@ -939,11 +939,11 @@
0 rows inserted/updated/deleted
ij> alter table atmcn_4 alter a null;
0 rows inserted/updated/deleted
-ij> -- show that a column which has a UNIQUE constraint cannot be modified NULL:
+ij> --set column, part of unique constraint, to null
create table atmcn_5 (a integer not null, b integer not null unique);
0 rows inserted/updated/deleted
ij> alter table atmcn_5 alter column b null;
-ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns.
+0 rows inserted/updated/deleted
ij> -- tests for ALTER TABLE ALTER COLUMN DEFAULT
create table atmod_1 (a integer, b varchar(10));
0 rows inserted/updated/deleted
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?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- 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 Fri Mar 7 09:25:56 2008
@@ -126,6 +126,7 @@
suite.addTest(ConglomerateSharingTest.suite());
suite.addTest(NullableUniqueConstraintTest.suite());
suite.addTest(OLAPTest.suite());
+ suite.addTest(UniqueConstraintSetNullTest.suite());
// Add the XML tests, which exist as a separate suite
// so that users can "run all XML tests" easily.
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Fri Mar 7 09:25:56 2008
@@ -594,7 +594,7 @@
-- statement is optional:
create table atmcn_4 (a integer not null, b integer);
alter table atmcn_4 alter a null;
--- show that a column which has a UNIQUE constraint cannot be modified NULL:
+--set column, part of unique constraint, to null
create table atmcn_5 (a integer not null, b integer not null unique);
alter table atmcn_5 alter column b null;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java?rev=634752&r1=634751&r2=634752&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java Fri Mar 7 09:25:56 2008
@@ -592,6 +592,19 @@
//duplicate key violation 23505
assertStatementError ("23505", s,
"insert into constraintest1 (i) values (2)");
+ //should able to drop nullablity
+ s.executeUpdate("alter table constraintest1 " +
+ "alter column i null");
+ //try creating index without seting column as not null
+ s.executeUpdate("alter table constraintest2 " +
+ "add constraint ucon1 unique(i, j)");
+ //test null values in unique constraint
+ s.executeUpdate ("insert into constraintest1 (j) values (1)");
+ s.executeUpdate ("insert into constraintest1 (j) values (1)");
+ //test null values in new index
+ s.executeUpdate ("insert into constraintest2 (j) values (1)");
+ s.executeUpdate ("insert into constraintest2 (j) values (1)");
+ break;
}
s.close();
}