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