Index: trunk/cgi-bin/consistency-check.pl
===================================================================
--- trunk/cgi-bin/consistency-check.pl	(revision 635)
+++ trunk/cgi-bin/consistency-check.pl	(revision 901)
@@ -2,4 +2,7 @@
 # ipdb/cgi-bin/consistency-check.pl
 # Does full check to see if the data in the db is consistent and complete.
+# Note this can't account for slightly squirrelly or pretzelly legacy data;
+# that has to be hand-verified when this script chokes on it and spits up
+# errors.
 ###
 # SVN revision info
@@ -8,5 +11,8 @@
 # Last update by $Author$
 ###
-# Copyright (C) 2004-2010 - Kris Deugau
+# Copyright (C) 2004-2010,2014,2017 - Kris Deugau
+
+use strict;
+use warnings;
 
 use DBI;
@@ -19,137 +25,115 @@
 use MyIPDB;
 
+# Convenience for calling from admin tools
 print "Content-type: text/plain\n\n";
 
-($dbh,$errstr) = connectDB_My;
+my ($dbh,$errstr) = connectDB_My;
 die $errstr if !$dbh;
 # May as well.  We need a number of globals.
 initIPDBGlobals($dbh);
 
-## Do some new init things to account for IPDB.pm changes
-my $tmp;
-# get master blocks in an array
-my @masterblocks;
-$tmp = getMasterList($dbh);
-foreach (@{$tmp}) {
-  push @masterblocks, NetAddr::IP->new($_->{master});
+my %idlist;
+my $sth = $dbh->prepare("SELECT id FROM allocations WHERE type='mm' ORDER BY cidr");
+$sth->execute;
+while (my ($mid) = $sth->fetchrow_array) {
+  checkcontainer($mid, \%idlist, 0);
 }
 
-print "Checking master containment...\n";
-
-# First check - make sure ALL routes and allocated blocks are part
-# of one of the master blocks.
-print "  Checking routed blocks:";
-$sth = $dbh->prepare("select cidr from routed");
+print "\nChecking that all allocations are referenced:\n";
+$sth = $dbh->prepare("SELECT id FROM allocations");
 $sth->execute;
-$flag = '';
-my $i;
-ROUTED: while (@data = $sth->fetchrow_array) {
-  $cidr = new NetAddr::IP $data[0];
-  foreach $master (@masterblocks) {
-    if ($master->contains($cidr)) { next ROUTED; }
+my $i = 0;
+while (my ($id) = $sth->fetchrow_array) {
+  delete $idlist{$id};
+  $i++;
+}
+print "  $i allocations confirmed referenced\n";
+$i = 0;
+$sth = $dbh->prepare("SELECT id FROM freeblocks");
+$sth->execute;
+while (my ($id) = $sth->fetchrow_array) {
+  delete $idlist{'fb'.$id};
+  $i++;
+}
+print "  $i freeblocks confirmed referenced\n";
+foreach (keys %idlist) {
+  if (/^fb/) {
+    print "  Unreferenced free block ID $_, $idlist{$_}\n";
+  } else {
+    print "  Unreferenced block ID $_, $idlist{$_}\n";
   }
-  print "\n    $cidr not mastered";
-  last if $i++>10;
-}
-print "$flag done.\n";
-
-# Next test:  All allocations must be part of a master.
-print "  Checking allocations:";
-$sth = $dbh->prepare("select cidr from allocations");
-$sth->execute;
-$flag = '';
-ALLOCATED: while (@data = $sth->fetchrow_array) {
-  $cidr = new NetAddr::IP $data[0];
-  foreach $master (@masterblocks) {
-    if ($master->contains($cidr)) { next ALLOCATED; }
-  }
-  print "\n    $cidr not mastered";
-  $flag = "\n ";
-}
-print "$flag done.\n";
-
-# Next:  free blocks
-print "  Checking freeblocks:";
-$sth = $dbh->prepare("select cidr from freeblocks order by cidr");
-$sth->execute;
-$flag = '';
-FREEBLOCK: while (@data = $sth->fetchrow_array) {
-  $cidr = new NetAddr::IP $data[0];
-  foreach $master (@masterblocks) {
-    if ($master->contains($cidr)) { next FREEBLOCK; }
-  }
-  print "\n    $cidr not mastered";
-  $flag = "\n ";
-}
-print "$flag done.\n";
-
-print "Done checking master containment.\n\n";
-
-print "Checking pool containment...\n";
-
-$sth = $dbh->prepare("select pool,ip from poolips order by ip");
-$sth->execute;
-while (@data = $sth->fetchrow_array) {
-  $pool = new NetAddr::IP $data[0];
-  $ip = new NetAddr::IP $data[1];
-  print "  IP $ip listed with incorrect pool $pool\n"
-    if !$pool->contains($ip);
-}
-$sth = $dbh->prepare("select distinct pool from poolips order by pool");
-$sth->execute;
-while (@data = $sth->fetchrow_array) {
-  $sth2 = $dbh->prepare("select cidr from allocations where cidr='$data[0]'");
-  $sth2->execute;
-  print "  Pool $data[0] does not exist in allocations table\n"
-    if (($sth2->fetchrow_array)[0] eq '');
 }
 
-print "Done checking pool containment.\n\n";
+print "\nChecking for correctness on 'defined' CustIDs:\n";
+$sth = $dbh->prepare("SELECT cidr,type,custid FROM allocations WHERE NOT (type='cn' OR type LIKE '_r') ORDER BY cidr");
+$sth->execute;
+while (my($cidr,$type,$custid) = $sth->fetchrow_array) {
+  print "$cidr ($disp_alloctypes{$type}) has incorrect CustID $custid\n"
+	if $custid ne $def_custids{$type};
+}
+print "Done predefined CustID correctness check.\n\n";
 
-print "Checking block-alignment consistency...\n";
+print "Checking for customer blocks with 'bad' CustIDs:\n";
+# Make sure cn-type ("customer netblock") blocks have "real" CustIDs.
+$sth = $dbh->prepare("SELECT cidr,custid,description FROM allocations WHERE type='cn' AND (custid='$IPDB::defcustid' OR custid='STAFF') ORDER BY cidr");
+$sth->execute;
+while (my ($cidr,$custid,$desc) = $sth->fetchrow_array) {
+  print "cn block $cidr ($desc) has incorrect CustID $custid\n";
+}
+print "Done checking customer blocks\n";
 
-# Block alignment consistency:  All allocated+free blocks within a master
-# must NOT overlap, and they must show no gaps.
-# eg, if we have blocks:
-#  master is 192.168.2.0/24
-#  allocated 192.168.2.0/29, 192.168.2.8/29, 192.168.2.64/26
-#  free 192.168.2.16/28, 192.168.2.32/27, 192.168.2.128/25
-# then we're OK, but if any one of the allocated or free blocks is missing,
-# something b0rked.
+exit;
 
-# (select cidr from allocations where cidr <<= '$master') union
-#  (select cidr from freeblocks where cidr <<= '$master')
-#  order by cidr
 
-foreach $master (@masterblocks) {
-  print "  Master $master:\n";
-  $prev = $master;
-  $sth = $dbh->prepare("(select network(cidr) as net, broadcast(cidr) as bcast ".
-	"from allocations where cidr <<= '$master' and not (type like '_c' or type in ('rm','mm')) ) ".
-	"union (select network(cidr) as net, broadcast(cidr) as bcast ".
-	"from freeblocks where cidr <<= '$master' and not (routed='c')) order by net");
-  $sth->execute;
+# All we need to check on is "do the allocations and free blocks within a container completely make up the container?"
+sub checkcontainer {
+  my $cid = shift;
+  my $idlist = shift;
+  my $depth = shift;
 
-  while (my ($net,$bcast) = $sth->fetchrow_array) {
+  # snarf the container's CIDR
+  my $sth = $dbh->prepare("SELECT cidr,type FROM allocations WHERE id = ?");
+  $sth->execute($cid);
+  my ($ccidr,$ctype) = $sth->fetchrow_array();
+  # note that we've "seen" the ID/CIDR
+  $idlist->{$cid} = $ccidr;
+  $ccidr = NetAddr::IP->new($ccidr);
+
+  $depth++;
+  print "error: gone too deep ($depth).  breaking off\n" if $depth > 6;
+  my $indent = '  ' x $depth;
+  print $indent."Checking $disp_alloctypes{$ctype} $ccidr ($cid)\n";
+
+  $sth = $dbh->prepare(qq(
+	SELECT id, cidr, type, network(cidr) AS net, broadcast(cidr) AS bcast FROM allocations WHERE parent_id = ?
+	UNION
+	SELECT id, cidr, 'f' AS type, network(cidr) AS net, broadcast(cidr) AS bcast FROM freeblocks WHERE parent_id = ?
+	ORDER BY cidr
+	));
+  $sth->execute($cid, $cid) or die $dbh->errstr;
+  my $prev = $ccidr;
+  while (my ($childid, $childcidr, $childtype, $childnet, $childbcast) = $sth->fetchrow_array) {
+    # note that we've "seen" the ID/CIDR, flagging freeblocks
+    $idlist->{$childid} = $childcidr if $childtype ne 'f';
+    $idlist->{'fb'.$childid} = $childcidr if $childtype eq 'f';
+    checkcontainer($childid, $idlist, $depth) if $childtype =~ /.[cm]/;
     my $prevn = $prev->broadcast->numeric + 1;
-    $cur = new NetAddr::IP $net;
+    my $cur = new NetAddr::IP $childnet;
     if ($cur->numeric == $prevn) {
       $prev = $cur;
       next;
     }
-#print "
-##fixme:  the rest of this could probably be squashed down quite a bit
-    if ($master->numeric == $prev->numeric) {
+    if ($ccidr->numeric == $prev->numeric) {
       # check if cur starts with master
       if ($cur->numeric > $prev->numeric) {
-        print "    Gap from start of master $master to first block $cur\n";
+        print $indent."  Gap from start of parent $ccidr to first block $cur\n";
       } elsif ($cur->numeric < $prev->numeric) {
-        print "    BIG problem!  Current block $cur begins before master $master!\n";
+        print $indent."  BIG problem!  Current block $cur begins before parent $ccidr!\n";
       }
     } else {
       if ($cur->numeric < ($prev->numeric + 1)) {
-        print "    Block ".$prev->network." overlaps block $cur\n";
+        print $indent."  Block ".$prev->network." overlaps block $cur\n";
       } elsif ($cur->numeric > ($prev->numeric + 1)) {
-        print "    Gap between end of block ".$prev->network." and block $cur\n";
+        print $indent."  Gap between end of block ".$prev->network." and block $cur\n";
       }
     }
@@ -157,114 +141,4 @@
     $prev = $cur;
     $prev--;
-
-  } # while (@data...)
-
-  $cur--;
-  $master--;
-  if ($cur->numeric ne $master->numeric) {
-    print "    Gap from $cur to end of master at $master\n";
   }
-  $master++;
-  print "  done $master.\n";
 }
-
-print "Done checking block alignment.\n\n";
-
-print "Checking containment on container blocks...\n";
-# First, we need a list of containers.
-# Then, we check all of the contained blocks to see if they're within
-# the proper container.
-$sth = $dbh->prepare("select cidr from allocations where type like '_c' order by cidr");
-$sth->execute;
-$i=0;
-while (@data = $sth->fetchrow_array) {
-  $containers[$i++] = new NetAddr::IP $data[0];
-}
-print "  Checking general containment:";
-$sth = $dbh->prepare("select cidr from allocations where type like '_r' order by cidr");
-$sth->execute;
-$flag = '';
-CONTAINED: while (@data = $sth->fetchrow_array) {
-  $cidr = new NetAddr::IP $data[0];
-  foreach $container (@containers) {
-    next CONTAINED if $container->contains($cidr);
-  }
-  print "\n    $cidr not contained";
-  $flag = "\n ";
-}
-print "$flag done.\n";
-print "  Checking alignment:\n";
-foreach $container (@containers) {
-  print "    Container $container:\n";
-  $prev = $container;
-  $sth = $dbh->prepare("(select network(cidr) as net, broadcast(cidr) as bcast ".
-        "from allocations where cidr <<= '$container' and type like '_r') ".
-        "union (select network(cidr) as net, broadcast(cidr) as bcast ".
-        "from freeblocks where cidr <<= '$container' and not (routed='y' or routed='n')) ".
-	"order by net");
-    $sth->execute;
-
-print "(select network(cidr) as net, broadcast(cidr) as bcast ".
-        "from allocations where cidr <<= '$container' and type like '_r')\n ".
-        "union (select network(cidr) as net, broadcast(cidr) as bcast ".
-        "from freeblocks where cidr <<= '$container' and not (routed='y' or routed='n')) ".
-        "order by net" if $container =~ /10.10.20/;
-
-  while (my ($net,$bcast) = $sth->fetchrow_array) {
-    my $prevn = $prev->broadcast->numeric + 1;
-    $cur = new NetAddr::IP $net;
-    if ($cur->numeric == $prevn) {
-      $prev = $cur;
-      next;
-    }
-
-    if ($container->numeric == $prev->numeric) {
-      # check if cur starts with master
-      if ($cur->numeric > $prev->numeric) {
-        print "      Gap from start of container $container to first block $cur\n";
-      } elsif ($cur->numeric < $prev->numeric) {
-        print "      BIG problem!  Current block $cur begins before container $container!\n";
-      }
-    } else {
-      if ($cur->numeric < ($prev->numeric + 1)) {
-        print "      Block ".$prev->network." overlaps block $cur\n";
-      } elsif ($cur->numeric > ($prev->numeric + 1)) {
-        print "      Gap between end of block ".$prev->network." and block $cur\n";
-      }
-    }
-
-    $prev = $cur;
-    $prev--;
-
-  } # while (@data...)
-
-  $cur--;
-  $container--;
-  if ($cur->numeric ne $container->numeric) {
-    print "      Gap from $cur to end of container at $container\n";
-  }
-  $container++;
-  print "    done $container.\n";
-
-}
-print "  done container alignment.\n";
-print "Done checking container containment.\n\n";
-
-print "Checking for correctness on 'defined' CustIDs:\n";
-# New check:  Make sure "defined" CustIDs are correct.
-$sth = $dbh->prepare("select cidr,type,custid from allocations where not (type='cn' or type like '_r') order by cidr");
-$sth->execute;
-while (@data = $sth->fetchrow_array) {
-  print "$data[0] ($disp_alloctypes{$data[1]}) has incorrect CustID $data[2]\n"
-	if $data[2] ne $def_custids{$data[1]};
-}
-print "Done predefined CustID correctness check.\n\n";
-
-print "Checking for customer blocks with 'bad' CustIDs:\n";
-# Make sure cn-type ("customer netblock") blocks have "real" CustIDs.
-$sth = $dbh->prepare("select cidr,type,custid from allocations where type='cn' and (custid='$IPDB::defcustid' or custid='STAFF') order by cidr");
-$sth->execute;
-while (@data = $sth->fetchrow_array) {
-  print "cn block $data[0] has incorrect CustID $data[2]\n";
-}
-print "Done checking customer blocks\n";
