db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1056168 - in /db/derby/code/branches/10.4: ./ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/sql/catalog/ java/engine/org/apache/derby/impl/sql/compile/ java/storeless/org/apache/derby/impl/storeless/ j...
Date Fri, 07 Jan 2011 02:33:57 GMT
Author: mamta
Date: Fri Jan  7 02:33:56 2011
New Revision: 1056168

URL: http://svn.apache.org/viewvc?rev=1056168&view=rev
Log:
Backporting the changes for DERBY-4874 to 10.4 codeline. This backport to 10.4 has been the
migration of changes committed to 10.6,
DERBY-4874 Trigger does not recognize new size of VARCHAR column expanded with ALTER TABLE.
It fails with ERROR 22001: A truncation error was encountered trying to shrink VARCHAR

The backport of DERBY-4874 from trunk to 10.6 codeline was done by hand (rather than a simple
migration from trunk) because the changes in trunk depended on DERBY-1482. DERBY-1482 can't
be backported to 10.6 and earlier because it had required system level changes which can't
be backported to pre-released Derby products.


Modified:
    db/derby/code/branches/10.4/   (props changed)
    db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
    db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
    db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
    db/derby/code/branches/10.4/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
    db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java

Propchange: db/derby/code/branches/10.4/
------------------------------------------------------------------------------
--- svn:mergeinfo (original)
+++ svn:mergeinfo Fri Jan  7 02:33:56 2011
@@ -1,2 +1,3 @@
 /db/derby/code/branches/10.5:814216,958230
+/db/derby/code/branches/10.6:1055601
 /db/derby/code/trunk:788436,793588,794303,796316,796372,797147,798347,798742,800523,803548,805696,809643,812669,816536,835286,882732,898635,915177,915733,917771,928065,934996,946794,954544,958163,958230,959550,980684,999119

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
Fri Jan  7 02:33:56 2011
@@ -44,6 +44,8 @@ import org.apache.derby.catalog.TypeDesc
 import org.apache.derby.catalog.UUID;
 import org.apache.derby.iapi.services.uuid.UUIDFactory;
 
+import org.apache.derby.impl.sql.compile.StatementNode;
+
 import java.sql.Types;
 import java.util.List;
 import java.util.Hashtable;
@@ -1108,6 +1110,73 @@ public interface DataDictionary
 				throws StandardException;
 
 	/**
+	 * This method does the job of transforming the trigger action plan text
+	 * as shown below. 
+	 * 	DELETE FROM t WHERE c = old.c
+	 * turns into
+	 *  DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	 *  	getTriggerExecutionContext().getOldRow().
+	 *      getInt(columnNumberFor'C'inRuntimeResultset);
+	 *  
+	 * It gets called either 
+	 * 1)at the trigger creation time for row level triggers or
+	 * 2)if the trigger got invalidated by some other sql earlier and the 
+	 * current sql needs that trigger to fire. For such a trigger firing 
+	 * case, this method will get called only if it is row level trigger 
+	 * with REFERENCES clause. This work was done as part of DERBY-4874. 
+	 * Before DERBY-4874, once the stored prepared statement for trigger 
+	 * action plan was generated, it was never updated ever again. But, 
+	 * one case where the trigger action plan needs to be regenerated is say
+	 * when the column length is changed for a column which is REFERENCEd as
+	 * old or new column value. eg of such a case would be say the Alter
+	 * table has changed the length of a varchar column from varchar(30) to
+	 * varchar(64) but the stored prepared statement associated with the 
+	 * trigger action plan continued to use varchar(30). To fix varchar(30) 
+	 * in stored prepared statement for trigger action sql to varchar(64), 
+	 * we need to regenerate the trigger action sql. This new trigger 
+	 * action sql will then get updated into SYSSTATEMENTS table.
+	 *
+	 * @param actionStmt This is needed to get access to the various nodes
+	 * 	generated by the Parser for the trigger action sql. These nodes will be
+	 * 	used to find REFERENCEs column nodes.
+	 * 
+	 * @param oldReferencingName The name specified by the user for REFERENCEs
+	 * 	to old row columns
+	 * 
+	 * @param newReferencingName The name specified by the user for REFERENCEs
+	 * 	to new row columns
+	 * 
+	 * @param triggerDefinition The original trigger action text provided by
+	 * 	the user during CREATE TRIGGER time.
+	 * 
+	 * @param referencedCols Trigger is defined on these columns (will be null
+	 *   in case of INSERT AND DELETE Triggers. Can also be null for DELETE
+	 *   Triggers if UPDATE trigger is not defined on specific column(s))
+	 *   
+	 * @param actionOffset offset of start of action clause
+	 * 
+	 * @param triggerTableDescriptor Table descriptor for trigger table
+	 * 
+	 * @param triggerEventMask TriggerDescriptor.TRIGGER_EVENT_XXX
+	 * 
+	 * @param createTriggerTime True if here for CREATE TRIGGER,
+	 * 
+	 * @return Transformed trigger action sql
+	 * @throws StandardException
+	 */
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+			boolean createTriggerTime)
+	throws StandardException;
+
+	/**
 	 * Load up the trigger descriptor list for this table
 	 * descriptor and return it.  If the descriptor list
 	 * is already loaded up, it is retuned without further

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
Fri Jan  7 02:33:56 2011
@@ -511,10 +511,29 @@ public class SPSDescriptor extends Tuple
 	 *
 	 * @return The text
 	 */
