Rails - Apr 2006 - me.defeated!

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


-- 
Posted via http://www.ruby-forum.com/.

On Apr 22, 2006, at 12:10 am, Gerald Anderson wrote:
> 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.
>
> <snip>
>
> Table parent
>   field id1 PK
>   field id2 PK
>   field other_fields
>
> Table child
>   field id1 PK
>   field id2 PK
>   field other_fields
>
> <snip>
>
> 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 ; )
>
>
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

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!

-- 
Posted via http://www.ruby-forum.com/.