def add_move_using_auth(cursor, conn, game_id, auth_token, board_config, san): query = '''INSERT INTO moves (game_id, player_hash, game_state, san) VALUES ( {game_id}, (SELECT player_hash FROM users WHERE users.auth_token = '{auth_token}'), '{board_state}', '{san}' )'''.format( game_id = game_id, auth_token = auth_token, board_state = board_config, san = san ) execute(cursor, conn, query)
def update_session(cursor, conn, auth_token): query = '''SELECT num_sessions FROM users WHERE users.auth_token = '{}' '''.format( auth_token) cursor.execute(query) num_session = cursor.fetchall()[0]['num_sessions'] if not num_session: num_session = 1 else: num_session += 1 query = '''UPDATE users SET num_sessions = {} WHERE users.auth_token = '{}' '''.format( num_session, auth_token) execute(cursor, conn, query)
def create_moves_table(cursor, conn): create_table_games = '''CREATE TABLE moves ( id SERIAL PRIMARY KEY NOT NULL, created time DEFAULT CURRENT_TIMESTAMP, deleted time, game_id int NOT NULL, player_hash char(32), game_state varchar (100), san varchar (10), FOREIGN KEY (game_id) REFERENCES games (id), FOREIGN KEY (player_hash) REFERENCES users (player_hash) )''' execute(cursor, conn, create_table_games)
def create_games_table(cursor, conn): create_table_games = '''CREATE TABLE games ( id SERIAL PRIMARY KEY NOT NULL, created time DEFAULT CURRENT_TIMESTAMP, ended time, deleted time, player_one char(32) NOT NULL, player_two char(32) NOT NULL, status varchar (100), ruleset varchar (20), FOREIGN KEY (player_one) REFERENCES users (player_hash), FOREIGN KEY (player_two) REFERENCES users (player_hash) )''' execute(cursor, conn, create_table_games)
def create_users_table(cursor, conn): create_table_users = '''CREATE TABLE users ( id SERIAL PRIMARY KEY NOT NULL, username varchar (50) UNIQUE NOT NULL, password varchar (50) NOT NULL, created time DEFAULT CURRENT_TIMESTAMP, num_games int, num_sessions int, player_hash char(32) UNIQUE NOT NULL, won_as_white int, won_as_black int, lost_as_white int, lost_as_black int, draw_as_white int, draw_as_black int, auth_token varchar (36) UNIQUE )''' execute(cursor, conn, create_table_users)
def add_opp_move_using_auth(cursor, conn, player_no, game_id, auth_token, board_config, san): player_no_curr = 'player_one' player_no_opp = 'player_two' if player_no == 1: player_no_curr = 'player_two' player_no_opp = 'player_one' query = '''INSERT INTO moves (game_id, player_hash, game_state, san) VALUES ( {game_id}, (SELECT games.{player_opp} FROM games WHERE games.{player_curr} = (SELECT users.player_hash FROM users WHERE users.auth_token = '{auth_token}') AND games.id = {game_id}), '{board_state}', '{san}')'''.format( game_id = game_id, auth_token = auth_token, player_curr = player_no_curr, player_opp = player_no_opp, board_state = board_config, san = san ) execute(cursor, conn, query)
def update_auth_token(cursor, conn, username, auth_token): query = '''UPDATE users SET auth_token = '{}' WHERE users.username = '******' '''.format( auth_token, username) execute(cursor, conn, query)
def add_user(cursor, conn, username, password, player_hash): query = '''INSERT INTO users (username, password, player_hash) VALUES ('{}', '{}', '{}')'''.format( username, password, player_hash) execute(cursor, conn, query)
def end_game(cursor, conn, game_id): query = '''UPDATE games SET status = 'finished', ended = now() WHERE games.id = {game_id}'''.format( game_id=game_id) execute(cursor, conn, query)
def update_game_status(cursor, conn, game_id, status): query = '''UPDATE games SET status = '{}' WHERE games.id = {} '''.format( status, game_id) execute(cursor, conn, query)
def add_game(cursor, conn, player_one_auth, player_two_auth): query = '''INSERT INTO games (player_one, player_two, status) VALUES ( (SELECT player_hash FROM users WHERE users.auth_token = '{p1auth}'), (SELECT player_hash FROM users WHERE users.auth_token = '{p2auth}'), 'ongoing')'''.format(p1auth=player_one_auth, p2auth=player_two_auth) execute(cursor, conn, query)