-	public final String getText()
+	public final synchronized String getText()
 	{
 		return text;
 	}
+	/**
+	 * It is possible that when a trigger is invalidated, the generated trigger
+	 * action sql associated with it needs to be regenerated. One example
+	 * of such a case would be when ALTER TABLE on the trigger table
+	 * changes the length of a column. The need for this code was found
+	 * as part of DERBY-4874 where the Alter table had changed the length 
+	 * of a varchar column from varchar(30) to varchar(64) but the generated 
+	 * trigger action plan continued to use varchar(30). To fix varchar(30) in
+	 * in trigger action sql to varchar(64), we need to regenerate the 
+	 * trigger action sql which is saved as stored prepared statement. This 
+	 * new trigger action sql will then get updated into SYSSTATEMENTS table.
+	 * DERBY-4874
+	 * 
+	 * @param newText
+	 */
+	public final synchronized void setText(String newText)
+	{
+		text = newText;
+	}
 
 	/**
 	 * Get the text of the USING clause used on CREATE

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java
Fri Jan  7 02:33:56 2011
@@ -30,7 +30,6 @@ import java.sql.Timestamp;
 
 import org.apache.derby.iapi.reference.SQLState;
 import org.apache.derby.iapi.services.sanity.SanityManager;
-import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.StatementType;
 import org.apache.derby.catalog.DependableFinder;
 import org.apache.derby.catalog.Dependable;
@@ -40,7 +39,9 @@ import org.apache.derby.iapi.sql.conn.La
 import org.apache.derby.iapi.store.access.TransactionController;
 import org.apache.derby.iapi.services.context.ContextService;
 
-import org.apache.derby.impl.sql.execute.DropTriggerConstantAction;
+import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Parser;
+import org.apache.derby.impl.sql.compile.StatementNode;
 
 import java.io.ObjectOutput;
 import java.io.ObjectInput;
@@ -301,8 +302,15 @@ public class TriggerDescriptor extends T
 	}
 
 	/**
-	 * Get the trigger action sps
+	 * Get the trigger action sps from SYSSTATEMENTS. If we find that
+	 * the sps is invalid and the trigger is defined at row level and it
+	 * has OLD/NEW transient variables through REFERENCES clause, then
+	 * the sps from SYSSTATEMENTS may not be valid anymore. In such a 
+	 * case, we regenerate the trigger action sql and use that for the
+	 * sps and update SYSSTATEMENTS using this new sps. This update of
+	 * SYSSTATEMENTS was introduced with DERBY-4874
 	 *
+	 * @param lcc	The LanguageConnectionContext to use.
 	 * @return the trigger action sps
 	 *
 	 * @exception StandardException on error
@@ -321,6 +329,46 @@ public class TriggerDescriptor extends T
 			actionSPS = getDataDictionary().getSPSDescriptor(actionSPSId);
 			lcc.commitNestedTransaction();
 		}
+		
+		//We need to regenerate the trigger action sql if 
+		//1)the trigger is found to be invalid, 
+		//2)the trigger is defined at row level (that is the only kind of 
+		//  trigger which allows reference to individual columns from 
+		//  old/new row)
+		//3)the trigger action plan has columns that reference 
+		//  old/new row columns
+		//This code was added as part of DERBY-4874 where the Alter table 
+		//had changed the length of a varchar column from varchar(30) to 
+		//varchar(64) but the trigger action plan continued to use varchar(30).
+		//To fix varchar(30) in trigger action sql to varchar(64), we need
+		//to regenerate the trigger action sql. This new trigger action sql
+		//will then get updated into SYSSTATEMENTS table.
+		if((!actionSPS.isValid() ||
+				 (actionSPS.getPreparedStatement() == null)) && 
+				 isRow &&
+				 (oldReferencingName != null || newReferencingName != null)) 
+		{
+			SchemaDescriptor compSchema;
+			compSchema = getDataDictionary().getSchemaDescriptor(triggerSchemaId, null);
+			CompilerContext newCC = lcc.pushCompilerContext(compSchema);
+			Parser	pa = newCC.getParser();
+			StatementNode stmtnode = (StatementNode)pa.parseStatement(triggerDefinition);
+			lcc.popCompilerContext(newCC);
+					
+			actionSPS.setText(getDataDictionary().getTriggerActionString(stmtnode, 
+					oldReferencingName,
+					newReferencingName,
+					triggerDefinition,
+					referencedCols,
+					0,
+					td,
+					-1,
+					false
+					));
+			//By this point, we are finished transforming the trigger action if
+			//it has any references to old/new transition variables.
+		}
+		
 		return actionSPS;
 	}
 

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
Fri Jan  7 02:33:56 2011
@@ -65,6 +65,12 @@ import org.apache.derby.iapi.sql.diction
 import org.apache.derby.iapi.sql.dictionary.ViewDescriptor;
 import org.apache.derby.iapi.sql.dictionary.SystemColumn;
 
+import org.apache.derby.impl.sql.compile.CollectNodesVisitor;
+import org.apache.derby.impl.sql.compile.ColumnReference;
+import org.apache.derby.impl.sql.compile.FromBaseTable;
+import org.apache.derby.impl.sql.compile.QueryTreeNode;
+import org.apache.derby.impl.sql.compile.StatementNode;
+import org.apache.derby.impl.sql.compile.TableName;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
 
 import org.apache.derby.impl.sql.depend.BasicDependencyManager;
@@ -79,6 +85,7 @@ import org.apache.derby.iapi.types.Numbe
 
 import org.apache.derby.iapi.types.SQLChar;
 import org.apache.derby.iapi.types.SQLVarchar;
+import org.apache.derby.iapi.types.TypeId;
 import org.apache.derby.iapi.types.StringDataValue;
 import org.apache.derby.iapi.types.DataTypeDescriptor;
 import org.apache.derby.iapi.types.DataValueDescriptor;
@@ -3756,6 +3763,7 @@ public final class	DataDictionaryImpl
 			if(firstCompilation)
 			{
 				updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID,
+						 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT,
 									 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE,
@@ -3764,6 +3772,7 @@ public final class	DataDictionaryImpl
 			{
 
 				updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID,
+						 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT,
 										 SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE };
@@ -4065,6 +4074,318 @@ public final class	DataDictionaryImpl
 	}
 
 	/**
+	 * Get the trigger action string associated with the trigger after the
+	 * references to old/new transition tables/variables in trigger action
+	 * sql provided by CREATE TRIGGER have been transformed eg
+	 *		DELETE FROM t WHERE c = old.c
+	 * turns into
+	 *	DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	 *		getTriggerExecutionContext().getOldRow().
+	 *      getInt(columnNumberFor'C'inTriggerTable)
+	 * or
+	 *	DELETE FROM t WHERE c in (SELECT c FROM OLD)
+	 * turns into
+	 *	DELETE FROM t WHERE c in 
+	 *		(SELECT c FROM new TriggerOldTransitionTable OLD)
+	 *
+	 * @param actionStmt This is needed to get access to the various nodes
+	 * 	generated by the Parser for the trigger action sql. These nodes will be
+	 * 	used to find REFERENCEs column nodes.
+	 * 
+	 * @param oldReferencingName The name specified by the user for REFERENCEs
+	 * 	to old row columns
+	 * 
+	 * @param newReferencingName The name specified by the user for REFERENCEs
+	 * 	to new row columns
+	 * 
+	 * @param triggerDefinition The original trigger action text provided by
+	 * 	the user during CREATE TRIGGER time.
+	 * 
+	 * @param referencedCols Trigger is defined on these columns (will be null
+	 *   in case of INSERT AND DELETE Triggers. Can also be null for DELETE
+	 *   Triggers if UPDATE trigger is not defined on specific column(s))
+	 * 
+	 * @param actionOffset offset of start of action clause
+	 * 
+	 * @param triggerTableDescriptor Table descriptor for trigger table
+	 * 
+	 * @param triggerEventMask TriggerDescriptor.TRIGGER_EVENT_XXX
+	 * 
+	 * @param createTriggerTime True if here for CREATE TRIGGER,
+	 * 	false if here because an invalidated row level trigger with 
+	 *  REFERENCEd columns has been fired and hence trigger action
+	 *  sql associated with SPSDescriptor may be invalid too.
+	 * 
+	 * @return Transformed trigger action sql
+	 * @throws StandardException
+	 */
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int actionOffset,
+			TableDescriptor triggerTableDescriptor,
+			int triggerEventMask,
+			boolean createTriggerTime
+			) throws StandardException
+	{
+		StringBuffer newText = new StringBuffer();
+		int start = 0;
+
+		//Total Number of columns in the trigger table
+		int numberOfColsInTriggerTable = triggerTableDescriptor.getNumberOfColumns();
+
+		CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
+		actionStmt.accept(visitor);
+		Vector refs = visitor.getList();
+		/* we need to sort on position in string, beetle 4324
+		 */
+		QueryTreeNode[] cols = sortRefs(refs, true);
+		
+		for (int i = 0; i < cols.length; i++)
+		{
+			ColumnReference ref = (ColumnReference) cols[i];
+			
+			/*
+			** Only occurrences of those OLD/NEW transition tables/variables 
+			** are of interest here.  There may be intermediate nodes in the 
+			** parse tree that have its own RCL which contains copy of 
+			** column references(CR) from other nodes. e.g.:  
+			**
+			** CREATE TRIGGER tt 
+			** AFTER INSERT ON x
+			** REFERENCING NEW AS n 
+			** FOR EACH ROW
+			**    INSERT INTO y VALUES (n.i), (999), (333);
+			** 
+			** The above trigger action will result in InsertNode that 
+			** contains a UnionNode of RowResultSetNodes.  The UnionNode
+			** will have a copy of the CRs from its left child and those CRs 
+			** will not have its beginOffset set which indicates they are 
+			** not relevant for the conversion processing here, so we can 
+			** safely skip them. 
+			*/
+			if (ref.getBeginOffset() == -1) 
+			{
+				continue;
+			}
+			
+			TableName tableName = ref.getTableNameNode();
+			if ((tableName == null) ||
+				((oldReferencingName == null || !oldReferencingName.equals(tableName.getTableName()))
&&
+				(newReferencingName == null || !newReferencingName.equals(tableName.getTableName()))))
+			{
+				continue;
+			}
+				
+			int tokBeginOffset = tableName.getBeginOffset();
+			int tokEndOffset = tableName.getEndOffset();
+			if (tokBeginOffset == -1)
+			{
+				continue;
+			}
+
+			if (createTriggerTime) {
+				checkInvalidTriggerReference(tableName.getTableName(),
+						oldReferencingName,
+						newReferencingName,
+						triggerEventMask);
+			}
+			String colName = ref.getColumnName();
+			int columnLength = ref.getEndOffset() - ref.getBeginOffset() + 1;
+
+			newText.append(triggerDefinition.substring(start, tokBeginOffset-actionOffset));
+			newText.append(genColumnReferenceSQL(triggerTableDescriptor, 
+					colName, 
+					tableName.getTableName(), 
+					tableName.getTableName().equals(oldReferencingName)));
+			start = tokEndOffset- actionOffset + columnLength + 2;
+		}
+
+		//By this point, we are finished transforming the trigger action if
+		//it has any references to old/new transition variables.
+		if (start < triggerDefinition.length())
+		{
+			newText.append(triggerDefinition.substring(start));
+		}
+		return newText.toString();
+	}
+
+	/*
+	** Check for illegal combinations here: insert & old or
+	** delete and new
+	*/
+	private void checkInvalidTriggerReference(String tableName,
+			String oldReferencingName,
+			String newReferencingName,
+			int triggerEventMask) throws StandardException
+	{
+		if (tableName.equals(oldReferencingName) && 
+			(triggerEventMask & TriggerDescriptor.TRIGGER_EVENT_INSERT) == TriggerDescriptor.TRIGGER_EVENT_INSERT)
+		{
+			throw StandardException.newException(SQLState.LANG_TRIGGER_BAD_REF_MISMATCH, "INSERT",
"new");
+		}
+		else if (tableName.equals(newReferencingName) && 
+			(triggerEventMask & TriggerDescriptor.TRIGGER_EVENT_DELETE) == TriggerDescriptor.TRIGGER_EVENT_DELETE)
+		{
+			throw StandardException.newException(SQLState.LANG_TRIGGER_BAD_REF_MISMATCH, "DELETE",
"old");
+		}
+	}
+	
+	/*
+	** Make sure the given column name is found in the trigger
+	** target table.  Generate the appropriate SQL to get it.
+	**
+	** @return a string that is used to get the column using
+	** getObject() on the desired result set and CAST it back
+	** to the proper type in the SQL domain. 
+	**
+	** @exception StandardException on invalid column name
+	*/
+	private String genColumnReferenceSQL
+	(
+			TableDescriptor td,
+		String			colName, 
+		String			tabName,
+		boolean			isOldTable
+	) throws StandardException
+	{
+		ColumnDescriptor colDesc = null;
+		if ((colDesc = td.getColumnDescriptor(colName)) == 
+                null)
+		{
+			throw StandardException.newException(
+                SQLState.LANG_COLUMN_NOT_FOUND, tabName+"."+colName);
+		}
+
+		/*
+		** Generate something like this:
+		**
+		** 		CAST (org.apache.derby.iapi.db.Factory::
+		**			getTriggerExecutionContext().getNewRow().
+		**				getObject(<colPosition>) AS DECIMAL(6,2))
+        **
+        ** Column position is used to avoid the wrong column being
+        ** selected problem (DERBY-1258) caused by the case insensitive
+        ** JDBC rules for fetching a column by name.
+		**
+		** The cast back to the SQL Domain may seem redundant
+		** but we need it to make the column reference appear
+		** EXACTLY like a regular column reference, so we need
+		** the object in the SQL Domain and we need to have the
+		** type information.  Thus a user should be able to do 
+		** something like
+		**
+		**		CREATE TRIGGER ... INSERT INTO T length(Column), ...
+        **
+        */
+
+		DataTypeDescriptor  dts     = colDesc.getType();
+		TypeId              typeId  = dts.getTypeId();
+
+        if (!typeId.isXMLTypeId())
+        {
+
+            StringBuffer methodCall = new StringBuffer();
+            methodCall.append(
+                "CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
+            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
+            methodCall.append(".getObject(");
+            methodCall.append(colDesc.getPosition());
+            methodCall.append(") AS ");
+
+            /*
+            ** getSQLString() returns <typeName> 
+            ** for user types, so call getSQLTypeName in that
+            ** case.
+            */
+            methodCall.append(
+                (typeId.userType() ? 
+                     typeId.getSQLTypeName() : dts.getSQLstring()));
+            
+            methodCall.append(") ");
+
+            return methodCall.toString();
+        }
+        else
+        {
+            /*  DERBY-2350
+            **
+            **  Triggers currently use jdbc 1.2 to access columns.  The default
+            **  uses getObject() which is not supported for an XML type until
+            **  jdbc 4.  In the meantime use getString() and then call 
+            **  XMLPARSE() on the string to get the type.  See Derby issue and
+            **  http://wiki.apache.org/db-derby/TriggerImplementation , for
+            **  better long term solutions.  Long term I think changing the
+            **  trigger architecture to not rely on jdbc, but instead on an
+            **  internal direct access interface to execution nodes would be
+            **  best future direction, but believe such a change appropriate
+            **  for a major release, not a bug fix.
+            **
+            **  Rather than the above described code generation, use the 
+            **  following for XML types to generate an XML column from the
+            **  old or new row.
+            ** 
+            **          XMLPARSE(DOCUMENT
+            **              CAST (org.apache.derby.iapi.db.Factory::
+            **                  getTriggerExecutionContext().getNewRow().
+            **                      getString(<colPosition>) AS CLOB)  
+            **                        PRESERVE WHITESPACE)
+            */
+
+            StringBuffer methodCall = new StringBuffer();
+            methodCall.append("XMLPARSE(DOCUMENT CAST( ");
+            methodCall.append(
+                "org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
+            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
+            methodCall.append(".getString(");
+            methodCall.append(colDesc.getPosition());
+            methodCall.append(") AS CLOB) PRESERVE WHITESPACE ) ");
+
+            return methodCall.toString();
+        }
+	}
+
+	/*
+	** Sort the refs into array.
+	*/
+	private QueryTreeNode[] sortRefs(Vector refs, boolean isRow)
+	{
+		int size = refs.size();
+		QueryTreeNode[] sorted = (QueryTreeNode[])refs.toArray(new QueryTreeNode[size]);
+		int i = 0;
+		/* bubble sort
+		 */
+		QueryTreeNode temp;
+		for (i = 0; i < size - 1; i++)
+		{
+			temp = null;
+			for (int j = 0; j < size - i - 1; j++)
+			{
+				if ((isRow && 
+					 sorted[j].getBeginOffset() > 
+					 sorted[j+1].getBeginOffset()
+					) ||
+					(!isRow &&
+					 ((FromBaseTable) sorted[j]).getTableNameField().getBeginOffset() > 
+					 ((FromBaseTable) sorted[j+1]).getTableNameField().getBeginOffset()
+					))
+				{
+					temp = sorted[j];
+					sorted[j] = sorted[j+1];
+					sorted[j+1] = temp;
+				}
+			}
+			if (temp == null)		// sorted
+				break;
+		}
+
+		return sorted;
+	}
+
+	/**
 	 * Get a TriggerDescriptor given its UUID.
 	 *
 	 * @param uuid	The UUID

Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
(original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java
Fri Jan  7 02:33:56 2011
@@ -285,7 +285,6 @@ public class CreateTriggerNode extends D
 		*/
 		if (triggerCols != null && triggerCols.size() != 0)
 		{
-			referencedColInts = new int[triggerCols.size()];
 			Hashtable columnNames = new Hashtable();
 			int tcSize = triggerCols.size();
 			for (int i = 0; i < tcSize; i++)
@@ -305,12 +304,7 @@ public class CreateTriggerNode extends D
 																rc.getName(),
 																tableName);
 				}
-
-				referencedColInts[i] = cd.getPosition();
 			}
