Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 45293 invoked from network); 29 Oct 2008 13:07:23 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 29 Oct 2008 13:07:23 -0000 Received: (qmail 42097 invoked by uid 500); 29 Oct 2008 13:07:28 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 42078 invoked by uid 500); 29 Oct 2008 13:07:27 -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 42069 invoked by uid 99); 29 Oct 2008 13:07:27 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 29 Oct 2008 06:07:27 -0700 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, 29 Oct 2008 13:06:20 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 1B6A1238889D; Wed, 29 Oct 2008 06:06:31 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r708900 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/execute/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Wed, 29 Oct 2008 13:06:30 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20081029130631.1B6A1238889D@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Wed Oct 29 06:06:30 2008 New Revision: 708900 URL: http://svn.apache.org/viewvc?rev=708900&view=rev Log: DERBY-481: Basic UPDATE support for generated columns. Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java?rev=708900&r1=708899&r2=708900&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java Wed Oct 29 06:06:30 2008 @@ -200,11 +200,13 @@ updated in the target table. This result set must contain a column which provides RowLocations that are valid in the target table, and new values to be placed in those rows. + @param generationClauses The code to compute column generation clauses if any @param checkGM The code to enforce the check constraints, if any @return the update operation as a result set. @exception StandardException thrown when unable to perform the update */ - ResultSet getUpdateResultSet(NoPutResultSet source, GeneratedMethod checkGM) + ResultSet getUpdateResultSet(NoPutResultSet source, GeneratedMethod generationClauses, + GeneratedMethod checkGM) throws StandardException; /** @@ -226,6 +228,7 @@ updated in the target table. This result set must contain a column which provides RowLocations that are valid in the target table, and new values to be placed in those rows. + @param generationClauses The code to compute generated columns, if any @param checkGM The code to enforce the check constraints, if any @param constantActionItem a constant action saved object reference @param rsdItem result Description, saved object id. @@ -233,6 +236,7 @@ @exception StandardException thrown when unable to perform the update */ ResultSet getDeleteCascadeUpdateResultSet(NoPutResultSet source, + GeneratedMethod generationClauses, GeneratedMethod checkGM, int constantActionItem, int rsdItem) Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java?rev=708900&r1=708899&r2=708900&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/UpdateNode.java Wed Oct 29 06:06:30 2008 @@ -21,14 +21,17 @@ package org.apache.derby.impl.sql.compile; +import org.apache.derby.catalog.DefaultInfo; +import org.apache.derby.catalog.UUID; + import org.apache.derby.iapi.services.context.ContextManager; import org.apache.derby.iapi.services.loader.GeneratedMethod; import org.apache.derby.iapi.services.compiler.MethodBuilder; - import org.apache.derby.impl.sql.compile.ActivationClassBuilder; import org.apache.derby.iapi.sql.conn.Authorizer; +import org.apache.derby.iapi.sql.compile.C_NodeTypes; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.impl.sql.execute.FKInfo; import org.apache.derby.iapi.services.compiler.MethodBuilder; @@ -42,6 +45,8 @@ import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; +import org.apache.derby.iapi.sql.dictionary.ColumnDescriptor; +import org.apache.derby.iapi.sql.dictionary.ColumnDescriptorList; import org.apache.derby.iapi.sql.dictionary.ConglomerateDescriptor; import org.apache.derby.iapi.sql.dictionary.ConstraintDescriptorList; import org.apache.derby.iapi.sql.dictionary.ConstraintDescriptor; @@ -74,6 +79,8 @@ import java.lang.reflect.Modifier; import java.sql.SQLException; +import java.util.ArrayList; +import java.util.Arrays; import java.util.Properties; import java.util.Vector; @@ -321,6 +328,15 @@ " on return from RS.bindExpressions()"); } + // + // Add generated columns whose generation clauses mention columns + // in the user's original update list. + // + ColumnDescriptorList addedGeneratedColumns = new ColumnDescriptorList(); + ColumnDescriptorList affectedGeneratedColumns = new ColumnDescriptorList(); + addGeneratedColumns + ( targetTableDescriptor, resultSet, affectedGeneratedColumns, addedGeneratedColumns ); + /* ** The current result column list is the one supplied by the user. ** Mark these columns as "updated", so we can tell later which @@ -357,6 +373,9 @@ fromList); getCompilerContext().popCurrentPrivType(); + // don't allow overriding of generation clauses + forbidGenerationOverrides( resultSet.getResultColumns(), true, addedGeneratedColumns ); + LanguageConnectionContext lcc = getLanguageConnectionContext(); if (lcc.getAutoincrementUpdate() == false) resultSet.getResultColumns().checkAutoincrement(null); @@ -447,7 +466,7 @@ readColsBitSet = getReadMap(dataDictionary, targetTableDescriptor, - afterColumns); + afterColumns, affectedGeneratedColumns ); afterColumns = fbt.addColsToList(afterColumns, readColsBitSet); resultColumnList = fbt.addColsToList(resultColumnList, readColsBitSet); @@ -554,7 +573,7 @@ getContextManager()); - if (hasCheckConstraints(dataDictionary, targetTableDescriptor)) + if (hasCheckConstraints(dataDictionary, targetTableDescriptor) || hasGenerationClauses( targetTableDescriptor ) ) { /* Get and bind all check constraints on the columns * being updated. We want to bind the check constraints against @@ -588,12 +607,18 @@ boolean hasTriggers = (getAllRelevantTriggers(dataDictionary, targetTableDescriptor, changedColumnIds, true).size() > 0); + ResultColumnList sourceRCL = hasTriggers ? resultColumnList : afterColumns; + + /* bind all generation clauses for generated columns */ + parseAndBindGenerationClauses + ( dataDictionary, targetTableDescriptor, afterColumns, resultColumnList, true, resultSet ); + /* Get and bind all constraints on the columns being updated */ checkConstraints = bindConstraints( dataDictionary, getNodeFactory(), targetTableDescriptor, null, - hasTriggers ? resultColumnList : afterColumns, + sourceRCL, changedColumnIds, readColsBitSet, false, @@ -817,19 +842,22 @@ } else { + // arg 2 generate code to evaluate generation clauses + generateGenerationClauses( resultColumnList, resultSet.getResultSetNumber(), acb, mb ); + // generate code to evaluate CHECK CONSTRAINTS - generateCheckConstraints( checkConstraints, acb, mb ); // arg 2 + generateCheckConstraints( checkConstraints, acb, mb ); // arg 3 if(isDependentTable) { mb.push(acb.addItem(makeConstantAction())); mb.push(acb.addItem(makeResultDescription())); mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null, "getDeleteCascadeUpdateResultSet", - ClassName.ResultSet, 4); + ClassName.ResultSet, 5); }else { mb.callMethod(VMOpcode.INVOKEINTERFACE, (String) null, "getUpdateResultSet", - ClassName.ResultSet, 2); + ClassName.ResultSet, 3); } } } @@ -851,6 +879,7 @@ * These are the columns needed to
    : *
  • maintain indices
    • *
  • maintain foreign keys
    • + *
  • maintain generated columns
    • *
  • support Replication's Delta Optimization
