factoids_upgrade_db.py

factoids_upgrade_db.py

# Migration script for https://github.com/ProgVal/Limnoria/commit/436d2bade8557111d13a586ab845ed43ff5872c8

import sqlite3
import sys

try:
    (_, old_path, new_path) = sys.argv
except ValueError:
    print("Syntax: %s <path_to_old.db> <path_to_new.db>")
    exit(1)

old_db = sqlite3.connect(old_path)
old_cur = old_db.cursor()

db = sqlite3.connect(new_path)
cur = db.cursor()

cur.execute("""CREATE TABLE keys (
               id INTEGER PRIMARY KEY,
               key TEXT UNIQUE ON CONFLICT REPLACE
               )""")
cur.execute("""CREATE TABLE factoids (
               id INTEGER PRIMARY KEY,
               added_by TEXT,
               added_at TIMESTAMP,
               fact TEXT UNIQUE ON CONFLICT REPLACE,
               locked BOOLEAN
               )""")
cur.execute("""CREATE TABLE relations (
               id INTEGER PRIMARY KEY,
               key_id INTEGER,
               fact_id INTEGER,
               usage_count INTEGER
               )""")

old_cur.execute("""SELECT key, added_by, added_at, fact, locked
                   FROM keys
                   INNER JOIN factoids ON (keys.id=factoids.key_id)
                   """)

for (key, added_by, added_at, fact, locked) in old_cur:
    cur.execute("SELECT id FROM keys WHERE key=?", (key,))
    rows = list(cur)
    if not rows:
        cur.execute("INSERT INTO keys(key) VALUES (?)", (key,))

    cur.execute("""INSERT INTO factoids (added_by, added_at, fact, locked)
                   VALUES (?, ?, ?, ?)""",
                (added_by, added_at, fact, locked))

    cur.execute("SELECT id FROM keys WHERE key=?", (key,))
    rows = list(cur)
    if rows:
        ((key_id,),) = rows

    cur.execute("SELECT id FROM factoids WHERE fact=?", (fact,))
    rows = list(cur)
    if rows:
        ((fact_id,),) = rows

    cur.execute("""INSERT INTO relations(key_id, fact_id, usage_count)
                   VALUES (?, ?, 0)
                   """,
                (key_id, fact_id))

db.commit()