class LocationToAddress(db.Model):
__tablename__ = "location_to_address"
address_id = db.Column(db.BigInteger, db.ForeignKey("address_cache.id"), nullable=False)
location_id = db.Column(db.BigInteger, db.ForeignKey("location_cache.id"), nullable=False)
__table_args__ = (
db.PrimaryKeyConstraint('address_id', 'location_id'),
)
@staticmethod
def get_address_for_location(lat, lng) -> List[Address]:
location = Location.get_by_location(lat, lng)
if location is None:
return []
return [x.address for x in LocationToAddress.query.filter_by(location_id=location.id).all()]
@staticmethod
def get_location_for_address(query) -> Optional[Location]:
addresses = Address.get_by_query(query)
if len(addresses) == 0:
return None
return LocationToAddress.query.filter_by(address_id=addresses[0].id).first().location
@staticmethod
def get_location_for_address_id(id) -> Optional[Location]:
loc = LocationToAddress.query.filter_by(address_id=id).first()
if loc is None:
return None
return loc.location
class DiscardEntry(db.Model):
__tablename__ = 'discardentry'
id = db.Column(db.Integer, primary_key=True)
cardId = db.Column(db.Integer, db.ForeignKey('card.id'), nullable=False)
gameId = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
card = db.relationship('Card', lazy=False)
game = db.relationship('Game', back_populates='discard', lazy=False)
class RoundBattle(db.Model):
__tablename__ = 'roundbattle'
id = db.Column(db.Integer, primary_key=True)
roundId = db.Column(db.Integer,
db.ForeignKey('gameround.id'),
nullable=False)
round = db.relationship('GameRound', back_populates="battles")
offendingPlayerId = db.Column(db.Integer,
db.ForeignKey('player.id'),
nullable=True)
offendingPlayer = db.relationship('Player',
foreign_keys=[offendingPlayerId])
defendingPlayerId = db.Column(db.Integer,
db.ForeignKey('player.id'),
nullable=True)
defendingPlayer = db.relationship('Player',
foreign_keys=[defendingPlayerId])
offensiveCardId = db.Column(db.Integer,
db.ForeignKey('card.id'),
nullable=True)
offensiveCard = db.relationship('Card')
defensiveCards = db.Column(db.Text, nullable=True)
isComplete = db.Column(db.Boolean, default=False)
creationOrder = db.Column(db.Integer, nullable=False)
class Player(db.Model):
__tablename__ = 'player'
id = db.Column(db.Integer, unique=True, primary_key=True)
gameId = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
game = db.relationship('Game', back_populates='players')
name = db.Column(db.String(20), nullable=False)
money = db.Column(db.Integer, nullable=False)
moneyAfterRound = db.Column(db.Integer, nullable=True)
hand = db.Column(db.Text, nullable=True)
neighbourId = db.Column(db.Integer,
db.ForeignKey('player.id'),
nullable=True)
neighbourRight = db.relationship('Player',
backref='neighbourLeft',
remote_side='Player.id',
uselist=False,
post_update=True)
isAdmin = db.Column(db.Boolean, nullable=False)
isOnline = db.Column(db.Boolean, nullable=False)
hasLeft = db.Column(db.Boolean, nullable=False, default=False)
__table_args__ = (db.UniqueConstraint('name',
'gameId',
name='_name_gameid_uc'), )
def __repr__(self):
return '<Player %r>' % self.name
class DeckEntry(db.Model):
#__table_args__ = {'extend_existing': True}
__tablename__ = 'deckentry'
id = db.Column(db.Integer, primary_key=True)
# TODO: Undo nullable
cardId = db.Column(db.Integer, db.ForeignKey('card.id'), nullable=True)
gameId = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
card = db.relationship('Card', lazy=False)
game = db.relationship('Game', back_populates='deck', lazy=False)
order = db.Column(db.Integer, nullable=False)
class ChatMember(db.Model):
__tablename__ = 'chat_members'
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
chat_id = db.Column(db.BigInteger,
db.ForeignKey('chats.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, default=timestamp())
is_group = db.Column(db.Boolean, default=False)
deleted = db.Column(db.TIMESTAMP, default=None)
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'chat_id'), )
def __eq__(self, other):
return self.user_id == other.user_id and self.chat_id == other.chat_id
def __repr__(self):
return f"ChatMember: {self.user}, {self.chat}"
@staticmethod
def get_private_chat(first_id, second_id):
first_id = int(first_id)
second_id = int(second_id)
first_chats = ChatMember.query.filter_by(user_id=first_id,
is_group=False).all()
second_chats = ChatMember.query.filter_by(user_id=second_id,
is_group=False).all()
chat = []
for i in first_chats:
for j in second_chats:
if i.chat_id == j.chat_id:
chat.append(i)
if len(chat) > 1:
return chat
elif len(chat) == 1:
return chat[0].chat
else:
return None
@staticmethod
def get_user_chats(user_id):
user_id = int(user_id)
return [
i.chat for i in list(
filter(
lambda x: x.deleted is None or
(x.chat.last_message is not None and x.deleted < x.chat.
last_message.time),
ChatMember.query.filter_by(user_id=user_id).all()))
]
class EventMember(db.Model):
__tablename__ = "event_members"
event_id = db.Column(db.BigInteger,
db.ForeignKey('events.id'),
nullable=False)
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, default=timestamp())
__table_args__ = (db.PrimaryKeyConstraint('event_id', 'user_id'), )
def __repr__(self):
return f"Event {self.event_id}: {self.user}"
class Card(db.Model):
__tablename__ = 'card'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(40), unique=True, nullable=False)
text = db.Column(db.Text, nullable=True)
### TODO imageURL should not be nullable at future
imageURL = db.Column(db.Text, nullable=True)
popUpText = db.Column(db.Text, nullable=True)
popUpURL = db.Column(db.Text, nullable=True)
typeId = db.Column(db.Integer,
db.ForeignKey('cardtype.id'),
nullable=False)
type = db.relationship('CardType',
back_populates='cards',
uselist=False,
lazy=False)
isCovid = db.Column(db.Boolean, default=False)
damage = db.Column(db.Integer, nullable=True)
countInDeck = db.Column(db.Integer, nullable=False)
offensiveAgainst = db.relationship('Defence',
foreign_keys='Defence.offenceCardId',
back_populates='offence',
lazy=False)
defensiveFrom = db.relationship('Defence',
foreign_keys='Defence.defenceCardId',
back_populates='defence',
lazy=False)
def __repr__(self):
return '<Card %r>' % self.name
class GameRound(db.Model):
__tablename__ = 'gameround'
# TODO: Add relationships
id = db.Column(db.Integer, primary_key=True)
game_id = db.Column(db.Integer, db.ForeignKey('game.id'), nullable=False)
game = db.relationship('Game', back_populates='rounds')
roundNo = db.Column(db.Integer, nullable=False)
isComplete = db.Column(db.Boolean, default=False)
isAccidentComplete = db.Column(db.Boolean, default=False)
currentPlayerId = db.Column(db.Integer,
db.ForeignKey('player.id'),
nullable=True)
currentPlayer = db.relationship('Player')
battles = db.relationship('RoundBattle',
back_populates="round",
cascade='all,delete')
class GroupMember(db.Model):
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
group_id = db.Column(db.BigInteger,
db.ForeignKey('groups.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, nullable=False, default=timestamp())
__tablename__ = "group_members"
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'group_id'), )
def __repr__(self):
return f"GroupMember: {self.user} -> {self.group}"
class ChatNotification(db.Model):
__tablename__ = 'notifications'
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
chat_id = db.Column(db.BigInteger,
db.ForeignKey('chats.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, default=timestamp())
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'chat_id'), )
def __repr__(self):
return f"ChatNotification: {self.user}, {self.chat}, time = {self.time}"
@staticmethod
def get_or_404(user_id: int, chat_id: int):
notification = ChatNotification.query.filter_by(
user_id=user_id, chat_id=chat_id).first()
if notification is None:
abort(404)
return notification
@staticmethod
def get_or_none(user_id: int, chat_id: int):
return ChatNotification.query.filter_by(user_id=user_id,
chat_id=chat_id).first()
@staticmethod
def get_notifications(user_id: int):
return ChatNotification.query.filter_by(user_id=user_id).all()
@staticmethod
def remove(user_id: int, chat_id: int):
ChatNotification.query.filter_by(user_id=user_id,
chat_id=chat_id).delete()
db.session.commit()
@staticmethod
def add(user_id: int, chat_id: int):
if ChatNotification.get_or_none(user_id, chat_id) is None:
tmp = ChatNotification(user_id=user_id,
chat_id=chat_id,
time=timestamp())
db.session.add(tmp)
db.session.commit()
class Friend(db.Model):
__tablename__ = "friends"
first_id = db.Column(db.BigInteger,
db.ForeignKey("users.id"),
nullable=False)
second_id = db.Column(db.BigInteger,
db.ForeignKey("users.id"),
nullable=False)
time = db.Column(db.TIMESTAMP, nullable=False, default=timestamp())
__table_args__ = (db.PrimaryKeyConstraint('first_id', 'second_id'), )
@staticmethod
def add(first_id: int, second_id: int):
if second_id < first_id:
first_id, second_id = second_id, first_id
if Friend.query.filter_by(first_id=first_id,
second_id=second_id).first() is not None:
return
record = Friend(first_id=first_id,
second_id=second_id,
time=timestamp())
db.session.add(record)
db.session.commit()
@staticmethod
def remove(first_id: int, second_id: int):
if second_id < first_id:
first_id, second_id = second_id, first_id
if Friend.query.filter_by(first_id=first_id,
second_id=second_id).first() is None:
return
record = Friend.query.filter_by(first_id=first_id,
second_id=second_id).first()
db.session.delete(record)
db.session.commit()
def __repr__(self):
return f"Friend: {self.first} <-> {self.second}"
class Message(db.Model):
__tablename__ = 'messages'
id = db.Column(db.BigInteger, nullable=False, primary_key=True, autoincrement=True)
chat_id = db.Column(db.BigInteger, db.ForeignKey('chats.id'), nullable=False)
user_id = db.Column(db.BigInteger, db.ForeignKey('users.id'), nullable=False)
time = db.Column(db.TIMESTAMP, default=timestamp())
is_read = db.Column(db.Boolean, default=False)
content = db.Column(db.JSON, default=json.dumps({'photos': [], 'audios': [], 'videos': [], 'map_pins': []}))
text = db.Column(db.VARCHAR(1000), default="")
chat_last_msg_rel = db.relationship(
'Chat',
backref='last_message',
lazy=True,
primaryjoin="Message.id==Chat.last_msg_id"
)
@staticmethod
def get_or_404(id: int):
message = Message.query.get(id)
if message is None:
abort(404)
return message
@staticmethod
def get_or_none(id: int):
return Message.query.get(id)
@staticmethod
def has_id(id: int) -> bool:
return Message.query.get(id) is None
@staticmethod
def get_history(chat_id: int, limit: int = 50):
return Message.query.filter_by(chat_id=chat_id).order_by(Message.time).limit(limit).all()
def __repr__(self):
return f"Message: from {self.user.username}: {self.text} || {'+' if self.is_read else '-'}"
class Defence(db.Model):
__tablename__ = 'defence'
id = db.Column(db.Integer, primary_key=True)
defenceCardId = db.Column(db.Integer,
db.ForeignKey('card.id'),
nullable=False)
offenceCardId = db.Column(db.Integer,
db.ForeignKey('card.id'),
nullable=False)
value = db.Column(db.Integer, nullable=False)
defence = db.relationship("Card",
back_populates='defensiveFrom',
foreign_keys=[defenceCardId],
uselist=False)
offence = db.relationship("Card",
back_populates='offensiveAgainst',
foreign_keys=[offenceCardId],
uselist=False)
def __repr__(self):
return '<Defence card value is %r>' % self.value
class PlayTime(db.Model):
__tablename__ = "play_times"
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
day_of_week = db.Column(db.Integer)
start_time = db.Column(db.Time)
end_time = db.Column(db.Time)
address_id = db.Column(db.BigInteger,
db.ForeignKey("address_cache.id"),
nullable=True)
location_id = db.Column(db.BigInteger,
db.ForeignKey("location_cache.id"),
nullable=True)
user_id = db.Column(db.BigInteger,
db.ForeignKey("users.id"),
nullable=False)
@staticmethod
def get(id: int):
return PlayTime.query.filter_by(id=id).first()
@staticmethod
def get_all_for_user_id(id: int):
return PlayTime.query.filter_by(user_id=id).all()
class UserVideos(db.Model):
__tablename__ = "user_sport_videos"
user_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
sport = db.Column(db.String, nullable=False)
video_id = db.Column(db.String, nullable=False)
__table_args__ = (db.PrimaryKeyConstraint('user_id', 'sport'), )
@staticmethod
def get_all_for_user_id(user_id: int) -> dict:
return {
i.sport: i.video_id
for i in UserVideos.query.filter_by(user_id=user_id).all()
}
@staticmethod
def get_video(user_id: int, sport: str) -> str:
return UserVideos.query.filter_by(user_id=user_id, sport=sport).first()
def __repr__(self) -> str:
return f"UserVideos: user = {self.user}, sport = {self.sport}, video_id = {self.video_id}"
class Event(db.Model):
from libs.models.EventPlayTimes import EventPlayTimes
__tablename__ = "events"
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(100), nullable=False)
description = db.Column(db.VARCHAR(300), nullable=True)
sport = db.Column(db.VARCHAR(50), nullable=False)
group_id = db.Column(db.BigInteger,
db.ForeignKey('groups.id'),
nullable=True)
creation_time = db.Column(db.TIMESTAMP, default=timestamp())
creator_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, nullable=True)
closed = db.Column(db.Boolean, default=False)
recurring = db.Column(db.Boolean, default=False)
event_members_rel = db.relationship('EventMember',
backref='event',
lazy=True)
event_play_time_rel = db.relationship('EventPlayTimes',
backref='event',
lazy=True)
def __repr__(self):
return f"Event {self.id}: {self.name}, {self.sport}"
@staticmethod
def get_or_404(id: int):
event = Event.query.get(id)
if event is None:
abort(404)
return event
@staticmethod
def get_or_none(id: int):
return Event.query.get(id)
def add_member(self, user):
from libs.models.EventMember import EventMember
res = EventMember.query.filter_by(event_id=self.id,
user_id=user.id).first()
if res is not None:
return
new_member = EventMember(event_id=self.id,
user_id=user.id,
time=timestamp())
db.session.add(new_member)
db.session.commit()
def get_members(self):
from libs.models.EventMember import EventMember
return [
i.user
for i in EventMember.query.filter_by(event_id=self.id).all()
]
def remove_member(self, user):
from libs.models.EventMember import EventMember
res = EventMember.query.filter_by(event_id=self.id,
user_id=user.id).first()
if res is not None:
db.session.delete(res)
db.session.commit()
def delete(self):
from libs.models.EventMember import EventMember
if self is None:
return
for i in EventMember.query.filter_by(event_id=self.id).all():
db.session.delete(i)
db.session.commit()
db.session.delete(self)
db.session.commit()
class Group(db.Model):
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
admin_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
sport = db.Column(db.String(50), nullable=False)
name = db.Column(db.String(50), nullable=False)
closed = db.Column(db.Boolean, default=False)
members_rel = db.relationship('GroupMember', backref='group', lazy=True)
chats_rel = db.relationship('Chat', backref='group', lazy=True)
event_rel = db.relationship('Event', backref='group', lazy=True)
__tablename__ = "groups"
def __repr__(self):
return f"Group {self.id}: {self.name}, {self.sport}, admin: {self.admin.username};"
@staticmethod
def get_by_sport(sport):
return Group.query.filter_by(sport=sport).all()
@staticmethod
def get_or_404(id: int):
group = Group.query.get(id)
if group is None:
abort(404)
return group
@staticmethod
def get_or_none(id: int):
return Group.query.get(id)
def get_members(self):
from libs.models.GroupMember import GroupMember
members = GroupMember.query.filter_by(group_id=self.id).all()
return [i.user for i in members]
def add_member(self, user):
from libs.models.GroupMember import GroupMember
if user.id not in self.get_members():
new_row = GroupMember(user_id=user.id,
group_id=self.id,
time=timestamp())
db.session.add(new_row)
db.session.commit()
def get_events(self):
from libs.models.Event import Event
return Event.query.filter_by(group_id=self.id).all()
def delete(self):
from libs.models.GroupMember import GroupMember
if self is None:
return
for e in self.get_events():
db.session.delete(e)
db.session.commit()
for i in GroupMember.query.filter_by(group_id=self.id).all():
db.session.delete(i)
db.session.commit()
db.session.delete(self)
db.session.commit()
class Chat(db.Model):
from libs.models.ChatMember import ChatMember
from libs.models.ChatNotification import ChatNotification
from libs.models.Message import Message
__tablename__ = 'chats'
id = db.Column(db.BigInteger, primary_key=True, autoincrement=True)
name = db.Column(db.VARCHAR(100), nullable=True)
admin_id = db.Column(db.BigInteger,
db.ForeignKey('users.id'),
nullable=False)
time = db.Column(db.TIMESTAMP, default=timestamp())
last_msg_id = db.Column(db.BigInteger,
db.ForeignKey('messages.id'),
nullable=True)
group_id = db.Column(db.BigInteger,
db.ForeignKey('groups.id'),
nullable=True) # is_group
deleted = db.Column(db.TIMESTAMP, nullable=True, default=None)
chat_member_rel = db.relationship('ChatMember', backref='chat', lazy=True)
message_rel = db.relationship('Message',
backref='chat',
lazy=True,
primaryjoin="Chat.id==Message.chat_id")
notification_rel = db.relationship('ChatNotification',
backref='chat',
lazy=True)
def __repr__(self):
return f"{'GroupChat' if self.group_id else 'Chat'}: {self.name}, {self.admin}" + \
" deleted" if self.deleted else ""
@staticmethod
def get_or_404(id: int):
chat = Chat.query.get(id)
if chat is None:
abort(404)
return chat
@staticmethod
def get_or_none(id: int):
return Chat.query.get(id)
def get_history(self):
from libs.models.Message import Message
return Message.get_history(self.id)
def get_members(self):
from libs.models.ChatMember import ChatMember
return [
i.user for i in ChatMember.query.filter_by(chat_id=self.id,
deleted=None).all()
]
def add_member(self, id: int, is_group=True):
from libs.models.ChatMember import ChatMember
if id not in [i.id for i in self.get_members()]:
new = ChatMember(user_id=id,
chat_id=self.id,
is_group=is_group,
time=timestamp())
db.session.add(new)
db.session.commit()
def update_last_msg(self, message):
self.last_msg_id = message.id
db.session.commit()
def get_new_messages(self, user_id=None):
from libs.models.Message import Message
if user_id is None:
return Message.query.filter_by(chat_id=self.id,
is_read=False).all()
else:
return list(
filter(
lambda x: x.user_id != user_id,
Message.query.filter_by(chat_id=self.id,
is_read=False).all()))