Skip to content

Instantly share code, notes, and snippets.

@oprypin
Last active November 10, 2018 17:58
Show Gist options
  • Save oprypin/339c699bce38eb1f898333df96022563 to your computer and use it in GitHub Desktop.
Save oprypin/339c699bce38eb1f898333df96022563 to your computer and use it in GitHub Desktop.
#!/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()
#!/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'
)
"
[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
[Unit]
Description=Daily maintenance of Quassel logs
[Timer]
OnCalendar=*-*-* 01:00:00
Persistent=true
[Install]
WantedBy=timers.target
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment