ActiveRecord requires sequence numbers in Oracle join table

rubyonrails-talk
1

I am mapping ActiveRecord classes to an existing Oracle database. This means I cannot change the database schema to fit ActiveRecord conventions.

Predictably, I have gotten into trouble: I have several join tables that do not have a primary key column, and therefore no number sequence defined. ActiveRecord insists on requiring a sequence, and gets unhappy when there is none to find.

Is there a way to tell ActiveRecord not to expect a sequence number (and no primary key)?

/Henrik Mårtensson

2

Regular join tables have no primary key normally in Rails. Do you mean those tables would naturally map to Rails join models?

-- fxn

3

Henrik:

I asked the same question months ago and got no responses. Even
though I said :id => false during my migration, AR complained
(ORA-02289: sequence does not exist) that it needed a sequence. I
created the sequence manually just for kicks, and AR complained
(ORA-00904: invalid identifier) that it couldn't insert into an id
column. Duh, there is no id column because we don't want one. So, I
looked further into the docs for AR::Base and tried set_sequence_name (nil) and got ORA-00936: missing expression: select .nextval from
dual. Thus, AR was still trying to use a sequence without a name. I
removed set_sequence_name and tried using set_primary_key to one of
my columns, and AR inserted the sequence number into that column.

So, I do not know how to tell AR not to use a sequence number. But I
thought I'd document what I've tried in case anyone else wants to
compare notes.

Has anyone else gotten any further on this?

Thanks,

-Anthony

4

I have just run into a very similar issue. I have a legacy schema, and the primary key is a char(3) column. There is obviously no sequence.

class Developer < ActiveRecord::Base   set_table_name "developer"   set_primary_key "developerid" end

In my view, the user is allowed to fill in an 'id' field (maxlength="3"), and in the create action, I manually set the id:

    @developer = Developer.new(params[:developer])     @developer.id = params[:id]

When I try to save this, I get the error: ORA-02289: sequence does not exist

There has got to be a way to disable the auto-sequence feature of ActiveRecord...

Anyone?

5

Well, I somehow got past this problem in my code. I guess I just wasn't properly assigning the id somehow. Got my form right and as long as an id is set on the new record, the sequence will not be called.

Here's the relevant code from the create method in ActiveRercord's base.rb:

        if self.id.nil? && connection.prefetch_primary_key?(self.class.table_name)           self.id = connection.next_sequence_value(self.class.sequence_name)         end

Maybe you can try overriding that method with a version of your own to get around your problem with composite keys. Or maybe something like http://compositekeys.rubyforge.org/ would help?

6

Ian Zabel wrote:

Well, I somehow got past this problem in my code. I guess I just wasn't properly assigning the id somehow. Got my form right and as long as an id is set on the new record, the sequence will not be called.

Correct, that's the expected/correct behavior -- so long as you define the PK properly, and set it yourself, it won't try to use a sequence.

7

Thank you for the replies, everyone! I am going to hack the create method. Looks like it is the best option.