[Dbix-class] problem with dbix-class fetch from mysql vs sqlite
Jean-Francois Lucier
Jean-Francois.Lucier at USherbrooke.ca
Thu May 29 14:54:09 BST 2008
Sorry about that. Thank you for notifying me. Here is the original
message. I hope everything will be ok this time.
Hello,
I did a few more test and here are the results. If I run the query
inside the sqlite3 shell, the result I get is :
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite> SELECT me.location_id, me.start, me.end, me.accession,
me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand <
45622542 ) AND ( me.aligment_id = 507781 ) ) ) ORDER BY (start*strand) DESC;
> 2884873|45620932|45620997||507781|507781|1|27|1.0|742163|transcript
> 2884874|45587961|45588122||507781|507781|1|27|1.0|742163|transcript
> 2884875|45567656|45567829||507781|507781|1|27|1.0|742163|transcript
> 2884876|45513662|45513766||507781|507781|1|27|1.0|742163|transcript
> 2884877|45507578|45507734||507781|507781|1|27|1.0|742163|transcript
> 2884878|45498308|45498672||507781|507781|1|27|1.0|742163|transcript
> 2884879|45404376|45404499||507781|507781|1|27|1.0|742163|transcript
> 2884880|45404032|45404175||507781|507781|1|27|1.0|742163|transcript
The same in query in the mysql shell:
> mysql> SELECT me.location_id, me.start, me.end, me.accession,
me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( ( start*strand <
45622542 ) AND ( me.aligment_id = 507781 ) ) ) ORDER BY (start*strand) DESC;
>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+
> | location_id | start | end | accession | aligment_id |
aligment_id | strand | reference_sequence_id | score | distant_id |
distant_table |
>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+
> | 2884873 | 45620932 | 45620997 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884874 | 45587961 | 45588122 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884875 | 45567656 | 45567829 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884876 | 45513662 | 45513766 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884877 | 45507578 | 45507734 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884878 | 45498308 | 45498672 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884879 | 45404376 | 45404499 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
> | 2884880 | 45404032 | 45404175 | NULL | 507781 |
507781 | 1 | 27 | 1 | 742163 |
transcript |
>
+-------------+----------+----------+-----------+-------------+-------------+--------+-----------------------+-------+------------+---------------+
When this query is runned inside my script using mysql, I get:
> bef
> SELECT me.location_id, me.start, me.end, me.accession,
me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( (
me.start*aligment.strand < ? ) AND ( me.aligment_id = ? ) ) ):
'45622542', '507781'
> location id = 2884873
> location id = 2884874
> location id = 2884875
> location id = 2884876
> location id = 2884877
> location id = 2884878
> location id = 2884879
> location id = 2884880
> aft
When this query is runned inside my script using sqlite, I get:
> bef
> SELECT me.location_id, me.start, me.end, me.accession,
me.aligment_id, aligment.aligment_id, aligment.strand,
aligment.reference_sequence_id, aligment.score, aligment.distant_id,
aligment.distant_table FROM location me JOIN aligment aligment ON (
aligment.aligment_id = me.aligment_id ) WHERE ( ( (
me.start*aligment.strand < ? ) AND ( me.aligment_id = ? ) ) ):
'45622542', '507781'
> location id = 2884872
> location id = 2884873
> location id = 2884874
> location id = 2884875
> location id = 2884876
> location id = 2884877
> location id = 2884878
> location id = 2884879
> location id = 2884880
> aft
Don't you find this weird. I run exactly the same query in sqlite shell,
and I dont get the same answer as inside DBIx::Class. When I run this
query using DBI only and sqlite, I get the right data fetched (the same
as mysql). As you can see, I removed the order by clause and I still get
the same response. The trace outputted above where done using this code:
> sub prev_location { #only for stored locations
> my ($self) = @_;
> warn("I am not sure this can work") unless ($self->in_storage);
> print STDERR "bef\n";
> my $x = $self->aligment->search_related_rs(
> 'locations',
> {'me.start*aligment.strand' => {'<' => $self->start *
$self->aligment->strand}},
> # {order_by => ['(start*strand) DESC']}
> );
> my $first;
>
> while( my $b = $x->next){
> if(!defined($first)){
> $first=$b;
> }
> print STDERR "location id = " . $b->location_id . "\n";
> }
> print STDERR "aft\n";
> return $first;
> }
Here is the result source definition for alignment table:
> package Bio::Annotations::Aligment;
> use warnings;
> use strict;
>
> use base qw/DBIx::Class/;
>
> __PACKAGE__->load_components(qw/PK::Auto Core/);
> __PACKAGE__->table('aligment');
> __PACKAGE__->add_columns('aligment_id', 'strand',
'reference_sequence_id', 'score', 'distant_id',
> 'distant_table');
> __PACKAGE__->set_primary_key('aligment_id');
> __PACKAGE__->belongs_to('reference_sequence',
'Bio::Annotations::ReferenceSequence',
> 'reference_sequence_id');
> __PACKAGE__->belongs_to(
> 'transcript',
> 'Bio::Annotations::Transcript',
> {'foreign.transcript_id' => 'self.distant_id',},
> { join => 'aligments',
> where => {'distant_table' => \" = 'transcript'"},
> }
> );
>
> __PACKAGE__->has_many(
> 'locations',
> 'Bio::Annotations::Location',
> 'aligment_id',
> { prefetch => ['aligment'],
> # order_by => ['start * strand', 'start']
> }
> );
>
>
> 1;
and the location table:
> package Bio::Annotations::Location;
> use warnings;
> use strict;
>
> use base qw/DBIx::Class/;
>
> __PACKAGE__->load_components(qw/PK::Auto Core/);
> __PACKAGE__->table('location');
> __PACKAGE__->add_columns('location_id', 'start', 'end', 'accession',
'aligment_id');
> __PACKAGE__->set_primary_key('location_id');
> __PACKAGE__->belongs_to('aligment', 'Bio::Annotations::Aligment',
'aligment_id');
>
> sub prev_location { #only for stored locations
> my ($self) = @_;
> warn("I am not sure this can work") unless ($self->in_storage);
> print STDERR "bef\n";
> my $x = $self->aligment->search_related_rs(
> 'locations',
> {'me.start*aligment.strand' => {'<' => $self->start *
$self->aligment->strand}},
> # {order_by => ['(start*strand) DESC']}
> );
> my $first;
>
> while( my $b = $x->next){
> if(!defined($first)){
> $first=$b;
> }
> print STDERR "location id = " . $b->location_id . "\n";
> }
> print STDERR "aft\n";
> return $first;
> }
>
Thanks again for your help...
JF
Matt S Trout wrote:
> On Thu, May 15, 2008 at 04:15:09PM -0400, Jean-Francois Lucier wrote:
>> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
>> <html>
>> <head>
>> </head>
>> <body bgcolor="#ffffff" text="#000000">
>> Hello,<br>
>
> Please don't post in HTML only to this list.
>
--
IMPORTANT: Veuillez penser à l'environnement avant d'imprimer ce courriel
Jean-Francois Lucier
Bio-informaticien
B10 solutions, consultation en bioinformatique
Centre de recherche clinique du CHUS
3001, 12e avenue Nord
Sherbrooke (Québec) J1H 5N4
CANADA
Téléphone: (819) 820-6868 ext: 12577
Fax: (819) 564-5392
More information about the DBIx-Class
mailing list