db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1589379 - in /db/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ e...
Date Wed, 23 Apr 2014 12:07:03 GMT
Author: rhillegas
Date: Wed Apr 23 12:07:02 2014
New Revision: 1589379

URL: http://svn.apache.org/r1589379
Log:
DERBY-6542: Add catalog support for using sequence generators to implement identity columns; commit derby-6542-01-ab-catalog.diff.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java   (with props)
Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
    db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/SystemProcedures.java Wed Apr 23 12:07:02 2014
@@ -2362,4 +2362,20 @@ public class SystemProcedures  {
         } catch (StandardException se) { throw PublicAPI.wrapStandardException(se); }
     }
 
+    /**
+     * Peek at the current value of an identity generator without advancing it.
+     *
+     * @param schemaName    The name of the schema holding the table.
+     * @param tableName    The name of the table in that schema.
+     *
+     * @exception SQLException if a database error occurs
+     **/
+    public static Long SYSCS_PEEK_AT_IDENTITY( String schemaName, String tableName )
+        throws SQLException
+    {
+        try {
+            return ConnectionUtil.getCurrentLCC().getDataDictionary().peekAtIdentity( schemaName, tableName );
+        } catch (StandardException se) { throw PublicAPI.wrapStandardException(se); }
+    }
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Wed Apr 23 12:07:02 2014
@@ -1849,6 +1849,14 @@ public interface DataDictionary
 
     /**
      * <p>
+     * Peek at the next value which will be returned by an identity generator.
+     * </p>
+     */
+    public Long peekAtIdentity( String schemaName, String tableName )
+        throws StandardException;
+
+    /**
+     * <p>
      * Peek at the next value which will be returned by a sequence generator.
      * </p>
      */

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/TableDescriptor.java Wed Apr 23 12:07:02 2014
@@ -1521,5 +1521,11 @@ public class TableDescriptor extends Uni
 		}
 	}
     
+    /** Make the name of an identity sequence generator from a table ID */
+    public  static  String  makeSequenceName( UUID tableID )
+    {
+        return tableID.toANSIidentifier();
+    }
+
 }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Wed Apr 23 12:07:02 2014
@@ -91,7 +91,10 @@ public final class DataTypeDescriptor im
      */
     public static final DataTypeDescriptor SMALLINT_NOT_NULL =
         SMALLINT.getNullabilityType(false);
-     
+
+    public  static  final   int MIN_VALUE_IDX = 0;
+    public  static  final   int MAX_VALUE_IDX = MIN_VALUE_IDX + 1;
+    public  static  final   int MAX_MIN_ARRAY_SIZE = MAX_VALUE_IDX + 1;
 
 	/*
 	** Static creators
@@ -1889,5 +1892,38 @@ public final class DataTypeDescriptor im
         }
         return name;    
     }
+
+    /**
+     * Get the maximum and minimum value for a fixed numeric type.
+     * Throws an unimplemented feature exception for a non-numeric type.
+     */
+    public  long[]  getNumericBounds()
+        throws StandardException
+    {
+        long[]  retval = new long[ MAX_MIN_ARRAY_SIZE ];
+
+        if ( getTypeId().equals( TypeId.SMALLINT_ID ) )
+        {
+            retval[ MIN_VALUE_IDX ] = Long.valueOf( Short.MIN_VALUE );
+            retval[ MAX_VALUE_IDX ] = Long.valueOf( Short.MAX_VALUE );
+        }
+        else if ( getTypeId().equals( TypeId.INTEGER_ID ) )
+        {
+            retval[ MIN_VALUE_IDX ] = Long.valueOf( Integer.MIN_VALUE );
+            retval[ MAX_VALUE_IDX ] = Long.valueOf( Integer.MAX_VALUE );
+        } else if ( getTypeId().equals( TypeId.BIGINT_ID ) )
+        {
+            // Could only be BIGINT
+            retval[ MIN_VALUE_IDX ] = Long.MIN_VALUE;
+            retval[ MAX_VALUE_IDX ] = Long.MAX_VALUE;
+        }
+        else
+        {
+            throw StandardException.newException( SQLState.BTREE_UNIMPLEMENTED_FEATURE );
+        }
+
+        return retval;
+    }
+    
 }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/TypeId.java Wed Apr 23 12:07:02 2014
@@ -241,7 +241,7 @@ public final class TypeId
             StoredFormatIds.TINYINT_TYPE_ID,
             StoredFormatIds.TINYINT_TYPE_ID_IMPL);
 
-        private static final TypeId BIGINT_ID = create(
+        public static final TypeId BIGINT_ID = create(
             StoredFormatIds.BIGINT_TYPE_ID,
             StoredFormatIds.BIGINT_TYPE_ID_IMPL);
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DD_Version.java Wed Apr 23 12:07:02 2014
@@ -516,6 +516,13 @@ public	class DD_Version implements	Forma
               bootingDictionary.getNonCoreTIByNumber(
                 DataDictionary.SYSTRIGGERS_CATALOG_NUM).getCatalogRowFactory(),
                 new int[] { 18 }, tc);
+            
+            // On ugrade from versions before 10.11, create system procedures
+            // added in 10.11.
+            bootingDictionary.create_10_11_system_procedures( tc, newlyCreatedRoutines );
+
+            // Add a sequence generator for every identity column
+            bootingDictionary.createIdentitySequences( tc );
         }
 
         // Grant PUBLIC access to some system routines

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Wed Apr 23 12:07:02 2014
@@ -455,6 +455,7 @@ public final class	DataDictionaryImpl
 	private static final String[] sysUtilFunctionsWithPublicAccess = { 
 												"SYSCS_GET_RUNTIMESTATISTICS", 
 												"SYSCS_PEEK_AT_SEQUENCE",
+												"SYSCS_PEEK_AT_IDENTITY",
 												};
 	
 	/**
@@ -10286,6 +10287,30 @@ public final class	DataDictionaryImpl
         }
     }
     
+    public Long peekAtIdentity( String schemaName, String tableName )
+        throws StandardException
+    {
+        LanguageConnectionContext lcc = getLCC();
+        TransactionController tc = lcc.getTransactionExecute();
+        SchemaDescriptor    sd = getSchemaDescriptor( schemaName, tc, true );
+        TableDescriptor td = getTableDescriptor( tableName, sd, tc );
+
+        if ( td == null )
+        {
+            throw StandardException.newException
+                (
+                 SQLState.LANG_OBJECT_NOT_FOUND_DURING_EXECUTION, "TABLE",
+                 ( schemaName + "." + tableName)
+                 );
+        }
+
+        return peekAtSequence
+            (
+             SchemaDescriptor.STD_SYSTEM_SCHEMA_NAME,
+             TableDescriptor.makeSequenceName( td.getUUID() )
+             );
+    }
+        
     public Long peekAtSequence( String schemaName, String sequenceName )
         throws StandardException
     {
@@ -11342,6 +11367,8 @@ public final class	DataDictionaryImpl
         create_10_9_system_procedures( tc, newlyCreatedRoutines );
         // add 10.10 specific system procedures
         create_10_10_system_procedures( tc, newlyCreatedRoutines );
+        // add 10.11 specific system procedures
+        create_10_11_system_procedures( tc, newlyCreatedRoutines );
     }
 
     /**
@@ -13259,6 +13286,49 @@ public final class	DataDictionaryImpl
         
     }
 
+    /**
+     * <p>
+     * Create system procedures that are part of the SYSCS_UTIL schema, added in version 10.11.
+     * </p>
+     *
+     * @param tc an instance of the Transaction Controller.
+     * @param newlyCreatedRoutines set of routines we are creating (used to add permissions later on)
+     **/
+    void create_10_11_system_procedures( TransactionController   tc, HashSet<String> newlyCreatedRoutines )
+        throws StandardException
+    {
+        UUID  sysUtilUUID = getSystemUtilSchemaDescriptor().getUUID();
+
+        // BIGINT
+        // SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY( VARCHAR(128), VARCHAR(128) )
+
+        {
+            // procedure argument names
+            String[] arg_names = { "schemaName", "tableName" };
+
+            // procedure argument types
+            TypeDescriptor[] arg_types =
+                {
+                    CATALOG_TYPE_SYSTEM_IDENTIFIER,
+                    CATALOG_TYPE_SYSTEM_IDENTIFIER
+                };
+
+            createSystemProcedureOrFunction(
+                "SYSCS_PEEK_AT_IDENTITY",
+                sysUtilUUID,
+                arg_names,
+                arg_types,
+				0,
+				0,
+                RoutineAliasInfo.READS_SQL_DATA,
+                false,
+                false,
+                DataTypeDescriptor.getCatalogType( Types.BIGINT ),
+                newlyCreatedRoutines,
+                tc);
+        }
+    }
+
 
 	/*
 	** Priv block code to load net work server meta data queries.
@@ -14311,4 +14381,72 @@ public final class	DataDictionaryImpl
             int formatId, byte[] columnBitMap) {
         return new DDColumnDependableFinder(formatId, columnBitMap);
     }
+
+    /**
+     * Create sequence generators for all identity columns on upgrade to 10.11.
+     */
+    void    createIdentitySequences( TransactionController tc )
+        throws StandardException
+    {
+        Hashtable<UUID,TableDescriptor> tableMap = hashAllTableDescriptorsByTableId( tc );
+
+        for ( UUID tableID : tableMap.keySet() )
+        {
+            TableDescriptor td = getTableDescriptor( tableID );
+            ColumnDescriptorList    cdl = td.getColumnDescriptorList();
+
+            for ( ColumnDescriptor cd : cdl )
+            {
+                if ( cd.isAutoincrement() )
+                {
+                    createIdentitySequence( td, cd, tc );
+                }
+            }
+        }
+    }
+
+    /**
+     * Create a sequence generator for an identity column on upgrade to 10.11.
+     */
+    private void    createIdentitySequence
+        (
+         TableDescriptor td,
+         ColumnDescriptor cd,   // the identity column
+         TransactionController tc
+         )
+        throws StandardException
+    {
+        DataTypeDescriptor  dtd = cd.getType();
+        long[]      bounds = dtd.getNumericBounds();
+        long    currentValue = cd.getAutoincValue();
+        long    initialValue = cd.getAutoincStart();
+        long    minValue = bounds[ DataTypeDescriptor.MIN_VALUE_IDX ];
+        long    maxValue = bounds[ DataTypeDescriptor.MAX_VALUE_IDX ];
+        long    stepValue = cd.getAutoincInc();
+        SchemaDescriptor    sd = getSystemSchemaDescriptor();
+
+        SequenceDescriptor  seqDef = getDataDescriptorGenerator().newSequenceDescriptor
+            (
+             sd,
+             getUUIDFactory().createUUID(),
+             TableDescriptor.makeSequenceName( td.getUUID() ),
+             dtd,
+             new Long( currentValue ),
+             initialValue,
+             minValue,
+             maxValue,
+             stepValue,
+             false         // whether the sequence can wrap-around
+             );
+
+        addDescriptor
+            (
+             seqDef,
+             null,  // parent
+             DataDictionary.SYSSEQUENCES_CATALOG_NUM,
+             false, // duplicatesAllowed
+             tc
+             );
+    }
+
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Wed Apr 23 12:07:02 2014
@@ -92,7 +92,16 @@ class NextSequenceNode extends ValueNode
 
         if ( sequenceDescriptor == null )
         {
-                throw StandardException.newException(SQLState.LANG_OBJECT_NOT_FOUND, "SEQUENCE", sequenceName.getFullTableName());
+            throw StandardException.newException(SQLState.LANG_OBJECT_NOT_FOUND, "SEQUENCE", sequenceName.getFullTableName());
+        }
+
+        //
+        // System sequences can only be operated at runtime when inserting
+        // a new identity value. See DERBY-6542.
+        //
+        if ( sd.isSystemSchema() )
+        {
+            throw StandardException.newException( SQLState.LANG_SYSTEM_SEQUENCE );
         }
 
         // set the datatype of the value node

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=1589379&r1=1589378&r2=1589379&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 Wed Apr 23 12:07:02 2014
@@ -1448,6 +1448,16 @@ class AlterTableConstantAction extends D
                 lcc, columnDescriptor.getDefaultDescriptor(dd));
 		}
 
