class UserProfile(ModelMixin, db.Model): """ Model that describes the 'user_profiles' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "user_profiles" user_id: int = db.Column(db.Integer, db.ForeignKey("users.id", ondelete="CASCADE"), primary_key=True) favourite_team_abbreviation: Optional[str] = db.Column( db.String(3), db.ForeignKey("teams.abbreviation"), nullable=True) description: Optional[str] = db.Column(db.String(255), nullable=True) country: Optional[str] = db.Column(db.String(255), nullable=True) user: User = db.relationship( "User", backref=db.backref("profile", cascade="all, delete", uselist=False), ) favourite_team: Team = db.relationship("Team")
class SeasonWinner(ModelMixin, db.Model): """ Model that describes the 'season_winners' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "season_winners" league: str = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) user: User = db.relationship("User", backref=db.backref("season_winners", cascade="all, delete")) @property def season_string(self) -> str: """ :return: The season string, e.g. Bundesliga 2019/20 """ return Config.league_string(self.league, self.season)
class ApiKey(IDModelMixin, db.Model): """ Model that describes the 'api_keys' SQL table An ApiKey is used for API access using HTTP basic auth """ __tablename__ = "api_keys" """ The name of the table """ user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) """ The ID of the user associated with this API key """ user: User = db.relationship("User", back_populates="api_keys") """ The user associated with this API key """ key_hash: str = db.Column(db.String(255), nullable=False) """ The hash of the API key """ creation_time: int = \ db.Column(db.Integer, nullable=False, default=time.time) """ The time at which this API key was created as a UNIX timestamp """ def has_expired(self) -> bool: """ Checks if the API key has expired. API Keys expire after 30 days :return: True if the key has expired, False otherwise """ return time.time() - self.creation_time > Config.MAX_API_KEY_AGE def verify_key(self, key: str) -> bool: """ Checks if a given key is valid :param key: The key to check :return: True if the key is valid, False otherwise """ try: _id, api_key = key.split(":", 1) if int(_id) != self.id: return False else: return verify_password(api_key, self.key_hash) except ValueError: return False
class Goal(ModelMixin, db.Model): """ Model that describes the "goals" SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "goals" __table_args__ = (db.ForeignKeyConstraint( ("home_team_abbreviation", "away_team_abbreviation", "league", "season", "matchday"), (Match.home_team_abbreviation, Match.away_team_abbreviation, Match.league, Match.season, Match.matchday)), db.ForeignKeyConstraint( ("player_name", "player_team_abbreviation"), (Player.name, Player.team_abbreviation))) league: str = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) matchday: int = db.Column(db.Integer, primary_key=True) home_team_abbreviation: str = db.Column(db.String(3), primary_key=True) away_team_abbreviation: str = db.Column(db.String(3), primary_key=True) home_score: int = db.Column(db.Integer, primary_key=True) away_score: int = db.Column(db.Integer, primary_key=True) player_name: str = db.Column(db.String(255), nullable=False) player_team_abbreviation: str = db.Column(db.String(3), nullable=False) minute: int = db.Column(db.Integer, nullable=False) minute_et: int = db.Column(db.Integer, nullable=True, default=0) own_goal: bool = db.Column(db.Boolean, nullable=False, default=False) penalty: bool = db.Column(db.Boolean, nullable=False, default=False) match: Match = db.relationship("Match", overlaps="goals") player: Player = db.relationship("Player", overlaps="goals")
class SeasonEvent(ModelMixin, db.Model): """ Model that describes the 'season_events' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "season_events" season: int = db.Column(db.Integer, primary_key=True) league: int = db.Column(db.String(255), primary_key=True) event_type: SeasonEventType = db.Column(db.Enum(SeasonEventType), primary_key=True) executed: bool = db.Column(db.Boolean, nullable=False, default=False)
class TelegramChatId(IDModelMixin, db.Model): """ Model that describes the 'telegram_chat_ids' SQL table Maps telegram chat ids to users """ __tablename__ = "telegram_chat_ids" """ The name of the table """ user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False) """ The ID of the user associated with this telegram chat ID """ user: User = db.relationship("User", back_populates="telegram_chat_id") """ The user associated with this telegram chat ID """ chat_id: str = db.Column(db.String(255), nullable=False) """ The telegram chat ID """ def send_message(self, message_text: str): """ Sends a message to the telegram chat :param message_text: The message text to send :return: None """ try: address = Address(self.chat_id) message = TextMessage(Config.TELEGRAM_BOT_CONNECTION.address, address, message_text) Config.TELEGRAM_BOT_CONNECTION.send(message) except AttributeError: app.logger.error("Failed to send telegram message: no connection")
class MatchdayWinner(ModelMixin, db.Model): """ Model that describes the 'matchday_winners' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "matchday_winners" league: str = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) matchday: int = db.Column(db.Integer, primary_key=True) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True) user: User = db.relationship("User", backref=db.backref("matchday_winners", cascade="all, delete"))
class LnRelease(ModelMixin, db.Model): """ Database model that keeps track of light novel releases """ __tablename__ = "ln_releases" """ The name of the database table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) media_item_id: int = db.Column(db.Integer, db.ForeignKey("media_items.id"), nullable=True) """ The ID of the media item referenced by this release """ media_item: MediaItem = db.relationship("MediaItem", back_populates="ln_releases") """ The media item referenced by this release """ release_date_string: str = db.Column(db.String(10), nullable=False) """ The release date as a ISO-8601 string """ series_name: str = db.Column(db.String(255), nullable=False) """ The series name """ volume: str = db.Column(db.String(255), nullable=False) """ The volume identifier """ publisher: Optional[str] = db.Column(db.String(255), nullable=True) """ The publisher """ purchase_link: Optional[str] = db.Column(db.String(255), nullable=True) """ Link to a store page """ digital: bool = db.Column(db.Boolean) """ Whether this is a digital release """ physical: bool = db.Column(db.Boolean) """ Whether this is a physical release """ @property def release_date(self) -> datetime: """ :return: The release date as a datetime object """ return datetime.strptime(self.release_date_string, "%Y-%m-%d") @property def volume_number(self) -> int: """ :return: The volume number as an integer """ try: if re.match(r"^p[0-9]+[ ]*v[0-9]+$", self.volume.lower()): return int(self.volume.lower().split("v")[1]) else: stripped = "" for char in self.volume: if char.isdigit() or char in [".", "-"]: stripped += char if "-" in stripped: stripped = stripped.split("-")[1] if "." in stripped: stripped = stripped.split(".")[0] return int(stripped) except (TypeError, ValueError): return 0 @property def identifier_tuple(self) -> Tuple[str, str, bool, bool]: """ :return: A tuple that uniquely identifies this database entry """ return self.series_name, self.volume, self.digital, self.physical def update(self, new_data: "LnRelease"): """ Updates the data in this record based on another object :param new_data: The object from which to use the new values :return: None """ self.media_item_id = new_data.media_item_id self.series_name = new_data.series_name self.volume = new_data.volume self.release_date_string = new_data.release_date_string self.purchase_link = new_data.purchase_link self.publisher = new_data.publisher self.physical = new_data.physical self.digital = new_data.digital def get_ids(self) -> List[MediaId]: """ :return: Any related Media IDs """ if self.media_item is None: return [] else: return MediaId.query.filter_by(media_item=self.media_item).all()
class Bet(ModelMixin, db.Model): """ Model that describes the 'bets' SQL table """ MAX_POINTS: int = 15 POSSIBLE_POINTS: List[int] = [0, 3, 7, 10, 12, 15] def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "bets" __table_args__ = (db.ForeignKeyConstraint( ("home_team_abbreviation", "away_team_abbreviation", "league", "season", "matchday"), (Match.home_team_abbreviation, Match.away_team_abbreviation, Match.league, Match.season, Match.matchday)), ) league: str = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) matchday: int = db.Column(db.Integer, primary_key=True) home_team_abbreviation: str = db.Column(db.String(3), primary_key=True) away_team_abbreviation: str = db.Column(db.String(3), primary_key=True) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), primary_key=True) home_score: int = db.Column(db.Integer, nullable=False) away_score: int = db.Column(db.Integer, nullable=False) points: int = db.Column(db.Integer, nullable=True) user: User = db.relationship("User", backref=db.backref("bets", cascade="all, delete")) match: Match = db.relationship("Match", overlaps="bets") def __repr__(self) -> str: """ :return: A string with which the object may be generated """ params = "" for key, val in self.__json__().items(): if key == "points": continue params += "{}={}, ".format(key, repr(val)) params = params.rsplit(",", 1)[0] return "{}({})".format(self.__class__.__name__, params) def __eq__(self, other: Any) -> bool: """ Checks the model object for equality with another object :param other: The other object :return: True if the objects are equal, False otherwise """ if isinstance(other, Bet): return self.user_id == other.user_id \ and self.home_team_abbreviation == \ other.home_team_abbreviation \ and self.away_team_abbreviation == \ other.away_team_abbreviation \ and self.home_score == other.home_score \ and self.away_score == other.away_score else: return False # pragma: no cover def evaluate(self) -> Optional[int]: """ Evaluates the current points score on this bet :return: The calculated points (or None if the math hasn't started yet) """ if not self.match.has_started: return None points = 0 bet_diff = self.home_score - self.away_score match_diff = \ self.match.home_current_score - self.match.away_current_score if bet_diff == match_diff: # Correct goal difference points += 5 if bet_diff * match_diff > 0: # Correct winner points += 7 elif bet_diff == 0 and match_diff == 0: # Draw points += 7 if self.home_score == self.match.home_current_score \ or self.away_score == self.match.away_current_score: points += 3 return points
class LeaderboardEntry(ModelMixin, db.Model): """ Model that describes the 'leaderboard_entries' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "leaderboard_entries" league: int = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) matchday: int = db.Column(db.Integer, primary_key=True) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), primary_key=True) points: int = db.Column(db.Integer, nullable=False) position: int = db.Column(db.Integer, nullable=False) no_bot_position: int = db.Column(db.Integer, nullable=False) previous_position: int = db.Column(db.Integer, nullable=False) no_bot_previous_position: int = db.Column(db.Integer, nullable=False) user: User = db.relationship("User", backref=db.backref("leaderboard_entries", cascade="all, delete")) def get_position_info(self, include_bots: bool) -> Tuple[int, int]: """ Retrieves position info :param include_bots: Whether or not to include bots in the ranking :return: Current Position, Previous Position """ current = self.position if include_bots else self.no_bot_position previous = self.previous_position \ if include_bots else self.no_bot_previous_position return current, previous def get_tendency(self, include_bots: bool) -> str: """ Calculates the position tendency :param include_bots: Whether or not to include bots :return: The tendency as a string (example '+2') """ current, previous = self.get_position_info(include_bots) tendency = previous - current if tendency < 0: return str(tendency) elif tendency > 0: return f"+{tendency}" else: return "-" def get_tendency_class(self, include_bots: bool) -> str: """ Calculates the tendency and returns the corrsponding CSS class :param include_bots: Whether or not to include bots :return: The tendency CSS class name """ current, previous = self.get_position_info(include_bots) if current < previous: return "chevron-circle-up" elif current > previous: return "chevron-circle-down" else: return "minus-circle" @classmethod def load_history(cls, league: str, season: int, matchday: int) -> \ List[Tuple[User, List["LeaderboardEntry"]]]: """ Loads the history for the previous matches in a season for each user :param league: The league for which to retrieve the history :param season: The season for which to retrieve the history :param matchday: The matchday for which to retrieve the history :return: The history as a list of tuples of users and a list of the corresponding LeaderboardEntry objects. Sorted by current position """ entries: List[LeaderboardEntry] = [ x for x in LeaderboardEntry.query.filter_by( league=league, season=season).options( db.joinedload(LeaderboardEntry.user)).all() if x.matchday <= matchday ] entries.sort(key=lambda x: x.matchday) history_dict: Dict[int, List[LeaderboardEntry]] = {} for entry in entries: if entry.user_id not in history_dict: history_dict[entry.user_id] = [] history_dict[entry.user_id].append(entry) history_list = [(history[-1].user, history) for _, history in history_dict.items()] history_list.sort(key=lambda x: x[1][-1].position) return history_list
class User(IDModelMixin, db.Model): """ Model that describes the 'users' SQL table A User stores a user's information, including their email address, username and password hash """ __tablename__ = "users" """ The name of the table """ username: str = db.Column(db.String(Config.MAX_USERNAME_LENGTH), nullable=False, unique=True) """ The user's username """ email: str = db.Column(db.String(150), nullable=False, unique=True) """ The user's email address """ password_hash: str = db.Column(db.String(255), nullable=False) """ The user's hashed password, salted and hashed. """ confirmed: bool = db.Column(db.Boolean, nullable=False, default=False) """ The account's confirmation status. Logins should be impossible as long as this value is False. """ confirmation_hash: str = db.Column(db.String(255), nullable=False) """ The account's confirmation hash. This is the hash of a key emailed to the user. Only once the user follows the link in the email containing the key will their account be activated """ telegram_chat_id: Optional["TelegramChatId"] = db.relationship( "TelegramChatId", uselist=False, back_populates="user", cascade="all, delete") """ Telegram chat ID for the user if set up """ api_keys: List["ApiKey"] = db.relationship("ApiKey", back_populates="user", cascade="all, delete") """ API keys for this user """ @property def is_authenticated(self) -> bool: """ Property required by flask-login :return: True if the user is confirmed, False otherwise """ return True @property def is_anonymous(self) -> bool: """ Property required by flask-login :return: True if the user is not confirmed, False otherwise """ return not self.is_authenticated # pragma: no cover @property def is_active(self) -> bool: """ Property required by flask-login :return: True """ return self.confirmed def get_id(self) -> str: """ Method required by flask-login :return: The user's ID as a unicode string """ return str(self.id) def verify_password(self, password: str) -> bool: """ Verifies a password against the password hash :param password: The password to check :return: True if the password matches, False otherwise """ return verify_password(password, self.password_hash) def verify_confirmation(self, confirmation_key: str) -> bool: """ Verifies a confirmation key against the confirmation hash :param confirmation_key: The key to check :return: True if the key matches, False otherwise """ return verify_password(confirmation_key, self.confirmation_hash)
class ReminderSettings(ModelMixin, db.Model): """ Database model that keeps track of reminder settings """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "reminder_settings" user_id: int = db.Column( db.Integer, db.ForeignKey("users.id"), primary_key=True ) reminder_type = db.Column(db.Enum(ReminderType), primary_key=True) active = db.Column(db.Boolean, nullable=False, default=True) reminder_time: int = db.Column(db.Integer, nullable=False, default=86400) last_reminder: str = db.Column(db.String(19), nullable=False, default="1970-01-01:01-01-01") user: User = db.relationship( "User", backref=db.backref("reminder_settings", cascade="all, delete") ) @property def reminder_time_delta(self) -> timedelta: """ :return: The 'reminder_time' parameter as a datetime timedelta """ return timedelta(seconds=self.reminder_time) @property def last_reminder_datetime(self) -> datetime: """ :return: The 'last_reminder' parameter as a datetime object """ return datetime.strptime(self.last_reminder, "%Y-%m-%d:%H-%M-%S") def set_reminder_time(self, reminder_time: int): """ Sets the reminder time and resets the time stored as the last reminder :param reminder_time: the new reminder time :return: None """ self.reminder_time = reminder_time self.last_reminder = "1970-01-01:01-01-01" db.session.commit() def get_due_matches(self) -> List[Match]: """ Checks if the reminder is due and returns a list of matches that the user still needs to bet on. :return: The matches for which the reminder is due """ now = datetime.utcnow() start = max(now, self.last_reminder_datetime) start_str = start.strftime("%Y-%m-%d:%H-%M-%S") then = now + self.reminder_time_delta then_str = then.strftime("%Y-%m-%d:%H-%M-%S") due_matches: List[Match] = [ x for x in Match.query.filter_by( season=Config.season(), league=Config.OPENLIGADB_LEAGUE ).all() if start_str < x.kickoff < then_str ] user_bet_matches = [ (bet.match.home_team_abbreviation, bet.match.away_team_abbreviation, bet.match.season) for bet in Bet.query.filter_by( user_id=self.user_id, season=Config.season(), league=Config.OPENLIGADB_LEAGUE ).options(db.joinedload(Bet.match)).all() ] to_remind = [] for match in due_matches: identifier = (match.home_team_abbreviation, match.away_team_abbreviation, match.season) if identifier not in user_bet_matches: to_remind.append(match) return to_remind def send_reminder(self): """ Sends a reminder message if it's due :return: None """ due = self.get_due_matches() if len(due) < 1: return else: app.logger.debug("Sending reminder to {}.".format(self.user.email)) message = render_template( "email/reminder.html", user=self.user, matches=due, hours=int(self.reminder_time / 3600) ) self.send_reminder_message(message) last_match = max(due, key=lambda x: x.kickoff) self.last_reminder = last_match.kickoff db.session.commit() def send_reminder_message(self, message: str): """ Sends a reminder message using the appropriate method of delivery :param message: The message to send :return: None """ if self.reminder_type == ReminderType.EMAIL: try: send_email( self.user.email, "Tippspiel Erinnerung", message, Config.SMTP_HOST, Config.SMTP_ADDRESS, Config.SMTP_PASSWORD, Config.SMTP_PORT ) except SMTPAuthenticationError: app.logger.error("Invalid SMTP settings, failed to send email") elif self.reminder_type == ReminderType.TELEGRAM: telegram = TelegramChatId.query.filter_by(user=self.user).first() if telegram is not None: message = BeautifulSoup(message, "html.parser").text message = "\n".join([x.strip() for x in message.split("\n")]) telegram.send_message(message)
class ServiceUsername(ModelMixin, db.Model): """ Database model that stores an external service username for a user """ __tablename__ = "service_usernames" """ The name of the database table """ __table_args__ = (db.UniqueConstraint("user_id", "username", "service", name="unique_service_username"), ) """ Makes sure that objects that should be unique are unique """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id", ondelete="CASCADE", onupdate="CASCADE"), nullable=False) """ The ID of the user associated with this service username """ user: User = db.relationship("User", backref=db.backref("service_usernames", lazy=True, cascade="all,delete")) """ The user associated with this service username """ username: str = db.Column(db.String(255), nullable=False) """ The service username """ service: ListService = db.Column(db.Enum(ListService), nullable=False) """ The external service this item is a username for """ @property def identifier_tuple(self) -> Tuple[int, str, ListService]: """ :return: A tuple that uniquely identifies this database entry """ return self.user_id, self.username, self.service def update(self, new_data: "ServiceUsername"): """ Updates the data in this record based on another object :param new_data: The object from which to use the new values :return: None """ self.user_id = new_data.user_id self.username = new_data.username self.service = new_data.service
class MediaId(ModelMixin, db.Model): """ Database model for media IDs. These are used to map media items to their corresponding external IDS on external sites. """ __tablename__ = "media_ids" """ The name of the database table """ __table_args__ = ( db.UniqueConstraint("media_item_id", "service", "media_type", name="unique_media_item_service_id"), db.UniqueConstraint("media_type", "service", "service_id", name="unique_service_id"), db.ForeignKeyConstraint( ["media_item_id", "media_type", "media_subtype"], [ "media_items.id", "media_items.media_type", "media_items.media_subtype" ]), ) """ Makes sure that objects that should be unique are unique """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) media_item_id: int = db.Column(db.Integer, nullable=False) """ The ID of the media item referenced by this ID """ media_item: MediaItem = db.relationship("MediaItem", back_populates="media_ids") """ The media item referenced by this ID """ media_type: MediaType = db.Column(db.Enum(MediaType), nullable=False) """ The media type of the list item """ media_subtype: MediaSubType = \ db.Column(db.Enum(MediaSubType), nullable=False) """ The media subtype of the list item """ service_id: str = db.Column(db.String(255), nullable=False) """ The ID of the media item on the external service """ service: ListService = db.Column(db.Enum(ListService), nullable=False) """ The service for which this object represents an ID """ media_user_states: List["MediaUserState"] = db.relationship( "MediaUserState", back_populates="media_id", cascade="all, delete") """ Media user states associated with this media ID """ chapter_guess: Optional["MangaChapterGuess"] = db.relationship( "MangaChapterGuess", uselist=False, back_populates="media_id", cascade="all, delete") """ Chapter Guess for this media ID (Only applicable if this is a manga title) """ @property def service_url(self) -> str: """ :return: The URL to the series for the given service """ url_format = list_service_url_formats[self.service] url = url_format \ .replace("@{media_type}", f"{self.media_type.value}") \ .replace("@{id}", self.service_id) return url @property def service_icon(self) -> str: """ :return: The path to the service's icon file """ return url_for( "static", filename=f"images/service_logos/{self.service.value}.png") @property def identifier_tuple(self) -> Tuple[MediaType, ListService, str]: """ :return: A tuple that uniquely identifies this database entry """ return self.media_type, self.service, self.service_id def update(self, new_data: "MediaId"): """ Updates the data in this record based on another object :param new_data: The object from which to use the new values :return: None """ self.media_item_id = new_data.media_item_id self.media_type = new_data.media_type self.service = new_data.service self.service_id = new_data.service_id
class Match(ModelMixin, db.Model): """ Model that describes the 'matches' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "matches" league: str = db.Column(db.String(255), primary_key=True) season: int = db.Column(db.Integer, primary_key=True) matchday: int = db.Column(db.Integer, primary_key=True) home_team_abbreviation: str = db.Column( db.String(3), db.ForeignKey("teams.abbreviation"), primary_key=True, ) away_team_abbreviation: str = db.Column( db.String(3), db.ForeignKey("teams.abbreviation"), primary_key=True) home_current_score: int = db.Column(db.Integer, nullable=False) away_current_score: int = db.Column(db.Integer, nullable=False) home_ht_score: int = db.Column(db.Integer) away_ht_score: int = db.Column(db.Integer) home_ft_score: int = db.Column(db.Integer) away_ft_score: int = db.Column(db.Integer) kickoff: str = db.Column(db.String(255), nullable=False) started: bool = db.Column(db.Boolean, nullable=False) finished: bool = db.Column(db.Boolean, nullable=False) home_team: "Team" = db.relationship( "Team", foreign_keys=[home_team_abbreviation], ) away_team: "Team" = db.relationship("Team", foreign_keys=[away_team_abbreviation]) goals: List["Goal"] = db.relationship("Goal", cascade="all, delete") bets: List["Bet"] = db.relationship("Bet", cascade="all, delete") @property def minute_display(self) -> str: """ This generates a string for displaying the current match minute. Sadly, since OpenligaDB does not provide information on the current minute, this can only offer an approximation. :return: A formatted string displaying the current match minute """ delta = (datetime.utcnow() - self.kickoff_datetime).total_seconds() delta = int(delta / 60) if self.finished: return "Ende" elif 0 <= delta <= 44: return "{}.".format(delta + 1) elif 45 <= delta < 47: # buffer for ET return "45." elif 47 <= delta <= 64: return "HZ" elif 65 <= delta <= 109: return "{}.".format(delta - 65 + 1 + 45) elif delta >= 110: return "90." else: return "-" @property def current_score(self) -> str: """ :return: The current score formatted as a string """ return "{}:{}".format(self.home_current_score, self.away_current_score) @property def ht_score(self) -> str: """ :return: The half time score formatted as a string """ return "{}:{}".format(self.home_ht_score, self.away_ht_score) @property def ft_score(self) -> str: """ :return: The full time score formatted as a string """ return "{}:{}".format(self.home_ft_score, self.away_ft_score) @property def kickoff_datetime(self) -> datetime: """ :return: A datetime object representing the kickoff time """ return datetime.strptime(self.kickoff, "%Y-%m-%d:%H-%M-%S") @kickoff_datetime.setter def kickoff_datetime(self, kickoff: datetime): """ Setter for the kickoff datetime :param kickoff: The new kickoff datetime :return: None """ self.kickoff = kickoff.strftime("%Y-%m-%d:%H-%M-%S") @property def kickoff_local_datetime(self) -> datetime: """ :return: A datetime object representing the kickoff time in local time """ return self.kickoff_datetime.astimezone(pytz.timezone("europe/berlin")) @property def kickoff_time_string(self) -> str: """ :return: A string representing the kickoff time """ return self.kickoff_local_datetime.strftime("%H:%M") @property def kickoff_date_string(self) -> str: """ :return: A string representing the kickoff date """ return self.kickoff_local_datetime.strftime("%d. %m. %Y") @property def has_started(self) -> bool: """ Checks if the match has started. This is to be preferred over the 'started' attribute, just in case the database update has failed for any reason. :return: True if the match has started, False otherwise """ return self.started or self.kickoff_datetime <= datetime.utcnow() @property def url(self) -> str: """ :return: The URL for this match's info page """ return url_for("info.match", league=self.league, season=self.season, matchday=self.matchday, matchup=f"{self.home_team_abbreviation}_" f"{self.away_team_abbreviation}")
class A(IDModelMixin, db.Model): __tablename__ = "a" s = db.Column(db.String(255))
class ChatMessage(ModelMixin, db.Model): """ Model that describes the 'chat_messages' SQL table """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "chat_messages" id: int = db.Column(db.Integer, primary_key=True, autoincrement=True) user_id: int = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=True) parent_id: int = db.Column(db.Integer, db.ForeignKey("chat_messages.id"), nullable=True) text: str = db.Column(db.String(255), nullable=False) creation_time: float = db.Column(db.Float, nullable=False, default=time.time) last_edit: float = db.Column(db.Float, nullable=False, default=time.time) edited: bool = db.Column(db.Boolean, nullable=False, default=False) deleted: bool = db.Column(db.Boolean, nullable=False, default=False) user: Optional[User] = db.relationship("User", backref=db.backref("chat_messages")) parent: "ChatMessage" = db.relationship("ChatMessage", back_populates="children", remote_side=[id], uselist=False) children: List["ChatMessage"] = db.relationship("ChatMessage", back_populates="parent", uselist=True) def get_text(self) -> Optional[str]: """ :return: The text of the chat message if the user still exists and the message has not been deleted """ if self.deleted or self.user is None: return None else: return self.text def edit(self, new_text: str): """ Edits the message :param new_text: The new message text :return: None """ self.text = new_text self.edited = True self.last_edit = time.time() def delete(self): """ Marks the message as deleted :return: None """ self.text = "" self.deleted = True def __json__(self, include_children: bool = False, ignore_keys: Optional[List[str]] = None) -> Dict[str, Any]: """ Makes sure to include children and parents :param include_children: Whether or not to include child objects :param ignore_keys: Which keys to ignore :return: The JSON data """ data = super().__json__(include_children, ignore_keys) # TODO Add child and parent relations return data
class Team(ModelMixin, db.Model): """ Model that describes the 'teams' SQL table A Team is the most basic data for a match, it relies on no other data, only primitives """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) __tablename__ = "teams" abbreviation: str = db.Column(db.String(3), primary_key=True) name: str = db.Column(db.String(50), nullable=False, unique=True) short_name: str = db.Column(db.String(16), nullable=False, unique=True) icon_svg: str = db.Column(db.String(255), nullable=False) icon_png: str = db.Column(db.String(255), nullable=False) players: List["Player"] = db.relationship("Player", cascade="all, delete") @property def home_matches(self) -> List[Match]: """ :return: A list of home matches for the team """ return Match.query.filter_by( home_team_abbreviation=self.abbreviation ).all() @property def away_matches(self) -> List[Match]: """ :return: A list of away matches for the team """ return Match.query.filter_by( away_team_abbreviation=self.abbreviation ).all() @property def matches(self) -> List[Match]: """ :return: A list of matches for the team """ return self.home_matches + self.away_matches @property def url(self) -> str: """ :return: The URL for this teams's info page """ return url_for("info.team", team_abbreviation=self.abbreviation) @classmethod def get_teams_for_season(cls, league: str, season: int) -> List["Team"]: """ Retrieves a list of all teams in a particular season :param league: The league in which to search for teams :param season: The season in which to search for teams :return: The list of teams """ match_samples = Match.query.filter_by( league=league, season=season, matchday=1 ).all() home_teams = [x.home_team for x in match_samples] away_teams = [x.away_team for x in match_samples] return home_teams + away_teams
class MediaItem(ModelMixin, db.Model): """ Database model for media items. These model a generic, site-agnostic representation of a series. """ __tablename__ = "media_items" """ The name of the database table """ __table_args__ = ( db.UniqueConstraint("media_type", "media_subtype", "romaji_title", name="unique_media_item_data"), db.UniqueConstraint("id", "media_type", "media_subtype", name="unique_media_item"), ) """ Makes sure that objects that should be unique are unique """ def __init__(self, *args, **kwargs): """ Initializes the Model :param args: The constructor arguments :param kwargs: The constructor keyword arguments """ super().__init__(*args, **kwargs) media_type: MediaType = db.Column(db.Enum(MediaType), nullable=False) """ The media type of the list item """ media_subtype: MediaSubType = db.Column(db.Enum(MediaSubType), nullable=False) """ The subtype (for example, TV short, movie oneshot etc) """ english_title: Optional[str] = db.Column(db.Unicode(255), nullable=True) """ The English title of the media item """ romaji_title: str = db.Column(db.Unicode(255), nullable=False) """ The Japanese title of the media item written in Romaji """ cover_url: str = db.Column(db.String(255), nullable=False) """ An URL to a cover image of the media item """ latest_release: Optional[int] = db.Column(db.Integer, nullable=True) """ The latest release chapter/episode for this media item """ latest_volume_release: Optional[int] = db.Column(db.Integer, nullable=True) """ The latest volume for this media item """ next_episode: Optional[int] = db.Column(db.Integer, nullable=True) """ The next episode to air """ next_episode_airing_time: Optional[int] = \ db.Column(db.Integer, nullable=True) """ The time the next episode airs """ releasing_state: ReleasingState = db.Column(db.Enum(ReleasingState), nullable=False) """ The current releasing state of the media item """ media_ids: List["MediaId"] = db.relationship("MediaId", back_populates="media_item", cascade="all, delete") """ Media IDs associated with this Media item """ ln_releases: List["LnRelease"] = db.relationship( "LnRelease", back_populates="media_item", cascade="all, delete") """ Light novel releases associated with this Media item """ @property def current_release(self) -> Optional[int]: """ The most current release, specifically tailored to the type of media :return: None """ if self.next_episode is not None: return self.next_episode - 1 elif self.latest_volume_release is not None: return self.latest_volume_release elif self.latest_release is not None: return self.latest_release else: return None @property def media_id_mapping(self) -> Dict[ListService, "MediaId"]: """ :return: A dictionary mapping list services to IDs for this media item """ return {x.service: x for x in self.media_ids} @property def identifier_tuple(self) -> Tuple[str, MediaType, MediaSubType]: """ :return: A tuple that uniquely identifies this database entry """ return self.romaji_title, self.media_type, self.media_subtype def update(self, new_data: "MediaItem"): """ Updates the data in this record based on another object :param new_data: The object from which to use the new values :return: None """ self.media_type = new_data.media_type self.media_subtype = new_data.media_subtype self.english_title = new_data.english_title self.romaji_title = new_data.romaji_title self.cover_url = new_data.cover_url self.latest_release = new_data.latest_release self.latest_volume_release = new_data.latest_volume_release self.releasing_state = new_data.releasing_state self.next_episode = new_data.next_episode self.next_episode_airing_time = new_data.next_episode_airing_time @property def title(self) -> str: """ :return: The default title for the media item. """ if self.english_title is None: return self.romaji_title else: return self.english_title @property def own_url(self) -> str: """ :return: The URL to the item's page on the otaku-info site """ return url_for("media.media", media_item_id=self.id) @property def next_episode_datetime(self) -> Optional[datetime]: """ :return: The datetime for when the next episode airs """ if self.next_episode_airing_time is None: return None else: return datetime.fromtimestamp(self.next_episode_airing_time)