Changeset 90 for trunk/DNSDB.pm

Timestamp:

04/12/11 14:46:22 (13 years ago)

Author:

Kris Deugau

Message:

/trunk

Checkpoint

• Comment-docu-tweak on addUser()
• Disallow blank usernames on creation
• Add docucomment on updateUser()
• Update getRecLine() to handle long records, switch to more compact $dbh->do() instead of explicit prepare/execute/fetch
• Update getDomRecs() to handle long records. Clean up SQL formatting.
• Update addRec() to handle long records
• Update updateRec() to handle long records
• Add fixme reminder to handle VegaDNS encrypted passwords (hex-coded MD5)
• Use getRecData() instead of local SQL for "Edit record" page
• Log user add and update actions
• Remember to set $permissions{admin} to 1 on update if user type is "superuser"
• Uncomment page=deluser segment; this was not integrated with the rest of the user add/update pages
• HTML-comment "Customer ID" (uid) on log pages; having trouble seeing a use-case
• Nitpick tweaks on record add/edit page
• Use HTML::Template's HTML-escaping on the record value on record add/edit page - required for eg SPF records with quotation marks

File:

• trunk/DNSDB.pm (modified) (7 diffs)

trunk/DNSDB.pm

@@ -690 +690 @@
 #   last name                   - defaults to blank
 #   phone                       - defaults to blank (could put other data within column def)
-# Returns (OK,OK) on success, (FAIL,<message>) on failure
+# Returns (OK,<uid>) on success, (FAIL,<message>) on failure
 sub addUser {
   $errstr = '';
@@ -699 +699 @@
   my $state = shift;
 
-  return ('FAIL',"Missing one or more required entries") if !defined($state);
+  return ('FAIL', "Missing one or more required entries") if !defined($state);
+  return ('FAIL', "Username must not be blank") if !$username;
 
   my $type = shift || 'u';      # create limited users by default - fwiw, not sure yet how this will interact with ACLs
@@ -816 +817 @@
 
 ## DNSDB:: updateUser()
-#
+# Update general data about user
 sub updateUser {
   my $dbh = shift;
@@ -1020 +1021 @@
   my $id = shift;
 
-  my $sql = "SELECT record_id,host,type,val,distance,weight,port,ttl".
-        (($def eq 'def' or $def eq 'y') ? ',group_id FROM default_' : ',domain_id FROM ').
-        "records WHERE record_id=?";
-  my $sth = $dbh->prepare($sql);
-  $sth->execute($id);
-
-  my ($recid,$host,$rtype,$val,$distance,$weight,$port,$ttl,$parid) = $sth->fetchrow_array();
-
-  if ($sth->err) {
+  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".
+        (($def eq 'def' or $def eq 'y') ? ',r.group_id FROM default_' : ',r.domain_id FROM ').
+        "records r LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id WHERE record_id=?";
+  my $ret = $dbh->selectrow_hashref($sql, undef, ($id) ) or warn $dbh->errstr;
+
+  if ($dbh->err) {
     $errstr = $DBI::errstr;
     return undef;
   }
-  my %ret;
-  $ret{recid}   = $recid;
-  $ret{host}    = $host;
-  $ret{type}    = $rtype;
-  $ret{val}     = $val;
-  $ret{distance}= $distance;
-  $ret{weight}  = $weight;
-  $ret{port}    = $port;
-  $ret{ttl}     = $ttl;
-  $ret{parid}   = $parid;
-
-  return %ret;
+
+  $ret->{val} = $ret->{recdata} if $ret->{longrec_id};  # put the long data in the real value space
+  delete $ret->{longrec_id};    # remove these since they shouldn't be exposed - the caller
+  delete $ret->{recdata};       # should not care about "long records" vs normal ones.
+
+  return $ret;
 }
 
@@ -1065 +1057 @@
   my $direction = shift || 'ASC';
 
-  my $sql = "SELECT record_id,host,type,val,distance,weight,port,ttl FROM ";
-  if ($type eq 'def' or $type eq 'y') {
-    $sql .= " default_records where group_id=$id";
+  $type = 'y' if $type eq 'def';
+
+  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 ";
+  $sql .= "default_" if $type eq 'y';
+  $sql .= "records r ";
+  $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
+  if ($type eq 'y') {
+    $sql .= "WHERE r.group_id=?";
   } else {
-    $sql .= " records where domain_id=$id";
-  }
-  $sql .= " and not type=$reverse_typemap{SOA} order by $order $direction";
+    $sql .= "WHERE r.domain_id=?";
+  }
+  $sql .= " AND NOT r.type=$reverse_typemap{SOA} ORDER BY r.$order $direction";
 ##fixme:  need to set nstart properly (offset is not internally multiplied with limit)
-  $sql .= " limit $nrecs offset ".($nstart*$nrecs) if $nstart ne 'all';
-
-  my $sth = $dbh->prepare($sql);
-  $sth->execute;
+  $sql .= " LIMIT $nrecs OFFSET ".($nstart*$nrecs) if $nstart ne 'all';
+
+  my $sth = $dbh->prepare($sql) or warn $dbh->errstr;
+  $sth->execute($id) or warn "$sql: ".$sth->errstr;
 
   my @retbase;
   while (my $ref = $sth->fetchrow_hashref()) {
+    $ref->{val} = $ref->{recdata} if $ref->{longrec_id};        # put the long data in the real value space
+    delete $ref->{longrec_id};  # remove these since they shouldn't be exposed - the caller
+    delete $ref->{recdata};     # should not care about "long records" vs normal ones.
     push @retbase, $ref;
   }
@@ -1133 +1133 @@
   }
 
-  my $sql = "insert into ".($defrec eq 'y' ? 'default_' : '')."records ($fields) values ($vallen)";
-##fixme: use array for values, replace "vallist" with series of ?,?,? etc
-# something is bugging me about this...
-#warn "DEBUG: $sql";
-  my $sth = $dbh->prepare($sql);
-  $sth->execute(@vallist);
-
-  return ('FAIL',$sth->errstr) if $sth->err;
+  # Allow transactions, and raise an exception on errors so we can catch it later.
+  # Use local to make sure these get "reset" properly on exiting this block
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
+  eval {
+    if (length($val) > 100 ) {
+      # extralong records get an entry in a separate table.
+      $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
+      my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM longrecs WHERE recdata=?", undef, ($val) );
+      $fields .= ",longrec_id";
+      $vallen .= ",?";
+      push @vallist, $longid;
+      $vallist[3] = ''; # so we don't barf when we insert the main record
+    }
+    $dbh->do("INSERT INTO ".($defrec eq 'y' ? 'default_' : '')."records ($fields) VALUES ($vallen)",
+        undef, @vallist);
+    $dbh->commit;
+  };
+  if ($@) {
+    my $msg = $@;
+    eval { $dbh->rollback; };
+    return ('FAIL',$msg);
+  }
 
   return ('OK','OK');
+
 } # end addRec()
 
