Changeset 371 for trunk

Timestamp:

11/27/07 12:27:45 (17 years ago)

Author:

Kris Deugau

Message:

/trunk

Merge bugfixes and enhancements from /branches/stable r360 through r369

Location:

trunk

Files:

• cgi-bin/IPDB.pm (modified) (6 diffs)
• cgi-bin/extras/db2rwhois.pl (modified) (7 diffs)
• cgi-bin/ipdb.psql (modified) (4 diffs)
• cgi-bin/main.cgi (modified) (1 diff)
• cgi-bin/search.cgi (modified) (5 diffs)
• cgi-bin/snCalc.cgi (modified) (2 diffs)
• help.html (modified) (1 diff)
• ipdb.css (modified) (1 diff)

trunk/cgi-bin/IPDB.pm

@@ -16 +16 @@
 use DBI;
 use Net::SMTP;
+use NetAddr::IP qw( Compact );
 use POSIX;
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
@@ -24 +25 @@
         %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist @masterblocks
         %allocated %free %routed %bigfree %IPDBacl
-        &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock &deleteBlock
-        &getBlockData &mailNotify
+        &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock &addMaster
+        &deleteBlock &getBlockData &mailNotify
         );
 
@@ -33 +34 @@
                 @masterblocks %allocated %free %routed %bigfree %IPDBacl
                 &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock
-                &deleteBlock &getBlockData &mailNotify
+                &addMaster &deleteBlock &getBlockData &mailNotify
                 )]
         );
@@ -179 +180 @@
 #  $dbh->disconnect;
 } # end checkDBSanity
+
+
+## IPDB::addMaster()
+# Does all the magic necessary to sucessfully add a master block
+# Requires database handle, block to add
+# Returns failure code and error message or success code and "message"
+sub addMaster {
+  my $dbh = shift;
+  my $cidr = new NetAddr::IP shift;
+
+  # Allow transactions, and raise an exception on errors so we can catch it later.
+  # Use local to make sure these get "reset" properly on exiting this block
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
+  # Wrap all the SQL in a transaction
+  eval {
+    my $sth = $dbh->prepare("select count(*) from masterblocks where cidr <<= '$cidr'");
+    $sth->execute;
+    my @data = $sth->fetchrow_array;
+
+    if ($data[0] eq 0) {
+      # First case - master is brand-spanking-new.
+##fixme: rwhois should be globally-flagable somewhere, much like a number of other things
+## maybe a db table called "config"?
+      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
+      $sth->execute;
+
+# Unrouted blocks aren't associated with a city (yet).  We don't rely on this
+# elsewhere though;  legacy data may have traps and pitfalls in it to break this.
+# Thus the "routed" flag.
+
+      $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
+        " values ('$cidr',".$cidr->masklen.",'<NULL>','n')");
+      $sth->execute;
+
+      # If we get here, everything is happy.  Commit changes.
+      $dbh->commit;
+
+    } # new master does not contain existing master(s)
+    else {
+
+      # collect the master(s) we're going to absorb, and snag the longest netmask while we're at it.
+      my $smallmask = $cidr->masklen;
+      $sth = $dbh->prepare("select cidr as mask from masterblocks where cidr <<= '$cidr'");
+      $sth->execute;
+      my @cmasters;
+      while (my @data = $sth->fetchrow_array) {
+        my $master = new NetAddr::IP $data[0];
+        push @cmasters, $master;
+        $smallmask = $master->masklen if $master->masklen > $smallmask;
+      }
+
+      # split the new master, and keep only those blocks not part of an existing master
+      my @blocklist;
+      foreach my $seg ($cidr->split($smallmask)) {
+        my $contained = 0;
+        foreach my $master (@cmasters) {
+          $contained = 1 if $master->contains($seg);
+        }
+        push @blocklist, $seg if !$contained;
+      }
+
+      # collect the unrouted free blocks within the new master
+      $sth = $dbh->prepare("select cidr from freeblocks where ".
+                "maskbits>=$smallmask and cidr <<= '$cidr' and routed='n'");
+      $sth->execute;
+      while (my @data = $sth->fetchrow_array) {
+        my $freeblock = new NetAddr::IP $data[0];
+        push @blocklist, $freeblock;
+      }
+
+      # combine the set of free blocks we should have now.
+      @blocklist = Compact(@blocklist);
+
+      # and now insert the new data.  Make sure to delete old masters too.
+
+      # freeblocks
+      $sth = $dbh->prepare("delete from freeblocks where cidr <<= ?");
+      my $sth2 = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed) values (?,?,'<NULL>','n')");
+      foreach my $newblock (@blocklist) {
+        $sth->execute("$newblock");
+        $sth2->execute("$newblock", $newblock->masklen);
+      }
+
+      # master
+      $sth = $dbh->prepare("delete from masterblocks where cidr <<= '$cidr'");
+      $sth->execute;
+      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
+      $sth->execute;
+
+      # *whew*  If we got here, we likely suceeded.
+      $dbh->commit;
+    } # new master contained existing master(s)
+  }; # end eval
+
+  if ($@) {
+    my $msg = $@;
+    eval { $dbh->rollback; };
+    return ('FAIL',$msg);
+  } else {
+    return ('OK','OK');
+  }
+} # end addMaster
 
 
@@ -511 +616 @@
       $sth = $dbh->prepare("delete from masterblocks where cidr='$cidr'");
       $sth->execute;
