def __getitem__(self, guild_id: int):
typecheck(guild_id, int, 'guild_id')
if guild_id not in self._prefixes:
prefix = db.config.get_prefix(guild_id)
self._prefixes[guild_id] = (self.default
if prefix is None else prefix)
return self._prefixes[guild_id]
def _get_most_recent_update(guild_id: int, channel_id: int,
cur: MySQLCursor) -> Optional[dt.datetime]:
typecheck(guild_id, int, 'guild_id')
cur.execute(f'''
SELECT MAX(timestamp) FROM g{guild_id}_messages as msgs
INNER JOIN channels ON (channels.key_id = msgs.channel)
WHERE channels.id = %s
GROUP BY channel;
''', (channel_id,))
timestamp = cur.fetchone()
return timestamp[0] if timestamp else None
def generate_tags(guild_id: int, cur: MySQLCursor = None):
typecheck(guild_id, int, 'guild_id')
logger.info(f'Generating part of speech tags for server {guild_id}')
cur.execute(f'''
SELECT users.id, channels.id, content FROM g{guild_id}_messages AS msgs
INNER JOIN users ON (msgs.user = users.key_id)
INNER JOIN channels ON (msgs.channel = channels.key_id)
WHERE content <> ''
''')
for user_id, channel_id, content in cur:
for tag, word in _get_tags(content):
_insert_word(guild_id, user_id, channel_id, tag, word)
def init_guild(guild: Guild, cur: MySQLCursor = None):
guild_id = guild.id
typecheck(guild_id, int, 'guild_id')
cur.execute(f'''
CREATE TABLE IF NOT EXISTS g{guild_id}_messages (
id BIGINT UNSIGNED NOT NULL,
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
content TEXT NOT NULL,
images TEXT,
PRIMARY KEY (id),
INDEX (user),
INDEX (channel)
);
CREATE TABLE IF NOT EXISTS g{guild_id}_markov (
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
base TEXT,
potentials MEDIUMTEXT,
INDEX (user),
INDEX (channel),
INDEX (base(32))
);
CREATE TABLE IF NOT EXISTS g{guild_id}_pos_tags (
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
tag VARCHAR(8) NOT NULL,
word TEXT NOT NULL,
use_count MEDIUMINT UNSIGNED DEFAULT 0,
INDEX (user),
INDEX (channel),
INDEX (tag(3)),
UNIQUE KEY unique_entry (user, channel, tag, word(32))
);
CREATE TABLE IF NOT EXISTS g{guild_id}_pins (
original BIGINT UNSIGNED NOT NULL,
pin BIGINT UNSIGNED NOT NULL
);
''', multi=True)
db_config.add_guild(guild_id)
for channel in guild.text_channels:
add_channel(channel)
def random_message(guild_id: int,
users: Optional[List[int]] = None,
channel: Optional[int] = None,
word_limit: Optional[LimitTuple] = None,
count: int = 1,
content: bool = False,
images: bool = False,
cur: MySQLCursor = None):
typecheck(guild_id, int, 'guild_id')
if isinstance(word_limit, Sequence) and not any(word_limit):
word_limit = None
# Case insensitivity was screwing with the word limit
# regex pattern (\S -> \s)
where_stmt = _create_where_statement(
user=len(users) if users else 0,
channel=1 if channel else 0,
regex=bool(word_limit),
content=content,
images=images,
case_sensitive=True
)
word_limit_regex = None
if word_limit:
min_ = word_limit[0]-1 if word_limit[0] else ''
max_ = word_limit[1]-1 if word_limit[1] else ''
word_limit_regex = (
rf'^(?:\S+ +){{{min_},{max_}}}\S+$')
# https://stackoverflow.com/a/41581041
script = f'''
SELECT msgs.id as msg_id, channels.id as channel_id,
users.name as user_name, timestamp, content, images
FROM g{guild_id}_messages AS msgs
INNER JOIN channels ON (msgs.channel = channels.key_id)
INNER JOIN users ON (msgs.user = users.key_id)
WHERE msgs.id IN (
SELECT id FROM (
SELECT id FROM g{guild_id}_messages
{where_stmt}
ORDER BY RAND() LIMIT %s
) t
)
'''
cur.execute(script, flat_pruned_list(users, channel, word_limit_regex,
count))
return cur
def get_pin_msg_id(guild_id: int,
original_msg_id: int,
cur: MySQLCursor = None) -> Optional[int]:
"""
Get the id of the associated pin message Hawkbot sent in a guild's
designated pins channel
:param guild_id: guild id
:param original_msg_id: the id of the original message that was pinned
:return: the id of the pin message Hawkbot sent linking to the original, or
None if the original message was not found
"""
typecheck(guild_id, int, 'guild_id')
cur.execute(
f'''
SELECT pin FROM g{guild_id}_pins WHERE original = %s
''', (original_msg_id, ))
pin_msg_id = cur.fetchone()
return pin_msg_id[0] if pin_msg_id else None
def pin_message(guild_id: int,
original_msg_id: int,
pin_msg_id: int,
cur: MySQLCursor = None):
"""
Add a mapping between an original message and Hawkbot's pin message sent
in a guild's designated pins channel
:param guild_id: guild id
:param original_msg_id: the id of the original message that was pinned
:param pin_msg_id: the id of the pin message Hawkbot sent linking to the
original
"""
typecheck(guild_id, int, 'guild_id')
cur.execute(
f'''
INSERT INTO g{guild_id}_pins (original, pin) VALUES (%s,%s)
''', (original_msg_id, pin_msg_id))
cnx.commit()
def get_random_words_by_tag(guild_id: int,
tag: str,
users: Optional[List[int]] = None,
channel: Optional[int] = None,
count: int = 1,
cur: MySQLCursor = None) -> List[str]:
typecheck(guild_id, int, 'guild_id')
where_stmt = _create_where_statement(
len(users) if users else 0, 1 if channel else 0, True)
cur.execute(
f'''
SELECT word FROM g{guild_id}_pos_tags
{where_stmt}
ORDER BY RAND() LIMIT %s
''', flat_pruned_list(users, channel, tag, count))
words = [w[0] for w in cur]
return words
def _insert_message(msg: discord.Message, cur: MySQLCursor = None):
guild_id = msg.guild.id
typecheck(guild_id, int, 'guild_id')
images = _get_images_urls(msg)
if images:
images = ';'.join(images)
add_user(msg.author)
add_channel(msg.channel)
cur.execute(f'''
INSERT INTO g{guild_id}_messages (
id, user, channel, timestamp, content, images
) VALUES (
%s,
(SELECT key_id FROM users WHERE id=%s),
(SELECT key_id FROM channels WHERE id=%s),
%s,%s,%s
)
''', (msg.id, msg.author.id, msg.channel.id, msg.created_at,
msg.content, images))
cnx.commit()
def unpin_message(guild_id: int,
original_msg_id: int,
cur: MySQLCursor = None) -> Optional[int]:
"""
Remove a mapping between an original message and Hawkbot's pin message sent
in a guild's designated pins channel
:param guild_id: guild id
:param original_msg_id: the id of the original message that was pinned
:return: the id of the pin message Hawkbot sent linking to the original, or
None if the original message was not found
"""
typecheck(guild_id, int, 'guild_id')
pin_msg_id = get_pin_msg_id(guild_id, original_msg_id)
if pin_msg_id:
cur.execute(
f'''
DELETE FROM g{guild_id}_pins WHERE original = %s
''', (original_msg_id, ))
cnx.commit()
return pin_msg_id
return None
def message_stats(guild_id: int,
pattern: str,
users: Optional[List[int]] = None,
channels: Optional[List[int]] = None,
case_sensitive: bool = False,
plot: bool = False,
cur: MySQLCursor = None):
typecheck(guild_id, int, 'guild_id')
where_stmt = _create_where_statement(
user=len(users) if users else 0,
channel=len(channels) if channels else 0,
regex=True,
case_sensitive=case_sensitive
)
if not plot:
cur.execute(f'''
SELECT users.name, COUNT(*)
FROM g{guild_id}_messages AS msgs
INNER JOIN channels ON (msgs.channel = channels.key_id)
INNER JOIN users ON (msgs.user = users.key_id)
{where_stmt}
GROUP BY user
ORDER BY users.name
''', flat_pruned_list(users, channels, pattern))
return cur
else:
cur.execute(f'''
SELECT
users.id, users.name, DATE_FORMAT(timestamp, '%Y-%m-%d'),
COUNT(*)
FROM g{guild_id}_messages AS msgs
INNER JOIN channels ON (msgs.channel = channels.key_id)
INNER JOIN users ON (msgs.user = users.key_id)
{where_stmt}
GROUP BY user, DATE_FORMAT(timestamp, '%Y-%m-%d')
ORDER BY timestamp
''', flat_pruned_list(users, channels, pattern))
return cur
def __setitem__(self, guild_id: int, prefix: Optional[str]):
typecheck(guild_id, int, 'guild_id')
db.config.set_prefix(guild_id, prefix)
self._prefixes[guild_id] = self.default if prefix is None else prefix
