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)