db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
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 GMT
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 @@
              
     }
         
+    /**
+     * <p>
+     * Various tests involving the DEFAULT literal in UPDATE statements.
+     * </p>
+     */
+    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
+             );
+
+    }
+
+    /**
+     * <p>
+     * Basic tests for altering a table and adding a generated column.
+     * </p>
+     */
+    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



Mime
View raw message