class User(Base): __tablename__ = 'users' id = Column(INT(unsigned=True), primary_key=True, nullable=False) user = Column(BIGINT(unsigned=True), nullable=False, unique=True) name = Column(String(37), nullable=False ) # sized off 32 char username + # + 4 char discriminator language = Column(String(2), default='EN', nullable=False) timezone = Column(String(32)) allowed_dm = Column(Boolean, default=True, nullable=False) patreon = Column(Boolean, nullable=False, default=False) dm_channel = Column(INT(unsigned=True), ForeignKey('channels.id', ondelete='SET NULL'), nullable=False) channel = relationship(Channel) def __repr__(self): return self.name or str(self.user) def __str__(self): return self.name or str(self.user) @classmethod def from_discord(cls, finding_user): return session.query(cls).filter(cls.user == finding_user.id).first() async def update_details(self, new_details): self.name = '{}#{}'.format(new_details.name, new_details.discriminator) if self.dm_channel is None: self.dm_channel = (await new_details.create_dm()).id
class Role(Base): __tablename__ = 'roles' id = Column(INT(unsigned=True), primary_key=True) name = Column(String(100)) role = Column(BIGINT(unsigned=True), unique=True, nullable=False) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id), nullable=False)
class Channel(Base): __tablename__ = 'channels' id = Column(INT(unsigned=True), primary_key=True) channel = Column(BIGINT(unsigned=True), unique=True) name = Column(String(100)) nudge = Column(SMALLINT, nullable=False, default=0) blacklisted = Column(Boolean, nullable=False, default=False) webhook_id = Column(BIGINT(unsigned=True), unique=True) webhook_token = Column(Text) paused = Column(Boolean, nullable=False, default=False) paused_until = Column(TIMESTAMP) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE')) guild = relationship(Guild, backref='channels') def __repr__(self): return '<#{}>'.format(self.channel) def __str__(self): return '<#{}>'.format(self.channel) @classmethod def get_or_create(cls, finding_channel) -> ('Channel', bool): c = session.query(cls).filter( cls.channel == finding_channel.id).first() new = False if c is None: g = session.query(Guild).filter( Guild.guild == finding_channel.guild.id).first() gid = None if g is None else g.id c = Channel(channel=finding_channel.id, name=finding_channel.name, guild_id=gid) session.add(c) new = True else: c.name = finding_channel.name session.flush() return c, new async def attach_webhook(self, channel): if (self.webhook_token or self.webhook_id) is None: hook = await channel.create_webhook(name='Reminders') self.webhook_token = hook.token self.webhook_id = hook.id
class Message(Base): __tablename__ = 'messages' id = Column(INT(unsigned=True), primary_key=True) content = Column(String(2048), nullable=False, default='') embed_id = Column(INT(unsigned=True), ForeignKey(Embed.id)) embed = relationship(Embed)
class Todo(Base): __tablename__ = 'todos' id = Column(INT(unsigned=True), primary_key=True) user_id = Column(INT(unsigned=True), ForeignKey(User.id)) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id)) value = Column(String(2000), nullable=False)
class Todo(Base): __tablename__ = 'todos' id = Column(INT(unsigned=True), primary_key=True) user_id = Column(INT(unsigned=True), ForeignKey(User.id)) user = relationship(User, backref='todo_list') guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id)) guild = relationship(Guild, backref='todo_list') value = Column(String(2000), nullable=False)
class EmbedField(db.Model): __tablename__ = 'embed_fields' id = db.Column(INT(unsigned=True), primary_key=True) title = db.Column(db.String(256), nullable=False) value = db.Column(db.String(1024), nullable=False) inline = db.Column(db.Boolean, nullable=False) reminder_id = db.Column(INT(unsigned=True), db.ForeignKey('reminders.id', ondelete='CASCADE'), nullable=False)
class Role(db.Model): __tablename__ = 'roles' id = db.Column(INT(unsigned=True), primary_key=True) role = db.Column(BIGINT(unsigned=True), unique=True, nullable=False) guild_id = db.Column(INT(unsigned=True), db.ForeignKey(Guild.id), nullable=False) name = db.Column(db.String(100)) def display_name(self): return self.name or str(self.id)
class Todo(Base): __tablename__ = 'todos' id = Column(INT(unsigned=True), primary_key=True) user_id = Column(INT(unsigned=True), ForeignKey(User.id, ondelete='CASCADE')) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE')) channel_id = Column(INT(unsigned=True), ForeignKey(Channel.id, ondelete='SET NULL')) value = Column(String(2000), nullable=False)
class CommandAlias(Base): __tablename__ = 'command_aliases' id = Column(INT(unsigned=True), primary_key=True) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE'), nullable=False) guild = relationship(Guild, backref='aliases') name = Column(String(12), nullable=False) command = Column(String(2048), nullable=False) UniqueConstraint('guild_id', 'name')
class Todo(db.Model): __tablename__ = 'todos' id = db.Column(INT(unsigned=True), primary_key=True) user_id = db.Column(INT(unsigned=True), db.ForeignKey(User.id, ondelete='CASCADE')) guild_id = db.Column(INT(unsigned=True), db.ForeignKey(Guild.id, ondelete='CASCADE')) channel_id = db.Column(INT(unsigned=True), db.ForeignKey(Channel.id, ondelete='SET NULL')) channel = db.relationship(Channel, backref='todo_list') value = db.Column(db.String(2000), nullable=False)
class Reminder(Base): __tablename__ = 'reminders' id = Column(INT(unsigned=True), primary_key=True) uid = Column(String(64), default=lambda: Reminder.create_uid(), unique=True) name = Column(String(24), default='Reminder') message_id = Column(INT(unsigned=True), ForeignKey(Message.id), nullable=False) message = relationship(Message) channel_id = Column(INT(unsigned=True), ForeignKey(Channel.id), nullable=True) time = Column(INT(unsigned=True)) enabled = Column(Boolean, nullable=False, default=True) avatar = Column( String(512), default= 'https://raw.githubusercontent.com/reminder-bot/logos/master/Remind_Me_Bot_Logo_PPic.jpg', nullable=False) username = Column(String(32), default='Reminder', nullable=False) method = Column(String(9)) interval = Column(INT(unsigned=True)) @staticmethod def create_uid() -> str: full: str = '' while len(full) < 64: full += secrets.choice(ALL_CHARACTERS) return full def message_content(self): if len(self.message.content) > 0: return self.message.content elif self.message.embed is not None: return self.message.embed.description else: return ''
class Guild(db.Model): __tablename__ = 'guilds' id = db.Column(INT(unsigned=True), primary_key=True) guild = db.Column(BIGINT(unsigned=True), unique=True) name = db.Column(db.String(100)) prefix = db.Column(db.String(5), default="$", nullable=False) channels = db.relationship('Channel', backref='guild', lazy='dynamic', foreign_keys='[Channel.guild_id]') roles = db.relationship('Role', backref='guild', lazy='dynamic') users = db.relationship('User', secondary=guild_users, primaryjoin=(guild_users.c.guild == id), secondaryjoin='(guild_users.c.user == User.id)', backref=db.backref('guilds', lazy='dynamic'), lazy='dynamic') command_restrictions = db.relationship( 'CommandRestriction', secondary= 'join(Role, CommandRestriction, CommandRestriction.role_id == Role.id)', primaryjoin='Role.guild_id == Guild.id', secondaryjoin='Role.id == CommandRestriction.role_id', backref='guild', lazy='dynamic', viewonly=False) todo_list = db.relationship('Todo', backref='guild', lazy='dynamic')
class Channel(Base): __tablename__ = 'channels' id = Column(INT(unsigned=True), primary_key=True) channel = Column(BIGINT(unsigned=True), unique=True) name = Column(String(100)) nudge = Column(SMALLINT, nullable=False, default=0) blacklisted = Column(Boolean, nullable=False, default=False) webhook_id = Column(BIGINT(unsigned=True), unique=True) webhook_token = Column(Text) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE'))
class Embed(Base): __tablename__ = 'embeds' id = Column(INT(unsigned=True), primary_key=True) title = Column(String(256), nullable=False, default='') description = Column(String(2048), nullable=False, default='') color = Column(MEDIUMINT(unsigned=True))
class CommandRestriction(Base): __tablename__ = 'command_restrictions' id = Column(Integer, primary_key=True) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE'), nullable=False) role_id = Column(INT(unsigned=True), ForeignKey(Role.id, ondelete='CASCADE'), nullable=False) role = relationship(Role) command = Column(ENUM('todos', 'natural', 'remind', 'interval', 'timer', 'del', 'look', 'alias'), nullable=False) UniqueConstraint('role_id', 'command')
class Favorites(db.Model): __tablename__ = 'favorites' user_id = db.Column('user_id', db.BigInteger, primary_key=True) sound_id = db.Column('sound_id', INT(unsigned=True), db.ForeignKey('sounds.id', ondelete='cascade'), primary_key=True)
class User(db.Model): __tablename__ = 'users' id = db.Column(INT(unsigned=True), primary_key=True) user = db.Column(BIGINT(unsigned=True), unique=True) patreon = db.Column(db.Boolean, nullable=False, default=False) dm_channel = db.Column(INT(unsigned=True), db.ForeignKey('channels.id', ondelete='SET NULL'), nullable=False) channel = db.relationship('Channel') language = db.Column(db.String(2), nullable=False, default='EN') name = db.Column(db.String(37)) def permitted_guilds(self): joins = db.session.query(guild_users) \ .filter(guild_users.c.user == self.id) \ .filter(guild_users.c.can_access) \ .all() return Guild.query.filter(Guild.id.in_([row.guild for row in joins])).all() def set_permitted_guilds(self, guilds): stmt = guild_users.update().where( guild_users.c.user == self.id).values(can_access=False) db.engine.execute(stmt) current_guilds = set(x.guild for x in db.session.query(guild_users).filter( guild_users.c.user == self.id)) for guild in guilds: if guild.id in current_guilds: stmt = guild_users.update()\ .where((guild_users.c.user == self.id) & (guild_users.c.guild == guild.id))\ .values(can_access=True) db.session.execute(stmt) elif guild.id is not None: db.session.execute(guild_users.insert().values( guild=guild.id, user=self.id, can_access=True)) db.session.commit()
class Channel(db.Model): __tablename__ = 'channels' id = db.Column(INT(unsigned=True), primary_key=True) channel = db.Column(BIGINT(unsigned=True), unique=True) name = db.Column(db.String(100)) blacklisted = db.Column(db.Boolean, nullable=False, default=False) webhook_id = db.Column(BIGINT(unsigned=True), unique=True) webhook_token = db.Column(db.Text) paused = db.Column(db.Boolean, nullable=False, default=False) paused_until = db.Column(TIMESTAMP) guild_id = db.Column(INT(unsigned=True), db.ForeignKey('guilds.id', ondelete='CASCADE'), nullable=False) def __repr__(self): return '{}.{}'.format(self.name, self.channel) def __hash__(self): return self.id.__hash__() def update_webhook(self, api_get, api_post, client_id): # get existing webhooks webhooks = api_get('channels/{}/webhooks'.format(self.channel)).json() if isinstance(webhooks, list): existing = [x for x in webhooks if x['user']['id'] == client_id] if len(existing) == 0: # get new webhook req = api_post('channels/{}/webhooks'.format(self.channel), { 'name': 'Reminders' }).json() self.webhook_id = req['id'] self.webhook_token = req['token'] else: self.webhook_id = existing[0]['id'] self.webhook_token = existing[0]['token']
class Guild(Base): __tablename__ = 'guilds' id = Column(INT(unsigned=True), primary_key=True) guild = Column(BIGINT(unsigned=True), unique=True) prefix = Column(String(5), default='$', nullable=False) timezone = Column(String(32), default='UTC', nullable=False) name = Column(String(100))
class Timer(Base): __tablename__ = 'timers' id = Column(INT(unsigned=True), primary_key=True) start_time = Column(TIMESTAMP, default=datetime.now, server_default='CURRENT_TIMESTAMP()', nullable=False) name = Column(String(32), nullable=False) owner = Column(BIGINT(unsigned=True), nullable=False)
class Event(db.Model): __tablename__ = 'events' id = db.Column(INT(unsigned=True), primary_key=True) time = db.Column(TIMESTAMP, default=datetime.now, server_default='CURRENT_TIMESTAMP()', nullable=False) event_name = db.Column(ENUM('edit', 'enable', 'disable', 'delete'), nullable=False) bulk_count = db.Column(INT(unsigned=True)) guild_id = db.Column(INT(unsigned=True), db.ForeignKey(Guild.id, ondelete='CASCADE'), nullable=False) guild = db.relationship(Guild) user_id = db.Column(INT(unsigned=True), db.ForeignKey(User.id, ondelete='SET NULL')) user = db.relationship(User) reminder_id = db.Column(INT(unsigned=True), db.ForeignKey(Reminder.id, ondelete='SET NULL')) reminder = db.relationship(Reminder) @classmethod def new_edit_event(cls, reminder, user_id): if reminder.channel.guild_id is not None: q = cls.query \ .filter(cls.guild_id == reminder.channel.guild_id) \ .filter(cls.time > (datetime.now() - timedelta(hours=2))) \ .first() if q is None or q.user_id != user_id: event = cls(event_name='edit', guild_id=reminder.channel.guild_id, user_id=user_id, reminder=reminder) db.session.add(event)
class Role(Base): __tablename__ = 'roles' id = Column(INT(unsigned=True), primary_key=True) name = Column(String(100)) role = Column(BIGINT(unsigned=True), unique=True, nullable=False) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE'), nullable=False) def __eq__(self, v): if isinstance(v, int): return self.role == v elif isinstance(v, Role): return self.id == v.id else: return False def __str__(self): return '<@&{}>'.format(self.role)
class Reminder(Base): __tablename__ = 'reminders' id = Column(INT(unsigned=True), primary_key=True) uid = Column(String(64), default=lambda: Reminder.create_uid(), unique=True) name = Column(String(24), default='Reminder') message_id = Column(INT(unsigned=True), ForeignKey(Message.id, ondelete='RESTRICT'), nullable=False) message = relationship(Message) channel_id = Column(INT(unsigned=True), ForeignKey(Channel.id, ondelete='CASCADE'), nullable=True) time = Column(INT(unsigned=True)) enabled = Column(Boolean, nullable=False, default=True) avatar = Column( String(512), default= 'https://raw.githubusercontent.com/reminder-bot/logos/master/Remind_Me_Bot_Logo_PPic.jpg', nullable=False) username = Column(String(32), default='Reminder', nullable=False) interval = Column(INT(unsigned=True)) method = Column(ENUM('remind', 'natural', 'dashboard', 'todo')) set_by = Column(INT(unsigned=True), ForeignKey(User.id, ondelete='SET NULL'), nullable=True) set_at = Column(TIMESTAMP, nullable=True, default=datetime.now, server_default='CURRENT_TIMESTAMP()') @staticmethod def create_uid() -> str: full: str = '' while len(full) < 64: full += secrets.choice(ALL_CHARACTERS) return full def message_content(self): if len(self.message.content) > 0: return self.message.content elif self.message.embed is not None: return self.message.embed.description else: return ''
class Reminder(Base): __tablename__ = 'reminders' id = Column(INT(unsigned=True), primary_key=True) uid = Column(String(64), unique=True) message_id = Column(INT(unsigned=True), nullable=False) channel_id = Column(INT(unsigned=True), ForeignKey('channels.id'), nullable=False) time = Column(BIGINT(unsigned=True)) enabled = Column(Boolean, nullable=False, default=True) avatar = Column( String(512), default= 'https://raw.githubusercontent.com/reminder-bot/logos/master/Remind_Me_Bot_Logo_PPic.jpg', nullable=False) username = Column(String(32), default='Reminder', nullable=False) method = Column(String(9)) interval = Column(INT(unsigned=True))
class User(Base): __tablename__ = 'users' id = Column(INT(unsigned=True), primary_key=True, nullable=False) user = Column(BIGINT(unsigned=True), nullable=False, unique=True) name = Column(String(37), nullable=False ) # sized off 32 char username + # + 4 char discriminator language = Column(String(2), default='EN', nullable=False) timezone = Column(String(32)) allowed_dm = Column(Boolean, default=True, nullable=False) patreon = Column(Boolean, nullable=False, default=False) dm_channel = Column(BIGINT(unsigned=True), nullable=False)
class Event(Base): __tablename__ = 'events' id = Column(INT(unsigned=True), primary_key=True) time = Column(TIMESTAMP, default=datetime.now, server_default='CURRENT_TIMESTAMP()', nullable=False) event_name = Column(ENUM('edit', 'enable', 'disable', 'delete'), nullable=False) bulk_count = Column(INT(unsigned=True)) guild_id = Column(INT(unsigned=True), ForeignKey(Guild.id, ondelete='CASCADE'), nullable=False) guild = relationship(Guild) user_id = Column(INT(unsigned=True), ForeignKey(User.id, ondelete='SET NULL')) user = relationship(User) reminder_id = Column(INT(unsigned=True), ForeignKey(Reminder.id, ondelete='SET NULL'))
class Guild(Base): __tablename__ = 'guilds' id = Column(INT(unsigned=True), primary_key=True) guild = Column(BIGINT(unsigned=True), unique=True) prefix = Column(String(5), default='$', nullable=False) timezone = Column(String(32), default='UTC', nullable=False) name = Column(String(100)) users = relationship('User', secondary=guild_users, primaryjoin=(guild_users.c.guild == id), secondaryjoin='(guild_users.c.user == User.id)', backref=backref('guilds', lazy='dynamic'), lazy='dynamic') # populated later in file command_restrictions = None
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker, scoped_session, relationship, backref from sqlalchemy.dialects.mysql import BIGINT, MEDIUMINT, SMALLINT, INTEGER as INT import configparser import time import typing import secrets from consts import ALL_CHARACTERS Base = declarative_base() guild_users = Table( 'guild_users', Base.metadata, Column('guild', INT(unsigned=True), ForeignKey('guilds.id')), Column('user', INT(unsigned=True), ForeignKey('users.id')), ) class Guild(Base): __tablename__ = 'guilds' id = Column(INT(unsigned=True), primary_key=True) guild = Column(BIGINT(unsigned=True), unique=True) prefix = Column(String(5), default='$', nullable=False) timezone = Column(String(32), default='UTC', nullable=False) name = Column(String(100))
from sqlalchemy.orm import sessionmaker, scoped_session, relationship, backref from sqlalchemy.dialects.mysql import BIGINT, MEDIUMINT, SMALLINT, INTEGER as INT, TIMESTAMP, ENUM import configparser from datetime import datetime import typing import secrets from consts import ALL_CHARACTERS Base = declarative_base() guild_users = Table( 'guild_users', Base.metadata, Column('guild', INT(unsigned=True), ForeignKey('guilds.id', ondelete='CASCADE'), nullable=False), Column('user', INT(unsigned=True), ForeignKey('users.id', ondelete='CASCADE'), nullable=False), Column('can_access', Boolean, nullable=False, default=False), UniqueConstraint('guild', 'user'), ) class Guild(Base): __tablename__ = 'guilds' id = Column(INT(unsigned=True), primary_key=True)