-      $sth = $dbh->prepare("delete from freeblocks where cidr='$cidr'");
+      $sth = $dbh->prepare("delete from freeblocks where cidr <<= '$cidr'");
       $sth->execute;
       $dbh->commit;
@@ -569 +674 @@
           # contained which have nevertheless been allocated from a container block)
           # We want to make certain that the freeblocks are properly "labelled"
-          $sth = $dbh->prepare("select cidr from freeblocks where cidr <<= '$container'");
+          $sth = $dbh->prepare("select cidr from freeblocks where cidr <<= '$container' order by maskbits desc");
         } else {
           # Standard deallocation.

trunk/cgi-bin/extras/db2rwhois.pl

@@ -10 +10 @@
 # Last update by $Author$
 ###
-# Copyright (C) 2004,2005 - Kris Deugau
+# Copyright (C) 2004-2007 - Kris Deugau
 
 use strict;
@@ -17 +17 @@
 use NetAddr::IP;
 use MyIPDB;
+use File::Path 'rmtree';
 
 $ENV{"PATH"} = "/bin;/usr/bin";
 
 my $rwhoisDataPath = "/etc/rwhoisd";
+
+my @autharea;
+my $authrw;
+# Use the template file to allow us to keep persistent nodes aside from netblock data
+open AUTHTEMPLATE, "<$rwhoisDataPath/rwhoisd.auth_template";
+my $template_persist;
+while (<AUTHTEMPLATE>) {
+  next if /^##/;
+  $template_persist = 1 if /^[a-z]/i;
+  $autharea[0] .= $_;
+}
 
 my ($dbh,$msg) = connectDB_My;
@@ -31 +43 @@
 my %def_custids;
 
+# Get the list of live directories for potential deletion
+opendir RWHOISROOT, $rwhoisDataPath;
+my %rwhoisdirs;
+foreach (readdir RWHOISROOT) {
+  $rwhoisdirs{$_} = 1 if /^net-/;
+}
+closedir RWHOISROOT;
+
+# prefetch alloctype data
+my $sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes");
+$sth->execute;
+while (my @data = $sth->fetchrow_array) {
+  $netnameprefix{$data[0]} = $data[2];
+}
+
+# Get the list of masters to export
+my $msth = $dbh->prepare("select cidr,ctime,mtime from masterblocks where rwhois='y'");
+$msth->execute;
+
+# Prepare to select subblocks for each master
+# Make sure to remove the private netblocks from this,
+# no use or point in broadcasting our use of them.
+# Also remove the details of our "reserved CORE/WAN" blocks;  they're not critical.
+my $ssth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ".
+        "from allocations where ".
+        "not (cidr <<= '192.168.0.0/16') and ".
+        "not (cidr <<= '172.16.0.0/12') and ".
+        "not (cidr <<= '10.0.0.0/8') and ".
+        "not (type = 'wr') and ".
+        "masklen(cidr) <=30 and ".
+        "cidr <<= ?");
+
+# Customer data, for those rare blocks we really need to delegate.
+my $custsth = $dbh->prepare("select name,street,city,province,country,pocode,phone,tech_handle,special ".
+        "from customers where custid=?");
+
 # Fill in data about our master blocks as allocated from ARIN
 # We open separate files for each of these as appropriate.
-# Note that this ASS-U-MEs that we do not add master IP blocks-
-# there should probably be a separate system for doing that.
-my $sth = $dbh->prepare("select cidr,ctime,mtime from masterblocks;");
-$sth->execute;
+# Changes in master blocks are treated as complete new masters - since we're exporting
+# all data every time, this isn't so terrible as it might seem.
 my $i=0;
