db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r437215 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTe...
Date Sat, 26 Aug 2006 19:42:03 GMT
Author: bpendleton
Date: Sat Aug 26 12:42:02 2006
New Revision: 437215

URL: http://svn.apache.org/viewvc?rev=437215&view=rev
Log:
DERBY-119: Add ALTER TABLE option to change column from NULL to NOT NULL

This change merges the db2AlterColumn and columnAlterClause routines in
the parser to provide a single ALTER TABLE ALTER COLUMN statement that
provides support for changing the NULL / NOT NULL constraint on a
column in a table.

I believe that the existing execution code in AlterTableConstantAction
is correct, and so this patch primarily involves parser changes to hook
up the compiler to the existing execution code.

The patch also modifies the constraint handling in ModifyColumnNode so
that a column cannot be changed to NULL-able if it is part of either a
primary key or a unique constraint. Prior to the patch, ModifyColumnNode
only checked for a PRIMARY KEY constraint, so this patch also adds a check
for a UNIQUE constraint. It also rewords the error message to describe the
new conditions which can cause the ALTER COLUMN to be rejected.

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/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.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/altertable.sql

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=437215&r1=437214&r2=437215&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
Sat Aug 26 12:42:02 2006
@@ -193,15 +193,17 @@
 			
 			else
 			{
-				// a column that is part of a primary key is being made
-				// nullable; can't be done.
+				// a column that is part of a primary key or unique constraint
+                // is being made nullable; can't be done.
 				if ((getNodeType() == 
 					 C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE) &&
-					(existingConstraint.getConstraintType() == 
-					 DataDictionary.PRIMARYKEY_CONSTRAINT))
+					((existingConstraint.getConstraintType() == 
+					 DataDictionary.PRIMARYKEY_CONSTRAINT) ||
+					 (existingConstraint.getConstraintType() == 
+					 DataDictionary.UNIQUE_CONSTRAINT)))
 				{
 				throw StandardException.newException(
-					 SQLState.LANG_MODIFY_COLUMN_PKEY_CONSTRAINT, name);
+					 SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT, name);
 				}
 				// unique key or primary key.
 				ConstraintDescriptorList 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Sat Aug
26 12:42:02 2006
@@ -11791,22 +11791,10 @@
 	}
 |
 	<ALTER> [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)

-		typeDescriptor = DB2AlterColumn(autoIncrementInfo)
+		tableElement = columnAlterClause(columnName)
 	{
 		changeType[0] = DDLStatementNode.MODIFY_TYPE;
-		/* typeDescriptor is not null for MODIFY_COLUMN_TYPE_NODE */
-		if (typeDescriptor != null)
-			tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
-						C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
-						columnName, null,
-						typeDescriptor, null,
-						getContextManager()));
-		else
-			tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
-						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
-						columnName,
-						null, null, autoIncrementInfo,
-						getContextManager()));
+		tableElementList.addTableElement(tableElement);
 		return lockGranularity;
 	}
 |
@@ -11842,29 +11830,61 @@
 	}
 }
 
-
-TableElementNode 
-columnAlter() throws StandardException :
-{
-	String columnName;
-	TableElementNode tn;
-}
-{
-	columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) tn = columnAlterClause(columnName)
-	{
-		return tn;
-	}
-}
-
+/*
+ * Various variants of the ALTER TABLE ALTER COLUMN statement.
+ *
+ * By the type we get here, we've parsed
+ *    ALTER TABLE tablename ALTER [COLUMN] columnname
+ * and here we parse the remainder of the ALTER COLUMN clause, one of:
+ *		SET DATA TYPE data_type
+ *		SET INCREMENT BY increment_value
+ *		RESTART WITH increment_restart_value
+ *		[WITH] DEFAULT default_value
+ *      [NOT] NULL
+ */
 TableElementNode
 columnAlterClause(String columnName) throws StandardException :
 {
 	ValueNode	defaultNode;
-	DataTypeDescriptor typeDescriptor;
 	long[]				autoIncrementInfo = new long[4];
+	long				autoIncrementIncrement = 1;
+	long				autoIncrementRestartWith = 1;
+	DataTypeDescriptor	typeDescriptor = null;
 }
 {
-	// MODIFY column_name [WITH] DEFAULT <Value>
+	LOOKAHEAD( {getToken(2).kind == DATA} )
+	<SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
+	{
+		return (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
+						columnName, null,
+						typeDescriptor, null,
+						getContextManager());
+	}
+|
+	LOOKAHEAD( {getToken(2).kind == INCREMENT} )
+	<SET> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
+	{
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE;
+		return (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+						columnName,
+						null, null, autoIncrementInfo,
+						getContextManager());
+	}
+|
+	<RESTART> <WITH> autoIncrementRestartWith = exactNumber()
+	{
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
+		return (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+						columnName,
+						null, null, autoIncrementInfo,
+						getContextManager());
+	}
+|
 	LOOKAHEAD( {getToken(1).kind == WITH || getToken(1).kind == _DEFAULT })
 	defaultNode = defaultClause(autoIncrementInfo, columnName)
 	{
@@ -11881,7 +11901,6 @@
 								getContextManager());
 	}
 |
