Demonstrating updates across many tables

clear.sh

#!/bin/sh

dropdb sample

renamify.pl

#!/usr/bin/perl -w

use strict;
use warnings FATAL => qw(all);
use DBI;
my $dbname = "sample";

my @magicfields = qw/createdby userid/;
main();

################

sub main
{
my %cfg = handle_args(@ARGV);
my %fm = get_table_fieldmappings();
my $dbh = do_connect();
foreach my $tname (keys %fm)
        {
        print "Table: $tname\n";
        print "\tField(s): " . join(',', @{$fm{$tname}}) . "\n";
        foreach my $field (@{$fm{$tname}})
                {
                my $dbu = $dbh->prepare("UPDATE $tname SET $field=? WHERE $field=?");
                $dbu->execute($cfg{new}, $cfg{old}); # Won't do anything if there are no fields to update. Simple as that.
                }
        }
}

#####

sub handle_args
{
my @args = @_;
my %ret;
if(@args != 2)
        {usage();}

$ret{old} = $args[0];
$ret{new} = $args[1];
return %ret;
}

sub usage
{
die "renamify OLD NEW\n";
}

#####
sub do_connect
{
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname"); # pass in username and password as 2 more args if you use auth
if(! $dbh->ping)
        {die "Not connected: $!\n";}
return $dbh;
}

sub get_table_fieldmappings
{       # Returns map of tablenames to a list of fields in them that are magic.
my %ret;
my $dbh = do_connect();
foreach my $queryterm (@magicfields)
        {
        my $dbq = $dbh->prepare("SELECT table_name,column_name FROM information_schema.columns
                                INNER JOIN information_schema.tables
                                USING(table_name,table_schema)
                                WHERE  table_type = 'BASE TABLE'
                                AND column_name ILIKE '%" . $queryterm . "%'");
        $dbq->execute();
        while(my $res = $dbq->fetchrow_hashref() )
                {
                my $table = $$res{table_name};
                my $col = $$res{column_name};
                push(@{$ret{$table}}, $col);
                }
        }
return %ret;
}

sample.sql

CREATE TABLE alpha (
        id SERIAL,
        location INTEGER,
        createdby INTEGER,
        cool BOOLEAN);

CREATE TABLE beta (
        id SERIAL,
        fish BOOLEAN,
        userid INTEGER,
        reason TEXT
        );

CREATE TABLE gamma (
        id SERIAL,
        pets INTEGER,
        hairstyle TEXT,
        eyecolor TEXT
        );

INSERT INTO alpha(location,createdby,cool) VALUES(4,23,true);
INSERT INTO alpha(location,createdby,cool) VALUES(7,23,true);
INSERT INTO alpha(location,createdby,cool) VALUES(4,51,true);
INSERT INTO alpha(location,createdby,cool) VALUES(78,59,true);
INSERT INTO alpha(location,createdby,cool) VALUES(34,26,true);
INSERT INTO alpha(location,createdby,cool) VALUES(19,59,true);
INSERT INTO alpha(location,createdby,cool) VALUES(18,28,true);

INSERT INTO beta(fish,userid,reason) VALUES(false, 58, 'Hairstyle incompatibility');
INSERT INTO beta(fish,userid,reason) VALUES(true, 59, 'Excessive laughter in meetings');
INSERT INTO beta(fish,userid,reason) VALUES(true, 22, 'Drinks too much water');
INSERT INTO beta(fish,userid,reason) VALUES(false, 22, 'Stares out window all day');
INSERT INTO beta(fish,userid,reason) VALUES(false, 27, 'Not enough faults');
INSERT INTO beta(fish,userid,reason) VALUES(false, 59, 'Lethal glare');

INSERT INTO gamma(pets,hairstyle) VALUES(12, 'Mohawk');
INSERT INTO gamma(pets,hairstyle) VALUES(11, 'Bouffant');
INSERT INTO gamma(pets,hairstyle) VALUES(1, 'Beehive');
INSERT INTO gamma(pets,hairstyle) VALUES(0, 'Bald');
INSERT INTO gamma(pets,hairstyle) VALUES(0, 'Rattail');
INSERT INTO gamma(pets,hairstyle) VALUES(4, 'Crewcut');

setup.sh

#!/bin/sh

createdb sample
psql sample < sample.sql