def update_devices(session, logger=None): """Update devices with stats.""" if logger is None: logger = app.logger device_stats = (session.query( distinct(DeviceStats.device_id).label("device_id"), func.first_value(DeviceStats.name).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.name == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("name"), func.first_value(DeviceStats.firstseen).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.firstseen == null(), None)], else_=DeviceStats.date).asc().nullslast()).label("firstseen"), func.max(DeviceStats.lastseen).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.lastseen == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("lastseen"), func.first_value(DeviceStats.aircraft_type).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.aircraft_type == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "aircraft_type"), func.first_value(DeviceStats.stealth).over( partition_by=DeviceStats.device_id, order_by=case( [(DeviceStats.stealth == null(), None)], else_=DeviceStats.date).desc().nullslast()).label("stealth"), func.first_value(DeviceStats.software_version).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.software_version == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "software_version"), func.first_value(DeviceStats.hardware_version).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.hardware_version == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "hardware_version"), func.first_value(DeviceStats.real_address).over( partition_by=DeviceStats.device_id, order_by=case([(DeviceStats.real_address == null(), None)], else_=DeviceStats.date).desc().nullslast()).label( "real_address"), ).order_by(DeviceStats.device_id).subquery()) upd = (update(Device).where( and_(Device.id == device_stats.c.device_id)).values({ "name": device_stats.c.name, "firstseen": device_stats.c.firstseen, "lastseen": device_stats.c.lastseen, "aircraft_type": device_stats.c.aircraft_type, "stealth": device_stats.c.stealth, "software_version": device_stats.c.software_version, "hardware_version": device_stats.c.hardware_version, "real_address": device_stats.c.real_address, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} Devices".format(update_counter)) return "Updated {} Devices".format(update_counter)
def create_device_stats(session, date, logger=None): """Add/update device stats.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(DeviceStats).filter( DeviceStats.date == date).delete() # Since "distinct count" does not work in window functions we need a work-around for receiver counting sq = (session.query( AircraftBeacon, func.dense_rank().over( partition_by=AircraftBeacon.device_id, order_by=AircraftBeacon.receiver_id).label("dr")).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.device_id != null())).filter( or_(AircraftBeacon.error_count == 0, AircraftBeacon.error_count == null())).subquery()) # Calculate stats, firstseen, lastseen and last values != NULL device_stats = session.query( distinct(sq.c.device_id).label("device_id"), literal(date).label("date"), func.max( sq.c.dr).over(partition_by=sq.c.device_id).label("receiver_count"), func.max(sq.c.altitude).over( partition_by=sq.c.device_id).label("max_altitude"), func.count(sq.c.device_id).over( partition_by=sq.c.device_id).label("aircraft_beacon_count"), func.first_value(sq.c.name).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.name == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("name"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("firstseen"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("lastseen"), func.first_value(sq.c.aircraft_type).over( partition_by=sq.c.device_id, order_by=case([(sq.c.aircraft_type == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "aircraft_type"), func.first_value(sq.c.stealth).over( partition_by=sq.c.device_id, order_by=case( [(sq.c.stealth == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("stealth"), func.first_value(sq.c.software_version).over( partition_by=sq.c.device_id, order_by=case([(sq.c.software_version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "software_version"), func.first_value(sq.c.hardware_version).over( partition_by=sq.c.device_id, order_by=case([(sq.c.hardware_version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "hardware_version"), func.first_value(sq.c.real_address).over( partition_by=sq.c.device_id, order_by=case([(sq.c.real_address == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "real_address"), ).subquery() # And insert them ins = insert(DeviceStats).from_select( [ DeviceStats.device_id, DeviceStats.date, DeviceStats.receiver_count, DeviceStats.max_altitude, DeviceStats.aircraft_beacon_count, DeviceStats.name, DeviceStats.firstseen, DeviceStats.lastseen, DeviceStats.aircraft_type, DeviceStats.stealth, DeviceStats.software_version, DeviceStats.hardware_version, DeviceStats.real_address, ], device_stats, ) res = session.execute(ins) insert_counter = res.rowcount session.commit() logger.debug("DeviceStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)) return "DeviceStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)
def update_receivers(session, logger=None): """Update receivers with stats.""" if logger is None: logger = app.logger receiver_stats = (session.query( distinct(ReceiverStats.receiver_id).label("receiver_id"), func.first_value(ReceiverStats.firstseen).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.firstseen == null(), None)], else_=ReceiverStats.date).asc().nullslast()).label( "firstseen"), func.first_value(ReceiverStats.lastseen).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.lastseen == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "lastseen"), func.first_value(ReceiverStats.location_wkt).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.location_wkt == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "location_wkt"), func.first_value(ReceiverStats.altitude).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.altitude == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "altitude"), func.first_value(ReceiverStats.version).over( partition_by=ReceiverStats.receiver_id, order_by=case( [(ReceiverStats.version == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label("version"), func.first_value(ReceiverStats.platform).over( partition_by=ReceiverStats.receiver_id, order_by=case([(ReceiverStats.platform == null(), None)], else_=ReceiverStats.date).desc().nullslast()).label( "platform"), ).order_by(ReceiverStats.receiver_id).subquery()) upd = (update(Receiver).where( and_(Receiver.id == receiver_stats.c.receiver_id)).values({ "firstseen": receiver_stats.c.firstseen, "lastseen": receiver_stats.c.lastseen, "location": receiver_stats.c.location_wkt, "altitude": receiver_stats.c.altitude, "version": receiver_stats.c.version, "platform": receiver_stats.c.platform, })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} Receivers".format(update_counter)) return "Updated {} Receivers".format(update_counter)
def create_receiver_stats(session, date, logger=None): """Add/update receiver stats.""" if logger is None: logger = app.logger (start, end) = date_to_timestamps(date) # First kill the stats for the selected date deleted_counter = session.query(ReceiverStats).filter( ReceiverStats.date == date).delete() # Select one day sq = session.query(ReceiverBeacon).filter( between(ReceiverBeacon.timestamp, start, end)).subquery() # Calculate stats, firstseen, lastseen and last values != NULL receiver_stats = session.query( distinct(sq.c.receiver_id).label("receiver_id"), literal(date).label("date"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).asc().nullslast()).label("firstseen"), func.first_value(sq.c.timestamp).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.timestamp == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("lastseen"), func.first_value(sq.c.location).over( partition_by=sq.c.receiver_id, order_by=case([(sq.c.location == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label( "location_wkt"), func.first_value(sq.c.altitude).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.altitude == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("altitude"), func.first_value(sq.c.version).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.version == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("version"), func.first_value(sq.c.platform).over( partition_by=sq.c.receiver_id, order_by=case( [(sq.c.platform == null(), None)], else_=sq.c.timestamp).desc().nullslast()).label("platform"), ).subquery() # And insert them ins = insert(ReceiverStats).from_select( [ ReceiverStats.receiver_id, ReceiverStats.date, ReceiverStats.firstseen, ReceiverStats.lastseen, ReceiverStats.location_wkt, ReceiverStats.altitude, ReceiverStats.version, ReceiverStats.platform, ], receiver_stats, ) res = session.execute(ins) insert_counter = res.rowcount session.commit() logger.warn("ReceiverStats for {}: {} deleted, {} inserted".format( date, deleted_counter, insert_counter)) # Update aircraft_beacon_count, aircraft_count and max_distance aircraft_beacon_stats = (session.query( AircraftBeacon.receiver_id, func.count(AircraftBeacon.timestamp).label("aircraft_beacon_count"), func.count(func.distinct( AircraftBeacon.device_id)).label("aircraft_count"), func.max(AircraftBeacon.distance).label("max_distance"), ).filter( and_(between(AircraftBeacon.timestamp, start, end), AircraftBeacon.error_count == 0, AircraftBeacon.quality <= MAX_PLAUSIBLE_QUALITY, AircraftBeacon.relay == null())).group_by( AircraftBeacon.receiver_id).subquery()) upd = (update(ReceiverStats).where( and_(ReceiverStats.date == date, ReceiverStats.receiver_id == aircraft_beacon_stats.c.receiver_id)).values({ "aircraft_beacon_count": aircraft_beacon_stats.c.aircraft_beacon_count, "aircraft_count": aircraft_beacon_stats.c.aircraft_count, "max_distance": aircraft_beacon_stats.c.max_distance })) result = session.execute(upd) update_counter = result.rowcount session.commit() logger.warn("Updated {} ReceiverStats".format(update_counter)) return "ReceiverStats for {}: {} deleted, {} inserted, {} updated".format( date, deleted_counter, insert_counter, update_counter)