#!/usr/local/bin/perl
####
#### YPD_Text_To_Mysql.PL, v.1.0
#### Programmers - Leonardo Marino and Raymond McCauley
#### (v 1.0) In Development: March 2000
#### Please cite the authors in any work or product based on this material.



# The DBI library is used as a standard database interface
# to communicate with Mysql.
use DBI;
use Getopt::Std;

use vars qw($opt_i
	    $opt_d
	    $db
	    );

getopt('id');

# Open log file.

$LogFile = 'genome_feature_upload.log';
open (LOG, "> $LogFile") || die "Cannot open $LogFile: $! \n";


print "\n";
print "doodle_Text_To_Mysql, Upload doodle text file to Mysql Database\n";
print "Press Ctrl + Break to exit.\n\n";
print LOG "\n";
print LOG "doodle_Text_To_Mysql, Upload doodle text file to Mysql Database\n";
print LOG "Press Ctrl + Break to exit.\n\n";


print "Opening connection with Mysql...\n";    
print LOG "Opening connection with Mysql...\n";   

my $dbuser = $ENV{MYSQL_USERID};
$db = "$opt_d";
print "dbuser is $dbuser\n";

my $dbh = DBI->connect("dbi:mysql:$db", $dbuser, undef,
    {AutoCommit => 0}) || die $DBI::errstr;

print "\n";
print LOG "\n";

# Open ASCII file for reading.

$doodleFile = "$opt_i";

print "\nOpening doodle ASCII file ($doodleFile) for reading...\n";
print LOG "\nOpening doodle ASCII file ($doodleFile) for reading...\n";


open (doodleFile, $doodleFile) || die "Cannot open $doodleFile: $! \n";

$linecount = 0;  %v = ();
while ($line = <doodleFile>) {

    $linecount = $linecount +1;
    
    # pad line
    $line = $line . '   ';

    # clean line
    $line =~ s/'/ /g;
    $line =~ s/"/ /g;
    $line =~ s/,/ /g;
    
    
    ###
    ### NOTE
    ### Must find a way to handle single quotes -- how to insert into
    ### Oracle?
    ### Substitute ' -> prime???
    ###
    



    ### Use split function to split read-inline ($line)
    ### based on tab (/t) and store in array (@line2).
    @line2 = split(/\t/,$line);
    
    # Replace dashes (-) with nothing.
    #$line2[0] =~ s/-//g; 
    
    # Pad with spaces, just in case.
    #$line2[0] . '         ';
    
    # Build a hash to clean values for SQL.
    
    %v = (  
	    seq_start              => substr($line2[0],0,10),
	    seq_end                => substr($line2[1],0,10),
	    score                  => substr($line2[2],0,10),
	    strand                 => substr($line2[3],0,15),
	    frame                  => substr($line2[4],0,1),
	    analysis               => substr($line2[5],0,10),
	    name                   => substr($line2[6],0,40),
	    hstrand                => substr($line2[7],0,4),
	    hstart                 => substr($line2[8],0,11),
	    hend                   => substr($line2[9],0,11),
	    hid                    => substr($line2[10],0,40),
	    hgbid                  => substr($line2[11],0,15),
	    evalue                 => substr($line2[12],0,20),
	    perc_id                => substr($line2[13],0,10)
    );
    
    # Loop through and clean. 
    
    while (($key, $value) = each %v) {
    
      # Get rid of leading white space
      $value =~ s/^\s+//g; 
    
      # Set zero length values to NULL
      if (length($value) == 0) {$value = 'null'}
#      if (length($value) == 0) {$value = '.'}  
      # Quote all non-null values for Oracle.
      if ($value ne 'null') {
        $value = "'" . $value . "'";
        }
      
      $v{$key} = $value;
    }
    
  
    # Build SQL to insert values into YPD table.
    
    print "Inserting # $linecount.\n";
    print LOG "Inserting # $linecount.\n";
        $feature_table = "$db"."_feature";
	$sql1 = "insert into $feature_table\n";

        $sql2 = "(seq_start,seq_end,score,strand,frame,analysis,name,hstrand,hstart,hend,hid,hgbid,evalue,perc_id)
            Values ($v{seq_start},$v{seq_end},$v{score},$v{strand},$v{frame},$v{analysis},$v{name},$v{hstrand},$v{hstart},$v{hend},$v{hid},$v{hgbid},$v{evalue},$v{perc_id})";
         $sql = "$sql1$sql2";   
    print LOG "$sql\n";

    $sth = $dbh->prepare($sql) ||   die $DBI::errstr;
    eval {$sth->execute() || die $DBI::errstr};

    if ($@) {
        print LOG "$DBI::errstr\n";
        while (($key, $value) = each %v) {
            print LOG "$key $value\n";
        }
    }
}

print "\nInsert ends.\n";
print LOG "\nInsert ends.\n";

### Close LOG output file.

print "\n";
print "Closing log file.\n\n";
print LOG "\n";
print LOG "Closing log file.\n\n";
close LOG;


### Ask about commit / rollback.

# Ask about commit / rollback
print "\n";
print "Examine log file.\n";
print "These records have not been committed.\n";
undef $answer;
while (!$answer) {
    print "Enter action (rollback or commit): ";
    chomp ($answer = <>);
    if ($answer eq 'commit' || $answer eq 'commit;') {
        $dbh->commit || die $DBI::errstr;
        print "\nUpdates committed!\n\n";
    } elsif ($answer eq 'rollback' || $answer eq 'rollback;') {
        $dbh->rollback || die $DBI::errstr;
        print "\nChanges rolled back!\n";
    } else {
        undef $answer;      
    }
}



# Disconnect from Mysql.
print "Closing connection to Mysql...\n";
print LOG "Closing connection to Mysql...\n";

$dbh->disconnect || warn $DBI::errstr;



### Wait for input to terminate program.
### (Keeps DOS Window open when running as a separate process.)
print "\n\nPress <Enter> to continue...";
chomp ($temp = <>);