scripts: Add schema v1->v2 migration scriptnext

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.

2 files changed, 85 insertions, 9 deletions

diff --git a/TODO.txt b/TODO.txt
index 6f54d7f..705f65c 100644
--- a/TODO.txt
+++ b/TODO.txt
@@ -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))