db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r711135 - 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/tests/lang/
Date Tue, 04 Nov 2008 00:12:00 GMT
Author: rhillegas
Date: Mon Nov  3 16:12:00 2008
New Revision: 711135

URL: http://svn.apache.org/viewvc?rev=711135&view=rev
Log:
DERBY-481: Forbid SET NULL/DEFAULT in delete actions for foreign keys defined on generated
columns.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java?rev=711135&r1=711134&r2=711135&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
Mon Nov  3 16:12:00 2008
@@ -24,6 +24,7 @@
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.reference.Limits;
 
+import org.apache.derby.iapi.services.io.FormatableBitSet;
 import org.apache.derby.iapi.services.sanity.SanityManager;
 
 import org.apache.derby.iapi.error.StandardException;
@@ -299,6 +300,7 @@
 	{
 		DataDictionary	dd = getDataDictionary();
 		int					numCheckConstraints = 0;
+		int numReferenceConstraints = 0;
         int numGenerationClauses = 0;
 		int numBackingIndexes = 0;
 
@@ -367,6 +369,9 @@
 			numCheckConstraints = tableElementList.countConstraints(
 									DataDictionary.CHECK_CONSTRAINT);
             
+            numReferenceConstraints = tableElementList.countConstraints(
+									DataDictionary.FOREIGNKEY_CONSTRAINT);
+            
             numGenerationClauses = tableElementList.countGenerationClauses();
 		}
 
@@ -380,7 +385,7 @@
 				String.valueOf(Limits.DB2_MAX_INDEXES_ON_TABLE));
 		}
 
-		if ( (numCheckConstraints > 0) || (numGenerationClauses > 0) )
+		if ( (numCheckConstraints > 0) || (numGenerationClauses > 0) || (numReferenceConstraints
> 0))
 		{
 			/* In order to check the validity of the check constraints and
 			 * generation clauses
@@ -392,13 +397,15 @@
 			 * no nodes which can return non-deterministic results.
 			 */
 			FromList fromList = makeFromList( dd, tableElementList, false );
+            FormatableBitSet    generatedColumns = baseTable.makeColumnMap( baseTable.getGeneratedColumns()
);
 
 			/* Now that we've finally goobered stuff up, bind and validate
 			 * the check constraints and generation clauses.
 			 */
 			if  (numCheckConstraints > 0) { tableElementList.bindAndValidateCheckConstraints(fromList);
}
 			if  (numGenerationClauses > 0)
-            { tableElementList.bindAndValidateGenerationClauses(fromList, baseTable.makeColumnMap(
baseTable.getGeneratedColumns() ) ); }
+            { tableElementList.bindAndValidateGenerationClauses(fromList, generatedColumns
); }
+            if ( numReferenceConstraints > 0) { tableElementList.validateForeignKeysOnGenerationClauses(
fromList, generatedColumns ); }
 		}
 
 		//Check if we are in alter table to update the statistics. If yes, then

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java?rev=711135&r1=711134&r2=711135&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
Mon Nov  3 16:12:00 2008
@@ -422,7 +422,7 @@
 				String.valueOf(Limits.DB2_MAX_INDEXES_ON_TABLE));
 		}
 
-		if ( (numCheckConstraints > 0) || (numGenerationClauses > 0) )
+		if ( (numCheckConstraints > 0) || (numGenerationClauses > 0) || (numReferenceConstraints
> 0) )
 		{
 			/* In order to check the validity of the check constraints and
 			 * generation clauses
@@ -434,12 +434,14 @@
 			 * no nodes which can return non-deterministic results.
 			 */
 			FromList fromList = makeFromList( null, tableElementList, true );
+            FormatableBitSet    generatedColumns = new FormatableBitSet();
 
 			/* Now that we've finally goobered stuff up, bind and validate
 			 * the check constraints and generation clauses.
 			 */
 			if  (numCheckConstraints > 0) { tableElementList.bindAndValidateCheckConstraints(fromList);
}
-			if  (numGenerationClauses > 0) { tableElementList.bindAndValidateGenerationClauses(fromList,
new FormatableBitSet() ); }
+			if  (numGenerationClauses > 0) { tableElementList.bindAndValidateGenerationClauses(fromList,
generatedColumns ); }
+            if ( numReferenceConstraints > 0) { tableElementList.validateForeignKeysOnGenerationClauses(
fromList, generatedColumns ); }
 		}
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?rev=711135&r1=711134&r2=711135&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
Mon Nov  3 16:12:00 2008
@@ -26,6 +26,7 @@
 
 import org.apache.derby.iapi.error.StandardException;
 
+import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.iapi.sql.compile.CompilerContext;
 import org.apache.derby.iapi.sql.compile.C_NodeTypes;
 
