Changeset 323


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

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed

  • trunk/DNSDB.pm

    r321 r323  
    4646        &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    4747        &addRec &updateRec &delRec
     48        &getLogCount &getLogEntries
    4849        &getTypelist
    4950        &parentID
    … …  
    6970                &getSOA &updateSOA &getRecLine &getDomRecs &getRecCount
    7071                &addRec &updateRec &delRec
     72                &getLogCount &getLogEntries
    7173                &getTypelist
    7274                &parentID
    … …  
    30683070  return ('OK',$logdata{entry});
    30693071} # end delRec()
     3072
     3073
     3074## DNSDB::getLogCount()
     3075# Get a count of log entries
     3076# Takes a database handle and a hash containing at least:
     3077# - Entity ID and entity type as the primary log "slice"
     3078sub getLogCount {
     3079  my $dbh = shift;
     3080
     3081  my %args = @_;
     3082
     3083  my @filterargs;
     3084##fixme:  which fields do we want to filter on?
     3085# push @filterargs,
     3086
     3087  $errstr = 'Missing primary parent ID and/or type';
     3088  # fail early if we don't have a "prime" ID to look for log entries for
     3089  return if !$args{id};
     3090
     3091  # or if the prime id type is missing or invalid
     3092  return if !$args{logtype};
     3093  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
     3094  $args{logtype} = 'domain' if $args{logtype} eq 'dom';         # hack pthui
     3095  return if !grep /^$args{logtype}$/, ('group', 'domain', 'revzone', 'user');
     3096
     3097  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
     3098
     3099  my $sql = "SELECT count(*) FROM log ".
     3100        "WHERE $id_col{$args{logtype}}=?".
     3101        ($args{filter} ? " AND entry ~* ?" : '');
     3102  my ($count) = $dbh->selectrow_array($sql, undef, ($args{id}, @filterargs) );
     3103  $errstr = $dbh->errstr if !$count;
     3104  return $count;
     3105} # end getLogCount()
     3106
     3107
     3108## DNSDB::getLogEntries()
     3109# Get a list of log entries
     3110# Takes arguments as with getLogCount() above, plus optional:
     3111# - sort field
     3112# - sort order
     3113# - offset for pagination
     3114sub getLogEntries {
     3115  my $dbh = shift;
     3116
     3117  my %args = @_;
     3118
     3119  my @filterargs;
     3120
     3121  # fail early if we don't have a "prime" ID to look for log entries for
     3122  return if !$args{id};
     3123
     3124  # or if the prime id type is missing or invalid
     3125  return if !$args{logtype};
     3126  $args{logtype} = 'revzone' if $args{logtype} eq 'rdns';       # hack pthui
     3127  $args{logtype} = 'domain' if $args{logtype} eq 'dom';         # hack pthui
     3128  return if !grep /^$args{logtype}$/, ('group', 'domain', 'revzone', 'user');
     3129
     3130  # Sorting defaults
     3131  $args{sortby} = 'stamp' if !$args{sortby};
     3132  $args{sortorder} = 'DESC' if !$args{sortorder};
     3133  $args{offset} = 0 if !$args{offset};
     3134
     3135  my %sortmap = (fname => 'name', username => 'email', entry => 'entry', stamp => 'stamp,log_id');
     3136  $args{sortby} = $sortmap{$args{sortby}};
     3137
     3138  my $sql = "SELECT user_id AS userid, email AS useremail, name AS userfname, entry AS logentry, ".
     3139        "date_trunc('second',stamp) AS logtime ".
     3140        "FROM log ".
     3141        "WHERE $id_col{$args{logtype}}=?".
     3142        ($args{filter} ? " AND entry ~* ?" : '').
     3143        " ORDER BY $args{sortby} $args{sortorder}".
     3144        ($args{offset} eq 'all' ? '' : " LIMIT $config{perpage} OFFSET ".$args{offset}*$config{perpage});
     3145  my $loglist = $dbh->selectall_arrayref($sql, { Slice => {} }, ($args{id}, @filterargs) );
     3146  $errstr = $dbh->errstr if !$loglist;
     3147  return $loglist;
     3148} # end getLogEntries()
    30703149
    30713150

  • trunk/dns.cgi

    r322 r323  
    9696  $session->param('reclistsortby','host');
    9797  $session->param('reclistorder','ASC');
     98  $session->param('logsortby','stamp');
     99  $session->param('logorder','DESC');
    98100}
    99101
    … …  
    14971499} elsif ($webvar{page} eq 'log') {
    14981500
    1499 ##fixme put in some real log-munching stuff
    1500   my $sql = "SELECT user_id, email, name, entry, date_trunc('second',stamp) FROM log WHERE ";
    15011501  my $id = $curgroup;  # we do this because the group log may be called from (almost) any page,
    15021502                       # but the others are much more limited.  this is probably non-optimal.
    15031503
    15041504  if ($webvar{ltype} && $webvar{ltype} eq 'user') {
    1505     $sql .= "user_id=?";
     1505##fixme:  where should we call this from?
    15061506    $id = $webvar{id};
    15071507    if (!check_scope(id => $id, type => 'user')) {
    … …  
    15111511    $page->param(logfor => 'user '.userFullName($dbh,$id));
    15121512  } elsif ($webvar{ltype} && $webvar{ltype} eq 'dom') {
    1513     $sql .= "domain_id=?";
    15141513    $id = $webvar{id};
    15151514    if (!check_scope(id => $id, type => 'domain')) {
    … …  
    15191518    $page->param(logfor => 'domain '.domainName($dbh,$id));
    15201519  } elsif ($webvar{ltype} && $webvar{ltype} eq 'rdns') {
    1521     $sql .= "rdns_id=?";
    15221520    $id = $webvar{id};
    15231521    if (!check_scope(id => $id, type => 'revzone')) {
    … …  
    15281526  } else {
    15291527    # Default to listing curgroup log
    1530     $sql .= "group_id=?";
    15311528    $page->param(logfor => 'group '.groupName($dbh,$id));
    15321529    # note that scope limitations are applied via the change-group check;
    15331530    # group log is always for the "current" group
    15341531  }
     1532  $webvar{ltype} = 'group' if !$webvar{ltype};
     1533  my $lcount = getLogCount($dbh, (id => $id, logtype => $webvar{ltype})) or push @debugbits, $DNSDB::errstr;
     1534
     1535  $page->param(id => $id);
     1536  $page->param(ltype => $webvar{ltype});
     1537
     1538  fill_fpnla($lcount);
     1539  fill_pgcount($lcount, "log entries", '');
     1540  $page->param(curpage => $webvar{page}.($webvar{ltype} ? "&ltype=$webvar{ltype}" : ''));
     1541
     1542  $sortby = 'stamp';
     1543  $sortorder = 'DESC';  # newest-first;  although filtering is probably going to be more useful than sorting
     1544# sort/order
     1545  $session->param($webvar{page}.'sortby', $webvar{sortby}) if $webvar{sortby};
     1546  $session->param($webvar{page}.'order', $webvar{order}) if $webvar{order};
     1547
     1548  $sortby = $session->param($webvar{page}.'sortby') if $session->param($webvar{page}.'sortby');
     1549  $sortorder = $session->param($webvar{page}.'order') if $session->param($webvar{page}.'order');
     1550
     1551  # Set up the column headings with the sort info
     1552  my @cols = ('fname','username','entry','stamp');
     1553  my %colnames = (fname => 'Name', username => 'Username/Email', entry => 'Log Entry', stamp => 'Date/Time');
     1554  fill_colheads($sortby, $sortorder, \@cols, \%colnames);
     1555
     1556##fixme:  increase per-page limit or use separate limit for log?  some ops give *lots* of entries...
     1557  my $logentries = getLogEntries($dbh, (id => $id, logtype => $webvar{ltype},
     1558        offset => $webvar{offset}, sortby => $sortby, sortorder => $sortorder));
     1559  $page->param(logentries => $logentries);
     1560
    15351561##fixme:
    15361562# - filtering
    15371563# - show reverse zone column?
    1538 # - pagination/limiting number of records - put newest-first so user
    1539 #   doesn't always need to go to the last page for recent activity?
    1540   my $sth = $dbh->prepare($sql);
    1541   $sth->execute($id);
    1542   my @logbits;
    1543   while (my ($uid, $email, $name, $entry, $stamp) = $sth->fetchrow_array) {
    1544     my %row;
    1545     $row{userfname} = $name;
    1546     $row{userid} = $uid;
    1547     $row{useremail} = $email;
    1548     ($row{logentry} = $entry) =~ s/\n/<br>\n/g;
    1549     ($row{logtime}) = ($stamp =~ /^(.+)-\d\d$/);
    1550     push @logbits, \%row;
    1551   }
    1552   $page->param(logentries => \@logbits);
     1564# - on log record creation, bundle "parented" log actions (eg, "AXFR record blah for domain foo",
     1565#   or "Add record bar for new domain baz") into one entry (eg, "AXFR domain foo", "Add domain baz")?
     1566#   need a way to expand this into the complete list, and to exclude "child" entries
    15531567
    15541568  # scope check fail target

  • trunk/templates/log.tmpl

    r180 r323  
    1010
    1111<table border="0" width="90%">
    12 <tr><th colspan="5"><div class="center maintitle">Log entries for <TMPL_VAR NAME=logfor></div></th></tr>
    13   <tr class="darkrowheader">
    14       <td>Name</td>
     12<tr><th colspan="3"><div class="center maintitle">Log entries for <TMPL_VAR NAME=logfor></div></th></tr>
     13<tr>
     14<td class="leftthird"><TMPL_INCLUDE NAME="pgcount.tmpl"></td>
     15<td align="center"><TMPL_INCLUDE NAME="fpnla.tmpl"></td>
     16<td class="rightthird">&nbsp;</td>
     17</tr>
     18</table>
     19<table border="0" width="90%">
    1520      <!-- Not sure "Customer ID" (filled with uid) is of any use... -->
    1621      <!-- td>Customer ID</td -->
    17       <td>Username/Email</td>
    18       <td>Log Entry</td>
    19       <td>Date / Time</td>
    20   </tr>
     22<tr class="darkrowheader">
     23<TMPL_LOOP NAME=colheads><TMPL_IF firstcol></TMPL_IF>
     24        <td><a href="dns.cgi?sid=<TMPL_VAR NAME=sid>&amp;page=<TMPL_VAR NAME=page><TMPL_IF
     25 NAME=offset>&amp;offset=<TMPL_VAR NAME=offset></TMPL_IF>&amp;sortby=<TMPL_VAR
     26 NAME=sortby>&amp;order=<TMPL_VAR NAME=order>&amp;id=<TMPL_VAR NAME=id>&amp;ltype=<TMPL_VAR
     27 NAME=ltype>"><TMPL_VAR NAME=colname></a><TMPL_IF
     28 NAME=sortorder>&nbsp;<img alt="<TMPL_VAR NAME=sortorder>" src="images/<TMPL_VARNAME=sortorder>.png"
     29 /></TMPL_IF></td></TMPL_LOOP>
     30</tr>
     31
    2132<TMPL_IF logentries>
    2233<TMPL_LOOP NAME=logentries>
Note: See TracChangeset for help on using the changeset viewer.