Use pagination on the maps page instead of a full select count(*).
[xonotic/xonstat.git] / xonstat / views / map.py
1 import logging
2 from collections import namedtuple
3 from datetime import datetime, timedelta
4
5 import sqlalchemy.sql.expression as expr
6 import sqlalchemy.sql.functions as func
7 from pyramid.httpexceptions import HTTPNotFound
8 from webhelpers.paginate import Page
9 from xonstat.models import DBSession, Server, Map, Game, PlayerGameStat, Player, PlayerCaptime
10 from xonstat.models.map import MapCapTime
11 from xonstat.util import page_url, html_colors
12 from xonstat.views.helpers import RecentGame, recent_games_q
13
14 log = logging.getLogger(__name__)
15
16 # Defaults
17 INDEX_COUNT = 20
18
19
20 class MapIndex(object):
21     """Returns a list of maps."""
22
23     def __init__(self, request):
24         """Common parameter parsing."""
25         self.request = request
26         self.page = request.params.get("page", 1)
27         self.last = request.params.get("last", None)
28
29         # all views share this data, so we'll pre-calculate
30         self.maps = self.map_index()
31
32     def map_index(self):
33         """Returns the raw data shared by all renderers."""
34         try:
35             map_q = DBSession.query(Map)
36
37             if self.last:
38                 map_q = map_q.filter(Map.map_id < self.last)
39
40             map_q = map_q.order_by(Map.map_id.desc()).limit(INDEX_COUNT)
41             maps = map_q.all()
42
43         except Exception as e:
44             log.debug(e)
45             raise HTTPNotFound
46
47         return maps
48
49     def html(self):
50         """For rendering this data using something HTML-based."""
51         # build the query string
52         query = {}
53         if len(self.maps) > 1:
54             query['last'] = self.maps[-1].map_id
55
56         return {
57             'maps': self.maps,
58             'query': query,
59         }
60
61     def json(self):
62         """For rendering this data using JSON."""
63         return {
64             'maps': [m.to_dict() for m in self.maps],
65             'last': self.last,
66         }
67
68
69 def _map_info_data(request):
70     map_id = int(request.matchdict['id'])
71
72     try:
73         leaderboard_lifetime = int(
74                 request.registry.settings['xonstat.leaderboard_lifetime'])
75     except:
76         leaderboard_lifetime = 30
77
78     leaderboard_count = 10
79     recent_games_count = 20
80
81     # captime tuples
82     Captime = namedtuple('Captime', ['player_id', 'nick_html_colors',
83         'fastest_cap', 'game_id'])
84
85     try:
86         gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
87
88         # recent games played in descending order
89         rgs = recent_games_q(map_id=map_id).limit(recent_games_count).all()
90         recent_games = [RecentGame(row) for row in rgs]
91
92         # top players by score
93         top_scorers = DBSession.query(Player.player_id, Player.nick,
94                 func.sum(PlayerGameStat.score)).\
95                 filter(Player.player_id == PlayerGameStat.player_id).\
96                 filter(Game.game_id == PlayerGameStat.game_id).\
97                 filter(Game.map_id == map_id).\
98                 filter(Player.player_id > 2).\
99                 filter(PlayerGameStat.create_dt >
100                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
101                 order_by(expr.desc(func.sum(PlayerGameStat.score))).\
102                 group_by(Player.nick).\
103                 group_by(Player.player_id).all()[0:leaderboard_count]
104
105         top_scorers = [(player_id, html_colors(nick), score) \
106                 for (player_id, nick, score) in top_scorers]
107
108         # top players by playing time
109         top_players = DBSession.query(Player.player_id, Player.nick,
110                 func.sum(PlayerGameStat.alivetime)).\
111                 filter(Player.player_id == PlayerGameStat.player_id).\
112                 filter(Game.game_id == PlayerGameStat.game_id).\
113                 filter(Game.map_id == map_id).\
114                 filter(Player.player_id > 2).\
115                 filter(PlayerGameStat.create_dt >
116                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
117                 order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\
118                 group_by(Player.nick).\
119                 group_by(Player.player_id).all()[0:leaderboard_count]
120
121         top_players = [(player_id, html_colors(nick), score) \
122                 for (player_id, nick, score) in top_players]
123
124         # top servers using/playing this map
125         top_servers = DBSession.query(Server.server_id, Server.name,
126                 func.count(Game.game_id)).\
127                 filter(Game.server_id == Server.server_id).\
128                 filter(Game.map_id == map_id).\
129                 filter(Game.create_dt >
130                         (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\
131                 order_by(expr.desc(func.count(Game.game_id))).\
132                 group_by(Server.name).\
133                 group_by(Server.server_id).all()[0:leaderboard_count]
134
135         # TODO make this a configuration parameter to be set in the settings
136         # top captimes
137         captimes_raw = DBSession.query(Player.player_id, Player.nick,
138             PlayerCaptime.fastest_cap, PlayerCaptime.game_id).\
139                 filter(PlayerCaptime.map_id == map_id).\
140                 filter(Player.player_id == PlayerCaptime.player_id).\
141                 order_by(PlayerCaptime.fastest_cap).\
142                 limit(10).\
143                 all()
144
145         captimes = [Captime(c.player_id, html_colors(c.nick),
146             c.fastest_cap, c.game_id) for c in captimes_raw]
147
148     except Exception as e:
149         gmap = None
150     return {'gmap':gmap,
151             'recent_games':recent_games,
152             'top_scorers':top_scorers,
153             'top_players':top_players,
154             'top_servers':top_servers,
155             'captimes':captimes,
156             }
157
158
159 def map_info(request):
160     """
161     List the information stored about a given map.
162     """
163     mapinfo_data =  _map_info_data(request)
164
165     # FIXME: code clone, should get these from _map_info_data
166     leaderboard_count = 10
167     recent_games_count = 20
168
169     for i in range(leaderboard_count-len(mapinfo_data['top_scorers'])):
170         mapinfo_data['top_scorers'].append(('-', '-', '-'))
171
172     for i in range(leaderboard_count-len(mapinfo_data['top_players'])):
173         mapinfo_data['top_players'].append(('-', '-', '-'))
174
175     for i in range(leaderboard_count-len(mapinfo_data['top_servers'])):
176         mapinfo_data['top_servers'].append(('-', '-', '-'))
177
178     return mapinfo_data
179
180
181 def map_info_json(request):
182     """
183     List the information stored about a given map. JSON.
184     """
185     return [{'status':'not implemented'}]
186
187
188 def map_captimes_data(request):
189     map_id = int(request.matchdict['id'])
190
191     current_page = request.params.get('page', 1)
192
193     try:
194         mmap = DBSession.query(Map).filter_by(map_id=map_id).one()
195
196         mct_q = DBSession.query(PlayerCaptime.fastest_cap, PlayerCaptime.create_dt,
197                 PlayerCaptime.player_id, PlayerCaptime.game_id,
198                 Game.server_id, Server.name.label('server_name'),
199                 PlayerGameStat.nick.label('player_nick')).\
200                 filter(PlayerCaptime.map_id==map_id).\
201                 filter(PlayerCaptime.game_id==Game.game_id).\
202                 filter(PlayerCaptime.map_id==Map.map_id).\
203                 filter(Game.server_id==Server.server_id).\
204                 filter(PlayerCaptime.player_id==PlayerGameStat.player_id).\
205                 filter(PlayerCaptime.game_id==PlayerGameStat.game_id).\
206                 order_by(expr.asc(PlayerCaptime.fastest_cap))
207
208     except Exception as e:
209         raise httpexceptions.HTTPNotFound
210
211     map_captimes = Page(mct_q, current_page, items_per_page=20, url=page_url)
212
213     map_captimes.items = [MapCapTime(row) for row in map_captimes.items]
214
215     return {
216             'map_id':map_id,
217             'map':mmap,
218             'captimes':map_captimes,
219         }
220
221 def map_captimes(request):
222     return map_captimes_data(request)
223
224 def map_captimes_json(request):
225     current_page = request.params.get('page', 1)
226     data = map_captimes_data(request)
227
228     return {
229             "map": data["map"].to_dict(),
230             "captimes": [e.to_dict() for e in data["captimes"].items],
231             "page": current_page,
232             }