db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r711571 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/ engine/org/apache/derby/impl/sql/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesting/functionTests/te...
Date Wed, 05 Nov 2008 14:19:15 GMT
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 );



Mime
View raw message