Changeset 680

Timestamp:

01/19/15 18:17:21 (9 years ago)

Author:

Kris Deugau

Message:

/trunk

Update db2rwhois for new database structure:

• new location for master blocks
• recheck ARIN policies for inclusion of netblock assignments in rWHOIS (v6 /64 should be included; was /56 at one point waaay back)
• file off another site-specific-ism that's built from the database and configuration

File:

• trunk/cgi-bin/extras/db2rwhois.pl (modified) (9 diffs)

trunk/cgi-bin/extras/db2rwhois.pl

@@ -61 +61 @@
 
 # Get the list of masters to export
-my $msth = $dbh->prepare("select cidr,ctime,mtime from masterblocks where rwhois='y'");
+my $msth = $dbh->prepare(q(
+        SELECT cidr, createstamp, modifystamp, id
+        FROM allocations
+        WHERE type='mm' AND swip='y'
+        ) );
 $msth->execute;
 
@@ -68 +72 @@
 # 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 family(cidr)=4) or (masklen(cidr) <=56 and family(cidr)=6)) and ".
-        "cidr <<= ?");
+my $ssth = $dbh->prepare(q(
+        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' OR type = 'mm') AND
+        ((masklen(cidr) <=30 AND family(cidr)=4) OR (masklen(cidr) <=64 AND family(cidr)=6)) AND
+        master_id = ? 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=?");
+my $custsth = $dbh->prepare(q(
+        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
@@ -86 +97 @@
 # all data every time, this isn't so terrible as it might seem.
 my $i=0;
-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";
+while (my ($master, $mcreate, $mmod, $mid) = $msth->fetchrow_array()) {
+
+  $masterblocks[$i] = new NetAddr::IP $master;
+  my ($ctime,undef) = split /\s/, $mcreate;
+  ($mmod,undef) = split /\s/, $mmod;
+
+  print "$masterblocks[$i] $ctime $mmod\n";
 
   my $date = strftime("%Y-%m-%d", localtime);
@@ -155 +166 @@
     print "  Creating org data...\n";
     open ORGDATAFILE, ">$netdatadir/data/org/ourorg.txt";
-    print ORGDATAFILE qq(ID: NETBLK-ISP.$masterblocks[$i]
+    print ORGDATAFILE qq(ID: NETBLK-$netnameprefix{mm}.$masterblocks[$i]
 Auth-Area: $masterblocks[$i]
 Org-Name: $IPDB::org_name
@@ -188 +199 @@
   open MASTERFILE,">$IPDB::rwhoisDataPath/$masterfilename";
 
-  print MASTERFILE "ID: NETBLK-ISP.$masterblocks[$i]\n".
+  print MASTERFILE "ID: NETBLK-$netnameprefix{mm}.$masterblocks[$i]\n".
         "Auth-Area: $masterblocks[$i]\n".
-        "Network-Name: ISP-".$masterblocks[$i]->network."\n".
+        "Network-Name: $netnameprefix{mm}-".$masterblocks[$i]->network."\n".
         "IP-Network: $masterblocks[$i]\n".
         "IP-Network-Block: ".$masterblocks[$i]->range."\n".
@@ -201 +212 @@
         "Tech-Contact: $IPDB::org_techhandle\n".
         "Created: $ctime\n".
-        "Updated: $mtime\n".
+        "Updated: $mmod\n".
         "Updated-By: $IPDB::org_email\n";
 
   # And now the subblocks
-  $ssth->execute("$masterblocks[$i]");
+  $ssth->execute($mid, $master) or die "nosubs: $!\n".$dbh->errstr."\n";
   while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $ssth->fetchrow_array) {
 
 # We get master block info from @masterblocks.
- # ID: NETBLK-ISP.10.0.0.0/8
+ # ID: NETBLK-$netnameprefix{mm}.10.0.0.0/8
  # Auth-Area: 10.0.0.0/8
- # Network-Name: ISP-10.0.2.144
+ # Network-Name: $netnameprefix{$type}-10.0.2.144
  # IP-Network: 10.0.2.144.144/29
  # IP-Network-Block: 10.0.2.144 - 10.0.2.151
@@ -229 +240 @@
 # cidr custid type city description notes maskbits
 
-# Fill in a generic entry for nameless allocations
-if ($desc =~ /^\s*$/) { $desc = $IPDB::org_name; }
+    # Fill in a generic entry for nameless allocations
+    if ($desc =~ /^\s*$/) { $desc = $IPDB::org_name; }
 
     # Fix up datestamps.  We don't *really* need sub-microsecond resolution on our exports...
@@ -256 +267 @@
 
     if ($swip eq 'n') {
-      print MASTERFILE "---\nID: NETBLK-ISP.$masterblocks[$i]\n".
+      print MASTERFILE "---\nID: NETBLK-$netnameprefix{mm}.$masterblocks[$i]\n".
         "Auth-Area: $masterblocks[$i]\n".
         "Network-Name: $netname-".$net->network."\n".
@@ -280 +291 @@
         $netname .= "-".$net->network;
       }
-      print MASTERFILE "---\nID: NETBLK-ISP.$masterblocks[$i]\n".
+      print MASTERFILE "---\nID: NETBLK-$netnameprefix{mm}.$masterblocks[$i]\n".
         "Auth-Area: $masterblocks[$i]\n".
         "Network-Name: $netname\n".