Skip to content

Instantly share code, notes, and snippets.

@miohtama
Created January 27, 2017 10:04
Show Gist options
  • Save miohtama/769dff10e5a3d633de8c364cda78a762 to your computer and use it in GitHub Desktop.
Save miohtama/769dff10e5a3d633de8c364cda78a762 to your computer and use it in GitHub Desktop.
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
Copy link

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

@sir-ragna
Copy link

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
Copy link

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');

@oxc
Copy link

oxc commented Jul 27, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment