diff options
| -rw-r--r-- | TODO.txt | 10 | ||||
| -rw-r--r-- | scripts/schema1-to-2.py | 84 |
2 files changed, 85 insertions, 9 deletions
@@ -1,20 +1,12 @@ -* Get tests running in their own directory * Write CLI * in progress, using click - https://click.pocoo.org/ * Write GUI + * in progress (tkinter) * Write docs * How? Sphinx? Needs research --- -Consider adding a 'dates' table that matches games to dates for purchase, -beating, and completing. Currently implemented via RFC2822-style headers within -the 'notes' field. More research is needed to determine if the notes field or a -table is a better way to achieve this. If an addition to the database format is -deemed necessary, a restructuring may be in order. - ---- - One of the things curious about managing a game collection that doubles as a backlog is, you get games on systems that were originally on other systems. How do you classify those games? The original game is the actual content you're 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)) |