def update_emotes(self):
log.debug('Updating BTTV Emotes...')
global_emotes = self.api.get_global_emotes()
channel_emotes = self.api.get_channel_emotes(
StreamHelper.get_streamer())
self.global_emotes = [emote['code'] for emote in global_emotes]
self.channel_emotes = [emote['code'] for emote in channel_emotes]
# Store channel emotes in redis
streamer = StreamHelper.get_streamer()
key = '{streamer}:emotes:bttv_channel_emotes'.format(streamer=streamer)
with RedisManager.pipeline_context() as pipeline:
pipeline.delete(key)
for emote in channel_emotes:
pipeline.hset(key, emote['code'], emote['emote_hash'])
self.all_emotes = []
with RedisManager.pipeline_context() as pipeline:
for emote in global_emotes + channel_emotes:
# Store all possible emotes, with their regex in an easily
# accessible list.
self.all_emotes.append(
self.build_emote(emote['code'], emote['emote_hash']))
# Make sure all available emotes are available in redis
pipeline.hset('global:emotes:bttv', emote['code'],
emote['emote_hash'])
def update_emotes(self):
log.debug('Updating BTTV Emotes...')
global_emotes = self.bttv_api.get_global_emotes()
channel_emotes = self.bttv_api.get_channel_emotes(StreamHelper.get_streamer())
self.global_emotes = [emote['code'] for emote in global_emotes]
self.channel_emotes = [emote['code'] for emote in channel_emotes]
# Store channel emotes in redis
streamer = StreamHelper.get_streamer()
key = '{streamer}:emotes:bttv_channel_emotes'.format(streamer=streamer)
with RedisManager.pipeline_context() as pipeline:
pipeline.delete(key)
for emote in channel_emotes:
pipeline.hset(key, emote['code'], emote['emote_hash'])
self.all_emotes = []
with RedisManager.pipeline_context() as pipeline:
for emote in global_emotes + channel_emotes:
# Store all possible emotes, with their regex in an easily
# accessible list.
self.all_emotes.append(self.build_emote(emote['code'], emote['emote_hash']))
# Make sure all available emotes are available in redis
pipeline.hset('global:emotes:bttv', emote['code'], emote['emote_hash'])
def handle_emotes(emote_counts):
# passed dict maps emote code (e.g. "Kappa") to an EmoteInstanceCount instance
streamer = StreamHelper.get_streamer()
redis_key = "{streamer}:emotes:count".format(streamer=streamer)
with RedisManager.pipeline_context() as redis:
for emote_code, instance_counts in emote_counts.items():
redis.zincrby(redis_key, instance_counts.count, emote_code)
def add_playsound(bot, source, message, event, args):
if message is not None and len(message.split(' ')) == 3:
playsoundCategory = message.split(' ')[0]
playsoundName = message.split(' ')[1]
playsoundURL = message.split(' ')[2]
_savedplaysounds = RedisManager.get().hgetall(
'playsounds:{}'.format(playsoundCategory))
if playsoundName in _savedplaysounds:
bot.say(
'Name of playsound already exists. Please choose a different name'
)
return False
elif playsoundURL in _savedplaysounds:
bot.say(
'URL of playsound already exists. Please choose a different link'
)
return False
with RedisManager.pipeline_context() as pipeline:
pipeline.hset('playsounds:{}'.format(playsoundCategory),
playsoundName, playsoundURL)
bot.say(
'Successfully added {} with the name {} to the playsound list'.
format(playsoundURL, playsoundName))
else:
bot.say('Invalid syntax. Usage: !add playsound category name url')
return False
def downgrade():
with RedisManager.pipeline_context() as redis:
redis.delete(
"{streamer}:current_quest_emote".format(streamer=streamer))
redis.delete("{streamer}:current_quest".format(streamer=streamer))
redis.delete(
"{streamer}:current_quest_progress".format(streamer=streamer))
redis.delete("{streamer}:quests:finished".format(streamer=streamer))
def upgrade():
with RedisManager.pipeline_context() as redis:
redis.delete(
'{streamer}:emotes:ffz_channel_emotes'.format(streamer=streamer))
redis.delete(
'{streamer}:emotes:bttv_channel_emotes'.format(streamer=streamer))
redis.delete('global:emotes:ffz_global')
redis.delete('global:emotes:bttv_global')
def redis_load(self):
""" Load data from redis using a newly created pipeline """
if self.redis_loaded:
return
with RedisManager.pipeline_context() as pipeline:
self.queue_up_redis_calls(pipeline)
data = pipeline.execute()
self.load_redis_data(data)
def redis_load(self):
""" Load data from redis using a newly created pipeline """
if self.redis_loaded:
return
with RedisManager.pipeline_context() as pipeline:
self.queue_up_redis_calls(pipeline)
data = pipeline.execute()
self.load_redis_data(data)
def upgrade():
bind = op.get_bind()
session = Session(bind=bind)
config_data = RedisManager.get().config_get('maxmemory')
max_memory = config_data['maxmemory']
print('redis max memory: {}'.format(max_memory))
RedisManager.get().config_set('maxmemory', str(int(max_memory) * 10))
with RedisManager.pipeline_context() as pipeline:
streamer = pb_config['main']['streamer']
num_lines_key = '{streamer}:users:num_lines'.format(streamer=streamer)
ignored_key = '{streamer}:users:ignored'.format(streamer=streamer)
last_active_key = '{streamer}:users:last_active'.format(
streamer=streamer)
last_seen_key = '{streamer}:users:last_seen'.format(streamer=streamer)
banned_key = '{streamer}:users:banned'.format(streamer=streamer)
username_raw_key = '{streamer}:users:username_raw'.format(
streamer=streamer)
pipeline.delete(num_lines_key, ignored_key, last_active_key,
last_seen_key, banned_key, username_raw_key)
for user in session.query(User):
if user.num_lines > 0:
pipeline.zadd(num_lines_key, user.username, user.num_lines)
if user.ignored:
pipeline.hset(ignored_key, user.username, 1)
if user.banned:
pipeline.hset(banned_key, user.username, 1)
if user.username != user.username_raw:
pipeline.hset(username_raw_key, user.username,
user.username_raw)
if user._last_seen:
pipeline.hset(last_seen_key, user.username,
user._last_seen.timestamp())
if user._last_active:
pipeline.hset(last_active_key, user.username,
user._last_active.timestamp())
RedisManager.get().config_set('maxmemory', int(max_memory))
### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('tb_user') as batch_op:
batch_op.drop_column('num_lines')
batch_op.drop_column('ignored')
batch_op.drop_column('last_active')
batch_op.drop_column('last_seen')
batch_op.drop_column('banned')
### end Alembic commands ###
session.commit()
def update_chatters_stage2(self, chatters):
points = 1 if self.bot.is_online else 0
log.debug('Updating {0} chatters'.format(len(chatters)))
self.bot.stream_manager.update_chatters(chatters, self.update_chatters_interval)
with RedisManager.pipeline_context() as pipeline:
with DBManager.create_session_scope() as db_session:
user_models = UserManager.get().bulk_load_user_models(chatters, db_session)
users = []
for username in chatters:
user_model = user_models.get(username, None)
user = UserManager.get().get_user(username, db_session=db_session, user_model=user_model, redis=pipeline)
users.append(user)
more_update_data = {}
if self.bot.is_online:
more_update_data['minutes_in_chat_online'] = self.update_chatters_interval
else:
more_update_data['minutes_in_chat_offline'] = self.update_chatters_interval
points_to_give_out = {}
dt_now = datetime.datetime.now().timestamp()
for user in users:
user._set_last_seen(dt_now)
num_points = points
if user.subscriber:
num_points *= 5
# TODO: Load user tags during the pipeline redis data fetch
if self.bot.streamer == 'forsenlol' and 'trumpsc_sub' in user.get_tags():
num_points *= 0.5
num_points = int(num_points)
if num_points not in points_to_give_out:
points_to_give_out[num_points] = []
points_to_give_out[num_points].append(user.username)
user.save(save_to_db=False)
for num_points, usernames in points_to_give_out.items():
payload = {
User.points: User.points + num_points,
}
if self.bot.is_online:
payload[User.minutes_in_chat_online] = User.minutes_in_chat_online + self.update_chatters_interval
else:
payload[User.minutes_in_chat_offline] = User.minutes_in_chat_offline + self.update_chatters_interval
db_session.query(User).filter(User.username.in_(usernames)).\
update(payload, synchronize_session=False)
pipeline.execute()
def update_channel_emotes(self):
_channel_emotes = self.api.get_channel_emotes(
StreamHelper.get_streamer())
self.channel_emotes = {}
for emote in _channel_emotes:
self.channel_emotes[emote['code']] = emote['emote_hash']
# Store channel emotes in redis
streamer = StreamHelper.get_streamer()
key = '{streamer}:emotes:bttv_channel_emotes'.format(streamer=streamer)
with RedisManager.pipeline_context() as pipeline:
for emote_code, emote_hash in self.channel_emotes.items():
pipeline.hset(key, emote_code, emote_hash)
def update_channel_emotes(self):
_channel_emotes = self.api.get_channel_emotes(StreamHelper.get_streamer())
self.channel_emotes = {}
for emote in _channel_emotes:
self.channel_emotes[emote['code']] = emote['emote_hash']
# Store channel emotes in redis
streamer = StreamHelper.get_streamer()
key = '{streamer}:emotes:bttv_channel_emotes'.format(streamer=streamer)
with RedisManager.pipeline_context() as pipeline:
pipeline.delete(key)
for emote_code, emote_hash in self.channel_emotes.items():
pipeline.hset(key, emote_code, emote_hash)
def upgrade():
bind = op.get_bind()
session = Session(bind=bind)
config_data = RedisManager.get().config_get('maxmemory')
max_memory = config_data['maxmemory']
print('redis max memory: {}'.format(max_memory))
RedisManager.get().config_set('maxmemory', str(int(max_memory) * 10))
with RedisManager.pipeline_context() as pipeline:
streamer = pb_config['main']['streamer']
num_lines_key = '{streamer}:users:num_lines'.format(streamer=streamer)
ignored_key = '{streamer}:users:ignored'.format(streamer=streamer)
last_active_key = '{streamer}:users:last_active'.format(streamer=streamer)
last_seen_key = '{streamer}:users:last_seen'.format(streamer=streamer)
banned_key = '{streamer}:users:banned'.format(streamer=streamer)
username_raw_key = '{streamer}:users:username_raw'.format(streamer=streamer)
pipeline.delete(num_lines_key, ignored_key, last_active_key, last_seen_key, banned_key, username_raw_key)
for user in session.query(User):
if user.num_lines > 0:
pipeline.zadd(num_lines_key, user.username, user.num_lines)
if user.ignored:
pipeline.hset(ignored_key, user.username, 1)
if user.banned:
pipeline.hset(banned_key, user.username, 1)
if user.username != user.username_raw:
pipeline.hset(username_raw_key, user.username, user.username_raw)
if user._last_seen:
pipeline.hset(last_seen_key, user.username, user._last_seen.timestamp())
if user._last_active:
pipeline.hset(last_active_key, user.username, user._last_active.timestamp())
RedisManager.get().config_set('maxmemory', int(max_memory))
### commands auto generated by Alembic - please adjust! ###
with op.batch_alter_table('tb_user') as batch_op:
batch_op.drop_column('num_lines')
batch_op.drop_column('ignored')
batch_op.drop_column('last_active')
batch_op.drop_column('last_seen')
batch_op.drop_column('banned')
### end Alembic commands ###
session.commit()
def command_masspoints(self, **options):
bot = options["bot"]
source = options["source"]
message = options["message"]
if not message:
return False
pointsArgument = message.split(" ")[0]
givePoints = 0
try:
givePoints = int(pointsArgument)
except ValueError:
bot.whisper(source.username_raw, "Error: You must give an integer")
return False
currentChatters = bot.twitch_tmi_api.get_chatters(bot.streamer)
numUsers = len(currentChatters)
if not currentChatters:
bot.say("Error fetching chatters")
return False
with RedisManager.pipeline_context() as pipeline:
with DBManager.create_session_scope() as db_session:
userModels = UserManager.get().bulk_load_user_models(
currentChatters, db_session)
for userName in currentChatters:
userModel = userModels.get(userName, None)
userInstance = UserManager.get().get_user(
userName,
db_session=db_session,
user_model=userModel,
redis=pipeline)
# Bot/idler check. Quicker than removing from list?
if userInstance.num_lines < 5:
numUsers -= 1
continue
if userInstance.subscriber:
userInstance.points += givePoints * self.settings[
"sub_points"]
else:
userInstance.points += givePoints
bot.say("{} just gave {} viewers {} points each! Enjoy FeelsGoodMan".
format(source.username_raw, numUsers, givePoints))
def update_emotes(self):
base_url = 'https://twitchemotes.com/api_cache/v2/{0}.json'
endpoints = [
'global',
'subscriber',
]
twitch_emotes = {}
twitch_subemotes = {}
for endpoint in endpoints:
log.debug('Refreshing {0} emotes...'.format(endpoint))
try:
data = requests.get(base_url.format(endpoint)).json()
except ValueError:
continue
if 'channels' in data:
for channel in data['channels']:
chan = data['channels'][channel]
# chan_id = chan['id']
emotes = chan['emotes']
emote_codes = []
pending_emotes = []
for emote in emotes:
emote_codes.append(emote['code'])
pending_emotes.append(emote)
prefix = os.path.commonprefix(emote_codes)
if len(prefix) > 1 and ''.join(
filter(lambda c: c.isalpha(), prefix)).islower():
for emote in pending_emotes:
twitch_emotes[emote['code']] = emote['image_id']
twitch_subemotes[emote['code']] = channel
else:
for code, emote_data in data['emotes'].items():
twitch_emotes[code] = emote_data['image_id']
with RedisManager.pipeline_context() as pipeline:
pipeline.delete('global:emotes:twitch_subemotes')
pipeline.hmset('global:emotes:twitch', twitch_emotes)
pipeline.hmset('global:emotes:twitch_subemotes', twitch_subemotes)
self.subemotes = twitch_subemotes
def upgrade():
bind = op.get_bind()
session = Session(bind=bind)
with RedisManager.pipeline_context() as pipeline:
streamer = pb_config['main']['streamer']
count_key = '{streamer}:emotes:count'.format(streamer=streamer)
epmrecord_key = '{streamer}:emotes:epmrecord'.format(streamer=streamer)
pipeline.delete(count_key, epmrecord_key)
for emote in session.query(Emote):
if emote.stats:
pipeline.zincrby(count_key, emote.code, emote.stats.count)
pipeline.zincrby(epmrecord_key, emote.code, emote.stats.tm_record)
op.drop_table('tb_emote_stats')
op.drop_table('tb_emote')
session.commit()
def upgrade():
bind = op.get_bind()
session = Session(bind=bind)
with RedisManager.pipeline_context() as pipeline:
streamer = pb_config['main']['streamer']
count_key = '{streamer}:emotes:count'.format(streamer=streamer)
epmrecord_key = '{streamer}:emotes:epmrecord'.format(streamer=streamer)
pipeline.delete(count_key, epmrecord_key)
for emote in session.query(Emote):
if emote.stats:
pipeline.zincrby(count_key, emote.code, emote.stats.count)
pipeline.zincrby(epmrecord_key, emote.code, emote.stats.tm_record)
op.drop_table('tb_emote_stats')
op.drop_table('tb_emote')
session.commit()
def update_emotes(self):
base_url = 'https://twitchemotes.com/api_cache/v2/{0}.json'
endpoints = [
'global',
'subscriber',
]
twitch_emotes = {}
twitch_subemotes = {}
for endpoint in endpoints:
log.debug('Refreshing {0} emotes...'.format(endpoint))
data = requests.get(base_url.format(endpoint)).json()
if 'channels' in data:
for channel in data['channels']:
chan = data['channels'][channel]
# chan_id = chan['id']
emotes = chan['emotes']
emote_codes = []
pending_emotes = []
for emote in emotes:
emote_codes.append(emote['code'])
pending_emotes.append(emote)
prefix = os.path.commonprefix(emote_codes)
if len(prefix) > 1 and ''.join(filter(lambda c: c.isalpha(), prefix)).islower():
for emote in pending_emotes:
twitch_emotes[emote['code']] = emote['image_id']
twitch_subemotes[emote['code']] = channel
else:
for code, emote_data in data['emotes'].items():
twitch_emotes[code] = emote_data['image_id']
with RedisManager.pipeline_context() as pipeline:
pipeline.delete('global:emotes:twitch_subemotes')
pipeline.hmset('global:emotes:twitch', twitch_emotes)
pipeline.hmset('global:emotes:twitch_subemotes', twitch_subemotes)
self.subemotes = twitch_subemotes
def update_chatters_stage2(self, chatters):
points = 1 if self.bot.is_online else 0
log.debug('Updating {0} chatters'.format(len(chatters)))
self.bot.stream_manager.update_chatters(chatters,
self.update_chatters_interval)
with RedisManager.pipeline_context() as pipeline:
with DBManager.create_session_scope() as db_session:
user_models = UserManager.get().bulk_load_user_models(
chatters, db_session)
users = []
for username in chatters:
user_model = user_models.get(username, None)
user = UserManager.get().get_user(username,
db_session=db_session,
user_model=user_model,
redis=pipeline)
users.append(user)
more_update_data = {}
if self.bot.is_online:
more_update_data[
'minutes_in_chat_online'] = self.update_chatters_interval
else:
more_update_data[
'minutes_in_chat_offline'] = self.update_chatters_interval
points_to_give_out = {}
dt_now = datetime.datetime.now().timestamp()
for user in users:
user._set_last_seen(dt_now)
num_points = points
if user.subscriber:
num_points *= 5
# TODO: Load user tags during the pipeline redis data fetch
if self.bot.streamer == 'forsenlol' and 'trumpsc_sub' in user.get_tags(
):
num_points *= 0.5
num_points = int(num_points)
if num_points not in points_to_give_out:
points_to_give_out[num_points] = []
points_to_give_out[num_points].append(user.username)
user.save(save_to_db=False)
for num_points, usernames in points_to_give_out.items():
payload = {
User.points: User.points + num_points,
}
if self.bot.is_online:
payload[
User.
minutes_in_chat_online] = User.minutes_in_chat_online + self.update_chatters_interval
else:
payload[
User.
minutes_in_chat_offline] = User.minutes_in_chat_offline + self.update_chatters_interval
db_session.query(User).filter(User.username.in_(usernames)).\
update(payload, synchronize_session=False)
pipeline.execute()
def up(cursor, bot):
redis = RedisManager.get()
# new: twitch_id (will be renamed to "id" after the migration below...)
cursor.execute('ALTER TABLE "user" ADD COLUMN twitch_id TEXT'
) # nullable for now. Will be NOT NULL later
cursor.execute('CREATE UNIQUE INDEX ON "user"(twitch_id)')
# username -> login
cursor.execute('ALTER TABLE "user" RENAME COLUMN username TO login')
cursor.execute("DROP INDEX user_username_idx") # UNIQUE
cursor.execute(
'ALTER TABLE "user" DROP CONSTRAINT user_username_key') # PRIMARY KEY
cursor.execute(
'CREATE INDEX ON "user"(login)') # create a new index, non-unique
# username_raw -> name
cursor.execute('ALTER TABLE "user" RENAME COLUMN username_raw TO name')
# level: add default
cursor.execute('ALTER TABLE "user" ALTER COLUMN level SET DEFAULT 100')
# points: add default
cursor.execute('ALTER TABLE "user" ALTER COLUMN points SET DEFAULT 0')
# subscriber: add default
cursor.execute(
'ALTER TABLE "user" ALTER COLUMN subscriber SET DEFAULT FALSE')
# new: moderator
cursor.execute(
'ALTER TABLE "user" ADD COLUMN moderator BOOLEAN NOT NULL DEFAULT FALSE'
)
# new: num_lines
cursor.execute(
'ALTER TABLE "user" ADD COLUMN num_lines BIGINT NOT NULL DEFAULT 0')
cursor.execute('CREATE INDEX ON "user"(num_lines)'
) # same reason as in 0002_create_index_on_user_points.py
def safe_to_int(input):
try:
return int(input)
except ValueError:
return None
# # threaded user id migration
# import time
# import random
# import threading
# import queue
# log.info("start user id migration")
# # migrate users to ID
# cursor.execute('SELECT COUNT(*) FROM "user"')
# users_count = cursor.fetchone()[0]
# q = queue.Queue(500)
# update_q = queue.Queue()
# def update_rows(all_user_data):
# # log.info("updating rows")
# for id, basics in all_user_data:
# if basics is not None:
# try:
# cursor.execute(
# 'UPDATE "user" SET twitch_id = %s, login = %s, name = %s WHERE id = %s' ,
# (basics.id, basics.login, basics.name, id),
# )
# except:
# log.exception("Error in update rows")
# log.info(f"XXX basics: {basics.login} - {basics.id}")
# raise
# class GetAndLockRows(threading.Thread):
# def __init__(self, group=None, target=None, name=None, args=(), kwargs=None, verbose=None):
# super(GetAndLockRows, self).__init__()
# self.target = target
# self.name = name
# def run(self):
# cursor.execute('DECLARE all_users CURSOR FOR SELECT id, login FROM "user" ORDER BY id FOR UPDATE')
# offset = 0
# while True:
# while not update_q.empty():
# update_rows(update_q.get())
# while q.full():
# while not update_q.empty():
# update_rows(update_q.get())
# log.info("helix api queue is full, waiting")
# time.sleep(random.random() * 0.5)
# cursor.execute("FETCH FORWARD 100 FROM all_users")
# rows = cursor.fetchall() # = [(id, login), (id, login), (id, login), ...]
# if len(rows) <= 0:
# break
# offset += 100
# log.info(f"{offset}/{users_count}")
# q.put(rows)
# while not update_q.empty():
# update_rows(update_q.get())
# cursor.execute("CLOSE all_users")
# log.info("Wait for q queue to fully empty")
# q.join()
# log.info("q queue is fully empty, process last users in update_q")
# while not update_q.empty():
# update_rows(update_q.get())
# log.info("done updating all rows")
# class ConsumeRowsAndGetHelixData(threading.Thread):
# def __init__(self, group=None, target=None, name=None, args=(), kwargs=None, verbose=None):
# super(ConsumeRowsAndGetHelixData, self).__init__()
# self.target = target
# self.name = name
# self.running = True
# def run(self):
# while self.running:
# while not q.empty():
# rows = q.get()
# usernames_to_fetch = [t[1] for t in rows]
# all_user_basics = retry_call(
# bot.twitch_helix_api.bulk_get_user_basics_by_login, fargs=[usernames_to_fetch], tries=3, delay=5
# )
# update_q.put(zip((t[0] for t in rows), all_user_basics))
# q.task_done()
# time.sleep(random.random() * 0.1)
# if users_count > 0:
# get_and_lock_rows = GetAndLockRows(name="get_and_lock_rows")
# get_and_lock_rows.start()
# consume_rows_and_get_helix_data = ConsumeRowsAndGetHelixData(name="consume_rows_and_get_helix_data")
# consume_rows_and_get_helix_data.start()
# get_and_lock_rows.join()
# consume_rows_and_get_helix_data.running = False
# consume_rows_and_get_helix_data.join()
# log.info("done with userid migration")
# points: INT -> BIGINT
log.info("change points to BIGINT")
cursor.execute(
'ALTER TABLE "user" ALTER COLUMN points SET DATA TYPE BIGINT')
log.info("import lines from redis")
for login, num_lines in redis.zscan_iter(
f"{bot.streamer.login}:users:num_lines",
score_cast_func=safe_to_int):
if num_lines is None:
# invalid amount in redis, skip
continue
cursor.execute('UPDATE "user" SET num_lines = %s WHERE login = %s',
(num_lines, login))
# new: tokens
cursor.execute(
'ALTER TABLE "user" ADD COLUMN tokens INT NOT NULL DEFAULT 0')
for login, tokens in redis.zscan_iter(f"{bot.streamer.login}:users:tokens",
score_cast_func=safe_to_int):
if tokens is None:
# invalid amount in redis, skip
continue
if tokens > 50:
tokens = 50
cursor.execute('UPDATE "user" SET tokens = %s WHERE login = %s',
(tokens, login))
# new: last_seen
log.info("import last seen from redis")
cursor.execute(
'ALTER TABLE "user" ADD COLUMN last_seen TIMESTAMPTZ DEFAULT NULL')
for login, last_seen_raw in redis.hscan_iter(
f"{bot.streamer.login}:users:last_seen"):
last_seen = datetime.datetime.fromtimestamp(float(last_seen_raw),
tz=datetime.timezone.utc)
cursor.execute('UPDATE "user" SET last_seen = %s WHERE login = %s',
(last_seen, login))
# new: last_active
log.info("import last active from redis")
cursor.execute(
'ALTER TABLE "user" ADD COLUMN last_active TIMESTAMPTZ DEFAULT NULL')
for login, last_active_raw in redis.hscan_iter(
f"{bot.streamer.login}:users:last_active"):
last_seen = datetime.datetime.fromtimestamp(float(last_active_raw),
tz=datetime.timezone.utc)
cursor.execute('UPDATE "user" SET last_active = %s WHERE login = %s',
(last_seen, login))
# minutes_in_chat_{online,offline} -> INTERVAL type and renamed to time_in_chat_...
cursor.execute(
'ALTER TABLE "user" RENAME COLUMN minutes_in_chat_online TO time_in_chat_online'
)
cursor.execute(
'ALTER TABLE "user" RENAME COLUMN minutes_in_chat_offline TO time_in_chat_offline'
)
cursor.execute("""
ALTER TABLE "user" ALTER COLUMN time_in_chat_online SET DATA TYPE INTERVAL
USING make_interval(mins := time_in_chat_online)
""")
cursor.execute("""
ALTER TABLE "user" ALTER COLUMN time_in_chat_offline SET DATA TYPE INTERVAL
USING make_interval(mins := time_in_chat_offline)
""")
cursor.execute(
"ALTER TABLE \"user\" ALTER COLUMN time_in_chat_online SET DEFAULT INTERVAL '0 minutes'"
)
cursor.execute(
"ALTER TABLE \"user\" ALTER COLUMN time_in_chat_offline SET DEFAULT INTERVAL '0 minutes'"
)
# new: ignored
cursor.execute(
'ALTER TABLE "user" ADD COLUMN ignored BOOLEAN NOT NULL DEFAULT FALSE')
for login in redis.hkeys(f"{bot.streamer.login}:users:ignored"):
cursor.execute('UPDATE "user" SET ignored = TRUE WHERE login = %s',
(login, ))
# new: banned
cursor.execute(
'ALTER TABLE "user" ADD COLUMN banned BOOLEAN NOT NULL DEFAULT FALSE')
for login in redis.hkeys(f"{bot.streamer.login}:users:banned"):
cursor.execute('UPDATE "user" SET banned = TRUE WHERE login = %s',
(login, ))
# note: username_raw is not migrated in from redis, since the username_raw data will be fetched
# fresh from the Twitch API below anyways.
# migrate users to ID
cursor.execute('SELECT COUNT(*) FROM "user"')
users_count = cursor.fetchone()[0]
# create Server-side cursor
cursor.execute(
'DECLARE all_users CURSOR FOR SELECT id, login FROM "user" ORDER BY id FOR UPDATE'
)
offset = 0
while True:
cursor.execute("FETCH FORWARD 100 FROM all_users")
rows = cursor.fetchall(
) # = [(id, login), (id, login), (id, login), ...]
if len(rows) <= 0:
# done!
break
offset += 100
log.info(f"{offset}/{users_count}")
usernames_to_fetch = [t[1] for t in rows]
all_user_basics = retry_call(
bot.twitch_helix_api.bulk_get_user_basics_by_login,
fargs=[usernames_to_fetch],
tries=3,
delay=5)
for id, basics in zip((t[0] for t in rows), all_user_basics):
if basics is not None:
cursor.execute(
'UPDATE "user" SET twitch_id = %s, login = %s, name = %s WHERE id = %s',
(basics.id, basics.login, basics.name, id),
)
# release the cursor again
cursor.execute("CLOSE all_users")
# update admin logs to primary-key by Twitch ID.
admin_logs_key = f"{bot.streamer.login}:logs:admin"
all_admin_logs = redis.lrange(admin_logs_key, 0, -1)
# all_admin_logs and new_log_entries are in newest -> oldest order
new_log_entries = []
for idx, raw_log_entry in enumerate(all_admin_logs):
log_entry = json.loads(raw_log_entry)
old_internal_id = log_entry["user_id"]
cursor.execute('SELECT twitch_id FROM "user" WHERE id = %s',
(old_internal_id, ))
row = cursor.fetchone()
if row is not None and row[0] is not None:
log_entry["user_id"] = row[0]
else:
log_entry["user_id"] = None
new_log_entries.append(log_entry)
@contextmanager
def also_move_pkey(table, column):
cursor.execute(f"ALTER TABLE {table} DROP CONSTRAINT {table}_pkey")
yield
cursor.execute(f"ALTER TABLE {table} ADD PRIMARY KEY ({column})")
def update_foreign_key(table, column, nullable=False):
cursor.execute(
f"ALTER TABLE {table} DROP CONSTRAINT {table}_{column}_fkey, ALTER COLUMN {column} SET DATA TYPE TEXT"
)
if not nullable:
cursor.execute(
f"ALTER TABLE {table} ALTER COLUMN {column} DROP NOT NULL")
cursor.execute(
f'UPDATE {table} T SET {column} = (SELECT twitch_id FROM "user" WHERE id = T.{column}::int)'
)
if not nullable:
cursor.execute(f"DELETE FROM {table} WHERE {column} IS NULL")
cursor.execute(
f"ALTER TABLE {table} ALTER COLUMN {column} SET NOT NULL")
if nullable:
on_delete_action = "SET NULL"
else:
on_delete_action = "CASCADE"
cursor.execute(
f'ALTER TABLE {table} ADD FOREIGN KEY ({column}) REFERENCES "user"(twitch_id) ON DELETE {on_delete_action}'
)
update_foreign_key("banphrase_data", "added_by", nullable=True)
update_foreign_key("banphrase_data", "edited_by", nullable=True)
update_foreign_key("command_data", "added_by", nullable=True)
update_foreign_key("command_data", "edited_by", nullable=True)
update_foreign_key("hsbet_bet", "user_id")
update_foreign_key("pleblist_song", "user_id", nullable=True)
update_foreign_key("prediction_run_entry", "user_id")
update_foreign_key("roulette", "user_id")
with also_move_pkey("user_duel_stats", "user_id"):
update_foreign_key("user_duel_stats", "user_id")
# delete users that were not found. farewell...
# the ON DELETE rules we set before will make these users disappear from other data structures too
cursor.execute('DELETE FROM "user" WHERE twitch_id IS NULL')
# drop the internal ID column
cursor.execute('ALTER TABLE "user" DROP COLUMN id')
# we can also now set the display name to be non-null
# since we definitely eliminated any legacy rows that might be missing that value
cursor.execute('ALTER TABLE "user" ALTER COLUMN name SET NOT NULL')
# Rename the twitch_id to id, and make it primary key
cursor.execute('ALTER TABLE "user" RENAME COLUMN twitch_id TO id')
cursor.execute('ALTER TABLE "user" ALTER COLUMN id SET NOT NULL')
cursor.execute('ALTER TABLE "user" ADD PRIMARY KEY(id)')
def delete_foreign_key(table, column):
cursor.execute(
f"ALTER TABLE {table} DROP CONSTRAINT {table}_{column}_fkey")
def add_foreign_key_again(table, column, nullable=False):
if nullable:
on_delete_action = "SET NULL"
else:
on_delete_action = "CASCADE"
cursor.execute(
f'ALTER TABLE {table} ADD FOREIGN KEY ({column}) REFERENCES "user"(id) ON DELETE {on_delete_action}'
)
# now this is special: We first had a users table with a primary key on the internal ID,
# then added a UNIQUE INDEX on (twitch_id) so we could have foreign keys pointing
# to the twitch_id (in update_foreign_key). We needed to definitely add those foreign keys back
# so we can get the cascading effect from the DELETE FROM "user" statement.
# Now we are left with two UNIQUE indexes indexing the same thing:
# - "user_pkey" PRIMARY KEY, btree (id)
# - "user_twitch_id_idx" UNIQUE, btree (id)
# all those foreign keys we created earlier are all referring (depend) on user_twitch_id_idx.
# If we want to eliminate user_twitch_id_idx, we have to drop all those foreign key constraints,
# DROP the index, and then create them again to make them use the primary key index (as they should).
# so this is what the following block does.
delete_foreign_key("banphrase_data", "added_by")
delete_foreign_key("banphrase_data", "edited_by")
delete_foreign_key("command_data", "added_by")
delete_foreign_key("command_data", "edited_by")
delete_foreign_key("hsbet_bet", "user_id")
delete_foreign_key("pleblist_song", "user_id")
delete_foreign_key("prediction_run_entry", "user_id")
delete_foreign_key("roulette", "user_id")
delete_foreign_key("user_duel_stats", "user_id")
cursor.execute("DROP INDEX user_twitch_id_idx")
add_foreign_key_again("banphrase_data", "added_by", nullable=True)
add_foreign_key_again("banphrase_data", "edited_by", nullable=True)
add_foreign_key_again("command_data", "added_by", nullable=True)
add_foreign_key_again("command_data", "edited_by", nullable=True)
add_foreign_key_again("hsbet_bet", "user_id")
add_foreign_key_again("pleblist_song", "user_id", nullable=True)
add_foreign_key_again("prediction_run_entry", "user_id")
add_foreign_key_again("roulette", "user_id")
add_foreign_key_again("user_duel_stats", "user_id")
# new: login_last_updated (+triggers)
cursor.execute(
'ALTER TABLE "user" ADD COLUMN login_last_updated TIMESTAMPTZ NOT NULL DEFAULT now()'
)
cursor.execute("""
CREATE FUNCTION trigger_user_update_login_last_updated()
RETURNS trigger AS
$$
BEGIN
NEW.login_last_updated = now();
RETURN NEW;
END
$$
LANGUAGE plpgsql
""")
cursor.execute("""
CREATE TRIGGER user_login_update
AFTER UPDATE OF login ON "user"
FOR EACH ROW EXECUTE PROCEDURE trigger_user_update_login_last_updated()
""")
with RedisManager.pipeline_context() as redis_pipeline:
# Overwrite admin logs
redis_pipeline.delete(admin_logs_key)
if len(new_log_entries) > 0:
redis_pipeline.rpush(
admin_logs_key,
*[json.dumps(entry) for entry in new_log_entries])
# Delete data that was moved in
redis_pipeline.delete(
f"{bot.streamer.login}:users:num_lines",
f"{bot.streamer.login}:users:tokens",
f"{bot.streamer.login}:users:last_seen",
f"{bot.streamer.login}:users:last_active",
f"{bot.streamer.login}:users:username_raw",
f"{bot.streamer.login}:users:ignored",
f"{bot.streamer.login}:users:banned",
)
def parse_message_twitch_emotes(self, source, message, tag, whisper):
message_emotes = []
new_user_tags = []
# Twitch Emotes
if tag:
emote_data = tag.split('/')
for emote in emote_data:
try:
emote_id, emote_occurrences = emote.split(':')
emote_indices = emote_occurrences.split(',')
# figure out how many times the emote occured in the message
emote_count = len(emote_indices)
first_index, last_index = emote_indices[0].split('-')
first_index = int(first_index)
last_index = int(last_index)
emote_code = message[first_index:last_index + 1]
if emote_code[0] == ':':
emote_code = emote_code.upper()
message_emotes.append({
'code': emote_code,
'twitch_id': emote_id,
'start': first_index,
'end': last_index,
'count': emote_count,
})
sub = self.subemotes.get(emote_code, None)
if sub:
new_user_tags.append('{sub}_sub'.format(sub=sub))
except:
log.exception('Exception caught while splitting emote data')
log.error('Emote data: {}'.format(emote_data))
log.error('Message: {}'.format(message))
# BTTV Emotes
for emote in self.bttv_emote_manager.valid_emotes:
num = 0
start = -1
end = -1
for match in emote['regex'].finditer(message):
num += 1
if num == 1:
start = match.span()[0]
end = match.span()[1] - 1 # don't ask me
if num > 0:
message_emotes.append({
'code': emote['code'],
'bttv_hash': emote['emote_hash'],
'start': start,
'end': end,
'count': num,
})
# FFZ Emotes
for emote in self.ffz_emote_manager.valid_emotes:
num = 0
start = -1
end = -1
for match in emote['regex'].finditer(message):
num += 1
if num == 1:
start = match.span()[0]
end = match.span()[1] - 1 # don't ask me
if num > 0:
message_emotes.append({
'code': emote['code'],
'ffz_id': emote['emote_id'],
'start': start,
'end': end,
'count': num,
})
if len(message_emotes) > 0 or len(new_user_tags) > 0:
streamer = StreamHelper.get_streamer()
with RedisManager.pipeline_context() as pipeline:
if not whisper:
for emote in message_emotes:
pipeline.zincrby('{streamer}:emotes:count'.format(streamer=streamer), emote['code'], emote['count'])
self.epm_incr(emote['code'], emote['count'])
user_tags = source.get_tags()
for tag in new_user_tags:
user_tags[tag] = (datetime.datetime.now() + datetime.timedelta(days=15)).timestamp()
source.set_tags(user_tags, redis=pipeline)
return message_emotes
def parse_message_twitch_emotes(self, source, message, tag, whisper):
message_emotes = []
new_user_tags = []
# Twitch Emotes
if tag:
emote_data = tag.split('/')
for emote in emote_data:
try:
emote_id, emote_occurrences = emote.split(':')
emote_indices = emote_occurrences.split(',')
# figure out how many times the emote occured in the message
emote_count = len(emote_indices)
first_index, last_index = emote_indices[0].split('-')
first_index = int(first_index)
last_index = int(last_index)
emote_code = message[first_index:last_index + 1]
if emote_code[0] == ':':
emote_code = emote_code.upper()
message_emotes.append({
'code': emote_code,
'twitch_id': emote_id,
'start': first_index,
'end': last_index,
'count': emote_count,
})
sub = self.subemotes.get(emote_code, None)
if sub:
new_user_tags.append('{sub}_sub'.format(sub=sub))
except:
log.exception(
'Exception caught while splitting emote data')
log.error('Emote data: {}'.format(emote_data))
log.error('Message: {}'.format(message))
# BTTV Emotes
for emote in self.bttv_emote_manager.all_emotes:
num = 0
start = -1
end = -1
for match in emote['regex'].finditer(message):
num += 1
if num == 1:
start = match.span()[0]
end = match.span()[1] - 1 # don't ask me
if num > 0:
message_emotes.append({
'code': emote['code'],
'bttv_hash': emote['emote_hash'],
'start': start,
'end': end,
'count': num,
})
# FFZ Emotes
for emote in self.ffz_emote_manager.all_emotes:
num = 0
start = -1
end = -1
for match in emote['regex'].finditer(message):
num += 1
if num == 1:
start = match.span()[0]
end = match.span()[1] - 1 # don't ask me
if num > 0:
message_emotes.append({
'code': emote['code'],
'ffz_id': emote['emote_id'],
'start': start,
'end': end,
'count': num,
})
if len(message_emotes) > 0 or len(new_user_tags) > 0:
streamer = StreamHelper.get_streamer()
with RedisManager.pipeline_context() as pipeline:
if not whisper:
for emote in message_emotes:
pipeline.zincrby(
'{streamer}:emotes:count'.format(
streamer=streamer), emote['code'],
emote['count'])
self.epm_incr(emote['code'], emote['count'])
user_tags = source.get_tags()
for tag in new_user_tags:
user_tags[tag] = (datetime.datetime.now() +
datetime.timedelta(days=15)).timestamp()
source.set_tags(user_tags, redis=pipeline)
return message_emotes
