The pain of full-stack ORM

Rails
1

I work in a nice little support/development/data-center shop which is
split down the middle in regards to application development. On the
one side we have our Java team and a massive Oracle system, and on the
other side we currently have our PHP team which ties into the same
massive Oracle system. The difference between the teams could be
defined as “long term projects” vs. “agile development”.

I was given permission to prototype Rails as a replacement for our out
of hand PHP system, and I came this close |…| to being in the clear
and happily coding with Rails at the enterprise level. The problem?
ActiveRecord doesn’t fit our Multiple Table Inheritance model which is
very deeply integrated into our collective database structure. While I
would love to do things the Rails way, I don’t think I will convince
anyone to rewrite huge Oracle database systems anytime soon.

The alternatives? I love Ruby, and I enjoy Rails. Rolling my own ORM
layer, or bending ActiveRecord is out of the question at the moment.
Part of the point of all this is to introduce a well supported system
to the shop from a…z. Sadly I’ve begun looking towards other
frameworks/languages that give me a little more flexibility at
selecting components (specifically ORM), and it looks like the worst
case scenario is that we turn into a 100% Java shop. I’m not against
Java, but if I have alternatives I’ll jump to those first.

If anyone has suggestions I am completely open to them. Remaining in a
Rails environment is obviously ideal, it really does make application
development enjoyable.

2

On Tue, Sep 19, 2006 at 06:40:59PM -0000, Brian J. wrote:
}
} I work in a nice little support/development/data-center shop which is
} split down the middle in regards to application development. On the
} one side we have our Java team and a massive Oracle system, and on the
} other side we currently have our PHP team which ties into the same
} massive Oracle system. The difference between the teams could be
} defined as “long term projects” vs. “agile development”.
}
} I was given permission to prototype Rails as a replacement for our out
} of hand PHP system, and I came this close |…| to being in the clear
} and happily coding with Rails at the enterprise level. The problem?
} ActiveRecord doesn’t fit our Multiple Table Inheritance model which is
} very deeply integrated into our collective database structure. While
I
} would love to do things the Rails way, I don’t think I will convince
} anyone to rewrite huge Oracle database systems anytime soon.
}
} The alternatives? I love Ruby, and I enjoy Rails. Rolling my own ORM
} layer, or bending ActiveRecord is out of the question at the moment.
} Part of the point of all this is to introduce a well supported system
} to the shop from a…z. Sadly I’ve begun looking towards other
} frameworks/languages that give me a little more flexibility at
} selecting components (specifically ORM), and it looks like the worst
} case scenario is that we turn into a 100% Java shop. I’m not against
} Java, but if I have alternatives I’ll jump to those first.
}
} If anyone has suggestions I am completely open to them. Remaining in
a
} Rails environment is obviously ideal, it really does make application
} development enjoyable.

There is a strong chance that you can deal with this using a combination
of
Single Table Inheritance and Polymorphic Associations. Google for them
(and Rails) and you should find what you need.

–Greg

3

On Sep 19, 2006, at 1:57 PM, Gregory S. wrote:

} defined as “long term projects” vs. “agile development”.
} would love to do things the Rails way, I don’t think I will convince
} anyone to rewrite huge Oracle database systems anytime soon.
…
There is a strong chance that you can deal with this using a
combination of
Single Table Inheritance and Polymorphic Associations. Google for them
(and Rails) and you should find what you need.

–Greg

Have you consider adding Rails-friendly views to your Oracle
database? You may be able to provide unified viesw to the troublesome
tables that will work with STI. Database views are one of the main
tools for using Rails in the enterprise. Of course, it can be
politically difficult to use them.

Scott

4

Scott W. wrote:

Have you consider adding Rails-friendly views to your Oracle
database? You may be able to provide unified viesw to the troublesome
tables that will work with STI. Database views are one of the main
tools for using Rails in the enterprise. Of course, it can be
politically difficult to use them.

Scott

While there are already Rails friendly views in place, they are only
handy for reading data. Writing out to a 2 - 5+ table hierarchy is
impossible with ActiveRecord as far as I can tell, although correct me
if I am wrong.

Today I managed to build a Rails Way solution, although it get’s a
little tricky as the table depth increases. It’s also a little harder
to deal with, since you don’t just do something like
“object.column_name” (which would be ideal), but could be doing
“object.next_object.column_name”, etc.

