#!/usr/bin/perl

# This is a periodic script. It is intended for use on a slave nameserver
# running PowerDNS. Its purpose is to go through all domains created on
# behalf of a supermaster nameserver, and expire any domains that haven't
# successfully been checked against their designated master server in a
# period of time longer than the expire field of the SOA record indicates.
#
# -- dpates / 17 Jan 2004 17:14

use strict;
use DBI;
use POSIX;

# Use this format for outputing the last-updated timestamp in a human-readable
# way.
my $timefmt = '%d %b %y %r';

# The information for connecting to the database server.
my $db_user = "pdns";
my $db_pass = "KOD44OeP";
my $db_dsn = "DBI:mysql:pdns_slaveonly";

# Connect to the database, and ensure that we succeeded.
my $dbh = DBI->connect($db_dsn, $db_user, $db_pass);
die ("Couldn't connect to database") unless defined $dbh;

# Set up statements that we're going to use in the script.
my $sth = $dbh->prepare("SELECT d.id, d.name, d.last_check, SUBSTRING_INDEX(SUBSTRING_INDEX(r.content, ' ', -2), ' ', 1) as expire_time FROM domains AS d LEFT JOIN records AS r ON d.id = r.domain_id WHERE d.type = 'SLAVE' AND d.account IS NOT NULL AND r.type = 'SOA' AND d.last_check + SUBSTRING_INDEX(SUBSTRING_INDEX(r.content, ' ', -2), ' ', 1) < UNIX_TIMESTAMP(NOW())");
my $dsth = $dbh->prepare("DELETE FROM domains WHERE id = ?");

# Count the number of complete iterations through the loop.
my $count = 0;

# Lookup all domains that have a non-NULL 'account' field, which means they
# were provisioned on the fly by PowerDNS for a designated supermaster server.
$sth->execute() or die("Couldn't do domain search: ", $sth->errstr());
while (my($domid, $name, $lastchk, $expiretime) = $sth->fetchrow_array()) {
	# Spit out some statistics on the domain, presently for debugging use.
	# This will probably be dropped later, once I'm more sure that this is
	# working as intended.
	print "Domain ", $name, " (domain ID #", $domid, "):\n";
	print "\tLast successful check against domain master: ", POSIX::strftime($timefmt, POSIX::localtime($lastchk)), "\n";
	print "\tExpire time is ", $expiretime, " seconds\n";

	# Remove the domain. Don't worry about dropping the individual records,
	# since the database is set up with InnoDB tables and using foreign-key
	# relationships, so that's handled automatically.
	$dsth->execute($domid) or die("Couldn't delete domain: ", $dsth->errstr());;
	print "\tRemoved domain from datastore\n";
	print "\n";

	# Maintain the count of expired domains.
	$count++;
}

# More debug output - mentioning how many domains we expired. This also will
# go eventually, once we're sure this script works as intended.
print "Expired ", $count, " domain", ($count == 1 ? "" : "s"), " from datastore this run\n" if $count > 0;
