def get_runners():
with sql.db_cursor(PGA_DB) as cursor:
query = ("select distinct runner from games "
"where runner is not null order by runner")
rows = cursor.execute(query)
results = rows.fetchall()
return [result[0] for result in results if result[0]]
def create_table(name, schema):
fields = ", ".join([field_to_string(**f) for f in schema])
fields = "(%s)" % fields
query = "CREATE TABLE IF NOT EXISTS %s %s" % (name, fields)
LOGGER.debug("[PGAQuery] %s", query)
with sql.db_cursor(PGA_DB) as cursor:
cursor.execute(query)
def get_used_runners():
"""Return a list of the runners in use by installed games."""
with sql.db_cursor(PGA_DB) as cursor:
query = ("select distinct runner from games "
"where runner is not null order by runner")
rows = cursor.execute(query)
results = rows.fetchall()
return [result[0] for result in results if result[0]]
def get_used_platforms():
"""Return a list of platforms currently in use"""
with sql.db_cursor(PGA_DB) as cursor:
query = ("select distinct platform from games "
"where platform is not null and platform is not '' order by platform")
rows = cursor.execute(query)
results = rows.fetchall()
return [result[0] for result in results if result[0]]
def get_used_runners_game_count():
"""Return a dictionary listing for each runner in use, how many games are using it."""
with sql.db_cursor(PGA_DB) as cursor:
query = ("select runner, count(*) from games "
"where runner is not null "
"group by runner "
"order by runner")
rows = cursor.execute(query)
results = rows.fetchall()
return {result[0]: result[1] for result in results if result[0]}
def get_used_platforms():
"""Return a list of platforms currently in use"""
with sql.db_cursor(PGA_DB) as cursor:
query = (
"select distinct platform from games "
"where platform is not null and platform is not '' order by platform"
)
rows = cursor.execute(query)
results = rows.fetchall()
return [result[0] for result in results if result[0]]
def get_used_platforms_game_count():
"""Return a dictionary listing for each platform in use, how many games are using it."""
with sql.db_cursor(PGA_DB) as cursor:
# The extra check for 'installed is 1' is needed because
# the platform lists don't show uninstalled games, but the platform of a game
# is remembered even after the game is uninstalled.
query = ("select platform, count(*) from games "
"where platform is not null and platform is not '' and installed is 1 "
"group by platform "
"order by platform")
rows = cursor.execute(query)
results = rows.fetchall()
return {result[0]: result[1] for result in results if result[0]}
def get_games(name_filter=None):
"""Get the list of every game in database."""
with sql.db_cursor(PGA_DB) as cursor:
if name_filter is not None:
query = "select * from games where name LIKE ?"
rows = cursor.execute(query, (name_filter, ))
else:
query = "select * from games"
rows = cursor.execute(query)
results = rows.fetchall()
column_names = [column[0] for column in cursor.description]
game_list = []
for row in results:
game_info = {}
for index, column in enumerate(column_names):
game_info[column] = row[index]
game_list.append(game_info)
return game_list
def get_schema(tablename):
"""
Fields:
- position
- name
- type
- not null
- default
- indexed
"""
tables = []
query = "pragma table_info('%s')" % tablename
with sql.db_cursor(PGA_DB) as cursor:
for row in cursor.execute(query).fetchall():
field = {
'name': row[1],
'type': row[2],
'not_null': row[3],
'default': row[4],
'indexed': row[5]
}
tables.append(field)
return tables
def get_games(name_filter=None, filter_installed=False):
"""Get the list of every game in database."""
with sql.db_cursor(PGA_DB) as cursor:
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])
rows = cursor.execute(query, params)
results = rows.fetchall()
column_names = [column[0] for column in cursor.description]
game_list = []
for row in results:
game_info = {}
for index, column in enumerate(column_names):
game_info[column] = row[index]
game_list.append(game_info)
return game_list
def get_schema(tablename):
"""
Fields:
- position
- name
- type
- not null
- default
- indexed
"""
tables = []
query = "pragma table_info('%s')" % tablename
with sql.db_cursor(PGA_DB) as cursor:
for row in cursor.execute(query).fetchall():
field = {
'name': row[1],
'type': row[2],
'not_null': row[3],
'default': row[4],
'indexed': row[5]
}
tables.append(field)
return tables
def get_games(name_filter=None, filter_installed=False):
"""Get the list of every game in database."""
with sql.db_cursor(PGA_DB) as cursor:
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"
rows = cursor.execute(query, params)
results = rows.fetchall()
column_names = [column[0] for column in cursor.description]
game_list = []
for row in results:
game_info = {}
for index, column in enumerate(column_names):
game_info[column] = row[index]
game_list.append(game_info)
return game_list
def add_field(tablename, field):
query = "ALTER TABLE %s ADD COLUMN %s %s" % (
tablename, field['name'], field['type']
)
with sql.db_cursor(PGA_DB) as cursor:
cursor.execute(query)
def read_sources():
with sql.db_cursor(PGA_DB) as cursor:
rows = cursor.execute("select uri from sources")
results = rows.fetchall()
return [row[0] for row in results]
def create():
"""Create the local PGA database."""
logger.debug("Running CREATE statement...")
with sql.db_cursor(PGA_DB) as cursor:
create_games(cursor)
create_sources(cursor)
def read_sources():
with sql.db_cursor(PGA_DB) as cursor:
rows = cursor.execute("select uri from sources")
results = rows.fetchall()
return [row[0] for row in results]
def remove_category_from_game(game_id, category_id):
"""Remove a category from a game"""
query = "DELETE FROM games_categories WHERE category_id=? AND game_id=?"
with sql.db_cursor(PGA_DB) as cursor:
sql.cursor_execute(cursor, query, (category_id, game_id))
def get_table_length(table='games'):
with sql.db_cursor(PGA_DB) as cursor:
query = "select count() from games"
cursor.execute(query)
return cursor.fetchone()[0]
def migrate():
"""Convert the playtime to float from text, to allow sorting correctly"""
with db_cursor(PGA_DB) as cursor:
for sql_statement in SQL_STATEMENTS:
cursor_execute(cursor, sql_statement)
def add_field(tablename, field):
query = "ALTER TABLE %s ADD COLUMN %s %s" % (tablename, field['name'],
field['type'])
with sql.db_cursor(PGA_DB) as cursor:
cursor.execute(query)
