Re: [Dbix-class] problem with dbix-class fetch from mysql vs sqlite

Jean-Francois Lucier Fri, 16 May 2008 07:49:04 -0700

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

Zbigniew Lukasiak wrote:

Hello,

On Thu, May 15, 2008 at 10:15 PM, Jean-Francois Lucier
<[EMAIL PROTECTED]> wrote:

Hello,


I have build an API using DBIx::Class that executes many queries on either
SQLite or mysql (same database schema). I have put traces in my script.

When I execute on mysql, everything goes well and my traces output the
following:

trace1 selfstart=45622542 selfaligmentstrand=1
me.location_id : 2884872
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 < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
start*strand DESC LIMIT 1: '45622542', '507781'
me.location_id : 2884873

for SQlite I get:

trace1 selfstart=45622542 selfaligmentstrand=1
me.location_id : 2884872
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 < ? ) AND ( me.aligment_id = ? ) ) ) ORDER BY
start*strand DESC LIMIT 1: '45622542', '507781'
me.location_id : 2884872

If I use directly DBI and execute the same query, evrything goes ok and I
get:

the following query will be executed: 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 LIMIT 1
reference_sequence_id=27
score=1
location_id=2884873
distant_id=742163
aligment_id=507781
accession=
end=45620997
strand=1
distant_table=transcript
start=45620932

This is the code that executes the query inside DBIx::Class along with the
traces:

sub prev_location {    #only for stored locations
    my ($self) = @_;
    warn("I am not sure this can work") unless ($self->in_storage);
    print STDERR "trace1 selfstart=".$self->start."
selfaligmentstrand=".$self->aligment->strand."\n";
    print STDERR "me.location_id : ".$self->location_id."\n";
    my $x = $self->aligment->search_related_rs(
        'locations',
        {'start*strand' => {'<' => $self->start * $self->aligment->strand}},
        {order_by => ['start*strand DESC']}
    )->slice(0, 0)->single;
    print STDERR "me.location_id : ".$x->location_id."\n";
    return $x;
}


Did you try to remove things from that query?  For example remove:
'->slice(0, 0)->single' (the query will then return an array - but
that's OK - you just need to verify the SQL query after all),
'{order_by => ['start*strand DESC']}', maybe other parts as well.  Try
to clean it a bit in general.

Then what would be also helpful is your ResultSource definitions.

-- 

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

_______________________________________________
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] problem with dbix-class fetch from mysql vs sqlite

Reply via email to