use strict;
use warnings;

use Bio::EnsEMBL::Gene;
use Bio::EnsEMBL::Registry;
use Bio::EnsEMBL::Utils::SqlHelper;
use Benchmark qw(cmpthese);

Bio::EnsEMBL::Registry->load_registry_from_db(
  -HOST => 'mysql.ebi.ac.uk',
  -PORT => 4157,
  -USER => 'anonymous',
  -DB_VERSION => 60
);

my $count = 50;

my $dba = Bio::EnsEMBL::Registry->get_DBAdaptor('arabidopsis_thaliana', 'core');
my $h = Bio::EnsEMBL::Utils::SqlHelper->new(-DB_CONNECTION => $dba->dbc());

my $sql = <<'SQL';
select gene_id, seq_region_start, seq_region_end, seq_region_strand, biotype, canonical_transcript_id
from gene
SQL

my $callbacks = sub {
  my $genes = $h->execute(
    -SQL => $sql,
    -CALLBACK => sub {
      my ($row) = @_;

      my ($gene_id, $seq_region_start, $seq_region_end,
        $seq_region_strand, $biotype, $canonical_transcript_id) = @{$row};

      my $data = {
        biotype       => $biotype,
        start         => $seq_region_start,
        end           => $seq_region_end,
        strand        => $seq_region_strand,
        dbID          => $gene_id,
        canonical_transcript_id => $canonical_transcript_id
      };
      return Bio::EnsEMBL::Gene->new_fast($data);
    }
  );
};

my $manually = sub {
  my $genes = $h->execute_with_sth(
    -SQL => $sql,
    -CALLBACK => sub {
      my ($sth) = @_;
      my @genes;
      $sth->execute();

      while(my $row = $sth->fetchrow_arrayref()) {

        my ($gene_id, $seq_region_start, $seq_region_end,
        $seq_region_strand, $biotype, $canonical_transcript_id) = @{$row};

        my $data = {
          biotype       => $biotype,
          start         => $seq_region_start,
          end           => $seq_region_end,
          strand        => $seq_region_strand,
          dbID          => $gene_id,
          canonical_transcript_id => $canonical_transcript_id
        };
        my $gene = Bio::EnsEMBL::Gene->new_fast($data);

        push(@genes, $gene);
      }

      return \@genes;
    }
  );
};

cmpthese($count, {
  'Using Callbacks' => $callbacks,
  'Manually' => $manually,
});