Changeset 237


Ignore:
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).

Location:
trunk
Files:
1 added
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r236 r237  
    3030        &loadConfig &connectDB &finish
    3131        &addDomain &delDomain &domainName &domainID
     32        &getZoneCount &getZoneList
    3233        &addGroup &delGroup &getChildren &groupName
    3334        &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    … …  
    5152                &loadConfig &connectDB &finish
    5253                &addDomain &delDomain &domainName &domainID
     54                &getZoneCount &getZoneList
    5355                &addGroup &delGroup &getChildren &groupName
    5456                &addUser &updateUser &delUser &userFullName &userStatus &getUserData
    … …  
    11841186  return $domid if $domid;
    11851187} # end domainID()
     1188
     1189
     1190## DNSDB::getZoneCount
     1191# Get count of zones in group or groups
     1192# Takes a database handle and hash containing:
     1193#  - the "current" group
     1194#  - an array of "acceptable" groups
     1195#  - a flag for forward/reverse zones
     1196#  - Optionally accept a "starts with" and/or "contains" filter argument
     1197# Returns an integer count of the resulting zone list.
     1198sub getZoneCount {
     1199  my $dbh = shift;
     1200
     1201  my %args = @_;
     1202
     1203  my @filterargs;
     1204  $args{filter} =~ s/\./\[\.\]/g if $args{filter};
     1205  push @filterargs, $args{startwith} if $args{startwith};
     1206  push @filterargs, $args{filter} if $args{filter};
     1207
     1208  my $sql;
     1209  # Not as compact, and fix-me-twice if the common bits get wrong, but much easier to read
     1210  if ($args{revrec} eq 'n') {
     1211    $sql = "SELECT count(*) FROM domains".
     1212        " WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     1213        ($args{startwith} ? " AND domain ~* ?" : '').
     1214        ($args{filter} ? " AND domain ~* ?" : '');
     1215  } else {
     1216    $sql = "SELECT count(*) FROM revzones".
     1217        " WHERE group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     1218        ($args{startwith} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '').
     1219        ($args{filter} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '');
     1220  }
     1221  my ($count) = $dbh->selectrow_array($sql, undef, @filterargs);
     1222  return $count;
     1223} # end getZoneCount()
     1224
     1225
     1226## DNSDB::getZoneList()
     1227# Get a list of zones in the specified group(s)
     1228# Takes the same arguments as getZoneCount() above
     1229# Returns a reference to an array of hashrefs suitable for feeding to HTML::Template
     1230sub getZoneList {
     1231  my $dbh = shift;
     1232
     1233  my %args = @_;
     1234
     1235  my @zonelist;
     1236
     1237  $args{sortorder} = 'ASC' if !grep $args{sortorder}, ('ASC','DESC');
     1238  $args{offset} = 0 if !$args{offset};
     1239
     1240  my @filterargs;
     1241  $args{filter} =~ s/\./\[\.\]/g if $args{filter};
     1242  push @filterargs, $args{startwith} if $args{startwith};
     1243  push @filterargs, $args{filter} if $args{filter};
     1244
     1245  my $sql;
     1246  # Not as compact, and fix-me-twice if the common bits get wrong, but much easier to read
     1247  if ($args{revrec} eq 'n') {
     1248    $args{sortby} = 'domain' if !grep $args{sortby}, ('revnet','group','status');
     1249    $sql = "SELECT domain_id,domain,status,groups.group_name AS group FROM domains".
     1250        " INNER JOIN groups ON domains.group_id=groups.group_id".
     1251        " WHERE domains.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     1252        ($args{startwith} ? " AND domain ~* ?" : '').
     1253        ($args{filter} ? " AND domain ~* ?" : '');
     1254  } else {
     1255    $args{sortby} = 'revnet' if !grep $args{sortby}, ('domain','group','status');
     1256    $sql = "SELECT rdns_id,revnet,status,groups.group_name AS group FROM revzones".
     1257        " INNER JOIN groups ON revzones.group_id=groups.group_id".
     1258        " WHERE revzones.group_id IN ($args{curgroup}".($args{childlist} ? ",$args{childlist}" : '').")".
     1259        ($args{startwith} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '').
     1260        ($args{filter} ? " AND CAST(revnet AS VARCHAR) ~* ?" : '');
     1261  }
     1262  # A common tail.
     1263  $sql .= " ORDER BY ".($args{sortby} eq 'group' ? 'groups.group_name' : $args{sortby}).
     1264        " $args{sortorder} ".($args{offset} eq 'all' ? '' : " LIMIT $config{perpage}".
     1265        " OFFSET ".$args{offset}*$config{perpage});
     1266  my $sth = $dbh->prepare($sql);
     1267  $sth->execute(@filterargs);
     1268  my $rownum = 0;
     1269
     1270  while (my @data = $sth->fetchrow_array) {
     1271    my %row;
     1272    $row{domainid} = $data[0];
     1273    $row{domain} = $data[1];
     1274    $row{status} = ($data[2] ? 'Active' : 'Inactive');
     1275    $row{group} = $data[3];
     1276    $row{bg} = ($rownum++)%2;
     1277    $row{mkactive} = !$data[2];
     1278#    $row{sid} = $args{sid};
     1279    $row{offset} = $args{offset};
     1280# ACLs
     1281#    $row{domain_edit} = ($permissions{admin} || $permissions{domain_edit});
     1282#    $row{domain_delete} = ($permissions{admin} || $permissions{domain_delete});
     1283    push @zonelist, \%row;
     1284  }
     1285
     1286  return \@zonelist;
     1287} # end getZoneList()
    11861288
    11871289

  • trunk/dns.cgi

    r234 r237  
    408408    changepage(page => "domlist");
    409409  }
     410
     411} elsif ($webvar{page} eq 'revzones') {
     412
     413  $webvar{revrec} = 'y';
     414  $page->param(curpage => $webvar{page});
     415  listzones();
    410416
    411417} elsif ($webvar{page} eq 'reclist') {
    … …  
    18811887  # on a page showing nothing.
    18821888  # For bonus points, this reverts to the original offset on clicking the "All" link (mostly)
    1883   if ($offset ne 'all') { 
     1889  if ($offset ne 'all') {
    18841890    $offset-- while ($offset * $perpage) >= $pgcount;
    18851891  }
    … …  
    18951901} # end fill_pgcount()
    18961902
    1897 sub listdomains {
    1898 
     1903
     1904sub listdomains { listzones(); }        # temp
     1905
     1906sub listzones {
    18991907# ACLs
    19001908  $page->param(domain_create    => ($permissions{admin} || $permissions{domain_create}) );
    … …  
    19061914  my $childlist = join(',',@childgroups);
    19071915
    1908   my $sql = "SELECT count(*) FROM domains WHERE group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1909         ($startwith ? " AND domain ~* ?" : '').
    1910         ($filter ? " AND domain ~* ?" : '');
    1911   my $sth = $dbh->prepare($sql);
    1912   $sth->execute(@filterargs);
    1913   my ($count) = $sth->fetchrow_array;
     1916  my $count = getZoneCount($dbh, (childlist => $childlist, curgroup => $curgroup, revrec => $webvar{revrec},
     1917        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef) ) );
    19141918
    19151919# fill page count and first-previous-next-last-all bits
    1916   fill_pgcount($count,"domains",groupName($dbh,$curgroup));
     1920  fill_pgcount($count,($webvar{revrec} eq 'n' ? 'domains' : 'revzones'),groupName($dbh,$curgroup));
    19171921  fill_fpnla($count);
    19181922
    … …  
    19251929
    19261930# set up the headers
    1927   my @cols = ('domain', 'status', 'group');
    1928   my %colheads = (domain => 'Domain', status => 'Status', group => 'Group');
     1931  my @cols = (($webvar{revrec} eq 'n' ? 'domain' : 'revnet'), 'status', 'group');
     1932  my %colheads = (domain => 'Domain', revnet => 'Reverse Zone', status => 'Status', group => 'Group');
    19291933  fill_colheads($sortby, $sortorder, \@cols, \%colheads);
    19301934
    … …  
    19341938
    19351939# waffle, waffle - keep state on these as well as sortby, sortorder?
     1940##fixme:  put this higher so the count doesn't get munched?
    19361941  $page->param("start$startwith" => 1) if $startwith && $startwith =~ /^(?:[a-z]|0-9)$/;
    19371942
    … …  
    19391944  $page->param(searchsubs => $searchsubs) if $searchsubs;
    19401945
    1941 ##fixme
    1942 ##fixme  push the SQL and direct database fiddling off into a sub in DNSDB.pm
    1943 ##fixme
    1944 
    19451946  $page->param(group => $curgroup);
    1946   my @domlist;
    1947   $sql = "SELECT domain_id,domain,status,groups.group_name AS group FROM domains".
    1948         " INNER JOIN groups ON domains.group_id=groups.group_id".
    1949         " WHERE domains.group_id IN ($curgroup".($childlist ? ",$childlist" : '').")".
    1950         ($startwith ? " AND domain ~* ?" : '').
    1951         ($filter ? " AND domain ~* ?" : '').
    1952         " ORDER BY ".($sortby eq 'group' ? 'groups.group_name' : $sortby).
    1953         " $sortorder ".($offset eq 'all' ? '' : " LIMIT $perpage OFFSET ".$offset*$perpage);
    1954   $sth = $dbh->prepare($sql);
    1955   $sth->execute(@filterargs);
    1956   my $rownum = 0;
    1957   while (my @data = $sth->fetchrow_array) {
    1958     my %row;
    1959     $row{domainid} = $data[0];
    1960     $row{domain} = $data[1];
    1961     $row{status} = ($data[2] ? 'Active' : 'Inactive');
    1962     $row{group} = $data[3];
    1963     $row{bg} = ($rownum++)%2;
    1964     $row{mkactive} = !$data[2];
    1965     $row{sid} = $sid;
    1966     $row{offset} = $offset;
    1967 # ACLs
    1968     $row{domain_edit} = ($permissions{admin} || $permissions{domain_edit});
    1969     $row{domain_delete} = ($permissions{admin} || $permissions{domain_delete});
    1970     push @domlist, \%row;
    1971   }
    1972   $page->param(domtable => \@domlist);
     1947
     1948  my $zonelist = getZoneList($dbh, (childlist => $childlist, curgroup => $curgroup,
     1949        revrec => $webvar{revrec},
     1950        filter => ($filter ? $filter : undef), startwith => ($startwith ? $startwith : undef),
     1951        offset => $webvar{offset}, sortby => $sortby, sortorder => $sortorder
     1952        ) );
     1953  foreach (@$zonelist) {
     1954    $_->{sid} = $sid;
     1955    $_->{domain_edit} = ($permissions{admin} || $permissions{domain_edit});
     1956    $_->{domain_delete} = ($permissions{admin} || $permissions{domain_delete});
     1957  }
     1958  $page->param(domtable => $zonelist);
    19731959} # end listdomains()
    19741960
Note: See TracChangeset for help on using the changeset viewer.