Changeset 218 for trunk

Timestamp:

04/06/05 17:06:28 (20 years ago)

Author:

Kris Deugau

Message:

/trunk

Updated SQL tabledefs with timestamps for master block, routed

block, allocation, and static IP "creation" times; also
allocation and static IP modification times.

Updated db2rwhois.pl with timestamp support and a little code cleanup.

Location:

trunk/cgi-bin

Files:

• extras/db2rwhois.pl (modified) (9 diffs)
• ipdb.psql (modified) (3 diffs)

trunk/cgi-bin/extras/db2rwhois.pl

@@ -17 +17 @@
 use NetAddr::IP;
 use MyIPDB;
+use Digest::MD5 qw(md5_hex);
 
 $ENV{"PATH"} = "/bin;/usr/bin";
@@ -41 +42 @@
 # 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 * from masterblocks;");
+my $sth = $dbh->prepare("select cidr,ctime from masterblocks;");
 $sth->execute;
 my $i=0;
 GETMASTERS: while (my @data = $sth->fetchrow_array()) {
 
-  if ($data[0] =~ /^(192.168.0.0|172.16.0.0|10.0.0.0)/) {
-    next GETMASTERS;
-  }
+#  if ($data[0] =~ /^(192.168.0.0|172.16.0.0|10.0.0.0)/) {
+#    next GETMASTERS;
+#  }
   $masterblocks[$i] = new NetAddr::IP $data[0];
 
@@ -55 +56 @@
   my $date;
   chomp ($date = `/bin/date +"%Y%m%d"`);
+  my ($ctime,undef) = split /\s+/, $data[1];
 
 # Whew!  Ugly little varmint.
@@ -73 +75 @@
         "Admin-Contact: ISP-ARIN-HANDLE\n".
         "Abuse-Contact: abuse\@example.com\n".
-        "Created: 20040308\n".
+        "Created: $ctime\n".
         "Updated: $date\n".
         "Updated-By: noc\@example.com\n";
@@ -87 +89 @@
 # Make sure to remove the private netblocks from this.
 # No use or point in broadcasting our use of them.
-$sth = $dbh->prepare("select * 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 custid='6750400'");
+#$sth = $dbh->prepare("select * 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')");
+# cidr,custid,type,city,description,notes,maskbits,circuitid,createstamp,modifystamp,newcustid
+
+$sth = $dbh->prepare("select cidr,custid,type,city,description,createstamp,modifystamp from allocations");
 $sth->execute;
 
-while (my @data = $sth->fetchrow_array()) {
+while (my ($cidr,$custid,$type,$city,$description,$ctime,$mtime) = $sth->fetchrow_array()) {
 
 # We get master block info from @masterblocks.
@@ -105 +110 @@
  # Updated-By: noc@example.com
 
-  # Get the "full" network number
-  my $net = new NetAddr::IP $data[0];
+  # Get the "full" network number - just in case.
+  my $net = new NetAddr::IP $cidr;
 
 # Assumptions:  All data in ipdb is public
@@ -121 +126 @@
         or print "File open error: '$!' on '$rwhoisDataPath/$masterfilename'\n";
 
-# cidr custid type city description notes maskbits
-
-# Creation date in record to eventually be "correct";  near-term will
-# be master's creation date;  immediate is today's date.
-my $date;
-      chomp ($date = `/bin/date +"%Y%m%d"`);
 if ($data[4] =~ /^\s*$/) { $data[4] = 'Friendly ISP'; }
+print "$type-type $net for $custid, created $ctime, modified $mtime\n";
       print MASTERFILE "---\nID: NETBLK-ISP.$master\n".
         "Auth-Area: $master\n".
@@ -133 +133 @@
         "IP-Network: $net\n".
         "IP-Network-Block: ".$net->range."\n".
-        "Organization: $data[4]\n".
+        "Organization: $description\n".
 #       "Tech-Contact: $data[9]\n".
         "Tech-Contact: abuse\@example.com\n".
         "Admin-Contact: ISP-ARIN-HANDLE\n".
-        "Created: $date\n".
-        "Updated: $date\n".
+        "Created: $ctime\n".
+        "Updated: $mtime\n".
         "Updated-By: noc\@example.com\n";
     }
@@ -154 +154 @@
 
 $dbh->disconnect;
+
+# This doesn't work, with a vengeance.
+#print qx{ /usr/bin/rwhois_indexer -c /etc/rwhoisd/rwhoisd.conf -i -v -s txt };

trunk/cgi-bin/ipdb.psql

@@ -70 +70 @@
         "circuitid" character varying(128) DEFAULT '' NOT NULL,
         "newcustid" integer,
+        "createstamp" timestamp DEFAULT now(),
+        "modifystamp" timestamp DEFAULT now(),
         CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
 );
@@ -86 +88 @@
         "maskbits" integer DEFAULT 128,
         "circuitid" character varying(128) DEFAULT '',
+        "createstamp" timestamp DEFAULT now(),
+        "modifystamp" timestamp DEFAULT now(),
         "newcustid" integer
 );
@@ -182 +186 @@
 pr      Dynamic-route DSL netblock      Dynamic-route DSL       203     
 \.
+
+--
+-- Trigger and matching function to update modifystamp on allocations, poolips
+--
+CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
+    BEGIN
+        NEW.modifystamp := ''now'';
+        RETURN NEW;
+    END;
+' LANGUAGE 'plpgsql';
+
+CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
+    FOR EACH ROW EXECUTE PROCEDURE up_modtime();
+
+CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
+    FOR EACH ROW EXECUTE PROCEDURE up_modtime();