#!/usr/bin/perl
#
# tkbind v 0.4a 11/11/1999
#
#    Edit (sql)bind zones via perl, Tk, and DBI
#
#    Copyright (C) 1999 Miles Lott <milos@insync.net>
# 
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
# 
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
# 
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#
# v 0.4a 11/11/1999
#     Oops! bug in sql save ( "replace" syntax ).  Should actually
#       work now.
#     Modified Database setup dialog to use checkboxes instead of entries
#       for yes/no configs.
#     Added shutdown handler for db setup.
#
# v 0.4 11/10/1999
#     Name change to reflect optional usage with sqlbind
#     New config file entry for database type !!!
#       You will need to save your config file again, then reconnect.
#       After that, all should be well...
#     Added ability to zap database
#       This drops the selected database and creates a new one, ready
#       for file imports - useful for new users who do not already
#       have an sqlbind database setup.
#       WARNING - This will ask if you are sure, but if you click 'Yes',
#                 it WILL DELETE your entire database !!!
#     Added ability to work with additional database types besides MySQL
#       I need feedback on this since I do not have them setup here yet.
#     Fixed replace syntax to work with standard sqlbind tables
#       This should continue to work for modified sqlbind tables that
#       contain serial/refresh/retry/expire/minttl as well.
#       Sorry about that :(
#
# v 0.3 11/04/1999
#     Added confirmation dialog for zone deletion
#     Added beta-quality export to standard bind zone files - enjoy!
#       This works for forward type zone exports only.
#     Additional config file entry !
#       Prompts for domain name on import if set to yes - see DB Setup dialog.
#     Mods to import code - may fix some and break some re: NS and MX imports
#       Code is commented accordingly - search for v 0.3
#     Added a shutdown handler which should close open tables, etc
#       prior to closing the application.  This was bogging down the mysql
#       server on subsequent runs or any other usage after editing massive
#       zone tables.
#
# v 0.2 11/01/1999
#     Added import from standard bind zone files
#     Import modified from code by ZeroDiVide <zerodiv@zerodivide.net> (Jason Orcutt)
#     Switched to Tk::Table for layout, which provides a decent vertical scrollbar
#     Cleaned up menu for non-implemented functions
#
# v 0.1 10/24/1999
#     Original release, branched from development work on mkdnstab

#------------------------------------------------------
$|=1;
use Tk;
require Tk::Dialog;
use DBI;
use Env qw(HOME);
$title="tkbind"; $version="0.4a";
$author = "Miles Lott\n<milos\@insync.net>";
$maincfg="$HOME/.sqlbedit";
$wkdir  = qx('pwd'); chomp($wkdir);
if (!$dirspec) { $dirspec=$wkdir;}
#------------------------------------------------------

$mw = MainWindow->new;
$mw->protocol('WM_DELETE_WINDOW', \&shutdown);
$mw->title("$title $version");
$mbar = $mw->Frame( -relief=>'ridge', -borderwidth=>2)
->pack( -fill=>'x', -anchor=>'e', -side=>'top');

$mbuttonF = $mbar->Menubutton(-text => "Zone", -tearoff => '0');
  $mbuttonF->command( -label => "Open zone", -command => \&dbwin);
  $mbuttonF->command( -label => "Import zone file", -command => \&getimportname);
  $mbuttonF->command( -label => "Export to forward zone file", -command => \&dbexport);
  # $mbuttonF->command( -label => "New zone", -command => \&new_def);
  $mbuttonF->command( -label => "Save zone as", -command => sub { &getnewname; } );
  $mf5 = $mbuttonF->command( -label => "Delete zone (!!!)", -command => \&dbdelete);
  $mbuttonF->command( -label => "Exit", -command => \&shutdown);
  $mbuttonF->pack( -side => 'left', -anchor => 'w');

  $mbuttonS = $mbar->Menubutton(-text => "Database", -tearoff => '0');
  $ms1 = $mbuttonS->command( -label => "Setup", -command => \&dbselect);
  $mbuttonS->command( -label => "(Re)Connect", -command => \&dbconnect);
  $ms3 = $mbuttonS->command( -label => "Disconnect", -command => \&dbdisconnect);
  $mbuttonS->pack( -side => 'left', -anchor => 'w');

  $mbuttonH = $mbar->Menubutton(-text => "Help", -tearoff => '0');
  $mbuttonH->command( -label => "About $title...", -command => \&about_box);
  $mbuttonH->pack( -side =>'left', -anchor => 'w'); 

### End main GUI

### Begin SQL (Dis)connect

sub dbconnect {
  if ($isopen) { &dbdisconnect; }
  $_=$dbtype;
  if    (/0/) {
    $@ = ""; eval { require Mysql; };
    if ($@) { &errwin($@,'MySQL modules not installed'); }
    else {
      print "Database type is MySQL.\n";
      $dsn = "DBI:mysql:database=$db;host=$dbhost;";
    }
  }
  elsif (/1/) {
    $@ = ""; eval { require DBD::Oracle; };
    if ($@) { &errwin($@,'Oracle module not installed'); }
    else {
      print "Database type is Oracle\n";
      $dsn = "DBI:oracle:database=$db;host=$dbhost;";
    }
  }
  elsif (/2/) {
    $@ = ""; eval { require DBD::Sybase; };
    if ($@) { &errwin($@,'Sybase module not installed'); }
    else {
      print "Database type is Oracle.\n";
      $dsn = "DBI:sybase:database=$db;host=$dbhost;";
    }
  }
  elsif (/3/) {
    $@ = ""; eval { require DBD::ODBC; };
    if ($@) { &errwin($@,'ODBC module not installed'); }
    else {
      print "Database type is ODBC.\n";
      $dsn = "DBI:odbc:database=$db;host=$dbhost;";
    }
  }
  if ($dsn) {
    $dbh = DBI->connect($dsn, $dbuser, $dbpass) or print "Can't connect to $dbhost server.  Reason: $DBI::errstr\n";
    if (!$DBI::errstr) {
      print "Successful connection to $db on $dbhost.\n";
      $isopen="1";
      if ($btndisc) { $btndisc->configure(-state => 'normal'); }
      if ($ms3) { $ms3->configure(-state => 'normal'); }
      if ($mf5) { $mf5->configure(-state => 'normal'); }
    }
  # if (!$defzone) { print "  No zone selected by default.\n"; }
  # else { print "  Zone \"$defzone\" selected.\n"; }
  }
  # else { &errwin('Due to changes in this version or an invalid config, please select a database type in Database Setup','version'); }
}

