Add support for p_date, b_date, c_date to CLInext

The library and CLI tool both can handle the new schema v2 that includes
these columns. Please use the schema migration script in the "scripts"
directory to continue using VGStash.

The list_games function has been completely re-worked to handle
arbitrary tabular data. Raw mode has remained, but the width switch (-w)
for default table view is no longer present.

The first VIEW supporting these columns is also available in FILTERS:
the backlog_age filter, which will show you the amount of time games
with a purchase date value have been sitting in your collection
unbeaten.

2 files changed, 136 insertions, 48 deletions

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)