root/veekun/trunk/script/dump_sql.pl

#!/usr/bin/perl

use strict;
use warnings;
use File::Basename;
use File::Spec;
use YAML;

# usage: dump_sql.pl [table_groups...]
# In general, just running the script with no arguments should do what you want.

my %TABLES = (
    base    => [qw/ edits error_log forums group_permissions groups messages posts sessions shoutbox threads thread_views user_groups users /],
    gallery => [qw/ creators gallery gallery_keywords item_keywords /],
    pokedex => [qw/ abilities berries contest_effects evo_chains flavor_text items locations location_sections location_encounters machines moves move_effects pokemon pokemon_abilities pokemon_breeds pokemon_items pokemon_moves types /],
);

my (undef, $path) = fileparse( File::Spec->rel2abs($0) );
chdir $path;

my $config = YAML::LoadFile('../vee.yml');
my ($db_db, $db_user, $db_pass) = @{ $config->{'Model::DBIC'}{connect_info} };
$db_db =~ s/.+://g;  # dbi:mysql:database -> database

my @table_groups = @ARGV ? @ARGV : keys %TABLES;

for my $table_group (@table_groups) {
    next unless exists $TABLES{$table_group};

    system mysqldump => qw/--skip-quote-names/,
        ( $table_group eq 'pokedex' ? '--skip-extended-insert' : '-d' ),  # cheap cheap hack to make sure Pokedex gets its data
        '-r' => "$table_group.sql",
        '-u' => $db_user,
        ( $db_pass ? "-p$db_pass" : () ),
        $db_db => @{ $TABLES{$table_group} };

    system sed => '-ri',  # -r == +awesome
        # remove the header at the top, as it contains the server version which can change across users
        '-e' => '1, /^$/ d',

        # get rid of AUTO_INCREMENT options, which we certainly do not want in fresh tables
        '-e' => 's/ AUTO_INCREMENT=[0-9]+//',

        # hold every INSERT command and don't print it
        '-e' => '/^INSERT INTO/ { H ; d }',

        # when we run out of INSERTS:
        # - hold the line and clear it from the pattern space
        # - swap hold space with the blank pattern space
        # - delete the newline from the very first hold
        # - delete the INSERT command on adjacent inserts
        # - as is default, print the entire pattern space
        '-e' => '/ENABLE KEYS/ { H ; s/.*// ; x ; s/^\n// ; s/;\nINSERT INTO \w+ VALUES /,\n/g }',

        # also don't want that 'dump completed' line; svn will take care of timestamps
        # this leaves two newlines at the end, but fixing that is too complex to be worth it
        '-e' => '/^-- Dump completed/, $ d',
        
        "$table_group.sql";
}