-GETMASTERS: while (my @data = $sth->fetchrow_array()) {
-
-# Techically, we only need to exclude 204.138.172.0/24, as we "own" all of the other blocks.
-# However, 205.207.184.0/23 and 206.130.64.0/24 are, um, awkward.
-  if ($data[0] =~ /^(192.168.0.0|172.16.0.0|10.0.0.0|20[456])/) {
-    next GETMASTERS;
-  }
+while (my @data = $msth->fetchrow_array()) {
+
   $masterblocks[$i] = new NetAddr::IP $data[0];
   my ($ctime,undef) = split /\s/, $data[1];
   my ($mtime,undef) = split /\s/, $data[2];
-print "$masterblocks[$i] $ctime $mtime\n";
+
+  print "$masterblocks[$i] $ctime $mtime\n";
 
   my $date;
   chomp ($date = `/bin/date +"%Y-%m-%d"`);
 
-# Whew!  Ugly little varmint.
-  my $masterfilename = "net-".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.
-    "/data/network/".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.".txt";
-
-# Need check here to create tree for netblock?
+  my $rwnet = "net-".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen;
+
+  # unflag the directory for deletion.  Whee!  Roundabout!
+  delete $rwhoisdirs{$rwnet};
+
+# Hokay.  Gonna do checks *here* to see if we need to create new master trees
+  my $netdatadir = "$rwhoisDataPath/$rwnet";
+  if (! -e $netdatadir) {
+    print " New master $masterblocks[$i]!\n";
+    print "  Creating directories...\n";
+    mkdir $netdatadir;
+    mkdir "$netdatadir/attribute_defs";
+    mkdir "$netdatadir/data";
+    mkdir "$netdatadir/data/network";
+    mkdir "$netdatadir/data/org";
+    mkdir "$netdatadir/data/referral";
+
+    my $serial;
+    chomp ($serial = `/bin/date '+%Y%m%d'000000000`);
+
+    print "  Creating SOA...\n";
+    open SOAFILE, ">$netdatadir/soa";
+    print SOAFILE qq(Serial-Number: $serial
+Refresh-Interval: 3600
+Increment-Interval: 1800
+Retry-Interval: 1800
+Time-To-Live: 86400
+Primary-Server: rwhois.example.com:4321
+Hostmaster: dns\@example.com
+);
+    close SOAFILE;
+
+    print "  Creating Schema...\n";
+    open SCHEMAFILE, ">$netdatadir/schema";
+    print SCHEMAFILE qq(name: network
+attributedef: $rwnet/attribute_defs/network.tmpl
+dbdir: $rwnet/data/network
+Schema-Version: $serial
+---
+name: organization
+attributedef: $rwnet/attribute_defs/org.tmpl
+dbdir: $rwnet/data/org
+description: Organization object
+Schema-Version: $serial
+---
+name: referral
+attributedef:$rwnet/attribute_defs/referral.tmpl
+dbdir:$rwnet/data/referral
+Schema-Version: $serial
+);
+    close SCHEMAFILE;
+
+    print "  Copying template files...\n";
+    qx { /bin/cp $rwhoisDataPath/skel/attribute_defs/* $netdatadir/attribute_defs/ };
+
+    print "  Creating org data...\n";
+    open ORGDATAFILE, ">$netdatadir/data/org/friendlyisp.txt";
+    print ORGDATAFILE qq(ID: NETBLK-ISP.$masterblocks[$i]
+Auth-Area: $masterblocks[$i]
+Org-Name: Friendly ISP
+Street-Address: 123 4th Street
+City: Anytown
+State: ON
+Postal-Code: H0H 0H0
+Country-Code: CA
+Phone: 000-555-1234
+Created: 20040308
+Updated: 20040308
+);
+    close ORGDATAFILE;
+
+    # Generate auth_area record, and add it to the array.
+    $authrw = 1;        # Flag for rewrite and daemon reload/restart
+
+  } # new master
+
+  # do this for all masters, so that we can use this array to export the data
+  # to rwhoisd.auth_area later if we need to
+  push @autharea, qq(type:master
+name:$masterblocks[$i]
+data-dir: $rwnet/data
+schema-file: $rwnet/schema
+soa-file: $rwnet/soa
+);
+
+  # Recreate the net-nnn.nnn.nnn.nnn-nn.txt data file
+  my $masterfilename = "$rwnet/data/network/".$masterblocks[$i]->addr."-".$masterblocks[$i]->masklen.".txt";
 
   open MASTERFILE,">$rwhoisDataPath/$masterfilename";
@@ -77 +201 @@
         "Updated-By: noc\@example.com\n";
 
-  close MASTERFILE;
-  $i++;
-}
-
-# prefetch alloctype data
-$sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes");
-$sth->execute;
-while (my @data = $sth->fetchrow_array) {
-  $netnameprefix{$data[0]} = $data[2];
-  $def_custids{$data[0]} = $data[1];
-}
-
-# Now read out the data in the "main" delegation list, and check it
-# with the master blocks.  We need to do this to decide which rWHOIS
-# "net-xxx.xxx.xxx.xxx-mask" tree the data belongs in.
-
-# Make sure to remove the private netblocks from this.
-# No use or point in broadcasting our use of them.
-# Also remove the details of our "reserved CORE/WAN" blocks;  they're not critical.
-$sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ".
-        "from allocations where ".
-        "not (cidr <<= '192.168.0.0/16') and ".
-        "not (cidr <<= '172.16.0.0/12') and ".
-        "not (cidr <<= '10.0.0.0/8') and ".
-        "not (type = 'wr') and ".
-        "masklen(cidr) <=30");
-#" and (custid='6750400' or custid like '%-RES' or custid like '%-BUS')");
-$sth->execute;
-
-my $custsth = $dbh->prepare("select name,street,city,province,country,pocode,phone,tech_handle,special from customers where custid=?");
-
-$i=0;
-while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $sth->fetchrow_array) {
+  # And now the subblocks
+  $ssth->execute("$masterblocks[$i]");
+  while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $ssth->fetchrow_array) {
 
 # We get master block info from @masterblocks.
@@ -124 +218 @@
  # Updated-By: noc@example.com
 
-  # Get the "full" network number
-  my $net = new NetAddr::IP $cidr;
+    # Get the "full" network number
+    my $net = new NetAddr::IP $cidr;
 
 # Assumptions:  All data in ipdb is public
 # If not, we need another field to indicate "public/private".
 
-  foreach my $master (@masterblocks) {
-    if ($master->contains($net)) {
-
-# Whew!  Ugly little varmint.
-      my $masterfilename = "net-".$master->addr."-".$master->masklen.
-        "/data/network/".$master->addr."-".$master->masklen.".txt";
-
-      open MASTERFILE,">>$rwhoisDataPath/$masterfilename"
-        or print "File open error: '$!' on '$rwhoisDataPath/$masterfilename'\n";
-
 # cidr custid type city description notes maskbits
 
@@ -145 +229 @@
 if ($desc =~ /^\s*$/) { $desc = 'Friendly ISP'; }
 
-      # Fix up datestamps.  We don't *really* need sub-microsecond resolution on our exports...
-      ($ctime) = ($ctime =~ /^(\d+-\d+-\d+)\s+/);
-      ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/);
+    # Fix up datestamps.  We don't *really* need sub-microsecond resolution on our exports...
+    ($ctime) = ($ctime =~ /^(\d+-\d+-\d+)\s+/);
+    ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/);
 
 # Notes:
@@ -166 +250 @@
 #  network:Class-Name:network                   [Provided by rWHOIS protocol]
 
-      my $netname = $netnameprefix{$type};
-
-      if ($swip eq 'y' && $def_custids{$type} eq '') {
-        # def_custids{$type} should only be blank on types that are
-        # allocated direct to customers.  i are sMart!!11!!oneone!
-        $custsth->execute($custid);
-        my ($name, $street, $city, $prov, $country, $pocode, $phone, $tech, $special) = $custsth->fetchrow_array;
-        $custsth->finish;
-        if ($special && $special =~ /NetName/ && $special =~ /$cidr/) {
-          ($netname) = ($special =~ /NetName$cidr: ([A-Z0-9_-]+)/);
-        } else {
-          $netname .= "-".$net->network;
-        }
-        print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
-                "Auth-Area: $master\n".
-                "Network-Name: $netname\n".
-                "IP-Network: $net\n".
-                "IP-Network-Block: ".$net->range."\n".
-                "Org-Name: $name\n".
-                "Street-Address: $street\n".
-                "City: $city\n".
-                "StateProv: $prov\n".
-                "Postal-Code: $pocode\n".
-                "Country-Code: $country\n".
-                "Tech-Contact: $tech\n".
-                "Created: $ctime\n".
-                "Updated: $mtime\n".
-                "Updated-By: noc\@example.com\n";
+    my $netname = $netnameprefix{$type};
+
+    if ($swip eq 'n') {
+      print MASTERFILE "---\nID: NETBLK-ISP.$masterblocks[$i]\n".
+        "Auth-Area: $masterblocks[$i]\n".
+        "Network-Name: $netname-".$net->network."\n".
+        "IP-Network: $net\n".
+        "IP-Network-Block: ".$net->range."\n".
+        "Org-Name: Friendly ISP\n".
+        "Street-Address: 123 4th Street\n".
+        "City: Anytown\n".
+        "StateProv: Ontario\n".
+        "Postal-Code: H0H 0H0\n".
+        "Country-Code: CA\n".
+        "Tech-Contact: ISP-ARIN-HANDLE\n".
+        "Created: $ctime\n".
+        "Updated: $mtime\n".
+        "Updated-By: noc\@example.com\n";
+    } else {
+      $custsth->execute($custid);
+      my ($name, $street, $city, $prov, $country, $pocode, $phone, $tech, $special) = $custsth->fetchrow_array;
+      $custsth->finish;
+      if ($special && $special =~ /NetName/ && $special =~ /$cidr/) {
+        ($netname) = ($special =~ /NetName$cidr: ([A-Z0-9_-]+)/);
       } else {
-        print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
-                "Auth-Area: $master\n".
-                "Network-Name: $netname-".$net->network."\n".
-                "IP-Network: $net\n".
-                "IP-Network-Block: ".$net->range."\n".
-                "Org-Name: Friendly ISP\n".
-                "Street-Address: 123 4th Street\n".
-                "City: Anytown\n".
-                "StateProv: Ontario\n".
-                "Postal-Code: H0H 0H0\n".
-                "Country-Code: CA\n".
-                "Tech-Contact: ISP-ARIN-HANDLE\n".
-                "Created: $ctime\n".
-                "Updated: $mtime\n".
-                "Updated-By: noc\@example.com\n";
-      } # decide which contact data to use (cust data only on swip==y && def custid=='')
-    } # net in master
-  } # foreach master
-
-
-
-  #  print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ".
-  #     "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\t| $data[9]\n";
-  #  print "$data[0]\t| $data[1]\t| $data[2]\t| $data[3]\t| $data[4]\t| ".
-  #     "$data[5]\t| $data[6]\t| $data[7]\t| $data[8]\n";
+        $netname .= "-".$net->network;
+      }
+      print MASTERFILE "---\nID: NETBLK-ISP.$masterblocks[$i]\n".
+        "Auth-Area: $masterblocks[$i]\n".
+        "Network-Name: $netname\n".
+        "IP-Network: $net\n".
+        "IP-Network-Block: ".$net->range."\n".
+        "Org-Name: $name\n".
+        "Street-Address: $street\n".
+        "City: $city\n".
+        "StateProv: $prov\n".
+        "Postal-Code: $pocode\n".
+        "Country-Code: $country\n".
+        "Tech-Contact: $tech\n".
+        "Created: $ctime\n".
+        "Updated: $mtime\n".
+        "Updated-By: noc\@example.com\n";
+    } # swip
+
+  } # while $ssth->fetchrow_array()
+
+  close MASTERFILE;
+
   $i++;
-} # while fetchrow_array()
-
-
+} # while $msth->fetchrow_array()
+
+# Now we see if there's obsolete netdata directories to be deleted,
+# and therefore an auth-area file to regenerate
+foreach my $netdir (keys %rwhoisdirs) {
+  print "deleting obsolete directory $netdir...\n";
+  rmtree ( "$rwhoisDataPath/$netdir", { verbose => 1, error => \my $errlist } );
+  for my $diag (@$errlist) {
+    my ($file, $message) = each %$diag;
+    if ($file eq '') {
+      print "general error: $message\n";
+    }
+  }
+  $authrw = 1;  # there's probably a more efficient place to put this.  Feh.
+}
+
+# Regenerate rwhoisd.auth_area if needed
+if ($authrw) {
+  print "Regenerating auth_area\n";
+  open RWHOISDAUTH, ">$rwhoisDataPath/rwhoisd.auth_area";
+  print RWHOISDAUTH "# WARNING: This file is autogenerated!  Any static nodes should\n".
+                "# be entered in /etc/rwhoisd/rwhoisd.auth_template\n";
+  if ($template_persist) {
+    print RWHOISDAUTH shift @autharea;
+    print RWHOISDAUTH "---\n";
+  }
+  # feh.  we need to know when we're at the end of the loop, because then
+  # we DON'T want to write the separator...
+  for (;@autharea;) {   # my head hurts.
+    print RWHOISDAUTH shift @autharea;
+    print RWHOISDAUTH "---\n" if @autharea;
+  }
+  close RWHOISDAUTH;
+
+  # restart/reload rwhoisd
+  if (-e "$rwhoisDataPath/rwhoisd.pid") {       # no pidfile, no restart.
+    print "Restarting rwhoisd\n";
+    open PIDFILE, "<$rwhoisDataPath/rwhoisd.pid";
+    my ($rwpid) = (<PIDFILE> =~ /^(\d+)/);
+    close PIDFILE;
+    kill 'HUP', $rwpid;
+  }
+}
+
+# and finally
 $dbh->disconnect;

trunk/cgi-bin/ipdb.psql

@@ -38 +38 @@
         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
         "ctime" timestamp DEFAULT now(),
-        "mtime" timestamp DEFAULT now()
+        "mtime" timestamp DEFAULT now(),
+        "rwhois" character(1) DEFAULT 'n' NOT NULL
 );
 
@@ -111 +112 @@
 GRANT ALL on "allocations" to "ipdb";
 
-CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes FROM poolips;
+CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.oldcustid, allocations.circuitid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.oldcustid, poolips.circuitid FROM poolips;
 
 REVOKE ALL on "searchme" from PUBLIC;
@@ -148 +149 @@
 ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP        ISP
 li      Static IP - LAN/POP     Static LAN/POP IP       190     NOC-VPN ISP
-li      Static IP - Management  Static management IP    191     NOC-VPN ISP
+ai      Static IP - Management  Static management IP    191     NOC-VPN ISP
 ld      Static Pool - LAN/POP   LAN pool        195     NOC-VPN ISP
-ld      Static Pool - Management        Management pool 196     NOC-VPN ISP
+ad      Static Pool - Management        Management pool 196     NOC-VPN ISP
 in      Internal netblock       Internal netblock       199     6750400 ISP
 wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400 ISP
@@ -159 +160 @@
 pr      Dynamic-route DSL netblock      Dynamic-route DSL       221             ISP
 ar      ATM block       ATM block       222             ISP
-fr      Fibre   Fibre   223             ISP
+fr      Fibre   Fibre   223     ATM-BUS ISP
 rm      Routing Routed netblock 500     6750400 ISP
 mm      Master block    Master block    999     6750400 ISP

trunk/cgi-bin/main.cgi

@@ -80 +80 @@
     print "<div type=heading align=center>Adding $cidr as master block....</div>\n";
 
-    # Allow transactions, and raise an exception on errors so we can catch it later.
-    # Use local to make sure these get "reset" properly on exiting this block
-    local $ip_dbh->{AutoCommit} = 0;
-    local $ip_dbh->{RaiseError} = 1;
-
-    # Wrap the SQL in a transaction
-    eval {
-      $sth = $ip_dbh->prepare("insert into masterblocks values ('$webvar{cidr}')");
-      $sth->execute;
-
-# Unrouted blocks aren't associated with a city (yet).  We don't rely on this
-# elsewhere though;  legacy data may have traps and pitfalls in it to break this.
-# Thus the "routed" flag.
-
-      $sth = $ip_dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
-        " values ('$webvar{cidr}',".$cidr->masklen.",'<NULL>','n')");
-      $sth->execute;
-
-      # If we get here, everything is happy.  Commit changes.
-      $ip_dbh->commit;
-    }; # end eval
-
-    if ($@) {
-      my $msg = $@;
+    my ($code,$msg) = addMaster($ip_dbh, $webvar{cidr});
+
+    if ($code eq 'FAIL') {
       carp "Transaction aborted because $msg";
-      eval { $ip_dbh->rollback; };
       syslog "err", "Could not add master block '$webvar{cidr}' to database: '$msg'";
       printError("Could not add master block $webvar{cidr} to database: $msg");

trunk/cgi-bin/search.cgi

@@ -279 +279 @@
 #    which probably shouldn't be for reasons of sanity.
 
+  my $cols = "cidr,custid,type,city,description";
+
   if ($category eq 'all') {
 
     print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n);
-    $sql = "select * from searchme";
+    $sql = "select $cols from searchme";
     my $count = countRows($sql);
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -293 +295 @@
     # Query for a customer ID.  Note that we can't restrict to "numeric-only"
     # as we have non-numeric custIDs in the legacy data.  :/
-    $sql = "select * from searchme where custid ilike '%$query%' or oldcustid ilike '%$query%'";
+    $sql = "select $cols from searchme where custid ilike '%$query%' or oldcustid ilike '%$query%'";
     my $count = countRows($sql);
     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
@@ -302 +304 @@
     print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
     # Query based on description (includes "name" from old DB).
-    $sql = "select * from searchme where description ilike '%$query%'".
+    $sql = "select $cols from searchme where description ilike '%$query%'".
         " or custid ilike '%$query%'";
     my $count = countRows($sql);
@@ -320 +322 @@
         # /0->/9 are silly to worry about right now.  I don't think
         # we'll be getting a class A anytime soon.  <g>
-        $sql = "select * from searchme where cidr='$query'";
+        $sql = "select $cols from searchme where cidr='$query'";
         queryResults($sql, $webvar{page}, 1);
       } else {
         #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
         # Partial match;  beginning of subnet and maskbits are provided
-        $sql = "select * from searchme where text(cidr) like '$net%' and ".
+        $sql = "select $cols from searchme where text(cidr) like '$net%' and ".
                 "text(cidr) like '%$maskbits'";
         my $count = countRows($sql);
@@ -336 +338 @@
       my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
       my $sfor = new NetAddr::IP $query;
-      $sth = $ip_dbh->prepare("select * from searchme where text(cidr) like '$net%'");
+      $sth = $ip_dbh->prepare("select $cols from searchme where text(cidr) like '$net%'");
       $sth->execute;
       while (my @data = $sth->fetchrow_array()) {
         my $cidr = new NetAddr::IP $data[0];
         if ($cidr->contains($sfor)) {
-          queryResults("select * from searchme where cidr='$cidr'", $webvar{page}, 1);
+          queryResults("select $cols from searchme where cidr='$cidr'", $webvar{page}, 1);
         }
       }
     } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
       #print "Finding matches with leading octet(s) $query<br>\n";
-      $sql = "select * from searchme where text(cidr) like '$query%'";
+      $sql = "select $cols from searchme where text(cidr) like '$query%'";
       my $count = countRows($sql);
       $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";

trunk/cgi-bin/snCalc.cgi

@@ -3 +3 @@
 use strict;             
 use warnings;   
+use CGI::Carp qw(fatalsToBrowser);
+use NetAddr::IP;
+use CommonWeb qw(:ALL);;
 
 #file snCalc.cgi        little subnet calculator app
@@ -11 +14 @@
 print "Content-Type: text/html\n\n";
 
-open(HTML, "../startsn.html")|| die "Could not open starsn.html :$!";
+open(HTML, "../startsn.html")|| die "Could not open startsn.html :$!";
 my $start = join('', <HTML>);
 close(HTML);
 print $start;
 
-if($webvar{input} =~ m/(\/\d\d)/)
-{
-        $input = $1;
-}
-else
-{
-        $input = '/29';
+# Clean up input so we don't divide by zero or something equally silly
+if ($webvar{input} =~ m/(\d+)/) {
+  $input = 1*$1;
+  $input = 3 if $input < 3;
+  $input = 29 if $input > 29;   # Not doing IPv6 yet...
+} else {
+  $input = 29;
 }
 
-if(substr($input, 1, 2) > 32 || substr($input, 1, 2) < 24)
-{
-        printAndExit("'$input' is an invalid netmask.");
-}
-else
-{               $input = substr($input, 1, 2);
-                my $ltinput = $input -1;
-                my $gtinput = $input +1;
-                
-                print qq(       <div class="center">
-                <table align="center" cellspacing="3" cellpadding="3"><tr>
-                <td class="heading" align="center"> Results for /$ltinput</td><td class="heading" align="center"> Results for /$input</td><td class="heading" align="center"> Results for /$gtinput</td></tr>
-                );      
+my $ltinput = $input - 1;
+my $gtinput = $input + 1;
 
-                if( $input =~ m|.*24.*|){
-                        $input = 256;
-                        $ltinput =128;
-                        undef($gtinput);
-                }
-                elsif( $input =~ m|.*25.*|){
-                        $input = 128;
-                        $ltinput = 64;
-                        $gtinput = 256;
-                }
-                elsif( $input =~ m|.*26.*|){
-                        $input = 64;
-                        $ltinput = 32;
-                        $gtinput = 128;
-                }
-                elsif( $input =~ m|.*27.*|){
-                        $input = 32;
-                        $ltinput = 16;
-                        $gtinput = 64;
-                }
-                elsif( $input =~ m|.*28.*|){
-                        $input = 16;
-                        $ltinput = 8;
-                        $gtinput = 32;
-                }
-                elsif( $input =~ m|.*29.*|){
-                        $input = 8;
-                        $ltinput = 4;
-                        $gtinput = 16;
-                }
-                elsif( $input =~ m|.*30.*|){
-                        $input = 4;
-                        $ltinput = 2;
-                        $gtinput = 8;
-                }
-                elsif( $input =~ m|.*31.*|){
-                        $input = 2;
-                        $ltinput = 1;
-                        $gtinput = 4;
-                }
-                elsif( $input =~ m|.*32.*|){
-                        $input = 1;
-                        $gtinput = 2;
-                        undef($ltinput);
-                }
+my $prenet = new NetAddr::IP "0.0.0.0/$ltinput";
+my $net = new NetAddr::IP "0.0.0.0/$input";
+my $postnet = new NetAddr::IP "0.0.0.0/$gtinput";
 
-                my ($center, $left, $right) = ('','','');
-                my $subnet;
-                
-                #add the subnet masks
-                
-                if($input) 
-                {               
-                  $subnet = 256 - $input;
-                  $center = qq(<div style="background-color: #00ff00">255.255.255.$subnet </div>);
-                }
+print qq(<div class="center">
+<table align="center" cellspacing="3" cellpadding="3">
+<tr>
+        <td class="heading" align="center">Results for /$ltinput</td>
+        <td class="heading" align="center">Results for /$input</td>
+        <td class="heading" align="center">Results for /$gtinput</td>
+</tr>
+);
 
-                if($ltinput)
-                {
-                  $subnet = 256 - $ltinput;
-                  $right = qq(<div style="background-color: #00ff00">255.255.255.$subnet</div>);
-                }
-                if($gtinput)
-                {
-                  $subnet = 256 - $gtinput;
-                  $left = qq(<div style="background-color: #00ff00">255.255.255.$subnet</div>);
-                }
+print qq(<tr><td valign="top">\n).
+        qq(     <div class="mask">).$prenet->mask."</div>\n".
+        qq(     <div class="wildcard">).$prenet->wildcard."</div>\n".
+        getranges($ltinput).
+        qq(</td>\n<td valign="top" bgcolor="#d0e0e0">\n).
+        qq(     <div class="mask">).$net->mask."</div>\n".
+        qq(     <div class="wildcard">).$net->wildcard."</div>\n".
+        getranges($input).
+        qq(</td>\n<td valign="top">).
+        qq(     <div class="mask">).$postnet->mask."</div>\n".
+        qq(     <div class="wildcard">).$postnet->wildcard."</div>\n".
+        getranges($gtinput);
+
+print "</td></tr>\n</table>\n";
+
+print qq(<input type="button" value="Back" onclick="history.go(-1)" class="heading">
+</div>
+</body>
+</html>
+);
+        
+# Just In Case
+exit 0;
+
+# subs
+sub xrange {
+  my $block = shift;
+  my $masklen = shift;
+  my $data = $block->range;
+  if ($masklen >= 24) {
+    $data =~ s/\b0\.0\.0\./x.x.x./g;
+  } elsif ($masklen >=16) {
+    $data =~ s/\b0\.0\.(\d+\.\d+)/x.x.$1/g;
+  } elsif ($masklen >=8) {
+    $data =~ s/\b0\.(\d+\.\d+\.\d+)/x.$1/g;
+  }
+  return $data;
+} # xrange()
 
 
-                for(my $i = 0; $i < 256; $i++)
-                {
-                        #left display -- one less than requested
-                        if(defined($gtinput) && $i % $gtinput == 0)
-                        {
-                                my $upper = $i + $gtinput -1;
-                                $left .= "x.x.x.$i - x.x.x.$upper</br>\n";
-                        }
-
-                        #center display -- the one requested
-                        if($i % $input == 0 )
-                        {
-                                my $upper = $i + $input - 1;
-                                $center .= "x.x.x.$i - x.x.x.$upper&nbsp;&nbsp;</br>\n";
-                        }
-
-                        #right display -- one more than requested
-                        if(defined($ltinput) && $i % $ltinput == 0)
-                        {
-                                my $upper = $i + $ltinput -1;
-                                $right .= "x.x.x.$i - x.x.x.$upper&nbsp;&nbsp;</br>\n";
-                        }
-                }
-                
-                print qq(<tr><td valign="top" >$left</td>
-                                <td valign="top" bgcolor="#d0e0e0">$center</td>
-                                <td valign="top" >$right</td></tr>
-                                );
-                print "</table>\n";
-
-                print '<input type="button" value="Back" onclick="history.go(-1)" class="heading"></div></body></html>';
-        
-}
-
-
-sub parse_post {
-  my $buffer;
-  if ($ENV{'REQUEST_METHOD'} eq "GET") {
-    $buffer=$ENV{'QUERY_STRING'}
-  } elsif ($ENV{'REQUEST_METHOD'} eq 'POST' && $ENV{'CONTENT_TYPE'} eq "application/x-www-form-urlencoded") {
-    read(STDIN, $buffer, $ENV{CONTENT_LENGTH});
+sub getranges {
+  my $masklen = shift;
+  my $ret = '';
+  my $super;
+  if ($masklen < 8) {
+    $super = new NetAddr::IP "0.0.0.0/0";
+  } elsif ($masklen < 16) {
+    $super = new NetAddr::IP "0.0.0.0/8";
+  } elsif ($masklen < 24) {
+    $super = new NetAddr::IP "0.0.0.0/16";
   } else {
-    $buffer = $ENV{'QUERY_STRING'};
-    $buffer || read(STDIN, $buffer, $ENV{CONTENT_LENGTH});
+    $super = new NetAddr::IP "0.0.0.0/24";
   }
-  my @pairs = split(/&/, $buffer);
-  my %webvarLocal;
-  foreach my $pair (@pairs) {
-    my ($name, $value) = split(/=/, $pair);
-    $name  =~ tr/+/ /;
-    $name  =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
-    $value =~ tr/+/ /;
-    $value =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
-    $value =~ s/\'/\\\'/g;
-    $webvarLocal{$name} = $value;
+  foreach my $net ($super->split($masklen)) {
+    $ret .= "\t".xrange($net,$masklen)."<br />\n";
   }
-  return %webvarLocal;
-}
-sub printAndExit
-{
-        my $errStr = $_[0];
-        print qq(
-        <center><p class="regular"> $errStr </p>
-        <input type="button" value="Back" onclick="history.go(-1)" class="heading">
-        </center>
-        );
-        exit(0);
-}
+  return $ret;
+} # getranges()

trunk/help.html

@@ -50 +50 @@
 <td colspan="2">
 <form method="POST" action="cgi-bin/snCalc.cgi">
-<input type="text" size="5" maxlength="10" name="input" class="regular">
-<input type="submit" value="Calculate" class="heading">
+ / <input type="text" size="5" maxlength="10" name="input" class="regular">
+<input type="submit" value="Calculate" class="heading">&nbsp; Show
+<span class="mask">subnet mask</span>, <span class="wildcard">wildcard mask</span>, 
+and possible subnet ranges for the entered mask length.
 </form>
 </tr>

trunk/ipdb.css

@@ -81 +81 @@
         color: red;
 }
+
+.mask {
+        background: #00ff00;
+}
+
+.wildcard {
+        background: #ffff00;
+}