sub dbdisconnect {
  if ($isopen) {
    $dbh->disconnect;
    if (!$DBI::errstr) { print "Successful disconnection from $db on $dbhost.\n"; }
    $isopen="";
    if ($btndisc) { $btndisc->configure(-state => 'disabled'); }
    if ($ms3) { $ms3->configure(-state => 'disabled'); }
    if ($mf5) { $mf5->configure(-state => 'disabled'); }
  }
}

### End SQL (Dis)connect

### Begin Main

&getconfig;
if ($start && $defzone) {
  &dbconnect;
  if (!$defzone && !$start) { $zone =~ s/\_\_/\-/g; $zone =~ s/\_/\./g; }
  else { $zone =~ s/\-/\_\_/g; $zone =~ s/\./\_/g; }
  &sqlbedit; $start=0;
}
else { $start=0; }

MainLoop;

### END Main

### Called routines

sub about_box {
  $abouttitle="About $title";
  $mw->Dialog(-text =>"$title,\nthe (SQL)BIND editor,\nis released under the GPL\nCopyright 1999 by $author\nversion $version",
    -title => $abouttitle, -default_button=>'OK', -buttons =>[qw/OK/])->Show;
}

sub errwin() {
  $mw->Dialog(-text =>"$_[0]", -title => "$0 $version $_[1] error",
    -default_button=>'OK', -buttons =>[qw/OK/])->Show;
}

sub confirm() {
  $answer = $mw->Dialog(-text =>"$_[0] $_[2] ?",
    -title => "Confirm request to $_[1]", -bitmap => 'question',
    -default_button=>'No', -buttons =>[qw/Yes No/])->Show;
}

sub filldbfields {
  for $i (0..4) {
    ($f,$l,$e) = ("mhead_${i}", "mhead_${i}_l", "mhead_${i}_e");
    ${$f} = $mdb->Frame();
    ${$f}->pack(-side => 'top', -pady => '2', -anchor => 'e');
    ${$l} = ${$f}->Label(-text => $dbfields[$i], -anchor => 'e');
    ${$e} = ${$f}->Entry(-width => '20', -relief => 'sunken');
    ${$e}->insert('0', $dbfill[$i]);
    ${$e}->pack(-side => 'right');
    ${$l}->pack(-side => 'right');
  }
}

