#!/usr/bin/perl # Quick sort of hack to populate new columns in DB use strict; use warnings; use Data::Dumper; use MyIPDB; # run the tabledef update first. This is "safe"; IPDB < 3.0 can still happily munch away at it. print qx { PGPASSWORD=ipdbpwd psql -h localhost -U ipdb ipdb {AutoCommit} = 0; local $dbh->{RaiseError} = 1; eval { my $get_id = $dbh->prepare("SELECT currval('allocations_id_seq')"); my $getm = $dbh->prepare("SELECT cidr,ctime,mtime,rwhois FROM masterblocks"); my $insm = $dbh->prepare("INSERT INTO allocations (cidr,type,createstamp,modifystamp,swip) VALUES (?,'mm',?,?,?)"); # master must be its own master # my $selfm = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE id = ? my $mfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='n'"); # my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ? AND NOT type='mm'"); my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ?"); my $setm_f = $dbh->prepare("UPDATE freeblocks SET master_id = ? WHERE cidr <<= ?"); my $setm_p = $dbh->prepare("UPDATE poolips SET master_id = ? WHERE ip <<= ?"); my $getr = $dbh->prepare("SELECT cidr,city FROM routed WHERE cidr <<= ?"); my $insr = $dbh->prepare("INSERT INTO allocations (cidr,type,city,parent_id) VALUES (?,'rm',?,?)"); my $rfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='y'"); my $updalloc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND NOT (type='rm' OR type='mm')"); # my $sth_alloc_container = $dbh->prepare("UPDATE allocations SET parent = ?,rdepth=3 WHERE cidr <<= ? AND type LIKE '_r'"); # my $sth_free_container = $dbh->prepare("UPDATE freeblocks SET parent = ?,rdepth=3 WHERE cidr <<= ?"); my $getc = $dbh->prepare("SELECT cidr,type,id FROM allocations WHERE cidr <<= ? AND type LIKE '_c'"); my $updc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND type LIKE '_r'"); my $cfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='c' WHERE cidr <<= ?"); my $getp = $dbh->prepare("SELECT cidr,id FROM allocations WHERE type LIKE '_d' OR type LIKE '_p'"); my $updpool = $dbh->prepare("UPDATE poolips SET parent_id = ? WHERE ip << ?"); # "spare" freeblocks that are technically part of a container, but whose formal container parent isn't actually # present. Arguably these could autoconvert the parent to a container, but IIRC in some cases live data has a # mix of types in a container. *sigh*. my $sparef = $dbh->prepare("SELECT cidr,id FROM freeblocks WHERE parent_id = 0"); my $fparent = $dbh->prepare("UPDATE freeblocks SET parent_id = ". "(SELECT id FROM allocations WHERE cidr >>= ? ORDER BY masklen(cidr) DESC LIMIT 1)". " WHERE id = ?"); # Need to disable the update trigger on the allocations table, so we don't mangle the real mtimes on the data. $dbh->do("DROP TRIGGER up_modtime ON allocations"); $getm->execute; while (my ($master,$mctime,$mmtime,$mswip) = $getm->fetchrow_array()) { # next unless $master eq '10.0.0.0/8'; # next unless $master eq '172.16.0.0/12'; print "$master\t"; $insm->execute($master, $mctime, $mmtime, $mswip); $get_id->execute; my ($mid) = $get_id->fetchrow_array(); print "$mid\n"; $mfree->execute($mid, $master); $getr->execute($master); while (my ($routed,$rcity) = $getr->fetchrow_array()) { print " $routed\t"; $insr->execute($routed, $rcity, $mid); $get_id->execute; my ($rid) = $get_id->fetchrow_array(); print "$rid\n"; $rfree->execute($rid, $routed); $updalloc->execute($rid, $routed); $getc->execute($routed); while (my ($container, $ctype, $cid) = $getc->fetchrow_array()) { print " $container"; $updc->execute($cid, $container); my $c = $cfree->execute($cid, $container); print " $c done?\n"; } } $setm_a->execute($mid, $master); $setm_f->execute($mid, $master); } $getp->execute(); while (my ($pool, $pid) = $getp->fetchrow_array()) { $updpool->execute($pid, $pool); } # "spare" freeblocks $sparef->execute(); while (my ($free, $fid) = $sparef->fetchrow_array()) { #print "$free, $fid: "; #my $par = $dbh->selectall_arrayref("SELECT id,cidr FROM allocations WHERE cidr >>= ? ORDER BY masklen(cidr)", # undef, ($free)); #print Dumper($par); $fparent->execute($free, $fid) } if ($ARGV[0] && $ARGV[0] == 'fetchdns') { # weesa gotsa BIIIIG job, gotta fetch all the current rDNS. note this relies # on having had the DNS data imported and munged into the new pseudotypes. # ... never mind. this will be opportunistic, since DNS data isn't viewed # unless adding (not present, we should hope!) or updating (where it should # be retrieved from the authoritative API in real time) } # Recreate modtime trigger on allocations, now that we're done monkeying with it. $dbh->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations FOR EACH ROW EXECUTE PROCEDURE up_modtime()"); $dbh->commit; #$dbh->rollback; }; # all wrapped up in an eval{} so we can roll it all back when we want to if ($@) { print "ebbeh? $@\n"; }