db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject 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...
Date Fri, 07 Mar 2008 17:25:58 GMT
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();
     }



Mime
View raw message