+        // If the column is an identity column (and the dictionary is at least version 10.11),
+        // then we need to drop the system-generated sequence backing it.
+        if (
+            columnDescriptor.isAutoincrement() &&
+            dd.checkVersion( DataDictionary.DD_VERSION_DERBY_10_11, null )
+            )
+        {
+            DropTableConstantAction.dropIdentitySequence( dd, td, activation );
+        }
+
 		//Now go through each trigger on this table and see if the column 
 		//being dropped is part of it's trigger columns or trigger action 
 		//columns which are used through REFERENCING clause
@@ -2192,8 +2202,27 @@ class AlterTableConstantAction extends D
 		{
 			dd.setAutoincrementValue(tc, td.getUUID(), columnInfo[ix].name,
 					 columnInfo[ix].autoincStart, false);
-		} 
+		}
 		// else we are simply changing the default value
+
+		if (
+            (columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT) ||
+            (columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART)
+            )
+        {
+            //
+            // If we're at level 10.11 or higher, we re-create the sequence generator
+            // for the auto-increment column. See derby-6542.
+            //
+            if ( dd.checkVersion( DataDictionary.DD_VERSION_DERBY_10_11, null ) )
+            {
+                DropTableConstantAction.dropIdentitySequence( dd, td, activation );
+
+                CreateSequenceConstantAction   csca = CreateTableConstantAction.makeCSCA
+                    ( columnInfo[ix], TableDescriptor.makeSequenceName( td.getUUID() ) );
+                csca.executeConstantAction( activation );
+            }
+        }
 	}
 	
     /**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java Wed Apr 23 12:07:02 2014
@@ -44,6 +44,9 @@ import org.apache.derby.iapi.sql.execute
 import org.apache.derby.iapi.sql.Activation;
 import org.apache.derby.iapi.sql.depend.DependencyManager;
 
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.TypeId;
+
 import org.apache.derby.iapi.error.StandardException;
 
 import org.apache.derby.shared.common.sanity.SanityManager;
@@ -272,30 +275,47 @@ class CreateTableConstantAction extends 
 			}
 
 			if (columnInfo[ix].autoincInc != 0)//dealing with autoinc column
-			columnDescriptor = new ColumnDescriptor(
-				                   columnInfo[ix].name,
-								   index++,
-								   columnInfo[ix].dataType,
-								   columnInfo[ix].defaultValue,
-								   columnInfo[ix].defaultInfo,
-								   td,
-								   defaultUUID,
-								   columnInfo[ix].autoincStart,
-								   columnInfo[ix].autoincInc,
-								   columnInfo[ix].autoinc_create_or_modify_Start_Increment
-							   );
-			else
-				columnDescriptor = new ColumnDescriptor(
-		                   columnInfo[ix].name,
-						   index++,
-						   columnInfo[ix].dataType,
-						   columnInfo[ix].defaultValue,
-						   columnInfo[ix].defaultInfo,
-						   td,
-						   defaultUUID,
-						   columnInfo[ix].autoincStart,
-						   columnInfo[ix].autoincInc
-					   );
+            {
+                columnDescriptor = new ColumnDescriptor
+                    (
+                     columnInfo[ix].name,
+                     index++,
+                     columnInfo[ix].dataType,
+                     columnInfo[ix].defaultValue,
+                     columnInfo[ix].defaultInfo,
+                     td,
+                     defaultUUID,
+                     columnInfo[ix].autoincStart,
+                     columnInfo[ix].autoincInc,
+                     columnInfo[ix].autoinc_create_or_modify_Start_Increment
+                     );
+
+                //
+                // If we're at level 10.11 or higher, we create a sequence generator
+                // for the auto-increment column. See derby-6542.
+                //
+                if ( dd.checkVersion( DataDictionary.DD_VERSION_DERBY_10_11, null ) )
+                {
+                    CreateSequenceConstantAction    csca = makeCSCA
+                        ( columnInfo[ ix ], TableDescriptor.makeSequenceName( toid ) );
+                    csca.executeConstantAction( activation );
+                }
+            }
+            else
+            {
+				columnDescriptor = new ColumnDescriptor
+                    (
+                     columnInfo[ix].name,
+                     index++,
+                     columnInfo[ix].dataType,
+                     columnInfo[ix].defaultValue,
+                     columnInfo[ix].defaultInfo,
+                     td,
+                     defaultUUID,
+                     columnInfo[ix].autoincStart,
+                     columnInfo[ix].autoincInc
+                     );
+            }
 
 			cdlArray[ix] = columnDescriptor;
 		}
@@ -388,5 +408,27 @@ class CreateTableConstantAction extends 
 
 	}
 
+    /** Create a sequence generator for an identity column */
+    public  static CreateSequenceConstantAction    makeCSCA
+        ( ColumnInfo info, String sequenceName )
+        throws StandardException
+    {
+        DataTypeDescriptor  dtd = info.dataType;
+        long[]      bounds = dtd.getNumericBounds();
+        long    minValue = bounds[ DataTypeDescriptor.MIN_VALUE_IDX ];
+        long    maxValue = bounds[ DataTypeDescriptor.MAX_VALUE_IDX ];
+
+        return new CreateSequenceConstantAction
+            (
+             SchemaDescriptor.STD_SYSTEM_SCHEMA_NAME,
+             sequenceName,
+             dtd,
+             info.autoincStart,
+             info.autoincInc,
+             maxValue,
+             minValue,
+             false
+             );
+    }
 
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/DropTableConstantAction.java Wed Apr 23 12:07:02 2014
@@ -152,7 +152,7 @@ class DropTableConstantAction extends DD
 				lcc.dropDeclaredGlobalTempTable(tableName);
 				return;
 			}
