class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(500))
filename = db.Column(db.String(64))
filename_s = db.Column(db.String(64))
filename_m = db.Column(db.String(64))
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
author = db.relationship('User', back_populates='photos')
can_comment = db.Column(db.Boolean, default=True)
flag = db.Column(db.Integer, default=0)
comments = db.relationship('Comment',
back_populates='photo',
cascade='all')
tags = db.relationship('Tag', secondary=tagging, back_populates='photos')
collectors = db.relationship('Collect',
back_populates='collected',
cascade='all')
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
users = db.relationship('User', back_populates='role')
permissions = db.relationship('Permission', secondary=role_permissions, back_populates='roles')
@staticmethod
def init_role():
roles_permissions_map = {
'Locked': ['FOLLOW', 'COLLECT'],
'User': ['FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD'],
'Moderator': ['FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD', 'MODERATE'],
'Administrator': ['FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD', 'MODERATE', 'ADMINISTER']
}
for role_name, permissions in roles_permissions_map.items():
role = Role.query.filter_by(name=role_name).first()
if not role:
role = Role(name=role_name)
db.session.add(role)
role.permissions = []
for pname in permissions:
permission = Permission.query.filter_by(name=pname).first()
if not permission:
permission = Permission(name=pname)
db.session.add(permission)
role.permissions.append(permission)
db.session.commit()
class Comment(db.Model):
"""
评论
"""
id = db.Column(db.Integer, primary_key=True)
# 内容
body = db.Column(db.Text)
# 时间
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
# 举报次数
flag = db.Column(db.Integer, default=0)
# 回复
replied_id = db.Column(db.Integer, db.ForeignKey("comment.id"))
author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
photo_id = db.Column(db.Integer, db.ForeignKey("photo.id"))
photo = db.relationship("Photo", back_populates="comments")
author = db.relationship("User", back_populates="comments")
replies = db.relationship("Comment",
back_populates="replied",
cascade="all")
replied = db.relationship("Comment",
back_populates="replies",
remote_side=[id])
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
# 描述
description = db.Column(db.String(500))
# 文件名
filename = db.Column(db.String(64))
# 小图
filename_s = db.Column(db.String(64))
# 中图
filename_m = db.Column(db.String(64))
# 时间
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
# 是否可评论
can_comment = db.Column(db.Boolean, default=True)
# 被举报次数
flag = db.Column(db.Integer, default=0)
# 用户
author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
author = db.relationship("User", back_populates="photos")
# 评论
comments = db.relationship("Comment",
back_populates="photo",
cascade="all")
# 收藏
collectors = db.relationship("Collect",
back_populates="collected",
cascade="all")
tags = db.relationship("Tag", secondary=tagging, back_populates="photos")
class Collect(db.Model):
collector_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
collected_id = db.Column(db.Integer, db.ForeignKey('photo.id'), primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
collector = db.relationship('User', back_populates='collections', lazy='joined')
collected = db.relationship('Photo', back_populates='collectors', lazy='joined')
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
users = db.relationship("User", back_populates="role")
permissions = db.relationship("Permission", secondary=roles_permissions, back_populates="roles")
@staticmethod
def init_role():
roles_permissions_map = {
"Locked": ["FOLLOW", "COLLECT"],
"User": ["FOLLOW", "COLLECT", "COMMENT", "UPLOAD"],
"Moderator": ["FOLLOW", "COLLECT", "COMMENT", "UPLOAD", "MODERATE"],
"Administrator": ["FOLLOW", "COLLECT", "COMMENT", "UPLOAD", "MODERATE", "ADMINISTER"]
}
for role_name in roles_permissions_map:
role = Role.query.filter_by(name=role_name).first()
if role is None:
role = Role(name=role_name)
db.session.add(role)
role.permissions = []
for permission_name in roles_permissions_map[role_name]:
permission = Permission.query.filter_by(name=permission_name).first()
if permission is None:
permission = Permission(name=permission_name)
db.session.add(permission)
role.permissions.append(permission)
db.session.commit()
class Follow(db.Model):
follower_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
followed_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
follower = db.relationship("User", foreign_keys=[follower_id], back_populates="following", lazy="joined")
followed = db.relationship("User", foreign_keys=[followed_id], back_populates="followers", lazy="joined")
class Collect(db.Model):
collector_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True)
collected_id = db.Column(db.Integer, db.ForeignKey("photo.id"), primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
collector = db.relationship("User", back_populates="collections", lazy="joined")
collected = db.relationship("Photo", back_populates="collectors", lazy="joined")
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True, comment="角色名称")
users = db.relationship('User', back_populates='role')
permissions = db.relationship('Permission',
secondary=roles_permissions,
back_populates='roles')
@staticmethod
def init_role():
roles_permissions_map = {
'Locked': ['FOLLOW', 'COLLECT'], # 被锁定用户
'User': ['FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD'], # 普通用户
'Moderator':
['FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD', 'MODERATE'], # 协管员
'Administrator': [
'FOLLOW', 'COLLECT', 'COMMENT', 'UPLOAD', 'MODERATE',
'ADMINISTER'
] # 管理员
}
for role_name in roles_permissions_map:
role = Role.query.filter_by(name=role_name).first()
if role is None:
role = Role(name=role_name)
db.session.add(role)
role.permissions = []
for permission_name in roles_permissions_map[role_name]:
permission = Permission.query.filter_by(
name=permission_name).first()
if permission is None:
permission = Permission(name=permission_name)
db.session.add(permission)
role.permissions.append(permission)
db.session.commit()
class Follow(db.Model):
"""连接user, user多对多关系"""
follower_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True) # 关注者
followed_id = db.Column(db.Integer, db.ForeignKey("user.id"), primary_key=True) # 被关注者
follower = db.relationship('User', foreign_keys=[follower_id], back_populates='following', lazy='joined')
followed = db.relationship('User', foreign_keys=[followed_id], back_populates='followers', lazy='joined')
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
class Role(db.Model):
"""
角色表，即可拥有的权限
"""
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
# 关联到用户
users = db.relationship("User", back_populates="role")
# 权限
permissions = db.relationship("Permission",
secondary=roles_permissions,
back_populates="roles")
@staticmethod
def init_role():
"""
初始化角色和权限信息
"""
roles_permissions_map = {
# 被禁用户
"Locked": ["FOLLOW", "COLLECT"],
# 普通用户
"User": ["FOLLOW", "COLLECT", "COMMENT", "UPLOAD"],
# 管理资源权限
"Moderator":
["FOLLOW", "COLLECT", "COMMENT", "UPLOAD", "MODERATE"],
# 管理员
"Administrator": [
"FOLLOW", # 关注用户
"COLLECT", # 收藏图片
"COMMENT", # 评论
"UPLOAD", # 上传
"MODERATE", # 修改
"ADMINISTER", # 管理
],
}
# 遍历
for role_name in roles_permissions_map:
# 取出Role对象，如果没有则创建
role = Role.query.filter_by(name=role_name).first()
if role is None:
role = Role(name=role_name)
db.session.add(role)
role.permissions = []
# 遍历每一种角色拥有的权限
for permission_name in roles_permissions_map[role_name]:
# 如果该角色在数据表中不存在，则创建
permission = Permission.query.filter_by(
name=permission_name).first()
if permission is None:
permission = Permission(name=permission_name)
db.session.add(permission)
# 追加到Role对象下
role.permissions.append(permission)
db.session.commit()
class Collect(db.Model):
'''关联表只能用来表示关系，不能用来存储数据，例如 tagging。
使用关联模型建立多对多关系可以用来存储数据。
'''
collector_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
collected_id = db.Column(db.Integer, db.ForeignKey('photo.id'), primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow) # 收藏的时间
collector = db.relationship('User', back_populates='collections', lazy='joined')
collected = db.relationship('Photo', back_populates='collectors', lazy='joined')
class Rate(db.Model):
id = db.Column(db.Integer, primary_key=True)
rate_value = db.Column(db.Integer(), default=0)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
rater_photo_id = db.Column(db.Integer, db.ForeignKey('photo.id'))
awarded_id = db.Column(db.Integer, db.ForeignKey('user.id'))
rater_photo = db.relationship('Photo',
foreign_keys=[rater_photo_id],
back_populates='rates')
awarded = db.relationship('User',
foreign_keys=[awarded_id],
back_populates='awards')
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(500), comment='图片描述')
filename = db.Column(db.String(64), comment='文件名')
filename_s = db.Column(db.String(64), comment="小尺寸图片文件名")
filename_m = db.Column(db.String(64), comment="大尺寸图片文件名")
timestamp = db.Column(db.DateTime, default=datetime.utcnow, comment='上传时间')
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
author = db.relationship('User', back_populates='photos')
tags = db.relationship('Tag', secondary=tagging, back_populates='photos')
collectors = db.relationship('Collect',
back_populates='collected',
cascade='all')
class Comment(db.Model):
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
flag = db.Column(db.Integer, default=0)
replied_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
photo_id = db.Column(db.Integer, db.ForeignKey('photo.id'))
photo = db.relationship('Photo', back_populates='comments')
author = db.relationship('User', back_populates='comments')
replies = db.relationship('Comment', back_populates='replied', cascade='all')
replied = db.relationship('Comment', back_populates='replies', remote_side=[id])
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(500))
filename = db.Column(db.String(64))
filename_s = db.Column(db.String(64))
filename_m = db.Column(db.String(64))
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
can_comment = db.Column(db.Boolean, default=True)
flag = db.Column(db.Integer, default=0)
author_id = db.Column(db.Integer, db.ForeignKey("user.id"))
author = db.relationship("User", back_populates="photos")
comments = db.relationship('Comment', back_populates="photo", cascade='all')
collectors = db.relationship("Collect", back_populates="collected", cascade='all')
tags = db.relationship("Tag", secondary=tagging, back_populates="photos")
class Invite(db.Model): # user_id(doctor) has been invited to the photo_id.
id = db.Column(db.Integer, primary_key=True)
photo_id = db.Column(db.Integer, db.ForeignKey('photo.id'))
photo = db.relationship('Photo',
foreign_keys=[photo_id],
back_populates='invites')
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
user = db.relationship('User',
foreign_keys=[user_id],
back_populates='invites')
status = db.Column(db.Boolean, default=False)
token_id = db.Column(db.String, nullable=False)
created_at = db.Column(db.DateTime, default=datetime.now)
updated_at = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
class Permission(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
roles = db.relationship('Role',
secondary=roles_permissions,
back_populates='permissions')
class Notification(db.Model):
id = db.Column(db.Integer, primary_key=True)
message = db.Column(db.Text)
is_read = db.Column(db.Boolean, default=False)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
receiver_id = db.Column(db.Integer, db.ForeignKey('user.id'))
receiver = db.relationship('User', back_populates='notifications')
class Permission(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
roles = db.relationship('Role',
secondary=roles_permissions,
back_populates='permissions')
def __repr__(self):
return '<Permission %r>' % self.name
class Permission(db.Model):
"""
权限表，给用户设置权限
"""
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
roles = db.relationship("Role",
secondary=roles_permissions,
back_populates="permissions")
class Tag(db.Model):
"""
图片标签
"""
id = db.Column(db.Integer, primary_key=True)
# 标签名称
name = db.Column(db.String(64), index=True, unique=True)
photos = db.relationship("Photo", secondary=tagging, back_populates="tags")
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(60))
body = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
can_comment = db.Column(db.Boolean, default=True)
category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
category = db.relationship('Category', back_populates='posts')
class Collect(db.Model):
collector_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True)
collected_id = db.Column(db.Integer,
db.ForeignKey('photo.id'),
primary_key=True)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
# photo.collectors.collector | photo.collectors获取包含收藏对象的Collect的列表，.collector/collected才会加载对应的用户和图片
# 这样就需要两次select，增加了一次查询，那么通过联结join这样只需要一次查询
# 收藏者
collector = db.relationship('User',
back_populates='collections',
lazy='joined')
# 被收藏图片
collected = db.relationship('Photo',
back_populates='collectors',
lazy='joined')
class Follow(db.Model):
# 两侧都在同一个User模型中，这种关系被称为自引用关系（Self-Referential Many-to-Many Relationship）。
follower_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True,
comment='关注者id')
followed_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
primary_key=True,
comment='被关注者id')
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
follower = db.relationship('User',
foreign_keys=[follower_id],
back_populates='following',
lazy='joined')
followed = db.relationship('User',
foreign_keys=[followed_id],
back_populates='followers',
lazy='joined')
class Photo(db.Model):
id = db.Column(db.Integer, primary_key=True)
description = db.Column(db.String(500))
filename = db.Column(db.String(64))
filename_s = db.Column(db.String(64))
filename_m = db.Column(db.String(64))
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
can_comment = db.Column(db.Boolean, default=True)
flag = db.Column(db.Integer, default=0)
author_id = db.Column(db.Integer, db.ForeignKey('user.id'))
rates = db.relationship(
'Rate',
foreign_keys=[Rate.rater_photo_id],
back_populates='rater_photo',
cascade='all',
lazy='dynamic') # get the rate data that are given by this photo user
author = db.relationship('User', back_populates='photos')
comments = db.relationship('Comment',
back_populates='photo',
cascade='all')
collectors = db.relationship('Collect',
back_populates='collected',
cascade='all')
tags = db.relationship('Tag', secondary=tagging, back_populates='photos')
invites = db.relationship('Invite',
foreign_keys=[Invite.photo_id],
back_populates='photo',
cascade='all',
lazy='dynamic')
class Doctor(db.Model):
cv = db.Column(db.String(150)) # the hospital name that doctor works.
address = db.Column(db.String(200)) # the address of hospital.
speciality = db.Column(db.String(150))
latitude = db.Column(db.String(20), default='35.392426')
longitude = db.Column(db.String(20), default='139.476048')
status = db.Column(
db.String(20), default='BAD'
) # represent status of the latitude and longitude, if it is flase, its values are not exact.
acct_id = db.Column(db.String(250))
balance = db.Column(db.Float(), default=0.0)
withdraws = db.relationship('WithDraw',
back_populates='doctor',
cascade='all')
id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
class Category(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30), unique=True)
posts = db.relationship('Post', back_populates='category')
# Deleting a category does not delete the article under that category.
# The articles under this category will be moved to the default category.
def delete(self):
default_category = Category.query.get(1)
posts = self.posts[:]
for post in posts:
post.category = default_category
db.session.delete(self)
db.session.commit()
class Notification(db.Model):
"""
消息提醒
"""
id = db.Column(db.Integer, primary_key=True)
# 消息内容
message = db.Column(db.Text, nullable=False)
# 是否已阅读
is_read = db.Column(db.Boolean, default=False)
# 时间
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
# 接收者的id
receiver_id = db.Column(db.Integer, db.ForeignKey("user.id"))
receiver = db.relationship("User", back_populates="notifications")
class WithDraw(db.Model):
id = db.Column(db.Integer, primary_key=True)
status = db.Column(db.Boolean, default=False)
amount = db.Column(db.Float(), default=0.0)
bank_code = db.Column(db.String(255))
branch_code = db.Column(db.String(255))
account_number = db.Column(db.String(255))
additional_bank_info = db.Column(db.Text(1000))
created_at = db.Column(db.DateTime, default=datetime.now)
updated_at = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
doctor_id = db.Column(db.Integer, db.ForeignKey('doctor.id'))
doctor = db.relationship('Doctor',
foreign_keys=[doctor_id],
back_populates='withdraws')