@@ -803,18 +804,97 @@
 
             for ( int i = 0; i < count; i++ )
             {
-                int         referencedColumnID = referencedColumns[ i ];
+                int         referencedColumnID = referencedColumns[ i ]; 
                 if ( generatedColumns.isSet( referencedColumnID ) )
                 {
                     throw StandardException.newException(SQLState.LANG_CANT_REFERENCE_GENERATED_COLUMN,
cdn.getColumnName());
                 }
-            }   // end of loop through referenced columns
+           }   // end of loop through referenced columns
 
         }       // end of loop through generated columns
-
+        
 	}
 
 	/**
+	 * Prevent foreign keys on generated columns from violating the SQL spec,
+	 * part 2, section 11.8 (<column definition>), syntax rule 12: the
+	 * referential action may not specify SET NULL or SET DEFAULT and the update
+	 * rule may not specify ON UPDATE CASCADE.  
+	 *
+	 * @param fromList		The FromList in question.
+	 * @param generatedColumns Bitmap of generated columns in the table.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	void validateForeignKeysOnGenerationClauses(FromList fromList, FormatableBitSet generatedColumns
)
+		throws StandardException
+	{
+        // nothing to do if there are no generated columns
+        if ( generatedColumns.getNumBitsSet() <= 0 ) { return; }
+        
+		FromBaseTable				table = (FromBaseTable) fromList.elementAt(0);
+        ResultColumnList        tableColumns = table.getResultColumns();
+		int						  size = size();
+
+        // loop through the foreign keys, looking for keys which violate the
+        // rulse we're enforcing
+		for (int index = 0; index < size; index++)
+		{
+			TableElementNode element = (TableElementNode) elementAt(index);
+
+			if (! (element instanceof FKConstraintDefinitionNode))
+			{
+				continue;
+			}
+
+			FKConstraintDefinitionNode fk = (FKConstraintDefinitionNode) element;
+            ConstraintInfo                      ci = fk.getReferencedConstraintInfo();
+            int                                     deleteRule = ci.getReferentialActionDeleteRule();
+            int                                     updateRule = ci.getReferentialActionUpdateRule();
+
+            //
+            // Currently we don't support ON UPDATE CASCADE. Someday we might.
+            // We're laying a trip-wire here so that we won't neglect to code the appropriate
check
+            // when we support ON UPDATE CASCADE.
+            //
+            if (
+                ( updateRule != StatementType.RA_RESTRICT ) &&
+                ( updateRule != StatementType.RA_NOACTION )
+                )
+            {
+                throw StandardException.newException( SQLState.BTREE_UNIMPLEMENTED_FEATURE
);
+            }
+            
+            if (
+                ( deleteRule != StatementType.RA_SETNULL ) &&
+                ( deleteRule != StatementType.RA_SETDEFAULT )
+                )
+            { continue; }
+
+            //
+            // OK, we have found a foreign key whose referential action is SET NULL or
+            // SET DEFAULT or whose update rule is ON UPDATE CASCADE.
+            // See if any of the key columns are generated columns.
+            //
+            ResultColumnList                keyCols = fk.getColumnList();
+            int                                     keyCount = keyCols.size();
+
+            for ( int i = 0; i < keyCount; i++ )
+            {
+                ResultColumn    keyCol = (ResultColumn) keyCols.elementAt( i );
+                String                  keyColName = keyCol.getName();
+                int     position = tableColumns.getPosition( keyColName, 1 );
+
+                if ( generatedColumns.isSet(  position ) )
+                {
+                    throw StandardException.newException(SQLState.LANG_BAD_FK_ON_GENERATED_COLUMN,
keyColName );
+                }
+            }
+
+        }   // end of loop through table elements
+    }
+    
+	/**
 	 * Fill in the ConstraintConstantAction[] for this create/alter table.
 	 * 
      * @param forCreateTable ConstraintConstantAction is for a create table.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=711135&r1=711134&r2=711135&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Nov  3 16:12:00
2008
@@ -1990,7 +1990,13 @@
             <msg>
                 <name>42XA5</name>
                 <text>Routine '{0}' may issue SQL and therefore cannot appear in a
GENERATION CLAUSE.</text>
-                <arg>value</arg>
+                <arg>routineName</arg>
+            </msg>
+
+            <msg>
+                <name>42XA6</name>
+                <text>'{0}' is a generated column. It cannot be part of a foreign key
whose referential action is SET NULL or SET DEFAULT or whose update rule is ON UPDATE CASCADE.</text>
+                <arg>columnName</arg>
             </msg>
 
             <msg>

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=711135&r1=711134&r2=711135&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
Mon Nov  3 16:12:00 2008
@@ -899,6 +899,7 @@
     String LANG_CANT_OVERRIDE_GENERATION_CLAUSE                  = "42XA3";
     String LANG_CANT_REFERENCE_GENERATED_COLUMN                  = "42XA4";
     String LANG_ROUTINE_CANT_PERMIT_SQL                                   = "42XA5";
+    String LANG_BAD_FK_ON_GENERATED_COLUMN                           = "42XA6";
 	String LANG_INVALID_USER_AGGREGATE_DEFINITION2                     = "42Y00";
 	String LANG_INVALID_CHECK_CONSTRAINT                               = "42Y01";
 	// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE                = "42Y02";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=711135&r1=711134&r2=711135&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
Mon Nov  3 16:12:00 2008
@@ -62,10 +62,11 @@
     private static  final   String  CANT_OVERRIDE_GENERATION_CLAUSE = "42XA3";
     private static  final   String  CANT_REFERENCE_GENERATED_COLUMN = "42XA4";
     private static  final   String  ROUTINE_CANT_ISSUE_SQL = "42XA5";
+    private static  final   String  BAD_FOREIGN_KEY_ACTION = "42XA6";
     private static  final   String  CONSTRAINT_VIOLATION = "23513";
     private static  final   String  FOREIGN_KEY_VIOLATION = "23503";
     private static  final   String  ILLEGAL_DUPLICATE = "23505";
-    private static  final   String  MISPLACED_SELECT = "42X01";
+    private static  final   String  SYNTAX_ERROR = "42X01";
     private static  final   String  COLUMN_OUT_OF_SCOPE = "42X04";
 
     ///////////////////////////////////////////////////////////////////////////////////
@@ -1425,7 +1426,7 @@
              );
         expectCompilationError
             (
-             MISPLACED_SELECT,
+             SYNTAX_ERROR,
              "create table t_br_2( a int, b int generated always as ( select a from t_br_1
) )"
              );
         expectCompilationError
@@ -1435,7 +1436,7 @@
              );
         expectCompilationError
             (
-             MISPLACED_SELECT,
+             SYNTAX_ERROR,
              "alter table t_br_3 add column b int generated always as ( select a from t_br_1
)"
              );
         expectCompilationError
@@ -2018,6 +2019,96 @@
 
     }
 
+    /**
+     * <p>
+     * Test that delete/update referential actions don't override generated columns.
+     * </p>
+     */
+    public  void    test_015_foreignKeyActions()
+        throws Exception
+    {
+        Connection  conn = getConnection();
+
+        //
+        // Schema
+        //
+        goodStatement
+            (
+             conn,
+             "create table t1_for_ra( a int, b int primary key )"
+             );
+        goodStatement
+            (
+             conn,
+             "create table t3_for_ra( a int, b int, constraint t3_for_ra_pk primary key(
a, b ) )"
+             );
+        goodStatement
+            (
+             conn,
+             "create table t6_for_ra( a int, b int generated always as (-a) )"
+             );
+
+        //
+        // Can't override a generated value via a cascading foreign key action.
+        //
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "create table t2_for_ra( a int, b int generated always as ( -a ) references
t1_for_ra( b ) on delete set null )"
+             );
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "create table t2_for_ra( a int, b int generated always as ( -a ) references
t1_for_ra( b ) on delete set default )"
+             );
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "create table t4_for_ra\n" +
+             "(\n" +
+             "   aa int,\n" +
+             "   bb int generated always as ( -aa ),\n" +
+             "   cc int,\n" +
+             "   constraint t4_for_ra_fk foreign key( aa, bb ) references t3_for_ra( a, b
) on delete set null\n" +
+             ")\n"
+             );
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "create table t4_for_ra\n" +
+             "(\n" +
+             "   aa int,\n" +
+             "   bb int generated always as ( -aa ),\n" +
+             "   cc int,\n" +
+             "   constraint t4_for_ra_fk foreign key( aa, bb ) references t3_for_ra( a, b
) on delete set default\n" +
+             ")\n"
+             );
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "alter table t6_for_ra\n" +
+             "  add constraint t6_for_ra_fk foreign key( b ) references t1_for_ra( b ) on
delete set null\n"
+             );
+        expectCompilationError
+            (
+             BAD_FOREIGN_KEY_ACTION,
+             "alter table t6_for_ra\n" +
+             "  add constraint t6_for_ra_fk foreign key( b ) references t1_for_ra( b ) on
delete set default\n"
+             );
+
+        //
+        // We don't currently support this syntax. But when we do, we want to
+        // make sure that we remember to disallow this use of generated columns.
+        // This will involve adding some more logic to TableElementList.validateForeignKeysOnGenerationClauses().
+        //
+        expectCompilationError
+            (
+             SYNTAX_ERROR,
+             "alter table t6_for_ra\n" +
+             "  add constraint t6_for_ra_fk foreign key( b ) references t1_for_ra( b ) on
update cascade\n"
+             );
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // MINIONS



Mime
View raw message