def add_new_user(self, user_name, user_email):
"""
Add the necessary entries to database for a newly registered user.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
logmsg = 'New Account: User: %s' % user_name
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
data = {'Name': user_name, 'Email': user_email, 'TimeNow': str(int(time.time()))}
sql_cmd = ("INSERT INTO Users "
"(UserId, Name, Email, FirstMail, LastDone, CurrentTask) "
"VALUES(NULL, :Name, :Email, datetime(:TimeNow, 'unixepoch', 'localtime')"
", NULL, 1)")
curs.execute(sql_cmd, data)
cons.commit()
# the new user has now been added to the database. Next we need
# to send him an email with the first task.
# read back the new users UserId and create a directory for putting his
# submissions in.
data = {'Email': user_email}
sql_cmd = "SELECT UserId FROM Users WHERE Email = :Email"
curs.execute(sql_cmd, data)
res = curs.fetchone()
user_id = str(res[0])
dir_name = 'users/'+ user_id
c.check_dir_mkdir(dir_name, self.logger_queue, self.name)
cons.close()
# NOTE: messageid is empty, cause this will be sent out by the welcome message!
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
sql_cmd = "SELECT GeneratorExecutable FROM TaskConfiguration WHERE TaskNr == 1"
curc.execute(sql_cmd)
res = curc.fetchone()
conc.close()
if res != None:
logmsg = "Calling Generator Script: " + str(res[0])
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
logmsg = "UserID " + user_id + ",UserEmail " + user_email
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
self.gen_queue.put(dict({"user_id": user_id, "user_email": user_email, \
"task_nr": "1", "message_id": ""}))
else:
# If there is no generator script, we assume, that there is a static
# description.txt which shall be used.
c.send_email(self.sender_queue, user_email, user_id, "Task", "1", "", "")
def run(self):
while True:
#connect to sqlite database ...
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, \
self.name)
# get number of users
sql_cmd = "SELECT COUNT(UserId) FROM Users;"
curs.execute(sql_cmd)
res = curs.fetchone()
count = int(res[0])
nr_mails_sent = get_statcounter_value(curs, 'nr_mails_sent')
nr_mails_fetched = get_statcounter_value(curs, 'nr_mails_fetched')
nr_questions_received = get_statcounter_value(curs, \
'nr_questions_received')
#connect to sqlite database ...
curst, const = c.connect_to_db('semesterstats.db', \
self.logger_queue, self.name)
self.check_and_create_table(curst, 'NrUserStats')
self.check_and_create_table(curst, 'NrSendStats')
self.check_and_create_table(curst, 'NrReceiveStats')
self.check_and_create_table(curst, 'NrQuestionStats')
insert_stat_db(curst, const, 'NrUserStats', \
count)
insert_stat_db(curst, const, 'NrSendStats', \
nr_mails_sent)
insert_stat_db(curst, const, 'NrReceiveStats', \
nr_mails_fetched)
insert_stat_db(curst, const, 'NrQuestionStats', \
nr_questions_received)
plot_stat_graph(curst, 'NrUserStats', \
'nr_users.png')
plot_stat_graph(curst, 'NrSendStats', \
'nr_mails_sent.png')
plot_stat_graph(curst, 'NrReceiveStats', \
'nr_mails_received.png')
plot_stat_graph(curst, 'NrQuestionStats', \
'nr_questions_received.png')
cons.close()
const.close()
#time.sleep(3600*12) #updating the images every 12h is enough
time.sleep(360) #updating the images every 12h is enough
def increment_submission_nr(self, user_id, task_nr):
"""
Increment submission number for a specific user and task.
Return new number or 0 if no previous submission for this task exists.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
try:
data = {'UserId':user_id, 'TaskNr': task_nr}
sql_cmd = ("UPDATE UserTasks SET NrSubmissions = NrSubmissions+1 "
"WHERE UserId = :UserId AND TaskNr = :TaskNr")
curs.execute(sql_cmd, data)
cons.commit()
sql_cmd = ("SELECT NrSubmissions from UserTasks "
"WHERE UserId = :UserId AND TaskNr = :TaskNr")
curs.execute(sql_cmd, data)
res = curs.fetchone()
cons.close()
return int(res[0])
except:
cons.close()
return 0
def read_specialmessage(self, msgname):
"""
read a special message from the DB
"""
curc, conc = c.connect_to_db(self.dbs["course"], \
self.queues["logger"], \
self.name)
data = {'msgname': msgname}
sql_cmd = ("SELECT EventText FROM SpecialMessages "
"WHERE EventName = :msgname")
curc.execute(sql_cmd, data)
res = curc.fetchone()
conc.close()
if not res:
logmsg = ("Error reading the Specialmessage named {0} from the "
"database").format(msgname)
c.log_a_msg(self.queues["logger"], self.name, logmsg, "ERROR")
message = "Hi something went wrong. Please contact the course admin"
else:
message = str(res[0])
return message
def generate_tasks_list(self):
"""
Generate the email body for response to list tasks.
"""
curc, conc = c.connect_to_db(self.dbs["course"], \
self.queues["logger"], \
self.name)
msg = ("Hi,\n\nYou requested the list of tasks for the "
"course, here you go:\n\n")
sql_cmd = ("SELECT TaskNr, TaskName, TaskStart, TaskDeadline, Score "
"FROM TaskConfiguration")
curc.execute(sql_cmd)
rows = curc.fetchall()
for row in rows:
msg = msg + ("Task Number: {0}\nTask Name: {1}\nStart: {2}\n"
"Deadline: {3}\nScore: {4}\n") \
.format(row[0], row[1], row[2], row[3], row[4])
msg = msg + 40 * "-" + "\n\n"
msg += "\n\nSo long, and thanks for all the fish!"
conc.close()
return msg
def get_taskoperator_emails(self, task_nr):
"""
Get the email adresses of all configured operators for a specific task.
Return a list.
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue,
self.name)
data = {'TaskNr': task_nr}
sql_cmd = "SELECT TaskOperator FROM TaskConfiguration WHERE TaskNr = :TaskNr"
curc.execute(sql_cmd, data)
result = curc.fetchone()
if result != None:
result = str(result[0])
taskoperator_emails = [
email.strip() for email in result.split(',')
]
else:
taskoperator_emails = ""
conc.close()
return taskoperator_emails
def increment_submission_nr(self, user_id, task_nr):
"""
Increment submission number for a specific user and task.
Return new number or 0 if no previous submission for this task exists.
"""
curs, cons = c.connect_to_db(self.dbs["semester"],
self.queues["logger"], self.name)
try:
data = {'UserId': user_id, 'TaskNr': task_nr}
sql_cmd = ("UPDATE UserTasks SET NrSubmissions = NrSubmissions+1 "
"WHERE UserId = :UserId AND TaskNr = :TaskNr")
curs.execute(sql_cmd, data)
cons.commit()
sql_cmd = ("SELECT NrSubmissions from UserTasks "
"WHERE UserId = :UserId AND TaskNr = :TaskNr")
curs.execute(sql_cmd, data)
res = curs.fetchone()
cons.close()
return int(res[0])
except:
cons.close()
return 0
def get_scriptpath(self, task_nr):
"""
Get the path to the tester script for task task_nr.
Returns None if not proper config.
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"], self.name)
data = {'task_nr': task_nr}
sql_cmd = ("SELECT TaskName, TestExecutable FROM TaskConfiguration "
"WHERE TaskNr == :task_nr")
curc.execute(sql_cmd, data)
res = curc.fetchone()
if not res:
logmsg = ("Failed to fetch Configuration for TaskNr: {0} from the "
"database! Table TaskConfiguration corrupted?").format(task_nr)
c.log_a_msg(self.queues["logger"], self.name, logmsg, "ERROR")
scriptpath = None
else:
task_name = res[0]
tester_name = res[1]
scriptpath = self.tasks_dir + "/" + task_name + "/" + tester_name
conc.close()
return scriptpath
def init_dbs():
_, cur = connect_to_db(db="postgres")
for db in dbs:
cur.execute("DROP DATABASE IF EXISTS {}".format(db))
cur.execute("DROP USER IF EXISTS {}".format(user))
cur.execute("CREATE USER {} WITH ENCRYPTED PASSWORD 'abc123'".format(user))
for db in dbs:
_, cur = connect_to_db(db="postgres")
cur.execute("CREATE DATABASE {}".format(db))
conn, cur = connect_to_db(db=db)
cur.execute(schema)
cur.execute(priviledges)
def get_plugin_data(self, plugin_list):
"""
Get the parameters for the configured plugins as dict for each plugin
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"],
self.name)
plugin_data_by_plugin = {k: [] for k in plugin_list}
for plugin_name in plugin_list:
sql_cmd = ("SELECT ParameterName, Value FROM PluginData "
"WHERE PluginName = :PluginName ")
data = {"PluginName": plugin_name}
curc.execute(sql_cmd, data)
rows = curc.fetchall()
if not rows:
break # error will be handled in execute_plugins
for row in rows:
parameter_name = row["ParameterName"]
value = row["Value"]
plugin_data_by_plugin[plugin_name].append(
(parameter_name, value))
conc.close()
return plugin_data_by_plugin
def get_registration_deadline(self):
"""
Get the registration deadline datetime.
Return datetime, if not found return 1 hour from now.
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"],
self.name)
sql_cmd = ("SELECT Content FROM GeneralConfig " \
"WHERE ConfigItem == 'registration_deadline'")
curc.execute(sql_cmd)
deadline_string = str(curc.fetchone()[0])
conc.close()
format_string = '%Y-%m-%d %H:%M:%S'
if deadline_string != 'NULL':
return datetime.datetime.strptime(deadline_string, format_string)
else:
# there is no deadline set, just assume it is in 1h from now.
date = datetime.datetime.now() + datetime.timedelta(0, 3600)
logmsg = "No Registration Deadline found, assuming: " + str(date)
c.log_a_msg(self.queues["logger"], self.name, logmsg, "ERROR")
return date
def check_and_init_db_table(dbname, tablename, fields):
"""
Check if a table exists, if not create it with fields.
"""
cur, con = c.connect_to_db(dbname, logger_queue, "autosub.py")
data = {'name': tablename}
sql_cmd = ("SELECT name FROM sqlite_master "
"WHERE type == 'table' AND name = :name")
cur.execute(sql_cmd, data)
res = cur.fetchall()
if res:
logmsg = 'table ' + tablename + ' exists'
c.log_a_msg(logger_queue, "autosub.py", logmsg, "DEBUG")
return 0
logmsg = 'table ' + tablename + ' does not exist'
c.log_a_msg(logger_queue, "autosub.py", logmsg, "DEBUG")
data = {'fields': fields}
sql_cmd = "CREATE TABLE {0}({1})".format(tablename, fields)
cur.execute(sql_cmd)
con.commit()
con.close()
return 1
def check_and_set_first_successful(self, user_id, task_nr):
"""
the FirstSuccessful field is used to keep track on how many attempts
were needed to solve a task.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, \
self.name)
# check if allready previous successful submission, if not set it
data = {'user_id': user_id, 'task_nr': task_nr}
sql_cmd = "SELECT FirstSuccessful FROM UserTasks WHERE UserId = :user_id AND TaskNr = :task_nr;"
curs.execute(sql_cmd, data)
res = curs.fetchone()
if res[0] == None:
# get last submission number
sql_cmd = "SELECT NrSubmissions FROM UserTasks WHERE UserId = :user_id AND TaskNr = :task_nr;"
curs.execute(sql_cmd, data)
res = curs.fetchone()
submission_nr = int(res[0])
# set first successful
data['subnr'] = submission_nr
sql_cmd = "UPDATE UserTasks SET FirstSuccessful = :subnr WHERE UserId = :user_id AND TaskNr = :task_nr;"
curs.execute(sql_cmd, data)
cons.commit()
#update statistics only on FirstSuccessful
self.increment_db_taskcounter('NrSuccessful', \
str(task_nr))
self.increment_db_taskcounter('NrSubmissions', \
str(task_nr))
cons.close()
def check_and_init_db_table(dbname, tablename, fields):
"""
Check if a table exists, if not create it with fields.
"""
cur, con = c.connect_to_db(dbname, logger_queue, "autosub.py")
data = {'name': tablename}
sql_cmd = "SELECT name FROM sqlite_master WHERE type == 'table' AND name = :name"
cur.execute(sql_cmd, data)
res = cur.fetchall()
if res:
logmsg = 'table ' + tablename + ' exists'
c.log_a_msg(logger_queue, "autosub.py", logmsg, "DEBUG")
#TODO: in this case, we might want to check if one entry per task is already
# there, and add new empty entries in case a task does not have one. This is only
# a problem, if the number of tasks in the config file is changed AFTER the
# TaskStats table has been changed!
con.close()
return 0
else:
logmsg = 'table ' + tablename + ' does not exist'
c.log_a_msg(logger_queue, "autosub.py", logmsg, "DEBUG")
data = {'fields': fields}
sql_cmd = "CREATE TABLE {0}({1})".format(tablename, fields)
cur.execute(sql_cmd)
con.commit()
con.close()
return 1
def get_task_parameters(self, uid, tasknr):
"""
Look up the taskParmeters, that were generated from the generator for
a indididual task.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
data = {"tasknr": tasknr, "uid": uid}
sql_cmd = "SELECT TaskParameters FROM UserTasks WHERE TaskNr == :tasknr AND UserId == :uid"
curs.execute(sql_cmd, data)
params = curs.fetchone()[0]
cons.close()
return params
def set_general_config_param(self, configitem, content):
"""
Set a general config parameter in the database.
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, "loadtester")
data = {'ConfigItem' : configitem, 'Content': content}
sql_cmd = ("INSERT INTO GeneralConfig (ConfigItem, Content) "
"VALUES(:ConfigItem, :Content)")
curc.execute(sql_cmd, data)
conc.commit()
conc.close()
def set_general_config_param(configitem, content):
"""
Set a general config parameter in the database.
"""
curc, conc = c.connect_to_db(coursedb, logger_queue, "autosub.py")
data = {'ConfigItem': configitem, 'Content': content}
sql_cmd = ("INSERT OR REPLACE INTO GeneralConfig (ConfigItem, Content) "
"VALUES(:ConfigItem, :Content)")
curc.execute(sql_cmd, data)
conc.commit()
conc.close()
def read_specialmessage(self, msgname):
"""
read a special message from the DB
"""
curc, conc = c.connect_to_db(self.coursedb, \
self.logger_queue, \
self.name)
data = {'msgname': msgname}
sql_cmd = "SELECT EventText FROM SpecialMessages WHERE EventName = :msgname;"
curc.execute(sql_cmd, data)
res = curc.fetchone()
conc.close()
return str(res[0])
def read_specialmessage(self, msgname):
"""
read a special message from the DB
"""
curc, conc = c.connect_to_db(self.coursedb, \
self.logger_queue, \
self.name)
data = {'msgname': msgname}
sql_cmd = "SELECT EventText FROM SpecialMessages WHERE EventName = :msgname;"
curc.execute(sql_cmd, data)
res = curc.fetchone()
conc.close()
return str(res[0])
def init_db_statvalue(self, countername, value):
"""
Add entries for the statistics counters, and initialize them to 0.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, "loadtester")
data = {'Name': countername, 'Value': str(value)}
sql_cmd = ("INSERT INTO StatCounters (CounterId, Name, Value) "
"VALUES(NULL, :Name, :Value)")
curs.execute(sql_cmd, data)
cons.commit()
cons.close()
def get_task_parameters(self, user_id, task_nr):
"""
Look up the taskParmeters, that were generated from the generator for
a indididual task.
"""
curs, cons = c.connect_to_db(self.dbs["semester"], self.queues["logger"], self.name)
data = {'task_nr': task_nr, 'user_id': user_id}
sql_cmd = ("SELECT TaskParameters FROM UserTasks "
"WHERE TaskNr == :task_nr AND UserId == :user_id")
curs.execute(sql_cmd, data)
params = curs.fetchone()[0]
cons.close()
return params
def get_challenge_mode(self):
"""
Get the configured challenge mode
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
sql_cmd = "SELECT Content FROM GeneralConfig WHERE ConfigItem = 'challenge_mode'"
curc.execute(sql_cmd)
challenge_mode = curc.fetchone()
conc.close()
return str(challenge_mode[0])
def get_configured_backend_interface(self, task_nr):
"""
Get the configured common file configured for the task
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"], self.name)
data = {'task_nr': task_nr}
sql_cmd = ("SELECT BackendInterfaceFile FROM TaskConfiguration "
"WHERE TaskNr == :task_nr")
curc.execute(sql_cmd, data)
common_file = curc.fetchone()[0]
conc.close()
return common_file
def get_challenge_mode(self):
"""
Get the configured challenge mode
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue,
self.name)
sql_cmd = "SELECT Content FROM GeneralConfig WHERE ConfigItem = 'challenge_mode'"
curc.execute(sql_cmd)
challenge_mode = curc.fetchone()
conc.close()
return str(challenge_mode[0])
def load_specialmessage_to_db(self, msgname):
"""
Load the SpecialMessages in the db.
"""
filecontent = "Dummy {0} Special Message".format(msgname)
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, "loadtest")
data = {'EventName': msgname, 'EventText': filecontent}
sql_cmd = ("INSERT INTO SpecialMessages (EventName, EventText) "
"VALUES(:EventName, :EventText)")
curc.execute(sql_cmd, data)
conc.commit()
conc.close()
def increment_db_taskcounter(self, countername, task_nr):
"""
the taskcounters are used to keep track for each task how many
users have solved that task successfully
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, \
self.name)
data = {'task_nr': task_nr}
# SET with parameter does not work --> string substitute here
sql_cmd = ("UPDATE TaskStats SET {0} = {0} + 1 "
"WHERE TaskId == :task_nr").format(countername)
curs.execute(sql_cmd, data)
cons.commit()
cons.close()
def get_admin_emails(self):
"""
Get the email adresses of all configured admins.
Return a list.
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"],
self.name)
sql_cmd = ("SELECT Content FROM GeneralConfig "
"WHERE ConfigItem == 'admin_email'")
curc.execute(sql_cmd)
result = curc.fetchone()[0]
return str(result)
def get_admin_emails(self):
"""
read e-mail adress(es) of adminstrator(s) from DB.
"""
curc, conc = c.connect_to_db(self.coursedb, \
self.logger_queue, \
self.name)
sql_cmd = "SELECT Content FROM GeneralConfig WHERE ConfigItem == 'admin_email'"
curc.execute(sql_cmd)
result = str(curc.fetchone()[0])
#split and put it in list
admin_emails = [email.strip() for email in result.split(',')]
conc.close()
return admin_emails
def get_admin_emails(self):
"""
read e-mail adress(es) of adminstrator(s) from DB.
"""
curc, conc = c.connect_to_db(self.coursedb, \
self.logger_queue, \
self.name)
sql_cmd = "SELECT Content FROM GeneralConfig WHERE ConfigItem == 'admin_email'"
curc.execute(sql_cmd)
result = str(curc.fetchone()[0])
#split and put it in list
admin_emails = [email.strip() for email in result.split(',')]
conc.close()
return admin_emails
def has_last_done(self, userid):
"""
Has the user a date in the LastDone field?
"""
curs, cons = c.connect_to_db(self.dbs["semester"], self.queues["logger"], \
self.name)
data = {'user_id': userid}
sql_cmd = ("SELECT UserId FROM Users "
"WHERE UserId == :user_id AND LastDone IS NOT NULL")
curs.execute(sql_cmd, data)
res = curs.fetchone()
last_done = (res != None)
cons.close()
return last_done
def get_taskoperator_emails(self, task_nr):
"""
Get the email adresses of all configured operators for a specific task.
Return a list.
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"],
self.name)
data = {'TaskNr': task_nr}
sql_cmd = ("SELECT TaskOperator FROM TaskConfiguration "
"WHERE TaskNr = :TaskNr")
curc.execute(sql_cmd, data)
result = curc.fetchone()[0]
return str(result)
def get_admin_emails(self):
"""
Get the email adresses of all configured admins.
Return a list.
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
sql_cmd = "SELECT Content FROM GeneralConfig WHERE ConfigItem == 'admin_email'"
curc.execute(sql_cmd)
result = str(curc.fetchone()[0])
admin_emails = [email.strip() for email in result.split(',')]
conc.close()
return admin_emails
def get_taskoperator_emails(self, task_nr):
"""
Get the email adresses of all configured operators for a specific task.
Return a list.
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
data = {'TaskNr': task_nr}
sql_cmd = "SELECT TaskOperator FROM TaskConfiguration WHERE TaskNr = :TaskNr"
curc.execute(sql_cmd, data)
result = str(curc.fetchone()[0])
taskoperator_emails = [email.strip() for email in result.split(',')]
conc.close()
return taskoperator_emails
def main():
parse_arguments()
# if neither plot by points or rank is set, only set plot by rank
if not args.by_rank and not args.by_points:
args.by_rank = True
conn = common.connect_to_db(args)
cur = conn.cursor()
if not os.path.exists(plot_path):
os.mkdir(plot_path)
plot_teams(cur)
cur.close()
conn.close()
def check_and_set_first_successful(self, user_id, task_nr):
"""
Set FirstSuccessful field of a UserTask, if not set, to last submission
"""
curs, cons = c.connect_to_db(self.dbs["semester"], self.queues["logger"], \
self.name)
# check if allready previous successful submission, if not set it
data = {'user_id': user_id, 'task_nr': task_nr}
sql_cmd = ("SELECT FirstSuccessful FROM UserTasks "
"WHERE UserId = :user_id AND TaskNr = :task_nr")
curs.execute(sql_cmd, data)
res = curs.fetchone()
if res[0] is None:
# get last submission number
sql_cmd = ("SELECT NrSubmissions FROM UserTasks "
"WHERE UserId = :user_id AND TaskNr = :task_nr")
curs.execute(sql_cmd, data)
res = curs.fetchone()
submission_nr = int(res[0])
# set first successful
data['subnr'] = submission_nr
sql_cmd = ("UPDATE UserTasks SET FirstSuccessful = :subnr "
"WHERE UserId = :user_id AND TaskNr = :task_nr")
curs.execute(sql_cmd, data)
cons.commit()
#update statistics only on FirstSuccessful
self.increment_db_taskcounter('NrSuccessful', \
str(task_nr))
self.increment_db_taskcounter('NrSubmissions', \
str(task_nr))
# insert into SucessfulTasks, if it is already existent ignore
data = {'user_id': user_id, 'task_nr': task_nr}
sql_cmd = (
"INSERT OR IGNORE INTO SuccessfulTasks (UserId, TaskNr) "
"VALUES (:user_id, :task_nr)")
curs.execute(sql_cmd, data)
cons.commit()
cons.close()
def load_specialmessage_to_db(env, msgname, filename):
"""
Load the SpecialMessages in the db.
"""
template = env.get_template(filename)
data = {'course_name' : course_name, "submission_email" : smtpmail,
'allow_requests': allow_requests}
template = template.render(data)
curc, conc = c.connect_to_db(coursedb, logger_queue, "autosub.py")
data = {'EventName': msgname, 'EventText': template}
sql_cmd = ("INSERT OR REPLACE INTO SpecialMessages (EventName, EventText) "
"VALUES(:EventName, :EventText)")
curc.execute(sql_cmd, data)
conc.commit()
conc.close()
def delete_usertask(self, user_id, task_nr):
"""
Delete existing usertask and its structures
"""
# delete db entry
curs, cons = c.connect_to_db(self.dbs["semester"],
self.queues["logger"], self.name)
data = {"user_id": user_id, "task_nr": task_nr}
sql_cmd = ("DELETE FROM UserTasks "
"WHERE TaskNr == :task_nr AND UserId == :user_id")
# remove directory
usertask_dir = 'users/' + str(user_id) + "/Task" + str(task_nr)
shutil.rmtree(usertask_dir)
curs.execute(sql_cmd, data)
cons.commit()
cons.close()
def a_status_is_requested(self, user_email, messageid):
"""
Process a question about a user status.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
data = {'Email': user_email}
sql_cmd = "SELECT UserId, CurrentTask FROM Users WHERE Email == :Email"
curs.execute(sql_cmd, data)
res = curs.fetchone()
user_id = res[0]
current_task = res[1]
cons.close()
c.send_email(self.sender_queue, user_email, user_id, "Status", current_task, \
"", messageid)
c.increment_db_statcounter(self.semesterdb, 'nr_status_requests', \
self.logger_queue, self.name)
def get_archive_dir(self):
"""
Get the name of the directory processed mails shall be moved to
on the IMAP server
"""
curc, conc = c.connect_to_db(self.dbs["course"], self.queues["logger"],
self.name)
data = {'config_item': 'archive_dir'}
sql_cmd = ("SELECT Content FROM GeneralConfig "
"WHERE ConfigItem= :config_item")
curc.execute(sql_cmd, data)
res = curc.fetchone()
archive_dir = str(res[0])
conc.close()
return archive_dir
def get_registration_deadline(self):
"""
Get the registration deadline datetime.
Return datetime, if not found return 1 hour from now.
"""
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
sql_cmd = ("SELECT Content FROM GeneralConfig " \
"WHERE ConfigItem == 'registration_deadline'")
curc.execute(sql_cmd)
deadline_string = str(curc.fetchone()[0])
conc.close()
format_string = '%Y-%m-%d %H:%M:%S'
if deadline_string != 'NULL':
return datetime.datetime.strptime(deadline_string, format_string)
else:
# there is no deadline set, just assume it is in 1h from now.
return datetime.datetime.now() + datetime.timedelta(0, 3600)
def clean_generalconfig(self):
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, "loadtester")
try:
sqlcmd = "DROP TABLE GeneralConfig;"
curc.execute(sqlcmd)
conc.commit()
except:
pass
sqlcmd = ("CREATE TABLE GeneralConfig ("
"ConfigItem Text PRIMARY KEY, Content TEXT);")
curc.execute(sqlcmd)
conc.commit()
conc.close()
self.set_general_config_param('num_tasks', "42")
self.set_general_config_param('registration_deadline', 'NULL')
self.set_general_config_param('archive_dir', 'archive/')
self.set_general_config_param('admin_email', '')
self.set_general_config_param('challenge_mode', "normal")
self.set_general_config_param('course_name', "load_test")
def check_if_whitelisted(self, user_email):
"""
Check if the given e-mail address is in the whitelist.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
data = {'Email': user_email}
sql_cmd = "SELECT * FROM WhiteList WHERE Email == :Email"
curs.execute(sql_cmd, data)
res = curs.fetchone()
cons.close()
if res != None:
return 1
else:
logmsg = "Got Mail from a User not on the WhiteList: " + user_email
c.log_a_msg(self.logger_queue, self.name, logmsg, "Warning")
c.increment_db_statcounter(self.semesterdb, 'nr_non_registered', \
self.logger_queue, self.name)
return 0
def loop_code(self):
"""
The code run in the while True loop of the mail fetcher thread.
"""
m = self.connect_to_imapserver()
if m != 0:
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
items = self.fetch_new_emails(m)
# iterate over all new e-mails and take action according to the structure
# of the subject line
for emailid in items:
c.increment_db_statcounter(self.semesterdb, 'nr_mails_fetched', \
self.logger_queue, self.name)
# fetching the mail, "`(RFC822)`" means "get the whole stuff", but you
# can ask for headers only, etc
resp, data = m.fetch(emailid, "(RFC822)")
# parsing the mail content to get a mail object
mail = email.message_from_bytes(data[0][1])
mail_subject = str(mail['subject'])
from_header = str(mail['From'])
split_header = str(from_header).split("<")
user_name = split_header[0]
try:
user_email = str(split_header[1].split(">")[0])
except:
user_email = str(mail['From'])
messageid = mail.get('Message-ID')
whitelisted = self.check_if_whitelisted(user_email)
if whitelisted:
data = {'Email': user_email}
sql_cmd = "SELECT UserId FROM Users WHERE Email = :Email"
curs.execute(sql_cmd, data)
res = curs.fetchall()
if res:
logmsg = "Got mail from an already known user!"
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
#TODO: Does sending a mail "Result bla" without number crash this?
if re.search('[Rr][Ee][Ss][Uu][Ll][Tt]', mail_subject):
searchObj = re.search('[0-9]+', mail_subject, )
if int(searchObj.group()) <= c.get_num_tasks(self.coursedb, \
self.logger_queue, self.name):
logmsg = "Processing a Result, UserId:{0} TaskNr:{1}"\
.format(user_email, searchObj.group())
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
self.take_new_results(user_email, searchObj.group(), \
mail, messageid)
else:
logmsg = ("Given Task number is higher than actual Number"
"of Tasks!")
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
c.send_email(self.sender_queue, user_email, "", \
"InvalidTask", "", "", messageid)
elif re.search('[Qq][Uu][Ee][Ss][Tt][Ii][Oo][Nn]', mail_subject):
self.a_question_was_asked(user_email, mail, messageid)
elif re.search('[Ss][Tt][Aa][Tt][Uu][Ss]', mail_subject):
self.a_status_is_requested(user_email, messageid)
else:
logmsg = ("Got a kind of message I do not understand. "
"Sending a usage mail...")
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
c.send_email(self.sender_queue, user_email, "", "Usage", "", \
"", messageid)
else:
reg_deadline = self.get_registration_deadline()
if reg_deadline > datetime.datetime.now():
self.add_new_user(user_name, user_email)
c.send_email(self.sender_queue, user_email, "", "Welcome", \
"", "", messageid)
else:
c.send_email(self.sender_queue, user_email, "", "RegOver", \
"", "", messageid)
else:
c.send_email(self.sender_queue, user_email, "", "NotAllowed", \
"", "", messageid)
try:
m.close()
except imaplib.IMAP4.abort:
logmsg = ("Closing connection to server was aborted "
"(probably a server-side problem). Trying to connect again ...")
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
#m.close()
except imaplib.IMAP4.error:
logmsg = ("Got an error when trying to connect to the imap server."
"Trying to connect again ...")
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
except:
logmsg = ("Got an unknown exception when trying to connect to the "
"imap server. Trying to connect again ...")
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
finally:
logmsg = "closed connection to imapserver"
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
# check if messages have been handled and need to be archived now
try:
next_send_msg = self.arch_queue.get(False)
except:
next_send_msg = 'NONE'
if next_send_msg != 'NONE':
c.log_a_msg(self.logger_queue, self.name, "moving a message!!!!!!!", \
"INFO")
m = self.connect_to_imapserver()
for next_msg in next_send_msg:
email_ids = self.fetch_all_emails(m)
for emailid in email_ids:
typ, msg_data = m.fetch(str(int(emailid)), "(BODY[HEADER])")
mail = email.message_from_bytes(msg_data[0][1])
if mail['Message-ID'] == next_send_msg.get('mid'):
logmsg = "Moving Message with ID: {0}"\
.format(mail['Message-ID'])
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
resp, data = m.fetch(emailid, "(UID)")
pattern_uid = re.compile('\d+ \(UID (?P<uid>\d+)\)')
match = pattern_uid.match(str(data[0]).split("'")[1])
msg_uid = match.group('uid')
result = m.uid('COPY', msg_uid, 'archive_vels')
if result[0] == 'OK':
mov, data = m.uid('STORE', msg_uid, '+FLAGS', \
'(\Deleted)')
m.expunge()
break
# m==0 is only possible in test-code (e.g. load_test.py)
if m != 0:
m.logout()
cons.close()
time.sleep(self.poll_period)
def run(self):
"""
Thread code for the worker thread.
"""
logmsg = "Starting " + self.name
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
while True:
logmsg = self.name + ": waiting for a new job."
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
nextjob = self.job_queue.get(True)
if nextjob:
tasknr = nextjob.get("taskNr")
user_id = nextjob.get("UserId")
user_email = nextjob.get("UserEmail")
message_id = nextjob.get("MessageId")
logmsg = self.name + "got a new job: {0} from the user with id: {1}".format(str(tasknr), str(user_id))
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
# check if there is a test executable configured in the
# database -- if not fall back on static test script.
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
try:
data = {"tasknr": tasknr}
sql_cmd = "SELECT TestExecutable FROM TaskConfiguration WHERE TaskNr == :tasknr"
curc.execute(sql_cmd, data)
testname = curc.fetchone()
except:
logmsg = "Failed to fetch TestExecutable for TaskNr: {0}".format(tasknr)
logmsg = logmsg + " from the Database! Table TaskConfiguration corrupted?"
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
if testname != None:
try:
data = {"tasknr": tasknr}
sql_cmd = "SELECT PathToTask FROM TaskConfiguration WHERE TaskNr == :tasknr"
curc.execute(sql_cmd, data)
path = curc.fetchone()
scriptpath = str(path[0]) + "/" + str(testname[0])
except: # if a testname was given, then a Path should be
# there as well!
logmsg = "Failed to fetch Path to Tasknr: {0}".format(tasknr)
logmsg = "{0} from the Database! Table TaskConfiguration corrupted?".format(logmsg)
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
else: # in case no testname was given, we fall back to the
# static directory structure
scriptpath = "tasks/task" + str(tasknr) + "/./tests.sh"
conc.close()
# get the task parameters
task_params = self.get_task_parameters(user_id, tasknr)
# run the test script
logmsg = "Running test script: " + scriptpath
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
command = '{0} {1} {2} "{3}" >> autosub.stdout 2>>autosub.stderr'.format(
scriptpath, user_id, tasknr, task_params
)
test_res = os.system(command)
if test_res: # not 0 returned
logmsg = "Test failed! User: {0} Task: {1}".format(user_id, tasknr)
logmsg = logmsg + " return value:" + str(test_res)
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
c.send_email(
self.sender_queue, str(user_email), str(user_id), "Failed", str(tasknr), "", str(message_id)
)
if test_res == 512: # Need to read up on this but os.system() returns
# 256 when the script returns 1 and 512 when the script returns 2, 768 when 3!
logmsg = "SecAlert: This test failed due to probable attack by user!"
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
c.send_email(
self.sender_queue,
str(user_email),
str(user_id),
"SecAlert",
str(tasknr),
"",
str(message_id),
)
elif test_res == 768:
logmsg = "TaskAlert: This test for TaskNr {0} and User {1} failed due an error with task/testbench analyzation!".format(
tasknr, user_id
)
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
c.send_email(
self.sender_queue,
str(user_email),
str(user_id),
"TaskAlert",
str(tasknr),
"",
str(message_id),
)
else: # 0 returned
logmsg = "Test succeeded! User: {0} Task: {1}".format(user_id, tasknr)
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
# Notify, the user that the submission was successful
c.send_email(self.sender_queue, str(user_email), str(user_id), "Success", str(tasknr), "", "")
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
currenttask = int(c.user_get_current_task(self.semesterdb, user_id, self.logger_queue, self.name))
# Next, a new Task is generated -- but only if a new task
# exists AND if a generator script exists (otherwise
# static task description is assumed, AND if users current
# task < the task that shall be generated (no Task has yet
# been generated for this user yet).
if currenttask < int(tasknr) + 1:
try:
data = {"tasknr": str(int(tasknr) + 1)}
sql_cmd = "SELECT GeneratorExecutable FROM TaskConfiguration WHERE TaskNr == :tasknr"
curc.execute(sql_cmd, data)
res = curc.fetchone()
except:
logmsg = "Failed to fetch Generator Script for Tasknr: {0}".format(tasknr)
logmsg = logmsg + "from the Database! Table TaskConfiguration corrupted?"
c.log_a_msg(self.logger_queue, self.name, logmsg, "ERROR")
finally:
conc.close()
task_start = c.get_task_starttime(self.coursedb, int(tasknr) + 1, self.logger_queue, self.name)
if task_start < datetime.datetime.now():
if res != None: # generator script for this task configured?
logmsg = "Calling Generator Script: " + str(res[0])
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
logmsg = "UserID {0}, UserEmail {1}".format(user_id, user_email)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
self.gen_queue.put(
dict(
{
"user_id": str(user_id),
"user_email": str(user_email),
"task_nr": str(int(tasknr) + 1),
"messageid": "",
}
)
)
else:
c.send_email(
self.sender_queue,
str(user_email),
str(user_id),
"Task",
str(int(tasknr) + 1),
"",
str(message_id),
)
else:
c.send_email(
self.sender_queue,
str(user_email),
str(user_id),
"CurLast",
str(int(tasknr) + 1),
"",
str(message_id),
)
def handle_next_mail(self):
"""
parse the subject/content of a mail and take appropriate action.
"""
#blocking wait on sender_queue
next_send_msg = self.sender_queue.get(True)
tasknr = str(next_send_msg.get('Task'))
messageid = str(next_send_msg.get('MessageId'))
uid = str(next_send_msg.get('UserId'))
recipient = str(next_send_msg.get('recipient'))
message_type = str(next_send_msg.get('message_type'))
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, \
self.name)
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, \
self.name)
attachments = []
# prepare fields for the e-mail
msg = MIMEMultipart()
msg['From'] = self.mail_user
msg['To'] = recipient
logmsg = "RECIPIENT: " + recipient
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
msg['Date'] = formatdate(localtime=True)
if message_type == "Task":
logmsg = "Task in send_queue: " + str(next_send_msg)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
numtasks = c.get_num_tasks(self.coursedb, \
self.logger_queue, self.name)
ctasknr = c.user_get_current_task(self.semesterdb, uid, \
self.logger_queue, self.name)
if numtasks+1 == int(tasknr): # last task solved!
msg['Subject'] = "Congratulations!"
message_text = self.read_specialmessage('CONGRATS')
if int(tasknr)-1 == int(ctasknr):
# statistics shall only be udated on the first
# successful submission
c.user_set_current_task(self.semesterdb, tasknr, uid, \
self.logger_queue, self.name)
self.increment_db_taskcounter(curs, cons, 'NrSuccessful', \
str(int(tasknr)-1))
self.increment_db_taskcounter(curs, cons, 'NrSubmissions', \
str(int(tasknr)-1))
self.check_and_set_last_done(curs, cons, uid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
else: # at least one more task to do: send out the description
# only send the task description, after the first
# successful submission
if int(tasknr)-1 <= int(ctasknr) or int(ctasknr) == 1:
msg['Subject'] = "Description Task" + str(tasknr)
dl_text = "\nDeadline for this Task: {0}\n".format(c.get_task_deadline(self.coursedb, tasknr, self.logger_queue, self.name))
data = {'tasknr': str(tasknr)}
sql_cmd = "SELECT PathToTask FROM TaskConfiguration WHERE TaskNr == :tasknr;"
curc.execute(sql_cmd, data)
paths = curc.fetchone()
if not paths:
logmsg = "It seems, the Path to Task {0} is not configured.".format(tasknr)
c.log_a_msg(self.logger_queue, self.name, \
logmsg, "WARNING")
message_text = "Sorry, but something went wrong... probably misconfiguration or missing configuration of Task {0}".format(tasknr)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), \
message_type)
else:
path_to_task = str(paths[0])
path_to_msg = path_to_task + "/description.txt"
message_text = self.read_text_file(path_to_msg) \
+ dl_text
data = {'tasknr': str(tasknr), 'uid': uid}
sql_cmd = "SELECT TaskAttachments FROM UserTasks WHERE TaskNr == :tasknr AND UserId == :uid;"
curs.execute(sql_cmd, data)
res = curs.fetchone()
logmsg = "got the following attachments: " + str(res)
c.log_a_msg(self.logger_queue, self.name, logmsg, \
"DEBUG")
if res:
attachments = str(res[0]).split()
# statistics shall only be udated on the first
# succesful submission
c.user_set_current_task(self.semesterdb, tasknr, uid, \
self.logger_queue, self.name)
self.increment_db_taskcounter(curs, cons, \
'NrSuccessful', \
str(int(tasknr)-1))
self.increment_db_taskcounter(curs, cons, \
'NrSubmissions', \
str(int(tasknr)-1))
msg = self.assemble_email(msg, message_text, \
attachments)
self.send_out_email(recipient, msg.as_string(), \
message_type)
self.backup_message(messageid)
elif message_type == "Failed":
self.increment_db_taskcounter(curs, cons, 'NrSubmissions', tasknr)
path_to_msg = "users/{0}/Task{1}".format(uid, tasknr)
error_msg = self.read_text_file("{0}/error_msg".format(path_to_msg))
msg['Subject'] = "Task" + tasknr + ": submission rejected"
message_text = "Error report:\n\n""" + error_msg
reply_attachments = []
try:
logmsg = "searching attachments in: {0}/error_attachments".format(path_to_msg)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
ats = os.listdir("{0}/error_attachments".format(path_to_msg))
logmsg = "got the following attachments: {0}".format(ats)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
for next_attachment in ats:
reply_attachments.append("{0}/error_attachments/{1}".format(path_to_msg, next_attachment))
except:
logmsg = "no attachments for failed task."
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
msg = self.assemble_email(msg, message_text, reply_attachments)
self.send_out_email(recipient, msg.as_string(), message_type)
self.backup_message(messageid)
elif message_type == "SecAlert":
admin_mails = self.get_admin_emails()
for admin_mail in admin_mails:
msg['To'] = admin_mail
path_to_msg = "users/"+ uid + "/Task" + tasknr + "/error_msg"
error_msg = self.read_text_file(path_to_msg)
msg['Subject'] = "Autosub Security Alert User:"******"Error report:\n\n""" + error_msg
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(admin_mail, msg.as_string(), message_type)
self.backup_message(messageid)
elif message_type == "TaskAlert":
admin_mails = self.get_admin_emails()
for admin_mail in admin_mails:
msg['To'] = admin_mail
msg['Subject'] = "Autosub Task Error Alert Task " \
+ tasknr + " User " + uid
message_text = "Something went wrong with task/testbench analyzation for Task " + tasknr +" and User " + uid + " . Either the entity or testbench analyzation threw an error."
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(admin_mail, msg.as_string(), message_type)
self.backup_message(messageid)
elif message_type == "Success":
msg['Subject'] = "Task " + tasknr + " submitted successfully"
message_text = "Congratulations!"
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
#set first done if not set yet
self.check_and_set_first_successful(curs, cons, uid, tasknr)
# no backup of message -- this is done after the new task
# description was sent to the user!
elif message_type == "Status":
msg['Subject'] = "Your Current Status"
message_text = self.generate_status_update(curs, recipient)
numtasks = c.get_num_tasks(self.coursedb, self.logger_queue, \
self.name)
if int(numtasks) >= int(tasknr):
#also attach current task
data = {'tasknr': str(tasknr), 'uid': uid}
sql_cmd = "SELECT TaskAttachments FROM UserTasks WHERE TaskNr == :tasknr AND UserId == :uid;"
curs.execute(sql_cmd, data)
res = curs.fetchone()
logmsg = "got the following attachments: " + str(res)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
if res:
attachments = str(res[0]).split()
msg = self.assemble_email(msg, message_text, attachments)
self.send_out_email(recipient, msg.as_string(), message_type)
else:
msg = self.assemble_email(msg, message_text, attachments)
self.send_out_email(recipient, msg.as_string(), message_type)
self.backup_message(messageid)
elif message_type == "InvalidTask":
msg['Subject'] = "Invalid Task Number"
message_text = self.read_specialmessage('INVALID')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "CurLast":
# we still need to increment the users task counter!
c.user_set_current_task(self.semesterdb, tasknr, uid, \
self.logger_queue, self.name)
msg['Subject'] = "Task{0} is not available yet".format(str(tasknr))
message_text = self.read_specialmessage('CURLAST')
message_text = "{0}\n\nThe Task is currently scheduled for: {1}".format(message_text, \
c.get_task_starttime(self.coursedb, str(tasknr), \
self.logger_queue, self.name))
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "DeadTask":
msg['Subject'] = "Deadline for Task{0} has passed.".format(str(tasknr))
message_text = self.read_specialmessage('DEADTASK')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "Usage":
msg['Subject'] = "Autosub Usage"
message_text = self.read_specialmessage('USAGE')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "Question":
msg['Subject'] = "Question received"
message_text = self.read_specialmessage('QUESTION')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "QFwd":
orig_mail = next_send_msg.get('Body')
msg['Subject'] = "Question from " + orig_mail['from']
if orig_mail.get_content_maintype() == 'multipart':
part = orig_mail.get_payload(0)
mbody = part.get_payload()
message_text = "Original subject: " + orig_mail['subject'] + "\n\nNote: This e-mail contained attachments which have been removed!\n"
message_text = "{0}\n\nOriginal body:\n{1}".format(message_text, mbody)
else:
mbody = orig_mail.get_payload()
message_text = "Original subject: " + orig_mail['subject'] + \
"\n\nOriginal body:\n" + str(mbody)
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "Welcome":
msg['Subject'] = "Welcome!"
message_text = self.read_specialmessage('WELCOME')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "RegOver":
msg['Subject'] = "Registration Deadline has passed"
message_text = self.read_specialmessage('REGOVER')
self.backup_message(messageid)
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
elif message_type == "NotAllowed":
msg['Subject'] = "Registration Not Successful."
message_text = self.read_specialmessage('NOTALLOWED')
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
self.backup_message(messageid)
else:
c.log_a_msg(self.logger_queue, self.name, \
"Unkown Message Type in the sender_queue!", "ERROR")
msg = self.assemble_email(msg, message_text, '')
self.send_out_email(recipient, msg.as_string(), message_type)
self.backup_message(messageid)
cons.close()
conc.close()
def activator_loop(self):
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, \
self.name)
# first we need to know, for which tasks, the message has already
# been sent out
sql_cmd = "SELECT * FROM TaskConfiguration WHERE TaskActive==0;"
curc.execute(sql_cmd)
res = curc.fetchone()
while res != None:
tasknr = res[0]
logmsg = "Task {0} is still inactive".format(str(tasknr))
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
# check if a tasks start time has come
task_starttime = datetime.datetime.strptime(res[1], c.format_string)
if task_starttime < datetime.datetime.now():
# first, let's set the task active!
data = {'tasknr': tasknr}
sql_cmd = "UPDATE TaskConfiguration SET TaskActive = 1 WHERE TaskNr == :tasknr;"
curc.execute(sql_cmd, data)
conc.commit()
# next, check if any users are waiting for that task
curs, cons = c.connect_to_db(self.semesterdb, \
self.logger_queue, self.name)
data = {'tasknr': tasknr}
sqlcmd = "SELECT * FROM Users WHERE CurrentTask == :tasknr;"
curs.execute(sqlcmd, data)
nextuser = curs.fetchone()
while nextuser != None:
logmsg = "The next example is sent to User {0} now.".format(tasknr)
c.log_a_msg(self.logger_queue, self.name, logmsg, "INFO")
uid = nextuser[0]
user_email = nextuser[2]
try:
data = {'tasknr': tasknr}
sql_cmd = "SELECT GeneratorExecutable FROM TaskConfiguration WHERE TaskNr == :tasknr;"
curc.execute(sql_cmd, data)
res = curc.fetchone()
except:
logmsg = "Failed to fetch Generator Script for Tasknr: "+ str(tasknr)
logmsg = logmsg + "from the Database! Table TaskConfiguration corrupted?"
c.log_a_msg(self.logger_queue, self.name, \
logmsg, "ERROR")
if res != None:
logmsg = "Calling Generator Script: {0}".format(res[0])
c.log_a_msg(self.logger_queue, self.name, \
logmsg, "DEBUG")
logmsg = "UserID {0}, UserEmail{1}".format(uid, \
user_email)
c.log_a_msg(self.logger_queue, self.name, \
logmsg, "DEBUG")
self.gen_queue.put(dict({"UserId": str(uid), \
"UserEmail": str(user_email), \
"TaskNr": str(tasknr), \
"MessageId": ""}))
else:
c.send_email(self.sender_queue, str(user_email), \
str(uid), "Task", str(tasknr), "", "")
nextuser = curs.fetchone()
cons.close()
res = curc.fetchone()
conc.close()
def test_load_code(self):
threadID = 2 # LOGGER IS NUMBER 1 !!!
queueSize = 500
poll_period = 5
numThreads = 8
worker_t = []
job_queue = queue.Queue(queueSize)
sender_queue = queue.Queue(queueSize)
gen_queue = queue.Queue(queueSize)
arch_queue = queue.Queue(queueSize)
self.email_queue = queue.Queue(20000) # used by the mock-up sender function instead of smtp
self.clean_whitelist()
self.clean_users()
self.clean_usertasks()
self.clean_statcounters()
self.clean_taskstats()
self.clean_generalconfig()
self.clean_specialmsg()
self.clean_taskconfig()
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, "loadtester")
this_time_yesterday = str(datetime.datetime.now() - datetime.timedelta(1)).split('.')[0]
this_time_tomorrow = str(datetime.datetime.now() + datetime.timedelta(1)).split('.')[0]
self.add_task(curc, conc, 1, this_time_yesterday, this_time_tomorrow, \
'tasks/implementation/VHDL/gates', 'generator.sh', \
'tester.sh', '5', '[email protected]', '1')
self.add_task(curc, conc, 2, this_time_yesterday, this_time_tomorrow, \
'tasks/implementation/VHDL/fsm', 'generator.sh', \
'tester.sh', '5', '[email protected]', '1')
conc.close()
for i in range(1, self.numusers+1):
self.insert_email_to_whitelist("testuser{0}@sometestdomain.abc".format(str(i)))
with mock.patch.multiple('fetcher.mailFetcher',
connect_to_imapserver=self.mock_connect_to_imapserver,
fetch_new_emails=self.mock_fetch_new_emails,
fetch_all_emails=self.mock_fetch_all_emails):
with mock.patch.multiple("imaplib.IMAP4",
fetch=self.mock_fetch,
close=self.mock_close):
with mock.patch("sender.MailSender.send_out_email", self.mock_send_out_email):
while threadID <= numThreads + 1:
tName = "Worker" + str(threadID-1)
t = worker.Worker(tName, job_queue, gen_queue, \
sender_queue, self.logger_queue, \
self.coursedb, self.semesterdb)
t.daemon = True
t.start()
worker_t.append(t)
threadID += 1
sender_t = sender.MailSender("sender", \
sender_queue, \
"[email protected]", \
"autosub_user", \
"autosub_passwd", \
"smtpserver", \
self.logger_queue, \
arch_queue, \
self.coursedb, \
self.semesterdb)
sender_t.daemon = True
sender_t.start()
threadID += 1
fetcher_t = fetcher.mailFetcher(threadID, "fetcher", \
job_queue, sender_queue, \
gen_queue, "autosub_user", \
"autosub_passwd", \
"imapserver", \
self.logger_queue, \
arch_queue, poll_period, \
self.coursedb, \
self.semesterdb)
fetcher_t.daemon = True
fetcher_t.start()
threadID += 1
generator_t = generator.taskGenerator(threadID, \
"generator", \
gen_queue, \
sender_queue, \
self.logger_queue, \
self.coursedb, \
self.semesterdb, \
"*****@*****.**")
generator_t.daemon = True
generator_t.start()
threadID += 1
t1 = datetime.datetime.now()
# There first test case is set above in the setup routine: self.testcase = "b'10'"
NrEntries = 0
while NrEntries < self.numusers:
curs, cons = c.connect_to_db(self.semesterdb, \
self.logger_queue, "testcode")
sqlcmd = "SELECT count(*) FROM UserTasks;"
curs.execute(sqlcmd)
NrEntries = int(curs.fetchone()[0])
cons.close()
#print(NrEntries)
t2 = datetime.datetime.now()
tc = "b'84'"
config = CP.ConfigParser()
config.readfp(open('tests/loadtest_testcases.cfg'))
testparam = eval(str(config.get(tc, 'generatorstring')))
curs, cons = c.connect_to_db(self.semesterdb, \
self.logger_queue, "testcode")
sqlcmd = ("UPDATE UserTasks SET "
"TaskParameters = '162159553761823' "
"WHERE TaskNr==1;").format(testparam)
curs.execute(sqlcmd)
cons.commit()
cons.close()
self.testcase = tc
while NrEntries < self.numusers:
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, "testcode")
sqlcmd = "SELECT count(*) FROM UserTasks;"
curs.execute(sqlcmd)
NrEntries = int(curs.fetchone()[0])
cons.close()
t3 = datetime.datetime.now()
delta1 = t2 - t1
delta2 = t3 - t2
print ("Duration of Registration Test: {0} s {1} us".format(delta1.seconds, delta1.microseconds))
print ("Duration of Result Test: {0} s {1} us".format(delta2.seconds, delta2.microseconds))
def take_new_results(self, user_email, task_nr, mail, messageid):
"""
Store a new submisson in the user's directory structure.
"""
curs, cons = c.connect_to_db(self.semesterdb, self.logger_queue, self.name)
data = {'Email': user_email}
sql_cmd = "SELECT UserId FROM Users WHERE Email = :Email"
curs.execute(sql_cmd, data)
res = curs.fetchone()
user_id = res[0]
deadline = c.get_task_deadline(self.coursedb, task_nr, self.logger_queue, \
self.name)
curtask = c.user_get_current_task(self.semesterdb, user_id, self.logger_queue, \
self.name)
if deadline < datetime.datetime.now():
#deadline has passed!
c.send_email(self.sender_queue, user_email, "", "DeadTask", str(task_nr), \
"", messageid)
elif curtask < task_nr:
#user is trying to submit a solution to a task although an earlier task
# was not solved.
c.send_email(self.sender_queue, user_email, "", "InvalidTask", str(task_nr), \
"", messageid)
else:
# get the user's UserId
data = {'Email': user_email}
sql_cmd = "SELECT UserId FROM Users WHERE Email = :Email"
curs.execute(sql_cmd, data)
res = curs.fetchone()
user_id = res[0]
# increment the submission_nr for the user
submission_nr = self.increment_submission_nr(int(user_id), int(task_nr))
#create a directory for putting his submission in:
detach_dir = 'users/{0}/Task{1}'.format(user_id, task_nr)
ts = datetime.datetime.now()
submission_dir = "/Submission{0}_{1}{2}{3}_{4}{5}{6}{7}".format(\
submission_nr, ts.year, ts.month, ts.day, ts.hour, ts.minute, \
ts.second, ts.microsecond)
current_dir = detach_dir + submission_dir
c.check_dir_mkdir(current_dir, self.logger_queue, self.name)
# use walk to create a generator, iterate on the parts and forget
# about the recursive headache
for part in mail.walk():
# multipart are just containers, so skip them
if part.get_content_maintype() == 'multipart':
continue
# is this part an attachment ?
if part.get('Content-Disposition') is None:
continue
filename = part.get_filename()
counter = 1
# if there is no filename, create one with a counter to avoid duplicates
if not filename:
filename = 'part-%03d%s' % (counter, 'bin')
counter += 1
att_path = os.path.join(current_dir, filename)
#Check if its already there
if not os.path.isfile(att_path):
# finally write the stuff
fp = open(att_path, 'wb')
fp.write(part.get_payload(decode=True))
fp.close()
cmd = "rm " + detach_dir + "/*" + " 2> /dev/null"
os.system(cmd)
cmd = "cp -R " + current_dir + "/* " + detach_dir + " > /dev/null"
os.system(cmd)
# Next, let's handle the task that shall be checked, and send a job
# request to the job_queue. The workers can then get it from there.
self.job_queue.put(dict({"UserId": user_id, "UserEmail": user_email, \
"message_type": "Task", "taskNr": task_nr, \
"MessageId": messageid}))
cons.close()
def generator_loop(self):
"""
Loop code for the generator thread
"""
#blocking wait on gen_queue
next_gen_msg = self.gen_queue.get(True)
logmsg = "gen_queue content:" + str(next_gen_msg)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
task_nr = next_gen_msg.get('task_nr')
user_id = next_gen_msg.get('user_id')
user_email = next_gen_msg.get('user_email')
messageid = next_gen_msg.get('messageid')
#generate the directory for the task
task_dir = 'users/' + str(user_id) + "/Task"+str(task_nr)
c.check_dir_mkdir(task_dir, self.logger_queue, self.name)
#generate the task description
desc_dir = task_dir + "/desc"
c.check_dir_mkdir(desc_dir, self.logger_queue, self.name)
# check if there is a generator executable configured in the database
# if not fall back on static generator script.
curc, conc = c.connect_to_db(self.coursedb, self.logger_queue, self.name)
data = {'TaskNr': task_nr}
sql_cmd = ("SELECT GeneratorExecutable FROM TaskConfiguration "
"WHERE TaskNr== :TaskNr")
curc.execute(sql_cmd, data)
generatorname = curc.fetchone()
if generatorname != None:
data = {'TaskNr': task_nr}
sql_cmd = "SELECT PathToTask FROM TaskConfiguration WHERE TaskNr == :TaskNr"
curc.execute(sql_cmd, data)
path = curc.fetchone()
scriptpath = str(path[0]) + "/" + str(generatorname[0])
else:
scriptpath = "tasks/task" + str(task_nr) + "/./generator.sh"
challenge_mode = self.get_challenge_mode()
command = scriptpath + " " + str(user_id) + " " + str(task_nr) + " " + \
self.submission_email + " " + str(challenge_mode) + " " + \
self.semesterdb + " >> autosub.stdout 2>>autosub.stderr"
logmsg = "generator command: {0}".format(command)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
generator_res = os.system(command)
if generator_res:
logmsg = "Failed to call generator script, return value: " + \
str(generator_res)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
logmsg = "Generated individual task for user/tasknr:" + str(user_id) + "/" + \
str(task_nr)
c.log_a_msg(self.logger_queue, self.name, logmsg, "DEBUG")
c.send_email(self.sender_queue, str(user_email), str(user_id), \
"Task", str(task_nr), "Your personal example", str(messageid))
conc.close()
def test_loop_code(self):
sender_queue = queue.Queue(10)
gen_queue = queue.Queue(10)
ta = TaskActivator(
"testactivator", gen_queue, sender_queue, self.logger_queue, "testcourse.db", "testsemester.db"
)
curc, conc = c.connect_to_db("testcourse.db", self.logger_queue, "testcode")
this_time_yesterday = str(datetime.datetime.now() - datetime.timedelta(1)).split(".")[0]
this_time_tomorrow = str(datetime.datetime.now() + datetime.timedelta(1)).split(".")[0]
this_time_nextmonth = str(datetime.datetime.now() + datetime.timedelta(30)).split(".")[0]
# Drop the task configuration table (start clean)
sqlcmd = "DROP table TaskConfiguration"
try:
curc.execute(sqlcmd)
conc.commit()
except:
logmsg = "No Table TaskConfiguration?"
c.log_a_msg(self.logger_queue, "Debugger", logmsg, "DEBUG")
sqlcmd = (
"CREATE TABLE TaskConfiguration (TaskNr INT PRIMARY KEY, "
"TaskStart DATETIME, TaskDeadline DATETIME, "
"PathToTask TEXT, GeneratorExecutable TEXT, "
"TestExecutable TEXT, Score INT, TaskOperator TEXT, "
"TaskActive BOOLEAN);"
)
curc.execute(sqlcmd)
conc.commit()
# test case 1: no effect -- nothing to do here for the activator thread
self.add_task(
curc,
conc,
1,
this_time_yesterday,
this_time_tomorrow,
"/home/testuser/path/to/task/implementation",
"generator.sh",
"tester.sh",
"5",
"[email protected]",
"1",
)
# test case 2: only a logmsg, there is an inactive task, however its starttime has not
# come (yet)
self.add_task(
curc,
conc,
2,
this_time_tomorrow,
this_time_nextmonth,
"/home/testuser/path/to/task/implementation",
"generator.sh",
"tester.sh",
"5",
"[email protected]",
"0",
)
ta.activator_loop()
# nothing to test for case 1
# check expected log message for test case 2
logmsg = self.logger_queue.get(True)
self.assertEqual(logmsg.get("loggername"), "testactivator")
self.assertEqual(logmsg.get("msg"), "Task 2 is still inactive")
self.assertEqual(logmsg.get("type"), "INFO")
# test case 3: only a logmsg, there is an inactive task, however its starttime has not
# come (yet)
this_time_plus3s = str(datetime.datetime.now() + datetime.timedelta(0, 3)).split(".")[0]
self.add_task(
curc,
conc,
3,
this_time_tomorrow,
this_time_plus3s,
"/home/testuser/path/to/task/implementation",
"generator.sh",
"tester.sh",
"5",
"[email protected]",
"0",
)
ta.activator_loop()
# check expected log message for test case 2
logmsg = self.logger_queue.get(True)
self.assertEqual(logmsg.get("loggername"), "testactivator")
self.assertEqual(logmsg.get("msg"), "Task 2 is still inactive")
self.assertEqual(logmsg.get("type"), "INFO")
# check expected log message for test case 3
logmsg = self.logger_queue.get(True)
self.assertEqual(logmsg.get("loggername"), "testactivator")
self.assertEqual(logmsg.get("msg"), "Task 3 is still inactive")
self.assertEqual(logmsg.get("type"), "INFO")
time.sleep(10)
ta.activator_loop()
# check expected log message for test case 2
logmsg = self.logger_queue.get(True)
self.assertEqual(logmsg.get("loggername"), "testactivator")
self.assertEqual(logmsg.get("msg"), "Task 2 is still inactive")
self.assertEqual(logmsg.get("type"), "INFO")
# check expected log message for test case 3
logmsg = self.logger_queue.get(True)
self.assertEqual(logmsg.get("loggername"), "testactivator")
self.assertEqual(logmsg.get("msg"), "Task 3 is still inactive")
self.assertEqual(logmsg.get("type"), "INFO")
