64 lines
1.9 KiB
Bash
64 lines
1.9 KiB
Bash
#!/usr/bin/env bash
|
|
#
|
|
# Löscht anonyme Kutt-Links die seit %MAX_AGE_DAYS% Tagen nicht mehr aufgerufen wurden.
|
|
# Als Referenzdatum gilt der letzte Visit oder — falls nie aufgerufen — das Erstelldatum.
|
|
#
|
|
# Nutzung: Per Cron z.B. täglich ausführen.
|
|
# 0 3 * * * /home/docker-projekte/kutt/scripts/kutt-links-cleanup.sh
|
|
#
|
|
|
|
set -euo pipefail
|
|
|
|
CONTAINER="kutt-db"
|
|
DB_USER="kutt"
|
|
DB_NAME="kutt"
|
|
MAX_AGE_DAYS=365
|
|
DRY_RUN="${1:-}"
|
|
|
|
SQL_SELECT="
|
|
SELECT l.id, l.address, l.target,
|
|
COALESCE(MAX(v.created_at), l.created_at) AS last_activity
|
|
FROM links l
|
|
LEFT JOIN visits v ON v.link_id = l.id
|
|
WHERE l.user_id IS NULL
|
|
GROUP BY l.id
|
|
HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days'
|
|
ORDER BY last_activity;
|
|
"
|
|
|
|
SQL_DELETE="
|
|
DELETE FROM links
|
|
WHERE id IN (
|
|
SELECT l.id
|
|
FROM links l
|
|
LEFT JOIN visits v ON v.link_id = l.id
|
|
WHERE l.user_id IS NULL
|
|
GROUP BY l.id
|
|
HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days'
|
|
);
|
|
"
|
|
|
|
echo "$(date '+%Y-%m-%d %H:%M:%S') — Cleanup anonymer Links (älter als ${MAX_AGE_DAYS} Tage ohne Zugriff)"
|
|
|
|
if [ "$DRY_RUN" = "--dry-run" ]; then
|
|
echo "DRY RUN — es wird nichts gelöscht."
|
|
docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -c "$SQL_SELECT"
|
|
else
|
|
CANDIDATES=$(docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -t -A -c "
|
|
SELECT COUNT(*) FROM links l
|
|
LEFT JOIN visits v ON v.link_id = l.id
|
|
WHERE l.user_id IS NULL
|
|
GROUP BY l.id
|
|
HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days';
|
|
" | wc -l)
|
|
|
|
if [ "$CANDIDATES" -eq 0 ]; then
|
|
echo "Keine abgelaufenen anonymen Links gefunden."
|
|
exit 0
|
|
fi
|
|
|
|
echo "${CANDIDATES} Link(s) werden gelöscht..."
|
|
docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -c "$SQL_DELETE"
|
|
echo "Fertig."
|
|
fi
|