-	// MODIFY column_name NULL
 	LOOKAHEAD ({getToken(1).kind == NULL })
 	<NULL>
 	{
@@ -11896,7 +11915,6 @@
 								getContextManager());
 	}
 |
-	// MODIFY column_name NOT NULL
 	LOOKAHEAD({getToken(1).kind == NOT})
 	<NOT> <NULL>
 	{
@@ -11909,49 +11927,6 @@
 								C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NOT_NULL_NODE,
 								columnName, null, null, null,
 								getContextManager());
-	}
-|
-	// MODIFY column_name varchar(64)
-	typeDescriptor = dataTypeDDL()
-	{
-		return (TableElementNode) nodeFactory.getNode(
-								C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
-								columnName,
-								null, typeDescriptor, null,
-								getContextManager());
-	}
-}
-
-/*
- * DB2 syntax for ALTER COLUMN
- */
-DataTypeDescriptor
-DB2AlterColumn(long[] autoIncrementInfo) throws StandardException :
-{
-	long				autoIncrementIncrement = 1;
-	long				autoIncrementRestartWith = 1;
-	DataTypeDescriptor	typeDescriptor = null;
-}
-{
-	LOOKAHEAD( {getToken(2).kind == DATA} )
-	<SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
-	{
-		return typeDescriptor;
-	}
-|
-	LOOKAHEAD( {getToken(2).kind == INCREMENT} )
-	<SET> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
-	{
-		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
-		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE;
-		return typeDescriptor;
-	}
-|
-	<RESTART> <WITH> autoIncrementRestartWith = exactNumber()
-	{
-		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
-		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
-		return typeDescriptor;
 	}
 }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Sat Aug 26
12:42:02 2006
@@ -720,7 +720,7 @@
 42Z17=Invalid length specified for column ''{0}''. Length must be greater than the current
column length.
 42Z18=Column ''{0}'' is part of a foreign key constraint ''{1}''. To alter the length of
this column, you should drop the constraint first, perform the ALTER TABLE, and then recreate
the constraint.
 42Z19=Column ''{0}'' is being referenced by at least one foreign key constraint ''{1}''.
To alter the length of this column, you should drop referencing constraints, perform the ALTER
TABLE and then recreate the constraints. 
-42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key, which cannot have
any nullable columns.
+42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
 
 #####
 # end of alter table modify constraints.

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=437215&r1=437214&r2=437215&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Sat Aug 26 12:42:02 2006
@@ -939,7 +939,7 @@
 	String LANG_MODIFY_COLUMN_INVALID_LENGTH						   = "42Z17";
 	String LANG_MODIFY_COLUMN_FKEY_CONSTRAINT						   = "42Z18";
 	String LANG_MODIFY_COLUMN_REFERENCED							   = "42Z19";
-	String LANG_MODIFY_COLUMN_PKEY_CONSTRAINT 						   = "42Z20";
+	String LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT					   = "42Z20";
 
 	String LANG_AI_INVALID_INCREMENT								   = "42Z21";
 	String LANG_AI_INVALID_TYPE										   = "42Z22";

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=437215&r1=437214&r2=437215&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
Sat Aug 26 12:42:02 2006
@@ -867,4 +867,63 @@
 0 rows inserted/updated/deleted
 ij> declare global temporary table session.logged(logged int) on commit delete rows not
logged;
 0 rows inserted/updated/deleted
+ij> -- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+0 rows inserted/updated/deleted
+ij> -- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+ERROR 23502: Column 'B'  cannot accept a NULL value.
+ij> insert into atmcn_1 values (1,1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+ij> alter table atmcn_1 alter column a not null;
+0 rows inserted/updated/deleted
+ij> -- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+ERROR 23502: Column 'A'  cannot accept a NULL value.
+ij> insert into atmcn_1 values (2,2);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+2          |2          
+ij> alter table atmcn_1 alter column b null;
+0 rows inserted/updated/deleted
+ij> insert into atmcn_1 (a) values (1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+2          |2          
+1          |NULL       
+ij> -- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+ERROR X0Y80: ALTER table 'APP.ATMCN_1' failed. Null data found in column 'B'.
+ij> -- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_2 alter column a null;
+ERROR 42Z20: Column 'A' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
+ij> create table atmcn_3 (a integer not null, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 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.
+ij> -- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- statement is optional:
+create table atmcn_4 (a integer not null, b integer);
+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:
+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.
 ij> 

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=437215&r1=437214&r2=437215&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
Sat Aug 26 12:42:02 2006
@@ -552,3 +552,32 @@
 drop table logged;
 declare global temporary table session.logged(logged int) on commit delete rows not logged;
 
+-- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+-- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+insert into atmcn_1 values (1,1);
+select * from atmcn_1;
+alter table atmcn_1 alter column a not null;
+-- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+insert into atmcn_1 values (2,2);
+select * from atmcn_1;
+alter table atmcn_1 alter column b null;
+insert into atmcn_1 (a) values (1);
+select * from atmcn_1;
+-- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+-- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+alter table atmcn_2 alter column a null;
+create table atmcn_3 (a integer not null, b integer not null);
+alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+alter table atmcn_3 alter column b null;
+-- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- 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:
+create table atmcn_5 (a integer not null, b integer not null unique);
+alter table atmcn_5 alter column b null;



Mime
View raw message