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