class UserRoles(db.Model):
__tablename__ = "user_roles"
id = db.Column(db.Integer(), primary_key=True)
user_id = db.Column(UUID(as_uuid=True),
db.ForeignKey("users.id", ondelete="CASCADE"))
role_id = db.Column(db.Integer(),
db.ForeignKey("roles.id", ondelete="CASCADE"))
class CurrencyPair(db.Model, BaseModel):
__tablename__ = 'currency_pair'
symbol = db.Column(db.String(15), unique=True, nullable=False)
from_currency_id = db.Column(db.Integer,
db.ForeignKey('currency.id'),
nullable=False)
from_currency = db.relationship("Currency",
foreign_keys=[from_currency_id],
lazy='subquery')
to_currency_id = db.Column(db.Integer,
db.ForeignKey('currency.id'),
nullable=False)
to_currency = db.relationship("Currency",
foreign_keys=[to_currency_id],
lazy='subquery')
@classmethod
def get_by_symbol(cls, symbol):
currency_pair = cls.query.filter_by(symbol=symbol).first()
if not currency_pair:
raise NoCurrencyPairBySymbolException(symbol)
return currency_pair
def __repr__(self):
return self.symbol
def get_market_repr(self, market_name):
if market_name == "Binance":
return self.binance_repr
elif market_name == "Bitmex":
return self.bitmex_repr
return self.symbol
def get_market_order_repr(self, market_name):
if market_name == "Binance":
return self.binance_order_repr
elif market_name == "Bitmex":
return self.bitmex_repr
return self.symbol
@property
def bitmex_repr(self):
return "{}/{}".format(self.to_currency.short_name.upper(),
self.from_currency.short_name.upper())
@property
def binance_repr(self):
return "{}{}".format(self.to_currency.short_name.upper(),
self.from_currency.short_name.upper())
@property
def binance_order_repr(self):
return "{}/{}".format(self.to_currency.short_name.upper(),
self.from_currency.short_name.upper())
class Post(ResourceMixin, db.Model):
__tablename__ = 'posts'
# Relationships
comments = db.relationship('Comment',
backref=db.backref('post', uselist=False),
passive_deletes=True,
lazy='dynamic')
# Properties
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), index=True, unique=True, nullable=False)
body = db.Column(db.ARRAY(db.String), nullable=False)
summary = db.Column(db.String(250), nullable=False)
img_src = db.Column(db.String(200))
thumbnail_src = db.Column(db.String(200))
view_count = db.Column(db.Integer, server_default='0')
# Foreign Keys
author_id = db.Column(db.Integer, db.ForeignKey('staff.id'))
practice_area_id = db.Column(db.Integer,
db.ForeignKey('practice_areas.id'))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Post, self).__init__(**kwargs)
@hybrid_property
def slug(self):
return slugify(self.title)
@classmethod
def find_by_title(cls, title):
"""
Find client by user ID.
:param user_id: user ID
:type title: str
:return: Client instance
"""
return cls.query.filter(cls.title == title).first()
def to_json(self):
return {
'id': self.id,
'title': self.title,
'author': self.author.user.first_last_name,
'body': self.body,
'summary': self.summary,
'practiceArea': self.practice_area.area,
'imgSrc': self.img_src,
'thumbnailSrc': self.thumbnail_src,
'created': self.created_on,
'updated': self.updated_on,
'views': self.view_count,
'authorPhoto': self.author.user.photo,
'slug': self.slug
}
class TouristerImage(db.Model):
""" used to associate many to many images to touristers """
__tablename__ = 'tourister_images'
__table_args__ = (db.PrimaryKeyConstraint('tourister_id', 'image_id'), )
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
image_id = db.Column(db.Integer, db.ForeignKey('image.id'), nullable=False)
class SnapshotModel(BaseModel):
__tablename__ = 'snapshot'
#:Primary key of the Model
id = db.Column(db.Integer, primary_key=True)
#:Metadata for IAP
measurement_tool = db.Column(db.String)
#:The ID of the phenobox which was used to create this snapshot
phenobox_id = db.Column(UUID(as_uuid=True), nullable=False)
#:Metadata for IAP
camera_position = db.Column(
ENUM('vis.side', 'vis.top', 'fluo.top', 'fluo.side', 'nir.top', 'nir.side', 'ir.top', 'ir.side',
name='camera_position_enum'), nullable=False,
server_default='vis.side')
#:Flag used to exclude this snapshot from analysis
excluded = db.Column(db.Boolean, server_default='f', default=False, nullable=False)
#:Foreign key to the corresponding Timestamp
timestamp_id = db.Column(db.Integer, db.ForeignKey('timestamp.id'), nullable=False)
#:SQLAlchemy relationship to the corresponding Timestamp
timestamp = db.relationship("TimestampModel", back_populates="snapshots", single_parent=True)
#:Foreign key to the corresponding Timestamp
plant_id = db.Column(db.Integer, db.ForeignKey('plant.id'), nullable=False)
#:SQLAlchemy relationship to the corresponding Plant
plant = db.relationship("PlantModel", back_populates="snapshots", single_parent=True)
#:SQLAlchemy relationship to all images belonging to this snapshot
images = db.relationship("ImageModel", back_populates="snapshot", cascade="all, delete-orphan", lazy='dynamic')
#:SQLAlchemy relationship to all analyses performed on this snapshot
postprocesses = db.relationship("PostprocessModel", secondary='postprocess_snapshot', back_populates='snapshots')
db.UniqueConstraint(plant_id, timestamp_id, name=u'uq_snapshot_plant_id_timestamp_id')
def purge(self):
# Only allow to delete a snapshot from an uncompleted timestamp
if not self.timestamp.completed:
shared_folder_map = current_app.config['SHARED_FOLDER_MAP']
raw_image_path = None
for image in self.images:
if image.type == 'raw':
if raw_image_path is None:
raw_image_path = get_local_path_from_smb(image.path, shared_folder_map)
os.remove(os.path.join(raw_image_path, image.filename))
db.session.delete(image)
db.session.delete(self)
db.session.commit()
return True
else:
return False # TODO throw exceptions instead of returning true or false
def __init__(self, plant_id, timestamp_id, camera_position, measurement_tool, phenobox_id):
self.plant_id = plant_id
self.timestamp_id = timestamp_id
self.camera_position = camera_position
self.measurement_tool = measurement_tool
self.phenobox_id = phenobox_id
def __repr__(self):
return '<Snapshot %d of plant %r>' % (self.id, self.plant.name)
class Testimonial(Base):
""" testimonials given by touristees """
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
user = db.relationship("User", uselist=False)
story = db.Column(db.String(800), nullable=False)
class PortfolioStocks(db.Model):
__tablename__ = "portfolio_stocks"
portfolio_id = db.Column(
db.Integer(),
db.ForeignKey("portfolio.id", ondelete="CASCADE"),
primary_key=True,
)
stock_id = db.Column(db.Integer(),
db.ForeignKey("stocks.id", ondelete="CASCADE"),
primary_key=True)
class Activity(Base):
""" activities a tourister has done """
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
image_id = db.Column(db.Integer, db.ForeignKey('image.id'), nullable=False)
detail = db.Column(db.String(300), nullable=False)
image = db.relationship("Image", uselist=False)
class SolvedChallenges(db.Model):
__tablename__ = 'solved_challenges'
user_id = db.Column(db.String(255),
db.ForeignKey('user.uuid'),
primary_key=True)
challenge_id = db.Column(db.String(255),
db.ForeignKey('challenge.identifier'),
primary_key=True)
time_solved = db.Column(db.DateTime,
nullable=False,
default=datetime.datetime.now)
user = relationship("User", back_populates="solved_challenges")
solved_challenge = relationship("Challenge", back_populates="users")
class Collect(db.Model):
collector_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True)
collected_id = db.Column(db.Integer,
db.ForeignKey('photo.id'),
primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
collector = db.relationship('User',
back_populates='collections',
lazy='joined')
collected = db.relationship('Photo',
back_populates='collectors',
lazy='joined')
class ImageModel(BaseModel):
__tablename__ = 'image'
#:Primary key of the Model
id = db.Column(db.Integer, primary_key=True)
#:The path, as SMB URL, to the image file
path = db.Column(db.String, nullable=False)
#:The filename of the image
filename = db.Column(db.String, nullable=False)
#:The type of the image. Indicates whether this image was processed or not
type = db.Column(ENUM('raw', 'segmented', name='image_type_enum'),
default="raw",
server_default='raw',
nullable=False)
#:The angle at which the image was taken
angle = db.Column(db.Integer, nullable=False)
#:Foreign key to the corresponding Snapshot
snapshot_id = db.Column(db.Integer,
db.ForeignKey('snapshot.id'),
nullable=False)
#:SQLAlchemy relationship to the corresponding Snapshot
snapshot = db.relationship("SnapshotModel",
back_populates="images",
single_parent=True)
# TODO add unique constraint for snapshot_id and path+filename
def __init__(self, snapshot_id, path, filename, angle, image_type):
self.snapshot_id = snapshot_id
self.path = path
self.filename = filename
self.type = image_type
self.angle = angle
def __repr__(self):
return '<Image %d of plant %r>' % (self.id, self.filename)
class FinalFeedback(db.Model, CRUDMixin):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
motivation = db.Column(db.Enum(MotivationFeedback), nullable=False)
user_uuid = db.Column(db.String(255), db.ForeignKey('user.uuid'))
user = relationship("User", back_populates="feedback")
class Transaction(BaseExchangeMixin, db.Model, BaseModel):
__tablename__ = 'transactions'
price = db.Column(db.Float, nullable=False)
sell_amount = db.Column(db.Float, nullable=False)
buy_amount = db.Column(db.Float, nullable=False)
fee = db.Column(db.Float, nullable=False)
previous_balance_from = db.Column(db.Float, nullable=False)
previous_balance_to = db.Column(db.Float, nullable=False)
after_balance_from = db.Column(db.Float, nullable=False)
after_balance_to = db.Column(db.Float, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
user = db.relationship("User", foreign_keys=[user_id])
is_completed = db.Column(db.Boolean, unique=False, default=False)
# average price buy/sell
avg_buy_price = db.Column(db.Float, nullable=True, unique=False)
avg_sell_price = db.Column(db.Float, nullable=True, unique=False)
def __init__(self):
pass
def __repr__(self):
return '<pair: {}>, <buy amount: {}>, <price: {}>, <fee: {}>, complete: {}'.format(
self.currency_pair.symbol, self.buy_amount, self.price, self.fee,
self.is_completed)
class Tourister(Base):
""" defines what a tourister can be """
__tablename__ = 'touristers'
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
firstname = db.Column(db.String(30), nullable=False)
lastname = db.Column(db.String(30), nullable=False)
middlename = db.Column(db.String(30))
username = db.Column(db.String(30), nullable=False)
mainLocation = db.Column(db.String(30))
story = db.Column(db.String(800))
occupations = db.relationship('Occupation',
backref='tourister',
lazy='dynamic')
languages = db.relationship('Language',
backref='tourister',
lazy='dynamic')
trips = db.relationship('Trip', backref='tourister', lazy='dynamic')
paypalInfo = db.relationship('PaypalInfo', backref='tourister')
education = db.relationship('Education',
backref='tourister',
lazy='dynamic')
socialNetworks = db.relationship("SocialNetwork",
backref='tourister',
lazy='dynamic')
testimonials = db.relationship("Testimonial",
backref='tourister',
lazy='dynamic')
activities = db.relationship('Activity',
backref='tourister',
lazy='dynamic')
images = db.relationship("Image", secondary='tourister_images')
class PaypalInfo(Base):
""" information needed to use user's paypal """
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
email = db.Column(db.String(100), nullable=False)
class Follow(db.Model):
follower_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True)
followed_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
follower = db.relationship('User',
foreign_keys=[follower_id],
back_populates='following',
lazy='joined')
followed = db.relationship('User',
foreign_keys=[followed_id],
back_populates='followers',
lazy='joined')
class RideshareAdditionalInfo(Base):
""" Additional info that can be added to a tourister service """
rideshare_detail_id = db.Column(db.Integer,
db.ForeignKey('rideshare_detail.id'),
nullable=False)
value = db.Column(db.String(100))
class ServicePerks(Base):
""" perks that can be added to a tourister service """
service_id = db.Column(db.Integer,
db.ForeignKey('services.id'),
nullable=False)
value = db.Column(db.String(100))
class TripPricing(Base):
""" different pricing models for trips a tourister can provide """
trip_id = db.Column(db.Integer, db.ForeignKey('trips.id'), nullable=False)
regular = db.Column(db.Float)
tripDuration = db.Column(db.Float)
overtimePrice = db.Column(db.Float)
class SocialNetwork(Base):
""" different social networks of a tourister """
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
type = db.Column(db.String(30))
src = db.Column(db.String(200))
class Education(Base):
""" education of a tourister """
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
school = db.Column(db.String(30))
degree = db.Column(db.String(30))
class Notification(db.Model):
id = db.Column(db.Integer, primary_key=True)
message = db.Column(db.Text, nullable=False)
is_read = db.Column(db.Boolean, default=False)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
receiver_id = db.Column(db.Integer, db.ForeignKey('user.id'))
receiver = db.relationship('User', back_populates='notifications')
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
flag = db.Column(db.Integer, default=0)
replied_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
photo_id = db.Column(db.Integer, db.ForeignKey('photo.id'))
photo = db.relationship('Photo', back_populates='comments')
author = db.relationship('User', back_populates='comments')
replies = db.relationship('Comment',
back_populates='replied',
cascade='all')
replied = db.relationship('Comment',
back_populates='replies',
remote_side=[id])
class AnalysisModel(BaseModel):
__tablename__ = 'analysis'
#:Primary key of the Model
id = db.Column(db.Integer, primary_key=True)
#:The IAP ID of the Analyis results in IAP
iap_id = db.Column(db.String, unique=True)
#:The path, as SMB URL, where the exported IAP results are stored
export_path = db.Column(db.String, nullable=True)
#:The id of the IAP pipeline with which this analysis has been processed
pipeline_id = db.Column(db.String, nullable=False)
#:Timestamp to indicate the start time of the analysis
started_at = db.Column(db.DateTime, nullable=True)
#:Timestamp to indicate the end time of the analysis
finished_at = db.Column(db.DateTime, nullable=True)
#:Foreign key to the corresponding Timestamp
timestamp_id = db.Column(db.Integer, db.ForeignKey('timestamp.id'), nullable=False)
#:SQLAlchemy relationship to the corresponding Timestamp
timestamp = db.relationship("TimestampModel", back_populates="analyses", single_parent=True)
#:SQLAlchemy relationship to all Postprocessings which have been applied to this analysis
postprocessings = db.relationship("PostprocessModel", back_populates="analysis", cascade="all, delete-orphan")
#:SQLAlchemy relationship to all Snapshots processed by this analysis
db.UniqueConstraint(timestamp_id, pipeline_id, name=u'uq_analysis_timestamp_id_pipeline_id')
@staticmethod
def get_or_create(timestamp_id, pipeline_id, session=None):
if session is None:
session = db.session
try:
return session.query(AnalysisModel).filter_by(timestamp_id=timestamp_id,
pipeline_id=pipeline_id).one(), False
except NoResultFound:
entry = AnalysisModel(timestamp_id, pipeline_id)
try:
session.add(entry)
session.flush()
return entry, True
except IntegrityError:
session.rollback()
return session.query(AnalysisModel).filter_by(timestamp_id=timestamp_id,
pipeline_id=pipeline_id).one(), False
def purge(self):
shared_folder_map = current_app.config['SHARED_FOLDER_MAP']
for postprocess in self.postprocessings:
postprocess.purge()
local_path = get_local_path_from_smb(self.report_path, shared_folder_map)
shutil.rmtree(local_path)
for image in self.timestamp.snapshots.images.where(ImageModel.type == 'segmented'):
db.session.delete(image)
def __init__(self, timestamp_id, pipeline_id):
self.timestamp_id = timestamp_id
self.pipeline_id = pipeline_id
def __repr__(self):
return '<Analysis %d with Pipeline %r of timestamp %d>' % (self.id, self.pipeline_id, self.timestamp_id)
class Appointment(ResourceMixin, db.Model):
__tablename__ = 'appointments'
# Properties
id = db.Column(db.Integer, primary_key=True)
scheduled_datetime = db.Column(db.Date, index=True)
subject_matter = db.Column(db.String(1000), nullable=False)
# Foreign Keys
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
class UserUserRole(db.Model, BaseModel):
__tablename__ = 'users_user_roles'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
product_id = db.Column(db.Integer, db.ForeignKey('user_roles.id'))
creation_date = db.Column(db.DateTime, default=datetime.utcnow)
user = db.relationship(User,
lazy='subquery',
backref=db.backref("users_user_roles",
cascade="all, delete-orphan",
lazy=True))
user_role = db.relationship(UserRole,
lazy='subquery',
backref=db.backref(
"users_user_roles",
cascade="all, delete-orphan",
lazy=True))
class Language(Base):
""" languages a tourister can speak/understand """
__tablename__ = 'languages'
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
name = db.Column(db.String(10), nullable=False)
proficiency = db.Column(db.String(10), nullable=False)
class Occupation(Base):
""" Occupations touristers can exercise (to find matches) """
__tablename__ = 'occupations'
tourister_id = db.Column(db.Integer,
db.ForeignKey('touristers.id'),
nullable=False)
name = db.Column(db.String(30), nullable=False)
affiliation = db.Column(db.String(30))
class Service(Base):
""" services a tourister can offer """
__tablename__ = 'services'
trip_id = db.Column(db.Integer, db.ForeignKey('trips.id'), nullable=False)
type = db.Column(db.String(30))
perks = db.relationship('ServicePerks', backref='service', lazy='dynamic')
details = db.relationship('RideshareDetail',
backref='service',
uselist=False)
class File(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(500))
filename = db.Column(db.String(64))
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
can_comment = db.Column(db.Boolean, default=True)
flag = db.Column(db.Integer, default=0)
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
enc_flag = db.Column(db.Boolean, default=False)
enc_policy = db.Column(db.String(200))
author = db.relationship('User', back_populates='files')
