#!/usr/bin/perl 
#===============================================================================
#
#         FILE:  yaml_to_sql.pl
#
#        USAGE:  ./yaml_to_sql.pl 
#
#  DESCRIPTION:  Converts two-section YAML file into SQL.
#
#      OPTIONS:  
#       --add-drop-table: 
#           add DROP TABLE into start of file (on by default),
#           use --noadd-drop-table to invert.
#                    
#       --insert-fldnames: 
#           use all field names in insert query.
#
#
# REQUIREMENTS:  Getopt::Long, YAML, Tie::IxHash
#         BUGS:  ---
#        NOTES:  ---
#      AUTHORS:  Pavel Boldin (davinchi), <boldin.pavel@gmail.com>
#                Walery Studennikov (despair), <despair@cpan.org>
#      COMPANY:  
#      VERSION:  1.0
#      CREATED:  26.11.2007 18:10:14 SAMT
#     REVISION:  ---
#===============================================================================

use strict;
use warnings;


use Getopt::Long;
use YAML;
use Tie::IxHash;

our ($drop_if_exists, $insert_fldnames) = (1, 0);

GetOptions(
    'add-drop-table!' => \$drop_if_exists,
    'insert-fldnames!' => \$insert_fldnames,
) or die "Cant parse args";

our $fname = shift @ARGV or die_help('Cant find filename');
our $outfile = shift @ARGV;

die "Output file $outfile already exists" if (defined $outfile && -f $outfile);

if (defined $outfile) {
    open my $output, '>', $outfile 
        or die "Cannot open $outfile for writing: $!";
    select($output);
}

our ($meta, $data) = YAML::LoadFile($fname) or die "Cant parse YAML file: $fname";

print_create_table($meta);

print "\n";

print_table_data($meta, $data);

if (defined $outfile) {
    close(select(STDOUT));
}

###########################################################################
#  Functions section
###########################################################################

sub die_help {
    print shift, "\n";
    
    print <<EOF;
Usage: $0 filename.yaml

Converts YAML into SQL.

YAML file should consist of two parts:
1st with Table meta data
2nd with array of table rows
Each row can be either an array (then order of fields matches table columns)
or an hash (then hash key is an field name)
EOF

    exit(shift || 0);
}

sub print_create_table {
    my $meta = shift;

    my $tname = $meta->{tablename} or die "No table name in $fname YAML";

    ref ($meta->{fields}) eq 'ARRAY' or die "No fields in $fname YAML";

    print 'DROP TABLE IF EXISTS `'.$tname.'`'.";\n" if $drop_if_exists;
    print 'CREATE TABLE `'.$tname.'` ('."\n";

    my @fields;

    foreach my $field (@{$meta->{fields}}) {
        die "Field entry is not an hash!" unless ref $field eq 'HASH';

        my ($field_name, $junk) = keys %$field;
        my ($field_value) = values %$field;
        die "Field entry has junk keys!" if $junk;

        push @fields, "\t\`$field_name\` $field_value";
    }

    print join(",\n", @fields);

    if ($meta->{meta}) {
        my $m = do {
            ref ($meta->{meta}) ? 
            $meta->{meta} :
            [ $meta->{meta} ];
        };

        print join (",\n\t", '', @{$m});
    }

    print "\n)";

    print "  " . $meta->{extra} if $meta->{extra};

    print ";\n";
}

sub _make_insert_line {
    my ($tname, $data) = @_;

    my $output = 'INSERT INTO `'.$tname.'` ';

    $output .= '( '. join (', ', map { "\`$_\`" } keys %$data). ' ) '
	if $insert_fldnames && ref $data eq 'HASH';

    $output .= 'VALUES (';

    $output .= join (', ', 
        map { 
            (my $a = $_) =~ s/'/\\'/g; 
            $a =~ /^[\d\.]+$/ ? $a : "'$a'" 
        } (ref($data) eq 'ARRAY' ? @$data : values %$data)
    );

    $output .= ');'."\n";

    return $output;
}

sub print_table_data {
    my ($meta, $data) = @_;

    die "Data is not array ref!" unless ref $data eq 'ARRAY';

    my $tname = $meta->{tablename};
    my $fields = scalar @{$meta->{fields}};

    my @fnames = map { keys %$_ } @{$meta->{fields}};

    foreach my $row (@$data) {
        if (ref($row) eq 'ARRAY') {
            print _make_insert_line($tname, $row);
        } elsif (ref($row) eq 'HASH') {

            my %fields = map { $_ => 1 } @fnames;

            $fields{$_}-- foreach keys %$row;

            if (index( join (',', values %fields),  '-1' ) >= 0) {
                die <<EOF;
Error in fields: no such field in table 
@{[ grep { $fields{$_} == '-1' } keys %$row ]}
EOF
            }

            tie my %sorted_row, 'Tie::IxHash';

            # sort keys within that row...
            %sorted_row = ( 
                map { exists $row->{$_} ? ($_ => $row->{$_}) : () } @fnames 
            );

            print _make_insert_line( $tname, \%sorted_row);
        } else {
            die "Error: $row is not array or hash";
        }
    }
}