[storm] Oracle sequence columns

[Jason Baker]

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);

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://lists.ubuntu.com/archives/storm/attachments/20091205/8929fdd4/attachment.htm