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()