Remove old logs from Quassel SQLite database

purge_logs.sh

#!/bin/sh

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
# -15 day means that *every* chatline stored before 16 days ago and so on are going to be eliminated.
#	  only the last 15 days are keeped.
DATE_TO_PRUNE='-15 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

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}');" || 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!"

You can also rebuild the database in place with the SQLite VACUUM command: sqlite3 file.sqlite VACUUM.

Thanks for sharing this but I believe it doesn't work. Quassel stores the time in milliseconds epoch time. Yet the strftime('%s', 'now') function returns epoch time in seconds. This script is unlikely to remove any lines.

The solution is to trim the last 3 characters from the time column.

SELECT count(*)
FROM backlog 
WHERE SUBSTR(time, 1, LENGTH(time)-3) < strftime('%s','now','-15 day');

The more appropriate fix would be the other way around:

SELECT COUNT(*) 
FROM backlog 
WHERE time < (julianday('now', '-15 day') - 2440587.5)*86400000.0

(based on the last example here: https://www.sqlite.org/lang_datefunc.html)

or, if you have sqlite3 >= 3.38.0, you can use unixepoch (untested):

SELECT COUNT(*) 
FROM backlog 
WHERE time < unixepoch('now', '-15 day')*1000.0