class Notification(db.Model):
__tablename__ = 'notifications'
id = db.Column(Integer, autoincrement=True, primary_key=True)
# The event of this notification
event_id = db.Column(Integer,
db.ForeignKey('events.id', ondelete='CASCADE'),
nullable=False)
event = db.relationship('Event', innerjoin=True)
# The recipient of this notification
recipient_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='CASCADE'),
nullable=False)
recipient = db.relationship('User', innerjoin=True)
# The time that this notification was read by the recipient
time_read = db.Column(DateTime)
##############################
def __repr__(self):
return '<Notification: id={}>' \
.format(self.id).encode('unicode_escape')
##############################
@classmethod
def query_unread(cls, recipient):
return cls.query(recipient=recipient, time_read=None)
@classmethod
def count_unread(cls, recipient):
return cls.query_unread(recipient).count()
##############################
def mark_read(self):
self.time_read = datetime.utcnow()
@classmethod
def mark_all_read(cls, recipient, filter_func=None):
query = cls.query(recipient=recipient) \
.outerjoin(Event) \
.filter(Event.id == Notification.event_id)
if filter_func is not None:
query = filter_func(query)
query.update(dict(time_read=datetime.utcnow()))
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 Elevation(db.Model):
__tablename__ = 'elevations'
rid = db.Column(Integer, autoincrement=True, primary_key=True)
rast = db.Column(Raster)
@classmethod
def get(cls, location):
"""
Returns the elevation at the given location or None.
location should be WKBElement or WKTElement.
"""
elevation = cls.rast.ST_Value(location)
query = db.session.query(elevation.label('elevation')) \
.filter(location.ST_Intersects(cls.rast)) \
.filter(elevation != None)
return query.scalar()
class TimeZone(db.Model):
__tablename__ = 'tz_world'
id = db.Column('gid', Integer, autoincrement=True, primary_key=True)
tzid = db.Column(String(30))
the_geom = db.Column(Geometry('MULTIPOLYGON'))
def __unicode__(self):
return self.tzid
def __repr__(self):
return ('<TimeZone: id=%d tzid=\'%s\'>' % (self.id, self.tzid)).encode('unicode_escape')
@classmethod
def by_location(cls, location):
location = location.make_point(srid=None)
filter = db.func.ST_Contains(cls.the_geom, location)
zone = db.session.query(cls.tzid).filter(filter).scalar()
if zone is None:
return None
return timezone(unicode(zone))
class Trace(db.Model):
"""
This table saves the locations and visiting times of the turnpoints
of an optimized Flight.
"""
__tablename__ = 'traces'
id = db.Column(Integer, autoincrement=True, primary_key=True)
flight_id = db.Column(Integer,
db.ForeignKey('flights.id', ondelete='CASCADE'),
nullable=False)
flight = db.relationship('Flight',
innerjoin=True,
backref=db.backref('traces',
passive_deletes=True))
contest_type = db.Column(String, nullable=False)
trace_type = db.Column(String, nullable=False)
distance = db.Column(Integer)
duration = db.Column(Interval)
times = db.Column(postgresql.ARRAY(DateTime), nullable=False)
_locations = db.Column('locations', Geometry('LINESTRING'), nullable=False)
@property
def speed(self):
if self.distance is None or self.duration is None:
return None
return float(self.distance) / self.duration.total_seconds()
@property
def locations(self):
return [
Location(longitude=location[0], latitude=location[1])
for location in to_shape(self._locations).coords
]
@locations.setter
def locations(self, locations):
points = [
'{} {}'.format(location.longitude, location.latitude)
for location in locations
]
wkt = "LINESTRING({})".format(','.join(points))
self._locations = WKTElement(wkt, srid=4326)
class Follower(db.Model):
__tablename__ = 'followers'
__table_args__ = (
db.UniqueConstraint('source_id', 'destination_id', name='unique_connection'),
)
id = db.Column(Integer, autoincrement=True, primary_key=True)
source_id = db.Column(
Integer, db.ForeignKey('users.id', ondelete='CASCADE'),
index=True, nullable=False)
source = db.relationship(
'User', foreign_keys=[source_id],
lazy='joined', backref='following')
destination_id = db.Column(
Integer, db.ForeignKey('users.id', ondelete='CASCADE'),
index=True, nullable=False)
destination = db.relationship(
'User', foreign_keys=[destination_id],
lazy='joined', backref='followers')
time = db.Column(DateTime, nullable=False, default=datetime.utcnow)
@classmethod
def follows(cls, source, destination):
return cls.query(source=source, destination=destination).count() > 0
@classmethod
def follow(cls, source, destination):
f = cls.query(source=source, destination=destination).first()
if not f:
f = Follower(source=source, destination=destination)
db.session.add(f)
@classmethod
def unfollow(cls, source, destination):
cls.query(source=source, destination=destination).delete()
class Airspace(db.Model):
__tablename__ = 'airspace'
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)
the_geom = db.Column(Geometry('POLYGON'))
name = db.Column(String(), nullable=False)
airspace_class = db.Column(String(3), nullable=False)
base = db.Column(String(30), nullable=False)
top = db.Column(String(30), nullable=False)
country_code = db.Column(String(2), nullable=False)
def __repr__(self):
return ('<Airspace: id=%d name=\'%s\'>' % (self.id, self.name)).encode('unicode_escape')
@classmethod
def by_location(cls, location):
'''Returns a query object of all airspaces at the location'''
return cls.query() \
.filter(cls.the_geom.ST_Contains(location.make_point()))
class FlightPhase(db.Model):
__tablename__ = 'flight_phases'
# Flight phase types
PT_POWERED = 1
PT_CRUISE = 2
PT_CIRCLING = 3
# Circling directions
CD_LEFT = 1
CD_MIXED = 2
CD_RIGHT = 3
CD_TOTAL = 4
id = db.Column(Integer, primary_key=True, autoincrement=True)
flight_id = db.Column(Integer,
db.ForeignKey('flights.id', ondelete='CASCADE'),
nullable=False,
index=True)
start_time = db.Column(DateTime)
end_time = db.Column(DateTime)
flight = db.relationship('Flight',
innerjoin=True,
backref=db.backref('_phases',
order_by=start_time,
passive_deletes=True))
aggregate = db.Column(Boolean, nullable=False)
phase_type = db.Column(Integer) # see PT_* constants
circling_direction = db.Column(Integer) # see CD_* constants
alt_diff = db.Column(Integer)
duration = db.Column(Interval)
fraction = db.Column(Integer)
distance = db.Column(Integer)
speed = db.Column(Float)
vario = db.Column(Float)
glide_rate = db.Column(Float)
count = db.Column(Integer, nullable=False)
class User(db.Model):
"""
User definition.
This is the user definition used by :mod:`repoze.who`, which requires at
least the ``user_name`` column.
"""
__tablename__ = 'users'
__searchable_columns__ = ['name']
id = db.Column(Integer, autoincrement=True, primary_key=True)
# eMail address and name of the user
email_address = db.column_property(db.Column(Unicode(255)),
comparator_factory=LowerCaseComparator)
first_name = db.Column(Unicode(255), nullable=False)
last_name = db.Column(Unicode(255))
# Hashed password
_password = db.Column('password', Unicode(128))
# The user's club (optional)
club_id = db.Column(Integer, db.ForeignKey('clubs.id',
ondelete='SET NULL'))
club = db.relationship('Club', foreign_keys=[club_id], backref='members')
# Tracking key and delay in minutes
tracking_key = db.Column(BigInteger, index=True)
tracking_delay = db.Column(SmallInteger, nullable=False, default=0)
# Time and IP of creation
created = db.Column(DateTime, default=datetime.utcnow)
created_ip = db.Column(INET)
# Time and IP of the last login
login_time = db.Column(DateTime)
login_ip = db.Column(INET)
# Password recovery information
recover_key = db.Column(Integer)
recover_time = db.Column(DateTime)
recover_ip = db.Column(INET)
# Units settings
distance_unit = db.Column(SmallInteger, nullable=False, default=1)
speed_unit = db.Column(SmallInteger, nullable=False, default=1)
lift_unit = db.Column(SmallInteger, nullable=False, default=0)
altitude_unit = db.Column(SmallInteger, nullable=False, default=0)
# Other settings
admin = db.Column(Boolean, nullable=False, default=False)
eye_candy = db.Column(Boolean, nullable=False, default=False)
##############################
def __repr__(self):
return ('<User: email=%s, display=%s>' %
(self.email_address, self.name)).encode('unicode_escape')
def __unicode__(self):
return self.name
##############################
@classmethod
def by_email_address(cls, email):
"""Return the user object whose email address is ``email``."""
return cls.query(email_address=email).first()
@classmethod
def by_credentials(cls, email, password):
"""
Return the user object whose email address is ``email`` if the
password is matching.
"""
user = cls.query(email_address=email).first()
if user and user.validate_password(password):
return user
@classmethod
def by_tracking_key(cls, key):
return cls.query(tracking_key=key).first()
@classmethod
def by_recover_key(cls, key):
return cls.query(recover_key=key).first()
## Flask Login ###############
def is_active(self):
return True
def is_authenticated(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return unicode(self.id)
##############################
@hybrid_property
def name(self):
if not self.last_name:
return self.first_name
return self.first_name + ' ' + self.last_name
@name.expression
def name_expression(cls):
return case([
(cls.last_name != None, cls.first_name + ' ' + cls.last_name),
],
else_=cls.first_name)
def initials(self):
parts = self.name.split()
initials = [p[0].upper() for p in parts if len(p) > 2 and '.' not in p]
return ''.join(initials)
##############################
def _set_password(self, password):
"""Hash ``password`` on the fly and store its hashed version."""
self._password = self._hash_password(password)
def _get_password(self):
"""Return the hashed version of the password."""
return self._password
password = db.synonym('_password',
descriptor=property(_get_password, _set_password))
@classmethod
def _hash_password(cls, password):
# Make sure password is a str because we cannot hash unicode objects
if isinstance(password, unicode):
password = password.encode('utf-8')
salt = sha256()
salt.update(os.urandom(60))
hash = sha256()
hash.update(password + salt.hexdigest())
password = salt.hexdigest() + hash.hexdigest()
# Make sure the hashed password is a unicode object at the end of the
# process because SQLAlchemy _wants_ unicode objects for Unicode cols
if not isinstance(password, unicode):
password = password.decode('utf-8')
return password
def validate_password(self, password):
"""
Check the password against existing credentials.
:param password: the password that was provided by the user to
try and authenticate. This is the clear text version that we will
need to match against the hashed one in the database.
:type password: unicode object.
:return: Whether the password is valid.
:rtype: bool
"""
# Make sure accounts without a password can't log in
if not self.password:
return False
hash = sha256()
if isinstance(password, unicode):
password = password.encode('utf-8')
hash.update(password + str(self.password[:64]))
return self.password[64:] == hash.hexdigest()
##############################
def generate_tracking_key(self):
self.tracking_key = struct.unpack('I', os.urandom(4))[0]
@property
def tracking_key_hex(self):
if self.tracking_key is None:
return None
return '%X' % self.tracking_key
@classmethod
def tracking_delay_interval(cls):
return cast(cast(cls.tracking_delay, String) + ' minutes', Interval)
##############################
def is_manager(self):
return self.admin
##############################
def generate_recover_key(self, ip):
self.recover_key = struct.unpack('I', os.urandom(4))[0] & 0x7fffffff
self.recover_time = datetime.utcnow()
self.recover_ip = ip
return self.recover_key
##############################
def is_readable(self, user):
"""Does the current user have full read access to this object?"""
return self.is_writable(user)
def is_writable(self, user):
return user and \
(self.id == user.id or
(self.password is None and self.club_id == user.club_id) or
user.is_manager())
##############################
def follows(self, other):
assert isinstance(other, User)
from skylines.model.follower import Follower
return Follower.follows(self, other)
##############################
def get_largest_flights(self):
'''
Returns a query with all flights by the user
as pilot ordered by distance
'''
from skylines.model.flight import Flight
return Flight.get_largest().filter_by(pilot=self)
##############################
@property
def unit_preset(self):
"""Calculate unit preset based on user unit preference.
If all user unit settings exactly matches one of the preset, return
that preset id. Otherwise return 0, that is interpreted as 'Custom'
preset.
"""
for pref, preset in enumerate(units.UNIT_PRESETS):
p = preset[1]
if not p:
continue
eq = [
p['distance_unit'] == units.DISTANCE_UNITS[self.distance_unit]
[0], p['speed_unit'] == units.SPEED_UNITS[self.speed_unit][0],
p['lift_unit'] == units.LIFT_UNITS[self.lift_unit][0],
p['altitude_unit'] == units.ALTITUDE_UNITS[
self.altitude_unit][0]
]
if all(eq):
return pref
return 0
@unit_preset.setter
def unit_preset(self, preset):
"""Set individual unit preferences according to given preset
"""
name, settings = units.UNIT_PRESETS[preset]
if settings:
self.distance_unit = units.unitid(units.DISTANCE_UNITS,
settings['distance_unit'])
self.speed_unit = units.unitid(units.SPEED_UNITS,
settings['speed_unit'])
self.lift_unit = units.unitid(units.LIFT_UNITS,
settings['lift_unit'])
self.altitude_unit = units.unitid(units.ALTITUDE_UNITS,
settings['altitude_unit'])
class Flight(db.Model):
__tablename__ = 'flights'
id = db.Column(Integer, autoincrement=True, primary_key=True)
time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
time_modified = db.Column(DateTime,
nullable=False,
default=datetime.utcnow)
pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='SET NULL'),
index=True)
pilot = db.relationship('User', foreign_keys=[pilot_id])
co_pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='SET NULL'),
index=True)
co_pilot = db.relationship('User', foreign_keys=[co_pilot_id])
club_id = db.Column(Integer,
db.ForeignKey('clubs.id', ondelete='SET NULL'),
index=True)
club = db.relationship('Club', backref='flights')
model_id = db.Column(Integer,
db.ForeignKey('models.id', ondelete='SET NULL'))
model = db.relationship('AircraftModel')
registration = db.Column(Unicode(32))
competition_id = db.Column(Unicode(5))
# The date of the flight in local time instead of UTC. Used for scoring.
date_local = db.Column(Date, nullable=False, index=True)
takeoff_time = db.Column(DateTime, nullable=False, index=True)
landing_time = db.Column(DateTime, nullable=False)
takeoff_location_wkt = db.Column('takeoff_location', Geometry('POINT'))
landing_location_wkt = db.Column('landing_location', Geometry('POINT'))
takeoff_airport_id = db.Column(
Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
takeoff_airport = db.relationship('Airport',
foreign_keys=[takeoff_airport_id])
landing_airport_id = db.Column(
Integer, db.ForeignKey('airports.id', ondelete='SET NULL'))
landing_airport = db.relationship('Airport',
foreign_keys=[landing_airport_id])
timestamps = deferred(db.Column(postgresql.ARRAY(DateTime),
nullable=False),
group='path')
locations = deferred(db.Column(Geometry('LINESTRING', srid=4326),
nullable=False),
group='path')
olc_classic_distance = db.Column(Integer)
olc_triangle_distance = db.Column(Integer)
olc_plus_score = db.Column(Float)
igc_file_id = db.Column(Integer,
db.ForeignKey('igc_files.id', ondelete='CASCADE'),
nullable=False)
igc_file = db.relationship('IGCFile', backref='flights', innerjoin=True)
needs_analysis = db.Column(Boolean, nullable=False, default=True)
def __repr__(self):
return ('<Flight: id=%d>' % self.id).encode('unicode_escape')
@hybrid_property
def duration(self):
return self.landing_time - self.takeoff_time
@hybrid_property
def year(self):
return self.date_local.year
@hybrid_property
def index_score(self):
if self.model and self.model.dmst_index > 0:
return self.olc_plus_score * 100 / self.model.dmst_index
else:
return self.olc_plus_score
@index_score.expression
def index_score(cls):
return case([(AircraftModel.dmst_index > 0,
cls.olc_plus_score * 100 / AircraftModel.dmst_index)],
else_=cls.olc_plus_score)
@year.expression
def year(cls):
return db.func.date_part('year', cls.date_local)
@property
def takeoff_location(self):
if self.takeoff_location_wkt is None:
return None
coords = to_shape(self.takeoff_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@takeoff_location.setter
def takeoff_location(self, location):
if location is None:
self.takeoff_location_wkt = None
else:
self.takeoff_location_wkt = location.to_wkt_element()
@property
def landing_location(self):
if self.landing_location_wkt is None:
return None
coords = to_shape(self.landing_location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
@landing_location.setter
def landing_location(self, location):
if location is None:
self.landing_location_wkt = None
else:
self.landing_location_wkt = location.to_wkt_element()
@classmethod
def by_md5(cls, _md5):
file = IGCFile.by_md5(_md5)
if file is None:
return None
return cls.query().filter_by(igc_file=file).first()
def is_writable(self, user):
return user and \
(self.igc_file.owner_id == user.id or
self.pilot_id == user.id or
user.is_manager())
def may_delete(self, user):
return user and (self.igc_file.owner_id == user.id
or user.is_manager())
@classmethod
def get_largest(cls):
'''Returns a query object ordered by distance'''
return cls.query().order_by(cls.olc_classic_distance.desc())
def get_optimised_contest_trace(self, contest_type, trace_type):
from skylines.model.trace import Trace
return Trace.query(contest_type=contest_type,
trace_type=trace_type,
flight=self).first()
def get_contest_speed(self, contest_type, trace_type):
contest = self.get_optimised_contest_trace(contest_type, trace_type)
return contest and contest.speed
@property
def speed(self):
return self.get_contest_speed('olc_plus', 'classic')
@property
def has_phases(self):
return bool(self._phases)
@property
def phases(self):
return [p for p in self._phases if not p.aggregate]
def delete_phases(self):
from skylines.model.flight_phase import FlightPhase
FlightPhase.query(flight=self).delete()
@property
def circling_performance(self):
from skylines.model.flight_phase import FlightPhase
stats = [
p for p in self._phases
if (p.aggregate and p.phase_type == FlightPhase.PT_CIRCLING
and p.duration.total_seconds() > 0)
]
order = [
FlightPhase.CD_TOTAL, FlightPhase.CD_LEFT, FlightPhase.CD_RIGHT,
FlightPhase.CD_MIXED
]
stats.sort(lambda a, b: cmp(order.index(a.circling_direction),
order.index(b.circling_direction)))
return stats
@property
def cruise_performance(self):
from skylines.model.flight_phase import FlightPhase
return [
p for p in self._phases
if p.aggregate and p.phase_type == FlightPhase.PT_CRUISE
]
def update_flight_path(self):
from skylines.lib.xcsoar_ import flight_path
from skylines.lib.datetime import from_seconds_of_day
# Run the IGC file through the FlightPath utility
path = flight_path(self.igc_file)
if len(path) < 2:
return False
# Save the timestamps of the coordinates
date_utc = self.igc_file.date_utc
self.timestamps = \
[from_seconds_of_day(date_utc, c.seconds_of_day) for c in path]
# Convert the coordinate into a list of tuples
coordinates = [(c.longitude, c.latitude) for c in path]
# Create a shapely LineString object from the coordinates
linestring = LineString(coordinates)
# Save the new path as WKB
self.locations = from_shape(linestring, srid=4326)
return True
class IGCFile(db.Model):
__tablename__ = 'igc_files'
id = db.Column(Integer, autoincrement=True, primary_key=True)
owner_id = db.Column(Integer, db.ForeignKey('users.id'), nullable=False)
owner = db.relationship('User', innerjoin=True)
time_created = db.Column(DateTime, nullable=False, default=datetime.utcnow)
filename = db.Column(String(), nullable=False)
md5 = db.Column(String(32), nullable=False, unique=True)
logger_id = db.Column(String(3))
logger_manufacturer_id = db.Column(String(3))
registration = db.Column(Unicode(32))
competition_id = db.Column(Unicode(5))
model = db.Column(Unicode(64))
date_utc = db.Column(Date, nullable=False)
def __repr__(self):
return ('<IGCFile: id=%d filename=\'%s\'>' %
(self.id, self.filename)).encode('unicode_escape')
@classmethod
def by_md5(cls, _md5):
return cls.query(md5=_md5).first()
def is_writable(self, user):
return user and \
(self.owner_id == user.id or
self.pilot_id == user.id or
user.is_manager())
def may_delete(self, user):
return user and user.is_manager()
def update_igc_headers(self):
path = files.filename_to_path(self.filename)
igc_headers = read_igc_headers(path)
if igc_headers is None:
return
if 'manufacturer_id' in igc_headers:
self.logger_manufacturer_id = igc_headers['manufacturer_id']
if 'logger_id' in igc_headers:
self.logger_id = igc_headers['logger_id']
if 'date_utc' in igc_headers:
self.date_utc = igc_headers['date_utc']
if 'model' in igc_headers and (igc_headers['model'] is None
or 0 < len(igc_headers['model']) < 64):
self.model = igc_headers['model']
if 'reg' in igc_headers and (igc_headers['reg'] is None
or 0 < len(igc_headers['reg']) < 32):
self.registration = igc_headers['reg']
if 'cid' in igc_headers and (igc_headers['cid'] is None
or 0 < len(igc_headers['cid']) < 5):
self.competition_id = igc_headers['cid']
def guess_registration(self):
from skylines.model.flight import Flight
# try to find another flight with the same logger and use it's aircraft registration
if (self.logger_id is not None
and self.logger_manufacturer_id is not None):
logger_id = self.logger_id
logger_manufacturer_id = self.logger_manufacturer_id
result = Flight.query().join(IGCFile) \
.filter(db.func.upper(IGCFile.logger_manufacturer_id) == db.func.upper(logger_manufacturer_id)) \
.filter(db.func.upper(IGCFile.logger_id) == db.func.upper(logger_id)) \
.filter(Flight.registration == None) \
.order_by(Flight.id.desc())
if self.logger_manufacturer_id.startswith('X'):
result = result.filter(Flight.pilot == self.owner)
result = result.first()
if result and result.registration:
return result.registration
return None
def guess_model(self):
from skylines.model import Flight, AircraftModel
# first try to find the reg number in the database
if self.registration is not None:
glider_reg = self.registration
result = Flight.query() \
.filter(db.func.upper(Flight.registration) == db.func.upper(glider_reg)) \
.order_by(Flight.id.desc()) \
.first()
if result and result.model_id:
return result.model_id
# try to find another flight with the same logger and use it's aircraft type
if (self.logger_id is not None
and self.logger_manufacturer_id is not None):
logger_id = self.logger_id
logger_manufacturer_id = self.logger_manufacturer_id
result = Flight.query().join(IGCFile) \
.filter(db.func.upper(IGCFile.logger_manufacturer_id) == db.func.upper(logger_manufacturer_id)) \
.filter(db.func.upper(IGCFile.logger_id) == db.func.upper(logger_id)) \
.filter(Flight.model_id == None) \
.order_by(Flight.id.desc())
if self.logger_manufacturer_id.startswith('X'):
result = result.filter(Flight.pilot == self.owner)
result = result.first()
if result and result.model_id:
return result.model_id
if self.model is not None:
glider_type = self.model.lower()
# otherwise, try to guess the glider model by the glider type igc header
text_fragments = [
'%{}%'.format(v)
for v in re.sub(r'[^a-z]', ' ', glider_type).split()
]
digit_fragments = [
'%{}%'.format(v)
for v in re.sub(r'[^0-9]', ' ', glider_type).split()
]
if not text_fragments and not digit_fragments:
return None
glider_type_clean = re.sub(r'[^a-z0-9]', '', glider_type)
result = AircraftModel.query() \
.filter(and_(
db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^a-z]', ' ').like(db.func.any(text_fragments)),
db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^0-9]', ' ').like(db.func.all(digit_fragments)))) \
.order_by(db.func.levenshtein(db.func.regexp_replace(db.func.lower(AircraftModel.name), '[^a-z0-9]', ''), glider_type_clean))
if result.first():
return result.first().id
# nothing found
return None
class TrackingFix(db.Model):
__tablename__ = 'tracking_fixes'
id = db.Column(Integer, autoincrement=True, primary_key=True)
time = db.Column(DateTime, nullable=False, default=datetime.utcnow)
location_wkt = db.Column('location', Geometry('POINT'))
track = db.Column(SmallInteger)
ground_speed = db.Column(REAL)
airspeed = db.Column(REAL)
altitude = db.Column(SmallInteger)
elevation = db.Column(SmallInteger)
vario = db.Column(REAL)
engine_noise_level = db.Column(SmallInteger)
pilot_id = db.Column(Integer,
db.ForeignKey('users.id', ondelete='CASCADE'),
nullable=False)
pilot = db.relationship('User', innerjoin=True)
ip = db.Column(INET)
def __repr__(self):
return '<TrackingFix: id={} time=\'{}\'>' \
.format(self.id, self.time).encode('unicode_escape')
@property
def location(self):
if self.location_wkt is None:
return None
coords = to_shape(self.location_wkt)
return Location(latitude=coords.y, longitude=coords.x)
def set_location(self, longitude, latitude):
self.location_wkt = from_shape(Point(longitude, latitude), srid=4326)
@property
def altitude_agl(self):
if not self.elevation:
raise ValueError('This TrackingFix has no elevation.')
return max(0, self.altitude - self.elevation)
@classmethod
def max_age_filter(cls, max_age):
"""
Returns a filter that makes sure that the fix is not older than a
certain time.
The delay parameter can be either a datetime.timedelta or a numeric
value that will be interpreted as hours.
"""
if isinstance(max_age, (int, long, float)):
max_age = timedelta(hours=max_age)
return cls.time >= datetime.utcnow() - max_age
@classmethod
def delay_filter(cls, delay):
"""
Returns a filter that makes sure that the fix was created at least
a certain time ago.
The delay parameter can be either a datetime.timedelta or a numeric
value that will be interpreted as minutes.
"""
if isinstance(delay, (int, long, float)):
delay = timedelta(minutes=delay)
return cls.time <= datetime.utcnow() - delay
@classmethod
def get_latest(cls, max_age=timedelta(hours=6)):
# Add a db.Column to the inner query with
# numbers ordered by time for each pilot
row_number = db.over(db.func.row_number(),
partition_by=cls.pilot_id,
order_by=cls.time.desc())
# Create inner query
subq = db.session \
.query(cls.id, row_number.label('row_number')) \
.join(cls.pilot) \
.filter(cls.max_age_filter(max_age)) \
.filter(cls.delay_filter(User.tracking_delay_interval())) \
.filter(cls.location_wkt != None) \
.subquery()
# Create outer query that orders by time and
# only selects the latest fix
query = cls.query() \
.options(db.joinedload(cls.pilot)) \
.filter(cls.id == subq.c.id) \
.filter(subq.c.row_number == 1) \
.order_by(cls.time.desc())
return query
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 MountainWaveProject(db.Model):
__tablename__ = 'mountain_wave_project'
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)
location = db.Column(Geometry('POINT', srid=4326))
axis = db.Column(Geometry('LINESTRING', srid=4326))
ellipse = db.Column(Geometry('LINESTRING', srid=4326))
name = db.Column(String())
country_code = db.Column(String(2))
vertical = db.Column(Float)
synoptical = db.Column(String(254))
main_wind_direction = db.Column(String(254))
additional = db.Column(String(254))
source = db.Column(String(254))
remark1 = db.Column(String(254))
remark2 = db.Column(String(254))
orientation = db.Column(Float)
rotor_height = db.Column(String(254))
weather_dir = db.Column(Integer)
axis_length = db.Column(Float)
def __repr__(self):
return ('<MountainWaveProject: id=%d name=\'%s\'>' %
(self.id, self.name)).encode('unicode_escape')
@classmethod
def by_location(cls, location):
'''Returns a query object of mountain waves around the location'''
return cls.query() \
.filter(db.func.ST_DWithin(
cast(location.make_point(), Geography),
cast(cls.location, Geography),
5000))