Esempio n. 1
0
class FlightComment(db.Model):
    __tablename__ = 'flight_comments'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)

    flight_id = db.Column(Integer,
                          db.ForeignKey('flights.id', ondelete='CASCADE'),
                          nullable=False,
                          index=True)
    flight = db.relationship('Flight',
                             innerjoin=True,
                             backref=db.backref('comments',
                                                order_by=time_created,
                                                passive_deletes=True))

    user_id = db.Column(Integer, db.ForeignKey('users.id',
                                               ondelete='SET NULL'))
    user = db.relationship('User', lazy='joined')

    def __repr__(self):
        return (
            '<FlightComment: id=%d user_id=%d flight_id=%d>' %
            (self.id, self.user_id, self.flight_id)).encode('unicode_escape')

    text = db.Column(Unicode, nullable=False)
Esempio n. 2
0
class Notification(db.Model):
    __tablename__ = 'notifications'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    # The event of this notification

    event_id = db.Column(Integer,
                         db.ForeignKey('events.id', ondelete='CASCADE'),
                         nullable=False)
    event = db.relationship('Event', innerjoin=True)

    # The recipient of this notification

    recipient_id = db.Column(Integer,
                             db.ForeignKey('users.id', ondelete='CASCADE'),
                             nullable=False)
    recipient = db.relationship('User', innerjoin=True)

    # The time that this notification was read by the recipient

    time_read = db.Column(DateTime)

    ##############################

    def __repr__(self):
        return '<Notification: id={}>' \
            .format(self.id).encode('unicode_escape')

    ##############################

    @classmethod
    def query_unread(cls, recipient):
        return cls.query(recipient=recipient, time_read=None)

    @classmethod
    def count_unread(cls, recipient):
        return cls.query_unread(recipient).count()

    ##############################

    def mark_read(self):
        self.time_read = datetime.utcnow()

    @classmethod
    def mark_all_read(cls, recipient, filter_func=None):
        query = cls.query(recipient=recipient) \
            .outerjoin(Event) \
            .filter(Event.id == Notification.event_id)

        if filter_func is not None:
            query = filter_func(query)

        query.update(dict(time_read=datetime.utcnow()))
Esempio n. 3
0
class Event(db.Model):
    __tablename__ = 'events'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    # Notification type

    type = db.Column(Integer, nullable=False)

    class Type:
        FLIGHT_COMMENT = 1
        FLIGHT = 2
        FOLLOWER = 3
        NEW_USER = 4
        CLUB_JOIN = 5

    # Event time

    time = db.Column(DateTime, nullable=False, default=datetime.utcnow)

    # The user that caused the event

    actor_id = db.Column(Integer,
                         db.ForeignKey('users.id', ondelete='CASCADE'),
                         nullable=False)
    actor = db.relationship('User', foreign_keys=[actor_id], innerjoin=True)

    # A user if this event is about a user (e.g. actor following user)

    user_id = db.Column(Integer, db.ForeignKey('users.id', ondelete='CASCADE'))
    user = db.relationship('User', foreign_keys=[user_id])

    # A club if this event is about a club (e.g. actor joining club)

    club_id = db.Column(Integer, db.ForeignKey('clubs.id', ondelete='CASCADE'))
    club = db.relationship('Club')

    # A flight if this event is about a flight

    flight_id = db.Column(Integer,
                          db.ForeignKey('flights.id', ondelete='CASCADE'))
    flight = db.relationship('Flight')

    # A flight comment if this event is about a flight comment

    flight_comment_id = db.Column(
        Integer, db.ForeignKey('flight_comments.id', ondelete='CASCADE'))
    flight_comment = db.relationship('FlightComment')

    ##############################

    def __repr__(self):
        return '<Event: id={} type={}>' \
            .format(self.id, self.type).encode('unicode_escape')
Esempio n. 4
0
class Club(db.Model):
    __tablename__ = 'clubs'
    __searchable_columns__ = ['name']
    __search_detail_columns__ = ['website']

    id = db.Column(Integer, autoincrement=True, primary_key=True)
    name = db.Column(Unicode(255), unique=True, nullable=False)

    owner_id = db.Column(
        Integer,
        db.ForeignKey('users.id',
                      use_alter=True,
                      name="users.id",
                      ondelete='SET NULL'))
    owner = db.relationship('User', foreign_keys=[owner_id])

    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)

    website = db.Column(Unicode(255))

    def __unicode__(self):
        return self.name

    def __repr__(self):
        return ('<Club: id=%d name=\'%s\'>' %
                (self.id, self.name)).encode('unicode_escape')

    def is_writable(self, user):
        return user and (self.id == user.club_id or user.is_manager())