*

* The returned map is a FormatableBitSet with 1 bit for each column in the @@ -864,6 +893,7 @@ * @param dd the data dictionary to look in * @param baseTable the base table descriptor * @param updateColumnList the rcl for the update. CANNOT BE NULL + * @param affectedGeneratedColumns columns whose generation clauses mention columns being updated * * @return a FormatableBitSet of columns to be read out of the base table * @@ -873,7 +903,8 @@ ( DataDictionary dd, TableDescriptor baseTable, - ResultColumnList updateColumnList + ResultColumnList updateColumnList, + ColumnDescriptorList affectedGeneratedColumns ) throws StandardException { @@ -884,8 +915,10 @@ relevantCdl = new ConstraintDescriptorList(); relevantTriggers = new GenericDescriptorList(); - FormatableBitSet columnMap = UpdateNode.getUpdateReadMap(baseTable, - updateColumnList, conglomVector, relevantCdl, relevantTriggers, needsDeferredProcessing ); + FormatableBitSet columnMap = getUpdateReadMap + ( + baseTable, updateColumnList, conglomVector, relevantCdl, + relevantTriggers, needsDeferredProcessing, affectedGeneratedColumns ); markAffectedIndexes( conglomVector ); @@ -919,6 +952,8 @@ * 5) finds all triggers which overlap the updated columns. * 6) if there are any triggers, marks all columns in the bitmap * 7) adds the triggers to an evolving list of triggers + * 8) finds all generated columns whose generation clauses mention + * the updated columns and adds all of the mentioned columns * * @param updateColumnList a list of updated columns * @param conglomVector OUT: vector of affected indices @@ -928,6 +963,7 @@ * deferred processing. set while evaluating this * routine if a trigger or constraint requires * deferred processing + * @param affectedGeneratedColumns columns whose generation clauses mention updated columns * * @return a FormatableBitSet of columns to be read out of the base table * @@ -940,7 +976,8 @@ Vector conglomVector, ConstraintDescriptorList relevantConstraints, GenericDescriptorList relevantTriggers, - boolean[] needsDeferredProcessing + boolean[] needsDeferredProcessing, + ColumnDescriptorList affectedGeneratedColumns ) throws StandardException { @@ -999,6 +1036,12 @@ } } + // + // Add all columns mentioned by generation clauses which are affected + // by the columns being updated. + // + addGeneratedColumnPrecursors( affectedGeneratedColumns, columnMap ); + /* ** If we have any triggers, then get all the columns ** because we don't know what the user will ultimately @@ -1019,6 +1062,110 @@ return columnMap; } + /** + * Add all of the columns mentioned by the generation clauses of generated + * columns. The generated columns were added when we called + * addGeneratedColumns earlier on. + */ + private static void addGeneratedColumnPrecursors + ( + ColumnDescriptorList affectedGeneratedColumns, + FormatableBitSet columnMap + ) + throws StandardException + { + int generatedColumnCount = affectedGeneratedColumns.size(); + + for ( int gcIdx = 0; gcIdx < generatedColumnCount; gcIdx++ ) + { + ColumnDescriptor gc = affectedGeneratedColumns.elementAt( gcIdx ); + int[] mentionedColumns = gc.getDefaultInfo().getReferencedColumnIDs(); + int mentionedColumnCount = mentionedColumns.length; + + for ( int mcIdx = 0; mcIdx < mentionedColumnCount; mcIdx++ ) + { + columnMap.set( mentionedColumns[ mcIdx ] ); + + } // done looping through mentioned columns + + } // done looping through affected generated columns + + } + + /** + * Add generated columns to the update list as necessary. We add + * any column whose generation clause mentions columns already + * in the update list. We fill in a list of all generated columns affected + * by this update. We also fill in a list of all generated columns which we + * added to the update list. + */ + private void addGeneratedColumns + ( + TableDescriptor baseTable, + ResultSetNode updateSet, + ColumnDescriptorList affectedGeneratedColumns, + ColumnDescriptorList addedGeneratedColumns + ) + throws StandardException + { + ResultColumnList updateColumnList = updateSet.getResultColumns(); + ColumnDescriptorList generatedColumns = baseTable.getGeneratedColumns(); + int generatedColumnCount = generatedColumns.size(); + int columnCount = baseTable.getMaxColumnID(); + FormatableBitSet columnMap = new FormatableBitSet(columnCount + 1); + UUID tableID = baseTable.getObjectID(); + + int[] changedColumnIds = updateColumnList.sortMe(); + + for (int ix = 0; ix < changedColumnIds.length; ix++) + { + columnMap.set(changedColumnIds[ix]); + } + + for ( int gcIdx = 0; gcIdx < generatedColumnCount; gcIdx++ ) + { + ColumnDescriptor gc = generatedColumns.elementAt( gcIdx ); + DefaultInfo defaultInfo = gc.getDefaultInfo(); + int[] mentionedColumns = defaultInfo.getReferencedColumnIDs(); + int mentionedColumnCount = mentionedColumns.length; + + // figure out if this generated column is affected by the + // update + for ( int mcIdx = 0; mcIdx < mentionedColumnCount; mcIdx++ ) + { + int mentionedColumnID = mentionedColumns[ mcIdx ]; + + if ( columnMap.isSet( mentionedColumnID ) ) + { + // Yes, we are updating one of the columns mentioned in + // this generation clause. + affectedGeneratedColumns.add( tableID, gc ); + + // If the generated column isn't in the update list yet, + // add it. + if ( !columnMap.isSet( gc.getPosition() ) ) + { + addedGeneratedColumns.add( tableID, gc ); + + // we will fill in the real value later on in parseAndBindGenerationClauses(); + ValueNode dummy = (ValueNode) getNodeFactory().getNode + ( C_NodeTypes.UNTYPED_NULL_CONSTANT_NODE, getContextManager()); + ResultColumn newResultColumn = (ResultColumn) getNodeFactory().getNode + ( C_NodeTypes.RESULT_COLUMN, gc.getType(), dummy, getContextManager()); + newResultColumn.setColumnDescriptor( baseTable, gc ); + newResultColumn.setName( gc.getColumnName() ); + + updateColumnList.addResultColumn( newResultColumn ); + } + + break; + } + } // done looping through mentioned columns + + } // done looping through generated columns + } + + /* * Force correlated column references in the SET clause to have the * name of the base table. This dances around the problem alluded to @@ -1137,5 +1284,5 @@ super.normalizeSynonymColumns(rcl, tableNameNode); } - + } // end of UpdateNode Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java?rev=708900&r1=708899&r2=708900&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java Wed Oct 29 06:06:30 2008 @@ -139,7 +139,8 @@ @see ResultSetFactory#getUpdateResultSet @exception StandardException thrown on error */ - public ResultSet getUpdateResultSet(NoPutResultSet source, GeneratedMethod checkGM) + public ResultSet getUpdateResultSet(NoPutResultSet source, GeneratedMethod generationClauses, + GeneratedMethod checkGM) throws StandardException { Activation activation = source.getActivation(); @@ -155,7 +156,7 @@ SanityManager.ASSERT(getAuthorizer(activation) != null, "Authorizer is null"); } getAuthorizer(activation).authorize(activation, Authorizer.SQL_WRITE_OP); - return new UpdateResultSet(source, checkGM, activation); + return new UpdateResultSet(source, generationClauses, checkGM, activation); } /** @@ -177,6 +178,7 @@ @exception StandardException thrown on error */ public ResultSet getDeleteCascadeUpdateResultSet(NoPutResultSet source, + GeneratedMethod generationClauses, GeneratedMethod checkGM, int constantActionItem, int rsdItem) @@ -184,7 +186,7 @@ { Activation activation = source.getActivation(); getAuthorizer(activation).authorize(activation, Authorizer.SQL_WRITE_OP); - return new UpdateResultSet(source, checkGM, activation, + return new UpdateResultSet(source, generationClauses, checkGM, activation, constantActionItem, rsdItem); } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java?rev=708900&r1=708899&r2=708900&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/UpdateResultSet.java Wed Oct 29 06:06:30 2008 @@ -85,6 +85,7 @@ private long heapConglom; private FKInfo[] fkInfoArray; private FormatableBitSet baseRowReadList; + private GeneratedMethod generationClauses; private GeneratedMethod checkGM; private int resultWidth; private int numberOfBaseColumns; @@ -103,15 +104,17 @@ */ /** * @param source update rows come from source + * @param generationClauses Generated method for computed generation clauses * @param checkGM Generated method for enforcing check constraints * @exception StandardException thrown on error */ UpdateResultSet(NoPutResultSet source, + GeneratedMethod generationClauses, GeneratedMethod checkGM, Activation activation) throws StandardException { - this(source, checkGM , activation, activation.getConstantAction(),null); + this(source, generationClauses, checkGM , activation, activation.getConstantAction(),null); } /* @@ -120,6 +123,7 @@ */ /** * @param source update rows come from source + * @param generationClauses Generated method for computed generation clauses * @param checkGM Generated method for enforcing check constraints * @param activation Activation * @param constantActionItem id of the update constant action saved objec @@ -127,13 +131,14 @@ * @exception StandardException thrown on error */ UpdateResultSet(NoPutResultSet source, + GeneratedMethod generationClauses, GeneratedMethod checkGM, Activation activation, int constantActionItem, int rsdItem) throws StandardException { - this(source, checkGM , activation, + this(source, generationClauses, checkGM , activation, ((ConstantAction)activation.getPreparedStatement().getSavedObject(constantActionItem)), (ResultDescription) activation.getPreparedStatement().getSavedObject(rsdItem)); @@ -148,10 +153,12 @@ */ /** * @param source update rows come from source + * @param generationClauses Generated method for computed generation clauses * @param checkGM Generated method for enforcing check constraints * @exception StandardException thrown on error */ UpdateResultSet(NoPutResultSet source, + GeneratedMethod generationClauses, GeneratedMethod checkGM, Activation activation, ConstantAction passedInConstantAction, @@ -163,6 +170,7 @@ // Get the current transaction controller tc = activation.getTransactionController(); this.source = source; + this.generationClauses = generationClauses; this.checkGM = checkGM; constants = (UpdateConstantAction) constantAction; @@ -440,6 +448,7 @@ while ( row != null ) { + evaluateGenerationClauses( generationClauses, activation, source, row ); /* By convention, the last column in the result set for an * update contains a SQLRef containing the RowLocation of Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=708900&r1=708899&r2=708900&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java Wed Oct 29 06:06:30 2008 @@ -485,419 +485,419 @@ } - // /** - // *

