def students_assigned(course_id, section):
cur = get_db().cursor()
cur.execute("""SELECT * FROM users
JOIN student_sessions AS ss
ON users.id = ss.student_id
WHERE ss.course_id = %s AND ss.section = %s;""",
(course_id, section))
return cur.fetchall()
def test_file_upload(client):
# Anonymous user should get redirected
response = client.post('/course/216/session/1/assignment/2/submit')
assert response.status_code == 302
with client:
client.post('/login',
data={
'email': '*****@*****.**',
'password': '******'
})
# As a student, upload a file
response = client.post(
'/course/216/session/1/assignment/2/submit',
data={'file': (io.BytesIO(b"This is a test file"), 'test.txt')},
follow_redirects=True)
assert response.status_code == 200
assert b'Assignment submitted successfully' in response.data
# Check that an uploaded file appears in the file system
file = open('portal/uploads/1-test.txt')
assert 'This is a test file' in file.read()
file.close()
# Check that a different file uploaded with the same name doesn't
# overwrite the original file
client.post('/login',
data={
'email': '*****@*****.**',
'password': '******'
})
response = client.post(
'/course/216/session/1/assignment/2/submit',
data={'file': (io.BytesIO(b"Hello World"), 'test.txt')})
file = open('portal/uploads/1-test.txt')
assert 'This is a test file' in file.read()
file.close()
file = open('portal/uploads/3-test.txt')
assert 'Hello World' in file.read()
file.close()
# Check that the filenames got added to the submissions table
with db.get_db() as con:
with con.cursor() as cur:
cur.execute('SELECT * FROM submissions')
submissions = cur.fetchall()
assert submissions[1]['file_name'] == '1-test.txt'
assert submissions[2]['file_name'] == '3-test.txt'
def courses_view(cour_id):
"""Shows details of a course to teacher"""
cur = get_db().cursor()
cur.execute("SELECT * FROM courses WHERE teacher_id = %s AND id = %s;",
(g.user['id'], cour_id))
course = cur.fetchone()
return render_template('portal/courses/viewcourse.html', course=course)
def teachers_assignments(class_list):
for cla in class_list:
with get_db() as con:
with con.cursor() as cur:
cur.execute(
"SELECT assignment_id, assignment_name, points_earned, points_available, instructions, due_date, sesh_id FROM assignments WHERE sesh_id = %s",
(cla, ))
teach_assignment = cur.fetchall()
return teach_assignment
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute(
'SELECT * FROM users WHERE id = ?', (user_id,)
).fetchone()
def get_student(id):
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("SELECT * FROM users WHERE id = %s", (id,))
student = cur.fetchone()
if student is None:
abort(404)
return student
def get_assignment(id):
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("SELECT * FROM assignments WHERE id = %s", (id,))
assignment = cur.fetchone()
if assignment is None:
abort(404)
return assignment
def test_add_user(runner, monkeypatch, app):
with app.app_context():
with db.get_db() as con:
with con.cursor() as cur:
check = cur.execute("SELECT * FROM users WHERE email = 'test'")
assert check == None
add_user('test', 'test', 'teacher', 'test')
check = cur.execute("SELECT * FROM users WHERE email = 'test'")
check = cur.fetchone()
assert check is not None
def course(id):
course = get_course(id)
if g.user['role'] == 'teacher':
with db.get_db() as con:
with con.cursor() as cur:
cur.execute('SELECT * FROM assignments WHERE course_id = %s',
(course['id'], ))
assignments = cur.fetchall()
cur.execute('SELECT * FROM sessions WHERE course_id = %s',
(course['id'], ))
sessions = cur.fetchall()
return render_template('courses/course.html',
assignments=assignments,
course=course,
sessions=sessions)
elif g.user['role'] == 'student':
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT a.id,
a.assignment_name,
a.course_id,
a.total_points,
s.points_scored,
s.student_id,
s.graded
FROM assignments AS a
JOIN submissions AS s ON a.id = s.assignment_id
WHERE s.student_id = %s AND a.course_id = %s""", (
g.user['id'],
id,
))
assignments = cur.fetchall()
return render_template('courses/course.html',
course=course,
assignments=assignments)
else:
abort(401)
def user_assignments(course_id, section):
cur = get_db().cursor()
# Pulls out all assignments for the course
cur.execute(
"""SELECT * FROM assignments
WHERE course_id = %s
AND section = %s;""", (course_id, section))
assignments = cur.fetchall()
return assignments
def courses():
"""View for the courses"""
# display the courses they own with a query
cur = get_db().cursor()
cur.execute("SELECT * FROM courses WHERE teacher_id = %s;",
(g.user['id'], ))
teacher_courses = cur.fetchall()
return render_template('portal/courses/courses.html',
teacher_courses=teacher_courses)
def delete_assignment(id):
get_assignment(id)
with get_db() as con:
with con.cursor() as cur:
cur.execute(
'DELETE FROM user_assignments WHERE assignment_id = %s', [id])
con.commit()
cur.execute('DELETE FROM assignments WHERE assignment_id = %s',
[id])
con.commit()
return redirect(url_for('assign.assignment_page'))
def list_courses():
sessions = []
if g.user['role'] == 'teacher':
with db.get_db() as con:
with con.cursor() as cur:
cur.execute('SELECT * FROM courses WHERE instructor_id = %s',
(g.user['id'], ))
courses = cur.fetchall()
for each in courses:
with db.get_db() as con:
with con.cursor() as cur:
cur.execute('SELECT * FROM sessions WHERE course_id = %s',
(each['id'], ))
sessions.append(cur.fetchall())
return render_template('courses/list.html',
courses=courses,
sessions=sessions)
elif g.user['role'] == 'student':
with db.get_db() as con:
with con.cursor() as cur:
cur.execute(
"""SELECT c.course_number,
c.course_title,
s.letter,
s.meets,
c.id
FROM sessions AS s
JOIN courses AS c ON s.course_id = c.id
JOIN user_sessions AS us ON s.id = us.session_id
WHERE us.student_id = %s""", (g.user[0], ))
student_sessions = cur.fetchall()
return render_template('courses/list.html',
student_sessions=student_sessions)
else:
abort(401)
def check_graded(assign_id, student_id):
cur = get_db().cursor()
cur.execute("""SELECT * FROM Grades
WHERE assignment_id = %s
AND student_sessions_id = %s;""",
(assign_id, student_id[0]))
graded = cur.fetchall()
if graded:
return graded[0]
else:
return None
def get_student_assignments(course_id, section):
cur = get_db().cursor()
# Pulls out all assignments for the course
cur.execute("""SELECT * FROM assignments a JOIN grades g
ON (a.id = g.assignment_id)
WHERE a.course_id = %s
AND a.section = %s;""",
(course_id, section))
assignments = cur.fetchall()
return assignments
def test_register(client, app):
assert client.get('/auth/register').status_code == 200
response = client.post('/auth/register',
data={
'username': '******',
'password': '******'
})
assert 'http://localhost/auth/login' == response.headers['Location']
with app.app_context():
assert get_db().execute(
"select * from user where username = '******'", ).fetchone() is not None
def test_author_required(app, client, auth):
# change the post author to another user
with app.app_context():
db = get_db()
db.execute('UPDATE post SET author_id = 2 WHERE id = 1')
db.commit()
auth.login()
# current user can't modify other user's post
assert client.post('/1/update').status_code == 403
assert client.post('/1/delete').status_code == 403
# current user doesn't see edit link
assert b'href="/1/update"' not in client.get('/').data
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT * FROM users WHERE id = %s', (user_id,)
)
g.user = cur.fetchone()
cur.close()
def test_delete_course(app, client, auth):
with app.app_context():
db = get_db()
cur = db.cursor()
auth.teacher_login()
response = client.post('/deletecourse', data={'course_to_delete': 4})
cur.execute("SELECT * FROM courses WHERE name = 'Security and Ethics';")
check = cur.fetchone()
assert check is None
def get_post(id, check_author=True):
post = get_db().execute(
' SELECT p.id, tittle, body, created, author_id, username'
' FROM post p JOIN users u ON p.author_id = u.id'
' WHERE p.id = ?', (id, )).fetchone()
if post is None:
abort(404, "Post id {0} doesn't exist.".format(id))
if check_author and post['author_id'] != g.user['id']:
abort(403)
return post
def index(id):
course = get_course(id)
user = get_user(id)
con = get_db()
cur = con.cursor(cursor_factory=DictCursor)
cur.execute("SELECT * FROM assignments WHERE course_id = %s", (id, ))
assignments = cur.fetchall()
cur.close()
return render_template('/assignments/index.html',
assignments=assignments,
user=user,
course=course)
def input_grades(id):
points_earned = request.form['earned']
student = request.form['student']
with get_db() as con:
with con.cursor() as cur:
print(student)
cur.execute(
"UPDATE user_assignments SET point_earned = %s WHERE student_id = %s AND assignment_id = %s",
(
points_earned,
student,
id,
))
con.commit()
def app():
db_fd, db_path = tempfile.mkstemp()
app = create_app({
'TESTING': True,
'DATABASE': db_path,
})
with app.app_context():
init_db()
get_db().executescript(_data_sql)
yield app
os.close(db_fd)
os.unlink(db_path)
@pytest.fixture
def client(app):
return app.test_client()
@pytest.fixture
def runner(app):
return app.test_cli_runner()
def test_edit_course_teacher(client, course, auth, app):
auth.login_teacher()
course.create('test', 'testing')
assert client.get('courses/1/edit').status_code == 200
client.post('courses/1/edit',
data={
'course_number': 'test2',
'course_title': 'testing2'
})
with app.app_context():
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("SELECT * FROM courses WHERE id = 1")
course = cur.fetchone()
assert course['course_number'] == 'test2'
def validate_student():
"""Return a list of valid session IDs for the logged in user"""
with get_db() as con:
with con.cursor() as cur:
cur.execute("""
SELECT s.id FROM sessions s JOIN roster r
ON s.id = r.session_id
WHERE r.student_id = %s
""", (g.user['id'],))
result = cur.fetchall()
valid_sessions = []
for row in result:
valid_sessions.append(str(row['id']))
return valid_sessions
def test_edit_sessions(client, auth):
auth.login_teacher()
assert client.get('sessions/1/edit').status_code == 200
with client:
client.post('sessions/1/edit', data = {'session_time': 'MTWRF', 'student_email': ['*****@*****.**', '*****@*****.**', '*****@*****.**', '*****@*****.**']})
with db.get_db() as con:
with con.cursor() as cur:
cur.execute("SELECT * FROM sessions WHERE id = 1")
session = cur.fetchone()
assert session['meets'] == 'MTWRF'
cur.execute("SELECT * FROM user_sessions WHERE session_id = 1")
students = cur.fetchall()
assert len(students) == 4
def home():
with get_db() as con:
with con.cursor() as cur:
cur.execute(
"""
SELECT c.course_name, c.course_code, s.session_name, s.meeting_days,
s.meeting_time, s.meeting_place, s.id, r.student_id
FROM courses c JOIN sessions s
ON c.id = s.course_id
JOIN roster r
ON s.id = r.session_id
WHERE r.student_id = %s
""", (g.user['id'], ))
courses = cur.fetchall()
return render_template('layouts/student/student-page.html',
courses=courses)
def test_delete_sessions(app, client, auth):
with app.app_context():
db = get_db()
cur = db.cursor()
auth.teacher_login()
response = client.post('/deletesession',
data={
'course_id': 1,
'section': 'A'
})
cur.execute(
"SELECT * FROM sessions WHERE section = 'A' and course_id = '1';")
check = cur.fetchone()
assert check is None
def test_assign_submit(client, auth, app):
auth.teacher_login()
# GET requests are redirected
response = client.get('/teacher/assignments/assign/submit')
assert 'http://localhost/teacher/sessions' == response.headers['Location']
# On POST, user should be able to insert assignments into the session_assignments
# junction table with a due date
response = client.post('/teacher/assignments/assign/submit',
data={
'date': '2020-05-08',
'session_id': 1,
'assign_id': 4
})
with app.app_context():
with get_db() as con:
with con.cursor() as cur:
cur.execute("""
SELECT * FROM session_assignments where assignment_id = 4 AND session_id = 1 """
)
assert cur.fetchone() is not None
# Teachers should not be able to submit assignments to sessions that they do not own
# or assign assignments that they do not own to any session
client.post(
'/teacher/assignments/assign/submit',
# The teacher does not own this session
data={
'date': '2020-05-08',
'session_id': 3,
'assign_id': 4
})
response = client.get('/teacher/sessions')
assert b'Something went wrong' in response.data
client.post(
'/teacher/assignments/assign/submit',
# The teacher does not own this assignment
data={
'date': '2020-05-08',
'session_id': 1,
'assign_id': 5
})
response = client.get('/teacher/sessions')
assert b'Something went wrong' in response.data
def app():
app = create_app({
'TESTING': True,
'DB_NAME': 'portal_test',
'DB_USER': '******',
})
with app.app_context():
init_db()
with open(os.path.join(os.path.dirname(__file__), 'data.sql'),
'rb') as f:
with get_db() as con:
with con.cursor() as cur:
cur.execute(f.read())
yield app
