#!/usr/bin/perl -w
# This simple perl script create mysql table from Visual Fox Pro table
# in MySql database (without SSL)

# PARAMETERS
my $table_name="telefony";     # name of the dbf table (without .dbf)
my $user="someone";            # mysql user
my $password="secret";         # mysql user password
my $hostname="localhost";      # localhost or ip address
my $database="data_base_name"; # mysql database
my $port="3306";               # mysql port
my $nullallow=" NOT NULL";     # make blank if you want to permit null values
# END OF PARAMETERS

my $sSql=""; # 
my $timestart = time();
use XBase;
use Getopt::Long;
use strict;
use DBI();
use List::Util qw[min max];

# Clear screen
use Term::ANSIScreen qw(cls);
my $clear_screen = cls();
print $clear_screen;
            
# open dbf table
my $table = new XBase "$table_name".".dbf" or die XBase->errstr;

# connect to MySql database 
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password);
# if dbf table was created in WIN 1250 code page
$dbh->do("SET CHARACTER SET cp1250;");
$dbh->do("SET NAMES cp1250;");


print ("\n\n\n");
print ("Table (dbf): $table_name -> Creating table $table_name in MySql database: $database \n");


# checking if table exist
my $table_exist=0;
$sSql="SHOW TABLES;";
my $sth = $dbh->prepare($sSql);
if (!$sth->execute) {
    print("Query: $sSql \n");
    die "Error: " . $sth->errstr . "\n";
}
my $names = $sth->{'NAME'};
my $numFields = $sth->{'NUM_OF_FIELDS'};
for (my $i = 0;  $i < $numFields;  $i++) {
#    printf("%s%s", $i ? "," : "", $$names[$i]);
}
print "\n";
while (my $ref = $sth->fetchrow_arrayref) {
    for (my $i = 0;  $i < $numFields;  $i++) {
#        printf("%s%s", $i ? "," : "", $$ref[$i]);
        if ( $$ref[$i] eq $table_name ) { $table_exist=1 }
    }
}
if ( $table_exist==1 ) {
    print("Table: $table_name exist in MySql database: $database \n"); 
    print("Cannot contiunue !\n");
    print("Delete table and try again !\n");
    $sth->finish();
    $dbh->disconnect();
    exit 0;
}

my @fields = $table->field_names;
my @types = $table->field_types;
my @len = $table->field_lengths;
my @dec = $table->field_decimals;
my $last_rec = $table->last_record +1 ;
my $sSql = "CREATE TABLE ".$table_name." ("; # first part of create statment
my $dane="";
my $deleted=0; # no of deleted records in table

# create table query
use Switch;
for (my $i=0; $i<=$#fields; $i++) 
{
# print "$fields[$i] : $types[$i] : $len[$i] : $dec[$i] \n";
 $sSql = $sSql.lc($fields[$i]); # field name in lower case
 switch ($types[$i]) {
    case "I" {
	$sSql = $sSql." INT(11)".$nullallow;
    }
    case "C" {
	$sSql = $sSql." VARCHAR(".$len[$i].")".$nullallow;
    }
    case "M" { # memo field
	$sSql = $sSql." TEXT".$nullallow;
    }
    case "D" { # date field
	$sSql = $sSql." DATE".$nullallow;
    }
    case "T" { # datetime field
	$sSql = $sSql." DATETIME".$nullallow;
    }
    case "L" { # BOOLEAN
	$sSql = $sSql." INT(1)".$nullallow;
    }
    case ["N","B"] { # numeric
	$sSql = $sSql." FLOAT(".$len[$i].",".$dec[$i].")".$nullallow;
    }
 }
 if ($i<$#fields) {$sSql=$sSql.","}
}
$sSql = $sSql.") TYPE=myisam;";
my $sth = $dbh->prepare($sSql);
if (!$sth->execute) {
    print("Query: $sSql \n");
    die "Error: " . $sth->errstr . "\n";
}
else
{
    print("Table: $table_name SUCCESSFULLY CREATED in MySql database: $database \n\n"); 
}
print("CREATE STATEMENT:\n$sSql \n");
$sth->finish();
$dbh->disconnect();