Changeset 32

Timestamp:

03/25/11 17:16:38 (14 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

Location:

trunk/dnsbl

Files:

• DNSBL.pm (modified) (8 diffs)
• Makefile (added)
• addparents.sql (added)
• browse.cgi (modified) (1 diff)
• delist-ip (added)
• dnsbl.cgi (modified) (2 diffs)
• dnsbl.sql (modified) (8 diffs)
• export-dnsbl (modified) (1 diff)
• setparents.pl (added)

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;

trunk/dnsbl/browse.cgi

@@ -54 +54 @@
 my $basesql = "SELECT b.block,o.orgname,b.listme,o.listme,b.comments,o.comments ".
         "FROM blocks b INNER JOIN orgs o ON b.orgid=o.orgid ".
-        "WHERE b.block <<= ";
+        "WHERE b.parent = ";
 my $sth0 = $dbh->prepare($basesql."'0/0' AND b.level=0 ORDER BY block");
 #my $sth0 = $dbh->prepare($basesql."'64/8' AND b.level=0 ORDER BY block");
 my $sth1 = $dbh->prepare($basesql."? AND b.level=1 ORDER BY block");
 my $sth2 = $dbh->prepare($basesql."? AND b.level=2 ORDER BY block");
-my $sthiplist = $dbh->prepare("select * from iplist where ip <<= ? order by ip");
+my $sthiplist = $dbh->prepare("select * from iplist where parent = ? order by ip");
 
 my %ipseen;

trunk/dnsbl/dnsbl.cgi

@@ -96 +96 @@
 
   my $err = '';
-  my $count = $dnsbl->report($webvar{ip});
   my $org0id = $dnsbl->orgexists($webvar{org0});
   if (!$org0id) {
@@ -132 +131 @@
     }
   }
+  my $count = $dnsbl->report($webvar{ip});
 
   $page->param(ip => $webvar{ip});

trunk/dnsbl/dnsbl.sql

@@ -5 +5 @@
 SET client_encoding = 'UTF8';
 SET check_function_bodies = false;
+SET client_min_messages = warning;
 
 --
--- TOC entry 7 (OID 196077)
 -- Name: blocks; Type: TABLE; Schema: public; Owner: dnsbl
 --
@@ -16 +16 @@
     orgid integer,
     "level" integer DEFAULT 0,
-    comments text DEFAULT ''::text
+    comments text DEFAULT ''::text,
+    parent cidr NOT NULL,
+    ipcount integer NOT NULL
 );
 
 
 --
--- TOC entry 8 (OID 196085)
 -- Name: iplist; Type: TABLE; Schema: public; Owner: dnsbl
 --
@@ -29 +30 @@
     count integer DEFAULT 1,
     s4list boolean DEFAULT false,
-    added timestamp with time zone DEFAULT now()
+    added timestamp with time zone DEFAULT now(),
+    parent cidr NOT NULL
 );
 
 
 --
--- TOC entry 9 (OID 196090)
 -- Name: orgs; Type: TABLE; Schema: public; Owner: dnsbl
 --
@@ -47 +48 @@
 
 --
--- TOC entry 10 (OID 196099)
 -- Name: autolist; Type: TABLE; Schema: public; Owner: dnsbl
 --
@@ -58 +58 @@
 
 --
--- TOC entry 11 (OID 196147)
 -- Name: misc; Type: TABLE; Schema: public; Owner: dnsbl
 --
@@ -70 +69 @@
 -- for tracking IPs that get removed from listing
 -- note we don't want a PK on IP as with iplist, since we may end up removing an IP more than once.  :/
+--
+-- Name: waslisted; Type: TABLE; Schema: public; Owner: dnsbl
+--
 
 CREATE TABLE waslisted (
@@ -79 +81 @@
 );
 
+
 --
--- Data for TOC entry 23 (OID 196099)
+-- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
+--
+
+ALTER TABLE ONLY autolist
+    ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
+
+
+--
+-- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
+--
+
+ALTER TABLE ONLY blocks
+    ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
+
+
+--
+-- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
+--
+
+ALTER TABLE ONLY iplist
+    ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
+
+
+--
+-- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
+--
+
+ALTER TABLE ONLY orgs
+    ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
+
+
+--
+-- Name: blocks_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
+--
+
+CREATE INDEX blocks_parent_index ON blocks USING btree (parent);
+
+
+--
+-- Name: ip_parent_index; Type: INDEX; Schema: public; Owner: dnsbl
+--
+
+CREATE INDEX ip_parent_index ON iplist USING btree (parent);
+
+
+--
+-- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
+--
+
+ALTER TABLE ONLY blocks
+    ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
+
+
+--
 -- Name: autolist; Type: TABLE DATA; Schema: public; Owner: dnsbl
 --
@@ -118 +174 @@
 0       2147483648
 \.
-
-
---
--- TOC entry 12 (OID 196101)
--- Name: blocks_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
---
-
-ALTER TABLE ONLY blocks
-    ADD CONSTRAINT blocks_pkey PRIMARY KEY (block);
-
-
---
--- TOC entry 13 (OID 196103)
--- Name: iplist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
---
-
-ALTER TABLE ONLY iplist
-    ADD CONSTRAINT iplist_pkey PRIMARY KEY (ip);
-
-
---
--- TOC entry 14 (OID 196105)
--- Name: orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
---
-
-ALTER TABLE ONLY orgs
-    ADD CONSTRAINT orgs_pkey PRIMARY KEY (orgid);
-
-
---
--- TOC entry 15 (OID 196215)
--- Name: autolist_pkey; Type: CONSTRAINT; Schema: public; Owner: dnsbl
---
-
-ALTER TABLE ONLY autolist
-    ADD CONSTRAINT autolist_pkey PRIMARY KEY (masklen);
-
-
---
--- TOC entry 25 (OID 196107)
--- Name: blocks_orgid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dnsbl
---
-
-ALTER TABLE ONLY blocks
-    ADD CONSTRAINT blocks_orgid_fkey FOREIGN KEY (orgid) REFERENCES orgs(orgid);
-

trunk/dnsbl/export-dnsbl

@@ -47 +47 @@
 my $mode = $ARGV[0] || 'tiny';
 
-#$dnsbl->export($ipref,$mode,1,'65.60/18');
-#$dnsbl->export($ipref,$mode,1,'67.136.0.0/14');
-#$dnsbl->export($ipref,$mode,1,'83.76/15');
-#$dnsbl->export($ipref,$mode,1,'76.73.0.0/17');
-#$dnsbl->export($ipref,$mode,1,'174.36.0.0/15');
 $dnsbl->initexport;
+#$dnsbl->export($ipref,$mode,1,'50.22.0.0/15');
 $dnsbl->export($ipref,$mode);