class Question(db.Model):
__tablename__ = 'question'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
question_name = db.Column(db.String(64))
description = db.Column(db.String(200))
task_id = db.Column(db.Integer, db.ForeignKey('task.id'))
task = db.relationship("Task", back_populates="questions")
users = db.relationship("User_question", back_populates="question")
def __repr__(self):
return 'id={}, question_name={},description={}'.format(
self.id, self.question_name, self.description)
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
user_quesions = db.session.query(User_question).filter(
User_question.question_id == self.id)
for user_quesion in user_quesions:
# print(user_quesion.question_id)
user_quesion.delete()
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
def add_question2task(self, task):
self.task_id = task.id
db.session.commit()
class Unit(db.Model):
__tablename__ = 'unit'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
unit_id = db.Column(db.String(20))
unit_name = db.Column(db.String(64))
tasks = db.relationship("Task", back_populates="unit")
users = db.relationship("User_unit", back_populates="unit")
def __repr__(self):
return 'unit_id={}, unit_name={}'.format(self.unit_id, self.unit_name)
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
for task in self.tasks:
# print(task)
task.delete()
user_units = db.session.query(User_unit).filter(
User_unit.unit_id == self.id)
for user_unit in user_units:
user_unit.delete()
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
class Script(db.Model):
id = db.Column(db.Integer, primary_key=True)
text = db.Column(db.Text())
src_title = db.Column(db.String(64))
src_part = db.Column(db.Integer)
def __repr__(self):
return '<Script {}>'.format(self.src_title + "-" + str(self.src_part))
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), unique=True)
email = db.Column(db.String(128), unique=True)
passwd_hash = db.Column(db.String(128))
def __repr__(self):
return '<User {}>'.format(self.username)
def set_password(self, password):
self.passwd_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.passwd_hash, password)
class Task(db.Model):
__tablename__ = 'task'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
task_name = db.Column(db.String(64))
description = db.Column(db.String(300))
create_time = db.Column(db.DateTime, default=datetime.now)
due_time = db.Column(db.DateTime)
pdf_url = db.Column(db.String(140))
pdf_id = db.Column(db.String(140))
unit_id = db.Column(db.Integer, db.ForeignKey('unit.id'))
unit = db.relationship("Unit", back_populates="tasks")
questions = db.relationship("Question", back_populates="task")
users = db.relationship("User_task", back_populates="task")
def __repr__(self):
return 'id={}, task_name={},description={}'.format(
self.id, self.task_name, self.description)
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
if self.pdf_url is not None and self.pdf_url != 'deleted':
pdf_id = self.pdf_id
file = route.drive.CreateFile({'id': pdf_id})
file.Delete()
for question in self.questions:
question.delete()
user_tasks = db.session.query(User_task).filter(
User_task.task_id == self.id)
for user_task in user_tasks:
user_task.delete()
db.session.delete(self)
db.session.commit()
def delete_pdf(self):
pdf_id = self.pdf_id
file = route.drive.CreateFile({'id': pdf_id})
file.Delete()
self.pdf_id = None
self.pdf_url = None
self.update()
def update(self):
db.session.commit()
def add_task2unit(self, unit):
self.unit_id = unit.id
db.session.commit()
def get_task_users(self):
users = []
for user_task in self.users:
user_id = user_task.user_id
users.append(
db.session.query(User).filter(User.id == user_id).one())
return users
class User_question(db.Model):
__tablename__ = 'user_question'
question_id = db.Column(db.Integer,
db.ForeignKey('question.id'),
primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
record_url = db.Column(db.String(140))
record_id = db.Column(db.String(140))
record_title = db.Column(db.String(140))
update_time = db.Column(db.DateTime, default=datetime.now)
question = db.relationship("Question", back_populates="users")
user = db.relationship("User", back_populates="questions")
@staticmethod
def add_user_question(user, question, record_url, record_id, record_title):
user_has_question = User_question(user=user,
question=question,
record_url=record_url,
record_id=record_id,
record_title=record_title)
db.session.add(user_has_question)
db.session.commit()
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
class User_task(db.Model):
__tablename__ = 'user_task'
task_id = db.Column(db.Integer, db.ForeignKey('task.id'), primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
comment = db.Column(db.String(140))
record_url = db.Column(db.String(140))
record_id = db.Column(db.String(140))
record_title = db.Column(db.String(140))
record_create_time = db.Column(db.DateTime)
mark = db.Column(db.Float)
task = db.relationship("Task", back_populates="users")
user = db.relationship("User", back_populates="tasks")
@staticmethod
def add_user_task(user, task):
user_has_task = User_task(user=user, task=task)
db.session.add(user_has_task)
db.session.commit()
# @staticmethod
# def get_user_task(user_id, task_id):
# user = db.session.query(User).filter(User.id == user_id).one()
# for task in user.tasks:
# if task.task_id == task_id:
# print(task.mark)
# return task
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
# this_task = db.session.query(Task).filter(Task.id == self.task_id).first()
# questions = this_task.tasks
# for question in questions:
# User_question.query.filter_by(question_id=question.id, user_id=self.user_id).first().delete()
questions = self.task.questions
for question in questions:
user_question = User_question.query.filter_by(
question_id=question.id, user_id=self.user_id).first()
if user_question:
user_question.delete()
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
class User_unit(db.Model):
__tablename__ = 'user_unit'
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
unit_id = db.Column(db.Integer, db.ForeignKey('unit.id'), primary_key=True)
user = db.relationship("User", back_populates="units")
unit = db.relationship("Unit", back_populates="users")
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
tasks = self.unit.tasks
for task in tasks:
user_task = User_task.query.filter_by(
task_id=task.id, user_id=self.user_id).first()
if user_task:
user_task.delete()
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
class Voice(db.Model, Serializer):
id = db.Column(db.Integer, primary_key=True)
filename = db.Column(db.String(128))
sentence = db.Column(db.String(256))
duration = db.Column(db.Interval)
script_id = db.Column(db.Integer, db.ForeignKey('script.id'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
created_at = db.Column(db.DateTime, default=datetime.utcnow)
def __repr__(self):
return '<Voice {}>'.format(str(self.id) + ":" + self.filename)
def serialize(self):
return Serializer.serialize(self)
class User(db.Model, UserMixin):
__tablename__ = 'user'
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_number = db.Column(db.String(64), unique=True,
index=True) # student number / staff number
first_name = db.Column(db.String(40))
last_name = db.Column(db.String(40))
email = db.Column(db.String(64), unique=True, index=True)
password_hash = db.Column(db.String(128))
is_teacher = db.Column(db.Integer, default=0)
is_activated = db.Column(db.Integer, default=0)
# units = db.relationship('Unit', secondary=user_unit,
# backref=db.backref('users', lazy='dynamic'),
# lazy='dynamic')
units = db.relationship("User_unit", back_populates="user")
tasks = db.relationship("User_task", back_populates="user")
questions = db.relationship("User_question", back_populates="user")
def __repr__(self):
return 'id={}, user_number={}, first_name={}, last_name={}, email={},password_hash={}, is_teacher={}, is_activated={}'.format(
self.id, self.user_number, self.first_name, self.last_name,
self.email, self.password_hash, self.is_teacher, self.is_activated)
def as_dict(self):
return {c.name: getattr(self, c.name) for c in self.__table__.columns}
# encode the password to hash code
def set_password(self, password):
self.password_hash = generate_password_hash(password)
# check password
def check_password(self, password):
return check_password_hash(self.password_hash, password)
def add(self):
db.session.add(self)
db.session.commit()
def delete(self):
user_units = db.session.query(User_unit).filter(
User_unit.user_id == self.id)
for user_unit in user_units:
user_unit.delete()
user_tasks = db.session.query(User_task).filter(
User_task.user_id == self.id)
for user_task in user_tasks:
user_task.delete()
user_quesions = db.session.query(User_question).filter(
User_question.user_id == self.id)
for user_quesion in user_quesions:
# print(user_quesion.question_id)
user_quesion.delete()
db.session.delete(self)
db.session.commit()
def update(self):
db.session.commit()
def student2teacher(self):
self.is_teacher = 1
db.session.commit()
def add_unit(self, unit):
user_has_unit = User_unit(user=self, unit=unit)
db.session.add(user_has_unit)
db.session.commit()
def delete_unit(self, unit):
user_has_unit = User_unit(user=self, unit=unit)
db.session.delete(user_has_unit)
db.session.commit()
# you can use this method to get all questions in this task of a student
def get_task_questions(self, task_id):
task_questions = []
for question in self.questions:
# print(question.question_id)
this_question = db.session.query(Question).filter(
Question.id == question.question_id).first()
# print(this_question)
if this_question.task_id == task_id:
task_questions.append(this_question)
return task_questions
def get_student_units(self):
student_units = []
for unit in self.units:
student_units.append(
db.session.query(Unit).filter(Unit.id == unit.unit_id).first())
return student_units
# you can use this method to get the mark of the task of a student of a specific task
def get_task_mark(self, task_id):
for task in self.tasks:
if task.task_id == task_id:
return task.mark
# you can use this method to get the comment for the task of a student of a specific task
def get_task_comment(self, task_id):
for task in self.tasks:
if task.task_id == task_id:
return task.comment
# you can use this method to get the feedback for the task of a student of a specific task
def get_task_feedbackURL(self, task_id):
for task in self.tasks:
if task.task_id == task_id:
return task.record_url
# get the record url of this question of this student
def get_question_record_url(self, question_id):
for question in self.questions:
if question.question_id == question_id:
return question.record_url
def get_user_task(self, task_id):
user = db.session.query(User).filter(User.id == self.id).one()
for task in user.tasks:
if task.task_id == task_id:
return task
@staticmethod
def get_user_list():
return db.session.query(User).all()
@staticmethod
def get_by_user_number(usernumber):
return db.session.query(User).filter(
User.user_number == usernumber).first()
@staticmethod
def get_by_id(user_id):
return db.session.query(User).filter(User.id == user_id).first()
def get_jwt(self, expire=3600):
return jwt.encode({
'email': self.email,
'exp': time.time() + expire
},
current_app.config['SECRET_KEY'],
algorithm='HS256').decode('utf-8')
@staticmethod
def verify_jwt(token):
try:
email = jwt.decode(token,
current_app.config['SECRET_KEY'],
algorithms=['HS256'])
email = email['email']
except:
return
return User.query.filter_by(email=email).first()
def email_is_verified(self):
self.is_activated = 1
self.update()
print("===============user email verified===========")
return