Ticket #106 (new defect)

Opened 5 years ago

Elixir 0.7.1 incompatible with Oracle due to lack of Sequence on Primary Key

Reported by: guest Owned by:
Priority: normal Milestone:
Component: core Version: 0.7.1
Keywords: oracle sequence primary key Cc: drobbins@…

Description

Elixir is incompatible with Oracle (tested 10.2.0.4) due to Elixir's lack of support for sequence-based primary keys which Oracle requires.

This can be fixed by modifying entity.py around line 228 as follows, to add a Sequence to the primary key:

           elif not self.has_pk and self.auto_primarykey:
                print "debug autokey"
                if isinstance(self.auto_primarykey, basestring):
                    colname = self.auto_primarykey
                else:
                    colname = options.DEFAULT_AUTO_PRIMARYKEY_NAME
                seq_name = "%s_%s_%s" % (self.tablename, colname, "seq")
                self.add_column(
                    Column(colname, options.DEFAULT_AUTO_PRIMARYKEY_TYPE, Sequence(seq_name),
                           primary_key=True))
        self._pk_col_done = True

I have confirmed that this fix works. However, it's likely incomplete as the code above this area in create_pk_cols() probably needs to be changed in similar fashion, to handle the cases where a primary key exists or auto_primarykey is false. I have not tested those code paths.

This requirement is documented in detail in the SQLAlchemy documentation here:

http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#auto-increment-behavior

It may be best to add an optional=True argument so that the Sequence is not used on databases that do not require it.

I believe that this bug may also impact Firebird as well but I do not have Firebird set up to confirm it.

Note: See TracTickets for help on using tickets.