示例#1
0
class GroupflightComment(db.Model):
    __tablename__ = "groupflight_comments"

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

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

    groupflight_id = db.Column(
        Integer,
        db.ForeignKey("groupflights.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    groupflight = db.relationship(
        "Groupflight",
        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")

    text = db.Column(Unicode, nullable=False)

    def __repr__(self):
        return unicode_to_str(
            "<GroupflightComment: id=%d user_id=%d groupflight_id=%d>"
            % (self.id, self.user_id, self.groupflight_id)
        )
示例#2
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')

    text = db.Column(Unicode, nullable=False)

    def __repr__(self):
        return (
            '<FlightComment: id=%d user_id=%d flight_id=%d>' %
            (self.id, self.user_id, self.flight_id)).encode('unicode_escape')
示例#3
0
class FlightMeetings(db.Model):
    __tablename__ = 'flight_meetings'

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

    source_id = db.Column(
        Integer, db.ForeignKey('flights.id', ondelete='CASCADE'),
        index=True, nullable=False)
    source = db.relationship(
        'Flight', foreign_keys=[source_id])

    destination_id = db.Column(
        Integer, db.ForeignKey('flights.id', ondelete='CASCADE'),
        index=True, nullable=False)
    destination = db.relationship(
        'Flight', foreign_keys=[destination_id])

    start_time = db.Column(DateTime, nullable=False)
    end_time = db.Column(DateTime, nullable=False)

    @classmethod
    def get_meetings(cls, source):
        flight_source = aliased(Flight, name='flight_source')
        flight_destination = aliased(Flight, name='flight_destination')

        q = cls.query() \
               .filter(or_(cls.source == source, cls.destination == source)) \
               .join(flight_source, cls.source_id == flight_source.id) \
               .join(flight_destination, cls.destination_id == flight_destination.id) \
               .filter(flight_source.is_rankable()) \
               .filter(flight_destination.is_rankable()) \
               .order_by(cls.start_time)

        meetings = OrderedDict()
        for mp in q:
            flight = mp.source if mp.source != source else mp.destination

            if flight.id not in meetings:
                meetings[flight.id] = dict(
                    flight=flight,
                    times=[]
                )

            meetings[flight.id]['times'].append(dict(
                start=mp.start_time,
                end=mp.end_time
            ))

        return meetings

    @classmethod
    def add_meeting(cls, source, destination, start_time, end_time):
        if source == destination:
            return

        mp = FlightMeetings(source=source, destination=destination,
                            start_time=start_time, end_time=end_time)

        db.session.add(mp)
示例#4
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) \
                  .join(cls.event) \
                  .outerjoin(Event.flight) \
                  .filter(Flight.is_rankable())

    @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) \
            .filter(Event.id == Notification.event_id)

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

        query.update(dict(time_read=datetime.utcnow()),
                     synchronize_session=False)
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')
示例#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],
                             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],
                                  backref="followers")

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

    @classmethod
    def follows(cls, source, destination):
        if source == destination:
            return False

        return cls.query(source=source, destination=destination).count() > 0

    @classmethod
    def follow(cls, source, destination):
        # don't allow to follow yourself
        if source == destination:
            return

        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()
示例#7
0
class Club(db.Model):
    __tablename__ = "clubs"
    __searchable_columns__ = ["name"]
    __search_detail_columns__ = ["website"]
    __search_detail_columns__ = ["email_address"]

    id = db.Column(Integer, autoincrement=True, primary_key=True)
    name = db.Column(Unicode(255), unique=True, nullable=False)
    email_address = db.column_property(db.Column(Unicode(255)),
                                       comparator_factory=LowerCaseComparator)
    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 unicode_to_str("<Club: id=%d name='%s'>" % (self.id, self.name))

    def is_writable(self, user):
        return user and (self.id == user.club_id or user.is_manager())
示例#8
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())
示例#9
0
class Groupflight(db.Model):
    __tablename__ = "groupflights"

    id = db.Column(Integer, autoincrement=True, primary_key=True)
    club_id = db.Column(Integer, db.ForeignKey("clubs.id"), index=True)
    club = db.relationship("Club", backref="groupflights")
    flight_plan_md5 = db.Column(String(32), nullable=False)
    landscape = db.Column(String(32), nullable=False)
    time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
    time_modified = db.Column(DateTime,
                              nullable=False,
                              default=datetime.utcnow)
    date_flight = db.Column(DateTime,
                            nullable=False,
                            default=datetime.utcnow().date())
    takeoff_airport_id = db.Column(Integer, db.ForeignKey("airports.id"))
    takeoff_airport = db.relationship("Airport",
                                      foreign_keys=[takeoff_airport_id])
示例#10
0
文件: trace.py 项目: yataOrg/skylines
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,
                           cascade="all, delete, delete-orphan"),
    )

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

    # the optimized distance in meters
    distance = db.Column(Integer)
    duration = db.Column(Interval)

    times = db.Column(postgresql.ARRAY(DateTime), nullable=False)
    _locations = db.Column("locations",
                           Geometry("LINESTRING", srid=4326),
                           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)
示例#11
0
class RefreshToken(db.Model):
    __tablename__ = 'token'

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    client_id = Client.client_id
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship('User', foreign_keys=[user_id])
    refresh_token = db.Column(db.String(255), unique=True)
    scopes = []

    def delete(self):
        db.session.delete(self)
        db.session.commit()
示例#12
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', srid=4326),
                           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)
示例#13
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()
示例#14
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",
            passive_deletes=True,
            order_by=start_time,
            cascade="all, delete, delete-orphan",
        ),
    )

    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)

    @property
    def seconds_of_day(self):
        return to_seconds_of_day(self.flight.takeoff_time, self.start_time)
示例#15
0
文件: oauth.py 项目: yataOrg/skylines
class RefreshToken(db.Model):
    __tablename__ = "token"

    id = db.Column(db.Integer, autoincrement=True, primary_key=True)
    client_id = Client.client_id
    user_id = db.Column(db.Integer,
                        db.ForeignKey("users.id", ondelete="CASCADE"),
                        nullable=False)
    user = db.relationship("User", foreign_keys=[user_id])
    refresh_token = db.Column(db.String(255), unique=True)
    scopes = []

    def delete(self):
        db.session.delete(self)
        db.session.commit()
示例#16
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', passive_deletes=True, order_by=start_time,
                           cascade='all, delete, delete-orphan'))

    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)
示例#17
0
文件: user.py 项目: yataOrg/skylines
class User(db.Model):
    """
    User definition.
    """

    __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), nullable=False)

    # 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, delay in minutes and other settings

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

    tracking_callsign = db.Column(Unicode(5))

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

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

    def __init__(self, *args, **kw):
        self.generate_tracking_key()
        super(User, self).__init__(*args, **kw)

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

    def __repr__(self):
        return unicode_to_str("<User: email=%s, display=%s>" %
                              (self.email_address, self.name))

    def __unicode__(self):
        return self.name

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

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

    @staticmethod
    def by_credentials(email, password, *args, **kwargs):
        """
        Return the user object whose email address is ``email`` if the
        password is matching.
        """
        user = User.by_email_address(email)
        if user and user.validate_password(password):
            return user

    @staticmethod
    def by_tracking_key(key):
        return User.query(tracking_key=key).first()

    @staticmethod
    def by_recover_key(key):
        return User.query(recover_key=key).first()

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

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

        return self.first_name + u" " + self.last_name

    @name.expression
    def name_expression(cls):
        return case(
            [(cls.last_name != None, cls.first_name + u" " + 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)

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

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

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

    @classmethod
    def _hash_password(cls, password, salt=None):
        if salt is None:
            salt = os.urandom(60)

        assert is_unicode(password)
        assert is_bytes(salt)

        salt_hash = sha256()
        salt_hash.update(salt)

        hash = sha256()
        hash.update((password + salt_hash.hexdigest()).encode("utf-8"))

        return str_to_unicode(salt_hash.hexdigest() + hash.hexdigest())

    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
        """

        assert is_unicode(password)

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

        hash = sha256()
        hash.update((password + self.password[:64]).encode("utf-8"))

        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)

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

    @hybrid_method
    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)

    @property
    def num_followers(self):
        from skylines.model.follower import Follower

        return Follower.query(destination=self).count()

    @property
    def num_following(self):
        from skylines.model.follower import Follower

        return Follower.query(source=self).count()

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

    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(Flight.pilot == self)

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

    def delete(self):
        from skylines.model.follower import Follower
        from skylines.model.igcfile import IGCFile

        for row in db.session.query(IGCFile).filter_by(owner_id=self.id):
            files.delete_file(row.filename)

        db.session.query(IGCFile).filter_by(owner_id=self.id).delete()

        db.session.query(Follower).filter_by(source_id=self.id).delete()
        db.session.query(Follower).filter_by(destination_id=self.id).delete()

        db.session.delete(self)
示例#18
0
class FlightPathChunks(db.Model):
    """
    This table stores flight path chunks of about 100 fixes per column which
    enable PostGIS/Postgres to do fast queries due to tight bounding boxes
    around those short flight pahts.
    """

    __tablename__ = 'flight_path_chunks'

    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)

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

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

    start_time = db.Column(DateTime, nullable=False, index=True)
    end_time = db.Column(DateTime, nullable=False, index=True)

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

    @staticmethod
    def get_near_flights(flight, filter=None):
        """
        WITH src AS
            (SELECT ST_Buffer(ST_Simplify(locations, 0.005), 0.015) AS src_loc_buf,
                    start_time AS src_start,
                    end_time AS src_end
             FROM flight_paths
             WHERE flight_id = 8503)
        SELECT (dst_points).geom AS dst_point,
               dst_times[(dst_points).path[1]] AS dst_time,
               dst_points_fid AS dst_flight_id
        FROM
            (SELECT ST_dumppoints(locations) as dst_points,
                    timestamps AS dst_times,
                    src_loc_buf,
                    flight_id AS dst_points_fid,
                    src_start,
                    src_end
            FROM flight_paths, src
            WHERE flight_id != 8503 AND
                  end_time >= src_start AND
                  start_time <= src_end AND
                  locations && src_loc_buf AND
                  _ST_Intersects(ST_Simplify(locations, 0.005), src_loc_buf)) AS foo
        WHERE _ST_Contains(src_loc_buf, (dst_points).geom);
        """

        cte = db.session.query(FlightPathChunks.locations.ST_Simplify(0.005).ST_Buffer(0.015).label('src_loc_buf'),
                               FlightPathChunks.start_time.label('src_start'),
                               FlightPathChunks.end_time.label('src_end')) \
            .filter(FlightPathChunks.flight == flight) \
            .cte('src')

        subq = db.session.query(func.ST_DumpPoints(FlightPathChunks.locations).label('dst_points'),
                                FlightPathChunks.timestamps.label('dst_times'),
                                cte.c.src_loc_buf,
                                FlightPathChunks.flight_id.label('dst_points_fid'),
                                cte.c.src_start,
                                cte.c.src_end) \
            .filter(and_(FlightPathChunks.flight != flight,
                         FlightPathChunks.end_time >= cte.c.src_start,
                         FlightPathChunks.start_time <= cte.c.src_end,
                         FlightPathChunks.locations.intersects(cte.c.src_loc_buf),
                         _ST_Intersects(FlightPathChunks.locations.ST_Simplify(0.005), cte.c.src_loc_buf))) \
            .subquery()

        dst_times = literal_column('dst_times[(dst_points).path[1]]')

        q = db.session.query(subq.c.dst_points.geom.label('dst_location'),
                             dst_times.label('dst_time'),
                             subq.c.dst_points_fid.label('dst_point_fid')) \
            .filter(_ST_Contains(subq.c.src_loc_buf, subq.c.dst_points.geom)) \
            .order_by(subq.c.dst_points_fid, dst_times) \
            .all()

        src_trace = to_shape(flight.locations).coords
        max_distance = 1000
        other_flights = dict()

        for point in q:
            dst_time = point.dst_time
            dst_loc = to_shape(point.dst_location).coords

            # we might have got a destination point earier than source takeoff
            # or later than source landing. Check this case and disregard early.
            if dst_time < flight.takeoff_time or dst_time > flight.landing_time:
                continue

            # find point closest to given time
            closest = bisect_left(flight.timestamps,
                                  dst_time,
                                  hi=len(flight.timestamps) - 1)

            if closest == 0:
                src_point = src_trace[0]
            else:
                # interpolate flight trace between two fixes
                dx = (dst_time - flight.timestamps[closest - 1]).total_seconds() / \
                     (flight.timestamps[closest] - flight.timestamps[closest - 1]).total_seconds()

                src_point_prev = src_trace[closest - 1]
                src_point_next = src_trace[closest]

                src_point = [
                    src_point_prev[0] +
                    (src_point_next[0] - src_point_prev[0]) * dx,
                    src_point_prev[1] +
                    (src_point_next[1] - src_point_prev[1]) * dx
                ]

            point_distance = Location(
                latitude=dst_loc[0][1],
                longitude=dst_loc[0][0]).geographic_distance(
                    Location(latitude=src_point[1], longitude=src_point[0]))

            if point_distance > max_distance:
                continue

            if point.dst_point_fid not in other_flights:
                other_flights[point.dst_point_fid] = []
                other_flights[point.dst_point_fid].append(
                    dict(times=list(), points=list()))

            elif len(other_flights[point.dst_point_fid][-1]['times']) and \
                    (dst_time - other_flights[point.dst_point_fid][-1]['times'][-1]).total_seconds() > 600:
                other_flights[point.dst_point_fid].append(
                    dict(times=list(), points=list()))

            other_flights[point.dst_point_fid][-1]['times'].append(dst_time)
            other_flights[point.dst_point_fid][-1]['points'].append(
                Location(latitude=dst_loc[0][1], longitude=dst_loc[0][0]))

        return other_flights

    @staticmethod
    def update_flight_path(flight):
        from skylines.lib.xcsoar_ import flight_path
        from skylines.lib.datetime import from_seconds_of_day

        # Now populate the FlightPathChunks table with the (full) flight path
        path_detailed = flight_path(flight.igc_file,
                                    max_points=3000,
                                    qnh=flight.qnh)
        if len(path_detailed) < 2:
            return False

        # Number of points in each chunck.
        num_points = 100

        # Interval of the current chunck: [i, j] (-> path_detailed[i:j + 1])
        i = 0
        j = min(num_points - 1, len(path_detailed) - 1)

        # Ensure that the last chunk contains at least two fixes
        if j == len(path_detailed) - 2:
            j = len(path_detailed) - 1

        FlightPathChunks.query().filter(
            FlightPathChunks.flight == flight).delete()
        date_utc = flight.igc_file.date_utc

        while True:
            flight_path = FlightPathChunks(flight=flight)

            # Save the timestamps of the coordinates
            flight_path.timestamps = \
                [from_seconds_of_day(date_utc, c.seconds_of_day) for c in path_detailed[i:j + 1]]

            flight_path.start_time = path_detailed[i].datetime
            flight_path.end_time = path_detailed[j].datetime

            # Convert the coordinate into a list of tuples
            coordinates = [(c.location['longitude'], c.location['latitude'])
                           for c in path_detailed[i:j + 1]]

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

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

            db.session.add(flight_path)

            if j == len(path_detailed) - 1:
                break
            else:
                i = j + 1
                j = min(j + num_points, len(path_detailed) - 1)

                if j == len(path_detailed) - 2:
                    j = len(path_detailed) - 1

        db.session.commit()

        return True
示例#19
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])

    # Fallback if the pilot is not registered
    pilot_name = db.Column(Unicode(255))

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

    # Fallback if the co-pilot is not registered
    co_pilot_name = db.Column(Unicode(255))

    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)
    scoring_start_time = db.Column(DateTime, nullable=True)
    scoring_end_time = db.Column(DateTime, nullable=True)
    landing_time = db.Column(DateTime, nullable=False)

    takeoff_location_wkt = db.Column('takeoff_location',
                                     Geometry('POINT', srid=4326))
    landing_location_wkt = db.Column('landing_location',
                                     Geometry('POINT', srid=4326))

    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)

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

    # Privacy level of the flight

    class PrivacyLevel:
        PUBLIC = 0
        LINK_ONLY = 1
        PRIVATE = 2

    privacy_level = db.Column(SmallInteger,
                              nullable=False,
                              default=PrivacyLevel.PUBLIC)

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

    def __repr__(self):
        return ('<Flight: id=%s, modified=%s>' %
                (self.id, self.time_modified)).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()

    # Permissions ################

    @hybrid_method
    def is_viewable(self, user):
        return (self.privacy_level == Flight.PrivacyLevel.PUBLIC
                or self.privacy_level == Flight.PrivacyLevel.LINK_ONLY
                or self.is_writable(user))

    @is_viewable.expression
    def is_viewable_expression(cls, user):
        return or_(cls.privacy_level == Flight.PrivacyLevel.PUBLIC,
                   cls.privacy_level == Flight.PrivacyLevel.LINK_ONLY,
                   cls.is_writable(user))

    @hybrid_method
    def is_listable(self, user):
        return (self.privacy_level == Flight.PrivacyLevel.PUBLIC
                or self.is_writable(user))

    @is_listable.expression
    def is_listable_expression(cls, user):
        return or_(cls.privacy_level == Flight.PrivacyLevel.PUBLIC,
                   cls.is_writable(user))

    @hybrid_method
    def is_rankable(self):
        return self.privacy_level == Flight.PrivacyLevel.PUBLIC

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

    @is_writable.expression
    def is_writable_expression(self, user):
        return user and (user.is_manager() or or_(IGCFile.owner_id == user.id,
                                                  self.pilot_id == user.id))

    @hybrid_method
    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

    def get_contest_legs(self, contest_type, trace_type):
        return ContestLeg.query(contest_type=contest_type, trace_type=trace_type,
                                flight=self) \
                         .filter(ContestLeg.end_time - ContestLeg.start_time > timedelta(0)) \
                         .order_by(ContestLeg.start_time)

    @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):
        self._phases = []

    @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, qnh=self.qnh)
        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.location['longitude'], c.location['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
示例#20
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')

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

    @staticmethod
    def for_flight_comment(comment):
        """
        Create notifications for the owner and pilots of the flight
        """
        return Event(type=Event.Type.FLIGHT_COMMENT,
                     actor=comment.user,
                     flight=comment.flight,
                     flight_comment=comment)

    @staticmethod
    def for_flight(flight):
        """
        Create notifications for the followers of the owner and pilots of the flight
        """
        return Event(type=Event.Type.FLIGHT,
                     actor_id=flight.igc_file.owner_id,
                     flight=flight)

    @staticmethod
    def for_follower(followed, follower):
        """
        Create event for the followed pilot about his new follower
        """
        return Event(type=Event.Type.FOLLOWER, actor=follower, user=followed)

    @staticmethod
    def for_new_user(user):
        """
        Create event for a new SkyLines user.
        """
        return Event(type=Event.Type.NEW_USER, actor=user)

    @staticmethod
    def for_club_join(club_id, user):
        """
        Create event for a user joining a club.
        """
        return Event(type=Event.Type.CLUB_JOIN, actor=user, club_id=club_id)
示例#21
0
文件: igcfile.py 项目: xxqhh/skylines
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 unicode_to_str("<IGCFile: id=%d filename='%s'>" %
                              (self.id, self.filename))

    @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
示例#22
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)
    time_visible = db.Column(DateTime, nullable=False, default=datetime.utcnow)

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

    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,
                         index=True)
    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 is_int(max_age) or isinstance(max_age, float):
            max_age = timedelta(hours=max_age)

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

    @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.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
示例#23
0
class ContestLeg(db.Model):
    """
    This table saves the legs of a optimized Flight.
    """

    __tablename__ = "contest_legs"

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

    flight_id = db.Column(
        Integer,
        db.ForeignKey("flights.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    flight = db.relationship(
        "Flight",
        innerjoin=True,
        backref=db.backref("_legs",
                           passive_deletes=True,
                           cascade="all, delete, delete-orphan"),
    )

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

    # direct distance from start to end
    distance = db.Column(Integer)

    # total height and duration of cruise phases
    cruise_height = db.Column(Integer)
    cruise_distance = db.Column(Integer)
    cruise_duration = db.Column(Interval)

    # total height and duration of climb phases
    climb_height = db.Column(Integer)
    climb_duration = db.Column(Interval)

    # start and end height
    start_height = db.Column(Integer)
    end_height = db.Column(Integer)

    # start and end time
    start_time = db.Column(DateTime, nullable=False)
    end_time = db.Column(DateTime, nullable=False)

    # start and end locations
    start_location_wkt = db.Column("start_location",
                                   Geometry("POINT", srid=4326))
    end_location_wkt = db.Column("end_location", Geometry("POINT", srid=4326))

    @property
    def duration(self):
        return self.end_time - self.start_time

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

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

    @property
    def seconds_of_day(self):
        return to_seconds_of_day(self.flight.takeoff_time, self.start_time)

    @property
    def start_location(self):
        if self.start_location_wkt is None:
            return None

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

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

    @property
    def end_location(self):
        if self.end_location_wkt is None:
            return None

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

    @end_location.setter
    def end_location(self, location):
        if location is None:
            self.end_location_wkt = None
        else:
            self.end_location_wkt = location.to_wkt_element()
示例#24
0
class User(db.Model):
    """
    User definition.
    """

    __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), nullable=False)

    # 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, delay in minutes and other settings

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

    tracking_callsign = db.Column(Unicode(5))

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

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

    def __init__(self, *args, **kw):
        self.generate_tracking_key()
        super(User, self).__init__(*args, **kw)

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

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

    def __unicode__(self):
        return self.name

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

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

    @staticmethod
    def by_credentials(email, password, *args, **kwargs):
        """
        Return the user object whose email address is ``email`` if the
        password is matching.
        """
        user = User.by_email_address(email)
        if user and user.validate_password(password):
            return user

    @staticmethod
    def by_tracking_key(key):
        return User.query(tracking_key=key).first()

    @staticmethod
    def by_recover_key(key):
        return User.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 + u' ' + self.last_name

    @name.expression
    def name_expression(cls):
        return case([
            (cls.last_name != None, cls.first_name + u' ' + 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)

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

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

    @password.setter
    def password(self, password):
        """Hash ``password`` on the fly and store its hashed version."""
        self._password = self._hash_password(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 or not 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)

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

    @hybrid_method
    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)

    @property
    def num_followers(self):
        from skylines.model.follower import Follower
        return Follower.query(destination=self).count()

    @property
    def num_following(self):
        from skylines.model.follower import Follower
        return Follower.query(source=self).count()

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

    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(Flight.pilot == self)
示例#25
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)
    time_modified = db.Column(DateTime,
                              nullable=False,
                              default=datetime.utcnow)
    filename = db.Column(String(), nullable=False)
    is_condor_file = db.Column(db.Boolean, default=False)
    landscape = db.Column(String(32), nullable=False)
    flight_plan_md5 = db.Column(String(32), 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)
    date_condor = db.Column(Date, nullable=False)

    def __repr__(self):
        return unicode_to_str("<IGCFile: id=%d filename='%s'>" %
                              (self.id, self.filename))

    @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)
        condor_fpl, landscape = read_condor_fpl(path)
        if igc_headers is None:
            return

        if len(condor_fpl) > 0:
            self.is_condor_file = True
            self.landscape = landscape
            self.flight_plan_md5 = file_md5(
                StringIO.StringIO('\n'.join(condor_fpl)))

        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 get_model(self):
        from skylines.model import AircraftModel

        if self.model is not None:
            result = (AircraftModel.query().filter(
                AircraftModel.name == self.model))
            if result.first():
                return result.first().id
        # nothing found
        return None