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_INCREMENT 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'} = length($_); } } }else{ if($cols[$j]{'value'} < length($_)){ $cols[$j]{'value'} = length($_); } } $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 and 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()); }