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 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 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)