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(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)
