From cb4300cd23ed4af9207d23c0b437870294cb161c Mon Sep 17 00:00:00 2001 From: zlg Date: Fri, 22 Sep 2023 01:02:37 -0700 Subject: scripts: Add schema v1->v2 migration script This script adds three columns to the schema, supporting the "Purchased", "Beaten", and "Completed" note headers. They are now converted to a UNIX timestamp and stored in a separate column so queries made against that metadata are easier. The library itself still needs to support all the new columns. --- scripts/schema1-to-2.py | 84 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 84 insertions(+) create mode 100644 scripts/schema1-to-2.py (limited to 'scripts') diff --git a/scripts/schema1-to-2.py b/scripts/schema1-to-2.py new file mode 100644 index 0000000..ac74789 --- /dev/null +++ b/scripts/schema1-to-2.py @@ -0,0 +1,84 @@ +import sqlite3 +import vgstash as vgs +import datetime as dt +import argparse + +# Migrate VGStash schema v1 to schema v2: +# + purchase_date : UNIX timestamp, date of a game's purchase +# + beaten_date : UNIX timestamp, date a game was beaten +# + complete_date : UNIX timestamp, date a game was completed 100% + +parser = argparse.ArgumentParser( + prog="schema1-to-2", + description="Migrate a VGStash database, in-place, from v1 to v2. This adds three columns and moves header data." + ) + +parser.add_argument( + "dbfile", + help="path to a VGStash database file", + ) + +opts = parser.parse_args() + +vgdb = vgs.DB(path=opts.dbfile) + +# Step 1: Add columns +columns = [ + ["Purchased: ", "p_date"], + ["Beaten: ", "b_date"], + ["Completed: ", "c_date"], + ] +for c in columns: + try: + print("Adding '{}' column...".format(c[1])) + # This is RISKY and RIPE FOR INJECTION. SQLite does not support + # parameterized column names during creation; this is a workaround. + vgdb.conn.execute("ALTER TABLE games ADD COLUMN {} INTEGER DEFAULT NULL".format(c[1])) + vgdb.conn.commit() + print("Success!") + except sqlite3.OperationalError: + print("Column {} already exists in the database. Skipping.".format(c[1])) + +# Step 2: Fetch notes +res = vgdb.conn.execute("SELECT rowid,notes FROM games WHERE notes NOT LIKE ''") +targets = res.fetchall() +converted_lines = 0 +removed_notes = 0 +for row in targets: + lines = row['notes'].splitlines() + newlines = [] + # Step 3: Check for headers in notes + for l in lines: + has_header = False + for i in columns: + if l.startswith(i[0]): + # Step 4: Convert and add data to new column location + has_header = True + isodate = l.split(":")[1].strip() + unixdate = dt.date.fromisoformat(isodate).strftime("%s") + # Same warning applies to ''.format usage and SQL queries + set_ts = vgdb.conn.execute("UPDATE games SET {} = ? WHERE rowid = ?".format(i[1]), (unixdate, row['rowid'])) + vgdb.conn.commit() + if (set_ts.rowcount > 0): + converted_lines+= 1 + continue + + if not has_header: + newlines.append(l) + if len(newlines) > 0: + for l in newlines: + if len(l) == 0: + newlines.remove(l) + else: + break + + # Step 5: Update notes, if any lines were removed in Step 4. + if (len(newlines) < len(lines)): + set_notes = vgdb.conn.execute("UPDATE games SET notes = ? WHERE rowid = ?", ("\n".join(newlines), row['rowid'])) + vgdb.conn.commit() + if (set_notes.rowcount > 0): + removed_notes+=1 + +# Step 6: Summarize what was done. +print("{} games' notes were updated.".format(removed_notes)) +print("{} details converted from Notes header to Column.".format(converted_lines)) -- cgit v1.2.3-54-g00ecf