Purges attachments from a trac instance, after a ticket has been closed for more than a given number of days.

trac_attachment_clean.sh

#!/bin/bash -e
days_before_purge=30
tracdb="/var/www/trac/db/trac.db"
attachment_dir="/var/www/trac/attachments/ticket"

sqlite3="/usr/bin/sqlite3"
sql_closed="select distinct id from ticket join attachment using (id) where status = \"closed\" and datetime(changetime/1000000, 'unixepoch') <= datetime('now', '-${days_before_purge} days');"
closed_tickets=$(echo ${sql_closed} | ${sqlite3} ${tracdb})

for id in $closed_tickets; do
    delete_sql="delete from attachment where id=\"${id}\";"
    ticket_directory="${attachment_dir}/${id}"
    if [ "$(ls -A ${ticket_directory})" ]; then
        echo "Deleting attachment files for ticket #${id}"
        rm -f ${ticket_directory}/*
    fi
    echo "Deleting attachments from db for ticket #${id}"
    echo ${delete_sql} | ${sqlite3} ${tracdb}
done