aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to '')
-rwxr-xr-xsrc/vgstash/__init__.py297
-rw-r--r--src/vgstash/test_vgstash.py145
-rw-r--r--src/vgstash/test_vgstash_cli.py73
-rw-r--r--src/vgstash_cli.py64
4 files changed, 579 insertions, 0 deletions
diff --git a/src/vgstash/__init__.py b/src/vgstash/__init__.py
new file mode 100755
index 0000000..61ae9e6
--- /dev/null
+++ b/src/vgstash/__init__.py
@@ -0,0 +1,297 @@
+import os
+import sys
+import sqlite3
+
+# Remixed vgstash. This time I chose an individual file on purpose:
+#
+# * Only need a single module to interface with a game DB. That's awesome.
+# * Keeps the backend separate from the UI, as intended.
+# * SQLA and pandas are both too large and unwieldy for my intent, and deps
+# will need to be kept low if I want to be cross-platform. sqlite3's part of
+# the standard library, can't beat that.
+# * GUI will be implemented in tkinter, Qt, and/or Kivy.
+
+PROGRESS = {
+ 'unbeatable': 0,
+ 'new': 1,
+ 'playing': 2,
+ 'beaten': 3,
+ 'complete': 4,
+}
+
+OWNERSHIP = {
+ 'unowned': 0,
+ 'physical': 1,
+ 'digital': 2,
+ 'both': 3
+}
+
+DEFAULT_CONFIG = {
+ 'db_location': os.getenv('VGSTASH_DB_LOCATION', os.path.join(os.getenv('HOME', os.curdir), '.vgstash.db')),
+ 'progress': os.getenv('VGSTASH_DEFAULT_PROGRESS', PROGRESS['playing']),
+ 'ownership': os.getenv('VGSTASH_DEFAULT_OWNERSHIP', OWNERSHIP['physical'])
+}
+
+FILTERS = {
+ 'allgames': "SELECT * FROM games ORDER BY system, title",
+ 'backlog': "SELECT * FROM games WHERE ownership > 0 AND progress = 0 OR progress = 1 ORDER BY system, title",
+ 'playlog': "SELECT * FROM games WHERE ownership > 0 AND progress = 2 ORDER BY system, title"
+}
+
+def kvmatch(arg, dict_map, fallback):
+ """
+ Match arg against keys or values in dict_map, returning fallback if no match.
+
+ This function performs a prefix-match against the keys in dict_map. Doing
+ such iteration partially defeats the purpose of using a dictionary, but
+ it offers considerable ease-of-use for the CLI and acts as a validation
+ function that will always return a valid value ready for committing to the
+ database.
+
+ It is generalized to support any value that needs to be mapped to an
+ integer, via the dict_map.
+ """
+ try:
+ ret = int(arg)
+ except TypeError:
+ ret = fallback
+ except ValueError:
+ found = False
+ for k in dict_map.keys():
+ if k.startswith(arg):
+ ret = dict_map[k]
+ found = True
+ break
+ if not found:
+ ret = fallback
+ finally:
+ if ret not in dict_map.values():
+ ret = fallback
+ return ret
+
+
+class DB(object):
+ """
+ The central class of vgstash. It handles everything relating to storing the
+ game collection.
+ """
+ def __init__(self, path=DEFAULT_CONFIG['db_location']):
+ """
+ Initiates the DB object with a 'conn' attribute, which holds the SQLite
+ connection. Additionally, the connection's 'row_factory' attribute is
+ set to sqlite3.Row to allow for string *and* integer indexes. This value
+ may be changed by the caller after the object is returned.
+ """
+ try:
+ self.conn = sqlite3.connect(path)
+ self.conn.row_factory = sqlite3.Row
+ except sqlite3.OperationalError as e:
+ print("%s: %s".format(e, path))
+ exit()
+
+ def add_filter(self, filter_name, stmt):
+ """
+ Adds a new filter keyword to the database. Note that values are passed
+ directly in, with no escaping. Use with caution.
+
+ 'filter_name' is the name of your filter, and will be the 'name' key
+ when viewed with list_filters(). usable via `vgscli list [filter name]`.
+ 'stmt' is a plain SELECT statement representing the SQLite VIEW.
+ """
+ if filter_name.startswith("sqlite_"):
+ raise ValueError("Cannot create a filter with the 'sqlite_' prefix.")
+
+ # The call to format() is needed because sqlite doesn't allow
+ # parameterized view or table names. This may affect database
+ # integrity, and may disappear in a later release.
+ res = self.conn.execute("CREATE VIEW\
+ IF NOT EXISTS \
+ '{}'\
+ AS\
+ {}".format(filter_name, stmt))
+ FILTERS[str(filter_name)] = str(stmt)
+ return self.has_filter(filter_name)
+
+ def add_game(self, game, update=True):
+ """
+ Adds a Game to the database. Returns True on success and False on
+ failure.
+ """
+ if self.has_game(game) and update:
+ return self.update_game(game, game)
+ else:
+ c = self.conn.execute("INSERT INTO games\
+ (title, system, ownership, progress, notes)\
+ VALUES\
+ (?, ?, ?, ?, ?)",
+ (game.title, game.system, game.ownership, game.progress, game.notes))
+ self.conn.commit()
+ return (c.rowcount > 0)
+
+ def create_schema(self):
+ """
+ Initializes the database with this basic schema:
+
+ games:
+ title (TEXT)
+ system (TEXT)
+ ownership (INTEGER)
+ progress (INTEGER)
+ notes (TEXT)
+
+ 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
+ and system to be present in the database.
+
+ Additionally, create_schema will add the default set of filters, which
+ are considered important for an ordinary vgstash DB.
+ """
+ # The UNIQUE clause ensures that no two games with the same title and
+ # system are added to the database. An sqlite3.IntegrityError will be
+ # raised when a match occurs; clients can decide how to handle it.
+ #
+ # The 'rowid' field is automatically generated by sqlite, and is thus
+ # omitted. External modifications to the database should be done
+ # through additional tables, and should reference the 'rowid' field to
+ # 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\
+ )")
+ # setup default filters while we're here
+ for name, sql in sorted(FILTERS.items()):
+ self.add_filter(name, sql)
+ return True
+ except sqlite3.OperationalError:
+ print("Table already exists, skipping.")
+ return False
+
+ def delete_filter(self, filter_name):
+ with self.conn:
+ # try:
+ self.conn.execute("DROP VIEW IF EXISTS '{}'".format(filter_name))
+ return True
+ # except:
+ # sys.exc_info()
+ # print("Could not remove filter '{}'.".format((filter_name))
+ return False
+
+ def delete_game(self, game):
+ """
+ Deletes a game from the database. Returns True on success and False
+ on failure.
+ """
+ if self.has_game(game):
+ c = self.conn.cursor()
+ c.execute("DELETE FROM games\
+ WHERE title=? AND system=?",
+ (game.title, game.system))
+ self.conn.commit()
+ return True
+ else:
+ return False
+
+ def has_game(self, game, fuzzy=False):
+ """
+ Returns whether or not the game is in the database.
+
+ game - The Game object to search for.
+ fuzzy - Fuzzy search, using internal 'LIKE' and replacing the game
+ title's spaces with '%' characters. Defaults to False.
+ """
+ if fuzzy:
+ game.title = "%".join(['', game.title.replace(" ", "%"), ''])
+ stmt = "SELECT * FROM games WHERE title LIKE ? AND system=?"
+ else:
+ stmt = "SELECT * FROM games WHERE title=? AND system=?"
+
+ res = self.conn.execute(stmt, (game.title, game.system)).fetchone()
+ # res is None if there isn't a match, which evaluates to False
+ return bool(res)
+
+ def has_filter(self, filter_name):
+ return filter_name in self.list_filters().keys() \
+ and filter_name in FILTERS
+
+ def list_filters(self):
+ """
+ Provides an iterable of filter names and their associated SELECT
+ statements.
+ """
+ # The 'sqlite_master' table is a built-in.
+ # This returns an iterable of sqlite3.Row, which can be accessed
+ # similarly to a dictionary.
+ res = self.conn.execute(\
+ "SELECT name,sql\
+ FROM sqlite_master\
+ WHERE\
+ type='view'\
+ ORDER BY name ASC").fetchall()
+ ret = {}
+ for row in res:
+ ret[row['name']] = row['sql']
+ # Be sure to sync with internal representation
+ FILTERS = ret
+ return ret
+
+ def list_games(self, filter='allgames'):
+ if filter not in FILTERS.keys():
+ filter = 'allgames'
+ return self.conn.execute(FILTERS[filter]).fetchall()
+
+ def update_filter(self, filter_name, stmt):
+ """
+ Updates a filter's definition within the database.
+
+ SQLite does not have a way to update VIEWs directly, so this is a
+ convenience function to make updating possible.
+ """
+ try:
+ self.delete_filter(filter_name)
+ self.add_filter(filter_name, stmt)
+ return True
+ except:
+ return False
+
+ def update_game(self, target, source):
+ """
+ Look for target in the database and (if found) update it with source's
+ information. Returns True on success and False on failure.
+
+ The same Game object may be passed as both target and source to update
+ a given game's values within the database.
+ """
+ # don't update unless it exists
+ if self.has_game(target):
+ c = self.conn.cursor()
+ # TODO: do this better
+ c.execute("UPDATE games\
+ SET title=?, system=?, ownership=?, progress=?\
+ WHERE title=? AND system=?",
+ (source.title, source.system, source.ownership, source.progress, target.title, target.system))
+ self.conn.commit()
+ return (c.rowcount > 0)
+ else:
+ return False
+
+
+class Game(object):
+ """The core data structure of vgstash."""
+ def __init__(self, title, system,
+ ownership=DEFAULT_CONFIG['ownership'],
+ progress=DEFAULT_CONFIG['progress'],
+ notes=""):
+
+ 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
diff --git a/src/vgstash/test_vgstash.py b/src/vgstash/test_vgstash.py
new file mode 100644
index 0000000..74f3c43
--- /dev/null
+++ b/src/vgstash/test_vgstash.py
@@ -0,0 +1,145 @@
+import os
+import pytest
+import vgstash
+import sqlite3
+
+def test_config():
+ assert vgstash.DEFAULT_CONFIG['db_location']
+ assert vgstash.DEFAULT_CONFIG['progress'] in vgstash.PROGRESS.values()
+ assert vgstash.DEFAULT_CONFIG['ownership'] in vgstash.OWNERSHIP.values()
+
+@pytest.fixture(scope="module")
+def vgstash_db():
+ vgstash.DEFAULT_CONFIG['db_location'] = '.vgstash.db'
+ yield vgstash.DB(vgstash.DEFAULT_CONFIG['db_location'])
+ os.remove(vgstash.DEFAULT_CONFIG['db_location'])
+
+def test_game_min():
+ game = vgstash.Game("test_game1", "system3")
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_ownership():
+ game = vgstash.Game("test_game2", "system3", 1)
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_ownership_str():
+ game = vgstash.Game("test_game2", "system3", 'd')
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_progress():
+ game = vgstash.Game("test_game3", "system3", progress=1)
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_progress_str():
+ game = vgstash.Game("test_game3", "system3", progress='c')
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_notes_no_own_or_progress():
+ game = vgstash.Game("test_game4", "system3", notes="Hello world")
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_game_full():
+ game = vgstash.Game("test_game5", "system3", 'b', 2, "Blah")
+ assert isinstance(game, vgstash.Game)
+ assert isinstance(game.title, str)
+ assert isinstance(game.system, str)
+ assert isinstance(game.ownership, int)
+ assert isinstance(game.progress, int)
+ assert isinstance(game.notes, str)
+ assert game.ownership in vgstash.OWNERSHIP.values()
+ assert game.progress in vgstash.PROGRESS.values()
+
+def test_db(vgstash_db):
+ assert isinstance(vgstash_db.conn, sqlite3.Connection)
+
+def test_db_create_schema(vgstash_db):
+ assert vgstash_db.create_schema()
+
+def test_db_add_game(vgstash_db):
+ game = vgstash.Game("db_add_game", "system")
+ assert vgstash_db.add_game(game)
+ assert vgstash_db.has_game(game)
+
+def test_db_add_game_ownership(vgstash_db):
+ game = vgstash.Game("db_add_game_ownership", "system2", 'p')
+ assert vgstash_db.add_game(game)
+ assert vgstash_db.has_game(game)
+
+def test_db_add_game_notes(vgstash_db):
+ game = vgstash.Game("db_add_game_notes", "system2", '-', '-', 'my notes')
+ assert vgstash_db.add_game(game)
+ assert vgstash_db.has_game(game)
+
+def test_db_update_game(vgstash_db):
+ oldgame = vgstash.Game("db_add_game", "system")
+ newgame = vgstash.Game("db_update_game", "system")
+ if vgstash_db.has_game(oldgame):
+ assert vgstash_db.update_game(oldgame, newgame)
+ assert vgstash_db.has_game(newgame)
+
+def test_db_delete_game(vgstash_db):
+ game = vgstash.Game("db_delete_game", "system2")
+ vgstash_db.add_game(game)
+ assert vgstash_db.delete_game(game)
+
+def test_db_list_games(vgstash_db):
+ res = vgstash_db.list_games()
+ assert isinstance(res, list)
+ assert isinstance(res[0], sqlite3.Row)
+
+def test_db_add_filter(vgstash_db):
+ assert vgstash_db.add_filter("db_add_filter", "SELECT * FROM games WHERE system = 'system2'")
+ assert vgstash_db.has_filter("db_add_filter")
+
+def test_db_update_filter(vgstash_db):
+ assert vgstash_db.update_filter("db_add_filter", "SELECT * FROM games WHERE system='system'")
+ assert vgstash_db.has_filter("db_add_filter")
+ assert "'system'" in vgstash.FILTERS["db_add_filter"]
+
+def test_db_delete_filter(vgstash_db):
+ assert vgstash_db.delete_filter("db_add_filter")
+
+def test_db_list_filters(vgstash_db):
+ assert len(vgstash_db.list_filters()) > 0
diff --git a/src/vgstash/test_vgstash_cli.py b/src/vgstash/test_vgstash_cli.py
new file mode 100644
index 0000000..ac123ef
--- /dev/null
+++ b/src/vgstash/test_vgstash_cli.py
@@ -0,0 +1,73 @@
+import click
+import os
+import pytest
+import vgstash
+import vgstash_cli
+
+from click.testing import CliRunner
+
+verbose = False
+
+def test_init():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['init'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == "Initializing the database...\nSchema created.\n"
+
+def test_add_minimum():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['add', 'Super Mario Bros.', 'NES'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == "Added Super Mario Bros. for NES. You physically own it and are playing it.\n"
+
+def test_add_ownership():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['add', 'The Legend of Zelda', 'NES', 'd'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == "Added The Legend of Zelda for NES. You digitally own it and are playing it.\n"
+
+def test_add_typical():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['add', 'Sonic the Hedgehog 2', 'Genesis', '0', '3'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == "Added Sonic the Hedgehog 2 for Genesis. You do not own it and have beaten it.\n"
+
+def test_add_full():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['add', 'Vectorman', 'Genesis', 'u', 'b', 'beep'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == "Added Vectorman for Genesis. You do not own it and have beaten it. It also has notes.\n"
+
+def test_list():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.list_games)
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == '\n'.join((
+ 'Sonic the Hedgehog 2|Genesis|0|3|',
+ 'Vectorman|Genesis|0|3|beep',
+ 'Super Mario Bros.|NES|1|2|',
+ 'The Legend of Zelda|NES|2|2|\n',
+ ))
+
+def test_list_filter():
+ runner = CliRunner()
+ result = runner.invoke(vgstash_cli.cli, ['list', 'playlog'])
+ if verbose:
+ print(result.output)
+ assert result.exit_code == 0
+ assert result.output == '\n'.join((
+ 'Super Mario Bros.|NES|1|2|',
+ 'The Legend of Zelda|NES|2|2|\n',
+ ))
diff --git a/src/vgstash_cli.py b/src/vgstash_cli.py
new file mode 100644
index 0000000..9f5eef4
--- /dev/null
+++ b/src/vgstash_cli.py
@@ -0,0 +1,64 @@
+import vgstash
+import sqlite3
+import click
+import sys
+
+def get_db():
+ """
+ Convenience function to fetch a vgstash DB object from the default location.
+ """
+ return vgstash.DB(vgstash.DEFAULT_CONFIG['db_location'])
+
+
+@click.group('vgstash')
+def cli():
+ pass
+
+
+@cli.command()
+def init():
+ db = get_db()
+ click.echo("Initializing the database...")
+ if db.create_schema():
+ click.echo("Schema created.")
+ else:
+ raise sqlite3.OperationalError("Cannot create schema.")
+
+@cli.command('add')
+@click.argument('title', type=str)
+@click.argument('system', type=str)
+@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):
+ db = get_db()
+ game = vgstash.Game(title, system, ownership, progress, notes)
+ try:
+ db.add_game(game, update=False)
+ own_clause = (
+ "do not own",
+ "physically own",
+ "digitally own",
+ "digitally and physically own",
+ )
+ progress_clause = (
+ "cannot beat",
+ "haven't started",
+ "are playing",
+ "have beaten",
+ "have completed",
+ )
+ note_clause = "" if len(game.notes) == 0 else " It also has notes."
+ click.echo("Added {} for {}. You {} it and {} it.{}".format(game.title, game.system, own_clause[game.ownership], progress_clause[game.progress], note_clause))
+ except sqlite3.IntegrityError as e:
+ print(e)
+ click.echo("Couldn't add game.")
+
+
+@cli.command('list')
+@click.argument('filter', required=False)
+def list_games(filter="allgames"):
+ db = get_db()
+ res = db.list_games(filter)
+ for r in res:
+ click.echo("{}|{}|{}|{}|{}".format(r['title'], r['system'], r['ownership'], r['progress'], r['notes']))