quassel-txt-export.py

"""
Python script to export all quassel (sqlite) database buffers to a human-readable username_network_buffer.txt
"""


import sqlite3
from datetime import datetime

db = sqlite3.connect("quassel-storage.sqlite")

def getUsers():
    c = db.cursor()
    c.execute("SELECT userid, username FROM quasseluser")

    users = {}

    for row in c.fetchall():
        uid, uname = row
        users[uid] = dict(id=uid,name=uname)
        print ("User: %d - %s" % (uid, uname))
    return users

def getNetworkIds():
    global db
    c = db.cursor()
    ids = {}

    c.execute("SELECT networkid, networkname, userid FROM network")

    for row in c.fetchall():
        nid, name, uid = row
        ids[nid] = dict(id=nid,name=name, userid=uid)
        print (" * %d - %s" % (nid, name))
    return ids

def getBuffers(n):
    nid = n['id']
    name = n['name']

    buffers = {}
    c = db.cursor()
    c.execute("SELECT bufferid, buffername FROM buffer WHERE networkid = ?", (nid,))

    for row in c.fetchall():
        bid, bname = row
        if len(bname.strip()) <= 0: bname = "no_buffer_name"
        buffers[bid] = dict(id=bid, name=bname)
        #print ("  %s > %s > %d - %s" % (users[n['userid']]['name'], name, bid, bname))
    return buffers

users = getUsers()
print ("Found %d users ..." % (len(users), ))
networks = getNetworkIds()
print ("Found %d networks ..." % (len(networks),))
print ("Creating one file per user per network per buffer ...\n")

for ui in users.keys():
    u = users[ui]
    print ("> User:", u['name'])
    for i in networks.keys():
        n = networks[i]
        if n['userid'] != ui: continue
        print ("  * Net:", n['name'])
        buffers = getBuffers(n)
        for bi in buffers.keys():
            b = buffers[bi]
            print ("    - Buffer:", b['name'])

            filename = "%s_%s_%s.txt" % (u['name'], n['name'], b['name'])
            with open(filename, "wb") as f:
                c = db.cursor()
                c.execute("SELECT time, message, sender.sender FROM backlog INNER JOIN (sender) ON (backlog.senderid = sender.senderid)  WHERE bufferid = ? ORDER BY backlog.time ASC", (bi,))
                for row in c.fetchall():
                    ts, message, sender = row
                    time = datetime.fromtimestamp(int(ts))
                    if '!' in sender:
                        sender = sender[:sender.index('!')]
                    f.write(("%s <%s> %s\n" % (time, sender, message)).encode('utf8'))