class GiveawayQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.loggingQueryHelper = LoggingQueryHelper()
self.sqlHelper = SQLHelper()
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def getPoints(self, username, channel):
return self.loggingQueryHelper.getPoints(username, channel)
def deductPoints(self, username, channel, points):
self.loggingQueryHelper.deductPoints(username, channel, points)
def __init__(self):
for sig in (SIGINT, SIGTERM):
signal(sig, self.kill)
self.ircSock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
self.irc_host = Settings.irc_host
self.irc_chan = '#' + Settings.irc_channel.lower()
self.connected = False
self._pluginFolder = './plugins/'
self._mainModule = 'plugin'
self._plugins = []
self.commands = []
self.msgRegister = []
self.joinPartHandlers = []
self.loadedPluginNames = []
self.moddedInList = ['#popethethird', '#joeruu']
self.ignoredPlugins = ['SpamPlugin']
self.ignoredUsers = []
self.queryHelper = BaseQueryHelper()
class TwitchBot:
def __init__(self):
for sig in (SIGINT, SIGTERM):
signal(sig, self.kill)
self.ircSock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
self.irc_host = Settings.irc_host
self.irc_chan = '#' + Settings.irc_channel.lower()
self.connected = False
self._pluginFolder = './plugins/'
self._mainModule = 'plugin'
self._plugins = []
self.commands = []
self.msgRegister = []
self.joinPartHandlers = []
self.loadedPluginNames = []
self.moddedInList = ['#popethethird', '#joeruu']
self.ignoredPlugins = ['SpamPlugin']
self.ignoredUsers = []
self.queryHelper = BaseQueryHelper()
def kill(self):
print("Twitch Bot Ending...")
for p in self._plugins:
p._kill()
def sendMessage(self, className, chan, message, needMod=True):
if (needMod and chan in self.moddedInList) or (not needMod):
print("%s: %s" % (chan, message))
self.ircSock.send(
str('PRIVMSG %s :%s\n' % (chan, message)).encode('UTF-8'))
else:
print("Channel %s attempted to use commands without modding bot" %
chan)
def connect(self, port):
self.ircSock.connect((self.irc_host, port))
self.ircSock.send(
str("Pass " + Settings.irc_oauth + "\r\n").encode('UTF-8'))
self.ircSock.send(
str("NICK " + Settings.irc_username + "\r\n").encode('UTF-8'))
for channel in self.queryHelper.getAllChannels():
if self.queryHelper.channelIsEnabled(channel.channel.lower()):
self.joinChannel(channel.channel.lower())
time.sleep(.5)
def joinChannel(self, channel):
self.ircSock.send(
str("JOIN " + channel.lower() + "\r\n").encode('UTF-8'))
time.sleep(.5)
# self.sendMessage(None, channel, "Hello! I am MiniGameBot. A list of my commands may be found at twitch.tv/PopeTheThird. Please ensure I am modded and allow 30-60 seconds after joining to prevent rate-limiting. Enjoy!", False)
def partChannel(self, channel):
self.ircSock.send(
str("PART " + channel.lower() + "\r\n").encode('UTF-8'))
def registerCommand(self, className, command, pluginFunction):
self.commands.append({
'regex': command,
'handler': pluginFunction,
'plugin': className
})
def registerAll(self, className, pluginFunction):
self.msgRegister.append({
'handler': pluginFunction,
'plugin': className
})
def registerJoinPartNotifications(self, className, pluginFunction):
self.joinPartHandlers.append({
'handler': pluginFunction,
'plugin': className
})
def handleIRCMessage(self, ircMessage):
print(ircMessage)
nick = ircMessage.split('!')[0][1:]
# Message to a channel
if ircMessage.find(' PRIVMSG #') != -1:
chan = ircMessage.split(' ')[2]
msg = ircMessage.split(' PRIVMSG ' + chan + ' :')[1]
for pluginDict in self.commands:
if re.search('^' + Settings.irc_trigger + pluginDict['regex'] + '\\b', msg, re.IGNORECASE) \
and not self.queryHelper.checkPluginDisabled(chan, pluginDict['plugin']):
if not nick in self.ignoredUsers:
handler = pluginDict['handler']
args = msg.split(" ")
handler(nick, chan, args)
if not (self.queryHelper.isMod(nick, chan)
or self.queryHelper.isAdmin(nick)):
self.ignoredUsers.append(nick)
threading.Timer(5,
self.removeIgnored,
args=(nick, )).start()
else:
print(
"User %s attempted to use command quickly in %s" %
(nick, chan))
for pluginDict in self.msgRegister:
if not self.queryHelper.checkPluginDisabled(
chan, pluginDict['plugin']):
handler = pluginDict['handler']
args = msg.split(" ")
handler(nick, chan, args)
elif ircMessage.find('PING ') != -1:
self.ircSock.send(str("PING :pong\n").encode('UTF-8'))
# User joined channel
elif ircMessage.find('JOIN ') != -1:
nick = ircMessage.split('!')[0][1:]
chan = ircMessage.split(' ')[2]
print(nick + " joined " + chan)
for handler in self.joinPartHandlers:
if not self.queryHelper.checkPluginDisabled(
chan, handler['plugin']):
handler['handler'](nick, chan, True)
# User left channel
elif ircMessage.find('PART ') != -1:
nick = ircMessage.split('!')[0][1:]
chan = ircMessage.split(' ')[2]
print(nick + " left " + chan)
for handler in self.joinPartHandlers:
if not self.queryHelper.checkPluginDisabled(
chan, handler['plugin']):
handler['handler'](nick, chan, False)
# User oped in channel
elif ircMessage.find('MODE ') != -1:
nick = ircMessage.split(' ')[-1]
chan = ircMessage.split(' ')[2]
op = ircMessage.split(' ')[3]
if nick.lower() == Settings.irc_username.lower():
if op == "+o" and not chan in self.moddedInList:
self.moddedInList.append(chan)
print("Modded in %s" % chan)
elif op == "-o" and chan in self.moddedInList:
self.moddedInList.remove(chan)
print("Unmodded in %s" % chan)
else:
pass
def removeIgnored(self, username):
if username in self.ignoredUsers:
self.ignoredUsers.remove(username)
def run(self):
line_sep_exp = re.compile(b'\r?\n')
socketBuffer = b''
while True:
try:
self.connected = True
socketBuffer += self.ircSock.recv(1024)
ircMsgs = line_sep_exp.split(socketBuffer)
socketBuffer = ircMsgs.pop()
for ircMsg in ircMsgs:
msg = ircMsg.decode('utf-8')
logging.info(msg)
Thread(target=self.handleIRCMessage, args=(msg, )).start()
except Exception as e:
print(traceback.format_exc())
raise e
def loadPlugins(self):
potentialPlugins = []
allPlugins = os.listdir(self._pluginFolder)
for i in allPlugins:
location = os.path.join(self._pluginFolder, i)
if not os.path.isdir(
location) or not self._mainModule + ".py" in os.listdir(
location):
continue
info = imp.find_module(self._mainModule, [location])
potentialPlugins.append({"name": i, "info": info})
print("Found plugin classes:")
for i in potentialPlugins:
try:
plugin = imp.load_module(self._mainModule, *i["info"])
pluginClasses = inspect.getmembers(plugin, inspect.isclass)
for className, classObj in pluginClasses:
if className == "BasePlugin" or className in self.loadedPluginNames or not issubclass(
classObj,
BasePlugin) or className in self.ignoredPlugins:
continue
print(className)
pluginInstance = classObj(self)
self._plugins.append(pluginInstance)
self.loadedPluginNames.append(className)
except Exception as e:
print("Error loading plugin.")
print(traceback.format_exc())
def reconnect(self):
try:
self.connect(6667)
self.run()
except Exception as e:
print(traceback.format_exc())
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.loggingQueryHelper = LoggingQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
class GameQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.loggingQueryHelper = LoggingQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute(
"CREATE TABLE IF NOT EXISTS `bets` "
"(`bet_id` int NOT NULL AUTO_INCREMENT,"
"`channel_id` int NOT NULL,"
"`user_id` int NOT NULL,"
"`status` ENUM('active', 'complete') DEFAULT 'active',"
"`match_id` INT NOT NULL,"
"`bet_for` int,"
"`bet_amount` double DEFAULT 0,"
"PRIMARY KEY (`bet_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `complete_matches` "
"(`match_id` int NOT NULL AUTO_INCREMENT,"
"`url` text,"
"`game` text,"
"`opponent1` text,"
"`opponent2` text,"
"`bet1` text,"
"`bet2` text,"
"`score` text,"
"PRIMARY KEY (`match_id`))")
db.commit()
except Exception as e:
print("Error initializing gaming tables")
print(traceback.format_exc())
finally:
db.close()
def insertComplete(self, game):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute(
"""INSERT IGNORE INTO `complete_matches` (`match_id`, `url`, `game`, `opponent1`, `opponent2`, `bet1`, `bet2`, `score`)
VALUES(%s, %s, %s, %s, %s, %s, %s, %s)""",
(game.id, game.link, game.game, game.opp1.encode("UTF-8"),
game.opp2.encode("UTF-8"), game.bet1, game.bet2,
game.score))
db.commit()
except Exception as e:
print("Error inserting new match archive %s %s %s" %
(game.opp1, game.opp2, game.link))
print(traceback.format_exc())
finally:
db.close()
def insertBet(self, bet, teamNum, match, username, channel):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""INSERT IGNORE INTO `bets` (`channel_id`, `user_id`, `match_id`, `bet_for`, `bet_amount`)
VALUES(%s, %s, %s, %s, %s)""",
(channel_id, user_id, match.id, teamNum, bet))
db.commit()
self.loggingQueryHelper.deductPoints(username, channel, bet)
except Exception as e:
print("Error inserting new bet")
print(traceback.format_exc())
finally:
db.close()
def getBets(self, username, channel, status):
bets = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""SELECT * FROM `bets` WHERE `channel_id` = %s AND `user_id` = %s AND `status` = %s""",
(channel_id, user_id, status))
rows = cur.fetchall()
for row in rows:
bets.append(Bet(row))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return bets
def getAllActiveBets(self):
bets = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT * FROM `bets` WHERE `status` = %s""",
('active', ))
rows = cur.fetchall()
for row in rows:
bets.append(Bet(row))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return bets
def getCompletedGame(self, matchID):
game = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute(
"""SELECT * FROM `complete_matches` WHERE `match_id` = %s""",
(matchID, ))
result = cur.fetchone()
if not result is None:
game = Game(result[2], result[0], "Recent", result[1],
result[3], result[4], result[5], result[6])
game.setScore(result[7])
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return game
def setComplete(self, betID):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute(
"""UPDATE `bets` SET `status` = %s WHERE `bet_id` = %s""",
('complete', betID))
db.commit()
except Exception as e:
print("Error updating match status")
print(traceback.format_exc())
finally:
db.close()
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def getPoints(self, username, channel):
return self.loggingQueryHelper.getPoints(username, channel)
def deductPoints(self, user_id, channel_id, points):
username = self.queryHelper.getUsername(user_id)
channel = self.queryHelper.getChannel(channel_id)
self.loggingQueryHelper.deductPoints(username, channel, points)
def increasePoints(self, user_id, channel_id, points):
username = self.queryHelper.getUsername(user_id)
channel = self.queryHelper.getChannel(channel_id)
self.loggingQueryHelper.increasePoints(username, channel, points)
class SpamQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("CREATE TABLE IF NOT EXISTS `spam` "
"(`channel_id` int NOT NULL,"
"`msg` VARCHAR(256) NOT NULL,"
"PRIMARY KEY (channel_id, msg))")
db.commit()
except Exception as e:
print("Error initializing spam tables")
print(traceback.format_exc())
finally:
db.close()
def addSpam(self, channel, msg):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute("""INSERT IGNORE INTO spam(channel_id, msg) VALUES(%s, %s)""", (channel_id, msg))
db.commit()
except Exception as e:
print("Error inserting new spam message")
print(traceback.format_exc())
finally:
db.close()
def getSpam(self, channel):
spamMessages = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute("""SELECT msg FROM spam WHERE channel_id = %s""", (channel_id,))
rows = cur.fetchall()
for row in rows:
spamMessages.append(str(row[0]))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return spamMessages
def removeSpam(self, channel, msg):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute("""DELETE FROM spam WHERE channel_id = %s AND msg = %s""", (channel_id, msg))
db.commit()
except Exception as e:
print("Error removing spam")
print(traceback.format_exc())
finally:
db.close()
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def prohibitLinks(self, channel):
self.queryHelper.setSetting(channel, "links_allowed", 0);
def allowLinks(self, channel):
self.queryHelper.setSetting(channel, "links_allowed", 1);
class TriviaQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.loggingQueryHelper = LoggingQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("CREATE TABLE IF NOT EXISTS `trivia_categories` "
"(`category_id` INT NOT NULL AUTO_INCREMENT,"
"`name` TEXT,"
"`description` TEXT,"
"PRIMARY KEY (`category_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `trivia_questions` "
"(`question_id` INT NOT NULL AUTO_INCREMENT,"
"`category_id` INT,"
"`question` TEXT,"
"`answer` TEXT,"
"`value` INT DEFAULT 0,"
"PRIMARY KEY (`question_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `trivia_categories_suggestions` "
"(`suggestion_id` INT NOT NULL AUTO_INCREMENT,"
"`name` TEXT,"
"`description` TEXT,"
"`user_id` TEXT,"
"`channel_id` TEXT,"
"`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
"PRIMARY KEY (`suggestion_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `trivia_question_suggestions` "
"(`suggestion_id` INT NOT NULL AUTO_INCREMENT,"
"`category` TEXT,"
"`question` TEXT,"
"`answer` TEXT,"
"`value` INT DEFAULT 0,"
"`user_id` TEXT,"
"`channel_id` TEXT,"
"`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
"PRIMARY KEY (`suggestion_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `trivia_leaderboard` "
"(`user_id` INT NOT NULL,"
"`channel_id` INT NOT NULL,"
"`answered` INT DEFAULT 1,"
"`points` INT DEFAULT 0,"
"PRIMARY KEY (`user_id`, `channel_id`))")
db.commit()
except Exception as e:
print("Error initializing trivia tables")
print(traceback.format_exc())
finally:
db.close()
def getCatName(self, category_id):
category = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT `name` FROM `trivia_categories` WHERE `category_id` = %s""", (category_id,))
result = cur.fetchone()
if not result is None:
category = result[0]
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return category
def getLocalScores(self, channel):
tuples = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute("""SELECT `user_id`, `answered`, `points` FROM `trivia_leaderboard` WHERE `channel_id` = %s ORDER BY `points` DESC LIMIT 5""", (channel_id,))
rows = cur.fetchall()
for row in rows:
tuples.append((self.queryHelper.getUsername(row[0]), row[1], row[2]))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return tuples
def getGlobalScores(self):
tuples = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT `user_id`, `answered`, `points` FROM `trivia_leaderboard` ORDER BY `points` DESC LIMIT 5""")
rows = cur.fetchall()
for row in rows:
tuples.append((self.queryHelper.getUsername(row[0]), row[1], row[2]))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return tuples
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def getPoints(self, username, channel):
return self.loggingQueryHelper.getPoints(username, channel)
def deductPoints(self, username, channel, points):
self.loggingQueryHelper.deductPoints(username, channel, points)
def increasePoints(self, username, channel, points):
self.loggingQueryHelper.increasePoints(username, channel, points)
self.updateLeaderboard(username, channel, points)
def updateLeaderboard(self, username, channel, points):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute("""INSERT INTO `trivia_leaderboard` (`channel_id`, `user_id`, `points`) VALUES (%s, %s, %s) ON DUPLICATE KEY
UPDATE `answered` = `answered` + 1, `points` = `points` + %s""", (channel_id, user_id, points, points))
db.commit()
except Exception as e:
print("Error updating leaderboard")
print(traceback.format_exc())
finally:
db.close()
def getCategoryNames(self):
categories = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT `name` FROM `trivia_categories`""")
rows = cur.fetchall()
for row in rows:
categories.append(row[0])
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return ", ".join(categories)
def insertCategorySuggestion(self, username, channel, name, description):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute("""INSERT IGNORE INTO `trivia_categories_suggestions` (`channel_id`, `user_id`, `name`, `description`)
VALUES(%s, %s, %s, %s)""", (channel_id, user_id, name, description))
db.commit()
except Exception as e:
print("Error inserting new category suggestion")
print(traceback.format_exc())
finally:
db.close()
def insertCategory(self, name, description):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""INSERT IGNORE INTO `trivia_categories` (`name`, `description`)
VALUES(%s, %s)""", (name, description))
db.commit()
except Exception as e:
print("Error inserting new category")
print(traceback.format_exc())
finally:
db.close()
def insertQuestionSuggestion(self, username, channel, category, question, answer, value):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute("""INSERT IGNORE INTO `trivia_question_suggestions` (`channel_id`, `user_id`, `category`, `question`, `answer`, `value`)
VALUES(%s, %s, %s, %s, %s, %s)""", (channel_id, user_id, category, question, answer, value))
db.commit()
except Exception as e:
print("Error inserting new question suggestion")
print(traceback.format_exc())
finally:
db.close()
def findCategoryID(self, category):
categories = []
result = -1
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT category_id, name FROM `trivia_categories`""")
rows = cur.fetchall()
for row in rows:
categories.append( { "id":row[0], "name":row[1].encode("UTF-8") } )
sortedList = sorted(categories, key=lambda x: difflib.SequenceMatcher(None, x['name'], category).ratio(), reverse=True)
result = sortedList[0]['id']
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return result
def insertQuestion(self, category, question, answer, value):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
category_id = self.findCategoryID(category)
cur.execute("""INSERT IGNORE INTO `trivia_questions` (`category_id`, `question`, `answer`, `value`)
VALUES(%s, %s, %s, %s)""", (category_id, question, answer, value))
db.commit()
except Exception as e:
print("Error inserting new question")
print(traceback.format_exc())
finally:
db.close()
def getQuestion(self, category):
question = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
closest_cat_id = self.findCategoryID(category)
cur.execute("""SELECT `question_id` FROM `trivia_questions` WHERE `category_id` = %s""", (closest_cat_id,))
questionIDList = cur.fetchall()
rand = randint(0, len(questionIDList) - 1)
cur.execute("""SELECT * FROM `trivia_questions` WHERE `question_id` = %s""", (questionIDList[rand][0],))
result = cur.fetchone()
question = Trivia(result[0], result[1], result[2], result[3], result[4])
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return question
def getRandomQuestion(self):
question = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT question_id FROM `trivia_questions`""")
questionIDList = cur.fetchall()
rand = randint(0, len(questionIDList) - 1)
cur.execute("""SELECT * FROM `trivia_questions` WHERE `question_id` = %s""", (questionIDList[rand][0],))
result = cur.fetchone()
question = Trivia(result[0], result[1], result[2], result[3], result[4])
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return question
class AdminQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("CREATE TABLE IF NOT EXISTS `bug_log` "
"(`bug_id` int NOT NULL AUTO_INCREMENT,"
"`user_id` int,"
"`channel_id` int,"
"`description` TEXT,"
"PRIMARY KEY (`bug_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `mail_log` "
"(`mail_id` int NOT NULL AUTO_INCREMENT,"
"`user_id` int,"
"`channel_id` int,"
"`message` TEXT,"
"PRIMARY KEY (`mail_id`))")
db.commit()
except Exception as e:
print("Error initializing admin tables")
print(traceback.format_exc())
finally:
db.close()
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def addChannel(self, channel, moderator, enabled=True):
return self.queryHelper.addChannel(channel, moderator, enabled)
def removeChannel(self, channel):
return self.queryHelper.removeChannel(channel)
def addMod(self, username, channel):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
user_id = self.queryHelper.getUserID(username)
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""INSERT IGNORE INTO mods (channel_id, user_id) VALUES(%s, %s)""",
(channel_id, user_id))
db.commit()
except Exception as e:
print("Error inserting new moderator")
print(traceback.format_exc())
finally:
db.close()
def logBug(self, username, channel, description):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
user_id = self.queryHelper.getUserID(username)
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""INSERT IGNORE INTO `bug_log` (`channel_id`, `user_id`, `description`) VALUES(%s, %s, %s)""",
(channel_id, user_id, description))
db.commit()
except Exception as e:
print("Error inserting new bug")
print(traceback.format_exc())
finally:
db.close()
def logMail(self, username, channel, message):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
user_id = self.queryHelper.getUserID(username)
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""INSERT IGNORE INTO `mail_log` (`channel_id`, `user_id`, `message`) VALUES(%s, %s, %s)""",
(channel_id, user_id, message))
db.commit()
except Exception as e:
print("Error inserting mail")
print(traceback.format_exc())
finally:
db.close()
def removeMod(self, username, channel):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
user_id = self.queryHelper.getUserID(username)
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""DELETE FROM mods WHERE user_id = %s AND channel_id = %s""",
(user_id, channel_id))
db.commit()
except Exception as e:
print("Error removing moderator")
print(traceback.format_exc())
finally:
db.close()
def viewMods(self, channel):
mods = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""SELECT user_id FROM mods WHERE channel_id = %s""",
(channel_id, ))
rows = cur.fetchall()
for row in rows:
mods.append(self.queryHelper.getUsername(row[0]))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return mods
def addCommand(self, channel, trigger, output):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""INSERT INTO `commands` (`channel_id`, `key`, `value`)
VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `value` = %s;""",
(channel_id, trigger, output, output))
db.commit()
except Exception as e:
print("Error inserting new command")
print(traceback.format_exc())
finally:
db.close()
def removeCommand(self, channel, key):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""DELETE FROM `commands` WHERE `channel_id` = %s AND `key` = %s""",
(channel_id, key))
db.commit()
except Exception as e:
print("Error removing command")
print(traceback.format_exc())
finally:
db.close()
def viewCommands(self, channel):
commands = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""SELECT `key` FROM `commands` WHERE `channel_id` = %s""",
(channel_id, ))
rows = cur.fetchall()
for row in rows:
commands.append(str(row[0]))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return commands
def getCommand(self, channel, key):
value = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute(
"""SELECT `value` FROM `commands` WHERE `channel_id` = %s AND `key` = %s""",
(channel_id, key))
result = cur.fetchone()
if not result is None:
value = str(result[0])
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return value
class AdQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("CREATE TABLE IF NOT EXISTS `advertisements` "
"(`channel_id` int NOT NULL AUTO_INCREMENT,"
"`time` int DEFAULT 5,"
"`message` TEXT,"
"`enabled` tinyint(1) DEFAULT 1,"
"PRIMARY KEY (`channel_id`))")
db.commit()
except Exception as e:
print("Error initializing advertisement tables")
print(traceback.format_exc())
finally:
db.close()
def isMod(self, username, channel):
return self.queryHelper.isMod(username, channel)
def isAdmin(self, username):
return self.queryHelper.isAdmin(username)
def insertOrUpdateAd(self, channel, interval, message):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
cur.execute("""INSERT INTO `advertisements` (`channel_id`, `time`, `message`)
VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE `time` = %s, `message` = %s;""",
(channel_id, interval, message, interval, message))
db.commit()
except Exception as e:
print("Error inserting or updating advertisement")
print(traceback.format_exc())
finally:
db.close()
def getAllAds(self):
ads = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT * FROM `advertisements` WHERE `enabled` = 1""")
rows = cur.fetchall()
for row in rows:
ads.append(Advertisement(row))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return ads
def getChannel(self, channel_id):
return self.queryHelper.getChannel(channel_id)
def setAdEnabled(self, channel, enabled):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
enabled_int = 1 if enabled else 0
cur.execute("""UPDATE `advertisements` SET `enabled` = %s WHERE `channel_id` = %s""", (enabled_int, channel_id))
db.commit()
except Exception as e:
print("Error updating ad enabled")
print(traceback.format_exc())
finally:
db.close()
def getAdFromID(self, channel_id):
ad = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("""SELECT * FROM `advertisements` WHERE `channel_id` = %s""", (channel_id,))
row = cur.fetchone()
ad = Advertisement(row)
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return ad
class LoggingQueryHelper():
def __init__(self):
self.queryHelper = BaseQueryHelper()
self.sqlHelper = SQLHelper()
self.initTables()
def initTables(self):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
cur.execute("CREATE TABLE IF NOT EXISTS `logs` "
"(`message_id` int NOT NULL AUTO_INCREMENT,"
"`channel_id` int NOT NULL,"
"`user_id` int NOT NULL,"
"`msg` text,"
"`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
"PRIMARY KEY (`message_id`))")
cur.execute("CREATE TABLE IF NOT EXISTS `time_spent`"
"(`user_id` int NOT NULL,"
"`channel_id` int NOT NULL,"
"`time_spent` int NOT NULL DEFAULT 0,"
"`points` double NOT NULL DEFAULT 0,"
"PRIMARY KEY (`user_id`, `channel_id`))")
db.commit()
except Exception as e:
print("Error initializing logging tables")
print(traceback.format_exc())
finally:
db.close()
def insertMsg(self, username, channel, msg):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""INSERT IGNORE INTO `logs` (`channel_id`, `user_id`, `msg`) VALUES(%s, %s, %s)""",
(channel_id, user_id, msg))
db.commit()
except Exception as e:
print("Error inserting new msg")
print(traceback.format_exc())
finally:
db.close()
def insertUser(self, username):
return self.queryHelper.insertUser(username)
def getMessages(self, username, channel):
messages = []
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""SELECT * FROM `logs` WHERE `channel_id` = %s AND `user_id` = %s""",
(channel_id, user_id))
rows = cur.fetchall()
for row in rows:
messages.append(Message(row))
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return messages
def updateTimeSpent(self, username, channel, time):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""INSERT INTO `time_spent` (`channel_id`, `user_id`) VALUES (%s, %s) ON DUPLICATE KEY
UPDATE `time_spent` = `time_spent` + %s, `points` = `points` + %s""",
(channel_id, user_id, time, time))
db.commit()
except Exception as e:
print("Error inserting new time spent")
print(traceback.format_exc())
finally:
db.close()
def deductPoints(self, username, channel, points):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""INSERT INTO `time_spent` (`channel_id`, `user_id`) VALUES (%s, %s) ON DUPLICATE KEY
UPDATE `points` = `points` - %s""",
(channel_id, user_id, points))
db.commit()
except Exception as e:
print("Error deducting points")
print(traceback.format_exc())
finally:
db.close()
def increasePoints(self, username, channel, points):
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""INSERT INTO `time_spent` (`channel_id`, `user_id`) VALUES (%s, %s) ON DUPLICATE KEY
UPDATE `points` = `points` + %s""",
(channel_id, user_id, points))
db.commit()
except Exception as e:
print("Error awarding points")
print(traceback.format_exc())
finally:
db.close()
def getPoints(self, username, channel):
value = None
try:
db = self.sqlHelper.getConnection()
with closing(db.cursor()) as cur:
channel_id = self.queryHelper.getChannelID(channel)
user_id = self.queryHelper.getUserID(username)
cur.execute(
"""SELECT `points` FROM `time_spent` WHERE `channel_id` = %s AND `user_id` = %s""",
(channel_id, user_id))
result = cur.fetchone()
if not result is None:
value = result[0]
except Exception as e:
print(traceback.format_exc())
finally:
db.close()
return value