class Collection(db.Model): """Table for storing references to various media""" __tablename__ = 'collection' id = Column(Integer, primary_key=True) title = Column(String(64, convert_unicode=True), nullable=False) username = Column(String(16, convert_unicode=True), nullable=False) notes = Column(Text) archived = Column(Boolean, default=False) created_at = Column(UtcDateTime(), default=utcnow()) updated_at = Column(UtcDateTime(), onupdate=utcnow()) medias = relationship("Media", secondary="collection_media") def toJSON(self): return { 'id': self.id, 'title': self.title, 'username': self.username, 'notes': self.notes, 'archived': self.archived, 'created_at': self.created_at, 'updated_at': self.updated_at, } def toFullJSON(self): return { 'id': self.id, 'title': self.title, 'username': self.username, 'notes': self.notes, 'archived': self.archived, 'created_at': self.created_at, 'updated_at': self.updated_at, 'media': [media.toJSON() for media in self.medias], }
class LongTimeout(Base): __tablename__ = "long_timeout" id = Column(INT, primary_key=True) user_id = Column(INT, ForeignKey("user.id", ondelete="CASCADE"), index=True, nullable=False) timeout_start = Column(UtcDateTime(), nullable=False) timeout_recent_end = Column(UtcDateTime()) timeout_end = Column(UtcDateTime(), nullable=False) timeout_author = Column(TEXT, nullable=False) user = relationship("User") def __init__( self, user_id, timeout_start, timeout_end, timeout_author, timeout_recent_end=(utils.now() + datetime.timedelta(days=14)), ): self.user_id = user_id self.timeout_start = timeout_start self.timeout_recent_end = timeout_recent_end self.timeout_end = timeout_end self.timeout_author = timeout_author user = relationship("User")
class model(TableBase): idmodel = Column(Integer, primary_key=True, autoincrement=True, nullable=False) idmodel_set = Column(Integer, ForeignKey('model_set.idmodel_set', onupdate='RESTRICT', ondelete='CASCADE'), nullable=False) idalgorithm = Column(Integer, ForeignKey('algorithm.idalgorithm', onupdate='RESTRICT', ondelete='CASCADE'), nullable=False) idmodel_status = Column(Integer, ForeignKey('model_status.idmodel_status', onupdate='RESTRICT', ondelete='RESTRICT'), nullable=False) insert_time = Column(UtcDateTime(timezone=True), default=utcnow(), nullable=False) uuid = Column(String(34), nullable=False) hyperparameters = Column(String(STRING_LENGTH), nullable=False) trained_time = Column(UtcDateTime(timezone=True), nullable=True) trained_package = Column(LargeBinary(STRING_LENGTH), nullable=True) __table_args__ = (UniqueConstraint('uuid'), Index('idxmodel_uuid', 'uuid'))
class Deck(Base): __tablename__ = "deck" id = Column(INT, primary_key=True) name = Column(TEXT) deck_class = Column("class", TEXT) link = Column(TEXT, nullable=False) first_used = Column(UtcDateTime(), nullable=False) last_used = Column(UtcDateTime(), nullable=False) times_used = Column(INT, nullable=False, default=1) def __init__(self): self.id = None self.name = "" self.deck_class = "undefined" self.link = None self.times_used = 1 def set(self, **options): self.name = options.get("name", self.name) self.deck_class = options.get("class", self.deck_class) self.link = options.get("link", self.link) self.times_used = options.get("times_used", self.times_used) self.first_used = options.get("first_used", self.first_used) self.last_used = options.get("last_used", self.last_used) @property def last_used_ago(self): return time_ago(self.last_used, "long") @property def first_used_ago(self): return time_ago(self.last_used, "long")
class Stream(Base): __tablename__ = "stream" id = Column(INT, primary_key=True) title = Column(TEXT, nullable=False) stream_start = Column(UtcDateTime(), nullable=False) stream_end = Column(UtcDateTime(), nullable=True) ended = Column(BOOLEAN, nullable=False, default=False) stream_chunks = relationship("StreamChunk", backref="stream", cascade="save-update, merge, expunge", lazy="joined") def __init__(self, created_at, **options): self.id = None self.title = options.get("title", "NO TITLE") self.stream_start = BaseAPI.parse_datetime(created_at) self.stream_end = None self.ended = False @property def uptime(self): """ Returns a TimeDelta for how long the stream was online, or is online. """ if self.ended is False: return utils.now() - self.stream_start return self.stream_end - self.stream_start
class BaseSQLModel(object): """Base model for SQLAlchemy.""" id = Column('id', Integer, primary_key=True) created = Column(UtcDateTime(), default=utcnow()) modified = Column(UtcDateTime(), default=utcnow(), onupdate=utcnow()) is_enabled = Column(Boolean(), default=True) is_deleted = Column(Boolean(), default=False)
class TimeMixin(object): """A mixin that adds ``created_at`` and ``updated_at`` columns to any declarative-mapped class.""" __table_args__ = {'extend_existing': True} created_at = Column(UtcDateTime(), nullable=False, default=utcnow()) updated_at = Column(UtcDateTime(), nullable=False, default=utcnow(), onupdate=utcnow())
class Event(Base): __tablename__ = "events" id = Column("id", Integer, primary_key=True, index=True) name = Column("name", String, nullable=False) start_date = Column("start_date", UtcDateTime(), nullable=False) end_date = Column("end_date", UtcDateTime(), nullable=False) drinks: List[Drink] = relationship("Drink", backref="drinks", lazy=False) throwups: List[Throwup] = relationship("Throwup", backref="throwups", lazy=False)
class PleblistSong(Base): __tablename__ = "pleblist_song" id = Column(INT, primary_key=True) stream_id = Column(INT, ForeignKey("stream.id", ondelete="CASCADE"), index=True, nullable=False) user_id = Column(INT, ForeignKey("user.id", ondelete="SET NULL"), nullable=True) youtube_id = Column(TEXT, index=True, nullable=False) date_added = Column(UtcDateTime(), nullable=False) date_played = Column(UtcDateTime(), nullable=True) skip_after = Column(INT, nullable=True) song_info = relationship( "PleblistSongInfo", uselist=False, primaryjoin= "PleblistSongInfo.pleblist_song_youtube_id==PleblistSong.youtube_id", foreign_keys="PleblistSongInfo.pleblist_song_youtube_id", cascade="save-update,merge,expunge", lazy="joined", ) def __init__(self, stream_id, youtube_id, **options): self.id = None self.stream_id = stream_id self.user_id = options.get("user_id", None) self.youtube_id = youtube_id self.date_added = utils.now() self.date_played = None self.skip_after = options.get("skip_after", None) if self.skip_after is not None and self.skip_after < 0: # Make sure skip_after cannot be a negative number self.skip_after = None def jsonify(self): return { "id": self.id, "youtube_id": self.youtube_id, "skip_after": self.skip_after, "info": self.song_info.jsonify() if self.song_info is not None else None, } @property def link(self): return f"youtu.be/{self.youtube_id}"
class GiveawayEntry(Base): __tablename__ = "giveaway_entries" id = Column(INT, primary_key=True, autoincrement=True) user_id = Column(TEXT, ForeignKey("user.discord_id", ondelete="CASCADE")) user = relationship("User") giveaway_id = Column(INT, ForeignKey('giveaways.id', ondelete="CASCADE")) giveaway = relationship("Giveaway", back_populates="entries") date_entered = Column(UtcDateTime(), nullable=False) tickets = Column(INT, nullable=False, default=1) def _remove(self, db_session): db_session.delete(self) @staticmethod def _create(db_session, user_id, giveaway_id, tickets): if GiveawayEntry.is_entered(db_session, user_id, giveaway_id): return None giveaway_entry = GiveawayEntry(user_id=user_id, giveaway_id=giveaway_id, date_entered=utils.now(), tickets=tickets) db_session.add(giveaway_entry) return giveaway_entry @staticmethod def is_entered(db_session, user_id, giveaway_id): return db_session.query(GiveawayEntry).filter_by( user_id=user_id).filter_by(giveaway_id=giveaway_id).one_or_none()
class PredictionRun(Base): __tablename__ = "prediction_run" id = Column(INT, primary_key=True) type = Column(INT, nullable=False, server_default="0") winner_id = Column(INT, nullable=True) started = Column(UtcDateTime(), nullable=False) ended = Column(UtcDateTime(), nullable=True) open = Column(BOOLEAN, nullable=False, default=True, server_default=sqlalchemy.sql.expression.true()) def __init__(self, type): self.id = None self.type = type self.winner_id = None self.started = utils.now() self.ended = None
class Upload(db.Model): """Table for storing references to various media""" __tablename__ = 'uploads' id = Column(Integer, primary_key=True) sha256 = Column(HashColumn(32), nullable=False) ext = Column(String(4, convert_unicode=True), nullable=False) username = Column(String(16, convert_unicode=True), nullable=False) created_at = Column(UtcDateTime(), default=utcnow()) def toJSON(self): return { 'id': self.id, 'sha256': self.sha256, 'ext': self.ext, 'username': self.username, 'url': self.url(), 'created_at': self.created_at, } def filename(self): return "{}{}".format(self.sha256, self.ext) def filepath(self): return join(app_cfg.DIR_UPLOADS, sha256_tree(self.sha256)) def fullpath(self): return join(self.filepath(), self.filename()) def url(self): return join(app_cfg.URL_UPLOADS, sha256_tree(self.sha256), self.filename())
class Movie(Base): id = Column(UUID(as_uuid=True), default=uuid4, primary_key=True) poster = Column(String, nullable=False) synopsis = Column(String, nullable=False) slug = Column(String, nullable=False, unique=True) title = Column(String, nullable=False, unique=True) release_date = Column(UtcDateTime(), nullable=False) __tablename__ = 'movies'
class Comment(Base): id = Column(UUID(as_uuid=True), default=uuid4, primary_key=True) author_id = Column(UUID(as_uuid=True), nullable=False) target_id = Column(UUID(as_uuid=True), nullable=False, ) created_at = Column(UtcDateTime(), nullable=False, default=utcnow()) comment = Column(JSON(), nullable=False) __tablename__ = 'comments'
class FeatureType(db.Model): """Table for referencing feature indexes""" __tablename__ = 'feature_type' id = Column(Integer, primary_key=True) active = Column(Boolean, default=True) modelzoo_name = Column(String(64, convert_unicode=True), nullable=False, unique=True) index_type = Column(String(16, convert_unicode=True), nullable=False) # faiss or annoy username = Column(String(16, convert_unicode=True)) created_at = Column(UtcDateTime(), default=utcnow()) updated_at = Column(UtcDateTime(), onupdate=utcnow()) process_id = Column(Integer, default=0) processed_at = Column(UtcDateTime(), nullable=True) index_id = Column(Integer, default=0) indexed_at = Column(UtcDateTime(), nullable=True) index_settings = Column(Text, nullable=True) def toJSON(self): return { 'id': self.id, 'active': self.active, 'modelzoo_name': self.modelzoo_name, 'username': self.username, 'index_type': self.index_type, # faiss, annoy 'created_at': self.created_at, 'processed_at': self.processed_at, 'process_id': self.process_id, 'indexed_at': self.indexed_at, 'index_id': self.index_id, 'index_settings': self.index_settings, # a JSON blob with e.g. factory type } def get_recipe(self, type): try: data = json.loads(self.index_settings) except Exception as e: print(e) return {} if type in data: return data[type] return {}
class RefreshToken(db.Model): id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.ForeignKey('user.id'), nullable=False) jti = db.Column(db.Text, nullable=False) exp = db.Column(UtcDateTime(), nullable=False) revoked = db.Column(db.Boolean, default=False) def __repr__(self): return "<RefreshToken {0} for {1} (expires {})>".format( self.jti, self.user, self.exp)
class Media(db.Model): """Table for storing references to various media""" __tablename__ = 'media' id = Column(Integer, primary_key=True) # parent_id = Column(Integer, ForeignKey('media.id'), nullable=True) mediaType = Column(MediaTypeColumn(), nullable=False) sha256 = Column(HashColumn(32), nullable=False) ext = Column(String(4, convert_unicode=True), nullable=False) frame = Column(Integer, nullable=True) created_at = Column(UtcDateTime(), default=utcnow()) # children = relationship("Media") media_features = relationship("MediaFeature") media_metadata = relationship("MediaMetadata") def toJSON(self): return { 'id': self.id, # 'parent_id': self.parent_id, 'mediaType': self.mediaType, 'sha256': self.sha256, 'frame': self.frame, 'ext': self.ext, 'url': self.url(), 'created_at': self.created_at, } def toFullJSON(self): siblings = db.session.query(Media).filter( Media.sha256 == self.sha256).all() return { 'el': self.toJSON(), 'siblings': [el.toJSON() for el in siblings], } def filename(self): if self.mediaType == 'video_frame': return "{}_{:03d}{}".format(self.sha256, self.frame, self.ext) return "{}{}".format(self.sha256, self.ext) def filetree(self): return sha256_tree(self.sha256) def filepath(self): return join(app_cfg.DIR_MEDIA, self.filetree()) def fullpath(self): return join(self.filepath(), self.filename()) def archivepath(self): return join('media', self.filename()) def url(self): return join(app_cfg.URL_MEDIA, self.filetree(), self.filename())
class AdminLogEntry(Base): __tablename__ = "admin_log_entry" id = Column(INT, primary_key=True) type = Column(TEXT, nullable=False) user_id = Column(TEXT, ForeignKey("user.discord_id", ondelete="SET NULL")) message = Column(TEXT, nullable=False) created_at = Column(UtcDateTime(), nullable=False, index=True) data = Column(JSONB, nullable=False) user = relationship("User")
class LinkTrackerLink(Base): __tablename__ = "link_data" id = Column(INT, primary_key=True) url = Column(TEXT) times_linked = Column(INT) first_linked = Column(UtcDateTime()) last_linked = Column(UtcDateTime()) def __init__(self, url): self.id = None self.url = url self.times_linked = 0 now = utils.now() self.first_linked = now self.last_linked = now def increment(self): self.times_linked += 1 self.last_linked = utils.now()
class Roulette(Base): __tablename__ = "roulette" id = Column(INT, primary_key=True) user_id = Column(INT, index=True, nullable=False) created_at = Column(UtcDateTime(), nullable=False) points = Column(INT, nullable=False) def __init__(self, user_id, points): self.user_id = user_id self.created_at = utils.now() self.points = points
class Throwup(Base): __tablename__ = "throwups" id = Column("id", Integer, primary_key=True, index=True) user_id = Column("user_id", Integer, ForeignKey("users.id"), nullable=False) timestamp = Column("timestamp", UtcDateTime(), default=utcnow()) event_id = Column("event_id", Integer, ForeignKey("events.id"), nullable=False)
class Drink(Base): __tablename__ = "drinks" id = Column("id", Integer, primary_key=True, index=True) drink = Column("drink", String) timestamp = Column("timestamp", UtcDateTime(), default=utcnow()) user_id = Column("user_id", Integer, ForeignKey("users.id"), nullable=False) event_id = Column("event_id", Integer, ForeignKey("events.id"), nullable=False)
class StreamChunk(Base): __tablename__ = "stream_chunk" id = Column(INT, primary_key=True) stream_id = Column(INT, ForeignKey("stream.id", ondelete="CASCADE"), nullable=False) broadcast_id = Column(TEXT, nullable=False) video_url = Column(TEXT, nullable=True) video_preview_image_url = Column(TEXT, nullable=True) chunk_start = Column(UtcDateTime(), nullable=False) chunk_end = Column(UtcDateTime(), nullable=True) def __init__(self, stream, broadcast_id, created_at, **options): self.id = None self.stream_id = stream.id self.broadcast_id = broadcast_id self.video_url = None self.video_preview_image_url = None self.chunk_start = BaseTwitchAPI.parse_datetime(created_at) self.chunk_end = None self.stream = stream
class LongTimeout(Base): __tablename__ = "long_timeout" username = Column(TEXT, primary_key=True, nullable=False, unique=True) timeout_start = Column(UtcDateTime(), nullable=False) timeout_recent_end = Column(UtcDateTime()) timeout_end = Column(UtcDateTime(), nullable=False) timeout_author = Column(TEXT, nullable=False) def __init__( self, username, timeout_start, timeout_end, timeout_author, timeout_recent_end=( datetime.datetime.now() + datetime.timedelta(days=14)).strftime("%Y-%m-%d %H:%M:%S"), ): self.username = username self.timeout_start = timeout_start self.timeout_recent_end = timeout_recent_end self.timeout_end = timeout_end self.timeout_author = timeout_author
class Timer(Base): __tablename__ = "timer" id = Column(Integer, primary_key=True) start = Column(UtcDateTime(), nullable=False) stop = Column(UtcDateTime(), nullable=True) task_id = Column(Integer, ForeignKey("task.id"), nullable=False) task = relationship("Task", back_populates="timers") @property def running(self): """True if the timer is currently running.""" return self.stop is None @property def elapsed(self): """ A timedelta representing the duration of a completed timer, or The current elapsed time for a running timer. """ if self.running: return datetime.now( timezone.utc).replace(microsecond=0) - self.start else: return self.stop - self.start def as_dict(self): """ Return the object as a plain dictionary. """ return { "id": self.id, "task": self.task.name, "start": local_time(self.start), "stop": local_time(self.stop), "elapsed": self.elapsed, }
class UserDuelStats(Base): __tablename__ = "user_duel_stats" user_id = Column(INT, ForeignKey("user.id"), primary_key=True, autoincrement=False) duels_won = Column(INT, nullable=False, default=0) duels_total = Column(INT, nullable=False, default=0) points_won = Column(INT, nullable=False, default=0) points_lost = Column(INT, nullable=False, default=0) last_duel = Column(UtcDateTime(), nullable=True) current_streak = Column(INT, nullable=False, default=0) longest_winstreak = Column(INT, nullable=False, default=0) longest_losestreak = Column(INT, nullable=False, default=0) user = relationship("User", cascade="", uselist=False, backref=backref("duel_stats", uselist=False, cascade="", lazy="select")) def __init__(self, user_id): self.user_id = user_id self.duels_won = 0 self.duels_total = 0 self.points_won = 0 self.points_lost = 0 self.current_streak = 0 self.longest_winstreak = 0 self.longest_losestreak = 0 @hybrid_property def duels_lost(self): return self.duels_total - self.duels_won @hybrid_property def winrate(self): return self.duels_won * 100 / self.duels_total @hybrid_property def profit(self): return self.points_won - self.points_lost
class widget(TableBase): idwidget = Column(Integer, primary_key=True, autoincrement=True, nullable=False) iddatasource = Column(Integer, ForeignKey('datasource.iddatasource', onupdate='RESTRICT', ondelete='CASCADE'), nullable=False) insert_time = Column(UtcDateTime(timezone=True), default=utcnow(), nullable=False) uuid = Column(String(STRING_LENGTH), nullable=False) __table_args__ = (UniqueConstraint('iddatasource', 'uuid'), Index('idxwidget_idatasource_uuid', 'iddatasource', 'uuid'), Index('idxwidget_iddatasource', 'iddatasource'))
class Frog(db.Model): __tablename__ = 'frogs' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50), unique=False, nullable=False) food = db.Column(db.Integer, nullable=False, default=0) cleanliness = db.Column(db.Integer, nullable=False, default=0) money = db.Column(db.Integer, nullable=False, default=0) image_id = db.Column(db.Integer, db.ForeignKey('images.id'), nullable=False) owner_id = db.Column(db.Integer, db.ForeignKey('users.id')) last_request = db.Column(UtcDateTime(), default=utcnow(), nullable=False) image = db.relationship("Image", backref=db.backref("frog", uselist=False)) def __repr__(self): return f'<Frog {self.id}; {self.name}>'
class CollectionMedia(db.Model): """Table for storing references to various media""" __tablename__ = 'collection_media' collection_id = Column(Integer, ForeignKey('collection.id'), primary_key=True) media_id = Column(Integer, ForeignKey('media.id'), primary_key=True) username = Column(String(16, convert_unicode=True), nullable=False) created_at = Column(UtcDateTime(), default=utcnow()) media = relationship("Media") def toJSON(self): return { 'collection_id': self.collection_id, 'media_id': self.media_id, 'username': self.username, 'created_at': self.created_at, }
class Timeout(Base): __tablename__ = "timeouts" id = Column(INT, primary_key=True) active = Column(BOOLEAN, default=True, nullable=False) user_id = Column( TEXT, ForeignKey("user.discord_id", ondelete="CASCADE"), nullable=False ) user = relationship("User", foreign_keys=[user_id]) issued_by_id = Column( TEXT, ForeignKey("user.discord_id", ondelete="SET NULL"), nullable=True ) issued_by = relationship("User", foreign_keys=[issued_by_id]) unbanned_by_id = Column( TEXT, ForeignKey("user.discord_id", ondelete="SET NULL"), nullable=True ) unbanned_by = relationship("User", foreign_keys=[unbanned_by_id]) ban_reason = Column(TEXT, nullable=True) unban_reason = Column(TEXT, nullable=True) until = Column(UtcDateTime(), nullable=True, server_default="NULL") created_at = Column(UtcDateTime(), nullable=False, default=utils.now()) unbanned_at = Column(UtcDateTime(), nullable=True, default=None) @property def time_left(self): return int( (self.until - utils.now()).total_seconds() if self.until > utils.now() and self.active else 0 ) if self.until else None def check_lengths(self, _date): if not self.until: return False if not self._date: return True return self.until < _date if _date else True def unban(self, db_session, unbanned_by_id, unban_reason): self.active = False self.unbanned_by_id = (unbanned_by_id,) self.unban_reason = unban_reason self.unbanned_at = utils.now() return self @staticmethod def _active_timeouts(db_session): return db_session.query(Timeout).filter_by(active=True).all() @staticmethod def _create(db_session, user_id, issued_by_id, until, ban_reason): timeout = Timeout( active=True, user_id=user_id, issued_by_id=issued_by_id, until=until, created_at=utils.now(), ban_reason=ban_reason, ) db_session.add(timeout) return timeout @staticmethod def _is_timedout(db_session, user_id): return ( db_session.query(Timeout) .filter_by(user_id=str(user_id)) .filter_by(active=True) .one_or_none() ) @staticmethod def _by_user_id(db_session, user_id): return db_session.query(Timeout).filter_by(user_id=user_id).all() @staticmethod def _by_id(db_session, _id): return db_session.query(Timeout).filter_by(id=_id).one_or_none()