ActiveRecord requires sequence numbers in Oracle join table

Rails
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

On Jan 23, 2007, at 12:15 PM, Henrik wrote:

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.

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

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?

5

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?

6

Ian Z. 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.

8

Ian Z. wrote:

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?

ActiveRecord looks at the id attribute to see if it needs to generate an
id using a sequence, or if its already been supplied. Unfortunately when
you use your own primary key AR still looks at the id column. You can
get around this by aliasing id to your own primary key attribute.

in the example above you can just add the following to the Developer
class

alias developerid id
alias developerid= id=

that way you can get and set developerid and it will update self.id so
active record won’t bother trying to use the sequence.

-enjoy!

-Brian