def InserAccount(user, password, title, salary):
with orm.session_scope() as session:
account = orm.Account(user_name=user,
password=password,
title=title,
salary=salary)
session.add(account)
def generate_stats_json_files():
with orm.session_scope() as session:
statsgen = cron.StatsGen(session, 'web/stats')
user_id_to_name = statsgen.get_user_id_to_name()
statsgen.output_users(user_id_to_name)
for user_id in user_id_to_name.keys():
statsgen.output_user(user_id)
def compare1():
with orm.session_scope() as session:
query = sqlalchemy.sql.text('''
select game.game_id,
game.end_time,
game_mode.name as game_mode,
user.name as username,
game_player.score
from game
join game_mode on game.game_mode_id = game_mode.game_mode_id
join game_player on game.game_id = game_player.game_id
join user on game_player.user_id = user.user_id
where game.imported = 1
order by game.game_id, game_player.player_index
''')
game_id_to_data = collections.defaultdict(lambda: {'scores': []})
for row in session.execute(query):
data = game_id_to_data[row.game_id]
data['end_time'] = row.end_time
data['game_mode'] = row.game_mode.decode()
data['scores'].append((row.username.decode(), row.score))
for game_id in sorted(game_id_to_data.keys()):
data = game_id_to_data[game_id]
print_game_import_row(data['end_time'], data['game_mode'],
data['scores'])
def main():
subprocess.call([
"mysql",
"-u",
"root",
"-proot",
"-e",
"drop schema if exists `acquire`; create schema `acquire` default character set utf8mb4 collate utf8mb4_bin;",
])
orm.Base.metadata.create_all(orm.engine)
with orm.session_scope() as session:
session.add(orm.GameMode(name="Singles"))
session.add(orm.GameMode(name="Teams"))
session.add(orm.GameState(name="Starting"))
session.add(orm.GameState(name="StartingFull"))
session.add(orm.GameState(name="InProgress"))
session.add(orm.GameState(name="Completed"))
session.add(orm.RatingType(name="Singles2"))
session.add(orm.RatingType(name="Singles3"))
session.add(orm.RatingType(name="Singles4"))
session.add(orm.RatingType(name="Teams"))
def compare1():
with orm.session_scope() as session:
query = sqlalchemy.sql.text('''
select game.game_id,
game.end_time,
game_mode.name as game_mode,
user.name as username,
game_player.score
from game
join game_mode on game.game_mode_id = game_mode.game_mode_id
join game_player on game.game_id = game_player.game_id
join user on game_player.user_id = user.user_id
where game.imported = 1
order by game.game_id, game_player.player_index
''')
game_id_to_data = collections.defaultdict(lambda: {'scores': []})
for row in session.execute(query):
data = game_id_to_data[row.game_id]
data['end_time'] = row.end_time
data['game_mode'] = row.game_mode.decode()
data['scores'].append((row.username.decode(), row.score))
for game_id in sorted(game_id_to_data.keys()):
data = game_id_to_data[game_id]
print_game_import_row(data['end_time'], data['game_mode'], data['scores'])
def generate_stats_json_files():
with orm.session_scope() as session:
statsgen = cron.StatsGen(session, '/opt/data/tim/stats')
user_id_to_name = statsgen.get_user_id_to_name()
statsgen.output_users(user_id_to_name)
for user_id in user_id_to_name.keys():
statsgen.output_user(user_id)
def recreate_some_games(server_):
input_dir = '/opt/data/tim/'
regex = re.compile(r'^(\d+)_0*(\d+)_0*(\d+).bin$')
in_progress_game_files = {}
for filename in os.listdir(input_dir):
match = regex.match(filename)
if match:
log_timestamp, internal_game_id, num_tiles_played = int(match.group(1)), int(match.group(2)), int(match.group(3))
in_progress_game_files[(log_timestamp, internal_game_id)] = (num_tiles_played, filename)
sql = sqlalchemy.sql.text('''
select
log_time,
number
from game
where completed_by_admin = 1
''')
with orm.session_scope() as session:
for row in session.execute(sql):
key = (row.log_time, row.number)
if key in in_progress_game_files:
del in_progress_game_files[key]
num_tiles_played_and_filenames = sorted(in_progress_game_files.values(), key=lambda x: (-x[0], x[1]))
for num_tiles_played_and_filenames in num_tiles_played_and_filenames[:5]:
num_tiles_played, filename = num_tiles_played_and_filenames
filename = input_dir + filename
print(filename)
recreate_game(server_, filename)
def main():
user_id_to_name = None
while True:
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Logs2DB(session, lookup)
kv_last_log_timestamp = lookup.get_key_value('cron last log timestamp')
last_log_timestamp = 1408905413 if kv_last_log_timestamp.value is None else int(kv_last_log_timestamp.value)
kv_last_offset = lookup.get_key_value('cron last offset')
last_offset = 0 if kv_last_offset.value is None else int(kv_last_offset.value)
completed_game_users = set()
for log_timestamp, filename in util.get_log_file_filenames('py', begin=last_log_timestamp):
if log_timestamp != last_log_timestamp:
last_offset = 0
with util.open_possibly_gzipped_file(filename) as f:
if last_offset:
f.seek(last_offset)
last_offset, new_completed_game_users = logs2db.process_logs(f, log_timestamp)
completed_game_users.update(new_completed_game_users)
last_log_timestamp = log_timestamp
kv_last_log_timestamp.value = last_log_timestamp
kv_last_offset.value = last_offset
session.flush()
if completed_game_users:
statsgen = StatsGen(session, 'stats_temp')
if not user_id_to_name:
user_id_to_name = statsgen.get_user_id_to_name()
for user in completed_game_users:
user_id_to_name[user.user_id] = user.name
statsgen.output_users(user_id_to_name)
for user in completed_game_users:
statsgen.output_user(user.user_id)
filenames = glob.glob('stats_temp/*.json')
if filenames:
all_filenames = filenames + [x + '.gz' for x in filenames]
command = ['zopfli']
command.extend(filenames)
subprocess.call(command)
command = ['touch', '-r', 'stats_temp/users.json']
command.extend(all_filenames)
subprocess.call(command)
command = ['mv']
command.extend(all_filenames)
command.append('web/stats')
subprocess.call(command)
time.sleep(60)
def output_all_stats_files():
with orm.session_scope() as session:
statsgen = StatsGen(session, "/tmp/tim/acquire/stats")
statsgen.output_ratings()
users_with_completed_games = statsgen.get_users_with_completed_games()
for [user_id, username, records] in sorted(users_with_completed_games):
print(user_id, username, records)
statsgen.output_user(user_id, username, records)
def UpdateAccount(id, user_name, password, title, salary):
with orm.session_scope() as session:
account = session.query(
orm.Account).filter(orm.Account.id == id).first()
if not account: return
account.user_name = user_name
account.password = password
account.salary = salary
account.title = title
def import_into_database():
class Cron1Logs2DB(cron.Logs2DB):
def calculate_new_ratings(self, game, game_players):
return
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Cron1Logs2DB(session, lookup)
with open('game_import_data.txt', 'r') as f:
logs2db.process_logs(f, 'x')
def import_into_database():
class Cron1Logs2DB(cron.Logs2DB):
def calculate_new_ratings(self, game, game_players):
return
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Cron1Logs2DB(session, lookup)
with open('game_import_data.txt', 'r') as f:
logs2db.process_logs(f, 'x')
def main():
subprocess.call(['mysql', '-u', 'root', '-proot', '-e', 'drop schema if exists `acquire`; create schema `acquire` default character set utf8mb4 collate utf8mb4_bin;'])
orm.Base.metadata.create_all(orm.engine)
with orm.session_scope() as session:
session.add(orm.GameMode(name='Singles'))
session.add(orm.GameMode(name='Teams'))
session.add(orm.GameState(name='Starting'))
session.add(orm.GameState(name='StartingFull'))
session.add(orm.GameState(name='InProgress'))
session.add(orm.GameState(name='Completed'))
session.add(orm.RatingType(name='Singles2'))
session.add(orm.RatingType(name='Singles3'))
session.add(orm.RatingType(name='Singles4'))
session.add(orm.RatingType(name='Teams'))
def main():
subprocess.call([
'mysql', '-u', 'root', '-proot', '-e',
'drop schema if exists `acquire`; create schema `acquire` default character set utf8mb4 collate utf8mb4_bin;'
])
orm.Base.metadata.create_all(orm.engine)
with orm.session_scope() as session:
session.add(orm.GameMode(name='Singles'))
session.add(orm.GameMode(name='Teams'))
session.add(orm.GameState(name='Starting'))
session.add(orm.GameState(name='StartingFull'))
session.add(orm.GameState(name='InProgress'))
session.add(orm.GameState(name='Completed'))
session.add(orm.RatingType(name='Singles2'))
session.add(orm.RatingType(name='Singles3'))
session.add(orm.RatingType(name='Singles4'))
session.add(orm.RatingType(name='Teams'))
def calculate_ratings():
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = cron.Logs2DB(session, lookup)
query = sqlalchemy.sql.text('''
select game.log_time,
game.number,
count(distinct game_player.game_player_id) as num_players
from game
join game_state on game.game_state_id = game_state.game_state_id
join game_player on game.game_id = game_player.game_id
where game_state.name = 'Completed'
group by game.game_id
having num_players > 1
order by game.end_time asc
''')
for row in session.execute(query):
game = lookup.get_game(row.log_time, row.number)
game_players = []
for player_index in range(row.num_players):
game_players.append(lookup.get_game_player(game, player_index))
logs2db.calculate_new_ratings(game, game_players)
def calculate_ratings():
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = cron.Logs2DB(session, lookup)
query = sqlalchemy.sql.text('''
select game.log_time,
game.number,
count(distinct game_player.game_player_id) as num_players
from game
join game_state on game.game_state_id = game_state.game_state_id
join game_player on game.game_id = game_player.game_id
where game_state.name = 'Completed'
group by game.game_id
having num_players between 2 and 4
order by game.end_time asc
''')
for row in session.execute(query):
game = lookup.get_game(row.log_time, row.number)
game_players = []
for player_index in range(row.num_players):
game_players.append(lookup.get_game_player(game, player_index))
logs2db.calculate_new_ratings(game, game_players)
def main():
user_id_to_name = None
while True:
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Logs2DB(session, lookup)
kv_last_filename = lookup.get_key_value('cron last filename')
last_filename = 0 if kv_last_filename.value is None else int(
kv_last_filename.value)
kv_last_offset = lookup.get_key_value('cron last offset')
last_offset = 0 if kv_last_offset.value is None else int(
kv_last_offset.value)
filenames = []
for filename in os.listdir('logs_py'):
try:
filename = int(filename)
except:
continue
if filename >= last_filename:
filenames.append(filename)
filenames.sort()
filename = 0
offset = 0
completed_game_users = set()
for filename in filenames:
offset = last_offset if filename == last_filename else 0
with open('logs_py/' + str(filename), 'r') as f:
if offset:
f.seek(offset)
offset, new_completed_game_users = logs2db.process_logs(
f, filename)
completed_game_users.update(new_completed_game_users)
kv_last_filename.value = filename
kv_last_offset.value = offset
session.flush()
if completed_game_users:
statsgen = StatsGen(session, 'stats_temp')
if not user_id_to_name:
user_id_to_name = statsgen.get_user_id_to_name()
for user in completed_game_users:
user_id_to_name[user.user_id] = user.name
statsgen.output_users(user_id_to_name)
for user in completed_game_users:
statsgen.output_user(user.user_id)
filenames = glob.glob('stats_temp/*.json')
if filenames:
all_filenames = filenames + [x + '.gz' for x in filenames]
command = ['zopfli']
command.extend(filenames)
subprocess.call(command)
command = ['touch', '-r', 'stats_temp/users.json']
command.extend(all_filenames)
subprocess.call(command)
command = ['mv']
command.extend(all_filenames)
command.append('web/stats')
subprocess.call(command)
time.sleep(60)
def main():
user_id_to_name = None
while True:
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Logs2DB(session, lookup)
kv_last_log_timestamp = lookup.get_key_value(
'cron last log timestamp')
last_log_timestamp = 1408905413 if kv_last_log_timestamp.value is None else int(
kv_last_log_timestamp.value)
kv_last_offset = lookup.get_key_value('cron last offset')
last_offset = 0 if kv_last_offset.value is None else int(
kv_last_offset.value)
completed_game_users = set()
for log_timestamp, filename in util.get_log_file_filenames(
'py', begin=last_log_timestamp):
if log_timestamp != last_log_timestamp:
last_offset = 0
with util.open_possibly_gzipped_file(filename) as f:
if last_offset:
f.seek(last_offset)
last_offset, new_completed_game_users = logs2db.process_logs(
f, log_timestamp)
completed_game_users.update(new_completed_game_users)
last_log_timestamp = log_timestamp
kv_last_log_timestamp.value = last_log_timestamp
kv_last_offset.value = last_offset
session.flush()
if completed_game_users:
statsgen = StatsGen(session, 'stats_temp')
if not user_id_to_name:
user_id_to_name = statsgen.get_user_id_to_name()
for user in completed_game_users:
user_id_to_name[user.user_id] = user.name
statsgen.output_users(user_id_to_name)
for user in completed_game_users:
statsgen.output_user(user.user_id)
filenames = glob.glob('stats_temp/*.json')
if filenames:
all_filenames = filenames + [x + '.gz' for x in filenames]
command = ['zopfli']
command.extend(filenames)
subprocess.call(command)
command = ['touch', '-r', 'stats_temp/users.json']
command.extend(all_filenames)
subprocess.call(command)
command = ['mv']
command.extend(all_filenames)
command.append('web/stats')
subprocess.call(command)
time.sleep(60)
def process_logs(write_stats_files):
with orm.session_scope() as session:
lookup = orm.Lookup(session)
logs2db = Logs2DB(session, lookup)
kv_last_log_timestamp = lookup.get_key_value("cron last log timestamp")
last_log_timestamp = (
1408905413
if kv_last_log_timestamp.value is None
else int(kv_last_log_timestamp.value)
)
kv_last_offset = lookup.get_key_value("cron last offset")
last_offset = 0 if kv_last_offset.value is None else int(kv_last_offset.value)
completed_game_users = set()
for log_timestamp, filename in util.get_log_file_filenames(
"py", begin=last_log_timestamp
):
if log_timestamp != last_log_timestamp:
last_offset = 0
with util.open_possibly_gzipped_file(filename) as f:
if last_offset:
f.seek(last_offset)
last_offset, new_completed_game_users = logs2db.process_logs(
f, log_timestamp
)
completed_game_users.update(new_completed_game_users)
last_log_timestamp = log_timestamp
kv_last_log_timestamp.value = last_log_timestamp
kv_last_offset.value = last_offset
session.flush()
if write_stats_files and completed_game_users:
statsgen = StatsGen(session, "stats_temp")
statsgen.output_ratings()
for user in completed_game_users:
record = lookup.get_record(user)
if record:
decoded = ujson.decode(record.encoded)
else:
decoded = get_empty_records()
statsgen.output_user(user.user_id, user.name, decoded)
ratings_filenames = glob.glob("stats_temp/*.json")
users_filenames = glob.glob("stats_temp/users/*.json")
if ratings_filenames:
command = ["zopfli"]
command.extend(ratings_filenames)
command.extend(users_filenames)
subprocess.call(command)
ratings_filenames = ratings_filenames + [
x + ".gz" for x in ratings_filenames
]
users_filenames = users_filenames + [x + ".gz" for x in users_filenames]
command = ["touch", "-r", "stats_temp/ratings.json"]
command.extend(ratings_filenames)
command.extend(users_filenames)
subprocess.call(command)
command = ["mv"]
command.extend(ratings_filenames)
command.append("web/stats/data")
subprocess.call(command)
command = ["mv"]
command.extend(users_filenames)
command.append("web/stats/data/users")
subprocess.call(command)
def GetAccount(id=None, user_name=None):
with orm.session_scope() as session:
return session.query(orm.Account).filter(
or_(orm.Account.id == id,
orm.Account.user_name == user_name)).first()
def DeleteAccount(user_name):
with orm.session_scope() as session:
account = GetAccount(user_name=user_name)
if account:
session.delete(account)
