From 2a5cd35e0e84c7990daa9c9c4b8b0858d59414a5 Mon Sep 17 00:00:00 2001 From: Jan Behrens Date: Wed, 8 Jan 2014 20:12:56 +0100 Subject: [PATCH] Add pagination to player_captimes & map_captimes; convert SQL queries to SQLalchemy --- xonstat/__init__.py | 15 ++-- xonstat/models.py | 6 ++ xonstat/templates/map_captimes.mako | 16 +++- xonstat/templates/player_captimes.mako | 9 +- xonstat/views/map.py | 112 +++++++++++++++---------- xonstat/views/player.py | 92 ++++++++++++-------- 6 files changed, 158 insertions(+), 92 deletions(-) diff --git a/xonstat/__init__.py b/xonstat/__init__.py index f7bd04c..3053367 100644 --- a/xonstat/__init__.py +++ b/xonstat/__init__.py @@ -45,20 +45,17 @@ def main(global_config, **settings): config.add_route("player_game_index_json", "/player/{player_id:\d+}/games.json") config.add_view(player_game_index_json, route_name="player_game_index_json", renderer="jsonp") - config.add_route("player_hashkey_info_text", "/player/me") - config.add_view(player_hashkey_info_text, route_name="player_hashkey_info_text", renderer="player_hashkey_info_text.mako") - config.add_route("player_info", "/player/{id:\d+}") config.add_view(player_info, route_name="player_info", renderer="player_info.mako") - config.add_route("player_hashkey_info_json", "/player/me.json") - config.add_view(player_hashkey_info_json, route_name="player_hashkey_info_json", renderer="jsonp") + config.add_route("player_info_json", "/player/{id:\d+}.json") + config.add_view(player_info_json, route_name="player_info_json", renderer="jsonp") config.add_route("player_hashkey_info_text", "/player/me") config.add_view(player_hashkey_info_text, route_name="player_hashkey_info_text", renderer="player_hashkey_info_text.mako") - config.add_route("player_info_json", "/player/{id:\d+}.json") - config.add_view(player_info_json, route_name="player_info_json", renderer="jsonp") + config.add_route("player_hashkey_info_json", "/player/me.json") + config.add_view(player_hashkey_info_json, route_name="player_hashkey_info_json", renderer="jsonp") config.add_route("player_elo_info_text", "/player/{hashkey}/elo.txt") config.add_view(player_elo_info_text, route_name="player_elo_info_text", renderer="player_elo_info_text.mako") @@ -76,10 +73,10 @@ def main(global_config, **settings): config.add_route("player_index_json", "/players.json") config.add_view(player_index_json, route_name="player_index_json", renderer="jsonp") - config.add_route("player_captimes", "/player/{id:\d+}/captimes") + config.add_route("player_captimes", "/player/{player_id:\d+}/captimes") config.add_view(player_captimes, route_name="player_captimes", renderer="player_captimes.mako") - config.add_route("player_captimes_json", "/player/{id:\d+}/captimes.json") + config.add_route("player_captimes_json", "/player/{player_id:\d+}/captimes.json") config.add_view(player_captimes_json, route_name="player_captimes_json", renderer="jsonp") config.add_route("player_weaponstats_data_json", "/player/{id:\d+}/weaponstats.json") diff --git a/xonstat/models.py b/xonstat/models.py index 0d469e1..a8be324 100644 --- a/xonstat/models.py +++ b/xonstat/models.py @@ -267,6 +267,12 @@ class PlayerCaptime(object): def __repr__(self): return "" % (self.player_id, self.map_id) + def fuzzy_date(self): + return pretty_date(self.create_dt) + + def epoch(self): + return timegm(self.create_dt.timetuple()) + class SummaryStat(object): def __repr__(self): diff --git a/xonstat/templates/map_captimes.mako b/xonstat/templates/map_captimes.mako index 4fc0ec5..1373fc0 100644 --- a/xonstat/templates/map_captimes.mako +++ b/xonstat/templates/map_captimes.mako @@ -1,16 +1,25 @@ <%inherit file="base.mako"/> <%namespace name="nav" file="nav.mako" /> +<%namespace file="navlinks.mako" import="navlinks" /> + +<%block name="navigation"> +${nav.nav('maps')} + <%block name="title"> Map captimes +% if len(captimes) == 0: +

Sorry, no caps yet. Get playing!

+

Back to map info page

+% else:

${map.name}

-

Back to map info page

+

Back to map info page

Fastest Flag Captures:

