[Dbix-class] RFC: Materialized views and DBIx::Class

[Matt S Trout]

Brandon Black wrote:
> On 3/29/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
>> J. Shirley wrote:
>>> Hey Matt,
>>>
>>> Sorry I'm not near an IRC client, but I wanted to run this by you to
>>> get your thoughts.  I'm looking at a materialized view system (pseudo
>>> views in PostgreSQL, but it should work the same in Oracle as well)
>>> for DBIx::Class.
>>>
>>> Since a view is composed of a grouping of other queries, usually to
>>> turn expensive queries into cheap queries, the find and find_or_create
>>> methods are effectively useless.  I'm thinking to change that it would
>>> be a good idea to add a plugin that allows the schema package to
>>> define what the materialized view is defined with.
>>>
>>> At the very least, a new relationship type like "composed_from" and
>>> then point to a relationship.  It would be almost the same as
>>> belongs_to but would allow the composed_from relations to be grouped
>>> together so the materialized view's create and find_or_create
>>> functions can be overloaded to create all subordinate records.
>>>
>>> The example I'm working with now is a view that combines business
>>> location, information, regions and some other attribute information
>>> that is performed from aggregate queries.  The aggregate queries are
>>> has_many relationships (really, might_have's since it doesn't need it)
>>> that are grouped but the aggregate data must have records to pull
>>> from.  I would ideally like to call create($attr ) and have
>>> DBIx::Class automatically send the right fields to the corresponding
>>> create functions in the order as determined by "composed_with".
>>>
>>> What are you thoughts on this?  Any obvious problems?  I know it has
>>> some short comings (as it won't deal with aggregate queries) but I
>>> think it'll do the trick.  If the idea gets your general approval (or
>>> fwd to others whom are more experienced in this area) I'll start
>>> working on a module for this.
>> Build a resultset representing the view. Reflect this into a resultsource
>> object. from clause returns the (SELECT ...), unless a flag is set that it's
>> been deployed as a real view, in which case just return the view name.
>>
>> Move insert/update/delete to being resultsource ops to get the last of the db
>> logic out of the objects and allow all the magic to happen outside the class
>> itself. No relationship should be required.
>>
>> I think. And I've sent this reply to the list as well so we can get this
>> conversation going properly :)
>>
> 
> Note that in PostgreSQL (and presumably at least some of the others)
> this can also be done at the database level.  For PostgreSQL it's
> called the "Rules" system:
> http://www.postgresql.org/docs/8.1/static/rules.html
> 
> In theory, if you create a materialized view with all of the correct
> Rules in place, you can treat the view just like a normal table, and
> all of the operations will trickle down to the correct underlying
> tables in the right way automagically (or in a very wrong way if
> that's what you want).

That's what I meant by "a real view". Read-only views aren't nearly as much fun :)

We'd still want to have all the metadata in DBIC though so it understands what 
other objects the current object is equivalent to etc.

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +