[Dbix-class] Dynamic/Flexible DBIC views

Lasse Makholm lasse at unity3d.com
Fri Nov 28 23:04:16 GMT 2014


On Fri, Nov 28, 2014 at 4:04 PM, Christian Lackas <lackas at lackas.net> wrote:

> * Darius Jokilehto <dariusjokilehto at yahoo.co.uk> [141122 07:43]:
>
> Dear Darius,
>
> thanks for your input (David as well).
>
> > You can implement this by passing an identity instead of a value, i.e.
> me.customer_id = me.customer_id. So either pass (untested):
> >  { -ident => 'me.customer_id' }
> > or if that doesn't work a scalar ref:
> >  \'me.customer_id'
> > Granted, it's still not pretty, but it should work.
>
> unfortunately, passing in literal parameters or even references to raw
> SQL does not work with bind values. Which does make sense since the
> whole point of bind values is that they are safely escaped values and
> avoid having to check the input yourself.
>
> Unfortunately, bind values seem the only way one can communicate with a
> virtual views, which makes them a lot less flexible than they could be.
>
> Will try to create views on the fly now, however, have not yet found out
> how to do this (that is not having the Schema load them automatically
> from pm files)...
>

Interesting problem... As it turns out, adding a result source dynamically
is not that difficult. The following works for me:

use feature qw(say);
use Data::Dumper;
use Scalar::Util qw(blessed);

use Class::MOP;

my $schema = MyApp::Schema->connect(...);

my $meta_class = Class::MOP::Class->create_anon_class(
superclasses => [ qw(DBIx::Class::Core) ],
);

my $class = $meta_class->name;
(my $table = $class) =~ s/::/_/g;

$class->load_components("InflateColumn::DateTime");
$class->table_class('DBIx::Class::ResultSource::View');
$class->table($table);
$class->result_source_instance->is_virtual(1);
$class->result_source_instance->view_definition(q{
SELECT 42 as number, "foo" as string, NOW() as date
});

$class->add_columns(
  number =>  { data_type => "bigint", default_value => 0, is_nullable => 0
},
  string =>  { data_type => "varchar", is_nullable => 1, size => 255 },
  date   =>  { data_type => "timestamp", datetime_undef_if_invalid => 1,
timezone => "UTC" },
);

$schema->register_source(Foo => $class->result_source_instance);

my $row = $schema->resultset('Foo')->first;

say blessed($row);
say blessed($row->date);
say Dumper({ $row->get_columns });


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:

my $view = $schema->create_virtual_view(
ArtistAlbumCountView => q{ SELECT artist_id, COUNT(*) AS album_count FROM
album GROUP BY artist_id },
artist_id => 'artist.id', # copy column + rel info
album_count => { data_type => 'bigint', ... },
);


Which would presumably allow you to traverse relations as usual:

my $artist = $view->search->first->artist;


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.

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.

On the other hand if your workers only serve a limited number of requests
before being restarted, this may never be a real problem.

Good luck! :-)

/L



>
> Christian
>
> --
> Dr. Christian Lackas, Managing Partner
> inviCRO, LLC -- In Imaging Yours
> http://www.invicro.com/  http://www.spect-ct.com/
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20141129/697bebe2/attachment.htm>


More information about the DBIx-Class mailing list