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
)

In the Rails console:

> 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">]

> ts.my_objects
NoMethodError: undefined method `my_objects' for #<Array:0x47c5830>

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

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

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

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

> In the Rails console:

> > 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-399D-48AD-A7A3-2AD48769F99B">]

> > ts.my_objects
> NoMethodError: undefined method `my_objects' for #<Array:0x47c5830>

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

> > 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".

> 2. 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?

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

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

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">

ts.id

=> "88E605D47-621C-4DD6-BD6F-D3ABD93437F8"

ts.ID

=> "88E605D47-621C-4DD6-BD6F-D3ABD93437F8"

Thanks!