International SQL-Ledger Network Association

Sometimes you have an SQL-Statement that you want to run on all the databases on your server. Here is the run_sql_on_all_databases.pl script to do so:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

my $driver   = "Pg";
my $database = "postgres";
my $dsn      = "DBI:$driver:dbname=$database";
my $userid   = "sql-ledger";
my $password = "";

my $dbh = DBI->connect( $dsn, $userid, $password, { RaiseError => 1 } )
  or die $DBI::errstr;

sub run_queries_on_all_dbs {
    my @queries = @_;

    my $max_query_length = 0;
    for my $query (@queries) {
        $max_query_length = length($query) if length($query) > $max_query_length;
    }

    printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30,        "-" x 10, "-" x 
$max_query_length;
    printf "| %-30s | %-10s | %-${max_query_length}s |\n", "Database Name", "Rows",   "Query";
    printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30,        "-" x 10, "-" x 
$max_query_length;

    my $dbs = $dbh->selectcol_arrayref("SELECT datname FROM pg_database WHERE datistemplate = false;");

    for my $db (@$dbs) {
        my $database_dsn = "DBI:$driver:dbname=$db";
        my $dbh          = DBI->connect( $database_dsn, $userid, $password, { RaiseError => 0, PrintError 
=> 0 } );

        if ( !$dbh ) {
            warn "Failed to connect to database '$db'. Error: $DBI::errstr\n";
            next;
        }

        for my $query (@queries) {
            my $rv = $dbh->do($query);
            if ( !defined $rv ) {
                warn "Failed to run query on database '$db'. Error: $DBI::errstr\n";
            } else {
                printf "| %-30s | %-10d | %-${max_query_length}s |\n", substr( $db, 0, 30 ), $rv, $query;
            }
        }

        $dbh->disconnect();
    }

    # Print table footer
    printf "+-%-30s-+-%-10s-+-%-${max_query_length}s-+\n", "-" x 30, "-" x 10, "-" x $max_query_length;
}

run_queries_on_all_dbs( "DELETE FROM gl WHERE id NOT IN (SELECT DISTINCT trans_id FROM acc_trans)", );

$dbh->disconnect();