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