#!/usr/bin/perl -w

# NAME
#       gupta_to_mysql - convert gupta database dump to MySQL
#
# SYNOPSIS
#       gupta_to_mysql guptadump.sql.load | mysql
#
# DESCRIPTION
#       gupta_to_mysql will convert a dump to MySQL statements.
#       
# AUTHOR
#       Ole Tange (ole@ange.dk)
#	Version: 2007-JUN-11
#
# LICENSE
#       GPL
#
# SEE ALSO
#       cat(1), tee(1)

$debug = 0;

$/="\r\n/\r\n";

$old_argv="";
while(<>) {
    if($ARGV ne $old_argv) { print STDERR $ARGV,"\n"; $old_argv = $ARGV; }
    if(/^SET LOADVERSION/) { next; }
    if(/^DELETE FROM (\S+) WHERE (\S+)/) { delete_cmd($1,$2); next; }
    if(/^DELETE/) { die($_); }
    if(/^INSERT INTO (\S+)\((.*)\) VALUES \((.*)\)\s+\\\s+.DATATYPES (\S+)\s+(.*)/smi) {
	insert_cmd($1,$2,$3,$4,$5); next; 
    }
    if(/^INSERT/) { die($_); }

    die "Unknown: $_";
}

print drop_tables();
print create_tables();
print insert_into_tables();

sub datatype {
    my %datatypemap = 
	("NUMERIC" => "DOUBLE",
	 "CHARACTER" => "VARCHAR(255)",
	 "DATE" => "DATETIME",
	 );
    my $datatype = shift;
    if($datatypemap{$datatype}) {
	return $datatypemap{$datatype};
    } else {
	die "$datatype is not in map";
    }
}

sub insert_into_tables {
    my @out = ();
    for $table (keys %{$gupta{'table'}}) {
	my $table_ref = $gupta{'table'}{$table};
	my @values = ();
	for $lineno (keys %{$table_ref->{'data'}}) {
	    $debug and print "lineno $lineno\n";
	    my $line_ref = $table_ref->{'data'}{$lineno};
	    my @valueline = ();
	    for $colno (sort { $a <=> $b } keys %{$line_ref}) {
		if($line_ref->{$colno} =~ /./) {
		    push @valueline, "'".$line_ref->{$colno}."'";
		} else {
		    push @valueline, "''";
		}
	    }
	    push @values, "(". join (",", @valueline). ")" ;
	}
	if(@values) {
	    for(@values) {
		push @out, "INSERT INTO `$table` VALUES \n", $_, ";\n\n";
	    }
	    # Do not do all values in a chuck as it results in:
	    # ERROR 1153 (08S01) at line 1215: Got a packet bigger than 'max_allowed_packet' bytes
	}
    }
    return @out;
}


sub create_tables {
    my @out = ();
    for $table (keys %{$gupta{'table'}}) {
	my $table_ref = $gupta{'table'}{$table};
	my @columns = ();
	for $column (sort { 
	    $table_ref->{'column'}{$a} <=> $table_ref->{'column'}{$b}
	} keys %{$table_ref->{'column'}}) {
	    my $posi = $table_ref->{'column'}{$column};
	    my $datatype = datatype($table_ref->{'datatype'}{$posi});
	    push @columns, "`$column` $datatype"
	}
	if(@columns) {
	    push @out, "CREATE TABLE `$table` (\n", join(",\n", @columns),
	    ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='$table';\n\n";
	}
    }
    return @out;
}

sub drop_tables {
    my @out = ();
    for $table (keys %{$gupta{'table'}}) {
	push @out, "DROP TABLE IF EXISTS `$table`;\n";
    }
    return @out;
}

sub insert_cmd {
    my $gupta_table = shift;
    my $column_names = shift;
    my $positions = shift;
    my $datatypes = shift;
    my $data = shift;
    $gupta_table =~ s/\./_/gi;
    my $table = $gupta_table;
    $column_names =~ s/\s*//g;
    my @column = split(/,/s, $column_names);
    my $i = 1;
    for $column (@column) {
	$gupta{'table'}{$table}{'column'}{$column} = $i;
	$i++;
    }

    $positions =~ s/\s*//g; $positions =~ s/://g;
    my @pos = split(/,/s, $positions);
    $i = 1;
    for $pos (@pos) {
	$gupta{'table'}{$table}{'positions'}{$pos} = $i;
	$i++;
    }

    my @datatypes = split(/,/s, $datatypes);
    $i = 1;
    for $type (@datatypes) {
	$gupta{'table'}{$table}{'datatype'}{$i}=$type;
	$i++;
    }
    my @dataline = split(/\r\n/, $data);
    my $lineno = 0;
    while($line = shift @dataline) {
	if($line=~m:^/$:) { next; }
	while($gupta{'table'}{$table}{'data'}{$lineno}) { $lineno++; }
	$debug and print "dataline $line\n";
	$line =~ s/\\/backslash/g; # Avoid: ('428','','\\Gupta\public\sources\win32\mxonl32\iniFiles\mtw301\','1473'),
	my $fieldno = 0;
	while ($line =~ s/^([^\",]*),
	       |
	       ^\"(([^\"]|"")*)\",//x) {
	    # Eat the line from the start a comma at a time while dealing with 
	    # lines like: ,,,"foo, ""bar"" fubar",,,
	    $fieldno++;
	    #print $line,"::$1::$2\n";
	    if(defined($1)) {
		$f = $1;
	    } elsif (defined($2)) {
		$f = $2;
	    } else { die("No data in or outside quotes: $line $1 $2\n"); }
	    $f =~ s/""/\"/g; $f =~ s/'/\''/g;
	    if($f ne '$long') {
		$gupta{'table'}{$table}{'data'}{$lineno}{$fieldno}=$f;
	    } else {
		# $long refers to a here document ending with //
		my $here_line;
		my $here_doc = "";
		while(defined($here_line = shift @dataline)) {
		    if($here_line eq "//") { last }
		    $here_line =~ s/""/\"/g; $here_line =~ s/'/\''/g;
		    $here_doc .= $here_line."\n";
		}
		$gupta{'table'}{$table}{'data'}{$lineno}{$fieldno}=$here_doc;		
	    }
	}
    }
}

sub delete_cmd {
    my $gupta_table = shift;
    my $where = shift;
    $gupta_table =~ s/\./_/gi;
    $gupta{'table'}{$gupta_table}{'delete'}{$where} = 1;
}