[storm] get_insert_identity on a firebird backend

[Môshe Van der Sterre]

Hi all,

I've been building a Firebird backend for storm, and am glad to say it
passes all database and store tests.

But there is one major problem: Firebird does not support any kind of
insert identity.
Firebird does support generators (sequences), but it has no implicit
auto increment feature (according to the documentation, auto increment
should be implemented explicitly as trigger or on the client side)
Implementing it client side is the only reliable way to know the
insert identity.
Unfortunatly, this is no easy task without presumptions on the database schema.

I think the clearest way for the user is something like:

db = create_database("firebird://user:pass@host/database.gdb")
class Foo(object):
    __storm_table__ = "foo"
    id = Int(primary=True, default=db.Generator("foo_id_generator"))
    title = Unicode()

I have implemented the db.Generator for a large part, but it hacks
away on many objects to get the information where it is needed. (think
about the connection object and table + column names)


The solution that needs no backend code (and eliminates the calls to
get_insert_identity):

db = create_database("firebird://user:pass@host/database.gdb")
store = Store(db)

def generator(name):
    def fetch():
        return store.execute("SELECT gen_id(%s, 1) FROM RDB$DATABASE"
% name).get_one()[0]
    return fetch

class Foo(object):
    __storm_table__ = "foo"
    id = Int(primary=True, default_factory=generator("foo_id_generator"))
    title = Unicode()

IMO, this is a lot boilerplate code, and it needs a store to be
available on class definition time.

I'm a bit stuck on finding a good solution, any ideas?

-- 
Mvg
Môshe van der Sterre
http://www.moshe.nl/
http://www.coecu.nl/