Ruby On Rails On Oracle Hurdles, Part 2

I've hit my first bumps in the road to hooking up RoR to an existing Oracle database schema. Like I posted yesterday, designing an RoR app from the ground up would be easier because you can build your database schema to RoR's expectations and limitations. And now I explain in more detail why...

Other than getting connected to an Oracle database (officially "Hurdle #1"), which I discussed yesterday, I've hit a few more snags.

Hurdle #2: Table naming. I'm trying to hook up to an in-house authentication/authorization database that is used by several of our application platforms. For RoR's "coding by convention" to work properly, you would ideally have plural table names, e.g. reps, rep_roles. Then when you generate your scaffolding, you would use singular names for the controller and view, e.g. rep, rep_role.

The existing database had singular table names. Generating scaffolding would come up with an error about the reps table not existing. Of course not, it's rep. My initial thought is, "how can I fool RoR?" And the answer was obvious, create views and see what happens.

create view reps as select * from rep;
create view rep_roles as select * from rep_role;

With newly created views sitting in from of the tables, I go back to the trusty command line and try to generate my scaffolding again. PRESTO!! It worked. It's a hack, it's trashy, it's shameful. But it works, and it was quicker than ripping apart the framework and touching code in inappropriate fashion.

So, Hurdle #2 has been overcome.

Hurdle #3: This one is what I alluded to yesterday about simple keys. These tables don't have simple "id" type keys. In MySQL when I was starting an app from scratch, of course I would use an auto-increment id. Works great. But according to the RoR on Oracle article (http://www.oracle.com/technology/pub/articles/saternos-rails.html), there are expectations for the primary key column being "id" and having a sequence with a naming convention of "singular-table-name"_seq, e.g. rep_seq for table reps.

Well, that's not going to happen here, not for this database. And despite the tagline of the article ("Learn techniques for creating a Ruby on Rails Web application that utilizes a legacy schema."), the article really doesn't address natural primary keys, whether they are simple one-column keys or complex multiple-column keys.

I can generate the scaffolding and launch the server. I get pages displaying all the rows in the table. But clicking on the show, edit, etc. links fail. The error, obviously is that it couldn't find an "id" column to look up the record by. I also noticed the PK fields aren't showing up on the list view or in the "create new" record view.

I've been working on other projects most of the day, so haven't had a chance to work on it beyond identifying what doesn't work.

So, Hurdle #3 remains a hurdle for now.

And I just had a few minutes to Google, and found this article (http://www.oracle.com/technology/pub/articles/tate-activeerecord.html) which basically says the same thing I am realizing. To quote:

But Active Record is not for every application. It does not handle legacy schemas nearly as well. For those, you need a mapping framework, not one that merely wraps tables with simple classes.

But then there is this blog (http://paulmwatson.com/journal/category/ruby-on-rails/) that has some encouraging words about setting the table name and PK column with the set_primary_key and set_table_name methods of the ActiveRecord model. (The specific post is at http://paulmwatson.com/journal/2006/11/02/wrangling-rails/)

Now that is worth a try, but still not so sure it will work for a VARCHAR primary key field, and still not going to cut it for multiple-column primary keys.