Changeset 324 for trunk/cgi-bin

Timestamp:

04/06/06 15:47:21 (19 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

Files:

• extras/db2rwhois.pl (modified) (8 diffs)
• extras/network.tmpl (added)
• extras/rwhois-config (added)
• extras/rwhois-net-skel.tar.gz (added)
• ipdb.psql (modified) (4 diffs)

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
 
 

trunk/cgi-bin/ipdb.psql

@@ -10 +10 @@
         "city" character varying(30),
         "province" character(2),
+        "country" character(2),
         "pocode" character varying(7),
         "phone" character varying(15),
-        "abuse" character varying(50),
+        "tech_handle" character varying(50),
+        "abuse_handle" character varying(50),
+        "admin_handle" character varying(50),
         "def_rdns" character varying(40),
-        "description" text,
+        "special" text,
         Constraint "customers_pkey" Primary Key ("custid")
 );
@@ -23 +26 @@
 CREATE TABLE "masterblocks" (
         "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
-        "ctime" timestamp DEFAULT now()
+        "ctime" timestamp DEFAULT now(),
+        "mtime" timestamp DEFAULT now()
 );
 
@@ -106 +110 @@
         "dispname" character varying(40) DEFAULT '',
         "listorder" integer DEFAULT 0,
-        "def_custid" character varying(16) DEFAULT ''
+        "def_custid" character varying(16) DEFAULT '',
+        "arin_netname" character varying(20) DEFAILT 'ISP'
 );
 
@@ -113 +118 @@
 --
 
-COPY alloctypes ("type", listname, dispname, listorder, def_custid) FROM stdin;
-cn      Customer netblock       Customer netblock       0       
-si      Static IP - Server pool Server pool IP  20      
-ci      Static IP - Cable       Static cable IP 21      
-di      Static IP - DSL Static DSL IP   22      
-mi      Static IP - Dialup      Static dialup IP        23      
-wi      Static IP - Wireless    Static wireless IP      24      
-sd      Static Pool - Servers   Server pool     40      6750400
-cd      Static Pool - Cable     Cable pool      41      CBL-BUS
-dp      Static Pool - DSL       DSL pool        42      DSL-BUS
-mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS
-wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS
-en      End-use netblock        End-use netblock        100     6750400
-me      Dialup netblock Dialup netblock 101     DIAL-RES
-de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES
-ce      Dynamic cable block     Dynamic cable block     103     CBL-RES
-we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES
-ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP
-li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400
-ai      Static IP - Managment   Static management IP    192     6750400
-ad      Static Pool - Managment Management pool 196     6750400
-ld      Static Pool - LAN/POP   LAN pool        195     6750400
-wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400
-pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400
-ac      Reserve for ATM ATM blocks      202     6750400
-wr      CORE/WAN block  CORE/WAN block  220     6750400
-pr      Dynamic-route DSL netblock      Dynamic-route DSL       221     
-ar      ATM block       ATM block       222     
-rm      Routing Routed netblock 500     6750400
-in      Internal netblock       Internal netblock       990     6750400
-mm      Master block    Master block    999     6750400
+COPY "alloctypes" FROM stdin;
+cn      Customer netblock       Customer netblock       0               ISPCUST
+si      Static IP - Server pool Server pool IP  20      6750400 ISP
+ci      Static IP - Cable       Static cable IP 21              ISP
+di      Static IP - DSL Static DSL IP   22              ISP
+mi      Static IP - Dialup      Static dialup IP        23              ISP
+wi      Static IP - Wireless    Static wireless IP      24              ISP
+sd      Static Pool - Servers   Server pool     40      6750400 ISP
+cd      Static Pool - Cable     Cable pool      41      CBL-BUS ISP-STATIC-CABLE
+dp      Static Pool - DSL       DSL pool        42      DSL-BUS ISP-STATIC-DSL
+mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS        ISP-STATIC-DIAL
+wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS  ISP-STATIC-WIFI
+en      End-use netblock        End-use netblock        100     6750400 ISP
+me      Dialup netblock Dialup netblock 101     DIAL-RES        ISP-DIAL
+de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES ISP-DSL
+ce      Dynamic cable block     Dynamic cable block     103     CBL-RES ISP-CABLE
+we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES  ISP-WIFI
+ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP        ISP
+li      Static IP - LAN/POP     Static LAN/POP IP       190     6750400 ISP
+ld      Static Pool - LAN/POP   LAN pool        191     6750400 ISP
+wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400 ISP
+pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400 ISP-STATIC-DSL
+ac      Reserve for ATM ATM blocks      202     6750400 ISP
+wr      CORE/WAN block  CORE/WAN block  220     6750400 ISP
+pr      Dynamic-route DSL netblock      Dynamic-route DSL       221             ISP
+ar      ATM block       ATM block       222             ISP
+rm      Routing Routed netblock 500     6750400 ISP
+in      Internal netblock       Internal netblock       990     6750400 ISP
+mm      Master block    Master block    999     6750400 ISP
 \.