def get_latest(cls, max_age=timedelta(hours=6)):
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(db.func.row_number(),
partition_by=cls.pilot_id,
order_by=cls.time.desc())
# Create inner query
subq = db.session \
.query(cls.id, row_number.label('row_number')) \
.join(cls.pilot) \
.filter(cls.max_age_filter(max_age)) \
.filter(cls.time_visible <= datetime.utcnow()) \
.filter(cls.location_wkt != None) \
.subquery()
# Create outer query that orders by time and
# only selects the latest fix
query = cls.query() \
.options(db.joinedload(cls.pilot)) \
.filter(cls.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(cls.time.desc())
return query
def get_latest(cls, max_age=timedelta(hours=6)):
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(db.func.row_number(),
partition_by=cls.pilot_id,
order_by=cls.time.desc())
# Create inner query
subq = db.session \
.query(cls.id, row_number.label('row_number')) \
.join(cls.pilot) \
.filter(cls.max_age_filter(max_age)) \
.filter(cls.time_visible <= datetime.utcnow()) \
.filter(cls.location_wkt != None) \
.subquery()
# Create outer query that orders by time and
# only selects the latest fix
query = cls.query() \
.options(db.joinedload(cls.pilot)) \
.filter(cls.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(cls.time.desc())
return query
def traffic_request_received(self, host, port, key, payload):
if len(payload) != 8: return
pilot = User.by_tracking_key(key)
if pilot is None:
log("%s TRAFFIC_REQUEST unknown pilot (key: %x)" % (host, key))
return
data = struct.unpack('!II', payload)
or_filters = []
flags = data[0]
if flags & TRAFFIC_FLAG_FOLLOWEES:
subq = db.session \
.query(Follower.destination_id) \
.filter(Follower.source_id == pilot.id) \
.subquery()
or_filters.append(TrackingFix.pilot_id.in_(subq))
if flags & TRAFFIC_FLAG_CLUB:
subq = db.session \
.query(User.id) \
.filter(User.club_id == pilot.club_id) \
.subquery()
or_filters.append(TrackingFix.pilot_id.in_(subq))
if len(or_filters) == 0:
return
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(db.func.row_number(),
partition_by=TrackingFix.pilot_id,
order_by=TrackingFix.time.desc())
# Create inner query
subq = db.session \
.query(TrackingFix.id, row_number.label('row_number')) \
.join(TrackingFix.pilot) \
.filter(TrackingFix.pilot_id != pilot.id) \
.filter(TrackingFix.max_age_filter(2)) \
.filter(TrackingFix.delay_filter(User.tracking_delay_interval())) \
.filter(TrackingFix.location_wkt != None) \
.filter(TrackingFix.altitude != None) \
.filter(or_(*or_filters)) \
.subquery()
# Create outer query that orders by time and
# only selects the latest fix
query = TrackingFix.query() \
.filter(TrackingFix.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(TrackingFix.time.desc()) \
.limit(32)
response = ''
count = 0
for fix in query:
location = fix.location
if location is None: continue
t = fix.time
t = t.hour * 3600000 + t.minute * 60000 + t.second * 1000 + t.microsecond / 1000
response += struct.pack('!IIiihHI', fix.pilot_id, t,
int(location.latitude * 1000000),
int(location.longitude * 1000000),
int(fix.altitude), 0, 0)
count += 1
response = struct.pack('!HBBI', 0, 0, count, 0) + response
response = struct.pack('!IHHQ', MAGIC, 0, TYPE_TRAFFIC_RESPONSE,
0) + response
response = set_crc(response)
self.socket.sendto(response, (host, port))
log("%s TRAFFIC_REQUEST %s -> %d locations" %
(host, unicode(pilot).encode('utf8', 'ignore'), count))
def traffic_request_received(self, host, port, key, payload):
if len(payload) != 8:
return
pilot = User.by_tracking_key(key)
if pilot is None:
log("%s TRAFFIC_REQUEST unknown pilot (key: %x)" % (host, key))
return
data = struct.unpack("!II", payload)
or_filters = []
flags = data[0]
if flags & TRAFFIC_FLAG_FOLLOWEES:
subq = (
db.session.query(Follower.destination_id)
.filter(Follower.source_id == pilot.id)
.subquery()
)
or_filters.append(TrackingFix.pilot_id.in_(subq))
if flags & TRAFFIC_FLAG_CLUB:
subq = (
db.session.query(User.id)
.filter(User.club_id == pilot.club_id)
.subquery()
)
or_filters.append(TrackingFix.pilot_id.in_(subq))
if len(or_filters) == 0:
return
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(
db.func.row_number(),
partition_by=TrackingFix.pilot_id,
order_by=TrackingFix.time.desc(),
)
# Create inner query
subq = (
db.session.query(TrackingFix.id, row_number.label("row_number"))
.join(TrackingFix.pilot)
.filter(TrackingFix.pilot_id != pilot.id)
.filter(TrackingFix.max_age_filter(2))
.filter(TrackingFix.time_visible <= datetime.utcnow())
.filter(TrackingFix.location_wkt != None)
.filter(TrackingFix.altitude != None)
.filter(or_(*or_filters))
.subquery()
)
# Create outer query that orders by time and
# only selects the latest fix
query = (
TrackingFix.query()
.filter(TrackingFix.id == subq.c.id)
.filter(subq.c.row_number == 1)
.order_by(TrackingFix.time.desc())
.limit(32)
)
response = ""
count = 0
for fix in query:
location = fix.location
if location is None:
continue
t = fix.time
t = (
t.hour * 3600000
+ t.minute * 60000
+ t.second * 1000
+ t.microsecond / 1000
)
response += struct.pack(
"!IIiihHI",
fix.pilot_id,
t,
int(location.latitude * 1000000),
int(location.longitude * 1000000),
int(fix.altitude),
0,
0,
)
count += 1
response = struct.pack("!HBBI", 0, 0, count, 0) + response
response = struct.pack("!IHHQ", MAGIC, 0, TYPE_TRAFFIC_RESPONSE, 0) + response
response = set_crc(response)
self.socket.sendto(response, (host, port))
log(
"%s TRAFFIC_REQUEST %s -> %d locations"
% (host, pilot.name.encode("utf8", "ignore"), count)
)