diff options
| -rw-r--r-- | TODO.txt | 10 | ||||
| -rwxr-xr-x | scripts/schema1-to-2.py | 86 | ||||
| -rwxr-xr-x | src/vgstash/__init__.py | 57 | ||||
| -rwxr-xr-x | src/vgstash_cli.py | 127 | ||||
| -rw-r--r-- | tox.ini | 2 |
5 files changed, 224 insertions, 58 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 100755 index 0000000..12faa29 --- /dev/null +++ b/scripts/schema1-to-2.py @@ -0,0 +1,86 @@ +#!/usr/bin/env python3 + +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)) diff --git a/src/vgstash/__init__.py b/src/vgstash/__init__.py index d28e587..dcf6c5b 100755 --- a/src/vgstash/__init__.py +++ b/src/vgstash/__init__.py @@ -1,6 +1,7 @@ import os import sys import sqlite3 +import datetime # Remixed vgstash. This time I chose an individual file on purpose: # @@ -11,6 +12,8 @@ import sqlite3 # the standard library, can't beat that. # * GUI will be implemented in tkinter, Qt, and/or Kivy. +VERSION = "0.3b8" + PROGRESS = { 'unbeatable': 0, 'new': 1, @@ -48,8 +51,15 @@ FILTERS = { 'physical': "SELECT * FROM games WHERE ownership = 1 ORDER BY system, title ASC", 'playlog': "SELECT * FROM games WHERE ownership > 0 AND progress = 2 ORDER BY system, title ASC", 'unowned': "SELECT * FROM games WHERE ownership = 0 ORDER BY system, title ASC", + 'backlog_age': "SELECT title, system, ownership, ((unixepoch() - p_date) / (60 * 60 * 24 * 365)) AS years, ((unixepoch() - p_date) / (60 * 60 * 24) % 365) AS days FROM games WHERE (progress > 0 AND progress < 3) AND p_date ORDER BY years DESC, days DESC, system ASC, title ASC", } +def iso_to_unix(t): + return datetime.date.fromisoformat(t).strftime("%s") + +def unix_to_iso(t): + return datetime.date.fromtimestamp(t).isoformat() + def kvmatch(arg, dict_map, fallback): """ Match arg against keys or values in dict_map, returning fallback if no match. @@ -149,10 +159,10 @@ class DB(object): return self.update_game(game, game) else: c = self.conn.execute("INSERT INTO games\ - (title, system, ownership, progress, notes)\ + (title, system, ownership, progress, notes, p_date, b_date, c_date)\ VALUES\ - (?, ?, ?, ?, ?)", - (game.title, game.system, game.ownership, game.progress, game.notes)) + (?, ?, ?, ?, ?, ?, ?, ?)", + (game.title, game.system, game.ownership, game.progress, game.notes, game.p_date, game.b_date, game.c_date)) self.conn.commit() return (c.rowcount > 0) @@ -166,6 +176,9 @@ class DB(object): ownership (INTEGER) progress (INTEGER) notes (TEXT) + p_date (INTEGER) + b_date (INTEGER) + c_date (INTEGER) The schema is configured to use the 'title' and 'system' columns as primary keys, meaning it is impossible for two games with the same name @@ -184,16 +197,19 @@ class DB(object): # get more exact data manipulation. Alternatively, use the *_filter # methods of this class to create custom reporting filters. try: - self.conn.execute("CREATE TABLE\ - IF NOT EXISTS\ - games (\ - title TEXT NOT NULL,\ - system TEXT NOT NULL,\ - ownership INTEGER NOT NULL DEFAULT 1,\ - progress INTEGER NOT NULL DEFAULT 1,\ - notes TEXT DEFAULT '',\ - UNIQUE (title, system) ON CONFLICT ROLLBACK\ - )") + self.conn.execute("""CREATE TABLE + IF NOT EXISTS + games ( + title TEXT NOT NULL, + system TEXT NOT NULL, + ownership INTEGER NOT NULL DEFAULT 1, + progress INTEGER NOT NULL DEFAULT 1, + notes TEXT DEFAULT '', + p_date INTEGER DEFAULT NULL, + b_date INTEGER DEFAULT NULL, + c_date INTEGER DEFAULT NULL, + UNIQUE (title, system) ON CONFLICT ROLLBACK + )""") # setup default filters while we're here for name, sql in sorted(FILTERS.items()): self.add_filter(name, sql) @@ -320,10 +336,11 @@ class DB(object): if self.has_game(target): c = self.conn.cursor() # TODO: do this better - c.execute("UPDATE games\ - SET title=?, system=?, ownership=?, progress=?, notes=?\ - WHERE title=? AND system=?", - (source.title, source.system, source.ownership, source.progress, source.notes, target.title, target.system)) + c.execute("""UPDATE games\ + SET title=?, system=?, ownership=?, progress=?, notes=?, + p_date=?, b_date=?, c_date=? + WHERE title=? AND system=?""", + (source.title, source.system, source.ownership, source.progress, source.notes, source.p_date, source.b_date, source.c_date, target.title, target.system)) self.conn.commit() return (c.rowcount > 0) else: @@ -335,10 +352,14 @@ class Game(object): def __init__(self, title, system, ownership=DEFAULT_CONFIG['ownership'], progress=DEFAULT_CONFIG['progress'], - notes=""): + notes="", + p_date="", b_date="", c_date=""): self.title = title self.system = system self.ownership = kvmatch(ownership, OWNERSHIP, DEFAULT_CONFIG['ownership']) self.progress = kvmatch(progress, PROGRESS, DEFAULT_CONFIG['progress']) self.notes = notes + self.p_date = p_date + self.b_date = b_date + self.c_date = c_date diff --git a/src/vgstash_cli.py b/src/vgstash_cli.py index 29f6026..046443d 100755 --- a/src/vgstash_cli.py +++ b/src/vgstash_cli.py @@ -33,7 +33,6 @@ def init(): else: raise sqlite3.OperationalError("Cannot create schema.") - def row_format(row, width, header): """ Prints a row from the result set into a nice table. @@ -77,10 +76,20 @@ def row_format(row, width, header): @click.argument('ownership', type=str, required=False, default=vgstash.DEFAULT_CONFIG['ownership']) @click.argument('progress', type=str, required=False, default=vgstash.DEFAULT_CONFIG['progress']) @click.argument('notes', type=str, required=False, default="") -def add(title, system, ownership, progress, notes): +@click.argument('p_date', type=str, required=False, default="") +@click.argument('b_date', type=str, required=False, default="") +@click.argument('c_date', type=str, required=False, default="") +def add(title, system, ownership, progress, notes, p_date, b_date, c_date): db = get_db() - game = vgstash.Game(title, system, ownership, progress, notes) + game = vgstash.Game(title, system, ownership, progress, notes, p_date, b_date, c_date) try: + # Convert user-input (meant to be RFC2822/ISO8601 dates) to timestamps + if game.p_date: + game.p_date = vgstash.iso_to_unix(game.p_date) + if game.b_date: + game.b_date = vgstash.iso_to_unix(game.b_date) + if game.c_date: + game.c_date = vgstash.iso_to_unix(game.c_date) db.add_game(game, update=False) own_clause = ( "do not own it", @@ -96,13 +105,15 @@ def add(title, system, ownership, progress, notes): "have beaten", "have completed", ) - note_clause = "" if len(game.notes) == 0 else " It also has notes." - click.echo("Added {} for {}. You {} and {} it.{}".format( + note_clause = "" if len(game.notes) == 0 else " It has notes." + date_clause = "" if not (game.p_date or game.b_date or game.c_date) else " It has date data." + click.echo("Added {} for {}. You {} and {} it.{}{}".format( game.title, game.system, own_clause[game.ownership], progress_clause[game.progress], note_clause, + date_clause )) except sqlite3.IntegrityError as e: print(e) @@ -112,33 +123,89 @@ def add(title, system, ownership, progress, notes): @cli.command('list') @click.argument('filter', type=click.Choice(vgstash.FILTERS.keys()), required=False, default="allgames") @click.option('--raw', '-r', is_flag=True, show_default=True, default=False, help="Output raw, pipe-delimited lines") -@click.option('--width', '-w', type=str, required=False, default=get_terminal_size(fallback=(80,24)).columns, help="The width of the table to output, in characters.") -def list_games(filter, raw, width): +def list_games(filter, raw): db = get_db() - res = db.list_games(filter) - first_pass = True - # res can be False if the filter doesn't exist, but Click should catch it - # and spit out an error before this function even starts. - for r in res: - if 'notes' in r.keys() and len(r['notes']) > 0: - notes = r['notes'].replace('\n', '\\n') - notes = notes.replace('\r', '\\r') - else: - notes = '' - if raw: - click.echo("|".join(( - r['title'], - r['system'], - str(r['ownership']), - str(r['progress']), - notes - )) - ) - else: - row_format(r, width, first_pass) - first_pass = False - + row_data = db.list_games(filter) + + if raw: + for r in row_data: + l = [] + for c in r: + if c == None: + l.append(str('')) + elif type(c) == int: + l.append(str(c)) + else: + tc = c.replace('\n', '\\n') + tc = tc.replace('\r', '\\r') + l.append(tc) + click.echo("|".join(l)) + return + # Get column names, and a list of widths ready to go with them + columns = row_data[0].keys() + widths = [] + for i in range(len(columns)): + widths.append(len(columns[i])) + + # Make a cache to manipulate the data with + row_cache = [] + for r in row_data: + cache_row = [] + for c in r: + cache_row.append(c) + row_cache.append(cache_row) + # We should have a full, mutable cache now! + + + for r in row_cache: + for i in range(len(columns)): + # process fields that need massaging for display + if r[i]: + if columns[i] == ("p_date"): + r[i] = vgstash.unix_to_iso(r[i]) + if columns[i] == ("b_date"): + r[i] = vgstash.unix_to_iso(r[i]) + if columns[i] == ("c_date"): + r[i] = vgstash.unix_to_iso(r[i]) + if columns[i] == "notes" and len(r[i]) > 0: + r[i] = "*" + if columns[i] == "progress": + r[i] = vgstash.vtok(r[i], vgstash.PROGRESS)[0].capitalize() + if columns[i] == "ownership": + r[i] = vgstash.vtok(r[i], vgstash.OWNERSHIP)[0].capitalize() + if r[i] == None: + r[i] = "" + if isinstance(r[i], int): + r[i] = str(r[i]) + # Store width in relevant list + w = len(str(r[i])) + if w > widths[i]: + widths[i] = w + + # print the top header + l = [] + left_fst = "{: <{w}s}" + right_fst = "{: >{w}s}" + center_fst = "{: ^{w}s}" + + for i in range(len(columns)): + l.append(center_fst.format(columns[i], w=widths[i])) + click.echo(" | ".join(l)) + + l = [] + for w in widths: + l.append("-"*w) + click.echo("-+-".join(l)) + + # print the collection now that the hard part is done! + for r in row_cache: + l = [] + for i in range(len(columns)): + # TODO: set different fstring based on column name + l.append(left_fst.format(r[i], w=widths[i])) + click.echo(" | ".join(l)) + @cli.command('delete') @click.argument('title', required=True) @click.argument('system', required=True) @@ -4,7 +4,7 @@ # and then run "tox" from this directory. [tox] -envlist = py39 +envlist = py311 [testenv] deps = |