Changeset 372 for trunk


Ignore:
Timestamp:
08/01/12 18:19:03 (12 years ago)
Author:
Kris Deugau
Message:

/trunk

Checkpoint adding location/view support. See #10.

  • minor location list template tweak
  • extend importer to deal with locations on records, and location definitions
  • extend exporter to handle locations

Also:

  • minor SQL error-log cleanup (boolean types use 'y'/'n' or 'true'/'false'). See #25, sort of
  • Fix handling of CNAME exports for reverse zones; the records look ugly but there's no simple way to autoconvert them to the template types on import
  • Fix lurking buglet in tabledef update; we're using a 4-char field for locations to allow for legacy uses.
Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/DNSDB.pm

    r370 r372  
    42934293# of remaining data, allows up to 255 raw bytes
    42944294
     4295  # Locations/views - worth including in the caching setup?
     4296  my $lochash = $dbh->selectall_hashref("SELECT location,iplist FROM locations", 'location');
     4297  foreach my $location (keys %$lochash) {
     4298    foreach my $ipprefix (split /,/, $lochash->{$location}{iplist}) {
     4299      print $datafile "%$location:$ipprefix\n";
     4300    }
     4301    print $datafile "%$location\n" if !$lochash->{$location}{iplist};
     4302  }
     4303
    42954304  # tracking hash so we don't double-export A+PTR or AAAA+PTR records.
    42964305  my %recflags;
    42974306
    42984307  my $domsth = $dbh->prepare("SELECT domain_id,domain,status,changed FROM domains WHERE status=1");
    4299   my $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
     4308  my $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id,location ".
    43004309        "FROM records WHERE domain_id=? AND type < 65280");     # Just exclude all types relating to rDNS
    4301   my $zonesth = $dbh->prepare("UPDATE domains SET changed=0 WHERE domain_id=?");
     4310  my $zonesth = $dbh->prepare("UPDATE domains SET changed='n' WHERE domain_id=?");
    43024311  $domsth->execute();
    43034312  while (my ($domid,$dom,$domstat,$changed) = $domsth->fetchrow_array) {
     
    43124321    if ($changed || -s "$config{exportcache}/$dom" == 0) {
    43134322      $recsth->execute($domid);
    4314       while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
     4323      while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid,$loc) = $recsth->fetchrow_array) {
    43154324        next if $recflags{$recid};
    4316 ##fixme:  need to store location in the db, and retrieve it here.
    4317 # temporarily hardcoded to empty so we can include it further down.
    4318         my $loc = '';
     4325
     4326        $loc = '' if !$loc;     # de-nullify - just in case
     4327##fixme:  handle case of record-with-location-that-doesn't-exist better.
     4328# note this currently fails safe (tested) - records with a location that
     4329# doesn't exist will not be sent to any client
     4330#       $loc = '' if !$lochash->{$loc};
    43194331
    43204332##fixme:  record validity timestamp. tinydns supports fiddling with timestamps.
     
    43494361  my $revsth = $dbh->prepare("SELECT rdns_id,revnet,status,changed FROM revzones WHERE status=1 ".
    43504362        "ORDER BY masklen(revnet) DESC");
     4363
    43514364# For reasons unknown, we can't sanely UNION these statements.  Feh.
    43524365# Supposedly it should work though (note last 3 lines):
     
    43614374#and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these
    43624375#clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)
    4363 
    4364   my $soasth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
     4376  my $soasth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id,location ".
    43654377        "FROM records WHERE rdns_id=? AND type=6");
    4366   $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
     4378  $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id,location ".
    43674379        "FROM records WHERE rdns_id=? AND not type=6 ".
    43684380        "ORDER BY masklen(CAST(val AS inet)) DESC, CAST(val AS inet)");
    4369   $zonesth = $dbh->prepare("UPDATE revzones SET changed=0 WHERE rdns_id=?");
     4381  $zonesth = $dbh->prepare("UPDATE revzones SET changed='n' WHERE rdns_id=?");
    43704382  $revsth->execute();
    43714383  while (my ($revid,$revzone,$revstat,$changed) = $revsth->fetchrow_array) {
     
    43844396      my (@zsoa) = $soasth->fetchrow_array();
    43854397      _printrec_tiny($datafile,'y',\%recflags,$revzone,
    4386         $zsoa[0],$zsoa[1],$zsoa[2],$zsoa[3],$zsoa[4],$zsoa[5],$zsoa[6],'','');
     4398        $zsoa[0],$zsoa[1],$zsoa[2],$zsoa[3],$zsoa[4],$zsoa[5],$zsoa[6],$zsoa[8],'');
    43874399
    43884400      $recsth->execute($revid);
    4389       while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
     4401      while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid,$loc) = $recsth->fetchrow_array) {
    43904402        next if $recflags{$recid};
    4391 ##fixme:  need to store location in the db, and retrieve it here.
    4392 # temporarily hardcoded to empty so we can include it further down.
    4393         my $loc = '';
     4403
     4404        $loc = '' if !$loc;     # de-nullify - just in case
     4405##fixme:  handle case of record-with-location-that-doesn't-exist better.
     4406# note this currently fails safe (tested) - records with a location that
     4407# doesn't exist will not be sent to any client
     4408#       $loc = '' if !$lochash->{$loc};
    43944409
    43954410##fixme:  record validity timestamp. tinydns supports fiddling with timestamps.
     
    45874602      } elsif ($typemap{$type} eq 'CNAME') {
    45884603
    4589         print $datafile "C$host:$val:$ttl:$stamp:$loc\n";
     4604        if ($revrec eq 'n') {
     4605          print $datafile "C$host:$val:$ttl:$stamp:$loc\n";
     4606        } else {
     4607          my $val2 = NetAddr::IP->new($val);
     4608          print $datafile "C"._ZONE($val2, 'ZONE', 'r', '.').($val2->{isv6} ? '.ip6.arpa' : '.in-addr.arpa').
     4609                ":$host:$ttl:$stamp:$loc\n";
     4610        }
    45904611
    45914612      } elsif ($typemap{$type} eq 'SRV') {
  • trunk/dns-1.0-1.2.sql

    r370 r372  
    33-- need this before we add any other bits
    44CREATE TABLE locations (
    5     loc character varying (4) PRIMARY KEY,
     5    location character varying (4) PRIMARY KEY,
    66    group_id integer NOT NULL DEFAULT 1,
    77    iplist text NOT NULL DEFAULT '',
  • trunk/templates/loclist.tmpl

    r370 r372  
    2222<td class="rightthird"><TMPL_INCLUDE NAME="sbox.tmpl"></td>
    2323</tr>
    24 <tr><td colspan="3" align="center"><TMPL_INCLUDE NAME="lettsearch.tmpl"></td></tr>
    2524<TMPL_IF addloc>
    2625<tr><td colspan="3" align="right"><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=loc">New Location/View</a></td></tr>
     
    3736<TMPL_LOOP name=loctable>
    3837<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
    39         <td align="left"><TMPL_IF edloc><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=location&amp;loc_action=edit&amp;loc=<TMPL_VAR NAME=loc>"><TMPL_VAR NAME=description></a><TMPL_ELSE><TMPL_VAR NAME=description></TMPL_IF></td>
     38        <td align="left"><TMPL_IF edloc><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=location&amp;loc_action=edit&amp;loc=<TMPL_VAR NAME=location>"><TMPL_VAR NAME=description></a><TMPL_ELSE><TMPL_VAR NAME=description></TMPL_IF></td>
    4039        <td><TMPL_VAR name=iplist></td>
    4140        <td><TMPL_VAR name=group_name></td>
  • trunk/tiny-import.pl

    r363 r372  
    6868  open FLAT, "<$flatfile";
    6969
    70   our $recsth = $dbh->prepare("INSERT INTO records (domain_id,rdns_id,host,type,val,distance,weight,port,ttl) ".
    71         " VALUES (?,?,?,?,?,?,?,?,?)");
     70  our $recsth = $dbh->prepare("INSERT INTO records (domain_id,rdns_id,host,type,val,distance,weight,port,ttl,location) ".
     71        " VALUES (?,?,?,?,?,?,?,?,?,?)");
    7272
    7373  my %deleg;
     
    7777    next if /^\s*$/;
    7878    chomp;
     79    s/\s*$//;
    7980    recslurp($_);
    8081  }
     
    187188      my ($rparent) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet >> ?", undef, ($ip));
    188189      if ($fparent && $rparent) {
    189         $recsth->execute($fparent, $rparent, $host, 65280, $ip, 0, 0, 0, $ttl);
     190        $recsth->execute($fparent, $rparent, $host, 65280, $ip, 0, 0, 0, $ttl, $loc);
    190191      } else {
    191192        push @deferred, $rec unless $nodefer;
     
    207208        ($code,$msg) = DNSDB::_zone2cidr($host);
    208209        my ($rparent) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet >> ?", undef, ($msg));
    209         $recsth->execute(0, $rparent, $targ, 5, $msg->addr, 0, 0, 0, $ttl);
     210        $recsth->execute(0, $rparent, $targ, 5, $msg->addr, 0, 0, 0, $ttl, $loc);
    210211
    211212##fixme:  automagically convert manually maintained sub-/24 delegations
     
    218219        my $fparent = DNSDB::_hostparent($dbh, $host);
    219220        if ($fparent) {
    220           $recsth->execute($fparent, 0, $host, 5, $targ, 0, 0, 0, $ttl);
     221          $recsth->execute($fparent, 0, $host, 5, $targ, 0, 0, 0, $ttl, $loc);
    221222        } else {
    222223          push @deferred, $rec unless $nodefer;
     
    245246#       if !$rparent;
    246247        if ($rparent) {
    247           $recsth->execute(0, $rparent, $ns, 2, $msg, 0, 0, 0, $ttl);
     248          $recsth->execute(0, $rparent, $ns, 2, $msg, 0, 0, 0, $ttl, $loc);
    248249        } else {
    249250          push @deferred, $rec unless $nodefer;
     
    252253        my $fparent = DNSDB::_hostparent($dbh, $zone);
    253254        if ($fparent) {
    254           $recsth->execute($fparent, 0, $zone, 2, $ns, 0, 0, 0, $ttl);
    255           $recsth->execute($fparent, 0, $ns, 2, $ip, 0, 0, 0, $ttl) if $ip;
     255          $recsth->execute($fparent, 0, $zone, 2, $ns, 0, 0, 0, $ttl, $loc);
     256          $recsth->execute($fparent, 0, $ns, 2, $ip, 0, 0, 0, $ttl, $loc) if $ip;
    256257        } else {
    257258          push @deferred, $rec unless $nodefer;
     
    283284      }
    284285      if ($rparent) {
    285         $recsth->execute(0, $rparent, $host, 12, $msg->addr, 0, 0, 0, $ttl);
     286        $recsth->execute(0, $rparent, $host, 12, $msg->addr, 0, 0, 0, $ttl, $loc);
    286287      } else {
    287288        push @deferred, $rec unless $nodefer;
     
    302303      my $domid = DNSDB::_hostparent($dbh, $host);
    303304      if ($domid) {
    304         $recsth->execute($domid, 0, $host, 1, $ip, 0, 0, 0, $ttl);
     305        $recsth->execute($domid, 0, $host, 1, $ip, 0, 0, 0, $ttl, $loc);
    305306      } else {
    306307        push @deferred, $rec unless $nodefer;
     
    323324        $dbh->do("INSERT INTO revzones (revnet,group_id,status) VALUES (?,1,1)", undef, ($msg));
    324325        my ($rdns) = $dbh->selectrow_array("SELECT currval('revzones_rdns_id_seq')");
    325         $recsth->execute(0, $rdns, "$contact:$master", 6, "$refresh:$retry:$expire:$minttl", 0, 0, 0, $ttl);
     326        $recsth->execute(0, $rdns, "$contact:$master", 6, "$refresh:$retry:$expire:$minttl", 0, 0, 0, $ttl, $loc);
    326327      } else {
    327328        $dbh->do("INSERT INTO domains (domain,group_id,status) VALUES (?,1,1)", undef, ($zone));
    328329        my ($domid) = $dbh->selectrow_array("SELECT currval('domains_domain_id_seq')");
    329         $recsth->execute($domid, 0, "$contact:$master", 6, "$refresh:$retry:$expire:$minttl", 0, 0, 0, $ttl);
     330        $recsth->execute($domid, 0, "$contact:$master", 6, "$refresh:$retry:$expire:$minttl", 0, 0, 0, $ttl, $loc);
    330331      }
    331332
     
    350351      my $domid = DNSDB::_hostparent($dbh, $zone);
    351352      if ($domid) {
    352         $recsth->execute($domid, 0, $zone, 15, $host, $dist, 0, 0, $ttl);
    353         $recsth->execute($domid, 0, $host, 1, $ip, 0, 0, 0, $ttl) if $ip;
     353        $recsth->execute($domid, 0, $zone, 15, $host, $dist, 0, 0, $ttl, $loc);
     354        $recsth->execute($domid, 0, $host, 1, $ip, 0, 0, 0, $ttl, $loc) if $ip;
    354355      } else {
    355356        push @deferred, $rec unless $nodefer;
     
    371372        ($code,$msg) = DNSDB::_zone2cidr($fqdn);
    372373        my ($rparent) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet >> ?", undef, ($msg));
    373         $recsth->execute(0, $rparent, $rdata, 16, "$msg", 0, 0, 0, $ttl);
     374        $recsth->execute(0, $rparent, $rdata, 16, "$msg", 0, 0, 0, $ttl, $loc);
    374375      } else {
    375376        my $domid = DNSDB::_hostparent($dbh, $fqdn);
    376377        if ($domid) {
    377           $recsth->execute($domid, 0, $fqdn, 16, $rdata, 0, 0, 0, $ttl);
     378          $recsth->execute($domid, 0, $fqdn, 16, $rdata, 0, 0, 0, $ttl, $loc);
    378379        } else {
    379380          push @deferred, $rec unless $nodefer;
     
    402403          ($rdns) = $dbh->selectrow_array("SELECT currval('revzones_rdns_id_seq')");
    403404# this would probably make a lot more sense to do hostmaster.$config{admindomain}
    404           $recsth->execute(0, $rdns, "hostmaster.$fqdn:$ns", 6, "16384:2048:1048576:2560", 0, 0, 0, "2560");
    405         }
    406         $recsth->execute(0, $rdns, $ns, 2, "$msg", 0, 0, 0, $ttl);
     405          $recsth->execute(0, $rdns, "hostmaster.$fqdn:$ns", 6, "16384:2048:1048576:2560", 0, 0, 0, "2560", $loc);
     406        }
     407        $recsth->execute(0, $rdns, $ns, 2, "$msg", 0, 0, 0, $ttl, $loc);
    407408##fixme:  (?)  implement full conversion of tinydns . records?
    408409# -> problem:  A record for NS must be added to the appropriate *forward* zone, not the reverse
     
    420421          $dbh->do("INSERT INTO domains (domain,group_id,status) VALUES (?,1,1)", undef, ($fqdn));
    421422          ($domid) = $dbh->selectrow_array("SELECT currval('domains_domain_id_seq')");
    422           $recsth->execute($domid, 0, "hostmaster.$fqdn:$ns", 6, "16384:2048:1048576:2560", 0, 0, 0, "2560");
    423         }
    424         $recsth->execute($domid, 0, $fqdn, 2, $ns, 0, 0, 0, $ttl);
    425         $recsth->execute($domid, 0, $ns, 1, $ip, 0, 0, 0, $ttl) if $ip;
     423          $recsth->execute($domid, 0, "hostmaster.$fqdn:$ns", 6, "16384:2048:1048576:2560", 0, 0, 0, "2560", $loc);
     424        }
     425        $recsth->execute($domid, 0, $fqdn, 2, $ns, 0, 0, 0, $ttl, $loc);
     426        $recsth->execute($domid, 0, $ns, 1, $ip, 0, 0, 0, $ttl, $loc) if $ip;
    426427      }
    427428
     
    429430    } elsif ($rec =~ /^\%/) {
    430431      $cnt{VIEWS}++;
     432
     433      # unfortunate that we don't have a guaranteed way to get a description on these.  :/
     434      my ($loc,$cnet) = split /:/, $rec, 2;
     435      $loc =~ s/^\%//;
     436      if (my ($iplist) = $dbh->selectrow_array("SELECT iplist FROM locations WHERE location = ?", undef, ($loc))) {
     437        if ($cnet) {
     438          $iplist .= ",$cnet";
     439          $dbh->do("UPDATE locations SET iplist = ? WHERE location = ?", undef, ($iplist, $loc));
     440        } else {
     441          # hmm.  spit out a warning?  if we already have entries for $loc, adding a null
     442          # entry will almost certainly Do The Wrong Thing(TM)
     443        }
     444      } else {
     445        $cnet = '' if !$cnet;   # de-nullify
     446        $dbh->do("INSERT INTO locations (location,iplist,description) VALUES (?,?,?)", undef, ($loc, $cnet, $loc));
     447      }
    431448
    432449    } elsif ($rec =~ /^:/) {
     
    471488        my $domid = DNSDB::_hostparent($dbh, $fqdn);
    472489        if ($domid) {
    473           $recsth->execute($domid, 0, $fqdn, 33, $target, $prio, $weight, $port, $ttl) if $domid;
     490          $recsth->execute($domid, 0, $fqdn, 33, $target, $prio, $weight, $port, $ttl, $loc) if $domid;
    474491        } else {
    475492          push @deferred, $rec unless $nodefer;
     
    488505        my $fparent = DNSDB::_hostparent($dbh, $fqdn);
    489506        if ($fparent) {
    490           $recsth->execute($fparent, 0, $fqdn, 28, $val->addr, 0, 0, 0, $ttl);
     507          $recsth->execute($fparent, 0, $fqdn, 28, $val->addr, 0, 0, 0, $ttl, $loc);
    491508        } else {
    492509          push @deferred, $rec unless $nodefer;
     
    501518          my ($rparent) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet >> ?", undef, ($msg));
    502519          if ($rparent) {
    503             $recsth->execute(0, $rparent, $txtstring, 16, "$msg", 0, 0, 0, $ttl);
     520            $recsth->execute(0, $rparent, $txtstring, 16, "$msg", 0, 0, 0, $ttl, $loc);
    504521          } else {
    505522            push @deferred, $rec unless $nodefer;
     
    508525          my $domid = DNSDB::_hostparent($dbh, $fqdn);
    509526          if ($domid) {
    510             $recsth->execute($domid, 0, $fqdn, 16, $txtstring, 0, 0, 0, $ttl);
     527            $recsth->execute($domid, 0, $fqdn, 16, $txtstring, 0, 0, 0, $ttl, $loc);
    511528          } else {
    512529            push @deferred, $rec unless $nodefer;
     
    527544          my ($rparent) = $dbh->selectrow_array("SELECT rdns_id FROM revzones WHERE revnet >> ?", undef, ($msg));
    528545          if ($rparent) {
    529             $recsth->execute(0, $rparent, "$email $txtrec", 17, "$msg", 0, 0, 0, $ttl);
     546            $recsth->execute(0, $rparent, "$email $txtrec", 17, "$msg", 0, 0, 0, $ttl, $loc);
    530547          } else {
    531548            push @deferred, $rec unless $nodefer;
     
    534551          my $domid = DNSDB::_hostparent($dbh, $fqdn);
    535552          if ($domid) {
    536             $recsth->execute($domid, 0, $fqdn, 17, "$email $txtrec", 0, 0, 0, $ttl);
     553            $recsth->execute($domid, 0, $fqdn, 17, "$email $txtrec", 0, 0, 0, $ttl, $loc);
    537554          } else {
    538555            push @deferred, $rec unless $nodefer;
     
    549566        my $domid = DNSDB::_hostparent($dbh, $fqdn);
    550567        if ($domid) {
    551           $recsth->execute($domid, 0, $fqdn, 44, $sshfp, 0, 0, 0, $ttl);
     568          $recsth->execute($domid, 0, $fqdn, 44, $sshfp, 0, 0, 0, $ttl, $loc);
    552569        } else {
    553570          push @deferred, $rec unless $nodefer;
Note: See TracChangeset for help on using the changeset viewer.