Me.defeated!


#1

Good afternoon (as appropriate ; )

I’m having a problem and thought I’d run it past these forums to see if
there even was a reasonable solution to it.

I’m trying to set up a rails application to pull data from a legacy
database. The trick is, that the database really wasn’t created with
RoR convention over configuration in mind. The trick is that every
table has compound primary keys, and no foreign key references. So, for
instance:

Table parent
field id1 PK
field id2 PK
field other_fields

Table child
field id1 PK
field id2 PK
field other_fields

Are the table definitions (informally anyway). Now, the way that this
particular DB works is that to get the child for any parent, you just
find the same id1 and id2. So the compound primary key is sort of an
informal kind of foreign key. I’m not a DBA, but the only possible
reason I can think of why this was done this was is that it was done in
MySQL before you could create FKs.

So, what I"m trying to do is create RoR models from these. In general I
need.

class Parent < ActiveRecord::Base
set_table_name “parent” # it doesn’t follow naming convention
has_one :child
end

class Child < ActiveRecord::Base
set_table_name “child”
belongs_to :parent
end

I try to get them. . .

parents = Parent.find(:all)
puts “parents count #{parents.size}” #this works fine
puts “child count #{parents.child.size}” # not so lucky ; )

It blows the following exception:

undefined method ‘child’ for #Array:0xb754722c

My understanding (as somebody who is still a novice at this rails stuff)
is that the “has_one :child” should create all of the Child methods in
the Parent class.

So, since that’s basic rails models, I figure it’s due to the fact that
there are no foreign keys defined that it doesn’t build the methods. I
did find the :class_name, :foreign_key, etc arguments for has_one and
belongs_to, but can’t figure out for the life of me how to handle this
situation with these funky compound keys.

Does anybody have any recommendations or pointers where I might find
some specific information about situations like this?

Thanks in advance!!

gander


#2

On Apr 22, 2006, at 12:10 am, Gerald A. wrote:

field id2 PK
set_table_name “parent” # it doesn’t follow naming convention
parents = Parent.find(:all)
puts “parents count #{parents.size}” #this works fine
puts “child count #{parents.child.size}” # not so lucky ; )

Hi gander

The problem here is that Rails does not support composite keys. It
expects either “id” or another single column that you name. No way
round that unfortunately.

So, since that’s basic rails models, I figure it’s due to the fact
that
there are no foreign keys defined that it doesn’t build the
methods. I
did find the :class_name, :foreign_key, etc arguments for has_one and
belongs_to, but can’t figure out for the life of me how to handle this
situation with these funky compound keys.

Does anybody have any recommendations or pointers where I might find
some specific information about situations like this?

The foreign keys aren’t actually an issue. Rails doesn’t analyse the
key constraints in your database to work out the relationships,
because it can’t infer the latter from the former… hence why you
have to use “has_XXX” and “belongs_to”.

I can only think of one way round your problem if you want to use the
dynamic Rails methods: create views that construct a virtual unique
id. This will look something (depending on your choice of SQL back-
end) like:

CREATE VIEW parents AS
SELECT (id1 * 10000000 + id2)::int4 as id, <other_fields>
FROM parent

CREATE VIEW children AS
SELECT (id1 * 10000000 + id2)::int4 as id, <other_fields>
FROM child

class Parent < ActiveRecord::Base
has_one :child
end

class Child < ActiveRecord::Base
belongs_to :parent
end

Now, if my sleep-deprived brain is still working, you should be able
to access the data through the views.

You say this is a legacy data. If you only need to read the data,
you’re done. If you want to write to the database, it will be more
complex. Your database system will need to support updateable
views. PostgreSQL will certainly handle this - the latest MySQL
MIGHT, but it’s updateable view support is ropey at best. You need
to decompose the virtual ID in the database into id1 and id2. You
didn’t say what meaning the two ids have, and also I don’t know if
Rails lets you control the id it uses to create new rows, which
together mean it may not be possible.

The bottom line is - supporting composite keys in general is HARD.
The only real way to handle them appears to be to migrate the data,
which is often not possible.

Ashley


#3

Ashley Thanks for the info! That’s a bummer that it’s not doable in a
straight-forward manner. The database is actually our Snort database
and the fields are the SID (sensor id) and CID (event id). Those two
fields are primary keys for every child table to the event table.

I do have to do modifications, so I may just end up writing some
simplistic finder methods in the parent table classes.

Thanks again!