strSql = 'select id, takeoff_icao, takeoff_lat, takeoff_lon, landing_icao, landing_lat, landing_lon from logbook_entries where takeoff_icao is null or landing_icao is null;' cur = dbs.getConnection().cursor() cur.execute(strSql) numUpdatedRecords = 0 for row in cur: (id, takeoffIcao, takeoffLat, takeoffLon, landingIcao, landingLat, landingLon) = row if not takeoffIcao and takeoffLat and takeoffLon: takeoffIcao = afm.getNearest(takeoffLat, takeoffLon) if takeoffIcao: print('takeoffIcao:', takeoffIcao) strSql = f"UPDATE logbook_entries set takeoff_icao = '{takeoffIcao}' where id = {id}" dbt.addStatement(strSql) numUpdatedRecords += 1 if not landingIcao and landingLat and landingLon: landingIcao = afm.getNearest(landingLat, landingLon) if landingIcao: print('landingIcao:', landingIcao) strSql = f"UPDATE logbook_entries set landing_icao = '{landingIcao}' where id = {id}" dbt.addStatement(strSql) numUpdatedRecords += 1 print('numUpdatedRecords:', numUpdatedRecords) while len(dbt.toDoStatements) > 0: print('len DB toDoStatements:', len(dbt.toDoStatements)) sleep(1)
class RedisReaper(object): RUN_INTERVAL = 5 * 60 # [s] REDIS_STALE_INTERVAL_1 = 20 * 60 # [s] REDIS_STALE_INTERVAL_2 = 30 * 60 # [s] REDIS_TTL_LIMIT = REDIS_RECORD_EXPIRATION - REDIS_STALE_INTERVAL_1 GS_THRESHOLD = getGroundSpeedThreshold(1, 'L') def __init__(self): print( f"[INFO] RedisReaper(lite) scheduled to run every {self.RUN_INTERVAL}s." ) self.dbt = DbThread(dbConnectionInfo=dbConnectionInfo) self.dbt.start() self.redis = StrictRedis(**redisConfig) self.influx = InfluxDbThread(dbName=INFLUX_DB_NAME, host=INFLUX_DB_HOST) self.airfieldManager = AirfieldManager() def doWork(self): airborne = [] keys = self.redis.keys('*status') for key in keys: key = key.decode('ascii') ttl = self.redis.ttl(key) value = self.redis.get(key) if value: # entries could have been deleted in the mean while.. status = int(value.decode('ascii').split(';')[0]) if status == 1: # 1 = airborne addr = key.split( '-' )[0] # in fact addressTypeStr + addr (e.g. I123456, F123456, O123456, ..) airborne.append(addr) numLanded = 0 for addr in airborne: prefix = addr[:1] addr = addr[1:] addrType = REVERSE_ADDRESS_TYPE.get(prefix, None) addrPrefixLong = ADDRESS_TYPE_PREFIX.get(addrType, None) if not addrPrefixLong: continue rs = self.influx.client.query( f"SELECT * FROM pos WHERE addr='{addrPrefixLong}{addr}' ORDER BY time DESC LIMIT 1;" ) for res in rs: # print('res:', res) time = res[0]['time'] ts = int( datetime.strptime( time, '%Y-%m-%dT%H:%M:%SZ').timestamp()) # UTC ts agl = res[0]['agl'] if res[0]['agl'] else 0 alt = res[0]['alt'] if res[0]['alt'] else 0 gs = res[0]['gs'] lat = res[0]['lat'] lon = res[0]['lon'] landingSuspected = False if 0 < agl < 100 and gs < self.GS_THRESHOLD: landingSuspected = True else: lastBeaconAge = datetime.utcnow().timestamp() - ts if lastBeaconAge > self.REDIS_STALE_INTERVAL_2: landingSuspected = True if landingSuspected: icaoLocation = self.airfieldManager.getNearest(lat, lon) # if not icaoLocation: # no outlandings yet.. # continue # print(f"addr: {addr}; dt: {dt / 60:.0f}min ; agl: {agl:.0f}m near {icaoLocation}") # set status as Landed in redis (or delete?): self.redis.set( f"{prefix}{addr}-status", '0;0' ) # 0 = on-ground; ts=0 to indicate forced landing self.redis.expire(key, REDIS_RECORD_EXPIRATION) # look-up related takeoff data: logbookItem: LogbookItem = findMostRecentTakeoff( addr, addrType) # create a LANDING logbook_event -> a stored procedure then creates a logbook_entry: flightTime = ts - logbookItem.takeoff_ts if flightTime < 0: flightTime = 0 icaoLocationVal = f"'{icaoLocation}'" if icaoLocation else 'null' strSql = f"INSERT INTO logbook_events " \ f"(ts, address, address_type, aircraft_type, event, lat, lon, location_icao, flight_time) " \ f"VALUES " \ f"({ts}, '{addr}', {logbookItem.address_type}, '{logbookItem.aircraft_type}', " \ f"'L', {lat:.5f}, {lon:.5f}, {icaoLocationVal}, {flightTime});" # print('strSql:', strSql) self.dbt.addStatement(strSql) numLanded += 1 if numLanded > 0: print(f"[INFO] RedisReaper: cleared {numLanded} stale records") def stop(self): self.dbt.stop()