db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r423266 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/referen...
Date Tue, 18 Jul 2006 22:11:51 GMT
Author: djd
Date: Tue Jul 18 15:11:50 2006
New Revision: 423266

URL: http://svn.apache.org/viewvc?rev=423266&view=rev
Log:
DERBY-551  Adds a new reliability bit mask(MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL) to CompilerContext.
Sets the above reliability for before triggers before binding the actionNode in CreateTriggerNode.
Checks the above reliability in CallStatementNode. After the called procedure is resolved,
the sql allowed
by the procedure is checked and if it is "modifies sql data" and if we have set the reliability
to
MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL, it means we are calling a procedure that modifies sql
data in the
action statement of a before trigger. In this case, an exception is thrown and trigger creation
fails.
Removes the check in InternalTriggerExecutionContext.validateStatement which was used to catch
the use of DML
in before triggers. This is a redundant check as use of DML in before triggers is now caught
at compile time.
Added a comment to indicate this.
Adds a new message to indicate procedures that modify sql data are not allowed in before triggers.
Reuses the same SQLState as for invalid statement in triggers. 

Patch submitted by Deepa Remesh

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CallStatementNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
    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/procedureInTrigger.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/CompilerContext.java
Tue Jul 18 15:11:50 2006
@@ -92,6 +92,12 @@
 	public	static	final	int			IGNORE_MISSING_CLASSES		=	0x00000100;
 	public	static	final	int			SCHEMA_ILLEGAL				=	0x00000200;
 	public  static  final   int			INTERNAL_SQL_ILLEGAL		=	0x00000400;
+	
+	/**
+	 * Calling procedures that modify sql data from before triggers is illegal. 
+	 * 
+	 */
+	public  static  final   int			MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL	=	0x00000800;
 
 	/** Standard SQL is legal */
 	public	static	final	int			SQL_LEGAL					=	(INTERNAL_SQL_ILLEGAL);

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CallStatementNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CallStatementNode.java?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CallStatementNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CallStatementNode.java
Tue Jul 18 15:11:50 2006
@@ -49,6 +49,8 @@
 import org.apache.derby.iapi.reference.ClassName;
 import org.apache.derby.iapi.services.classfile.VMOpcode;
 
+import org.apache.derby.catalog.types.RoutineAliasInfo;
+import org.apache.derby.iapi.reference.SQLState;
 
 import java.lang.reflect.Modifier;
 
@@ -168,6 +170,10 @@
 							null,
 							null);
 
+		// Disallow creation of BEFORE triggers which contain calls to 
+		// procedures that modify SQL data. 
+  		checkReliability();
+
 		getCompilerContext().popCurrentPrivType();
 		return this;
 	}
