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?
Hello, On 30 Jun., 16:58, Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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)?
On Jul 3, 9:42 am, bvdb <b...-i0KHexwcgiY@public.gmane.org> wrote:> Hello, > > On 30 Jun., 16:58, Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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
On 4 Jul., 00:10, Matt Jones <al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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.