Changeset 397 for trunk/DNSDB.pm

Timestamp:

10/02/12 18:15:38 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Caught some inconsistencies getting the RPC CGI updated.

• Normalize to use of odd in record list, same as other lists. Also normalize which case is which in templates so all lists look consistent.
• Normalize calling convention and database calls in list-getting subs
  • getZoneList, with light tweak to template for field name
  • getDomRecs, with conversion from fixed arguments to hash
• Normalize checks on list offsets to the best one, from getZoneList

File:

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

trunk/DNSDB.pm

@@ -2174 +2174 @@
         ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage}".
         " OFFSET ".$args{offset}*$config{perpage});
-  my $sth = $dbh->prepare($sql);
-  $sth->execute(@filterargs);
-  my $rownum = 0;
-
-  while (my @data = $sth->fetchrow_array) {
-    my %row;
-    $row{domainid} = $data[0];
-    $row{domain} = $data[1];
-    $row{status} = $data[2];
-    $row{group} = $data[3];
-    push @zonelist, \%row;
-  }
-
-  return \@zonelist;
+
+  my $ret = $dbh->selectall_arrayref($sql, { Slice => {} }, (@filterargs) );
+  return $ret;
 } # end getZoneList()
 
@@ -2467 +2456 @@
   # protection against bad or missing arguments
   $args{sortorder} = 'ASC' if !$args{sortorder};
-  $args{offset} = 0 if !$args{offset};
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
 
   # munge sortby for columns in database
@@ -2693 +2682 @@
   $args{sortorder} = 'ASC' if !$args{sortorder};
   $args{sortby} = 'u.username' if !$args{sortby};
-  $args{offset} = 0 if !$args{offset};
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
 
   my $sql = "SELECT u.user_id, u.username, u.firstname || ' ' || u.lastname AS fname, u.type, g.group_name, u.status ".
@@ -3123 +3112 @@
   $args{sortorder} = 'ASC' if !$args{sortorder};
   $args{sortby} = 'l.description' if !$args{sortby};
-  $args{offset} = 0 if !$args{offset};
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
 
   my $sql = "SELECT l.location, l.description, l.iplist, g.group_name ".
@@ -3307 +3296 @@
   $errstr = '';
   my $dbh = shift;
-  my $def = shift;
-  my $rev = shift;
-  my $id = shift;
-  my $nrecs = shift || 'all';
-  my $nstart = shift || 0;
-
-## for order, need to map input to column names
-  my $order = shift || 'host';
-  my $direction = shift || 'ASC';
-
-  my $filter = shift || '';
+
+  my %args = @_;
+
+  my @filterargs;
+
+  push @filterargs, $args{filter} if $args{filter};
+
+  # protection against bad or missing arguments
+  $args{sortorder} = 'ASC' if !$args{sortorder};
+  $args{sortby} = 'host' if !$args{sortby} && $args{rev} eq 'n';        # default sort by host on domain record list
+  $args{sortby} = 'val' if !$args{sortby} && $args{rev} eq 'y';         # default sort by IP on revzone record list
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;  
 
   # sort reverse zones on IP, correctly
-  # do other fiddling with $order while we're at it.
-  $order = "r.$order";
-  $order = 'CAST (r.val AS inet)' if $rev eq 'y' && $order eq 'r.val';
-  $order = 't.alphaorder' if $order eq 'r.type';
+  # do other fiddling with $args{sortby} while we're at it.
+  $args{sortby} = "r.$args{sortby}";
+  $args{sortby} = 'CAST (r.val AS inet)' if $args{revrec} eq 'y' && $args{sortby} eq 'r.val';
+  $args{sortby} = 't.alphaorder' if $args{sortby} eq 'r.type';
 
   my $sql = "SELECT r.record_id,r.host,r.type,r.val,r.ttl";
-  $sql .= ",l.description AS locname" if $def eq 'n';
-  $sql .= ",r.distance,r.weight,r.port" if $rev eq 'n';
-  $sql .= " FROM "._rectable($def,$rev)." r ";
+  $sql .= ",l.description AS locname" if $args{defrec} eq 'n';
+  $sql .= ",r.distance,r.weight,r.port" if $args{revrec} eq 'n';
+  $sql .= " FROM "._rectable($args{defrec},$args{revrec})." r ";
   $sql .= "INNER JOIN rectypes t ON r.type=t.val ";     # for sorting by type alphabetically
-  $sql .= "LEFT JOIN locations l ON r.location=l.location " if $def eq 'n';
-  $sql .= "WHERE "._recparent($def,$rev)." = ?";
+  $sql .= "LEFT JOIN locations l ON r.location=l.location " if $args{defrec} eq 'n';
+  $sql .= "WHERE "._recparent($args{defrec},$args{revrec})." = ?";
   $sql .= " AND NOT r.type=$reverse_typemap{SOA}";
-  $sql .= " AND host ~* ?" if $filter;
-  # use alphaorder column for "correct" ordering of sort-by-type instead of DNS RR type number
-  $sql .= " ORDER BY $order $direction";
+  $sql .= " AND host ~* ?" if $args{filter};
+  $sql .= " ORDER BY $args{sortby} $args{sortorder}";
   # ensure consistent ordering by sorting on record_id too
-  $sql .= ", record_id $direction";
-
-  my @bindvars = ($id);
-  push @bindvars, $filter if $filter;
-
-  # just to be ultraparanoid about SQL injection vectors
-  if ($nstart ne 'all') {
-    $sql .= " LIMIT ? OFFSET ?";
-    push @bindvars, $nrecs;
-    push @bindvars, ($nstart*$nrecs);
-  }
-  my $sth = $dbh->prepare($sql) or warn $dbh->errstr;
-  $sth->execute(@bindvars) or warn "$sql: ".$sth->errstr;
-
-  my @retbase;
-  while (my $ref = $sth->fetchrow_hashref()) {
-    push @retbase, $ref;
-  }
-
-  my $ret = \@retbase;
+  $sql .= ", record_id $args{sortorder}";
+  $sql .= ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
+
+  my @bindvars = ($args{id});
+  push @bindvars, $args{filter} if $args{filter};
+
+  my $ret = $dbh->selectall_arrayref($sql, { Slice => {} }, (@bindvars) );
   return $ret;
 } # end getDomRecs()
@@ -3782 +3758 @@
   $args{sortby} = 'stamp' if !$args{sortby};
   $args{sortorder} = 'DESC' if !$args{sortorder};
-  $args{offset} = 0 if !$args{offset};
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
 
   my %sortmap = (fname => 'name', username => 'email', entry => 'entry', stamp => 'stamp');