Return-Path: X-Original-To: apmail-db-derby-commits-archive@www.apache.org Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D4D0D6227 for ; Thu, 30 Jun 2011 18:12:43 +0000 (UTC) Received: (qmail 12263 invoked by uid 500); 30 Jun 2011 18:12:43 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 12230 invoked by uid 500); 30 Jun 2011 18:12:43 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 12222 invoked by uid 99); 30 Jun 2011 18:12:43 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Jun 2011 18:12:43 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 30 Jun 2011 18:12:33 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id D6B97238885D; Thu, 30 Jun 2011 18:12:10 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1141645 [1/2] - in /db/derby/code/branches/10.8: java/engine/org/apache/derby/catalog/ java/engine/org/apache/derby/iapi/reference/ java/engine/org/apache/derby/iapi/sql/dictionary/ java/engine/org/apache/derby/impl/db/ java/engine/org/apa... Date: Thu, 30 Jun 2011 18:12:10 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20110630181210.D6B97238885D@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Thu Jun 30 18:12:09 2011 New Revision: 1141645 URL: http://svn.apache.org/viewvc?rev=1141645&view=rev Log: DERBY-4437: Port the work on identity performance/concurrency from the trunk to the 10.8 branch. Added: db/derby/code/branches/10.8/java/engine/org/apache/derby/catalog/SequencePreallocator.java - copied unchanged from r1138434, db/derby/code/trunk/java/engine/org/apache/derby/catalog/SequencePreallocator.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java - copied, changed from r1138434, db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat - copied unchanged from r1135754, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/reference/Property.java db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/db/BasicDatabase.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java db/derby/code/branches/10.8/java/engine/org/apache/derby/loc/messages.xml db/derby/code/branches/10.8/java/shared/org/apache/derby/shared/common/reference/SQLState.java db/derby/code/branches/10.8/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java db/derby/code/branches/10.8/tools/javadoc/publishedapi.ant Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/reference/Property.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/reference/Property.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/reference/Property.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/reference/Property.java Thu Jun 30 18:12:09 2011 @@ -627,6 +627,24 @@ public interface Property { int LANG_SEQGEN_CACHE_SIZE_DEFAULT =32; /** + * The size of the identity generator cache + * used by the data dictionary. Database. Static. + *

