From derby-commits-return-13663-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Wed Jan 05 19:39:56 2011 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 39707 invoked from network); 5 Jan 2011 19:39:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 5 Jan 2011 19:39:56 -0000 Received: (qmail 48372 invoked by uid 500); 5 Jan 2011 19:39:56 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 48308 invoked by uid 500); 5 Jan 2011 19:39:56 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 48300 invoked by uid 99); 5 Jan 2011 19:39:56 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 05 Jan 2011 19:39:56 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 05 Jan 2011 19:39:51 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 3ECA323889E1; Wed, 5 Jan 2011 19:39:03 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1055601 - in /db/derby/code/branches/10.6/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ storeless/org/apache/derby/impl/storeless/ testing/org/apache/... Date: Wed, 05 Jan 2011 19:39:03 -0000 To: derby-commits@db.apache.org From: mamta@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20110105193903.3ECA323889E1@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: mamta Date: Wed Jan 5 19:39:02 2011 New Revision: 1055601 URL: http://svn.apache.org/viewvc?rev=1055601&view=rev Log: Backporting the changes for DERBY-4874 to 10.6 codeline. 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 Had to hand do these changes 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.6/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java db/derby/code/branches/10.6/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Modified: db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original) +++ db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Wed Jan 5 19:39:02 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; @@ -1167,6 +1169,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.6/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java (original) +++ db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java Wed Jan 5 19:39:02 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.6/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java (original) +++ db/derby/code/branches/10.6/java/engine/org/apache/derby/iapi/sql/dictionary/TriggerDescriptor.java Wed Jan 5 19:39:02 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.6/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original) +++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Wed Jan 5 19:39:02 2011 @@ -68,6 +68,12 @@ import org.apache.derby.iapi.sql.diction import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor; import org.apache.derby.iapi.sql.dictionary.PermDescriptor; +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; @@ -84,6 +90,7 @@ import org.apache.derby.iapi.types.SQLBo import org.apache.derby.iapi.types.SQLChar; import org.apache.derby.iapi.types.SQLLongint; 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.UserType; import org.apache.derby.iapi.types.DataTypeDescriptor; @@ -4274,6 +4281,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, @@ -4282,6 +4290,7 @@ public final class DataDictionaryImpl { updCols = new int[] {SYSSTATEMENTSRowFactory.SYSSTATEMENTS_VALID, + SYSSTATEMENTSRowFactory.SYSSTATEMENTS_TEXT, SYSSTATEMENTSRowFactory.SYSSTATEMENTS_LASTCOMPILED, SYSSTATEMENTSRowFactory.SYSSTATEMENTS_USINGTEXT, SYSSTATEMENTSRowFactory.SYSSTATEMENTS_CONSTANTSTATE }; @@ -4581,6 +4590,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() 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 + ** 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() 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.6/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java (original) +++ db/derby/code/branches/10.6/java/engine/org/apache/derby/impl/sql/compile/CreateTriggerNode.java Wed Jan 5 19:39:02 2011 @@ -286,7 +286,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++) @@ -306,12 +305,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 @@ -341,14 +335,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 @@ -373,75 +369,66 @@ public class CreateTriggerNode extends D // the actions of before triggers may not reference generated columns if ( isBefore ) { forbidActionsOnGenCols(); } - 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 { @@ -450,6 +437,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(); @@ -474,7 +463,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() " : @@ -490,6 +478,11 @@ public class CreateTriggerNode extends D } start=tokEndOffset-actionOffset+1; } + if (start < originalActionText.length()) + { + newText.append(originalActionText.substring(start)); + } + transformedActionText = newText.toString(); } /* @@ -497,13 +490,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); } @@ -516,16 +507,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; @@ -611,121 +594,7 @@ public class CreateTriggerNode extends D { return left.equals( right ); } - } - - /* - ** 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() 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 - ** 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() 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 Modified: db/derby/code/branches/10.6/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original) +++ db/derby/code/branches/10.6/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Wed Jan 5 19:39:02 2011 @@ -64,6 +64,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! @@ -481,6 +482,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.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java?rev=1055601&r1=1055600&r2=1055601&view=diff ============================================================================== --- db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java (original) +++ db/derby/code/branches/10.6/java/testing/org/apache/derbyTesting/functionTests/tests/lang/TriggerTest.java Wed Jan 5 19:39:02 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