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."}