def insert(self, mysql: MySQLWrapper):
mysql.query(
"INSERT INTO Authors (HLTVID, Name) VALUES (%s, %s) ON DUPLICATE KEY UPDATE Name=%s;",
(self.hltvID, self.name, self.name,)
)
self.sqlID = self.getSQLID(mysql)
def getEndSignal(mysql: MySQLWrapper):
mysql.query("SELECT Value FROM Signals WHERE SignalName='End';")
results = mysql.fetchResults()
if results is None or results[0][0] == 1:
return True
else:
return False
def getRefreshTime(mysql: MySQLWrapper):
mysql.query("SELECT Value FROM Signals WHERE SignalName='Refresh';")
results = mysql.fetchResults()
if results is None:
return 30
else:
return results[0][0]
def insert(self, mysql: MySQLWrapper):
mysql.query(
(
"INSERT INTO Threads (HLTVID, ForumID, AuthorID, Title, NumResponses, Time) VALUES "
"(%s, %s, %s, %s, %s, %s) "
"ON DUPLICATE KEY UPDATE HLTVID=%s;"
),
(self.hltvID, self.forumID, self.author.sqlID, self.title, len(self.posts), self.timestamp, self.hltvID,)
)
self.sqlID = self.getSQLID(mysql)
def insert(self, mysql: MySQLWrapper):
mysql.query(
(
"INSERT INTO Posts (HLTVID, ThreadID, ReplyNum, AuthorID, Content, Time, HateRating, OffRating) VALUES "
"(%s, %s, %s, %s, %s, %s, %s, %s) "
"ON DUPLICATE KEY UPDATE HLTVID=%s;"
),
(self.getHLTVID(), self.threadID, self.index, self.author.sqlID, self.content, self.timestamp, self.hateRating, self.offRating, self.getHLTVID(),)
)
self.sqlID = self.getSQLID(mysql)
def getSQLID(self, mysql: MySQLWrapper):
mysql.query(
"SELECT ThreadID FROM Threads WHERE HLTVID=%s;",
(self.hltvID,)
)
result = mysql.fetchResults()
if result is None:
return None
else:
return result[0][0]
def getSQLID(self, mysql: MySQLWrapper):
mysql.query(
"SELECT PostID FROM Posts WHERE HLTVID=%s;",
(self.getHLTVID(),)
)
result = mysql.fetchResults()
if result is None:
return None
else:
return result[0][0]
def getForums(mysql: MySQLWrapper):
mysql.query("SELECT ForumID, HLTVID, Name FROM Forums;")
results = mysql.fetchResults()
if results is None:
return []
else:
forums = []
for row in results:
forumInfo = Forum(
sqlID = row[0], hltvID = row[1], name = row[2]
)
if '/' in forumInfo.hltvID:
forums += [forumInfo]
return forums
# Detailed bot documentation will be added at a later stage in development.
from authorization import AuthorizationInfo
from mysqlwrapper import MySQLWrapper
from toornament import ToornamentInterface, PlayerInfo, TeamInfo
from discordhelper import DiscordHelper
from config import BotConfig
import discord
from discord import Colour, Embed
from discord.ext import commands
import traceback
auth = AuthorizationInfo("auth.json")
mysql = MySQLWrapper(auth)
toornament = ToornamentInterface(auth, mysql)
bot = commands.Bot(command_prefix='.ecc')
discordHelper = DiscordHelper(bot, toornament)
cfg = BotConfig(discordHelper, mysql)
##### COMMANDS #####
@bot.command()
async def emote(ctx, tournamentID, emoteName, imageURL):
newEmote = await discordHelper.createEmote(tournamentID, emoteName,
imageURL)
await ctx.send(f"Created new emote: <:{newEmote.name}:{newEmote.id}>")
def setEndSignal(mysql: MySQLWrapper, enable: bool):
enableInt = 0
if enable:
enableInt = 1
mysql.query("INSERT INTO Signals (SignalName, Value) VALUES (%s, %s) ON DUPLICATE KEY UPDATE Value=%s;", ('End', enableInt, enableInt,))
mysql.db.commit()
mysql.db.commit()
# Retrieves the End-Signal of the database
def getEndSignal(mysql: MySQLWrapper):
mysql.query("SELECT Value FROM Signals WHERE SignalName='End';")
results = mysql.fetchResults()
if results is None or results[0][0] == 1:
return True
else:
return False
# Connect to MySQL database
auth = AuthorizationInfo("auth.json")
mysql = MySQLWrapper(auth)
overwrite = False
# Creates required tables
initializeTables(mysql, overwrite)
# Defines forums to be observed
forums = []
forums += [Forum("Offtopic", "17/off-topic")]
forums += [Forum("CSGO", "28/counter-strike-global-offensive")]
forums += [Forum("Hardware", "16/hardware-tweaks")]
for forum in forums:
forum.insert(mysql)
# By default, the forums are scraped every 15min
from mysqlwrapper import MySQLWrapper
from authorization import AuthorizationInfo
auth = AuthorizationInfo("auth.json")
mysql = MySQLWrapper(auth)
mysql.query("UPDATE Signals SET Active=1 WHERE SignalName=%s;", ('End'))
print(
'Sent end signal to scraper. It will terminate after the current cycle is complete.'
)
def initializeTables(mysql: MySQLWrapper, overwrite: bool = False):
# Creates database tables
# They all follow the same guidelines:
# - The first ID is the SQL ID on which tables can be joined
# - The second ID (HLTVID) is the unique identifier used to reconstruct the URL on HLTV.org
# - These are both unique, so "ON DUPLICATE" statements trigger if the same HLTV item is tried to be added twice
# Posts: Contains all posts (responses to threads, or the initial post in a thread)
# HLTVID - For https://www.hltv.org/forums/threads/2329019/whos-dumber#r43857044 it'd be: '2329019/whos-dumber#r43857044'
# (This has an overlap with the Thread HLTVID, but without it the uniqueness of a post couldn't be checked easily with ON DUPLICATE)
# ThreadID - SQL ID of the thread the post is a response to
# AuthorID - SQL ID of the author of the post
# ReplyNum - Number of the reply in the thread (e.g. 3 for the 3rd response to a thread). 0 for the initial post.
# Content - The content of the post in utf8
# Time - The time at which the post was made
# HateRating - A confidence score between 0.0 and 1.0, indicating how likely this post is hatespeech
# OffRating- A confidence score between 0.0 and 1.0, indicating how likely this post contains offensive language
mysql.createTable("Posts", (
"PostID INT AUTO_INCREMENT, "
"HLTVID VARCHAR(511) NOT NULL, "
"ThreadID INT NOT NULL, "
"AuthorID INT NOT NULL, "
"ReplyNum INT NOT NULL, "
"Content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"Time DATETIME DEFAULT NOW(), "
"HateRating FLOAT DEFAULT 0, "
"OffRating FLOAT DEFAULT 0, "
"PRIMARY KEY(PostID), "
"UNIQUE (HLTVID)"
),
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
overwrite)
# Authors: Contains all forum authors (accounts that created threads or posts)
# HLTV - For https://www.hltv.org/profile/766189/nabaski it'd be '766189/nabaski'
# Name - Forum name of the author
mysql.createTable("Authors", (
"AuthorID INT AUTO_INCREMENT, "
"HLTVID VARCHAR(127) NOT NULL, "
"Name VARCHAR(127) NOT NULL, "
"PRIMARY KEY(AuthorID), "
"UNIQUE (HLTVID)"
),
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
overwrite)
# Threads: Contains all forum threads
# HLTVID - For https://www.hltv.org/forums/threads/2329019/whos-dumber it'd be '2329019/whos-dumber'
# ForumID - SQL ID of the forum the post was made in
# AuthorID - SQL ID of the author who created the thread
# NumResponses - The number of posts in the thread
# Time - The time at which the thread was created
mysql.createTable("Threads", (
"ThreadID INT AUTO_INCREMENT, "
"HLTVID VARCHAR(511) NOT NULL, "
"ForumID INT NOT NULL, "
"AuthorID INT NOT NULL, "
"Title VARCHAR(511) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, "
"NumResponses INT DEFAULT 0, "
"Time DATETIME DEFAULT NOW(), "
"PRIMARY KEY(ThreadID), "
"UNIQUE (HLTVID)"
),
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
overwrite)
# Forums: Contains all forums that are monitored
# HLTV - For https://www.hltv.org/forums/17/off-topic it'd be '17/off-topic'
# Name - Name of the forum
mysql.createTable("Forums", (
"ForumID INT AUTO_INCREMENT, "
"HLTVID VARCHAR(63) NOT NULL, "
"Name VARCHAR(63) NOT NULL, "
"PRIMARY KEY(ForumID), "
"UNIQUE (HLTVID)"
),
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
overwrite)
# Signals: Used to pause or exit the program cleanly
# Currently required signals:
# End: If set to 1, this program will terminate after the end of the current refresh
# Refresh: Time in minutes between refreshs
mysql.createTable("Signals", (
"SignalName VARCHAR(63), "
"Value INT DEFAULT 0, "
"PRIMARY KEY(SignalName)"
),
"CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",
overwrite)