-    }
+        }
 
 		/* Lock the table before we access the data dictionary
 		 * to prevent deadlocks.
@@ -196,6 +196,19 @@ class DropTableConstantAction extends DD
 		
         for (ColumnDescriptor cd : cdl)
 		{
+            //
+            // If we are at level 10.11 or higher, then we need to drop the
+            // sequence generator which backs the identity column.
+            // See DERBY-6542.
+            //
+            if (
+                cd.isAutoincrement() &&
+                dd.checkVersion( DataDictionary.DD_VERSION_DERBY_10_11, null )
+                )
+            {
+                dropIdentitySequence( dd, td, activation );
+            }
+            
 			// If column has a default we drop the default and
 			// any dependencies
 			if (cd.getDefaultInfo() != null)
@@ -278,6 +291,19 @@ class DropTableConstantAction extends DD
 
 	}
 
+    /** Drop the sequence generator backing an identity column */
+    public  static  void    dropIdentitySequence
+        ( DataDictionary dataDictionary, TableDescriptor tableDescriptor, Activation activation )
+        throws StandardException
+    {
+        DropSequenceConstantAction  dsca = new DropSequenceConstantAction
+            (
+             dataDictionary.getSystemSchemaDescriptor(),
+             TableDescriptor.makeSequenceName( tableDescriptor.getUUID() )
+             );
+        dsca.executeConstantAction( activation );
+    }
+
 	private void dropAllConstraintDescriptors(TableDescriptor td, Activation activation)
 		throws StandardException
 	{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Wed Apr 23 12:07:02 2014
@@ -2323,6 +2323,11 @@ Guide.
             </msg>
 
 	        <msg>
+                <name>42XAR</name>
+                <text>The NEXT VALUE operator may not be used on a system-owned sequence generator.</text>
+            </msg>
+
+	        <msg>
                 <name>42XBA</name>
                 <text>The schema, table or column does not exist or the column is not a string type.</text>
             </msg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Wed Apr 23 12:07:02 2014
@@ -956,6 +956,7 @@ public interface SQLState {
     String LANG_NO_SUBQUERIES_IN_MATCHED_CLAUSE         = "42XAO";
     String LANG_NO_SYNONYMS_IN_MERGE                            = "42XAP";
     String LANG_NO_DCL_IN_MERGE                                         = "42XAQ";
+    String LANG_SYSTEM_SEQUENCE                                         = "42XAR";
     String LANG_INVALID_USER_AGGREGATE_DEFINITION2                     = "42Y00";
 	String LANG_INVALID_CHECK_CONSTRAINT                               = "42Y01";
     // String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE             = "42Y02";

Modified: db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original)
+++ db/derby/code/trunk/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Wed Apr 23 12:07:02 2014
@@ -758,6 +758,12 @@ public class EmptyDictionary implements 
 		// Auto-generated method stub
     }
     
+    public Long peekAtIdentity( String schemaName, String tableName )
+        throws StandardException
+    {
+		return null;
+    }
+    
     public Long peekAtSequence( String schemaName, String sequenceName )
         throws StandardException
     {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ij7.out Wed Apr 23 12:07:02 2014
@@ -167,6 +167,7 @@ SYSCS_UTIL    |SYSCS_GET_RUNTIMESTATISTI
 SYSCS_UTIL    |SYSCS_GET_USER_ACCESS       |org.apache.derby.catalog.SystemPro&
 SYSCS_UTIL    |SYSCS_GET_XPLAIN_MODE       |org.apache.derby.catalog.SystemPro&
 SYSCS_UTIL    |SYSCS_GET_XPLAIN_SCHEMA     |org.apache.derby.catalog.SystemPro&
+SYSCS_UTIL    |SYSCS_PEEK_AT_IDENTITY      |org.apache.derby.catalog.SystemPro&
 SYSCS_UTIL    |SYSCS_PEEK_AT_SEQUENCE      |org.apache.derby.catalog.SystemPro&
 SYSIBM        |BLOBCREATELOCATOR           |org.apache.derby.impl.jdbc.LOBStor&
 SYSIBM        |BLOBGETBYTES                |org.apache.derby.impl.jdbc.LOBStor&

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java Wed Apr 23 12:07:02 2014
@@ -167,6 +167,9 @@ public class TestDbMetaData extends Base
         { null, "SYSCS_UTIL", "SYSCS_GET_XPLAIN_SCHEMA",
           "org.apache.derby.catalog.SystemProcedures." +
           "SYSCS_GET_XPLAIN_SCHEMA", FUNCTION_NO_TABLE_VALUE, GENERIC_NAME },
+        { null, "SYSCS_UTIL", "SYSCS_PEEK_AT_IDENTITY",
+          "org.apache.derby.catalog.SystemProcedures." +
+          "SYSCS_PEEK_AT_IDENTITY", FUNCTION_NO_TABLE_VALUE, GENERIC_NAME },
         { null, "SYSCS_UTIL", "SYSCS_PEEK_AT_SEQUENCE",
           "org.apache.derby.catalog.SystemProcedures." +
           "SYSCS_PEEK_AT_SEQUENCE", FUNCTION_NO_TABLE_VALUE, GENERIC_NAME },

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GrantRevokeDDLTest.java Wed Apr 23 12:07:02 2014
@@ -7284,6 +7284,7 @@ public final class GrantRevokeDDLTest ex
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
+            {"PUBLIC", "TEST_DBO", "N"},
         };
         
         JDBC.assertFullResultSet(rs, expRS, true);
