diff options
Diffstat (limited to '')
| -rwxr-xr-x | src/vgstash/__init__.py | 297 | ||||
| -rw-r--r-- | src/vgstash/test_vgstash.py | 145 | ||||
| -rw-r--r-- | src/vgstash/test_vgstash_cli.py | 73 | ||||
| -rw-r--r-- | src/vgstash_cli.py | 64 |
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'])) |