def guess_model(self): from skylines.model import Flight, AircraftModel # first try to find the reg number in the database if self.registration is not None: glider_reg = self.registration result = DBSession.query(Flight) \ .filter(func.upper(Flight.registration) == func.upper(glider_reg)) \ .order_by(desc(Flight.id)) \ .first() if result and result.model_id: return result.model_id # try to find another flight with the same logger and use it's aircraft type if (self.logger_id is not None and self.logger_manufacturer_id is not None): logger_id = self.logger_id logger_manufacturer_id = self.logger_manufacturer_id result = DBSession.query(Flight).outerjoin(IGCFile) \ .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \ .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \ .filter(Flight.model_id == None) \ .order_by(desc(Flight.id)) if self.logger_manufacturer_id.startswith('X'): result = result.filter(Flight.pilot == self.owner) result = result.first() if result and result.model_id: return result.model_id if self.model is not None: glider_type = self.model.lower() # otherwise, try to guess the glider model by the glider type igc header text_fragments = ['%{}%'.format(v) for v in re.sub(r'[^a-z]', ' ', glider_type).split()] digit_fragments = ['%{}%'.format(v) for v in re.sub(r'[^0-9]', ' ', glider_type).split()] if not text_fragments and not digit_fragments: return None glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type) result = DBSession \ .query(AircraftModel) \ .filter(and_( func.regexp_replace(func.lower(AircraftModel.name), '[^a-z]', ' ').like(func.any(text_fragments)), func.regexp_replace(func.lower(AircraftModel.name), '[^0-9]', ' ').like(func.all(digit_fragments)))) \ .order_by(func.levenshtein(func.regexp_replace(func.lower(AircraftModel.name), '[^a-z0-9]', ''), glider_type_clean)) if result.first(): return result.first().id # nothing found return None
def create_follower_notification(followed, follower): ''' Create notification for the followed pilot about his new follower ''' item = Notification(type=Notification.NT_FOLLOWER, sender=follower, recipient=followed) DBSession.add(item)
def get_info(cls, location): '''Returns a query object of mountain waves around the location''' return DBSession.query(cls) \ .filter(func.ST_DWithin( cast(WKTElement(location.to_wkt(), srid=4326), Geography), cast(cls.location, Geography), 5000))
def get_clustered_locations(location_column, threshold_radius=1000, filter=None): ''' SELECT ST_Centroid( (ST_Dump( ST_Union( ST_Buffer( takeoff_location_wkt::geography, 1000 )::geometry ) ) ).geom) FROM flights WHERE pilot_id=31; ''' # Cast the takeoff_location_wkt column to Geography geography = cast(location_column, Geography) # Add a metric buffer zone around the locations buffer = cast(geography.ST_Buffer(threshold_radius), Geometry) # Join the locations into one MultiPolygon union = buffer.ST_Union() # Split the MultiPolygon into separate polygons dump = union.ST_Dump().geom # Calculate center points of each polygon locations = func.ST_Centroid(dump) query = DBSession.query(locations.label('location')) if filter is not None: query = query.filter(filter) return [Location.from_wkb(row.location) for row in query]
def get_optimised_contest_trace(self, contest_type, trace_type): from skylines.model.trace import Trace query = DBSession.query(Trace) \ .filter(Trace.contest_type == contest_type) \ .filter(Trace.trace_type == trace_type) \ .filter(Trace.flight == self).first() return query
def mark_all_read(cls, user, filter_func=None): query = DBSession.query(cls) \ .filter(cls.recipient == user) if filter_func is not None: query = filter_func(query) query.update(dict(time_read=datetime.utcnow()))
def by_location(cls, location): location = func.ST_MakePoint(location.longitude, location.latitude) filter = functions.gcontains(cls.the_geom, location) zone = DBSession.query(cls.tzid).filter(filter).scalar() if zone is None: return None return timezone(unicode(zone))
def by_location(cls, location, distance_threshold = 0.025): airport = DBSession.query(cls, functions.distance(cls.location_wkt, location.to_wkt()).label('distance'))\ .order_by(functions.distance(cls.location_wkt, location.to_wkt())).first() if airport is not None and (distance_threshold is None or airport.distance < distance_threshold): return airport.Airport else: return None
def create_flight_notifications(flight): ''' Create notifications for the followers of the owner and pilots of the flight ''' # Create list of flight-related users senders = [flight.pilot_id, flight.co_pilot_id, flight.igc_file.owner_id] senders = OrderedDict([(s, None) for s in senders if s is not None]) # Request followers/recipients of the flight-related users from the DB followers = DBSession.query(Follower.source_id, Follower.destination_id) \ .filter(Follower.destination_id.in_(senders.keys())) \ .all() # Determine the recipients and their most important sender recipients = dict() # For each flight-related user in decreasing importance .. for sender in senders.keys(): # For each of his followers for follower in followers: if follower.destination_id != sender: continue # Don't send notifications to the senders if they follow each other if follower.source_id in senders: continue # If the recipient/follower is not registered # yet by a more important sender if follower.source_id not in recipients: # Register the recipient with the sender's id recipients[follower.source_id] = sender # Create notifications for the recipients for recipient, sender in recipients.iteritems(): item = Notification(type=Notification.NT_FLIGHT, sender_id=sender, recipient_id=recipient, flight=flight) DBSession.add(item)
def by_location(cls, location, distance_threshold=0.025): location = WKTElement(location.to_wkt(), srid=4326) distance = func.ST_Distance(cls.location_wkt, location) airport = DBSession.query(cls, distance.label('distance')) \ .order_by(distance).first() if airport is not None and (distance_threshold is None or airport.distance < distance_threshold): return airport.Airport else: return None
def create_flight_comment_notifications(comment): ''' Create notifications for the owner and pilots of the flight ''' # Create list of potential recipients (using Set to avoid duplicates) recipients = Set([comment.flight.igc_file.owner, comment.flight.pilot, comment.flight.co_pilot]) # Create notifications for the recipients in the Set for recipient in recipients: # There is no need to notify the user that caused the notification if recipient is None or recipient == comment.user: continue item = Notification(type=Notification.NT_FLIGHT_COMMENT, sender=comment.user, recipient=recipient, flight=comment.flight, flight_comment=comment) DBSession.add(item)
def get_requested_record_list(model, ids, **kw): """Similar to get_requested_record(), but expects a comma-separated list of ids, and returns a list of (unique) records.""" ids = _parse_id_list(ids) q = DBSession.query(model).filter(model.id.in_(ids)) q = _patch_query(q, **kw) records = {record.id: record for record in q} if len(records) != len(ids): raise HTTPNotFound( detail=_('Sorry, {num_missing} of the requested records ({ids}) do not exist in our database.') .format(num_missing=(len(ids) - len(records)), ids=ids)) return [records[id] for id in ids]
def setUp(self): """Prepare model test fixture.""" try: new_attrs = {} new_attrs.update(self.attrs) new_attrs.update(self.do_get_dependencies()) self.obj = self.klass(**new_attrs) DBSession.add(self.obj) DBSession.flush() return self.obj except: DBSession.rollback() raise
def get_requested_record(model, id, **kw): """Look up a record with the id (string) specified by a remote client. Aborts the current request if the id is malformed or if the record does not exist.""" try: id = int(id) except ValueError: raise HTTPNotFound(detail=_('Sorry, the record id ({id}) that you ' 'requested is not a valid id.').format(id=id)) q = DBSession.query(model) q = _patch_query(q, **kw) record = q.get(id) if record is None: raise HTTPNotFound(detail=_('Sorry, there is no such record ({id}) in ' 'our database.').format(id=id)) return record
def document(self, *args, **kwargs): """Render the error document""" # Merge the user into the current DBSession # to prevent DetachedInstanceError if request.identity is not None: request.identity['user'] = DBSession.merge(request.identity['user']) resp = request.environ.get('pylons.original_response') if resp is None: raise HTTPNotFound match = re.search(re_message, resp.body) if match is not None: default_message = '<p>{}</p>'.format(match.group(1).strip()) else: default_message = ("<p>We're sorry but we weren't able to process " " this request.</p>") values = dict(prefix=request.environ.get('SCRIPT_NAME', ''), code=request.params.get('code', resp.status_int), message=request.params.get('message', default_message)) return values
def guess_registration(self): from skylines.model.flight import Flight # try to find another flight with the same logger and use it's aircraft registration if self.logger_id is not None \ and self.logger_manufacturer_id is not None: logger_id = self.logger_id logger_manufacturer_id = self.logger_manufacturer_id result = DBSession.query(Flight).outerjoin(IGCFile) \ .filter(func.upper(IGCFile.logger_manufacturer_id) == func.upper(logger_manufacturer_id)) \ .filter(func.upper(IGCFile.logger_id) == func.upper(logger_id)) \ .filter(Flight.registration != None) \ .order_by(desc(Flight.id)) if self.logger_manufacturer_id.startswith('X'): result = result.filter(Flight.pilot == self.owner) result = result.first() if result and result.registration: return result.registration return None
def count_unread_notifications(user): return DBSession.query(Notification) \ .filter(Notification.recipient == user) \ .filter(Notification.time_read == None).count()
def by_recover_key(cls, key): return DBSession.query(cls).filter_by(recover_key=key).first()
def query(cls, source, destination): assert(isinstance(source, User)) assert(isinstance(destination, User)) return DBSession.query(cls) \ .filter_by(source=source, destination=destination)
def by_md5(cls, _md5): file = IGCFile.by_md5(_md5) if file is None: return None return DBSession.query(cls).filter_by(igc_file=file).first()
def by_md5(cls, _md5): return DBSession.query(cls).filter_by(md5=_md5).first()
def tearDown(self): """Finish model test fixture.""" DBSession.rollback()
def test_query_obj(self): """Model objects can be queried""" obj = DBSession.query(self.klass).one() for key, value in self.attrs.iteritems(): assert_equals(getattr(obj, key), value)
def get_largest(cls): '''Returns a query object ordered by distance''' return DBSession.query(cls).order_by(desc(cls.olc_classic_distance))
def delete_phases(self): from skylines.model.flight_phase import FlightPhase DBSession.query(FlightPhase) \ .filter(FlightPhase.flight == self) \ .delete()
def follow(cls, source, destination): f = cls.query(source, destination).first() if not f: f = Follower(source=source, destination=destination) DBSession.add(f)
def distance(self, location): loc1 = cast(self.location_wkt.wkt, 'geography') loc2 = func.ST_GeographyFromText(location.to_wkt()) return DBSession.scalar(func.ST_Distance(loc1, loc2))
def by_tracking_key(cls, key): return DBSession.query(cls).filter_by(tracking_key=key).first()
def by_name(cls, _name): return DBSession.query(cls).filter_by(name=_name).first()
def by_email_address(cls, email): """Return the user object whose email address is ``email``.""" return DBSession.query(cls).filter_by(email_address=email).first()