Table Inheritance based on a function

Hello,
we have a problem in a CMS project that we believe is basic for RoR
developement and could concern others as well - or have already:

RoR implements the 'Single Table Inheritance' (STI) Pattern:
http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html

.. through its 'ActiveRecord' ORM.
The Fowler example mentions a 'type' column and RoR implements this
directly - which we consider inconsistent with DB abastraction:
It forces a 'type' column in the database following the application -
instead of the application asking the DB to derive the type _property_
from immanent record properties.

Practically, we would like to have a database function or condition to
control the type.
As a first step this can be done like this:
Select *, CASE WHEN some_condition THEN 'a' ELSE 'b' END AS type
  FROM table1;

We could define this as a view and put ut in a RoR model.
Problem is RoR can not tell a real table from a view (another
deficiency IMO) - and will try to UPDATE or INSERT the view which
fails.

So what we'd need is a more flexible way to define STI, or maybe a way
to define a different Table for SELECTs (here a DB-view) in a model
than for UPDATEs and INSERTs.

Has anybody done this before or what are your proposals for a solution?

How is this different from setting the type attribute in a before_save
callback, other than moving the calculation from Ruby to SQL and doing
it on *every* query? If it's 100% necessary to move the type
calculation into the DB, triggers could accomplish much the same
thing.

But maybe I'm missing something - is there a use case where caching
the type field like this wouldn't work? The only thing I can think of
is a situation where the object's type depends on *external*
parameters (eg, the current date), but that seems exceptionally
unlikely.

--Matt Jones

Hello,

How is this different from setting the type attribute in a before_save
callback, other than moving the calculation from Ruby to SQL and doing
it on *every* query? ...

... is there a use case where caching
the type field like this wouldn't work? The only thing I can think of
is a situation where the object's type depends on *external*
parameters (eg, the current date),

Of course the reason to do have the type derived from data in every
query would be that the data might change, depending on user input,
external phenomenon or another application accessing the same
database.
Abstracting data from application was one reason to use DBs in the
first place.

If it's 100% necessary to move the type
calculation into the DB, triggers could accomplish much the same
thing.

Triggers could also provide an "updateable view", but this would mean
introducing quite some code into the DB just to facilitate a special
application mechanism.
If the information about 'type' is already given by other columns, an
extra column is more like a cheap hack.

Am I really the only one who sees a shortcoming here?
And has no one ever wanted to access DB-Views with RoR (i.e. having
the app understand that a certain relation is _not_ updateable)?

Hello,

> How is this different from setting the type attribute in a before_save
> callback, other than moving the calculation from Ruby to SQL and doing
> it on *every* query? ...
> ... is there a use case where caching
> the type field like this wouldn't work? The only thing I can think of
> is a situation where the object's type depends on *external*
> parameters (eg, the current date),

Of course the reason to do have the type derived from data in every
query would be that the data might change, depending on user input,
external phenomenon or another application accessing the same
database.
Abstracting data from application was one reason to use DBs in the
first place.

Even the view method is going to end up binding the two together,
unless you plan on somehow returning *different* values for type
depending on the calling application.

> If it's 100% necessary to move the type
> calculation into the DB, triggers could accomplish much the same
> thing.

Triggers could also provide an "updateable view", but this would mean
introducing quite some code into the DB just to facilitate a special
application mechanism.

Maybe I'm being dense, but it doesn't seem like any more "code in the
DB" than defining the view.

If the information about 'type' is already given by other columns, an
extra column is more like a cheap hack.

Or, as the computer scientists call it, "memoization". I'd presume
that the types involved change far less often than the data is
accessed.

Am I really the only one who sees a shortcoming here?
And has no one ever wanted to access DB-Views with RoR (i.e. having
the app understand that a certain relation is _not_ updateable)?

Some Google searching shows that most of the discussion around views
and ActiveRecord focuses on either using updatable views or on doing
read-only stuff with non-updatable views. I did find the Class-table
Inheritance plugin, which may yield some insight for your project
(docs @ http://clti.rubyforge.org/). The biggest issue is that while
views are part of the SQL standard, the rules for updating are very DB-
specific. Since AR is targeted to be database-independent, the result
is totally ignoring the feature.

--Matt Jones

Even the view method is going to end up binding the two together,

Of course, but it will derive the 'type' at the moment of the query,
and not a type that was last UPDATEd by the last RoR application (and
hopefully anybody else accessing the DB).

Maybe I'm being dense, but it doesn't seem like any more "code in the
DB" than defining the view.

Unfortunately it _is_ a lot more.
A database view is per se not updateable - several mechanism are being
discussed for a solution - like here for Postgres:
http://wiki.postgresql.org/wiki/Updatable_views

None of these concepts is simple and without debate.
And IMHO updateable view is also violating the "spirit of the
inventor" of relational DBs.

Or, as the computer scientists call it, "memoization". I'd presume
that the types involved change far less often than the data is
accessed.

The database and its developers are mostly more succesful in deciding
how to optimize access to its tables than an application. This is one
of the organizational decisions a database implies.

Some Google searching shows that most of the discussion around views
and ActiveRecord focuses on either using updatable views or on doing
read-only stuff with non-updatable views. I did find the Class-table
Inheritance plugin, which may yield some insight for your project
(docs @http://clti.rubyforge.org/).

Ok, I'll look closer into this.

The biggest issue is that while
views are part of the SQL standard, the rules for updating are very DB-
specific. Since AR is targeted to be database-independent, the result
is totally ignoring the feature.

Exactly - that's why it would make sense to have a standard RoR method
for setting a DB function - see example in my first post - for
deriving the class; instead of a fixed column.

And concerning DB-views, which is a separate issue:
A standard RoR solution for DB-Views would be nice.