#!/usr/bin/perl

# $Id: data2sql.pl,v 1.1 2004/11/21 10:55:03 hutch Exp $
# purpose of the script:
# migrate tinydns data into vegadns
# this script follows after axfr2data.pl

my $Verbose = 1;
my $db = "vegadns";
my $mysqlhost = "localhost";
my $port = "3306";
my $user = "vegadns";
my $passwd = "vegadnspw";

my $indir = "./tmp";
my $donelist = "$indir/DONELIST.txt";
my $suffix = ".data";

my($dbh,$sth,$domain,$l,$datasource,$id,$Query,@Done,@row,$def_nsct,$line,$stripped,$h,$skip);
my(%def_ns,%def_soa);
my($ct,$ct2);

# set either of these to 1 if you want the settings in default_records
# to be applied instead
my $default_soa = 0;
my $default_ns = 0;

# owner_id and group_owner_id
# set these if you want all the records to have these set
my $owner_id = 0;
my $group_owner_id = 0;

# set to 0 if you do not want a check made for the existence of a domain in the db
my $checkdom = 1;

# if there are records thar you would like to NOT enter into the db,
# put them into this array. regex.
@skiplist = ("localhost");

if (! -e "$donelist") { die "$donelist NOT found\n"; }

# get the list
open(MYFILE, "$donelist") or die("$!");
while ($l=<MYFILE>) {
	chomp($l);
	# skip comments
	next if($l =~ /^#.*/);
	push (@Done, $l) if($l);
}
close(MYFILE);

use DBI;
# setup connection
$datasource = "DBI:mysql:database=$db;host=$mysqlhost;port=$port";
# Open the database.
$dbh = DBI->connect($datasource, $user, $passwd);
if(! $dbh) { die "ERROR in opening $db\n"; }

# collect default SOA and NS, if set
if ($default_soa) {
	$Query = "SELECT host,val FROM default_records WHERE type='S'";
	$sth = $dbh->prepare($Query);
	if(! $sth->execute()) { die "error in database\n"; }
	# just get the first
	@row = $sth->fetchrow_array;
	$def_soa{'host'} = $row[0];
	$def_soa{'val'}  = $row[1];
	print "run query: $Query\n"    if($Verbose);
}	
if ($default_ns) {
	$Query = "SELECT host,val,distance,ttl FROM default_records WHERE type='N'";
	$sth = $dbh->prepare($Query);
	if(! $sth->execute()) { die "error in database\n"; }
	$def_nsct = 0;
	while (@row = $sth->fetchrow_array) {
		$def_ns{'host'}[$def_nsct]      = $row[0];
		$def_ns{'val'}[$def_nsct]       = $row[1];
		$def_ns{'distance'}[$def_nsct]  = $row[2];
		$def_ns{'ttl'}[$def_nsct]       = $row[3];
		$def_nsct++;
	}
	print "run query: $Query\n"    if($Verbose);
}

# add to db
foreach $domain (@Done) {
	if ( -e "$indir/" . $domain . "$suffix")
	{
		# skip if its already there
		if ($checkdom) {
			$Query = "SELECT domain_id FROM domains WHERE domain='$domain' and status='active'";
			$sth = $dbh->prepare($Query);
			if(! $sth->execute()) { die "error in database\n"; }
			@row = $sth->fetchrow_array;
			print "run query: $Query\n"    if($Verbose);
			if ($row[0]) {
				print "skipping $domain, already in database\n" if($Verbose);
				next;
			}
		}
	
		# create initial record in domains
		$Query = "INSERT INTO domains SET ";
		$Query .= "domain='$domain', ";
		if ($owner_id)
		{
			$Query .= "owner_id=$owner_id, ";
		}
		if ($group_owner_id)
		{
			$Query .= "group_owner_id=$group_owner_id, ";
		}
		$Query .= "status = 'active' ";
		$sth = $dbh->prepare($Query);
		if(! $sth->execute()) { die "error in database\n"; }
		print "run query: $Query\n"    if($Verbose);
		
		# get the id
		# cannot get last_insert_id to work ;(
		#$id = $dbh->last_insert_id(undef,undef,undef,undef);
		
		$Query = "SELECT domain_id FROM domains WHERE domain='$domain'";
		$sth = $dbh->prepare($Query);
		if(! $sth->execute()) { die "error in database\n"; }
		@row = $sth->fetchrow_array;
		print "run query: $Query\n"    if($Verbose);
		$id = $row[0];
		
		print "added $domain ($id)\n"    if($Verbose);
		# open each data record
		open (MYFILE, "$indir/" . $domain . "$suffix") or die("$!");
		$ct = 0;
		while ($line = <MYFILE>) {
			# skip comments
			next if($line =~ /^#.*/);
			chomp($line);
			next if(! $line);
			
			# skiplist
			if ( $#skiplist+1 > 0 ) {
				$gotskip = 0;
				foreach $skip (@skiplist) {
					print "looking for $skip in skiplist\n"    if($Verbose);
					if ( $line =~ /$skip/ ) {
						print "skipping $skip\n"    if($Verbose);
						$gotskip = 1;
						last;
					}
				}
				if ($gotskip) {
					next;
				}
			}
			
			# Strip first char
			$stripped = $line;
			$stripped =~ s/^.//;
			my @array = split(":", $stripped);
			#print "stripped: $stripped\n"    if($Verbose);	
			
			# Format the array according to the type
			my %out_array;
			if ($line =~ /^\+/) {
				# A
				$out_array{'host'} = $array[0];
				$out_array{'type'} = 'A';
				$out_array{'val'} = $array[1];
				$out_array{'distance'} = '';
				$out_array{'ttl'} = $array[2];
				print "line +: $line\n"    if($Verbose);	
			}
			elsif ($line =~ /^C/) {
				# CNAME
				$out_array{'host'} = $array[0];
				$out_array{'type'} = 'C';
				$out_array{'val'} = $array[1];
				$out_array{'distance'} = '';
				$out_array{'ttl'} = $array[2];
				print "line C: $line\n"    if($Verbose);	
			}
			elsif ($line =~ /^@/) {
				# MX
				$out_array{'host'} = $array[0];
				$out_array{'type'} = 'M';
				$out_array{'val'} = $array[2];
				$out_array{'distance'} = $array[3];
				$out_array{'ttl'} = $array[4];
				print "line \@: $line\n"    if($Verbose);	
			}
			elsif ($line =~ /^&/) {
				# NS
				$out_array{'host'} = $array[0];
				$out_array{'type'} = 'N';
				$out_array{'val'} = $array[2];
				$out_array{'distance'} = '';
				$out_array{'ttl'} = $array[3];
				print "line \&: $line\n"    if($Verbose);	
			}
			elsif ($line =~ /^Z/) {
				# SOA
				$out_array{'host'} = $array[2].":".$array[1];
				$out_array{'type'} = 'S';
				$out_array{'val'} = $array[4].":".$array[5].":".$array[6].":".$array[7];
				$out_array{'distance'} = '';
				$out_array{'ttl'} = $array[8];		
				print "line Z: $line\n"    if($Verbose);
			}
			elsif ($line =~ /^\^/) {
				# PTR
				$out_array{'host'} = $array[0];
				$out_array{'type'} = 'P';
				$out_array{'val'} = $array[1];
				$out_array{'distance'} = '';
				$out_array{'ttl'} = $array[2];		
				print "line ^: $line\n"    if($Verbose);	
			}
			elsif ($line =~ /^\:/) {
				# Is a leading colon, check the n field for the record type
				if($array[1] eq '16') {
					# Is a TXT record
					$out_array{'host'} = $array[0];
					$out_array{'type'} = 'T';
					$out_array{'val'} = $array[2];
					$out_array{'distance'} = '';
					$out_array{'ttl'} = $array[3];
					print "line :: $line\n"    if($Verbose);	
				}
			}
			else {
				# uh, no valid input
				next;
			}
			
			# use default if requested
			if ($default_soa and ($out_array{'type'} eq "S")) {
				$out_array{'val'} = $def_soa['val'];
				$out_array{'host'}= $def_soa['host'];
			}
			
			if ($default_ns  and ($out_array{'type'} eq "N")) {
				for ($ct2=0; $ct2 < $def_nsct; $ct2++) {
					$h = $def_ns{'host'}[$ct2];
					$h =~ s/DOMAIN/$domain/;
					$Query  = "INSERT INTO records SET ";
					$Query .= "domain_id='" . int($id)                  . "', ";
					$Query .= "host='"      . $h                        . "', ";
					$Query .= "type='N', ";
					$Query .= "val='"       . $def_ns{'val'}[$ct2]      . "', ";
					$Query .= "distance='"  . $def_ns{'distance'}[$ct2] . "', ";
					$Query .= "ttl='"       . $def_ns{'ttl'}[$ct2]      . "' ";
					$sth = $dbh->prepare($Query);
					if(! $sth->execute()) { die "error in database\n"; }
					print "run query: $Query\n"    if($Verbose);
				}
			}
			else
			{
				$h = $out_array{'host'};
				$h =~ s/\\052/*/;
				$Query  = "INSERT INTO records SET ";
				$Query .= "domain_id='" . int($id)               . "', ";
				$Query .= "host='"      .  $h                    . "', ";
				$Query .= "type='"      . $out_array{'type'}     . "', ";
				$Query .= "val='"       . $out_array{'val'}      . "', ";
				$Query .= "distance = '"  . $out_array{'distance'} . "', ";
				$Query .= "ttl = '"       . $out_array{'ttl'}      . "' ";
				$sth = $dbh->prepare($Query);
				if(! $sth->execute()) { die "error in database\n"; }				
				print "run query: $Query\n"    if($Verbose);
			}
			$ct++;
		}
		close(MYFILE);
	}
}
## eof ##