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