Changeset 599

Timestamp:

03/21/14 16:44:13 (10 years ago)

Author:

Kris Deugau

Message:

/trunk

Prepare for changes to allow any record in either forward or reverse
zones, no matter how silly.

This commit adds a database function to catch errors with CASTing a
column as inet, so that any place we need to sort records based on the
IP value of a column succeeds by treating non-IP values as 0.0.0.0/0.

Location:

trunk

Files:

• DNSDB.pm (modified) (3 diffs)
• dns-1.2.3-1.2.4.sql (added)
• dns.sql (modified) (2 diffs)

trunk/DNSDB.pm

@@ -3667 +3667 @@
   foreach my $sf (split /,/, $args{sortby}) {
     $sf = "r.$sf";
-    $sf =~ s/r\.val/CAST (r.val AS inet)/
+    $sf =~ s/r\.val/inetlazy(r.val)/
         if $args{revrec} eq 'y' && $args{defrec} eq 'n';
     $sf =~ s/r\.type/t.alphaorder/;
@@ -4341 +4341 @@
 ##fixme?  may need to narrow things down more by octet-chopping and doing text comparisons before casting.
   my ($revpatt) = $dbh->selectrow_array("SELECT host FROM records ".
-        "WHERE (type in (12,65280,65281,65282,65283,65284)) AND rdns_id = ? AND CAST (val AS inet) >>= ? ".
-        "ORDER BY CAST (val AS inet) DESC LIMIT 1", undef, ($revid, $cidr) );
+        "WHERE (type in (12,65280,65281,65282,65283,65284)) AND rdns_id = ? AND inetlazy(val) >>= ? ".
+        "ORDER BY inetlazy(val) DESC LIMIT 1", undef, ($revid, $cidr) );
   return $revpatt;
 } # end getRevPattern()
@@ -5164 +5164 @@
   my $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id,location,extract(epoch from stamp),expires,stampactive ".
         "FROM records WHERE rdns_id=? AND NOT type=6 ".
-        "ORDER BY masklen(CAST(val AS inet)) DESC, CAST(val AS inet)");
+        "ORDER BY masklen(inetlazy(val)) DESC, inetlazy(val)");
   my $revsth = $dbh->prepare("SELECT rdns_id,revnet,status,changed FROM revzones WHERE status=1 ".
         "ORDER BY masklen(revnet) DESC, rdns_id");

trunk/dns.sql

@@ -6 +6 @@
 -- SET SESSION AUTHORIZATION 'dnsdb';
 
+-- pre-pg8.3, this must be run as a superuser
+CREATE LANGUAGE plpgsql;
+-- it's required for:
+
+-- Return proper conversion of string to inet, or 0.0.0.0/0 if the string is
+-- not a valid inet value.  We need to do this to support "funky" records that
+-- may not actually have valid IP address values.  Used for ORDER BY
+CREATE OR REPLACE FUNCTION inetlazy (rdata text) RETURNS inet AS $$
+BEGIN
+        RETURN CAST(rdata AS inet);
+EXCEPTION
+        WHEN OTHERS THEN
+                RETURN CAST('0.0.0.0/0' AS inet);
+END;
+$$ LANGUAGE plpgsql;
+
+
 -- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
 CREATE TABLE misc (
@@ -14 +31 @@
 
 COPY misc (misc_id, key, value) FROM stdin;
-1       dbversion       1.2
+1       dbversion       1.2.4
 \.