[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: SequenceGenerator.html 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > createseq_args_bind_b.diff, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, > derby-712-05-al-sequenceGenerator.diff, derby-712-06-aa-restrictedDrops.diff, > SequenceGenerator.html, SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-06-aa-restrictedDrops.diff 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > createseq_args_bind_b.diff, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, > derby-712-05-al-sequenceGenerator.diff, derby-712-06-aa-restrictedDrops.diff, > SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-05-al-sequenceGenerator.diff 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. --- > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > createseq_args_bind_b.diff, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, > derby-712-05-al-sequenceGenerator.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: createseq_args_bind_b.diff 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > createseq_args_bind_b.diff, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, > SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Issue & fix info: [Patch Available] > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, derby-712-05-af-sequenceGenerator.diff, > SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-05-af-sequenceGenerator.diff 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 nu
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: createseq_args_bind_a.diff Reattaching patch, as previous attachment was excluded from ASF license by mistake. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, createseq_args_bind_a.diff, > derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: createseq_args_bind_a.diff 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? > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: (was: createseq_args_bind_a.diff) > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-04-aa-dblook.diff 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > derby-712-04-aa-dblook.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-03-ac-usagePrivilege.diff 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, > SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-03-aa-usagePrivilege.diff 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > derby-712-03-aa-usagePrivilege.diff, SequenceGenerator.html, > sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: sequences_next_value_a.patch 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > SequenceGenerator.html, sequences_next_value_a.patch > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: derby-712-02-aa-privilegeNodeCleanup.diff 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, derby-712-02-aa-privilegeNodeCleanup.diff, > SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: create_drop_sequence_d.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_drop_sequence_d.patch, > create_sequence_a.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: create_drop_sequence_c.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_drop_sequence_c.patch, create_sequence_a.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: create_drop_sequence_b.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, > create_drop_sequence_a.patch, create_drop_sequence_b.patch, > create_sequence_a.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: create_drop_sequence_a.patch 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? > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, cat
[jira] Updated: (DERBY-712) Support for sequences
[
https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suran Jayathilaka updated DERBY-712:
Attachment: create_sequence_a.patch
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;
}
{qualifiedName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
> Support for sequences
> -
>
> Key: DERBY-712
> URL: https://issues.apache.org/jira/browse/DERBY-712
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Environment: feature request
>Reporter: Tony Dahbura
>Assignee: Suran Jayathilaka
> Fix For: 10.6.0.0
>
> Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch,
> catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch,
> catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff,
> create_sequence_a.patch, SequenceGenerator.html
>
>
> 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.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Issue & fix info: (was: [Patch Available]) > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Sukhitha Prabhath Jayathilaka updated DERBY-712: Comment: was deleted (was: Hi Rick, That's great! Thank you so much for your patience and help. I will get started on the CREATE/DROP SEQUENCE work right away. Thanks! Suran) > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: catalogs_h.diff 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: catalogs_g.diff 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! > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, catalogs_g.diff, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_f_2.patch Reattaching modified patch (catalogs_f_2.patch) since 2 required changes in dropAllPermDescriptors() were missing in catalogs_f.patch. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > catalogs_f_2.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_f.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: Improvement > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, > SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_e.patch With this patch all the tests that were failing due to the new catalogs are passing. Please review and comment. Thanks! Suran > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: altertable.diff catalogs_d.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, > catalogs_c.patch, catalogs_d.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_c.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: catalogs_a.patch, catalogs_b.patch, catalogs_c.patch, > SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_b.patch 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. > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: catalogs_a.patch, catalogs_b.patch, > SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Derby Info: [Patch Available] Fix Version/s: 10.6.0.0 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Fix For: 10.6.0.0 > > Attachments: catalogs_a.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Suran Jayathilaka updated DERBY-712: Attachment: catalogs_a.patch 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Attachments: catalogs_a.patch, SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Rick Hillegas updated DERBY-712: Attachment: SequenceGenerator.html 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 > Support for sequences > - > > Key: DERBY-712 > URL: https://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Issue Type: New Feature > Components: SQL > Environment: feature request >Reporter: Tony Dahbura >Assignee: Suran Jayathilaka > Attachments: SequenceGenerator.html > > > 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. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-712) Support for sequences
[ http://issues.apache.org/jira/browse/DERBY-712?page=all ] Mike Matrigali updated DERBY-712: - Component: SQL > Support for sequences > - > > Key: DERBY-712 > URL: http://issues.apache.org/jira/browse/DERBY-712 > Project: Derby > Type: New Feature > Components: SQL > Environment: feature request > Reporter: Tony Dahbura > > 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
