[Dbix-class] RFC: DBIx::Class::TypedView (or Is this CPAN worthy?)

[Nilson Santos Figueiredo Junior]

Here we use MySQL and, unfortunately, its views are not as flexible as
the ones available on other RDBMS (Pg, for instance) and you can't
properly code update rules for them. MySQL can only update views whose
rows can be directly mapped to a single table row and, even then, it's
fairly stupid.

In particular, MySQL can't handle  views defined as a simple union of
other tables, which is a use case somewhat present in our
applications.

Let me exemplify the situation.
You've got table A and table B, then you define view V as:

  SELECT
     'A' AS item_type,
     A.some_field AS field
  FROM A
  UNION
  SELECT
     'B' AS item_type,
     B.some_other_field AS field
   FROM B;

There's a direct mapping between each of the rows but MySQL isn't
smart enough to handle it automatically and doesn't provide any means
to allow the rules to be user-specified.

So I ended up coding DBIx::Class::TypedView, which is a DBIC component
that makes insert, update and delete operations DWIM on these tables
provided you correctly specify the type mapping (i.e. which
"item_type" corresponds to another given schema class) inside your
schema class.

In the above case, on your view schema class you'd have to add these
lines in order to make it DWIM:

$pkg->define_typed_view(
    'item_type' => {
        'A' => {
            class => 'ASchemaClass',
            transform => { field => 'some_field' },
        },
        'B' => {
            class => 'BSchemaClass,
            transform => { field => 'some_other_field' }
        }
    }
);

It works for everything that ends up calling insert/update/delete so
relationship helpers also work. Notably, update and deletes on
resultset probably won't work unless you use the _all variants (since,
AFAIK, they are the only ones that call the underlying methods).

This has been pretty useful to me but I don't really know wether it's
too specific to my setup or not. I think that anyone using MySQL views
could find a use it for it but maybe I'm mistaken.

Of course, I'd also be glad to know if I just over-engineered a bit
and there was a much simpler solution.

-Nilson Santos F. Jr.