#!/usr/bin/perl -w
#Script for creating DLZ PostgreSQL zone
use strict;
use DBI;

my $version = '0.1';
my ($db_server, $db_user, $db_name, $db_passwd);
my ($zone_filename, $zone_name, $ns_count, $mx_count, $xfr_count, $ttl, $type,
    $data, $resp_person, $serial, $refresh, $retry, $expire, $minimum);
my ($dbh, $query);
my ($inp, $i) = (undef, 0);
my (@ns_servers, @mx_servers, @xfr_servers) = ((), (), ());
my ($soa_table, $xfr_table) = ('soa_table', 'xfr_table');
my  ($u_int,        $digit,     $domain_name,           $alpha_string,  $string,    $ip_addr) =
    ('^[0-9]+$',    '^\d+$',    '^[0-9a-zA-Z\.-]+$',    '^[a-zA-Z]+$',  '.+',       '^\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}$');

###################################################################
    #Start execution
    print "Script for adding DLZ zone in PostgreSQL database (v$version) started...\n\n";


    print "Configuring...\n\n";

    $db_server = get_param("Enter database server addres or hostname ('localhost' is default): ",
                    0, $domain_name, 'localhost');
    $db_name = get_param("Enter database name: ", 1, $string);
    $db_user = get_param("Enter database user name: ", 1, $alpha_string);
    $db_passwd = get_param("Enter database user password (may be empty): ",
                                    0, $string, '', 1);

    while(1)
    {
        $zone_filename = get_param("Enter zone filename: ", 1, $string);
        if( -T $zone_filename){last};
        print "File '$zone_filename' not exist or not text file!\n";
    }

    $zone_name = get_param("Enter zone name: ", 1, $string);

    print "Filling SOA data:\n";
    $ns_count = get_param("Enter DNS count (default 2): ", 0, $u_int, 2);

    for($i = 0; $i < $ns_count; ++$i)
    {
        push(@ns_servers, get_param("DNS #".($i + 1).": ", 1, $domain_name));
    }

    $mx_count = get_param("Enter MX count (default 0): ", 0, $domain_name, 0);
    for($i = 0; $i < $mx_count; ++$i)
    {
        push(@{$mx_servers[$i]}, get_param("Name MX #".($i + 1).": ", 1, $domain_name));
        push(@{$mx_servers[$i]}, get_param("Prio MX #".($i + 1).": ", 1, $u_int));
    }

    $xfr_count = get_param("Enter XFR servers count (default 0): ", 0, $u_int, 0);
    for($i = 0; $i < $xfr_count; ++$i)
    {
        push(@xfr_servers, get_param("XFR server #".($i + 1).": ", 1, $ip_addr));
    }
    $ttl = get_param("TTL (in seconds): ", 1, $u_int);
    $resp_person = get_param("Resp. person: ", 1, $domain_name);
    $serial = get_param("Serial: ", 1, $u_int);
    $refresh = get_param("Refresh: ", 1, $u_int);
    $retry = get_param("Retry: ", 1, $u_int);
    $expire = get_param("Expire: ", 1, $u_int);
    $minimum = get_param("Minimum: ", 1, $u_int);

    print "\nConfirm creating zone $zone_name [N/y]: ";
    $inp = <STDIN>;
    if(!defined($inp) || !($inp =~ /^[yY]$/))
    {
        print "Aborted.\n";
        exit;
    }

    print "\nStart creating zone $zone_name\n\n";

    print "Connecting to database server '$db_server' as '$db_user'...";
    $dbh = DBI -> connect("dbi:Pg:dbname=$db_name;host=$db_server",
                            $db_user, $db_passwd, {PrintError => 0});
    if (defined($DBI::err))
    {
        print $DBI::errstr . "\n";
        exit($DBI::err);
    }
    print "Ok\n";

    print "Create main zone table '$zone_name'... ";
    if(!exec_query(\$dbh, "CREATE TABLE \"$zone_name\"
                    (
                      host text,
                      type text,
                      mx_priority text,
                      data text
                    )WITH OIDS")){
        goto _exit;
    }
    print "Ok\n";
    print "Create index 'host_index_$zone_name' on '$zone_name'... ";
    if(!exec_query(\$dbh, "CREATE INDEX \"host_index_$zone_name\" ON \"$zone_name\" USING btree (host);")){
        goto _exit;
    }
    print "Ok\n";

    print "\nCreating and configuring database finished.\n\n";
    print "Inserting data in database...\n\n";

    print "Filling $soa_table.\n";

    print "Inserting SOA record for '$zone_name'... ";
    if(!exec_query(\$dbh, "INSERT INTO $soa_table(zone, host, ttl, type, data,
        resp_person, serial, refresh, retry,expire, minimum)
        VALUES ('$zone_name', '\@', $ttl, 'SOA', '$ns_servers[0]',
        '$resp_person', $serial, $refresh, $retry, $expire, $minimum)")){
        exit;
    }
    print "Ok\n";
    foreach my $ns (@ns_servers)
    {
        print "Inserting NS '$ns' in $soa_table... ";
        if(!exec_query(\$dbh, "INSERT INTO $soa_table(zone, host, type, data)
        VALUES ('$zone_name', '\@', 'NS', '$ns.$zone_name.')")){
            goto _exit;
        }
        print "Ok\n";
    }
    foreach my $mx (@mx_servers)
    {
        print "Inserting MX '@{$mx}[0]' with prio '@{$mx}[1]' in $soa_table... ";
        if(!exec_query(\$dbh, "INSERT INTO \"$zone_name\"
                                      (host, type, mx_priority, data)
                                values('\@', 'MX', @{$mx}[1], '@{$mx}[0]')")){
            goto _exit;
        }
        print "Ok\n";
    }
    open (ZONE, $zone_filename) || die "Can't open $zone_filename: $!";

    print "Infill $xfr_table:\n";
    foreach my $xfr_server (@xfr_servers)
    {
        print "Inserting server $xfr_server for zone $zone_name... ";
        if(!exec_query(\$dbh, "INSERT INTO $xfr_table (zone, client) VALUES ('$zone_name', '$xfr_server')")){
                goto _exit;
        }
        print "Ok\n";
    }

    print "Infill $xfr_table complite.\n";
    print "Inserting hosts data in tables...\n";
    print "Inserted in \"$zone_name\":           ";
    while(<ZONE>)
    {
        if(!/^([a-zA-Z0-9-\.\*]+)\s+in\s+(\w+)\s+(.+?)[\$\s;]/i) { next; };
        if(!exec_query(\$dbh,
                "INSERT INTO \"$zone_name\" (host, type, data) VALUES ('".lc($1)."', '".uc($2)."', '$3')"
            ))
        {
            print "Continue? [N/y]: ";
            $inp = <STDIN>;
            if(!defined($inp) || !($inp =~ /^[yY]$/))
            {
                print "Aborted.\n";
                goto _exit;
            }
        }
        printf(("\b"x7)."%7d", ++$i);
    }
    print "\nInfill main table complite.\n";
    seek (ZONE, 0, 0);

    $i = 0;
    print "Inserted in revers zone tables:           ";
    while(<ZONE>)
    {
        if(/^([a-zA-Z0-9-\.\*]+)\s+in\s+\w+\s+10\.(\d+)\.(\d+)\.(\d+)[\$\s;]/i)
        {
            $query = "INSERT INTO \"10.in-addr.arpa\" (host, type, data) VALUES ('$4.$3.$2', 'PTR', '$1.$zone_name.')";
        }elsif(/^([a-zA-Z0-9-\.\*]+)\s+in\s+\w+\s+192\.168\.(\d+)\.(\d+)[\$\s;]/i)
        {
            $query = "INSERT INTO \"168.192.in-addr.arpa\" (host, type, data) VALUES ('$3.$2', 'PTR', '$1.$zone_name.')";
        }else {next;};

        if(!exec_query(\$dbh, $query))
        {
            print "Continue? [N/y]: ";
            $inp = <STDIN>;
            if(!defined($inp) || !($inp =~ /^[yY]$/))
            {
                print "Aborted.\n";
                goto _exit;
            }
        }
        printf(("\b"x7)."%7d", ++$i);
    }
    close ZONE;
    print "\nInfill reverse tables complite.\n\n";

    print "Congratulations! All operation complite succefull!\n\n";
_exit:
    $dbh -> disconnect();
    print "Exit.\n";
    exit;

###################################################################

sub get_param
{
    my ($prompt, $must, $pattern, $default, $passwd) = @_;
    my $inp = undef;
    for(;;){
        print $prompt;
        if(defined($passwd)){ `stty -echo`;}
        $inp = <STDIN>;
        if(defined($passwd)){`stty echo`; print "\n";}
        chomp $inp;
        if((!defined($inp) || ($inp eq '')) && !$must) {last};
        if(!($inp =~ /$pattern/)){
            $inp = undef;
            print "Invalid input.\n";
            next;
        }
        defined($inp) && last;
    }
    if((!defined($inp) || $inp eq '') && defined($default) ) {$inp = $default};
    return $inp;
}

sub exec_query
{
    my $dbh_ref = $_[0];
    my $query = $_[1];
    my $sth = $$dbh_ref -> prepare($query);
    my $rv = $sth->execute();
    if (!defined $rv)
    {
            print "ERROR executing query '$query' : " . $$dbh_ref -> errstr . "\n";
        return 0;
    }
    $sth->finish();
    return 1;
}
