Brute forcing the database ordering problem

Ovid on 2004-12-23T22:01:15

It's really annoying when trying to add a foreign key constraint to a table that doesn't exist because you haven't created it yet. So you create the database and then you add the constraint.

Then you try to reference a view that doesn't exist. Or a trigger. Or stored procedure. I've been dealing with this quite a bit because we have a ton of dynamically generated SQL. Trying to dynamically determine the order of everything was a pain, so I cheated and took the easy way out. Here's my very embarrasing brute force method of solving the problem:

sub do {
    my $self    = shift;
    my %actions = map { $_ => 1 } @_;
    my $count   = keys %actions;
    my $dbh     = $self->_dbh;

    my ($schema_created, @failures);

    while ( ! $schema_created ) {
        foreach my $action (keys %actions) {
            eval {$dbh->do($action)};
            if ($@) {
                push @failures => [$action => $@];
            } else {
                delete $actions{$action};
            }
        } 
        if ( ! @failures ) {
            $schema_created = 1;
        } elsif ( $count == keys %actions ) {
            foreach my $failure (@failures) {
                warn "Action: \n$failure->[0]\n"
                    ."Failure reason: $@\n----------\n";
            }
            die "Database schema creation failed.";
        } else {
            @failures = ();
            $count    = keys %actions;
        }
    }
    return $self;
}