dump_quassel_logs.py

#!/usr/bin/env python3
"""Create a file tree with all chat logs from Quassel.

Usage:
    ./dump_quassel_logs.py /var/lib/quassel/quassel-storage.sqlite

The file names will have this format:
    ./network/#channel/YYYY-MM-DD.log
The lines in each file will have this format:
    [HH:MM:SS] <nick> message
"""
import itertools
import logging
import os
import sqlite3
import sys

conn = sqlite3.connect(sys.argv[1], detect_types=sqlite3.PARSE_COLNAMES)

cur = conn.execute('''
    SELECT CASE WHEN value >= 31 THEN 1000.0 ELSE 1.0 END
      FROM coreinfo
     WHERE key = 'schemaversion'
''')
[(time_multiplier,)] = cur

cur = conn.execute('''
      SELECT backlog.bufferid
           , lower(network.networkname)
           , buffer.buffercname
        FROM backlog, buffer, network
       WHERE backlog.bufferid = buffer.bufferid
         AND buffer.networkid = network.networkid
    GROUP BY backlog.bufferid
''')
for bufferid, network, channel in cur:
    os.makedirs(f'{network}/{channel}', exist_ok=True)
    cur = conn.execute('''
          SELECT datetime(backlog.time / ?, 'unixepoch') '[timestamp]'
               , substr(sender.sender, 1, instr(sender.sender || '!', '!') - 1)
               , backlog.message
            FROM backlog, sender
           WHERE backlog.bufferid = ?
             AND backlog.senderid = sender.senderid
             AND backlog.type IN (0x1, 0x4)
        ORDER BY backlog.time
    ''', (time_multiplier, bufferid))
    for date, group in itertools.groupby(cur, lambda row: row[0].date()):
        filename = f'{network}/{channel}/{date}.log'
        print(f'Writing to {filename!r}', file=sys.stderr)
        with open(filename, 'w') as file:
            for time, nick, message in group:
                file.write(f'[{time:%H:%M:%S}] <{nick}> {message}\n')

conn.close()

prune_quassel_logs.sh

#!/bin/bash
set -u
set -x

delete_before="$(date '+%Y-%m-%d' -d "${2- 7 days ago}")"
sqlite3 "$1" "
    DELETE FROM backlog
    WHERE time < strftime('%s', '${delete_before}') * (
        SELECT CASE WHEN value >= 31 THEN 1000.0 ELSE 1.0 END
        FROM coreinfo
        WHERE key = 'schemaversion'
    )
"

quassel-logs.service

[Unit]
Description=Quassel logs maintenance

[Service]
User=quassel
Type=oneshot
WorkingDirectory=/full/path/to/irclogs
ExecStart=/full/path/to/dump_quassel_logs.py /var/lib/quassel/quassel-storage.sqlite
ExecStart=/full/path/to/prune_quassel_logs.sh /var/lib/quassel/quassel-storage.sqlite

quassel-logs.timer

[Unit]
Description=Daily maintenance of Quassel logs

[Timer]
OnCalendar=*-*-* 01:00:00
Persistent=true

[Install]
WantedBy=timers.target