Changeset 723


Ignore:
Timestamp:
06/17/16 18:01:13 (9 years ago)
Author:
Kris Deugau
Message:

/trunk

Record search is substantially complete, including scope limits for users
in subgroups, sorting, and various links into zones or direct record
editing (records attached to both a forward and reverse zone.
See #57.

Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r722 r723  
    48844884
    48854885
    4886 ## DNSDB::recSearchCount()
    4887 # Get a total count for a global record search
    4888 ## DNSDB::recSearch()
    4889 # Find records matching the search string
    4890 sub recSearch {
    4891   my $self = shift;
    4892   my $dbh = $self->{dbh};
    4893 
    4894   my %args = @_;
    4895 
    4896 my $sql = q(
    4897 SELECT r.domain_id,d.domain,g1.group_name AS domgroup,r.rdns_id,z.revnet AS revzone,g2.group_name AS
    4898 revgroup,r.host,t.name AS rectype,r.val,l.description AS location
     4886# a collection of joins for the record search
     4887our $recsearchsqlbase = q(
    48994888FROM records r
    49004889LEFT JOIN domains d ON r.domain_id = d.domain_id
    … …  
    49074896);
    49084897
    4909 # Limit scope based on group
    4910 if ($args{group} != 1) {
    4911   my @grouplist;
    4912   $self->getChildren($args{group}, \@grouplist);
    4913   my $groupset = join(',', $args{group}, @grouplist);
    4914   # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
    4915   $sql .= "AND (
    4916   (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
    4917   (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
    4918   (g1.group_id IS NULL AND g2.group_id IN ($groupset))
    4919   )
     4898
     4899## DNSDB::recSearchCount()
     4900# Get a total count for a global record search
     4901# Takes a hash with the search string and the login group of the user
     4902sub recSearchCount {
     4903  my $self = shift;
     4904  my $dbh = $self->{dbh};
     4905  my %args = @_;
     4906
     4907  my $sql = "SELECT count(*)".$recsearchsqlbase;
     4908
     4909  # Limit scope based on group
     4910  if ($args{group} != 1) {
     4911    my @grouplist;
     4912    $self->getChildren($args{group}, \@grouplist);
     4913    my $groupset = join(',', $args{group}, @grouplist);
     4914    # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
     4915    $sql .= "AND (
     4916    (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
     4917    (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
     4918    (g1.group_id IS NULL AND g2.group_id IN ($groupset))
     4919    )
    49204920";
    4921 }
    4922 
    4923 my $foo = q(
    4924 SELECT r.domain_id,d.domain,g1.group_name AS domgroup,r.rdns_id,z.revnet AS revzone,g2.group_name AS
    4925 revgroup,r.host,t.name AS rectype,r.val,l.description AS location
    4926 FROM records r
    4927 LEFT JOIN domains d on r.domain_id = d.domain_id
    4928   LEFT JOIN groups g1 on d.group_id = g1.group_id
    4929 LEFT JOIN revzones z on r.rdns_id = z.rdns_id
    4930   LEFT JOIN groups g2 on z.group_id = g2.group_id
    4931 JOIN rectypes t ON r.type = t.val
    4932 LEFT JOIN locations l ON r.location = l.location
    4933 WHERE r.type <> 6 AND (r.host ~* 'kings' OR r.val ~* 'kings');
    4934 );
    4935 #ORDER BY record_id
    4936 
    4937 #warn "$sql\n";
    4938 # safety valve.  probably need a way to flag this back to the caller.
    4939 if (!$args{searchfor} || $args{searchfor} =~ /^.{0,2}$/) { $sql .= "LIMIT 10"; }
    4940 
    4941 #$sql .= "LIMIT 20" if $sql !~ /LIMIT/;
    4942 
    4943   return $dbh->selectall_arrayref($sql, { Slice => {} }, $args{searchfor}, $args{searchfor}) or warn $dbh->errstr;
     4921  }
     4922
     4923  my $count = $dbh->selectrow_array($sql, undef, $args{searchfor}, $args{searchfor});
     4924  $errstr = $dbh->errstr if !$count;
     4925  return $count;
     4926
     4927} # end recSearchCount()
     4928
     4929
     4930## DNSDB::recSearch()
     4931# Find records matching the search string
     4932# Takes a hash with the search string, login group of the user, pagination offset, sort field,
     4933# and sort direction
     4934# Returns a reference to a list of hashrefs
     4935sub recSearch {
     4936  my $self = shift;
     4937  my $dbh = $self->{dbh};
     4938  my %args = @_;
     4939
     4940  my $sql = q(SELECT
     4941    r.domain_id, d.domain, g1.group_name AS domgroup,
     4942    r.rdns_id, z.revnet AS revzone, g2.group_name AS revgroup,
     4943    r.host, t.name AS rectype, r.val, l.description AS location, r.record_id).
     4944    $recsearchsqlbase;
     4945
     4946  # Limit scope based on group
     4947  if ($args{group} != 1) {
     4948    my @grouplist;
     4949    $self->getChildren($args{group}, \@grouplist);
     4950    my $groupset = join(',', $args{group}, @grouplist);
     4951    # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
     4952    $sql .= "AND (
     4953    (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
     4954    (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
     4955    (g1.group_id IS NULL AND g2.group_id IN ($groupset))
     4956    )
     4957";
     4958  }
     4959
     4960  # mixed tables means this isn't a simple prefix like the regular record list filter.  :/
     4961  my %sortmap = (
     4962    domain => 'd.domain',
     4963    revzone => 'z.revnet',
     4964    host => 'r.host',
     4965    type => 't.name',
     4966    val => 'inetlazy(r.val)',
     4967    location => 'r.location',
     4968    );
     4969
     4970  # Sorting defaults
     4971  $args{sortorder} = 'ASC' if !$args{sortorder} || !grep /^$args{sortorder}$/, ('ASC','DESC');
     4972  $args{sortby} = 'r.host' if !$args{sortby} || $args{sortby} !~ /^[\w_.]+$/;
     4973  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
     4974
     4975  $args{sortby} = $sortmap{$args{sortby}} if $args{sortby} !~ /\./;
     4976
     4977  # Add sort and offset to SQL
     4978  $sql .= "ORDER BY $args{sortby} $args{sortorder},record_id ASC\n";
     4979  $sql .= ($args{offset} eq 'all' ? '' : "LIMIT $self->{perpage} OFFSET ".$args{offset}*$self->{perpage});
     4980
     4981##fixme: should probably sent the warning somewhere else
     4982  return $dbh->selectall_arrayref($sql, { Slice => {} }, $args{searchfor}, $args{searchfor})
     4983    or warn $dbh->errstr;
     4984
    49444985} # end recSearch()
    49454986

  • trunk/dns.cgi

    r722 r723  
    18611861} elsif ($webvar{page} eq 'recsearch') {
    18621862
    1863 ##FIXME
    1864 ##FIXME  Limit scope of search!!
    1865 ##FIXME
    1866 
    1867 #  my $count = $dnsdb->recSearchCount(searchfor => $webvar{searchfor});
    1868 
    1869   $page->param(searchfor => $webvar{searchfor});
    1870   my $recset = $dnsdb->recSearch(searchfor => $webvar{searchfor}, group => $logingroup);
     1863  # we do this for the domain and record list filter/search - it should be extremely rare to
     1864  # need to search on characters outside this set until we get into IDNs
     1865  $webvar{searchfor} =~ s/[^a-zA-Z0-9_.:\@-]//g if $webvar{searchfor};
     1866
     1867  # save the search in the session, same as the "filter" in various other lists...
     1868  if (defined($webvar{searchfor})) {
     1869    if ($session->param('recsearch') && $webvar{searchfor} ne $session->param('recsearch')) {
     1870      $uri_self =~ s/\&amp;offset=[^&]//;
     1871      $offset = 0;
     1872    }
     1873    $session->param(recsearch => $webvar{searchfor});
     1874  }
     1875  my $searchfor = $session->param('recsearch');
     1876
     1877  $sortby = 'host';
     1878  $session->param($webvar{page}.'sortby', $webvar{sortby}) if $webvar{sortby};
     1879  $session->param($webvar{page}.'order', $webvar{order}) if $webvar{order};
     1880  $sortby = $session->param($webvar{page}.'sortby') if $session->param($webvar{page}.'sortby');
     1881  $sortorder = $session->param($webvar{page}.'order') if $session->param($webvar{page}.'order');
     1882
     1883  # some magic to label and linkify the column headers for sorting
     1884  my @cols = ('domain','revzone','host','type','val','location');
     1885  my %colheads = (domain => "Domain (Group)", revzone => "Reverse zone (Group)", host => "Host",
     1886        type => "Type", val => "IP/value", location => "Location");
     1887  fill_colheads($sortby, $sortorder, \@cols, \%colheads);
     1888
     1889  # pgcount.tmpl
     1890  my $count = $dnsdb->recSearchCount(searchfor => $searchfor, group => $logingroup);
     1891  fill_pgcount($count, "records");
     1892  fill_fpnla($count);
     1893
     1894  # and a bit for fpnla.tmpl
     1895  $page->param(curpage => $webvar{page});
     1896
     1897  $page->param(searchfor => $searchfor);
     1898  my $recset = $dnsdb->recSearch(searchfor => $searchfor, group => $logingroup, offset => $webvar{offset},
     1899    sortby => $sortby, sortorder => $sortorder);
    18711900  $page->param(searchresults => $recset);
    18721901

  • trunk/templates/menu.tmpl

    r722 r723  
    3232<hr />
    3333Find records(s):
    34 <form action="<TMPL_VAR NAME=script_self>&amp;page=recsearch" method="POST">
     34<form action="<TMPL_VAR NAME=script_self>&amp;page=recsearch" method="post">
    3535<input name="searchfor" />
    3636<input type="submit" value=" Search " />

  • trunk/templates/recsearch.tmpl

    r722 r723  
    1515<td align="center"><TMPL_INCLUDE NAME="fpnla.tmpl"></td>
    1616<td class="rightthird">
    17                 <form action="<TMPL_VAR NAME=script_self>">
    18                 <input type="hidden" name="page" value="recsearch" />
    19                 <input type="hidden" name="offset" value="0" />
    20                 <input name="searchfor" value="<TMPL_VAR NAME=searchfor>" />
    21                 <input type="submit" value=" Refine search " />
    22                 </form>
     17        <form action="<TMPL_VAR NAME=script_self>" method="post">
     18        <input type="hidden" name="page" value="recsearch" />
     19        <input type="hidden" name="offset" value="0" />
     20        <input name="searchfor" value="<TMPL_VAR NAME=searchfor>" />
     21        <input type="submit" value=" Refine search " />
     22        </form>
    2323</td>
    2424</tr>
    … …  
    2626
    2727<table class="altrows" width="98%" border="0" cellpadding="3">
    28 <tr><th>Domain (Group)</th><th>Reverse zone (Group)</th><th>Host</th><th>Type</th><th>IP/value</th><th>Location</th></tr>
    29 <tbody>
     28<tr>
     29<TMPL_LOOP NAME=colheads>       <th><a href="<TMPL_VAR NAME=script_self>&amp;page=<TMPL_VAR
     30 NAME=page><TMPL_IF NAME=offset>&amp;offset=<TMPL_VAR NAME=offset></TMPL_IF>&amp;sortby=<TMPL_VAR
     31 NAME=sortby>&amp;order=<TMPL_VAR NAME=order>"><TMPL_VAR NAME=colname></a><TMPL_IF
     32 NAME=sortorder>&nbsp;<img alt="<TMPL_VAR NAME=sortorder>" src="images/<TMPL_VAR
     33 NAME=sortorder>.png" /></TMPL_IF></th>
     34</TMPL_LOOP>
     35</tr>
     36
     37<TMPL_IF searchresults>
    3038<TMPL_LOOP NAME=searchresults>
    3139<tr>
    32 <td><TMPL_IF domain_id><a href="<TMPL_VAR NAME=script_self>&amp;page=reclist&amp;id=<TMPL_VAR
    33 NAME=domain_id>&amp;defrec=n&amp;filter=<TMPL_VAR NAME=searchfor>"><TMPL_VAR NAME=domain></a> (<TMPL_VAR
    34 NAME=domgroup>)</TMPL_IF></td>
    35 <td><TMPL_IF rdns_id><a href="<TMPL_VAR NAME=script_self>&amp;page=reclist&amp;id=<TMPL_VAR NAME=rdns_id>&amp;defrec=n&amp;revrec=y"><TMPL_VAR NAME=revzone></a> (<TMPL_VAR NAME=revgroup>)</TMPL_IF></td>
     40<td><TMPL_IF domain_id><a href="<TMPL_VAR NAME=script_self>&amp;page=reclist&amp;id=<TMPL_VAR
     41 NAME=domain_id>&amp;defrec=n&amp;filter=<TMPL_VAR NAME=searchfor>"><TMPL_VAR NAME=domain></a> (<TMPL_VAR
     42 NAME=domgroup>)</TMPL_IF></td>
     43<td><TMPL_IF rdns_id><a href="<TMPL_VAR NAME=script_self>&amp;page=reclist&amp;id=<TMPL_VAR
     44 NAME=rdns_id>&amp;defrec=n&amp;revrec=y&amp;filter=<TMPL_VAR NAME=searchfor>"><TMPL_VAR
     45 NAME=revzone></a> (<TMPL_VAR NAME=revgroup>)</TMPL_IF></td>
     46<TMPL_IF domain_id>
     47<td><a href="<TMPL_VAR NAME=script_self>&amp;page=record&amp;parentid=<TMPL_VAR
     48 NAME=domain_id>&amp;defrec=n&amp;revrec=n&amp;recact=edit&amp;id=<TMPL_VAR
     49 NAME=record_id>"><TMPL_VAR NAME=host></a></td>
     50<TMPL_ELSE>
    3651<td><TMPL_VAR NAME=host></td>
     52</TMPL_IF>
    3753<td><TMPL_VAR NAME=rectype></td>
     54<TMPL_IF rdns_id>
     55<td><a href="<TMPL_VAR NAME=script_self>&amp;page=record&amp;parentid=<TMPL_VAR
     56 NAME=domain_id>&amp;defrec=n&amp;revrec=y&amp;recact=edit&amp;id=<TMPL_VAR
     57 NAME=record_id>"><TMPL_VAR NAME=val></a></td>
     58<TMPL_ELSE>
    3859<td><TMPL_VAR NAME=val></td>
     60</TMPL_IF>
    3961<td><TMPL_VAR NAME=location></td>
    4062</tr>
    4163</TMPL_LOOP>
    42 </tbody>
     64<TMPL_ELSE>
     65<tr><td colspan="5" align="center">No records found matching '<TMPL_VAR NAME=searchfor>'</td></tr>
     66</TMPL_IF>
    4367</table>
     68
     69<div class="center"><TMPL_INCLUDE NAME="fpnla.tmpl"></div>
     70
     71</td>
     72</tr>
     73</table>
     74
Note: See TracChangeset for help on using the changeset viewer.