use strict;
use warnings;
use DBI;

my($dbh);

system('date');
$dbh = DBI->connect('dbi:Pg:dbname=test1','','',{AutoCommit=>0})
                        or die $dbh->errstr;

#populate($dbh);
select_test($dbh);

$dbh->disconnect;

system('date');

exit 0;

sub populate{

        my($dbh)=@_;
        my($sth,$sql,$cnt);
        $cnt=0;

        $sql="insert into chromosomes values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        $sth=$dbh->prepare($sql) or die $dbh->errstr;
        eval{
                open my $fh , "<", "hg19.fa.out" or die $!;
                while(my $line=<$fh>){
                        $cnt++;
                        next if $cnt <= 3;

                        chomp $line;
                        $line =~ s/^\s+//;
                        my @vals=split(/\s+/, $line);
                        #begin and end to box type
                        $vals[5]="(($vals[5],0),($vals[6],0))";
                        splice(@vals,6,1);

                        $sth->execute(@vals);
                }
                close $fh;
                $sth->finish;
        };
        if($@){
                $dbh->rollback;
        }else {
                $dbh->commit;
        }
}
sub select_test{
        my($dbh)=@_;
        my($sth,$sql);

        #SQL for overlaps
        #$sql="select * from chromosomes where pos && box '((10000,0),(20000,0))'";
        #SQL for include
        $sql="select * from chromosomes where pos @ box '((10000,0),(20000,0))'";

        $sth=$dbh->prepare($sql) or die $dbh->errstr;
        $sth->execute  or die $dbh->errstr;
        while(my $href= $sth->fetchrow_hashref){
                print $href->{pos} . "\n";
        }
        $sth->finish;
}