Changeset 237 for trunk/DNSDB.pm

Timestamp:

02/17/12 17:55:18 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Checkpoint - almost finished updating domain list code to handle
reverse zones as well (see #26).
Still needs some UI polish in the template (flags to convert
"domain" to "reverse zone"), and possibly bringing some of the
HTML::Template-feeding bits for the actual list back out of the
DNSDB.pm sub after pushing the SQL in there (see #1).

File:

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

trunk/DNSDB.pm

@@ -30 +30 @@
         &loadConfig &connectDB &finish
         &addDomain &delDomain &domainName &domainID
+        &getZoneCount &getZoneList
         &addGroup &delGroup &getChildren &groupName
         &addUser &updateUser &delUser &userFullName &userStatus &getUserData
@@ -51 +52 @@
                 &loadConfig &connectDB &finish
                 &addDomain &delDomain &domainName &domainID
+                &getZoneCount &getZoneList
                 &addGroup &delGroup &getChildren &groupName
                 &addUser &updateUser &delUser &userFullName &userStatus &getUserData
@@ -1184 +1186 @@
   return $domid if $domid;
 } # end domainID()
+
+
+## DNSDB::getZoneCount
+# Get count of zones in group or groups
+# Takes a database handle and hash containing:
+#  - the "current" group
+#  - an array of "acceptable" groups
+#  - a flag for forward/reverse zones
+#  - Optionally accept a "starts with" and/or "contains" filter argument
+# Returns an integer count of the resulting zone list.
+sub getZoneCount {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @filterargs;
+  $args{filter} =~ s/\./\[\.\]/g if $args{filter};
+  push @filterargs, $args{startwith} if $args{startwith};
+  push @filterargs, $args{filter} if $args{filter};
+
+  my $sql;
+  # Not as compact, and fix-me-twice if the common bits get wrong, but much easier to read
+  if ($args{revrec} eq 'n') {
+    $sql = "SELECT count(*) FROM domains".
+        " WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND domain ~* ?" : '').
+        ($args{filter} ? " AND domain ~* ?" : '');
+  } else {
+    $sql = "SELECT count(*) FROM revzones".
+        " WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '').
+        ($args{filter} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '');
+  }
+  my ($count) = $dbh->selectrow_array($sql, undef, @filterargs);
+  return $count;
+} # end getZoneCount()
+
+
+## DNSDB::getZoneList()
+# Get a list of zones in the specified group(s)
+# Takes the same arguments as getZoneCount() above
+# Returns a reference to an array of hashrefs suitable for feeding to HTML::Template
+sub getZoneList {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @zonelist;
+
+  $args{sortorder} = 'ASC' if !grep $args{sortorder}, ('ASC','DESC');
+  $args{offset} = 0 if !$args{offset};
+
+  my @filterargs;
+  $args{filter} =~ s/\./\[\.\]/g if $args{filter};
+  push @filterargs, $args{startwith} if $args{startwith};
+  push @filterargs, $args{filter} if $args{filter};
+
+  my $sql;
+  # Not as compact, and fix-me-twice if the common bits get wrong, but much easier to read
+  if ($args{revrec} eq 'n') {
+    $args{sortby} = 'domain' if !grep $args{sortby}, ('revnet','group','status');
+    $sql = "SELECT domain_id,domain,status,groups.group_name AS group FROM domains".
+        " INNER JOIN groups ON domains.group_id=groups.group_id".
+        " WHERE domains.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND domain ~* ?" : '').
+        ($args{filter} ? " AND domain ~* ?" : '');
+  } else {
+    $args{sortby} = 'revnet' if !grep $args{sortby}, ('domain','group','status');
+    $sql = "SELECT rdns_id,revnet,status,groups.group_name AS group FROM revzones".
+        " INNER JOIN groups ON revzones.group_id=groups.group_id".
+        " WHERE revzones.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
+        ($args{startwith} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '').
+        ($args{filter} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '');
+  }
+  # A common tail.
+  $sql .= " ORDER BY ".($args{sortby} eq 'group' ? 'groups.group_name' : $args{sortby}).
+        " $args{sortorder} ".($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] ? 'Active' : 'Inactive');
+    $row{group} = $data[3];
+    $row{bg} = ($rownum++)%2;
+    $row{mkactive} = !$data[2];
+#    $row{sid} = $args{sid};
+    $row{offset} = $args{offset};
+# ACLs
+#    $row{domain_edit} = ($permissions{admin} || $permissions{domain_edit});
+#    $row{domain_delete} = ($permissions{admin} || $permissions{domain_delete});
+    push @zonelist, \%row;
+  }
+
+  return \@zonelist;
+} # end getZoneList()