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

[Matt S Trout]

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

-- 
      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/ +