You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Tony Dahbura (JIRA)" <de...@db.apache.org> on 2005/11/17 05:09:29 UTC
[jira] Created: (DERBY-712) Support for sequences
Support for sequences
---------------------
Key: DERBY-712
URL: http://issues.apache.org/jira/browse/DERBY-712
Project: Derby
Type: New Feature
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
[jira] Commented: (DERBY-712) Support for sequences
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806140#action_12806140 ]
Knut Anders Hatlen commented on DERBY-712:
------------------------------------------
Of course I meant to say derby.language.sequenceGeneratorCacheSize...
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12774398#action_12774398 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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().
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12683527#action_12683527 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Sukhitha Prabhath Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728836#action_12728836 ]
Sukhitha Prabhath Jayathilaka commented on DERBY-712:
-----------------------------------------------------
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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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, catalogs_f.patch, catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, create_drop_sequence_a.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] Commented: (DERBY-712) Support for sequences
Posted by "Ryan Shillington (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12521145 ]
Ryan Shillington commented on DERBY-712:
----------------------------------------
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.
> 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
>
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12794790#action_12794790 ]
Knut Anders Hatlen commented on DERBY-712:
------------------------------------------
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
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12729860#action_12729860 ]
Kathey Marsden commented on DERBY-712:
--------------------------------------
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
> 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
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Updated: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12723241#action_12723241 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Karl Pietrzak (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12626451#action_12626451 ]
Karl Pietrzak commented on DERBY-712:
-------------------------------------
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. =)
> 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
>
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12750018#action_12750018 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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).
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806101#action_12806101 ]
Knut Anders Hatlen commented on DERBY-712:
------------------------------------------
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?
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12830942#action_12830942 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 907654.
> 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] Resolved: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-712.
---------------------------------
Resolution: Fixed
Resolving this issue. I believe that the work is done.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805529#action_12805529 ]
Knut Anders Hatlen commented on DERBY-712:
------------------------------------------
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.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12749939#action_12749939 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12828150#action_12828150 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Thanks, Suran. The patch looks great. I am running tests now.
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12683950#action_12683950 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12663385#action_12663385 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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.
> 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
>
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12803804#action_12803804 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12744124#action_12744124 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Mario Montoya MartÃnez (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-712?page=comments#action_12374817 ]
Mario Montoya MartÃnez commented on DERBY-712:
----------------------------------------------
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?
> 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
[jira] Updated: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12742529#action_12742529 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12742477#action_12742477 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728294#action_12728294 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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;
}
{ <SEQUENCE> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12742007#action_12742007 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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_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] Commented: (DERBY-712) Support for sequences
Posted by "Zied Hamdi (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12516727 ]
Zied Hamdi commented on DERBY-712:
----------------------------------
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
> 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
>
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718882#action_12718882 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12794633#action_12794633 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12787462#action_12787462 ]
Kristian Waagan commented on DERBY-712:
---------------------------------------
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);
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12787016#action_12787016 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Tests passed cleanly. Committed derby-712-02-aa-privilegeNodeCleanup.diff at subversion revision 888060.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Zied Hamdi (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12516735 ]
Zied Hamdi commented on DERBY-712:
----------------------------------
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
> 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
>
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12828225#action_12828225 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805918#action_12805918 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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.
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12663457#action_12663457 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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
>
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12741357#action_12741357 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Hi Suran,
Thanks for the patch. Here are some answers to your question:
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12683918#action_12683918 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728199#action_12728199 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12805137#action_12805137 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Regression tests passed cleanly for me on derby-712-05-af-sequenceGenerator.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, 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12806120#action_12806120 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Thanks, Knut. I'm happy with your proposed property name. I'll make that change in the next rev of the 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, 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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 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.
-------
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12794794#action_12794794 ]
Knut Anders Hatlen commented on DERBY-712:
------------------------------------------
Looks like NextSequenceNode.java was forgotten in the previous commit. Added that file and committed revision 894146.
> 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] Issue Comment Edited: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12741357#action_12741357 ]
Rick Hillegas edited comment on DERBY-712 at 8/10/09 8:07 AM:
--------------------------------------------------------------
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
was (Author: rhillegas):
Hi Suran,
Thanks for the patch. Here are some answers to your question:
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728837#action_12728837 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12727735#action_12727735 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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, 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12726170#action_12726170 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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.
> 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, 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12727698#action_12727698 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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
> 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, 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12626545#action_12626545 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Hi Karl,
No-one has picked up this enhancement request. Support for sequence numbers did not change in the latest feature release, 10.4.
> 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
>
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12733232#action_12733232 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "nicolas de loof (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12518159 ]
nicolas de loof commented on DERBY-712:
---------------------------------------
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 !
> 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
>
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728829#action_12728829 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12787483#action_12787483 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Thanks, Kristian. Added the sanity manager guard in subversion revision 888421.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12723107#action_12723107 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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!
> 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] Assigned: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suran Jayathilaka reassigned DERBY-712:
---------------------------------------
Assignee: Suran Jayathilaka
> 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
>
> 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
Posted by "Sukhitha Prabhath Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12712573#action_12712573 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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.
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12728376#action_12728376 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12718862#action_12718862 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Suran Jayathilaka (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12735411#action_12735411 ]
Suran Jayathilaka commented on DERBY-712:
-----------------------------------------
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.
> 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
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12833880#action_12833880 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Committed derby-712-05-al-sequenceGenerator.diff at subversion revision 910255.
> 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] Commented: (DERBY-712) Support for sequences
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12713202#action_12713202 ]
Rick Hillegas commented on DERBY-712:
-------------------------------------
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
> 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.