You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by rh...@apache.org on 2008/10/30 13:45:19 UTC
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/
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