#!/usr/bin/perl -w

# $Id: data2mysql.pl,v 1.5 2008-04-25 16:55:47 mike Exp $
#
# Converts data from a simple, easy-to-edit format called d2m into SQL
# INSERT statements suitable for feeding to MySQL.  The d2m format is
# defined as follows:
#	Comments, from "#" to the end of the line, are discarded
#	Leading and trailing whitespace is discarded
#	Blank lines are ignored
#	Otherwise, lines are either Table or Row Directives:
#	Table Directives looks like this: *<name>=<col1>,<col2>,...
#		They indicate that following lines are row lines for
#		the table called <name>, and that they consist of
#		values for the specified values in the specified
#		order.
#	Row Directives look like this: <val1>,<val2>,...
#		The indicate that the specified values should be added
#		as a row to the table most recently indicated by a
#		Table Directive, with the values corresponding in
#		order to the columns nominated in that Directive.
# If the -o command-line option is given, then the output is suitable
# for feeding to Oracle rather than MySQL.

use strict;
use warnings;
use Getopt::Std;

my %opts;
if (!getopts('mo', \%opts)) {
    print STDERR "\
Usage: $0 [options] [<input-file> ...]
	-m	Generate SQL suitable for MySQL [default]
	-o	Generate SQL suitable for Oracle
";
    exit 1;
}

die "$0: -m and -o options conflict"
    if $opts{m} && $opts{o};
my $oracle = $opts{o};

if ($oracle) {
    # This is pretty dumb, but Oracle takes every ampersand (&) in a
    # fragment of SQL as an invitation to interactively prompt for the
    # value of the variable named after it.  And there is NO WAY to
    # quote the ampersand to make it literal.  According to the FAQ:
    #	http://www.orafaq.com/wiki/SQL*Plus_FAQ#How_does_one_disable_interactive_prompting_in_SQL.2APlus.3F
    # you can get around it this way:
    print "SET DEFINE OFF\n";
}

my $table = undef;
my @columns;

while (<>) {
    chomp();
    s/^#.*//;
    s/[^&0-9]#.*//;
    s/\s+$//;
    s/^\s+//;
    next if !$_;
    if (/^\*(.*)=(.*)/) {
	$table = $1;
	@columns = split /,/, $2;
	next;
    }

    die "$0: no Table Directive before first Row Directive"
	if !defined $table;

    my @data = split /,/, $_, -1;

    # Serial-specific hacks are neither necessary (as matching is
    # case-insensitive) nor desirable (since these are edited in the
    # Admin UI).
    if (0 && $table eq "serial") {
	foreach my $i (0 .. $#columns) {
	    if ($columns[$i] eq "name") {
		# Normalise case and whitespace in serial title
		my $title = lc($data[$i]);
		$title =~ s/^\s+//;
		$title =~ s/\s+$//;
		$title =~ s/\s+/ /g;
		$data[$i] = $title;
	    } elsif ($columns[$i] eq "issn") {
		# Normalise hyphens and whitespace in ISSN
		my $issn = $data[$i];
		$issn =~ s/\s+//g;
		$issn =~ s/-//g;
		$data[$i] = $issn;
	    }
	}
    }

    if ($oracle) {
	# Unlike MySQL, Oracle actually honours foreign key constraints.
	my @values;
	my @cc;
	foreach my $i (0..$#columns) {
	    my $col = $columns[$i];
	    my $val = $data[$i];
	    if ($col =~ /_id$/ &&
		(!defined $val || $val eq "" || $val == 0)) {
		# Undefined link-ID: omit from INSERT statement
	    } else {
		push @cc, $col;
		push @values, $val;
	    }
	}
	print(qq[INSERT INTO "$table" (], join(", ", map { qq["$_"] } @cc), ") ",
	      "VALUES (", join(", ", map { s/[']/''/g; "'$_'" } @values), ");\n");
    } else {
	print("INSERT INTO $table (", join(", ", @columns), ") ",
	      "VALUES (", join(", ", map { s/[']/''/g; "'$_'" } @data), ");\n");
    }
}

if ($oracle) {
    # *sigh* ... Oracle doesn't quit at the end of the input file.
    print "QUIT\n";
}