db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r503550 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ storeless/org/apache/derby/im...
Date Mon, 05 Feb 2007 04:58:53 GMT
Author: bpendleton
Date: Sun Feb  4 20:58:52 2007
New Revision: 503550

URL: http://svn.apache.org/viewvc?view=rev&rev=503550
Log:
DERBY-1909: ALTER TABLE DROP COLUMN needs to update GRANTed privileges

When ALTER TABLE DROP COLUMN is used to drop a column from a table, it needs to update the GRANTed column privileges on that table.

The core of this proposed patch involves refactoring and reusing the
DERBY-1847 method which knows how to rewrite SYSCOLPERMS rows
to update the COLUMNS column. The DERBY-1847 version of that code
only handled the case of adding a bit to the COLUMNS column; this patch
extends that method to support removing a bit from the COLUMNS
column as well, then calls the method from the AlterTable execution logic.


Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.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/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Sun Feb  4 20:58:52 2007
@@ -539,6 +539,25 @@
 	public void	updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController tc)
 	throws StandardException;
 	
+	/**
+	 * Update SYSCOLPERMS to reflect the dropping of a column from a table.
+	 *
+	 * This method rewrites SYSCOLPERMS rows to update the COLUMNS bitmap
+	 * to reflect the removal of a column from a table.
+	 *
+	 * Currently, this code gets called during execution phase of
+	 * ALTER TABLE .. DROP COLUMN .. 
+	 *
+	 * @param tableID	The UUID of the table whose column has been dropped
+	 * @param tc		TransactionController for the transaction
+	 * @param columnDescriptor   Info about the dropped column
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void	updateSYSCOLPERMSforDropColumn(UUID tableID,
+			TransactionController tc, ColumnDescriptor columnDescriptor)
+	throws StandardException;
+	
 	
 	/**
 	 * Drops all routine permission descriptors for the given routine.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Sun Feb  4 20:58:52 2007
@@ -2364,6 +2364,45 @@
 	public void	updateSYSCOLPERMSforAddColumnToUserTable(UUID tableID, TransactionController tc)
 	throws StandardException
 	{
+		rewriteSYSCOLPERMSforAlterTable(tableID, tc, null);
+	}
+	/**
+	 * Update SYSCOLPERMS due to dropping a column from a table.
+	 *
+	 * Since ALTER TABLE .. DROP COLUMN .. has removed a column from the
+	 * table, we need to shrink COLUMNS by removing the corresponding bit
+	 * position, and shifting all the subsequent bits "left" one position.
+	 *
+	 * @param tableID	The UUID of the table from which a col has been dropped
+	 * @param tc		TransactionController for the transaction
+	 * @param columnDescriptor   Information about the dropped column
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	public void updateSYSCOLPERMSforDropColumn(UUID tableID, 
+			TransactionController tc, ColumnDescriptor columnDescriptor)
+		throws StandardException
+	{
+		rewriteSYSCOLPERMSforAlterTable(tableID, tc, columnDescriptor);
+	}
+	/**
+	 * Workhorse for ALTER TABLE-driven mods to SYSCOLPERMS
+	 *
+	 * This method finds all the SYSCOLPERMS rows for this table. Then it
+	 * iterates through each row, either adding a new column to the end of
+	 * the table, or dropping a column from the table, as appropriate. It
+	 * updates each SYSCOLPERMS row to store the new COLUMNS value.
+	 *
+	 * @param tableID	The UUID of the table being altered
+	 * @param tc		TransactionController for the transaction
+	 * @param columnDescriptor   Dropped column info, or null if adding
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	private void rewriteSYSCOLPERMSforAlterTable(UUID tableID,
+			TransactionController tc, ColumnDescriptor columnDescriptor)
+		throws StandardException
+	{
 		// In Derby authorization mode, permission catalogs may not be present
 		if (!usesSqlAuthorization)
 			return;
@@ -2395,8 +2434,8 @@
 			false);
 
 		/* Next, using each of the ColPermDescriptor's uuid, get the unique row 
-		in SYSCOLPERMS and expand the "COLUMNS" column in SYSCOLPERMS to 
-		accomodate the newly added column to the tableid*/
+		in SYSCOLPERMS and adjust the "COLUMNS" column in SYSCOLPERMS to 
+		accomodate the added or dropped column in the tableid*/
 		ColPermsDescriptor colPermsDescriptor;
 		ExecRow curRow;
 		ExecIndexRow uuidKey;
@@ -2411,8 +2450,34 @@
 			curRow=ti.getRow(tc, uuidKey, rf.COLPERMSID_INDEX_NUM);
 	        FormatableBitSet columns = (FormatableBitSet) curRow.getColumn( 
 					  SYSCOLPERMSRowFactory.COLUMNS_COL_NUM).getObject();
-	        int currentLength = columns.getLength();
-	        columns.grow(currentLength+1);
+			// See whether this is ADD COLUMN or DROP COLUMN. If ADD, then
+			// add a new bit to the bit set. If DROP, then remove the bit
+			// for the dropped column.
+			if (columnDescriptor == null)
+			{
+				int currentLength = columns.getLength();
+				columns.grow(currentLength+1);
+			}
+			else
+			{
+				FormatableBitSet modifiedColumns=new FormatableBitSet(columns);
+				modifiedColumns.shrink(columns.getLength()-1);
+				// All the bits from 0 ... colPosition-2 are OK. The bits from
+				// colPosition to the end need to be shifted 1 to the left.
+				// The bit for colPosition-1 simply disappears from COLUMNS.
+				// ColumnPosition values count from 1, while bits in the
+				// FormatableBitSet count from 0.
+				for (int i = columnDescriptor.getPosition()-1;
+						i < modifiedColumns.getLength();
+						i++)
+				{
+					if (columns.isSet(i+1))
+						modifiedColumns.set(i);
+					else
+						modifiedColumns.clear(i);
+				}
+				columns = modifiedColumns;
+			}
 	        curRow.setColumn(SYSCOLPERMSRowFactory.COLUMNS_COL_NUM,
 					  dvf.getDataValue((Object) columns));
 			ti.updateRow(uuidKey, curRow,

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?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- 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 Sun Feb  4 20:58:52 2007
@@ -12005,10 +12005,6 @@
 	[ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
 				 dropColumnReferentialAction(behavior)
 	{
-		if( getLanguageConnectionContext().usesSqlAuthorization())
-                    throw StandardException.newException(
-                        SQLState.NOT_IMPLEMENTED,
-                        "ALTER TABLE DROP COLUMN (sqlAuthorization=true)");
 		return (TableElementNode) nodeFactory.getNode(
 						C_NodeTypes.DROP_COLUMN_NODE,
 						columnName, null,

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?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- 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 Sun Feb  4 20:58:52 2007
@@ -673,17 +673,6 @@
 	 * fixup of schema objects (such as triggers and column
 	 * privileges) which refer to columns by column position number.
 	 * 
-	 * Currently, column privileges are not repaired when
-	 * dropping a column. This is bug DERBY-1909, and for the
-	 * time being we simply reject DROP COLUMN if it is specified
-	 * when sqlAuthorization is true (that check occurs in the
-	 * parser, not here). When DERBY-1909 is fixed:
-	 *  - Update this comment
-	 *  - Remove the check in dropColumnDefinition() in the parser
-	 *  - consolidate all the tests in altertableDropColumn.sql
-	 *    back into altertable.sql and remove the separate
-	 *    altertableDropColumn files
-	 * 
 	 * Indexes are a bit interesting. The official SQL spec
 	 * doesn't talk about indexes; they are considered to be
 	 * an imlementation-specific performance optimization.
@@ -957,6 +946,9 @@
 								 true, tc);
 			}
 		}
+		// Adjust the column permissions rows in SYSCOLPERMS to reflect the
+		// changed column positions due to the dropped column:
+		dd.updateSYSCOLPERMSforDropColumn(td.getUUID(), tc, columnDescriptor);
 	}
 
 	private void modifyColumnType(Activation activation,

Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Sun Feb  4 20:58:52 2007
@@ -225,6 +225,12 @@
 
 	}
 
+	public void updateSYSCOLPERMSforDropColumn(UUID tableID,
+		TransactionController tc, ColumnDescriptor columnDescriptor)
+	    throws StandardException
+	{
+	}
+
 	public void dropAllRoutinePermDescriptors(UUID routineID,
 			TransactionController tc) throws StandardException {
 		// TODO Auto-generated method stub

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?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- 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 Sun Feb  4 20:58:52 2007
@@ -974,13 +974,6 @@
 -1         |b         
 3          |three     
 42         |forty two 
-ij> -- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
--- mode. This is because of bug DERBY-1909, which involves how to fix up
--- the GRANTed column permissions following a DROP COLUMN.
-create table atdc_1 (a integer, b integer);
-0 rows inserted/updated/deleted
-ij> alter table atdc_1 drop column b;
-ERROR 0A000: Feature not implemented: ALTER TABLE DROP COLUMN (sqlAuthorization=true).
 ij> -- Tests for renaming a column. These tests are in altertable.sql because
 -- renaming a column is closely linked, conseptually, to other table
 -- alterations. However, the actual syntax is:
@@ -1219,4 +1212,434 @@
 A                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
 B2                  |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
 C                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+ij> -- alter table tests for ALTER TABLE DROP COLUMN.
+-- The overall syntax is:
+--    ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+-- 
+create table atdc_0 (a integer);
+0 rows inserted/updated/deleted
+ij> create table atdc_1 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 1);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A          |B          
+-----------------------
+1          |1          
+ij> select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME                                                                                                                      |COLUMNNUMB&|COLUMNDATATYPE 
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               |1          |INTEGER        
+B                                                                                                                               |2          |INTEGER        
+ij> alter table atdc_1 drop column b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A          
+-----------
+1          
+ij> select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME                                                                                                                      |COLUMNNUMB&|COLUMNDATATYPE 
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               |1          |INTEGER        
+ij> alter table atdc_1 add column b varchar (20);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 'new val');
+1 row inserted/updated/deleted
+ij> insert into atdc_1 (a, b) values (2, 'two val');
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A          |B                   
+--------------------------------
+1          |NULL                
+1          |new val             
+2          |two val             
+ij> select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME                                                                                                                      |COLUMNNUMB&|COLUMNDATATYPE 
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               |1          |INTEGER        
+B                                                                                                                               |2          |VARCHAR(20)    
+ij> alter table atdc_1 add column c integer;
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (3, null, 3);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A          |B                   |C          
+--------------------------------------------
+1          |NULL                |NULL       
+1          |new val             |NULL       
+2          |two val             |NULL       
+3          |NULL                |3          
+ij> alter table atdc_1 drop b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A          |C          
+-----------------------
+1          |NULL       
+1          |NULL       
+2          |NULL       
+3          |3          
+ij> select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME                                                                                                                      |COLUMNNUMB&|COLUMNDATATYPE 
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A                                                                                                                               |1          |INTEGER        
+C                                                                                                                               |2          |INTEGER        
+ij> -- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_01 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_01 has been dropped.
+ij> create table atdc_1_02 (a int not null primary key, b int);
+0 rows inserted/updated/deleted
+ij> create table atdc_1_03 (a03 int, 
+   constraint a03_fk foreign key (a03) references atdc_1_02(a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_02 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_02 has been dropped.
+WARNING 01500: The constraint A03_FK on table ATDC_1_03 has been dropped.
+ij> -- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysconstraints where tableid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID                        |TABLEID                             |CONSTRAINTNAME                                                                                                                  |&|SCHEMAID                            |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|ATDC_CONSTRAINT_1                                                                                                               |C|xxxxFILTERED-UUIDxxxx|E|0          
+ij> select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+		where sc.constraintid = con.constraintid and con.tableid = st.tableid
+              and st.tablename = 'ATDC_1';
+CONSTRAINTID                        |CHECKDEFINITION                                                                                                                 |REFERENCEDCOLU&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|(a > 0)                                                                                                                         |(1)            
+ij> alter table atdc_1 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'ATDC_CONSTRAINT_1' is dependent on that object.
+ij> -- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_CONSTRAINT_1 on table ATDC_1 has been dropped.
+ij> select * from sys.sysconstraints where tableid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID                        |TABLEID                             |CONSTRAINTNAME                                                                                                                  |&|SCHEMAID                            |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+   b int not null,
+   c int constraint atdc_1_c_chk check (c is not null),
+   d int not null unique,
+   e int,
+   f int,
+   constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+0 rows inserted/updated/deleted
+ij> -- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A                   |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
+B                   |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
+C                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+D                   |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
+E                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+F                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+ij> alter table atdc_1_constraints drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column b restrict;
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_constraints drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'ATDC_1_C_CHK' is dependent on that object.
+ij> alter table atdc_1_constraints drop column d restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'D' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column e restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'E' because CONSTRAINT 'ATDC_1_E_FK' is dependent on that object.
+ij> describe atdc_1_constraints;
+COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A                   |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
+C                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+D                   |INTEGER  |0   |10  |10    |NULL      |NULL      |NO      
+E                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+F                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+ij> alter table atdc_1_constraints drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+WARNING 01500: The constraint ATDC_1_E_FK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_1_C_CHK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column d cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column e cascade;
+0 rows inserted/updated/deleted
+ij> describe atdc_1_constraints;
+COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+F                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+ij> -- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'ATDC_NOSUCH' because it does not exist.
+ij> -- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+0 rows inserted/updated/deleted
+ij> alter table atdc_2 drop column b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> alter table atdc_2 drop b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> -- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+ERROR 42X01: Syntax error: Encountered "\'a\'" at line 2, column 32.
+ij> -- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+ERROR 42X01: Syntax error: Encountered "column" at line 2, column 32.
+ij> alter table atdc_2 drop column;
+ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 30.
+ij> alter table atdc_2 drop column constraint;
+ERROR 42X01: Syntax error: Encountered "constraint" at line 1, column 32.
+ij> alter table atdc_2 drop column primary;
+ERROR 42X01: Syntax error: Encountered "primary" at line 1, column 32.
+ij> alter table atdc_2 drop column foreign;
+ERROR 42X01: Syntax error: Encountered "foreign" at line 1, column 32.
+ij> alter table atdc_2 drop column check;
+ERROR 42X01: Syntax error: Encountered "check" at line 1, column 32.
+ij> create table atdc_3 (a integer);
+0 rows inserted/updated/deleted
+ij> create index atdc_3_idx_1 on atdc_3 (a);
+0 rows inserted/updated/deleted
+ij> -- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'THE *LAST* COLUMN A' because TABLE 'RENC_SCHEMA_1.ATDC_3' is dependent on that object.
+ij> drop index atdc_3_idx_1;
+0 rows inserted/updated/deleted
+ij> -- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+0 rows inserted/updated/deleted
+ij> insert into atdc_4 values (1,2,3,4,5);
+1 row inserted/updated/deleted
+ij> create index atdc_4_idx_1 on atdc_4 (a);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_2 on atdc_4 (b, c, d);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_3 on atdc_4 (c, a);
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+    (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME                                                                                                                |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx                                                                                            |false
+ATDC_4_IDX_1                                                                                                                    |true 
+ATDC_4_IDX_2                                                                                                                    |true 
+ATDC_4_IDX_3                                                                                                                    |true 
+ij> show indexes from atdc_4;
+TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES   
+----------------------------------------------------------------------------
+ATDC_4              |A                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |B                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |C                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |D                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |C                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |A                   |true  |3   |A   |NULL    |NULL    
+ij> -- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+--    index atdc_4_idx_1 is entirely dropped
+--    index atdc_4_idx_2 is left alone but the column positions are fixed up
+--    index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+    (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME                                                                                                                |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx                                                                                            |false
+ATDC_4_IDX_2                                                                                                                    |true 
+ATDC_4_IDX_3                                                                                                                    |true 
+ij> show indexes from atdc_4;
+TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES   
+----------------------------------------------------------------------------
+ATDC_4              |B                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |C                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |D                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |C                   |true  |3   |A   |NULL    |NULL    
+ij> describe atdc_4;
+COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+B                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+C                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+D                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+E                   |INTEGER  |0   |10  |10    |NULL      |NULL      |YES     
+ij> -- The effect of dropping column c is that:
+--    index atdc_4_idx_2 is modified to refer to columns b and d
+--    index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+0 rows inserted/updated/deleted
+ij> show indexes from atdc_4;
+TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES   
+----------------------------------------------------------------------------
+ATDC_4              |B                   |true  |3   |A   |NULL    |NULL    
+ATDC_4              |D                   |true  |3   |A   |NULL    |NULL    
+ij> select * from atdc_4 where c = 3;
+ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C' is not a column in the target table.
+ij> select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+1          
+-----------
+1          
+ij> select conglomeratename, isindex from sys.sysconglomerates
+     where conglomeratename like 'ATDC_4%';
+CONGLOMERATENAME                                                                                                                |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+ATDC_4_IDX_2                                                                                                                    |true 
+ij> drop index atdc_4_idx_2;
+0 rows inserted/updated/deleted
+ij> -- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_1 (vw_b) as select b from atdc_5;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5 drop column b restrict;
+ERROR X0Y23: Operation 'DROP COLUMN RESTRICT' cannot be performed on object 'ATDC_5(B)' because VIEW 'ATDC_VW_1' is dependent on that object.
+ij> select * from atdc_vw_1;
+VW_B       
+-----------
+ij> -- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_1 has been dropped.
+ij> select * from atdc_vw_1;
+ERROR 42X05: Table/View 'ATDC_VW_1' does not exist.
+ij> -- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5a drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_5A_2 has been dropped.
+WARNING 01501: The view ATDC_VW_5A_1 has been dropped.
+ij> select * from atdc_vw_5a_1;
+ERROR 42X05: Table/View 'ATDC_VW_5A_1' does not exist.
+ij> select * from atdc_vw_5a_2;
+ERROR 42X05: Table/View 'ATDC_VW_5A_2' does not exist.
+ij> -- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> create trigger atdc_6_trigger_1 after update of b on atdc_6
+	for each row values current_date;
+0 rows inserted/updated/deleted
+ij> alter table atdc_6 drop column b restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'B' because TRIGGER 'ATDC_6_TRIGGER_1' is dependent on that object.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME                                                                                                                     
+--------------------------------------------------------------------------------------------------------------------------------
+ATDC_6_TRIGGER_1                                                                                                                
+ij> alter table atdc_6 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01502: The trigger ATDC_6_TRIGGER_1 on table ATDC_6 has been dropped.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME                                                                                                                     
+--------------------------------------------------------------------------------------------------------------------------------
+ij> create table atdc_7 (a int, b int, c int, primary key (a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_7 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_7 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_7 has been dropped.
+ij> create table atdc_8 (a int, b int, c int, primary key (b, c));
+0 rows inserted/updated/deleted
+ij> alter table atdc_8 drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_8 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_8 has been dropped.
+ij> create table atdc_9 (a int not null, b int);
+0 rows inserted/updated/deleted
+ij> alter table atdc_9 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> -- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> grant select(a, b, c) on atdc_10 to bryan;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolperms;
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1, 2}      
+ij> alter table atdc_10 drop column b restrict;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolperms;
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+ij> alter table atdc_10 drop column b cascade;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_10'.
+ij> select * from sys.syscolperms;
+COLPERMSID                          |GRANTEE                                                                                                                         |GRANTOR                                                                                                                         |TABLEID                             |&|COLUMNS        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+xxxxFILTERED-UUIDxxxx|BRYAN                                                                                                                           |APP                                                                                                                             |xxxxFILTERED-UUIDxxxx|s|{0, 1}         
+ij> -- Include the test from the DERBY-1909 report:
+drop table d1909;
+ERROR 42Y55: 'DROP TABLE' cannot be performed on 'D1909' because it does not exist.
+ij> create table d1909 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> grant select (a) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select (a,b) on d1909 to user2;
+0 rows inserted/updated/deleted
+ij> grant update(c) on d1909 to super_user;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE                                                                                                                         |&|COLUMNS        
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1                                                                                                                           |s|{0}            
+USER2                                                                                                                           |s|{0, 1}         
+SUPER_USER                                                                                                                      |u|{2}            
+ij> alter table d1909 drop column a;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE                                                                                                                         |&|COLUMNS        
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1                                                                                                                           |s|{}             
+USER2                                                                                                                           |s|{0}            
+SUPER_USER                                                                                                                      |u|{1}            
+ij> grant update(b) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select(c) on d1909 to user1;
+0 rows inserted/updated/deleted
+ij> grant select(c) on d1909 to user2;
+0 rows inserted/updated/deleted
+ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
+GRANTEE                                                                                                                         |&|COLUMNS        
+--------------------------------------------------------------------------------------------------------------------------------------------------
+USER1                                                                                                                           |s|{1}            
+USER2                                                                                                                           |s|{0, 1}         
+SUPER_USER                                                                                                                      |u|{1}            
+USER1                                                                                                                           |u|{0}            
 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?view=diff&rev=503550&r1=503549&r2=503550
==============================================================================
--- 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 Sun Feb  4 20:58:52 2007
@@ -611,12 +611,6 @@
 select * from atmod_1;
 
 
--- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
--- mode. This is because of bug DERBY-1909, which involves how to fix up
--- the GRANTed column permissions following a DROP COLUMN.
-create table atdc_1 (a integer, b integer);
-alter table atdc_1 drop column b;
-
 -- Tests for renaming a column. These tests are in altertable.sql because
 -- renaming a column is closely linked, conseptually, to other table
 -- alterations. However, the actual syntax is:
@@ -744,4 +738,194 @@
 -- But this should work, and should find the table in the other schema
 rename column renc_schema_2.renc_8.b to b2;
 describe renc_schema_2.renc_8;
+
+-- alter table tests for ALTER TABLE DROP COLUMN.
+
+-- The overall syntax is:
+--    ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+-- 
+create table atdc_0 (a integer);
+create table atdc_1 (a integer, b integer);
+insert into atdc_1 values (1, 1);
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 drop column b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column b varchar (20);
+insert into atdc_1 values (1, 'new val');
+insert into atdc_1 (a, b) values (2, 'two val');
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column c integer;
+insert into atdc_1 values (3, null, 3);
+select * from atdc_1;
+alter table atdc_1 drop b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+       from sys.syscolumns where referenceid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+alter table atdc_1_01 drop column c cascade;
+create table atdc_1_02 (a int not null primary key, b int);
+create table atdc_1_03 (a03 int, 
+   constraint a03_fk foreign key (a03) references atdc_1_02(a));
+alter table atdc_1_02 drop column a cascade;
+-- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+select * from sys.sysconstraints where tableid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+		where sc.constraintid = con.constraintid and con.tableid = st.tableid
+              and st.tablename = 'ATDC_1';
+alter table atdc_1 drop column a restrict;
+-- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+select * from sys.sysconstraints where tableid in
+            (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+   b int not null,
+   c int constraint atdc_1_c_chk check (c is not null),
+   d int not null unique,
+   e int,
+   f int,
+   constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+-- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a restrict;
+alter table atdc_1_constraints drop column b restrict;
+alter table atdc_1_constraints drop column c restrict;
+alter table atdc_1_constraints drop column d restrict;
+alter table atdc_1_constraints drop column e restrict;
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a cascade;
+alter table atdc_1_constraints drop column c cascade;
+alter table atdc_1_constraints drop column d cascade;
+alter table atdc_1_constraints drop column e cascade;
+describe atdc_1_constraints;
+-- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+-- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+alter table atdc_2 drop column b;
+alter table atdc_2 drop b;
+-- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+-- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+alter table atdc_2 drop column;
+alter table atdc_2 drop column constraint;
+alter table atdc_2 drop column primary;
+alter table atdc_2 drop column foreign;
+alter table atdc_2 drop column check;
+create table atdc_3 (a integer);
+create index atdc_3_idx_1 on atdc_3 (a);
+-- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+drop index atdc_3_idx_1;
+-- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+insert into atdc_4 values (1,2,3,4,5);
+create index atdc_4_idx_1 on atdc_4 (a);
+create index atdc_4_idx_2 on atdc_4 (b, c, d);
+create index atdc_4_idx_3 on atdc_4 (c, a);
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+    (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+-- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+--    index atdc_4_idx_1 is entirely dropped
+--    index atdc_4_idx_2 is left alone but the column positions are fixed up
+--    index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+    (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+describe atdc_4;
+-- The effect of dropping column c is that:
+--    index atdc_4_idx_2 is modified to refer to columns b and d
+--    index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+show indexes from atdc_4;
+select * from atdc_4 where c = 3;
+select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+select conglomeratename, isindex from sys.sysconglomerates
+     where conglomeratename like 'ATDC_4%';
+drop index atdc_4_idx_2;
+-- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+create view atdc_vw_1 (vw_b) as select b from atdc_5;
+alter table atdc_5 drop column b restrict;
+select * from atdc_vw_1;
+-- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+select * from atdc_vw_1;
+-- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+alter table atdc_5a drop column b cascade;
+select * from atdc_vw_5a_1;
+select * from atdc_vw_5a_2;
+-- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+create trigger atdc_6_trigger_1 after update of b on atdc_6
+	for each row values current_date;
+alter table atdc_6 drop column b restrict;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+alter table atdc_6 drop column b cascade;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+create table atdc_7 (a int, b int, c int, primary key (a));
+alter table atdc_7 drop column a restrict;
+alter table atdc_7 drop column a cascade;
+create table atdc_8 (a int, b int, c int, primary key (b, c));
+alter table atdc_8 drop column c restrict;
+alter table atdc_8 drop column c cascade;
+create table atdc_9 (a int not null, b int);
+alter table atdc_9 drop column a restrict;
+-- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+grant select(a, b, c) on atdc_10 to bryan;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b restrict;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b cascade;
+select * from sys.syscolperms;
+-- Include the test from the DERBY-1909 report:
+drop table d1909;
+create table d1909 (a int, b int, c int);
+grant select (a) on d1909 to user1;
+grant select (a,b) on d1909 to user2;
+grant update(c) on d1909 to super_user;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
+alter table d1909 drop column a;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
+grant update(b) on d1909 to user1;
+grant select(c) on d1909 to user1;
+grant select(c) on d1909 to user2;
+select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t
+	where c.tableid = t.tableid and t.tablename='D1909';
 



Mime
View raw message