Esempio n. 5
0
class TrackingSession(db.Model):
    __tablename__ = 'tracking_sessions'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    pilot_id = db.Column(Integer,
                         db.ForeignKey('users.id', ondelete='CASCADE'),
                         nullable=False)
    pilot = db.relationship('User', innerjoin=True)

    lt24_id = db.Column(BigInteger, index=True)

    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
    ip_created = db.Column(INET)

    time_finished = db.Column(DateTime)
    ip_finished = db.Column(INET)

    # client application
    client = db.Column(Unicode(32))
    client_version = db.Column(Unicode(8))

    # device information
    device = db.Column(Unicode(32))
    gps_device = db.Column(Unicode(32))

    # aircraft information
    aircraft_type = db.Column(SmallInteger)
    aircraft_model = db.Column(Unicode(64))

    # status of the pilot after landing
    #
    # 0-> "Everything OK"
    # 1-> "Need retrieve"
    # 2-> "Need some help, nothing broken"
    # 3-> "Need help, maybe something broken"
    # 4-> "HELP, SERIOUS INJURY"
    finish_status = db.Column(SmallInteger)

    def __repr__(self):
        return '<TrackingSession: id={}>'.format(
            self.id).encode('unicode_escape')

    @classmethod
    def by_lt24_id(cls, lt24_id, filter_finished=True):
        query = cls.query(lt24_id=lt24_id)

        if filter_finished:
            query = query.filter_by(time_finished=None)

        return query.order_by(cls.time_created.desc()).first()
Esempio n. 6
0
class Follower(db.Model):
    __tablename__ = 'followers'
    __table_args__ = (
        db.UniqueConstraint('source_id', 'destination_id', name='unique_connection'),
    )

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    source_id = db.Column(
        Integer, db.ForeignKey('users.id', ondelete='CASCADE'),
        index=True, nullable=False)
    source = db.relationship(
        'User', foreign_keys=[source_id],
        lazy='joined', backref='following')

    destination_id = db.Column(
        Integer, db.ForeignKey('users.id', ondelete='CASCADE'),
        index=True, nullable=False)
    destination = db.relationship(
        'User', foreign_keys=[destination_id],
        lazy='joined', backref='followers')

    time = db.Column(DateTime, nullable=False, default=datetime.utcnow)

    @classmethod
    def follows(cls, source, destination):
        return cls.query(source=source, destination=destination).count() > 0

    @classmethod
    def follow(cls, source, destination):
        f = cls.query(source=source, destination=destination).first()
        if not f:
            f = Follower(source=source, destination=destination)
            db.session.add(f)

    @classmethod
    def unfollow(cls, source, destination):
        cls.query(source=source, destination=destination).delete()
Esempio n. 7
0
class Trace(db.Model):
    """
    This table saves the locations and visiting times of the turnpoints
    of an optimized Flight.
    """

    __tablename__ = 'traces'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    flight_id = db.Column(Integer,
                          db.ForeignKey('flights.id', ondelete='CASCADE'),
                          nullable=False)
    flight = db.relationship('Flight',
                             innerjoin=True,
                             backref=db.backref('traces',
                                                passive_deletes=True))

    contest_type = db.Column(String, nullable=False)
    trace_type = db.Column(String, nullable=False)

    distance = db.Column(Integer)
    duration = db.Column(Interval)

    times = db.Column(postgresql.ARRAY(DateTime), nullable=False)
    _locations = db.Column('locations', Geometry('LINESTRING'), nullable=False)

    @property
    def speed(self):
        if self.distance is None or self.duration is None:
            return None

        return float(self.distance) / self.duration.total_seconds()

    @property
    def locations(self):
        return [
            Location(longitude=location[0], latitude=location[1])
            for location in to_shape(self._locations).coords
        ]

    @locations.setter
    def locations(self, locations):
        points = [
            '{} {}'.format(location.longitude, location.latitude)
            for location in locations
        ]
        wkt = "LINESTRING({})".format(','.join(points))
        self._locations = WKTElement(wkt, srid=4326)
Esempio n. 8
0
class FlightPhase(db.Model):
    __tablename__ = 'flight_phases'

    # Flight phase types
    PT_POWERED = 1
    PT_CRUISE = 2
    PT_CIRCLING = 3

    # Circling directions
    CD_LEFT = 1
    CD_MIXED = 2
    CD_RIGHT = 3
    CD_TOTAL = 4

    id = db.Column(Integer, primary_key=True, autoincrement=True)
    flight_id = db.Column(Integer,
                          db.ForeignKey('flights.id', ondelete='CASCADE'),
                          nullable=False,
                          index=True)

    start_time = db.Column(DateTime)
    end_time = db.Column(DateTime)

    flight = db.relationship('Flight',
                             innerjoin=True,
                             backref=db.backref('_phases',
                                                order_by=start_time,
                                                passive_deletes=True))

    aggregate = db.Column(Boolean, nullable=False)
    phase_type = db.Column(Integer)  # see PT_* constants
    circling_direction = db.Column(Integer)  # see CD_* constants
    alt_diff = db.Column(Integer)
    duration = db.Column(Interval)
    fraction = db.Column(Integer)
    distance = db.Column(Integer)
    speed = db.Column(Float)
    vario = db.Column(Float)
    glide_rate = db.Column(Float)
    count = db.Column(Integer, nullable=False)
Esempio n. 9
0
class User(db.Model):
    """
    User definition.

    This is the user definition used by :mod:`repoze.who`, which requires at
    least the ``user_name`` column.
    """

    __tablename__ = 'users'
    __searchable_columns__ = ['name']

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    # eMail address and name of the user

    email_address = db.column_property(db.Column(Unicode(255)),
                                       comparator_factory=LowerCaseComparator)

    first_name = db.Column(Unicode(255), nullable=False)
    last_name = db.Column(Unicode(255))

    # Hashed password

    _password = db.Column('password', Unicode(128))

    # The user's club (optional)

    club_id = db.Column(Integer, db.ForeignKey('clubs.id',
                                               ondelete='SET NULL'))
    club = db.relationship('Club', foreign_keys=[club_id], backref='members')

    # Tracking key and delay in minutes

    tracking_key = db.Column(BigInteger, index=True)
    tracking_delay = db.Column(SmallInteger, nullable=False, default=0)

    # Time and IP of creation

    created = db.Column(DateTime, default=datetime.utcnow)
    created_ip = db.Column(INET)

    # Time and IP of the last login

    login_time = db.Column(DateTime)
    login_ip = db.Column(INET)

    # Password recovery information

    recover_key = db.Column(Integer)
    recover_time = db.Column(DateTime)
    recover_ip = db.Column(INET)

    # Units settings

    distance_unit = db.Column(SmallInteger, nullable=False, default=1)
    speed_unit = db.Column(SmallInteger, nullable=False, default=1)
    lift_unit = db.Column(SmallInteger, nullable=False, default=0)
    altitude_unit = db.Column(SmallInteger, nullable=False, default=0)

    # Other settings

    admin = db.Column(Boolean, nullable=False, default=False)
    eye_candy = db.Column(Boolean, nullable=False, default=False)

    ##############################

    def __repr__(self):
        return ('<User: email=%s, display=%s>' %
                (self.email_address, self.name)).encode('unicode_escape')

    def __unicode__(self):
        return self.name

    ##############################

    @classmethod
    def by_email_address(cls, email):
        """Return the user object whose email address is ``email``."""
        return cls.query(email_address=email).first()

    @classmethod
    def by_credentials(cls, email, password):
        """
        Return the user object whose email address is ``email`` if the
        password is matching.
        """
        user = cls.query(email_address=email).first()
        if user and user.validate_password(password):
            return user

    @classmethod
    def by_tracking_key(cls, key):
        return cls.query(tracking_key=key).first()

    @classmethod
    def by_recover_key(cls, key):
        return cls.query(recover_key=key).first()

    ## Flask Login ###############

    def is_active(self):
        return True

    def is_authenticated(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return unicode(self.id)

    ##############################

    @hybrid_property
    def name(self):
        if not self.last_name:
            return self.first_name

        return self.first_name + ' ' + self.last_name

    @name.expression
    def name_expression(cls):
        return case([
            (cls.last_name != None, cls.first_name + ' ' + cls.last_name),
        ],
                    else_=cls.first_name)

    def initials(self):
        parts = self.name.split()
        initials = [p[0].upper() for p in parts if len(p) > 2 and '.' not in p]
        return ''.join(initials)

    ##############################

    def _set_password(self, password):
        """Hash ``password`` on the fly and store its hashed version."""
        self._password = self._hash_password(password)

    def _get_password(self):
        """Return the hashed version of the password."""
        return self._password

    password = db.synonym('_password',
                          descriptor=property(_get_password, _set_password))

    @classmethod
    def _hash_password(cls, password):
        # Make sure password is a str because we cannot hash unicode objects
        if isinstance(password, unicode):
            password = password.encode('utf-8')
        salt = sha256()
        salt.update(os.urandom(60))
        hash = sha256()
        hash.update(password + salt.hexdigest())
        password = salt.hexdigest() + hash.hexdigest()
        # Make sure the hashed password is a unicode object at the end of the
        # process because SQLAlchemy _wants_ unicode objects for Unicode cols
        if not isinstance(password, unicode):
            password = password.decode('utf-8')
        return password

    def validate_password(self, password):
        """
        Check the password against existing credentials.

        :param password: the password that was provided by the user to
            try and authenticate. This is the clear text version that we will
            need to match against the hashed one in the database.
        :type password: unicode object.
        :return: Whether the password is valid.
        :rtype: bool

        """

        # Make sure accounts without a password can't log in
        if not self.password:
            return False

        hash = sha256()
        if isinstance(password, unicode):
            password = password.encode('utf-8')
        hash.update(password + str(self.password[:64]))
        return self.password[64:] == hash.hexdigest()

    ##############################

    def generate_tracking_key(self):
        self.tracking_key = struct.unpack('I', os.urandom(4))[0]

    @property
    def tracking_key_hex(self):
        if self.tracking_key is None:
            return None

        return '%X' % self.tracking_key

    @classmethod
    def tracking_delay_interval(cls):
        return cast(cast(cls.tracking_delay, String) + ' minutes', Interval)

    ##############################

    def is_manager(self):
        return self.admin

    ##############################

    def generate_recover_key(self, ip):
        self.recover_key = struct.unpack('I', os.urandom(4))[0] & 0x7fffffff
        self.recover_time = datetime.utcnow()
        self.recover_ip = ip
        return self.recover_key

    ##############################

    def is_readable(self, user):
        """Does the current user have full read access to this object?"""
        return self.is_writable(user)

    def is_writable(self, user):
        return user and \
            (self.id == user.id or
             (self.password is None and self.club_id == user.club_id) or
             user.is_manager())

    ##############################

    def follows(self, other):
        assert isinstance(other, User)
        from skylines.model.follower import Follower
        return Follower.follows(self, other)

    ##############################

    def get_largest_flights(self):
        '''
        Returns a query with all flights by the user
        as pilot ordered by distance
        '''
        from skylines.model.flight import Flight
        return Flight.get_largest().filter_by(pilot=self)

    ##############################

    @property
    def unit_preset(self):
        """Calculate unit preset based on user unit preference.

        If all user unit settings exactly matches one of the preset, return
        that preset id. Otherwise return 0, that is interpreted as 'Custom'
        preset.
        """
        for pref, preset in enumerate(units.UNIT_PRESETS):
            p = preset[1]
            if not p:
                continue
            eq = [
                p['distance_unit'] == units.DISTANCE_UNITS[self.distance_unit]
                [0], p['speed_unit'] == units.SPEED_UNITS[self.speed_unit][0],
                p['lift_unit'] == units.LIFT_UNITS[self.lift_unit][0],
                p['altitude_unit'] == units.ALTITUDE_UNITS[
                    self.altitude_unit][0]
            ]
            if all(eq):
                return pref

        return 0

    @unit_preset.setter
    def unit_preset(self, preset):
        """Set individual unit preferences according to given preset
        """
        name, settings = units.UNIT_PRESETS[preset]
        if settings:
            self.distance_unit = units.unitid(units.DISTANCE_UNITS,
                                              settings['distance_unit'])
            self.speed_unit = units.unitid(units.SPEED_UNITS,
                                           settings['speed_unit'])
            self.lift_unit = units.unitid(units.LIFT_UNITS,
                                          settings['lift_unit'])
            self.altitude_unit = units.unitid(units.ALTITUDE_UNITS,
                                              settings['altitude_unit'])
Esempio n. 10
0
class Flight(db.Model):
    __tablename__ = 'flights'

    id = db.Column(Integer, autoincrement=True, primary_key=True)
    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
    time_modified = db.Column(DateTime,
                              nullable=False,
                              default=datetime.utcnow)

    pilot_id = db.Column(Integer,
                         db.ForeignKey('users.id', ondelete='SET NULL'),
                         index=True)
    pilot = db.relationship('User', foreign_keys=[pilot_id])

    co_pilot_id = db.Column(Integer,
                            db.ForeignKey('users.id', ondelete='SET NULL'),
                            index=True)
    co_pilot = db.relationship('User', foreign_keys=[co_pilot_id])

    club_id = db.Column(Integer,
                        db.ForeignKey('clubs.id', ondelete='SET NULL'),
                        index=True)
    club = db.relationship('Club', backref='flights')

    model_id = db.Column(Integer,
                         db.ForeignKey('models.id', ondelete='SET NULL'))
    model = db.relationship('AircraftModel')
    registration = db.Column(Unicode(32))
    competition_id = db.Column(Unicode(5))

    # The date of the flight in local time instead of UTC. Used for scoring.
    date_local = db.Column(Date, nullable=False, index=True)

    takeoff_time = db.Column(DateTime, nullable=False, index=True)
    landing_time = db.Column(DateTime, nullable=False)
    takeoff_location_wkt = db.Column('takeoff_location', Geometry('POINT'))
    landing_location_wkt = db.Column('landing_location', Geometry('POINT'))

    takeoff_airport_id = db.Column(
        Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
    takeoff_airport = db.relationship('Airport',
                                      foreign_keys=[takeoff_airport_id])

    landing_airport_id = db.Column(
        Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
    landing_airport = db.relationship('Airport',
                                      foreign_keys=[landing_airport_id])

    timestamps = deferred(db.Column(postgresql.ARRAY(DateTime),
                                    nullable=False),
                          group='path')

    locations = deferred(db.Column(Geometry('LINESTRING', srid=4326),
                                   nullable=False),
                         group='path')

    olc_classic_distance = db.Column(Integer)
    olc_triangle_distance = db.Column(Integer)
    olc_plus_score = db.Column(Float)

    igc_file_id = db.Column(Integer,
                            db.ForeignKey('igc_files.id', ondelete='CASCADE'),
                            nullable=False)
    igc_file = db.relationship('IGCFile', backref='flights', innerjoin=True)

    needs_analysis = db.Column(Boolean, nullable=False, default=True)

    def __repr__(self):
        return ('<Flight: id=%d>' % self.id).encode('unicode_escape')

    @hybrid_property
    def duration(self):
        return self.landing_time - self.takeoff_time

    @hybrid_property
    def year(self):
        return self.date_local.year

    @hybrid_property
    def index_score(self):
        if self.model and self.model.dmst_index > 0:
            return self.olc_plus_score * 100 / self.model.dmst_index
        else:
            return self.olc_plus_score

    @index_score.expression
    def index_score(cls):
        return case([(AircraftModel.dmst_index > 0,
                      cls.olc_plus_score * 100 / AircraftModel.dmst_index)],
                    else_=cls.olc_plus_score)

    @year.expression
    def year(cls):
        return db.func.date_part('year', cls.date_local)

    @property
    def takeoff_location(self):
        if self.takeoff_location_wkt is None:
            return None

        coords = to_shape(self.takeoff_location_wkt)
        return Location(latitude=coords.y, longitude=coords.x)

    @takeoff_location.setter
    def takeoff_location(self, location):
        if location is None:
            self.takeoff_location_wkt = None
        else:
            self.takeoff_location_wkt = location.to_wkt_element()

    @property
    def landing_location(self):
        if self.landing_location_wkt is None:
            return None

        coords = to_shape(self.landing_location_wkt)
        return Location(latitude=coords.y, longitude=coords.x)

    @landing_location.setter
    def landing_location(self, location):
        if location is None:
            self.landing_location_wkt = None
        else:
            self.landing_location_wkt = location.to_wkt_element()

    @classmethod
    def by_md5(cls, _md5):
        file = IGCFile.by_md5(_md5)
        if file is None:
            return None

        return cls.query().filter_by(igc_file=file).first()

    def is_writable(self, user):
        return user and \
            (self.igc_file.owner_id == user.id or
             self.pilot_id == user.id or
             user.is_manager())

    def may_delete(self, user):
        return user and (self.igc_file.owner_id == user.id
                         or user.is_manager())

    @classmethod
    def get_largest(cls):
        '''Returns a query object ordered by distance'''
        return cls.query().order_by(cls.olc_classic_distance.desc())

    def get_optimised_contest_trace(self, contest_type, trace_type):
        from skylines.model.trace import Trace
        return Trace.query(contest_type=contest_type,
                           trace_type=trace_type,
                           flight=self).first()

    def get_contest_speed(self, contest_type, trace_type):
        contest = self.get_optimised_contest_trace(contest_type, trace_type)
        return contest and contest.speed

    @property
    def speed(self):
        return self.get_contest_speed('olc_plus', 'classic')

    @property
    def has_phases(self):
        return bool(self._phases)

    @property
    def phases(self):
        return [p for p in self._phases if not p.aggregate]

    def delete_phases(self):
        from skylines.model.flight_phase import FlightPhase
        FlightPhase.query(flight=self).delete()

    @property
    def circling_performance(self):
        from skylines.model.flight_phase import FlightPhase
        stats = [
            p for p in self._phases
            if (p.aggregate and p.phase_type == FlightPhase.PT_CIRCLING
                and p.duration.total_seconds() > 0)
        ]
        order = [
            FlightPhase.CD_TOTAL, FlightPhase.CD_LEFT, FlightPhase.CD_RIGHT,
            FlightPhase.CD_MIXED
        ]
        stats.sort(lambda a, b: cmp(order.index(a.circling_direction),
                                    order.index(b.circling_direction)))
        return stats

    @property
    def cruise_performance(self):
        from skylines.model.flight_phase import FlightPhase
        return [
            p for p in self._phases
            if p.aggregate and p.phase_type == FlightPhase.PT_CRUISE
        ]

    def update_flight_path(self):
        from skylines.lib.xcsoar_ import flight_path
        from skylines.lib.datetime import from_seconds_of_day

        # Run the IGC file through the FlightPath utility
        path = flight_path(self.igc_file)
        if len(path) < 2:
            return False

        # Save the timestamps of the coordinates
        date_utc = self.igc_file.date_utc
        self.timestamps = \
            [from_seconds_of_day(date_utc, c.seconds_of_day) for c in path]

        # Convert the coordinate into a list of tuples
        coordinates = [(c.longitude, c.latitude) for c in path]

        # Create a shapely LineString object from the coordinates
        linestring = LineString(coordinates)

        # Save the new path as WKB
        self.locations = from_shape(linestring, srid=4326)

        return True
Esempio n. 11
0
class IGCFile(db.Model):
    __tablename__ = 'igc_files'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    owner_id = db.Column(Integer, db.ForeignKey('users.id'), nullable=False)
    owner = db.relationship('User', innerjoin=True)

    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
    filename = db.Column(String(), nullable=False)
    md5 = db.Column(String(32), nullable=False, unique=True)

    logger_id = db.Column(String(3))
    logger_manufacturer_id = db.Column(String(3))

    registration = db.Column(Unicode(32))
    competition_id = db.Column(Unicode(5))
    model = db.Column(Unicode(64))

    date_utc = db.Column(Date, nullable=False)

    def __repr__(self):
        return ('<IGCFile: id=%d filename=\'%s\'>' %
                (self.id, self.filename)).encode('unicode_escape')

    @classmethod
    def by_md5(cls, _md5):
        return cls.query(md5=_md5).first()

    def is_writable(self, user):
        return user and \
            (self.owner_id == user.id or
             self.pilot_id == user.id or
             user.is_manager())

    def may_delete(self, user):
        return user and user.is_manager()

    def update_igc_headers(self):
        path = files.filename_to_path(self.filename)
        igc_headers = read_igc_headers(path)
        if igc_headers is None:
            return

        if 'manufacturer_id' in igc_headers:
            self.logger_manufacturer_id = igc_headers['manufacturer_id']

        if 'logger_id' in igc_headers:
            self.logger_id = igc_headers['logger_id']

        if 'date_utc' in igc_headers:
            self.date_utc = igc_headers['date_utc']

        if 'model' in igc_headers and (igc_headers['model'] is None
                                       or 0 < len(igc_headers['model']) < 64):
            self.model = igc_headers['model']

        if 'reg' in igc_headers and (igc_headers['reg'] is None
                                     or 0 < len(igc_headers['reg']) < 32):
            self.registration = igc_headers['reg']

        if 'cid' in igc_headers and (igc_headers['cid'] is None
                                     or 0 < len(igc_headers['cid']) < 5):
            self.competition_id = igc_headers['cid']

    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 = Flight.query().join(IGCFile) \
                .filter(db.func.upper(IGCFile.logger_manufacturer_id) == db.func.upper(logger_manufacturer_id)) \
                .filter(db.func.upper(IGCFile.logger_id) == db.func.upper(logger_id)) \
                .filter(Flight.registration == None) \
                .order_by(Flight.id.desc())

            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 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 = Flight.query() \
                .filter(db.func.upper(Flight.registration) == db.func.upper(glider_reg)) \
                .order_by(Flight.id.desc()) \
                .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 = Flight.query().join(IGCFile) \
                .filter(db.func.upper(IGCFile.logger_manufacturer_id) == db.func.upper(logger_manufacturer_id)) \
                .filter(db.func.upper(IGCFile.logger_id) == db.func.upper(logger_id)) \
                .filter(Flight.model_id == None) \
                .order_by(Flight.id.desc())

            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 = AircraftModel.query() \
                .filter(and_(
                    db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^a-z]', ' ').like(db.func.any(text_fragments)),
                    db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^0-9]', ' ').like(db.func.all(digit_fragments)))) \
                .order_by(db.func.levenshtein(db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^a-z0-9]', ''), glider_type_clean))

            if result.first():
                return result.first().id

        # nothing found
        return None
Esempio n. 12
0
class TrackingFix(db.Model):
    __tablename__ = 'tracking_fixes'

    id = db.Column(Integer, autoincrement=True, primary_key=True)

    time = db.Column(DateTime, nullable=False, default=datetime.utcnow)

    location_wkt = db.Column('location', Geometry('POINT'))

    track = db.Column(SmallInteger)
    ground_speed = db.Column(REAL)
    airspeed = db.Column(REAL)
    altitude = db.Column(SmallInteger)
    elevation = db.Column(SmallInteger)
    vario = db.Column(REAL)
    engine_noise_level = db.Column(SmallInteger)

    pilot_id = db.Column(Integer,
                         db.ForeignKey('users.id', ondelete='CASCADE'),
                         nullable=False)
    pilot = db.relationship('User', innerjoin=True)

    ip = db.Column(INET)

    def __repr__(self):
        return '<TrackingFix: id={} time=\'{}\'>' \
               .format(self.id, self.time).encode('unicode_escape')

    @property
    def location(self):
        if self.location_wkt is None:
            return None

        coords = to_shape(self.location_wkt)
        return Location(latitude=coords.y, longitude=coords.x)

    def set_location(self, longitude, latitude):
        self.location_wkt = from_shape(Point(longitude, latitude), srid=4326)

    @property
    def altitude_agl(self):
        if not self.elevation:
            raise ValueError('This TrackingFix has no elevation.')

        return max(0, self.altitude - self.elevation)

    @classmethod
    def max_age_filter(cls, max_age):
        """
        Returns a filter that makes sure that the fix is not older than a
        certain time.

        The delay parameter can be either a datetime.timedelta or a numeric
        value that will be interpreted as hours.
        """

        if isinstance(max_age, (int, long, float)):
            max_age = timedelta(hours=max_age)

        return cls.time >= datetime.utcnow() - max_age

    @classmethod
    def delay_filter(cls, delay):
        """
        Returns a filter that makes sure that the fix was created at least
        a certain time ago.

        The delay parameter can be either a datetime.timedelta or a numeric
        value that will be interpreted as minutes.
        """

        if isinstance(delay, (int, long, float)):
            delay = timedelta(minutes=delay)

        return cls.time <= datetime.utcnow() - delay

    @classmethod
    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.delay_filter(User.tracking_delay_interval())) \
            .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