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