Remove old logs from Quassel SQLite database

purge_logs.sh

#!/bin/bash

set -u

CURRENT_PATH="/var/lib/quassel/quassel-storage.sqlite"
BAK_PATH="/data/backup/quasselcore/quassel-storage.sqlite.bak"

DATE_TO_PRUNE='-180 day'

# This is an additional filter to only prune from a selected list of buffers,
# you can make this variable an empty string if not needed
# (the ids can be found in the table "buffer" and depend on your case, this is just for example)
BUFFER_IDS_TO_PRUNE='
AND bufferid IN (
        302, -- #rust-fr
        301, -- ##rust
        300, -- #C++-general
        298, -- #gitea
        296, -- #gitlab
        295, -- #postgresql
        293, -- #c++-basic
        292, -- #syncthing
        224, -- #ipfs
        161, -- #syncthing
        192  -- #postgis    -- note: no comma for the last one!
)
'

SQL_QUERY_PRUNE="
DELETE
FROM backlog
WHERE time < strftime('%s', 'now', '${DATE_TO_PRUNE}') * 1000.0
${BUFFER_IDS_TO_PRUNE}
;
"

cur_ts() {
    echo "[$(date --iso-8601=seconds)] "
}  
   
start_step() {
    echo -n "$(cur_ts) $*"
}  
   
done_step() {
    echo ".. done"
}  
   
die() {
    echo -e "\n$(cur_ts)$*"
        exit 1
}  
   
is_quassel_running() {
        pgrep quasselcore > /dev/null
        echo $?
}  
   
echo "$(cur_ts) == Start =="
   
# ----------------------------------------------------------------------------------------
start_step "Checking whether quassel is running .."
if [[ "$(is_quassel_running)" -eq 0 ]]; then
        echo -e "\n$(cur_ts) ERROR: quassel is running, stop it first!"
        exit 1;
fi 
done_step
   
[[ -f "${CURRENT_PATH}" ]] || die "unable to find current database at '${CURRENT_PATH}'"
   
# ----------------------------------------------------------------------------------------
start_step "Creating a backup of the db .."

# temporarily rename any old backup while we are working, will be deleted at the end only
if [[ -f "${BAK_PATH}" ]]; then
   mv "${BAK_PATH}" "${BAK_PATH}~"
fi

cp "${CURRENT_PATH}" "${BAK_PATH}" || die "unable to create a backup copy at '${BAK_PATH}'"

done_step

# ----------------------------------------------------------------------------------------
start_step "Cleaning up the database .."
# purge the db from old entry
sqlite3 "${CURRENT_PATH}" "${SQL_QUERY_PRUNE}" || die "Purge failed"
done_step

# ----------------------------------------------------------------------------------------
#start_step "Vacuuming database .."
## vacuum the db to save disk space (the db doesn't shrink automatically)
#sqlite3 "${CURRENT_PATH}" VACUUM || die "Vacuum failed"
#done_step

# ----------------------------------------------------------------------------------------
if [[ -f "${BAK_PATH}~" ]]; then
    start_step "Removing previous backup .."
    rm "${BAK_PATH}~" || die "Failed to remove previous backup"
    done_step
fi

echo "$(cur_ts) == End =="