From derby-commits-return-10909-apmail-db-derby-commits-archive=db.apache.org@db.apache.org Thu Oct 30 12:45:42 2008 Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 5150 invoked from network); 30 Oct 2008 12:45:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 30 Oct 2008 12:45:41 -0000 Received: (qmail 28356 invoked by uid 500); 30 Oct 2008 12:45:47 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 28283 invoked by uid 500); 30 Oct 2008 12:45:46 -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 28273 invoked by uid 99); 30 Oct 2008 12:45:46 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Oct 2008 05:45:46 -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; Thu, 30 Oct 2008 12:44:40 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 6E90E23888F1; Thu, 30 Oct 2008 05:45:20 -0700 (PDT) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r709152 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/tests/lang/ Date: Thu, 30 Oct 2008 12:45:19 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20081030124520.6E90E23888F1@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Thu Oct 30 05:45:19 2008 New Revision: 709152 URL: http://svn.apache.org/viewvc?rev=709152&view=rev Log: DERBY-3922: Support for adding generated columns via ALTER TABLE. Modified: 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/AlterTableConstantAction.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/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=709152&r1=709151&r2=709152&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 Thu Oct 30 05:45:19 2008 @@ -81,6 +81,7 @@ import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; +import java.util.HashSet; import java.util.Properties; import java.util.Vector; @@ -1109,17 +1110,18 @@ throws StandardException { ResultColumnList updateColumnList = updateSet.getResultColumns(); + int count = updateColumnList.size(); ColumnDescriptorList generatedColumns = baseTable.getGeneratedColumns(); int generatedColumnCount = generatedColumns.size(); int columnCount = baseTable.getMaxColumnID(); - FormatableBitSet columnMap = new FormatableBitSet(columnCount + 1); + HashSet updatedColumns = new HashSet(); UUID tableID = baseTable.getObjectID(); - int[] changedColumnIds = updateColumnList.sortMe(); - - for (int ix = 0; ix < changedColumnIds.length; ix++) + for (int ix = 0; ix < count; ix++) { - columnMap.set(changedColumnIds[ix]); + String name = ((ResultColumn)updateColumnList.elementAt( ix )).getName(); + + updatedColumns.add( name ); } for ( int gcIdx = 0; gcIdx < generatedColumnCount; gcIdx++ ) @@ -1129,13 +1131,18 @@ int[] mentionedColumns = defaultInfo.getReferencedColumnIDs(); int mentionedColumnCount = mentionedColumns.length; + // handle the case of setting a generated column to the DEFAULT + // literal + if ( updatedColumns.contains( gc.getColumnName() ) ) { affectedGeneratedColumns.add( tableID, gc ); } + // figure out if this generated column is affected by the // update for ( int mcIdx = 0; mcIdx < mentionedColumnCount; mcIdx++ ) { - int mentionedColumnID = mentionedColumns[ mcIdx ]; + ColumnDescriptor mentionedColumn = baseTable.getColumnDescriptor( mentionedColumns[ mcIdx ] ); + String mentionedColumnName = mentionedColumn.getColumnName(); - if ( columnMap.isSet( mentionedColumnID ) ) + if ( updatedColumns.contains( mentionedColumnName ) ) { // Yes, we are updating one of the columns mentioned in // this generation clause. @@ -1143,7 +1150,7 @@ // If the generated column isn't in the update list yet, // add it. - if ( !columnMap.isSet( gc.getPosition() ) ) + if ( !updatedColumns.contains( gc.getColumnName() ) ) { addedGeneratedColumns.add( tableID, gc ); Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=709152&r1=709151&r2=709152&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java Thu Oct 30 05:45:19 2008 @@ -28,6 +28,7 @@ import java.util.List; import java.util.Properties; +import org.apache.derby.catalog.DefaultInfo; import org.apache.derby.catalog.DependableFinder; import org.apache.derby.catalog.IndexDescriptor; import org.apache.derby.catalog.UUID; @@ -1339,10 +1340,7 @@ // Update the new column to its default, if it has a non-null default if (columnDescriptor.hasNonNullDefault()) { - updateNewColumnToDefault(activation, - columnInfo[ix].name, - columnInfo[ix].defaultInfo.getDefaultText(), - lcc); + updateNewColumnToDefault(activation, columnDescriptor, lcc); } // Update SYSCOLPERMS table which tracks the permissions granted @@ -3068,8 +3066,7 @@ * instead we get a nested connection and * issue the appropriate update statement. * - * @param columnName column name - * @param defaultText default text + * @param columnDescriptor catalog descriptor for the column * @param lcc the language connection context * * @exception StandardException if update to default fails @@ -3077,12 +3074,18 @@ private void updateNewColumnToDefault ( Activation activation, - String columnName, - String defaultText, + ColumnDescriptor columnDescriptor, LanguageConnectionContext lcc ) throws StandardException { + DefaultInfo defaultInfo = columnDescriptor.getDefaultInfo(); + String columnName = columnDescriptor.getColumnName(); + String defaultText; + + if ( defaultInfo.isGeneratedColumn() ) { defaultText = "default"; } + else { defaultText = columnDescriptor.getDefaultInfo().getDefaultText(); } + /* Need to use delimited identifiers for all object names * to ensure correctness. */ 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=709152&r1=709151&r2=709152&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 Thu Oct 30 05:45:19 2008 @@ -961,6 +961,319 @@ } + /** + *

