class HealthServiceTimeSlot(db.Model): __tablename__ = 'health_service_timeslots' id = db.Column(db.Integer, primary_key=True, autoincrement=True) start = db.Column('start', db.DateTime(timezone=True), nullable=False, info={'label': u'เริ่ม'}) end = db.Column('end', db.DateTime(timezone=True), nullable=False, info={'label': u'สิ้นสุด'}) service_id = db.Column(db.ForeignKey('health_service_services.id')) site_id = db.Column(db.ForeignKey('health_service_sites.id')) staff_id = db.Column(db.ForeignKey('staff_account.id'), nullable=False) created_by = db.relationship('StaffAccount') quota = db.Column(db.Integer, info={'label': 'Quota'}) cancelled_at = db.Column(db.DateTime(timezone=True)) site = db.relationship(HealthServiceSite, backref=db.backref('slots')) service = db.relationship(HealthServiceService, backref=db.backref('slots')) @property def remaining(self): return self.quota - len(self.bookings) @property def is_available(self): return self.remaining > 0
class DocReceiveRecord(db.Model): __tablename__ = 'doc_receive_records' id = db.Column(db.Integer, primary_key=True, autoincrement=True) predefined_comment = db.Column( db.String(255), info={ 'label': 'Predefined Comment', 'choices': [(c, c) for c in [u'แจ้งเพื่อทราบ', u'แจ้งเพื่อพิจารณา', u'ขอความร่วมมือเข้าร่วม']] }) comment = db.Column(db.Text, info={'label': 'Additional Comment'}) sent_at = db.Column(db.DateTime(timezone=True)) sender_id = db.Column(db.ForeignKey('staff_account.id')) round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id')) round_org = db.relationship(DocRoundOrg, backref=db.backref('sent_records', lazy='dynamic')) doc_id = db.Column(db.ForeignKey('doc_documents.id')) rejected = db.Column(db.Boolean(), default=False) members = db.relationship(StaffPersonalInfo, secondary=receipt_receivers) sender = db.relationship(StaffAccount) doc = db.relationship(DocDocument, backref=db.backref('doc_receipts', lazy='dynamic', cascade='all, delete-orphan'))
class EduQACourse(db.Model): __tablename__ = 'eduqa_courses' id = db.Column(db.Integer, primary_key=True, autoincrement=True) th_code = db.Column(db.String(255), nullable=False, info={'label': u'รหัส'}) en_code = db.Column(db.String(255), nullable=False, info={'label': u'English Code'}) th_name = db.Column(db.String(255), nullable=False, info={'label': u'ชื่อภาษาไทย'}) en_name = db.Column(db.String(255), nullable=False, info={'label': u'English Title'}) semester = db.Column(db.String(), info={'label': u'ภาคการศึกษา'}) academic_year = db.Column(db.String(), info={'label': u'ปีการศึกษา'}) th_desc = db.Column(db.Text(), info={'label': u'คำอธิบายรายวิชา'}) en_desc = db.Column(db.Text(), info={'label': u'Description'}) lecture_credit = db.Column(db.Numeric(), default=0, info={'label': u'หน่วยกิตบรรยาย'}) lab_credit = db.Column(db.Numeric(), default=0, info={'label': u'หน่วยกิตปฏิบัติ'}) created_at = db.Column(db.DateTime(timezone=True)) updated_at = db.Column(db.DateTime(timezone=True)) creator_id = db.Column(db.ForeignKey('staff_account.id')) updater_id = db.Column(db.ForeignKey('staff_account.id')) creator = db.relationship(StaffAccount, foreign_keys=[creator_id]) updater = db.relationship(StaffAccount, foreign_keys=[updater_id]) category_id = db.Column(db.ForeignKey('eduqa_course_categories.id')) category = db.relationship(EduQACourseCategory, backref=db.backref('courses', lazy='dynamic')) revision_id = db.Column(db.ForeignKey('eduqa_curriculum_revisions.id')) revision = db.relationship(EduQACurriculumnRevision, backref=db.backref('courses', lazy='dynamic')) @property def credits(self): return self.lecture_credit + self.lab_credit
class EduQAInstructor(db.Model): __tablename__ = 'eduqa_course_instructors' id = db.Column(db.Integer, primary_key=True, autoincrement=True) account_id = db.Column(db.ForeignKey('staff_account.id')) account = db.relationship(StaffAccount, backref=db.backref('instructed_courses', lazy='dynamic')) courses = db.relationship('EduQACourse', secondary=course_instructors, backref=db.backref('instructors', lazy='dynamic')) @property def fullname(self): return self.account.personal_info.fullname
class HealthServiceBooking(db.Model): __tablename__ = 'health_service_bookings' id = db.Column(db.Integer, primary_key=True, autoincrement=True) slot_id = db.Column(db.ForeignKey('health_service_timeslots.id')) slot = db.relationship(HealthServiceTimeSlot, backref=db.backref('bookings')) cancelled = db.Column(db.DateTime(timezone=True)) created_at = db.Column(db.DateTime(timezone=True)) updated_at = db.Column(db.DateTime(timezone=True)) confirmed_at = db.Column(db.DateTime(timezone=True)) user_id = db.Column(db.ForeignKey('health_service_app_users.id')) user = db.relationship('HealthServiceAppUser', backref=db.backref('bookings'))
class DocDocument(db.Model): __tablename__ = 'doc_documents' id = db.Column(db.Integer, primary_key=True, autoincrement=True) round_id = db.Column(db.ForeignKey('doc_rounds.id')) round = db.relationship(DocRound, backref=db.backref('documents', lazy='dynamic', order_by='DocDocument.number', cascade='all, delete-orphan')) number = db.Column(db.Integer(), info={'label': u'Number'}) deadline = db.Column(db.DateTime(timezone=True), info={'label': 'Deadline'}) addedAt = db.Column(db.DateTime(timezone=True)) file_name = db.Column(db.String(255)) url = db.Column(db.String(255)) priority = db.Column(db.String(255), info={ 'label': 'Priority', 'choices': [(c, c) for c in [u'ปกติ', u'ด่วน', u'ด่วนที่สุด']] }) stage = db.Column(db.String(255), info={ 'label': 'Stage', 'choices': [(c, c) for c in [u'drafting', u'ready', u'sent']] }) title = db.Column(db.String(255), info={'label': 'Title'}) summary = db.Column(db.Text(), info={'label': 'Summary'}) comment = db.Column(db.Text(), info={'label': 'Comment'}) category_id = db.Column(db.ForeignKey('doc_categories.id')) category = db.relationship(DocCategory, backref=db.backref( 'documents', lazy='dynamic', cascade='all, delete-orphan')) def get_recipients(self, round_org_id): receipt = self.doc_receipts.filter_by(round_org_id=round_org_id, doc_id=self.id).first() if receipt: return receipt.members else: return []
class Test(db.Model): __tablename__ = 'tests' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False, unique=True) results = db.relationship('TestResult', cascade="delete", backref=db.backref('test', lazy=True))
class Role(db.Model): __tablename__ = 'sys_role' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(64), unique=True, comment=u'角色名称') describe = db.Column(db.String(200), unique=True, comment=u'角色描述') status = db.Column(db.Integer, default=1, comment=u'状态') createTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'创建时间') createBy = db.Column(db.String(50), comment=u'创建人') updateTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'修改时间') updateBy = db.Column(db.String(50), comment=u'修改人') # @property # def permissions(self): # permissions = Permission.query.join(sys_role_permission).join(Role).filter(Role.id == self.id) # return permissions menus = db.relationship('Menu', secondary=sys_role_menu, backref=db.backref('menus', lazy='dynamic')) # @property # def menus(self): # menus = Menu.query.join(sys_role_menu).join(Role).filter(Role.id == self.id).order_by(Menu.type, Menu.order).all() # return menus @property def permission_names(self): strs = '' for i in range(len(self.menus)): strs = strs + self.menus[i].name + ',' return strs @property def create_time(self): return self.createTime.strftime('%Y-%m-%d') @property def menu_ids(self): ids = [] for i in range(len(self.menus)): if self.menus[i].url is not None: ids.append(self.menus[i].id) return ids def __init__(self): pass def save(self): globals.db.add(self) globals.db.commit() def update(self): globals.db.commit() def delete(self): globals.db.delete(self) globals.db.commit() def __repr__(self): return '<Role %r>' % self.name
class EduQACurriculumnRevision(db.Model): __tablename__ = 'eduqa_curriculum_revisions' id = db.Column(db.Integer, primary_key=True, autoincrement=True) curriculum_id = db.Column(db.ForeignKey('eduqa_curriculums.id')) curriculum = db.relationship(EduQACurriculum, backref=db.backref('revisions')) revision_year = db.Column(db.Date(), nullable=False, info={'label': u'วันที่ปรับปรุงล่าสุด'}) def __str__(self): return u'{}: ปี {}'.format(self.curriculum, self.revision_year)
class VehicleBooking(db.Model): __tablename__ = 'scheduler_vehicle_bookings' id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True) vehicle_id = db.Column('vehicle_id', db.ForeignKey('scheduler_vehicle_resources.id'), nullable=False) vehicle = db.relationship('VehicleResource', backref=db.backref('bookings')) title = db.Column('title', db.String(255), nullable=False) init_milage = db.Column('init_milage', db.Integer, nullable=True) end_milage = db.Column('end_milage', db.Integer, nullable=True) toll_fee = db.Column('toll_fee', db.Float(), default=0.0) distance = db.Column('distance', db.Integer, nullable=True) init_location = db.Column('init_location', db.String(255), nullable=True) destination = db.Column('destination', db.String(255), nullable=True) start = db.Column('start', db.DateTime(timezone=True), nullable=False) end = db.Column('end', db.DateTime(timezone=True), nullable=False) iocode_id = db.Column('iocode_id', db.ForeignKey('iocodes.id')) iocode = db.relationship('IOCode', backref=db.backref('vehicle_bookings')) org_id = db.Column('org_id', db.ForeignKey('orgs.id')) org = db.relationship('Org', backref=db.backref('vehicle_bookings')) num_passengers = db.Column('num_passengers', db.Integer()) approved = db.Column('approved', db.Boolean(), default=False) closed = db.Column('closed', db.Boolean(), default=False) created_at = db.Column('created_at', db.DateTime(timezone=True), server_default=func.now()) created_by = db.Column('created_by', db.ForeignKey('staff_account.id')) updated_at = db.Column('updated_at', db.DateTime(timezone=True), server_default=None) updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id')) cancelled_at = db.Column('cancelled_at', db.DateTime(timezone=True), server_default=None) cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id')) approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id')) approved_at = db.Column('approved_at', db.DateTime(timezone=True), server_default=None) desc = db.Column('desc', db.Text()) google_event_id = db.Column('google_event_id', db.String(64)) google_calendar_id = db.Column('google_calendar_id', db.String(255))
class EduQAAcademicStaff(db.Model): __tablename__ = 'eduqa_academic_staff' id = db.Column(db.Integer, primary_key=True, autoincrement=True) roles = db.Column(db.String(), info={'label': u'บทบาท', 'choices': ( ('staff', u'อาจารย์ประจำ'), ('head', u'ประธานหลักสูตร'), ('committee', u'ผู้รับผิดชอบหลักสูตร') )}) curriculumn_id = db.Column(db.ForeignKey('eduqa_curriculum_revisions.id')) curriculumn = db.relationship(EduQACurriculumnRevision, backref=db.backref('staff'))
class SmartClassOnlineAccount(db.Model): __tablename__ = 'smartclass_scheduler_online_accounts' id = db.Column('id', db.Integer, primary_key=True, autoincrement=True) name = db.Column('name', db.String(), nullable=False) resource_type_id = db.Column( 'resource_type_id', db.ForeignKey('smartclass_scheduler_resource_types.id')) resource_type = db.relationship(SmartClassResourceType, backref=db.backref('resources')) def __str__(self): return self.name
class EduQACurriculum(db.Model): __tablename__ = 'eduqa_curriculums' id = db.Column(db.Integer, primary_key=True, autoincrement=True) program_id = db.Column(db.ForeignKey('eduqa_programs.id'), ) program = db.relationship(EduQAProgram, backref=db.backref('curriculums')) th_name = db.Column(db.String(), nullable=False, info={'label': u'ชื่อ'}) en_name = db.Column(db.String(), nullable=False, info={'label': 'Title'}) def __str__(self): return self.th_name
class DocRoundOrg(db.Model): __tablename__ = 'doc_round_orgs' id = db.Column(db.Integer, primary_key=True, autoincrement=True) sent_at = db.Column(db.DateTime(timezone=True)) round_id = db.Column(db.ForeignKey('doc_rounds.id')) org_id = db.Column(db.ForeignKey('orgs.id')) finished_at = db.Column(db.DateTime(timezone=True)) org = db.relationship('Org') round = db.relationship(DocRound, backref=db.backref('targets', lazy='dynamic', cascade='all, delete-orphan'))
class DocDocumentReach(db.Model): __tablename__ = 'doc_document_reaches' id = db.Column(db.Integer, primary_key=True, autoincrement=True) reached_at = db.Column(db.DateTime(timezone=True)) reacher_id = db.Column(db.ForeignKey('staff_account.id')) reacher = db.relationship(StaffAccount, backref=db.backref('doc_reaches', lazy='dynamic')) doc_id = db.Column(db.ForeignKey('doc_documents.id')) starred = db.Column(db.Boolean(False)) note = db.Column(db.Text()) doc = db.relationship(DocDocument, backref=db.backref('reaches', lazy='dynamic', cascade='all, delete-orphan')) round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id')) round_org = db.relationship(DocRoundOrg, backref=db.backref('doc_reaches', lazy='dynamic')) sender_comment = db.Column(db.Text()) receiver_comment = db.Column(db.Text()) receiver_commented_at = db.Column(db.DateTime(timezone=True)) created_at = db.Column(db.DateTime(timezone=True))
class EduQACourseSession(db.Model): __tablename__ = 'eduqa_course_sessions' id = db.Column(db.Integer, primary_key=True, autoincrement=True) course_id = db.Column(db.ForeignKey('eduqa_courses.id')) start = db.Column(db.DateTime(timezone=True), nullable=False, info={'label': u'เริ่ม'}) end = db.Column(db.DateTime(timezone=True), nullable=False, info={'label': u'สิ้นสุด'}) type_ = db.Column(db.String(255), info={'label': u'รูปแบบการสอน', 'choices': [(c, c) for c in (u'บรรยาย', u'ปฏิบัติการ', u'กิจกรรม', u'สอบ')]}) desc = db.Column(db.Text()) course = db.relationship(EduQACourse, backref=db.backref('sessions', lazy='dynamic')) instructors = db.relationship('EduQAInstructor', secondary=session_instructors, backref=db.backref('sessions', lazy='dynamic')) @property def total_hours(self): delta = self.end - self.start return u'{} ชม. {} นาที'.format(delta.seconds//3600, (delta.seconds//60)%60) @property def total_seconds(self): delta = self.end - self.start return delta.seconds
class DocRoundOrgReach(db.Model): __tablename__ = 'doc_round_org_reaches' id = db.Column(db.Integer, primary_key=True, autoincrement=True) reached_at = db.Column(db.DateTime(timezone=True)) reacher_id = db.Column(db.ForeignKey('staff_account.id')) reacher = db.relationship(StaffAccount) created_at = db.Column(db.DateTime(timezone=True)) round_org_id = db.Column(db.ForeignKey('doc_round_orgs.id')) round_org = db.relationship(DocRoundOrg, backref=db.backref( 'round_reaches', lazy='dynamic', cascade='all, delete-orphan'))
class RoomResource(db.Model): __tablename__ = 'scheduler_room_resources' id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True) location = db.Column('location', db.String(length=16)) number = db.Column('number', db.String(16)) occupancy = db.Column('occupancy', db.Integer(), nullable=False) desc = db.Column('desc', db.Text()) business_hour_start = db.Column('business_hour_start', db.Time()) business_hour_end = db.Column('business_hour_end', db.Time()) availability_id = db.Column('availability_id', db.ForeignKey('scheduler_room_avails.id')) type_id = db.Column('type_id', db.ForeignKey('scheduler_room_types.id')) reservations = db.relationship('RoomEvent', backref='room') equipments = db.relationship(AssetItem, backref=db.backref('room')) def __repr__(self): return u'Room: {}, ID: {}'.format(self.number, self.id)
class SmartClassOnlineAccountEvent(db.Model): __tablename__ = 'smartclass_scheduler_online_account_events' id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True) account_id = db.Column( 'account_id', db.ForeignKey('smartclass_scheduler_online_accounts.id'), nullable=False) title = db.Column('title', db.String(255), nullable=False, info={'label': u'กิจกรรม'}) start = db.Column('start', db.DateTime(timezone=True), nullable=False, info={'label': u'เริ่ม'}) end = db.Column('end', db.DateTime(timezone=True), nullable=False, info={'label': u'สิ้นสุด'}) occupancy = db.Column('occupancy', db.Integer(), info={'label': u'ผู้เข้าร่วม'}) approved = db.Column('approved', db.Boolean(), default=True) created_at = db.Column('created_at', db.DateTime(timezone=True), server_default=func.now()) created_by = db.Column('created_by', db.ForeignKey('staff_account.id')) creator = db.Column('creator', db.String(), nullable=True, info={'label': u'ชื่อผู้ลงทะเบียน'}) updated_at = db.Column('updated_at', db.DateTime(timezone=True), server_default=None) updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id')) cancelled_at = db.Column('cancelled_at', db.DateTime(timezone=True), server_default=None) cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id')) approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id')) approved_at = db.Column('approved_at', db.DateTime(timezone=True), server_default=None) note = db.Column('note', db.Text(), info={'label': u'หมายเหตุ'}) account = db.relationship(SmartClassOnlineAccount, backref=db.backref('events'))
class Challenge(db.Model): """ Challenge Model for storing recipe challenges """ __tablename__ = "challenge" id = db.Column(db.Integer, primary_key=True, autoincrement=True) recipe_id = db.Column(db.Integer, db.ForeignKey('recipe.id')) recipe = db.relationship("Recipe", backref=db.backref("challenge", uselist=False)) start_date = db.Column(db.DateTime) end_date = db.Column(db.DateTime) @hybrid_property def num_contestants(self): return self.recipe.remix_count @hybrid_property def top_three(self): return self.recipe.remixes[:3]
class RoomEvent(db.Model): __tablename__ = 'scheduler_room_reservations' id = db.Column('id', db.Integer(), primary_key=True, autoincrement=True) room_id = db.Column('room_id', db.ForeignKey('scheduler_room_resources.id'), nullable=False) category_id = db.Column('category_id', db.ForeignKey('scheduler_event_categories.id')) category = db.relationship('EventCategory', backref=db.backref('events')) title = db.Column('title', db.String(255), nullable=False) start = db.Column('start', db.DateTime(timezone=True), nullable=False) end = db.Column('end', db.DateTime(timezone=True), nullable=False) iocode_id = db.Column('iocode_id', db.ForeignKey('iocodes.id')) occupancy = db.Column('occupancy', db.Integer()) # number of sets of food/refreshment requested refreshment = db.Column('refreshment', db.Integer(), default=0) request = db.Column('request', db.Text()) # comma separated list of things approved = db.Column('approved', db.Boolean(), default=True) created_at = db.Column('created_at', db.DateTime(timezone=True), server_default=func.now()) created_by = db.Column('created_by', db.ForeignKey('staff_account.id')) updated_at = db.Column('updated_at', db.DateTime(timezone=True), server_default=None) updated_by = db.Column('updated_by', db.ForeignKey('staff_account.id')) cancelled_at = db.Column('cancelled_at', db.DateTime(timezone=True), server_default=None) cancelled_by = db.Column('cancelled_by', db.ForeignKey('staff_account.id')) approved_by = db.Column('approved_by', db.ForeignKey('staff_account.id')) approved_at = db.Column('approved_at', db.DateTime(timezone=True), server_default=None) extra_items = db.Column('extra_items', db.JSON) note = db.Column('note', db.Text()) google_event_id = db.Column('google_event_id', db.String(64)) google_calendar_id = db.Column('google_calendar_id', db.String(255))
class Post(db.Model): """ Description of Post model. Rows ----------- :post_id: int [pk] :authorId: varchar [ref: > users.id] :title: varchar :body: text :post_time: timestamp [not null] :avg_rating: float :num_rating: int """ # Columns post_id = db.Column(db.Integer, primary_key=True) # _author_id = db.Column(db.String(256), db.ForeignKey( # 'user.username'), nullable=False) author_name = db.Column(db.String(256), nullable=False) title = db.Column(db.String(128), nullable=False) body = db.Column(db.Text, nullable=False) post_time = db.Column(db.DateTime, default=datetime.datetime.now()) avg_rating = db.Column(db.Float, default=0.0) num_rating = db.Column(db.Integer, default=0) # Relationships # author = db.relationship('User', backref='posts', lazy=False) tags = db.relationship('Tag', secondary=postTagJunction, lazy='subquery', backref=db.backref('posts', lazy=True)) images = db.relationship('ImgLink', secondary=imgPostJunction, lazy='subquery') def __init__(self, author, title, body): self.author_name = author self.title = title self.body = body db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() @staticmethod def getArticlesByTags(tagList, connector='OR'): """ Get all articles that have a tag in tagList. If connector is AND intersection of all posts set for each tag will be returned. If it is OR, union will be returned """ stmt = select([Post, postTagJunction.c.post_id.label("pid"), postTagJunction.c.tag_id]).distinct().where(and_(postTagJunction.c.post_id == Post.post_id, postTagJunction.c.tag_id.in_(tagList))) result = db.session.execute(stmt) results = result.fetchall() # return results # taking only unique values unique_results = dict() # print(results.c) for result in results: # print(dict(result)) if unique_results.get(result.post_id, 0) == 0: unique_results[result.post_id] = result # print(unique_results[1].post_id) return unique_results.values() @staticmethod def getArticles(post_id): return Post.query.filter_by(post_id=post_id).first() @staticmethod def getRandomizedArticles(size): return sample(Post.query.all(), size) def addTags(self, list_of_tags): self.tags.extend(list_of_tags) db.session.commit() def associateImage(self, imgId): img = ImgLink.query.filter_by(id=imgId).first() self.images.append(img) db.session.commit() def tagDump(self): dump = [] for tag in self.tags: dump.append(tag.name) return dump def linkDump(self): dump = [] for img in self.images: dump.append(img.link) return dump
class User(UserMixin, db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(64), index=True, unique=True) email = db.Column(db.String(120), index=True, unique=True) password_hash = db.Column(db.String(128)) posts = db.relationship("Post", backref="author", lazy="dynamic") comments = db.relationship("Comment", backref="author", lazy="dynamic") about_me = db.Column(db.String(140)) last_seen = db.Column(db.DateTime, default=datetime.utcnow) followed = db.relationship( "User", secondary=followers, primaryjoin=(followers.c.follower_id == id), secondaryjoin=(followers.c.followed_id == id), backref=db.backref("followers", lazy="dynamic"), lazy="dynamic", ) posts_liked = db.relationship( "Post", secondary=likes, primaryjoin=(likes.c.user_id == id), secondaryjoin=(likes.c.post_id == id), backref=db.backref("likes", lazy="dynamic"), lazy="dynamic", ) messages_sent = db.relationship('Message', foreign_keys='Message.sender_id', backref='author', lazy='dynamic') messages_received = db.relationship('Message', foreign_keys='Message.recipient_id', backref='recipient', lazy='dynamic') last_message_read_time = db.Column(db.DateTime) notifications = db.relationship('Notification', backref='user', lazy='dynamic') def __repr__(self): return f"<User: {self.username}>" def set_password(self, password): self.password_hash = generate_password_hash(password) def check_password(self, password): return check_password_hash(self.password_hash, password) def avatar(self, size): digest = md5(self.email.lower().encode("utf-8")).hexdigest() return f"https://www.gravatar.com/avatar/{digest}?d=identicon&s={size}" def follow(self, user): if not self.is_following(user): self.followed.append(user) def unfollow(self, user): if self.is_following(user): self.followed.remove(user) def is_following(self, user): return self.followed.filter(followers.c.followed_id == user.id).count() > 0 def followed_posts(self): followed = Post.query.join( followers, (followers.c.followed_id == Post.user_id) ).filter(followers.c.follower_id == self.id) return followed.union(self.posts).order_by(Post.timestamp.desc()) def like_post(self, post): if not self.like_post.liked(post): self.posts_liked.append(post) def unlike_post(self, post): if self.liked(post): self.like_post.remove(post) def liked(self, post): return self.posts_liked.filter(likes.c.post_id==post.id).count() > 0 def get_reset_password_token(self, expires_in=600): return jwt.encode( {"reset_password": self.id, "exp": time() + expires_in}, current_app.config["SECRET_KEY"], algorithm="HS256", ) @staticmethod def verify_reset_password_token(token): try: id = jwt.decode( token, current_app.config["SECRET_KEY"], algorithms=["HS256"] )["reset_password"] except: return return User.query.get(id) def new_messages(self): last_read_time = self.last_message_read_time or datetime(1900, 1, 1) return Message.query.filter_by(recipient=self).filter( Message.timestamp > last_read_time ).count() def add_notification(self, name, data): self.notifications.filter_by(name=name).delete() n = Notification(name=name, payload_json=json.dumps(data), user=self) db.session.add(n) return n def notify_followers(self, name, data): for follower in self.followers: follower.notifications.filter_by(name=name).delete() follower.add_notification(name, data)
class UserEntity(db.Model, UserMixin, CRUDMixin): """ Stores the basic information about the user. Implements the functions as required by: https://flask-login.readthedocs.org/en/latest/ """ __tablename__ = 'User' id = db.Column("usrID", db.Integer, primary_key=True) email = db.Column("usrEmail", db.String(255), nullable=False, unique=True) first = db.Column("usrFirst", db.String(255), nullable=False) last = db.Column("usrLast", db.String(255), nullable=False) minitial = db.Column("usrMI", db.String(1), nullable=False) added_at = db.Column("usrAddedAt", db.DateTime, nullable=False, server_default='0000-00-00 00:00:00') modified_at = db.Column("usrModifiedAt", db.TIMESTAMP, nullable=False) email_confirmed_at = db.Column("usrEmailConfirmedAt", db.DateTime, nullable=False, server_default='0000-00-00 00:00:00') active = db.Column("usrIsActive", db.Boolean(), nullable=False, server_default='1') access_expires_at = db.Column("usrAccessExpiresAt", db.DateTime, nullable=False, server_default='0000-00-00 00:00:00') password_hash = db.Column("usrPasswordHash", db.String(255), nullable=False, server_default='') # @OneToMany roles = db.relationship(RoleEntity, secondary=UserRoleEntity.__tablename__, backref=db.backref('users'), lazy='dynamic') """ `lazy` defines when SQLAlchemy will load the data from the: 'select' (which is the default) means that SQLAlchemy will load the data as necessary in one go using a standard select statement. 'joined' tells SQLAlchemy to load the relationship in the same query as the parent using a JOIN statement. 'subquery' works like 'joined' but instead SQLAlchemy will use a subquery. 'dynamic' is special and useful if you have may items. Instead of loading the items SQLAlchemy will return another query object which you can further refine before loading them items. This is usually what you want if you expect more than a handful of items for this relationship. """ def is_active(self): """ An user can be blocked by setting a flag in the """ return self.active def is_expired(self): """ An user can be blocked by setting expiration date to yesterday""" return self.access_expires_at < datetime.today() def is_anonymous(self): """ Flag instances of valid users """ return False def is_authenticated(self): """ Returns True if the user is authenticated, i.e. they have provided valid credentials. (Only authenticated users will fulfill the criteria of login_required.) """ return True def get_id(self): """ The id encrypted in the session """ return unicode(self.id) def get_roles(self): """ Return text representation of user roles """ return [role.name for role in self.roles] def get_name(self): """ :rtype string :return concat(first, ' ', last) """ return "{} {}".format(self.first.encode('utf-8'), self.last.encode('utf-8')) def get_email_verification_token(self, salt, secret): """ :rtype string :return the email verification token stored in the """ return utils.get_email_token(self.email, salt, secret) def serialize(self): """Return object data for jsonification""" return { 'id': self.id, 'email': self.email, 'roles': [r.name for r in self.roles], 'first': self.first, 'last': self.last, 'minitial': self.minitial, 'is_active': True if self.active else False, 'is_expired': True if self.is_expired() else False, 'added_at': utils.localize_est_date(self.added_at), 'email_confirmed_at': utils.localize_est_datetime(self.email_confirmed_at), 'access_expires_at': utils.localize_est_datetime(self.access_expires_at) } def __repr__(self): return "<UserEntity (usrID: {0.id}, usrEmail: {0.email}, " \ "usrIsActive: {0.active})>".format(self)
class User(db.Model, UserMixin): """ Description of User model. Columns ----------- :id: int [pk] :username: varchar(128) [not NULL] :password: varchar(128) [not NULL] :first_name: varchar(255) [not NULL] :last_name: varchar(255) :dob: date :email: varchar(255) [not NULL] :fb_handle: varchar(255) :g_handle: varchar(255) :medium_handle: varchar(255) :twitter_handle: varchar(255) :linkedin_handle: varchar(255) :bio: text :occupation: varchar(255) :profile_picture: int :last_login: timestamp :creation_time: timestamp :is_verified: boolean """ # Columns id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(128), unique=True, nullable=False) password = db.Column(db.String(128), nullable=False) first_name = db.Column(db.String(255), default="") last_name = db.Column(db.String(255), default="") dob = db.Column(db.DateTime) email = db.Column(db.String(255), nullable=False) fb_handle = db.Column(db.String(255)) g_handle = db.Column(db.String(255)) medium_handle = db.Column(db.String(255)) twitter_handle = db.Column(db.String(255)) linkedin_handle = db.Column(db.String(255)) profile_picture = db.Column(db.Integer) bio = db.Column(db.Text) occupation = db.Column(db.String(255)) last_login = db.Column(db.DateTime) creation_time = db.Column(db.DateTime) is_verified = db.Column(db.Boolean, default=False) # Relationships tags = db.relationship('Tag', secondary=userTagJunction, lazy='subquery', backref=db.backref("users", lazy=True)) saves = db.relationship('Post', secondary=userPostInteraction, lazy=True, backref=db.backref("savers", lazy=True)) payments = db.relationship('Payment', backref='user', lazy=True) def __init__(self, username, password, email): self.username = username self.password = generate_password_hash(password) self.email = email self.is_verified = False self.profile_picture = 1 db.session.add(self) db.session.commit() @staticmethod @login_manager.user_loader def load_user(id): return User.query.filter_by(id=id).first() def update_col(self, key, value): self.__dict__[key] = value db.session.commit() def check_password(self, password): return check_password_hash(self.password, password) def resetPassword(self, newPassword): # Pass in a hashed password self.password = generate_password_hash(newPassword) db.session.commit() def addPayment(self, data): pay = Payment(self, data.get("amount"), data.get("api_response")) self.payments.append(pay) db.session.commit() def get_id(self): return self.id # We do not need to implement update metadata. # Actually, it can be updated ad hoc by assignment without calling commit. def isVerified(self): return self.is_verified def setVerified(self): self.is_verified = True db.session.commit() def setNewTag(self, tag): self.tags.append(tag) db.session.commit() def setTagPriority(self, tag, priority): result = self.getTagPriority(tag) if result is None: self.setNewTag(tag) s = userTagJunction.update().\ values(priority=PriorityType(priority)).\ where(and_( userTagJunction.c.user_id == self.id, userTagJunction.c.keyword_id == tag.id)) db.session.execute(s) db.session.commit() def getTagPriority(self, tag): s = select([userTagJunction]).where(and_( userTagJunction.c.user_id == self.id, userTagJunction.c.keyword_id == tag.id)) result = list(db.session.execute(s)) try: return result[0]["priority"] except IndexError: return None def savePost(self, post): if post not in self.saves: self.saves.append(post) else: self.saves.remove(post) db.session.commit() def ratePost(self, post, rating): try: s = userPostInteraction.update().\ where(user_id=self.id, post_id=post.post_id).\ values(rating=rating) db.session.execute(s) except BaseException: # User has not yet saved the post so there is no entry here s = userPostInteraction.insert().\ values(save=False, rating=rating, user_id=self.id, post_id=post.post_id) db.session.execute(s) finally: db.session.commit()
class UserModel(db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) email = db.Column(db.String(255), unique=True, nullable=False) username = db.Column(db.String(50), unique=True) password_hash = db.Column(db.String(100)) role_id = db.Column(db.Integer, db.ForeignKey('role.id'), nullable=False) card_lists = db.relationship('CardListModel', secondary=user_card_list, backref=db.backref('users', lazy='dynamic')) cards = db.relationship('CardModel', backref='user', lazy='dynamic') comments = db.relationship('CommentModel', backref='user', lazy='dynamic') @property def password(self): raise AttributeError('password: write-only field') @password.setter def password(self, password): self.password_hash = flask_bcrypt.generate_password_hash( password).decode('utf-8') def check_password(self, password): return flask_bcrypt.check_password_hash(self.password_hash, password) def json(self): return {'id': self.id, 'email': self.email, 'username': self.username} @staticmethod def encode_auth_token(user_id): """ Generates the Auth Token :return: string """ try: payload = { 'exp': datetime.datetime.utcnow() + datetime.timedelta(days=1, seconds=5), 'iat': datetime.datetime.utcnow(), 'sub': user_id } return jwt.encode(payload, key, algorithm='HS256') except Exception as e: return e @staticmethod def decode_auth_token(auth_token): """ Decodes the auth token :param auth_token: :return: integer|string """ try: payload = jwt.decode(auth_token, key, algorithms='HS256') is_blacklisted_token = BlacklistToken.check_blacklist(auth_token) if is_blacklisted_token: return 'Token blacklisted. Please log in again.' else: return payload['sub'] except jwt.ExpiredSignatureError: return 'Signature expired. Please log in again.' except jwt.InvalidTokenError: return 'Invalid token. Please log in again.' def __repr__(self): return f"<User name:'{self.username}', id:'{self.id}', role: {self.role}>"
class Post(Base, SearchableMixin): """ Description of User model. Columns ----------- :id: int [pk] :title: Text [not NULL] :author_id: int [Foreign Key] :creation_time: DateTime [not NULL] :last_edit_time: DateTime [not NULL] :post_body: Text # Relationships :comments: Relationship -> Comments (one to many) """ # Columns id = db.Column(db.Integer, db.ForeignKey("base.id"), primary_key=True) post_id = db.Column(db.Integer, autoincrement=True, primary_key=True, unique=True) title = db.Column(db.Text, nullable=False) post_movie = db.Column(db.String(20)) tags = db.Column(db.JSON) __searchable__ = ['title', 'body', 'tags'] __mapper_args__ = { 'polymorphic_identity': 'post', 'inherit_condition': (id == Base.id) } comments = db.relationship( 'Comment', primaryjoin="(Post.post_id == Comment.parent_post_id)", backref=db.backref('post'), lazy='dynamic') def __init__(self, author_id, post_movie, title, post_body, tags): super().__init__(author_id, post_body, "post") self.title = title self.post_movie = post_movie self.tags = tags db.session.add(self) db.session.commit() def add_comment(self, author_id, comment_body): parent_post_id = self.id comment = Comment(author_id, parent_post_id, comment_body) self.comments.append(comment) db.session.commit() return comment.id def update_col(self, key, value): setattr(self, key, value) db.session.commit() def delete_post(self, post_id): post = Post.query.filter_by(id=post_id).delete() db.session.commit()
class User(db.Model, UserMixin): __tablename__ = 'sys_user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) username = db.Column(db.String(80), unique=True, nullable=False, comment=u'用户名') password_hash = db.Column(db.String(128), comment="加密密码") email = db.Column(db.String(120), unique=True, comment=u'email') phone = db.Column(db.String(20), unique=True, comment=u'手机号码') avatar = db.Column(db.String(200), comment=u'头像') status = db.Column(db.Integer, default=1, comment=u'状态') lastLoginTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'最后登录时间') createTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'创建时间') createBy = db.Column(db.String(50), comment=u'创建人') updateTime = db.Column(db.DATETIME, server_default=func.now(), comment=u'修改时间') updateBy = db.Column(db.String(50), comment=u'修改人') roles = db.relationship( 'Role', secondary=sys_user_role, backref=db.backref('users', lazy='dynamic') ) @property def role_names(self): strs = '' for i in range(len(self.roles)): strs = strs + self.roles[i].name + ',' return strs # @property # def permissions(self): # permissions = Permission.query.join(sys_role_permission).join(Role).join(sys_user_role).join(User). \ # filter( # User.id == self.id # ) # return permissions @property def menus(self): menus = Menu.query.join(sys_role_menu).join(Role).join(sys_user_role).join(User). \ filter( User.id == self.id ).order_by(Menu.type, Menu.order).all() return menus def check(self, action): permission = self.permissions.filter(Permission.action == action).first() return bool(permission) def __init__(self, username, password): self.username = username self.password = password def __init__(self, username, phone): self.username = username self.password = phone @property def password(self): return self.password_hash # raise AttributeError('不能直接获取明文密码!') @property def create_time(self): return self.createTime.strftime('%Y-%m-%d') # def auth(method): # @functools.wraps(method) # def wrapper(*args, **kwargs): # user_id = session.get('user_id') # if not user_id: # return abort(403) # return method(*args, **kwargs) # # return wrapper @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_password(self, password): return check_password_hash(self.password_hash, password) def get_id(self): return self.username def update(self): globals.db.commit() def delete(self): globals.db.delete(self) globals.db.commit() def save(self): globals.db.add(self) globals.db.commit()
class Recipe(db.Model): """ Recipe Model for storing recipes """ __tablename__ = "recipe" id = db.Column(db.Integer, primary_key=True, autoincrement=True) title = db.Column(db.String(255), nullable=False) created_on = db.Column(db.DateTime, nullable=False) cooktime = db.Column(db.String(255), nullable=False) preptime = db.Column(db.String(255), nullable=False) totaltime = db.Column(db.String(255), nullable=False) username = db.Column(db.String(50), db.ForeignKey('user.username'), nullable=False) remixes = db.relationship("Recipe", backref=db.backref('parent', remote_side=[id])) parent_id = db.Column(db.Integer, db.ForeignKey('recipe.id')) public = db.Column(db.Boolean) difficulty = db.Column(db.Integer, nullable=True) servings = db.Column(db.String(255), nullable=False) source = db.Column(db.String(255), nullable=False) calories = db.Column(db.Integer, nullable=True) cost = db.Column(db.Float, nullable=True) description = db.Column(db.Text) f_image = db.Column(db.Integer, nullable=True) images = db.relationship('Image', order_by='Image.id', backref='recipe', cascade='all,delete,delete-orphan') ingredients = db.relationship('Ingredient', order_by='Ingredient.number', collection_class=ordering_list('number', count_from=1), backref='recipe', lazy=True, cascade='all,delete,delete-orphan') steps = db.relationship('Step', order_by='Step.number', collection_class=ordering_list('number', count_from=1), backref='recipe', lazy=True, cascade='all,delete,delete-orphan') @hybrid_property def remix_count(self): return len(self.remixes) @remix_count.expression def _remix_count_expression(cls): q = db.select([db.func.count(Recipe.parent_id)]).\ where(Recipe.parent_id == cls.id).\ label("remix_count") return q @hybrid_property def featured_image(self): if self.f_image is not None: return self.images[self.f_image] return None @hybrid_property def community_images(self): return (list( filter(lambda img: img.username != self.username, self.images)) + [ r.featured_image for r in self.remixes if r.featured_image is not None ]) @hybrid_property def owner_images(self): return list( filter(lambda img: img.username == self.username, self.images)) @hybrid_property def likes_count(self): return len(self.likers) #TODO make has_liked work when nested, as in viewing other user's recipes @hybrid_method def has_liked(self, username): return db.session.query(likes).filter( likes.c.username == username).filter( likes.c.recipe_id == self.id).first() != None @likes_count.expression def _likes_count_expression(cls): return (db.select([ db.func.count(likes.c.username).label("likes_count") ]).where(likes.c.recipe_id == cls.id).label("sum_likes")) def __repr__(self): return "<Recipe '{}'>".format(self.title)
class Post(db.Model): """ Description of Post model. Rows ----------- :post_id: int [pk] :authorId: varchar [ref: > users.id] :title: varchar :body: text :post_time: timestamp [not null] :avg_rating: float :num_rating: int """ # Columns post_id = db.Column(db.Integer, primary_key=True) _author_id = db.Column(db.String(256), db.ForeignKey('user.username'), nullable=False) title = db.Column(db.String(128), nullable=False) body = db.Column(db.Text, nullable=False) post_time = db.Column(db.DateTime, default=datetime.datetime.now()) avg_rating = db.Column(db.Float, default=0.0) num_rating = db.Column(db.Integer, default=0) # Relationships author = db.relationship('User', backref='posts', lazy=False) tags = db.relationship('Tag', secondary=postTagJunction, lazy='subquery', backref=db.backref('posts', lazy=True)) # savers = db.relationship('User', secondary=postSaves, lazy=True, # backref=db.backref('posts', lazy='subquery')) images = db.relationship('ImgLink', secondary=imgPostJunction, lazy='subquery') def __init__(self, author, title, body): self.author = author self.title = title self.body = body db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() # Getters and Setters for the fields # @hybrid_property # def author_id(self): # return self.author # @author_id.setter # def author_id(self, authorId): # try: # user = User.query.filter_by(id=authorId).first() # if user.last_logout != None: # raise LoginError # else: # self._author_id = authorId # except: # # Stub to be handled later # print("Get back to login page") @classmethod def getArticlesByTags(cls, tagList, connector='AND'): """ Get all articles that have a tag in tagList. If connector is AND intersection of all posts set for each tag will be returned. If it is OR, union will be returned """ if connector == 'AND': posts = set() for tag in tagList: if len(posts) == 0: posts = set(cls.query.filter(tag in cls.tags).all()) else: posts.intersection( set(cls.query.filter(tag in cls.tags).all())) return list(posts) elif connector == 'OR': posts = set() for tag in tagList: if len(posts) == 0: posts = set(cls.query.filter(tag in cls.tags).all()) else: posts.union(set(cls.query.filter(tag in cls.tags).all())) return list(posts) @staticmethod def getArticles(post_id): return Post.query.filter_by(post_id=post_id).first() @staticmethod def getRandomizedArticles(size): return sample(Post.query.all(), size)