- // * Verify basic update behavior for generated columns. - // *

- // */ - // public void test_006_basicUpdate() - // throws Exception - // { - // Connection conn = getConnection(); - // int counter; - // - // goodStatement - // ( - // conn, - // "create function f_minus\n" + - // "(\n" + - // " a int\n" + - // ")\n" + - // "returns int\n" + - // "language java\n" + - // "deterministic\n" + - // "parameter style java\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" - // ); - // goodStatement - // ( - // conn, - // "create function f_readMinusCounter()\n" + - // "returns int\n" + - // "language java\n" + - // "parameter style java\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.readMinusCounter'\n" - // ); - // goodStatement - // ( - // conn, - // "create table t_update_1( a int, b int generated always as( f_minus(a) ) check ( b < 0 ), c int )" - // ); - // goodStatement - // ( - // conn, - // "create unique index t_update_1_b on t_update_1( b )" - // ); - // - // counter = readMinusCounter( conn ); - // goodStatement - // ( - // conn, - // "insert into t_update_1( a, c ) values ( 1, 100 ), ( 2, 200 ), ( 3, 300 )" - // ); - // assertEquals( counter + 3, readMinusCounter( conn ) ); - // - // counter = readMinusCounter( conn ); - // goodStatement - // ( - // conn, - // "update t_update_1\n" + - // "set a = a + 10 where a > 1\n" - // ); - // assertEquals( counter + 2, readMinusCounter( conn ) ); - // - // // you can use the DEFAULT keyword to set a generated column - // goodStatement - // ( - // conn, - // "update t_update_1\n" + - // "set a = a + 10, b = default where c = 300\n" - // ); - // - // // fails trying to override a generation clause - // expectCompilationError - // ( - // CANT_OVERRIDE_GENERATION_CLAUSE, - // "update t_update_1\n" + - // "set a = a + 10, b = -3 where c = 300\n" - // ); - // - // // fails on a violation of the check constraint on the generated column - // expectExecutionError - // ( - // conn, - // CONSTRAINT_VIOLATION, - // "update t_update_1\n" + - // "set a = -100\n" + - // "where a = 1\n" - // ); - // - // // fails because it violates the unique index on the generated column - // expectExecutionError - // ( - // conn, - // ILLEGAL_DUPLICATE, - // "update t_update_1\n" + - // "set a = 12\n" + - // "where a = 1\n" - // ); - // - // // - // // Verify that all of the expected rows are in the table having the - // // generated column. - // // - // assertResults - // ( - // conn, - // "select * from t_update_1 order by c", - // new String[][] - // { - // { "1" , "-1" , "100" }, - // { "12" , "-12" , "200" }, - // { "23" , "-23" , "300" }, - // }, - // false - // ); - // } - - // /** - // *

- // * Verify basic trigger interaction with generated columns - // *

- // */ - // public void test_007_basicTriggers() - // throws Exception - // { - // Connection conn = getConnection(); - // - // // - // // Setup schema for test - // // - // goodStatement - // ( - // conn, - // "create function triggerReports()\n" + - // "returns TABLE\n" + - // " (\n" + - // " contents varchar( 100 )\n" + - // " )\n" + - // "language java\n" + - // "parameter style DERBY_JDBC_RESULT_SET\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.triggerReport'\n" - // ); - // goodStatement - // ( - // conn, - // "create procedure clearTriggerReports\n" + - // "()\n" + - // "language java\n" + - // "parameter style java\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.clearTriggerReports'\n" - // ); - // goodStatement - // ( - // conn, - // "create procedure report_proc\n" + - // "( tag varchar( 40 ), a int, b int, c int )\n" + - // "language java\n" + - // "parameter style java\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n" - // ); - // goodStatement - // ( - // conn, - // "create procedure wide_report_proc\n" + - // "( tag varchar( 40 ), old_a int, old_b int, old_c int, new_a int, new_b int, new_c int )\n" + - // "language java\n" + - // "parameter style java\n" + - // "no sql\n" + - // "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n" - // ); - // goodStatement - // ( - // conn, - // "create table t1_trig( a int, b int generated always as ( f_minus(a) ), c int )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_insert_row_trigger\n" + - // "no cascade before insert on t1_trig\n" + - // "referencing new as ar\n" + - // "for each row\n" + - // "call report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_insert_row_trigger\n" + - // "after insert on t1_trig\n" + - // "referencing new as ar\n" + - // "for each row\n" + - // "call report_proc( 'after_insert_row_trigger', ar.a, ar.b, ar.c ) \n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_update_row_trigger\n" + - // "no cascade before update on t1_trig\n" + - // "referencing old as br new as ar\n" + - // "for each row\n" + - // "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a, ar.b, ar.c )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_update_row_trigger\n" + - // "after update on t1_trig\n" + - // "referencing old as br new as ar\n" + - // "for each row\n" + - // "call wide_report_proc( 'after_update_row_trigger', br.a, br.b, br.c, ar.a, ar.b, ar.c )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_delete_row_trigger\n" + - // "no cascade before delete on t1_trig\n" + - // "referencing old as br\n" + - // "for each row\n" + - // "call report_proc( 'before_delete_row_trigger', br.a, br.b, br.c )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_delete_row_trigger\n" + - // "after delete on t1_trig\n" + - // "referencing old as br\n" + - // "for each row\n" + - // "call report_proc( 'after_delete_row_trigger', br.a, br.b, br.c )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_insert_statement_trigger\n" + - // "no cascade before insert on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'before_insert_statement_trigger', -1, -1, -1 )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_insert_statement_trigger\n" + - // "after insert on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'after_insert_statement_trigger', -1, -1, -1 )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_update_statement_trigger\n" + - // "no cascade before update on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'before_update_statement_trigger', -1, -1, -1 )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_before_delete_statement_trigger\n" + - // "no cascade before delete on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'before_delete_statement_trigger', -1, -1, -1 )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_update_statement_trigger\n" + - // "after update on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'after_update_statement_trigger', -1, -1, -1 )\n" - // ); - // goodStatement - // ( - // conn, - // "create trigger t1_trig_after_delete_statement_trigger\n" + - // "after delete on t1_trig\n" + - // "for each statement\n" + - // "call report_proc( 'after_delete_statement_trigger', -1, -1, -1 )\n" - // ); - // - // // - // // Now run the tests. - // // - // assertTriggerStatus - // ( - // conn, - // "insert into t1_trig( a ) values ( 1 ), ( 2 ), ( 3 )", - // new String[][] - // { - // { "before_insert_row_trigger: [ 1, -1, null ]" }, - // { "before_insert_row_trigger: [ 2, -2, null ]" }, - // { "before_insert_row_trigger: [ 3, -3, null ]" }, - // { "before_insert_statement_trigger: [ -1, -1, -1 ]" }, - // { "after_insert_row_trigger: [ 1, -1, null ]" }, - // { "after_insert_row_trigger: [ 2, -2, null ]" }, - // { "after_insert_row_trigger: [ 3, -3, null ]" }, - // { "after_insert_statement_trigger: [ -1, -1, -1 ]" }, - // } - // ); - // assertTriggerStatus - // ( - // conn, - // "update t1_trig set a = a + 10", - // new String[][] - // { - // { "before_update_row_trigger: [ 1, -1, null, 11, -11, null ]" }, - // { "before_update_row_trigger: [ 2, -2, null, 12, -12, null ]" }, - // { "before_update_row_trigger: [ 3, -3, null, 13, -13, null ]" }, - // { "before_update_statement_trigger: [ -1, -1, -1 ]" }, - // { "after_update_row_trigger: [ 1, -1, null, 11, -11, null ]" }, - // { "after_update_row_trigger: [ 2, -2, null, 12, -12, null ]" }, - // { "after_update_row_trigger: [ 3, -3, null, 13, -13, null ]" }, - // { "after_update_statement_trigger: [ -1, -1, -1 ]" }, - // } - // ); - // assertTriggerStatus - // ( - // conn, - // "delete from t1_trig where a > 11", - // new String[][] - // { - // { "before_delete_row_trigger: [ 12, -12, null ]" }, - // { "before_delete_row_trigger: [ 13, -13, null ]" }, - // { "before_delete_statement_trigger: [ -1, -1, -1 ]" }, - // { "after_delete_row_trigger: [ 12, -12, null ]" }, - // { "after_delete_row_trigger: [ 13, -13, null ]" }, - // { "after_delete_statement_trigger: [ -1, -1, -1 ]" }, - // } - // ); - // - // } + /** + *

