def GET(self, **kwargs):
"""logs the user in
Note that this GET request doesn't take json like the other requests
"""
row = kwargs
if row.has_key('username') and row.has_key('password'):
conn, cur = databeast.connect()
#does user exist?
cur.execute("SELECT COUNT(*) FROM users WHERE username=%s", [row['username']])
count = cur.fetchall()[0][0]
if count == 1:
sql = "SELECT password FROM users WHERE username = %s"
cur.execute(sql, [row['username']])
hashed_pw = cur.fetchall()[0][0]
if bcrypt.hashpw(row['password'].encode('utf-8'), hashed_pw.encode('utf-8')) == hashed_pw:
output = [True, "User %s logged in." % (row['username'])]
#open a training session
session.openSession(row['username'])
else:
output = [False, "Invalid login credentials."]
else:
output = [False, "User %s does not exist." % row['username']]
return output
else:
raise cherrypy.HTTPError(400)
def tearDown(self):
conn, cur = databeast.connect()
cur.execute("DELETE FROM sessions WHERE username = %s", ['unit_test_user_7'])
cur.execute("DELETE FROM sessions WHERE username = %s", [self.username])
cur.execute("DELETE FROM users WHERE username = %s", [self.username])
cur.execute("DELETE FROM sessions WHERE username = %s", ['unit_test_user_6'])
conn.commit()
conn.close()
def closeSession(username):
"""close a training session for a given user within the database
"""
conn, cur = databeast.connect()
sql = """UPDATE sessions SET end_time=%s, complete=FALSE
WHERE username=%s AND end_time = NULL"""
cur.execute(sql, [datetime.now(), username])
conn.commit()
conn.close()
def PUT(self):
"""Mark a task as played and update the open session
"""
row = cherrypy.request.json
if row.has_key('task') and row.has_key('username'):
task = row['task']
username = row['username']
#get the most recent open session
conn, cur = databeast.connect()
sql = """SELECT session, tasks_to_play, tasks_played
FROM sessions
WHERE username = %s AND end_time IS NULL
ORDER BY session DESC"""
cur.execute(sql, [username])
result = cur.fetchall()
if result:
session = result[0][0]
tasks_to_play = result[0][1]
tasks_played = result[0][2]
#move the completed task to played list
tasks_to_play.remove(task)
if tasks_played:
tasks_played.append(task)
else:
tasks_played = [task]
#update DB
sql = """UPDATE sessions
SET tasks_to_play = %s, tasks_played = %s
WHERE username = %s AND session = %s"""
cur.execute(sql, [tasks_to_play, tasks_played, username, session])
conn.commit()
#close the session if we're out of tasks_played
if len(tasks_to_play) == 0:
sql = """UPDATE sessions
SET complete = TRUE, end_time = %s
WHERE username = %s AND session = %s"""
cur.execute(sql, [datetime.now(), username, session])
conn.commit()
return tasks_to_play
else:
raise cherrypy.HTTPError(400, "No open session for %s." % username)
else:
raise cherrypy.HTTPError(400, "Missing username and/or task")
def GET(self, **kwargs):
"""Get the current open session given a username
returns a list [day, week, task_list]
"""
if kwargs.has_key('username'):
#retrieve the open session (if exists from the db)
conn, cur = databeast.connect()
sql = """SELECT session, tasks_to_play FROM sessions
WHERE username = %s AND end_time IS NULL
ORDER BY start_time DESC"""
cur.execute(sql, [kwargs['username']])
result = cur.fetchall()
if result:
return result[0]
else:
return None
else:
raise cherrypy.HTTPError(400, "Missing username")
def GET(self, **kwargs):
"""Get the problems for a given week and problem_set
"""
if kwargs.has_key('week'):
#retrieve the open session (if exists from the db)
conn, cur = db.connect()
query = "SELECT n1 FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
cur.execute(query)
op1 = cur.fetchall()
query = "SELECT n2 FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
cur.execute(query)
op2 = cur.fetchall()
query = "SELECT problem_id FROM problems WHERE week='%s' AND problem_set='%s'"%(kwargs['week'], kwargs['problem_set'])
cur.execute(query)
problem_id = cur.fetchall()
if op1:
return op1, op2, problem_id
else:
return None
else:
raise cherrypy.HTTPError(400, "Missing week")
def openSession(username):
"""
open a training session for a given user within the database
IMPORTANT - this function is only run during a login event
returns db status message
"""
conn, cur = databeast.connect()
#first determine whether any open sessions exist
sql = "SELECT count(*) FROM sessions WHERE username = %s AND end_time IS NULL"
cur.execute(sql, [username])
result = cur.fetchall()
#TODO - check to see whether we are on the same training day - would want
#to resume previous session in this case, and not close this one
if result[0][0] >= 1:
closeSession(username) #closes any active session for that user
#now, determine the previous session number
sql = "SELECT MAX(session) FROM sessions WHERE username = %s"
cur.execute(sql, [username])
result = cur.fetchall()
if result[0][0]:
session = result[0][0] + 1
else:
session = 1
conn.close()
#now, open the new session
d = {}
d['username'] = username
d['tasks_to_play'] = tasks #TODO - counterbalancing
d['training_day'] = 1 #TODO - increment based on previous training day
d['week'] = 1
d['session'] = session
result = databeast.insert(d, "sessions")
return result
def test_task_delivery(self):
"""sessions should close after all tasks completed"""
tasks = ["task1", "task2", "task3"]
session.openSession('unit_test_user_7')
#override the task list for the purpose of this test
conn, cur = databeast.connect()
sql = "UPDATE sessions SET tasks_to_play = %s WHERE username = %s"
cur.execute(sql, [tasks, 'unit_test_user_7'])
conn.commit()
#mark the first task as done
payload = {'username': '******', 'task':'task1'}
r = requests.put(self.session_url, json=payload)
json_data = json.loads(r.text)
self.assertTrue(json_data == ["task2", "task3"])
#mark the second task as done
payload = {'username': '******', 'task':'task2'}
r = requests.put(self.session_url, json=payload)
json_data = json.loads(r.text)
self.assertTrue(json_data == ["task3"])
#mark the third (and final) task as done
payload = {'username': '******', 'task':'task3'}
r = requests.put(self.session_url, json=payload)
json_data = json.loads(r.text)
self.assertTrue(json_data == [])
#check that session is closed
sql = "SELECT complete FROM sessions WHERE username = %s"
cur.execute(sql, ['unit_test_user_7'])
result = cur.fetchall()
conn.close()
self.assertTrue(result[0][0] == True)