Author: rhillegas
Date: Wed Nov 5 06:19:14 2008
New Revision: 711571
URL: http://svn.apache.org/viewvc?rev=711571&view=rev
Log:
DERBY-481: Add support for NOT NULL constraints on generated columns.
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ResultColumnDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericColumnDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.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/ResultColumnDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ResultColumnDescriptor.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ResultColumnDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/ResultColumnDescriptor.java
Wed Nov 5 06:19:14 2008
@@ -98,6 +98,11 @@
*/
boolean isAutoincrement();
+ /**
+ * Return true if this result column represents a generated column.
+ */
+ public boolean hasGenerationClause();
+
/*
* NOTE: These interfaces are intended to support JDBC. There are some
* JDBC methods on java.sql.ResultSetMetaData that have no equivalent
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericColumnDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericColumnDescriptor.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericColumnDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/GenericColumnDescriptor.java
Wed Nov 5 06:19:14 2008
@@ -67,6 +67,7 @@
private DataTypeDescriptor type;
private boolean isAutoincrement;
private boolean updatableByCursor;
+ private boolean hasGenerationClause;
/**
* Niladic constructor for Formatable
@@ -98,6 +99,7 @@
type = rcd.getType();
isAutoincrement = rcd.isAutoincrement();
updatableByCursor = rcd.updatableByCursor();
+ hasGenerationClause = rcd.hasGenerationClause();
}
/**
@@ -174,6 +176,8 @@
return updatableByCursor;
}
+ public boolean hasGenerationClause() { return hasGenerationClause; }
+
//////////////////////////////////////////////
//
// FORMATABLE
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
Wed Nov 5 06:19:14 2008
@@ -454,7 +454,7 @@
/* DB2 requires non-nullable columns to have a default in ALTER TABLE */
if (td != null && !getType().isNullable() && defaultNode == null)
{
- if (!isAutoincrement)
+ if (!isAutoincrement && !hasGenerationClause())
throw StandardException.newException(SQLState.LANG_DB2_NOT_NULL_COLUMN_INVALID_DEFAULT,
getColumnName());
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java
Wed Nov 5 06:19:14 2008
@@ -259,7 +259,7 @@
if ( generationClauses != null )
{
- evaluateGenerationClauses( generationClauses, activation, sourceResultSet, execRow
);
+ evaluateGenerationClauses( generationClauses, activation, sourceResultSet, execRow,
false );
}
if (checkGM != null && !hasBeforeStatementTrigger)
@@ -981,7 +981,7 @@
autoGeneratedKeysRowsHolder.insert(getCompactRow(row, columnIndexes));
// fill in columns that are computed from expressions on other columns
- evaluateGenerationClauses( generationClauses, activation, sourceResultSet, row
);
+ evaluateGenerationClauses( generationClauses, activation, sourceResultSet, row,
false );
/*
** If we're doing a deferred insert, insert into the temporary
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NoRowsResultSetImpl.java
Wed Nov 5 06:19:14 2008
@@ -31,6 +31,7 @@
import org.apache.derby.iapi.services.sanity.SanityManager;
import org.apache.derby.iapi.services.stream.HeaderPrintWriter;
import org.apache.derby.iapi.sql.Activation;
+import org.apache.derby.iapi.sql.ResultColumnDescriptor;
import org.apache.derby.iapi.sql.ResultDescription;
import org.apache.derby.iapi.sql.ResultSet;
import org.apache.derby.iapi.sql.Row;
@@ -42,6 +43,7 @@
import org.apache.derby.iapi.sql.execute.ExecRow;
import org.apache.derby.iapi.sql.execute.NoPutResultSet;
import org.apache.derby.iapi.sql.execute.ResultSetStatisticsFactory;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.types.DataValueDescriptor;
/**
@@ -72,6 +74,10 @@
protected long beginExecutionTime;
protected long endExecutionTime;
+ private int firstColumn = -1;
+ private int[] generatedColumnPositions; // 1-based positions
+ private DataValueDescriptor[] normalizedGeneratedValues; // one for each slot in generatedColumnPositions
+
NoRowsResultSetImpl(Activation activation)
throws StandardException
{
@@ -590,7 +596,8 @@
GeneratedMethod generationClauses,
Activation activation,
NoPutResultSet source,
- ExecRow newRow
+ ExecRow newRow,
+ boolean isUpdate
)
throws StandardException
{
@@ -607,6 +614,32 @@
try {
source.setCurrentRow( newRow );
generationClauses.invoke(activation);
+
+ //
+ // Now apply NOT NULL checks and other coercions. For non-generated columns,
these
+ // are performed in the driving ResultSet.
+ //
+ if ( firstColumn < 0 ) { firstColumn = NormalizeResultSet.computeStartColumn(
isUpdate, activation.getResultDescription() ); }
+ if ( generatedColumnPositions == null ) { setupGeneratedColumns( activation,
(ValueRow) newRow ); }
+
+ ResultDescription resultDescription = activation.getResultDescription();
+ int count = generatedColumnPositions.length;
+
+ for ( int i = 0; i < count; i++ )
+ {
+ int position = generatedColumnPositions[ i ];
+
+ DataValueDescriptor normalizedColumn = NormalizeResultSet.normalizeColumn
+ (
+ resultDescription.getColumnDescriptor( position ).getType(),
+ newRow,
+ position,
+ normalizedGeneratedValues[ i ],
+ resultDescription
+ );
+
+ newRow.setColumn( position, normalizedColumn );
+ }
}
finally
{
@@ -623,6 +656,48 @@
}
/**
+ * Construct support for normalizing generated columns.
+ */
+ private void setupGeneratedColumns( Activation activation, ValueRow newRow )
+ throws StandardException
+ {
+ ResultDescription resultDescription = activation.getResultDescription();
+ int columnCount = resultDescription.getColumnCount();
+ ExecRow emptyRow = newRow.getNewNullRow();
+ int generatedColumnCount = 0;
+
+ // first count the number of generated columns
+ for ( int i = 1; i <= columnCount; i++ )
+ {
+ if ( i < firstColumn ) { continue; }
+
+ ResultColumnDescriptor rcd = resultDescription.getColumnDescriptor( i );
+
+ if ( rcd.hasGenerationClause() ) { generatedColumnCount++; }
+ }
+
+ // now allocate and populate support structures
+ generatedColumnPositions = new int[ generatedColumnCount ];
+ normalizedGeneratedValues = new DataValueDescriptor[ generatedColumnCount ];
+
+ int idx = 0;
+ for ( int i = 1; i <= columnCount; i++ )
+ {
+ if ( i < firstColumn ) { continue; }
+
+ ResultColumnDescriptor rcd = resultDescription.getColumnDescriptor( i );
+
+ if ( rcd.hasGenerationClause() )
+ {
+ generatedColumnPositions[ idx ] = i;
+ normalizedGeneratedValues[ idx ] = emptyRow.getColumn( i );
+
+ idx++;
+ }
+ }
+ }
+
+ /**
* Run check constraints against the current row. Raise an error if
* a check constraint is violated.
*
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.java?rev=711571&r1=711570&r2=711571&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/NormalizeResultSet.java
Wed Nov 5 06:19:14 2008
@@ -104,16 +104,7 @@
numCols = resultDescription.getColumnCount();
- /*
- An update row, for an update statement which sets n columns; i.e
- UPDATE tab set x,y,z=.... where ...;
- has,
- before values of x,y,z after values of x,y,z and rowlocation.
- need only normalize after values of x,y,z.
- i.e insead of starting at index = 1, I need to start at index = 4.
- also I needn't normalize the last value in the row.
- */
- startCol = (forUpdate) ? ((numCols - 1)/ 2) + 1 : 1;
+ startCol = computeStartColumn( forUpdate, resultDescription );
normalizedRow = activation.getExecutionFactory().getValueRow(numCols);
recordConstructorTime();
}
@@ -278,6 +269,65 @@
return currentRow;
}
+ /**
+ * <p>
+ * Compute the start column for an update/insert.
+ * </p>
+ */
+ public static int computeStartColumn( boolean isUpdate, ResultDescription desc )
+ {
+ int count = desc.getColumnCount();
+
+ /*
+ An update row, for an update statement which sets n columns; i.e
+ UPDATE tab set x,y,z=.... where ...;
+ has,
+ before values of x,y,z after values of x,y,z and rowlocation.
+ need only normalize after values of x,y,z.
+ i.e insead of starting at index = 1, I need to start at index = 4.
+ also I needn't normalize the last value in the row.
+ */
+ return (isUpdate) ? ((count - 1)/ 2) + 1 : 1;
+ }
+
+
+ /**
+ * Normalize a row. For now, this means calling constructors through
+ * the type services to normalize a type to itself. For example,
+ * if you're putting a char(30) value into a char(15) column, it
+ * calls a SQLChar constructor with the char(30) value, and the
+ * constructor truncates the value and makes sure that no non-blank
+ * characters are truncated.
+ *
+ * In the future, this mechanism will be extended to do type conversions,
+ * as well. I didn't implement type conversions yet because it looks
+ * like a lot of work, and we needed char and varchar right away.
+ *
+ * @exception StandardException thrown on failure
+ */
+ public static DataValueDescriptor normalizeColumn
+ (DataTypeDescriptor dtd, ExecRow sourceRow, int sourceColumnPosition, DataValueDescriptor
resultCol, ResultDescription desc )
+ throws StandardException
+ {
+ DataValueDescriptor sourceCol = sourceRow.getColumn( sourceColumnPosition );
+
+ try {
+ DataValueDescriptor returnValue = dtd.normalize( sourceCol, resultCol );
+
+ return returnValue;
+ } catch (StandardException se) {
+ // Catch illegal null insert and add column info
+ if (se.getMessageId().startsWith(SQLState.LANG_NULL_INTO_NON_NULL))
+ {
+ ResultColumnDescriptor columnDescriptor = desc.getColumnDescriptor( sourceColumnPosition
);
+ throw StandardException.newException
+ (SQLState.LANG_NULL_INTO_NON_NULL, columnDescriptor.getName());
+ }
+ //just rethrow if not LANG_NULL_INTO_NON_NULL
+ throw se;
+ }
+ }
+
//
// class implementation
//
@@ -303,53 +353,55 @@
{
int whichCol;
- if (desiredTypes == null)
- {
- desiredTypes = new DataTypeDescriptor[numCols];
- for (whichCol = 1; whichCol <= numCols; whichCol++)
- {
- DataTypeDescriptor dtd = resultDescription.getColumnDescriptor(whichCol).getType();
+ if (desiredTypes == null) { desiredTypes = fetchResultTypes( resultDescription ); }
- desiredTypes[whichCol - 1] = dtd;
- }
+ int count = resultDescription.getColumnCount();
- }
-
- for (whichCol = 1; whichCol <= numCols; whichCol++)
+ for (int i = 1; i <= count; i++)
{
- DataValueDescriptor sourceCol = sourceRow.getColumn(whichCol);
+ DataValueDescriptor sourceCol = sourceRow.getColumn( i );
if (sourceCol != null)
{
DataValueDescriptor normalizedCol;
// skip the before values in case of update
- if (whichCol < startCol)
- normalizedCol = sourceCol;
+ if (i < startCol)
+ { normalizedCol = sourceCol; }
else
- try {
- normalizedCol =
- desiredTypes[whichCol - 1].normalize(sourceCol,
- normalizedRow.getColumn(whichCol));
- } catch (StandardException se) {
- // Catch illegal null insert and add column info
- if (se.getMessageId().startsWith(SQLState.LANG_NULL_INTO_NON_NULL))
- {
- ResultColumnDescriptor columnDescriptor =
- resultDescription.getColumnDescriptor(whichCol);
- throw
- StandardException.newException(SQLState.LANG_NULL_INTO_NON_NULL,
- columnDescriptor.getName());
- }
- //just rethrow if not LANG_NULL_INTO_NON_NULL
- throw se;
- }
+ {
+ normalizedCol = normalizeColumn
+ ( desiredTypes[i - 1], sourceRow, i, normalizedRow.getColumn(i),
resultDescription );
+ }
- normalizedRow.setColumn(whichCol, normalizedCol);
+ normalizedRow.setColumn(i, normalizedCol);
}
}
return normalizedRow;
}
+ /**
+ * <p>
+ * Fetch the result datatypes out of the activation.
+ * </p>
+ */
+ private DataTypeDescriptor[] fetchResultTypes( ResultDescription desc )
+ throws StandardException
+ {
+ int count = desc.getColumnCount();
+
+ DataTypeDescriptor[] result = new DataTypeDescriptor[ count ];
+
+ for ( int i = 1; i <= count; i++)
+ {
+ ResultColumnDescriptor colDesc = desc.getColumnDescriptor( i );
+ DataTypeDescriptor dtd = colDesc.getType();
+
+ result[i - 1] = dtd;
+ }
+
+ return result;
+ }
+
/**
* @see NoPutResultSet#updateRow
*/
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=711571&r1=711570&r2=711571&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 Nov 5 06:19:14 2008
@@ -448,7 +448,7 @@
while ( row != null )
{
- evaluateGenerationClauses( generationClauses, activation, source, row );
+ evaluateGenerationClauses( generationClauses, activation, source, row, true );
/* 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=711571&r1=711570&r2=711571&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 Nov 5 06:19:14 2008
@@ -63,6 +63,7 @@
private static final String CANT_REFERENCE_GENERATED_COLUMN = "42XA4";
private static final String ROUTINE_CANT_ISSUE_SQL = "42XA5";
private static final String BAD_FOREIGN_KEY_ACTION = "42XA6";
+ private static final String NOT_NULL_VIOLATION = "23502";
private static final String CONSTRAINT_VIOLATION = "23513";
private static final String FOREIGN_KEY_VIOLATION = "23503";
private static final String ILLEGAL_DUPLICATE = "23505";
@@ -1636,7 +1637,7 @@
conn,
"create table t_dt_varchar\n" +
"(\n" +
- " a char( 20 ),\n" +
+ " a varchar( 20 ),\n" +
" b char( 20 ) generated always as ( upper( a ) ),\n" +
" c varchar( 20 ) generated always as ( upper( a ) ),\n" +
" d long varchar generated always as ( upper( a ) ),\n" +
@@ -2109,6 +2110,116 @@
);
}
+ /**
+ * <p>
+ * Test NOT NULL constraints on generated columns.
+ * </p>
+ */
+ public void test_016_notNull()
+ throws Exception
+ {
+ Connection conn = getConnection();
+
+ //
+ // Schema
+ //
+ goodStatement
+ (
+ conn,
+ "create table t1_nn( a int, b int generated always as (-a) not null, c int )"
+ );
+ goodStatement
+ (
+ conn,
+ "create table t2_nn( a int, c int )"
+ );
+
+ //
+ // Populate first table
+ //
+ goodStatement
+ (
+ conn,
+ "insert into t1_nn( a ) values ( 1 )"
+ );
+ expectExecutionError
+ (
+ conn,
+ NOT_NULL_VIOLATION,
+ "insert into t1_nn( c ) values ( 1 )"
+ );
+ goodStatement
+ (
+ conn,
+ "update t1_nn set a = a + 1"
+ );
+ expectExecutionError
+ (
+ conn,
+ NOT_NULL_VIOLATION,
+ "update t1_nn set a = null"
+ );
+ assertResults
+ (
+ conn,
+ "select * from t1_nn order by a",
+ new String[][]
+ {
+ { "2", "-2", null, },
+ },
+ true
+ );
+
+ //
+ // Populate and alter second table
+ //
+ goodStatement
+ (
+ conn,
+ "insert into t2_nn values ( 1, 1 )"
+ );
+ goodStatement
+ (
+ conn,
+ "alter table t2_nn\n" +
+ " add column b int generated always as (-a) not null\n"
+ );
+ goodStatement
+ (
+ conn,
+ "insert into t2_nn( a ) values ( 2 )"
+ );
+ expectExecutionError
+ (
+ conn,
+ NOT_NULL_VIOLATION,
+ "insert into t2_nn( c ) values ( 10 )"
+ );
+ goodStatement
+ (
+ conn,
+ "update t2_nn set a = a + 1"
+ );
+ expectExecutionError
+ (
+ conn,
+ NOT_NULL_VIOLATION,
+ "update t2_nn set a = null"
+ );
+ assertResults
+ (
+ conn,
+ "select * from t2_nn order by a",
+ new String[][]
+ {
+ { "2", "1", "-2", },
+ { "3", null, "-3", },
+ },
+ true
+ );
+ }
+
+
///////////////////////////////////////////////////////////////////////////////////
//
// MINIONS
@@ -2155,6 +2266,7 @@
private void expectExecutionError( Connection conn, String sqlState, String query
)
throws Exception
{
+ println( "\nExpecting " + sqlState + " when executing:\n\t" );
PreparedStatement ps = chattyPrepare( conn, query );
assertStatementError( sqlState, ps );
|