- 
-			// sort the list
-			java.util.Arrays.sort(referencedColInts);
 		}
 
 		//If attempting to reference a SESSION schema table (temporary or permanent) in the trigger
action, throw an exception
@@ -340,14 +334,16 @@ public class CreateTriggerNode extends D
 	** 1) validate the referencing clause (if any)
 	**
 	** 2) convert trigger action text.  e.g. 
-	**		DELETE FROM t WHERE c = old.c
+	**	DELETE FROM t WHERE c = old.c
 	** turns into
-	**		DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
-	**					getTriggerExecutionContext().getOldRow().getInt('C');
+	**	DELETE FROM t WHERE c = org.apache.derby.iapi.db.Factory::
+	**		getTriggerExecutionContext().getOldRow().
+	**      getInt(columnNumberFor'C'inTriggerTable);
 	** or
-	**		DELETE FROM t WHERE c in (SELECT c FROM OLD)
+	**	DELETE FROM t WHERE c in (SELECT c FROM OLD)
 	** turns into
-	**		DELETE FROM t WHERE c in (SELECT c FROM new TriggerOldTransitionTable OLD)
+	**	DELETE FROM t WHERE c in (
+	**      SELECT c FROM new TriggerOldTransitionTable OLD)
 	**
 	** 3) check all column references against new/old transition 
 	**	variables (since they are no longer 'normal' column references
@@ -369,75 +365,66 @@ public class CreateTriggerNode extends D
 	{
 		validateReferencesClause(dd);
 
-		StringBuffer newText = new StringBuffer();
-		boolean regenNode = false;
+		String transformedActionText;
 		int start = 0;
+		if (triggerCols != null && triggerCols.size() != 0) {
+			//If the trigger is defined on speific columns, then collect
+			//their column positions and ensure that those columns do
+			//indeed exist in the trigger table.
+			referencedColInts = new int[triggerCols.size()];
+			ResultColumn rc;
+			ColumnDescriptor cd;
+			//This is the most interesting case for us. If we are here, 
+			//then it means that the trigger is defined at the row level
+			//and a set of trigger columns are specified in the CREATE
+			//TRIGGER statement. This can only happen for an UPDATE
+			//trigger.
+			//eg
+			//CREATE TRIGGER tr1 AFTER UPDATE OF c12 ON table1 
+			//    REFERENCING OLD AS oldt NEW AS newt
+			//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+			
+			for (int i=0; i < triggerCols.size(); i++){
+				rc = (ResultColumn)triggerCols.elementAt(i);
+				cd = triggerTableDescriptor.getColumnDescriptor(rc.getName());
+				//Following will catch the case where an invalid trigger column
+				//has been specified in CREATE TRIGGER statement.
+				//CREATE TRIGGER tr1 AFTER UPDATE OF c1678 ON table1 
+				//    REFERENCING OLD AS oldt NEW AS newt
+				//    FOR EACH ROW UPDATE table2 SET c24=oldt.c14;
+				if (cd == null)
+				{
+					throw StandardException.newException(SQLState.LANG_COLUMN_NOT_FOUND_IN_TABLE, 
+																rc.getName(),
+																tableName);
+				}
+				referencedColInts[i] = cd.getPosition();
+			}
+			// sort the list
+			java.util.Arrays.sort(referencedColInts);
+		}
+		
 		if (isRow)
 		{
 			/*
 			** For a row trigger, we find all column references.  If
 			** they are referencing NEW or OLD we turn them into
-			** getTriggerExecutionContext().getOldRow().getInt('C');
+			** getTriggerExecutionContext().
+			** getOldRow().getInt(columnNumberFor'C'inTriggerTable);
 			*/
-			CollectNodesVisitor visitor = new CollectNodesVisitor(ColumnReference.class);
-			actionNode.accept(visitor);
-			Vector refs = visitor.getList();
-			/* we need to sort on position in string, beetle 4324
-			 */
-			QueryTreeNode[] cols = sortRefs(refs, true);
-
-			for (int i = 0; i < cols.length; i++)
-			{
-				ColumnReference ref = (ColumnReference) cols[i];
-				
-				/*
-				** Only occurrences of those OLD/NEW transition tables/variables 
-				** are of interest here.  There may be intermediate nodes in the 
-				** parse tree that have its own RCL which contains copy of 
-				** column references(CR) from other nodes. e.g.:  
-				**
-				** CREATE TRIGGER tt 
-				** AFTER INSERT ON x
-				** REFERENCING NEW AS n 
-				** FOR EACH ROW
-				**    INSERT INTO y VALUES (n.i), (999), (333);
-				** 
-				** The above trigger action will result in InsertNode that 
-				** contains a UnionNode of RowResultSetNodes.  The UnionNode
-				** will have a copy of the CRs from its left child and those CRs 
-				** will not have its beginOffset set which indicates they are 
-				** not relevant for the conversion processing here, so we can 
-				** safely skip them. 
-				*/
-				if (ref.getBeginOffset() == -1) 
-				{
-					continue;
-				}
-				
-				TableName tableName = ref.getTableNameNode();
-				if ((tableName == null) ||
-					((oldTableName == null || !oldTableName.equals(tableName.getTableName())) &&
-					(newTableName == null || !newTableName.equals(tableName.getTableName()))))
-				{
-					continue;
-				}
-					
-				int tokBeginOffset = tableName.getBeginOffset();
-				int tokEndOffset = tableName.getEndOffset();
-				if (tokBeginOffset == -1)
-				{
-					continue;
-				}
-
-				regenNode = true;
-				checkInvalidTriggerReference(tableName.getTableName());
-				String colName = ref.getColumnName();
-				int columnLength = ref.getEndOffset() - ref.getBeginOffset() + 1;
-
-				newText.append(originalActionText.substring(start, tokBeginOffset-actionOffset));
-				newText.append(genColumnReferenceSQL(dd, colName, tableName.getTableName(), tableName.getTableName().equals(oldTableName)));
-				start = tokEndOffset- actionOffset + columnLength + 2;
-			}
+			//Now that we have verified that are no invalid column references
+			//for trigger columns, let's go ahead and transform the OLD/NEW
+			//transient table references in the trigger action sql.
+			transformedActionText = getDataDictionary().getTriggerActionString(actionNode, 
+					oldTableName,
+					newTableName,
+					originalActionText,
+					referencedColInts,
+					actionOffset,
+					triggerTableDescriptor,
+					triggerEventMask,
+					true
+					);			
 		}
 		else
 		{
@@ -446,6 +433,8 @@ public class CreateTriggerNode extends D
 			** the from table is NEW or OLD (or user designated alternates
 			** REFERENCING), we turn them into a trigger table VTI.
 			*/
+			StringBuffer newText = new StringBuffer();
+
 			CollectNodesVisitor visitor = new CollectNodesVisitor(FromBaseTable.class);
 			actionNode.accept(visitor);
 			Vector refs = visitor.getList();
@@ -470,7 +459,6 @@ public class CreateTriggerNode extends D
 
 				checkInvalidTriggerReference(baseTableName);
 
-				regenNode = true;
 				newText.append(originalActionText.substring(start, tokBeginOffset-actionOffset));
 				newText.append(baseTableName.equals(oldTableName) ?
 								"new org.apache.derby.catalog.TriggerOldTransitionRows() " :
@@ -486,6 +474,11 @@ public class CreateTriggerNode extends D
 				}
 				start=tokEndOffset-actionOffset+1;
 			}
+			if (start < originalActionText.length())
+			{
+				newText.append(originalActionText.substring(start));
+			}
+			transformedActionText = newText.toString();
 		}
 
 		/*
@@ -493,13 +486,11 @@ public class CreateTriggerNode extends D
 		** Also, we reset the actionText to this new value.  This
 		** is what we are going to stick in the system tables.
 		*/
-		if (regenNode)
+		boolean regenNode = false;
+		if (!transformedActionText.equals(actionText))
 		{
-			if (start < originalActionText.length())
-			{
-				newText.append(originalActionText.substring(start));
-			}
-			actionText = newText.toString();
+			regenNode = true;
+			actionText = transformedActionText;
 			actionNode = parseStatement(actionText, true);
 		}
 
@@ -512,16 +503,8 @@ public class CreateTriggerNode extends D
 	private QueryTreeNode[] sortRefs(Vector refs, boolean isRow)
 	{
 		int size = refs.size();
-		QueryTreeNode[] sorted = new QueryTreeNode[size];
+		QueryTreeNode[] sorted = (QueryTreeNode[]) refs.toArray(new QueryTreeNode[size]);
 		int i = 0;
-		for (Enumeration e = refs.elements(); e.hasMoreElements(); )
-		{
-			if (isRow)
-				sorted[i++] = (ColumnReference)e.nextElement();
-			else
-				sorted[i++] = (FromBaseTable)e.nextElement();
-		}
-
 		/* bubble sort
 		 */
 		QueryTreeNode temp;
@@ -552,120 +535,6 @@ public class CreateTriggerNode extends D
 	}
 
 	/*
-	** Make sure the given column name is found in the trigger
-	** target table.  Generate the appropriate SQL to get it.
-	**
-	** @return a string that is used to get the column using
-	** getObject() on the desired result set and CAST it back
-	** to the proper type in the SQL domain. 
-	**
-	** @exception StandardException on invalid column name
-	*/
-	private String genColumnReferenceSQL
-	(
-		DataDictionary	dd, 
-		String			colName, 
-		String			tabName,
-		boolean			isOldTable
-	) throws StandardException
-	{
-		ColumnDescriptor colDesc = null;
-		if ((colDesc = triggerTableDescriptor.getColumnDescriptor(colName)) == 
-                null)
-		{
-			throw StandardException.newException(
-                SQLState.LANG_COLUMN_NOT_FOUND, tabName+"."+colName);
-		}
-
-		/*
-		** Generate something like this:
-		**
-		** 		CAST (org.apache.derby.iapi.db.Factory::
-		**			getTriggerExecutionContext().getNewRow().
-		**				getObject(<colPosition>) AS DECIMAL(6,2))
-        **
-        ** Column position is used to avoid the wrong column being
-        ** selected problem (DERBY-1258) caused by the case insensitive
-        ** JDBC rules for fetching a column by name.
-		**
-		** The cast back to the SQL Domain may seem redundant
-		** but we need it to make the column reference appear
-		** EXACTLY like a regular column reference, so we need
-		** the object in the SQL Domain and we need to have the
-		** type information.  Thus a user should be able to do 
-		** something like
-		**
-		**		CREATE TRIGGER ... INSERT INTO T length(Column), ...
-        **
-        */
-
-		DataTypeDescriptor  dts     = colDesc.getType();
-		TypeId              typeId  = dts.getTypeId();
-
-        if (!typeId.isXMLTypeId())
-        {
-
-            StringBuffer methodCall = new StringBuffer();
-            methodCall.append(
-                "CAST (org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
-            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
-            methodCall.append(".getObject(");
-            methodCall.append(colDesc.getPosition());
-            methodCall.append(") AS ");
-
-            /*
-            ** getSQLString() returns <typeName> 
-            ** for user types, so call getSQLTypeName in that
-            ** case.
-            */
-            methodCall.append(
-                (typeId.userType() ? 
-                     typeId.getSQLTypeName() : dts.getSQLstring()));
-            
-            methodCall.append(") ");
-
-            return methodCall.toString();
-        }
-        else
-        {
-            /*  DERBY-2350
-            **
-            **  Triggers currently use jdbc 1.2 to access columns.  The default
-            **  uses getObject() which is not supported for an XML type until
-            **  jdbc 4.  In the meantime use getString() and then call 
-            **  XMLPARSE() on the string to get the type.  See Derby issue and
-            **  http://wiki.apache.org/db-derby/TriggerImplementation , for
-            **  better long term solutions.  Long term I think changing the
-            **  trigger architecture to not rely on jdbc, but instead on an
-            **  internal direct access interface to execution nodes would be
-            **  best future direction, but believe such a change appropriate
-            **  for a major release, not a bug fix.
-            **
-            **  Rather than the above described code generation, use the 
-            **  following for XML types to generate an XML column from the
-            **  old or new row.
-            ** 
-            **          XMLPARSE(DOCUMENT
-            **              CAST (org.apache.derby.iapi.db.Factory::
-            **                  getTriggerExecutionContext().getNewRow().
-            **                      getString(<colPosition>) AS CLOB)  
-            **                        PRESERVE WHITESPACE)
-            */
-
-            StringBuffer methodCall = new StringBuffer();
-            methodCall.append("XMLPARSE(DOCUMENT CAST( ");
-            methodCall.append(
-                "org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().");
-            methodCall.append(isOldTable ? "getOldRow()" : "getNewRow()");
-            methodCall.append(".getString(");
-            methodCall.append(colDesc.getPosition());
-            methodCall.append(") AS CLOB) PRESERVE WHITESPACE ) ");
-
-            return methodCall.toString();
-        }
-	}
-
-	/*
 	** Check for illegal combinations here: insert & old or
 	** delete and new
 	*/

Modified: db/derby/code/branches/10.4/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
(original)
+++ db/derby/code/branches/10.4/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
Fri Jan  7 02:33:56 2011
@@ -61,6 +61,7 @@ import org.apache.derby.iapi.types.DataT
 import org.apache.derby.iapi.types.DataValueFactory;
 import org.apache.derby.iapi.types.NumberDataValue;
 import org.apache.derby.iapi.types.RowLocation;
+import org.apache.derby.impl.sql.compile.StatementNode;
 
 /**
  * DataDictionary implementation that does nothing!
@@ -447,6 +448,21 @@ public class EmptyDictionary implements 
 
 	}
 
+	public String getTriggerActionString(
+			StatementNode actionStmt,
+			String oldReferencingName,
+			String newReferencingName,
+			String triggerDefinition,
+			int[] referencedCols,
+			int actionOffset,
+			TableDescriptor td,
+			int triggerEventMask,
+			boolean createTriggerTime)
+	throws StandardException {
+		// TODO Auto-generated method stub
+		return null;
+	}
+
 	public TriggerDescriptor getTriggerDescriptor(UUID uuid)
 			throws StandardException {
 		// TODO Auto-generated method stub

Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1056168&r1=1056167&r2=1056168&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
(original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java
Fri Jan  7 02:33:56 2011
@@ -107,6 +107,54 @@ public class TriggerTest extends BaseJDB
     }
     
     /**
+     * Altering the column length should regenerate the trigger
+     * action plan which is saved in SYSSTATEMENTS. DERBY-4874
+     * 
+     * @throws SQLException 
+     * 
+     */
+    public void testAlerColumnLength() throws SQLException
+    {
+        Statement s = createStatement();
+        s.executeUpdate("CREATE TABLE TestAlterTable( " +
+        		"element_id INTEGER NOT NULL, "+
+        		"altered_id VARCHAR(30) NOT NULL, "+
+        		"counter SMALLINT NOT NULL DEFAULT 0, "+
+        		"timets TIMESTAMP NOT NULL)");
+        s.executeUpdate("CREATE TRIGGER mytrig "+
+        		"AFTER UPDATE ON TestAlterTable "+
+        		"REFERENCING NEW AS newt OLD AS oldt "+
+        		"FOR EACH ROW MODE DB2SQL "+
+        		"  UPDATE TestAlterTable set "+
+        		"  TestAlterTable.counter = CASE WHEN "+
+        		"  (oldt.counter < 32767) THEN (oldt.counter + 1) ELSE 1 END "+
+        		"  WHERE ((newt.counter is null) or "+
+        		"  (oldt.counter = newt.counter)) " +
+        		"  AND newt.element_id = TestAlterTable.element_id "+
+        		"  AND newt.altered_id = TestAlterTable.altered_id");
+        s.executeUpdate("ALTER TABLE TestAlterTable ALTER altered_id "+
+        		"SET DATA TYPE VARCHAR(64)");
+        s.executeUpdate("insert into TestAlterTable values (99, "+
+        		"'012345678901234567890123456789001234567890',"+
+        		"1,CURRENT_TIMESTAMP)");
+
+        ResultSet rs = s.executeQuery("SELECT element_id, counter "+
+        		"FROM TestAlterTable");
+        JDBC.assertFullResultSet(rs, 
+        		new String[][] {{"99", "1"}});
+        
+        s.executeUpdate("update TestAlterTable "+
+        		"set timets = CURRENT_TIMESTAMP "+
+        		"where ELEMENT_ID = 99");
+        rs = s.executeQuery("SELECT element_id, counter "+
+        		"FROM TestAlterTable");
+        JDBC.assertFullResultSet(rs, 
+        		new String[][] {{"99", "2"}});
+
+        s.executeUpdate("DROP TABLE TestAlterTable");
+    }
+    
+    /**
      * Test the firing order of triggers. Should be:
      * 
      * Before operations



Mime
View raw message