X-Git-Url: http://de.git.xonotic.org/?a=blobdiff_plain;f=xonstat%2Fviews%2Fmain.py;h=3f76f1e56168ce52fe037db2c843656b61baf963;hb=f0f55403d0405bf14e035133c21832afd0ba05ce;hp=76eeb073aac229a113e1c15e5e0bce9b72ea7607;hpb=5f80cb7090f4221ba5213f301bf1be9b1fbaa040;p=xonotic%2Fxonstat.git diff --git a/xonstat/views/main.py b/xonstat/views/main.py index 76eeb07..3f76f1e 100644 --- a/xonstat/views/main.py +++ b/xonstat/views/main.py @@ -1,69 +1,117 @@ import logging -import sqlalchemy.sql.functions as func -import sqlalchemy.sql.expression as expr -from beaker.cache import cache_regions, cache_region -from collections import namedtuple from datetime import datetime, timedelta -from pyramid.response import Response -from xonstat.models import * -from xonstat.util import * -from xonstat.views.helpers import RecentGame, recent_games_q -from webhelpers.paginate import Page +from beaker.cache import cache_region +from sqlalchemy import text +from xonstat.models import DBSession, PlayerRank, ActivePlayer, ActiveServer, ActiveMap +from xonstat.views.helpers import RecentGame, recent_games_q log = logging.getLogger(__name__) @cache_region('hourly_term') -def get_summary_stats(): +def summary_stats_data(scope="all"): """ - Gets the following aggregate or "summary" statistics about stats: - - the total number of players (total_players) - - the total number of servers (total_servers) - - the total number of games (total_games) - - the total number of dm games (dm_games) - - the total number of duel games (duel_games) - - the total number of ctf games (ctf_games) - - It is worth noting that there is also a table built to house these - stats in case the query in this function becomes too long for the - one time it runs per hour. In that case there is a script in the - xonstatdb repo - update_summary_stats.sql - that can be used via - cron to update the data offline. + Gets the summary stats (number of active players, the game type, and the number of games) + for a given scope. + + :param scope: The scope to fetch from the table. May be "all" or "day". + :return: list[tuple] """ - summary_stats = DBSession.query("total_players", "total_servers", - "total_games", "dm_games", "duel_games", "ctf_games").\ - from_statement( - """ - with total_games as ( - select game_type_cd, count(*) total_games - from games - where game_type_cd in ('duel', 'dm', 'ctf') - group by game_type_cd - ), - total_players as ( - select count(*) total_players - from players - where active_ind = true - ), - total_servers as ( - select count(*) total_servers - from servers - where active_ind = true - ) - select tp.total_players, ts.total_servers, dm.total_games+ - duel.total_games+ctf.total_games total_games, - dm.total_games dm_games, duel.total_games duel_games, - ctf.total_games ctf_games - from total_games dm, total_games duel, total_games ctf, - total_players tp, total_servers ts - where dm.game_type_cd = 'dm' - and ctf.game_type_cd = 'ctf' - and duel.game_type_cd = 'duel' - """ - ).one() - - return summary_stats + sql = text("SELECT num_players, game_type_cd, num_games, create_dt refresh_dt " + "FROM summary_stats_mv " + "WHERE scope = :scope " + "ORDER BY sort_order ") + + try: + ss = DBSession.query("num_players", "game_type_cd", "num_games", "refresh_dt").\ + from_statement(sql).params(scope=scope).all() + + return ss + except Exception as e: + log.error(e) + return [] + + +def summary_stats_json(request): + scope = request.params.get("scope", "all") + if scope not in ["all", "day"]: + scope = "all" + + ss = summary_stats_data(scope) + + # default values + players = 0 + last_refreshed = "unknown" + games = [] + + if len(ss) > 0: + players = ss[0].num_players + last_refreshed = ss[0].refresh_dt.isoformat() + games = [{"game_type_cd": r.game_type_cd, "num_games": r.num_games} for r in ss] + + return { + "players": players, + "scope": scope, + "last_refreshed": last_refreshed, + "games": games, + } + + +@cache_region('hourly_term') +def summary_stats_string(scope="all"): + """ + Assembles the summary stats data into a readable line for direct inclusion in templates. + """ + try: + ss = summary_stats_data(scope) + + i = 1 + total_games = 0 + other_games = 0 + for row in ss: + # the number of players is constant in each row + total_players = row.num_players + + total_games += row.num_games + + # we can't show all game types on the single summary line, so any + # past the fifth one will get bundled in to an "other" count + if i > 5: + other_games += row.num_games + + i += 1 + + # don't send anything if we don't have any activity + if total_games == 0: + stat_line = None + else: + # This is ugly because we're doing template-like stuff within the + # view code. The alternative isn't any better, though: we would + # have to assemble the string inside the template by using a Python + # code block. For now I'll leave it like this since it is the lesser + # of two evils IMO. + # Also we need to hard-code the URL structure in here to allow caching, + # which also sucks. + in_paren = "; ".join(["{:2,d} {}".format( + g.num_games, + "{0}".format(g.game_type_cd) + ) for g in ss[:5]]) + + if other_games > 0: + in_paren += "; {:2,d} other".format(other_games) + + stat_line = "{:2,d} players and {:2,d} games ({})".format( + total_players, + total_games, + in_paren + ) + + except Exception as e: + stat_line = None + raise e + + return stat_line @cache_region('hourly_term') @@ -89,125 +137,58 @@ def get_ranks(game_type_cd): return ranks -def top_players_by_time_q(cutoff_days): - """ - Query for the top players by the amount of time played during a date range. - - Games older than cutoff_days days old are ignored. - """ - - # only games played during this range are considered - right_now = datetime.utcnow() - cutoff_dt = right_now - timedelta(days=cutoff_days) - - top_players_q = DBSession.query(Player.player_id, Player.nick, - func.sum(PlayerGameStat.alivetime)).\ - filter(Player.player_id == PlayerGameStat.player_id).\ - filter(Player.player_id > 2).\ - filter(expr.between(PlayerGameStat.create_dt, cutoff_dt, right_now)).\ - order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ - group_by(Player.nick).\ - group_by(Player.player_id) - - return top_players_q - - @cache_region('hourly_term') -def get_top_players_by_time(cutoff_days): +def get_top_players_by_time(limit=None, start=None): """ The top players by the amount of time played during a date range. - - Games older than cutoff_days days old are ignored. """ - # how many to retrieve - count = 10 + q = DBSession.query(ActivePlayer) - # only games played during this range are considered - right_now = datetime.utcnow() - cutoff_dt = right_now - timedelta(days=cutoff_days) - - top_players_q = top_players_by_time_q(cutoff_days) - - top_players = top_players_q.limit(count).all() - - top_players = [(player_id, html_colors(nick), score) \ - for (player_id, nick, score) in top_players] - - return top_players + if start is not None: + q = q.filter(ActivePlayer.sort_order >= start) + q = q.order_by(ActivePlayer.sort_order) -def top_servers_by_players_q(cutoff_days): - """ - Query to get the top servers by the amount of players active - during a date range. - - Games older than cutoff_days days old are ignored. - """ - # only games played during this range are considered - right_now = datetime.utcnow() - cutoff_dt = right_now - timedelta(days=cutoff_days) + if limit is not None: + q = q.limit(limit) - top_servers_q = DBSession.query(Server.server_id, Server.name, - func.count()).\ - filter(Game.server_id==Server.server_id).\ - filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\ - order_by(expr.desc(func.count(Game.game_id))).\ - group_by(Server.server_id).\ - group_by(Server.name) - - return top_servers_q + return q.all() @cache_region('hourly_term') -def get_top_servers_by_players(cutoff_days): +def get_top_servers_by_play_time(limit=None, start=None): """ - The top servers by the amount of players active during a date range. - - Games older than cutoff_days days old are ignored. + The top servers by the cumulative amount of time played on them during a given interval. """ - # how many to retrieve - count = 10 + q = DBSession.query(ActiveServer) - top_servers = top_servers_by_players_q(cutoff_days).limit(count).all() + if start is not None: + q = q.filter(ActiveServer.sort_order >= start) - return top_servers + q = q.order_by(ActiveServer.sort_order) + if limit is not None: + q = q.limit(limit) -def top_maps_by_times_played_q(cutoff_days): - """ - Query to retrieve the top maps by the amount of times it was played - during a date range. - - Games older than cutoff_days days old are ignored. - """ - # only games played during this range are considered - right_now = datetime.utcnow() - cutoff_dt = right_now - timedelta(days=cutoff_days) - - top_maps_q = DBSession.query(Game.map_id, Map.name, - func.count()).\ - filter(Map.map_id==Game.map_id).\ - filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\ - order_by(expr.desc(func.count())).\ - group_by(Game.map_id).\ - group_by(Map.name) - - return top_maps_q + return q.all() @cache_region('hourly_term') -def get_top_maps_by_times_played(cutoff_days): +def get_top_maps_by_games(limit=None, start=None): """ - The top maps by the amount of times it was played during a date range. - - Games older than cutoff_days days old are ignored. + The top maps by the number of games played during a date range. """ - # how many to retrieve - count = 10 + q = DBSession.query(ActiveMap) - top_maps = top_maps_by_times_played_q(cutoff_days).limit(count).all() + if start is not None: + q = q.filter(ActiveMap.sort_order >= start) - return top_maps + q = q.order_by(ActiveMap.sort_order) + + if limit is not None: + q = q.limit(limit) + + return q.all() def _main_index_data(request): @@ -221,10 +202,9 @@ def _main_index_data(request): recent_games_count = 20 # summary statistics for the tagline - try: - summary_stats = get_summary_stats() - except: - summary_stats = None + stat_line = summary_stats_string("all") + day_stat_line = summary_stats_string("day") + # the three top ranks tables ranks = [] @@ -237,13 +217,13 @@ def _main_index_data(request): back_then = datetime.utcnow() - timedelta(days=leaderboard_lifetime) # top players by playing time - top_players = get_top_players_by_time(leaderboard_lifetime) + top_players = get_top_players_by_time(10) - # top servers by number of total players played - top_servers = get_top_servers_by_players(leaderboard_lifetime) + # top servers by number of games + top_servers = get_top_servers_by_play_time(10) # top maps by total times played - top_maps = get_top_maps_by_times_played(leaderboard_lifetime) + top_maps = get_top_maps_by_games(10) # recent games played in descending order rgs = recent_games_q(cutoff=back_then).limit(recent_games_count).all() @@ -254,7 +234,8 @@ def _main_index_data(request): 'top_maps':top_maps, 'recent_games':recent_games, 'ranks':ranks, - 'summary_stats':summary_stats, + 'stat_line':stat_line, + 'day_stat_line':day_stat_line, } @@ -262,22 +243,7 @@ def main_index(request): """ Display the main page information. """ - mainindex_data = _main_index_data(request) - - # FIXME: code clone, should get these from _main_index_data - leaderboard_count = 10 - recent_games_count = 20 - - for i in range(leaderboard_count-len(mainindex_data['top_players'])): - mainindex_data['top_players'].append(('-', '-', '-')) - - for i in range(leaderboard_count-len(mainindex_data['top_servers'])): - mainindex_data['top_servers'].append(('-', '-', '-')) - - for i in range(leaderboard_count-len(mainindex_data['top_maps'])): - mainindex_data['top_maps'].append(('-', '-', '-')) - - return mainindex_data + return _main_index_data(request) def main_index_json(request): @@ -287,43 +253,61 @@ def main_index_json(request): return [{'status':'not implemented'}] -def top_players_by_time(request): - current_page = request.params.get('page', 1) - - cutoff_days = int(request.registry.settings.\ - get('xonstat.leaderboard_lifetime', 30)) - - top_players_q = top_players_by_time_q(cutoff_days) - - top_players = Page(top_players_q, current_page, items_per_page=25, url=page_url) - - top_players.items = [(player_id, html_colors(nick), score) \ - for (player_id, nick, score) in top_players.items] +def top_players_index(request): + try: + start = int(request.params.get('start', None)) + except: + start = None - return {'top_players':top_players} + top_players = get_top_players_by_time(20, start) + # building a query string + query = {} + if len(top_players) > 1: + query['start'] = top_players[-1].sort_order + 1 -def top_servers_by_players(request): - current_page = request.params.get('page', 1) + return { + 'top_players':top_players, + 'query':query, + 'start':start, + } - cutoff_days = int(request.registry.settings.\ - get('xonstat.leaderboard_lifetime', 30)) - top_servers_q = top_servers_by_players_q(cutoff_days) +def top_servers_index(request): + try: + start = int(request.params.get('start', None)) + except: + start = None - top_servers = Page(top_servers_q, current_page, items_per_page=25, url=page_url) + top_servers = get_top_servers_by_play_time(20, start) - return {'top_servers':top_servers} + # building a query string + query = {} + if len(top_servers) > 1: + query['start'] = top_servers[-1].sort_order + 1 + return { + 'top_servers':top_servers, + 'query':query, + 'start':start, + } -def top_maps_by_times_played(request): - current_page = request.params.get('page', 1) - cutoff_days = int(request.registry.settings.\ - get('xonstat.leaderboard_lifetime', 30)) +def top_maps_index(request): + try: + start = int(request.params.get('start', None)) + except: + start = None - top_maps_q = top_maps_by_times_played_q(cutoff_days) + top_maps = get_top_maps_by_games(20, start) - top_maps = Page(top_maps_q, current_page, items_per_page=25, url=page_url) + # building a query string + query = {} + if len(top_maps) > 1: + query['start'] = top_maps[-1].sort_order + 1 - return {'top_maps':top_maps} + return { + 'top_maps':top_maps, + 'query':query, + 'start':start, + }