def leave(room_id, user_id):
room_id = get_room_id(room_id)
query = """
UPDATE pp.rooms
set players = array_remove(players, %s)
where id = %s
returning id
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id, room_id]
)
query = """
UPDATE pp.users
set room_id = null
where id = %s
returning id
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id]
)
return True
def post_rooms(user_id, name=None, passphrase=None, location=None):
exists = False
if name:
exists = execute_query(
datasource="db/pillpool",
query="select exists(select 1 from pp.rooms where name = %s and date_deleted is null and name is not null)",
db_params=[name]
)[0]
if exists:
return [{"error": True, "message": "A room with this name already exists, try another name", "parameter": None}]
query = """
INSERT into pp.rooms(
owner_user_id
, name
, passphrase
, location
)
VALUES(
%s
, %s
, %s
, %s
) returning id, name
"""
room = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id, name, passphrase, location]
)[0]
join(room["id"], user_id)
return {"name": room["name"], "id": room["id"]}
def post_games(room_id, user_id, balls_per_player):
room_id = get_room_id(room_id)
game_already_running_query = "select id from pp.games where room_id = %s and winner is null;"
game_already_running_result = execute_query(
datasource="db/pillpool",
query=game_already_running_query,
db_params=[room_id]
)
if game_already_running_result:
return {"status_code": 404, "error": True, "message": "This room already has a running game.", "game_id": game_already_running_result[0]["id"], "parameter": None}
query = """
INSERT into pp.games (
room_id
, players
) select
r.id
, r.players
from pp.rooms r
where r.id = %s
and %s = ANY(r.players)
and not exists(select 1 from pp.games where room_id = r.id and winner is null)
returning id, players
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id, user_id]
)
if result:
game_id = result[0]["id"]
players = result[0]["players"]
# Randomly assign the balls to players
players_by_balls = players * balls_per_player
balls = dict((el,{"player": None, "state": "live", "potted_by": None, "date_potted": None}) for el in range(1,16))
balls_remaining = list(balls.keys())
random.shuffle(balls_remaining)
for player in players_by_balls:
ball = balls_remaining.pop()
balls[ball]["player"] = player
query = """
UPDATE pp.games
set balls = %s
where id = %s
returning id
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[json.dumps(balls), game_id]
)
return get_games_id(room_id, game_id, user_id)
else:
return {"status_code": 404, "error": True, "message": "This room does not exist."}
def get_rooms_id(room_id, user_id, last_modified = None):
room_id = get_room_id(room_id)
query = """
SELECT
r.id
, r.owner_user_id
, u.first_name as owner_first_name
, u.last_name as owner_last_name
, r.name
, r.location
, u.email
, u.photo
, (select json_object_agg(id, json_build_object('username',username,'emoji',null)) from pp.users where id = ANY(r.players)) as players
, (select g.id from pp.games g where room_id = r.id and date_deleted is null and winner is null order by id desc limit 1) as game_id
from pp.rooms r
left join pp.users u
on u.id = r.owner_user_id
where r.id = %s
and %s = ANY(r.players)
and r.date_deleted is null
and (r.date_modified > %s) is not False;
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id, user_id, last_modified]
)
if result:
for i, player_id in enumerate(sorted(result[0]["players"])):
result[0]["players"][player_id]["emoji"] = EMOJIS[int(room_id)%len(EMOJIS)][i]
return result[0]
else:
return {}
def athena(request):
try:
limit = int(request.GET["limit"])
if limit > 5:
limit = 5
except:
limit = 5
query = """
SELECT
*
from acciona.acciona
limit %(limit)s;
"""
result = execute_query(datasource={
"type":
"athena",
"key":
"db/athena",
"region_name":
"us-east-1",
"database_name":
"acciona",
"s3_dir":
"scada-json-fs/acciona-backup-scada/companies/acciona"
},
query=query,
db_params={"limit": limit})
return {"result": result}
def get_games(room_id, user_id):
room_id = get_room_id(room_id)
query = """
SELECT
g.id
, g.winner
, u.username as winner_username
, (select username from pp.users where id = ANY(g.players))
, g.date_created
, g.date_modified
from pp.games g
inner join pp.rooms r
on r.id = g.room_id
left join pp.users u
on u.id = g.winner
where r.id = %s
and %s = ANY(r.players)
and g.date_deleted is null;
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id, user_id]
)
return result
def join(room_id, user_id):
# If player is in a different room, remove them:
room_id = get_room_id(room_id)
query = """
UPDATE pp.rooms r
set players = array_remove(players, u.room_id)
from (
select room_id from pp.users where id = %s
) u
where r.id = u.room_id
returning id;
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id]
)
# Add player to new room
query = """
UPDATE pp.rooms
set players = array_append(players, %s)
where id = %s
and (not (%s = ANY(players)) or players is null)
returning id
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id, room_id, user_id]
)
# Update player's room_id so we know where they are
query = """
UPDATE pp.users
set room_id = %s
where id = %s
returning id
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id, user_id]
)
return True
def get_room_id(room_id):
try:
room_id = int(room_id)
except:
room_id = str(room_id)
if type(room_id) == str:
query = """
select id from pp.rooms where name = %s
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id]
)
return result[0]["id"]
else:
return room_id
def scada_sample(request):
try:
limit = int(request.GET["limit"])
if limit > 5:
limit = 5
except:
limit = 5
query = """
SELECT
*
from dev.metrics
limit %s;
"""
result = execute_query(datasource="db/scada",
query=query,
db_params=[limit])
return {"result": result}
def get_users_id(user_id):
query = """
SELECT
u.id
, json_build_object(
'id', u.id,
'username', u.username,
'first', u.first_name,
'last', u.last_name,
'email', u.email,
'photo', u.photo,
'guest', u.guest
) as user
from pp.users u
where id = %s
"""
result = execute_query(datasource="db/pillpool",
query=query,
db_params=[user_id])
return result[0]
def get_rooms(user_id):
query = """
SELECT
r.id
, r.owner_user_id
, u.username as owner_username
, r.name
, r.location
from pp.rooms r
left join pp.users u
on u.id = r.owner_user_id
where (r.owner_user_id = %s or %s=ANY(players))
and r.date_deleted is null;
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[user_id, user_id]
)
return result
def validate_credentials(email, password):
"""
Validates email and password against database returning a token dictionary.
Parameters:
email (str): email to log in with
password (str): Password to log in with
Response:
token (dict): Dictionary token (JWT)
"""
try:
query = """
SELECT
u.id
, json_build_object(
'id', u.id,
'username', u.username,
'first', u.first_name,
'last', u.last_name,
'email', u.email,
'photo', u.photo,
'guest', u.guest
) as user
from pp.users u
where u.email = %s
and u.guest = false
and u.date_deleted is null
and u.temp is null
and encode(digest(%s||u.salt, 'sha256'), 'hex')=u.password;
"""
result = execute_query(datasource="db/pillpool",
query=query,
db_params=[email, password])
if result:
return result[0]
else:
raise HTTPForbidden("Invalid Username or Password.")
except:
raise HTTPForbidden("Invalid Username or Password.")
def get_rooms_id_live_game(room_id, user_id):
room_id = get_room_id(room_id)
query = """
select
g.id as game_id
from pp.games g
where room_id = %s
and date_deleted is null
and winner is null
order by id desc limit 1
and %s = ANY(players)
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[room_id, user_id]
)
if result:
return result[0]
else:
return {"game_id": None}
def get_games_id(room_id, game_id, user_id, last_modified=None):
room_id = get_room_id(room_id)
query = """
SELECT
g.id
, g.winner
, u.username as winner_username
, (select json_object_agg(id, json_build_object('emoji', null, 'username',username,'balls_remaining',0, 'balls', '[]'::jsonb)) from pp.users where id = ANY(g.players)) as players
, g.balls
, digest(g.balls::text::bytea, 'sha256')::text as ball_hash
, g.date_created
, g.date_modified
, coalesce((round(g.date_modified::numeric, 2) > round(%s::numeric, 2)),False) as send_response
from pp.games g
inner join pp.rooms r
on r.id = g.room_id
left join pp.users u
on u.id = g.winner
where r.id = %s
and g.id = %s
and %s = ANY(r.players)
and g.date_deleted is null;
"""
result = execute_query(
datasource="db/pillpool",
query=query,
db_params=[last_modified, room_id, game_id, user_id]
)
my_balls = []
remaining_players = []
if result:
if last_modified and not result[0]["send_response"]:
return {"status_code": 204}
else:
del result[0]["send_response"]
# TODO: Need to process the balls object, to obfuscate who owns each ball before returning it to the client side.
for ball_number, ball_info in result[0]["balls"].items():
ball_owner = ball_info["player"]
# See how many are left per player
if ball_info["state"] == "live" and ball_owner:
result[0]["players"][str(ball_owner)]["balls_remaining"] += 1
""" This makes the balls appear in the list for you, maybe want to disable this if it's confusing? """
if int(ball_owner) == user_id:
result[0]["players"][str(ball_owner)]["balls"].append(int(ball_number))
else:
result[0]["players"][str(ball_owner)]["balls"].append(None)
if ball_owner not in remaining_players:
remaining_players.append(ball_owner)
elif ball_owner and ball_info["state"] == "potted":
result[0]["players"][str(ball_owner)]["balls"].append(int(ball_number))
# Aggregate what balls are left for me
if ball_owner == user_id:
my_balls.append(int(ball_number))
# Remove information about who has each ball
del result[0]["balls"][ball_number]["player"]
result[0]["my_balls"] = my_balls
result[0]["remaining_players"] = remaining_players
for i, player_id in enumerate(sorted(result[0]["players"])):
result[0]["players"][player_id]["emoji"] = EMOJIS[int(room_id)%len(EMOJIS)][i]
return result[0]
else:
return {"status_code": 404, "error": True, "message": "This game does not exist."}
