use strict;
use XBase;
use DBI;
use Getopt::Std;

sub buildTable;
sub parse_data;
sub alter_table;
sub query($);
sub mod;

getopt('ijd');
our($opt_i,$opt_j,$opt_d);

if(!(defined($opt_i) && defined($opt_j) && defined($opt_d))){ exit; }

my $host = 'localhost';
my $user = '###user###';
my $pass = '###password###';

my $dbh = DBI->connect("DBI:mysql:database=$opt_d;host=$host", $user, 
+$pass );
if(!defined($dbh)){exit(-1);}

my $table = 'jn'.$opt_j;    # Table Name
my @cols = $dbh->tables;    # Using cols as a temp holder
foreach(@cols){
  $_=~s/`//g;
  if($_ eq $table){ exit(-2); }
}
@cols = ();

$opt_i=~s/\\{1,2}/\//g;
my $dbf = new XBase $opt_i or die Xbase->errstr;

my $temp = "";
if($opt_i=~/^(.+)\/(.+?)$/){ $temp = $1."\/".$table."_".$^T; }

mod();

sub buildTable{
  my @names = $dbf->field_names;
  my @size = $dbf->field_lengths;
  my $sql = 'CREATE TABLE '.$table.' (GENID mediumint UNSIGNED AUTO_IN
+CREMENT NOT NULL,';
  for(my $i = 0; $i <= $#names; $i++){
    $sql .= $names[$i].' char('.$size[$i].') null,';
    $cols[$i] = {'name'=> $names[$i], 'type'=>'n','value'=>0};
  }
  return $sql.='PRIMARY KEY(GENID))';
}

sub parse_data(){
  open (TEMP, ">".$temp) or die("Cannot create temp file");
  my $cursor = $dbf->prepare_select;
  my $i = 1;
  my $sql .= '';
  my @queries = ();
  while(my @data = $cursor->fetch){
    my $j = 0;
    for(@data){
      s/([\'\"\;\\\/\*])/\\$1/g;     
      if($cols[$j]{'type'} eq 'n'){
        if(/^\d$/){
          if($cols[$j]{'value'} < $_){ $cols[$j]{'value'} = $_; }
        }else{
          $cols[$j]{'type'} = 'c';
          if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = le
+ngth($_); }
        }
      }else{
        if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = leng
+th($_); }
      }
      $j++;
    }
    print TEMP $i.',"'.join('","', @data)."\"\n";
    $i++;
  }
}

sub alter_table(){
  my @new_cols = ();
  my $type = "";
  for(my $i=0;$i<$#cols;$i++){
    if($cols[$i]{'type'} eq 'n'){
      $type = pick_type($cols[$i]{'value'}, 'mysql');  
      #if undefined reset and return false;
    }else{
      $type = 'varchar('.$cols[$i]{'value'}.')';
    }
    
    if(!defined $type){
      return undef;
    }else{
      $new_cols[$i] = ' MODIFY '.$cols[$i]{'name'}.' '.$type.' NULL';
    }
    
    if($cols[$i]{'value'} == 0){
      $new_cols[$i] = ' DROP COLUMN '.$cols[$i]{'name'};
    }
    
  }
  return 'ALTER TABLE '.$table.' '.join(',',@new_cols);   
}

sub pick_type($$){
  
  my @type = ();
  if($_[1] eq 'mysql'){
    @type = ( {'name' => 'tinyint' , 's' => 128, 'u' => 255, },
              {'name' => 'smallint' , 's' => 32767, 'u' => 65535, },
              {'name' => 'mediumint' , 's' => 8388607, 'u' => 16777215
+, },
              {'name' => 'int' , 's' => 2147483647, 'u' => 4294967295,
+ }
            )
  }else{
    return undef;
  }
  

  #only checking if smaller than unsigned - later check for negative a
+nd floats
  for(my $i=0; $i <= $#type; $i++){
    if($_[0] < $type[$i]{'u'}){ return $type[$i]{'name'}; }

  }
  return undef;
}

sub query($){
  my $sth = $dbh->prepare($_[0]);
  $sth->execute(); 
}

sub mod{
  query(buildTable());
  parse_data();
  query('LOAD DATA INFILE "'.$temp.'" INTO TABLE '.$table.'
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
        LINES TERMINATED BY "\r\n"');
  query(alter_table());
}
[download]