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

[Brandon Black]

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).

-- Brandon