#!/usr/bin/perl 
#===============================================================================
#
#         FILE:  yaml_to_sql.pl
#
#        USAGE:  ./yaml_to_sql.pl 
#
#  DESCRIPTION:  Converts SQL file into two-section YAML.
#
#      OPTIONS:  
#       --dump-meta: 
#           Dump meta information (CREATE TABLE) into YAML.
#           On by default, to invert use --nodump-meta.
#       --dump-data: (--nodump-data)
#           Dump data (INSERT INTO) into YAML.
#           On by default, to invert use --nodump-data.
#       --only-hashref:
#           Dump data (INSERT INTO) only in hashref (key: value pairs).
#           On by default, to invert use --noonly-hashref, then
#           only inserts with fields will be converted
#           into hashrefs.
#       
# REQUIREMENTS:  Getopt::Long, YAML::Dumper, 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 YAML::Old;
use Tie::IxHash;
use Getopt::Long;

# config

our ($dump_data, $dump_meta, $only_hashref) = (1, 1, 1);

# read options

GetOptions( 'dump-data!'    => \$dump_data,
            'dump-meta!'    => \$dump_meta,
            'only-hashref!' => \$only_hashref) or die "Cannot parse arguments";

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

# open file

my $input = \*STDIN;

if ($fname ne '-') {
    open $input, '<', $fname or die "Cant open $fname for reading: $!";
}

our $txt = do { local $/; <$input> };

if ($fname ne '-') {
    close $input;
}

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);
}

# go!

our @commands = split /;\n/, $txt;

our ($tname, $extra, @fields, @inserts, @meta) = ('([\w\-_\d]+)');

foreach my $command (@commands) {
    if ($command =~ /^DROP TABLE IF/io) {
        # warn "Ignoring DROP TABLE";
        next;
    }


    if (    $dump_meta 
        &&  $command =~ /CREATE\s+TABLE\s+(?:IF NOT EXISTS\s+)?`?([\w\-_\d]+)`? \((.*)\)\s*(.*)$/ios) {
        $tname eq '([\w\-_\d]+)' 
            or die "Two many create tables in one file";

        $tname = $1;
        $extra = $3;

        my @create_fields = split /,\n/, $2;

        foreach my $field (@create_fields) {
            if (    $field =~ /^\s*(PRIMARY|UNIQUE)\s+KEY/o 
                ||  $field =~ /^\s*INDEX/o
            ) {
                $field =~ s/^\s+//g;
                $field =~ s/\s+$//g;
                push @meta, $field;
            } else {
                $field =~ /`?([\w\d\-_]+)`?\s*(.*)/o or next;

                push @fields, { $1 => $2 };
            }
        }

        next;
    }

    if ($dump_data && $command =~ /INSERT INTO\s+`?$tname`?(.*)/is) {
        my $data = $1;

        if ($2) { 
            die "Insert into table before any create table: $fname";
            $tname = $1;
            $data = $2;
        }

        my ($fields, $values) = split /\s+VALUES\s*/, $data;

        s/^\s*\(\s*//s, s/\s*\);?\s*$//s foreach($fields, $values);

        # FROM THE DEEP OF SQL::Parser...
        my @values = do {
            my $i = -1;
            my $fields;
            my $e = '\\';
            $e = quotemeta($e);

            # they are genius, right?
            $values =~ 
                s~(?<!')'(([^'$e]|$e.|'')+)'~push(@$fields,$1);$i++;"?$i?"~ge;

            #
            s/''/\\'/g foreach @$fields;
            if ( $values =~ tr/[^\\]'// % 2 == 1 ) { 
                # What's this?
                # this is checking if after all substituion of '...'
                # there remains some unpaired "'" s
                $values =~ s/^.*\?(.+)$/$1/;
                die "Mismatched single quote before: '$values'\n";
            }
            if ($values =~ /\?\?(\d)\?/) {
                my $sql = $fields->[$1];
                die "Mismatched single quote: '$sql\n";
            }
            s/$e'/'/g, s/^'(.*)'$/$1/ foreach @$fields;

            map { 
                /\?(\d+)\?/ 
                ? 
                $fields->[$1] : 
                (
                    $_ eq "''" ? '' : $_
                ) } split /,\s*/, $values;
        };


        if ($fields || $only_hashref) {
            my @fields;
            
            @fields = split /,\s*/, $fields if $fields;

            @fields = map { keys %$_ } @::fields unless @fields;

            s/^`//, s/`$// foreach @fields;

            my $hash;

            tie %$hash, 'Tie::IxHash';

            unless (@fields == @values) {
                warn "Fields count dont matches values count: $fname command: $command";
                next;
            }


            @$hash{@fields} = @values;

            push @inserts, $hash;
        } else {
            push @inserts, \@values;
        }
    }
}

die "No table in file $fname"  if ($tname eq '([\w\-_\d]+)');

our $first;

tie %$first, 'Tie::IxHash';

%$first = (
    tablename   =>  $tname, 
    fields      =>  \@fields, 
    meta        =>  \@meta, 
    extra       =>  $extra 
);

{
    no warnings 'once';
    local $YAML::SortKeys = 0;

    print Dump($first, \@inserts);
}

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

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

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

Converts SQL into YAML.

YAML file will consist of two parts
1st with Table meta data
2nd with array of table rows
Each row will be either an array (then order of fields matches table columns)
or an hash (then hash key is an field name), depending on
have insert field list or not.
EOF

    exit(shift || 0);
}

=head1 NAME

SQL to YAML

=head1 DESCRIPT

Converts .sql files (now only MySQL or compatible) into the YAML file describing table and all it contents.

=head1 README

If you want to convert your .sql files into yaml file with table info and data columns -- use this script! (with pair in yaml_to_sql.pl)

=head1 PREREQUISITES

This script requires the C<strict>, C<Tie::IxHash>, C<YAML::Dumper> and C<Getopt::Long> modules.

=pod OSNAMES 

any

=pod SCRIPT CATEGORIES

DB

=cut