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

[Matt S Trout]

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

git://git.shadowcat.co.uk/dbsrights/DBIx-Class-ResultSource-MultipleTableInheritance.git

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.