1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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))
|
