[
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.
