#!/opt/local/bin/perl -- # -*- Perl -*-

use strict;
use English;

my $committed = 0;
my %TABLES = ();
my %ROWS = ();

chop($_ = scalar(<>));

die "Not an SQL dump? $_\n" if $_ ne "BEGIN TRANSACTION;";

my $line = "";
while (<>) {
    chop;

    if ($line ne '') {
	$line .= "\n$_";
    } else {
	$line = $_;
    }

    if ($line =~ /\;$/is) {
	if ($line =~ /^CREATE TABLE (\S+) \(\s*(.*?)\s*\);/s) {
	    parseTable($1, $2);
	} elsif ($line =~ /^INSERT INTO \"(\S+)\" VALUES\((.+)\);/s) {
	    parseInsert($1, $2);
	} elsif ($line =~ /^CREATE INDEX /) {
	    # nop;
	} elsif ($line eq 'COMMIT;') {
	    $committed = 1;
	} else {
	    die "Unexpected: $line\n";
	}

	$line = "";
    }
}

die "No commit?\n" if !$committed;

print "<database xmlns='http://nwalsh.com/ns/sqltoxml'>\n";

foreach my $table (sort keys %TABLES) {
    print "<table name='$table'>\n";
    print "<columns>\n";
    my @cols = @{$TABLES{$table}};
    my %cols = ();
    my $pos = 1;
    while (@cols) {
	my $name = shift @cols;
	my $type = shift @cols;
	$cols{$pos} = $name;
	$pos++;

	print "  <column name='$name'";
	print " type='$type'" if $type ne '';
	print "/>\n";
    }
    print "</columns>\n";
    print "<rows>\n";

    if (exists $ROWS{$table}) {
	my $data = $ROWS{$table};

	foreach my $row (@{$data}) {
	    print "  <row>\n";
	    my @values = @{$row};
	    $pos = 0;
	    while (@values) {
		my $val = shift @values;
		$pos++;

		if (defined($val)) {
		    print "    <", $cols{$pos}, ">";

		    $val =~ s/&/&amp;/sg;
		    $val =~ s/</&lt;/sg;
		    $val =~ s/>/&gt;/sg;
		    
		    print $val;
		    print "</", $cols{$pos}, ">\n";
		}
	    }
	    print "  </row>\n";
	}
    }

    print "</rows>\n";
    print "</table>\n\n";
}

print "</database>\n";

sub parseTable {
    my $name = shift;
    my $values = shift;

    #print STDERR "Parsing $name...\n";

    my @cols = ();

    foreach my $col (split(/,\s+/, $values)) {
	if ($col =~ /^(\S+) (\S+)/) {
	    push (@cols, ($1, $2));
	} elsif ($col =~ /^(\S+)$/) {
	    push (@cols, ($1, ""));
	} else {
	    die "Unexpected column: $col\n" unless $col =~ /^(\S+) (\S+).*$/;
	}
    }

    $TABLES{$name} = \@cols;
}
    
sub parseInsert {
    my $name = shift;
    my $values = shift;

    my @vals = ();
    while ($values ne '') {
	if ($values =~ /^\'(([^\']|\'\')*)\'$/s) {
	    local $_ = $1;
	    $values = "";
	    s/\'\'/\'/sg;
	    push (@vals, $_);
	} elsif ($values =~ /^\'(([^\']|\'\')*)\',(.*)$/s) {
	    local $_ = $1;
	    $values = $3;
	    s/\'\'/\'/sg;
	    push (@vals, $_);
	} elsif ($values =~ /([^,]+),?(.*)$/s) {
	    if ($1 eq 'NULL') {
		push (@vals, undef);
	    } else {
		push (@vals, $1);
	    }
	    $values = $2;
	} else {
	    die "Unparsable value: $values\n";
	}
    }

    my $data = [];
    if (exists ($ROWS{$name})) {
	$data = $ROWS{$name};
	#print STDERR "R $name ", $data, "\n";
    } else {
	$ROWS{$name} = $data;
	#print STDERR "N $name ", $data, "\n";
    }

    push (@{$data}, \@vals);
}