Viewing recently edited etherpads

Prescript: The amazing etherpad was recently open sourced. Get it here: http://code.google.com/p/etherpad. This is the first question that I know of on StackOverflow about the etherpad code. If you're part of the etherpad open source community, you might want to subscribe to the RSS feed for questions tagged 'etherpad' just in case this catches on!

My actual question, which assumes you have etherpad installed on your own server:

First, here's a query to view recently edited pads:

SELECT id,lastWriteTime,creationTime,headRev 
  FROM PAD_SQLMETA ORDER BY lastWriteTime, headRev;

Or, if you want to run it from a unix prompt:

mysql -u root -pPASSWD etherpad -e "select id,lastWriteTime,creationTime,headRev 
  from PAD_SQLMETA order by lastWriteTime, headRev"

That's handy, however lastWriteTime actually gets updated every time someone so much as views a pad in their browser. I'd rather sort the pads by when they were actually last edited. There's probably a fancy SQL query involving a join with another table that would show actual last edit time. Does anyone know what that is? Alternatively, you could have a script that notices when headRev changes but that doesn't seem like the cleanest way to do it.

Solution

I haven't quite figured out the answer to my original question but I wrote a script that achieves something similar. It's main purpose is to export plain text versions of all my pads and store them on my laptop. As a side effect it shows me which pads have changed and lets me see a diff since the last exported version. It also shows which ones were newly created.

First, create the following script, padlist.pl, on the server that hosts your etherpad instance:

#!/usr/bin/env perl

$list = `mysql -u root -pPASSWD etherpad -e 
         "select id from PAD_SQLMETA order by lastWriteTime DESC, headRev DESC"`;

$list =~ s/^id\s*\n//s;  # get rid of the column header mysql adds.
print $list;

Then run the following script, fetchall.pl, on your local machine. It will suck down snapshots of all your pads and tell you which ones have changed and which have newly appeared.

#!/usr/bin/env perl

use LWP::Simple qw(get);
$| = 1;  # autoflush.
$server = "server.com"; # the server that hosts your etherpad instance.

$pads = `ssh $server etherpad/padlist.pl`;
@padlist = split(/\s+/, $pads);

$neednewline = 0; # printing "." for each pad where nothing to be done.
for(@padlist) {
  $ep = $_;
  $localfile = "$ep.txt";
  if(-e $localfile) { 
    $localexists = 1; 
    $localcontent = do {local (@ARGV,$/) = $localfile; <>};
  } else { $localexists = 0; }
  $livecontent = get("http://$server/ep/pad/export/$ep/latest?format=txt");
  if($livecontent ne $localcontent) {
    if($neednewline) { print "\n";  $neednewline = 0; }
    if($localexists) { 
      print "CHANGED: $ep\n"; 
      open(F, ">prev/$localfile") or die "Probably need to create 'prev' dir.";
      print F $localcontent;
      close(F);
    } else { print "NEW:     $ep\n"; }
    open(F, ">$localfile") or die;
    print F $livecontent;
    close(F);
  } else {
    print ".";
    $neednewline = 1;
} }

To see a diff of pad foo since the last time I fetched it:

diff prev/foo.txt foo.txt