#!/usr/bin/env perl
#
# dbf2sql.pl coverter 0.5
# Author: Manuel Baena Garcia <mbaena@lcc.uma.es>
# Copyright (c) 2005  Universidad de Malaga - All rights reverved -
#
# This program is free software; you can redistribute it and/or modify it under
# the same terms as Perl itself.

# 0.5: 
#     - Support MEMO fields.
#     - Support Float Point fields.
#     - Exclude deleted records.
#     - Replace ' with \' in strings.
#     - do all insert in one transaction.

use strict;
use XBase;

sub parsefield {
  my $type = shift;
  my $length = shift;
  my $decimal = shift;
  my $result;
  if ($type =~ /^[NF]$/) {
    $result = "NUMERIC($length,$decimal)";
  } elsif ($type eq "C") {
    $result = "CHARACTER VARYING($length)";
  } elsif ($type eq "M") {
    $result = "TEXT";
  } elsif ($type eq "D") {
    $result = "TIMESTAMP";
  } elsif ($type eq "L") {
    $result = "BOOLEAN";
  }
  return $result;
}

sub parsedata {
  my $table = shift;
  my $field_number = shift;
  my $value = shift;
  my $type = $table->{field_types}[$field_number];
  if ($type =~ /^[CM]$/) {
    $value =~ s/\'/\\\'/g;
    $value = "\'". $value . "\'";
  } elsif ($type eq "D") {
    $value = "\'". $value . "\'";
  } elsif ($type eq "L") {
    if ($value) { 
      $value = "TRUE";
    } else {
      $value = "FALSE";
    }
  }
  return $value;
}

my $table = new XBase shift or die XBase->errstr;
my $name = shift;

my @columns;
my @f_n = $table->field_names;
my @f_t = $table->field_types;
my @f_l = $table->field_lengths;
my @f_d = $table->field_decimals;
for (0..$table->last_field) {
  push @columns, @f_n[$_] . " " x (16 -length(@f_n[$_])) .
                     parsefield(@f_t[$_], @f_l[$_], @f_d[$_]);
}
print "CREATE TABLE $name (\n  ";
print join(",\n  ", @columns);
print ")\;\n\n";

print "BEGIN;\n";

for my $record (0..$table->last_record) {
  my @column;
  my @values;
  my ($deleted, @record) = $table->get_record($record);
  next if $deleted;
  for my $field (0..@record-1) {
    if (@record[$field] !~ /^$/) {
      push @column, $table->{field_names}[$field];
      push @values, parsedata($table, $field, @record[$field]);
    }
  }
  next if (@column == 0);
  print "INSERT INTO $name (". join(', ', @column) . ")\n";
  print  "VALUES (" . join(', ', @values) . ")\;\n";
}

print "END;\n";