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 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 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 Client(db.Model): __tablename__ = 'clients' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(16)) def __init__(self, name): self.name = name def create(self): db.session.add(self) db.session.commit() return self
class ProductArea(db.Model): __tablename__ = 'productAreas' id = db.Column(db.Integer, primary_key=True, autoincrement=True) area_type = db.Column(db.String(16)) def __init__(self, area_type): self.area_type = area_type def create(self): db.session.add(self) db.session.commit() return self
class User(db.Model): uid = db.Column(db.Integer,primary_key=True) username = db.Column(db.String(20),unique=True,nullable=False) password = db.Column(db.String(200),nullable=False) 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() @staticmethod def generate_hash(password): return sha256.hash(password) @staticmethod def verify_hash(password,hash): return sha256.verify(password,hash)
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 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 TargetGroup(Base): __tablename__ = 'target_group' name = db.Column(db.String(24), unique=True, nullable=False) external_id = db.Column(db.String(12), unique=True, nullable=False) # TargetGroup model would have associations with it self # via parent_id which would form a tree with multiple root nodes parent_id = db.Column(db.Integer, db.ForeignKey('target_group.id'), nullable=True) secret_code = db.Column(db.String(12), unique=True, nullable=False) panel_provider_id = db.Column(db.Integer, db.ForeignKey('panel_provider.id'), nullable=False) def __init__(self, name, external_id, secret_code, panel_provider_id, parent_id=None): self.name = name self.external_id = external_id self.parent_id = parent_id self.secret_code = secret_code self.panel_provider_id = panel_provider_id def create(self): db.session.add(self) db.session.commit() return self def __repr__(self): return '<TargetGroup: %r>' % self.name
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 Feature(db.Model): __tablename__ = 'features' id = db.Column(db.Integer, primary_key=True, autoincrement=True) title = db.Column(db.String(100)) description = db.Column(db.String(250)) client_id = db.Column(db.Integer, db.ForeignKey('clients.id')) priority = db.Column(db.Integer) target_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) product_area_id = db.Column(db.Integer, db.ForeignKey('productAreas.id')) def __init__(self, title, description, client_id, priority, target_date, product_area_id): self.title = title self.description = description self.client_id = client_id self.priority = priority self.target_date = target_date self.product_area_id = product_area_id def create(self): db.session.add(self) db.session.commit() return self
class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key = True) username = db.Column(db.String(120), unique = True, nullable = False) password = db.Column(db.String(120), nullable = False) isVerified = db.Column(db.Boolean, nullable=False,default=False) email = db.Column(db.String(120), unique = True, nullable = False) def create(self): db.session.add(self) db.session.commit() return self @classmethod def find_by_email(cls, email): return cls.query.filter_by(email = email).first() @classmethod def find_by_username(cls, username): return cls.query.filter_by(username = username).first() @staticmethod def generate_hash(password): return sha256.hash(password) @staticmethod def verify_hash(password, hash): return sha256.verify(password, hash)
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 Tag(db.Model): __tablename__ = "tags" __table_args__ = {"extend_existing": True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(64), nullable=False, index=True, unique=True) def create(self) -> "Tag": db.session.add(self) db.session.commit() return self @classmethod def find_all(cls) -> List["Tag"]: return cls.query.all()
class Priority(db.Model): __tablename__ = 'priorities' id = db.Column(db.Integer, primary_key=True, autoincrement=True) number = db.Column(db.Integer) definition = db.Column(db.String(16)) def __init__(self, number, definition): self.number = number self.definition = definition def create(self): db.session.add(self) db.session.commit() return self
class Book(db.Model): __tablename__ = 'books' id = db.Column(db.Integer, primary_key=True, autoincrement=True) title = db.Column(db.String(50)) year = db.Column(db.Integer) author_id = db.Column(db.Integer, db.ForeignKey('authors.id')) def __init__(self, title, year, author_id=None): self.title = title self.year = year self.author_id = author_id def create(self): db.session.add(self) db.session.commit() return self
class Car(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(), unique=True) def create(self): db.session.add(self) db.session.commit() return self def delete(self): db.session.delete(self) db.session.commit() return self def update(self): db.session.add(self) db.session.commit() return self
class Movie(db.Model): __tablename__ = 'movies' id = db.Column(db.Integer, primary_key=True, autoincrement=True) title = db.Column(db.String(50)) year = db.Column(db.Integer) Director_id = db.Column(db.Integer, db.ForeignKey('directors.id'), nullable=False) def __init__(self, title, year, director_id=None): self.title = title self.year = year self.director_id = director_id def create(self): db.session.add(self) db.session.commit() return self
class Comments(db.Model): __tablename__ = 'comments' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(100)) time = db.Column(db.DateTime, server_default=db.func.now()) comment = db.Column(db.Text) post_id = db.Column(db.Integer, db.ForeignKey('posts.id')) def __init__(self, name, time, comment, post_id=None): self.name = name self.time = time self.comment = comment self.post_id = post_id def create(self): db.session.add(self) db.session.commit() return self
class Result(db.Model, CRUD): __tablename__ = 'results' id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 报告产出日期 date = db.Column(db.DateTime, server_default=db.func.now()) sample_id = db.Column(db.Integer, db.ForeignKey('samples.id')) # 结果状态: 未出具, 未审核, 已出具 status = db.Column(db.SmallInteger, default=0) # 审核员: 按理说是角色为审核员的人 auditor = db.Column(db.String(10), nullable=True) auditor_date = db.Column(db.DateTime, nullable=True) content = db.Column(db.JSON) def __init__(self, date, sample_id, status, auditor, content): self.date = date self.sample_id = sample_id self.status = status self.auditor = auditor self.auditor_date = auditor_date self.content = content
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 PanelProvider(Base): __tablename__ = 'panel_provider' code = db.Column(db.String(24), unique=True, nullable=False) # TODO: do we need this? # location_groups = db.relationship('LocationGroup', backref='panel_provider', lazy=True) def __init__(self, code, location_groups=[]): self.code = code self.location_groups = location_groups def create(self): db.session.add(self) db.session.commit() return self def __repr__(self): return '<PanelProvider: %r>' % self.code
class Json(db.Model): __tablename__ = 'json' id = db.Column(db.BigInteger, primary_key=True, autoincrement=True) data = db.Column(db.String(10000)) created = db.Column(db.DateTime, server_default=db.func.now()) updated = db.Column(db.DateTime, onupdate=db.func.now()) def __init__(self, data): self.data = data def create(self): db.session.add(self) db.session.commit() return self @staticmethod def count_json(uid): """Count the number of JSONs a user has access to :param uid: :return: int """ try: # get json through user mapping path query_user_json = db.session.query( Json.id).join(JsonAccessMap).filter(JsonAccessMap.user == uid) # get json through team mapping path teams = db.session.query( Team.id).join(TeamMemberMap).filter(TeamMemberMap.user == uid) query_team_json = db.session.query( Json.id).join(TeamJsonMap).filter(TeamJsonMap.team.in_(teams)) # count distinct json in team and user path json_count = db.session.query(count(Json.id.distinct()))\ .filter(or_(Json.id.in_(query_user_json), Json.id.in_(query_team_json))).scalar() return json_count except Exception as e: logging.error(e) raise
class Driver(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True) def __init__(self, name): self.name = name def create(self): db.session.add(self) db.session.commit() return self def delete(self): db.session.delete(self) db.session.commit() return self def update(self): db.session.add(self) db.session.commit() return self
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 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): __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 Confirmation(db.Model): __tablename__ = "confirmations" id = db.Column(db.String(50), primary_key=True) expire_at = db.Column(db.Integer, nullable=False) confirmed = db.Column(db.Boolean, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) def __init__(self, user_id: int, **kwargs): super().__init__(**kwargs) self.user_id = user_id self.id = uuid4().hex self.expire_at = int(time()) + CONFIRMATION_EXPIRATION_DELTA self.confirmed = False @classmethod def find_by_id(cls, _id: str) -> "Confirmation": return cls.query.filter_by(id=_id).first() @property def expired(self) -> bool: return time() > self.expire_at def force_to_expire(self) -> None: if not self.expired: self.expire_at = int(time()) self.save_to_db() def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.delete(self) db.session.commit()
class Contract(db.Model, CRUD): __tablename__ = 'contracts' id = db.Column(db.Integer, primary_key=True, autoincrement=True) subproject_id = db.Column(db.Integer, db.ForeignKey('subprojects.id')) name = db.Column(db.String(255)) sign_date = db.Column(db.DateTime) start_date = db.Column(db.DateTime) end_date = db.Column(db.DateTime) region = db.Column(db.String(255)) agency_id = db.Column(db.Integer, db.ForeignKey('agencies.id')) contact_id = db.Column(db.Integer, db.ForeignKey('contacts.id')) product_type = db.Column(db.String(100)) sequence_area = db.Column(db.String(100)) sequence_type = db.Column(db.String(100)) data_size = db.Column(db.String(100)) def __init__(self, name, sign_date, start_date, end_date, region, product_type, sequence_area, sequence_type, data_size, subproject_id=None, agency_id=None, contact_id=None): self.subproject_id = subproject_id self.name = name self.sign_date = sign_date self.start_date = start_date self.end_date = end_date self.region = region self.agency_id = agency_id self.contact_id = contact_id self.product_type = product_type self.sequence_area = sequence_area self.sequence_type = sequence_type self.data_size = data_size
class Category(db.Model): # id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), nullable=False) def __repr__(self): return '<Category %r>' % self.name