Changeset 761 for trunk/cgi-bin

Timestamp:

07/28/15 18:07:38 (9 years ago)

Author:

Kris Deugau

Message:

/trunk

Review and refine db-update.pl

• Reorder a number of SQL calls; remove some stale ones that were redundant or even unused
• Remove a number of commented and uncommented debugging odds and ends

File:

• trunk/cgi-bin/db-update.pl (modified) (4 diffs)

trunk/cgi-bin/db-update.pl

@@ -27 +27 @@
   my $get_id = $dbh->prepare("SELECT currval('allocations_id_seq')");
 
+  # master blocks move to the allocations table
   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 <<= ?");
 
+  # routed blocks move to the allocations table
   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'");
+  # update freeblocks with new parent relation info
+  my $mfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='n'");
+  my $setm_f = $dbh->prepare("UPDATE freeblocks SET master_id = ? WHERE cidr <<= ?");
   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 << ?");
+  # update allocations with new parent relation info
+  my $getc = $dbh->prepare("SELECT cidr,type,id FROM allocations WHERE cidr <<= ? AND type LIKE '_c'");
+  my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ?");
+  my $updalloc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND NOT (type='rm' OR type='mm')");
+  my $updc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND type LIKE '_r'");
 
-# "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*.
+  # update poolips with new parent relation info
+  my $getp = $dbh->prepare("SELECT cidr,id,master_id FROM allocations WHERE type LIKE '_d' OR type LIKE '_p'");
+  my $updpool = $dbh->prepare("UPDATE poolips SET parent_id = ?, master_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 = ".
@@ -60 +59 @@
         " WHERE id = ?");
 
-  # Need to disable the update trigger on the allocations table, so we don't mangle the real mtimes on the data.
+  # Need to disable the update trigger on the allocations and poolips tables,
+  # so we don't mangle the real mtimes on the data.
   $dbh->do("DROP TRIGGER up_modtime ON allocations");
+  $dbh->do("DROP TRIGGER up_modtime ON poolips");
 
   $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";
+    # copy master to allocations table
     $insm->execute($master, $mctime, $mmtime, $mswip);
     $get_id->execute;
     my ($mid) = $get_id->fetchrow_array();
     print "$mid\n";
+    # parent relation for free blocks directly under the master
     $mfree->execute($mid, $master);
 
@@ -77 +78 @@
     while (my ($routed,$rcity) = $getr->fetchrow_array()) {
       print "  $routed\t";
+      # copy routed to allocations table
       $insr->execute($routed, $rcity, $mid);
       $get_id->execute;
       my ($rid) = $get_id->fetchrow_array();
       print "$rid\n";
+      # parent relation for free blocks directly under the routed block
       $rfree->execute($rid, $routed);
+      # parent relation for allocations in the routed block
       $updalloc->execute($rid, $routed);
       $getc->execute($routed);
       while (my ($container, $ctype, $cid) = $getc->fetchrow_array()) {
         print "    $container";
+        # container blocks are now functionally equivalent to routed blocks;
+        # update the parent relations on the contained blocks to treat the
+        # container as the parent, not the routed block
         $updc->execute($cid, $container);
         my $c = $cfree->execute($cid, $container);
-print " $c done?\n";
       }
     }
+    # Just In Case.  Bulk-set the master ID on all allocations, then freeblocks,
+    # within the master.  This could theoretically be merged into the updates
+    # above, but edge cases kept happening.
     $setm_a->execute($mid, $master);
     $setm_f->execute($mid, $master);
   }
 
+  # Update poolips with new parent and master relation info
   $getp->execute();
-  while (my ($pool, $pid) = $getp->fetchrow_array()) {
-    $updpool->execute($pid, $pool);
+  while (my ($pool, $pid, $pmaster) = $getp->fetchrow_array()) {
+    $updpool->execute($pid, $pmaster, $pool);
   }
 
-  # "spare" freeblocks
+  # clean up "spare" freeblocks from formally-incorrect use of container types
   $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)
   }
@@ -120 +126 @@
   # 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->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips 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 ($@) {