However, this is proving to be satisfactory at some level since it
keeps me in the Rails development business. I can post how I am doing
this while still maintaining some semblance of vertical table
inheritance if anyone is interested.

Brian

Brian

5

On Sep 20, 2006, at 6:11 PM, Brian J. wrote:

handy for reading data. Writing out to a 2 - 5+ table hierarchy is
keeps me in the Rails development business. I can post how I am doing
this while still maintaining some semblance of vertical table
inheritance if anyone is interested.

Brian

Sure, I’d be interested. Why can’t you update via the views as well?
Are there limitations in Oracle updateable views, is it the schema,
or just a mismatch with ActiveRecord’s conventions?

This is just curiosity – my enterprise database is an un-normalized,
no-referential-integrity pile of data.

Scott

6

On Sep 22, 2006, at 1:32 PM, Brian J. wrote:

has_one :work, :foreign_key => ‘id’
def after_initialize
has_one :work, :foreign_key => ‘id’

#####################
)
@work.save
Key points to note:

Brian J.

Ugh. Thanks for the detail. Always a bit harder in practice than it
appears from the “Enterprise Rub” talk at some conference, isn’t it?
It seems like you are on the road to modeling your database using
delegation, which is probably the way Rails “sees” the database.

7

Scott W. wrote:

Sure, I’d be interested. Why can’t you update via the views as well?
Are there limitations in Oracle updateable views, is it the schema,
or just a mismatch with ActiveRecord’s conventions?

This is just curiosity – my enterprise database is an un-normalized,
no-referential-integrity pile of data.

Scott

When joining one or more tables in Oracle, it turns the view into
read-only by default. There are ways to create updateable views,
however it requires some database level magic to make it work. I guess
in the end the limitation is due to the schema design in general.

The following is my kind of “Rails Way” work around. I admit that
there are still some kinks I am working out, but it does seem to behave
as I would like it to.

#####################

The models:

#####################
class Work < ActiveRecord::Base
set_table_name “work”
set_sequence_name “core_work_mgmt.seq_work”
end

class StatusableWork < ActiveRecord::Base # extends Work
set_table_name “statusable_work”
has_one :work, :foreign_key => ‘id’

def before_create
self.work.save
self.id = self.work.id
end

def before_update
self.work.save
end

def after_initialize
if self.id.nil?
self.work = Work.new
end
end

end

class CnsProject < ActiveRecord::Base # extends StatusableWork
set_table_name “cns_project”
has_one :statusable_work, :foreign_key => ‘id’
has_one :work, :foreign_key => ‘id’

def before_create
self.statusable_work.save
self.id = self.statusable_work.id
end

def before_update
self.statusable_work.save
self.work.save
end

def after_initialize
if self.id.nil?
self.statusable_work = StatusableWork.new
end
end

end

#####################

Some Controller Logic:

#####################

# Note that uncommenting the :include allows me to query using
# stuff like: work.column_name = ?, statusable_work.column_name = ?
def index
  @work = CnsProject.find(
    :all,
    #:include => [:work, :statusable_work],
    :conditions => ["project_number = ?", 12345],
    :limit => 10
   )
end

def update
  @work = CnsProject.find(
    :first,
    :include => [:work],
    :conditions => ["work.name = ? AND project_number = ?",

“Serious Work”, 12345]
)
@work.statusable_work.work.name = “Not so Serious Work”
@work.save

   redirect_to :action => "index"
end

There still seems to be some flaws (like object reference during
updates), but this is about as close as I can get it the “Rails Way”
and make it behave almost like our Java/PHP models. The difference is
that the column/methods aren’t all referenced at the object level, and
saving stuff out correctly requires a little more complicated logic.

Key points to note:

  • Since Work holds the sequence, and all id’s in each table must
    reflect the number Work generates, we have to save a Work object out
    first, then a StatusableWork, then a CnsProject, each setting their id
    from the ‘parent’ object id.
  • When instantiating a new ‘subclass’, it needs to initialize an
    instance of it’s parent class so that those methods can be referenced.

That’s pretty much where I stand right now. I’m open to any more
suggestions and ideas that anyone may have.

Brian J.

8

I guess you could always look at Rbatis…

I have personally never used it but I do use IBatis for my Java
projects.

Its a bit less “automatic” than Activerecord but may be a better fit.

Serge