Forum: Ruby on Rails me.defeated!

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
5c6cb9d9aa9093fdac776f6622f6545b?d=identicon&s=25 Gerald Anderson (gander)
on 2006-04-22 01:10
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
A90204c955db033cd975f7bb0ec9600b?d=identicon&s=25 Ashley Moran (Guest)
on 2006-04-22 01:48
(Received via mailing list)
On Apr 22, 2006, at 12:10 am, Gerald Anderson 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.

> <snip>

> 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
5c6cb9d9aa9093fdac776f6622f6545b?d=identicon&s=25 Gerald Anderson (gander)
on 2006-04-22 01:54
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!
This topic is locked and can not be replied to.