#!/usr/local/bin/perl -w

# This script takes arguments that specify the desired
# database interactions.  The user may load data 
# from a tab-delimited BLAST output file into the
# specified table and/or query the table based on
# e-value, start and stop positions delimiting a region
# of interest in a given query sequence

# If loading from the BLAST file, create and/or insert 
# into a database table having the following fields:
# (q_id char, s_id char, identity int, length int, 
#  mismatch int, gap int, q_start int, q_end int, 
#  s_start int, s_end int, e_val int, bit_score int);
#
# If performing a query, find which table entries 
# have e-values smaller than or equal to the e-value
# argument and match the specified query sequence
# within the range of bases passed in as arguments

use strict;
use Getopt::Long;
use DBI;

my ($fname, $e_val, $qs, $qe, $load, $tbl, $qid);
my ($dbh, @tables, $test, $sth);
my $usage = "Usage $0 -t table [-load -f file] 
   [-qid query_id -qs start -qe end -e_val e_value]\n";

# Get the command line options
$load = $e_val = $qid = $qs = $qe = "";  #Set to false
GetOptions("file=s" => \$fname,
           "table=s" => \$tbl,
           "load" => \$load,
           "e_val=s" => \$e_val,
           "qid=s" => \$qid,
           "qstart=i" => \$qs,
           "qend=i" => \$qe);
# Check to see if required options were specified:
# Must have database table name.  If load option
# is given, must have filename.  If any of e_value,
# qid, qstart, or qend options are specified, all
# of them must be specified
if (!defined $tbl) {
  die $usage;
}
if ($load and !defined $fname)  {
  die $usage;
}
if ((defined $qid or defined $qs or defined $qe or defined $e_val) and
    !(defined $qid and defined $qs and defined $qe and defined $e_val)) {
  die $usage;
}

# General DBI handle for connection to SQLite of MySQL
# For SQLite the login and password are empty 
#     as SQLite does not need them
# For MySQL the login and password are needed
# (Comment out the two lines for the database 
# type that is not being used)

# We print the error statement using $dbh->errstr

# $dbh = DBI->connect( 
# "DBI:SQLite:/home/molbio/james/db/mydb","", 
# "",{RaiseError=>1}) or 
# die "Cannot connect" . $dbh->errstr;
$dbh = DBI->connect('DBI:mysql:my_db','mysql_user',   
                                   'mysql_pswd', {RaiseError=>1}) or
    die "Cannot connect" . $dbh->errstr;

if ($load) {
  # If the results table does not exist, create it
  @tables = $dbh->tables();
  if(grep{$_ eq "$tbl"} @tables) {
     print "Table $tbl exists; inserting values\n";
  } else {
  $test = $dbh->do("create table $tbl 
         (q_id char(100),s_id char(100),identity float,
                  length int,mismatch int,gap int,
                  q_start int,q_end int,s_start int,
                  s_end int,e_val double,bit_score int)") or
        die "Cannot create table $tbl";
  }

  # Prepare an SQL statement which is a template
  # We will fill in data from the file, loading it into the
  # table line by line
  $sth = $dbh->prepare("INSERT INTO $tbl VALUES
                                     (?,?,?,?,?,?,?,?,?,?,?,?)") or
     die "Could not prepare statement: " . $dbh->errstr;

  # Now read data from the BLAST file
  open(FH,"$fname") or
    die "Cannot open $fname for reading\n";

  while(my $line = <FH>) {
      # Remove newline
      chomp $line;
      if ( $line  =~  /^#/ ) { next; }
      my($q_id,$s_id,$identity,$length,$mismatch,$gap,
            $q_start,$q_end,$s_start,$s_end,$e_val,
            $bit_score ) = split(/\t/,$line) ;
  
      # Execute the query
      $sth->execute($q_id,$s_id,$identity,$length,
                         $mismatch,$gap,$q_start,$q_end,
                         $s_start,$s_end,$e_val,$bit_score) or
           die "Couldn't execute statement: " . $sth->errstr;
  }
  $sth->finish;
} # End if load

# If no e_value, no query is needed
if (!$e_val) { exit; }

# Prepare the query to find the matches that satisfy
# the criteria defined by $qid, $e_val, $qs, and $qe
$sth = $dbh->prepare("SELECT s_id, e_val, length 
                                     FROM $tbl WHERE 
                                     q_id = '$qid'  AND
                                     e_val <= $e_val AND 
                                     $qs <= s_end AND
                                     $qe >= s_start");

# Execute query and fetch results
$sth->execute;
while (my @result = $sth->fetchrow_array) {
    print "@result\n";
}
$sth->finish;
$dbh->disconnect;