def isDuplicateSighting(targetData): dbConn, dbCursor = huntdb.getConnectionWithCursor() # Get time since last report query = """ SELECT abs(extract(epoch from datetime) - extract(epoch from %s)) AS timedelta FROM hunts.sightings WHERE targetID = %s AND isDead = %s ORDER BY timedelta ASC LIMIT 1;""" queryInputs = (targetData["time"], targetData["targetID"], targetData["isDead"]) dbCursor.execute(query, queryInputs) result = dbCursor.fetchone() huntdb.putConnectionWithCursor(dbConn, dbCursor) if result is None: # Target has never been reported before, not a duplicate report return False # Consider it a duplicate if reported in the last 10 minutes nearestDelta = datetime.timedelta(seconds = result[0]) minimumDelta = datetime.timedelta(minutes = 10) if nearestDelta < minimumDelta: return True else: return False
def getHistories(inputData):
dbConn, dbCursor = huntdb.getConnectionWithCursor(dictCursor = True)
if "after" in inputData:
query = """
SELECT extract(epoch from datetime) as datetime, isDead, xCoord, yCoord
FROM hunts.sightings
WHERE targetID = %s AND %s < datetime
ORDER BY datetime DESC
LIMIT %s;"""
timePartition = inputData["after"]
else:
query = """
SELECT extract(epoch from datetime) as datetime, isDead, xCoord, yCoord
FROM hunts.sightings
WHERE targetID = %s AND datetime < %s
ORDER BY datetime DESC
LIMIT %s;"""
timePartition = inputData["before"]
targetHistories = {}
for tID in inputData["targetIDs"]:
queryInput = (tID, timePartition, inputData["maxRecords"])
dbCursor.execute(query, queryInput)
targetHistories[tID] = [dict(x) for x in dbCursor.fetchall()]
huntdb.putConnectionWithCursor(dbConn, dbCursor)
return targetHistories
def addSighting(targetData):
dbConn, dbCursor = huntdb.getConnectionWithCursor()
submitterIP = None
rowData = (targetData["time"], targetData["isDead"], targetData["targetID"], targetData["xCoord"], targetData["yCoord"], submitterIP)
dbCursor.execute("""INSERT INTO hunts.sightings VALUES (%s);""" % ", ".join(("%s",)*len(rowData)), rowData)
dbConn.commit()
huntdb.putConnectionWithCursor(dbConn, dbCursor)
def getZones(): dbConn, dbCursor = huntdb.getConnectionWithCursor(dictCursor = True) query = """SELECT zoneID, zoneName FROM hunts.zones ORDER BY zoneID ASC;""" dbCursor.execute(query) zones = [dict(x) for x in dbCursor.fetchall()] huntdb.putConnectionWithCursor(dbConn, dbCursor) return zones
def getInputs(rawInput):
if type(rawInput) is not dict:
raise ValueError("Expected dictionary, got %s" % type(rawInput))
# TODO: Implement proper authentication, sessions and/or API keys
password = rawInput.get("password")
if password != "LI1lfnrw":
raise ValueError("Invalid password.")
if "time" not in rawInput:
rawInput["time"] = time.time()
# Make sure we have all the data we need
# TODO: Find a more graceful way of including None and/or multiple types
requiredKeysAndTypes = [("targetID", [int,]), ("xCoord", [int, type(None)]), ("yCoord", [int, type(None)]), ("isDead", [bool,]), ("time", [int, float])]
for key, valueTypes in requiredKeysAndTypes:
if key not in rawInput:
raise ValueError("Missing mandatory input '%s'." % key)
elif type(rawInput[key]) not in valueTypes:
raise ValueError("'%s' must be %s, got %s" % (valueTypes, type(rawInput[key])))
targetStatus = {}
targetStatus["targetID"] = rawInput.get("targetID")
targetStatus["xCoord"] = rawInput.get("xCoord")
targetStatus["yCoord"] = rawInput.get("yCoord")
targetStatus["isDead"] = rawInput.get("isDead")
targetStatus["time"] = datetime.datetime.utcfromtimestamp(rawInput.get("time"))
# Verify map coordinates are within valid constraints
minCoord = 0
maxCoord = 41
xCoord = targetStatus.get("xCoord")
yCoord = targetStatus.get("yCoord")
if xCoord is not None and (xCoord < minCoord or xCoord > maxCoord):
raise ValueError("'xCoord' is out of range [%s, %s], got %s." % (minCoord, maxCoord, xCoord))
if yCoord is not None and (yCoord < minCoord or yCoord > maxCoord):
raise ValueError("'yCoord' is out of range [%s, %s], got %s." % (minCoord, maxCoord, yCoord))
# Make sure target exists
dbConn, dbCursor = huntdb.getConnectionWithCursor()
dbCursor.execute("""SELECT count(1) FROM hunts.sightings WHERE hunts.sightings.targetID = %s;""", (targetStatus.get("targetID"),))
targetExists = dbCursor.fetchone()
huntdb.putConnectionWithCursor(dbConn, dbCursor)
if not targetExists:
raise ValueError("Invalid targetID.")
return targetStatus
def getTargets(): dbConn, dbCursor = huntdb.getConnectionWithCursor(dictCursor = True) query = """ SELECT t.targetID, t.targetName, r.rankName, z.zoneName, extract(epoch from t.minSpawnTime) as minSpawnTime FROM hunts.targets AS t JOIN hunts.ranks AS r ON r.rankID = t.rankID JOIN hunts.zones AS z ON z.zoneID = t.zoneID ORDER BY t.targetID ASC;""" dbCursor.execute(query) targets = [dict(x) for x in dbCursor.fetchall()] huntdb.putConnectionWithCursor(dbConn, dbCursor) return targets
def getStatus(targetIDs):
dbConn, dbCursor = huntdb.getConnectionWithCursor(dictCursor = True)
query = """
SELECT DISTINCT ON (targetID) extract(epoch from datetime) AS lastSeen, isDead
FROM hunts.sightings
WHERE targetID = %s
ORDER BY targetID, datetime DESC;"""
statuses = {}
for tID in targetIDs:
dbCursor.execute(query, (tID, ))
state = dbCursor.fetchone()
if state is not None:
statuses[tID] = dict(state)
else:
statuses[tID] = None
huntdb.putConnectionWithCursor(dbConn, dbCursor)
return statuses
