[jira] Created: (DERBY-712) Support for sequences

[Apache Derby Developers mailing list]

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

[Apache Derby Developers mailing list]

     [ 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

[Apache Derby Developers mailing list]

    [ 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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