class Info(db.Model, CRUD): __tablename__ = 'infos' id = db.Column(db.Integer, primary_key=True, autoincrement=True) c_name = db.Column(db.String(255), nullable=True) e_name = db.Column(db.String(255), nullable=True) # TODO 类型信息,暂时还不确定是否和category有区别 desc = db.Column(db.String(255), nullable=True) ref_min = db.Column(db.Float, nullable=True) ref_max = db.Column(db.Float, nullable=True) alias = db.Column(db.String(50), nullable=True) refs = db.relationship('Ref', backref='info', lazy='dynamic') categories = db.relationship('Category', secondary=category_infos, backref='infos') def __init__(self, c_name, e_name, desc, ref_min, ref_max, alias, refs=[], categories=[]): self.c_name = c_name self.e_name = e_name self.desc = desc self.ref_min = ref_min self.ref_max = ref_max self.alias = alias self.refs = refs self.categories = categories
class User(db.Model): id = db.Column(db.Integer, primary_key=True, autoincrement=True) first_name = db.Column(db.String(30), nullable=False) last_name = db.Column(db.String(30), nullable=False) username = db.Column(db.String(20), unique=True, nullable=False) email = db.Column(db.String(40), nullable=False) hash_password = db.Column(db.String(140)) avatar = db.Column(db.String(20)) posts = db.relationship('Post', backref='user') def __init__(self, *args, **kwargs): self.posts = [] super(User, self).__init__(*args, **kwargs) @property def password(self): raise AttributeError('password is write only field') @password.setter def password(self, password): self.hash_password = sha256.hash(password) def check_password(self, password): return sha256.verify(password, self.hash_password) def create(self): db.session.add(self) db.session.commit() return self
class Location(Base): __tablename__ = 'location' name = db.Column(db.String(12), unique=True, nullable=False) external_id = db.Column(db.String(12), unique=True, nullable=False) secret_code = db.Column(db.String(12), unique=True, nullable=False) location_groups = db.relationship('LocationGroup', secondary=location_groups, lazy='subquery', backref=db.backref('locations', lazy=True)) def __init__(self, name, external_id, secret_code): self.name = name self.external_id = external_id self.secret_code = secret_code def create(self): db.session.add(self) db.session.commit() return self def __repr__(self): return '<Location: %r>' % self.name
class Country(Base): __tablename__ = 'country' # Longest country code is a 6-length char "ITU callsign prefixes" # Shortest are made of 2 chars # https://en.wikipedia.org/wiki/Country_code country_code = db.Column(db.String(6), unique=True, nullable=False) panel_provider_id = db.Column(db.Integer, db.ForeignKey('panel_provider.id'), nullable=False) # Country is linked with LocationGroup via one to many relationship location_groups = db.relationship('LocationGroup', backref='country', lazy=True) def __init__(self, country_code, panel_provider_id, location_groups=[]): self.country_code = country_code self.panel_provider_id = panel_provider_id self.location_groups = location_groups def create(self): db.session.add(self) db.session.commit() return self def __repr__(self): return '<Country: %r>' % self.country_code
class Batch(db.Model, CRUD): __tablename__ = 'batches' id = db.Column(db.Integer, primary_key=True, autoincrement=True) agency_id = db.Column(db.Integer, db.ForeignKey('agencies.id')) contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id')) # 样本类型: 粪便, 脑脊液,DNA,土壤,发酵品,其他 sample_type = db.Column(db.SmallInteger) # 样品状态, 到样状态(干冰保存,冰袋冻存,室温,异常) status = db.Column(db.SmallInteger) # 状态特征: 固态粪便,液体脑积液微黄 character = db.Column(db.String(255)) deliver_time = db.Column(db.DateTime, server_default=db.func.now()) # deliver_time = db.Column(db.DateTime, default=datetime.now) arrive_time = db.Column(db.DateTime, server_default=db.func.now()) express_num = db.Column(db.String(30), nullable=True) # 入库时间 store_time = db.Column(db.DateTime, server_default=db.func.now()) # 存储位置及温度 position_id = db.Column(db.Integer, db.ForeignKey('positions.id')) # 所属项目 subproject_id = db.Column(db.Integer, db.ForeignKey('subprojects.id')) # 技术路线 roadmap_id = db.Column(db.Integer, db.ForeignKey('roadmaps.id')) # 备注, 异常情况 remark = db.Column(db.Text, nullable=True) samples = db.relationship('Sample', backref='batch', lazy='dynamic') def __init__(self, agency_id, contact_id, sample_type, status, character, deliver_time, arrive_time, express_num, store_time, position_id, subproject_id, roadmap_id, remark, samples=[]): self.agency_id = agency_id self.contact_id = contact_id self.sample_type = sample_type self.status = status self.character = character # self.deliver_time = datetime_parser.parse(deliver_time).astimezone( # tzutc()).replace(tzinfo=None) # self.arrive_time = datetime_parser.parse(arrive_time).astimezone( # tzutc()).replace(tzinfo=None) self.deliver_time = deliver_time self.arrive_time = arrive_time self.express_num = express_num self.store_time = store_time self.position_id = position_id self.subproject_id = subproject_id self.roadmap_id = roadmap_id self.remark = remark self.samples = samples
class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(128)) username = db.Column(db.String(128)) password = db.Column(db.String(1024)) email = db.Column(db.String(128), unique=True, nullable=False) created = db.Column(db.DateTime, server_default=db.func.now()) isVerified = db.Column(db.Boolean, nullable=False, default=False) keys = db.relationship('Key', backref='User', cascade="all, delete-orphan") def __init__(self, name, username, password, email, keys=[]): self.name = name self.username = username self.password = sha256_crypt.hash(password) self.email = email self.keys = keys def create(self): db.session.add(self) db.session.commit() return self @classmethod def find_by_username(cls, username): return cls.query.filter_by(username=username).first() @classmethod def find_by_email(cls, email): return cls.query.filter_by(email=email).first() @staticmethod def verify_hash(password, hash): return sha256_crypt.verify(password, hash)
class Video(BaseMixin, db.Model): """ video model """ __tablename__ = 'video' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(120), unique=True, nullable=True) video_files = db.relationship('VideoFile', backref=db.backref('video'), lazy=False) image_files = db.relationship('ImageFile', backref=db.backref('video'), lazy=False) created_at = db.Column(db.DateTime(), nullable=True, server_default=func.now()) description = db.Column(db.String(120)) def __repr__(self): return '<Video %r>' % self.title
class Project(db.Model, CRUD): __tablename__ = 'projects' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(255)) description = db.Column(db.String(500)) manager = db.Column(db.Integer) subprojects = db.relationship('Subproject', backref='project', lazy='dynamic') def __init__(self, name, subprojects=[]): self.name = name self.subprojects = subprojects
class Agency(db.Model, CRUD): __tablename__ = 'agencies' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(64)) address = db.Column(db.Text) contacts = db.relationship( 'Contact', backref=backref('agency', cascade='all, delete-orphan', single_parent=True), lazy='dynamic' ) batches = db.relationship( 'Batch', backref='agency', lazy='dynamic' ) def __init__(self, name, address, contacts=[], batches=[]): self.name = name self.address = address self.contacts = contacts self.batches = batches
class Post(db.Model): # id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(80), nullable=False) body = db.Column(db.Text, nullable=False) pub_date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False) category = db.relationship('Category', backref=db.backref('posts', lazy=True)) def __repr__(self): return '<Post %r>' % self.id
class Post(db.Model): __tablename__ = "posts" __table_args__ = {"extend_existing": True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) body = db.Column(db.Text) title = db.Column(db.String(120)) timestamp = db.Column(db.DateTime, default=datetime.utcnow) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) # Relationships tags = db.relationship(Tag, secondary=post_tag, backref=db.backref("posts_")) author = db.relationship("User", backref="posts") comments = db.relationship('Comment', backref='post', lazy='dynamic') def save_to_db(self) -> "Post": db.session.add(self) db.session.commit() return self def add_to_session(self): db.session.add(self) def db_commit(self): db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit() @property def comments_count(self): return self.comments.filter_by(confirmed=True).count() @classmethod def find_all_confirmed(cls) -> List["Post"]: return cls.query.filter_by(confirmed=True).order_by(cls.timestamp.desc())
class Product(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20), nullable=False, unique=True) categories = db.relationship("Category", secondary=associations, back_populates="products", lazy="dynamic") def __init__(self, name): self.name = name def create(self): db.session.add(self) db.session.commit() return self
class User(db.Model, CRUD): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, autoincrement=True) email = db.Column(db.String(256)) password = db.Column(db.String(256), nullable=False, unique=True) roles = db.relationship('Role', secondary=role_users, backref='users') def __init__(self, email, password, roles=[]): self.email = email self.password = Bcrypt().generate_password_hash(password).decode() for item in roles: role = Role.query.filter_by(slug=item).one() self.roles.append(role) def password_is_valid(self, password): return Bcrypt().check_password_hash(self.password, password) def generate_token(self, user_id): try: payload = { 'exp': datetime.utcnow() + timedelta(minutes=60), 'iat': datetime.utcnow(), 'sub': user_id } jwt_string = jwt.encode(payload, os.getenv('SECRET'), algorithm='HS256') return jwt_string except Exception as e: return str(e) # def create_with_roles(self): # if len(self.roles): # for role in self.roles: # @staticmethod def decode_token(token): try: payload = jwt.decode(token, os.getenv('SECRET')) return payload['sub'] except jwt.ExpiredSignatureError: return 'Token过期,请重新登录获取' except jwt.InvalidTokenError: return 'Token不合法,请登录或注册'
class Sample(db.Model, CRUD): __tablename__ = 'samples' id = db.Column(db.Integer, primary_key=True, autoincrement=True) batch_id = db.Column(db.Integer, db.ForeignKey('batches.id')) client_id = db.Column(db.Integer, db.ForeignKey('clients.id')) pmid = db.Column(db.String(20)) ori_num = db.Column(db.String(20)) # 与下面的amount配合,标识是重量还是体积 amount_type = db.Column(db.SmallInteger, nullable=True) # 样品量,体积或重量(ml/g) amount = db.Column(db.Float, nullable=True) # 测序类型 sequence_method = db.Column(db.SmallInteger, nullable=True) primer = db.Column(db.SmallInteger, nullable=True) sequencer = db.Column(db.SmallInteger, nullable=True) # 文库编号 library_id = db.Column(db.Integer, db.ForeignKey('libraries.id')) # 备注, 异常情况 remark = db.Column(db.Text, nullable=True) results = db.relationship('Result', backref='sample', lazy='dynamic') def __init__(self, batch_id, client_id, pmid, ori_num, amount_type, amount, sequence_method, primer, sequencer, library_id, remark, results=[]): self.batch_id = batch_id self.client_id = client_id self.pmid = pmid self.ori_num = ori_num self.amount_type = amount_type self.amount = amount self.sequence_method = sequence_method self.primer = primer self.sequencer = sequencer self.library_id = library_id self.remark = remark self.results = results
class Author(db.Model): __tablename__ = 'authors' id = db.Column(db.Integer, primary_key=True,autoincrement=True) first_name = db.Column(db.String(20)) last_name = db.Column(db.String(20)) created = db.Column(db.DateTime, server_default=db.func.now()) books = db.relationship('Book', backref='Author', cascade="all, delete-orphan") def __init__(self, first_name, last_name, books=[]): self.first_name = first_name self.last_name = last_name self.books = books def create(self): db.session.add(self) db.session.commit() return self
class Author(db.Model): __tablename__ = 'authors' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String) surname = db.Column(db.String) created = db.Column(db.DateTime, server_default=db.func.now()) books = db.relationship('Book', backref='Author') def __init__(self, name, surname, books=[]): self.name = name self.surname = surname self.books = books def create(self): db.session.add(self) db.session.commit() return self
class Client(db.Model): __tablename__ = 'clients' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(100), nullable=False) email = db.Column(db.String(100), unique=True, nullable=False) favorite_products = db.relationship('FavoriteProducts', backref='Client', cascade='all, delete-orphan') def __init__(self, name, email, favorite_products=[]): self.name = name self.email = email self.favorite_products = favorite_products def create(self): db.session.add(self) db.session.commit() return self
class Client(db.Model, CRUD): __tablename__ = 'clients' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(20)) gender = db.Column(db.SmallInteger(), nullable=True) age = db.Column(db.SmallInteger(), nullable=True) height = db.Column(db.Float, nullable=True) weight = db.Column(db.Float, nullable=True) # 额外信息,包括各项指标 extra = db.Column(db.JSON(), nullable=True) samples = db.relationship('Sample', backref='client', lazy='dynamic') def __init__(self, name, gender, age, height, weight, extra, samples=[]): self.name = name self.gender = gender self.age = age self.height = height self.weight = weight self.extra = extra self.samples = samples
class Category(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20), nullable=False, unique=True) products = db.relationship("Product", secondary=associations, back_populates="categories", lazy="dynamic") def __init__(self, name, **kwargs): self.name = name products = kwargs.get('products', None) if products: for p in products: product = Product(name=p) self.products.append(product) db.session.add(product) def create(self): db.session.add(self) db.session.commit() return self
class Author(db.Model): __tablename__ = "authors" id = db.Column(db.Integer, primary_key=True, autoincrement=True) first_name = db.Column(db.String(20)) last_name = db.Column(db.String(20)) avatar = db.Column(db.String(220), nullable=True) created = db.Column(db.DateTime, server_default=func.now()) books = db.relationship("Book", backref="Author", cascade="all, delete-orphan") def create(self): db.session.add(self) db.session.commit() return self def __init__(self, first_name, last_name, books=[]): self.first_name = first_name self.last_name = last_name self.books = books def __repr__(self): return "<Product %d>" % self.id
class Posts(db.Model): __tablename__ = 'posts' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(100)) createdAt = db.Column(db.DateTime, server_default=db.func.now()) category = db.Column(db.String(100)) post = db.Column(db.Text) image = db.Column(db.String(100), nullable=True) comments = db.relationship('Comments', backref='Posts', cascade="all, delete-orphan") def __init__(self, name, createdAt, category, post, comments=[]): self.name = name self.createdAt = createdAt self.category = category self.post = post self.image = image self.comments = comments def create(self): db.session.add(self) db.session.commit() return self
class User(db.Model): __tablename__ = "users" __table_args__ = {"extend_existing": True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(64), unique=True, index=True) email = db.Column(db.String(64), unique=True, index=True) is_admin = db.Column(db.Boolean) avatar = db.Column(db.String(164)) password_hash = db.Column(db.String(120), nullable=False) timestamp = db.Column(db.DateTime, default=datetime.utcnow) first_name = db.Column(db.String(64)) last_name = db.Column(db.String(64)) bio = db.Column(db.Text) # Relationship confirmation = db.relationship( "Confirmation", backref="user", lazy="dynamic", cascade="all, delete-orphan" ) comments = db.relationship( Comment, backref="author", lazy="dynamic", cascade="all, delete-orphan" ) def __init__(self, **kwargs): super().__init__(**kwargs) if self.email == os.environ.get("ADMIN_EMAIL"): self.is_admin = True else: self.is_admin = False @property def most_recent_confirmation(self) -> "Confirmation": return self.confirmation.order_by(db.desc(Confirmation.expire_at)).first() def save_to_db(self) -> "User": db.session.add(self) db.session.commit() return self def delete_from_db(self) -> None: db.session.delete(self) db.session.commit() @classmethod def find_all(cls) -> List["User"]: return cls.query.all() @property def password(self): raise AttributeError("password is not a readable attribute") @password.setter def password(self, password) -> None: self.password_hash = generate_password_hash(password) def check_password(self, password) -> bool: return check_password_hash(self.password_hash, password) @classmethod def find_by_username(cls, username) -> "User": return cls.query.filter_by(username=username).first() @classmethod def find_by_email(cls, email) -> "User": return cls.query.filter_by(email=email).first() def send_confirmation_email(self) -> Response: link = request.url_root[0:-1] + url_for( "confirmationresource", confirmation_id=self.most_recent_confirmation.id ) subject = "Registration confirmation" text = f"Please click the link to confirm your registration: {link}" html = f"<html>Please click the link to confirm your registration: <a href={link}>link</a></html>" return Mailgun.send_email( to=[self.email], subject=subject, text=text, html=html )