+ * Externally visible. + */ + String LANG_IDGEN_CACHE_SIZE = "derby.language.identityGeneratorCacheSize"; + int LANG_IDGEN_CACHE_SIZE_DEFAULT =32; + + /** + * Name of the implementation of SequencePreallocator which is used + * to tune how many values Derby pre-allocates for identity columns + * and sequences. Database. Static. + *

+ * Externally visible. + */ + String LANG_SEQUENCE_PREALLOCATOR = "derby.language.sequence.preallocator"; + + /** derby.language.stalePlanCheckInterval

Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java Thu Jun 30 18:12:09 2011 @@ -214,6 +214,13 @@ public interface DataDictionary public void clearCaches() throws StandardException; /** + * Clear all of the sequence number generators. + * + * @exception StandardException Standard Derby error policy + */ + public void clearSequenceCaches() throws StandardException; + + /** * Inform this DataDictionary that we are about to start reading it. This * means using the various get methods in the DataDictionary. * Generally, this is done during query compilation. @@ -1715,29 +1722,6 @@ public interface DataDictionary throws StandardException; /** - * getSetAutoincrementValue fetches the autoincrement value from - * SYSCOLUMNS given a row location. If doUpdate is true it updates - * the autoincrement column with the new value. - * the value returned by this routine is the new value and *NOT* the - * value in the system catalogs. - * - * @param rl RowLocation of the entry in SYSCOLUMNS. - * @param tc TransactionController to use. - * @param doUpdate Write the new value to disk if TRUE. - * @param newValue A NumberDataValue to use to return incremented value. If - * null, then the caller simply wants the current value fromd disk. - * @param wait If true, then the caller wants to wait for locks. When - * using a nested user xaction we want to timeout right away if the parent - * holds the lock. - */ - public NumberDataValue getSetAutoincrementValue(RowLocation rl, - TransactionController tc, - boolean doUpdate, - NumberDataValue newValue, - boolean wait) - throws StandardException; - - /** * sets a new value in SYSCOLUMNS for a particular * autoincrement column. * @@ -1756,21 +1740,22 @@ public interface DataDictionary throws StandardException; /** - * Get the next number from an ANSI/ISO sequence generator - * which was created with the CREATE SEQUENCE statement. May - * raise an exception if the sequence was defined as NO CYCLE and - * the range of the sequence is exhausted. May allocate a range of - * sequence numbers and update the CURRENTVALUE column of the - * corresponding row in SYSSEQUENCES. This work is done in the + * Get the next number from an identity or sequence generator + * which was created with the CREATE TABLE or CREATE SEQUENCE statement. May + * raise an exception if the generator was defined as NO CYCLE and + * the range of the generator is exhausted. May allocate a range of + * numbers and update the current column of the + * corresponding row in SYSCOLULMNS or SYSSEQUENCES. This work is done in the * execution transaction of the current session. * - * @param sequenceUUIDstring String value of the UUID which identifies the sequence - * @param returnValue This is a data value to be stuffed with the next sequence number. + * @param catalogNumber Number of the catalog that manages the values (either SYSCOLUMNS_CATALOG_NUM or SYSSEQUENCES_CATALOG_NUM) + * @param uuidString String value of the UUID which identifies the table or the sequence + * @param returnValue This is a data value to be stuffed with the next number in the range. * - * @throws StandardException if the sequence does not cycle and its range is exhausted + * @throws StandardException if the generator does not cycle and its range is exhausted */ public void getCurrentValueAndAdvance - ( String sequenceUUIDstring, NumberDataValue returnValue ) + ( int catalogNumber, String uuidString, NumberDataValue returnValue ) throws StandardException; /** Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java Thu Jun 30 18:12:09 2011 @@ -32,6 +32,7 @@ import org.apache.derby.iapi.sql.depend. import org.apache.derby.iapi.sql.depend.Dependent; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.iapi.types.DataTypeDescriptor; +import org.apache.derby.iapi.types.TypeId; import org.apache.derby.iapi.store.access.TransactionController; import org.apache.derby.impl.sql.catalog.DDdependableFinder; @@ -45,6 +46,9 @@ import org.apache.derby.impl.sql.catalog public class SequenceDescriptor extends TupleDescriptor implements Provider, Dependent, PrivilegedSQLObject { + // indexes into array of computed min/max values + public static final int MIN_VALUE = 0; + public static final int MAX_VALUE = MIN_VALUE + 1; private UUID sequenceUUID; private String sequenceName; @@ -90,6 +94,35 @@ public class SequenceDescriptor extends this.canCycle = canCycle; } + /** + * Compute the minimum and maximum values for a sequence range. + * Returns an array of two Longs. The first Long is the minimum value, + * The second is the maximum value. + */ + public static Long[] computeMinMax + ( + DataTypeDescriptor dataType, + Object minValue, + Object maxValue + ) + { + Long[] retval = new Long[ 2 ]; + + if (dataType.getTypeId().equals(TypeId.SMALLINT_ID)) { + retval[ MIN_VALUE ] = (minValue != null ? (Long) minValue : new Long(Short.MIN_VALUE)); + retval[ MAX_VALUE ] = (maxValue != null ? (Long) maxValue : new Long(Short.MAX_VALUE)); + } else if (dataType.getTypeId().equals(TypeId.INTEGER_ID)) { + retval[ MIN_VALUE ] = (minValue != null ? (Long) minValue : new Long(Integer.MIN_VALUE)); + retval[ MAX_VALUE ] = (maxValue != null ? (Long) maxValue : new Long(Integer.MAX_VALUE)); + } else { + // Could only be BIGINT + retval[ MIN_VALUE ] = (minValue != null ? (Long) minValue : new Long(Long.MIN_VALUE)); + retval[ MAX_VALUE ] = (maxValue != null ? (Long) maxValue : new Long(Long.MAX_VALUE)); + } + + return retval; + } + /** * @see UniqueTupleDescriptor#getUUID */ Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/db/BasicDatabase.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/db/BasicDatabase.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/db/BasicDatabase.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/db/BasicDatabase.java Thu Jun 30 18:12:09 2011 @@ -241,6 +241,14 @@ public class BasicDatabase implements Mo } public void stop() { + try { + // on orderly shutdown, try not to leak unused numbers from the sequence generators. + dd.clearSequenceCaches(); + } + catch (Throwable t) + { + t.printStackTrace(Monitor.getStream().getPrintWriter()); + } active = false; } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Thu Jun 30 18:12:09 2011 @@ -385,11 +385,13 @@ public final class DataDictionaryImpl CacheManager nameTdCache; private CacheManager spsNameCache; private CacheManager sequenceGeneratorCache; + private CacheManager idGeneratorCache; private Hashtable spsIdHash; // private Hashtable spsTextHash; int tdCacheSize; int stmtCacheSize; private int seqgenCacheSize; + private int idgenCacheSize; /* Cache of permissions data */ CacheManager permissionsCache; @@ -633,6 +635,10 @@ public final class DataDictionaryImpl seqgenCacheSize = PropertyUtil.intPropertyValue(Property.LANG_SEQGEN_CACHE_SIZE, value, 0, Integer.MAX_VALUE, Property.LANG_SEQGEN_CACHE_SIZE_DEFAULT); + value = startParams.getProperty(Property.LANG_IDGEN_CACHE_SIZE); + idgenCacheSize = PropertyUtil.intPropertyValue(Property.LANG_IDGEN_CACHE_SIZE, value, + 0, Integer.MAX_VALUE, Property.LANG_IDGEN_CACHE_SIZE_DEFAULT); + value = startParams.getProperty(Property.LANG_PERMISSIONS_CACHE_SIZE); permissionsCacheSize = PropertyUtil.intPropertyValue(Property.LANG_PERMISSIONS_CACHE_SIZE, value, 0, Integer.MAX_VALUE, Property.LANG_PERMISSIONS_CACHE_SIZE_DEFAULT); @@ -688,6 +694,9 @@ public final class DataDictionaryImpl sequenceGeneratorCache = cf.newCacheManager ( this, "SequenceGeneratorCache", seqgenCacheSize, seqgenCacheSize ); + idGeneratorCache = cf.newCacheManager + ( this, "IdentityGeneratorCache", idgenCacheSize, idgenCacheSize ); + /* Get the object to coordinate cache transitions */ cacheCoordinator = new ShExLockable(); @@ -979,6 +988,7 @@ public final class DataDictionaryImpl else if ( cm == nameTdCache ) { return new NameTDCacheable( this ); } else if ( cm == permissionsCache ) { return new PermissionsCacheable( this ); } else if ( cm == sequenceGeneratorCache ) { return new SequenceUpdater.SyssequenceUpdater( this ); } + else if ( cm == idGeneratorCache ) { return new SequenceUpdater.SyscolumnsUpdater( this ); } else { return new SPSNameCacheable( this ); } } @@ -8629,91 +8639,26 @@ public final class DataDictionaryImpl return rla; } - - /** - * @see DataDictionary#getSetAutoincrementValue - */ - public NumberDataValue getSetAutoincrementValue( - RowLocation rl, - TransactionController tc, - boolean doUpdate, - NumberDataValue newValue, - boolean wait) - throws StandardException - { - - FormatableBitSet columnToUpdate = new - FormatableBitSet(SYSCOLUMNSRowFactory.SYSCOLUMNS_COLUMN_COUNT); - int columnNum = SYSCOLUMNSRowFactory.SYSCOLUMNS_AUTOINCREMENTVALUE; - TabInfoImpl ti = coreInfo[SYSCOLUMNS_CORE_NUM]; - ConglomerateController heapCC = null; - SYSCOLUMNSRowFactory rf = (SYSCOLUMNSRowFactory) ti.getCatalogRowFactory(); - ExecRow row = rf.makeEmptyRow(); - - FormatableBitSet columnToRead = new - FormatableBitSet(SYSCOLUMNSRowFactory.SYSCOLUMNS_COLUMN_COUNT); - - // FormatableBitSet is 0 based. - columnToRead.set(columnNum - 1); // current value. - columnToRead.set(columnNum); // start value. - columnToRead.set(columnNum + 1); // increment value. - - try - { - /* if wait is true then we need to do a wait while trying to - open/fetch from the conglomerate. note we use wait both to - open as well as fetch from the conglomerate. - */ - heapCC = - tc.openConglomerate( - ti.getHeapConglomerate(), - false, - (TransactionController.OPENMODE_FORUPDATE | - ((wait) ? 0 : TransactionController.OPENMODE_LOCK_NOWAIT)), - TransactionController.MODE_RECORD, - TransactionController.ISOLATION_REPEATABLE_READ); - - boolean baseRowExists = - heapCC.fetch(rl, row.getRowArray(), columnToRead, wait); - - columnToUpdate.set(columnNum - 1); // current value. - - // while the Row interface is 1 based. - NumberDataValue currentAI = (NumberDataValue)row.getColumn(columnNum); - long currentAIValue = currentAI.getLong(); - - if (doUpdate) - { - // we increment and store the new value in SYSCOLUMNS - NumberDataValue increment = (NumberDataValue)row.getColumn(columnNum + 2); - currentAI = currentAI.plus(currentAI, increment, currentAI); - row.setColumn(columnNum, currentAI); - heapCC.replace(rl, row.getRowArray(), columnToUpdate); - } - - // but we return the "currentAIValue"-- i.e the value before - // incrementing it. - if (newValue != null) - { - // user has passed in an object; set the current value in there and - // return it. - newValue.setValue(currentAIValue); - return newValue; - } - - else + private RowLocation computeIdentityRowLocation(TransactionController tc, + TableDescriptor td) + throws StandardException + { + int size; + if (!(td.tableHasAutoincrement())) { return null; } + + size = td.getNumberOfColumns(); + + for (int i = 0; i < size; i++) + { + ColumnDescriptor cd = td.getColumnDescriptor(i + 1); + if (cd.isAutoincrement()) { - // reuse the object read from row. - currentAI.setValue(currentAIValue); - return currentAI; + return computeRowLocation(tc, td, cd.getColumnName()); } - } - finally - { - if (heapCC != null) - heapCC.close(); - } - } + } + + return null; + } private ConglomerateDescriptor bootstrapOneIndex ( @@ -8880,10 +8825,12 @@ public final class DataDictionaryImpl { nameTdCache.cleanAll(); nameTdCache.ageOut(); + OIDTdCache.cleanAll(); OIDTdCache.ageOut(); - sequenceGeneratorCache.cleanAll(); - sequenceGeneratorCache.ageOut(); + + clearSequenceCaches(); + if (spsNameCache != null) { //System.out.println("CLEARING SPS CACHE"); @@ -8894,6 +8841,19 @@ public final class DataDictionaryImpl } } + /** + Flush sequence caches to disk so that we don't leak unused, pre-allocated numbers. + */ + public void clearSequenceCaches() throws StandardException + { + sequenceGeneratorCache.cleanAll(); + sequenceGeneratorCache.ageOut(); + + idGeneratorCache.cleanAll(); + idGeneratorCache.ageOut(); + } + + /** Add the required entries to the data dictionary for a System table. */ @@ -10009,7 +9969,8 @@ public final class DataDictionaryImpl /** * sets a new value in SYSCOLUMNS for a particular - * autoincrement column. + * autoincrement column. this throws away the sequence generator for the + * value so that it must be created from scratch. * * @param tc Transaction Controller to use. * @param columnName Name of the column. @@ -10059,9 +10020,23 @@ public final class DataDictionaryImpl bArray, colsToUpdate, tc); + + // remove the generator for this identity column so that it will be reinitialized with the new value. + flushIdentityFromCache( tableUUID ); + return; } + /** + * Remove an id generator from the cache so that it will have to be recreated. + * This method is called after changing the generator on disk. + */ + private void flushIdentityFromCache( UUID tableID ) throws StandardException + { + Cacheable idGenerator = idGeneratorCache.findCached( tableID.toString() ); + if ( idGenerator != null ) { idGeneratorCache.remove( idGenerator ); } + } + /** * Computes the RowLocation in SYSCOLUMNS for a particular * autoincrement column. @@ -10090,6 +10065,108 @@ public final class DataDictionaryImpl } /** + * Computes the RowLocation in SYSCOLUMNS for the identity column of a table. Also + * constructs a sequence descriptor describing the current state of the identity sequence. + * + * @param tc Transaction Controller to use. + * @param tableIDstring UUID of the table as a string + * @param rowLocation OUTPUT param for returing the row location + * @param sequenceDescriptor OUTPUT param for return the sequence descriptor + * + * @exception StandardException thrown on failure. + */ + void computeIdentityRowLocation + ( TransactionController tc, String tableIDstring, RowLocation[] rowLocation, SequenceDescriptor[] sequenceDescriptor ) + throws StandardException + { + UUID tableID = getUUIDFactory().recreateUUID( tableIDstring ); + TableDescriptor td = getTableDescriptor( tableID ); + + // there should only be 1 identity column per table + rowLocation[ 0 ] = computeIdentityRowLocation( tc, td ); + + TabInfoImpl ti = coreInfo[SYSCOLUMNS_CORE_NUM]; + ConglomerateController heapCC = null; + SYSCOLUMNSRowFactory rf = (SYSCOLUMNSRowFactory) ti.getCatalogRowFactory(); + ExecRow row = rf.makeEmptyRow(); + FormatableBitSet columnsToFetch = new FormatableBitSet( SYSCOLUMNSRowFactory.SYSCOLUMNS_COLUMN_COUNT ); + + for ( int i = 0; i < SYSCOLUMNSRowFactory.SYSCOLUMNS_COLUMN_COUNT; i++ ) + { + columnsToFetch.set( i ); + } + + try + { + heapCC = + tc.openConglomerate( + ti.getHeapConglomerate(), + false, + 0, + TransactionController.MODE_RECORD, + TransactionController.ISOLATION_REPEATABLE_READ); + + heapCC.fetch( rowLocation[ 0 ], row.getRowArray(), columnsToFetch, true ); + + ColumnDescriptor cd = (ColumnDescriptor) rf.buildDescriptor( row, td, this ); + DataTypeDescriptor dtd = cd.getType(); + Long[] minMax = SequenceDescriptor.computeMinMax( dtd, null, null ); + + sequenceDescriptor[ 0 ] = getDataDescriptorGenerator().newSequenceDescriptor + ( + td.getSchemaDescriptor(), + td.getUUID(), + td.getName(), + dtd, + new Long( cd.getAutoincValue() ), + cd.getAutoincStart(), + minMax[ SequenceDescriptor.MIN_VALUE ].longValue(), + minMax[ SequenceDescriptor.MAX_VALUE ].longValue(), + cd.getAutoincInc(), + false + ); + } + finally + { + if (heapCC != null) { heapCC.close(); } + } + } + + /** + * Set the current value of an identity sequence. This method does not perform + * any sanity checking but assumes that the caller knows what they are doing. If the + * old value on disk is not what we expect it to be, then we are in a race with another + * session. They won and we don't update the value on disk. However, if the old value + * is null, that is a signal to us that we should update the value on disk anyway. + * + * @param tc Transaction Controller to use. + * @param rowLocation Row in SYSCOLUMNS to update. + * @param wait True if we should wait for locks + * @param oldValue What we expect to find in the AUTOINCREMENTVALUE column. + * @param newValue What to stuff into the AUTOINCREMENTVALUE column. + * + * @return Returns true if the value was successfully updated, false if we lost a race with another session. + * + * @exception StandardException thrown on failure. + */ + boolean updateCurrentIdentityValue + ( TransactionController tc, RowLocation rowLocation, boolean wait, Long oldValue, Long newValue ) + throws StandardException + { + return updateCurrentSeqValue + ( + tc, + rowLocation, + wait, + oldValue, + newValue, + coreInfo[SYSCOLUMNS_CORE_NUM], + SYSCOLUMNSRowFactory.SYSCOLUMNS_COLUMN_COUNT, + SYSCOLUMNSRowFactory.SYSCOLUMNS_AUTOINCREMENTVALUE + ); + } + + /** * Computes the RowLocation in SYSSEQUENCES for a particular sequence. Also * constructs the sequence descriptor. * @@ -10147,11 +10224,55 @@ public final class DataDictionaryImpl ( TransactionController tc, RowLocation rowLocation, boolean wait, Long oldValue, Long newValue ) throws StandardException { - int columnNum = SYSSEQUENCESRowFactory.SYSSEQUENCES_CURRENT_VALUE; - FormatableBitSet columnToUpdate = new FormatableBitSet( SYSSEQUENCESRowFactory.SYSSEQUENCES_COLUMN_COUNT ); - TabInfoImpl ti = getNonCoreTI( SYSSEQUENCES_CATALOG_NUM ); + return updateCurrentSeqValue + ( + tc, + rowLocation, + wait, + oldValue, + newValue, + getNonCoreTI( SYSSEQUENCES_CATALOG_NUM ), + SYSSEQUENCESRowFactory.SYSSEQUENCES_COLUMN_COUNT, + SYSSEQUENCESRowFactory.SYSSEQUENCES_CURRENT_VALUE + ); + } + + /** + * Set the current value of an ANSI/ISO sequence or identity column. This method does not perform + * any sanity checking but assumes that the caller knows what they are doing. If the + * old value on disk is not what we expect it to be, then we are in a race with another + * session. They won and we don't update the value on disk. However, if the old value + * is null, that is a signal to us that we should update the value on disk anyway. + * + * @param tc Transaction Controller to use. + * @param rowLocation Row in SYSSEQUENCES or SYSCOLUMNS to update. + * @param wait True if we should wait for locks + * @param oldValue What we expect to find in the currentvalue column. + * @param newValue What to stuff into the current value column. + * @param ti Table info for the catalog that is being updated. + * @param columnsInRow Number of columns in the catalog row. + * @param columnNum ID of the current value column + * + * @return Returns true if the value was successfully updated, false if we lost a race with another session. + * + * @exception StandardException thrown on failure. + */ + private boolean updateCurrentSeqValue + ( + TransactionController tc, + RowLocation rowLocation, + boolean wait, + Long oldValue, + Long newValue, + TabInfoImpl ti, + int columnsInRow, + int columnNum + ) + throws StandardException + { + FormatableBitSet columnToUpdate = new FormatableBitSet( columnsInRow ); ConglomerateController heapCC = null; - SYSSEQUENCESRowFactory rf = (SYSSEQUENCESRowFactory) ti.getCatalogRowFactory(); + CatalogRowFactory rf = ti.getCatalogRowFactory(); ExecRow row = rf.makeEmptyRow(); // FormatableBitSet is 0 based. @@ -10207,13 +10328,28 @@ public final class DataDictionaryImpl * @see org.apache.derby.iapi.sql.dictionary.DataDictionary#getCurrentValueAndAdvance */ public void getCurrentValueAndAdvance - ( String sequenceUUIDstring, NumberDataValue returnValue ) + ( int catalogNumber, String uuidString, NumberDataValue returnValue ) throws StandardException { + CacheManager cm = null; SequenceUpdater sequenceUpdater = null; try { - sequenceUpdater = (SequenceUpdater) sequenceGeneratorCache.find( sequenceUUIDstring ); + switch( catalogNumber ) + { + case SYSSEQUENCES_CATALOG_NUM: + cm = sequenceGeneratorCache; + break; + + case SYSCOLUMNS_CATALOG_NUM: + cm = idGeneratorCache; + break; + + default: + throw StandardException.newException( SQLState.BTREE_UNIMPLEMENTED_FEATURE ); + } + + sequenceUpdater = (SequenceUpdater) cm.find( uuidString ); sequenceUpdater.getCurrentValueAndAdvance( returnValue ); } @@ -10221,7 +10357,7 @@ public final class DataDictionaryImpl { if ( sequenceUpdater != null ) { - sequenceGeneratorCache.release( sequenceUpdater ); + cm.release( sequenceUpdater ); } } } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java Thu Jun 30 18:12:09 2011 @@ -20,6 +20,7 @@ */ package org.apache.derby.impl.sql.catalog; +import org.apache.derby.catalog.SequencePreallocator; import org.apache.derby.iapi.error.StandardException; import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.services.cache.Cacheable; @@ -111,14 +112,6 @@ public class SequenceGenerator // /////////////////////////////////////////////////////////////////////////////////// - /** - * Default number of values to pre-allocate. In the future, we may want to provide - * something more sophisticated. For instance, we might want to make Derby tune - * this number per sequence generator or give the user the power to override Derby's - * decision. - */ - public static final int DEFAULT_PREALLOCATION_COUNT = 5; - /** If pre-allocation drops below this level, then we need to grab another chunk of numbers */ private static final int PREALLOCATION_THRESHHOLD = 1; @@ -168,9 +161,15 @@ public class SequenceGenerator // This is where we restart the sequence if we wrap around. private final long _RESTART_VALUE; - // Name of the sequence (for error messages). + // Name of the schema that the sequence lives in. + private final String _SCHEMA_NAME; + + // Name of the sequence. private final String _SEQUENCE_NAME; + // Logic to determine how many values to pre-allocate + private final SequencePreallocator _PREALLOCATOR; + /////////////////////////////////////////////////////////////////////////////////// // // VARIABLES @@ -183,12 +182,6 @@ public class SequenceGenerator // This is the next value which the generator will hand out. private long _currentValue; - // This is the number of values to pre-allocate per chunk. Right now this - // is a constant which we figure out when we initialize the generator. - // However, this number could change over time if, for instance, Derby - // tunes it on the fly. - private long _valuesPerAllocation; - // This is the remaining number of values which were pre-allocated on disk // by bumping the contents of SYSSEQUENCES.CURRENTVALUE. private long _remainingPreallocatedValues; @@ -209,7 +202,9 @@ public class SequenceGenerator long maxValue, long minValue, long restartValue, - String sequenceName + String schemaName, + String sequenceName, + SequencePreallocator sequencePreallocator ) { if ( currentValue == null ) @@ -229,14 +224,14 @@ public class SequenceGenerator _MIN_VALUE = minValue; _RESTART_VALUE = restartValue; _STEP_INCREASES = ( _INCREMENT > 0 ); + _SCHEMA_NAME = schemaName; _SEQUENCE_NAME = sequenceName; + _PREALLOCATOR = sequencePreallocator; // // Next call to getCurrentValueAndAdvance() will cause us to ask our caller to allocate a new range of values. // _remainingPreallocatedValues = 1L; - - _valuesPerAllocation = computePreAllocationCount(); } /////////////////////////////////////////////////////////////////////////////////// @@ -247,6 +242,15 @@ public class SequenceGenerator /** *

+ * Get the name of the schema of this sequence generator. Technically, this doesn't need to be + * synchronized. But it is simpler to just maintain a rule that all public methods + * should be synchronized. + *

+ */ + public synchronized String getSchemaName() { return _SCHEMA_NAME; } + + /** + *

* Get the name of this sequence generator. Technically, this doesn't need to be * synchronized. But it is simpler to just maintain a rule that all public methods * should be synchronized. @@ -299,7 +303,7 @@ public class SequenceGenerator if ( _isExhausted ) { throw StandardException.newException - ( SQLState.LANG_SEQUENCE_GENERATOR_EXHAUSTED, _SEQUENCE_NAME ); + ( SQLState.LANG_SEQUENCE_GENERATOR_EXHAUSTED, _SCHEMA_NAME, _SEQUENCE_NAME ); } long retval[] = new long[ CVAA_LENGTH ]; @@ -403,6 +407,8 @@ public class SequenceGenerator */ private void computeNewAllocation( long oldCurrentValue, long[] retval ) throws StandardException { + int preferredValuesPerAllocation = computePreAllocationCount(); + // // The values are growing toward one of the endpoints of the legal range, // either the largest legal value or the smallest legal value. First find out @@ -414,10 +420,10 @@ public class SequenceGenerator long newValueOnDisk; long valuesToAllocate; - if ( remainingLegalValues >= _valuesPerAllocation ) + if ( remainingLegalValues >= preferredValuesPerAllocation ) { - newValueOnDisk = oldCurrentValue + ( _valuesPerAllocation * _INCREMENT ); - valuesToAllocate = _valuesPerAllocation; + newValueOnDisk = oldCurrentValue + ( preferredValuesPerAllocation * _INCREMENT ); + valuesToAllocate = preferredValuesPerAllocation; } else { @@ -425,13 +431,13 @@ public class SequenceGenerator if ( _CAN_CYCLE ) { - long spillOverValues = _valuesPerAllocation - remainingLegalValues; + long spillOverValues = preferredValuesPerAllocation - remainingLegalValues; // account for the fact that the restart value itself is a legal value spillOverValues--; newValueOnDisk = _RESTART_VALUE + ( spillOverValues * _INCREMENT ); - valuesToAllocate = _valuesPerAllocation; + valuesToAllocate = preferredValuesPerAllocation; } else { @@ -490,9 +496,11 @@ public class SequenceGenerator */ private int computePreAllocationCount() { - int happyResult = DEFAULT_PREALLOCATION_COUNT; + int happyResult = _PREALLOCATOR.nextRangeSize( _SCHEMA_NAME, _SEQUENCE_NAME ); int unhappyResult = PREALLOCATION_THRESHHOLD; + if ( happyResult < unhappyResult ) { return unhappyResult; } + double min = _MIN_VALUE; double max = _MAX_VALUE; double range = max - min; Copied: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java (from r1138434, db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java) URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java?p2=db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java&p1=db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java&r1=1138434&r2=1141645&rev=1141645&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceRange.java Thu Jun 30 18:12:09 2011 @@ -38,12 +38,10 @@ public class SequenceRange implemen /////////////////////////////////////////////////////////////////////////////////// /** - * Default number of values to pre-allocate. In the future, we may want to provide - * something more sophisticated. For instance, we might want to make Derby tune - * this number per sequence generator or give the user the power to override Derby's - * decision. + * Default number of values to pre-allocate. This is the size of the preallocation range + * used by other databases. See DERBY-4437. */ - private static final int DEFAULT_PREALLOCATION_COUNT = 5; + private static final int DEFAULT_PREALLOCATION_COUNT = 20; /////////////////////////////////////////////////////////////////////////////////// // @@ -51,6 +49,8 @@ public class SequenceRange implemen // /////////////////////////////////////////////////////////////////////////////////// + private int _rangeSize; + /////////////////////////////////////////////////////////////////////////////////// // // CONSTRUCTOR @@ -58,7 +58,17 @@ public class SequenceRange implemen /////////////////////////////////////////////////////////////////////////////////// /**

0-arg constructore needed to satisfy the SequencePreallocator contract.

*/ - public SequenceRange() {} + public SequenceRange() + { + this( DEFAULT_PREALLOCATION_COUNT ); + } + + public SequenceRange( int rangeSize ) + { + if ( rangeSize <= 0 ) { rangeSize = DEFAULT_PREALLOCATION_COUNT; } + + _rangeSize = rangeSize; + } /////////////////////////////////////////////////////////////////////////////////// // @@ -72,7 +82,7 @@ public class SequenceRange implemen String sequenceName ) { - return DEFAULT_PREALLOCATION_COUNT; + return _rangeSize; } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java Thu Jun 30 18:12:09 2011 @@ -20,13 +20,16 @@ */ package org.apache.derby.impl.sql.catalog; +import org.apache.derby.catalog.SequencePreallocator; import org.apache.derby.iapi.db.Database; import org.apache.derby.iapi.error.StandardException; +import org.apache.derby.iapi.reference.Property; import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.services.cache.Cacheable; import org.apache.derby.iapi.services.cache.CacheManager; import org.apache.derby.iapi.services.context.ContextManager; import org.apache.derby.iapi.services.context.ContextService; +import org.apache.derby.iapi.services.property.PropertyUtil; import org.apache.derby.iapi.services.sanity.SanityManager; import org.apache.derby.iapi.sql.conn.LanguageConnectionContext; import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor; @@ -434,6 +437,47 @@ public abstract class SequenceUpdater im // /////////////////////////////////////////////////////////////////////////////////// + /** Make a new range allocator (called when the generator is instantiated) */ + protected SequencePreallocator makePreallocator( TransactionController tc ) + throws StandardException + { + String propertyName = Property.LANG_SEQUENCE_PREALLOCATOR; + String className = PropertyUtil.getServiceProperty( tc, propertyName ); + + if ( className == null ) { return new SequenceRange(); } + + try { + // If the property value was a number rather than a class name, then + // use that as the default size for preallocated ranges. + if ( isNumber( className ) ) + { + return new SequenceRange( Integer.parseInt( className ) ); + } + + return (SequencePreallocator) Class.forName( className ).newInstance(); + } + catch (ClassNotFoundException e) { throw missingAllocator( propertyName, className, e ); } + catch (ClassCastException e) { throw missingAllocator( propertyName, className, e ); } + catch (InstantiationException e) { throw missingAllocator( propertyName, className, e ); } + catch (IllegalAccessException e) { throw missingAllocator( propertyName, className, e ); } + catch (NumberFormatException e) { throw missingAllocator( propertyName, className, e ); } + } + private StandardException missingAllocator( String propertyName, String className, Exception e ) + { + return StandardException.newException( SQLState.LANG_UNKNOWN_SEQUENCE_PREALLOCATOR, e, propertyName, className ); + } + private boolean isNumber( String text ) + { + int length = text.length(); + + for ( int i = 0; i < length; i++ ) + { + if ( !Character.isDigit( text.charAt( i ) ) ) { return false; } + } + + return true; + } + /** Get the time we wait for a lock, in milliseconds--overridden by unit tests */ protected int getLockTimeout() { @@ -454,13 +498,62 @@ public abstract class SequenceUpdater im /////////////////////////////////////////////////////////////////////////////////// // - // INNER CLASSES + // NESTED CLASSES // /////////////////////////////////////////////////////////////////////////////////// /** *

* Specific implementation of SequenceUpdater for the sequences managed by + * SYSCOLUMNS. + *

+ */ + public static final class SyscolumnsUpdater extends SequenceUpdater + { + private RowLocation _sequenceRowLocation; + + public SyscolumnsUpdater() { super(); } + public SyscolumnsUpdater( DataDictionaryImpl dd ) { super( dd ); } + + // + // SequenceUpdater BEHAVIOR + // + + protected SequenceGenerator createSequenceGenerator( TransactionController readOnlyTC ) + throws StandardException + { + RowLocation[] rowLocation = new RowLocation[ 1 ]; + SequenceDescriptor[] sequenceDescriptor = new SequenceDescriptor[ 1 ]; + + _dd.computeIdentityRowLocation( readOnlyTC, _uuidString, rowLocation, sequenceDescriptor ); + + _sequenceRowLocation = rowLocation[ 0 ]; + + SequenceDescriptor isd = sequenceDescriptor[ 0 ]; + + return new SequenceGenerator + ( + isd.getCurrentValue(), + isd.canCycle(), + isd.getIncrement(), + isd.getMaximumValue(), + isd.getMinimumValue(), + isd.getStartValue(), + isd.getSchemaDescriptor().getSchemaName(), + isd.getSequenceName(), + makePreallocator( readOnlyTC ) + ); + } + + protected boolean updateCurrentValueOnDisk( TransactionController tc, Long oldValue, Long newValue, boolean wait ) throws StandardException + { + return _dd.updateCurrentIdentityValue( tc, _sequenceRowLocation, wait, oldValue, newValue ); + } + } + + /** + *

+ * Specific implementation of SequenceUpdater for the sequences managed by * SYSSEQUENCES. *

*/ @@ -495,7 +588,9 @@ public abstract class SequenceUpdater im isd.getMaximumValue(), isd.getMinimumValue(), isd.getStartValue(), - isd.getSequenceName() + isd.getSchemaDescriptor().getSchemaName(), + isd.getSequenceName(), + makePreallocator( readOnlyTC ) ); } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java Thu Jun 30 18:12:09 2011 @@ -26,6 +26,7 @@ import org.apache.derby.iapi.services.sa import org.apache.derby.iapi.sql.compile.CompilerContext; import org.apache.derby.iapi.sql.execute.ConstantAction; import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor; +import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor; import org.apache.derby.iapi.types.DataTypeDescriptor; import org.apache.derby.iapi.reference.SQLState; import org.apache.derby.iapi.types.TypeId; @@ -83,17 +84,9 @@ public class CreateSequenceNode extends _stepValue = (stepValue != null ? (Long) stepValue : new Long(1)); - if (_dataType.getTypeId().equals(TypeId.SMALLINT_ID)) { - _minValue = (minValue != null ? (Long) minValue : new Long(Short.MIN_VALUE)); - _maxValue = (maxValue != null ? (Long) maxValue : new Long(Short.MAX_VALUE)); - } else if (_dataType.getTypeId().equals(TypeId.INTEGER_ID)) { - _minValue = (minValue != null ? (Long) minValue : new Long(Integer.MIN_VALUE)); - _maxValue = (maxValue != null ? (Long) maxValue : new Long(Integer.MAX_VALUE)); - } else { - // Could only be BIGINT - _minValue = (minValue != null ? (Long) minValue : new Long(Long.MIN_VALUE)); - _maxValue = (maxValue != null ? (Long) maxValue : new Long(Long.MAX_VALUE)); - } + Long[] minMax = SequenceDescriptor.computeMinMax( _dataType, minValue, maxValue ); + _minValue = minMax[ SequenceDescriptor.MIN_VALUE ]; + _maxValue = minMax[ SequenceDescriptor.MAX_VALUE ]; if (initialValue != null) { _initialValue = (Long) initialValue; Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Thu Jun 30 18:12:09 2011 @@ -28,6 +28,7 @@ import org.apache.derby.iapi.services.co import org.apache.derby.iapi.services.compiler.LocalField; import org.apache.derby.iapi.sql.compile.CompilerContext; import org.apache.derby.iapi.services.classfile.VMOpcode; +import org.apache.derby.iapi.sql.dictionary.DataDictionary; import org.apache.derby.iapi.sql.dictionary.SequenceDescriptor; import org.apache.derby.iapi.sql.dictionary.SchemaDescriptor; @@ -133,6 +134,7 @@ public class NextSequenceNode extends Va int dataTypeFormatID = sequenceDescriptor.getDataType().getNull().getTypeFormatId(); mb.pushThis(); + mb.push( DataDictionary.SYSSEQUENCES_CATALOG_NUM ); mb.push( sequenceUUIDstring ); mb.push( dataTypeFormatID ); mb.callMethod @@ -141,7 +143,7 @@ public class NextSequenceNode extends Va ClassName.BaseActivation, "getCurrentValueAndAdvance", ClassName.NumberDataValue, - 2 + 3 ); } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java Thu Jun 30 18:12:09 2011 @@ -670,22 +670,23 @@ public abstract class BaseActivation imp } /** - * Called by generated code to get the next number in an ANSI/ISO sequence - * and advance the sequence. Raises an exception if the sequence was declared + * Called by generated code to get the next number in an identity or sequence generator + * and advance the generator. Raises an exception if the generator was declared * NO CYCLE and its range is exhausted. * - * @param sequenceUUIDstring The string value of the sequence's UUID + * @param catalogNumber SYSCOLUMNS_CATALOG_NUM or SYSSEQUENCES_CATALOG_NUM, depending on the kind of generator + * @param uuidString The string value of the generator's uuid (table uuid for identity generators and sequence uuid for sequence generators) * @param typeFormatID The format id of the data type to be returned. E.g., StoredFormatIds.SQL_INTEGER_ID. * * @return The next number in the sequence */ protected NumberDataValue getCurrentValueAndAdvance - ( String sequenceUUIDstring, int typeFormatID ) + ( int catalogNumber, String uuidString, int typeFormatID ) throws StandardException { NumberDataValue ndv = (NumberDataValue) getDataValueFactory().getNull( typeFormatID, StringDataValue.COLLATION_TYPE_UCS_BASIC ); - lcc.getDataDictionary().getCurrentValueAndAdvance( sequenceUUIDstring, ndv ); + lcc.getDataDictionary().getCurrentValueAndAdvance( catalogNumber, uuidString, ndv ); return ndv; } Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertConstantAction.java Thu Jun 30 18:12:09 2011 @@ -251,24 +251,6 @@ public class InsertConstantAction extend public long getAutoincIncrement(int i) { return autoincIncrement[i]; } /** - * Does the target table has autoincrement columns. - * - * @return True if the table has ai columns - */ - public boolean hasAutoincrement() - { - return (autoincRowLocation != null); - } - - /** - * gets the row location - */ - public RowLocation[] getAutoincRowLocation() - { - return autoincRowLocation; - } - - /** * Get the formatID which corresponds to this class. * * @return the formatID of this class Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/impl/sql/execute/InsertResultSet.java Thu Jun 30 18:12:09 2011 @@ -349,19 +349,18 @@ class InsertResultSet extends DMLWriteRe // Is this a bulkInsert or regular insert? String insertMode = constants.getProperty("insertMode"); - RowLocation[] rla; - - if ((rla = constants.getAutoincRowLocation()) != null) + if ( hasAutoincrement() ) { - aiCache = - new NumberDataValue[rla.length]; - for (int i = 0; i < resultDescription.getColumnCount(); i++) + int cacheLength = resultDescription.getColumnCount(); + aiCache = new NumberDataValue[ cacheLength ]; + for (int i = 0; i < cacheLength; i++) { - if (rla[i] == null) - continue; ResultColumnDescriptor rcd = resultDescription.getColumnDescriptor(i + 1); - aiCache[i] = (NumberDataValue)rcd.getType().getNull(); + if ( rcd.getType().getTypeId().isNumericTypeId() ) + { + aiCache[i] = (NumberDataValue)rcd.getType().getNull(); + } } } @@ -391,6 +390,18 @@ class InsertResultSet extends DMLWriteRe //System.out.println("new InsertResultSet " + sourceResultSet.getClass()); } + + /** + * Return true if the table has an autoincrement column. + */ + private boolean hasAutoincrement() throws StandardException + { + // Global temporary tables don't have table descriptors but they + // don't have identity columns either + TableDescriptor tabdesc = getTableDescriptor(); + if ( tabdesc == null ) { return false; } + else { return tabdesc.tableHasAutoincrement(); } + } /** @exception StandardException Standard Derby error policy @@ -581,6 +592,28 @@ class InsertResultSet extends DMLWriteRe } } + /** + * Get the table descriptor if it hasn't already been looked up. + */ + private TableDescriptor getTableDescriptor() + throws StandardException + { + if ( td == null ) { td = getDataDictionary().getTableDescriptor(constants.targetUUID); } + + return td; + } + + /** + * Get the data dictionary if it hasn't already been looked up. + */ + private DataDictionary getDataDictionary() + throws StandardException + { + if ( dd == null ) { dd = lcc.getDataDictionary(); } + + return dd; + } + /** * If user didn't provide columns list for auto-generated columns, then only include * columns with auto-generated values in the resultset. Those columns would be ones @@ -765,9 +798,9 @@ class InsertResultSet extends DMLWriteRe } else { - dvd = dd.getSetAutoincrementValue( - constants.autoincRowLocation[index], - tc, false, aiCache[index], true); + dvd = (NumberDataValue) aiCache[ index ].getNewNull(); + dd.getCurrentValueAndAdvance + ( DataDictionary.SYSCOLUMNS_CATALOG_NUM, getTableDescriptor().getUUID().toString(), dvd ); startValue = dvd.getLong(); } lcc.autoincrementCreateCounter(td.getSchemaName(), @@ -786,77 +819,12 @@ class InsertResultSet extends DMLWriteRe else { - NumberDataValue newValue; - TransactionController nestedTC = null, tcToUse = tc; - - try - { - nestedTC = tc.startNestedUserTransaction(false); - tcToUse = nestedTC; - } - - catch (StandardException se) - { - // If I cannot start a Nested User Transaction use the parent - // transaction to do all the work. - tcToUse = tc; - } - - try - { - /* If tcToUse == tc, then we are using parent xaction-- this - can happen if for some reason we couldn't start a nested - transaction - */ - newValue = dd.getSetAutoincrementValue( - constants.autoincRowLocation[index], - tcToUse, true, aiCache[index], (tcToUse == tc)); - } + NumberDataValue newValue = aiCache[ index ]; - catch (StandardException se) - { - if (tcToUse == tc) - { - /* we've using the parent xaction and we've timed out; just - throw an error and exit. - */ - throw se; - } + dd.getCurrentValueAndAdvance + ( DataDictionary.SYSCOLUMNS_CATALOG_NUM, getTableDescriptor().getUUID().toString(), newValue ); - if (se.getMessageId().equals(SQLState.LOCK_TIMEOUT)) - { - // if we couldn't do this with a nested xaction, retry with - // parent-- we need to wait this time! - newValue = dd.getSetAutoincrementValue( - constants.autoincRowLocation[index], - tc, true, aiCache[index], true); - } - else if (se.getMessageId().equals(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE)) - { - // if we got an overflow error, throw a more meaningful - // error message - throw StandardException.newException( - SQLState.LANG_AI_OVERFLOW, - se, - constants.getTableName(), - constants.getColumnName(index)); - } - else throw se; - } - finally - { - // no matter what, commit the nested transaction; if something - // bad happened in the child xaction lets not abort the parent - // here. - if (nestedTC != null) - { - nestedTC.commit(); - nestedTC.destroy(); - } - } - aiCache[index] = newValue; - if (setIdentity) - identityVal = newValue.getLong(); + if (setIdentity) { identityVal = newValue.getLong(); } } return aiCache[index]; @@ -889,7 +857,7 @@ class InsertResultSet extends DMLWriteRe private void normalInsertCore(LanguageConnectionContext lcc, boolean firstExecute) throws StandardException { - boolean setUserIdentity = constants.hasAutoincrement() && isSingleRowResultSet(); + boolean setUserIdentity = hasAutoincrement() && isSingleRowResultSet(); boolean firstDeferredRow = true; ExecRow deferredRowBuffer = null; long user_autoinc=0; Modified: db/derby/code/branches/10.8/java/engine/org/apache/derby/loc/messages.xml URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/engine/org/apache/derby/loc/messages.xml?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/engine/org/apache/derby/loc/messages.xml (original) +++ db/derby/code/branches/10.8/java/engine/org/apache/derby/loc/messages.xml Thu Jun 30 18:12:09 2011 @@ -674,7 +674,8 @@ Guide. 2200H.S - Sequence generator '{0}' does not cycle. No more values can be obtained from this sequence generator. + Sequence generator '{0}.{1}' does not cycle. No more values can be obtained from this sequence generator. + schemaName sequenceName @@ -3433,6 +3434,13 @@ Guide. sequenceName + + X0Y85.S + The Derby property '{0}' identifies a class which cannot be instantiated: '{1}'. See the next exception for details. + propertyName + className + + Modified: db/derby/code/branches/10.8/java/shared/org/apache/derby/shared/common/reference/SQLState.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original) +++ db/derby/code/branches/10.8/java/shared/org/apache/derby/shared/common/reference/SQLState.java Thu Jun 30 18:12:09 2011 @@ -719,8 +719,8 @@ public interface SQLState { String LANG_INVALID_TRIM_SET = "22027"; String LANG_STRING_TOO_LONG = "22028"; String LANG_ESCAPE_IS_NULL = "22501"; - String LANG_INVALID_ROW_COUNT_OFFSET = "2201X"; String LANG_INVALID_ROW_COUNT_FIRST = "2201W"; + String LANG_INVALID_ROW_COUNT_OFFSET = "2201X"; String LANG_ROW_COUNT_OFFSET_FIRST_IS_NULL = "2201Z"; /* @@ -1369,6 +1369,7 @@ public interface SQLState { String LANG_NULL_DATA_IN_NON_NULL_COLUMN = "X0Y80.S"; String LANG_IGNORE_MISSING_INDEX_ROW_DURING_DELETE = "X0Y83.S"; String LANG_TOO_MUCH_CONTENTION_ON_SEQUENCE = "X0Y84.S"; + String LANG_UNKNOWN_SEQUENCE_PREALLOCATOR = "X0Y85.S"; // TEMPORARY EXECUTION RESTRICTIONS Modified: db/derby/code/branches/10.8/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java (original) +++ db/derby/code/branches/10.8/java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java Thu Jun 30 18:12:09 2011 @@ -1,6 +1,6 @@ /* - Derby - Class org.apache.impl.storeless.EmptyDictionary + Derby - Class org.apache.derby.impl.storeless.EmptyDictionary Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with @@ -79,6 +79,11 @@ public class EmptyDictionary implements } + public void clearSequenceCaches() throws StandardException { + // TODO Auto-generated method stub + + } + public int startReading(LanguageConnectionContext lcc) throws StandardException { // TODO Auto-generated method stub @@ -702,7 +707,7 @@ public class EmptyDictionary implements } public void getCurrentValueAndAdvance - ( String sequenceUUIDstring, NumberDataValue returnValue ) + ( int catalogNumber, String uuidString, NumberDataValue returnValue ) throws StandardException { // TODO Auto-generated method stub @@ -714,13 +719,6 @@ public class EmptyDictionary implements return null; } - public NumberDataValue getSetAutoincrementValue(RowLocation rl, - TransactionController tc, boolean doUpdate, - NumberDataValue newValue, boolean wait) throws StandardException { - // TODO Auto-generated method stub - return null; - } - public void setAutoincrementValue(TransactionController tc, UUID tableUUID, String columnName, long aiValue, boolean incrementNeeded) throws StandardException { Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java (original) +++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java Thu Jun 30 18:12:09 2011 @@ -3006,7 +3006,7 @@ public final class AlterTableTest extend "where c.referenceid = t.tableid and t.tablename='D3175'"); JDBC.assertUnorderedResultSet(rs, new String[][]{ {"X", "1", "VARCHAR(12)", null, null, null, null, "D3175", "T", "R"}, - {"ID", "2", "INTEGER NOT NULL", "GENERATED_BY_DEFAULT", "3", "1", "1", "D3175", "T", "R"} + {"ID", "2", "INTEGER NOT NULL", "GENERATED_BY_DEFAULT", "22", "1", "1", "D3175", "T", "R"} }); } Modified: db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java?rev=1141645&r1=1141644&r2=1141645&view=diff ============================================================================== --- db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java (original) +++ db/derby/code/branches/10.8/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java Thu Jun 30 18:12:09 2011 @@ -33,8 +33,13 @@ import junit.framework.TestSuite; import org.apache.derbyTesting.junit.BaseJDBCTestCase; import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; import org.apache.derbyTesting.junit.JDBC; +import org.apache.derbyTesting.junit.SupportFilesSetup; public class AutoIncrementTest extends BaseJDBCTestCase { + + private static final String SEQUENCE_OVERFLOW = "2200H"; + private static final String IMPORT_FILE_NAME = "t_4437_2.dat"; + public AutoIncrementTest(String name) { super (name); @@ -221,7 +226,7 @@ public class AutoIncrementTest extends B String[][]expectedRows=new String[][]{{"0","0"},{"1","2"},{"2","4"},{"33","6"}}; JDBC.assertFullResultSet(rs,expectedRows); rs = s.executeQuery("select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'AIS'"); - expectedRows=new String[][]{{"AIS","8","0","2"}}; + expectedRows=new String[][]{{"AIS","40","0","2"}}; JDBC.assertFullResultSet(rs,expectedRows); } @@ -378,7 +383,7 @@ public class AutoIncrementTest extends B expectedRows=new String[][]{{"1","1"},{"2","2"},{"3","3"}}; JDBC.assertFullResultSet(rs,expectedRows); rs=s.executeQuery("select b.tablename, a.autoincrementvalue, a.autoincrementstart, a.autoincrementinc from sys.syscolumns a, sys.systables b where a.referenceid=b.tableid and a.columnname ='S1' and b.tablename = 'TAB1'"); - expectedRows=new String[][]{{"TAB1","4","1","1"}}; + expectedRows=new String[][]{{"TAB1","21","1","1"}}; JDBC.assertFullResultSet(rs,expectedRows); s.executeUpdate("create table tab2 (lvl int, s1 bigint generated always as identity)"); s.executeUpdate("create trigger tab1_after2 after insert on tab3 referencing new as newrow for each row insert into tab2 (lvl) values 1,2,3"); @@ -490,7 +495,7 @@ public class AutoIncrementTest extends B JDBC.assertFullResultSet(rs,expectedRows); s.execute("insert into lockt1 (x) values (3)"); rs=s.executeQuery("select * from lock_table order by tabname, type desc, mode, cnt"); - expectedRows=new String[][]{{"APP ","UserTran","TABLE ","1 ","IX","LOCKT1 ","GRANT","ACTIVE"},{"APP ","UserTran","ROW ","1 ","X","LOCKT1 ","GRANT","ACTIVE"},{"APP ","UserTran","TABLE ","1 ","IX","SYSCOLUMNS ","GRANT","ACTIVE"},{"APP ","UserTran","TABLE ","1 ","S","SYSCOLUMNS ","GRANT","ACTIVE"},{"APP ","UserTran","ROW ","2 ","X","SYSCOLUMNS ","GRANT","ACTIVE"}}; + expectedRows=new String[][]{{"APP ","UserTran","TABLE ","1 ","IX","LOCKT1 ","GRANT","ACTIVE"},{"APP ","UserTran","ROW ","1 ","X","LOCKT1 ","GRANT","ACTIVE"},{"APP ","UserTran","TABLE ","1 ","S","SYSCOLUMNS ","GRANT","ACTIVE"}}; JDBC.assertFullResultSet(rs,expectedRows); commit(); @@ -596,10 +601,10 @@ public class AutoIncrementTest extends B ResultSet rs; Statement pst=createStatement(); Statement s=createStatement(); - assertStatementError("22003", pst,"insert into ai_over1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)"); - assertStatementError("22003", pst,"insert into ai_over1 (x) values (1)"); + assertStatementError(SEQUENCE_OVERFLOW, pst,"insert into ai_over1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)"); + assertStatementError(SEQUENCE_OVERFLOW, pst,"insert into ai_over1 (x) values (1)"); s.executeUpdate("insert into ai_over2 (x) values (1),(2),(3),(4),(5),(6),(7),(8)"); - assertStatementError("22003", pst,"insert into ai_over2 (x) values (9),(10)"); + assertStatementError(SEQUENCE_OVERFLOW, pst,"insert into ai_over2 (x) values (9),(10)"); String[][]expectedRows=new String[][]{{"1","-32760"},{"2","-32761"},{"3","-32762"},{"4","-32763"},{"5","-32764"},{"6","-32765"},{"7","-32766"},{"8","-32767"}}; rs=s.executeQuery("select * from ai_over2"); JDBC.assertFullResultSet(rs,expectedRows); @@ -608,12 +613,12 @@ public class AutoIncrementTest extends B rs=s.executeQuery("select * from ai_over3"); expectedRows=new String[][]{{"1","2147483646"},{"2","2147483647"}}; JDBC.assertFullResultSet(rs,expectedRows); - assertStatementError("22003", pst,"insert into ai_over3 (x) select x from ai_over3"); + assertStatementError(SEQUENCE_OVERFLOW, pst,"insert into ai_over3 (x) select x from ai_over3"); //bigint overflow check - s.executeUpdate("insert into ai_over4 (x) values (1),(2)"); - assertStatementError("22003", pst,"insert into ai_over4 (x) values (3)"); + s.executeUpdate("insert into ai_over4 (x) values (1),(2),(3)"); + assertStatementError(SEQUENCE_OVERFLOW, pst,"insert into ai_over4 (x) values (4)"); rs=s.executeQuery("select * from ai_over4"); - expectedRows=new String[][]{{"1","9223372036854775805"},{"2","9223372036854775806"}}; + expectedRows=new String[][]{{"1","9223372036854775805"},{"2","9223372036854775806"},{"3","9223372036854775807"}}; JDBC.assertFullResultSet(rs,expectedRows); } @@ -939,7 +944,7 @@ public class AutoIncrementTest extends B expectedRows=new String[][]{{"2","2"},{"2","9999"}}; JDBC.assertFullResultSet(rs,expectedRows); rs=s.executeQuery("select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'REC11'"); - expectedRows=new String[][]{{"REC11","4","2","2"}}; + expectedRows=new String[][]{{"REC11","42","2","2"}}; JDBC.assertFullResultSet(rs,expectedRows); assertStatementError("42837",s,"alter table restartt1 alter column c12 RESTART WITH 2"); assertStatementError("42X49",s,"alter table restartt1 alter column rec11 RESTART WITH 2.20"); @@ -974,7 +979,7 @@ public class AutoIncrementTest extends B assertStatementError("23505",s,"insert into t1lock(c12) values(3)"); rs=s.executeQuery("select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC from sys.syscolumns where COLUMNNAME = 'LOCKC11'"); //Utilities.showResultSet(rs); - expectedRows=new String[][]{{"LOCKC11","2","1","1"}}; + expectedRows=new String[][]{{"LOCKC11","21","1","1"}}; JDBC.assertFullResultSet(rs,expectedRows); rs=s.executeQuery("select * from t1lock"); @@ -1081,9 +1086,227 @@ public class AutoIncrementTest extends B assertStatementError("42XA7",s,"alter table d4006 alter column y default null"); } + + /** + *

+ * Test that alter table interacts well with the sequence-generator-based identity generators. + *

+ */ + public void test_4437_01_alterTable() throws Exception + { + Statement s = createStatement(); + String[][] expectedResults; + + s.execute("create table t_4437_1( a int, b int generated always as identity )"); + s.execute("insert into t_4437_1( a ) values (100), (101), (102), (103), (104), (105)"); + expectedResults = new String[][] + { + { "100", "1", }, + { "101", "2", }, + { "102", "3", }, + { "103", "4", }, + { "104", "5", }, + { "105", "6", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults ); + + s.execute("alter table t_4437_1 alter column b restart with 22"); + s.execute("insert into t_4437_1( a ) values (110), (111), (112), (113), (114), (115)"); + expectedResults = addResults + ( + expectedResults, + new String[][] + { + { "110", "22", }, + { "111", "23", }, + { "112", "24", }, + { "113", "25", }, + { "114", "26", }, + { "115", "27", }, + } + ); + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults ); + + s.execute("alter table t_4437_1 alter column b set increment by 3"); + s.execute("insert into t_4437_1( a ) values (120), (121), (122), (123), (124)"); + expectedResults = addResults + ( + expectedResults, + new String[][] + { + { "120", "30", }, + { "121", "33", }, + { "122", "36", }, + { "123", "39", }, + { "124", "42", }, + } + ); + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults ); + + s.execute("alter table t_4437_1 alter column b set increment by 4"); + s.execute("insert into t_4437_1( a ) values (130), (131), (132), (133), (134), (135)"); + expectedResults = addResults + ( + expectedResults, + new String[][] + { + { "130", "46", }, + { "131", "50", }, + { "132", "54", }, + { "133", "58", }, + { "134", "62", }, + { "135", "66", }, + } + ); + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults ); + + s.execute( "drop table t_4437_1" ); + } + private String[][] addResults( String[][] original, String[][] extras ) + { + int originalLength = original.length; + int extrasLength = extras.length; + String[][] result = new String[ originalLength + extrasLength ][]; + + int idx = 0; + for ( int i = 0; i < originalLength; i++ ) { result[ idx++ ] = original[ i ]; } + for ( int i = 0; i < extrasLength; i++ ) { result[ idx++ ] = extras[ i ]; } + + return result; + } - public static Test suite() { - return new CleanDatabaseTestSetup( + /** + *

+ * Test that bulk import interacts well with the sequence-generator-based identity generators. + *

+ */ + public void test_4437_02_bulkImport() throws Exception + { + Statement s = createStatement(); + String[][] expectedResults; + + s.execute( "create table t_4437_2( a int, b int generated always as identity (start with -10, increment by -3) )" ); + s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)" ); + expectedResults = new String[][] + { + { "110", "-10", }, + { "111", "-13", }, + { "112", "-16", }, + { "113", "-19", }, + { "114", "-22", }, + { "115", "-25", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults ); + + // import running in replace mode resets the sequence counter + s.execute( "call syscs_util.syscs_import_data( null, 'T_4437_2', 'A', null, 'extin/" + IMPORT_FILE_NAME + "', null, null, null, 1 )" ); + expectedResults = new String[][] + { + { "100", "-10", }, + { "101", "-13", }, + { "102", "-16", }, + { "103", "-19", }, + { "104", "-22", }, + { "105", "-25", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults ); + + // truncate does not reset the counter + s.execute( "truncate table t_4437_2" ); + s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)" ); + expectedResults = new String[][] + { + { "110", "-28", }, + { "111", "-31", }, + { "112", "-34", }, + { "113", "-37", }, + { "114", "-40", }, + { "115", "-43", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults ); + + // delete does not reset the counter either + s.execute( "delete from t_4437_2" ); + s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)" ); + expectedResults = new String[][] + { + { "110", "-46", }, + { "111", "-49", }, + { "112", "-52", }, + { "113", "-55", }, + { "114", "-58", }, + { "115", "-61", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults ); + + // import running in insert mode does not reset the sequence counter + s.execute( "call syscs_util.syscs_import_data( null, 'T_4437_2', 'A', null, 'extin/" + IMPORT_FILE_NAME + "', null, null, null, 0 )" ); + expectedResults = new String[][] + { + { "100", "-64", }, + { "101", "-67", }, + { "102", "-70", }, + { "103", "-73", }, + { "104", "-76", }, + { "105", "-79", }, + { "110", "-46", }, + { "111", "-49", }, + { "112", "-52", }, + { "113", "-55", }, + { "114", "-58", }, + { "115", "-61", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults ); + + s.execute( "drop table t_4437_2" ); + } + + /** + *

+ * Test that deferred inserts work well with the sequence-generator-based identity generators. + *

+ */ + public void test_4437_03_deferredInsert() throws Exception + { + Statement s = createStatement(); + String[][] expectedResults; + + s.execute( "create table t_4437_3( a int, b smallint generated always as identity (start with 9990, increment by -10) )" ); + s.execute( "insert into t_4437_3( a ) values (100), (101), (102), (103), (104), (105)" ); + expectedResults = new String[][] + { + { "100", "9990", }, + { "101", "9980", }, + { "102", "9970", }, + { "103", "9960", }, + { "104", "9950", }, + { "105", "9940", }, + }; + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_3 order by a" ), expectedResults ); + + // deferred inserts should continue the sequence where it left off + s.execute( "insert into t_4437_3( a ) select a + 10 from ( select a from t_4437_3 order by a ) s" ); + expectedResults = addResults + ( + expectedResults, + new String[][] + { + { "110", "9930", }, + { "111", "9920", }, + { "112", "9910", }, + { "113", "9900", }, + { "114", "9890", }, + { "115", "9880", }, + } + ); + JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_3 order by a" ), expectedResults ); + + s.execute( "drop table t_4437_3" ); + } + + public static Test suite() + { + Test cleanDatabaseSuite = new CleanDatabaseTestSetup( new TestSuite(AutoIncrementTest.class, "AutoIncrementTest")) { protected void decorateSQL(Statement s) throws SQLException @@ -1091,5 +1314,16 @@ public class AutoIncrementTest extends B createSchemaObjects(s); } }; + + // + // Copies the data file to a location which can be read. + // + Test result = new SupportFilesSetup + ( + cleanDatabaseSuite, + new String [] { "functionTests/tests/lang/" + IMPORT_FILE_NAME } + ); + + return result; } }