class Game(db.Model): id = db.Column(db.Integer, primary_key=True) #accessname = db.Column(db.String(255), unique=True, nullable=True) status = db.Column(db.Integer, default=0) #1 if active, 0 if not active game_type_id = db.Column( db.Integer, db.ForeignKey("game_type.id", ondelete="SET NULL")) host_id = db.Column( db.Integer, db.ForeignKey( "player.id", ondelete="SET NULL")) #One of the players has to be a host tournament_id = db.Column( db.Integer, db.ForeignKey( "tournament.id", ondelete="SET NULL")) #Being part of a tournament is optional game_token = db.Column(db.String(20), unique=True, nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=func.now()) finished_at = db.Column(db.DateTime) #Relationships to other models game_type = db.relationship("GameType", back_populates="game") host = db.relationship("Player", back_populates="game") scores = db.relationship("PlayerScore", cascade="all, delete-orphan", back_populates="game") tournament = db.relationship("Tournament", back_populates="game")
class Comment(db.Model): __tablename__ = 'comments' id = db.Column(db.Integer, primary_key=True) contents = db.Column(db.Text, nullable=False) post_id = db.Column(db.Integer, db.ForeignKey('posts.id'), nullable=False) author_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) created_at = db.Column(db.DateTime, nullable=False, default=dt.datetime.now) modified_at = db.Column(db.DateTime, nullable=False, default=dt.datetime.now) def save(self): self.modified_at = dt.datetime.now() db.session.add(self) db.session.commit() def update(self, **kwargs): for attr, value in kwargs.items(): setattr(self, attr, value) self.save() def delete(self): db.session.delete(self) db.session.commit()
class Answer(BaseDateTimeModel): __tablename__ = 'answers' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True) # noqa: A003 text = db.Column(db.Text(), nullable=False) is_approve = db.Column(db.Boolean, default=False) question_id = db.Column(db.Integer, db.ForeignKey('questions.id')) likes_count = db.Column(db.Integer, default=0, nullable=False) user_relation = db.relationship( 'AnswerUsersRelations', back_populates='answers', ) owner_id = db.Column(db.Integer, db.ForeignKey('users.id')) users = db.relationship('User', back_populates='answers') def update_likes_count(self, like_status: int, on_created=False): if on_created and like_status: self.likes_count += 1 elif on_created and not like_status: self.likes_count -= 1 elif not on_created and like_status: self.likes_count += 2 else: self.likes_count -= 2
class PlayerScore(db.Model): id = db.Column(db.Integer, primary_key=True) player_id = db.Column(db.Integer, db.ForeignKey("player.id", ondelete="CASCADE"), nullable=False) score = db.Column(db.Float(24)) game_id = db.Column(db.Integer, db.ForeignKey("game.id", ondelete="CASCADE"), nullable=False) #Relationships to other models player = db.relationship("Player", back_populates="score") game = db.relationship("Game", back_populates="scores") __table__args__ = (db.UniqueConstraint('player_id', 'game_id', '_player_id_game_id_uc'), )
class Post(BaseModel): __tablename__ = "posts" id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True) title = db.Column(db.String(100), nullable=False) content = db.Column(db.Text, nullable=False) views = db.Column(db.Integer, nullable=False) status = db.Column(db.Integer, nullable=False) created_time = db.Column(UTCDateTime(timezone=True), default=time_utcnow, nullable=False) updated_time = db.Column(UTCDateTime(timezone=True), default=time_utcnow, nullable=False) def __init__(self, user_id, title, content, *args, **kwargs): self.user_id = user_id self.title = title self.content = content self.views = 0 self.status = 1 self.created_time = datetime.now() self.updated_time = datetime.now() if kwargs and kwargs['id']: self.id = kwargs['id'] @classmethod def get_post(cls, post_id): post = cls.query.get(post_id) return post
class Email(db.Model): """ Email Model """ id = db.Column(db.Integer, unique=True, primary_key=True, autoincrement=True) email = db.Column(db.String(250), nullable=True, unique=True) contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'))
class Answer(BaseDateTimeModel): __tablename__ = 'answers' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True) # noqa: A003 text = db.Column(db.Text(), nullable=False) is_approve = db.Column(db.Boolean, default=False) question_id = db.Column(db.Integer, db.ForeignKey('questions.id'))
class AnswerUsersRelations(BaseDateTimeModel): __tablename__ = 'answer_users_relations' id = db.Column(db.Integer, primary_key=True) # noqa: A003 answer_id = db.Column( db.Integer, db.ForeignKey('answers.id'), ) answers = db.relationship( 'Answer', back_populates='user_relation', ) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship( 'User', back_populates='answer_relation', )
class TestAnswer(db.Model): __tablename__ = 'test_answers' id = db.Column(db.Integer, primary_key=True) # noqa: A003 text = db.Column(db.Text, nullable=False) right = db.Column(db.Boolean, nullable=False, default=False) question_id = db.Column(db.Integer, db.ForeignKey('test_questions.id')) question = db.relationship('TestQuestion', back_populates='answers')
class Chapter(db.Model): __tablename__ = 'chapters' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer(), primary_key=True) # noqa: A003 name = db.Column(db.Text(), nullable=False) order_number = db.Column(db.Integer, nullable=False) questions = db.relationship('Question', back_populates='chapter') section_id = db.Column(db.Integer, db.ForeignKey('sections.id')) section = db.relationship('Section', back_populates='chapters')
class Question(BaseDateTimeModel): __tablename__ = 'questions' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer(), primary_key=True) # noqa: A003 text = db.Column(db.Text(), nullable=False) order_number = db.Column(db.Integer, nullable=False) user = db.Column(db.Integer, db.ForeignKey('users.id')) answers = db.relationship( 'Answer', backref='questions', lazy='dynamic', ) chapter_id = db.Column(db.Integer, db.ForeignKey('chapters.id')) chapter = db.relationship( 'Chapter', back_populates='questions') test_case = db.relationship( 'TestCase', back_populates='question', uselist=False) def __str__(self): return f'Вопрос #{self.order_number} {self.text}'
class Leaderboard(db.Model): id = db.Column(db.Integer, primary_key=True) player_id = db.Column(db.Integer, db.ForeignKey("player.id", ondelete="CASCADE"), nullable=False, index=True) game_type_id = db.Column(db.Integer, db.ForeignKey("game_type.id", ondelete="CASCADE"), nullable=False, index=True) wins = db.Column(db.Integer, nullable=False) losses = db.Column(db.Integer, nullable=False) #Relationships to other models game_type = db.relationship("GameType", back_populates="lboard") player = db.relationship("Player", back_populates="lboard") #every player_id and game_type_id combination is unique __table__args__ = (db.UniqueConstraint('player_id', 'game_type_id', '_player_id_game_type_id_uc'), )
class RequestModel(db.Model): __tablename__ = "requests" id = db.Column(db.Text(length=36), default=lambda: str(uuid.uuid4()), primary_key=True) theme = db.Column(db.Text, db.ForeignKey("request_themes.id")) title = db.Column(db.String(80), nullable=False) body = db.Column(db.String(190), nullable=False) creator = db.Column(db.Text(length=36), db.ForeignKey("users.id"), nullable=False) # executor = db.Column(db.Text(length=36), db.ForeignKey("users.id"), nullable=True) status = db.Column(db.Integer, nullable=False) @classmethod def find_by_id(cls, _id: int) -> "RequestModel": return cls.query.filter_by(id=_id).first() @classmethod def find_by_theme(cls, theme: str) -> "RequestModel": return cls.query.filter_by(theme=theme).first() def turn_to_json(self) -> dict: return { "id": self.id, "theme": self.theme, "title": self.title, "body": self.body, "status": self.status, "creator": self.creator } def save_to_db(self): db.session.add(self) db.session.commit() def delete_from(self): db.session.delete(self) db.session.commit()
class Answer(BaseDateTimeModel): __tablename__ = 'answers' __table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True) # noqa: A003 text = db.Column(db.Text(), nullable=False) is_approve = db.Column(db.Boolean, default=False) question_id = db.Column(db.Integer, db.ForeignKey('questions.id')) likes_count = db.Column(db.Integer, default=0, nullable=False) user_relation = db.relationship( 'AnswerUsersRelations', back_populates='answers', )
class TestCase(db.Model): """Question test cases set model.""" __tablename__ = 'test_cases' id = db.Column(db.Integer, primary_key=True) # noqa: A003 question_id = db.Column(db.Integer, db.ForeignKey('questions.id')) question = db.relationship('Question', back_populates='test_case') test_questions = db.relationship('TestQuestion', back_populates='test_case') def __str__(self): return f'Тесты для {self.question}'
class Chat(db.Model): """ Telegram chat. One user can have multiple chats. """ __tablename__ = "chats" # Columns id = db.Column(db.Integer, primary_key=True) telegram_id = db.Column( db.BigInteger, unique=True, index=True, nullable=False, comment="Unique ID to identificate chat in Telegram") type = db.Column(db.Enum(ChatType), nullable=False, comment="Type of Telegram chat") user_id = db.Column( db.Integer, db.ForeignKey("users.id"), nullable=False, comment="Through this chat message can be sent to this user") # Relationships user = db.relationship("User", back_populates="chats", uselist=False) def __repr__(self): return f"<Chat {self.id}>" @staticmethod def create_fake(user): """ Creates fake chat. :param user: User to associate created chat with. """ from faker import Faker fake = Faker() random_number = fake.pyint(min_value=1, max_value=10, step=1) result = Chat(user=user) result.telegram_id = fake.pyint(min_value=10000000, max_value=10000000000, step=1) result.type = (fake.random_element(list(ChatType)) if (random_number % 10 == 0) else ChatType.PRIVATE) return result
class TestQuestionUserRelation(db.Model): """Relation with User and TestQuestion.""" __tablename__ = 'test_questions_users_relations' id = db.Column(db.Integer, primary_key=True) # noqa: A003 completed = db.Column(db.Boolean, nullable=False, default=False) test_question_id = db.Column( db.Integer, db.ForeignKey('test_questions.id'), ) test_question = db.relationship( 'TestQuestion', back_populates='user_relation', ) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship( 'User', back_populates='question_relation', ) def __str__(self): return f'Связь пользователя {self.user} и вопроса {self.test_question}'
class TestQuestion(db.Model): """Question for test case.""" __tablename__ = 'test_questions' id = db.Column(db.Integer, primary_key=True) # noqa: A003 text = db.Column(db.Text, nullable=False) question_type = db.Column(db.Integer, nullable=False) test_case_id = db.Column(db.Integer, db.ForeignKey('test_cases.id')) test_case = db.relationship('TestCase', back_populates='test_questions') user_relation = db.relationship( 'TestQuestionUserRelation', back_populates='test_question', ) answers = db.relationship('TestAnswer', back_populates='question') def __str__(self): slise_size = 50 return f'{self.text[:slise_size]}, тест кейс: {self.test_case}'
class Device(BaseModel): """ Devices """ __tablename__ = "devices" id = db.Column(db.Integer, primary_key=True) # pk hub_id = db.Column(db.Integer, db.ForeignKey("hubs.id"), nullable=False) # fk: hub_id name = db.Column(db.String(100), nullable=False) # device name device_type = db.Column(db.Integer, nullable=False) # device type 0: simulator, 1: real device platform = db.Column(db.String(100), nullable=False) # platform: iOS/Android model = db.Column(db.String(100), nullable=False) # device model platform_version = db.Column(db.String(100), nullable=False) # platform version api_level = db.Column(db.String(100), nullable=False) # api level (only for Android) status = db.Column(db.Integer, nullable=False) # device status 0: available, 1: unavailable created_time = Column(DateTime, default=datetime.datetime.utcnow) updated_time = Column(DateTime, default=datetime.datetime.utcnow) def __init__(self, hub_id, name, device_type, platform, model, platform_version, api_level, *args, **kwargs): self.hub_id = hub_id self.name = name self.device_type = device_type self.platform = platform self.model = model self.platform_version = platform_version self.api_level = api_level self.status = 0 self.created_time = datetime.datetime.now() self.updated_time = datetime.datetime.now() if kwargs and kwargs['id']: self.id = kwargs['id'] @staticmethod def all_devices(): """ Get all devices. :return: """ return Device.query.order_by(Device.id.desc()).all()
class ConfirmationModel(db.Model): __tablename__ = 'confirmations' id = db.Column(db.String(50), primary_key=True) expire_at = db.Column(db.Integer, nullable=False) confirmed = db.Column(db.Boolean, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) user = db.relationship("UserModel") def __init__(self, user_id: int, **kwargs): super().__init__(**kwargs) self.user_id = user_id self.id = uuid4().hex self.expire_at = int(time.time()) + EXPIRATION_DELTA self.confirmed = False @property def expired(self) -> bool: return time.time() > self.expire_at def force_to_expire(self) -> None: if not self.expired: self.expire_at = time.time() self.save_to_db() @classmethod def find_by_id(cls, _id: str) -> "ConfirmationModel": return cls.query.filter_by(id=_id).first() def save_to_db(self) -> None: db.session.add(self) db.session.commit() def delete_from_db(self) -> None: db.session.add(self) db.session.commit()
class Post(db.Model): __tablename__ = 'posts' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(128), nullable=False) description = db.Column(db.Text, nullable=False) contents = db.Column(db.Text, nullable=False) tags = db.Column(ARRAY(db.String)) comments = db.relationship( 'Comment', backref='post', lazy='dynamic', cascade='save-update, merge, delete, delete-orphan') owner_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) favorited_by = db.relationship( 'User', secondary=favorites_assoc, backref=db.backref('favorites', lazy='dynamic'), # Eager load the users who favorited this post using an # additional SELECT IN query lazy='selectin') created_at = db.Column(db.DateTime, nullable=False, default=dt.datetime.now) modified_at = db.Column(db.DateTime, nullable=False, default=dt.datetime.now) def save(self): self.modified_at = dt.datetime.now() db.session.add(self) db.session.commit() def update(self, **kwargs): for attr, value in kwargs.items(): setattr(self, attr, value) self.save() def delete(self): db.session.delete(self) db.session.commit() def favorite(self, user): self.favorited_by.append(user) self.save() def unfavorite(self, user): try: self.favorited_by.remove(user) self.save() except ValueError: pass @staticmethod def get_all(): return Post.query.all() @staticmethod def get_one(_id): return Post.query.get(_id) @hybrid_property def favorites_count(self): return len(self.favorited_by) def __repr__(self): return f"<id {self.id}>"
import datetime as dt from sqlalchemy import select, func, bindparam from flask import url_for from sqlalchemy.orm import column_property, aliased from sqlalchemy.ext.hybrid import hybrid_property from sqlalchemy.dialects.postgresql import ARRAY from flask_jwt_extended import current_user from src.extensions import db, bcrypt favorites_assoc = db.Table( 'favorites_assoc', db.Column('user_id', db.Integer, db.ForeignKey('users.id'), primary_key=True), db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True)) favaliased = aliased(favorites_assoc) class User(db.Model): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(128), nullable=False) bio = db.Column(db.String(300), nullable=True) username = db.Column(db.String(128), nullable=False, unique=True) email = db.Column(db.String(128), unique=True, nullable=False) password = db.Column(db.String(128), nullable=True)