Changeset 761


Ignore:
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:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/cgi-bin/db-update.pl

    r679 r761  
    2727  my $get_id = $dbh->prepare("SELECT currval('allocations_id_seq')");
    2828
     29  # master blocks move to the allocations table
    2930  my $getm = $dbh->prepare("SELECT cidr,ctime,mtime,rwhois FROM masterblocks");
    3031  my $insm = $dbh->prepare("INSERT INTO allocations (cidr,type,createstamp,modifystamp,swip) VALUES (?,'mm',?,?,?)");
    31   # master must be its own master
    32 #  my $selfm = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE id = ?
    33   my $mfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='n'");
    34 #  my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ? AND NOT type='mm'");
    35   my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ?");
    36   my $setm_f = $dbh->prepare("UPDATE freeblocks SET master_id = ? WHERE cidr <<= ?");
    37   my $setm_p = $dbh->prepare("UPDATE poolips SET master_id = ? WHERE ip <<= ?");
    3832
     33  # routed blocks move to the allocations table
    3934  my $getr = $dbh->prepare("SELECT cidr,city FROM routed WHERE cidr <<= ?");
    4035  my $insr = $dbh->prepare("INSERT INTO allocations (cidr,type,city,parent_id) VALUES (?,'rm',?,?)");
    4136  my $rfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='y'");
    4237
    43   my $updalloc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND NOT (type='rm' OR type='mm')");
    44 
    45 #  my $sth_alloc_container = $dbh->prepare("UPDATE allocations SET parent = ?,rdepth=3 WHERE cidr <<= ? AND type LIKE '_r'");
    46 #  my $sth_free_container = $dbh->prepare("UPDATE freeblocks SET parent = ?,rdepth=3 WHERE cidr <<= ?");
    47   my $getc = $dbh->prepare("SELECT cidr,type,id FROM allocations WHERE cidr <<= ? AND type LIKE '_c'");
    48   my $updc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND type LIKE '_r'");
     38  # update freeblocks with new parent relation info
     39  my $mfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='m' WHERE cidr <<= ? AND routed='n'");
     40  my $setm_f = $dbh->prepare("UPDATE freeblocks SET master_id = ? WHERE cidr <<= ?");
    4941  my $cfree = $dbh->prepare("UPDATE freeblocks SET parent_id = ?, routed='c' WHERE cidr <<= ?");
    5042
    51   my $getp = $dbh->prepare("SELECT cidr,id FROM allocations WHERE type LIKE '_d' OR type LIKE '_p'");
    52   my $updpool = $dbh->prepare("UPDATE poolips SET parent_id = ? WHERE ip << ?");
     43  # update allocations with new parent relation info
     44  my $getc = $dbh->prepare("SELECT cidr,type,id FROM allocations WHERE cidr <<= ? AND type LIKE '_c'");
     45  my $setm_a = $dbh->prepare("UPDATE allocations SET master_id = ? WHERE cidr <<= ?");
     46  my $updalloc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND NOT (type='rm' OR type='mm')");
     47  my $updc = $dbh->prepare("UPDATE allocations SET parent_id = ? WHERE cidr <<= ? AND type LIKE '_r'");
    5348
    54 # "spare" freeblocks that are technically part of a container, but whose formal container parent isn't actually
    55 # present.  Arguably these could autoconvert the parent to a container, but IIRC in some cases live data has a
    56 # mix of types in a container.  *sigh*.
     49  # update poolips with new parent relation info
     50  my $getp = $dbh->prepare("SELECT cidr,id,master_id FROM allocations WHERE type LIKE '_d' OR type LIKE '_p'");
     51  my $updpool = $dbh->prepare("UPDATE poolips SET parent_id = ?, master_id = ? WHERE ip << ?");
     52
     53  # "spare" freeblocks that are technically part of a container, but whose formal container parent
     54  # isn't actually present.  Arguably these could autoconvert the parent to a container, but IIRC
     55  # in some cases live data has a mix of types in a container.  *sigh*.
    5756  my $sparef = $dbh->prepare("SELECT cidr,id FROM freeblocks WHERE parent_id = 0");
    5857  my $fparent = $dbh->prepare("UPDATE freeblocks SET parent_id = ".
     
    6059        " WHERE id = ?");
    6160
    62   # Need to disable the update trigger on the allocations table, so we don't mangle the real mtimes on the data.
     61  # Need to disable the update trigger on the allocations and poolips tables,
     62  # so we don't mangle the real mtimes on the data.
    6363  $dbh->do("DROP TRIGGER up_modtime ON allocations");
     64  $dbh->do("DROP TRIGGER up_modtime ON poolips");
    6465
    6566  $getm->execute;
    6667  while (my ($master,$mctime,$mmtime,$mswip) = $getm->fetchrow_array()) {
    67 #    next unless $master eq '10.0.0.0/8';
    68 #    next unless $master eq '172.16.0.0/12';
    6968    print "$master\t";
     69    # copy master to allocations table
    7070    $insm->execute($master, $mctime, $mmtime, $mswip);
    7171    $get_id->execute;
    7272    my ($mid) = $get_id->fetchrow_array();
    7373    print "$mid\n";
     74    # parent relation for free blocks directly under the master
    7475    $mfree->execute($mid, $master);
    7576
     
    7778    while (my ($routed,$rcity) = $getr->fetchrow_array()) {
    7879      print "  $routed\t";
     80      # copy routed to allocations table
    7981      $insr->execute($routed, $rcity, $mid);
    8082      $get_id->execute;
    8183      my ($rid) = $get_id->fetchrow_array();
    8284      print "$rid\n";
     85      # parent relation for free blocks directly under the routed block
    8386      $rfree->execute($rid, $routed);
     87      # parent relation for allocations in the routed block
    8488      $updalloc->execute($rid, $routed);
    8589      $getc->execute($routed);
    8690      while (my ($container, $ctype, $cid) = $getc->fetchrow_array()) {
    8791        print "    $container";
     92        # container blocks are now functionally equivalent to routed blocks;
     93        # update the parent relations on the contained blocks to treat the
     94        # container as the parent, not the routed block
    8895        $updc->execute($cid, $container);
    8996        my $c = $cfree->execute($cid, $container);
    90 print " $c done?\n";
    9197      }
    9298    }
     99    # Just In Case.  Bulk-set the master ID on all allocations, then freeblocks,
     100    # within the master.  This could theoretically be merged into the updates
     101    # above, but edge cases kept happening.
    93102    $setm_a->execute($mid, $master);
    94103    $setm_f->execute($mid, $master);
    95104  }
    96105
     106  # Update poolips with new parent and master relation info
    97107  $getp->execute();
    98   while (my ($pool, $pid) = $getp->fetchrow_array()) {
    99     $updpool->execute($pid, $pool);
     108  while (my ($pool, $pid, $pmaster) = $getp->fetchrow_array()) {
     109    $updpool->execute($pid, $pmaster, $pool);
    100110  }
    101111
    102   # "spare" freeblocks
     112  # clean up "spare" freeblocks from formally-incorrect use of container types
    103113  $sparef->execute();
    104114  while (my ($free, $fid) = $sparef->fetchrow_array()) {
    105 #print "$free, $fid: ";
    106 #my $par = $dbh->selectall_arrayref("SELECT id,cidr FROM allocations WHERE cidr >>= ? ORDER BY masklen(cidr)",
    107 #       undef, ($free));
    108 #print Dumper($par);
    109115    $fparent->execute($free, $fid)
    110116  }
     
    120126  # Recreate modtime trigger on allocations, now that we're done monkeying with it.
    121127  $dbh->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations FOR EACH ROW EXECUTE PROCEDURE up_modtime()");
     128  $dbh->do("CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips FOR EACH ROW EXECUTE PROCEDURE up_modtime()");
    122129
    123130  $dbh->commit;
    124 #$dbh->rollback;
    125131}; # all wrapped up in an eval{} so we can roll it all back when we want to
    126132if ($@) {
Note: See TracChangeset for help on using the changeset viewer.