sub fillefields {
  require Tk::Table;
  $mqd=$mqe=$mqf="";
  $mqe = $mw->Frame( -relief=>'raised', -borderwidth=>2 );
  $mqe->pack(-fill => 'both', -side => 'top', -anchor => 'nw');

  $tab = $mqe->Table(
    -rows => 1, -columns => 15, -scrollbars => '',
    -fixedrows => 1, -fixedcolumns => 1,
    -takefocus => 1);
  $tab->pack(-expand=> 1, -fill => 'both');

  $lbl[1] = $mqe->Label( -borderwidth=>3, -width => '4',-text => $efields[0]);
  $tmp = $tab->put(1,1,$lbl[1]);
  $lbl[2] = $mqe->Label( -borderwidth=>3, -width => '24',-text => $efields[1]);
  $tmp = $tab->put(1,2,$lbl[2]);
  $lbl[3] = $mqe->Label( -borderwidth=>3, -width => '15',-text => $efields[2]);
  $tmp = $tab->put(1,3,$lbl[3]);
  $lbl[4] = $mqe->Label( -borderwidth=>3, -width => '5',-text => $efields[3]);
  $tmp = $tab->put(1,4,$lbl[4]);
  $lbl[5] = $mqe->Label( -borderwidth=>3, -width => '5',-text => $efields[4]);
  $tmp = $tab->put(1,5,$lbl[5]);
  $lbl[6] = $mqe->Label( -borderwidth=>3, -width => '7',-text => $efields[5]);
  $tmp = $tab->put(1,6,$lbl[6]);
  $lbl[7] = $mqe->Label( -borderwidth=>3, -width => '4',-text => $efields[6]);
  $tmp = $tab->put(1,7,$lbl[7]);
  $lbl[8] = $mqe->Label( -borderwidth=>3, -width => '22',-text => $efields[7]);
  $tmp = $tab->put(1,8,$lbl[8]);
  $lbl[9] = $mqe->Label( -borderwidth=>3, -width => '12',-text => $efields[8]);
  $tmp = $tab->put(1,9,$lbl[9]);

  $mqf = $mw->Frame( -relief=>'ridge', -borderwidth=>2 );
  $mqf->pack(-fill => 'y', -side => 'left', -anchor => 'nw');

  $table = $mqf->Table(
    -rows => 10, -columns => 15,
    -scrollbars => 'e', -fixedrows => 1,
    -fixedcolumns => 1, -takefocus => 1);
  $table->pack(-expand=> 1, -fill => 'both',);

  for $i (1..$#sqlID) {
    $ei[$i] = $mqf->Entry(-width => '4', -relief => 'sunken');
    $ei[$i]->insert('0', $sqlID[$i]); $tmp = $table->put($i+1, 1, $ei[$i]);
    $eo[$i] = $mqf->Entry(-width => '24', -relief => 'sunken');
    $eo[$i]->insert('0', $sqlOrigin[$i]); $tmp = $table->put($i+1, 2, $eo[$i]);
    $ew[$i] = $mqf->Entry(-width => '15', -relief => 'sunken');
    $ew[$i]->insert('0', $sqlOwner[$i]); $tmp = $table->put($i+1,3, $ew[$i]);
    $ec[$i] = $mqf->Entry(-width => '5', -relief => 'sunken');
    $ec[$i]->insert('0', $sqlClass[$i]); $tmp = $table->put($i+1,4, $ec[$i]);
    $et[$i] = $mqf->Entry(-width => '5', -relief => 'sunken');
    $et[$i]->insert('0', $sqlTTL[$i]); $tmp = $table->put($i+1,5, $et[$i]);
    $ey[$i] = $mqf->Entry(-width => '7', -relief => 'sunken');
    $ey[$i]->insert('0', $sqlType[$i]); $tmp = $table->put($i+1,6, $ey[$i]);
    $ep[$i] = $mqf->Entry(-width => '4', -relief => 'sunken');
    $ep[$i]->insert('0', $sqlPref[$i]); $tmp = $table->put($i+1,7, $ep[$i]);
    $ed[$i] = $mqf->Entry(-width => '22', -relief => 'sunken');
    $ed[$i]->insert('0', $sqlData[$i]); $tmp = $table->put($i+1,8, $ed[$i]);
    $em[$i] = $mqf->Entry(-width => '12', -relief => 'sunken');
    $em[$i]->insert('0', $sqlComment[$i]); $tmp = $table->put($i+1,9, $em[$i]);

    if ($sqlSerialNumber[$i]) {
      $mqd = $mw->Frame( -relief=>'ridge', -borderwidth=>2 );
      $mqd->pack(-fill => 'y', -side => 'top', -anchor => 'nw', before => $mqe);

      $mqd->Label( -borderwidth=>3, -width => '7',-text => 'Serial')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $es[$i] = $mqd->Entry(-width => '20', -relief => 'sunken')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $es[$i]->insert('0', $sqlSerialNumber[$i]);
      $mqd->Label( -borderwidth=>3, -width => '8',-text => 'Refresh')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $ef[$i] = $mqd->Entry(-width => '10', -relief => 'sunken')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $ef[$i]->insert('0', $sqlRefresh[$i]);
      $mqd->Label( -borderwidth=>3, -width => '7',-text => 'Retry')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $er[$i] =  $mqd->Entry(-width => '10', -relief => 'sunken')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $er[$i]->insert('0', $sqlRetry[$i]);
      $mqd->Label( -borderwidth=>3, -width => '7',-text => 'Expire')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $ee[$i] =  $mqd->Entry(-width => '10', -relief => 'sunken')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $ee[$i]->insert('0', $sqlExpire[$i]);
      $mqd->Label( -borderwidth=>3, -width => '7',-text => 'MinTTL')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $el[$i] =  $mqd->Entry(-width => '10', -relief => 'sunken')
        ->pack(-fill => 'x', -side => 'left', -anchor => 'nw');
      $el[$i]->insert('0', $sqlMinTTL[$i]);
    }
  }
}

sub clearefields {
  for $i ($#l..1) {
    if ($l[$i]) {
      $l[$i]->destroy;
    }
  }
  for $i ($#em..1) {
    if ($em[$i]) {
      $ei[$i]->destroy('0', 'end'); $eo[$i]->destroy('0', 'end');
      $ew[$i]->destroy('0', 'end'); $ec[$i]->destroy('0', 'end');
      $et[$i]->destroy('0', 'end'); $ey[$i]->destroy('0', 'end');
      $ep[$i]->destroy('0', 'end'); $ed[$i]->destroy('0', 'end');
      $em[$i]->destroy('0', 'end');
    }
  }
  if ($mqf) { $mqf->destroy(); }
  if ($mqe) { $mqe->destroy(); }
  if ($mqd) { $mqd->destroy(); }
}

sub getefields {
  for $i (1..$#sqlID) {
    $sqlID[$i]      = $ei[$i]->get(); $sqlOrigin[$i]  = $eo[$i]->get();
    $sqlOwner[$i]   = $ew[$i]->get(); $sqlClass[$i]   = $ec[$i]->get();
    $sqlTTL[$i]     = $et[$i]->get(); $sqlType[$i]    = $ey[$i]->get();
    $sqlPref[$i]    = $ep[$i]->get(); $sqlData[$i]    = $ed[$i]->get();
    $sqlTime[$i]="NULL"; $sqlComment[$i] = $em[$i]->get();
    if ($sqlSerialNumber[$i]) {
      $sqlSerialNumber[$i]    = $es[$i]->get();
      $sqlRefresh[$i]	= $ef[$i]->get();
      $sqlRetry[$i]	= $er[$i]->get();
      $sqlExpire[$i]	= $ee[$i]->get();
      $sqlMinTTL[$i]	= $el[$i]->get();
    }
  }
}

sub getdbfields {
  for $i (0..4) {
    ($f,$l,$e) = ("mhead_${i}", "mhead_${i}_l", "mhead_${i}_e");
    $dbfill[$i] = ${$e}->get();
  }
}

sub enadbset {
  $ms1->configure(-state => 'normal');
  $mdb->destroy;
}

