[storm] Oracle sequence columns

[James Henstridge]

On Sun, Dec 6, 2009 at 12:38 AM, Jason Baker <jbaker at zeomega.com> wrote:
> On Fri, Dec 4, 2009 at 5:31 PM, Jamu Kakar <jkakar at kakar.ca> wrote:
>>
>> Hi Jason,
>>
>> On Fri, Dec 4, 2009 at 2:43 PM, Jason Baker <jbaker at zeomega.com> wrote:
>> > One thing we'd like to have in storm is the ability to specify a
>> > sequence
>> > that we can use by default on primary keys.  For instance, suppose I
>> > have a
>> > class like this:
>> >     class Foo(object):
>> >         __storm_table__ = 'foo'
>> >         foo_pk = Int(primary=True)
>> > If we want foo_pk to autoincrement on Oracle, our only option is to
>> > define a
>> > trigger that will call nextval on the sequence.  It would be nice to be
>> > able
>> > to do something like this:
>> >     class Foo(object):
>> >         __storm_table__ = 'foo'
>> >         foo_pk = Int(primary=True, sequence_name='foo_seq')
>> > This way, when I insert a new Foo, it will automatically pull from
>> > foo_seq
>> > and I don't have to define a trigger.  Of course for this to be useful,
>> > other platforms would need to ignore that keyword argument.  I'd be
>> > willing
>> > to work on this if someone could give me some feedback into the best way
>> > to
>> > implement this.
>>
>> I don't really understand how Oracle works
>
> One thing that's important to understand is that Oracle doesn't have any way
> to define an IDENTITY/AUTOINCREMENT field.  You may or may not already know
> this, I just thought it would be good to mention it in case you didn't.  :-)
>
>>
>>  In other words,
>> do you need Storm to issue a particular query whenever you perform
>> an insert to get autoincrement behaviour or do you just need to
>> define a trigger as part of your schema?
>
>
> In Oracle circles, a trigger is considered to be a hacky workaround for
> applications that aren't able to work with sequences.  Here's how it would
> work.  Suppose I define a table and sequence like this:
>     CREATE TABLE foo(
>     foo_pk INT PRIMARY KEY);
>     CREATE SEQUENCE foo_seq;
> I can make the table foo use foo_seq one of two ways:
>  1.  I can define a trigger that will automatically get a value from foo_seq
> every time a row is inserted
>  2.  I can write the INSERT query like this:
>     INSERT INTO foo(foo_pk) VALUES (foo_seq.nextval);

PostgreSQL also works in terms of sequences as well.  When you create
a table with:

  CREATE TABLE foo (foo_pk SERIAL PRIMARY KEY);

It is actually short hand for something like:

   CREATE SEQUENCE foo_foo_pk_seq;
   CREATE TABLE foo (foo_pk INT NOT NULL DEFAULT
nextval('foo_foo_pk_seq') PRIMARY KEY);

As the default value pulls from the sequence, if you don't specify the
column in the INSERT query, it automatically gets the sequence value.
Is something like this possible with Oracle?

> Of course, Storm has a way of compiling sequences.  But unless I'm missing
> something, the only real way to take advantage of that would be something
> like this:
>
>     foo_row = Foo()
>     if db_platform == 'oracle':
>         foo_row.foo_pk = Sequence('foo_seq')
>     store.add(foo_row)
> But it would be nice to have a way of defining a column that will get its
> value from a particular sequence automatically.
> Another idea that just occurred to me is that most of the time, sequences
> will be defined by giving the table name plus some kind of suffix like
> "_seq".  So it would save a *lot* of tedium if I could do something like
> this:
> SequenceProperty = sequencemaker(suffix='_seq')
> class Foo(object):
>     __storm_table__ = 'foo'
>     # assume the sequence will be named 'foo_seq' since the table is named
> foo
>     foo_pk = SequenceProperty(primary=True, datatype=Int)
> And have backends like MySQL, SQL Server, and SQLite ignore the fact that
> it's a Sequence property

Well, if you are writing an application for Oracle, you could always
do something like this:

class Foo(object):
    __storm_table__ = 'foo'
    foo_pk = Int(primary=True, default=Nextval('foo_seq'))

Where Nextval is an expression class that compiles to the right
syntax.  If you've got access to an "INSERT ... RETURNING ..." style
statement to retrieve the allocated primary key, then that might be
enough.

James.