SQL Queries for purgin old data from kiwibnc messages.db

kiwibnc-purge-db.sql

/* Test how many records the UTC date would remove */
SELECT (SELECT COUNT(*) FROM logs WHERE time <= strftime('%s000','2022-05-25 12:50:00')) || '/' || (SELECT COUNT(*) FROM logs)

/* Remove records from logs before the set UTC date */
DELETE FROM logs WHERE time <= strftime('%s000','2022-05-25 12:31:00');

/* Clean data of any orphaned records */
DELETE FROM data WHERE id NOT IN (
    SELECT msgtagsref AS ids FROM logs
    UNION
    SELECT dataref AS ids FROM logs
    UNION
    SELECT prefixref AS ids FROM logs
    UNION
    SELECT paramsref AS ids FROM logs
);