source: trunk/dnsbl/setparents.pl@ 32

#!/usr/bin/perl
# hack to set new parent column on blocks and iplist tables

use strict;
use warnings;
use DBI;

use DNSBL;

my $dnsbl = new DNSBL;

# default DB info - all other settings should be loaded from the DB.
my $dbhost = "dbhost";
my $dbname = "dnsbl";
my $dbuser = "dnsbl";
my $dbpass = "spambgone";

die "Need config argument\n" if !$ARGV[0];
my $cfgname = shift @ARGV;

# Load a config ref containing DB host, name, user, and pass info based on
# from the server name + full script web path.  This allows us to host
# multiple instances without having to duplicate the code.
# This file is a Perl fragment to be processed inline.
if (-e "/etc/dnsbl/$cfgname.conf") {
  my $cfg = `cat /etc/dnsbl/$cfgname.conf`;
  ($cfg) = ($cfg =~ /^(.+)$/s);         # avoid warnings, failures, and general nastiness with taint mode
  eval $cfg;
}

my $dbh = $dnsbl->connect($dbhost, $dbname, $dbuser, $dbpass);

print "block parents\n";
my $sthplist = $dbh->prepare("SELECT block FROM blocks WHERE level=?");
my $sthclist = $dbh->prepare("UPDATE blocks SET parent=? WHERE level=? AND block << ?");
foreach my $level ((0,1)) {
  $sthplist->execute($level) or die "failed to get blocklist: ".$sthplist->errstr."\n";
  while (my ($block) = $sthplist->fetchrow_array) {
    $sthclist->execute($block,$level+1,$block) or die "failed setparent on $block subs: ".$sthclist->errstr."\n";
  }
}
print " done\n";

print "block counts\n";
my $sthblist = $dbh->prepare("SELECT block FROM blocks");
my $sthcount = $dbh->prepare("UPDATE blocks SET ipcount=(SELECT count(*) FROM iplist WHERE ip << ?) WHERE block = ?");
$sthblist->execute;
while (my ($block) = $sthblist->fetchrow_array) {
  $sthcount->execute($block,$block) or die "failed setcount on $block: ".$sthcount->errstr."\n";
}
print " done\n";

print "ip parents\n";
$sthplist = $dbh->prepare("SELECT block FROM blocks ORDER BY level");
$sthclist = $dbh->prepare("UPDATE iplist SET parent = ? WHERE ip << ?");
$sthplist->execute;
while (my ($block) = $sthplist->fetchrow_array) {
  $sthclist->execute($block,$block);
}

# and yes, we do this in one huge block.
$dbh->commit;
print " done\n";