#!/usr/bin/perl -w
#
# Script to convert DBF files into relation databases.
# It was used to transfer information from DBF files into
# MYSQL database.
#
# Author: Mikhail Stepanov (stepanov.michael@gmail.com)
# Date	: 06/06/05
#

use strict;
use warnings;

use XBase;
use DBI;

use Data::Dumper;

use vars qw($VERSION $DEBUG);

BEGIN {
	$VERSION = '0.01';
	$DEBUG = 0;
}

#
# Database connection paramenets
#
my $db_name = 'db_name';
my $db_user = 'db_user';
my $db_pass = 'db_pass';
my $db_host = 'db_host';

#
# Mapping DBF types and MYSQL types. I did simple mapping because 
# my data were very simple. Be free to modify that mapping to
# customize for your case. 
#
my %type_map = (
	C => 'VARCHAR',
	N => 'INTEGER',
	I => 'INTEGER',
	L => 'TINYINT',
	D => 'DATE',
	M => 'TEXT',
);

#
# As input parameters you should pass one or more DBF files.
#
my @files = @ARGV;

#
# Get connect to the relation database
#
my $dbh = DBI->connect("dbi:mysql:database$db_name;host=$db_host", $db_user, $db_pass) or 
											die "Cannot connect to the [$db_name]: $DBI::errstr!";
#
# Main loop
#
for my $db_file(@files) {
	my($table_name) = $db_file =~ m#.*/(.*?).dbf#i;

	print "NAME: $db_file - $table_name ...\n" if $DEBUG;

	my $xbase = new XBase $db_file or die XBase->errstr;

	my @fields = $xbase->field_names();
	my @types = $xbase->field_types();
	my @lengths = $xbase->field_lengths();

	my $create = "CREATE TABLE IF NOT EXISTS `$table_name` (";

	for my $i(0 .. $#fields) {
		print "Name: $fields[$i]; type: $types[$i]; length: $lengths[$i]\n" if $DEBUG;
		
		#
		# Need to customize for more complicated cases or others databases
		#
		if($types[$i] =~ /(C|I|N|L)/) {
			$create .= "`$fields[$i]` $type_map{$types[$i]}($lengths[$i])";
		} elsif($types[$i] =~ /(D|M)/) {
			$create .= "`$fields[$i]` $type_map{$types[$i]}";
		}
	
		$create .= ", " unless $i >= $#fields;
	}
	
	#
	# I've added additional field to the each table to know data of creation for each records.
	#
	$create .= ", `d_created` DATETIME)";

	print "CREATE: $create\n" if $DEBUG;


	my $ret = $dbh->do($create) or die "Cannot create table [$table_name]: $DBI::errstr!";
	
	#
	# Inserting data
	#
	if($ret) {
		my $insert = '';
		for (0 .. $xbase->last_record) {
			my @rec = $xbase->get_record($_);
			
			shift @rec; # The first item is a flag of delete: 0/1
		
			my $insert_query = "INSERT INTO `$table_name` VALUES(".join(', ', map { $dbh->quote($_) } @rec).", now())";
			$ret = $dbh->do($insert_query);

			print "INSERT [$_] record: [$insert_query] $ret\n" if $DEBUG;
		}
	}

	$xbase->close;
}

$dbh->disconnect;								

exit(0);