@@ -1179 +1196 @@
   }
 
-  my $sth = $dbh->prepare("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records ".
-        "SET host=?,type=?,val=?,ttl=?,distance=?,weight=?,port=? ".
-        "WHERE record_id=?");
-  $sth->execute($host,$type,$val,$ttl,$dist,$weight,$port,$id);
-
-  return ('FAIL',$sth->errstr."<br>\n$errstr<br>\n") if $sth->err;
+#  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 ";
+#  $sql .= "default_" if $type eq 'y';
+#  $sql .= "records r ";
+#  $sql .= "LEFT OUTER JOIN longrecs l ON r.longrec_id=l.longrec_id ";
+
+  # get the long record ID, if any
+  my ($longid) = $dbh->selectrow_array("SELECT longrec_id FROM ".($defrec eq 'y' ? 'default_' : '')."records ".
+        "WHERE record_id=?", undef, ($id) );
+
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
+  eval {
+    # there's really no tidy way to squash this down.  :/
+    if (length($val) > 100) {
+      if ($longid) {
+        $dbh->do("UPDATE longrecs SET recdata=? WHERE longrec_id=?", undef, ($val, $longid) );
+      } else {
+##fixme:  has to be a better way to be sure we get the right recid back once inserted...
+        $dbh->do("INSERT INTO longrecs (recdata) VALUES (?)", undef, ($val) );
+        my ($newlongid) = $dbh->selectrow_array("SELECT currval('longrecs_longrec_id_seq')");
+        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=? ".
+                "WHERE record_id=?", undef, ('', $newlongid, $id) );
+      }
+    } else {
+      if ($longid) {
+        $dbh->do("DELETE FROM longrecs WHERE longrec_id=?", undef, ($longid) );
+        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=?,longrec_id=NULL ".
+                "WHERE record_id=?", undef, ($val, $id) );
+      } else {
+        $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records SET val=? ".
+                "WHERE record_id=?", undef, ($val, $id) );
+      }
+    }
+
+    $dbh->do("UPDATE ".($defrec eq 'y' ? 'default_' : '')."records ".
+        "SET host=?,type=?,ttl=?,distance=?,weight=?,port=? ".
+        "WHERE record_id=?", undef, ($host, $type, $ttl, $dist, $weight, $port, $id) );
+
+  };
+  if ($@) {
+    my $msg = $@;
+    $dbh->rollback;
+    return ('FAIL', $msg);
+  }
+#  return ('FAIL',$sth->errstr."<br>\n$errstr<br>\n") if $sth->err;
 
   return ('OK','OK');