Dynamic/Flexible DBIC views

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Dynamic/Flexible DBIC views

Christian Lackas
Hi Everybody,

tl;dr: what is the best way to dynamically create the definition of
a virtual view or create an entire virtual view on the fly?

Background:
To fetch aggregated data from the database, I recently started to add
virtual views to our schema (it quickly became to hard to express the
joins and subqueries within the constraints of DBIC).

That said, I often have a family of related queries, that produce the
same output, however, based on different input. E.g. I would like to
aggregate project information for one customer, a set of customers or
all customers. The aggregate information for instance is the
count/sum/min/max/etc of revenue per month.

If I am not mistaken then the user can only pass in bind values to the
view, right?

For instance I have something like this in the view:

    __PACKAGE__->result_source_instance->view_definition(q[
        select ... from projects me
          inner join ( select ... ) join_name
        where me.customer_id = ? and ...
    ]);

and then can get the desired information with

    my $res = $view->search( {}, { bind => [ 42 ] } );

However, I cannot directly use this to get the same information for all
customers, right?

I already came up with this workaround, which gets me a little closer to
what I need, however, I don't really like it:


    __PACKAGE__->result_source_instance->view_definition(q[
        select ... from projects me
          inner join ( select ... ) join_name
        where (? or me.customer_id = ?) and ...
    ]);

    my $res = $view->search( {}, { bind => [ 0, 42 ] } ); # customer 42
    my $res = $view->search( {}, { bind => [ 1, 0 ] } );  # all customers

Thus, just adding another bind value that overrules the search for a
specific customer.
And this quickly becomes quite complicated if I know also want to add an
optional 'customer.type = ?' to work on projects belonging to a set of
customers, and I don't have any idea how I could implement a
'me.customer_id in (?)' with a flexible number.

So what is the best solution here? Can I somehow add parameters to the
view that then can be taken into account, or is it better to create the
view I need on the fly feeding in the definition I need (and how is that
done best)?

Thanks,
 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

David Cantrell
On Fri, Nov 21, 2014 at 08:38:33AM -0500, Christian Lackas wrote:

> I have something like this in the view:
>     __PACKAGE__->result_source_instance->view_definition(q[
>         select ... from projects me
>           inner join ( select ... ) join_name
>         where me.customer_id = ? and ...
>     ]);
>
> and then can get the desired information with
>     my $res = $view->search( {}, { bind => [ 42 ] } );
>
> [but bind values are icky]

When I had a similar problem a couple of years ago I was told that what
you're doing here is the best solution. If anyone has come up with
something better since, I'd love to know too.

--
David Cantrell | top google result for "topless karaoke murders"

Compromise: n: lowering my standards so you can meet them

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

Darius Jokilehto-2
In reply to this post by Christian Lackas





> On Friday, 21 November 2014, 15:35, Christian Lackas <[hidden email]> wrote:


[...]

>
> Thus, just adding another bind value that overrules the search for a
> specific customer.
> And this quickly becomes quite complicated if I know also want to add an
> optional 'customer.type = ?' to work on projects belonging to a set of
> customers, and I don't have any idea how I could implement a
> 'me.customer_id in (?)' with a flexible number.

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.

Darius

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

Christian Lackas
* Darius Jokilehto <[hidden email]> [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)...

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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

Lasse Makholm


On Fri, Nov 28, 2014 at 4:04 PM, Christian Lackas <[hidden email]> wrote:
* Darius Jokilehto <[hidden email]> [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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

Christian Lackas
* Lasse Makholm <[hidden email]> [141128 18:05]:

Dear Lasse,

thanks a lot for your help!

Your example works very well for me also, and a view factory makes my
live much easier when dealing with complicated queries.

One question, though. Do you have any idea why this works:

    my @rows = $schema->resultset('Foo')->all;

however, using next results in an endless loop returning the first
entry over and over again:

    while (my $row = $schema->resultset('Foo')->next) {
        ...
    }

Thanks again,
 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@...
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic/Flexible DBIC views

Adam Sjøgren
Christian writes:

> however, using next results in an endless loop returning the first
> entry over and over again:
>
>     while (my $row = $schema->resultset('Foo')->next) {
>         ...
>     }

You are creating a new resultset object in each iteration(!)

Try something like:

  my $foos=$schema->resultset('Foo');
  while (my $row=$foos->next) {
      ...
  }


  Best regards,

    Adam

--
 "In the past we would do little things for love, but         Adam Sjøgren
  but things, big things required money. Now we can do   [hidden email]
  big things for love."

_______________________________________________
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@...