Up to [Platon] / scripts / perl / mysql

Added --add-column-names commandline option.

#!/usr/bin/perl

#
# mysqldump-convert.pl - convert extended MySQL dump into normal MySQL dump
#
#
# Developed by Lubomir Host 'rajo' <rajo AT platon.sk>
# Copyright (c) 2004 Platon SDG, http://platon.sk/
# Licensed under terms of GNU General Public License.
# All rights reserved.
#
# Changelog:
# 2004-10-13 - created
#
# Keywords: MySQL, SQL dump, mysqldump, convert MySQL, convertors, regular expression, regexp, quoted escaped string
#
# Usage: mysqldump --opt -u user Database | ./mysqldump-convert.pl
#
#
# Notes:
#        - in vim editor use this regexp: /\('\([^'\\]\|\(\\.\)\|\(\\\\\)\)*'\)
#

# $Platon: scripts/perl/mysql/mysqldump-convert.pl,v 1.4 2005/04/26 08:49:29 rajo Exp $

use strict;

my ($insert_t, $create_t, $column_t, $value_t) = (1, 1, 1, 1);

$| = 1;

my $f_column_names = 0;

$f_column_names = 1 if (scalar(@ARGV) > 0 and $ARGV[0] eq '--add-column-names');

my $input = '';
my @column_names = ( );

while (my $line = <STDIN>) {
    $input .= $line; # read and add next line into buffer
    
    #print "INPUT: $input";

    if ($f_column_names and $input =~ s/^(\s*CREATE\s+TABLE\s\S+\s+\(\s*)/(/mi) { # {{{
        # first separate begin of insert command
        print $1;
        $create_t = 0;
        $column_t = 0;
        my $values = '';
        while ($create_t == 0 && $input =~ s/^\(//m) {
            while  ($column_t == 0) {
                if ($input =~ s/^(\s*(?:PRIMARY\s+)?KEY\s+\([^\)]+\).*)//mi) {
                    $column_t = 0;
                    $create_t = 0;
                    print $1;
                }
                elsif ($input =~ s/^(\s*\)[^;]*;)\s*//m) { # end of create table
                    $column_t = 1;
                    $create_t = 1;
                    print $1;
                }
                elsif ($input =~ s/^(\s*)([^`,)]+|`(?:[^`\\]|(?:\\.)|(?:\\\\))*`)(\s*)//m) {
                    print $1, $2, $3;
                    push @column_names, $2;
                }
                if ($input =~ s/^([^,]*),(\s*)//m) {
                    $column_t = 0;
                    $create_t = 0;
                    print "$1,$2";
                }
                else { # line end but columns/values list continues on the next line
                    # following line is fix for endless loop, DON'T REMOVE them !!!
                    $input .= <STDIN>;
                }
            }
        }
        $column_t = 1;
        # get first column
    } # }}}
    elsif ($input =~ s/^(\s*INSERT\s+INTO\s\S+\s+.*VALUES\s+)//mi) { # {{{
        # first separate begin of insert command
        my $insert_command = $1; # save sql insert

        #use Data::Dumper;
        #print Dumper(\@column_names);

        $insert_t = 0;
        $column_t = 0;
        my $column_count = 0;
        my $values = '';
        while ($insert_t == 0 && $input =~ s/^\(//m) {
            while  ($column_t == 0) {
                if ($input =~ s/^\s*([^',)]+|'(?:[^'\\]|(?:\\.)|(?:\\\\))*')\s*//m) {
                    my $value = $1;
                    $values .= $f_column_names ? "\n/* $column_names[$column_count] */\t$value" : $value;
                    $column_count++;
                }
                if ($input =~ s/^,\s*//m) {
                    $column_t = 0;
                    $insert_t = 0;
                    $values .= ",";
                }
                elsif ($input =~ s/^\s*\)\s*,\s*\(//m) { # end of values
                    $column_t = 0;
                    $insert_t = 0;
                    print "$insert_command($values);\n";
                    $values = '';
                    $column_count = 0;
                }
                elsif ($input =~ s/^\s*\)\s*;\s*//m) { # end of insert
                    $column_t = 1;
                    $insert_t = 1;
                    print "$insert_command($values);\n";
                    $values = '';
                }
                else { # line end but columns/values list continues on the next line
                    # following line is fix for endless loop, DON'T REMOVE them !!!
                    $input .= <STDIN>;
                }
            }
        }
        $column_t = 1;
        # get first column
    } # }}}
    else { # print and clear input buffer
        print $input;
        $input = '';
    }
}


# vim: ts=4
# vim600: fdm=marker fdl=0 fdc=3