aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorzlg <zlg@zlg.space>2023-09-22 01:02:37 -0700
committerzlg <zlg@zlg.space>2023-09-22 01:02:37 -0700
commitcb4300cd23ed4af9207d23c0b437870294cb161c (patch)
treec47074f62a5aee1675523a765febd4015f3aeb5c
parenttests: Add long-lost test_import.json (diff)
downloadvgstash-cb4300cd23ed4af9207d23c0b437870294cb161c.tar.gz
vgstash-cb4300cd23ed4af9207d23c0b437870294cb161c.tar.bz2
vgstash-cb4300cd23ed4af9207d23c0b437870294cb161c.tar.xz
vgstash-cb4300cd23ed4af9207d23c0b437870294cb161c.zip
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.
-rw-r--r--TODO.txt10
-rw-r--r--scripts/schema1-to-2.py84
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))