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()
