Changeset 130 for trunk/DNSDB.pm


Ignore:
Timestamp:
10/04/11 10:34:18 (13 years ago)
Author:
Kris Deugau
Message:

/trunk

Duhhhhh... "text" fields don't have any performance penalty
over varchar or char. Rip out the longrecs business in favour
of much simpler record tables. Reverts addition of same from
r90.

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r129 r130  
    11701170  my $id = shift;
    11711171
    1172   my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata".
    1173         (($def eq 'def' or $def eq 'y') ? ',r.group_id FROM default_' : ',r.domain_id FROM ').
    1174         "records r LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id WHERE record_id=?";
     1172  my $sql = "SELECT record_id,host,type,val,distance,weight,port,ttl".
     1173        (($def eq 'def' or $def eq 'y') ? ',group_id FROM default_' : ',domain_id FROM ').
     1174        "records WHERE record_id=?";
    11751175  my $ret = $dbh->selectrow_hashref($sql, undef, ($id) );
    11761176
    … …  
    11851185  }
    11861186
    1187   $ret->{val} = $ret->{recdata} if $ret->{longrec_id};  # put the long data in the real value space
    1188   delete $ret->{longrec_id};    # remove these since they shouldn't be exposed - the caller
    1189   delete $ret->{recdata};       # should not care about "long records" vs normal ones.
    11901187  $ret->{parid} = (($def eq 'def' or $def eq 'y') ? $ret->{group_id} : $ret->{domain_id});
    11911188
    … …  
    12141211  $type = 'y' if $type eq 'def';
    12151212
    1216   my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata FROM ";
     1213  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl FROM ";
    12171214  $sql .= "default_" if $type eq 'y';
    12181215  $sql .= "records r ";
    12191216  $sql .= "INNER JOIN rectypes t ON r.type=t.val ";     # for sorting by type alphabetically
    1220   $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
    12211217  if ($type eq 'y') {
    12221218    $sql .= "WHERE r.group_id=?";
    … …  
    12341230  my @retbase;
    12351231  while (my $ref = $sth->fetchrow_hashref()) {
    1236     $ref->{val} = $ref->{recdata} if $ref->{longrec_id};        # put the long data in the real value space
    1237     delete $ref->{longrec_id};  # remove these since they shouldn't be exposed - the caller
    1238     delete $ref->{recdata};     # should not care about "long records" vs normal ones.
    12391232    push @retbase, $ref;
    12401233  }
    … …  
    13211314
    13221315  eval {
    1323     if (length($val) > 100 ) {
    1324       # extralong records get an entry in a separate table.
    1325       $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
    1326       my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($val) );
    1327       $fields .= ",longrec_id";
    1328       $vallen .= ",?";
    1329       push @vallist, $longid;
    1330       $vallist[3] = ''; # so we don't barf when we insert the main record
    1331     }
    13321316    $dbh->do("INSERT INTO ".($defrec eq 'y' ? 'default_' : '')."records ($fields) VALUES ($vallen)",
    13331317        undef, @vallist);
    … …  
    13781362  }
    13791363
    1380 #  my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,r.longrec_id,l.recdata FROM ";
    1381 #  $sql .= "default_" if $type eq 'y';
    1382 #  $sql .= "records r ";
    1383 #  $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
    1384 
    1385   # get the long record ID, if any
    1386   my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM ".($defrec eq 'y' ? 'default_' : '')."records ".
    1387         "WHERE record_id=?", undef, ($id) );
    1388 
    13891364  local $dbh->{AutoCommit} = 0;
    13901365  local $dbh->{RaiseError} = 1;
    13911366
    13921367  eval {
    1393     # there's really no tidy way to squash this down.  :/
    1394     if (length($val) > 100) {
    1395       if ($longid) {
    1396         $dbh->do("UPDATE longrecs SET recdata=? WHERE longrec_id=?", undef, ($val, $longid) );
    1397       } else {
    1398 ##fixme:  has to be a better way to be sure we get the right recid back once inserted...
    1399         $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
    1400         my ($newlongid) = $dbh->selectrow_array("SELECT currval('longrecs_longrec_id_seq')");
    1401         $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=? ".
    1402                 "WHERE record_id=?", undef, ('', $newlongid, $id) );
    1403       }
    1404     } else {
    1405       if ($longid) {
    1406         $dbh->do("DELETE FROM longrecs WHERE longrec_id=?", undef, ($longid) );
    1407         $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=NULL ".
    1408                 "WHERE record_id=?", undef, ($val, $id) );
    1409       } else {
    1410         $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=? ".
    1411                 "WHERE record_id=?", undef, ($val, $id) );
    1412       }
    1413     }
    1414 
    14151368    $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records ".
    1416         "SET host=?,type=?,ttl=?,distance=?,weight=?,port=? ".
    1417         "WHERE record_id=?", undef, ($host, $type, $ttl, $dist, $weight, $port, $id) );
    1418 
     1369        "SET host=?,val=?,type=?,ttl=?,distance=?,weight=?,port=? ".
     1370        "WHERE record_id=?", undef, ($host, $val, $type, $ttl, $dist, $weight, $port, $id) );
     1371    $dbh->commit;
    14191372  };
    14201373  if ($@) {
    … …  
    14231376    return ('FAIL', $msg);
    14241377  }
    1425 #  return ('FAIL',$sth->errstr."<br>\n$errstr<br>\n") if $sth->err;
    14261378
    14271379  return ('OK','OK');
    … …  
    16911643## but don't really seem enthusiastic about it.
    16921644        my $rrdata = $rr->txtdata;
    1693         if (length($rrdata) > 100 ) {
    1694           # extralong records get an entry in a separate table.
    1695           $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($rrdata) );
    1696           my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($rrdata) );
    1697           $sql .= ",longrec_id";
    1698           $vallen .= ",?";
    1699           push @vallist, '';
    1700           push @vallist, $longid;
    1701         } else {
    1702           push @vallist, $rrdata;
    1703         }
     1645        push @vallist, $rrdata;
    17041646      } elsif ($type eq 'SPF') {
    17051647##fixme: and the same caveat here, since it is apparently a clone of ::TXT
    17061648        my $rrdata = $rr->txtdata;
    1707         if (length($rrdata) > 100 ) {
    1708           # extralong records get an entry in a separate table.
    1709           $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($rrdata) );
    1710           my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($rrdata) );
    1711           $sql .= ",longrec_id";
    1712           $vallen .= ",?";
    1713           push @vallist, '';
    1714           push @vallist, $longid;
    1715         } else {
    1716           push @vallist, $rrdata;
    1717         }
     1649        push @vallist, $rrdata;
    17181650      } elsif ($type eq 'AAAA') {
    17191651        push @vallist, $rr->address;
    … …  
    17301662      } else {
    17311663        my $rrdata = $rr->rdatastr;
    1732         if (length($rrdata) > 100 ) {
    1733           # extralong records get an entry in a separate table.
    1734           $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($rrdata) );
    1735           my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($rrdata) );
    1736           $sql .= ",longrec_id";
    1737           $vallen .= ",?";
    1738           push @vallist, '';
    1739           push @vallist, $longid;
    1740         } else {
    1741           push @vallist, $rrdata;
    1742         }
     1664        push @vallist, $rrdata;
    17431665        # Finding a different record type is not fatal.... just problematic.
    17441666        # We may not be able to export it correctly.
    … …  
    18581780
    18591781  my $domsth = $dbh->prepare("SELECT domain_id,domain,status FROM domains WHERE status=1");
    1860   my $recsth = $dbh->prepare("SELECT r.host,r.type,r.val,r.distance,r.weight,r.port,r.ttl,l.recdata ".
    1861         "FROM records r LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id WHERE domain_id=?");
     1782  my $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl ".
     1783        "FROM records WHERE domain_id=?");
    18621784  $domsth->execute();
    18631785  while (my ($domid,$dom,$domstat) = $domsth->fetchrow_array) {
    18641786    $recsth->execute($domid);
    1865     while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$lval) = $recsth->fetchrow_array) {
     1787    while (my ($host,$type,$val,$dist,$weight,$port,$ttl) = $recsth->fetchrow_array) {
    18661788##fixme:  need to store location in the db, and retrieve it here.
    18671789# temporarily hardcoded to empty so we can include it further down.
    … …  
    18731795# ~~ 2^62 + time()
    18741796my $stamp = '';
    1875 
    1876       $val = $lval if $lval;
    18771797
    18781798# raw packet in unknown format:  first byte indicates length
Note: See TracChangeset for help on using the changeset viewer.