sub dbselect {
  $ms1->configure(-state => 'disabled');
  $mdb = $mw->Toplevel(-title => "$title Database Setup");
  $mdb->protocol('WM_DELETE_WINDOW', \&enadbset);
  $mhead = $mdb->Frame( -relief=>'ridge', -borderwidth=>2)
    ->pack( -fill=>'x', -anchor=>'nw', -side=>'top');

  @dbfields = ('Database Host','Database','User','Pass','Default Zone');

  $mright = $mdb->Frame( -relief=>'ridge', -borderwidth=>2)
    ->pack( -fill=>'x', -anchor=>'ne', -side=>'right');
  $mright->Label( -text => 'Database Type', -anchor => 'w')
    ->pack( -fill=>'x', -anchor=>'nw', -side=>'top');
  @typerad = ('MySQL',' Oracle','Sybase',' ODBC');
  for my $type (0..3) {
    $mright->Radiobutton(
	-text => "$typerad[$type]", -variable => \$dbtype, anchor => 'w',
	-relief => 'flat', -value => $type)->pack(-side => 'top');
  }
  $btnzap = $mright->Button(-text => 'Zap Database');
  $btnzap->configure(-command =>  sub { &zapdb; });
  $btnzap->pack(-side => 'bottom', -padx => '2');

  $mbot = $mdb->Frame( -relief=>'ridge', -borderwidth=>2)
    ->pack( -fill=>'x', -anchor=>'nw', -side=>'bottom');
  $mbot->Checkbutton(
	-text => "Connect at Start?", -variable => \$cstart, anchor => 'e',
	-relief => 'flat')->pack(-side => 'top');
  $mbot->Checkbutton(
	-text => "Ask table name on import?", -variable => \$asknew, anchor => 'e',
	-relief => 'flat')->pack(-side => 'top');
  $mbot->Checkbutton(
	-text => "Create reverse on export?", -variable => \$mkrev, anchor => 'e',
	-relief => 'flat')->pack(-side => 'top');

  &filldbfields;
  $btnok = $mhead->Button(-text => 'OK');
  $btnok->configure(-command =>  sub { &cfgsave; $ms1->configure(-state => 'normal'); $mdb->destroy; });
  $btnok->pack(-side => 'left', -padx => '2');
  $btnsave = $mhead->Button(-text => 'Save');
  $btnsave->configure(-command => \&cfgsave);
  $btnsave->pack(-side => 'left', -padx => '2');
  $btnconnect = $mhead->Button(-text => '(Re)Connect');
  $btnconnect->configure(-command =>  sub { &dbconnect; });
  $btnconnect->pack(-side => 'left', -padx => '2');
  $btndisc = $mhead->Button(-text => 'Disconnect');
  $btndisc->configure(-command =>  sub { &dbdisconnect; }); 
  $btndisc->pack(-side => 'left', -padx => '2');
  $btncancel = $mhead->Button(-text => 'Cancel');
  $btncancel->configure(-command => sub { &enadbset; });
  $btncancel->pack(-side => 'left', -padx => '2');
  if (!$isopen) { $btndisc->configure(-state => 'disabled'); }
}

sub cfgsave {
  &getdbfields;
  if ($cstart eq "" || $cstart eq "0") { $start=""; } else { $start="yes"; }
  if ($asknew eq "" || $asknew eq "0") { $askname=""; } else { $askname="yes"; }
  if ($mkrev eq "" || $mkrev eq "0") { $reverse=""; } else { $reverse="yes"; }
  $wrtime = scalar(localtime(time));
  open (CONFIG, ">$maincfg") or die "Couldn't open $maincfg for write.\n";
    print CONFIG "\#\#\# tkbind database setup\n\#\n";
    print CONFIG "\#\#\# Database type (Mysql == 0(default), Oracle == 1, Sybase == 2, ODBC == 3)\n";
    print CONFIG "type\t$dbtype\n";
    print CONFIG "host\t$dbfill[0]\n";
    print CONFIG "db\t$dbfill[1]\n";
    print CONFIG "user\t$dbfill[2]\n";
    print CONFIG "pass\t$dbfill[3]\n\n";
    print CONFIG "\#\#\# Default zone\n";
    print CONFIG "zone\t$dbfill[4]\n\n";
    print CONFIG "\#\#\# Connect at start\?\n";
    print CONFIG "cstart\t$start\n\n";
    print CONFIG "\#\#\# Always ask for new domain name on zone import\?\n";
    print CONFIG "asknew\t$askname\n\n";
    print CONFIG "\#\#\# Create reverse zone file on export\?\n";
    print CONFIG "reverse\t$reverse\n\n";
    print CONFIG "\#\#\# $maincfg written at $wrtime\n";
  close CONFIG;
  &getconfig;
}

sub getconfig {
  open (CONFIG, $maincfg) or &errwin('No config file - Use Database setup to make one.', $0);
    while (<CONFIG>) { chomp $_;
      if (/\Atype\t(.+)/) { $dbtype=$1; }
      if (/\Ahost\t(.+)/) { $dbhost=$1; }
      if (/\Adb\t(.+)/) { $db=$1; }
      if (/\Auser\t(.+)/) { $dbuser=$1; }
      if (/\Apass\t(.+)/) { $dbpass=$1; }
      if (/\Azone\t(.+)/) { $defzone=$1; }
      if (/\Acstart\t(.+)/) { $start=$1; }
      if (/\Aasknew\t(.+)/) { $askname=$1; }
      if (/\Areverse\t(.+)/) { $reverse=$1; }
    }
  close CONFIG;
    if ($start eq "") { $cstart="0"; } else { $cstart="1"; }
    if ($askname eq "") { $asknew="0"; } else { $asknew="1"; }
    if ($reverse eq "") { $mkrev="0"; } else { $mkrev="1"; }
    @dbfill = ($dbhost,$db,$dbuser,$dbpass,$defzone,$cstart,$asknew,$mkrev);
}

sub dbadd {
  # Write to new zone
  # Replaces saveas_def ?.dnstab
  # &getefields;
  # print "$domain\t$newname\n";
  $zone = $domain;
  $zone =~ s/\-/\_\_/g;
  $zone =~ s/\./\_/g;

  if ($newname) {
    $newname="";
    print "New zone is $zone\n";
    if (!$isopen) { &dbconnect; }
    $create = "CREATE TABLE $zone (
      sqlID int(11) DEFAULT '0' NOT NULL auto_increment,
      sqlOrigin char(100) DEFAULT '' NOT NULL, sqlOwner char(100),
      sqlClass char(20) DEFAULT '' NOT NULL, sqlTTL char(15),
      sqlType char(10) DEFAULT '' NOT NULL, sqlPref char(5),
      sqlData char(255) DEFAULT '' NOT NULL,
      sqlTime timestamp(14), sqlComment char(30), PRIMARY KEY (sqlID))";

    $dbc = $dbh->prepare($create) || &errwin('prepare_sql', $0);
    $dbc->execute || &errwin('sql', $0);

    for $i (1..$#sqlID) {
      $tmpquery = "INSERT INTO $zone VALUES ('$sqlID[$i]','$sqlOrigin[$i]',
          '$sqlOwner[$i]','$sqlClass[$i]','$sqlTTL[$i]','$sqlType[$i]',
          '$sqlPref[$i]','$sqlData[$i]',NULL,'$sqlComment[$i]')";

      $dben = $dbh->prepare($tmpquery) || &errwin('prepare_sql', $0);
      $dben->execute || &errwin('sql', $0);
    } 
  }
}

sub dbdelete {
  # Delete zone !!!
  &confirm('Completely delete', 'delete zone',$domain);
  chomp($answer); $_ = $answer;
  if (/No/g)  { ; }
  else {
    &getefields;
    $dquery = "DROP table $zone";
    $dbd = $dbh->prepare($dquery) || &errwin('prepare_sql', $0);
    $dbd->execute || &errwin('sql', $0);
    if (!$DBI::errstr) {
      # &clearefields;
      &getefields;
    }
  }
}

sub getnewname {
  # Window to select new domain name for sql saveas
  &getefields;
  $mnname = $mw->Toplevel(-title => "Copy zone to...");
  $mnewsel = $mnname->Frame( -relief=>'ridge', -borderwidth=>2)
    ->pack( -fill=>'x', -anchor=>'nw', -side=>'top');
  $mnewsel->Label(-text => "Zone", -anchor => 'w');
  $nname = $mnewsel->Entry( -textvariable => \$domain)
    ->pack( -fill=>'x', -anchor=>'ne', -side=>'top');
  $btnewok = $mnewsel->Button(-text => 'OK')
    ->pack( -anchor=>'nw', -side=>'left');
  $btnewok->configure(-command =>  sub { $domain=$nname->get(); $newname="1"; &dbadd; $mnname->destroy; } );
  $btnewcl = $mnewsel->Button(-text => 'Cancel')
    ->pack( -anchor=>'ne', -side=>'right');
  $btnewcl->configure(-command => sub { $mnname->destroy; } );
}

sub getimportname {
  if ($asknew) {
    # Window to select new domain name for sql import
    $mmname = $mw->Toplevel(-title => "Import new zone as...");
    $mnewsel = $mmname->Frame( -relief=>'ridge', -borderwidth=>2)
      ->pack( -fill=>'x', -anchor=>'nw', -side=>'top');
    $mnewsel->Label(-text => "Zone", -anchor => 'w');
    $mname = $mnewsel->Entry( -textvariable => \$domain)
      ->pack( -fill=>'x', -anchor=>'ne', -side=>'top');
    $btnmok = $mnewsel->Button(-text => 'OK')
      ->pack( -anchor=>'nw', -side=>'left');
    $btnmok->configure(-command =>  sub { $domain=$mname->get(); $newname="1"; &dbimport; $mmname->destroy; } );
    $btnmcl = $mnewsel->Button(-text => 'Cancel')
      ->pack( -anchor=>'ne', -side=>'right');
    $btnmcl->configure(-command => sub { $mmname->destroy; } );
  }
  else { &dbimport; }
}

sub dbwin {
  $mdbwin = $mw->Toplevel(-title => "Zone Select");
  $msel = $mdbwin->Frame( -relief=>'ridge', -borderwidth=>2)
    ->pack( -fill=>'x', -anchor=>'nw', -side=>'top');
  $msel->Label(-text => "Zone", -anchor => 'w');
  my $list = $msel->Scrolled(qw/Listbox -setgrid 1 -height 10 -scrollbars e/);
    $list->pack(qw/-side left -expand yes -fill both/);

  $zquery = "SHOW TABLES";
  if (!$isopen) { &dbconnect; }
  $dbz = $dbh->prepare($zquery) || &errwin('prepare_sql', $0);
  if ($zquery) {
    $dbz->execute || &errwin('sql', $0);
    while ( $zlist=$dbz->fetchrow ) {
      if ( $zlist ne "zonedef" ) {
        # print "$zlist\n";
        $list->insert(0,$zlist); } } }

  $list->bind('<Double-1>' =>
    sub {
      $defzone="";$szone=$_[0]->get('active'); $zone=$szone;
      &sqlbedit; $mdbwin->destroy },
    );

  $btndbwok = $msel->Button(-text => 'OK');
  $btndbwok->configure(-command => sub {
    $defzone=""; $szone=$list->get('active'); $zone=$szone;
    &sqlbedit; $mdbwin->destroy; });
  $btndbwok->pack(-side => 'left', -padx => '2');
}

sub nodefwin {
  $answer = $mw->Dialog(-text =>"$_[0]", -title => "$0 $version $_[1] problem.",
    -default_button=>'OK', -buttons =>[qw/OK Cancel/])->Show;
  # print $answer;
  chomp($answer); $_ = $answer; if (/OK/g) { &dbadd; }
}

sub sqlbedit {
  if (!$isopen) { &dbconnect; }
  if ($dsn) {
    if (!$szone) { 
      $zone=$domain=$defzone;
      print "Selected zone is $zone\n";
      $zone =~ s/\-/\_\_/g; $zone =~ s/\./\_/g;
    }
    else {
      $domain=$zone;
      $domain =~ s/\_\_/\-/g; $domain =~ s/\_/\./g;
    }

    $equery = "SELECT * from $zone"; $sqlSerialNumber[1]='';
    print "Query is $equery\n";
      $dbe = $dbh->prepare($equery) || &errwin('prepare_sql', $0);
      $dbe->execute || &errwin('sql', $0); $z=1; @elist=@sqlID="";

    while ( @elist=$dbe->fetchrow ) {
      ($sqlID[$z],$sqlOrigin[$z],$sqlOwner[$z],$sqlClass[$z],$sqlTTL[$z],
       $sqlType[$z],$sqlPref[$z],$sqlData[$z],$sqlTime[$z],
       $sqlComment[$z],$sqlSerialNumber[$z],$sqlRefresh[$z],$sqlRetry[$z],
       $sqlExpire[$z],$sqlMinTTL[$z]) = @elist;
      $z=$z+1;
    }

    if ($mqframe) { $mqframe->destroy; }
    $mqframe = $mw->Frame( -relief=>'ridge', -borderwidth=>2)
      ->pack( -fill=>'both', -anchor=>'nw', -side=>'top');
    $mqframe->Label(-text => "Zone:", -anchor => 'w')
      ->pack( -side => 'left', -padx => '2');
    $mqframe->Entry(-text => \$domain, -width => '30')
      ->pack( -side => 'left', -padx => '2');

    $btneok = $mqframe->Button(-text => 'OK');
    $btneok->pack(-side => 'right', -padx => '2');
    $btneok->configure(-command => sub {
      &getefields; &sqlbsave; print "saved...\n";});
    @efields = ('ID','Origin','Owner','Class','TTL','Type','Pref','Data','Comment');
    &clearefields;
    &fillefields;
    # print $sqlSerialNumber[1];
  }
}

sub sqlbsave {
  if (!$isopen) { &dbconnect; }
  if ($sqlSerialNumber[1]) {
    for $i (1..$#sqlID) {
      $tmpquery = "REPLACE INTO $zone VALUES ('$sqlID[$i]','$sqlOrigin[$i]',
	'$sqlOwner[$i]','$sqlClass[$i]','$sqlTTL[$i]','$sqlType[$i]',
	'$sqlPref[$i]','$sqlData[$i]',NULL,'$sqlComment[$i]',
	'$sqlSerialNumber[$i]','$sqlRefresh[$i]','$sqlRetry[$i]',
	'$sqlExpire[$i]','$sqlMinTTL[$i]')";
      $dben = $dbh->prepare($tmpquery) || &errwin('prepare_sql', $0);
      $dben->execute || &errwin('sql', $0);
    }
  }
  else {
    for $i (1..$#sqlID) {
      $tmpquery = "REPLACE INTO $zone VALUES ('$sqlID[$i]','$sqlOrigin[$i]',
	'$sqlOwner[$i]','$sqlClass[$i]','$sqlTTL[$i]','$sqlType[$i]',
	'$sqlPref[$i]','$sqlData[$i]',NULL,'$sqlComment[$i]')";
      $dben = $dbh->prepare($tmpquery) || &errwin('prepare_sql', $0);
      $dben->execute || &errwin('sql', $0);
    }
  }
}

sub null { }

sub shutdown {
  print"Shutting down... Please wait for open table\(s\) to close.\n";
  exit;
}

sub dbimport {
  @types =( ["All Files", [qw/*/] ] );
  $zonefile = $mw->getOpenFile(
    -filetypes => \@types,
    -initialdir => $dirspec);

  if ($zonefile) {
    if ($newname) { $zone = $domain; $newname=""; }
    else { use File::Basename; $zone = basename($zonefile); }

    $zonedb = $zone;
    $zonedb =~ s/\-/\_\_/g;
    $zonedb =~ s/\./\_/g;

    $addtable = "CREATE TABLE $zonedb ( sqlID INT(11) DEFAULT '0' NOT NULL AUTO_INCREMENT,
      sqlOrigin char(100) DEFAULT '' NOT NULL, sqlOwner char(100),
      sqlClass char(20) DEFAULT '' NOT NULL, sqlTTL BIGINT(15) DEFAULT '0' NULL,
      sqlType char(10) DEFAULT '' NOT NULL, sqlPref INT(5) DEFAULT '0' NULL,
      sqlData char(255) DEFAULT '' NOT NULL, sqlTime timestamp(14), sqlComment char(30),
      sqlSerialNumber BIGINT(30) DEFAULT '0' NOT NULL,
      sqlRefresh BIGINT(15) DEFAULT '0' NOT NULL, sqlRetry BIGINT(15) DEFAULT '0' NOT NULL,
      sqlExpire BIGINT(15) DEFAULT '0' NOT NULL, sqlMinTTL BIGINT(15) DEFAULT '0' NOT NULL,
      PRIMARY KEY (sqlID), INDEX( sqlID ) )";

    $dbimport = $dbh->prepare($addtable) || &errwin('prepare_sql', $0);
    $dbimport->execute || &errwin('sql', $0);
    &import_parser;
    $zone=$zonedb;
    &sqlbedit;
  }
}

sub dbexport {
  # &errwin('Sorry - not yet implemented :(', $0)

  @types =( ["All Files", [qw/*/] ] );
  $zonefile = $mw->getSaveFile(
    -filetypes => \@types,
    -initialdir => $dirspec);

  if ($zonefile) {
    &getefields;
    &mkfwd;
#    &mkrev;
  }
}

sub zapdb {
  $dtmp = "$db database on $dbhost";
  &confirm('Completely delete and recreate empty', 'zap database',$dtmp);
  chomp($answer); $_ = $answer;
  if (/No/g)  { ; }
  else {
    $zquery = "DROP database if exists $db";
    $znew   = "CREATE database $db";
    $dbzap = $dbh->prepare($zquery) || &errwin('prepare_sql', $0);
    $dbzap->execute || &errwin('deletion error', $0);
    $dbzap = $dbh->prepare($znew) || &errwin('prepare_sql', $0);
    $dbzap->execute || &errwin('creation error', $0);
    &dbconnect;
 }
}

sub tbldef_conv {
  ($fwd,$rev,$headers,$domain,$subnet,$hostname,$hostip,$mailex1,$mailip1,
   $mailpref1,$mailex2,$mailip2,$mailpref2,$mailex3,$mailip3,$mailpref3,
   $master,$prefix,$serial,$refresh,$retry,$expire,$minimum,$range,
   $dnsfwd,$dnsrev,$conf,$dnsconf,$dnsloc,$forwarder1,$forwarder2,$ismaster,
   $mserver) = @tbldef;
}

sub tbldef_make {
  @tbldef = ($ID,$fwd,$rev,$headers,$domain,$subnet,$hostname,$hostip,$mailex1,
   $mailip1,$mailpref1,$mailex2,$mailip2,$mailpref2,$mailex3,$mailip3,
   $mailpref3,$master,$prefix,$serial,$refresh,$retry,$expire,$minimum,
   $range,$dnsfwd,$dnsrev,$conf,$dnsconf,$dnsloc,$forwarder1,$forwarder2,
   $ismaster,$mserver)
}

sub import_parser {
  # ZeroDiVide
  open (INPUT_FILE, $zonefile);

  while( $input_line = <INPUT_FILE> ) { 
    chomp( $input_line );
    $input_line =~ s/\r\r//g;
    ( $content, $comment ) = split( ';', $input_line );
    if ( $content =~ /SOA/i ) {
      $soa_flag = 1;
      $soa_text = $content;
      next;
    }
    if ( $soa_flag == 1 && $content !~ /\)/ ) {
      $soa_text .= $content;
      next;
    }
    if ( $soa_flag == 1 && $content =~ /\)/ ) {
      $soa_text .= $content;
      $content  = $soa_text;
      $soa_flag = 2;
    }
    $content =~ s/\t/ /g;
    while( $content =~ /  / ) {
      $content =~ s/  / /g;
    }
    $content =~ s/^ //;
    if ( $content eq "" ) { next; }
    if (
    $content =~ /(.*)\sIN\sSOA\s(.*)\s(.*)\s\(\s(\d*)\s(\d*)\s(\d*)\s(\d*)\s(\d*)\s\)/ ||
    $content =~ /(.*)\sIN\sSOA\s(.*)\s(.*)\s\(\s(\d*)\s(\d*)\s(\d*)\s(\d*)\s(\d*)\)/ ) {
      local $origin = $1; local $contact = $3;
      local $serial = $4; local $refresh = $5;
      local $retry = $6; local $expire = $7;
      local $min_ttl = $8; $contact =~ s/\.$//g;
      $previous_name = $zone; $table_name = $zone;
      $table_name =~ s/\-/\_\_/g; $table_name =~ s/\./\_/g;

    insert_record($zone, '@', 'IN', '', 'SOA', '', $zone, 'NULL', '',
      $serial, $refresh, $retry, $expire, $min_ttl);
    next;
    }
    if ( $content =~ /IN\sNS(.*)/ ) {
      $server = $1; $server =~ s/\.$//;
      # v 0.3 - The next line removes spaces from $server, maybe only my problem
      $server =~ s/\ //;
      #insert_record( $zone, '', 'IN', '', 'NS', '', $server, 'NULL', $comment );
      insert_record( $zone, '@', 'IN', '', 'NS', '', $server, 'NULL', $comment );
      next;
    }
    $name = ''; $target_addr = ''; $mx_weight = ''; $mx_target = '';
    if ( $content =~ /^IN\sA\s(.*)/ ) {
      $target_addr = $1; $target_addr =~ s/\.$//;
      insert_record( $zone, $previous_name, 'IN', '', 'A', '', $target_addr, 'NULL', $comment );
      next;
    }
    if ( $content =~ /^IN\sMX\s(\d*)\s(.*)/ ) {
      $mx_weight = $1; $mx_target = $2; $mx_target =~ s/\.$//; $mx_weight =~ s/\.$//;
      insert_record( $zone, $previous_name, 'IN', '', 'MX', $mx_weight, $mx_target, 'NULL', $comment );
      next;
    }
    if ( $content =~ /(.*)\sIN\sA\s(.*)/ ) {
      $name = $1; $target_addr = $2; $name =~ s/\.$//; $target_addr =~ s/\.$//;
      $previous_name = $name;
      insert_record( $zone, $name, 'IN', '', 'A', '', $target_addr, 'NULL', $comment );
      next;
    }
    if ( $content =~ /(.*)\sIN\sMX\s(\d*)\s(.*)/ ) {
      # $name = $1; $mx_weight = $2; $mx_addr = $2; $name =~ s/\.$//;
      # v 0.3 - was getting empty MXes
      $name = $1; $mx_weight = $2; $mx_target = $3; $name =~ s/\.$//;
      $mx_weight =~ s/\.$//; $mx_addr =~ s/\.$//; $previous_name = $name;
      insert_record( $zone, $name, 'IN', '', 'MX', $mx_weight, $mx_target, 'NULL', $comment );
      next;
    }
    if ( $content =~ /(.*)\sIN\sCNAME\s(.*)/ ) {
      $name = $1; $target_addr = $2; $name =~ s/\.$//;
      $target_addr =~ s/\.$//; $previous_name = $name;
      insert_record( $zone, $name, 'IN', '', 'CNAME', '', $target_addr, 'NULL', $comment );
      next;
    }
    if ( $content =~ /(\d*)\sIN\sPTR\s(.*)/ ) {
      $octet = $1; $target_name = $2; $octet  =~ s/\.$//; $target_name =~ s/\.$//;
      insert_record( $zone, $octet, 'IN', '', 'PTR', '', $target_name, 'NULL', $comment );
    }
  }
  close( INPUT_FILE );
}

