class DynamicQueueModel(QueueModelMixin, DeclarativeBase): __tablename__ = 'dynamic_queue' crawl_at = Column(BigInteger, nullable=False) netloc = query_expression() partition_id = query_expression() partition_seed = Column(Integer, nullable=False)
class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True) foo = Column(Integer) question_count: Mapped[int] = query_expression() answer_count: int = query_expression()
class Study(Base, AnnotatedModel): __tablename__ = "study" add_date = Column(DateTime, nullable=True) mod_date = Column(DateTime, nullable=True) gold_name = Column(String, nullable=False, default="") gold_description = Column(String, nullable=False, default="") scientific_objective = Column(String, nullable=False, default="") doi = Column(String, ForeignKey("doi_info.id"), nullable=False) multiomics = Column(Integer, nullable=False, default=0) # TODO: Specify a default expression so that sample counts are present in # non-search responses. sample_count = query_expression() omics_counts = query_expression() omics_processing_counts = query_expression() principal_investigator_id = Column(UUID(as_uuid=True), ForeignKey("principal_investigator.id"), nullable=False) principal_investigator = relationship("PrincipalInvestigator", cascade="all") principal_investigator_name = association_proxy("principal_investigator", "name") @property def principal_investigator_image_url(self): return f"/api/principal_investigator/{self.principal_investigator_id}" principal_investigator_websites = relationship("StudyWebsite", cascade="all", lazy="joined") publication_dois = relationship("StudyPublication", cascade="all", lazy="joined") doi_object = relationship("DOIInfo", cascade="all", lazy="joined") doi_info = association_proxy("doi_object", "info") @property def open_in_gold(self) -> Optional[str]: return gold_url("https://gold.jgi.doe.gov/study?id=", self.id) @property def publication_doi_info(self) -> Dict[str, Any]: doi_info = { d.publication.doi: d.publication.doi_object.info for d in self.publication_dois # type: ignore } doi_info[self.doi] = self.doi_info return doi_info
class MProduct(db.Model): __tablename__ = 'marketplace_products' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String()) images = db.Column(db.Text) description = db.Column(db.String()) availability = db.Column(db.Boolean, default=True) min_order_quantity = db.Column(db.Integer, default=1) length = db.Column(db.Float) weight = db.Column(db.Float) height = db.Column(db.Float) price = db.Column(db.Float) price_currency_id = db.Column( db.Integer, db.ForeignKey('marketplace_currency.id', ondelete="CASCADE")) seller_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete="CASCADE")) brand_id = db.Column( db.Integer, db.ForeignKey('marketplace_brands.id', ondelete="CASCADE")) is_featured = db.Column(db.Boolean, default=False) condition = db.Column(db.String()) lead_time = db.Column(db.String()) created_at = db.Column(db.DateTime, default=db.func.now()) updated_at = db.Column(db.DateTime, default=db.func.now(), onupdate=db.func.now()) # categories = db.relationship("MCategory", secondary='marketplace_product_categories', # backref=backref("products"), # primaryjoin=(MProductCategory.product_id == id), # secondaryjoin=(MProductCategory.category_id == MCategory.id)) categories = db.relationship("MCategory", secondary='marketplace_product_categories', backref=db.backref('products')) # variants = db.relationship("MVariant", secondary='marketplace_product_variants', # backref=backref("products"), # primaryjoin=(MProductVariant.product_id == id), # secondaryjoin=(MProductVariant.variant_id == MVariant.id)) variants = db.relationship("MVariant", secondary='marketplace_product_variants', backref=db.backref('products')) brand = db.relationship("MBrand", backref="products") price_currency = db.relationship("MCurrency") seller = db.relationship("User", backref="products") score = query_expression() @property def rating(self): return db.session.query(db.func.avg( MReview.score)).filter(MReview.product_id == self.id).scalar() @property def image_items(self): return [ url_for('_uploads.uploaded_file', setname='images', filename=image, _external=True) for image in json.loads(self.images) ]
class B(fixtures.ComparableEntity, Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey('a.id')) p = Column(Integer) q = Column(Integer) b_expr = query_expression()
class A(fixtures.ComparableEntity, Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) x = Column(Integer) y = Column(Integer) my_expr = query_expression() bs = relationship("B", order_by="B.id")
class QuoteRelation(PseudoMaterializedView): __tablename__ = 'baked_quote_stats' year = Column(Integer, primary_key=True) bid = Column(Integer, primary_key=True) quoter_uid = Column(Integer, ForeignKey('users.uid'), primary_key=True) quoted_uid = Column(Integer, ForeignKey('users.uid'), primary_key=True) count = Column(Integer, default=0) intensity = query_expression() quoter = relationship('User', foreign_keys=quoter_uid) quoted = relationship('User', foreign_keys=quoted_uid)
class Task(TrackModificationTimeMixin, Base): Type = _TaskType query = session.query_property(TaskQuery) id = sa.Column(ps.UUID(as_uuid=True), default=uuid4, primary_key=True) type = sa.Column(sa.Enum(_TaskType), nullable=False) raw_forbidden_for = so.relationship(lambda: Exclusion) forbidden_for = association_proxy("raw_forbidden_for", "user_id") is_skippable = so.query_expression() @staticmethod def get_skippable_expression(user_id, on_date=None): # when changes are made here, they should also be made in TaskQuery.assignable() function # this logic is duplicated there for performance reasons assign = so.aliased(Assignment) stmt = (session.query( assign.task_id, sa.func.bool_or( assign.user_id != user_id).label("started_by_anyone"), ).filter(assign.is_relevant(on_date)).group_by( assign.task_id).subquery()) tsk = so.aliased(Task) is_skippable = (session.query( TaskSet.allow_skip & (tsk.type != Task.Type.CC) & (~tsk.is_pushback) & (tsk.assigned_group_id.is_(None) | (tsk.assigned_group_id == TaskSet.default_group_id) | ~sa.func.coalesce(stmt.c.started_by_anyone, False)) ).select_from(tsk).join(TaskSet, tsk.task_set).outerjoin(stmt).filter( tsk.id == Task.id).as_scalar()) return is_skippable @hybrid_method def is_relevant(self, on_date=None): if on_date is None: on_date = datetime.utcnow() return self.deadline is None or (on_date <= self.deadline) @is_relevant.expression def is_relevant(self, on_date=None): if on_date is None: on_date = datetime.utcnow() return on_date <= sa.func.coalesce( self.deadline, sa.cast("infinity", self.deadline.type))
class User(db.Model, TimestampMixin): query_class = UserQuery id = db.Column(db.Integer, primary_key=True) todos = db.relationship('Todo', backref='user', lazy=True) email = db.Column(db.String(255), unique=True, nullable=False) password_hash = db.Column(db.String(100)) first_name = db.Column(db.Unicode(100)) last_name = db.Column(db.Unicode(100)) auth_sub = db.Column(db.String(100)) notifications_enabled = db.Column(db.Boolean()) notifications_radius_meters = db.Column(db.Float()) phone = db.Column(db.String(100)) email = db.Column(db.String(100)) avatar = db.Column(db.String(100)) expires_at = db.Column(db.DateTime()) # Store last location of User. This properties is just used for postgis POC location = db.Column( fields.PointColumn(geometry_type='POINT', management=True, use_st_prefix=False)) # Distance from user current location to a coordinate distance = query_expression() lat = query_expression() lng = query_expression() @property def password(self): raise AttributeError('password is a write only field') @password.setter def password(self, password): self.password_hash = bcrypt \ .generate_password_hash(password) \ .decode('utf-8') def check_password(self, password): return bcrypt.check_password_hash(self.password_hash, password) @classmethod def create_new_user(cls, data): user = User.query.get_by_email(email=data['email']) if not user: new_user = User(email=data['email'], password=data['password'], first_name=data['first_name'], last_name=data['last_name']) db.session.add(new_user) db.session.commit() return new_user def update_location(self, lat, lng, **kwargs): point = 'POINT(%f %f)' % (lng, lat) wkb_element = WKTElement(point, srid=4326) self.location = wkb_element db.session.add(self) db.session.commit() return self
class Book(TimeMixin, UUIDMixin, db.Model): __tablename__ = 'book' __repr_attrs__ = ["id", "name", "categories", "collected_times"] id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(50)) other_name = db.Column(db.String(50)) isbn = db.Column(db.String(13)) pub_date = db.Column(db.String(10)) publisher = db.Column(db.String(20)) douban_id = db.Column(db.Integer) authors = association_proxy('_authors', 'name') copies = db.relationship('BookOfUser') categories = db.relationship('Category', secondary='books_of_categories', backref='books') expr = query_expression() @declared_attr def _authors(cls): class Author(db.Model): __tablename__ = f"{cls.__tablename__}_author" id = db.Column(db.Integer, autoincrement=True, primary_key=True) name = db.Column(db.String(50)) parent_id = db.Column(db.ForeignKey(f'{cls.__tablename__}.id'), nullable=False) def __init__(self, name): self.name = name return db.relationship(Author) @hybrid_property def all_copies(self): return self.copies @hybrid_property def collected_times(self): return len(self.collected_users) @collected_times.expression def copy_nums(cls): return cls.collected_users.count() @hybrid_property def cover_img_url(self): new_covers = sorted(self.thumbnails, key=lambda x: x.create_at, reverse=True) return new_covers[0].url if new_covers else NO_IMG @property def detail_img_url(self): return self.cover_img_url @classmethod def newly_books(cls): return cls.query.order_by(Book.create_at.desc()).limit(5).all() @classmethod def most_copies(cls, num=5): count_expr = func.count(cls.copies) query_string = cls.query.options(with_expression(cls.expr, count_expr))\ .group_by(Book.id).order_by(count_expr.desc()).limit(num) return query_string.all() @classmethod def most_collected(cls): return cls.query.order_by(cls.collected_times, 'desc').all()