[Dbix-class] Schema::Loader and views on Pg

Matt S Trout mst at shadowcat.co.uk
Sun May 2 19:59:56 GMT 2010

On Sat, May 01, 2010 at 12:53:28PM -0700, Dorian Taylor (Lists) wrote:
> Hey guys, been a while...

Welcome back.
> I have a Postgres (current) database that has a bunch of log tables.  
> On top of those is a bunch of views that take the most recent record,  
> basically going like:
> CREATE VIEW foo AS SELECT * FROM foo_log a
> WHERE NOT EXISTS (SELECT 1 from foo_log b where b.since > a.since);
> ("Since" is a timestamp and part of the primary key in the log table,  
> but not in the view, of course.)
> Anyhow, I'm trying to use Schema::Loader (current) to build out the  
> schema and it (naturally) sees these views as not having primary keys  
> and the columns as nullable (they aren't in the underlying tables) and  
> of course no relationships, though the underlying log tables are  
> related to one another. I don't expect Schema::Loader can see that, I  
> just want to override it.
> So what I'm wondering is if there is a way to tell Schema::Loader  
> "these views are just like those tables minus one dimension" or if I'm  
> better off just concocting the classes for those views by hand.

What about letting S::L pull the column names and then just setting the
primary keys and adding rels as a fixup?

Note that once you've got $other_class loaded you can always do

my $source = My::Schema::Foo->result_source_instance;

and then pull the relationships straight out of the main foo source.

See the code in


for some interesting Pg madness with views and result source introspection
(also, mad props to Amiri Barksdale for documenting the thing for me - just
waiting for a couple more people to say "yes it works" and I'll see about
shipping it to CPAN)

> Oh, one other thing. I could have sworn a few years ago that I could  
> abridge the class names in relation specs, just putting Foo instead of  
> My::Schema::Foo in has_many/belongs_to etc. Did that change?

It only ever sort of worked. In fact, IME it only worked reliably if you
were me (or at least wrote all your add_relationship calls by hand with
explicit join conditions).

After trying about ten different ways to fix it and realising that very
few people used the thing at all, I ripped it out sideways on the grounds
that "never works" was overall less annoying than "mostly works except you
get half way through a project and hit a bug and get an incomprehensible
error message".

Also, aliased.pm can pretty much eliminate the extra typing just as well :)

Matt S Trout - Shadowcat Systems - Perl consulting with a commit bit and a clue

http://shadowcat.co.uk/blog/matt-s-trout/   http://twitter.com/shadowcat_mst/

Email me now on mst (at) shadowcat.co.uk and let's chat about how our Catalyst
commercial support, training and consultancy packages could help your team.

More information about the DBIx-Class mailing list