Changeset 324 for trunk/cgi-bin/extras

Timestamp:

04/06/06 15:47:21 (18 years ago)

Author:

Kris Deugau

Message:

/trunk

Refactor rWHOIS export for easier rWHOIS data access.
Update SQL tabledefs (customers table, alloctypes table) and

predefined alloctypes list with database changes.

Add rWHOIS configuration files and notes

Location:

trunk/cgi-bin/extras

Files:

• db2rwhois.pl (modified) (8 diffs)
• network.tmpl (added)
• rwhois-config (added)
• rwhois-net-skel.tar.gz (added)

trunk/cgi-bin/extras/db2rwhois.pl

@@ -28 +28 @@
 
 my @masterblocks;
+my %netnameprefix;
 
 # Fill in data about our master blocks as allocated from ARIN
@@ -33 +34 @@
 # 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 from masterblocks;");
+my $sth = $dbh->prepare("select cidr,ctime,mtime from masterblocks;");
 $sth->execute;
 my $i=0;
@@ -45 +46 @@
   $masterblocks[$i] = new NetAddr::IP $data[0];
   my ($ctime,undef) = split /\s/, $data[1];
-
-print "$masterblocks[$i] $data[1]\n";
+  my ($mtime,undef) = split /\s/, $data[2];
+print "$masterblocks[$i] $ctime $mtime\n";
 
   my $date;
@@ -64 +65 @@
         "IP-Network: $masterblocks[$i]\n".
         "IP-Network-Block: ".$masterblocks[$i]->range."\n".
-        "Organization: Friendly ISP\n".
-        "Tech-Contact: noc\@example.com\n".
-        "Admin-Contact: ISP-ARIN-HANDLE\n".
-        "Abuse-Contact: abuse\@example.com\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: $date\n".
+        "Updated: $mtime\n".
         "Updated-By: noc\@example.com\n";
 
@@ -76 +80 @@
 }
 
+# prefetch alloctype data
+$sth = $dbh->prepare("select type,def_custid,arin_netname from alloctypes where listorder <500");
+$sth->execute;
+while (my @data = $sth->fetchrow_array) {
+  $netnameprefix{$data[0]} = $data[2];
+}
+
 # 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
@@ -83 +94 @@
 # 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 ".
+$sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp,swip ".
         "from allocations where ".
         "not (cidr <<= '192.168.0.0/16') and ".
@@ -93 +104 @@
 $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) = $sth->fetchrow_array) {
+while (my ($cidr, $custid, $type, $city, $desc, $ctime, $mtime, $swip) = $sth->fetchrow_array) {
 
 # We get master block info from @masterblocks.
@@ -134 +147 @@
       ($mtime) = ($mtime =~ /^(\d+-\d+-\d+)\s+/);
 
-      print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
-        "Auth-Area: $master\n".
-        "Network-Name: ISP-".$net->network."\n".
-        "IP-Network: $net\n".
-        "IP-Network-Block: ".$net->range."\n".
-        "Organization: $desc\n".
-#       "Tech-Contact: $data[9]\n".
-        "Tech-Contact: abuse\@example.com\n".
-        "Admin-Contact: ISP-ARIN-HANDLE\n".
-        "Created: $ctime\n".
-        "Updated: $mtime\n".
-        "Updated-By: noc\@example.com\n";
-    }
-  }
+# Notes:
+# Network-name should contain some component of "description"
+# Cust address/contact data should be included;  NB, no phone for ARIN!
+#  network:ID: NET-WIDGET
+#  network:Network-Name: WIDGET                 [IPDB description, sort of]
+#  network:IP-Network: 10.1.1.0/24
+#  network:Org-Name: Widget Corp                [Cust name;  from billing?]
+#  network:Street-Address: 211 Oak Drive        [May need more than one line, OR...]
+#  network:City: Pineville                      [...this line...]
+#  network:StateProv: WI                        [...and this line...]
+#  network:Postal-Code: 48888                   [...and this line]
+#  network:Country-Code: US
+#  network:Tech-Contact: BZ142-MYRWHOIS         [ARIN handle?]
+#  network:Updated: 19991221                    [timestamp from db]
+#  network:Updated-By: jo@myrwhois.net          [noc@example, since that's our POC for IP netspace issues]
+#  network:Class-Name:network                   [Provided by rWHOIS protocol]
+
+      my $netname = $netnameprefix{$type};
+
+      if ($swip eq 'n') {
+        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";
+      } 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 {
+          $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";
+      } # swip
+
+    } # net in master
+  } # foreach master