mangle an SQL dump

mungesql.pl

#!/usr/bin/perl -w
use strict;

=head
    Split up a big .sql file into mutiple pieces for each table represented,
    and fix things along the way.
ASSUMPTIONS:
    1. All SQL is uppercase
    2. Table initialization on a line beginning with CREATE TABLE tablename
    3. Closing paren/semicolon after CREATE TABLE on a line by itself
    4. All statements following a CREATE TABLE are related to that table
    5. The entire file starts with BEGIN [TRANSACTION], and ends with COMMIT
    6. All CREATE INDEX statements are at the end of the file
    7. No quoted text within an INSERT statement contains the character
       sequence "\nCREATE TABLE".
=cut

# If true, replace CREATE TABLE statements with DELETE FROM table.
# Use this to keep existing schemas.
use constant DELETE_NOT_CREATE => 1;

my $of = '01'; # Output file sequence
my $ofh = undef;

sub nextfile(;$) {
    my ($name) = @_;
    if ($ofh) {
        print $ofh "COMMIT;\n";
        close $ofh;
    }
    print("nextfile: " . ($name // "(undef)") . "\n");
    if ($name) {
        open $ofh, ">", $of++ . '-' . $name . '.sql';
        print $ofh "BEGIN TRANSACTION;\n";
    } else {
        $ofh = undef;
    }
}


my $table = undef;
my $in_create = 0;

while (<>) {
    # Quick fix for stupid sqlite2 crap: some of these were not quoted
    # because they look like numbers, which breaks postgres
    # (Not caring about doing this with other tables, because they all seem
    # to work)
    s/^(\s*INSERT\s+INTO\s+search_words\s+VALUES\(\d+,)([^']+)(\);)$/$1'$2'$3/;

    if (/^\s*(?:BEGIN(?:\s+TRANSACTION)?|COMMIT)\s*;\s*$/) {
        # don't care, we output our own transactions
        next;
    } elsif (/^\s*CREATE\s+TABLE\s+(\w+)\b/) {
        $table = $1;
        nextfile($table);
        if (DELETE_NOT_CREATE) {
            # Ignore everything until closing paren, and replace the
            # whole CREATE TABLE statement with a DELETE FROM instead.
            print $ofh "DELETE FROM $table;\n";
            $in_create = 1;
            next;
        }
    } elsif ($in_create) {
        # this block only happens if DELETE_NOT_CREATE is true
        if (/^\s*\);\s*$/) {
            $in_create = 0;
        }
        next;
    } elsif (/^\s*CREATE\s+(?:UNIQUE\s+)?INDEX/) {
        if ($table ne "index") {
            $table = "index";
            nextfile($table);
        }
    }
    if (defined($ofh)) {
        print $ofh $_;
    }
}

nextfile(); # close ofh