def best_ziggurats(c, limit=6):
return [
list(r) for r in query_rows(
c, '''SELECT player, place, zig_time
FROM ziggurats
ORDER BY deepest DESC, zig_time DESC''')
]
def top_combo_scores(c):
"""Returns all the top-scoring games for each unique character combo, ordered
in descending order of score."""
rows = query_rows(c,
game_select_from('top_combo_scores') +
" ORDER BY charabbr")
return xdict_rows(rows)
def top_combo_scores(c):
"""Returns all the top-scoring games for each unique character combo, ordered
in descending order of score."""
rows = query_rows(
c,
game_select_from('top_combo_scores') + " ORDER BY charabbr")
return xdict_rows(rows)
def update_db_streaks(self, c):
# in the full cao db, this is usually around 100 streaks at any given time
self.db_streaks = dict()
s = query_rows(
c, 'SELECT LOWER(player), id FROM streaks WHERE active = 1')
if s:
self.db_streaks.update(s)
def player_best_first_last(c, player):
fields = scload.LOG_DB_SCOLUMNS
q = [(game_select_from('player_best_games') +
'''WHERE name = %s ORDER BY sc DESC LIMIT 1'''),
(game_select_from('player_first_games') + '''WHERE name = %s'''),
(game_select_from('player_last_games') + '''WHERE name = %s''')]
q = " UNION ALL ".join(["(" + x + ")" for x in q])
return xdict_rows(query_rows(c, q, player, player, player))
def most_pacific_wins(c, limit=5):
return xdict_rows(
query_rows(c,
game_select_from('wins') +
# This filters all games where the statistic is unavailable.
""" WHERE kills > 0
ORDER BY kills, id LIMIT %s""",
limit))
def player_get_stats(c, player):
stats = {}
rows = query_rows(
c, '''SELECT charabbr, games_played, best_xl, wins
FROM player_char_stats
WHERE name = %s''', player)
for r in rows:
stats[r[0].lower()] = {'games': r[1], 'xl': r[2], 'wins': r[3]}
return stats
def most_pacific_wins(c, limit=5):
return xdict_rows(
query_rows(
c,
game_select_from('wins') +
# This filters all games where the statistic is unavailable.
""" WHERE kills > 0
ORDER BY kills, id LIMIT %s""",
limit))
def player_get_stats(c, player):
stats = { }
rows = query_rows(c, '''SELECT charabbr, games_played, best_xl, wins
FROM player_char_stats
WHERE name = %s''', player)
for r in rows:
stats[r[0].lower()] = { 'games': r[1],
'xl': r[2],
'wins': r[3] }
return stats
def player_best_first_last(c, player):
fields = scload.LOG_DB_SCOLUMNS
q = [(game_select_from('player_best_games') +
'''WHERE name = %s ORDER BY sc DESC LIMIT 1'''),
(game_select_from('player_first_games') +
'''WHERE name = %s'''),
(game_select_from('player_last_games') +
'''WHERE name = %s''')]
q = " UNION ALL ".join(["(" + x + ")" for x in q])
return xdict_rows(query_rows(c, q, player, player, player))
def date_stats(c):
dates = query_rows(
c, '''SELECT which_day, games_ended, games_won
FROM per_day_stats ORDER BY which_day DESC''')
result = []
month = [None]
def new_month_stat(this_month):
return {
'month': this_month,
'games': 0,
'players': 0,
'wins': 0,
'winners': {}
}
def flush_month(month):
if not month:
return
result.append(fixup_month(c, month))
def inc_month_stats(date, eday, stats):
this_month = date.strftime('%Y-%m')
if not month[0] or month[0]['month'] != this_month:
flush_month(month[0])
month[0] = new_month_stat(this_month)
m = month[0]
m['games'] += stats['games']
m['wins'] += stats['wins']
mwinners = m['winners']
for wstat in stats['winners']:
if not mwinners.has_key(wstat[0]):
mwinners[wstat[0]] = 0
mwinners[wstat[0]] += wstat[1]
def record_date(d):
date = d[0]
edate = string_date(d[0])
stats = per_day_stats(c, edate, d[0], d[1], d[2])
inc_month_stats(date, edate, stats)
stats['winners'] = fixup_winners(stats['winners'])
result.append(stats)
for d in dates:
record_date(d)
flush_month(month[0])
return result
def top_killers(c):
deaths = query_first(c, '''SELECT SUM(kills) FROM top_killers''')
logf = logfields_prefixed('k.')
rows = query_rows(c,
"SELECT t.ckiller, t.kills, " +
logf +
''' FROM top_killers t, killer_recent_kills k
WHERE t.ckiller = k.ckiller
AND t.ckiller NOT IN ('leaving', 'quitting', 'winning')
ORDER BY t.kills DESC, t.ckiller''')
def fix_killer_row(r):
perc = calc_perc_pretty(r[1], deaths) + '%'
g = row_to_xdict(r[2:])
return [r[0], perc, r[1], linked_text(g, morgue_link, g['name'])]
return [fix_killer_row(x) for x in rows]
def top_killers(c):
deaths = query_first(c, '''SELECT SUM(kills) FROM top_killers''')
logf = logfields_prefixed('k.')
rows = query_rows(
c, "SELECT t.ckiller, t.kills, " + logf +
''' FROM top_killers t, killer_recent_kills k
WHERE t.ckiller = k.ckiller
AND t.ckiller NOT IN ('leaving', 'quitting', 'winning')
ORDER BY t.kills DESC, t.ckiller''')
def fix_killer_row(r):
perc = calc_perc_pretty(r[1], deaths) + '%'
g = row_to_xdict(r[2:])
return [r[0], perc, r[1], linked_text(g, morgue_link, g['name'])]
return [fix_killer_row(x) for x in rows]
def date_stats(c):
dates = query_rows(c,
'''SELECT which_day, games_ended, games_won
FROM per_day_stats ORDER BY which_day DESC''')
result = []
month = [None]
def new_month_stat(this_month):
return { 'month': this_month,
'games': 0,
'players': 0,
'wins': 0,
'winners': { } }
def flush_month(month):
if not month:
return
result.append(fixup_month(c, month))
def inc_month_stats(date, eday, stats):
this_month = date.strftime('%Y-%m')
if not month[0] or month[0]['month'] != this_month:
flush_month(month[0])
month[0] = new_month_stat(this_month)
m = month[0]
m['games'] += stats['games']
m['wins'] += stats['wins']
mwinners = m['winners']
for wstat in stats['winners']:
if not mwinners.has_key(wstat[0]):
mwinners[wstat[0]] = 0
mwinners[wstat[0]] += wstat[1]
def record_date(d):
date = d[0]
edate = string_date(d[0])
stats = per_day_stats(c, edate, d[0], d[1], d[2])
inc_month_stats(date, edate, stats)
stats['winners'] = fixup_winners(stats['winners'])
result.append(stats)
for d in dates:
record_date(d)
flush_month(month[0])
return result
def all_player_stats(c):
rows = query_rows(c, '''SELECT name, games_played, games_won,
total_score, best_xl, best_score,
first_game_start, last_game_end
FROM players
ORDER BY name''')
def flatten_row(r):
return [r[s] for s in
("total_score name games_played games_won win_perc " +
"best_xl best_score avg_score first_game last_game").split()]
res = []
for r in rows:
rl = list(r)
res.append(flatten_row(fixup_player_stats(c, rl)))
return res
def best_players_by_total_score(c):
rows = query_rows(c, '''SELECT name, games_played, games_won,
total_score, best_score,
first_game_start, last_game_end
FROM players WHERE total_score > 500
ORDER BY total_score DESC''')
res = []
for r in rows:
rl = list(r)
games = player_best_first_last(c, rl[0])
rl[4] = linked_text(games[0], morgue_link, human_number(rl[4]))
rl[5] = linked_text(games[1], morgue_link, rl[5])
rl[6] = linked_text(games[2], morgue_link, rl[6])
win_perc = calc_perc_pretty(rl[2], rl[1]) + "%"
avg_score = calc_avg_int(rl[3], rl[1])
res.append([rl[3]] + list(rl[0:3]) + [win_perc] + [rl[4]]
+ [avg_score] + list(rl[5:]))
return res
def top_thing_scorers(c, table, thing):
games = xdict_rows(query_rows(c, game_select_from(table)
+ " ORDER BY name, " + thing))
score_counts = { }
def inc_count(g):
name = g['name']
if not score_counts.has_key(name):
score_counts[name] = [ ]
l = score_counts[name]
l.append(linked_text(g, morgue_link, g[thing]))
for g in games:
inc_count(g)
best_players = score_counts.items()
best_players.sort(lambda a, b: len(b[1]) - len(a[1]))
return [[len(x[1]), x[0], ", ".join(x[1])] for x in best_players]
def top_thing_scorers(c, table, thing):
games = xdict_rows(
query_rows(c,
game_select_from(table) + " ORDER BY name, " + thing))
score_counts = {}
def inc_count(g):
name = g['name']
if not score_counts.has_key(name):
score_counts[name] = []
l = score_counts[name]
l.append(linked_text(g, morgue_link, g[thing]))
for g in games:
inc_count(g)
best_players = score_counts.items()
best_players.sort(lambda a, b: len(b[1]) - len(a[1]))
return [[len(x[1]), x[0], ", ".join(x[1])] for x in best_players]
def best_players_by_total_score(c):
rows = query_rows(
c, '''SELECT name, games_played, games_won,
total_score, best_score,
first_game_start, last_game_end
FROM players WHERE total_score > 500
ORDER BY total_score DESC''')
res = []
for r in rows:
rl = list(r)
games = player_best_first_last(c, rl[0])
rl[4] = linked_text(games[0], morgue_link, human_number(rl[4]))
rl[5] = linked_text(games[1], morgue_link, rl[5])
rl[6] = linked_text(games[2], morgue_link, rl[6])
win_perc = calc_perc_pretty(rl[2], rl[1]) + "%"
avg_score = calc_avg_int(rl[3], rl[1])
res.append([rl[3]] + list(rl[0:3]) + [win_perc] + [rl[4]] +
[avg_score] + list(rl[5:]))
return res
def winner_stats(c):
rows = query_rows(c,
'''SELECT p.games_won, p.name, p.games_played,
p.max_runes, p.best_score, p.total_score, '''
+ logfields_prefixed('g.') +
''' FROM players p, player_best_games g
WHERE p.name = g.name AND p.best_score = g.sc
AND p.games_won > 0
ORDER BY p.games_won DESC, p.games_played,
p.best_score DESC''')
results = []
for r in rows:
results.append(list(r[0:3]) +
[calc_perc_pretty(r[0], r[2]) + '%', r[3],
linked_text(row_to_xdict(r[6:]), morgue_link,
human_number(r[4])),
human_number(r[5]),
human_number(calc_avg_int(r[5], r[2]))])
return results
def winner_stats(c):
rows = query_rows(
c, '''SELECT p.games_won, p.name, p.games_played,
p.max_runes, p.best_score, p.total_score, ''' +
logfields_prefixed('g.') + ''' FROM players p, player_best_games g
WHERE p.name = g.name AND p.best_score = g.sc
AND p.games_won > 0
ORDER BY p.games_won DESC, p.games_played,
p.best_score DESC''')
results = []
for r in rows:
results.append(
list(r[0:3]) + [
calc_perc_pretty(r[0], r[2]) + '%', r[3],
linked_text(row_to_xdict(r[6:]), morgue_link, human_number(
r[4])),
human_number(r[5]),
human_number(calc_avg_int(r[5], r[2]))
])
return results
def all_player_stats(c):
rows = query_rows(
c, '''SELECT name, games_played, games_won,
total_score, best_xl, best_score,
first_game_start, last_game_end
FROM players
ORDER BY name''')
def flatten_row(r):
return [
r[s] for s in (
"total_score name games_played games_won win_perc " +
"best_xl best_score avg_score first_game last_game").split()
]
res = []
for r in rows:
rl = list(r)
res.append(flatten_row(fixup_player_stats(c, rl)))
return res
def top_thing_scorers(c, table, thing):
# TODO can't this just be done with a count query?
games = xdict_rows(
query_rows(c,
game_select_from(table) + " ORDER BY name, " + thing))
score_counts = dict()
def inc_count(g):
name = g['name'].lower()
if not score_counts.has_key(name):
score_counts[name] = []
l = score_counts[name]
l.append(linked_text(g, morgue_link, g[thing]))
for g in games:
inc_count(g)
best_players = score_counts.items()
best_players.sort(lambda a, b: len(b[1]) - len(a[1]))
return [[len(x[1]),
canonicalize_player_name(c, x[0]), ", ".join(x[1])]
for x in best_players]
def gkills(c):
return kill_list(
query_rows(c, '''SELECT ghost, victim FROM ghost_victims'''))
def winners_for_day(c, day):
return query_rows(c,
'''SELECT player, wins FROM date_players
WHERE which_day = %s AND wins > 0
ORDER BY wins DESC, player''',
day)
def best_ziggurats(c, limit=6):
return [list(r) for r in
query_rows(c, '''SELECT player, place, zig_time
FROM ziggurats
ORDER BY deepest DESC, zig_time DESC''')]
def youngest_rune_finds(c, limit=6):
return query_rows(c, '''SELECT player, rune, xl, rune_time
FROM low_xl_rune_finds
ORDER BY xl, rune_time LIMIT %s''', limit)
def date_stats(c, restricted=False):
if restricted:
dates = query_rows(
c, '''SELECT which_day, games_ended, games_won
FROM per_day_stats
WHERE which_day > curdate() - interval 1 month
ORDER BY which_day DESC''')
else:
dates = query_rows(
c, '''SELECT which_day, games_ended, games_won
FROM per_day_stats ORDER BY which_day DESC''')
result = list()
# TODO: if one of these queries (likely the monthly one) returns a completely
# empty list, fill in the dates?
if len(dates) == 0:
return result
month = [None]
def new_month_stat(this_month):
return {
'month': this_month,
'games': 0,
'players': 0,
'wins': 0,
'winners': {}
}
def flush_month(month):
if not month:
return
result.append(fixup_month(c, month))
def inc_month_stats(date, eday, stats):
this_month = date.strftime('%Y-%m')
if not month[0] or month[0]['month'] != this_month:
flush_month(month[0])
month[0] = new_month_stat(this_month)
m = month[0]
m['games'] += stats['games']
m['wins'] += stats['wins']
mwinners = m['winners']
for wstat in stats['winners']:
if not mwinners.has_key(wstat[0]):
mwinners[wstat[0]] = 0
mwinners[wstat[0]] += wstat[1]
def record_date(d):
date = d[0]
edate = string_date(d[0])
stats = per_day_stats(c, edate, d[0], d[1], d[2])
inc_month_stats(date, edate, stats)
stats['winners'] = fixup_winners(stats['winners'])
result.append(stats)
# complex footwork here to fill in empty days
# note that the order of the query is descending, so we iterate backwards
cur = dates[0][0].date()
end = dates[-1][0].date()
delta = datetime.timedelta(days=1)
i = 0
while cur > end:
if dates[i][0].date() == cur:
record_date(dates[i])
i += 1
else:
dt_cur = datetime.datetime.combine(cur,
datetime.datetime.min.time())
record_date((dt_cur, 0, 0))
cur -= delta
flush_month(month[0])
return result
def gvictims(c):
return kill_list(query_rows(c,
'''SELECT victim, ghost FROM ghost_victims'''))
def gkills(c):
return kill_list(query_rows(c,
'''SELECT ghost, victim FROM ghost_victims'''))
def youngest_rune_finds(c, limit=6):
return query_rows(
c, '''SELECT player, rune, xl, rune_time
FROM low_xl_rune_finds
ORDER BY xl, rune_time LIMIT %s''', limit)
def winners_for_day(c, day):
return query_rows(
c, '''SELECT player, wins FROM date_players
WHERE which_day = %s AND wins > 0
ORDER BY wins DESC, player''', day)
def gvictims(c):
return kill_list(
query_rows(c, '''SELECT victim, ghost FROM ghost_victims'''))
