#!/usr/bin/perl -w
#--------------------------------------------------------------------------------------------------------------
# what the script does:
#   1.for each table in .tab file
#	read in and store in mysql
#   2.export to mysql format
#
#   To skip step 2, use flag "--noexport".
#
# usage: 
# ./tab2mysql.pl -in ~/research/fta/data/overnet03_tab/ -out ~/research/fta/data/overnet03_sql -db fta
#
# to import result into mysql:
# mysql fta < fta.sql
#
# Contact: Derrick Kondo derrick.kondo :: inria fr
#
# Copyright 2009 by Failure Trace Archive site (http://fta.inria.fr)
#
#--------------------------------------------------------------------------------------------------------------


use strict;
use DBI;
 
# Module to help handle command line switches
use Getopt::Long;

# method headers
sub main();
sub extract_parameters();

sub replace_w_undef($);
sub import_to_mysql ($$);
sub read_into_table ($$$);
sub createDir ($);

main;

sub main (){
    my (@args, $start, $stop, $cwd);

    my ($inputdir, $outputdir, $db, $export);

    ($inputdir, $outputdir, $db, $export) = extract_parameters();
    
    my ($dsn, $user, $password, $dbh);
    # data source name
    $dsn = "DBI:mysql:$db:localhost";
#    $user = "dkondo";
     $user = "javadi";
    $password = "";


    # Connecting is exppensive.
    # Auto commit so that changes are automatically saved when executed
    $dbh = DBI->connect ($dsn, $user, $password,
			 # warn and die if error
			 { PrintError => 1, RaiseError => 1, AutoCommit => 1 });



    import_to_mysql($inputdir, $dbh);
     

# export_mysql 
    createDir ($outputdir);
    chdir $outputdir or die "Can't cd to $outputdir: $!\n";

    if ($export){
	print "Exporting mysql to $db.sql\n";
	`mysqldump $db > $db.sql`;
    } else {
	print "SKIPPING export of mysql to $db.sql\n";
    }
    $dbh->disconnect;

}


sub createDir ($){
    my ($dir) = @_;                                                                                                           
                                                                                                                              
    if (! -e $dir){                                                                                                           
        print "Creating directory $dir\n";                                                                                    
        my $retval = mkdir($dir, 0777);                                                                                       
        if ($retval == 0)                                                                                                     
        {                                                                                                                     
            die "Fatal Error: $dir could not be created\n Message is: $!\n. Program Terminating....\n";                       
        }                                                                                                                     
    }                                                                                                                         
                                                                                                                              
}                                                                                                                             

sub import_to_mysql ($$){

    my ($inputdir, $dbh) = @_;

# open input directory   
    opendir (INPUTDIR, $inputdir);

    my ($file, @files);

    @files = grep { /^\w+\.tab$/ } readdir (INPUTDIR);
    
    foreach $file (@files){
      print "Inserting data in $file\n";
      read_into_table ($inputdir, $file, $dbh);

    }

    close (INPUTDIR);
}


sub read_into_table ($$$){
  my ($inputdir, $file, $dbh) = @_;

  my ($table, $suffix);
  ($table, $suffix) = split /\./, $file;

  my ($sth, $insertion);
  $sth = undef;
  $insertion = "INSERT INTO $table VALUES(";

  open (IN, "$inputdir/$file") or die "file $file could not be opened";

  my ($line, @info);

  while ($line = <IN>){
    chomp ($line);

    # remove leading whitespace
    # eturns $line with no leading whitespace                                       
    $line =~ s/^\s+//; 

    next if $line =~ /^(\#)/;  # skip comment

    @info = split /\t/, $line;

# prepare the sql statement after you know the number of fields
    if (!defined ($sth)){

      my ($i);
      for ($i=0; $i<@info; $i++){
	$insertion .= "?,";
      }
      # remove last comma
      chop ($insertion);

      $insertion .= ")";
#       print "\t$insertion\n";
      $sth = $dbh->prepare($insertion);
    }

# insert
#     print "\tnum fields " . @info . "\n";
#     print "\t\'@info\'\n";

    replace_w_undef (\@info);

    $sth->execute (@info) or die "failed insert";
  }

  close (IN);

}

# replace "NULL" string with undef, required for correct
# interpretation by mysql
sub replace_w_undef($){
  my ($info) = @_;

  my ($elem, $i);

  for ($i=0; $i<@$info; $i++){

    if ($info->[$i] eq "NULL"){
      $info->[$i] = undef;
    }
  }

}

sub extract_parameters()
{
	my %params = (
		inputdir => \(my $inputdir = undef),
		outputdir => \(my $outputdir = undef),
		db => \(my $db = undef),
		export => \(my $export = 1),

	);

	# Call Library function which will extract and remove all switches and
	# their corresponding values.
	# These parameters will be removed from @ARGV

	my $errorval = &GetOptions(\%params, "inputdir=s",
				   "outputdir=s", "db=s", "export!");
                        # the exclaimation point allows for the negation
                        # of the switch (i.e. -nobackup/-nobody is a switch)

	# Error value should have returned 1 if nothing went wrong
	# Otherwise, an unlisted parameter was specified.
	if($errorval !=1)
	{
		# Expecting GetOptions to state error.

		print "Exiting Program...\n";
		exit 0;
	}

	defined ($inputdir) or die "inputdir not defined\n";
if ($export){
    defined ($outputdir) or die "outputdir not defined\n";
}
	defined ($db) or die "db not defined\n";

	return ($inputdir, $outputdir, $db, $export);
	
}