sub insert_record {
   # ZeroDiVide 
   local $origin = shift; local $owner = shift;
   local $class = shift; local $ttl = shift;
   local $type = shift; local $pref = shift;
   local $data = shift; local $time = shift;
   local $comment = shift; local $serial_number = shift;
   local $refresh = shift; local $retry = shift;
   local $expire = shift; local $min_ttl = shift;

   $sql = "INSERT INTO $zonedb ( sqlID, sqlOrigin, sqlOwner,
          sqlClass, sqlTTL, sqlType, sqlPref, sqlData, sqlTime,
          sqlComment";

   if ( $serial_number ne '' ) {
     $sql .= ",sqlSerialNumber,sqlRefresh,sqlRetry,sqlExpire,sqlMinTTL";
   }

  $sql .= " ) VALUES ( " ;
  $sql .= "'', '$origin','$owner','$class','$ttl','$type','$pref','$data','NOW','$comment'";

  if ( $serial_number ne '' ) {
    $sql .= ",'$serial_number','$refresh','$retry','$expire','$min_ttl'";
  }

  $sql .= ")";
  $dbrecords = $dbh->prepare($sql) || &errwin('prepare_sql', $0);
  $dbrecords->execute || &errwin('sql', $0);
}

sub mkfwd {
  if ($sqlSerialNumber[1]) { $serial=$sqlSerialNumber[1]; } else { $serial=localtime(time); }
  if ($sqlRefresh[1]) { $refresh=$sqlRefresh[1]; } else { $refresh=28800; }
  if ($sqlRetry[1]) { $retry=$sqlRetry[1]; } else { $retry=14400; }
  if ($sqlExpire[1]) { $expire=$sqlExpire[1]; } else { $expire=360000; }
  if ($sqlMinTTL[1]) { $minimum=$sqlMinTTL[1]; } else { $minimum=10800; }

  open (FWD,">$zonefile");

  print FWD "\$ORIGIN $domain\.\n\$TTL $minimum\n\n";
  print FWD "\@\tIN\tSOA\t\t$domain\.\troot\.$hostname\.$domain\.\t(\n";
  print FWD "\t\t\t\t$serial \;\t\tSerial\n\t\t\t\t$refresh\t\;\t\tRefresh\n";
  print FWD "\t\t\t\t$retry\t\;\t\tRetry\n\t\t\t\t$expire\t\;\t\tExpire\n";
  print FWD "\t\t\t\t$minimum\ )\t\;\t\tMinimum\n\n";

  for $i ( 1..$#sqlID ) {
    $_=$sqlType[$i]; chomp $_;
    if (/\bA\b/) {
      $_=$sqlOwner[$i];
      if (/@/) {
        print FWD "\@\t\tIN\tA\t\t$sqlData[$i]\n";
      }
    }
  }

  for $i ( 1..$#sqlID ) {
    $_=$sqlType[$i]; chomp $_;
    if (/\bNS\b/) { print FWD "\@\t\tIN\tNS\t\t$sqlData[$i]\n"; }
  }
  for $i ( 1..$#sqlID ) {
     $_=$sqlType[$i]; chomp $_;
    if (/\bMX\b/) { print FWD "\@\t\tIN\tMX\t$sqlPref[$i]\t$sqlData[$i]\n"; }
  }
  print FWD "\n";
  for $i ( 1..$#sqlID ) {
    $_=$sqlType[$i]; chomp $_;
    if (/\bA\b/ || /\bCNAME\b/) {
      if ( $sqlOwner[$i] ne $sqlOrigin[$i] ) {
        if ( length ( $sqlOwner[$i] ) > 7 ) {
          print FWD "$sqlOwner[$i]\t$sqlClass[$i]\t$sqlType[$i]\t$sqlData[$i]\n";
        }
        else {
          print FWD "$sqlOwner[$i]\t\t$sqlClass[$i]\t$sqlType[$i]\t$sqlData[$i]\n";
        }
      }
    }
    shift;
  }
  close FWD;
}

sub mkrev {
  # FIX ME !!!
  qx($mkrdns $zonefile > $revname);
}

# FIN