Changeset 32 for trunk/dnsbl/DNSBL.pm

Timestamp:

03/25/11 17:16:38 (13 years ago)

Author:

Kris Deugau

Message:

/trunk/dnsbl

Changeset to improve export speed on DNSBL database. By keeping
IP counts for each block, and directly tracking the parent of
each block and IP, an export taking ~90 seconds can be brought
down under 20.

• updated initial tabledef SQL
• Add addparents.sql SQL tabeldef and setparents.pl data update script
• Tweak reporting of IP since it now requires the parent block(s) so the parent field can be filled in

Also add delist-ip script and Makefile

File:

• trunk/dnsbl/DNSBL.pm (modified) (8 diffs)

trunk/dnsbl/DNSBL.pm

@@ -138 +138 @@
 sub initexport {
   $parsth = $dbh->prepare("SELECT count(i.ip),b.block,b.level,b.listme AS oobblock,o.listme AS ooborg ".
-        "FROM iplist i INNER JOIN blocks b ON i.ip << b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
+        "FROM iplist i INNER JOIN blocks b ON i.parent = b.block INNER JOIN orgs o ON b.orgid = o.orgid ".
         "WHERE b.block >>= ? ".
         "GROUP BY b.block,b.level,b.listme,o.listme ORDER BY b.block");
-  $sthmoron = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE ip << ? ORDER BY ip");
+  $sthmoron = $dbh->prepare("SELECT ip,s4list FROM iplist WHERE parent = ?");
 }
 
@@ -166 +166 @@
   if ($rep =~ /^[\d.]+$/) {
     # weesa gonna ASS-U-ME IP addresses are sanely formatted.
-    $sth = $dbh->prepare("SELECT count FROM iplist WHERE ip=?");
-    $sth->execute($rep) or die "eep?  ".$dbh->errstr."\n";
-    $rows = $sth->rows;
-    if ($rows == 0) {
-      $sth = $dbh->prepare("INSERT INTO iplist (ip) VALUES (?)");
-    } elsif ($rows == 1) {
-      $sth = $dbh->prepare("UPDATE iplist SET count=count+1 WHERE ip=?");
-    } else {
-      die "db corrupt: found $rows matches on $rep\n";
+    eval {
+      $sth = $dbh->prepare("SELECT count FROM iplist WHERE ip=?");
+      $sth->execute($rep) or die "eep?  ".$dbh->errstr."\n";
+      $rows = $sth->rows;
+      if ($rows == 0) {
+        $sth = $dbh->prepare("INSERT INTO iplist (ip,parent) VALUES ".
+                "(?,(SELECT block FROM blocks WHERE block >> ? ORDER BY level DESC LIMIT 1))");
+        $sth->execute($rep,$rep) or die "couldn't add entry for $rep: ".$dbh->errstr."\n";
+      } elsif ($rows == 1) {
+        $sth = $dbh->prepare("UPDATE iplist SET count=count+1 WHERE ip=?");
+        $sth->execute($rep) or die "couldn't update listing for $rep: ".$dbh->errstr."\n";
+      } else {
+        die "db corrupt: found $rows matches on $rep\n";
+      }
+      $sth = $dbh->prepare("SELECT block FROM blocks WHERE block >> ?");
+      $sth->execute($rep);
+      my $updsth = $dbh->prepare("UPDATE blocks SET ipcount=(SELECT count(*) FROM iplist WHERE ip << ?) WHERE block=?");
+      while (my ($block) = $sth->fetchrow_array) {
+        $updsth->execute($block,$block);
+      }
+      $dbh->commit;
+    };
+    if ($@) {
+      my $msg = $@;
+      return "failed adding $rep: $msg";
     }
-    $sth->execute($rep) or die "couldn't update listing for $rep: ".$dbh->errstr."\n";
   } else {
     return;
   }
-  $dbh->commit;
   return $rows;
 } # end report()
@@ -228 +242 @@
   return "$blockin not a single CIDR range" if !$block;
 
-  local $dbh->{AutoCommit} = 1; # force autocommit
-
-  my $sth = $dbh->prepare("INSERT INTO blocks (block,orgid,level) VALUES (?,?,?)");
-  $sth->execute("$block",$orgid,$level);
-  return $sth->errstr if $sth->err;
+#  local $dbh->{AutoCommit} = 1;        # force autocommit
+
+  my $sth;
+  eval {
+    my $parent = '0/0';
+    if ($level > 0) {
+      $sth = $dbh->prepare("SELECT block FROM blocks WHERE block >> ? ORDER BY level DESC LIMIT 1");
+      $sth->execute("$block");
+      ($parent) = $sth->fetchrow_array;
+    }
+    $sth = $dbh->prepare("INSERT INTO blocks (block,orgid,level,parent,ipcount) VALUES (?,?,?,?,".
+        "(SELECT count(*) FROM iplist WHERE ip << ?))");
+    $sth->execute("$block",$orgid,$level,$parent,"$block");
+    $sth = $dbh->prepare("UPDATE iplist SET parent=? WHERE parent=? AND ip << ?");
+    $sth->execute("$block",$parent,"$block");
+    $dbh->commit;
+  };
+  if ($@) {
+    my $msg = $@;
+    eval { dbh->rollback; };
+    return "failed to add $block: $msg";
+  }
   # nb: no need to return anything, since the CIDR block is the key
 }
@@ -287 +318 @@
     return if !$block;
 
-    $sth = $dbh->prepare("SELECT count(*) FROM iplist WHERE ip << ?");
+    $sth = $dbh->prepare("SELECT ipcount FROM blocks WHERE block = ?");
     $sth->execute($entity);
     my ($bcount) = $sth->fetchrow_array;
@@ -343 +374 @@
   }
 
-  my $sth = $dbh->prepare("SELECT count(*) FROM blocks WHERE block << ?");
+  my $sth = $dbh->prepare("SELECT count(*) FROM blocks WHERE parent = ?");
   $sth->execute($container);
   my ($nblocks) = $sth->fetchrow_array();
@@ -356 +387 @@
   my $bcount;
   if ($container ne '0.0.0.0/0') {
-    $sth = $dbh->prepare("SELECT count(*) FROM iplist WHERE ip << ?");
-    $sth->execute($container);
-    ($bcount) = $sth->fetchrow_array();
-
-    $sth = $dbh->prepare("SELECT b.listme,o.listme ".
+    $sth = $dbh->prepare("SELECT b.ipcount,b.listme,o.listme ".
         "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
         "WHERE b.block = ?");
     $sth->execute($container);
-    ($listme,$listorg) = $sth->fetchrow_array();
+    ($bcount,$listme,$listorg) = $sth->fetchrow_array();
 
     $bitmask |= $bitfields{$level-1} if $bcount >= $autolist{$masklen};
@@ -374 +401 @@
   if ($nblocks > 0) {
     my $sthsubblocks = $dbh->prepare("SELECT block FROM blocks ".
-        "WHERE level=? and block << ? ORDER BY block, masklen(block) DESC");
+        "WHERE level = ? AND parent = ?");
     $sthsubblocks->execute($level, $container);
     while (my ($cidr) = $sthsubblocks->fetchrow_array()) {
@@ -490 +517 @@
 
   my $cidr = new NetAddr::IP "$block";
-  my $sth = $dbh->prepare("select count(*) from iplist where ip << ?");
+  my $sth = $dbh->prepare("SELECT ipcount FROM blocks WHERE block = ?");
   $sth->execute("$cidr");
   my ($count) = $sth->fetchrow_array;