convert_db

#!/usr/bin/env python3

import sqlite3
import sys 

# this script converts the database of the previous version of the cyberbot to the current version
# it takes into account the two plugins affected by this change: gitlab and voting

def copy_rooms(c):
    c.execute("""INSERT INTO main.rooms SELECT * FROM old.rooms;""")
    print(c.rowcount)

def set_plugin_room_info(c):
    c.execute("""INSERT INTO main.room_plugins SELECT roomid, pluginname FROM old.room_plugins;""")
    print(c.rowcount)

def convert_gitlab(c):
    c.execute("""INSERT INTO main.room_plugin_data
            SELECT old.room_plugins.roomid, 'gitlab', old.plugin_data.key, old.plugin_data.value
            FROM old.room_plugins INNER JOIN old.plugin_data ON old.plugin_data.pluginid = old.room_plugins.pluginid
            where old.plugin_data.key = "gitlabtokens" or old.plugin_data.key = "config";""")
    print(c.rowcount)

def convert_voting(c):
    c.execute("""INSERT INTO main.room_plugin_data
            SELECT old.room_plugins.roomid, 'voting', old.plugin_data.key, old.plugin_data.value
            FROM old.room_plugins INNER JOIN old.plugin_data ON old.plugin_data.pluginid = old.room_plugins.pluginid
            where old.plugin_data.key = "active_polls" or old.plugin_data.key = "onlyadmincreators";""")
    print(c.rowcount)

def load_dbs(new_db, old_db):
    conn = sqlite3.connect(new_db)
    c = conn.cursor()
    tables = c.execute("""
        SELECT name
        FROM sqlite_master
        WHERE type ='table' AND name NOT LIKE 'sqlite_%';
        """).fetchall()
    # attention here, meaning of "plugin_data" has changed
    # room_data: global room data
    # plugin_data: global plugin data
    # room_plugin_data: data local to a plugin x room combination
    # room_plugins: which plugins are loaded in which room
    if not all((t,) in tables for t in ["rooms", "plugins", "room_plugins", "room_data", "plugin_data", "room_plugin_data"]):
        c.execute("""
        CREATE TABLE rooms (
            roomid     VARCHAR PRIMARY KEY
        );
        """)
        c.execute("""
        CREATE TABLE plugins (
            pluginname VARCHAR PRIMARY KEY
        );
        """)
        c.execute("""
        CREATE TABLE room_plugins (
            roomid     VARCHAR,
            pluginname VARCHAR,
            PRIMARY KEY (roomid, pluginname)
        );
        """)
        c.execute("""
        CREATE TABLE room_data (
            roomid     VARCHAR,
            key        VARCHAR,
            value      TEXT,
            PRIMARY KEY (roomid, key)
        );
        """)
        c.execute("""
        CREATE TABLE plugin_data (
            pluginname VARCHAR,
            key        VARCHAR,
            value      TEXT,
            PRIMARY KEY (pluginname, key)
        );
        """)
        c.execute("""
        CREATE TABLE room_plugin_data (
            roomid     VARCHAR,
            pluginname VARCHAR,
            key        VARCHAR,
            value      TEXT,
            PRIMARY KEY (roomid, pluginname, key)
        );
        """)
    conn.execute("""ATTACH DATABASE (?) AS old;""", (old_db,));
    return conn

def main():
    if (len(sys.argv) != 3):
        print("Usage: convert_db new_db old_db")
        return
    conn = load_dbs(sys.argv[1], sys.argv[2])
    c = conn.cursor()
    copy_rooms(c)
    conn.commit()
    set_plugin_room_info(c)
    conn.commit()
    convert_gitlab(c)
    conn.commit()
    convert_voting(c)
    conn.commit()

if __name__ == "__main__":
    main()