+ * Verify basic update behavior for generated columns. + *

+ */ + public void test_006_basicUpdate() + throws Exception + { + Connection conn = getConnection(); + int counter; + + goodStatement + ( + conn, + "create function f_minus\n" + + "(\n" + + " a int\n" + + ")\n" + + "returns int\n" + + "language java\n" + + "deterministic\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.minus'\n" + ); + goodStatement + ( + conn, + "create function f_readMinusCounter()\n" + + "returns int\n" + + "language java\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.readMinusCounter'\n" + ); + goodStatement + ( + conn, + "create table t_update_1( a int, b int generated always as( f_minus(a) ) check ( b < 0 ), c int )" + ); + goodStatement + ( + conn, + "create unique index t_update_1_b on t_update_1( b )" + ); + + counter = readMinusCounter( conn ); + goodStatement + ( + conn, + "insert into t_update_1( a, c ) values ( 1, 100 ), ( 2, 200 ), ( 3, 300 )" + ); + assertEquals( counter + 3, readMinusCounter( conn ) ); + + counter = readMinusCounter( conn ); + goodStatement + ( + conn, + "update t_update_1\n" + + "set a = a + 10 where a > 1\n" + ); + assertEquals( counter + 2, readMinusCounter( conn ) ); + + // you can use the DEFAULT keyword to set a generated column + goodStatement + ( + conn, + "update t_update_1\n" + + "set a = a + 10, b = default where c = 300\n" + ); + + // fails trying to override a generation clause + expectCompilationError + ( + CANT_OVERRIDE_GENERATION_CLAUSE, + "update t_update_1\n" + + "set a = a + 10, b = -3 where c = 300\n" + ); + + // fails on a violation of the check constraint on the generated column + expectExecutionError + ( + conn, + CONSTRAINT_VIOLATION, + "update t_update_1\n" + + "set a = -100\n" + + "where a = 1\n" + ); + + // fails because it violates the unique index on the generated column + expectExecutionError + ( + conn, + ILLEGAL_DUPLICATE, + "update t_update_1\n" + + "set a = 12\n" + + "where a = 1\n" + ); + + // + // Verify that all of the expected rows are in the table having the + // generated column. + // + assertResults + ( + conn, + "select * from t_update_1 order by c", + new String[][] + { + { "1" , "-1" , "100" }, + { "12" , "-12" , "200" }, + { "23" , "-23" , "300" }, + }, + false + ); + } + + /** + *

+ * Verify basic trigger interaction with generated columns + *

+ */ + public void test_007_basicTriggers() + throws Exception + { + Connection conn = getConnection(); + + // + // Setup schema for test + // + goodStatement + ( + conn, + "create function triggerReports()\n" + + "returns TABLE\n" + + " (\n" + + " contents varchar( 100 )\n" + + " )\n" + + "language java\n" + + "parameter style DERBY_JDBC_RESULT_SET\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.triggerReport'\n" + ); + goodStatement + ( + conn, + "create procedure clearTriggerReports\n" + + "()\n" + + "language java\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.clearTriggerReports'\n" + ); + goodStatement + ( + conn, + "create procedure report_proc\n" + + "( tag varchar( 40 ), a int, b int, c int )\n" + + "language java\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n" + ); + goodStatement + ( + conn, + "create procedure wide_report_proc\n" + + "( tag varchar( 40 ), old_a int, old_b int, old_c int, new_a int, new_b int, new_c int )\n" + + "language java\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.GeneratedColumnsTest.showValues'\n" + ); + goodStatement + ( + conn, + "create table t1_trig( a int, b int generated always as ( f_minus(a) ), c int )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_insert_row_trigger\n" + + "no cascade before insert on t1_trig\n" + + "referencing new as ar\n" + + "for each row\n" + + "call report_proc( 'before_insert_row_trigger', ar.a, ar.b, ar.c )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_insert_row_trigger\n" + + "after insert on t1_trig\n" + + "referencing new as ar\n" + + "for each row\n" + + "call report_proc( 'after_insert_row_trigger', ar.a, ar.b, ar.c ) \n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_update_row_trigger\n" + + "no cascade before update on t1_trig\n" + + "referencing old as br new as ar\n" + + "for each row\n" + + "call wide_report_proc( 'before_update_row_trigger', br.a, br.b, br.c, ar.a, ar.b, ar.c )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_update_row_trigger\n" + + "after update on t1_trig\n" + + "referencing old as br new as ar\n" + + "for each row\n" + + "call wide_report_proc( 'after_update_row_trigger', br.a, br.b, br.c, ar.a, ar.b, ar.c )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_delete_row_trigger\n" + + "no cascade before delete on t1_trig\n" + + "referencing old as br\n" + + "for each row\n" + + "call report_proc( 'before_delete_row_trigger', br.a, br.b, br.c )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_delete_row_trigger\n" + + "after delete on t1_trig\n" + + "referencing old as br\n" + + "for each row\n" + + "call report_proc( 'after_delete_row_trigger', br.a, br.b, br.c )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_insert_statement_trigger\n" + + "no cascade before insert on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'before_insert_statement_trigger', -1, -1, -1 )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_insert_statement_trigger\n" + + "after insert on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'after_insert_statement_trigger', -1, -1, -1 )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_update_statement_trigger\n" + + "no cascade before update on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'before_update_statement_trigger', -1, -1, -1 )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_before_delete_statement_trigger\n" + + "no cascade before delete on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'before_delete_statement_trigger', -1, -1, -1 )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_update_statement_trigger\n" + + "after update on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'after_update_statement_trigger', -1, -1, -1 )\n" + ); + goodStatement + ( + conn, + "create trigger t1_trig_after_delete_statement_trigger\n" + + "after delete on t1_trig\n" + + "for each statement\n" + + "call report_proc( 'after_delete_statement_trigger', -1, -1, -1 )\n" + ); + + // + // Now run the tests. + // + assertTriggerStatus + ( + conn, + "insert into t1_trig( a ) values ( 1 ), ( 2 ), ( 3 )", + new String[][] + { + { "before_insert_row_trigger: [ 1, -1, null ]" }, + { "before_insert_row_trigger: [ 2, -2, null ]" }, + { "before_insert_row_trigger: [ 3, -3, null ]" }, + { "before_insert_statement_trigger: [ -1, -1, -1 ]" }, + { "after_insert_row_trigger: [ 1, -1, null ]" }, + { "after_insert_row_trigger: [ 2, -2, null ]" }, + { "after_insert_row_trigger: [ 3, -3, null ]" }, + { "after_insert_statement_trigger: [ -1, -1, -1 ]" }, + } + ); + assertTriggerStatus + ( + conn, + "update t1_trig set a = a + 10", + new String[][] + { + { "before_update_row_trigger: [ 1, -1, null, 11, -11, null ]" }, + { "before_update_row_trigger: [ 2, -2, null, 12, -12, null ]" }, + { "before_update_row_trigger: [ 3, -3, null, 13, -13, null ]" }, + { "before_update_statement_trigger: [ -1, -1, -1 ]" }, + { "after_update_row_trigger: [ 1, -1, null, 11, -11, null ]" }, + { "after_update_row_trigger: [ 2, -2, null, 12, -12, null ]" }, + { "after_update_row_trigger: [ 3, -3, null, 13, -13, null ]" }, + { "after_update_statement_trigger: [ -1, -1, -1 ]" }, + } + ); + assertTriggerStatus + ( + conn, + "delete from t1_trig where a > 11", + new String[][] + { + { "before_delete_row_trigger: [ 12, -12, null ]" }, + { "before_delete_row_trigger: [ 13, -13, null ]" }, + { "before_delete_statement_trigger: [ -1, -1, -1 ]" }, + { "after_delete_row_trigger: [ 12, -12, null ]" }, + { "after_delete_row_trigger: [ 13, -13, null ]" }, + { "after_delete_statement_trigger: [ -1, -1, -1 ]" }, + } + ); + + } - // /** - // *

- // * Verify basic interaction of foreign keys with generated columns - // *

- // */ - // public void test_008_basicForeignKeys() - // throws Exception - // { - // Connection conn = getConnection(); - // - // // - // // Setup schema for test - // // - // goodStatement - // ( - // conn, - // "create table t1_for( a int, b int generated always as ( f_minus(a) ) primary key, c int )" - // ); - // goodStatement - // ( - // conn, - // "create table t2_for( a int, b int references t1_for( b ), c int )" - // ); - // goodStatement - // ( - // conn, - // "create table t3_for( a int, b int primary key, c int )" - // ); - // goodStatement - // ( - // conn, - // "create table t4_for( a int, b int generated always as ( f_minus(a) ) references t3_for( b ), c int )" - // ); - // - // // - // // Initial data. - // // - // goodStatement - // ( - // conn, - // "insert into t1_for( a ) values ( 1 ), ( 2 ), ( 3 )" - // ); - // goodStatement - // ( - // conn, - // "insert into t2_for( b ) values ( -1 ), ( -3 )" - // ); - // goodStatement - // ( - // conn, - // "insert into t3_for( b ) values ( 1 ), ( 2 ), ( 3 )" - // ); - // goodStatement - // ( - // conn, - // "insert into t4_for( a ) values ( -1 ), ( -2 ), ( -3 )" - // ); - // - // // - // // Let's violate some foreign keys. - // // - // expectExecutionError - // ( - // conn, - // FOREIGN_KEY_VIOLATION, - // "update t1_for set a = a + 10 where a = 1" - // ); - // expectExecutionError - // ( - // conn, - // FOREIGN_KEY_VIOLATION, - // "update t4_for set a = a + 10 where a = -1" - // ); - // expectExecutionError - // ( - // conn, - // FOREIGN_KEY_VIOLATION, - // "insert into t4_for( a ) values ( -4 )" - // ); - // } + /** + *

+ * Verify basic interaction of foreign keys with generated columns + *

+ */ + public void test_008_basicForeignKeys() + throws Exception + { + Connection conn = getConnection(); + + // + // Setup schema for test + // + goodStatement + ( + conn, + "create table t1_for( a int, b int generated always as ( f_minus(a) ) primary key, c int )" + ); + goodStatement + ( + conn, + "create table t2_for( a int, b int references t1_for( b ), c int )" + ); + goodStatement + ( + conn, + "create table t3_for( a int, b int primary key, c int )" + ); + goodStatement + ( + conn, + "create table t4_for( a int, b int generated always as ( f_minus(a) ) references t3_for( b ), c int )" + ); + + // + // Initial data. + // + goodStatement + ( + conn, + "insert into t1_for( a ) values ( 1 ), ( 2 ), ( 3 )" + ); + goodStatement + ( + conn, + "insert into t2_for( b ) values ( -1 ), ( -3 )" + ); + goodStatement + ( + conn, + "insert into t3_for( b ) values ( 1 ), ( 2 ), ( 3 )" + ); + goodStatement + ( + conn, + "insert into t4_for( a ) values ( -1 ), ( -2 ), ( -3 )" + ); + + // + // Let's violate some foreign keys. + // + expectExecutionError + ( + conn, + FOREIGN_KEY_VIOLATION, + "update t1_for set a = a + 10 where a = 1" + ); + expectExecutionError + ( + conn, + FOREIGN_KEY_VIOLATION, + "update t4_for set a = a + 10 where a = -1" + ); + expectExecutionError + ( + conn, + FOREIGN_KEY_VIOLATION, + "insert into t4_for( a ) values ( -4 )" + ); + } /** *