+ * Various tests involving the DEFAULT literal in UPDATE statements. + *

+ */ + public void test_010_updateDefaultLiteral() + throws Exception + { + Connection conn = getConnection(); + + // + // Schema + // + goodStatement + ( + conn, + "create table t_ud_1( a int, b int generated always as ( a*a ) , c int )" + ); + goodStatement + ( + conn, + "create table t_ud_2( a int, b int generated always as ( a*c ) , c int )" + ); + + // initial values + goodStatement + ( + conn, + "insert into t_ud_1( a ) values ( 1 ), ( 2 )" + ); + goodStatement + ( + conn, + "insert into t_ud_2( a ) values ( 1 ), ( 2 )" + ); + + // + // Tests of generated column depending on one other column. + // + expectCompilationError + ( + CANT_OVERRIDE_GENERATION_CLAUSE, + "update t_ud_1 set b = a*a" + ); + assertResults + ( + conn, + "select * from t_ud_1 order by a", + new String[][] + { + { "1" , "1" , null }, + { "2" , "4" , null }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_1 set c = -1, b = default" + ); + assertResults + ( + conn, + "select * from t_ud_1 order by a", + new String[][] + { + { "1" , "1" , "-1" }, + { "2" , "4" , "-1" }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_1 set a = 2*a, b = default" + ); + assertResults + ( + conn, + "select * from t_ud_1 order by a", + new String[][] + { + { "2" , "4" , "-1" }, + { "4" , "16" , "-1" }, + }, + false + ); + + + // + // Tests of generated column depending on two other columns. + // + expectCompilationError + ( + CANT_OVERRIDE_GENERATION_CLAUSE, + "update t_ud_2 set b = a*c" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "1" , null, null }, + { "2" , null, null }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_2 set b = default" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "1" , null, null }, + { "2" , null, null }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_2 set c = -5" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "1" , "-5" , "-5" }, + { "2" , "-10" , "-5" }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_2 set c = -3, b = default" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "1" , "-3" , "-3" }, + { "2" , "-6" , "-3" }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_2 set a = 2*a, b = default" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "2" , "-6" , "-3" }, + { "4" , "-12" , "-3" }, + }, + false + ); + + goodStatement + ( + conn, + "update t_ud_2 set a = a - 1, b = default, c = 4" + ); + assertResults + ( + conn, + "select * from t_ud_2 order by a", + new String[][] + { + { "1" , "4" , "4" }, + { "3" , "12" , "4" }, + }, + false + ); + + } + + /** + *

+ * Basic tests for altering a table and adding a generated column. + *

+ */ + public void test_011_basicAlter() + throws Exception + { + Connection conn = getConnection(); + + // + // Schema + // + goodStatement + ( + conn, + "create table t_alt_1( a int, c int )" + ); + goodStatement + ( + conn, + "create function f_alt_deterministic( a int )\n" + + "returns int\n" + + "language java\n" + + "deterministic\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'java.lang.Math.abs'\n" + ); + goodStatement + ( + conn, + "create function f_alt_non_deterministic( a int )\n" + + "returns int\n" + + "language java\n" + + "parameter style java\n" + + "no sql\n" + + "external name 'java.lang.Math.abs'\n" + ); + + // + // Initial values + // + goodStatement + ( + conn, + "insert into t_alt_1( a ) values ( 1 ), ( 2 )" + ); + + // + // Now alter the table and add a generated column. + // + goodStatement + ( + conn, + "alter table t_alt_1 add column b int generated always as ( -a )" + ); + assertResults + ( + conn, + "select * from t_alt_1 order by a", + new String[][] + { + { "1" , null, "-1" }, + { "2" , null, "-2" }, + }, + false + ); + + goodStatement + ( + conn, + "insert into t_alt_1( a ) values ( 3 ), ( 4 )" + ); + assertResults + ( + conn, + "select * from t_alt_1 order by a", + new String[][] + { + { "1" , null, "-1" }, + { "2" , null, "-2" }, + { "3" , null, "-3" }, + { "4" , null, "-4" }, + }, + false + ); + + expectCompilationError + ( + UNSTABLE_RESULTS, + "alter table t_alt_1 add column d int generated always as ( f_alt_non_deterministic( a ) )" + ); + + goodStatement + ( + conn, + "alter table t_alt_1 add column d int generated always as ( f_alt_deterministic( a ) )" + ); + assertResults + ( + conn, + "select * from t_alt_1 order by a", + new String[][] + { + { "1" , null, "-1", "1" }, + { "2" , null, "-2", "2" }, + { "3" , null, "-3", "3" }, + { "4" , null, "-4", "4" }, + }, + false + ); + + } + /////////////////////////////////////////////////////////////////////////////////// // // MINIONS