Problems working with Legacy database - associations

Hi Folks,

I’m new to Rails (and Ruby), and right off the bat, I need to work
with a legacy database (SQL Server 2008). I’m having trouble building
my models and specifying the associations. The primary key of each
table is a field called “ID”, but it is not an auto-incrementing
integer. It’s a UUID. The table names are not plurals and some of
them are reserved words n Ruby, the field names are all-caps, except
for the field called ‘Type’.

Let me show you three tables and their corresponding models:

create table CLASSIFICATION (
ID uniqueidentifier primary key not null,
CLASS_TEXT varchar(64) not null
)

create table TABLE_SCHEMA (
ID uniqueidentifier primary key not null,
TABLE_NAME varchar(255) not null,
DISPLAY_NAME varchar(255) not null,
Type tinyint not null,
DESCR varchar(255),
DELETED_FLAG bit not null default 0,
CLASS_ID uniqueidentifier not null
)

create table OBJECT (
ID uniqueidentifier primary key not null,
PHYSICAL_NAME varchar(255) not null,
DISPLAY_NAME varchar(255) not null,
DELETED_FLAG bit not null default 0,
CLASS_ID uniqueidentifier not null
)

±-------------------------+
±-------------------------+
| |
<---------------------------| |
| CLASSIFICATION | | TABLE_SCHEMA |
| |<-------+ ±----

| |
±-------------------------+ | |
±--------------------------+
| |
±------------+
| |
| OBJECT |
| |
±------------+

Here are my models:

class Classification < ActiveRecord::Base
set_table_name ‘classification’
set_primary_key ‘id’

has_many :table_schemas,
:class_name => ‘TableSchema’,
:foreign_key => ‘class_id’
has_many :my_objects,
:class_name => ‘MyObject’,
:foreign_key => ‘class_id’

every model has setUuid, so I won’t show it in the other models

before_save :setUuid
private
def setUuid
self.ID = UUIDTools::UUID.timestamp_create()
end
end

class TableSchema < ActiveRecord::Base
set_table_name ‘table_schema’
set_primary_key ‘id’
self.inheritance_column = nil # because one of the columns is ‘Type’

has_many :my_objects,
:class_name => ‘MyObject’,
:foreign_key => ‘table_id’
belongs_to :classification,
:class_name => ‘Classification’,
:foreign_key => ‘class_id’
end

class MyObject < ActiveRecord::Base # can’t call the class
“Object”
set_table_name ‘object’
set_primary_key ‘id’

belongs_to :table_schema,
:class_name => ‘TableSchema’,
:foreign_key => ‘table_id’
belongs_to :classification,
:class_name => ‘Classification’,
:foreign_key => ‘class_id’
end

In the Rails console:

irb> ts = TableSchema.find(:first, :conditions => “table_name =
‘ENTITY_INST’”)
=> [#<TableSchema ID: “88E65D47-621C-4DD6-BD6F-B9ABD93437F8”,
TABLE_NAME: “ENTITY_INST”, DISPLAY_NAME: “Entity Instance”
, TYPE: 1, DESCR: “All “real things” - by label”, DELETED_FLAG:
false, CLASS_ID: “085F7B9E-3
99D-48AD-A7A3-2AD48769F99B”>]

irb> ts.my_objects
NoMethodError: undefined method `my_objects’ for #Array:0x47c5830

irb> c = Classification.last
=> #<Classification ID: “3E3A8383-8469-4847-8485-C6761B09FD46”,
CLASS_TEXT: “UNCLASSIFIED”>

irb> c.my_objects
=> []

  1. Why is ts.my_objects not defined, but c.my_objects IS (apparently)
    defined?
  2. c.my_objects should return dozens of MyObject objects. Why does it
    return none?
  • Mark

I’m using Rails 3.0.3
Ruby 1.8.7
SQL Server 2008
Windows XP ()

On 15 Feb 2011, at 20:26, Mark [email protected] wrote:

99D-48AD-A7A3-2AD48769F99B">]

  1. Why is ts.my_objects not defined, but c.my_objects IS (apparently)
    defined?

It looks like you’re getting an array containing a single object back,
rather than that single object. Not sure why.

  1. c.my_objects should return dozens of MyObject objects. Why does it
    return none?

Does the SQL generated (check the log file) look right?

On Feb 16, 3:21am, Frederick C. [email protected]
wrote:

=> [#<TableSchema ID: “88E65D47-621C-4DD6-BD6F-B9ABD93437F8”,

irb> c.my_objects
=> []

  1. Why is ts.my_objects not defined, but c.my_objects IS (apparently)
    defined?

It looks like you’re getting an array containing a single object back, rather
than that single object. Not sure why.

No, I’m getting a “NoMethodError: undefined method `my_objects’” from
“ts.my_objects”, and I’m getting an empty array from c.my_objects".

  1. c.my_objects should return dozens of MyObject objects. Why does it
    return none?

Does the SQL generated (check the log file) look right?

The SQL generated by c.my_objects is:

SELECT [object].* FROM [object] WHERE ([object].class_id = NULL)

Obviously, “class_id = NULL” is incorrect. My guess is that NULL is
the return value of a call to c.id, and indeed, c.id return nil. c.ID
returns the correct value. I think that I need to add a method called
id that returns ID. Is that right? How do I do that?

On Feb 16, 7:48 pm, “Robert Pankowecki (rupert)”
[email protected] wrote:

Maybe try
set_primary_key ‘ID’
instead of
set_primary_key ‘id’

?

Robert Pankowecki

Thanks, Robert.

Doing

set_primary_key ‘ID’

seems to work. So, now

irb> ts = TableSchema.first
=> #<TableSchema ID: “88E605D47-621C-4DD6-BD6F-D3ABD93437F8”,
TABLE_NAME: “ENTITY_INST”, DISPLAY_NAME: “Entity Instance”, Type: 1,
DESCR: “All “real things” - by label”, DELETED_FLAG: false,
CLASS_ID: “085F7B9E-39 9D-48AD-A7A3-2AD45269F99B”>
irb> ts.id
=> “88E605D47-621C-4DD6-BD6F-D3ABD93437F8”
irb> ts.ID
=> “88E605D47-621C-4DD6-BD6F-D3ABD93437F8”

Thanks!

Obviously, “class_id = NULL” is incorrect. My guess is that NULL is
the return value of a call to c.id, and indeed, c.id return nil. c.ID
returns the correct value. I think that I need to add a method called
id that returns ID. Is that right? How do I do that?

Maybe try
set_primary_key ‘ID’
instead of
set_primary_key ‘id’

?

Robert Pankowecki

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs