source: trunk/cgi-bin/db-update.pl@ 709

#!/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 <ipdb-2.7-3.0.sql };

my $dbh;
my $errstr;
($dbh,$errstr) = connectDB_My;
my $verbose = 0;

die "db conn failed: $errstr\n" if !$dbh;

initIPDBGlobals($dbh);

local $dbh->{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";
}