class UserSurveyModel(db.Model):
__tablename__ = 'user_survey'
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime(timezone=True), default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True), onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
user = db.relationship('UserModel')
survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'), nullable=False)
survey = db.relationship('SurveyModel')
user_scores = db.relationship('UserScoreModel', lazy='dynamic')
user_answers = db.relationship('UserAnswerModel', lazy='dynamic')
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()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_all(cls) -> List["UserSurveyModel"]:
return cls.query.all()
class UserModel(db.Model):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True)
person_id = db.Column(db.String(80), nullable=False, unique=True)
created_at = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True),
onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
user_surveys = db.relationship('UserSurveyModel', lazy='dynamic')
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()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_by_person_id(cls, person_id):
return cls.query.filter_by(person_id=person_id).first()
@classmethod
def find_all(cls) -> List["UserModel"]:
return cls.query.all()
class Freigabe(db.Model, ModelSupport):
__tablename__ = 'freigabe'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(250), nullable=False, unique=True)
Auflagen = db.Column(db.Boolean, default=False, nullable=False)
Freigabeart = db.Column(db.String(250))
Auslaufdatum = db.Column(db.DateTime, nullable=False)
Freigabe_gültig_für_n_Ansätze = db.Column(db.Integer)
Bemerkung = db.Column(db.String(1000))
Fertigungsaufträge = db.Column(db.String(500))
date_created = db.Column(db.DateTime(timezone=True),
server_default=func.now())
last_updated = db.Column(db.DateTime(timezone=True),
server_default=func.now(),
server_onupdate=func.now())
last_updated_by = db.Column(db.String(50), nullable=True)
def __repr__(self):
return '<Freigabe id:{} text:{}>'.format(self.id, self.Freigabe)
@staticmethod
def find_by_id(freigabe_id):
assert (freigabe_id is not None), "Freigabe id shouldn't be null"
freigabe = Freigabe.query.get(freigabe_id)
return freigabe
@staticmethod
def delete_by_id(id):
Freigabe.query.get(id).delete()
db.session.commit()
@staticmethod
def create(freigabe_dict=None, freigabe_obj=None):
if freigabe_dict:
try:
return Freigabe(**freigabe_dict).insert()
except IntegrityError as e:
if e.orig.__class__.__name__ == "UniqueViolation":
abort(400, message='error.create_user.already_exist')
else:
raise e
else:
return freigabe_obj.insert()
@staticmethod
def upsert(freigabe_dict):
freigabe = Freigabe.find_by_id(freigabe_dict['id'])
if freigabe is not None:
freigabe.update(freigabe_dict)
return "update"
else:
freigabe_clean_dict = {}
for key, value in freigabe_dict.items():
if hasattr(Freigabe, key):
freigabe_clean_dict[key] = value
Freigabe(**freigabe_clean_dict).insert()
return "insert"
class SurveyModel(db.Model):
__tablename__ = 'survey'
id = db.Column(db.Integer, primary_key=True)
survey_id = db.Column(db.String(80), nullable=False, unique=True)
title = db.Column(JSON, nullable=False)
created_at = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True),
onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
questions = db.relationship('QuestionModel', lazy='dynamic')
# user_surveys = db.relationship('UserSurveyModel', lazy='dynamic')
def surveyjs_json(self):
return {
'title':
self.title,
'pages': [{
"name": item.question_id,
"elements": [item.question],
"visibleIf": item.visible_if,
"title": item.page_title
} for item in self.questions.order_by(QuestionModel.id).all()],
"showProgressBar":
"top"
}
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()
@classmethod
def find_by_survey_id(cls, survey_id):
return cls.query.filter_by(survey_id=survey_id).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_all(cls) -> List["SurveyModel"]:
return cls.query.all()
class QuestionModel(db.Model):
__tablename__ = 'question'
id = db.Column(db.Integer, primary_key=True)
question_id = db.Column(db.String(80), nullable=False)
question = db.Column(JSON, nullable=False)
page_title = db.Column(JSON)
visible_if = db.Column(db.String(80))
reverse_score = db.Column(db.Boolean, default=False)
scoring_rule_id = db.Column(db.Integer, default=1)
created_at = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True),
onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
survey_id = db.Column(db.Integer,
db.ForeignKey('survey.id'),
nullable=False)
survey = db.relationship('SurveyModel')
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def save_to_db_all(self) -> None:
db.session.bulk_save_objects(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_question_id(cls, question_id):
return cls.query.filter_by(question_id=question_id).first()
@classmethod
def find_by_question_and_survey(cls, question_id, survey_id):
return cls.query.filter_by(question_id=question_id).filter_by(
survey_id=survey_id).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_all(cls) -> List["QuestionModel"]:
return cls.query.all()
class Coupon(db.Model, Base):
"""Model for Udemy course coupon db table."""
__tablename__ = "coupons"
id = db.Column(db.Integer, primary_key=True)
courseId = db.Column(db.Integer, db.ForeignKey("courses.id"))
code = db.Column(db.String, nullable=False)
link = db.Column(db.String, nullable=False)
price = db.Column(db.Numeric(4, 2), nullable=False)
utcExpirationISO = db.Column(db.DateTime(timezone=True), nullable=False)
def __init__(
self,
code: str,
link: str,
utcExpirationISO: str,
price: float,
courseId: int = None,
update_db: bool = True,
):
"""Create record and add to db, translating the expiration date to utc."""
self.courseId = courseId
self.code = code
self.utcExpirationISO = dateutil.parser.parse(utcExpirationISO)
self.price = price
self.link = link
if update_db:
self.update_db()
def to_dict(self):
"""Return the called upon resource to dictionary format."""
return {
"id": self.id,
"courseId": self.courseId,
"code": self.code,
"link": self.link,
"price": float(self.price),
"utcExpirationISO": datetime.isoformat(self.utcExpirationISO),
}
def is_valid(self) -> bool:
"""Return boolean representing whether the coupon is valid."""
return self.utcExpirationISO > datetime.now(utc)
def update(self, updated_data):
"""Update with new data."""
for key, value in updated_data.items():
if key == "id":
continue
setattr(self, key, value)
def __repr__(self):
"""Return a pretty print version of the retrieved resource."""
return f""" < CourseCoupon(id={self.id},
class ScoreTypeModel(db.Model):
__tablename__ = 'score_type'
id = db.Column(db.Integer, primary_key=True)
score_type_id = db.Column(db.String(80), nullable=False, unique=True)
title = db.Column(db.String(80), nullable=False)
items = db.Column(db.ARRAY(db.String), nullable=False)
calculation_rule_id = db.Column(db.Integer, default=1)
created_at = db.Column(db.DateTime(timezone=True), default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True), onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
survey_id = db.Column(db.Integer, db.ForeignKey('survey.id'), nullable=False)
survey = db.relationship('SurveyModel')
# score_type = db.relationship('UserScoreModel', lazy='dynamic')
def save_to_db(self) -> None:
db.session.add(self)
db.session.commit()
def save_to_db_all(self) -> None:
db.session.bulk_save_objects(self)
db.session.commit()
def delete_from_db(self) -> None:
db.session.delete(self)
db.session.commit()
@classmethod
def find_by_score_type_id(cls, score_type_id):
return cls.query.filter_by(score_type_id=score_type_id).first()
@classmethod
def find_by_survey_id(cls, survey_id) -> List["ScoreTypeModel"]:
return cls.query.filter_by(survey_id=survey_id).all()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_all(cls) -> List["ScoreTypeModel"]:
return cls.query.all()
class Order(db.Model):
__tablename__ = 'orders'
id = db.Column(db.Integer, primary_key=True)
customer_firstname = db.Column(db.String(80))
customer_lastname = db.Column(db.String(80))
customer_address = db.Column(db.String(80))
date = db.Column(db.DateTime(timezone=True), server_default=func.now())
total = db.Column(db.Float(precision=2))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('User')
products_ordered = db.relationship('ProductOrdered', lazy='dynamic')
order_status_id = db.Column(db.Integer, db.ForeignKey('order_status.id'))
order_status = db.relationship('OrderStatus')
def __init__(self, customer_firstname, customer_lastname, customer_address,
user_id):
self.customer_firstname = customer_firstname
self.customer_lastname = customer_lastname
self.customer_address = customer_address
self.total = 0
self.user_id = user_id
self.order_status_id = 1 # the first state
@classmethod
def find_by_id(cls, id):
return cls.query.filter_by(id=id).first()
@classmethod
def get_all(cls):
return cls.query.order_by(cls.date.desc()).all()
@classmethod
def find_by_user_id(cls, user_id):
return cls.query.filter_by(user_id=user_id).order_by(cls.date.desc())
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def add_product(self, product_id, product_quantity):
if (self.id == None):
self.save_to_db()
self.total += Product.find_by_id(product_id).price * product_quantity
self.save_to_db()
ProductOrdered(self.id, product_id, product_quantity).save_to_db()
def promote_status(self):
if self.order_status_id < 3:
self.order_status_id += 1
self.save_to_db()
class Matstamm(db.Model, ModelSupport):
__tablename__ = 'materialstamm'
id = db.Column(db.Integer, primary_key=True)
turbine_new = db.Column(db.String(250), nullable=False)
turbine_alt = db.Column(db.String(250), nullable=False)
stufe = db.Column(db.String(100), nullable=False)
prozessmatstamm = db.Column(db.String(100), nullable=False, unique=True)
technologie = db.Column(db.String(250), nullable=False)
beschichtung = db.Column(db.String(250), nullable=False)
werkstoff = db.Column(db.String(250), nullable=False)
date_created = db.Column(db.DateTime(timezone=True),
server_default=func.now())
last_updated = db.Column(db.DateTime(timezone=True),
server_default=func.now(),
server_onupdate=func.now())
def __repr__(self):
return '<Alias id:{} text:{}>'.format(self.id, self.text)
@staticmethod
def find_by_aid(mts_id):
assert (mts_id is not None), "Matstamm id shouldn't be null"
mts = Matstamm.query.get(mts_id)
return mts
@staticmethod
def create(mts_dict=None, mts_obj=None):
if mts_dict:
try:
return Matstamm(**mts_dict).insert()
except IntegrityError as e:
if e.orig.__class__.__name__ == "UniqueViolation":
abort(400, message='error.create_alias.already_exist')
else:
raise e
else:
return mts_obj.insert()
@staticmethod
def delete_by_prozessmatstamm(id):
Matstamm.query.get(id).delete()
db.session.commit()
class ShortURL(db.Model):
__tablename__ = 'urls'
id = db.Column(db.Integer, primary_key=True)
origin_url = db.Column(db.String(256), index=True)
shorten_url = db.Column(db.String(32), unique=True) # 务必建立唯一索引
created_at = db.Column(db.DateTime()) # start_at_desc
created_by = db.Column(db.Integer) # owner
is_public = db.Column(db.Integer, default=False) # 是否是公开的网址
def __repr__(self):
return "URL origin %s, shorten %s" % (self.origin_url[:16], self.shorten_url)
class UserScoreModel(db.Model):
__tablename__ = 'user_score'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(80), default="Unknown Name")
score = db.Column(db.Float)
created_at = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True),
onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
user_survey_id = db.Column(db.Integer,
db.ForeignKey('user_survey.id'),
nullable=False)
user_survey = db.relationship('UserSurveyModel')
score_type_id = db.Column(db.Integer,
db.ForeignKey('score_type.id'),
nullable=False)
# score_type = db.relationship('ScoreTypeModel')
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()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
@classmethod
def find_all(cls) -> List["UserScoreModel"]:
return cls.query.all()
class AnswerScoreModel(db.Model):
__tablename__ = 'answer_score'
id = db.Column(db.Integer, primary_key=True)
score = db.Column(db.Integer)
created_at = db.Column(db.DateTime(timezone=True),
default=datetime.datetime.utcnow)
updated_at = db.Column(db.DateTime(timezone=True),
onupdate=datetime.datetime.utcnow)
deleted = db.Column(db.Boolean, default=False)
user_answer = db.relationship('UserAnswerModel', lazy='dynamic')
def save_to_db(self):
db.session.add(self)
db.session.commit()
@classmethod
def find_by_score(cls, score):
return cls.query.filter_by(score=score).first()
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
first_name = db.Column(db.String(255))
last_name = db.Column(db.String(255))
email = db.Column(db.String(255), unique=True)
password = db.Column(db.String(255))
active = db.Column(db.Boolean())
confirmed_at = db.Column(db.DateTime())
roles = db.relationship('Role', secondary=roles_users,
backref=db.backref('users', lazy='dynamic'))
def __str__(self):
return self.email
def set_password(self, password):
self.password = encrypt_password(password)
class Users(UserMixin, db.Model):
"""Admin Model
model for user admin models, user is allowed to add a question to poll
"""
__tablename__ = "users"
__table_args__ = {'extend_existing': True}
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(30), unique=True, nullable=False)
admin = db.Column(db.Boolean, nullable=False, default=False)
mail = db.Column(db.String(40), unique=True, nullable = False)
password = db.Column(db.String(), nullable=False)
confirmed = db.Column(db.Boolean(), nullable = False, default=False)
confimed_on = db.Column(db.DateTime(), default = datetime.now())
def __repr__(self):
return f'{self.id}, {self.username}, {self.mail}, {self.password}'
class TftStreamModel(Base):
"""
Модель таблицы tft_stream.
Здесь записывается информации о начале стрима.
"""
__tablename__ = 'tft_stream'
id = db.Column(db.Integer, primary_key=True)
start_datetime = db.Column(db.DateTime())
match_id = db.Column(db.VARCHAR(25))
user_id = db.Column(db.Integer, db.ForeignKey('tft_user.id'))
user = db.orm.relationship('TftUserModel', backref='streams')
@property
def nickname(self):
return
@nickname.setter
def nickname(self, value):
user = TftUserModel.query.filter_by(nickname=value).first()
print(user)
if user:
self.user_id = user.id
else:
raise DbRecordNotExist
class Case(db.Model, json.Serialisable):
__tablename__ = 'case'
id = db.Column(db.Integer, primary_key=True)
deed_id = db.Column(db.Integer)
conveyancer_id = db.Column(db.Integer)
status = db.Column(db.String())
last_updated = db.Column(db.DateTime())
created_on = db.Column(db.DateTime())
case_ref = db.Column(db.String())
def __init__(self,
conveyancer_id,
deed_id=None,
status='Case created',
last_updated=None,
created_on=None,
case_ref=''):
if deed_id is not None:
self.deed_id = deed_id
self.conveyancer_id = conveyancer_id
self.status = status
self.case_ref = case_ref
if last_updated is not None:
self.last_updated = last_updated
else:
self.last_updated = datetime.now()
if created_on is not None:
self.created_on = created_on
else:
self.created_on = datetime.now()
def save(self):
db.session.add(self)
db.session.commit()
@staticmethod
def all():
return Case.query.all()
@staticmethod
def all_with_borrowers():
return db.session.query(Case, Borrower).outerjoin(Borrower).all()
@staticmethod
def get(id_):
return Case.query.filter_by(id=id_).first()
@staticmethod
def get_by_deed_id(deed_id):
return Case.query.filter_by(deed_id=deed_id).first()
@staticmethod
def delete(id_):
case = Case.query.filter_by(id=id_).first()
if case is None:
return case
db.session.delete(case)
db.session.commit()
return case
class TestModel(db.Model):
__tablename__ = 'test'
attr1 = db.Column(db.String(32))
attr2 = db.Column(db.Integer())
attr3 = db.Column(db.DateTime()) # 必须DT都大写
class Coupon(db.Model, Base):
"""Model for Udemy course coupon db table."""
__tablename__ = "coupons"
id = db.Column(db.Integer, primary_key=True)
courseId = db.Column(db.Integer, db.ForeignKey("courses.id"))
link = db.Column(db.String, nullable=False)
price = db.Column(db.Numeric(4, 2), nullable=False)
utcExpirationISO = db.Column(db.DateTime(timezone=True), nullable=False)
def __init__(
self,
link: str,
utcExpirationISO: str,
price: float,
courseId: int = None,
update_db: bool = True,
):
"""Create record and add to db, translating the expiration date to utc."""
self.courseId = courseId
self.utcExpirationISO = dateutil.parser.parse(utcExpirationISO)
self.price = price
self.link = link
if update_db:
self.update_db()
def to_dict(self):
"""Return the called upon resource to dictionary format."""
return {
"id": self.id,
"courseId": self.courseId,
"link": self.link,
"price": float(self.price),
"utcExpirationISO": datetime.isoformat(self.utcExpirationISO),
}
def is_valid(self) -> bool:
"""Return boolean representing whether the coupon is valid."""
return self.utcExpirationISO > datetime.now(utc)
def update_from_patch(self, json_patch: Dict):
"""Update based on JsonPatch."""
current_data = self.to_dict()
# Apply patch to existing dict
updated_data = jsonpatch.apply_patch(current_data, json_patch)
# Apply the patched dictionary back to the model
for key, value in updated_data.items():
setattr(self, key, value)
self.update_db()
def __repr__(self):
"""Return a pretty print version of the retrieved resource."""
return f""" < CourseCoupon(id={self.id},
class User(db.Model,UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(256), index=True)
password_hash = db.Column(db.String(256))
user_type = db.Column(db.Integer) # 用户类型,1表示超级管理员,2表示普通用户
email = db.Column(db.String(64), unique=True, index=True)
member_since = db.Column(db.DateTime(), default=datetime.now)
last_seen = db.Column(db.DateTime(), default=datetime.now)
confirmed = db.Column(db.Boolean, default=True) # TODO some problems with the sendmail lib or config
def ping(self):
self.last_seen = datetime.now()
db.session.add(self)
@property
def password(self):
raise AttributeError("No password attr")
@password.setter
def password(self, pwd):
self.password_hash = generate_password_hash(pwd)
def check_password(self,pwd):
return check_password_hash(self.password_hash, pwd)
###
def generate_confirmation_token(self, expiration=3600):
s = Serializer(current_app.config['SECRET_KEY'], expiration)
return s.dumps({'confirm': self.id})
def confirm(self, token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return False
if data.get('confirm') != self.id:
return False
self.confirmed = True
db.session.add(self)
return True
def generate_reset_token(self, expiration=3600):
s = Serializer(current_app.config['SECRET_KEY'], expiration)
return s.dumps({'reset': self.id})
def reset_password(self, token, new_password):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return False
if data.get('reset') != self.id:
return False
self.password = new_password
db.session.add(self)
return True
def generate_email_change_token(self, new_email, expiration=3600):
s = Serializer(current_app.config['SECRET_KEY'], expiration)
return s.dumps({'change_email': self.id, 'new_email': new_email})
def change_email(self, token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return False
if data.get('change_email') != self.id:
return False
new_email = data.get('new_email')
if new_email is None:
return False
if self.query.filter_by(email=new_email).first() is not None:
return False
self.email = new_email
db.session.add(self)
return True
###
def generate_auth_token(self, expiration):
s = Serializer(current_app.config['SECRET_KEY'],
expires_in=expiration)
return s.dumps({'id': self.id}).decode('ascii')
@staticmethod
def verify_auth_token(token):
s = Serializer(current_app.config['SECRET_KEY'])
try:
data = s.loads(token)
except:
return None
return User.query.get(data['id'])