db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-712) Support for sequences
Date Wed, 12 Aug 2009 17:49:14 GMT

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


Mime
View raw message