@@ -7332,6 +7333,7 @@ public final class GrantRevokeDDLTest ex
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
             {"PUBLIC", "TEST_DBO", "N"},
+            {"PUBLIC", "TEST_DBO", "N"},
             {"USER2", "USER1", "N"}
         };
         

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java?rev=1589379&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java Wed Apr 23 12:07:02 2014
@@ -0,0 +1,274 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.IdentitySequenceTest
+
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to you under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+     http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.DatabasePropertyTestSetup;
+import org.apache.derbyTesting.junit.Decorator;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+
+/**
+ * Test for identity columns backed by sequence generators.
+ */
+public class IdentitySequenceTest extends GeneratedColumnsHelper
+{
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // CONSTANTS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    private static  final   String  BAD_NEXT_VALUE = "42XAR";
+    private static  final   String  TABLE_DOESNT_HAVE_IDENTITY = "X0X81";
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // STATE
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // CONSTRUCTOR
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    /**
+     * Create a new instance.
+     */
+
+    public IdentitySequenceTest( String name )
+    {
+        super(name);
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // JUnit BEHAVIOR
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+
+    /**
+     * Construct top level suite in this JUnit test
+     */
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite();
+
+        Test    cleanTest = new CleanDatabaseTestSetup
+            (
+             TestConfiguration.embeddedSuite( IdentitySequenceTest.class )
+             );
+
+        suite.addTest( cleanTest );
+        
+        return suite;
+    }
+
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // TESTS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    /**
+     * <p>
+     * Test catalog changes.
+     * </p>
+     */
+    public  void    test_001_catalog()
+        throws Exception
+    {
+        Connection  conn = getConnection();
+
+        goodStatement
+            (
+             conn,
+             "create table t1_01\n" +
+             "(\n" +
+             "    a int generated always as identity ( start with 10, increment by 20 ),\n" +
+             "    b int\n" +
+             ")\n"
+             );
+        String  sequenceName = getIdentitySequenceName( conn, "t1_01" );
+
+        // sequence should be in SYS, its name should be based on the table id,
+        // and its start/stop/max/min/cycle values should be correct.
+        String  sequenceStats =
+            "select\n" +
+            "    c.schemaName, s.sequenceName, s.currentValue, s.startValue,\n" +
+            "    s.minimumValue, s.maximumValue, s.increment, s.cycleoption\n" +
+            "from sys.syssequences s, sys.sysschemas c\n" +
+            "where s.schemaID = c.schemaID\n";
+
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][]
+             {
+                 { "SYS", sequenceName, "10", "10", "-2147483648", "2147483647", "20", "N" },
+             },
+             false
+             );
+
+        assertResults
+            (
+             conn,
+             "values syscs_util.syscs_peek_at_identity( 'APP', 'T1_01' )",
+             new String[][]
+             {
+                 { "10" },
+             },
+             false
+             );
+
+        // should not be able to issue a NEXT VALUE on the sequence generator which backs the identity column
+        expectCompilationError
+            ( conn, BAD_NEXT_VALUE, "values ( next value for sys.\"" + sequenceName + "\" )" );
+
+        // alter the identity column and observe that the sequence generator changes
+        goodStatement( conn, "alter table t1_01 alter column a set increment by 15" );
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][]
+             {
+                 { "SYS", sequenceName, "10", "10", "-2147483648", "2147483647", "15", "N" },
+             },
+             false
+             );
+        goodStatement( conn, "alter table t1_01 alter column a restart with 500" );
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][]
+             {
+                 { "SYS", sequenceName, "500", "500", "-2147483648", "2147483647", "15", "N" },
+             },
+             false
+             );
+        
+        // system sequence should disappear when the table is dropped
+        goodStatement( conn, "drop table t1_01" );
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][] {},
+             false
+             );
+
+        // can't add an identity column to a table
+        goodStatement( conn, "create table t2_01( b int )" );
+        expectCompilationError
+            ( conn, CANT_ADD_IDENTITY,
+              "alter table t2_01 add column a int generated always as identity ( start with 10, increment by 20 )" );
+
+        // dropping an identity column should drop the sequence generator too
+        goodStatement
+            (
+             conn,
+             "create table t1_03\n" +
+             "(\n" +
+             "    a int generated always as identity ( start with 10, increment by 20 ),\n" +
+             "    b int\n" +
+             ")\n"
+             );
+        sequenceName = getIdentitySequenceName( conn, "t1_03" );
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][]
+             {
+                 { "SYS", sequenceName, "10", "10", "-2147483648", "2147483647", "20", "N" },
+             },
+             false
+             );
+        assertResults
+            (
+             conn,
+             "values syscs_util.syscs_peek_at_identity( 'APP', 'T1_03' )",
+             new String[][]
+             {
+                 { "10" },
+             },
+             false
+             );
+        goodStatement( conn, "alter table t1_03 drop column a" );
+        assertResults
+            (
+             conn,
+             sequenceStats,
+             new String[][] {},
+             false
+             );
+        expectExecutionError
+            ( conn, TABLE_DOESNT_HAVE_IDENTITY,
+             "values syscs_util.syscs_peek_at_identity( 'APP', 'T1_03' )"
+              );
+    }
+    
+    ///////////////////////////////////////////////////////////////////////////////////
+    //
+    // MINIONS
+    //
+    ///////////////////////////////////////////////////////////////////////////////////
+
+    private String  getIdentitySequenceName( Connection conn, String tableName )
+        throws Exception
+    {
+        PreparedStatement   ps = chattyPrepare
+            ( conn, "select tableID from sys.systables where tablename = ?" );
+        ps.setString( 1, tableName.toUpperCase() );
+        ResultSet   rs = ps.executeQuery();
+        rs.next();
+        String  uuidString = rs.getString( 1 );
+        rs.close();
+        ps.close();
+
+        return uuidToSequenceName( uuidString );
+    }
+
+    public  static  String  uuidToSequenceName( String uuidString )
+    {
+        return "U" + uuidString.replace( "-", "X" );
+    }
+
+    
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/IdentitySequenceTest.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/RolesTest.java Wed Apr 23 12:07:02 2014
@@ -599,9 +599,9 @@ public class RolesTest extends BaseJDBCT
 
         assertSysColPermsRowCount(0, 2, 2);
 
