You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by dj...@apache.org on 2006/07/19 00:11:51 UTC
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...
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