def login(username, password):
"""
Create a new session for the user and returns a session key to identify
the user from now on.
:param username: the user's username
:param password: the user's password
:returns: session key (string) or None if credentials wrong & expiry date
(datetime.datetime)
"""
# Get the user's password hash from the database
with Cursor() as c:
sql = "select pw_hash from users where username = %s"
c.execute(sql, (username))
pw_hash = c.fetchone()[0]
# Compare password with hash
if not bcrypt.checkpw(password.encode('utf-8'), pw_hash.encode('utf-8')):
return None, None
# Create a new session key and calculate expiry
session = secrets.token_urlsafe(64)[0:48]
expiry_date = datetime.datetime.now() + datetime.timedelta(days=7)
expires = expiry_date.isoformat()
# Insert session key into database
with WriteCursor() as c:
sql = """insert into sessions
values(%s, %s, %s, %s);"""
c.execute(sql, (username, session, expires, expires))
return session, expiry_date
def get_last_test(test, vm='%'):
"""
Returns the results of the last run of a given test.
:param test: test to get results for (string, e.g. 'dns')
:param vm: limit to results on this vm (string, e.g. 'vm01')
:returns: results as dict if there are any, e.g. {'passed': 2, ...},
None otherwise
"""
with Cursor() as c:
# test_results.vm like %s works since default value for arg vm is '%'
sql = """select passed, failed, output, date, test_results.vm
from test_results
inner join run_on on test_results.vm = run_on.vm
where test_results.test = %s and test_results.vm like %s
order by date DESC
limit 1;"""
c.execute(sql, (test, vm))
row = c.fetchone()
# If row is None, now results exist for specified arguments
if row is None:
return None
passed, failed, output, date, vm = row
return {
'passed': passed,
'failed': failed,
'output': output,
'date': date,
'vm': vm
}
def schedule_exists(time):
"""
Checks whether a schedule for given time exists.
:param time: the time to check for (datetime.datetime)
:returns: whether a schedule exists (boolean)
"""
with Cursor() as c:
sql = """select * from test_schedule
where by_user = '******' and scheduled_for = %s; """
c.execute(sql, (time))
return len(c.fetchall()) > 0
def get_scheduled_tests():
"""
Gets all the tests scheduled for now or before call from the database.
:returns: list of tests (list of strings, e.g. ['dns'])
"""
with Cursor() as c:
sql = """select run_on.test, run_on.vm from run_on
where exists (
select * from test_schedule
where scheduled_for <= utc_timestamp() and run = False
);"""
c.execute(sql)
return c.fetchall()
def get_vms_of_test(test):
"""
Get all the vms that are associated with a given test.
Limited to vms that the test has actually been run on at some point.
:param vm: the test (string, e.g. 'dns')
:returns: list of vms (list of strings, e.g. ['vm01', 'vm02'])
"""
with Cursor() as c:
sql = """select distinct test_results.vm from test_results
inner join run_on on test_results.vm = run_on.vm and test_results.test = run_on.test
where run_on.test = %s
order by test_results.vm ASC;"""
c.execute(sql, (test))
return flatten_results(c.fetchall())
def get_username():
"""
Returns the username associated with the current session.
:returns: username (string), or None in case of expired/nonexistent session
"""
if 'session' not in request.cookies:
return None
with Cursor() as c:
sql = """select username
from sessions
where id = %s;"""
c.execute(sql, (request.cookies['session']))
username = c.fetchone()
return None if username is None else username[0]
def get_all_weeks():
"""
Get all the weeks for which exist tests in the database.
:returns: list of weeks (list of ints, e.g. [1, 2])
"""
with Cursor() as c:
sql = """select distinct week from weeks
where exists (
select * from run_on
where weeks.test = run_on.test)
order by week;"""
c.execute(sql)
weeks = c.fetchall()
# Flatten list, e.g. from ((1), (2)) to [1,2]
return flatten_results(weeks)
def get_all_vms():
"""
Get all the vms listed in the database
Limited to the vms on which at least one test has run at some point.
:returns: list of vms (list of strings, e.g. ['vm01', 'vm02'])
"""
with Cursor() as c:
sql = """select distinct vms.vm from vms
inner join run_on on vms.vm = run_on.vm
inner join test_results on vms.vm = test_results.vm
order by vms.vm ASC;"""
c.execute(sql)
tests = c.fetchall()
# Flatten list, e.g. from (('vm01'), ('vm02')) to ['vm01', 'vm02']
return flatten_results(tests)
def scheduled_test_ran_since(date):
"""
Returns whether a scheduled tests have run since given date.
(Returns whether schedules for after date have been marked run).
:param date: given date (datetime.datetime or MySQL accepted string)
:returns: whether tests have run (boolean)
"""
with Cursor() as c:
sql = """select * from test_schedule
where scheduled_for >= %s
and scheduled_for <= utc_timestamp()
and run = True;"""
c.execute(sql, (date))
# If cursor returns entries, tests have been run
return len(c.fetchall()) > 0
def next_tests_scheduled():
"""
Returns the next time for which a test is scheduled.
:returns: time (datetime.dateime) or None if no tests are scheduled
"""
with Cursor() as c:
sql = """select scheduled_for
from test_schedule
where run = False
order by scheduled_for ASC
limit 1;
"""
c.execute(sql)
sched = c.fetchone()
return sched if sched is None else sched[0]
def is_admin():
"""
Returns whether the current session is associated with an admin user.
:returns: session's user is admin (boolean)
"""
# Try to get username, if not logged in return False
username = get_username()
if username is None:
return False
with Cursor() as c:
sql = """select admin
from users
where username = %s;"""
c.execute(sql, (username))
return c.fetchone()[0]
def get_tests_of_vm(vm):
"""
Get all the tests that are associated with a given vm and which week they
are scheduled for.
Limited to tests that have actually been run on the vm at some point.
:param vm: the name of the vm (string, e.g. 'vm01')
:returns: list of weeks & tests (list of pairs, e.g. [[3, 'dns']])
"""
with Cursor() as c:
sql = """select distinct weeks.week, test_results.test from test_results
inner join run_on on test_results.vm = run_on.vm and test_results.test = run_on.test
inner join weeks on test_results.test = weeks.test
where test_results.vm = %s
order by weeks.week ASC;"""
c.execute(sql, (vm))
return c.fetchall()
def get_tests_in_week(week):
"""
Get all the tests for a given week.
Limited to the tests that have actually been executed at some point.
:param week: week to get tests for
:returns: a list of tests (list of strings, e.g. ['dns'])
"""
with Cursor() as c:
sql = """select distinct weeks.test from (select * from weeks order by week, week_num) weeks
inner join run_on on weeks.test = run_on.test
inner join test_results on weeks.test = test_results.test and run_on.vm = test_results.vm
where weeks.week = %s;"""
c.execute(sql, (week))
tests = c.fetchall()
# Flatten list, e.g. from (('dns'), ('dhcp')) to ['dns', 'dhcp']
return flatten_results(tests)
def is_authorized():
"""
Checks whether the request is coming from an authorized session.
Uses the cookies username and session and checks whether this combination
also exists in the valid (and not expired) sessions in the database.
:returns: whether request is authorized (boolean)
"""
# If cookie is not even set, request is unauthorized for sure
if 'session' not in request.cookies:
return False
# Extract session key from cookie
session = request.cookies['session']
# Check whether session exists
sql = """select * from sessions
where expires >= utc_timestamp() and logout >= utc_timestamp() and id = %s;"""
with Cursor() as c:
c.execute(sql, (session))
session_active = len(c.fetchall()) >= 1
return session_active
def get_test_history(test, vm):
"""
Returns all test results of the last week from given test run on given vm.
:param test: test's name (string, e.g. 'dns')
:param vm: vm's name (string, e.g. 'vm01')
"""
with Cursor() as c:
sql = """select passed, failed, output, date
from test_results
where test = %s and vm = %s
and date >= subdate(utc_timestamp(), interval 7 day)
order by date DESC;"""
c.execute(sql, (test, vm))
tests = c.fetchall()
return {
'passed': list(map(lambda x: x[0], tests)),
'failed': list(map(lambda x: x[1], tests)),
'quota': list(map(lambda x: x[0] / (x[0] + x[1]), tests)),
'output': list(map(lambda x: x[2], tests)),
'date': list(map(lambda x: x[3], tests))
}