#!/usr/local/bin/perl
####
#### interpro2doodle, 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
	    );

getopt('id');

# Open log file.

$LogFile = 'interpro_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};
my $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";

$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;
    
    # clean line
    $line =~ s/'/ /g; # Replace ' with space.
    $line =~ s/"/ /g; # Replace " with space.
    $line =~ s/,/ /g; # Replace , with space.
    
    ### Use split function to split read-inline ($line)
    ### based on tab (/t) and store in array (@line2).
    @line2 = split(/\s/,$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 = (  
	    sp_id                  => substr($line2[0],0,10),
	    xref                   => substr($line2[1],0,13),
	    ip_start               => substr($line2[2],0,4),
	    ip_end                 => substr($line2[3],0,4),
	    ip_id                  => substr($line2[4],0,13),
    );
    
    # 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";
    
    $sql = "insert into sp2interpro
              (sp_id,xref,ip_start,ip_end,ip_id)
            Values ($v{sp_id},$v{xref},$v{ip_start},$v{ip_end},$v{ip_id})";
            
    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 = <>);