1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110
|
#!/usr/bin/perl
use strict;
use warnings;
use LWP::Simple;
use Benchmark qw( timethese cmpthese );
my $db = dbin();
my $schema = DB::Schema->connect( );
my $long;
my $short;
my $count = 1000;
my $t = timethese(
$count,
{
'list' => sub {
my $html = get("lien");
my ( @public, @not );
while ( $html =~/<td class=\"rich-table-cell \" id=\":j_id\d+\:\d+\:j_id\d+\"><a href=\"\/([^;]+)[^\"]+\">([^<]+)<\/a><\/td>/gsi){
my $short = $1;
( my $long = $2 ) =~ s/^(\w* \w*.*\bv\d)\.\d(.*)/$1$2/;
my $sql ="select \"$long\", \"$short\", file
from genome inner join genome_info using (genome_id)
where organism like \"%($short)%\" and \"status\" !=\'deleted\' ";
my $rows = SQL($sql);
if ( @$rows[0] ) {
push( @public, @$rows[0] );
}
else {
push @not, [ $long, $short ];
}
}
},
'list_dbix' => sub {
my $html = get("lien");
my ( @public, @not );
while ( $html =~/<td class=\"rich-table-cell \" id=\":j_id\d+\:\d+\:j_id\d+\"><a href=\"\/([^;]+)[^\"]+\">([^<]+)<\/a><\/td>/gsi){
my $short = $1;
( my $long = $2 ) =~ s/^(\w* \w*.*\bv\d)\.\d(.*)/$1$2/;
my %where = (
'organism' => { '-like' => "%($short)%" },
'status' => [ -or => { '!=' => 'deleted' }, undef ]
);
my $ra_row_geno_tabls = get_genome_info_in_both_tables( \%where );
if ($ra_row_geno_tabls) {
my @tmp;
push( @tmp, $long );
push( @tmp, $short );
push( @tmp, $ra_row_geno_tabls->[0]->[1] );
push( @tmp, $ra_row_geno_tabls->[0]->[0] );
push( @public, \@tmp );
}
else {
push @not, [ $long, $short ];
}
}
}
}
);
cmpthese $t;
sub SQL {
my $query = shift;
my $sth;
eval {
$sth = $db->prepare($query);
$sth->execute();
};
if ($@) {
print "Error in the database: $@";
exit;
}
my $rows = $sth->fetchall_arrayref;
$sth->finish;
return $rows;
}
sub get_genome_info_in_both_tables {
my $where = shift @_;
my @rowgenotbl;
my $search = $schema->resultset('Genome')->search(
$where,
{
join => 'genome_info',
'+select' => [
'genome_info.file', 'genome_info.source'
],
'+as' => [ 'file', 'source' ]
}
);
foreach my $geno ( $search->all() ) {
push @rowgenotbl,
[
$geno->file, $geno->source
];
}
return 0 if ( !@rowgenotbl );
return \@rowgenotbl;
} |
Partager