Changeset 723 for trunk/DNSDB.pm

Timestamp:

06/17/16 18:01:13 (8 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.

File:

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

trunk/DNSDB.pm

@@ -4884 +4884 @@
 
 
-## DNSDB::recSearchCount()
-# Get a total count for a global record search
-## DNSDB::recSearch()
-# Find records matching the search string
-sub recSearch {
-  my $self = shift;
-  my $dbh = $self->{dbh};
-
-  my %args = @_;
-
-my $sql = q(
-SELECT r.domain_id,d.domain,g1.group_name AS domgroup,r.rdns_id,z.revnet AS revzone,g2.group_name AS 
-revgroup,r.host,t.name AS rectype,r.val,l.description AS location
+# a collection of joins for the record search
+our $recsearchsqlbase = q(
 FROM records r
 LEFT JOIN domains d ON r.domain_id = d.domain_id
@@ -4907 +4896 @@
 );
 
-# Limit scope based on group
-if ($args{group} != 1) {
-  my @grouplist;
-  $self->getChildren($args{group}, \@grouplist);
-  my $groupset = join(',', $args{group}, @grouplist);
-  # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
-  $sql .= "AND (
-  (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
-  (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
-  (g1.group_id IS NULL AND g2.group_id IN ($groupset))
-  )
+
+## DNSDB::recSearchCount()
+# Get a total count for a global record search
+# Takes a hash with the search string and the login group of the user
+sub recSearchCount {
+  my $self = shift;
+  my $dbh = $self->{dbh};
+  my %args = @_;
+
+  my $sql = "SELECT count(*)".$recsearchsqlbase;
+
+  # Limit scope based on group
+  if ($args{group} != 1) {
+    my @grouplist;
+    $self->getChildren($args{group}, \@grouplist);
+    my $groupset = join(',', $args{group}, @grouplist);
+    # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
+    $sql .= "AND (
+    (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
+    (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
+    (g1.group_id IS NULL AND g2.group_id IN ($groupset))
+    )
 ";
-}
-
-my $foo = q(
-SELECT r.domain_id,d.domain,g1.group_name AS domgroup,r.rdns_id,z.revnet AS revzone,g2.group_name AS 
-revgroup,r.host,t.name AS rectype,r.val,l.description AS location
-FROM records r
-LEFT JOIN domains d on r.domain_id = d.domain_id
-  LEFT JOIN groups g1 on d.group_id = g1.group_id
-LEFT JOIN revzones z on r.rdns_id = z.rdns_id
-  LEFT JOIN groups g2 on z.group_id = g2.group_id
-JOIN rectypes t ON r.type = t.val
-LEFT JOIN locations l ON r.location = l.location
-WHERE r.type <> 6 AND (r.host ~* 'kings' OR r.val ~* 'kings');
-);
-#ORDER BY record_id
-
-#warn "$sql\n";
-# safety valve.  probably need a way to flag this back to the caller.
-if (!$args{searchfor} || $args{searchfor} =~ /^.{0,2}$/) { $sql .= "LIMIT 10"; }
-
-#$sql .= "LIMIT 20" if $sql !~ /LIMIT/;
-
-  return $dbh->selectall_arrayref($sql, { Slice => {} }, $args{searchfor}, $args{searchfor}) or warn $dbh->errstr;
+  }
+
+  my $count = $dbh->selectrow_array($sql, undef, $args{searchfor}, $args{searchfor});
+  $errstr = $dbh->errstr if !$count;
+  return $count;
+
+} # end recSearchCount()
+
+
+## DNSDB::recSearch()
+# Find records matching the search string
+# Takes a hash with the search string, login group of the user, pagination offset, sort field,
+# and sort direction
+# Returns a reference to a list of hashrefs
+sub recSearch {
+  my $self = shift;
+  my $dbh = $self->{dbh};
+  my %args = @_;
+
+  my $sql = q(SELECT
+    r.domain_id, d.domain, g1.group_name AS domgroup,
+    r.rdns_id, z.revnet AS revzone, g2.group_name AS revgroup,
+    r.host, t.name AS rectype, r.val, l.description AS location, r.record_id).
+    $recsearchsqlbase;
+
+  # Limit scope based on group
+  if ($args{group} != 1) {
+    my @grouplist;
+    $self->getChildren($args{group}, \@grouplist);
+    my $groupset = join(',', $args{group}, @grouplist);
+    # oh my aching HEAD.  there has to be a better way to do conditions on joined tables...
+    $sql .= "AND (
+    (g1.group_id IN ($groupset) AND g2.group_id IN ($groupset)) OR
+    (g1.group_id IN ($groupset) AND g2.group_id IS NULL) OR
+    (g1.group_id IS NULL AND g2.group_id IN ($groupset))
+    )
+";
+  }
+
+  # mixed tables means this isn't a simple prefix like the regular record list filter.  :/
+  my %sortmap = (
+    domain => 'd.domain',
+    revzone => 'z.revnet',
+    host => 'r.host',
+    type => 't.name', 
+    val => 'inetlazy(r.val)',
+    location => 'r.location',
+    );
+
+  # Sorting defaults
+  $args{sortorder} = 'ASC' if !$args{sortorder} || !grep /^$args{sortorder}$/, ('ASC','DESC');
+  $args{sortby} = 'r.host' if !$args{sortby} || $args{sortby} !~ /^[\w_.]+$/;
+  $args{offset} = 0 if !$args{offset} || $args{offset} !~ /^(?:all|\d+)$/;
+
+  $args{sortby} = $sortmap{$args{sortby}} if $args{sortby} !~ /\./;
+
+  # Add sort and offset to SQL
+  $sql .= "ORDER BY $args{sortby} $args{sortorder},record_id ASC\n";
+  $sql .= ($args{offset} eq 'all' ? '' : "LIMIT $self->{perpage} OFFSET ".$args{offset}*$self->{perpage});
+
+##fixme: should probably sent the warning somewhere else
+  return $dbh->selectall_arrayref($sql, { Slice => {} }, $args{searchfor}, $args{searchfor})
+    or warn $dbh->errstr;
+
 } # end recSearch()