@@ -306,5 +312,37 @@
 	int getPrivType()
 	{
 		return Authorizer.EXECUTE_PRIV;
+	}
+	
+	/**
+	 * This method checks if the called procedure allows modification of SQL 
+	 * data. If yes, it cannot be compiled if the reliability is 
+	 * <code>CompilerContext.MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL</code>. This 
+	 * reliability is set for BEFORE triggers in the create trigger node. This 
+	 * check thus disallows creation of BEFORE triggers which contain calls to 
+	 * procedures that modify SQL data in the trigger action statement.  
+	 * 
+	 * @throws StandardException
+	 */
+	private void checkReliability() throws StandardException {
+		if(getSQLAllowedInProcedure() == RoutineAliasInfo.MODIFIES_SQL_DATA &&
+				getCompilerContext().getReliability() == CompilerContext.MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL)

+			throw StandardException.newException(SQLState.LANG_UNSUPPORTED_TRIGGER_PROC);
+	}
+	
+	/**
+	 * This method checks the SQL allowed by the called procedure. This method 
+	 * should be called only after the procedure has been resolved.
+	 * 
+	 * @return	SQL allowed by the procedure
+	 */
+	private short getSQLAllowedInProcedure() {
+		RoutineAliasInfo routineInfo = ((MethodCallNode)methodCall.getJavaValueNode()).routineInfo;
+		
+		// If this method is called before the routine has been resolved, routineInfo will be null

+		if (SanityManager.DEBUG)
+			SanityManager.ASSERT((routineInfo != null), "Failed to get routineInfo");
+
+		return routineInfo.getSQLAllowed();
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Tue Jul 18 15:11:50 2006
@@ -271,7 +271,14 @@
 			*/
 			if (needInternalSQL)
 				compilerContext.setReliability(CompilerContext.INTERNAL_SQL_LEGAL);
-
+			
+			// For before triggers, the action statement cannot contain calls
+			// to procedures that modify SQL data. If the action statement 
+			// contains a procedure call, this reliability will be used during
+			// bind of the call statement node. 
+			if(isBefore)
+				compilerContext.setReliability(CompilerContext.MODIFIES_SQL_DATA_PROCEDURE_ILLEGAL);
+					
 			actionNode.bind();
 
 			if (whenClause != null)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InternalTriggerExecutionContext.java
Tue Jul 18 15:11:50 2006
@@ -327,25 +327,21 @@
 	 */
 	public void validateStatement(ConstantAction constantAction) throws StandardException
 	{
-		// DDL statements are not allowed in triggers. Parser does not allow 
-		// DDL statements in a trigger's action statement. This runtime check
-		// is needed only for DDL statements executed by procedures within a 
-		// trigger context. 
-		if (constantAction instanceof DDLConstantAction) {
+
+		// DDL statements are not allowed in triggers. Direct use of DDL
+		// statements in a trigger's action statement is disallowed by the
+		// parser. However, this runtime check is needed to prevent execution
+		// of DDL statements by procedures within a trigger context. 
+ 		if (constantAction instanceof DDLConstantAction) {
 			throw StandardException.newException(SQLState.LANG_NO_DDL_IN_TRIGGER, triggerd.getName(),
constantAction.toString());
 		}
 		
-		// No INSERT/UPDATE/DELETE for a before trigger. Parser does not allow 
-		// these DML statements in a trigger's action statement in a before 
-		// trigger. Currently, parser does not disallow creation of before 
-		// triggers calling procedures that modify SQL data. This runtime check
-		// is needed to not allow execution of these DML statements by procedures
-		// within a before trigger context. 
-	 	else if (triggerd.isBeforeTrigger() && 
-				constantAction instanceof WriteCursorConstantAction)
-		{
-			throw StandardException.newException(SQLState.LANG_NO_DML_IN_TRIGGER, triggerd.getName(),
targetTableName);
-		}
+		// No INSERT/UPDATE/DELETE for a before trigger. There is no need to 
+ 		// check this here because parser does not allow these DML statements
+ 		// in a trigger's action statement in a before trigger. Parser also 
+ 		// disallows creation of before triggers calling procedures that modify
+ 		// SQL data.   
+		
 	}
 
 	/////////////////////////////////////////////////////////

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=423266&r1=423265&r2=423266&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 Tue Jul 18
15:11:50 2006
@@ -578,6 +578,7 @@
 42Z93=Constraints ''{0}'' and ''{1}'' have the same set of columns, which is not allowed.

 42Z9B=The external virtual table interface does not support BLOB or CLOB columns. ''{0}''
column ''{1}''. 
 42Z9D=''{0}'' statements are not allowed in ''{1}'' triggers.
+42Z9D.S.1=Procedures that modify SQL data are not allowed in BEFORE triggers.
 42Z9E=Constraint ''{0}'' is not a {1} constraint.
 42X94={0} ''{1}'' does not exist.
 42X96=The database class path contains an unknown jar file ''{0}''.

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=423266&r1=423265&r2=423266&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
Tue Jul 18 15:11:50 2006
@@ -985,8 +985,9 @@
     String LANG_READ_UNCOMMITTED                                       = "42Z9A";
     String LANG_VTI_BLOB_CLOB_UNSUPPORTED                              = "42Z9B";
 	String LANG_UNSUPPORTED_TRIGGER_STMT		   					   = "42Z9D";
-    String LANG_DROP_CONSTRAINT_TYPE                                   = "42Z9E";
-    String LANG_QUERY_TOO_COMPLEX                                      = "42ZA0";
+	String LANG_UNSUPPORTED_TRIGGER_PROC		   					   = "42Z9D.S.1";
+	String LANG_DROP_CONSTRAINT_TYPE                                   = "42Z9E";
+	String LANG_QUERY_TOO_COMPLEX                                      = "42ZA0";
     String LANG_INVALID_SQL_IN_BATCH                                   = "42ZA1";
 
 	//following 3 matches the DB2 sql states

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/procedureInTrigger.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/procedureInTrigger.out?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/procedureInTrigger.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/procedureInTrigger.out
Tue Jul 18 15:11:50 2006
@@ -384,24 +384,10 @@
 ij> drop trigger refer_old_row_trig;
 0 rows inserted/updated/deleted
 ij> --- create a before trigger that calls a procedure that modifies sql data. 
---- trigger creation will pass but firing should fail
+--- trigger creation should fail
 create trigger before_trig_modifies_sql no cascade BEFORE insert on t2 
 	for each STATEMENT mode db2sql call proc_modifies_sql_insert_op(1, 'one');
-0 rows inserted/updated/deleted
-ij> --- try to insert 2 rows
-insert into t2 values (1,2), (2,4);
-ERROR 38000: The exception 'SQL Exception: INSERT, UPDATE and DELETE are not permitted on
table APP.T2 because trigger BEFORE_TRIG_MODIFIES_SQL is active.' was thrown while evaluating
an expression.
-ERROR X0Y70: INSERT, UPDATE and DELETE are not permitted on table APP.T2 because trigger
BEFORE_TRIG_MODIFIES_SQL is active.
-ij> --- check trigger is not fired.
-select * from t1;
-I          |B              
----------------------------
-ij> --- check inserts failed
-select * from t2;
-X          |Y          
------------------------
-ij> drop trigger before_trig_modifies_sql;
-0 rows inserted/updated/deleted
+ERROR 42Z9D: Procedures that modify SQL data are not allowed in BEFORE triggers.
 ij> --- in a BEFORE trigger, call a procedure which actually modifies SQL data	
 --- trigger creation will pass but firing should fail
 create trigger bad_before_trig no cascade BEFORE insert on t2 
@@ -409,8 +395,8 @@
 0 rows inserted/updated/deleted
 ij> --- try to insert 2 rows
 insert into t2 values (1,2), (2,4);
-ERROR 38000: The exception 'SQL Exception: INSERT, UPDATE and DELETE are not permitted on
table APP.T2 because trigger BAD_BEFORE_TRIG is active.' was thrown while evaluating an expression.
-ERROR X0Y70: INSERT, UPDATE and DELETE are not permitted on table APP.T2 because trigger
BAD_BEFORE_TRIG is active.
+ERROR 38000: The exception 'SQL Exception: The external routine is not allowed to execute
SQL statements.' was thrown while evaluating an expression.
+ERROR 38001: The external routine is not allowed to execute SQL statements.
 ij> --- check trigger is not fired.
 select * from t1;
 I          |B              

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql?rev=423266&r1=423265&r2=423266&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/procedureInTrigger.sql
Tue Jul 18 15:11:50 2006
@@ -274,16 +274,9 @@
 drop trigger refer_old_row_trig;
 
 --- create a before trigger that calls a procedure that modifies sql data. 
---- trigger creation will pass but firing should fail
+--- trigger creation should fail
 create trigger before_trig_modifies_sql no cascade BEFORE insert on t2 
 	for each STATEMENT mode db2sql call proc_modifies_sql_insert_op(1, 'one');
---- try to insert 2 rows
-insert into t2 values (1,2), (2,4);
---- check trigger is not fired.
-select * from t1;
---- check inserts failed
-select * from t2;
-drop trigger before_trig_modifies_sql;
 
 --- in a BEFORE trigger, call a procedure which actually modifies SQL data	
 --- trigger creation will pass but firing should fail



Mime
View raw message