-        assertSysRoutinePermsRowCount(9, // 9 pre-existing grants to PUBLIC
-                                      10,
-                                      10);
+        assertSysRoutinePermsRowCount(10, // 10 pre-existing grants to PUBLIC
+                                      11,
+                                      11);
 
         /*
          * DROP ROLE
@@ -629,12 +629,12 @@ public class RolesTest extends BaseJDBCT
                                   // to admin is de facto to a user
                                   // named admin:
                                   2);
-        assertSysRoutinePermsRowCount(9, 9,
+        assertSysRoutinePermsRowCount(10, 10,
                                       //  nonDbo run: role admin
                                       // has been dropped, so this
                                       // run's grant to admin is de
                                       // facto to a user named admin:
-                                      10);
+                                      11);
 
         doStmt("drop role \"NONE\"",
                sqlAuthorizationRequired, null , roleDboOnly);
@@ -656,7 +656,7 @@ public class RolesTest extends BaseJDBCT
                                     1,
                                     0);
         assertSysColPermsRowCount(0,0,0);
-        assertSysRoutinePermsRowCount(9,9,9);
+        assertSysRoutinePermsRowCount(10,10,10);
 
         // roles foo and bar survive to nonDbo run and beyond:
         assertSysRolesRowCount(0, 5, 5);

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Wed Apr 23 12:07:02 2014
@@ -247,6 +247,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(NewOptimizerOverridesTest.suite());
         suite.addTest(XMLOptimizerTraceTest.suite());
         suite.addTest(MergeStatementTest.suite());
+        suite.addTest(IdentitySequenceTest.suite());
         suite.addTest(NestedCommitTest.suite());
         suite.addTest(ForeignKeysNonSpsTest.suite());
         suite.addTest(LOBDB2compatibilityTest.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java Wed Apr 23 12:07:02 2014
@@ -22,6 +22,7 @@ package org.apache.derbyTesting.function
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.SQLWarning;
 import java.sql.Statement;
@@ -451,6 +452,105 @@ public class Changes10_11 extends Upgrad
         }
     }
 
+    /** Test the addition of sequence generators to back identity columns */
+    public void testIdentitySequence() throws Exception
+    {
+        Properties  properties = TestConfiguration.getSystemProperties();
+        if ( getBooleanProperty( properties, TestConfiguration.KEY_OMIT_LUCENE ) )  { return; }
+
+        Version initialVersion = new Version( getOldMajor(), getOldMinor(), 0, 0 );
+        Version firstVersionHavingSequences = new Version( 10, 6, 0, 0 );
+        boolean hasSequences = initialVersion.compareTo( firstVersionHavingSequences ) >= 0;
+
+        Statement statement = createStatement();
+
+        String  peek = "values syscs_util.syscs_peek_at_identity( 'APP', 'IDSEQ1' )";
+        
+        switch ( getPhase() )
+        {
+            case PH_CREATE:
+                statement.executeUpdate
+                    (
+                     "create function uuidToSequenceName( uuid char( 36 ) ) returns varchar( 128 )\n" +
+                     "language java parameter style java no sql\n" +
+                     "external name 'org.apache.derbyTesting.functionTests.tests.lang.IdentitySequenceTest.uuidToSequenceName'\n"
+                     );
+                statement.executeUpdate
+                    ( "create table idseq1( a int generated always as identity ( start with 10, increment by 20 ), b int )" );
+                statement.executeUpdate( "insert into idseq1( b ) values ( 1 ), ( 20 )" );
+                if ( hasSequences ) { assertEquals( 0, countSequences( statement ) ); }
+                assertStatementError( UNRECOGNIZED_PROCEDURE, statement, peek );
+                break;
+            case PH_POST_SOFT_UPGRADE:
+                statement.executeUpdate( "create table idseq2( a int generated always as identity, b int )" );
+                if ( hasSequences ) { assertEquals( 0, countSequences( statement ) ); }
+                assertStatementError( UNRECOGNIZED_PROCEDURE, statement, peek );
+                break;
+            case PH_SOFT_UPGRADE:
+                statement.executeUpdate( "create table idseq3( a int generated always as identity, b int )" );
+                if ( hasSequences ) { assertEquals( 0, countSequences( statement ) ); }
+                assertStatementError( UNRECOGNIZED_PROCEDURE, statement, peek );
+                break;
+            case PH_HARD_UPGRADE:
+                statement.executeUpdate( "create table idseq4( a int generated always as identity, b int )" );
+                assertEquals
+                    (
+                     4,
+                     count
+                     (
+                      statement,
+                      "select count(*)\n" +
+                      "from sys.systables t, sys.syssequences s\n" +
+                      "where uuidToSequenceName( t.tableid ) = s.sequencename\n" +
+                      "and t.tablename like 'IDSEQ%'"
+                      )
+                     );
+                JDBC.assertFullResultSet
+                    (
+                     statement.executeQuery( peek ),
+                     new String[][]
+                     {
+                         { "50" },
+                     }
+                     );
+                JDBC.assertFullResultSet
+                    (
+                     statement.executeQuery
+                     (
+                      "select sch.schemaName,\n" +
+                      "s.currentvalue, s.startvalue, s.minimumvalue, s.maximumvalue, s.increment, s.cycleoption\n" +
+                      "from sys.sysschemas sch, sys.systables t, sys.syssequences s\n" +
+                      "where t.tablename = 'IDSEQ1'\n" +
+                      "and uuidToSequenceName( t.tableid ) = s.sequencename\n" +
+                      "and sch.schemaid = s.schemaid\n"
+                      ),
+                     new String[][]
+                     {
+                         { "SYS", "50", "10", "-2147483648", "2147483647", "20", "N" },
+                     }
+                     );
+                break;
+        }
+    }
+    private int countSequences( Statement statement )
+        throws Exception
+    {
+        return count( statement, "select count(*) from sys.syssequences" );
+    }
+    private int count( Statement statement, String query ) throws Exception
+    {
+        ResultSet   rs = statement.executeQuery( query );
+        rs.next();
+
+        try {
+            return rs.getInt( 1 );
+        }
+        finally
+        {
+            rs.close();
+        }
+    }
+
     /** Return the boolean value of a system property */
     private static  boolean getBooleanProperty( Properties properties, String key )
     {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_2.java Wed Apr 23 12:07:02 2014
@@ -336,6 +336,7 @@ public class Changes10_2 extends Upgrade
                     {"SYSCS_GET_RUNTIMESTATISTICS"},
                     {"SYSCS_INPLACE_COMPRESS_TABLE"},
                     {"SYSCS_MODIFY_PASSWORD"},
+                    {"SYSCS_PEEK_AT_IDENTITY"},
                     {"SYSCS_PEEK_AT_SEQUENCE"},
                     {"SYSCS_SET_RUNTIMESTATISTICS"},
                     {"SYSCS_SET_STATISTICS_TIMING"},

Modified: db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java?rev=1589379&r1=1589378&r2=1589379&view=diff
==============================================================================
--- db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java (original)
+++ db/derby/code/trunk/java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java Wed Apr 23 12:07:02 2014
@@ -61,10 +61,12 @@ public class DB_Sequence
 	public static void doSequences( Connection conn )
 		throws SQLException
     {
+        // exclude system-generated sequences. see DERBY-6542.
 		PreparedStatement ps = conn.prepareStatement
             (
              "SELECT SCHEMAID, SEQUENCENAME, SEQUENCEDATATYPE, STARTVALUE, MINIMUMVALUE, MAXIMUMVALUE, INCREMENT, CYCLEOPTION\n" +
-             "FROM SYS.SYSSEQUENCES"
+             "FROM SYS.SYSSEQUENCES\n" +
+             "WHERE CAST( SCHEMAID AS CHAR( 36) ) != '8000000d-00d0-fd77-3ed8-000a0a0b1900'"
              );
         ResultSet rs = ps.executeQuery();
 



Mime
View raw message