[Dbix-class] how do resultsets actually work under the hood?
I was in conversation with a Java programmer today about the design of a database system, in a nutshell he was saying that in Java he would create a 'collection' of all rows in a table and then iterate over the collection of objects. If it was too big he would 'split' the query by searching for all records starting with 'a', then 'b' etc. I was appalled that this would eat up memory, and starting going on about DBIC and how in Perl I would create a resultset then iterate over it using 'next'. I soon realized that I didn't have much idea how this 'just worked' under the surface (in DBI I presume?) without using large amounts of memory to hold every row from the table. I have done some digging since, I have looked at the DBI code, but frankly I got rather lost. So, can anyone give a concise description on how this works, in particular. Where (if anywhere) does it store a record of every row in a resultset so that I can just call 'next' and quickly get the next row. Or does it do this by making a request to the database for every row? Isn't this slow? Thank you. Iain. ___ 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
Re: [Dbix-class] Complex joins
Brilliant! That is indeed the answer I was (blindly) looking for. Thanks greatly. Iain. On 10 April 2013 20:46, Matthew Phillips ma...@cpan.org wrote: Hi Lain, What you are looking for is the coderef syntax for declaring a relationship as describe here: https://metacpan.org/module/DBIx::Class::Relationship::Base#condition Cheers, Matt On Wed, Apr 10, 2013 at 3:40 PM, Iain C Docherty dbix-cl...@iain-docherty.com wrote: I have googled for this, as far as I can tell DBIx::Class does not support complex joins. An example of which is below. select star.id,star.name,body.id,body.name,building.class,empire.name from star LEFT JOIN probes ON star.id = probes.star_id AND probes.alliance_id=26 LEFT JOIN body ON star.id = body.star_id AND probes.id is not null LEFT JOIN empire ON body.empire_id = empire.id LEFT JOIN building ON body.id = building.body_id AND building.class='Lacuna::DB::Result::Building::Permanent::Ravine' WHERE star.x -10 AND star.x 10 AND star.y -10 AND star.y 10 group by body.id Is there any way that this could be implemented in DBIx::Class or do I have to drop down to SQL and implement a custom ResultSource? (and in case you are wondering, this is to do with the open source project at https://github.com/plainblack/Lacuna-Server-Open ) -- Kind Regards Iain ___ 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 ___ 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 ___ 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
Re: [Dbix-class] So they want ME to give a presentationaboutDBIx::Class
Octavian Rasnita wrote: From: Ihnen, David dih...@amazon.com Good point, especially for an introduction... why to use an ORM. I'll have to spin up myself a bit on Template Toolkit (I'd been using HTML::Template previously) and will probably have to merely mention rather than explore the FormMu stuff, as I've never worked in Catalyst. David You can tell them that they can use a resultset in a template directly like: [snip] Of course, using this way, the designer will be able to print something like [% user.password %] or [% user.another_private_info %] and the programmers might not want to allow the designers of the templates to have that freedom, but if this is not an issue, it is a very helpful way of using DBIC. Its not just security thats a problem. Your front end guy makes a small template change and the next thing you know your web app is generating hundreds of SQL queries in its view because you didn't think that relationship needed pre-fetching. (Pre-fetch is a good thing to talk about as its often missed by people starting with DBIC) I vote for not letting the template guys have access to the DB. Give them an intermediate object or wipe the schemas storage before passing to the template That way you still have a nice object to play with and can trap unexpected access to the DB whilst still in development. I do know some people who prefer the flexibility you get by having the view be able to access the full DBIC object though. So as always, TMTOWTDI :-) Iain Hubbard. ___ 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
Re: [Dbix-class] Removing rows older than a given number of minutes ...
Alexander Hartmaier wrote: That's my preferred way of doing this and it works great. I second that. Also, using NOW() in MySQL breaks the query cache. Not an issue here but worth bearing in mind. Iain. ___ 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
Re: [Dbix-class] Fwd: [Catalyst] Selecting from more tables (DBIC- bug?)
my $uuu = $schema-resultset('User')-search({},{
prefetch = {blogs = 'blog_comments'},
select = ['me.id'],
as = ['user_id'],
});
print $uuu-first-username;
I think the single column that should be printed should be me.id, but
here is the generated SQL:
SELECT me.id, blogs.id, blogs.user, blogs.date_create,
blogs.date_modify, blogs.title, blogs.body, blogs.markup_lang,
blogs.tags, blogs.active, blog_comments.id, blog_comments.user,
blog_comments.blog, blog_comments.date_time, blog_comments.body,
blog_comments.markup_lang, blog_comments.active FROM user me LEFT JOIN
blog blogs ON blogs.user = me.id LEFT JOIN blog_comment blog_comments
ON blog_comments.blog = blogs.id ORDER BY blogs.user, blog_comments.blog:
Is it normal to get all the columns from the joined tables?
Yes. You have asked DBIC to prefetch the data from the blogs tables. So
it has.
Iain.
___
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
Re: [Dbix-class] Rapid Data Extraction
Using the slices is a horrible encapsulation violation. I can deal with that on our side, but not on the dbic side. Is there a clean way of fetching all of that at once? A bit of a vague answer, but we once had a discussion with mst (at northwestengland.pm) about stopping row objects accessing the database in the view. While I cant remember the full details it was along the lines of undefing the rows access to the schema. It looks like you already have the row object created so you would save yourself the time to create a whole new object and guarantee no DB access. There was also talk of logging any templates that tried to access the db too. Sorry I cant remember any more details but its a path to explore. Iain. ___ 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
Re: [Dbix-class] Multiple self join problem
Terence Monteiro wrote:
On Thu, Nov 12, 2009 at 08:29:46AM -0500, Rob Kinyon wrote:
On Thu, Nov 12, 2009 at 08:08, Terence Monteiro tere...@deeproot.co.in wrote:
I have 2 tables, one for soccer teams and one for players. I want to
find the team having players A, B and C.
Schema::Team has many Schema::Player
How could I accomplish the above? In SQL, one way would be:
select teams.* from teams
inner join players p1 on p1.team = team.id
inner join players p2 on p2.team = team.id
inner join players p3 on p3.team = team.id
where p1.name = 'A'
and p2.name = 'B'
and p3.name = 'C';
How could I do this using DBIx::Class?
$schema-model('teams')-search({
'players.name' = 'A',
'players2.name' = 'B',
'players3.name' = 'C',
}, {
join = [ 'players', 'players', 'players' ],
});
This is well documented.
Thanks, Rob. It worked with a slight modification:
$schema-model('teams')-search( {
'players.name' = 'A',
'players_2.name' = 'B',
'players_3.name' = 'C',
}, {
join = [ 'players', 'players', 'players' ],
} );
btw, where is it documented? I couldn't find it after googling or in
DBIx::Class::Manual::Cookbook.
http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/Manual/Joining.pod
google joining to the same table dbix::class its the top link
Iain.
___
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
Re: [Dbix-class] A DBIC based library
On Sat, 2009-06-06 at 11:27 +0200, Zbigniew Lukasiak wrote:
Hi,
I am trying to figure out how to package a DBIC Result as a library.
You can find more background in my blog post:
http://perlalchemy.blogspot.com/2009/06/packaging-cross-cutting-catalyst.html
. What I tried is simply defining that Result class in a separate
package - and later subclassing it:
package CatalystX::Comments::DBICResult;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__-load_components(qw/TimeStamp PK::Auto Core/);
__PACKAGE__-table('comment');
__PACKAGE__-add_columns(
id = { data_type = 'integer', is_auto_increment = 1, is_nullable = 0 },
item_id = { data_type = 'integer', is_nullable = 0 },
body = { data_type = 'text', default_value = undef, is_nullable = 1 },
nick = { data_type = 'varchar', default_value = undef, is_nullable = 1
},
email = { data_type = 'varchar', default_value = undef, is_nullable = 1
},
web_site = { data_type = 'varchar', default_value = undef,
is_nullable = 1 },
ip = { data_type = 'varchar', default_value = undef, is_nullable = 1 },
t_created = { data_type = 'datetime', set_on_create = 1 },
t_updated = { data_type = 'datetime', set_on_create = 1,
set_on_update = 1 },
);
__PACKAGE__-set_primary_key('id');
1;
And the subclass:
package PiraciDrogowi::DBSchema::Comment;
use strict;
use warnings;
use base 'CatalystX::Comments::DBICResult';
__PACKAGE__-belongs_to('incydent',
'PiraciDrogowi::DBSchema::Incydent', { id = 'item_id' });
1;
Obviously that was rather naive approach - but it was the first I thought up.
It nearly works:
use lib 'lib';
use PiraciDrogowi::DBSchema;
my $schema = PiraciDrogowi::DBSchema-connect( 'dbi:SQLite:dbname=piraci.db'
);
my $rs = $schema-resultset('Comment');
print $rs-count, \n;
my $comment = $rs-first;
print $comment-incydent-id;
This prints:
1
Can't locate object method incydent via package
CatalystX::Comments::DBICResult at a.pl line 9.
So it finds the record - but it does not see the relationships I added
in the subclass.
I too would be grateful for any advice on this.
For example say bugzilla used DBIC and I wanted to write an extension.
How could I add extra methods to the row object without modifying the
existing result source.
Iain.
___
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
Re: [Dbix-class] specify join type
On Thu, 2009-02-19 at 18:13 +0100, Bernhard Graf wrote:
Nigel Metheringham wrote:
Not easily for a single query, however a relationship can have a
join_type attribute - relationship attributes are documented in
DBIx::Class::Relationship::Base under add_relationship
I know this, but I actually wanted to use both join types.
You can always have 2 identical, other than the join_type attribute,
relationships defined under different names, to allow both forms of
joins to be performed as required...
Good idea!
This is what we do, however we forgot to turn off cascading on the extra
relationship e.g.
{cascade_delete = 0, cascade_copy = 0, join_type = 'left'}
and it caused us all kinds of fun.
Iain.
___
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
Re: [Dbix-class] Selecting max() of count()
On Wed, 2008-07-30 at 14:55 +0200, BUNK (Jacob Bunk Nielsen) wrote:
$baskets-search({ },
{ select = [ { count = 'item_id' }, ],
as = [ 'no_of_items' ],
group_by = [ 'basket_id' ],
})-get_column('no_of_items')-max;
Unfortunately this doesn't cut it. It ends up complaining that there is
no column called 'no_of_items' for the query:
SELECT MAX( no_of_items )
FROM shop.basket me
WHERE ( ( ( basket_id = ? ) OR ( basket_id = ? ) ) )
GROUP BY basket_id with ParamValues: 1='1', 2='2'
It totally ignores my part about 'select count(item_id) as no_of_items'.
How do I make dbix-class make the right database query?
I think its the as = [ 'no_of_items' ] not doing what you think.
Note that the as attribute has absolutely nothing to with the sql
syntax SELECT foo AS bar
http://search.cpan.org/~ash/DBIx-Class-0.08010/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures
Iain.
GMG Regional Digital is part of the Guardian Media Group plc.
CONFIDENTIALITY NOTICE. The information contained in this e-mail is intended
only for [EMAIL PROTECTED] It may contain privileged and confidential
information that is exempt from disclosure by law and if you are not an
intended recipient, you must not copy, distribute or take any action in
reliance on it. If you have received this e-mail in error, you may notify us by
telephone on 44 (0)161 832 7200. E-mail transmission cannot be guaranteed to be
secure or error-free. The sender ([EMAIL PROTECTED]) therefore does not accept
liability for any errors or omissions in the contents of this message, which
arise as a result of e-mail transmission. If verification is required please
request a hard-copy version.
Scanned by MailDefender - managed email security from intY -
www.maildefender.net
___
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/[EMAIL PROTECTED]
Re: [Dbix-class] Selecting max() of count()
On Fri, 2008-08-01 at 12:06 +0200, BUNK (Jacob Bunk Nielsen) wrote:
Perhaps, but it also ignores any mentioning of 'count'. This doesn't
seem logical to me. Do you have any idea why it ignores count?
The max is obviously causing a problem, how about a different approach?
** untested **
my $basket = $schema-resultset('shop.basket')-search(
{basket_id = [1, 2]},
{
select = [ \'COUNT(me.item_id) as c' ],
as = ['c'],
order_by = ['c desc'],
'group_by' = 'me.basket_id'
}
)-single;
print $basket-get_column('c'), \n;
** untested **
Should get you the max count, but the method may not be to your liking.
Iain
GMG Regional Digital is part of the Guardian Media Group plc.
CONFIDENTIALITY NOTICE. The information contained in this e-mail is intended
only for [EMAIL PROTECTED] It may contain privileged and confidential
information that is exempt from disclosure by law and if you are not an
intended recipient, you must not copy, distribute or take any action in
reliance on it. If you have received this e-mail in error, you may notify us by
telephone on 44 (0)161 832 7200. E-mail transmission cannot be guaranteed to be
secure or error-free. The sender ([EMAIL PROTECTED]) therefore does not accept
liability for any errors or omissions in the contents of this message, which
arise as a result of e-mail transmission. If verification is required please
request a hard-copy version.
Scanned by MailDefender - managed email security from intY -
www.maildefender.net
___
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/[EMAIL PROTECTED]
