<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Nov 28, 2014 at 4:04 PM, Christian Lackas <span dir="ltr"><<a href="mailto:lackas@lackas.net" target="_blank">lackas@lackas.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">* Darius Jokilehto <<a href="mailto:dariusjokilehto@yahoo.co.uk">dariusjokilehto@yahoo.co.uk</a>> [141122 07:43]:<br>
<br>
Dear Darius,<br>
<br>
thanks for your input (David as well).<br>
<span class=""><br>
> You can implement this by passing an identity instead of a value, i.e. me.customer_id = me.customer_id. So either pass (untested):<br>
> { -ident => 'me.customer_id' }<br>
> or if that doesn't work a scalar ref:<br>
> \'me.customer_id'<br>
> Granted, it's still not pretty, but it should work.<br>
<br>
</span>unfortunately, passing in literal parameters or even references to raw<br>
SQL does not work with bind values. Which does make sense since the<br>
whole point of bind values is that they are safely escaped values and<br>
avoid having to check the input yourself.<br>
<br>
Unfortunately, bind values seem the only way one can communicate with a<br>
virtual views, which makes them a lot less flexible than they could be.<br>
<br>
Will try to create views on the fly now, however, have not yet found out<br>
how to do this (that is not having the Schema load them automatically<br>
from pm files)...<br></blockquote><div><br></div><div>Interesting problem... As it turns out, adding a result source dynamically is not that difficult. The following works for me:</div><div><br></div></div></div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">use feature qw(say);</font></div><div><span style="font-family:monospace">use Data::Dumper;</span><br></div><div><font face="monospace">use Scalar::Util qw(blessed);</font></div></div></div></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div class="gmail_quote"><div><font face="monospace">use Class::MOP;</font></div><div><font face="monospace"><br></font></div></div></div></blockquote><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div class="gmail_quote"><div><font face="monospace">my $schema = MyApp::Schema->connect(...);</font></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">my $meta_class = Class::MOP::Class->create_anon_class(</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><span class="" style="white-space:pre"> </span>superclasses => [ qw(DBIx::Class::Core) ],</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">my $class = $meta_class->name;</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><div><font face="monospace">(my $table = $class) =~ s/::/_/g;</font></div></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->load_components("InflateColumn::DateTime");</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->table_class('DBIx::Class::ResultSource::View');</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->table($table);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->result_source_instance->is_virtual(1);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->result_source_instance->view_definition(q{</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><span class="" style="white-space:pre"> </span>SELECT 42 as number, "foo" as string, NOW() as date</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">});</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$class->add_columns(</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"> number => { data_type => "bigint", default_value => 0, is_nullable => 0 },</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"> string => { data_type => "varchar", is_nullable => 1, size => 255 },</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"> date => { data_type => "timestamp", datetime_undef_if_invalid => 1, timezone => "UTC" },</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">$schema->register_source(Foo => $class->result_source_instance);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">my $row = $schema->resultset('Foo')->first;</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><br></font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">say blessed($row);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">say blessed($row->date);</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">say Dumper({ $row->get_columns });</font></div></div></div></div></blockquote><div class="gmail_extra"><div class="gmail_quote"><div><br></div><div>Creating a "virtual view factory" should mostly be a SMOP. Additionally, with a bit of introspection, you could copy column and relationship definitions from your normal result sources where it makes sense. This would allow you to do something a la:</div><div><br></div></div></div><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">my $view = $schema->create_virtual_view(</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><font face="monospace"><span class="" style="white-space:pre"> </span>ArtistAlbumCountView => </font><span style="font-family:monospace">q{ SELECT artist_id, COUNT(*) AS album_count FROM album GROUP BY artist_id },</span></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><span class="" style="white-space:pre"> </span>artist_id => '<a href="http://artist.id">artist.id</a>', # copy column + rel info</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace"><span class="" style="white-space:pre"> </span>album_count => { data_type => 'bigint', ... },</font></div></div></div></div><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">);</font></div></div></div></div></blockquote><div style="font-family:monospace"><font face="monospace"><br></font></div><font face="arial, helvetica, sans-serif">Which would presumably allow you to traverse relations as usual:</font><div><font face="arial, helvetica, sans-serif"><br></font><blockquote style="margin:0px 0px 0px 40px;border:none;padding:0px"><div class="gmail_extra"><div class="gmail_quote"><div><div><font face="monospace">my $artist = $view->search->first->artist;</font></div></div></div></div></blockquote><div class="gmail_extra"><div class="gmail_quote"><div><br></div><div>Some things to look out for would be leaking memory if you create a large number of views. DBIx::Class implements unregister_schema() but I don't see any supported way of destroying the result source instance.</div><div><br></div><div>Also, I'm not sure to which degree you can destroy a package/class and avoid leaking memory over time. Things like having a pool of reusable class names or hashing the SQL query and putting it in the class name to avoid duplicating views might help here.</div><div><br></div><div>On the other hand if your workers only serve a limited number of requests before being restarted, this may never be a real problem.<br></div><div><br></div><div>Good luck! :-)</div><div><br></div><div>/L</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<span class="im"><br>
Christian<br>
<br>
--<br>
Dr. Christian Lackas, Managing Partner<br>
inviCRO, LLC -- In Imaging Yours<br>
<a href="http://www.invicro.com/" target="_blank">http://www.invicro.com/</a> <a href="http://www.spect-ct.com/" target="_blank">http://www.spect-ct.com/</a><br>
<br>
</span><div class=""><div class="h5">_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</div></div></blockquote></div><br></div></div></div>