@@ -25,7 +34,7 @@ Map captimes - % for ct in captimes: + % for ct in captimes.items: view ${ct.fastest_cap.total_seconds()} seconds @@ -45,3 +54,6 @@ Map captimes
+ +${navlinks("map_captimes", captimes.page, captimes.last_page, id=map_id, search_query=request.GET)} +% endif diff --git a/xonstat/templates/player_captimes.mako b/xonstat/templates/player_captimes.mako index a9872e4..2564160 100644 --- a/xonstat/templates/player_captimes.mako +++ b/xonstat/templates/player_captimes.mako @@ -1,5 +1,10 @@ <%inherit file="base.mako"/> <%namespace name="nav" file="nav.mako" /> +<%namespace file="navlinks.mako" import="navlinks" /> + +<%block name="navigation"> +${nav.nav('players')} + <%block name="title"> Player captimes @@ -29,7 +34,7 @@ Player captimes - % for ct in captimes: + % for ct in captimes.items: view ${ct.fastest_cap.total_seconds()} seconds @@ -43,4 +48,6 @@ Player captimes + +${navlinks("player_captimes", captimes.page, captimes.last_page, player_id=player_id, search_query=request.GET)} % endif diff --git a/xonstat/views/map.py b/xonstat/views/map.py index 004f22a..2d3957a 100644 --- a/xonstat/views/map.py +++ b/xonstat/views/map.py @@ -166,59 +166,83 @@ def map_info_json(request): def map_captimes_data(request): map_id = int(request.matchdict['id']) - - MapCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', + + if request.params.has_key('page'): + current_page = request.params['page'] + else: + current_page = 1 + + MapCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', + 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', 'player_id', 'player_nick', 'player_nick_stripped', 'player_nick_html', 'game_id', 'server_id', 'server_name']) - dbquery = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', - 'server_id', 'server_name', 'player_nick').\ - from_statement( - "SELECT ct.fastest_cap, " - "ct.create_dt, " - "ct.player_id, " - "ct.game_id, " - "g.server_id, " - "s.name server_name, " - "pgs.nick player_nick " - "FROM player_map_captimes ct, " - "games g, " - "maps m, " - "servers s, " - "player_game_stats pgs " - "WHERE ct.map_id = :map_id " - "AND g.game_id = ct.game_id " - "AND g.server_id = s.server_id " - "AND m.map_id = ct.map_id " - "AND pgs.player_id = ct.player_id " - "AND pgs.game_id = ct.game_id " - "ORDER BY ct.fastest_cap " - "LIMIT 25" - ).params(map_id=map_id).all() - mmap = DBSession.query(Map).filter_by(map_id=map_id).one() - map_captimes = [] - for row in dbquery: - map_captimes.append(MapCaptimes( - fastest_cap=row.fastest_cap, - create_dt=row.create_dt, - create_dt_epoch=timegm(row.create_dt.timetuple()), - create_dt_fuzzy=pretty_date(row.create_dt), - player_id=row.player_id, - player_nick=row.player_nick, - player_nick_stripped=strip_colors(row.player_nick), - player_nick_html=html_colors(row.player_nick), - game_id=row.game_id, - server_id=row.server_id, - server_name=row.server_name, - )) + #mct_q = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', + # 'server_id', 'server_name', 'player_nick').\ + # from_statement( + # "SELECT ct.fastest_cap, " + # "ct.create_dt, " + # "ct.player_id, " + # "ct.game_id, " + # "g.server_id, " + # "s.name server_name, " + # "pgs.nick player_nick " + # "FROM player_map_captimes ct, " + # "games g, " + # "maps m, " + # "servers s, " + # "player_game_stats pgs " + # "WHERE ct.map_id = :map_id " + # "AND g.game_id = ct.game_id " + # "AND g.server_id = s.server_id " + # "AND m.map_id = ct.map_id " + # "AND pgs.player_id = ct.player_id " + # "AND pgs.game_id = ct.game_id " + # "ORDER BY ct.fastest_cap " + # "LIMIT 25" + # ).params(map_id=map_id) + + #try: + if True: + mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt, + PlayerCaptime.player_id, PlayerCaptime.game_id, + Game.server_id, Server.name.label('server_name'), + PlayerGameStat.nick.label('player_nick')).\ + filter(PlayerCaptime.map_id==map_id).\ + filter(PlayerCaptime.game_id==Game.game_id).\ + filter(PlayerCaptime.map_id==Map.map_id).\ + filter(Game.server_id==Server.server_id).\ + filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\ + filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\ + order_by(expr.asc(PlayerCaptime.fastest_cap)) + + map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url) + + map_captimes.items = [MapCaptimes( + fastest_cap=row.fastest_cap, + create_dt=row.create_dt, + create_dt_epoch=timegm(row.create_dt.timetuple()), + create_dt_fuzzy=pretty_date(row.create_dt), + player_id=row.player_id, + player_nick=row.player_nick, + player_nick_stripped=strip_colors(row.player_nick), + player_nick_html=html_colors(row.player_nick), + game_id=row.game_id, + server_id=row.server_id, + server_name=row.server_name, + ) for row in map_captimes.items] + + #except Exception as e: + else: + map = None + map_captimes = None return { - 'captimes':map_captimes, 'map_id':map_id, - 'map_url':request.route_url('map_info', id=map_id), 'map':mmap, + 'captimes':map_captimes, } def map_captimes(request): diff --git a/xonstat/views/player.py b/xonstat/views/player.py index 75e9ff8..2041290 100644 --- a/xonstat/views/player.py +++ b/xonstat/views/player.py @@ -3,6 +3,7 @@ import logging import pyramid.httpexceptions import sqlalchemy as sa import sqlalchemy.sql.functions as func +import sqlalchemy.sql.expression as expr from calendar import timegm from collections import namedtuple from webhelpers.paginate import Page @@ -926,15 +927,13 @@ def player_elo_info_data(request): (idfp, status) = verify_request(request) log.debug("d0_blind_id verification: idfp={0} status={1}\n".format(idfp, status)) - hashkey = request.matchdict['hashkey'] -<<<<<<< HEAD log.debug("\n----- BEGIN REQUEST BODY -----\n" + request.body + "----- END REQUEST BODY -----\n\n") -======= + + hashkey = request.matchdict['hashkey'] # the incoming hashkey is double quoted, and WSGI unquotes once... hashkey = unquote(hashkey) ->>>>>>> master try: player = DBSession.query(Player).\ @@ -998,41 +997,58 @@ def player_elo_info_text(request): def player_captimes_data(request): - player_id = int(request.matchdict['id']) + player_id = int(request.matchdict['player_id']) if player_id <= 2: player_id = -1; - PlayerCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', - 'player_id', 'game_id', 'map_id', 'map_name', 'server_id', 'server_name']) + if request.params.has_key('page'): + current_page = request.params['page'] + else: + current_page = 1 - dbquery = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', 'map_id', - 'map_name', 'server_id', 'server_name').\ - from_statement( - "SELECT ct.fastest_cap, " - "ct.create_dt, " - "ct.player_id, " - "ct.game_id, " - "ct.map_id, " - "m.name map_name, " - "g.server_id, " - "s.name server_name " - "FROM player_map_captimes ct, " - "games g, " - "maps m, " - "servers s " - "WHERE ct.player_id = :player_id " - "AND g.game_id = ct.game_id " - "AND g.server_id = s.server_id " - "AND m.map_id = ct.map_id " - #"ORDER BY ct.fastest_cap " - "ORDER BY ct.create_dt desc" - ).params(player_id=player_id).all() + PlayerCaptimes = namedtuple('PlayerCaptimes', ['fastest_cap', + 'create_dt', 'create_dt_epoch', 'create_dt_fuzzy', + 'player_id', 'game_id', 'map_id', 'map_name', 'server_id', 'server_name']) player = DBSession.query(Player).filter_by(player_id=player_id).one() - player_captimes = [] - for row in dbquery: - player_captimes.append(PlayerCaptimes( + #pct_q = DBSession.query('fastest_cap', 'create_dt', 'player_id', 'game_id', 'map_id', + # 'map_name', 'server_id', 'server_name').\ + # from_statement( + # "SELECT ct.fastest_cap, " + # "ct.create_dt, " + # "ct.player_id, " + # "ct.game_id, " + # "ct.map_id, " + # "m.name map_name, " + # "g.server_id, " + # "s.name server_name " + # "FROM player_map_captimes ct, " + # "games g, " + # "maps m, " + # "servers s " + # "WHERE ct.player_id = :player_id " + # "AND g.game_id = ct.game_id " + # "AND g.server_id = s.server_id " + # "AND m.map_id = ct.map_id " + # #"ORDER BY ct.fastest_cap " + # "ORDER BY ct.create_dt desc" + # ).params(player_id=player_id) + + try: + pct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt, + PlayerCaptime.player_id, PlayerCaptime.game_id, PlayerCaptime.map_id, + Map.name.label('map_name'), Game.server_id, Server.name.label('server_name')).\ + filter(PlayerCaptime.player_id==player_id).\ + filter(PlayerCaptime.game_id==Game.game_id).\ + filter(PlayerCaptime.map_id==Map.map_id).\ + filter(Game.server_id==Server.server_id).\ + order_by(expr.asc(PlayerCaptime.fastest_cap)) # or PlayerCaptime.create_dt + + player_captimes = Page(pct_q, current_page, items_per_page=20, url=page_url) + + # replace the items in the canned pagination class with more rich ones + player_captimes.items = [PlayerCaptimes( fastest_cap=row.fastest_cap, create_dt=row.create_dt, create_dt_epoch=timegm(row.create_dt.timetuple()), @@ -1042,14 +1058,18 @@ def player_captimes_data(request): map_id=row.map_id, map_name=row.map_name, server_id=row.server_id, - server_name=row.server_name, - )) + server_name=row.server_name + ) for row in player_captimes.items] + + except Exception as e: + player = None + player_captimes = None return { - 'captimes':player_captimes, 'player_id':player_id, - 'player_url':request.route_url('player_info', id=player_id), 'player':player, + 'captimes':player_captimes, + #'player_url':request.route_url('player_info', id=player_id), } -- 2.39.2