source: trunk/dnsbl/check-iplist.pl@ 2

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

use DNSBL;

my $dnsbl = new DNSBL;

my $dbh = $dnsbl->connect;

print "checking IP containment...\n";
my $sth = $dbh->prepare("select ip from iplist order by ip");
$sth->execute;
my $cksth = $dbh->prepare("select block from blocks where block >> ?");
while (my @data = $sth->fetchrow_array) {
  $cksth->execute($data[0]);
  print "$data[0] has no container\n" if $cksth->rows == 0;
}

print "checking level 2 blocks...\n";
$sth = $dbh->prepare("select block from blocks where level=2");
$sth->execute;
$cksth = $dbh->prepare("select block from blocks where block >> ? and level=1");
while (my @data = $sth->fetchrow_array) {
  $cksth->execute($data[0]);
  print "$data[0] has no container\n" if $cksth->rows == 0;
}

print "checking level 1 blocks...\n";
$sth = $dbh->prepare("select block from blocks where level=1");
$sth->execute;
$cksth = $dbh->prepare("select block from blocks where block >> ? and level=0");
while (my @data = $sth->fetchrow_array) {
  $cksth->execute($data[0]);
  print "$data[0] has no container\n" if $cksth->rows == 0;
}

print "checking for empty blocks\n";
$sth = $dbh->prepare("select block from blocks");
$sth->execute;
$cksth = $dbh->prepare("select ip from iplist where ip << ?");
while (my @data = $sth->fetchrow_array) {
  $cksth->execute($data[0]);
  print "$data[0] has no IPs\n" if $cksth->rows == 0;
}