#!/usr/local/bin/perl
# dump my DNS data from MySQL and output it to stdout in tinydns-data format

use Date::Format;
use DBI;

$DB= DBI->connect("DBI:mysql:DNS" . ";mysql_read_default_file=/root/.my.cnf", root, $password);

# handle location records
$QueryString="select * from Location order by IP DESC";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $Location=$SQLData[1];
  $IP=$SQLData[2];
  print ("\%$Location:$IP\n");
}

# handle NS records
$QueryString="select * from NS order by Domain,ServerFQDN";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $Domain=$SQLData[1];
  $IP=$SQLData[2];
  $ServerFQDN=$SQLData[3];
  $TTL=$SQLData[4];
  $Active=$SQLData[5];
  $TTL =~ s/^0$//;
  $Record="\&$Domain:$IP:$ServerFQDN:$TTL";
  if ($Active ne "Y")  {
    $Record="# $Record";
  }
  print ("$Record\n");
}

# handle A records
$QueryString="select * from A order by FQDN,Location";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $FQDN=$SQLData[1];
  $IP=$SQLData[2];
  $TTL=$SQLData[3];
  $Location=$SQLData[4];
  $Active=$SQLData[5];
  $TTL =~ s/^0$//;
  $Record="\+$FQDN:$IP:$TTL\:\:$Location";
  if ($Active ne "Y")  {
    $Record =~ s/\+/-/;
  }
  print ("$Record\n");
}

# handle MX records
$QueryString="select * from MX order by FQDN,Distance";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $FQDN=$SQLData[1];
  $IP=$SQLData[2];
  $ServerFQDN=$SQLData[3];
  $Distance=$SQLData[4];
  $TTL=$SQLData[5];
  $Location=$SQLData[6];
  $Active=$SQLData[7];
  $TTL =~ s/^0$//;
  $Record="\@$FQDN:$IP:$ServerFQDN:$Distance:$TTL\:\:$Location";
  if ($Active ne "Y")  {
    $Record="# $Record";
  }
  print ("$Record\n");
}

# handle SOA records
$QueryString="select * from SOA order by Domain";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $Domain=$SQLData[1];
  $DNSServerFQDN=$SQLData[2];
  $ContactEmail=$SQLData[3];
  $SerialNumber=$SQLData[4];
  $RefreshTime=$SQLData[5];
  $RetryTime=$SQLData[6];
  $ExpireTime=$SQLData[7];
  $MinimumTime=$SQLData[8];
  $TTL=$SQLData[9];
  $Location=$SQLData[10];
  $Active=$SQLData[11];
  $SerialNumber =~ s/^0$//;
  $RefreshTime =~ s/^0$//;
  $RetryTime =~ s/^0$//;
  $ExpireTime =~ s/^0$//;
  $MinimumTime =~ s/^0$//;
  $TTL =~ s/^0$//;
  $Record="Z$Domain:$DNSServerFQDN:$ContactEmail:$SerialNumber:$RefreshTime:$RetryTime:$ExpireTime:$MinimumTime:$TTL\:\:$Location";
  if ($Active ne "Y")  {
    $Record="# $Record";
  }
  print ("$Record\n");
}

# handle PTR records
$QueryString="select * from PTR order by ArpaFQDN";
$QueryHandle=$DB->prepare($QueryString);
$QueryHandle->execute;
while (@SQLData=$QueryHandle->fetchrow_array)  {
  $ArpaFQDN=$SQLData[1];
  $FQDN=$SQLData[2];
  $TTL=$SQLData[3];
  $Location=$SQLData[4];
  $Active=$SQLData[5];
  $TTL =~ s/^0$//;
  $Record="\^$ArpaFQDN:$FQDN:$TTL\:\:$Location";
  if ($Active ne "Y")  {
    $Record="# $Record";
  }
  print ("$Record\n");
}