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
