xonstat/views.py

   1 import datetime
   2 import logging
   3 import re
   4 import time
   5 from pyramid.response import Response
   6 from sqlalchemy.orm.exc import MultipleResultsFound, NoResultFound
   7 from sqlalchemy import desc
   8 from webhelpers.paginate import Page, PageURL
   9 from xonstat.models import *
  10 from xonstat.util import page_url
  11
  12 log = logging.getLogger(__name__)
  13
  14 ##########################################################################
  15 def main_index(request):
  16     """
  17     This is the main entry point to the entire site.
  18     """
  19     log.debug("testing logging; entered MainHandler.index()")
  20     return {'project':'xonstat'}
  21
  22
  23 ##########################################################################
  24 def player_index(request):
  25     """
  26     Provides a list of all the current players.
  27     """
  28     players = DBSession.query(Player)
  29
  30     log.debug("testing logging; entered PlayerHandler.index()")
  31     return {'players':players}
  32
  33 def player_info(request):
  34     """
  35     Provides detailed information on a specific player
  36     """
  37     player_id = request.matchdict['id']
  38     try:
  39         player = DBSession.query(Player).filter_by(player_id=player_id).one()
  40
  41         weapon_stats = DBSession.query("descr", "actual_total",
  42                 "max_total", "hit_total", "fired_total", "frags_total").\
  43                 from_statement(
  44                     "select cw.descr, sum(actual) actual_total, "
  45                     "sum(max) max_total, sum(hit) hit_total, "
  46                     "sum(fired) fired_total, sum(frags) frags_total "
  47                     "from xonstat.player_weapon_stats ws, xonstat.cd_weapon cw "
  48                     "where ws.weapon_cd = cw.weapon_cd "
  49                     "and player_id = :player_id "
  50                     "group by descr "
  51                     "order by descr"
  52                 ).params(player_id=player_id).all()
  53
  54         log.debug(weapon_stats)
  55
  56         recent_games = DBSession.query(PlayerGameStat, Game, Server, Map).\
  57                 filter(PlayerGameStat.player_id == player_id).\
  58                 filter(PlayerGameStat.game_id == Game.game_id).\
  59                 filter(Game.server_id == Server.server_id).\
  60                 filter(Game.map_id == Map.map_id).\
  61                 order_by(Game.game_id.desc())[0:10]
  62
  63     except Exception as e:
  64         player = None
  65         weapon_stats = None
  66         recent_games = None
  67     return {'player':player,
  68             'recent_games':recent_games,
  69             'weapon_stats':weapon_stats}
  70
  71
  72 def player_game_index(request):
  73     """
  74     Provides an index of the games in which a particular
  75     player was involved. This is ordered by game_id, with
  76     the most recent game_ids first. Paginated.
  77     """
  78     player_id = request.matchdict['player_id']
  79
  80     if 'page' in request.matchdict:
  81         current_page = request.matchdict['page']
  82     else:
  83         current_page = 1
  84
  85     try:
  86         player = DBSession.query(Player).filter_by(player_id=player_id).one()
  87
  88         games_q = DBSession.query(PlayerGameStat, Game, Server, Map).\
  89                 filter(PlayerGameStat.player_id == player_id).\
  90                 filter(PlayerGameStat.game_id == Game.game_id).\
  91                 filter(Game.server_id == Server.server_id).\
  92                 filter(Game.map_id == Map.map_id).\
  93                 order_by(Game.game_id.desc())
  94
  95         games = Page(games_q, current_page, url=page_url)
  96
  97
  98     except Exception as e:
  99         player = None
 100         games = None
 101         raise e
 102
 103     return {'player':player,
 104             'games':games}
 105
 106
 107 def player_weapon_stats(request):
 108     """
 109     List the accuracy statistics for the given player_id in a particular
 110     game.
 111     """
 112     game_id = request.matchdict['game_id']
 113     pgstat_id = request.matchdict['pgstat_id']
 114     try:
 115         pwstats = DBSession.query(PlayerWeaponStat, Weapon).\
 116                 filter(PlayerWeaponStat.weapon_cd==Weapon.weapon_cd).\
 117                 filter_by(game_id=game_id).\
 118                 filter_by(player_game_stat_id=pgstat_id).\
 119                 order_by(Weapon.descr).\
 120                 all()
 121
 122         pgstat = DBSession.query(PlayerGameStat).\
 123                 filter_by(player_game_stat_id=pgstat_id).one()
 124
 125         game = DBSession.query(Game).filter_by(game_id=game_id).one()
 126
 127         log.debug(pwstats)
 128         log.debug(pgstat)
 129         log.debug(game)
 130
 131     except Exception as e:
 132         pwstats = None
 133         pgstat = None
 134         game = None
 135         raise e
 136     return {'pwstats':pwstats, 'pgstat':pgstat, 'game':game}
 137
 138
 139 ##########################################################################
 140 def game_index(request):
 141     """
 142     Provides a list of current games, with the associated game stats.
 143     These games are ordered by game_id, with the most current ones first.
 144     Paginated.
 145     """
 146     if 'page' in request.matchdict:
 147         current_page = request.matchdict['page']
 148     else:
 149         current_page = 1
 150
 151     games_q = DBSession.query(Game, Server, Map).\
 152             filter(Game.server_id == Server.server_id).\
 153             filter(Game.map_id == Map.map_id).\
 154             order_by(Game.game_id.desc())
 155
 156     games = Page(games_q, current_page, url=page_url)
 157
 158     pgstats = {}
 159     for (game, server, map) in games:
 160         pgstats[game.game_id] = DBSession.query(PlayerGameStat).\
 161                 filter(PlayerGameStat.game_id == game.game_id).\
 162                 order_by(PlayerGameStat.rank).\
 163                 order_by(PlayerGameStat.score).all()
 164
 165     return {'games':games,
 166             'pgstats':pgstats}
 167
 168
 169 def game_info(request):
 170     """
 171     List the game stats (scoreboard) for a particular game. Paginated.
 172     """
 173     game_id = request.matchdict['id']
 174     try:
 175         notfound = False
 176
 177         (start_dt, game_type_cd, server_id, server_name, map_id, map_name) = \
 178         DBSession.query("start_dt", "game_type_cd", "server_id",
 179                 "server_name", "map_id", "map_name").\
 180                 from_statement("select g.start_dt, g.game_type_cd, "
 181                         "g.server_id, s.name as server_name, g.map_id, "
 182                         "m.name as map_name "
 183                         "from games g, servers s, maps m "
 184                         "where g.game_id = :game_id "
 185                         "and g.server_id = s.server_id "
 186                         "and g.map_id = m.map_id").\
 187                         params(game_id=game_id).one()
 188
 189         player_game_stats = DBSession.query(PlayerGameStat).\
 190                 from_statement("select * from player_game_stats "
 191                         "where game_id = :game_id "
 192                         "order by score desc").\
 193                             params(game_id=game_id).all()
 194     except Exception as inst:
 195         notfound = True
 196         start_dt = None
 197         game_type_cd = None
 198         server_id = None
 199         server_name = None
 200         map_id = None
 201         map_name = None
 202         player_game_stats = None
 203
 204     return {'notfound':notfound,
 205             'start_dt':start_dt,
 206             'game_type_cd':game_type_cd,
 207             'server_id':server_id,
 208             'server_name':server_name,
 209             'map_id':map_id,
 210             'map_name':map_name,
 211             'player_game_stats':player_game_stats}
 212
 213
 214 ##########################################################################
 215 def server_info(request):
 216     """
 217     List the stored information about a given server.
 218     """
 219     server_id = request.matchdict['id']
 220     try:
 221         server = DBSession.query(Server).filter_by(server_id=server_id).one()
 222         recent_games = DBSession.query(Game, Server, Map).\
 223                 filter(Game.server_id == server_id).\
 224                 filter(Game.server_id == Server.server_id).\
 225                 filter(Game.map_id == Map.map_id).\
 226                 order_by(Game.game_id.desc())[0:10]
 227
 228     except Exception as e:
 229         server = None
 230         recent_games = None
 231     return {'server':server,
 232             'recent_games':recent_games}
 233
 234
 235 def server_game_index(request):
 236     """
 237     List the games played on a given server. Paginated.
 238     """
 239     server_id = request.matchdict['server_id']
 240     current_page = request.matchdict['page']
 241
 242     try:
 243         server = DBSession.query(Server).filter_by(server_id=server_id).one()
 244
 245         games_q = DBSession.query(Game, Server, Map).\
 246                 filter(Game.server_id == server_id).\
 247                 filter(Game.server_id == Server.server_id).\
 248                 filter(Game.map_id == Map.map_id).\
 249                 order_by(Game.game_id.desc())
 250
 251         games = Page(games_q, current_page, url=page_url)
 252     except Exception as e:
 253         server = None
 254         games = None
 255         raise e
 256
 257     return {'games':games,
 258             'server':server}
 259
 260
 261 ##########################################################################
 262 def map_info(request):
 263     """
 264     List the information stored about a given map.
 265     """
 266     map_id = request.matchdict['id']
 267     try:
 268         gmap = DBSession.query(Map).filter_by(map_id=map_id).one()
 269     except:
 270         gmap = None
 271     return {'gmap':gmap}
 272
 273
 274 ##########################################################################
 275 def get_or_create_server(session=None, name=None):
 276     """
 277     Find a server by name or create one if not found. Parameters:
 278
 279     session - SQLAlchemy database session factory
 280     name - server name of the server to be found or created
 281     """
 282     try:
 283         # find one by that name, if it exists
 284         server = session.query(Server).filter_by(name=name).one()
 285         log.debug("Found server id {0} with name {1}.".format(
 286             server.server_id, server.name))
 287     except NoResultFound, e:
 288         server = Server(name=name)
 289         session.add(server)
 290         session.flush()
 291         log.debug("Created server id {0} with name {1}".format(
 292             server.server_id, server.name))
 293     except MultipleResultsFound, e:
 294         # multiple found, so use the first one but warn
 295         log.debug(e)
 296         servers = session.query(Server).filter_by(name=name).order_by(
 297                 Server.server_id).all()
 298         server = servers[0]
 299         log.debug("Created server id {0} with name {1} but found \
 300                 multiple".format(
 301             server.server_id, server.name))
 302
 303     return server
 304
 305 def get_or_create_map(session=None, name=None):
 306     """
 307     Find a map by name or create one if not found. Parameters:
 308
 309     session - SQLAlchemy database session factory
 310     name - map name of the map to be found or created
 311     """
 312     try:
 313         # find one by the name, if it exists
 314         gmap = session.query(Map).filter_by(name=name).one()
 315         log.debug("Found map id {0} with name {1}.".format(gmap.map_id,
 316             gmap.name))
 317     except NoResultFound, e:
 318         gmap = Map(name=name)
 319         session.add(gmap)
 320         session.flush()
 321         log.debug("Created map id {0} with name {1}.".format(gmap.map_id,
 322             gmap.name))
 323     except MultipleResultsFound, e:
 324         # multiple found, so use the first one but warn
 325         log.debug(e)
 326         gmaps = session.query(Map).filter_by(name=name).order_by(
 327                 Map.map_id).all()
 328         gmap = gmaps[0]
 329         log.debug("Found map id {0} with name {1} but found \
 330                 multiple.".format(gmap.map_id, gmap.name))
 331
 332     return gmap
 333
 334
 335 def create_game(session=None, start_dt=None, game_type_cd=None,
 336         server_id=None, map_id=None, winner=None):
 337     """
 338     Creates a game. Parameters:
 339
 340     session - SQLAlchemy database session factory
 341     start_dt - when the game started (datetime object)
 342     game_type_cd - the game type of the game being played
 343     server_id - server identifier of the server hosting the game
 344     map_id - map on which the game was played
 345     winner - the team id of the team that won
 346     """
 347
 348     game = Game(start_dt=start_dt, game_type_cd=game_type_cd,
 349                 server_id=server_id, map_id=map_id, winner=winner)
 350     session.add(game)
 351     session.flush()
 352     log.debug("Created game id {0} on server {1}, map {2} at time \
 353             {3} and on map {4}".format(game.game_id,
 354                 server_id, map_id, start_dt, map_id))
 355
 356     return game
 357
 358
 359 def get_or_create_player(session=None, hashkey=None, nick=None):
 360     """
 361     Finds a player by hashkey or creates a new one (along with a
 362     corresponding hashkey entry. Parameters:
 363
 364     session - SQLAlchemy database session factory
 365     hashkey - hashkey of the player to be found or created
 366     nick - nick of the player (in case of a first time create)
 367     """
 368     # if we have a bot
 369     if re.search('^bot#\d+$', hashkey):
 370         player = session.query(Player).filter_by(player_id=1).one()
 371     # if we have an untracked player
 372     elif re.search('^player#\d+$', hashkey):
 373         player = session.query(Player).filter_by(player_id=2).one()
 374     # else it is a tracked player
 375     else:
 376         # see if the player is already in the database
 377         # if not, create one and the hashkey along with it
 378         try:
 379             hashkey = session.query(Hashkey).filter_by(
 380                     hashkey=hashkey).one()
 381             player = session.query(Player).filter_by(
 382                     player_id=hashkey.player_id).one()
 383             log.debug("Found existing player {0} with hashkey {1}.".format(
 384                 player.player_id, hashkey.hashkey))
 385         except:
 386             player = Player()
 387
 388             if nick:
 389                 player.nick = nick
 390
 391             session.add(player)
 392             session.flush()
 393             hashkey = Hashkey(player_id=player.player_id, hashkey=hashkey)
 394             session.add(hashkey)
 395             log.debug("Created player {0} with hashkey {1}.".format(
 396                 player.player_id, hashkey.hashkey))
 397
 398     return player
 399
 400 def create_player_game_stat(session=None, player=None,
 401         game=None, player_events=None):
 402     """
 403     Creates game statistics for a given player in a given game. Parameters:
 404
 405     session - SQLAlchemy session factory
 406     player - Player record of the player who owns the stats
 407     game - Game record for the game to which the stats pertain
 408     player_events - dictionary for the actual stats that need to be transformed
 409     """
 410
 411     # in here setup default values (e.g. if game type is CTF then
 412     # set kills=0, score=0, captures=0, pickups=0, fckills=0, etc
 413     # TODO: use game's create date here instead of now()
 414     pgstat = PlayerGameStat(create_dt=datetime.datetime.now())
 415
 416     # set player id from player record
 417     pgstat.player_id = player.player_id
 418
 419     #set game id from game record
 420     pgstat.game_id = game.game_id
 421
 422     # all games have a score
 423     pgstat.score = 0
 424
 425     if game.game_type_cd == 'dm':
 426         pgstat.kills = 0
 427         pgstat.deaths = 0
 428         pgstat.suicides = 0
 429     elif game.game_type_cd == 'ctf':
 430         pgstat.kills = 0
 431         pgstat.captures = 0
 432         pgstat.pickups = 0
 433         pgstat.drops = 0
 434         pgstat.returns = 0
 435         pgstat.carrier_frags = 0
 436
 437     for (key,value) in player_events.items():
 438         if key == 'n': pgstat.nick = value
 439         if key == 't': pgstat.team = value
 440         if key == 'rank': pgstat.rank = value
 441         if key == 'alivetime':
 442             pgstat.alivetime = datetime.timedelta(seconds=int(round(float(value))))
 443         if key == 'scoreboard-drops': pgstat.drops = value
 444         if key == 'scoreboard-returns': pgstat.returns = value
 445         if key == 'scoreboard-fckills': pgstat.carrier_frags = value
 446         if key == 'scoreboard-pickups': pgstat.pickups = value
 447         if key == 'scoreboard-caps': pgstat.captures = value
 448         if key == 'scoreboard-score': pgstat.score = value
 449         if key == 'scoreboard-deaths': pgstat.deaths = value
 450         if key == 'scoreboard-kills': pgstat.kills = value
 451         if key == 'scoreboard-suicides': pgstat.suicides = value
 452
 453     # check to see if we had a name, and if
 454     # not use the name from the player id
 455     if pgstat.nick == None:
 456         pgstat.nick = player.nick
 457
 458     session.add(pgstat)
 459     session.flush()
 460
 461     return pgstat
 462
 463
 464 def create_player_weapon_stats(session=None, player=None,
 465         game=None, pgstat=None, player_events=None):
 466     """
 467     Creates accuracy records for each weapon used by a given player in a
 468     given game. Parameters:
 469
 470     session - SQLAlchemy session factory object
 471     player - Player record who owns the weapon stats
 472     game - Game record in which the stats were created
 473     pgstat - Corresponding PlayerGameStat record for these weapon stats
 474     player_events - dictionary containing the raw weapon values that need to be
 475         transformed
 476     """
 477     pwstats = []
 478
 479     for (key,value) in player_events.items():
 480         matched = re.search("acc-(.*?)-cnt-fired", key)
 481         if matched:
 482             weapon_cd = matched.group(1)
 483             pwstat = PlayerWeaponStat()
 484             pwstat.player_id = player.player_id
 485             pwstat.game_id = game.game_id
 486             pwstat.player_game_stat_id = pgstat.player_game_stat_id
 487             pwstat.weapon_cd = weapon_cd
 488
 489             if 'n' in player_events:
 490                 pwstat.nick = player_events['n']
 491             else:
 492                 pwstat.nick = player_events['P']
 493
 494             if 'acc-' + weapon_cd + '-cnt-fired' in player_events:
 495                 pwstat.fired = int(round(float(
 496                         player_events['acc-' + weapon_cd + '-cnt-fired'])))
 497             if 'acc-' + weapon_cd + '-fired' in player_events:
 498                 pwstat.max = int(round(float(
 499                         player_events['acc-' + weapon_cd + '-fired'])))
 500             if 'acc-' + weapon_cd + '-cnt-hit' in player_events:
 501                 pwstat.hit = int(round(float(
 502                         player_events['acc-' + weapon_cd + '-cnt-hit'])))
 503             if 'acc-' + weapon_cd + '-hit' in player_events:
 504                 pwstat.actual = int(round(float(
 505                         player_events['acc-' + weapon_cd + '-hit'])))
 506             if 'acc-' + weapon_cd + '-frags' in player_events:
 507                 pwstat.frags = int(round(float(
 508                         player_events['acc-' + weapon_cd + '-frags'])))
 509
 510             session.add(pwstat)
 511             pwstats.append(pwstat)
 512
 513     return pwstats
 514
 515
 516 def parse_body(request):
 517     """
 518     Parses the POST request body for a stats submission
 519     """
 520     # storage vars for the request body
 521     game_meta = {}
 522     player_events = {}
 523     current_team = None
 524     players = []
 525
 526     log.debug(request.body)
 527
 528     for line in request.body.split('\n'):
 529         try:
 530             (key, value) = line.strip().split(' ', 1)
 531
 532             if key in 'V' 'T' 'G' 'M' 'S' 'C' 'R' 'W':
 533                 game_meta[key] = value
 534
 535             if key == 'P':
 536                 # if we were working on a player record already, append
 537                 # it and work on a new one (only set team info)
 538                 if len(player_events) != 0:
 539                     players.append(player_events)
 540                     player_events = {}
 541
 542                 player_events[key] = value
 543
 544             if key == 'e':
 545                 (subkey, subvalue) = value.split(' ', 1)
 546                 player_events[subkey] = subvalue
 547             if key == 'n':
 548                 player_events[key] = value
 549             if key == 't':
 550                 player_events[key] = value
 551         except:
 552             # no key/value pair - move on to the next line
 553             pass
 554
 555     # add the last player we were working on
 556     if len(player_events) > 0:
 557         players.append(player_events)
 558
 559     return (game_meta, players)
 560
 561
 562 def create_player_stats(session=None, player=None, game=None,
 563         player_events=None):
 564     """
 565     Creates player game and weapon stats according to what type of player
 566     """
 567     if 'joins' in player_events and 'matches' in player_events\
 568             and 'scoreboardvalid' in player_events:
 569                 pgstat = create_player_game_stat(session=session,
 570                         player=player, game=game, player_events=player_events)
 571                 if not re.search('^bot#\d+$', player_events['P']):
 572                         create_player_weapon_stats(session=session,
 573                             player=player, game=game, pgstat=pgstat,
 574                             player_events=player_events)
 575
 576
 577 def stats_submit(request):
 578     """
 579     Entry handler for POST stats submissions.
 580     """
 581     try:
 582         session = DBSession()
 583
 584         (game_meta, players) = parse_body(request)
 585
 586         # verify required metadata is present
 587         if 'T' not in game_meta or\
 588             'G' not in game_meta or\
 589             'M' not in game_meta or\
 590             'S' not in game_meta:
 591             log.debug("Required game meta fields (T, G, M, or S) missing. "\
 592                     "Can't continue.")
 593             raise Exception("Required game meta fields (T, G, M, or S) missing.")
 594
 595         has_real_players = False
 596         for player_events in players:
 597             if not player_events['P'].startswith('bot'):
 598                 if 'joins' in player_events and 'matches' in player_events\
 599                     and 'scoreboardvalid' in player_events:
 600                     has_real_players = True
 601
 602         if not has_real_players:
 603             raise Exception("No real players found. Stats ignored.")
 604
 605         server = get_or_create_server(session=session, name=game_meta['S'])
 606         gmap = get_or_create_map(session=session, name=game_meta['M'])
 607
 608         if 'W' in game_meta:
 609             winner = game_meta['W']
 610         else:
 611             winner = None
 612
 613         game = create_game(session=session,
 614                 start_dt=datetime.datetime(
 615                     *time.gmtime(float(game_meta['T']))[:6]),
 616                 server_id=server.server_id, game_type_cd=game_meta['G'],
 617                 map_id=gmap.map_id, winner=winner)
 618
 619         # find or create a record for each player
 620         # and add stats for each if they were present at the end
 621         # of the game
 622         for player_events in players:
 623             if 'n' in player_events:
 624                 nick = player_events['n']
 625             else:
 626                 nick = None
 627
 628             player = get_or_create_player(session=session,
 629                     hashkey=player_events['P'], nick=nick)
 630             log.debug('Creating stats for %s' % player_events['P'])
 631             create_player_stats(session=session, player=player, game=game,
 632                     player_events=player_events)
 633
 634         session.commit()
 635         log.debug('Success! Stats recorded.')
 636         return Response('200 OK')
 637     except Exception as e:
 638         session.rollback()
 639         raise e