A script to set to a cron tab to clear the sequencer.sqlite db
2
truncate_seq.sh
1#!/usr/bin/env bash
2# Trims the sequencer.sqlite db on a PDS
3# usage {db path} {hours back to trim}
4# /pds/sequencer.sqlite 48
5set -euo pipefail
6# Uncomment to log the out put somewhere
7#exec &>> /pds/capture-log.txt
8DB_PATH="${1:-sequencer.sqlite}"
9HOURS_AGO="${2:-48}"
10
11# Calculate the cutoff timestamp in ISO8601 format
12TRUNCATE_FROM=$(date -u -d "${HOURS_AGO} hours ago" +"%Y-%m-%dT%H:%M:%S.000Z" 2>/dev/null \
13 || date -u -v-${HOURS_AGO}H +"%Y-%m-%dT%H:%M:%S.000Z") # macOS fallback
14
15echo "Deleting rows from repo_seq where sequencedAt < ${TRUNCATE_FROM}"
16
17sqlite3 \
18 -cmd ".timeout 10000" \
19 "${DB_PATH}" \
20 "PRAGMA journal_mode=WAL;
21 PRAGMA busy_timeout=10000;
22 DELETE FROM repo_seq WHERE sequencedAt < '${TRUNCATE_FROM}';"
23
24echo "Done."