Changeset 722 for trunk/DNSDB.pm

Timestamp:

06/13/16 16:06:41 (8 years ago)

Author:

Kris Deugau

Message:

/trunk

Checkpoint adding global record search. See #57.

File:

• trunk/DNSDB.pm (modified) (1 diff)

trunk/DNSDB.pm

@@ -4882 +4882 @@
   return $loglist;
 } # end getLogEntries()
+
+
+## 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
+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 ~* ? OR r.val ~* ?)
+);
+
+# 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;
+} # end recSearch()