def game_key_in_db(c, g):
# check just two tables: player_recent_games and wins. This could result
# in some duplicates being added, but I'm not sure if the trouble here is
# worth it -- because the db doesn't store all games, it relies on (to some
# degree) reading games in order no matter what.
n = query_first(c, '''SELECT count(game_key) FROM player_recent_games
WHERE game_key = %s''', g['game_key'])
if n > 0:
return True
n = query_first(c, '''SELECT count(game_key) FROM wins
WHERE game_key = %s''', g['game_key'])
return n > 0
def init_most_recent_from_db(self, c):
import morgue.time
db_time = query_first(
c, '''SELECT MAX(start_time) FROM all_recent_games''')
if db_time is None:
self.most_recent_start = None
self.empty_db_start = True
else:
self.most_recent_start = morgue.time.morgue_timestring(db_time)
def fixup_month(c, month):
mwin = month['winners'].items()
month['players'] = query_first(c, '''SELECT COUNT(DISTINCT player)
FROM date_players
WHERE which_month = %s''',
month['month'].replace('-', ''))
def sort_winners(a, b):
if a[1] != b[1]:
return int(b[1] - a[1])
else:
return (a[1] < b[1] and -1) or (a[1] > b[1] and 1) or 0
mwin.sort(sort_winners)
month['winners'] = fixup_winners(mwin)
return month
def update_topN(c, g, n):
if topN_count(c) >= n:
if g['sc'] > lowest_highscore(c):
query_do(c,'''DELETE FROM top_games
WHERE id = %s''',
query_first(c, '''SELECT id FROM top_games
ORDER BY sc LIMIT 1'''))
insert_game(c, g, 'top_games')
lowest_highscore.flush()
dirty_pages('top-N', 'overview')
else:
insert_game(c, g, 'top_games')
dirty_pages('top-N', 'overview')
topN_count.flush()
def update_player_best_games(c, g):
player = g['name']
if player_best_game_count(c, player) >= MAX_PLAYER_BEST_GAMES:
if g['sc'] > player_lowest_highscore(c, player):
query_do(c, '''DELETE FROM player_best_games WHERE id = %s''',
query_first(c, '''SELECT id FROM player_best_games
WHERE name = %s
ORDER BY sc LIMIT 1''',
player))
insert_game(c, g, 'player_best_games')
player_lowest_highscore.flush_key(player)
else:
insert_game(c, g, 'player_best_games')
player_best_game_count.flush_key(player)
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 _player_create_streak_from_first(self, c, player, g):
query_do(c, '''INSERT INTO streaks
(player, start_game_time, end_game_time,
active, ngames)
VALUES (%s, %s, %s, %s, %s)''',
player, g['end_time'], g['end_time'],
True, 0)
sid = query_first(c, '''SELECT id FROM streaks
WHERE player = %s AND active = 1''',
player)
if self.db_streaks.has_key(player):
error("Player %s already has an ongoing streak!" % player)
self.db_streaks[player] = sid
# return the newly-created streak id
return sid
def update_player_best_games(c, g):
player = g['name']
if player_best_game_count(c, player) >= MAX_PLAYER_BEST_GAMES:
if g['sc'] > player_lowest_highscore(c, player):
query_do(
c, '''DELETE FROM player_best_games WHERE id = %s''',
query_first(
c, '''SELECT id FROM player_best_games
WHERE name = %s
ORDER BY sc LIMIT 1''', player))
insert_game(c, g, 'player_best_games')
player_lowest_highscore.flush_key(player)
else:
insert_game(c, g, 'player_best_games')
player_best_game_count.flush_key(player)
def update_topN(c, g, n):
if topN_count(c) >= n:
if g['sc'] > lowest_highscore(c):
query_do(
c, '''DELETE FROM top_games
WHERE id = %s''',
query_first(
c, '''SELECT id FROM top_games
ORDER BY sc LIMIT 1'''))
insert_game(c, g, 'top_games')
lowest_highscore.flush()
dirty_pages('top-N', 'overview')
else:
insert_game(c, g, 'top_games')
dirty_pages('top-N', 'overview')
topN_count.flush()
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 update_topN(c, g, n):
if topN_count(c) >= n:
if g['sc'] > lowest_highscore(c):
# note: for some reason this particular query is faster than just a simple
# DELETE FROM ... ORDER BY query.
query_do(c,'''DELETE FROM top_games
WHERE id = %s''',
query_first(c, '''SELECT id FROM top_games
ORDER BY sc LIMIT 1'''))
insert_game(c, g, 'top_games')
lowest_highscore.flush()
dirty_pages('top-N', 'overview')
else:
insert_game(c, g, 'top_games')
dirty_pages('top-N', 'overview')
topN_count.flush()
def fixup_month(c, month):
mwin = month['winners'].items()
month['players'] = query_first(
c, '''SELECT COUNT(DISTINCT player)
FROM date_players
WHERE which_month = %s''',
month['month'].replace('-', ''))
def sort_winners(a, b):
if a[1] != b[1]:
return int(b[1] - a[1])
else:
return (a[1] < b[1] and -1) or (a[1] > b[1] and 1) or 0
mwin.sort(sort_winners)
month['winners'] = fixup_winners(mwin)
return month
def _player_create_streak_from_last(self, c, player):
end_time = player_last_game_end_time(c, player)
query_do(c, '''INSERT INTO streaks
(player, start_game_time, end_game_time,
active, ngames)
VALUES (%s, %s, %s, %s, %s)''',
player, end_time, end_time,
True, 1)
# Record the game that started the streak:
query_do(c,
"INSERT INTO streak_games (" + scload.LOG_DB_SCOLUMNS + ") " +
"SELECT " + scload.LOG_DB_SCOLUMNS +
''' FROM player_last_games WHERE name = %s''', player)
sid = query_first(c, '''SELECT id FROM streaks
WHERE player = %s AND active = 1''',
player)
if self.db_streaks.has_key(player):
error("Player %s already has an ongoing streak!" % player)
# return the newly-created streak id
self.db_streaks[player] = sid
return sid
def player_recent_game_count(c, player):
return query_first(c, '''SELECT COUNT(*) FROM player_recent_games
WHERE name = %s''',
player)
def player_lowest_highscore(c, player):
return query_first(c, '''SELECT MIN(sc) FROM player_best_games
WHERE name = %s''',
player)
def ziggurat_entry_count(c):
return query_first(c, '''SELECT COUNT(*) FROM ziggurats''')
def lowest_highscore(c): return query_first(c, '''SELECT MIN(sc) FROM top_games''')
def player_last_game_end_time(c, player):
return query_first(
c, '''SELECT end_time FROM player_last_games
WHERE name = %s''', player)
def low_xl_rune_count(c): return query_first(c, '''SELECT COUNT(*) FROM low_xl_rune_finds''')
def player_active_streak_id(c, player):
return query_first(c, '''SELECT id FROM streaks
WHERE player = %s AND active = 1''',
player)
def lowest_highscore(c):
return query_first(c, '''SELECT MIN(sc) FROM top_games''')
def count_players_per_day(c, day):
return query_first(
c, '''SELECT COUNT(*) FROM date_players
WHERE which_day = %s''', day)
def player_lowest_highscore(c, player):
return query_first(
c, '''SELECT MIN(sc) FROM player_best_games
WHERE name = %s''', player)
def low_xl_rune_count(c):
return query_first(c, '''SELECT COUNT(*) FROM low_xl_rune_finds''')
def player_recent_game_count(c, player):
return query_first(
c, '''SELECT COUNT(*) FROM player_recent_games
WHERE name = %s''', player)
def player_active_streak_id(c, player):
return query_first(
c, '''SELECT id FROM streaks
WHERE player = %s AND active = 1''',
player)
def all_recent_game_count(c): return query_first(c, '''SELECT COUNT(*) FROM all_recent_games''')
def player_last_game_end_time(c, player):
return query_first(c, '''SELECT end_time FROM player_last_games
WHERE name = %s''',
player)
def count_players_per_day(c, day):
return query_first(c,
'''SELECT COUNT(*) FROM date_players
WHERE which_day = %s''',
day)
def topN_count(c):
return query_first(c, '''SELECT COUNT(*) FROM top_games''')
def topN_count(c): return query_first(c, '''SELECT COUNT(*) FROM top_games''')
def ziggurat_entry_count(c): return query_first(c, '''SELECT COUNT(*) FROM ziggurats''')
def all_recent_game_count(c):
return query_first(c, '''SELECT COUNT(*) FROM all_recent_games''')