Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      feature request
    • Issue & fix info:
      Patch Available

      Description

      Would like to see support added for sequences. This would permit a select against the sequence to always obtain a ever increasing/decreasing value. The identity column works fine but there are times for applications where the application needs to obtain the sequence number and use it prior to the database write. Subsequent calls to the table/column would result in a new number on each call.

      SQL such as the following:
      SELECT NEXT VALUE FOR sequence_name FROM sometable ; would result in a next value.

      1. SequenceGenerator.html
        24 kB
        Rick Hillegas
      2. catalogs_a.patch
        57 kB
        Suran Jayathilaka
      3. catalogs_b.patch
        52 kB
        Suran Jayathilaka
      4. catalogs_c.patch
        70 kB
        Suran Jayathilaka
      5. catalogs_d.patch
        69 kB
        Suran Jayathilaka
      6. altertable.diff
        18 kB
        Suran Jayathilaka
      7. catalogs_e.patch
        69 kB
        Suran Jayathilaka
      8. catalogs_f.patch
        69 kB
        Suran Jayathilaka
      9. catalogs_f_2.patch
        69 kB
        Suran Jayathilaka
      10. catalogs_g.diff
        69 kB
        Rick Hillegas
      11. catalogs_h.diff
        70 kB
        Rick Hillegas
      12. create_sequence_a.patch
        18 kB
        Suran Jayathilaka
      13. create_drop_sequence_a.patch
        29 kB
        Suran Jayathilaka
      14. create_drop_sequence_b.patch
        34 kB
        Suran Jayathilaka
      15. create_drop_sequence_c.patch
        39 kB
        Suran Jayathilaka
      16. create_drop_sequence_d.patch
        40 kB
        Suran Jayathilaka
      17. derby-712-02-aa-privilegeNodeCleanup.diff
        10 kB
        Rick Hillegas
      18. sequences_next_value_a.patch
        10 kB
        Suran Jayathilaka
      19. derby-712-03-aa-usagePrivilege.diff
        17 kB
        Rick Hillegas
      20. derby-712-03-ac-usagePrivilege.diff
        18 kB
        Rick Hillegas
      21. derby-712-04-aa-dblook.diff
        28 kB
        Rick Hillegas
      22. createseq_args_bind_a.diff
        16 kB
        Suran Jayathilaka
      23. derby-712-05-af-sequenceGenerator.diff
        87 kB
        Rick Hillegas
      24. createseq_args_bind_b.diff
        22 kB
        Suran Jayathilaka
      25. derby-712-05-al-sequenceGenerator.diff
        99 kB
        Rick Hillegas
      26. derby-712-06-aa-restrictedDrops.diff
        12 kB
        Rick Hillegas
      27. SequenceGenerator.html
        24 kB
        Rick Hillegas

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.
          Hide
          Dag H. Wanvik added a comment -

          Thanks, Brett. I just did. It will take some time before the update is reflected on the web site, though.

          Show
          Dag H. Wanvik added a comment - Thanks, Brett. I just did. It will take some time before the update is reflected on the web site, though.
          Hide
          Brett Wooldridge added a comment -

          FAQ needs to be updated.

          Show
          Brett Wooldridge added a comment - FAQ needs to be updated.
          Hide
          Rick Hillegas added a comment -

          Resolving this issue. I believe that the work is done.

          Show
          Rick Hillegas added a comment - Resolving this issue. I believe that the work is done.
          Hide
          Rick Hillegas added a comment -

          Attaching rev 2 of the functional spec, with these changes:

          • Only allow REVOKE USAGE with RESTRICT.
          • Clarify restrictions on usage of NEXT VALUE FOR expressions.
          • Only allow RESTRICTed drops of sequences.

          More testing needs to be done, but I believe that the functionality described by this spec has been implemented.

          Show
          Rick Hillegas added a comment - Attaching rev 2 of the functional spec, with these changes: Only allow REVOKE USAGE with RESTRICT. Clarify restrictions on usage of NEXT VALUE FOR expressions. Only allow RESTRICTed drops of sequences. More testing needs to be done, but I believe that the functionality described by this spec has been implemented.
          Hide
          Rick Hillegas added a comment -

          Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 910255.

          Show
          Rick Hillegas added a comment - Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 910255.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-06-aa-restrictedDrops.diff. This patch limits DROP SEQUENCE to RESTRICTed drops. This makes the syntax conform to the current behavior. The DROP SEQUENCE statement raises an error if any triggers or views mention the sequence. This is NOT the behavior described by the functional spec. The spec called for CASCADEd drops. The spec will be revised after this syntax change is checked in. Tests passed cleanly for me.

          Touches the following files:

          ----------

          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj

          Require the RESTRICT keyword in DROP SEQUENCE statements.

          ----------

          M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java

          Added a comment noting the RESTRICTed behavior of sequence drops.

          ----------

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java
          M java/testing/org/apache/derbyTesting/junit/JDBC.java

          Alter the tests to reflect this syntax change.

          Show
          Rick Hillegas added a comment - Attaching derby-712-06-aa-restrictedDrops.diff. This patch limits DROP SEQUENCE to RESTRICTed drops. This makes the syntax conform to the current behavior. The DROP SEQUENCE statement raises an error if any triggers or views mention the sequence. This is NOT the behavior described by the functional spec. The spec called for CASCADEd drops. The spec will be revised after this syntax change is checked in. Tests passed cleanly for me. Touches the following files: ---------- M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Require the RESTRICT keyword in DROP SEQUENCE statements. ---------- M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java Added a comment noting the RESTRICTed behavior of sequence drops. ---------- M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTPermsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java M java/testing/org/apache/derbyTesting/junit/JDBC.java Alter the tests to reflect this syntax change.
          Hide
          Rick Hillegas added a comment -

          Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 907654.

          Show
          Rick Hillegas added a comment - Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 907654.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-05-al-sequenceGenerator.diff. This reworks the previous version of the patch, replacing a deadlock situation with a potential race-condition.

          The separation of functionality between SequenceUpdater and SequenceGenerator is now more clear:

          o SequenceUpdater handles handshaking with the catalog.

          o SequenceGenerator is just a calculator which does no i/o.

          This patch includes more extensive header comments for both classes,explaining how they are used. The basic logic flow is documented in in SequenceUpdater. Hopefully this is clear enough that other people will be able to reason about its correctness and spot flaws. Here is that header comment:

          ---------

          Here is the algorithm pursued when the caller asks for the next number in a sequence:

          o We try to get the next number from a cache of pre-allocated numbers. The endpoint (last number in the pre-allocated range) was previously recorded in the catalog row which describes this sequence. If we are successful in getting the next number, we return it and all is well.

          o Otherwise, we must allocate a new range by updating the catalog row. At this point we may find ourselves racing another session, which also needs the next number in the sequence.

          o When we try to update the catalog row, we check to see whether the current value there is what we expect it to be. If it is, then all is well: we update the catalog row then return to the first step to try to get the next number from the new cache of pre-allocated numbers.

          o If, however, the value in the catalog row is not what we expect, then another session has won the race to update the catalog. We accept this fact gracefully and do not touch the catalog. Instead, we return to the first step and try to get the next number from the new cache of numbers which the other session has just pre-allocated.

          o We only allow ourselves to retry this loop a small number of times. If we still can't get the next number in the sequence, we raise an exception complaining that there is too much contention on the generator.

          If applications start seeing exceptions complaining that there is too much contention on a sequence generator, then we should improve this algorithm. Here are some options based on the idea that contention should go down if we increase the number of pre-allocated numbers:

          o We can let the user change the size of the pre-allocated range.

          o Derby can increase the size of the pre-allocated range when Derby detects too much contention.

          -------

          Show
          Rick Hillegas added a comment - Attaching derby-712-05-al-sequenceGenerator.diff. This reworks the previous version of the patch, replacing a deadlock situation with a potential race-condition. The separation of functionality between SequenceUpdater and SequenceGenerator is now more clear: o SequenceUpdater handles handshaking with the catalog. o SequenceGenerator is just a calculator which does no i/o. This patch includes more extensive header comments for both classes,explaining how they are used. The basic logic flow is documented in in SequenceUpdater. Hopefully this is clear enough that other people will be able to reason about its correctness and spot flaws. Here is that header comment: --------- Here is the algorithm pursued when the caller asks for the next number in a sequence: o We try to get the next number from a cache of pre-allocated numbers. The endpoint (last number in the pre-allocated range) was previously recorded in the catalog row which describes this sequence. If we are successful in getting the next number, we return it and all is well. o Otherwise, we must allocate a new range by updating the catalog row. At this point we may find ourselves racing another session, which also needs the next number in the sequence. o When we try to update the catalog row, we check to see whether the current value there is what we expect it to be. If it is, then all is well: we update the catalog row then return to the first step to try to get the next number from the new cache of pre-allocated numbers. o If, however, the value in the catalog row is not what we expect, then another session has won the race to update the catalog. We accept this fact gracefully and do not touch the catalog. Instead, we return to the first step and try to get the next number from the new cache of numbers which the other session has just pre-allocated. o We only allow ourselves to retry this loop a small number of times. If we still can't get the next number in the sequence, we raise an exception complaining that there is too much contention on the generator. If applications start seeing exceptions complaining that there is too much contention on a sequence generator, then we should improve this algorithm. Here are some options based on the idea that contention should go down if we increase the number of pre-allocated numbers: o We can let the user change the size of the pre-allocated range. o Derby can increase the size of the pre-allocated range when Derby detects too much contention. -------
          Hide
          Rick Hillegas added a comment -

          Thanks again for the patch, Suran. I have committed it at subversion revision 905369. Along the way I also updated the canons for dblook_test and dblook_test_territory since this patch changes the results slightly. Thanks!

          Show
          Rick Hillegas added a comment - Thanks again for the patch, Suran. I have committed it at subversion revision 905369. Along the way I also updated the canons for dblook_test and dblook_test_territory since this patch changes the results slightly. Thanks!
          Hide
          Rick Hillegas added a comment -

          Thanks, Suran. The patch looks great. I am running tests now.

          Show
          Rick Hillegas added a comment - Thanks, Suran. The patch looks great. I am running tests now.
          Hide
          Suran Jayathilaka added a comment -

          I added SQLState codes 42XAC through 42XAG and corresponding messages. Also changed the bind time arg checks as suggested. Please review and comment.
          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - I added SQLState codes 42XAC through 42XAG and corresponding messages. Also changed the bind time arg checks as suggested. Please review and comment. Thanks! Suran
          Hide
          Knut Anders Hatlen added a comment -

          Of course I meant to say derby.language.sequenceGeneratorCacheSize...

          Show
          Knut Anders Hatlen added a comment - Of course I meant to say derby.language.sequenceGeneratorCacheSize...
          Hide
          Rick Hillegas added a comment -

          Thanks, Knut. I'm happy with your proposed property name. I'll make that change in the next rev of the patch.

          Show
          Rick Hillegas added a comment - Thanks, Knut. I'm happy with your proposed property name. I'll make that change in the next rev of the patch.
          Hide
          Knut Anders Hatlen added a comment -

          One small thing I forgot to mention is that the name of the property that controls the size of the sequence generator cache is a little cryptic. Should we call it derby.language.sequenceGeneratorCache instead?

          Show
          Knut Anders Hatlen added a comment - One small thing I forgot to mention is that the name of the property that controls the size of the sequence generator cache is a little cryptic. Should we call it derby.language.sequenceGeneratorCache instead?
          Hide
          Rick Hillegas added a comment -

          Thanks for the quick review, Knut. I will make the change you suggest. I think there is also an unchecked deadlock in this solution which arises if we have to update the catalog tuple in the execution transaction (not the subtransaction). That work happens inside the critical section and I think that the Java synchronization could deadlock with the Store lock. Need to think about how to tackle this. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for the quick review, Knut. I will make the change you suggest. I think there is also an unchecked deadlock in this solution which arises if we have to update the catalog tuple in the execution transaction (not the subtransaction). That work happens inside the critical section and I think that the Java synchronization could deadlock with the Store lock. Need to think about how to tackle this. Thanks.
          Hide
          Knut Anders Hatlen added a comment -

          I had a look at the 05-af patch, and the approach looks good to me (obligatory nit-pick: many lines exceed 80 characters).

          I'm not sure, though, if clearIdentity() is the right place to update the system tables to prevent holes in the sequences. Perhaps clean() is a better place. That method can more easily be called from checkpoint/shutdown, so that we don't get holes when rebooting the database. It also allows throwing exceptions, so that we don't need try/catch to suppress exceptions seen when updating the value on disk.

          Show
          Knut Anders Hatlen added a comment - I had a look at the 05-af patch, and the approach looks good to me (obligatory nit-pick: many lines exceed 80 characters). I'm not sure, though, if clearIdentity() is the right place to update the system tables to prevent holes in the sequences. Perhaps clean() is a better place. That method can more easily be called from checkpoint/shutdown, so that we don't get holes when rebooting the database. It also allows throwing exceptions, so that we don't need try/catch to suppress exceptions seen when updating the value on disk.
          Hide
          Rick Hillegas added a comment -

          Regression tests passed cleanly for me on derby-712-05-af-sequenceGenerator.diff.

          Show
          Rick Hillegas added a comment - Regression tests passed cleanly for me on derby-712-05-af-sequenceGenerator.diff.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-05-af-sequenceGenerator.diff. This patch adds cached sequence generators to the data dictionary and makes the NEXT VALUE FOR clause yield the expected results. Regression tests have passed for me but I am re-running them just to be safe.

          User-visible behavior changes as follows:

          A) When you issue a NEXT VALUE FOR clause, the value of the sequence generator really advances as expected.

          B) If you shutdown the database after using a sequence generator, the generator may leak up to 5 values. That is, when you reboot, the next value for the generator may be up to 5 steps beyond what you expected.

          Here is the approach I adopted:

          1) SequenceGenerator - This is a general class which manages ranges of sequence values. It advances a counter and hands out the next sequence value in a range. In addition, it calculates chunk sizes for sub-ranges which can be pre-allocated. Right now, pre-allocation is pretty dumb: we just pre-allocate 5 numbers at a time. In the future we could do something fancier. For instance, we could give the user a property for tuning the size of pre-allocated ranges, or Derby could auto-tune this number itself based on run-time behavior.

          2) SequenceUpdater - This is an abstract class. It is a Cacheable to be stored in the data dictionary. Concrete subclasses are responsible for particular types of sequences. For instance, there is an inner subclass which is responsible for ANSI/ISO sequence generators. In the future, we could add another inner subclass which is responsible for identity columns. There is also a testing subclass which I used to test boundary conditions related to the currently unimplemented optional clauses of the CREATE SEQUENCE statement. The SequenceUpdater does the following tasks:

          a) It calls its corresponding SequenceGenerator to calculate the next value in a sequence.

          b) It manages the updating of the system tables when a pre-allocated range needs to be recorded on disk.

          c) It reclaims unused pre-allocated values when the data dictionary caches have to be flushed. This prevents us from leaking values when DDL is performed. In the future, similar reclamation could be performed during the orderly shutdown of a database. This probably requires some discussion.

          There are some tricky bits in the implementation:

          i) There are a couple synchronized methods in SequenceGenerator. It is my hope that these critical sections are short.

          ii) The on-disk catalog value (in this case, SYS.SYSSEQUENCES.CURRENTVALUE) is updated in a read/write subtransaction of the session's execution transaction if possible--and that subtransaction is then immediately committed. However, if it is not possible to obtain an immediate lock on the catalog row, we fall back to updating the row in the parent transaction, that is, in the session's execution transaction. This is similar to what is done for identity columns today.

          iii) Updating the value in the catalog row is managed by a little hopping back and forth among the DataDictionary, the SequenceUpdater, and the SequenceGenerator. Here's what the flow of control looks like:

          o An Activation asks the DataDictionary for the next value in a sequence.

          o The DataDictionary forwards the request to the SequenceUpdater.

          o The SequenceUpdater forwards the request to the SequenceGenerator.

          o If the SequenceGenerator determines that a new range needs to be allocated, it calculates the new endpoint of the range. Then the SequenceGenerator asks the SequenceUpdater to allocate the new range.

          o The SequenceUpdater forwards that request to the DataDictionary.

          o The DataDictionary updates the catalog row.

          o Everyone returns and the Activation gets the value it asked for.

          Touches the following files:

          -------

          A java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java

          The SequenceGenerator described in (1) above.

          -------

          A java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java

          The SequenceUpdater described in (2) above.

          -------

          M java/engine/org/apache/derby/iapi/reference/Property.java
          M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java

          DataDictionary support for cached sequence generators.

          -------

          M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java
          M java/engine/org/apache/derby/impl/sql/catalog/SYSSEQUENCESRowFactory.java
          M java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java
          M java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java

          A null in SYS.SYSSEQUENCES.CURRENTVALUE now means that a non-cycling sequence generator is exhausted. That is, it has run through its range of legal values.

          -------

          M java/engine/org/apache/derby/loc/messages.xml
          M java/shared/org/apache/derby/shared/common/reference/SQLState.java

          New error message for exhausted sequence generators.

          -------

          M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java

          Code-generation for the NEXT VALUE FOR clause.

          -------

          M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java

          Run-time support for the NEXT VALUE FOR clause.

          -------

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SGVetter.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java
          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java

          Regression tests for cached sequence generators. SGVetter is a re-implementation of sequence generation used to test the correctness of the production SequenceGenerator. SGVetter is less efficient than SequenceGenerator but it is easier to reason about its correctness.

          -------

          Show
          Rick Hillegas added a comment - Attaching derby-712-05-af-sequenceGenerator.diff. This patch adds cached sequence generators to the data dictionary and makes the NEXT VALUE FOR clause yield the expected results. Regression tests have passed for me but I am re-running them just to be safe. User-visible behavior changes as follows: A) When you issue a NEXT VALUE FOR clause, the value of the sequence generator really advances as expected. B) If you shutdown the database after using a sequence generator, the generator may leak up to 5 values. That is, when you reboot, the next value for the generator may be up to 5 steps beyond what you expected. Here is the approach I adopted: 1) SequenceGenerator - This is a general class which manages ranges of sequence values. It advances a counter and hands out the next sequence value in a range. In addition, it calculates chunk sizes for sub-ranges which can be pre-allocated. Right now, pre-allocation is pretty dumb: we just pre-allocate 5 numbers at a time. In the future we could do something fancier. For instance, we could give the user a property for tuning the size of pre-allocated ranges, or Derby could auto-tune this number itself based on run-time behavior. 2) SequenceUpdater - This is an abstract class. It is a Cacheable to be stored in the data dictionary. Concrete subclasses are responsible for particular types of sequences. For instance, there is an inner subclass which is responsible for ANSI/ISO sequence generators. In the future, we could add another inner subclass which is responsible for identity columns. There is also a testing subclass which I used to test boundary conditions related to the currently unimplemented optional clauses of the CREATE SEQUENCE statement. The SequenceUpdater does the following tasks: a) It calls its corresponding SequenceGenerator to calculate the next value in a sequence. b) It manages the updating of the system tables when a pre-allocated range needs to be recorded on disk. c) It reclaims unused pre-allocated values when the data dictionary caches have to be flushed. This prevents us from leaking values when DDL is performed. In the future, similar reclamation could be performed during the orderly shutdown of a database. This probably requires some discussion. There are some tricky bits in the implementation: i) There are a couple synchronized methods in SequenceGenerator. It is my hope that these critical sections are short. ii) The on-disk catalog value (in this case, SYS.SYSSEQUENCES.CURRENTVALUE) is updated in a read/write subtransaction of the session's execution transaction if possible--and that subtransaction is then immediately committed. However, if it is not possible to obtain an immediate lock on the catalog row, we fall back to updating the row in the parent transaction, that is, in the session's execution transaction. This is similar to what is done for identity columns today. iii) Updating the value in the catalog row is managed by a little hopping back and forth among the DataDictionary, the SequenceUpdater, and the SequenceGenerator. Here's what the flow of control looks like: o An Activation asks the DataDictionary for the next value in a sequence. o The DataDictionary forwards the request to the SequenceUpdater. o The SequenceUpdater forwards the request to the SequenceGenerator. o If the SequenceGenerator determines that a new range needs to be allocated, it calculates the new endpoint of the range. Then the SequenceGenerator asks the SequenceUpdater to allocate the new range. o The SequenceUpdater forwards that request to the DataDictionary. o The DataDictionary updates the catalog row. o Everyone returns and the Activation gets the value it asked for. Touches the following files: ------- A java/engine/org/apache/derby/impl/sql/catalog/SequenceGenerator.java The SequenceGenerator described in (1) above. ------- A java/engine/org/apache/derby/impl/sql/catalog/SequenceUpdater.java The SequenceUpdater described in (2) above. ------- M java/engine/org/apache/derby/iapi/reference/Property.java M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java DataDictionary support for cached sequence generators. ------- M java/engine/org/apache/derby/iapi/sql/dictionary/SequenceDescriptor.java M java/engine/org/apache/derby/impl/sql/catalog/SYSSEQUENCESRowFactory.java M java/engine/org/apache/derby/iapi/sql/dictionary/DataDescriptorGenerator.java M java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java A null in SYS.SYSSEQUENCES.CURRENTVALUE now means that a non-cycling sequence generator is exhausted. That is, it has run through its range of legal values. ------- M java/engine/org/apache/derby/loc/messages.xml M java/shared/org/apache/derby/shared/common/reference/SQLState.java New error message for exhausted sequence generators. ------- M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java Code-generation for the NEXT VALUE FOR clause. ------- M java/engine/org/apache/derby/impl/sql/execute/BaseActivation.java Run-time support for the NEXT VALUE FOR clause. ------- A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SGVetter.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/UDTTest.java A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceGeneratorTest.java Regression tests for cached sequence generators. SGVetter is a re-implementation of sequence generation used to test the correctness of the production SequenceGenerator. SGVetter is less efficient than SequenceGenerator but it is easier to reason about its correctness. -------
          Hide
          Rick Hillegas added a comment -

          Thanks for the new patch, Suran. It looks very good. First some responses to your questions:

          >What is the correct SQLState code to be used for Exception messages in cases like the INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios.

          You will need to create some new codes in org.apache.derby.shared.common.reference.SQLState and some corresponding new messages in trunk/java/engine/org/apache/derby/loc/messages.xml

          For the codes themselves, I can't find any guidance in the ANSI/ISO standard beyond the general rule that SQLStates for syntax errors should begin with 42. I see that there is an empty range of Derby SQLStates starting with 42XAC. I recommend that you allocate your codes out of that range.

          >Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both are present. Is this a valid constraint or does the parser code need to be modified?

          You are right, Derby is being over-strict here. The ANSI/ISO standard allows the optional clauses to appear in any order. The only restriction is that a given option can be specified at most once. You are welcome to relax Derby's restrictions here. You may want to use the CREATE FUNCTION/PROCEDURE syntax as a template for how to do this. However, I would recommend doing this work in another patch after you have finished the bind() logic for the optional clauses.

          Concerning the patch:

          In CreateSequenceNode, I like the way that you put the defaulting logic in the initializer and left validation to the bind() stage.

          As noted above, you will want to create specific error messages for the validation checks you perform in CreateSequenceNode.bindStatement(). The generic error messages don't give the user enough information to figure out what the problem is.

          It seems to me that you only need to compare max, min, and initial values in one place, rather than duplicating this check for each datatype.

          Thanks!

          Show
          Rick Hillegas added a comment - Thanks for the new patch, Suran. It looks very good. First some responses to your questions: >What is the correct SQLState code to be used for Exception messages in cases like the INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios. You will need to create some new codes in org.apache.derby.shared.common.reference.SQLState and some corresponding new messages in trunk/java/engine/org/apache/derby/loc/messages.xml For the codes themselves, I can't find any guidance in the ANSI/ISO standard beyond the general rule that SQLStates for syntax errors should begin with 42. I see that there is an empty range of Derby SQLStates starting with 42XAC. I recommend that you allocate your codes out of that range. >Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both are present. Is this a valid constraint or does the parser code need to be modified? You are right, Derby is being over-strict here. The ANSI/ISO standard allows the optional clauses to appear in any order. The only restriction is that a given option can be specified at most once. You are welcome to relax Derby's restrictions here. You may want to use the CREATE FUNCTION/PROCEDURE syntax as a template for how to do this. However, I would recommend doing this work in another patch after you have finished the bind() logic for the optional clauses. Concerning the patch: In CreateSequenceNode, I like the way that you put the defaulting logic in the initializer and left validation to the bind() stage. As noted above, you will want to create specific error messages for the validation checks you perform in CreateSequenceNode.bindStatement(). The generic error messages don't give the user enough information to figure out what the problem is. It seems to me that you only need to compare max, min, and initial values in one place, rather than duplicating this check for each datatype. Thanks!
          Hide
          Suran Jayathilaka added a comment -

          Reattaching patch, as previous attachment was excluded from ASF license by mistake.

          Show
          Suran Jayathilaka added a comment - Reattaching patch, as previous attachment was excluded from ASF license by mistake.
          Hide
          Suran Jayathilaka added a comment -

          This draft patch contains the binding changes for the optional clauses of Sequence creation. I've added some tests to SequenceTests but there are more creation scenarios that need to be covered. Please review and comment.

          What is the correct SQLState code to be used for Exception messages in cases like the INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios.

          Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both are present. Is this a valid constraint or does the parser code need to be modified?

          Show
          Suran Jayathilaka added a comment - This draft patch contains the binding changes for the optional clauses of Sequence creation. I've added some tests to SequenceTests but there are more creation scenarios that need to be covered. Please review and comment. What is the correct SQLState code to be used for Exception messages in cases like the INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios. Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both are present. Is this a valid constraint or does the parser code need to be modified?
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-04-aa-dblook.diff. This adds dblook support for sequences. This includes generating the privilege ddl. The regression tests passed cleanly for me. Committed at subversion revision 898123.

          Touches the following files:

          M java/tools/org/apache/derby/tools/dblook.java
          A java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java
          M java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java

          Logic to recreate the CREATE SEQUENCE statements.

          M java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java

          Logic to recreate the GRANT USAGE ON SEQUENCE statements.

          M java/tools/org/apache/derby/loc/toolsmessages.properties

          Banners for the sequences ddl and privileges.

          M java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java
          M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
          M java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java

          In reconstructing generic CREATE SEQUENCE ddl, I needed to generate the optional clauses of that statement. I added parser support for the optional clauses. However, at bind-time, we raise an exception for anything but the vanilla CREATE SEQUENCE statement. While I was in there, I amended the creation of the sequence tuple so that the default start value is the smallest (most negative) integer. This is what the spec says should be done.

          M java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql
          M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out
          M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out

          Heartbeat test for recreating sequence ddl.

          Show
          Rick Hillegas added a comment - Attaching derby-712-04-aa-dblook.diff. This adds dblook support for sequences. This includes generating the privilege ddl. The regression tests passed cleanly for me. Committed at subversion revision 898123. Touches the following files: M java/tools/org/apache/derby/tools/dblook.java A java/tools/org/apache/derby/impl/tools/dblook/DB_Sequence.java M java/tools/org/apache/derby/impl/tools/dblook/DB_Alias.java Logic to recreate the CREATE SEQUENCE statements. M java/tools/org/apache/derby/impl/tools/dblook/DB_GrantRevoke.java Logic to recreate the GRANT USAGE ON SEQUENCE statements. M java/tools/org/apache/derby/loc/toolsmessages.properties Banners for the sequences ddl and privileges. M java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj M java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java In reconstructing generic CREATE SEQUENCE ddl, I needed to generate the optional clauses of that statement. I added parser support for the optional clauses. However, at bind-time, we raise an exception for anything but the vanilla CREATE SEQUENCE statement. While I was in there, I amended the creation of the sequence tuple so that the default start value is the smallest (most negative) integer. This is what the spec says should be done. M java/testing/org/apache/derbyTesting/functionTests/tests/tools/dblook_makeDB_2.sql M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out M java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Heartbeat test for recreating sequence ddl.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-03-ac-usagePrivilege.diff. This improves the unit test cleanup logic to prevent it from trying to drop sequences when the server hasn't been upgraded to 10.6. This version passes the regression tests cleanly. Committed at subversion revision 896883.

          Show
          Rick Hillegas added a comment - Attaching derby-712-03-ac-usagePrivilege.diff. This improves the unit test cleanup logic to prevent it from trying to drop sequences when the server hasn't been upgraded to 10.6. This version passes the regression tests cleanly. Committed at subversion revision 896883.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-03-aa-usagePrivilege.diff. This patch adds support for the USAGE privilege on sequences. While I was in there, I also made it illegal to drop a schema that contains sequences. I am running regression tests now.

          Touches the following files:

          M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java
          M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java

          Wire in checking for the USAGE privilege.

          M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

          Fixed a bug which prevented us from detecting that a schema has sequences in it.

          A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

          Regression tests for USAGE privilege on sequences.

          M java/testing/org/apache/derbyTesting/junit/JDBC.java

          Made the CleanDatabaseTestSetup decorator drop sequences too when it is dropping schema objects.

          Show
          Rick Hillegas added a comment - Attaching derby-712-03-aa-usagePrivilege.diff. This patch adds support for the USAGE privilege on sequences. While I was in there, I also made it illegal to drop a schema that contains sequences. I am running regression tests now. Touches the following files: M java/engine/org/apache/derby/impl/sql/compile/NextSequenceNode.java M java/engine/org/apache/derby/impl/sql/execute/DDLConstantAction.java Wire in checking for the USAGE privilege. M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java Fixed a bug which prevented us from detecting that a schema has sequences in it. A java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequencePermsTest.java M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Regression tests for USAGE privilege on sequences. M java/testing/org/apache/derbyTesting/junit/JDBC.java Made the CleanDatabaseTestSetup decorator drop sequences too when it is dropping schema objects.
          Hide
          Knut Anders Hatlen added a comment -

          Looks like NextSequenceNode.java was forgotten in the previous commit. Added that file and committed revision 894146.

          Show
          Knut Anders Hatlen added a comment - Looks like NextSequenceNode.java was forgotten in the previous commit. Added that file and committed revision 894146.
          Hide
          Knut Anders Hatlen added a comment -

          After the last commit, derby.jar contains no classes at all when I build in my environment. The same thing seems to have happened in the Tinderbox: http://dbtg.foundry.sun.com/derby/test/tinderbox_trunk16/jvm1.6/testing/Limited/testSummary-894002.html

          Show
          Knut Anders Hatlen added a comment - After the last commit, derby.jar contains no classes at all when I build in my environment. The same thing seems to have happened in the Tinderbox: http://dbtg.foundry.sun.com/derby/test/tinderbox_trunk16/jvm1.6/testing/Limited/testSummary-894002.html
          Hide
          Rick Hillegas added a comment -

          Thanks Suran. The patch looks good. I made a couple changes:

          1) I fixed DBMSNodes.properties. The addition of the NextSequenceNode to the jar file pushed the DropSequenceNode out of the jar file.

          2) I changed the test case for NEXT VALUE FOR to call execute() rather than executeUpdate(). The latter call kills the statement.

          With these changes, the regression tests passed cleanly for me. Committed at subversion revision 894002. Thanks!

          Show
          Rick Hillegas added a comment - Thanks Suran. The patch looks good. I made a couple changes: 1) I fixed DBMSNodes.properties. The addition of the NextSequenceNode to the jar file pushed the DropSequenceNode out of the jar file. 2) I changed the test case for NEXT VALUE FOR to call execute() rather than executeUpdate(). The latter call kills the statement. With these changes, the regression tests passed cleanly for me. Committed at subversion revision 894002. Thanks!
          Hide
          Suran Jayathilaka added a comment -

          First patch for NEXT VALUE implementation.
          TODO : Implement actual logic for getting the next value for a sequence, replacing the constant returning logic in this patch.

          Show
          Suran Jayathilaka added a comment - First patch for NEXT VALUE implementation. TODO : Implement actual logic for getting the next value for a sequence, replacing the constant returning logic in this patch.
          Hide
          Rick Hillegas added a comment -

          Thanks, Kristian. Added the sanity manager guard in subversion revision 888421.

          Show
          Rick Hillegas added a comment - Thanks, Kristian. Added the sanity manager guard in subversion revision 888421.
          Hide
          Kristian Waagan added a comment -

          Patch 'derby-712-02-aa-privilegeNodeCleanup.diff' introduces a SanityManager.THROWASSERT which isn't guarded by an "if (SanityManager.DEBUG)". This causes the SanityManager class to be included in the insane builds, which again chokes maven2/SetDerbyVersion.java (I just committed it).

          From PrivilegeNode:
          +
          + default:
          + SanityManager.THROWASSERT( "Invalid privilege objectType: " + this.objectType);

          Show
          Kristian Waagan added a comment - Patch 'derby-712-02-aa-privilegeNodeCleanup.diff' introduces a SanityManager.THROWASSERT which isn't guarded by an "if (SanityManager.DEBUG)". This causes the SanityManager class to be included in the insane builds, which again chokes maven2/SetDerbyVersion.java (I just committed it). From PrivilegeNode: + + default: + SanityManager.THROWASSERT( "Invalid privilege objectType: " + this.objectType);
          Hide
          Rick Hillegas added a comment -

          Tests passed cleanly. Committed derby-712-02-aa-privilegeNodeCleanup.diff at subversion revision 888060.

          Show
          Rick Hillegas added a comment - Tests passed cleanly. Committed derby-712-02-aa-privilegeNodeCleanup.diff at subversion revision 888060.
          Hide
          Rick Hillegas added a comment -

          Attaching derby-712-02-aa-privilegeNodeCleanup.diff. This slightly reworks the code in PrivilegeNode so that it will be easier to add the USAGE privilege. GrantRevokeDDLTest runs cleanly. I intend to commit this patch if the full regression tests pass.

          This patch makes the following changes:

          1) Abstracts out the notion of a qualified objectName, which is common to all privileges.

          2) Moves the self-revoke check outside the individual bind() cases so that it doesn't have to be repeated for each case.

          3) Adds braces around dangling conditional consequences because, in my experience, unbraced single-line consequences are a brittle source of bugs.

          Touches the following file:

          M java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java

          Show
          Rick Hillegas added a comment - Attaching derby-712-02-aa-privilegeNodeCleanup.diff. This slightly reworks the code in PrivilegeNode so that it will be easier to add the USAGE privilege. GrantRevokeDDLTest runs cleanly. I intend to commit this patch if the full regression tests pass. This patch makes the following changes: 1) Abstracts out the notion of a qualified objectName, which is common to all privileges. 2) Moves the self-revoke check outside the individual bind() cases so that it doesn't have to be repeated for each case. 3) Adds braces around dangling conditional consequences because, in my experience, unbraced single-line consequences are a brittle source of bugs. Touches the following file: M java/engine/org/apache/derby/impl/sql/compile/PrivilegeNode.java
          Hide
          Suran Jayathilaka added a comment -

          Sequence NEXT VALUE implementation work Q & A:
          Thanks to Rick Hillegas for answers.

          1. How does escapedValueFunction work in valueExpressionPrimary?
          If you run the "javadoc" ant target, you can browse a nice summary of the grammar in
          javadoc/language/sqlgrammar.html
          There you will see that escapedValueFunction covers invocations of Derby-supplied functions. These can be aliased as JDBC escaped functions. These in turn are functions whose api the JDBC spec tries to standardize--most databases support these functions but the SQL committee never got around to standardizing the apis.

          2. In fact, what is the purpose of the valueExpressionPrimary() method?
          In general, the Derby grammar does a pretty good job of following the grammar laid out in part 2 of the SQL Standard. In this case, valueExpressionPrimary() is trying to model the <value expression primary> clause in section 6.3 of the Standard (you can find the Standard here: http://www.wiscorp.com/SQLStandards.html )

          3. How can I add NextSequenceNode as an option here?
          As you can see from the Standard grammar, <next value expression> is a kind of <value expression primary> and lives in the grammar parallel to <column reference> and <cast specification>. So I would recommend adding nextValueExpression() as another option inside valueExpressionPrimary().

          Show
          Suran Jayathilaka added a comment - Sequence NEXT VALUE implementation work Q & A: Thanks to Rick Hillegas for answers. 1. How does escapedValueFunction work in valueExpressionPrimary? If you run the "javadoc" ant target, you can browse a nice summary of the grammar in javadoc/language/sqlgrammar.html There you will see that escapedValueFunction covers invocations of Derby-supplied functions. These can be aliased as JDBC escaped functions. These in turn are functions whose api the JDBC spec tries to standardize--most databases support these functions but the SQL committee never got around to standardizing the apis. 2. In fact, what is the purpose of the valueExpressionPrimary() method? In general, the Derby grammar does a pretty good job of following the grammar laid out in part 2 of the SQL Standard. In this case, valueExpressionPrimary() is trying to model the <value expression primary> clause in section 6.3 of the Standard (you can find the Standard here: http://www.wiscorp.com/SQLStandards.html ) 3. How can I add NextSequenceNode as an option here? As you can see from the Standard grammar, <next value expression> is a kind of <value expression primary> and lives in the grammar parallel to <column reference> and <cast specification>. So I would recommend adding nextValueExpression() as another option inside valueExpressionPrimary().
          Hide
          Rick Hillegas added a comment -

          Thanks again for the patch, Suran. The tests passed cleanly for me. Committed create_drop_sequence_d.patch at subversion revision 810151 (note: I made that small change to DBMSnodes.properties).

          Show
          Rick Hillegas added a comment - Thanks again for the patch, Suran. The tests passed cleanly for me. Committed create_drop_sequence_d.patch at subversion revision 810151 (note: I made that small change to DBMSnodes.properties).
          Hide
          Rick Hillegas added a comment -

          Thanks for the new patch, Suran.

          > For now, is it correct to only invalidate for DependencyManager.USER_RECOMPILE_REQUEST and to add actions for DependencyManager.REVOKE_PRIVILEGE etc. after GRANT/REVOKE functionality has been added? If this revision looks ok I will start running the regression tests.

          Sounds good to me.

          > After enabling authentication for SequenceTest, the current tests run fine. What other test scenarios should be added to this test class?

          I think the tests you have now are adequate. As you add more functionality, there will be more to test.

          By the way, the test failed for me when I ran it against jar files. That is because a ClassNotFoundException was being raised when the engine tried to fault in CreateSequenceNode. That, in turn, was because the same property name was being used for CreateSequenceNode and DropSequenceNode in DBMSnodes.properties. The test ran cleanly for me after I made the following change to DBMSnodes.properties:

          -derby.module.cloudscapenodes.gw=org.apache.derby.impl.sql.compile.DropSequenceNode
          +derby.module.cloudscapenodes.gx=org.apache.derby.impl.sql.compile.DropSequenceNode

          I am running the full regression tests now. Thanks.

          Show
          Rick Hillegas added a comment - Thanks for the new patch, Suran. > For now, is it correct to only invalidate for DependencyManager.USER_RECOMPILE_REQUEST and to add actions for DependencyManager.REVOKE_PRIVILEGE etc. after GRANT/REVOKE functionality has been added? If this revision looks ok I will start running the regression tests. Sounds good to me. > After enabling authentication for SequenceTest, the current tests run fine. What other test scenarios should be added to this test class? I think the tests you have now are adequate. As you add more functionality, there will be more to test. By the way, the test failed for me when I ran it against jar files. That is because a ClassNotFoundException was being raised when the engine tried to fault in CreateSequenceNode. That, in turn, was because the same property name was being used for CreateSequenceNode and DropSequenceNode in DBMSnodes.properties. The test ran cleanly for me after I made the following change to DBMSnodes.properties: -derby.module.cloudscapenodes.gw=org.apache.derby.impl.sql.compile.DropSequenceNode +derby.module.cloudscapenodes.gx=org.apache.derby.impl.sql.compile.DropSequenceNode I am running the full regression tests now. Thanks.
          Hide
          Suran Jayathilaka added a comment -

          This revision adds the suggested changes.

          When I made SequenceDescriptor implement the Dependent interface to be able to call dm.clearDependencies() in the drop() method, I added the other required methods in the interface.
          For now, is it correct to only invalidate for DependencyManager.USER_RECOMPILE_REQUEST and to add actions for DependencyManager.REVOKE_PRIVILEGE etc. after GRANT/REVOKE functionality has been added? If this revision looks ok I will start running the regression tests.

          After enabling authentication for SequenceTest, the current tests run fine. What other test scenarios should be added to this test class?

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - This revision adds the suggested changes. When I made SequenceDescriptor implement the Dependent interface to be able to call dm.clearDependencies() in the drop() method, I added the other required methods in the interface. For now, is it correct to only invalidate for DependencyManager.USER_RECOMPILE_REQUEST and to add actions for DependencyManager.REVOKE_PRIVILEGE etc. after GRANT/REVOKE functionality has been added? If this revision looks ok I will start running the regression tests. After enabling authentication for SequenceTest, the current tests run fine. What other test scenarios should be added to this test class? Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the new create_drop_seequence_c.patch. I have a couple comments:

          SequenceDescriptor.drop() - I would also add a call to dm.clearDependencies() as is done in the corresponding code in TriggerDescriptor.

          org.apache.derbyTesting.functionTests.tests.lang._Suite.suite() - This method still needs to add SequenceTest to its list of tests. This is what wires SequenceTest into the regression tests so that it is not just a standalone test.

          SequenceTest - It does not appear to me that this test is running with authentication turned on. Take a look at GeneratedColumnsPermsTest for an example of how to do this. You should be able to get user-specific connections by calling openUserConnection() rather than by calling DriverManager.getConnection(). Once you have made this change, it will be interesting to see what happens to the test cases you have commented out. This will also be a good foundation for adding GRANT/REVOKE tests when you get to that part of the project.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the new create_drop_seequence_c.patch. I have a couple comments: SequenceDescriptor.drop() - I would also add a call to dm.clearDependencies() as is done in the corresponding code in TriggerDescriptor. org.apache.derbyTesting.functionTests.tests.lang._Suite.suite() - This method still needs to add SequenceTest to its list of tests. This is what wires SequenceTest into the regression tests so that it is not just a standalone test. SequenceTest - It does not appear to me that this test is running with authentication turned on. Take a look at GeneratedColumnsPermsTest for an example of how to do this. You should be able to get user-specific connections by calling openUserConnection() rather than by calling DriverManager.getConnection(). Once you have made this change, it will be interesting to see what happens to the test cases you have commented out. This will also be a good foundation for adding GRANT/REVOKE tests when you get to that part of the project. Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          After the changes, I ran the SQL script and it execute successfuly. Following is the output I got
          -------------------------------------
          ij version 10.6
          ij> connect 'jdbc:derby:memory:patient;create=true;user=admin;password=adminpassword' as admin_conn;
          ij> connect 'jdbc:derby:memory:patient;user=alice;password=alicepassword' as alice_conn;
          ij(ALICE_CONN)> – should implicitly create the ALICE schema
          create sequence alice_seq;
          0 rows inserted/updated/deleted
          ij(ALICE_CONN)> – should fail because the sequence already exists
          create sequence alice_seq;
          ERROR X0Y68: Sequence 'ALICE_SEQ' already exists.
          ij(ALICE_CONN)> connect 'jdbc:derby:memory:patient;user=ruth;password=ruthpassword' as ruth_conn;
          ij(RUTH_CONN)> – should not be able to drop someone else's objects
          drop sequence alice.alice_seq;
          ERROR 42507: User 'RUTH' can not perform the operation in schema 'ALICE'.
          ij(RUTH_CONN)> – should not be able to create objects in someone else's schema
          create sequence alice.ruth_seq;
          ERROR 42507: User 'RUTH' can not perform the operation in schema 'ALICE'.
          ij(RUTH_CONN)> set connection alice_conn;
          ij(ALICE_CONN)> --should succeed
          drop sequence alice_seq;
          0 rows inserted/updated/deleted
          ij(ALICE_CONN)> – should succeed
          create sequence alice_seq;
          0 rows inserted/updated/deleted
          ij(ALICE_CONN)>
          ----------------------------
          When recreating these actions in JUnit, I found that testDropOtherSchemaSequence() and testCreateOtherSchemaSequence() tests take a long time to finish and they fail. I'm not sure if I'm taking the right way for creating different user connections. Theses two tests are commented out in the testcase.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - After the changes, I ran the SQL script and it execute successfuly. Following is the output I got ------------------------------------- ij version 10.6 ij> connect 'jdbc:derby:memory:patient;create=true;user=admin;password=adminpassword' as admin_conn; ij> connect 'jdbc:derby:memory:patient;user=alice;password=alicepassword' as alice_conn; ij(ALICE_CONN)> – should implicitly create the ALICE schema create sequence alice_seq; 0 rows inserted/updated/deleted ij(ALICE_CONN)> – should fail because the sequence already exists create sequence alice_seq; ERROR X0Y68: Sequence 'ALICE_SEQ' already exists. ij(ALICE_CONN)> connect 'jdbc:derby:memory:patient;user=ruth;password=ruthpassword' as ruth_conn; ij(RUTH_CONN)> – should not be able to drop someone else's objects drop sequence alice.alice_seq; ERROR 42507: User 'RUTH' can not perform the operation in schema 'ALICE'. ij(RUTH_CONN)> – should not be able to create objects in someone else's schema create sequence alice.ruth_seq; ERROR 42507: User 'RUTH' can not perform the operation in schema 'ALICE'. ij(RUTH_CONN)> set connection alice_conn; ij(ALICE_CONN)> --should succeed drop sequence alice_seq; 0 rows inserted/updated/deleted ij(ALICE_CONN)> – should succeed create sequence alice_seq; 0 rows inserted/updated/deleted ij(ALICE_CONN)> ---------------------------- When recreating these actions in JUnit, I found that testDropOtherSchemaSequence() and testCreateOtherSchemaSequence() tests take a long time to finish and they fail. I'm not sure if I'm taking the right way for creating different user connections. Theses two tests are commented out in the testcase. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          As you noted, you'll need to move on to the next task after you are done with the machinery for basic CREATE/DROP SEQUENCE. I think that the best next step would be to build the machinery for actually using sequences in statements. I think it will be easier if you do this first and then come back later to add the GRANT/REVOKE USAGE ON SEQUENCE machinery, which restricts who can use a given sequence.

          So I recommened starting out allowing anybody to use a NEXT VALUE FOR <sequence> right now. At the same time, link DERBY-712 to a new JIRA for adding the GRANT/REVOKE machinery.

          The semantics of the NEXT VALUE clause are:

          i) It can appear in scalar expressions.

          ii) It is forbidden in certain contexts (see the section in the functional spec titled "New Syntax for Using Sequences").

          You will need to make the following changes, described below. To speed up review, you may want to chunk in these changes in this order:

          A) Parser changes.

          B) Binding changes.

          C) Code generation changes.

          D) Execution changes.

          (A) Parser Changes

          In the AST, I think you will want to represent NEXT VALUE FOR <sequence> with a new NextSequenceNode, a kind of ValueNode. I think you will get into the least trouble if you follow the ANSI grammar as closely as possible. According to part 2, section 6.3 of the ANSI spec, a <next value expression> is one of the possibilities for a <value expression primary>. So I would make NextSequenceNode one of the possibilities for valueExpressionPrimary() in sqlgrammar.

          (B) Binding Changes

          When binding a NextSequenceNode, you will want to enforce the restrictions in the spec section titled "New Syntax for Using Sequences". I think this will be easiest if you push/pop and inspect restriction contexts using the CompilerContext.getReliability()/setReliability() machinery. You can use the GENERATION_CLAUSE_RESTRICTION logic as a template.

          (C) Code Generation and Execution Changes

          Fortunately, sequences are a lot like autoincrement columns. I think you will want a SequenceCounter object which is a lot like an AutoincrementCounter. I think it's fine to let GenericLanguageConnectionContext manage a cache of SequenceCounters.

          A big difference between sequences and autoincrement counters is this: multiple NEXT SEQUENCE FOR <sequence> clauses in a single statement should resolve to the same value, whereas multiple rows in a single INSERT statement will each get their own unique autoincrement value. I think this means that you will want to cache the NEXT SEQUENCE value in the statement's Activation.

          That, in turn, means that when you generate() the executable code, you will want to generate a call to some new method on the Activation rather than directly to a method on the LanguageConnectionContext.

          This is just a sketch of what makes sense to me. You will probably have a lot of questions and after looking into the code, you may have some better ideas.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, As you noted, you'll need to move on to the next task after you are done with the machinery for basic CREATE/DROP SEQUENCE. I think that the best next step would be to build the machinery for actually using sequences in statements. I think it will be easier if you do this first and then come back later to add the GRANT/REVOKE USAGE ON SEQUENCE machinery, which restricts who can use a given sequence. So I recommened starting out allowing anybody to use a NEXT VALUE FOR <sequence> right now. At the same time, link DERBY-712 to a new JIRA for adding the GRANT/REVOKE machinery. The semantics of the NEXT VALUE clause are: i) It can appear in scalar expressions. ii) It is forbidden in certain contexts (see the section in the functional spec titled "New Syntax for Using Sequences"). You will need to make the following changes, described below. To speed up review, you may want to chunk in these changes in this order: A) Parser changes. B) Binding changes. C) Code generation changes. D) Execution changes. (A) Parser Changes In the AST, I think you will want to represent NEXT VALUE FOR <sequence> with a new NextSequenceNode, a kind of ValueNode. I think you will get into the least trouble if you follow the ANSI grammar as closely as possible. According to part 2, section 6.3 of the ANSI spec, a <next value expression> is one of the possibilities for a <value expression primary>. So I would make NextSequenceNode one of the possibilities for valueExpressionPrimary() in sqlgrammar. (B) Binding Changes When binding a NextSequenceNode, you will want to enforce the restrictions in the spec section titled "New Syntax for Using Sequences". I think this will be easiest if you push/pop and inspect restriction contexts using the CompilerContext.getReliability()/setReliability() machinery. You can use the GENERATION_CLAUSE_RESTRICTION logic as a template. (C) Code Generation and Execution Changes Fortunately, sequences are a lot like autoincrement columns. I think you will want a SequenceCounter object which is a lot like an AutoincrementCounter. I think it's fine to let GenericLanguageConnectionContext manage a cache of SequenceCounters. A big difference between sequences and autoincrement counters is this: multiple NEXT SEQUENCE FOR <sequence> clauses in a single statement should resolve to the same value, whereas multiple rows in a single INSERT statement will each get their own unique autoincrement value. I think this means that you will want to cache the NEXT SEQUENCE value in the statement's Activation. That, in turn, means that when you generate() the executable code, you will want to generate a call to some new method on the Activation rather than directly to a method on the LanguageConnectionContext. This is just a sketch of what makes sense to me. You will probably have a lot of questions and after looking into the code, you may have some better ideas. Thanks! -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the additional work on the patch. I think it's very close to being ready for a regression test run and checkin.

          I ran some more experiments and it seems that we need more work on the bind() logic for CREATE SEQUENCE. In particular, I noticed that we were not implicitly creating the schema if the sequence is the first object in it. We do implicitly create the schema for other objects. I wanted to get the following script to run cleanly and verify the correct behavior of various aspects of sequence creation. I noticed that the first CREATE SEQUENCE statement failed unexpectedly.

          connect 'jdbc:derby:memory:patient;create=true;user=admin;password=adminpassword' as admin_conn;

          connect 'jdbc:derby:memory:patient;user=alice;password=alicepassword' as alice_conn;

          – should implicitly create the ALICE schema
          create sequence alice_seq;

          – should fail because the sequence already exists
          create sequence alice_seq;

          connect 'jdbc:derby:memory:patient;user=ruth;password=ruthpassword' as ruth_conn;

          – should not be able to drop someone else's objects
          drop sequence alice.alice_seq;

          – should not be able to create objects in someone else's schema
          create sequence alice.ruth_seq;

          set connection alice_conn;

          --should succeed
          drop sequence alice_seq;

          – should succeed
          create sequence alice_seq;

          I ran this script as follows:

          java -cp $CLASSPATH \
          -Dderby.connection.requireAuthentication=true \
          -Dderby.authentication.provider=BUILTIN \
          -Dderby.user.admin=adminpassword \
          -Dderby.user.alice=alicepassword \
          -Dderby.user.ruth=ruthpassword \
          -Dderby.database.sqlAuthorization=true \
          org.apache.derby.tools.ij script.sql

          To fix the schema-creation problem, I'm recommending some changes to the code in CreateSequenceNode to implicitly create the user schema as is done with other schema objects. See below.

          Here are some comments on the attached code:

          CreateSequenceNode

          • init() We need to add the following statement to the end of this method:

          implicitCreateSchema = true;

          • bindStatement() We need to rewrite this method:

          public void bindStatement() throws StandardException {
          CompilerContext cc = getCompilerContext();

          // implicitly create the schema if it does not exist.
          // this method also compiles permissions checks
          SchemaDescriptor sd = getSchemaDescriptor();

          // set the default schema name if the user did not explicitly specify a schema
          if ( sequenceName.getSchemaName() == null )

          { sequenceName.setSchemaName( sd.getSchemaName() ); }

          }

          GenericConstantActionFactory.java

          • getDropSequenceConstantAction() The header should say DROP SEQUENCE rather than DROP ROLE

          Tests

          A couple comments about tests:

          1) I don't understand why SequenceTest limits itself to JDBC3 and higher. It seems to me that this functionality ought to work in JSR169 environments too.

          2) I recommend adding some more test cases to SequenceTest. I would add cases to verify that the script above runs correctly.

          3) Right now SequenceTest is a standalone test. I think that it should be wired into the _Suite in its package.

          4) I recommend adding an upgrade test case to verify that the CREATE/DROP SEQUENCE statements work in legacy databases only after they have been hard-upgraded to 10.6.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the additional work on the patch. I think it's very close to being ready for a regression test run and checkin. I ran some more experiments and it seems that we need more work on the bind() logic for CREATE SEQUENCE. In particular, I noticed that we were not implicitly creating the schema if the sequence is the first object in it. We do implicitly create the schema for other objects. I wanted to get the following script to run cleanly and verify the correct behavior of various aspects of sequence creation. I noticed that the first CREATE SEQUENCE statement failed unexpectedly. connect 'jdbc:derby:memory:patient;create=true;user=admin;password=adminpassword' as admin_conn; connect 'jdbc:derby:memory:patient;user=alice;password=alicepassword' as alice_conn; – should implicitly create the ALICE schema create sequence alice_seq; – should fail because the sequence already exists create sequence alice_seq; connect 'jdbc:derby:memory:patient;user=ruth;password=ruthpassword' as ruth_conn; – should not be able to drop someone else's objects drop sequence alice.alice_seq; – should not be able to create objects in someone else's schema create sequence alice.ruth_seq; set connection alice_conn; --should succeed drop sequence alice_seq; – should succeed create sequence alice_seq; I ran this script as follows: java -cp $CLASSPATH \ -Dderby.connection.requireAuthentication=true \ -Dderby.authentication.provider=BUILTIN \ -Dderby.user.admin=adminpassword \ -Dderby.user.alice=alicepassword \ -Dderby.user.ruth=ruthpassword \ -Dderby.database.sqlAuthorization=true \ org.apache.derby.tools.ij script.sql To fix the schema-creation problem, I'm recommending some changes to the code in CreateSequenceNode to implicitly create the user schema as is done with other schema objects. See below. Here are some comments on the attached code: CreateSequenceNode init() We need to add the following statement to the end of this method: implicitCreateSchema = true; bindStatement() We need to rewrite this method: public void bindStatement() throws StandardException { CompilerContext cc = getCompilerContext(); // implicitly create the schema if it does not exist. // this method also compiles permissions checks SchemaDescriptor sd = getSchemaDescriptor(); // set the default schema name if the user did not explicitly specify a schema if ( sequenceName.getSchemaName() == null ) { sequenceName.setSchemaName( sd.getSchemaName() ); } } GenericConstantActionFactory.java getDropSequenceConstantAction() The header should say DROP SEQUENCE rather than DROP ROLE Tests A couple comments about tests: 1) I don't understand why SequenceTest limits itself to JDBC3 and higher. It seems to me that this functionality ought to work in JSR169 environments too. 2) I recommend adding some more test cases to SequenceTest. I would add cases to verify that the script above runs correctly. 3) Right now SequenceTest is a standalone test. I think that it should be wired into the _Suite in its package. 4) I recommend adding an upgrade test case to verify that the CREATE/DROP SEQUENCE statements work in legacy databases only after they have been hard-upgraded to 10.6. Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          Thanks a lot for your comments Rick.
          This patch contains the changes you suggested. Please review and comment.

          What do you suggest as my next step in the implementation?
          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Thanks a lot for your comments Rick. This patch contains the changes you suggested. Please review and comment. What do you suggest as my next step in the implementation? Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the create_drop patch. Looks like good progress. I have a couple comments:

          1) I see from your "drop sequence" stack trace that you are dealing with the Derby type system now. There should be only one type system, but by now Derby has at least four type systems. The root of the problem is that the original Cloudscape type system muddled the distinction between data types and data values. The other type systems then developed organically in order to compensate for that original design problem. What you want to do is to follow the example of the other datatype code in the catalogs, viz., the datatype column in SYSCOLUMNS. You need to borrow the datatype translation code from SYSCOLUMNS.buildDescriptor(). Replace the exception-raising line in SYSSEQUENCESRowFactory.buildDescriptor() with the following code:

          TypeDescriptor catalogType = (TypeDescriptor) row.getColumn(SYSSEQUENCES_SEQUENCEDATATYPE).getObject();
          DataTypeDescriptor dataType = DataTypeDescriptor.getType(catalogType);

          The explanation for why you do this is offered by the comment around the corresponding coercion logic in SYSCOLUMNSRowFactory.buildDescriptor().

          2) I think that the missing sequence descriptor condition can be handled with the same SQLState used by other DROP code. See, for instance, the corresponding code in DropTriggerConstantAction. You would write something like this:

          throw StandardException.newException(SQLState.LANG_OBJECT_NOT_FOUND_DURING_EXECUTION, "SEQUENCE",
          (schemaName + "." + sequenceName));

          3) Also, I notice that I get an assertion failure on the second CREATE statement in this batch:

          create sequence foo;
          create sequence foo;

          This is because you need to implement some more methods in SequenceDescriptor. Take a look at the TupleDescriptor superclass for all methods which raise NOTREACHED assertions if they are not overridden.

          4) Your SequenceDescriptor.drop() code is a good start, but you also need to invalidate other compiled statements which depend on the sequence which is about to disappear. The next time one of those statements executes, this invalidation will cause the statement to recompile, discover that the sequence no longer exists, and fail. You need to call DependencyManager.invalidateFor(). See the drop() logic in the other tuple descriptors for examples of how to do this.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the create_drop patch. Looks like good progress. I have a couple comments: 1) I see from your "drop sequence" stack trace that you are dealing with the Derby type system now. There should be only one type system, but by now Derby has at least four type systems. The root of the problem is that the original Cloudscape type system muddled the distinction between data types and data values. The other type systems then developed organically in order to compensate for that original design problem. What you want to do is to follow the example of the other datatype code in the catalogs, viz., the datatype column in SYSCOLUMNS. You need to borrow the datatype translation code from SYSCOLUMNS.buildDescriptor(). Replace the exception-raising line in SYSSEQUENCESRowFactory.buildDescriptor() with the following code: TypeDescriptor catalogType = (TypeDescriptor) row.getColumn(SYSSEQUENCES_SEQUENCEDATATYPE).getObject(); DataTypeDescriptor dataType = DataTypeDescriptor.getType(catalogType); The explanation for why you do this is offered by the comment around the corresponding coercion logic in SYSCOLUMNSRowFactory.buildDescriptor(). 2) I think that the missing sequence descriptor condition can be handled with the same SQLState used by other DROP code. See, for instance, the corresponding code in DropTriggerConstantAction. You would write something like this: throw StandardException.newException(SQLState.LANG_OBJECT_NOT_FOUND_DURING_EXECUTION, "SEQUENCE", (schemaName + "." + sequenceName)); 3) Also, I notice that I get an assertion failure on the second CREATE statement in this batch: create sequence foo; create sequence foo; This is because you need to implement some more methods in SequenceDescriptor. Take a look at the TupleDescriptor superclass for all methods which raise NOTREACHED assertions if they are not overridden. 4) Your SequenceDescriptor.drop() code is a good start, but you also need to invalidate other compiled statements which depend on the sequence which is about to disappear. The next time one of those statements executes, this invalidation will cause the statement to recompile, discover that the sequence no longer exists, and fail. You need to call DependencyManager.invalidateFor(). See the drop() logic in the other tuple descriptors for examples of how to do this. Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          The call to initAndCheck(null) in CreateSequenceNode.init() was a line left in the code by mistake.

          In this patch I added basic implementation of DROP SEQUENCE.

          When running the SequenceTest, while the CREATE statement seems to execute ok, I get the following exception for DROP.

          1) testDropSequence(org.apache.derbyTesting.functionTests.tests.lang.SequenceTest)java.sql.SQLException: Java exception: 'org.apache.derby.catalog.types.TypeDescriptorImpl cannot b
          e cast to org.apache.derby.iapi.types.DataTypeDescriptor: java.lang.ClassCastException'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
          at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87)
          at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.functionTests.tests.lang.SequenceTest.testDropSequence(SequenceTest.java:118)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Caused by: java.sql.SQLException: Java exception: 'org.apache.derby.catalog.types.TypeDescriptorImpl cannot be cast to org.apache.derby.iapi.types.DataTypeDescriptor: java.lang.Cla
          ssCastException'.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70)
          ... 34 more
          Caused by: java.lang.ClassCastException: org.apache.derby.catalog.types.TypeDescriptorImpl cannot be cast to org.apache.derby.iapi.types.DataTypeDescriptor
          at org.apache.derby.impl.sql.catalog.SYSSEQUENCESRowFactory.buildDescriptor(SYSSEQUENCESRowFactory.java:235)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(DataDictionaryImpl.java:8673)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(DataDictionaryImpl.java:8388)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSequenceDescriptor(DataDictionaryImpl.java:12747)
          at org.apache.derby.impl.sql.compile.DropSequenceNode.bindStatement(DropSequenceNode.java:81)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:316)
          at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:822)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606)
          ... 27 more

          I don't get where the datatype, which is a DataTypeDescriptor object at the time of creation, turned into a TypeDescriptorImpl instance.

          Also, in the executeConstantAction() method of DropSequenceConstantAction, what's the SQLState code that I should use for the exception when the sequenceDescriptor is null?

          Show
          Suran Jayathilaka added a comment - Hi Rick, The call to initAndCheck(null) in CreateSequenceNode.init() was a line left in the code by mistake. In this patch I added basic implementation of DROP SEQUENCE. When running the SequenceTest, while the CREATE statement seems to execute ok, I get the following exception for DROP. 1) testDropSequence(org.apache.derbyTesting.functionTests.tests.lang.SequenceTest)java.sql.SQLException: Java exception: 'org.apache.derby.catalog.types.TypeDescriptorImpl cannot b e cast to org.apache.derby.iapi.types.DataTypeDescriptor: java.lang.ClassCastException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Util.java:87) at org.apache.derby.impl.jdbc.Util.javaException(Util.java:244) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:403) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:614) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.functionTests.tests.lang.SequenceTest.testDropSequence(SequenceTest.java:118) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: java.sql.SQLException: Java exception: 'org.apache.derby.catalog.types.TypeDescriptorImpl cannot be cast to org.apache.derby.iapi.types.DataTypeDescriptor: java.lang.Cla ssCastException'. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory40.java:119) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:70) ... 34 more Caused by: java.lang.ClassCastException: org.apache.derby.catalog.types.TypeDescriptorImpl cannot be cast to org.apache.derby.iapi.types.DataTypeDescriptor at org.apache.derby.impl.sql.catalog.SYSSEQUENCESRowFactory.buildDescriptor(SYSSEQUENCESRowFactory.java:235) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndexMinion(DataDictionaryImpl.java:8673) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(DataDictionaryImpl.java:8388) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSequenceDescriptor(DataDictionaryImpl.java:12747) at org.apache.derby.impl.sql.compile.DropSequenceNode.bindStatement(DropSequenceNode.java:81) at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:316) at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:822) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:606) ... 27 more I don't get where the datatype, which is a DataTypeDescriptor object at the time of creation, turned into a TypeDescriptorImpl instance. Also, in the executeConstantAction() method of DropSequenceConstantAction, what's the SQLState code that I should use for the exception when the sequenceDescriptor is null?
          Hide
          Rick Hillegas added a comment - - edited

          Hi Suran,

          Thanks for the patch. Here are some answers to your questions:

          1) You don't need to add the permissions check which you saw in CREATE ROLE. The only permission you need to worry about is the permission to create objects in the target schema. I address that topic below.

          2) The bind() logic is responsible for filling in the current schema name if the user doesn't specify a schema. I address that topic below too.

          Here is a quick review of CreateSequenceNode:

          • I don't understand why initAndCheck() is called twice. I think that all you need is the call which takes sequenceName as arg. But let me know your motivation for adding this line if you think it is necessary.
          • The header comment of bindStatement() should be revised. The main points of this method are to resolve the schema name, determine privilege checks, and vet the variables in the CREATE SEQUENCE statement.
          • In bindStatement() you will want to resolve the schema name. Something like the following should do the trick. This will verify that the schema name is valid (if specified) and if the schema name is not specified, this will fill in the name of the session's current schema:

          sequenceName.bind( getDataDictionary() );

          • If you add the following code to bindStatement() (after you have bound the schema name), then a runtime check will be made to verify that the creator has permission to create objects in the target schema:

          SchemaDescriptor sd = getSchemaDescriptor();

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - - edited Hi Suran, Thanks for the patch. Here are some answers to your questions: 1) You don't need to add the permissions check which you saw in CREATE ROLE. The only permission you need to worry about is the permission to create objects in the target schema. I address that topic below. 2) The bind() logic is responsible for filling in the current schema name if the user doesn't specify a schema. I address that topic below too. Here is a quick review of CreateSequenceNode: I don't understand why initAndCheck() is called twice. I think that all you need is the call which takes sequenceName as arg. But let me know your motivation for adding this line if you think it is necessary. The header comment of bindStatement() should be revised. The main points of this method are to resolve the schema name, determine privilege checks, and vet the variables in the CREATE SEQUENCE statement. In bindStatement() you will want to resolve the schema name. Something like the following should do the trick. This will verify that the schema name is valid (if specified) and if the schema name is not specified, this will fill in the name of the session's current schema: sequenceName.bind( getDataDictionary() ); If you add the following code to bindStatement() (after you have bound the schema name), then a runtime check will be made to verify that the creator has permission to create objects in the target schema: SchemaDescriptor sd = getSchemaDescriptor(); Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          Attaching initial (incomplete) draft of the create sequences patch for review. Tests were not run with this patch.

          A couple of questions:
          1.
          Is it necessary to require a PRIVILEGE in CreateSequenceNode.bindStatement(), like done in CreateRoleNode (below)?
          ----------------
          if (isPrivilegeCollectionRequired())

          { cc.addRequiredRolePriv(name, Authorizer.CREATE_ROLE_PRIV); }

          ----------------

          If yes, do I use the MODIFY_SCHEMA_PRIV or define new ones as CREATE_SEQUENCE_PRIV ?

          2.
          In the very basic testcase I've added I attempt to execute a Create Sequence statement without specifying a schema. This fails saying the schema name is null. Why isn't the default schema being picked up?

          -------------
          Also adding here with thanks, a question answered by Rick Hillegas.

          Q: What's the method to get the schema name in sqlgrammar.jj?

          A:
          The data structure you are looking for is called TableName. It contains a schema name and an object name within that schema. The bind() phase of compilation knows how to fill in the default schema name if the user doesn't supply an explicit schema name. For an example of how to use TableName, see sqlgrammar.functionDefinition(). Your code will end up looking something like this:

          StatementNode
          sequenceDefinition() throws StandardException :

          { TableName qualifiedName = null; }

          { <SEQUENCE> qualifiedName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)

          Show
          Suran Jayathilaka added a comment - Attaching initial (incomplete) draft of the create sequences patch for review. Tests were not run with this patch. A couple of questions: 1. Is it necessary to require a PRIVILEGE in CreateSequenceNode.bindStatement(), like done in CreateRoleNode (below)? ---------------- if (isPrivilegeCollectionRequired()) { cc.addRequiredRolePriv(name, Authorizer.CREATE_ROLE_PRIV); } ---------------- If yes, do I use the MODIFY_SCHEMA_PRIV or define new ones as CREATE_SEQUENCE_PRIV ? 2. In the very basic testcase I've added I attempt to execute a Create Sequence statement without specifying a schema. This fails saying the schema name is null. Why isn't the default schema being picked up? ------------- Also adding here with thanks, a question answered by Rick Hillegas. Q: What's the method to get the schema name in sqlgrammar.jj? A: The data structure you are looking for is called TableName. It contains a schema name and an object name within that schema. The bind() phase of compilation knows how to fill in the default schema name if the user doesn't supply an explicit schema name. For an example of how to use TableName, see sqlgrammar.functionDefinition(). Your code will end up looking something like this: StatementNode sequenceDefinition() throws StandardException : { TableName qualifiedName = null; } { <SEQUENCE> qualifiedName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
          Hide
          Suran Jayathilaka added a comment -

          Posting some question I had and the answers given by Rick.

          • Like CreateTriggerNode etc, why isn't there a CreateFunctionNode? the functionDefinition() method is using the CreateAliasNode class.
            Good point. CREATE FUNCTION is a good template from the perspective of the parser but maybe a bit confusing from the perspective of the data structures. Derby SQL routines (functions and procedures) are called Aliases internally. This is because functions and procedures used to be much lighter-weight objects. Originally, Derby functions and procedures were just names (aliases) for user-coded static java methods, and declaring them was just a simple matter of binding a Derby name to the external code. This, however, is not the ANSI syntax for declaring functions and procedures. Today, Derby uses the ANSI language for declaring functions and procedures, which means that functions and procedures are much more heavy-weight than the original aliases were.
          • How are the various jj_2_*() mthods in SQLParser used?
          • In SQLParser, What's the sue of the jj_la1_* arrays?
            Ah, looks like you are poking around in SQLParser. Don't bother with that file. The grammar lives in sqlgrammar.jj, a yacc-like grammar which is much easier to understand and extend. We use the javacc grammar generator to turn the sqlgrammar.jj source into a number of generated files, one of which is the barely readable SQLParser which you're looking at right now. Let me know if you need help understanding sqlgrammar.jj. It's fairly straightforward if you are familiar with yacc-style grammars but you may need some pointers if you haven't worked on one of these grammars before.
          Show
          Suran Jayathilaka added a comment - Posting some question I had and the answers given by Rick. Like CreateTriggerNode etc, why isn't there a CreateFunctionNode? the functionDefinition() method is using the CreateAliasNode class. Good point. CREATE FUNCTION is a good template from the perspective of the parser but maybe a bit confusing from the perspective of the data structures. Derby SQL routines (functions and procedures) are called Aliases internally. This is because functions and procedures used to be much lighter-weight objects. Originally, Derby functions and procedures were just names (aliases) for user-coded static java methods, and declaring them was just a simple matter of binding a Derby name to the external code. This, however, is not the ANSI syntax for declaring functions and procedures. Today, Derby uses the ANSI language for declaring functions and procedures, which means that functions and procedures are much more heavy-weight than the original aliases were. How are the various jj_2_*() mthods in SQLParser used? In SQLParser, What's the sue of the jj_la1_* arrays? Ah, looks like you are poking around in SQLParser. Don't bother with that file. The grammar lives in sqlgrammar.jj, a yacc-like grammar which is much easier to understand and extend. We use the javacc grammar generator to turn the sqlgrammar.jj source into a number of generated files, one of which is the barely readable SQLParser which you're looking at right now. Let me know if you need help understanding sqlgrammar.jj. It's fairly straightforward if you are familiar with yacc-style grammars but you may need some pointers if you haven't worked on one of these grammars before.
          Hide
          Rick Hillegas added a comment -

          Hi Kathey,

          792245 added two new catalogs. This means that you need to throw away and recreate databases which were created by previous versions of the 10.6 trunk.

          Regards,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Kathey, 792245 added two new catalogs. This means that you need to throw away and recreate databases which were created by previous versions of the 10.6 trunk. Regards, -Rick
          Show
          Kathey Marsden added a comment - I asked this question in DERBY-4053 but decided it was better to ask here. Are 10.6 databases before the 792245 change expected to be unusable after that change? Please note the following comments in DERBY-4053 : https://issues.apache.org/jira/browse/DERBY-4053?focusedCommentId=12729806&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12729806 https://issues.apache.org/jira/browse/DERBY-4053?focusedCommentId=12729857&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_12729857
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          That's great news indeed.
          Thanks so much for your extreme patience and help! I will get started on the CREATE/DROP SEQUENCE work right away.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick, That's great news indeed. Thanks so much for your extreme patience and help! I will get started on the CREATE/DROP SEQUENCE work right away. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Thanks again for all of your hard work on this first increment, Suran. With the small tweak in the catalogs_h.diff patch, the tests ran cleanly for me. I have committed that patch at subversion revision 792245.

          Congratulations!

          Now I think you could get started on the work for CREATE/DROP SEQUENCE. I think that the machinery for CREATE/DROP FUNCTION would be a useful template.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Thanks again for all of your hard work on this first increment, Suran. With the small tweak in the catalogs_h.diff patch, the tests ran cleanly for me. I have committed that patch at subversion revision 792245. Congratulations! Now I think you could get started on the work for CREATE/DROP SEQUENCE. I think that the machinery for CREATE/DROP FUNCTION would be a useful template. Thanks! -Rick
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          The failures occur during the test of soft-upgrade. The initial failure is caused because the test is trying to drop a schema. In doing so, the data dictionary checks to see whether the schema is empty. Since you added a new kind of schema object (sequences) and logic to check whether SYSSEQUENCES is empty, the data dictionary tries to scan SYSSEQUENCES. But in soft-upgrade mode, this table doesn't exist. It is only created during hard upgrade. That's what results in the funny -1 conglomerate id, which raises the initial exception.The fix is to only scan SYSSEQUENCES if the data dictionary is at level 10.6 or greater. I added this check on top of the changes you made to DD_Version and have uploaded the resulting patch: catalogs_h.diff

          With this patch, the upgrade tests run cleanly for me. So does standalone DatabaseMetaDataTests. I am running the full regression tests now.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, The failures occur during the test of soft-upgrade. The initial failure is caused because the test is trying to drop a schema. In doing so, the data dictionary checks to see whether the schema is empty. Since you added a new kind of schema object (sequences) and logic to check whether SYSSEQUENCES is empty, the data dictionary tries to scan SYSSEQUENCES. But in soft-upgrade mode, this table doesn't exist. It is only created during hard upgrade. That's what results in the funny -1 conglomerate id, which raises the initial exception.The fix is to only scan SYSSEQUENCES if the data dictionary is at level 10.6 or greater. I added this check on top of the changes you made to DD_Version and have uploaded the resulting patch: catalogs_h.diff With this patch, the upgrade tests run cleanly for me. So does standalone DatabaseMetaDataTests. I am running the full regression tests now. Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          Thanks for fixing the patch. I made those changes in dropAllPermDescriptors() and the uniqueness array in my own code (which I think are the only 2 places that needed to be changed?).

          I added the following in DD_Version.doFullUpgrade().

          --------------------------------------------------------------------------
          if (fromMajorVersionNumber <= DataDictionary.DD_VERSION_DERBY_10_5)

          { // On ugrade from versions before 10.6, create system procedures // added in 10.6. bootingDictionary.create_10_6_system_procedures(tc, newlyCreatedRoutines); // On upgrade from versions before 10.6, create system catalogs // added in 10.6 bootingDictionary.upgradeMakeCatalog( tc, DataDictionary.SYSSEQUENCES_CATALOG_NUM); bootingDictionary.upgradeMakeCatalog( tc, DataDictionary.SYSPERMS_CATALOG_NUM); }

          -----------------------------------------------------------------------------

          When I ran upgrade tests with 10.5.1.1. as the sole old release, I did see the test failures you ran into.

          >java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=D:/projects/derby-trunk/tests/old-rels junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.upgradeTests._Suite
          ...
          ...
          ALARM: Non-existing location for jar files: 'D:/projects/derby-trunk/tests/old-rels\10.4.2.0'. Upgrade tests can NOT be run!
          ...............E...............E..E.E........
          .......................E..................
          .................
          Time: 78.156
          There were 5 errors:
          1) testGetTablesModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          ...
          Tests run: 99, Failures: 0, Errors: 5

          I can't figure out what's causing this failure, which I think is causing the remaining failures in turn.

          Show
          Suran Jayathilaka added a comment - Hi Rick, Thanks for fixing the patch. I made those changes in dropAllPermDescriptors() and the uniqueness array in my own code (which I think are the only 2 places that needed to be changed?). I added the following in DD_Version.doFullUpgrade(). -------------------------------------------------------------------------- if (fromMajorVersionNumber <= DataDictionary.DD_VERSION_DERBY_10_5) { // On ugrade from versions before 10.6, create system procedures // added in 10.6. bootingDictionary.create_10_6_system_procedures(tc, newlyCreatedRoutines); // On upgrade from versions before 10.6, create system catalogs // added in 10.6 bootingDictionary.upgradeMakeCatalog( tc, DataDictionary.SYSSEQUENCES_CATALOG_NUM); bootingDictionary.upgradeMakeCatalog( tc, DataDictionary.SYSPERMS_CATALOG_NUM); } ----------------------------------------------------------------------------- When I ran upgrade tests with 10.5.1.1. as the sole old release, I did see the test failures you ran into. >java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=D:/projects/derby-trunk/tests/old-rels junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.upgradeTests._Suite ... ... ALARM: Non-existing location for jar files: 'D:/projects/derby-trunk/tests/old-rels\10.4.2.0'. Upgrade tests can NOT be run! ...............E...............E..E.E........ .......................E.................. ................. Time: 78.156 There were 5 errors: 1) testGetTablesModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:95) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) ... Tests run: 99, Failures: 0, Errors: 5 I can't figure out what's causing this failure, which I think is causing the remaining failures in turn.
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          We are getting very close with this patch but there is still a little more work to do. Now you need to add upgrade logic which will create the new catalogs in old databases which are hard-upgraded to 10.6. This is really easy to do.

          The test failures in the DatabaseMetaData cases occur when those cases are run as part of the upgrade tests. You need to add some upgrade logic to DD_Version.doFullUpgrade(). That logic will create the new SYSSEQUENCES and SYSPERMS catalogs in old databases. You will want to add these catalogs if the original database version is 10.5 or earlier. Look for the addition of SYSROLES as a template for how to do this.

          To verify that you have done this correctly, you will want to run the upgrade tests. The following works for me:

          java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=$OLD_RELEASE_DIRECTORY junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.upgradeTests._Suite

          where $OLD_RELEASE_DIRECTORY is the full pathname of the directory which holds your old Derby releases. That directory should have a directory for each previous release on which you want to test upgrade. For instance, my old release directory has the following subdirectories (for the purposes of this work, you could probably just get away with the 10.5.1.1 release):

          10.0.2.1
          10.1.1.0
          10.1.2.1
          10.1.3.1
          10.2.1.6
          10.2.2.0
          10.2.2.1
          10.3.1.4
          10.3.2.1
          10.3.3.0
          10.4.1.3
          10.4.2.0
          10.4.2.1
          10.5.1.1

          Each of those subdirectories, in turn, should hold the jar files for that release.

          I think that you did not trip across these test failures because when you run the JUnit tests, you don't set the -DderbyTesting.oldReleasePath flag on the command line. That's what triggers the upgrade tests. To make sure that you always run the upgrade tests as part of your regression test run, make sure to use that flag. E.g.:

          java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=$OLD_RELEASE_DIRECTORY junit.textui.TestRunner org.apache.derbyTesting.functionTests.suites.All

          Thanks!

          Show
          Rick Hillegas added a comment - Hi Suran, We are getting very close with this patch but there is still a little more work to do. Now you need to add upgrade logic which will create the new catalogs in old databases which are hard-upgraded to 10.6. This is really easy to do. The test failures in the DatabaseMetaData cases occur when those cases are run as part of the upgrade tests. You need to add some upgrade logic to DD_Version.doFullUpgrade(). That logic will create the new SYSSEQUENCES and SYSPERMS catalogs in old databases. You will want to add these catalogs if the original database version is 10.5 or earlier. Look for the addition of SYSROLES as a template for how to do this. To verify that you have done this correctly, you will want to run the upgrade tests. The following works for me: java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=$OLD_RELEASE_DIRECTORY junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.upgradeTests._Suite where $OLD_RELEASE_DIRECTORY is the full pathname of the directory which holds your old Derby releases. That directory should have a directory for each previous release on which you want to test upgrade. For instance, my old release directory has the following subdirectories (for the purposes of this work, you could probably just get away with the 10.5.1.1 release): 10.0.2.1 10.1.1.0 10.1.2.1 10.1.3.1 10.2.1.6 10.2.2.0 10.2.2.1 10.3.1.4 10.3.2.1 10.3.3.0 10.4.1.3 10.4.2.0 10.4.2.1 10.5.1.1 Each of those subdirectories, in turn, should hold the jar files for that release. I think that you did not trip across these test failures because when you run the JUnit tests, you don't set the -DderbyTesting.oldReleasePath flag on the command line. That's what triggers the upgrade tests. To make sure that you always run the upgrade tests as part of your regression test run, make sure to use that flag. E.g.: java -XX:MaxPermSize=128M -Xmx512m -DderbyTesting.oldReleasePath=$OLD_RELEASE_DIRECTORY junit.textui.TestRunner org.apache.derbyTesting.functionTests.suites.All Thanks!
          Hide
          Rick Hillegas added a comment -

          I am still seeing errors in the JUnit tests. I am running against the jar files. I see these errors:

          1) testAttributeAccumulatedConnectionCount(org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest)java.security.PrivilegedActionException: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0122-55c7-0fa8-ffffe1d7aa3e
          at java.security.AccessController.doPrivileged(Native Method)
          at org.apache.derbyTesting.functionTests.tests.management.MBeanTest.getAttribute(MBeanTest.java:379)
          at org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest.testAttributeAccumulatedConnectionCount(NetworkServerMBeanTest.java:93)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          Caused by: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0122-55c7-0fa8-ffffe1d7aa3e
          at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1010)
          at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:627)
          at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:659)
          at org.apache.derbyTesting.functionTests.tests.management.MBeanTest$4.run(MBeanTest.java:382)
          ... 41 more
          2) testGetSchemasModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          Caused by: ERROR XSAI2: The conglomerate (-1) requested does not exist.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286)
          at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(RAMTransaction.java:399)
          at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1308)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaReferenced(DataDictionaryImpl.java:2305)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaEmpty(DataDictionaryImpl.java:2241)
          at org.apache.derby.iapi.sql.dictionary.SchemaDescriptor.drop(SchemaDescriptor.java:419)
          at org.apache.derby.impl.sql.execute.DropSchemaConstantAction.executeConstantAction(DropSchemaConstantAction.java:99)
          at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
          ... 85 more

          and then 81 errors like this:

          3) testGetColumnsModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: Schema 'ONE_DMD_TEST' already exists.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createSchemasForTests(DatabaseMetaDataTest.java:980)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createTablesForTest(DatabaseMetaDataTest.java:1333)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetColumnsModify(DatabaseMetaDataTest.java:1402)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          Caused by: ERROR X0Y68: Schema 'ONE_DMD_TEST' already exists.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303)
          at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantActionMinion(CreateSchemaConstantAction.java:138)
          at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:104)
          at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
          ... 91 more

          I will put some time into diagnosing what's going on here.

          Show
          Rick Hillegas added a comment - I am still seeing errors in the JUnit tests. I am running against the jar files. I see these errors: 1) testAttributeAccumulatedConnectionCount(org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest)java.security.PrivilegedActionException: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0122-55c7-0fa8-ffffe1d7aa3e at java.security.AccessController.doPrivileged(Native Method) at org.apache.derbyTesting.functionTests.tests.management.MBeanTest.getAttribute(MBeanTest.java:379) at org.apache.derbyTesting.functionTests.tests.management.NetworkServerMBeanTest.testAttributeAccumulatedConnectionCount(NetworkServerMBeanTest.java:93) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) Caused by: javax.management.InstanceNotFoundException: org.apache.derby:type=NetworkServer,system=c013800d-0122-55c7-0fa8-ffffe1d7aa3e at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getMBean(DefaultMBeanServerInterceptor.java:1010) at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttribute(DefaultMBeanServerInterceptor.java:627) at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttribute(JmxMBeanServer.java:659) at org.apache.derbyTesting.functionTests.tests.management.MBeanTest$4.run(MBeanTest.java:382) ... 41 more 2) testGetSchemasModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) Caused by: ERROR XSAI2: The conglomerate (-1) requested does not exist. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286) at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(RAMTransaction.java:399) at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1308) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaReferenced(DataDictionaryImpl.java:2305) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaEmpty(DataDictionaryImpl.java:2241) at org.apache.derby.iapi.sql.dictionary.SchemaDescriptor.drop(SchemaDescriptor.java:419) at org.apache.derby.impl.sql.execute.DropSchemaConstantAction.executeConstantAction(DropSchemaConstantAction.java:99) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 85 more and then 81 errors like this: 3) testGetColumnsModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: Schema 'ONE_DMD_TEST' already exists. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createSchemasForTests(DatabaseMetaDataTest.java:980) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createTablesForTest(DatabaseMetaDataTest.java:1333) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetColumnsModify(DatabaseMetaDataTest.java:1402) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) Caused by: ERROR X0Y68: Schema 'ONE_DMD_TEST' already exists. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303) at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantActionMinion(CreateSchemaConstantAction.java:138) at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:104) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 91 more I will put some time into diagnosing what's going on here.
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the new patch, which adds another index to SYSPERMS. After looking at dropAllPermDescriptors() again, I realized that I misled you: The while loop needs to use 2 indexes. The first is the index you just added-the one which is keyed on the id of the privileged object. The second is the original index which you were using-the one which is keyed on the unique id of the permissions tuple itself. I tweaked dropAllPermDescriptors() accordingly. I also tweaked SYSPERMSRowFactory: now that there are 2 indexes, the uniqueness array needs to be filled in (one of the indexes is unique, the other isn't). I have regenerated the patch and am attaching it as catalogs_g.diff

          Am running the regressions tests now. Thanks!

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the new patch, which adds another index to SYSPERMS. After looking at dropAllPermDescriptors() again, I realized that I misled you: The while loop needs to use 2 indexes. The first is the index you just added- the one which is keyed on the id of the privileged object. The second is the original index which you were using -the one which is keyed on the unique id of the permissions tuple itself. I tweaked dropAllPermDescriptors() accordingly. I also tweaked SYSPERMSRowFactory: now that there are 2 indexes, the uniqueness array needs to be filled in (one of the indexes is unique, the other isn't). I have regenerated the patch and am attaching it as catalogs_g.diff Am running the regressions tests now. Thanks!
          Hide
          Suran Jayathilaka added a comment -

          Reattaching modified patch (catalogs_f_2.patch) since 2 required changes in dropAllPermDescriptors() were missing in catalogs_f.patch.

          Show
          Suran Jayathilaka added a comment - Reattaching modified patch (catalogs_f_2.patch) since 2 required changes in dropAllPermDescriptors() were missing in catalogs_f.patch.
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          Thank you for your quick reply. When I ran suites.All on an updated trunk codebase (with my changes), I did not see the test failures you mentioned above. lang.AlterTableTest failed, which I have rectified in my next revision of the patch.

          In this revision I added an index on ObjectId to SYSPERMSRowFactory.

          I'm running tests on Vista 64, AMD Phenom II CPU and Sun JDK 1.6.0_10.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick, Thank you for your quick reply. When I ran suites.All on an updated trunk codebase (with my changes), I did not see the test failures you mentioned above. lang.AlterTableTest failed, which I have rectified in my next revision of the patch. In this revision I added an index on ObjectId to SYSPERMSRowFactory. I'm running tests on Vista 64, AMD Phenom II CPU and Sun JDK 1.6.0_10. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          I don't think that fix is going to work. The third argument to ti.getRow() is the id of an index, not a column position in a base row. I think that the problem is that there is no usable index into SYSPERMS keyed on OBJECTID. I believe that you need to add such an index to SYSPERMS and then use its id in all of the calls inside the while loop (ti.getRow(), rf.buildIndexKeyRow(), ti.deleteRow()).

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, I don't think that fix is going to work. The third argument to ti.getRow() is the id of an index, not a column position in a base row. I think that the problem is that there is no usable index into SYSPERMS keyed on OBJECTID. I believe that you need to add such an index to SYSPERMS and then use its id in all of the calls inside the while loop (ti.getRow(), rf.buildIndexKeyRow(), ti.deleteRow()). Thanks, -Rick
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          I changed the DataDictionaryImpl.dropAllPermDescriptors() method as follows by changing the index in the ti.getRow() call to 3, which is the index of the object Id. I think it should behave correctly now. If this is correct, I will attach the revised patch when the tests are finished.

          public void dropAllPermDescriptors(UUID objectID, TransactionController tc)
          throws StandardException {
          TabInfoImpl ti = getNonCoreTI(SYSPERMS_CATALOG_NUM);
          SYSPERMSRowFactory rf = (SYSPERMSRowFactory) ti.getCatalogRowFactory();
          DataValueDescriptor objIdOrderable;
          ExecRow curRow;
          PermissionsDescriptor perm;

          // In Derby authorization mode, permission catalogs may not be present
          if (!usesSqlAuthorization)
          return;

          /* Use objIDOrderable in both start and stop position for scan. */
          objIdOrderable = getIDValueAsCHAR(objectID);

          /* Set up the start/stop position for the scan */
          ExecIndexRow keyRow = exFactory.getIndexableRow(1);
          keyRow.setColumn(1, objIdOrderable);

          while ((curRow = ti.getRow(tc, keyRow, 3)) != null)

          { perm = (PermDescriptor) rf.buildDescriptor(curRow, (TupleDescriptor) null, this); removePermEntryInCache(perm); // Build new key based on UUID and drop the entry as we want to drop // only this row ExecIndexRow uuidKey; uuidKey = rf.buildIndexKeyRow(rf.PERMS_UUID_IDX_NUM, perm); ti.deleteRow(tc, uuidKey, rf.PERMS_UUID_IDX_NUM); }

          }

          Also, when I ran org.apache.derbyTesting.functionTests.tests.derbynet._Suite, I did not see the failure which you mentioned. I am currently running suites.All and will check if there are remaining test failures.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick, I changed the DataDictionaryImpl.dropAllPermDescriptors() method as follows by changing the index in the ti.getRow() call to 3, which is the index of the object Id. I think it should behave correctly now. If this is correct, I will attach the revised patch when the tests are finished. public void dropAllPermDescriptors(UUID objectID, TransactionController tc) throws StandardException { TabInfoImpl ti = getNonCoreTI(SYSPERMS_CATALOG_NUM); SYSPERMSRowFactory rf = (SYSPERMSRowFactory) ti.getCatalogRowFactory(); DataValueDescriptor objIdOrderable; ExecRow curRow; PermissionsDescriptor perm; // In Derby authorization mode, permission catalogs may not be present if (!usesSqlAuthorization) return; /* Use objIDOrderable in both start and stop position for scan. */ objIdOrderable = getIDValueAsCHAR(objectID); /* Set up the start/stop position for the scan */ ExecIndexRow keyRow = exFactory.getIndexableRow(1); keyRow.setColumn(1, objIdOrderable); while ((curRow = ti.getRow(tc, keyRow, 3)) != null) { perm = (PermDescriptor) rf.buildDescriptor(curRow, (TupleDescriptor) null, this); removePermEntryInCache(perm); // Build new key based on UUID and drop the entry as we want to drop // only this row ExecIndexRow uuidKey; uuidKey = rf.buildIndexKeyRow(rf.PERMS_UUID_IDX_NUM, perm); ti.deleteRow(tc, uuidKey, rf.PERMS_UUID_IDX_NUM); } } Also, when I ran org.apache.derbyTesting.functionTests.tests.derbynet._Suite, I did not see the failure which you mentioned. I am currently running suites.All and will check if there are remaining test failures. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Thanks for the new patch, Suran. All of the old harness tests pass for me now. However, I am seeing some errors in the JUnit tests. Before I describe those errors, I have some comments on the code:

          DataDictionaryImpl.dropAllPermDescriptors() - It looks to me as though the id of the privileged object (e.g., the sequence generator) is being used as the key for an index built on a permissions id. This will fail to match any rows. In addition, one of the comments in this method refers to the passed-in id as a table id, which is probably not true.

          I see the following error when I run the JUnit suite org.apache.derbyTesting.functionTests.tests.derbynet._Suite. I do not see this error when I run this suite on a clean codeline before the patch is applied:

          .........................................
          .........................................
          ...............java.net.SocketException: Connection reset
          at java.net.SocketInputStream.read(SocketInputStream.java:168)
          at java.net.SocketInputStream.read(SocketInputStream.java:90)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(NetworkServerControlImpl.java:2764)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(NetworkServerControlImpl.java:2708)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(NetworkServerControlImpl.java:1169)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.ping(NetworkServerControlImpl.java:1144)
          at org.apache.derby.drda.NetworkServerControl.ping(NetworkServerControl.java:395)
          at org.apache.derbyTesting.junit.NetworkServerTestSetup.pingForServerUp(NetworkServerTestSetup.java:561)
          at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.canPingServer(ServerPropertiesTest.java:256)
          at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.ttestSetPortPriority(ServerPropertiesTest.java:448)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:585)
          at junit.framework.TestCase.runTest(TestCase.java:164)
          at junit.framework.TestCase.runBare(TestCase.java:130)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:120)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.framework.TestSuite.runTest(TestSuite.java:230)
          at junit.framework.TestSuite.run(TestSuite.java:225)
          at junit.framework.TestSuite.runTest(TestSuite.java:230)
          at junit.framework.TestSuite.run(TestSuite.java:225)
          at junit.textui.TestRunner.doRun(TestRunner.java:121)
          at junit.textui.TestRunner.start(TestRunner.java:185)
          at junit.textui.TestRunner.main(TestRunner.java:143)
          java.net.SocketException: Connection reset
          at java.net.SocketInputStream.read(SocketInputStream.java:168)
          at java.net.SocketInputStream.read(SocketInputStream.java:90)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(NetworkServerControlImpl.java:2764)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(NetworkServerControlImpl.java:2708)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(NetworkServerControlImpl.java:1169)
          at org.apache.derby.impl.drda.NetworkServerControlImpl.ping(NetworkServerControlImpl.java:1144)
          at org.apache.derby.drda.NetworkServerControl.ping(NetworkServerControl.java:395)
          at org.apache.derbyTesting.junit.NetworkServerTestSetup.pingForServerUp(NetworkServerTestSetup.java:561)
          at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.canPingServer(ServerPropertiesTest.java:256)
          at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.ttestSetPortPriority(ServerPropertiesTest.java:448)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:585)
          at junit.framework.TestCase.runTest(TestCase.java:164)
          at junit.framework.TestCase.runBare(TestCase.java:130)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.framework.TestResult$1.protect(TestResult.java:106)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.framework.TestResult.run(TestResult.java:109)
          at junit.framework.TestCase.run(TestCase.java:120)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.framework.TestResult.runProtected(TestResult.java:124)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.framework.TestSuite.runTest(TestSuite.java:230)
          at junit.framework.TestSuite.run(TestSuite.java:225)
          at junit.framework.TestSuite.runTest(TestSuite.java:230)
          at junit.framework.TestSuite.run(TestSuite.java:225)
          at junit.textui.TestRunner.doRun(TestRunner.java:121)
          at junit.textui.TestRunner.start(TestRunner.java:185)
          at junit.textui.TestRunner.main(TestRunner.java:143)
          ..........................
          ..............

          In addition, when I ran the full JUnit test suite, I saw the following error:

          1) testGetSchemasModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          Caused by: ERROR XSAI2: The conglomerate (-1) requested does not exist.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286)
          at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(RAMTransaction.java:399)
          at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1308)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaReferenced(DataDictionaryImpl.java:2305)
          at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaEmpty(DataDictionaryImpl.java:2241)
          at org.apache.derby.iapi.sql.dictionary.SchemaDescriptor.drop(SchemaDescriptor.java:419)
          at org.apache.derby.impl.sql.execute.DropSchemaConstantAction.executeConstantAction(DropSchemaConstantAction.java:99)
          at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
          ... 85 more

          and then 81 instances of this error:

          2) testGetColumnsModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: Schema 'ONE_DMD_TEST' already exists.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createSchemasForTests(DatabaseMetaDataTest.java:980)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createTablesForTest(DatabaseMetaDataTest.java:1333)
          at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetColumnsModify(DatabaseMetaDataTest.java:1402)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          Caused by: ERROR X0Y68: Schema 'ONE_DMD_TEST' already exists.
          at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303)
          at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantActionMinion(CreateSchemaConstantAction.java:138)
          at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:104)
          at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235)
          ... 91 more

          I don't know a smaller test scenario which shows these problems. I did not see these errors when I ran the JUnit suite org.apache.derbyTesting.functionTests.tests.jdbcapi._Suite.

          Show
          Rick Hillegas added a comment - Thanks for the new patch, Suran. All of the old harness tests pass for me now. However, I am seeing some errors in the JUnit tests. Before I describe those errors, I have some comments on the code: DataDictionaryImpl.dropAllPermDescriptors() - It looks to me as though the id of the privileged object (e.g., the sequence generator) is being used as the key for an index built on a permissions id. This will fail to match any rows. In addition, one of the comments in this method refers to the passed-in id as a table id, which is probably not true. I see the following error when I run the JUnit suite org.apache.derbyTesting.functionTests.tests.derbynet._Suite. I do not see this error when I run this suite on a clean codeline before the patch is applied: ......................................... ......................................... ...............java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:168) at java.net.SocketInputStream.read(SocketInputStream.java:90) at org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(NetworkServerControlImpl.java:2764) at org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(NetworkServerControlImpl.java:2708) at org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(NetworkServerControlImpl.java:1169) at org.apache.derby.impl.drda.NetworkServerControlImpl.ping(NetworkServerControlImpl.java:1144) at org.apache.derby.drda.NetworkServerControl.ping(NetworkServerControl.java:395) at org.apache.derbyTesting.junit.NetworkServerTestSetup.pingForServerUp(NetworkServerTestSetup.java:561) at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.canPingServer(ServerPropertiesTest.java:256) at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.ttestSetPortPriority(ServerPropertiesTest.java:448) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:164) at junit.framework.TestCase.runBare(TestCase.java:130) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:120) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) at junit.textui.TestRunner.doRun(TestRunner.java:121) at junit.textui.TestRunner.start(TestRunner.java:185) at junit.textui.TestRunner.main(TestRunner.java:143) java.net.SocketException: Connection reset at java.net.SocketInputStream.read(SocketInputStream.java:168) at java.net.SocketInputStream.read(SocketInputStream.java:90) at org.apache.derby.impl.drda.NetworkServerControlImpl.fillReplyBuffer(NetworkServerControlImpl.java:2764) at org.apache.derby.impl.drda.NetworkServerControlImpl.readResult(NetworkServerControlImpl.java:2708) at org.apache.derby.impl.drda.NetworkServerControlImpl.pingWithNoOpen(NetworkServerControlImpl.java:1169) at org.apache.derby.impl.drda.NetworkServerControlImpl.ping(NetworkServerControlImpl.java:1144) at org.apache.derby.drda.NetworkServerControl.ping(NetworkServerControl.java:395) at org.apache.derbyTesting.junit.NetworkServerTestSetup.pingForServerUp(NetworkServerTestSetup.java:561) at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.canPingServer(ServerPropertiesTest.java:256) at org.apache.derbyTesting.functionTests.tests.derbynet.ServerPropertiesTest.ttestSetPortPriority(ServerPropertiesTest.java:448) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at junit.framework.TestCase.runTest(TestCase.java:164) at junit.framework.TestCase.runBare(TestCase.java:130) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.framework.TestResult$1.protect(TestResult.java:106) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.framework.TestResult.run(TestResult.java:109) at junit.framework.TestCase.run(TestCase.java:120) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.framework.TestResult.runProtected(TestResult.java:124) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) at junit.framework.TestSuite.runTest(TestSuite.java:230) at junit.framework.TestSuite.run(TestSuite.java:225) at junit.textui.TestRunner.doRun(TestRunner.java:121) at junit.textui.TestRunner.start(TestRunner.java:185) at junit.textui.TestRunner.main(TestRunner.java:143) .......................... .............. In addition, when I ran the full JUnit test suite, I saw the following error: 1) testGetSchemasModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: The conglomerate (-1) requested does not exist. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.junit.JDBC.dropSchema(JDBC.java:323) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.tearDown(DatabaseMetaDataTest.java:184) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) Caused by: ERROR XSAI2: The conglomerate (-1) requested does not exist. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:286) at org.apache.derby.impl.store.access.RAMTransaction.findExistingConglomerate(RAMTransaction.java:399) at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java:1308) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaReferenced(DataDictionaryImpl.java:2305) at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.isSchemaEmpty(DataDictionaryImpl.java:2241) at org.apache.derby.iapi.sql.dictionary.SchemaDescriptor.drop(SchemaDescriptor.java:419) at org.apache.derby.impl.sql.execute.DropSchemaConstantAction.executeConstantAction(DropSchemaConstantAction.java:99) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 85 more and then 81 instances of this error: 2) testGetColumnsModify(org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest)java.sql.SQLException: Schema 'ONE_DMD_TEST' already exists. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:201) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:391) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:346) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2201) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:81) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1323) at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:625) at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(EmbedStatement.java:175) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createSchemasForTests(DatabaseMetaDataTest.java:980) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.createTablesForTest(DatabaseMetaDataTest.java:1333) at org.apache.derbyTesting.functionTests.tests.jdbcapi.DatabaseMetaDataTest.testGetColumnsModify(DatabaseMetaDataTest.java:1402) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) Caused by: ERROR X0Y68: Schema 'ONE_DMD_TEST' already exists. at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:303) at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantActionMinion(CreateSchemaConstantAction.java:138) at org.apache.derby.impl.sql.execute.CreateSchemaConstantAction.executeConstantAction(CreateSchemaConstantAction.java:104) at org.apache.derby.impl.sql.execute.MiscResultSet.open(MiscResultSet.java:64) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:416) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:297) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1235) ... 91 more I don't know a smaller test scenario which shows these problems. I did not see these errors when I ran the JUnit suite org.apache.derbyTesting.functionTests.tests.jdbcapi._Suite.
          Hide
          Suran Jayathilaka added a comment -

          With this patch all the tests that were failing due to the new catalogs are passing. Please review and comment.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - With this patch all the tests that were failing due to the new catalogs are passing. Please review and comment. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          The altertable.diff indicates that the test was run under the old harness using the Derby client. Looking through the output of a full test run, which I launched earlier today, I only see altertable.sql logged once--as an embedded test. Are you sure that you are supposed to run this test under the Derby client?

          The dropcrash2 test is one of the Store tests which is never run standalone. It depends on the setup performed by tests which run before it in the storerecovery suite, so dropcrash2 is only run as part of that suite. You run that suite like this:

          java org.apache.derbyTesting.functionTests.harness.RunSuite storerecovery

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, The altertable.diff indicates that the test was run under the old harness using the Derby client. Looking through the output of a full test run, which I launched earlier today, I only see altertable.sql logged once--as an embedded test. Are you sure that you are supposed to run this test under the Derby client? The dropcrash2 test is one of the Store tests which is never run standalone. It depends on the setup performed by tests which run before it in the storerecovery suite, so dropcrash2 is only run as part of that suite. You run that suite like this: java org.apache.derbyTesting.functionTests.harness.RunSuite storerecovery Hope this helps, -Rick
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          In catalogs_d.patch I removed the typo in GrantRevokeDDLTest and the println in JDBC which was indeed a debug line I'd forgotten to delete.

          I've also attached the diff file from running lang/altertable.sql. Could you please have a look and help me understand what's causing this test to fail?

          Also, how do I run tests like dropcrash2 which aren't JUnit tests nor harness tests?

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick, In catalogs_d.patch I removed the typo in GrantRevokeDDLTest and the println in JDBC which was indeed a debug line I'd forgotten to delete. I've also attached the diff file from running lang/altertable.sql. Could you please have a look and help me understand what's causing this test to fail? Also, how do I run tests like dropcrash2 which aren't JUnit tests nor harness tests? Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the revised patch with test fixes. I haven't re-run the tests with this patch but in general the changes to the tests look good. A couple points:

          o In GrantRevokeDDLTest, it looks like the name of SYSPERMS has been mis-spelled.

          o There's a new println in JDBC. That may be ok but it looks like it might be a piece of debug scaffolding which wasn't removed before the patch was generated.

          Thanks!

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the revised patch with test fixes. I haven't re-run the tests with this patch but in general the changes to the tests look good. A couple points: o In GrantRevokeDDLTest, it looks like the name of SYSPERMS has been mis-spelled. o There's a new println in JDBC. That may be ok but it looks like it might be a piece of debug scaffolding which wasn't removed before the patch was generated. Thanks!
          Hide
          Suran Jayathilaka added a comment -

          Added changes to the JUnit test cases so that they pass now.
          I'm having a hard time finding where the failures are occurring in the the harness tests, so I thought I'd submit this updated patch and keep working on them.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Added changes to the JUnit test cases so that they pass now. I'm having a hard time finding where the failures are occurring in the the harness tests, so I thought I'd submit this updated patch and keep working on them. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          My regression test run finished. As expected, the catalogs_b.patch introduces a number of errors, largely related to the fact that the existing tests need to be made aware of the new catalogs:

          Failures in tests run under the old harness:

          altertable
          compressTable
          dropcrash2

          Failures in JUnit tests:

          SystemCatalogTest
          DatabaseMetaDataTest
          PrimaryKeyTest
          ViewsTest
          metadataMultiConnTest
          OfflineBackupTest
          CollationTest
          GrantRevokeDDLTest
          DMDBugsTest
          ToolScripts

          Show
          Rick Hillegas added a comment - My regression test run finished. As expected, the catalogs_b.patch introduces a number of errors, largely related to the fact that the existing tests need to be made aware of the new catalogs: Failures in tests run under the old harness: altertable compressTable dropcrash2 Failures in JUnit tests: SystemCatalogTest DatabaseMetaDataTest PrimaryKeyTest ViewsTest metadataMultiConnTest OfflineBackupTest CollationTest GrantRevokeDDLTest DMDBugsTest ToolScripts
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          The new catalogs_b.patch looks very good. It will need another round or two of adjusting but it is definitely looking solid. I have applied the patch and am running regression tests now. I will post my results when the tests finish.

          I have some comments on the new patch:

          PermDescriptor

          o You should remove the permUUID field from this class since it shadows the oid field in the superclass. In my experience, this kind of shadowing always causes bugs in the future because it is so easy to lose track of which copy of the redundant field is being used. So the constructor for this class should call setUUID( permUUID ) rather than stuffing the shadowing field via "this.permUUID = permUUID".

          o Once you do that, you can get rid of the getUUID(), setUUID(), and getObjectID() methods in this class.

          o I have reservations about the second constructor for this class (the one which is used to create a key descriptor rather than a full-fledged permissions descriptor). However, I see that you are following the data dictionary patterns for the other permissions descriptors. I think you are right to follow the existing patterns. But we should put a sticky note on our brains to remind ourselves to clean up this pattern in the future--partially initialized data is brittle. This is something we can revisit when we collapse all of the permissions catalogs into the master catalog which you are creating. Don't worry, that's another project and not part of the sequence generator work.

          SYSSEQUENCESRowFactory

          o I think that you should flip the order of the keys in the second index. That is, the key order should be ( schemaID, sequenceName ) rather than ( sequenceName, schemaID ). This will speed up the check in DataDictionaryImpl.isSchemaEmpty(). But maybe you have a use for an index with key order ( sequenceName, schemaID ) which I'm not seeing. If so, then I would recommend adding a third index. Note that if you swap the key order, then you will need to make a corresponding change to DataDictionaryImpl.getSequenceDescriptor(String, SchemaDescriptor)

          o I think that buildColumnList() should return an array of 10 column descriptors, one for each column in the table. See, for instance, the corresponding method in SYSALIASESRowFactory.

          SYSROLESRowFactory

          o As with SYSSEQUENCESRowFactory, I think that buildColumnList() should return a larger array, one cell for each column in the table.

          DataDictionary

          o The header comment for getPermDescriptor() does not seem to describe the method.

          EmptyDictionary

          o I think that you can remove the stub for dropSequenceDescriptor(String, TransactionController) because you have removed this method from the DataDictionary interface.

          DataDictionaryImpl

          o The header comments on getUncachedPermDescriptor() getPermDescriptor(), and dropAllPermDescriptors() look like they were copied from pre-existing methods, so they should be revised to describe the new methods.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, The new catalogs_b.patch looks very good. It will need another round or two of adjusting but it is definitely looking solid. I have applied the patch and am running regression tests now. I will post my results when the tests finish. I have some comments on the new patch: PermDescriptor o You should remove the permUUID field from this class since it shadows the oid field in the superclass. In my experience, this kind of shadowing always causes bugs in the future because it is so easy to lose track of which copy of the redundant field is being used. So the constructor for this class should call setUUID( permUUID ) rather than stuffing the shadowing field via "this.permUUID = permUUID". o Once you do that, you can get rid of the getUUID(), setUUID(), and getObjectID() methods in this class. o I have reservations about the second constructor for this class (the one which is used to create a key descriptor rather than a full-fledged permissions descriptor). However, I see that you are following the data dictionary patterns for the other permissions descriptors. I think you are right to follow the existing patterns. But we should put a sticky note on our brains to remind ourselves to clean up this pattern in the future--partially initialized data is brittle. This is something we can revisit when we collapse all of the permissions catalogs into the master catalog which you are creating. Don't worry, that's another project and not part of the sequence generator work. SYSSEQUENCESRowFactory o I think that you should flip the order of the keys in the second index. That is, the key order should be ( schemaID, sequenceName ) rather than ( sequenceName, schemaID ). This will speed up the check in DataDictionaryImpl.isSchemaEmpty(). But maybe you have a use for an index with key order ( sequenceName, schemaID ) which I'm not seeing. If so, then I would recommend adding a third index. Note that if you swap the key order, then you will need to make a corresponding change to DataDictionaryImpl.getSequenceDescriptor(String, SchemaDescriptor) o I think that buildColumnList() should return an array of 10 column descriptors, one for each column in the table. See, for instance, the corresponding method in SYSALIASESRowFactory. SYSROLESRowFactory o As with SYSSEQUENCESRowFactory, I think that buildColumnList() should return a larger array, one cell for each column in the table. DataDictionary o The header comment for getPermDescriptor() does not seem to describe the method. EmptyDictionary o I think that you can remove the stub for dropSequenceDescriptor(String, TransactionController) because you have removed this method from the DataDictionary interface. DataDictionaryImpl o The header comments on getUncachedPermDescriptor() getPermDescriptor(), and dropAllPermDescriptors() look like they were copied from pre-existing methods, so they should be revised to describe the new methods. Thanks, -Rick
          Hide
          Suran Jayathilaka added a comment -

          Thanks Rick for the detailed suggestions on rectifications in the previous patch. Far from being discouraged, the amount of learning and insight I receive is invaluable.

          I attempted to cover most of them in this revised patch. Please review and comment.

          Show
          Suran Jayathilaka added a comment - Thanks Rick for the detailed suggestions on rectifications in the previous patch. Far from being discouraged, the amount of learning and insight I receive is invaluable. I attempted to cover most of them in this revised patch. Please review and comment.
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for the first rev of the patch. Lots of good stuff. Here are some comments. Despite all of the great work here, I think that another patch will be needed and that in turn will probably need some adjusting. Don't be discouraged, this is an iterative process and your foundation here is solid.

          o In a couple of the files, you have removed the imports of individual classes and replaced them with a bulk import of all the classes in a package. Perhaps your IDE did this for you. The convention in Derby is to declare each import individually. I have weak religion on this style topic, but there are some reasons for this convention, which you can find at http://stackoverflow.com/questions/147454/why-is-using-a-wild-card-with-a-java-import-statement-bad/147532 and http://192.220.96.201/essays/java-style/structure.html I recommend sticking with the Derby style at least for your initial submissions.

          o There are funny line endings for the new files in the patch. I think this may be because you haven't set the new files to have native eol-style. Before generating the patch, you need to issue the following command on each of the new files:

          svn propset svn:eol-style native $NEW_FILE

          o I think that PermDescriptor should extend PermissionsDescriptor. This will make the code easier to understand and it will probably make some existing logic just work for the USAGE privilege when you code support for USAGE. Consult RoutinePermsDescriptor for the pattern to follow.

          o Similarly, I suspect that SYSPERMSRowFactory should extend PermissionsCatalogRowFactory and follow the pattern of SYSROUTINEPERMSRowFactory.

          o For some reason one of your new classes, SYSSEQUENCESRowFactory, appears in the patch as a diff from SYSROLESRowFactory rather than as a new class. This makes it impossible to apply the patch and it makes the patch hard to read.

          o Following the existing pattern in DataDictionaryImpl, dropSequence() should be dropSequenceDescriptor() and the argument should be a SequenceDescriptor. I don't think that this drop code is going to do the right thing. It looks to me as though it is trying to use the sequence name (a String datum) as a sequence id (a UUID datum) in order to drop the sequence by using the unique index on sequence id. Take a look at DataDictionaryImpl.dropTrigger() for the pattern to follow.

          o Similarly, getSequenceDescriptor(String sequenceName) should be getSequenceDescriptor(String sequenceName, SchemaDescriptor sd). This is because the sequence name is not globally unique, it is only unique within a given schema. The code here is not going to work. It is trying to use the sequence name as a UUID in order to look up the sequence descriptor using the index on sequence id. Instead, it needs to use the second index on SYSSEQUENCES, the one which has a name and a UUID key. Again, see getTriggerDescriptor(String name, SchemaDescriptor sd) for the pattern to follow.

          o I don't understand how DataDictionaryImpl.dropPerm() and getPermDescriptor() will be used. I suspect that if you make PermDescriptor follow the pattern of RoutinePermDescriptor, then you will not need these methods--you will need other methods!

          o In SYSPERMSRowFactory, it looks as though you have allocated UUIDs for 3 indexes but you have only defined one of them. I suspect you will need more than 1 index. In any event, the index you have defined is the first index, not the 3rd index as indicated in the list of UUIDs.

          o Don't forget to write header comments for the new methods which you add to the DataDictionary interface

          o I think that FileInfoDescriptor would be a good pattern to follow for SequenceDescriptor. In particular, I think you will want SequenceDescriptor to implement UniqueSQLObjectDescriptor. I don't understand why SequenceDescriptor needs a writeExternal() method. Will these descriptors be persisted in any way other than being flattened into tuples in SYSSEQUENCES? I understand the need for the getXXX() methods, but why are the setXXX() methods necessary? I would eliminate them unless these objects really need to be mutable.

          o On second thought, I don't think you need a Formatable id for the the SequenceDescriptors in StoredFormatIds. I apologize for misleading you there. I think you just need a Formatable id for the SequenceDescriptorFinder.

          o Have you tried running the regression tests with this patch? Usually when someone adds a new catalog, this breaks the tests which track the shape of the system schema. You probably need to submit some updated tests and/or test canons with this patch.

          Thanks!
          -Rick

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for the first rev of the patch. Lots of good stuff. Here are some comments. Despite all of the great work here, I think that another patch will be needed and that in turn will probably need some adjusting. Don't be discouraged, this is an iterative process and your foundation here is solid. o In a couple of the files, you have removed the imports of individual classes and replaced them with a bulk import of all the classes in a package. Perhaps your IDE did this for you. The convention in Derby is to declare each import individually. I have weak religion on this style topic, but there are some reasons for this convention, which you can find at http://stackoverflow.com/questions/147454/why-is-using-a-wild-card-with-a-java-import-statement-bad/147532 and http://192.220.96.201/essays/java-style/structure.html I recommend sticking with the Derby style at least for your initial submissions. o There are funny line endings for the new files in the patch. I think this may be because you haven't set the new files to have native eol-style. Before generating the patch, you need to issue the following command on each of the new files: svn propset svn:eol-style native $NEW_FILE o I think that PermDescriptor should extend PermissionsDescriptor. This will make the code easier to understand and it will probably make some existing logic just work for the USAGE privilege when you code support for USAGE. Consult RoutinePermsDescriptor for the pattern to follow. o Similarly, I suspect that SYSPERMSRowFactory should extend PermissionsCatalogRowFactory and follow the pattern of SYSROUTINEPERMSRowFactory. o For some reason one of your new classes, SYSSEQUENCESRowFactory, appears in the patch as a diff from SYSROLESRowFactory rather than as a new class. This makes it impossible to apply the patch and it makes the patch hard to read. o Following the existing pattern in DataDictionaryImpl, dropSequence() should be dropSequenceDescriptor() and the argument should be a SequenceDescriptor. I don't think that this drop code is going to do the right thing. It looks to me as though it is trying to use the sequence name (a String datum) as a sequence id (a UUID datum) in order to drop the sequence by using the unique index on sequence id. Take a look at DataDictionaryImpl.dropTrigger() for the pattern to follow. o Similarly, getSequenceDescriptor(String sequenceName) should be getSequenceDescriptor(String sequenceName, SchemaDescriptor sd). This is because the sequence name is not globally unique, it is only unique within a given schema. The code here is not going to work. It is trying to use the sequence name as a UUID in order to look up the sequence descriptor using the index on sequence id. Instead, it needs to use the second index on SYSSEQUENCES, the one which has a name and a UUID key. Again, see getTriggerDescriptor(String name, SchemaDescriptor sd) for the pattern to follow. o I don't understand how DataDictionaryImpl.dropPerm() and getPermDescriptor() will be used. I suspect that if you make PermDescriptor follow the pattern of RoutinePermDescriptor, then you will not need these methods--you will need other methods! o In SYSPERMSRowFactory, it looks as though you have allocated UUIDs for 3 indexes but you have only defined one of them. I suspect you will need more than 1 index. In any event, the index you have defined is the first index, not the 3rd index as indicated in the list of UUIDs. o Don't forget to write header comments for the new methods which you add to the DataDictionary interface o I think that FileInfoDescriptor would be a good pattern to follow for SequenceDescriptor. In particular, I think you will want SequenceDescriptor to implement UniqueSQLObjectDescriptor. I don't understand why SequenceDescriptor needs a writeExternal() method. Will these descriptors be persisted in any way other than being flattened into tuples in SYSSEQUENCES? I understand the need for the getXXX() methods, but why are the setXXX() methods necessary? I would eliminate them unless these objects really need to be mutable. o On second thought, I don't think you need a Formatable id for the the SequenceDescriptors in StoredFormatIds. I apologize for misleading you there. I think you just need a Formatable id for the SequenceDescriptorFinder. o Have you tried running the regression tests with this patch? Usually when someone adds a new catalog, this breaks the tests which track the shape of the system schema. You probably need to submit some updated tests and/or test canons with this patch. Thanks! -Rick
          Hide
          Suran Jayathilaka added a comment -

          Attaching initial patch (ctalogsa.patch) for the SYSSEQUENCES and SYSPERMS catalogs. Made from svn diff against the updated trunk code. I'm certain there will be quite a few shortcomings in this patch and am eagerly awaiting your feedback. So please review and comment.
          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Attaching initial patch (ctalogsa.patch) for the SYSSEQUENCES and SYSPERMS catalogs. Made from svn diff against the updated trunk code. I'm certain there will be quite a few shortcomings in this patch and am eagerly awaiting your feedback. So please review and comment. Thanks! Suran
          Hide
          Suran Jayathilaka added a comment -

          Following are some questions that I asked Rick Hillegas and Rick's answers to them. Posting here because the conversation wasn't sent to the mailing list, which I will do henceforth.
          -----------------------------------------------------------------------------------------

          1. Once I have the barebones classes for the SEQUENCE catalog, what's the best way to get about testing them and fleshing them out?
          A: It's usually easiest to write the test after you've done the parser changes for CREATE/DROP'ing your schema objects. Right now, you could add an upgrade test to verify that the new catalog is created after hard-upgrade but not after soft-upgrade. The upgrade tests live in trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/

          2. How can I define what comes in the indexColumnPositions array in the SYSSEQUENCESRowFactory?
          I currently have
          private static final int[][] indexColumnPositions =
          {

          {SYSSEQUENCES_SEQUENCEID}

          ,

          {SYSSEQUENCES_SEQUENCENAME, SYSSEQUENCES_SCHEMAID}

          };
          A: That looks like a good start. That is the array which defines the number and shapes of the indexes defined on your catalog. As you get into coding support for compilation and dependency tracking, you may discover that you need to define some other indexes to speed up access to the sequence descriptors. You can add more indexes later if you need them, so you don't have to worry too much about this right now...

          3. What's the use of the "uniqueness" array?
          A: The array is null if ALL of the indexes on the catalog are unique indexes. If any of the indexes is non-unique, then you need to fill in an array of booleans, one per index. The slot in that array is true if the corresponding index is unique, and false otherwise. See for instance SYSCOLPERMSRowFactory. Right now both of your indexes are unique so you can leave the uniqueness array as null.

          4. In StoredFormatIds, I see 2 fields for some elements like TRIGGERS
          i.e.
          /**
          class org.apache.derby.impl.sql.catalog.TriggerDescriptorFinder
          */
          static public final int TRIGGER_DESCRIPTOR_FINDER_V01_ID = (MIN_ID_2 + 320);

          /**
          class org.apache.derby.impl.sql.catalog.TriggerDescriptorFinder
          */
          static public final int TRIGGER_DESCRIPTOR_V01_ID = (MIN_ID_2 + 316);
          What's the difference in usage of these 2 IDs? And why is there not a class named TriggerDescriptorFinder as mentioned in the comments here?
          A: I think that at one point there was a DependableFinder for every kind of TupleDescriptor. The DependableFinders are objects which are serialized into the DEPENDENTFINDER and PROVIDERFINDER columns of SYSDEPENDS. When they are deserialized, they are capable of asking the data dictionary for the correct kind of TupleDescriptors for the corresponding UUID columns in SYSDEPENDS. This is how persistent dependencies are serialized and how they reconstruct the dependency graph when they are read in.
          At some point most of the DependableFinders were collapsed into a single class, DDdependableFinder and some tricky (de)serialization logic was added so that the serialized form of the DependableFinders could be as simple as possible, namely a format id corresponding to the appropriate kind of TupleDescriptor.
          Because triggers can invoke sequence generators, you will have to use SYSDEPENDS to record persistent dependencies between triggers and sequences. So you will need to add a DependableFinder format id as well as a format id for the SequenceDescriptor.

          5. In the Authorizer class, do I need to add fields for creating/modifying/dropping sequences and perms, or are they covered in SCHEMA privileges?
          A: Creating/dropping sequences should behave like the creating/dropping of other schema objects: If you own the schema, then you implicitly have permission to create/drop any kind of schema object inside that schema. So no new privileges are needed just to create/drop sequences.
          To invoke a sequence, however, a new privilege is needed: USAGE. This is like EXECUTE_PRIV or INSERT_PRIV.

          6. Where in the code do the catalog tables get created when creating a new database?
          A: That happens in DataDictionaryImpl.createDictionaryTables().

          7. Do I need to add entries to the C_NodeTypes class? I didn't fully understand the purpose of this class from the javadoc comments.
          A: This class was introduced in order to eliminate a large NodeFactory interface. That was a worthy goal, but I think that as a result, node creation has been made weakly typed and hard to follow. Each kind of node has its own corresponding node type. When you get to the parser work, you will need to add a CreateSequenceNode, a DropSequenceNode, and a NextValue node--each with its own node type in C_NodeTypes.

          Show
          Suran Jayathilaka added a comment - Following are some questions that I asked Rick Hillegas and Rick's answers to them. Posting here because the conversation wasn't sent to the mailing list, which I will do henceforth. ----------------------------------------------------------------------------------------- 1. Once I have the barebones classes for the SEQUENCE catalog, what's the best way to get about testing them and fleshing them out? A: It's usually easiest to write the test after you've done the parser changes for CREATE/DROP'ing your schema objects. Right now, you could add an upgrade test to verify that the new catalog is created after hard-upgrade but not after soft-upgrade. The upgrade tests live in trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/ 2. How can I define what comes in the indexColumnPositions array in the SYSSEQUENCESRowFactory? I currently have private static final int[][] indexColumnPositions = { {SYSSEQUENCES_SEQUENCEID} , {SYSSEQUENCES_SEQUENCENAME, SYSSEQUENCES_SCHEMAID} }; A: That looks like a good start. That is the array which defines the number and shapes of the indexes defined on your catalog. As you get into coding support for compilation and dependency tracking, you may discover that you need to define some other indexes to speed up access to the sequence descriptors. You can add more indexes later if you need them, so you don't have to worry too much about this right now... 3. What's the use of the "uniqueness" array? A: The array is null if ALL of the indexes on the catalog are unique indexes. If any of the indexes is non-unique, then you need to fill in an array of booleans, one per index. The slot in that array is true if the corresponding index is unique, and false otherwise. See for instance SYSCOLPERMSRowFactory. Right now both of your indexes are unique so you can leave the uniqueness array as null. 4. In StoredFormatIds, I see 2 fields for some elements like TRIGGERS i.e. /** class org.apache.derby.impl.sql.catalog.TriggerDescriptorFinder */ static public final int TRIGGER_DESCRIPTOR_FINDER_V01_ID = (MIN_ID_2 + 320); /** class org.apache.derby.impl.sql.catalog.TriggerDescriptorFinder */ static public final int TRIGGER_DESCRIPTOR_V01_ID = (MIN_ID_2 + 316); What's the difference in usage of these 2 IDs? And why is there not a class named TriggerDescriptorFinder as mentioned in the comments here? A: I think that at one point there was a DependableFinder for every kind of TupleDescriptor. The DependableFinders are objects which are serialized into the DEPENDENTFINDER and PROVIDERFINDER columns of SYSDEPENDS. When they are deserialized, they are capable of asking the data dictionary for the correct kind of TupleDescriptors for the corresponding UUID columns in SYSDEPENDS. This is how persistent dependencies are serialized and how they reconstruct the dependency graph when they are read in. At some point most of the DependableFinders were collapsed into a single class, DDdependableFinder and some tricky (de)serialization logic was added so that the serialized form of the DependableFinders could be as simple as possible, namely a format id corresponding to the appropriate kind of TupleDescriptor. Because triggers can invoke sequence generators, you will have to use SYSDEPENDS to record persistent dependencies between triggers and sequences. So you will need to add a DependableFinder format id as well as a format id for the SequenceDescriptor. 5. In the Authorizer class, do I need to add fields for creating/modifying/dropping sequences and perms, or are they covered in SCHEMA privileges? A: Creating/dropping sequences should behave like the creating/dropping of other schema objects: If you own the schema, then you implicitly have permission to create/drop any kind of schema object inside that schema. So no new privileges are needed just to create/drop sequences. To invoke a sequence, however, a new privilege is needed: USAGE. This is like EXECUTE_PRIV or INSERT_PRIV. 6. Where in the code do the catalog tables get created when creating a new database? A: That happens in DataDictionaryImpl.createDictionaryTables(). 7. Do I need to add entries to the C_NodeTypes class? I didn't fully understand the purpose of this class from the javadoc comments. A: This class was introduced in order to eliminate a large NodeFactory interface. That was a worthy goal, but I think that as a result, node creation has been made weakly typed and hard to follow. Each kind of node has its own corresponding node type. When you get to the parser work, you will need to add a CreateSequenceNode, a DropSequenceNode, and a NextValue node--each with its own node type in C_NodeTypes.
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick!

          Thank you for your prompt reply. This is verily a delightful opportunity for me.

          I have started working on the catalogs as you suggested, and will submit a proposal for GSoC when the accepting of proposals commences. I will definitely seek guidance from you and the community should I run into any problem.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick! Thank you for your prompt reply. This is verily a delightful opportunity for me. I have started working on the catalogs as you suggested, and will submit a proposal for GSoC when the accepting of proposals commences. I will definitely seek guidance from you and the community should I run into any problem. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Hi Suran. This is terrific. I would be delighted to mentor you as you implement this feature. I have added this project as one of Derby's Summer of Code possibilities: http://wiki.apache.org/general/SummerOfCode2009#derby-project

          Now that you are ready to code, I would recommend starting with the catalog work:

          1) Add the new SYSSEQUENCES and SYSPERMS tables

          2) Add upgrade logic and tests to support the creation of these catalogs in old (10.5 and earlier) databases

          The SYSROLES table was just added to Derby in 10.5 and should serve as a good example of how to add a new catalog.

          Feel free to pepper me with questions. Thanks!

          Show
          Rick Hillegas added a comment - Hi Suran. This is terrific. I would be delighted to mentor you as you implement this feature. I have added this project as one of Derby's Summer of Code possibilities: http://wiki.apache.org/general/SummerOfCode2009#derby-project Now that you are ready to code, I would recommend starting with the catalog work: 1) Add the new SYSSEQUENCES and SYSPERMS tables 2) Add upgrade logic and tests to support the creation of these catalogs in old (10.5 and earlier) databases The SYSROLES table was just added to Derby in 10.5 and should serve as a good example of how to add a new catalog. Feel free to pepper me with questions. Thanks!
          Hide
          Suran Jayathilaka added a comment -

          Hi Rick,

          First of all thank you very much for posting this detailed functional specification.

          For the Computer Science degree course I am following in my university, we're supposed to complete a research or implementation based project. Having had a wonderful experience working with the Derby community last year, I decided to contribute to Derby as fulfillment of this requirement, and selected this issue, i.e. Adding sequence generators for Apache Derby. In the days since I assigned this issue to myself, I did some background research and submitted a project proposal, literature survey and interim report which were accepted by the university project committee. My plan was to begin the implementation work immediately after the semester exams which have only just finished.

          Although I haven't been able to produce any work I can share with the community, I am now ready to begin with the coding, and would be very much obliged if you'd be willing to act as a mentor to me during the implementation process.

          Furthermore, since Google Summer of Code 2009 is starting, I would like to submit a proposal for implementing sequence generators for Derby, and would like to have your feedback.

          Thanks!
          Suran

          Show
          Suran Jayathilaka added a comment - Hi Rick, First of all thank you very much for posting this detailed functional specification. For the Computer Science degree course I am following in my university, we're supposed to complete a research or implementation based project. Having had a wonderful experience working with the Derby community last year, I decided to contribute to Derby as fulfillment of this requirement, and selected this issue, i.e. Adding sequence generators for Apache Derby. In the days since I assigned this issue to myself, I did some background research and submitted a project proposal, literature survey and interim report which were accepted by the university project committee. My plan was to begin the implementation work immediately after the semester exams which have only just finished. Although I haven't been able to produce any work I can share with the community, I am now ready to begin with the coding, and would be very much obliged if you'd be willing to act as a mentor to me during the implementation process. Furthermore, since Google Summer of Code 2009 is starting, I would like to submit a proposal for implementing sequence generators for Derby, and would like to have your feedback. Thanks! Suran
          Hide
          Rick Hillegas added a comment -

          Attaching the first rev of a functional spec for sequence generators. This spec describes changes needed to create, invoke, and drop sequence generators. This spec does not address the incremental work needed to:

          1) alter an existing sequence generator

          2) wire sequence generators into the Statement.getGeneratedKeys() logic

          Show
          Rick Hillegas added a comment - Attaching the first rev of a functional spec for sequence generators. This spec describes changes needed to create, invoke, and drop sequence generators. This spec does not address the incremental work needed to: 1) alter an existing sequence generator 2) wire sequence generators into the Statement.getGeneratedKeys() logic
          Hide
          Rick Hillegas added a comment -

          Hi Suran,

          Thanks for expressing interest in this feature. It would be a great addition to Derby.

          I think that a successful feature starts out with a good plan. You will want to think about the following:

          A) Figure out how much of the ANSI/ISO syntax you want to implement.

          B) Understand how much work is involved.

          I recommend reading a functional spec for one of our new features in order to get a good overview of what you will need to think about. I recommend taking a look at the functional spec for generated columns. See http://issues.apache.org/jira/browse/DERBY-481

          If you don't already have a copy of the 2008 SQL standard, you can get it here: http://www.wiscorp.com/SQLStandards.html

          More comments below.

          ------------ A ----------------

          I think that the right place to start is to indicate how much of the ANSI/ISO syntax you want to implement.

          The key syntax sections of the 2008 SQL Standard seem to be in part 2:

          4.21 Sequence generators (The overview of how sequence generators behave)

          6.13 <next value expression> (The syntax for selecting the next value from a sequence generator)

          11.62<sequence generator definition> (The syntax for declaring a sequence generator)

          11.63<alter sequence generator statement> (The syntax for changing a sequence generator)

          11.64<drop sequence generator statement> (The syntax for deleting a sequence generator)

          I recommend starting out with a modest plan, which you can grow incrementally over time. Start out with the simplest create/drop syntax:

          CREATE SEQUENCE schemaName.sequenceGeneratorName

          DROP SEQUENCE schemaName.sequenceGeneratorName RESTRICT

          That is,

          1) Don't start out implementing the fancy clauses on the CREATE SEQUENCE statement.

          2) Don't bother implementing ALTER SEQUENCE right away.

          3) Don't bother implementing DROP SEQUENCE ... CASCADE right away.

          When you add support for

          NEXT VALUE FOR schemaName.sequenceGeneratorName

          you will need to prevent this clause from appearing in a number of contexts.

          Besides the syntax, catalog support will be the other major piece of the external api for your feature. You will probably end up adding a new system catalog, SYS.SYSSEQUENCES. A good place to start here is to study the layout of the SEQUENCES table in part 11 of the 2008 SQL standard, section 6.42.

          I don't think this feature has any implications for Derby's implementations of JDBC metadata.

          ------------ B ----------------

          If you read one of our recent functional specs, you will see that you will need to plan for the following pieces of work. Other people may volunteer to do some of this work. At a minimum, I'm sure the community will give you plenty of advice:

          1) Syntax (discussed above)
          2) Catalog support (discussed above)
          3) Dependency management
          4) Upgrade logic
          5) dblook support for recreating your CREATE SEQUENCE statements
          6) User documentation

          Show
          Rick Hillegas added a comment - Hi Suran, Thanks for expressing interest in this feature. It would be a great addition to Derby. I think that a successful feature starts out with a good plan. You will want to think about the following: A) Figure out how much of the ANSI/ISO syntax you want to implement. B) Understand how much work is involved. I recommend reading a functional spec for one of our new features in order to get a good overview of what you will need to think about. I recommend taking a look at the functional spec for generated columns. See http://issues.apache.org/jira/browse/DERBY-481 If you don't already have a copy of the 2008 SQL standard, you can get it here: http://www.wiscorp.com/SQLStandards.html More comments below. ------------ A ---------------- I think that the right place to start is to indicate how much of the ANSI/ISO syntax you want to implement. The key syntax sections of the 2008 SQL Standard seem to be in part 2: 4.21 Sequence generators (The overview of how sequence generators behave) 6.13 <next value expression> (The syntax for selecting the next value from a sequence generator) 11.62<sequence generator definition> (The syntax for declaring a sequence generator) 11.63<alter sequence generator statement> (The syntax for changing a sequence generator) 11.64<drop sequence generator statement> (The syntax for deleting a sequence generator) I recommend starting out with a modest plan, which you can grow incrementally over time. Start out with the simplest create/drop syntax: CREATE SEQUENCE schemaName.sequenceGeneratorName DROP SEQUENCE schemaName.sequenceGeneratorName RESTRICT That is, 1) Don't start out implementing the fancy clauses on the CREATE SEQUENCE statement. 2) Don't bother implementing ALTER SEQUENCE right away. 3) Don't bother implementing DROP SEQUENCE ... CASCADE right away. When you add support for NEXT VALUE FOR schemaName.sequenceGeneratorName you will need to prevent this clause from appearing in a number of contexts. Besides the syntax, catalog support will be the other major piece of the external api for your feature. You will probably end up adding a new system catalog, SYS.SYSSEQUENCES. A good place to start here is to study the layout of the SEQUENCES table in part 11 of the 2008 SQL standard, section 6.42. I don't think this feature has any implications for Derby's implementations of JDBC metadata. ------------ B ---------------- If you read one of our recent functional specs, you will see that you will need to plan for the following pieces of work. Other people may volunteer to do some of this work. At a minimum, I'm sure the community will give you plenty of advice: 1) Syntax (discussed above) 2) Catalog support (discussed above) 3) Dependency management 4) Upgrade logic 5) dblook support for recreating your CREATE SEQUENCE statements 6) User documentation
          Hide
          Suran Jayathilaka added a comment -

          Hi!

          I would like to work on adding this much desired feature to Derby. I have already started reading up on Sequences and going through the code. I would be very grateful for some pointers from experienced Derby developers as to a entry point to begin development.

          Thanks!
          Suran.

          Show
          Suran Jayathilaka added a comment - Hi! I would like to work on adding this much desired feature to Derby. I have already started reading up on Sequences and going through the code. I would be very grateful for some pointers from experienced Derby developers as to a entry point to begin development. Thanks! Suran.
          Hide
          Rick Hillegas added a comment -

          Hi Karl,

          No-one has picked up this enhancement request. Support for sequence numbers did not change in the latest feature release, 10.4.

          Show
          Rick Hillegas added a comment - Hi Karl, No-one has picked up this enhancement request. Support for sequence numbers did not change in the latest feature release, 10.4.
          Hide
          Karl Pietrzak added a comment -

          Is this still true? I ask because the last post here was over a here ago, and perhaps Derby got sequence support along the way. =)

          Show
          Karl Pietrzak added a comment - Is this still true? I ask because the last post here was over a here ago, and perhaps Derby got sequence support along the way. =)
          Hide
          Ryan Shillington added a comment -

          FWIW, this would be very high value. Currently our work around is to create tables with auto-increment that look like sequences. Sucks.

          Also, I believe that DERBY-103 is a duplicate of this issue. It has lots of votes too.

          Show
          Ryan Shillington added a comment - FWIW, this would be very high value. Currently our work around is to create tables with auto-increment that look like sequences. Sucks. Also, I believe that DERBY-103 is a duplicate of this issue. It has lots of votes too.
          Hide
          nicolas de loof added a comment -

          I'd like to use Derby as a replacement for Oracle during development, to allow the "database per developer" principle.

          I can't change my hibernate/jpa annotations and have to set the generator to "sequence". Having support for them in derby would be great for me !

          Show
          nicolas de loof added a comment - I'd like to use Derby as a replacement for Oracle during development, to allow the "database per developer" principle. I can't change my hibernate/jpa annotations and have to set the generator to "sequence". Having support for them in derby would be great for me !
          Hide
          Zied Hamdi added a comment -

          Ok I'm happy it works:

          using these annotations:

          @Id
          @GeneratedValue(strategy = TABLE, generator = "persistent_gen")
          @TableGenerator(name = "persistent_gen", table = "persistent_key_gen", pkColumnName = "seq_name",
          valueColumnName = "seq_value", pkColumnValue = "Persistence_seq", initialValue = 1, allocationSize = 1)

          Regards,
          Zied Hamdi

          Show
          Zied Hamdi added a comment - Ok I'm happy it works: using these annotations: @Id @GeneratedValue(strategy = TABLE, generator = "persistent_gen") @TableGenerator(name = "persistent_gen", table = "persistent_key_gen", pkColumnName = "seq_name", valueColumnName = "seq_value", pkColumnValue = "Persistence_seq", initialValue = 1, allocationSize = 1) Regards, Zied Hamdi
          Hide
          Zied Hamdi added a comment -

          Hi people there,

          I'm using derby as a jee/jpa application endpoint (through hibernate), before I encountred this missing feature Derby seamed to me the perfect product, evolving drastically even in performance if I beleive what was said in the sun tech days(compared to mySql). I think the sequence mecanism is one that should be implemented by a serious database like Derby, one but not the only good reason for that is that persistence frameworks are more and more present in applications, and to have portability between databases (to be able to migrate to Derby), it's important to have this feature that I imagine doesn't take a lot to implement / compared to what it brings to users.

          I'll try the @TableGenerator approach, hoping it will work.

          Regards,
          Zied Hamdi

          Show
          Zied Hamdi added a comment - Hi people there, I'm using derby as a jee/jpa application endpoint (through hibernate), before I encountred this missing feature Derby seamed to me the perfect product, evolving drastically even in performance if I beleive what was said in the sun tech days(compared to mySql). I think the sequence mecanism is one that should be implemented by a serious database like Derby, one but not the only good reason for that is that persistence frameworks are more and more present in applications, and to have portability between databases (to be able to migrate to Derby), it's important to have this feature that I imagine doesn't take a lot to implement / compared to what it brings to users. I'll try the @TableGenerator approach, hoping it will work. Regards, Zied Hamdi
          Hide
          Mario Montoya Martínez added a comment -

          There's a way to get the current value of a sequence in Oracle.

          SELECT sequence_name.CURRVAL FROM DUAL.

          Is that what you need?

          Show
          Mario Montoya Martínez added a comment - There's a way to get the current value of a sequence in Oracle. SELECT sequence_name.CURRVAL FROM DUAL. Is that what you need?

            People

            • Assignee:
              Suran Jayathilaka
              Reporter:
              Tony Dahbura
            • Votes:
              17 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development