Weekly purge of Quassel chat database for sqlite instances

purge_quassel_db-sqlite.sh

#!/bin/sh
# Taken from http://blog.encomiabile.it/2011/02/03/prune-quassel-database/
# Taken from https://gist.github.com/Und3rf10w/36a2034cb2c8eafbfb801c1a89e1f8bc

BAK_PATH="${HOME}/.config/quassel-irc.org/quassel-storage.sqlite.bak"
CURRENT_PATH="${HOME}/.config/quassel-irc.org/quassel-storage.sqlite"
# first day of data that will be maintained
# -7 day means that *every* chatline stored before 8 days ago and so on are going to be eliminated.
#         only the last 7 days are keeped.
DATE_TO_PRUNE='-7 day'

die() {
  echo "$@"
  exit 1
}

# is quassel running?
is_quassel_running() {
  pgrep "^quassel$" > /dev/null
  echo $?
}

if [ $(is_quassel_running) -eq 0 ]; then
  echo "ERROR: quassel is running, stop it first!"
  exit 1;
fi

SCHEMA_VERSION="$(sqlite3 "$CURRENT_PATH" "SELECT value FROM coreinfo WHERE key = 'schemaversion';")"
SCRIPT_TESTED_SCHEMA_VERSION="31"
if [ "$SCHEMA_VERSION" -ne "$SCRIPT_TESTED_SCHEMA_VERSION" ]; then
  echo "ERROR: unexpected SQL schema version $SCHEMA_VERSION, expected $SCRIPT_TESTED_SCHEMA_VERSION. The script might not work on different schema versions."
  exit 1
fi

echo -n "Creating a backup and a temporary copy of the db .."

mv "$CURRENT_PATH" "$BAK_PATH" || die "unable to create a copy backup"
cp "$BAK_PATH" "$BAK_PATH.tmp" || die "unable to create a temporary copy of the db"
echo ".. done!"

echo -n "Cleaning up the database .."

# purge the db from old entry
sqlite3 "$BAK_PATH.tmp" "DELETE FROM backlog WHERE time < strftime('%s','now','${DATE_TO_PRUNE}') * 1000;" || die "Purge failed"
sqlite3 "$BAK_PATH.tmp" "DELETE FROM sender WHERE sender.senderid NOT IN (SELECT senderid FROM backlog);" || die "Purge failed"
echo ".. done!"

echo -n "Rebuilding database .."
# rebuild the db to save disk space (the db doesn't shrink automatically)
sqlite3 "$BAK_PATH.tmp" ".dump" | sqlite3 "$CURRENT_PATH" || die "Rebuild failed"
echo ".. done!"

echo -n "Deleting temporary files .."

# delete rubbish
rm "$BAK_PATH.tmp" || die "rm failed"
echo ".. done!"