class PointStatusModel(db.Model, BaseMixin): __tablename__ = 'point_status' student_id: str = db.Column(db.String, db.ForeignKey('student.id', ondelete='CASCADE'), primary_key=True) good_point: int = db.Column(db.Integer, default=0) bad_point: int = db.Column(db.Integer, default=0) penalty_level: int = db.Column(db.Integer, default=0) penalty_status: bool = db.Column(db.Boolean, default=False) def __init__(self, student_id: str): self.student_id = student_id @staticmethod def get_point_status(student_id: str): point_status: PointStatusModel = PointStatusModel.query.filter_by(student_id=student_id).first() if point_status is None: point_status = PointStatusModel(student_id).save() return { 'badPoint': point_status.bad_point, 'goodPoint': point_status.good_point, 'penaltyLevel': point_status.penalty_level, 'penaltyStatus': point_status.penalty_status, 'advice': get_advice(point_status.bad_point) }
class NoticeModel(db.Model, BaseMixin): __tablename__ = 'notice' id: int = db.Column(db.Integer, primary_key=True) post_date: datetime = db.Column(db.DateTime) title: str = db.Column(db.String(50)) content: str = db.Column(db.String(2000)) def __init__(self, title: str, content: str): self.post_date: datetime = self.kst_now() self.title: str = title self.content: str = content @staticmethod def get_notice_list() -> dict: return { 'noticeList': [ { 'id': notice.id, 'postDate': str(notice.post_date), 'title': notice.title } for notice in NoticeModel.query.order_by(desc(NoticeModel.post_date)).all() ] } @staticmethod def get_notice(notice_id) -> dict: notice: NoticeModel = NoticeModel.query.filter_by(id=notice_id).first() if notice is None: raise NoContentException() return { 'content': notice.content, 'title': notice.title, 'postDate': str(notice.post_date) }
class RuleModel(db.Model, BaseMixin): __tablename__ = 'rule' id: int = db.Column(db.Integer, primary_key=True) post_date: datetime = db.Column(db.DateTime) title: str = db.Column(db.String(50)) content: str = db.Column(db.String(200)) def __init__(self, title: str, content: str): self.post_date: datetime = BaseMixin.kst_now() self.title: str = title self.content: str = content @staticmethod def get_rule_list() -> dict: return { 'ruleList': [ { 'id': rule.id, 'postDate': str(rule.post_date), 'title': rule.title } for rule in RuleModel.query.all() ] } @staticmethod def get_rule(rule_id) -> dict: rule: RuleModel = RuleModel.query.filter_by(id=rule_id).first() if rule is None: raise NoContentException() return { 'content': rule.content, 'title': rule.title, 'postDate': str(rule.post_date) }
class Task(db.Model): __tablename__ = 'task' task_id = db.Column(db.Integer(), primary_key = True, autoincrement = True) task_name = db.Column(db.String(64), nullable = False) description = db.Column(db.Text, nullable = False, default = '') deadline = db.Column(db.DateTime(), nullable = False) problems = db.relationship( 'Problem', secondary = 'problem_in_task', backref = db.backref('tasks', lazy = 'dynamic'), lazy = 'dynamic' ) groups = db.relationship( 'UserGroup', secondary = 'task_for_usergroup', backref = db.backref('tasks', lazy = 'dynamic'), lazy = 'dynamic' ) @property def available(self): return self.deadline > datetime.now()
class PointHistoryModel(db.Model, BaseMixin): __tablename__ = 'point_history' id: int = db.Column(db.Integer, primary_key=True) student_id: str = db.Column( db.String, db.ForeignKey('student.id', ondelete='CASCADE')) point_id: int = db.Column( db.Integer, db.ForeignKey('point_item.id', ondelete='CASCADE')) point_date: date = db.Column(db.Date) def __init__(self, student_id: str, point_id: int): self.student_id: str = student_id self.point_id: int = point_id self.point_date: date = date.today() @staticmethod def get_point_history(student_id: str): histories: List[ 'PointHistoryModel'] = PointHistoryModel.query.filter_by( student_id=student_id).all() result = {'point_history': []} for history in histories: temp = PointItemModel.get_point_item(history.point_id) temp['date'] = str(history.point_date) result['point_history'].append(temp) return result
class Role(db.Model): """ 角色表 """ __tablename__ = "Role" role_id = db.Column( "role_id", INTEGER, primary_key=True, nullable=False, autoincrement=True, doc="value is 1 means it is admin which has all permissions") role_name = db.Column("role_name", VARCHAR(255), nullable=False) create_time = db.Column("create_time", TIMESTAMP, nullable=False, server_default=func.now) status = db.Column("status", INTEGER(), default=1) # 角色状态, 1.正常 0.停用 def __init__(self, role_name): self.role_name = role_name @classmethod def insert(cls, role_name): one = cls(role_name) db.session.add(one) db.session.commit()
class User_role(db.Model): """ 用户角色关联表 """ __tablename__ = 'User_Role' RUN = 1 STOP = 0 ERROR = -1 id = db.Column("id", INTEGER(), primary_key=True, nullable=False, autoincrement=True) # 分组id user_id = db.Column("user_id", INTEGER(), db.ForeignKey("User.uid"), nullable=False) # 用户id role_id = db.Column("role_id", INTEGER(), db.ForeignKey("Role.id"), nullable=False) # 分组id status = db.Column('status', INTEGER(), nullable=False, default=1) # 分组状态 def __init__(self, user_id, role_id): self.User_id = user_id self.role_id = role_id @classmethod def insert(cls, user_id, role_id): one = cls(user_id, role_id) db.session.add(one) db.session.commit()
class Role_Module(db.Model): """ 角色模块关联表 """ __tablename__ = "Role_Module" id = db.Column("id", INTEGER(), primary_key=True, nullable=False, autoincrement=True) # 角色与模块关联id role_id = db.Column("role_id", INTEGER(), db.ForeignKey("Role.id"), nullable=False) # 角色id, 外键, 关联Role表id module_id = db.Column("module_id", INTEGER(), db.ForeignKey("Module.id"), nullable=False) # 模块id, 外键, 关联Module表id status = db.Column("status", INTEGER(), default=1) # 关联状态, 1.正常 0.停用 def __init__(self, role_id, module_id): self.role_id = role_id self.module_id = module_id @classmethod def insert(cls, role_id, module_id): one = cls(role_id, module_id) db.session.add(one) db.session.commit()
class MealModel(db.Model, BaseMixin): __tablename__ = 'meal' date: Date = db.Column(db.Date, primary_key=True) type: int = db.Column(db.Integer, primary_key=True) # type (0: 아침, 1: 점심, 2: 저녁) meal: str = db.Column(db.String(300)) # ||로 구분하여 입력 def __init__(self, date: Date, type: int, meal: str): self.date = date self.type = type self.meal = meal @staticmethod def get_meal(date: Date) -> dict: meal_list: List['MealModel'] = MealModel.query.filter_by( date=date).order_by('type') return { str(date): { type_list[meal.type]: meal.meal.split('||') for meal in meal_list } } @staticmethod def save_meal(date: Date, type: int, meal: str): meal_data: MealModel = MealModel.query.filter_by(date=date).first() if meal_data: meal_data.delete() MealModel(date, type, meal).save() @db.validates('type') def validate_type(self, key, type): self.assert_validation(type in [0, 1, 2]) return type
class StayApplyModel(db.Model, BaseMixin): __tablename__ = 'stay_apply' student_id: str = db.Column(db.String(20), db.ForeignKey('student.id', ondelete='CASCADE'), primary_key=True) value: int = db.Column(db.Integer) def __init__(self, student_id: str, value: int): self.student_id = student_id self.value = value @staticmethod def get_stay_apply_status(student_id: str) -> dict: stay: 'StayApplyModel' = StayApplyModel.query.filter_by( student_id=student_id).first() if stay is None: stay = StayApplyModel(student_id, 4).save() return {'value': stay.value} @staticmethod def post_stay_apply(student_id: str, value: int): stay = StayApplyModel.query.filter( StayApplyModel.student_id == student_id).first() if stay: stay.delete() StayApplyModel(student_id, value).save() @db.validates('value') def validate_value(self, key, value): self.assert_validation(1 <= value <= 4) return value
class Motto(BasicModel): __tablename__ = 'motto' content = db.Column(db.Text, unique=True, nullable=False) tag = db.Column(db.String(64), unique=True, nullable=False, default='all') source = db.Column(db.String(64), nullable=True, index=True) def __repr__(self): return '<Motto %r>' % self.id
class UserGroup(db.Model): __tablename__ = 'user_group' gid = db.Column(db.Integer, primary_key=True, autoincrement=True) group_name = db.Column(db.String(64), nullable=False) description = db.Column(db.Text, nullable=False, default='') users = db.relationship('User', secondary='user_in_group', backref=db.backref('groups', lazy='dynamic'), lazy='dynamic')
class PledgeHeaderRelationship(db.Model): __tablename__ = 'TBL_PLEDGEHEADERRELATIONSHIP' # Primary Key pledgeid = db.Column(db.String(150), primary_key=True, nullable=False) serialnumber = db.Column(db.String(50), primary_key=True, nullable=False) # Designation : PRIMARY / SOFT designation = db.Column(db.String(10), index=True) """ Relationships """ def __repr__(self): return "<Pledge {},{} ({})>".format( self.pledgeid, self.serialnumber, self.designation)
class Announcement(db.Model): __tablename__ = 'announcement' aid = db.Column(db.Integer(), primary_key=True, autoincrement=True) uid = db.Column(db.Integer, db.ForeignKey('user.uid')) title = db.Column(db.String(64), nullable=False) description = db.Column(db.Text(), nullable=False) publish_time = db.Column(db.DateTime(), nullable=False, default=datetime.now) user = db.relationship('User', backref='announcements')
class Tag(BaseModel, BaseMixin): __tablename__ = 'tag' id = db.Column(db.Integer, primary_key=True) tag_group_id = db.Column(db.Integer, db.ForeignKey('tag_group.id', ondelete='CASCADE')) name = db.Column(db.String(255), nullable=False) language = db.Column(db.String(255), nullable=False) def __init__(self, tag_group, name, language): self.tag_group = tag_group self.name = name self.language = language
class UnsignedStudentModel(db.Model, BaseMixin): __tablename__ = 'unsigned_student' uuid: str = db.Column(db.String, primary_key=True) name: str = db.Column(db.String) number: int = db.Column(db.Integer) email: str = db.Column(db.String) def __init__(self, name: str, number: int, email: str, uuid=None): self.uuid: str = UnsignedStudentModel.generate_uuid( email) if uuid is None else uuid self.name: str = name self.number: int = number self.email: str = email @staticmethod def get_unsigned_student(uuid: str) -> 'UnsignedStudentModel': unsigned_student = UnsignedStudentModel.query.filter_by( uuid=uuid).first() if unsigned_student is None: raise NoContentException() return unsigned_student @staticmethod def generate_uuid(key: str) -> str: while True: sha = hashlib.sha1() sha.update(key.encode()) key = sha.hexdigest[:5] if not UnsignedStudentModel.query.filter_by(uuid=key).first(): return key @db.validates('number') def validate_number(self, key, number): grade = number // 1000 class_ = number // 100 % 10 number_ = number % 100 self.assert_validation(grade in (1, 2, 3)) self.assert_validation(class_ in (1, 2, 3, 4)) self.assert_validation(number_ in range(1, 21)) return number @db.validates('email') def validate_email(self, key, email): self.assert_validation( re.match(r"^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-][email protected]$", email) is not None) return email
class FindPWModel(db.Model, BaseMixin): __tablename__ = 'find_pw' id = db.Column(db.String(32), primary_key=True, default=str(uuid4())) student = db.Column(db.String(20), db.ForeignKey('student.id', ondelete='CASCADE')) date = db.Column(db.DateTime, default=BaseMixin.kst_now()) def __init__(self, student): self.student = student self.save() @staticmethod def check_uuid(uuid: str): find_pw = FindPWModel.query.filter_by(id=uuid).first() StudentModel.reset_pw(find_pw.student)
class Stage(db.Model): __tablename__ = 'TBL_STAGE' """ CONSTRAINT PK_STAGE PRIMARY KEY (STAGEID) """ stageid = db.Column(db.Float, primary_key=True, nullable=False, index=True) stage = db.Column(db.String(50), index=True) stagealias = db.Column(db.String(50)) description = db.Column(db.String(50)) history = db.Column(db.SmallInteger, nullable=False, default=0) # (mssql:bit) Yes(-1)/No(0, Null)/Error(other) """ Relationships """ def __repr__(self): return "<Stage {} {} history {}>".format( self.stageid, self.stage, self.history)
class PledgeInstalmentsNextActive(db.Model): __tablename__ = 'TBL_PLEDGEINSTALMENTS_NEXTACTIVE' """ CONSTRAINT PK_PLEDGEINSTALMENTS_NEXTACTIVE PRIMARY KEY (PLEDGEID) """ pledgeid = db.Column(db.String(150), primary_key=True, nullable=False, index=True) instalmentid = db.Column(db.Float, index=True) # ! Important instalment = db.Column(db.Float, index=True) # mssql: money ! Important datedue = db.Column(db.DateTime, index=True) # ! Important amountpaid = db.Column(db.Float, index=True) # mssql: money """ Relationships """ def __repr__(self): return "<PledgeN {} Ins {} ${} Due {}>".format( self.pledgeid, self.instalmentid, self.instalment, self.datedue)
class TestSet(db.Model): __tablename__ = 'testset' testset_id = db.Column(db.Integer, primary_key=True, autoincrement=True) full_score = db.Column(db.DECIMAL(6, 2), nullable=False, server_default='0') tests = db.relationship('Test', secondary='test_in_testset', backref=db.backref('testsets', lazy='dynamic'), lazy='dynamic') submissions = db.relationship('Submission', backref='testset', lazy='dynamic')
class SurrogatePK(object): """A mixin that adds a surrogate integer 'primary key' column named ``id`` to any declarative-mapped class. """ __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True) @classmethod def get_by_id(cls, id): try: id = int(id) except: raise TypeError('id must int') if id <= 0: raise ValueError('ID must not be negative or zero!') if any((isinstance(id, basestring) and id.isdigit(), isinstance(id, (int, float))), ): return cls.query.get(int(id)) return None @classmethod def query_one(cls, **kwargs): if kwargs: return cls.query.filter_by(**kwargs).first() return None
class DiskFolder(Base, SurrogatePK): __tablename__ = 'f_folder' id = db.Column(db.INTEGER, primary_key=True) folder_name = db.Column(db.String(500)) folder_path = db.Column(db.String(1000)) group_id = db.Column(db.INTEGER) user_id = db.Column(db.INTEGER) is_trash = db.Column(db.INTEGER, default=0) is_share = db.Column(db.INTEGER, default=0) is_user_group = db.Column(db.INTEGER) create_time = db.Column(db.DATETIME, default=datetime.now()) update_time = db.Column(db.DATETIME) def __init__(self, folder_name, folder_path, group_id, user_id, **kwargs): db.Model.__init__(self, folder_name=folder_name, folder_path=folder_path, group_id=group_id, user_id=user_id, **kwargs)
class CompanyTagMapping(BaseModel, BaseMixin): __tablename__ = 'company_tag_mapping' id = db.Column(db.Integer, primary_key=True) company_id = db.Column(db.Integer, db.ForeignKey('company.id', ondelete='CASCADE')) tag_group_id = db.Column(db.Integer, db.ForeignKey('tag_group.id', ondelete='CASCADE')) def __init__(self, company_id: int, tag_group_id: int): self.company_id = company_id self.tag_group_id = tag_group_id @staticmethod def get_company_tag_mapping(company_id: int, tag_group_id: int): return CompanyTagMapping.query.filter_by( company_id=company_id, tag_group_id=tag_group_id).first()
class Tag(db.Model): __tablename__ = 'tag' tag_id = db.Column(db.Integer(), primary_key=True, autoincrement=True) tag_name = db.Column(db.String(64), nullable=False, unique=True) problems = db.relationship('Problem', secondary='tag_of_problem', backref=db.backref('tags', lazy='dynamic'), lazy='dynamic') def __repr__(self): return self.tag_name @property def id(self): return self.tag_id
def ReferenceCol(tablename, nullable=False, pk_name='id', **kwargs): """Column that adds primary key foreign key reference. Usage: :: category_id = ReferenceCol('category') category = relationship('Category', backref='categories') """ return db.Column(db.ForeignKey("{0}.{1}".format(tablename, pk_name)), nullable=nullable, **kwargs) # pragma: no cover
class Submission(db.Model): __tablename__ = 'submission' sid = db.Column(db.Integer(), primary_key=True, autoincrement=True) pid = db.Column(db.Integer(), db.ForeignKey('problem.pid')) uid = db.Column(db.Integer(), db.ForeignKey('user.uid')) testset_id = db.Column(db.Integer, db.ForeignKey('testset.testset_id')) result = db.Column(db.Text, nullable=True) score = db.Column(db.DECIMAL(6, 2), nullable=True) code = db.Column(db.Text, nullable=False) is_solution = db.Column(db.Boolean(), nullable=False, server_default='0') submit_time = db.Column(db.DateTime(), nullable=False, default=datetime.now) problem = db.relationship('Problem', backref=db.backref('submissions', lazy='dynamic')) user = db.relationship('User', backref=db.backref('submissions', lazy='dynamic')) tasks = db.relationship('Task', secondary='submission_in_task', backref=db.backref('submissions', lazy='dynamic'), lazy='dynamic') @property def full_score(self): return self.testset.full_score if self.testset else None @property def status(self): if not current_user.is_teacher: for task in self.tasks.all(): if task.available: return 'hidden' if self.result is None: return 'pending' if self.result == 'running': return 'running' if self.result == 'system_error': return 'system_error' if self.full_score == self.score: return 'accepted' if self.full_score > self.score: return 'wrong_answer' return 'other'
class BasicModel(db.Model): __abstract__ = True id = db.Column(db.String(64), primary_key=True, unique=True, nullable=True) create_time = db.Column(db.DateTime, default=datetime.utcnow) def init_id(self): self.id = str(uuid.uuid4().hex) def add(self): db.session.add(self) db.session.commit() def update(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit()
class FacilityReportModel(db.Model, BaseMixin): __tablename__ = 'facility_report' id: int = db.Column(db.Integer, primary_key=True) student_id: str = db.Column( db.String, db.ForeignKey('student.id', ondelete='CASCADE')) room: int = db.Column(db.Integer) content: str = db.Column(db.String) def __init__(self, student_id: str, room: int, content: str): self.student_id: str = student_id self.room: int = room self.content: str = content @staticmethod def post_facility_report(student_id: str, room: int, content: str): FacilityReportModel(student_id, room, content).save() @db.validates('room') def validate_room(self, key, room): self.assert_validation(201 <= room <= 518) return room
class Backup(Base, SurrogatePK): __tablename__ = 'f_backup' id = db.Column(db.Integer, primary_key=True) show_name = db.Column(db.String(500)) file_name = db.Column(db.String(500)) path = db.Column(db.String(600)) file_size = db.Column(db.INTEGER) user_id = db.Column(db.INTEGER) is_trash = db.Column(db.INTEGER, default=0) is_share = db.Column(db.INTEGER) create_time = db.Column(db.DATETIME, default=datetime.now()) def __init__(self, show_name, file_name, file_size, **kwargs): db.Model.__init__(self, show_name=show_name, file_name=file_name, file_size=file_size, **kwargs)
class PointItemModel(db.Model, BaseMixin): __tablename__ = 'point_item' id = db.Column(db.Integer, primary_key=True) reason: str = db.Column(db.String) point: int = db.Column(db.Integer) type: bool = db.Column(db.Boolean) def __init__(self, reason: str, point: int, type: bool): self.reason: str = reason self.point: int = point self.type: bool = type @staticmethod def get_point_item(id: int): point_item: PointItemModel = PointItemModel.query.filter_by(id=id).first() return { 'point': point_item.point, 'pointType': point_item.type, 'reason': point_item.reason }