[storm] Oracle sequence columns

[James Henstridge]

On Fri, Mar 5, 2010 at 11:47 PM, Drew Smathers <drew.smathers at gmail.com> wrote:
> On Sun, Dec 6, 2009 at 10:54 AM, Brad Allen <ballen at zeomega.com> wrote:
>>
>> On Sun, Dec 6, 2009 at 12:21 AM, James Henstridge <james at jamesh.id.au>
>> wrote:
>>
>> > 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.
>>
>> We need Foo to work with other database backends, too, not just
>> Oracle; having a default in the context of MS SQL, even if the MSSQL
>> backend compiled Nextval to empty string, would cause syntax errors
>> for MS SQL.
>>
>> It looks like the SQLAlchemy solution to this is to provide a special
>> 'sequence' parameter for column declarations, which would be ignored
>> by backends which don't have sequences.
>>
>> Table('sometable', metadata,
>>        Column('id', Integer, Sequence('some_id_seq'), primary_key=True)
>>    )
>>
>> I think it would make sense to add something like this to Storm.
>>
>
>
> Sorry for the very late addition to this old thread, but am I completely
> missing something here?  We've been using storm for a while now and do
> something just like the above for sequence support in oracle:
> class Foo:
>     __storm_table__ = 'foos'
>    id = Int(primary=True, default=Sequence('foos_sequence')
> The thing that sucks about this is breaking compatibility between backends.
>  This is an issue since unit tests are run with sqlite, so we have some
> funky hacks we run during unit test startup:
> def patchModels():
>      Foo.id = Int(primary=True)
>      ...
> And this sucks more, because references and reference sets have to be reset
> as well (and maybe other details I've missed).
> What I'd really like is a way to use Sequence without breaking other
> backends (essentially turn it into a noop and implement things on the
> database side like autoincrement).

Well, the way to do that would be in the Oracle backend's
Connection.execute() method.  When you store.add() an object, an
Insert() object will be created with insert.primary_key set to the
primary key Column objects.  This object is then passed to execute().

If the Insert object is not setting the primary key, then the backend
could easily modify the statement to set the primary key to the
relevant sequence values.  This way it would be mostly transparent
outside of the backend code.  The main problem is how to  find the
sequence name, which is what Brad was asking about.

So the main thing missing from Storm's core here is some way to
annotate properties with extra information ignored by the core, and a
way to access those annotations from the corresponding Column object.
This is similar to what people who want schema generation have been
after, so perhaps we can work out a design satisfies both use cases.

James.