def get_games( name_filter=None, filter_installed=False, filter_runner=None, select=None, show_installed_first=False, ): """Get the list of every game in database.""" query = "select * from games" params = [] filters = [] if select: query = "select ? from games" params.append(select) if name_filter: params.append(name_filter) filters.append("name LIKE ?") if filter_installed: filters.append("installed = 1") if filter_runner: params.append(filter_runner) filters.append("runner = ?") if filters: query += " WHERE " + " AND ".join(filters) if show_installed_first: query += " ORDER BY installed DESC, slug" else: query += " ORDER BY slug" return sql.db_query(PGA_DB, query, tuple(params))
def get_games_in_category(category_name): """Get the ids of games in database.""" query = ("select game_id from games_categories " "JOIN categories ON categories.id = games_categories.category_id " "WHERE categories.name=?") return [ game["game_id"] for game in sql.db_query(PGA_DB, query, (category_name, )) ]
def get_categories_in_game(game_id): """Get the categories of a game in database.""" query = ( "select categories.name from categories " "JOIN games_categories ON categories.id = games_categories.category_id " "JOIN games ON games.id = games_categories.game_id " "WHERE games.id=?") return [ category["name"] for category in sql.db_query(PGA_DB, query, (game_id, )) ]
def get_games_where(**conditions): """ Query games table based on conditions Args: conditions (dict): named arguments with each field matches its desired value. Special values for field names can be used: <field>__isnull will return rows where `field` is NULL if the value is True <field>__not will invert the condition using `!=` instead of `=` <field>__in will match rows for every value of `value`, which should be an iterable Returns: list: Rows matching the query """ query = "select * from games" condition_fields = [] condition_values = [] for field, value in conditions.items(): field, *extra_conditions = field.split("__") if extra_conditions: extra_condition = extra_conditions[0] if extra_condition == "isnull": condition_fields.append("{} is {} null".format( field, "" if value else "not")) if extra_condition == "not": condition_fields.append("{} != ?".format(field)) condition_values.append(value) if extra_condition == "in": if not hasattr(value, "__iter__"): raise ValueError("Value should be an iterable (%s given)" % value) if len(value) > 999: raise ValueError( "SQLite limnited to a maximum of 999 parameters.") if value: condition_fields.append("{} in ({})".format( field, ", ".join("?" * len(value)) or "")) condition_values = list(chain(condition_values, value)) else: condition_fields.append("{} = ?".format(field)) condition_values.append(value) condition = " AND ".join(condition_fields) if condition: query = " WHERE ".join((query, condition)) else: # FIXME: Inspect and document why we should return # an empty list when no condition is present. return [] return sql.db_query(PGA_DB, query, tuple(condition_values))
def get_games(name_filter=None, filter_installed=False): """Get the list of every game in database.""" query = "select * from games" params = () filters = [] if name_filter: params = (name_filter, ) filters.append("name LIKE ?") if filter_installed: filters.append("installed = 1") if filters: query += " WHERE " + " AND ".join([f for f in filters]) query += " ORDER BY slug" return sql.db_query(PGA_DB, query, params)
def get_games_where(**conditions): """ Query games table based on conditions Args: conditions (dict): named arguments with each field matches its desired value. Special values for field names can be used: <field>__isnull will return rows where `field` is NULL if the value is True <field>__not will invert the condition using `!=` instead of `=` <field>__in will match rows for every value of `value`, which should be an iterable Returns: list: Rows matching the query """ query = "select * from games" condition_fields = [] condition_values = [] for field, value in conditions.items(): field, *extra_conditions = field.split('__') if extra_conditions: extra_condition = extra_conditions[0] if extra_condition == 'isnull': condition_fields.append('{} is {} null'.format(field, '' if value else 'not')) if extra_condition == 'not': condition_fields.append("{} != ?".format(field)) condition_values.append(value) if extra_condition == 'in': if not hasattr(value, '__iter__'): raise ValueError("Value should be an iterable (%s given)" % value) if len(value) > 999: raise ValueError("SQLite limnited to a maximum of 999 parameters.") if value: condition_fields.append("{} in ({})".format( field, ', '.join('?' * len(value)) or "" )) condition_values = list(chain(condition_values, value)) else: condition_fields.append("{} = ?".format(field)) condition_values.append(value) condition = " AND ".join(condition_fields) if condition: query = " WHERE ".join((query, condition)) else: # FIXME: Inspect and document why we should return an empty list when # no condition is present. return [] return sql.db_query(PGA_DB, query, tuple(condition_values))
def get_games(name_filter=None, filter_installed=False, filter_runner=None, select='*'): """Get the list of every game in database.""" query = "select " + select + " from games" params = [] filters = [] if name_filter: params.append(name_filter) filters.append("name LIKE ?") if filter_installed: filters.append("installed = 1") if filter_runner: params.append(filter_runner) filters.append("runner = ?") if filters: query += " WHERE " + " AND ".join([f for f in filters]) query += " ORDER BY slug" return sql.db_query(PGA_DB, query, tuple(params))
def get_desktop_games(): query = "select * from games where runner = 'linux' and installer_slug = 'desktopapp'" return sql.db_query(PGA_DB, query)
def get_steam_games(): """Return the games with a SteamID""" query = "select * from games where steamid is not null and steamid != ''" return sql.db_query(PGA_DB, query)