Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Remove old logs from Quassel SQLite database
#!/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!"
@bbarenblat

This comment has been minimized.

Copy link

@bbarenblat bbarenblat commented May 15, 2020

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

@sir-ragna

This comment has been minimized.

Copy link

@sir-ragna sir-ragna commented Dec 19, 2020

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.

@sir-ragna

This comment has been minimized.

Copy link

@sir-ragna sir-ragna commented Dec 19, 2020

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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment