How to list all pads

This was posted to the mailing list by Daniel Reeves. The script was actually written by Rob Felty.

The following script will extract the full list of public pads sorted by number of revisions:

mysql -u USER -pPSW etherpad_lite -e 'select store.key from store' \
   | grep -Eo '^pad:[^:]+' \
   | sed -e 's/pad://' \
   | sort \
   | uniq -c \
   | sort -rn \
   | awk '{if ($1!="2") {print $2 }}'

or even simpler:

select distinct substring(store.key,5,locate(':',store.key,5)-5) from store where store.key like "pad:%"

sqlite3:

#!/bin/bash
sqlite3 ./pad.db 'select store.key from store' \
   | grep -Eo '^pad:[^:]+' \
   | sed -e 's/pad://' \
   | sort \
   | uniq -c \
   | sort -rn \
   | awk '{if ($1!="2") {print $2 }}'