class FcmTokens(db.Model):
id = db.Column(db.Integer, primary_key=True)
token = db.Column(db.String(200), nullable=False, unique=True)
# person_id = db.Column(db.String(20), nullable=False)
person_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
db.UniqueConstraint('token', 'person_id', 'fcm_token_person_id'),
def to_dict(self):
return {
'id': self.id,
'token': self.token,
'person_id': self.person_id,
}
def __init__(self, token=None, person_id=None):
self.token = token
# person id is the current user id
self.person_id = person_id
def serialize(self):
return {
'id': self.id,
'token': self.token,
'person_id': self.person_id,
}
class CITypeRelation(db.Model):
__tablename__ = "ci_type_relations"
ctr_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
parent_id = db.Column(db.Integer,
db.ForeignKey("ci_types.type_id"),
primary_key=True)
parent = db.relationship(
"CIType", primaryjoin="CIType.type_id==CITypeRelation.parent_id")
child_id = db.Column(db.Integer,
db.ForeignKey("ci_types.type_id"),
primary_key=True)
child = db.relationship(
"CIType", primaryjoin="CIType.type_id==CITypeRelation.child_id")
relation_type = db.Column(db.String(7),
db.Enum("contain",
"connect",
"deploy",
"install",
name="relation_type"),
default="contain")
__table_args__ = (db.UniqueConstraint("parent_id",
"child_id",
name="parent_child_uniq"), )
class CIRelation(db.Model):
__tablename__ = "ci_relations"
cr_id = db.Column(db.Integer, primary_key=True, autoincrement=True)
first_ci_id = db.Column(db.Integer,
db.ForeignKey("cis.ci_id"),
primary_key=True)
second_ci_id = db.Column(db.Integer,
db.ForeignKey("cis.ci_id"),
primary_key=True)
first_ci = db.relationship("CI",
primaryjoin="CI.ci_id==CIRelation.first_ci_id")
second_ci = db.relationship(
"CI", primaryjoin="CI.ci_id==CIRelation.second_ci_id")
relation_type = db.Column(db.String(8),
db.Enum("connect",
"deploy",
"install",
"contain",
name="relation_type"),
nullable=False)
more = db.Column(db.Integer, db.ForeignKey("cis.ci_id"))
__table_args__ = (db.UniqueConstraint("first_ci_id",
"second_ci_id",
name="first_second_uniq"), )
class Business(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(50), nullable=False)
store_address = db.Column(db.String(50), nullable=False)
latitude = db.Column(db.Float, nullable=False)
longitude = db.Column(db.Float, nullable=False)
# Direct access to corresponding manager(user) using Business.manager
manager_address = db.Column(db.String(50),
db.ForeignKey('user.email'),
nullable=False)
# Direct access to corresponding city(city) using Business.city
city_id = db.Column(db.Integer, db.ForeignKey('city.id'), nullable=False)
offers = db.relationship('Offer',
backref='business',
lazy=True,
cascade='all, delete-orphan')
__table_args__ = (db.UniqueConstraint(
'name',
'store_address',
'city_id',
name='_business_name_address_city_uc'), )
def __init__(self,
name,
store_address,
city_id,
manager_address,
latitude,
longitude,
offers=[]):
self.name = name
self.store_address = store_address
self.city_id = city_id
self.manager_address = manager_address
self.latitude = latitude
self.longitude = longitude
self.offers = offers
def __repr__(self):
return "<name='%s', store_address='%s', city_id=%d, manager_address='%s', latitude=%f, longitude=%f, offers=%r>" \
% (self.name, self.store_address, self.city_id, self.manager_address, self.latitude, self.longitude, [o.id for o in self.offers])
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id': self.id,
'name': self.name,
'store_address': self.store_address,
'city': self.city.serialize,
'latitude': self.latitude,
'longitude': self.longitude,
'offers': [o.serialize for o in self.offers],
}
class DocGroup(db.Model):
__tablename__ = "doc_group"
d_id = db.Column(db.SmallInteger,
primary_key=True,
nullable=False,
autoincrement=True)
name = db.Column(db.String(64), nullable=False)
alias = db.Column(db.String(128), nullable=False)
category = db.Column(db.String(64), nullable=False, default="menu")
enabled = db.Column(db.Boolean, default=True)
order = db.Column(db.SMALLINT)
__table_args__ = (db.UniqueConstraint("name"), )
class CITypeAttribute(db.Model):
__tablename__ = "type_attributes"
type_id = db.Column(db.Integer,
db.ForeignKey("ci_types.type_id"),
primary_key=True)
attr_id = db.Column(db.Integer,
db.ForeignKey("ci_attributes.attr_id"),
primary_key=True)
is_required = db.Column(db.Boolean, default=False)
__table_args__ = (db.UniqueConstraint("type_id",
"attr_id",
name="type_attr_uniq"), )
class DocRelation(db.Model):
__tablename__ = 'doc_relation'
d_id = db.Column(db.SmallInteger,
primary_key=True,
nullable=False,
autoincrement=True)
group_id = db.Column(db.SmallInteger,
db.ForeignKey("doc_group.d_id"),
primary_key=True)
name = db.Column(db.String(64), nullable=False)
description = db.Column(db.Text, nullable=False)
__table_args__ = (db.UniqueConstraint("group_id",
"name",
name="group_id_name"), )
class Interest(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.String(50), nullable=False)
__table_args__ = (db.UniqueConstraint('name', name='_interest_name_uc'), )
def __init__(self, name):
self.name = name
def __repr__(self):
return "<name='%s'>" % self.name
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id': self.id,
'name': self.name,
}
class Rarity(db.Model):
__tablename__ = 'rarities'
id = db.Column(db.Integer, primary_key=True)
rarity = db.Column(db.String, nullable=False)
code = db.Column(db.String, nullable=False)
card_id = db.Column(db.Integer, db.ForeignKey('cards.id'), nullable=False)
countries = db.relationship('Country', secondary=countriesrarities, backref=db.backref('raritiess'))
__table_args__ = (db.UniqueConstraint('rarity', 'code'),)
def __init__(self, rarity, code, card_id):
self.rarity = rarity
self.code = code
self.card_id = card_id
def __repr__(self):
return self.code
class Banlist(db.Model):
__tablename__ = 'banlists'
id = db.Column(db.Integer, primary_key=True)
start_date = db.Column(db.Date, nullable=False)
end_date = db.Column(db.Date)
game_type = db.Column(db.Enum('Advanced', 'Traditional', name='game_type'))
region = db.Column(db.Enum('TCG', 'OCG', name='region'), nullable=False)
bans = db.relationship('Ban', backref='banlist')
__table_args__ = (db.UniqueConstraint('start_date', 'game_type', 'region'),)
def __init__ (self, start_date, end_date, game_type, region):
self.start_date = start_date
self.end_date = end_date
self.game_type = game_type
self.region = region
def __repr__(self):
return "<Banlist {0} / {1} - {2} - {3}>".format(self.start_date, self.end_date, self.game_type, self.region)
class InteractedUsers(db.Model):
__tablename__ = 'interacted_users'
id = db.Column(db.Integer,
primary_key=True)
interacted_id = db.Column(db.Integer)
at_location = db.Column(Geography(geometry_type='POINT',
srid=4326),
nullable=True)
at_time = db.Column(db.TIMESTAMP(120), nullable=True,
default=dt.datetime.utcnow())
# define relationships with other tables
person_id = db.Column(db.Integer,
db.ForeignKey('user.id'),
nullable=False)
db.UniqueConstraint('interacted_id', 'person_id',
'person_interaction'),
def __init__(self, point=None,
person_id=None,
interacted_id=None):
self.at_location = point
# default
self.at_time = dt.datetime.utcnow()
# person id is the current user id
self.person_id = person_id
# we will get this from post api call
self.interacted_id = interacted_id
def serialize(self):
return {
'id': self.id,
'interacted_id': self.interacted_id,
'at_location_lat': str(to_shape(self.at_location).y),
'at_location_lon': str(to_shape(self.at_location).x),
'at_time': self.at_time,
'person_id': self.person_id,
}
class SysDict(BasicModel):
"""
系统字典
"""
__tablename__ = 'SYS_DICT'
__table_args__ = (db.UniqueConstraint('VALUE',
'TYPE',
name='uix_SYS_DICT_VALUE_TYPE'), )
value = db.Column(db.String(length=64),
name='VALUE',
index=True,
comment='数据值')
label = db.Column(db.String(length=64),
name='LABEL',
nullable=False,
comment='标签名')
type = db.Column(db.String(length=64),
name='TYPE',
index=True,
nullable=False,
comment='类型')
description = db.Column(db.String(length=225),
name='DESCRIPTION',
comment='描述')
sort = db.Column(db.Integer, name='SORT', default=10, comment='排序')
def dao_find_page(self, page, error_out=False):
"""
分页条件查询
:param page:
:param error_out:
:return:
"""
# 条件查询
filter = []
if is_not_empty(self.type):
filter.append(SysDict.type == self.type)
if is_not_empty(self.description):
filter.append(
SysDict.description.like(
'%{description}%'.format(description=self.description)))
pagination = self.query.filter(*filter).\
order_by(SysDict.sort.asc(), SysDict.type.asc(), SysDict.create_date.asc()).\
paginate(page=page.page, per_page=page.page_size, error_out=error_out)
page.init_pagination(pagination)
# 数据序列化 json
data_dict, errors = SysDictSchema().dump(page.data, many=True)
Assert.is_true(is_empty(errors), errors)
page.data = data_dict
return page, pagination
@cache.memoize()
def dao_get(self, id):
"""
:param id:
:return:
"""
return super().dao_get(id)
@cache.memoize()
def dao_get_type_value(self, type, value):
"""
根据类型和value查询字典
:param type:
:param value:
:return:
"""
return self.query.filter(SysDict.type == type,
SysDict.value == value).first()
@cache.memoize()
def dao_get_type_values(self, type):
"""
根据类型查询全部value
:param type:
:return:
"""
return self.query.filter(SysDict.type == type).order_by(
SysDict.sort.asc()).all()
@cache.memoize()
def dao_get_all(self):
"""
获取全部字典
:return:
"""
dicts = self.query.order_by(SysDict.sort.asc()).all()
return dicts
@cache.memoize()
def dao_get_types(self):
"""
查询字典类型列表
:return:
"""
types = []
for dict_type in self.query.with_entities(
SysDict.type).distinct().all():
types.append(dict_type[0])
return types
@cache.delete_cache([
dao_get, dao_get_all, dao_get_types, dao_get_type_value,
dao_get_type_values
])
def dao_add(self, **kwargs):
"""
添加字典
:param kwargs:
:return:
"""
super().dao_create()
with db.auto_commit_db(**kwargs) as s:
s.add(self)
@cache.delete_cache([
dao_get, dao_get_all, dao_get_types, dao_get_type_value,
dao_get_type_values
])
def dao_delete(self, **kwargs):
"""
删除字典
:param kwargs:
:return:
"""
super().dao_delete(**kwargs)
class Offer(db.Model):
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
start_time = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
end_time = db.Column(db.TIMESTAMP(timezone=True), nullable=False)
description = db.Column(db.String(100), nullable=False)
# Direct access to corresponding offer(offer) using Business
business_id = db.Column(db.Integer,
db.ForeignKey('business.id'),
nullable=False)
interests = db.relationship('Interest',
secondary=offer_interest,
lazy='subquery',
backref=db.backref('offers', lazy=True))
users_accepted = db.relationship('User',
secondary=user_accepted_offer,
lazy='subquery',
backref=db.backref('offers_accepted',
lazy=True))
users_viewed = db.relationship('User',
secondary=user_viewed_offer,
lazy='subquery',
backref=db.backref('offers_viewed',
lazy=True))
__table_args__ = (db.UniqueConstraint(
'description', 'business_id', name='_offer_description_business_uc'), )
def __init__(self,
business_id,
start_time,
end_time,
description,
interests=[],
users_accepted=[],
users_viewed=[]):
self.business_id = business_id
self.start_time = start_time
self.end_time = end_time
self.description = description
self.interests = interests
self.users_accepted = users_accepted
self.users_viewed = users_viewed
def __repr__(self):
return "<business_id=%d, start_time=%r, end_time=%r, description='%s', interests=%r, users_accepted=%d, users_viewed=%d>" % \
(self.business_id, self.start_time, self.end_time, self.description, [i.name for i in self.interests], len(self.users_accepted), len(self.users_viewed))
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id': self.id,
'business': {
'id': self.business.id,
'name': self.business.name
},
'start_time': self.start_time.isoformat(),
'end_time': self.end_time.isoformat(),
'description': self.description,
'interests': [i.serialize for i in self.interests],
'accepts': len(self.users_accepted),
'views': len(self.users_viewed),
}
class AppUser(ModelBase):
__tablename__ = 'app_user'
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
provider = db.Column(db.String(32), nullable=False) # String ID of the provider
internal_id = db.Column(db.String(), nullable=False) # ID that is specific to the provider
language = db.Column(db.String(5), nullable=False)
# Flag indicating whether a user has been informed about the new site or not
notified_new_site = db.Column(db.Boolean(), nullable=False, default=False, server_default=expression.false())
enabled = db.Column(db.Boolean(), nullable=False, default=True, server_default=expression.true())
data = db.Column(db.Text(), nullable=True) # Stores data specific to the provider
__table_args__ = (
db.UniqueConstraint('provider', 'internal_id'),
)
subscriptions = db.relationship('UserDayCampusPreference', backref='user', passive_deletes=True)
feature_participations = db.relationship('FeatureParticipation', backref='user', passive_deletes=True)
def __init__(self, provider: str, internal_id: str, language: str):
if not isinstance(provider, str):
raise expected('provider', provider, str)
if not isinstance(internal_id, str):
raise expected('internal_id', internal_id, str)
if not isinstance(language, str):
raise expected('language', language, str)
self.provider = provider
self.internal_id = internal_id
self.language = language
def set_campus(self, day: Day, campus: Campus, active=None):
sub = UserDayCampusPreference.get_for_user(self, day)
if sub is None:
UserDayCampusPreference.create(self, day, campus, active=True if active is None else active)
else:
sub.campus = campus
if active is not None:
sub.active = active
def set_day_active(self, day: Day, active: bool):
sub = UserDayCampusPreference.get_for_user(self, day)
if sub is None:
if active:
raise ValueError('Cannot set subscription active if there is no campus set')
else:
sub.active = active
def get_campus(self, day: Day) -> 'Optional[Campus]':
sub = UserDayCampusPreference.get_for_user(self, day)
if sub is not None:
return sub.campus
else:
return None
def get_subscription(self, day: Day) -> 'Optional[UserDayCampusPreference]':
return UserDayCampusPreference.get_for_user(self, day)
def set_language(self, language: str):
self.language = language
def set_active(self, day: Day, active: bool):
sub = UserDayCampusPreference.get_for_user(self, day)
if sub is None:
raise ValueError('User does not have a subscription on day {}'.format(day.name))
sub.active = active
@staticmethod
def create(provider: str, internal_id: str, language: str) -> 'AppUser':
user = AppUser(provider, internal_id, language)
db.session.add(user)
return user
def delete(self):
db.session.delete(self)
@staticmethod
def find_subscribed_users_by_day(day: Day, provider=None) -> 'List[AppUser]':
q = AppUser.query
if provider:
q = q.filter_by(provider=provider)
return q.join(AppUser.subscriptions).filter(db.and_(UserDayCampusPreference.day == day,
UserDayCampusPreference.active == expression.true(),
AppUser.enabled == expression.true()
)).order_by(AppUser.provider, AppUser.internal_id).all()
@staticmethod
def find_by_id(provider: str, internal_id: str) -> 'Optional[AppUser]':
return AppUser.query.filter_by(provider=provider, internal_id=internal_id).first()
@staticmethod
def find_by_provider(provider: str) -> 'List[AppUser]':
return AppUser.query.filter_by(provider=provider).order_by(AppUser.internal_id).all()
def __hash__(self):
return hash(self.id)
class RegisteredUser(ModelBase, UserMixin):
__tablename__ = 'registered_users'
id = db.Column(db.Integer(), primary_key=True, autoincrement=True)
provider = db.Column(db.String(16), nullable=False)
subject = db.Column(db.String(), nullable=False)
name = db.Column(db.String(), nullable=False)
email = db.Column(db.String(), nullable=False, unique=True)
profile_picture = db.Column(db.String(), nullable=False)
registered_on = db.Column(db.DateTime(), nullable=False, server_default=functions.now())
activated_on = db.Column(db.DateTime(), nullable=True)
web_subscriptions = db.Column(db.String(), nullable=False, server_default='[]')
roles: 'List[Role]' = db.relationship('Role', secondary=user_roles_table, back_populates='users')
submissions = db.relationship('LearningDatapointSubmission', backref='registered_user', passive_deletes=True)
__table_args__ = (
db.UniqueConstraint('provider', 'subject'),
)
def __init__(self, provider: str, subject: str, name: str, email: str, profile_picture: str):
if not isinstance(provider, str):
raise expected('provider', provider, str)
if not isinstance(subject, str):
raise expected('subject', subject, str)
if not isinstance(name, str):
raise expected('name', name, str)
if not isinstance(email, str):
raise expected('email', email, str)
if not isinstance(profile_picture, str):
raise expected('profile_picture', profile_picture, str)
self.provider = provider
self.subject = subject
self.name = name
self.email = email
self.profile_picture = profile_picture
@staticmethod
def create(provider: str, subject: str, name: str, email: str, profile_picture: str,
add_to_db=True) -> 'RegisteredUser':
user = RegisteredUser(provider, subject, name, email, profile_picture)
if add_to_db:
db.session.add(user)
return user
def delete(self):
db.session.delete(self)
# Overrides UserMixin.is_active
@property
def is_active(self):
return self.activated_on is not None
# Query methods
@staticmethod
def get_by_id(user_id: int) -> 'Optional[RegisteredUser]':
return RegisteredUser.query.filter_by(id=user_id).first()
@staticmethod
def find_by_provider_id(provider: str, subject: str) -> 'Optional[RegisteredUser]':
return RegisteredUser.query.filter_by(provider=provider, subject=subject).first()
@staticmethod
def find_by_email(email: str) -> 'Optional[RegisteredUser]':
return RegisteredUser.query.filter_by(email=email).first()
@staticmethod
def get_all() -> 'List[RegisteredUser]':
return RegisteredUser.query.all()
@staticmethod
def get_all_active() -> 'List[RegisteredUser]':
return RegisteredUser.query.filter(RegisteredUser.activated_on != None).all()
@staticmethod
def get_all_by_role(role: 'Role') -> 'List[RegisteredUser]':
return role.users
# return RegisteredUser.query.filter(
# UserRoles.user_id == RegisteredUser.id,
# UserRoles.role_id == role.id
# ).all()
# Roles functions
def get_roles(self) -> 'List[Role]':
return self.roles
def add_role(self, role: 'Role'):
self.roles.append(role)
def remove_role(self, role: 'Role'):
self.roles.remove(role)
def is_role(self, role: 'Union[str, Role]') -> bool:
if isinstance(role, str):
role = Role.find_by_name(role)
return role is not None and role in self.roles
elif isinstance(role, Role):
return role in self.roles
else:
raise ValueError('role')
# Subscriptions functions
def get_subscriptions(self) -> 'List[AdminSubscription]':
return json.loads(self.web_subscriptions)
def set_subscriptions(self, subscriptions: 'List[AdminSubscription]'):
self.web_subscriptions = json.dumps(subscriptions)
def add_subscription(self, endpoint: str, keys: Dict[str, str]):
subscriptions: 'List[AdminSubscription]' = []
found = False
for sub in self.get_subscriptions():
subscriptions.append(sub)
if sub['endpoint'] == endpoint:
found = True
if not found:
subscriptions.append({'endpoint': endpoint, 'keys': keys})
self.set_subscriptions(subscriptions)
def remove_subscription(self, endpoint: str):
self.set_subscriptions([sub for sub in self.get_subscriptions() if sub['endpoint'] != endpoint])
@staticmethod
def replace_subscription(old_endpoint: str, endpoint: str, keys: Dict[str, str]):
for user in RegisteredUser.get_all():
user.set_subscriptions([sub if sub['endpoint'] != old_endpoint else {'endpoint': endpoint, 'keys': keys}
for sub in user.get_subscriptions()])
def __hash__(self):
return hash(self.id)