Changeset 130 for trunk


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.

Location:
trunk
Files:
2 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

  • trunk/dns.sql

    r102 r130  
    88CREATE TABLE default_records (
    99    record_id serial NOT NULL,
    10     longrec_id integer,
    1110    group_id integer DEFAULT 1 NOT NULL,
    12     host character varying(100) DEFAULT ''::character varying NOT NULL,
     11    host text DEFAULT '' NOT NULL,
    1312    "type" integer DEFAULT 1 NOT NULL,
    14     val character varying(100) DEFAULT ''::character varying NOT NULL,
     13    val text DEFAULT '' NOT NULL,
    1514    distance integer DEFAULT 0 NOT NULL,
    1615    weight integer DEFAULT 0 NOT NULL,
    1716    port integer DEFAULT 0 NOT NULL,
    1817    ttl integer DEFAULT 86400 NOT NULL,
    19     description character varying(255)
     18    description text
    2019);
    2120
    … …  
    9796    domain_id integer NOT NULL,
    9897    record_id serial NOT NULL,
    99     longrec_id integer,
    100     host character varying(100) DEFAULT ''::character varying NOT NULL,
     98    host text DEFAULT '' NOT NULL,
    10199    "type" integer DEFAULT 1 NOT NULL,
    102     val character varying(100) DEFAULT ''::character varying NOT NULL,
     100    val text DEFAULT '' NOT NULL,
    103101    distance integer DEFAULT 0 NOT NULL,
    104102    weight integer DEFAULT 0 NOT NULL,
    105103    port integer DEFAULT 0 NOT NULL,
    106104    ttl integer DEFAULT 7200 NOT NULL,
    107     description character varying(255)
    108 );
    109 
    110 CREATE TABLE longrecs (
    111     longrec_id serial NOT NULL,
    112     recdata text
     105    description text
    113106);
    114107
Note: See TracChangeset for help on using the changeset viewer.