Changeset 323 for trunk/DNSDB.pm

Timestamp:

04/29/12 18:20:17 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Moved some more SQL out of dns.cgi (see #1). Added pagination
and sorting to the log display while I was at it.

File:

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

trunk/DNSDB.pm

@@ -46 +46 @@
         &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
         &addRec &updateRec &delRec
+        &getLogCount &getLogEntries
         &getTypelist
         &parentID
@@ -69 +70 @@
                 &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
                 &addRec &updateRec &delRec
+                &getLogCount &getLogEntries
                 &getTypelist
                 &parentID
@@ -3068 +3070 @@
   return ('OK',$logdata{entry});
 } # end delRec()
+
+
+## DNSDB::getLogCount()
+# Get a count of log entries
+# Takes a database handle and a hash containing at least:
+# - Entity ID and entity type as the primary log "slice"
+sub getLogCount {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @filterargs;
+##fixme:  which fields do we want to filter on?
+# push @filterargs, 
+
+  $errstr = 'Missing primary parent ID and/or type';
+  # fail early if we don't have a "prime" ID to look for log entries for
+  return if !$args{id};
+
+  # or if the prime id type is missing or invalid
+  return if !$args{logtype};
+  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
+  $args{logtype} = 'domain' if $args{logtype} eq 'dom';         # hack pthui
+  return if !grep /^$args{logtype}$/, ('group', 'domain', 'revzone', 'user');
+
+  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
+
+  my $sql = "SELECT count(*) FROM log ".
+        "WHERE $id_col{$args{logtype}}=?".
+        ($args{filter} ? " AND entry ~* ?" : '');
+  my ($count) = $dbh->selectrow_array($sql, undef, ($args{id}, @filterargs) );
+  $errstr = $dbh->errstr if !$count;
+  return $count;
+} # end getLogCount()
+
+
+## DNSDB::getLogEntries()
+# Get a list of log entries
+# Takes arguments as with getLogCount() above, plus optional:
+# - sort field
+# - sort order
+# - offset for pagination
+sub getLogEntries {
+  my $dbh = shift;
+
+  my %args = @_;
+
+  my @filterargs;
+
+  # fail early if we don't have a "prime" ID to look for log entries for
+  return if !$args{id};
+
+  # or if the prime id type is missing or invalid
+  return if !$args{logtype};
+  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
+  $args{logtype} = 'domain' if $args{logtype} eq 'dom';         # hack pthui
+  return if !grep /^$args{logtype}$/, ('group', 'domain', 'revzone', 'user');
+
+  # Sorting defaults
+  $args{sortby} = 'stamp' if !$args{sortby};
+  $args{sortorder} = 'DESC' if !$args{sortorder};
+  $args{offset} = 0 if !$args{offset};
+
+  my %sortmap = (fname => 'name', username => 'email', entry => 'entry', stamp => 'stamp,log_id');
+  $args{sortby} = $sortmap{$args{sortby}};
+
+  my $sql = "SELECT user_id AS userid, email AS useremail, name AS userfname, entry AS logentry, ".
+        "date_trunc('second',stamp) AS logtime ".
+        "FROM log ".
+        "WHERE $id_col{$args{logtype}}=?".
+        ($args{filter} ? " AND entry ~* ?" : '').
+        " ORDER BY $args{sortby} $args{sortorder}".
+        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
+  my $loglist = $dbh->selectall_arrayref($sql, { Slice => {} }, ($args{id}, @filterargs) );
+  $errstr = $dbh->errstr if !$loglist;
+  return $loglist;
+} # end getLogEntries()