def builds(page): page_size = 50 page = max(1, page) with DbCursor() as c: student = _get_student(c) user_id, _, _, login, _, _ = student group_repos = get_groups(c, user_id) repos = [login] + group_repos c.execute( '''SELECT build_name, source, status, score, `commit`, message, job, started FROM builds WHERE source in (%s) ORDER BY started DESC LIMIT ? OFFSET ?''' % (",".join(["?"] * len(repos))), repos + [page_size + 1, (page - 1) * page_size]) builds = c.fetchall() if not builds and page > 1: abort(404) more_pages = len(builds) == page_size + 1 if more_pages: builds = builds[:-1] full_scores = { assignment.name: assignment.full_score for assignment in config.assignments } builds_info = (build + (full_scores.get(build[6]), ) for build in builds) template_common = _template_common(c) return render_template("dashboard/builds.html", builds_info=builds_info, page=page, more_pages=more_pages, **template_common)
def builds(page): page_size = 50 page = max(1, page) with DbCursor() as c: c.execute( '''SELECT build_name, source, status, score, `commit`, message, job, started FROM builds ORDER BY started DESC LIMIT ? OFFSET ?''', [page_size + 1, (page - 1) * page_size]) builds = c.fetchall() if not builds and page > 1: abort(404) more_pages = len(builds) == page_size + 1 if more_pages: builds = builds[:-1] full_scores = { assignment.name: assignment.full_score for assignment in config.assignments } builds_info = (build + (full_scores.get(build[6]), ) for build in builds) return render_template("ta/builds.html", builds_info=builds_info, page=page, more_pages=more_pages, **_template_common())
def student_id(): github = github_username() if request.method == "GET": return render_template("onboarding/student_id.html", github=github) try: mailer_job = None github_job = None with DbCursor() as c: user = get_user_by_github(c, github) if user: return redirect(url_for("dashboard.index")) student_id = request.form.get("f_student_id") if not student_id: fail_validation("Student ID is required") user = get_user_by_student_id(c, student_id) if not user: fail_validation("Student not found with that student ID") user_id, name, _, login, old_github, email = user if old_github: fail_validation( "Another GitHub account has been associated with that student " "ID already.") if not name: fail_validation( "There is no name associated with this account. (Contact your TA?)" ) if not login: fail_validation( "There is no login associated with this account. (Contact your " "TA?)") if not email: fail_validation( "There is no email associated with this account. (Contact your " "TA?)") c.execute('''UPDATE users SET github = ? WHERE sid = ?''', [github, student_id]) if not config.github_read_only_mode: github_job = repomanager_queue.create(c, "assign_repo", (login, [github])) if config.mailer_enabled: if config.inst_account_enabled: attachments = [("pdf", get_inst_account_form_path(login))] else: attachments = [] email_payload = create_email( "onboarding_confirm", email, "%s Autograder Registration" % config.course_number, _attachments=attachments, name=name, login=login, inst_account_enabled=config.inst_account_enabled) mailer_job = mailer_queue.create(c, "send", email_payload) if config.mailer_enabled and mailer_job: mailer_queue.enqueue(mailer_job) if not config.github_read_only_mode and github_job: repomanager_queue.enqueue(github_job) return redirect(url_for(_get_next_step("onboarding.student_id"))) except ValidationError as e: return redirect_with_error(url_for("onboarding.student_id"), e)
def gradeslog(page): page_size = 50 page = max(1, page) with DbCursor() as c: c.execute( '''SELECT gradeslog.transaction_name, gradeslog.source, users.id, users.name, users.github, users.super, gradeslog.assignment, gradeslog.score, gradeslog.slipunits, gradeslog.updated, gradeslog.description FROM gradeslog LEFT JOIN users ON gradeslog.user = users.id ORDER BY updated DESC LIMIT ? OFFSET ?''', [page_size + 1, (page - 1) * page_size]) entries = c.fetchall() if not entries and page > 1: abort(404) more_pages = len(entries) == page_size + 1 if more_pages: entries = entries[:-1] full_scores = { assignment.name: assignment.full_score for assignment in config.assignments } events = [entry + (full_scores.get(entry[6]), ) for entry in entries] return render_template("ta/gradeslog.html", events=events, page=page, more_pages=more_pages, **_template_common())
def assignments(): with DbCursor() as c: student = _get_student(c) user_id, _, _, login, _, _ = student super_value = get_super(c, user_id) dropped = super_value is not None and super_value < 0 c.execute( "SELECT assignment, score, slipunits, updated FROM grades WHERE user = ?", [user_id]) grade_info = { assignment: (score, slipunits, updated) for assignment, score, slipunits, updated in c.fetchall() } template_common = _template_common(c) assignments_info = [] if not dropped: for assignment in config.assignments: a = assignment.student_view(login) if now_compare(a.not_visible_before) >= 0: assignments_info.append( (a.name, a.full_score, a.weight, a.due_date) + grade_info.get(a.name, (None, None, None))) return render_template("dashboard/assignments.html", assignments_info=assignments_info, **template_common)
def _get_next_step(current_step): # I know this kind of logic requires O(2^N) different cases, but right now there's only 1 config # option that affects this list (it's the student photos enable/disable), so it's simplest to # express the 2 alternatives this way. # # When we add more features to the onboarding process, you can come up with a better, # generalized way of determining the onboarding steps. if config.student_photos_enabled: steps = ["onboarding.student_id", "onboarding.photo"] else: steps = ["onboarding.student_id"] if current_step is None: return steps[0] step_i = steps.index(current_step) if step_i == len(steps) - 1: # This is the final step of onboarding. # Authenticate the user and redirect them to the dashboard. with DbCursor() as c: user = get_user_by_github(c, github_username()) assert user user_id_, _, _, _, _, _ = user authenticate_as_user(user_id_) return "onboarding.welcome" else: return steps[step_i + 1]
def photo(): github = github_username() if request.method == "GET": return render_template("onboarding/photo.html", github=github) try: with DbCursor() as c: user = get_user_by_github(c, github) user_id, _, _, _, _, _ = user photo_base64 = request.form.get("f_photo_cropped") photo_prefix = "data:image/jpeg;base64," if not photo_base64: fail_validation( "No photo submitted. Please choose a photo.") if not photo_base64.startswith(photo_prefix): fail_validation( "Unrecognized photo format. (Potential autograder bug?)" ) photo_binary = buffer( binascii.a2b_base64(photo_base64[len(photo_prefix):])) if len(photo_binary) > 2**21: fail_validation( "Photo exceeds maximum allowed size (2MiB).") c.execute("UPDATE users SET photo = ? WHERE id = ?", [photo_binary, user_id]) return redirect(url_for(_get_next_step("onboarding.photo"))) except ValidationError as e: return redirect_with_error(url_for("onboarding.photo"), e)
def repo(repo): with DbCursor() as c: owners = get_repo_owners(c, repo) if not owners: abort(404) c.execute( '''SELECT id, name, sid, login, github, email, super, photo FROM users WHERE id in (%s)''' % ",".join(["?"] * len(owners)), owners) students = c.fetchall() c.execute( '''SELECT build_name, source, status, score, `commit`, message, job, started FROM builds WHERE source = ? ORDER BY started DESC''', [repo]) builds = c.fetchall() full_scores = { assignment.name: assignment.full_score for assignment in config.assignments } builds_info = (build + (full_scores.get(build[6]), ) for build in builds) return render_template("ta/repo.html", repo=repo, students=students, builds_info=builds_info, **_template_common())
def build_now(): job_name = request.form.get("f_job_name") repo = request.form.get("f_repo") assignment = get_assignment_by_name(job_name) if not assignment: abort(400) assignment = assignment.student_view(repo) if assignment.manual_grading: abort(400) with DbCursor() as c: student = _get_student(c) user_id, _, _, login, _, _ = student super_value = get_super(c, user_id) dropped = super_value is not None and super_value < 0 if assignment.is_group: repos = get_groups(c, user_id) else: repos = [login] if repo not in repos: abort(403) if now_compare(assignment.not_visible_before, add_grace_period( assignment.cannot_build_after)) != 0 or dropped: abort(400) branch_hash = get_branch_hash(repo, "master") message = None if branch_hash: message = get_commit_message(repo, branch_hash) # This section doesn't absolutely NEED to be consistent with the previous transaction, # since we have not specified any actual data constraints. The only possible logical error # would be to fulfill a request when the current user's permissions have been revoked # between these two transactions. with DbCursor() as c: build_name = create_build(c, job_name, repo, branch_hash, message) if should_limit_source(repo, job_name): rate_limit_fail_build(build_name) else: job = Job(build_name, repo, "Web interface") dockergrader_queue.enqueue(job) return redirect(url_for("dashboard.builds_one", name=build_name))
def welcome(): github = github_username() with DbCursor() as c: user = get_user_by_id(c, user_id()) return render_template("onboarding/welcome.html", github=github, user=user, inst_account_enabled=config.inst_account_enabled)
def assignments_one_timeseries_grade_percentiles(name): with DbCursor() as c: data = Datasets.timeseries_grade_percentiles(c, name) if not data: abort(404) resp = Response(json.dumps(data)) resp.headers["Content-Type"] = "application/json" return resp
def test_rollback(self): with DbCursor() as c: c.execute("DELETE FROM options WHERE key = ?", ["test_value1"]) with self.assertRaises(ValueError): with DbCursor() as c: c.execute("INSERT INTO options (key, value) VALUES (?, ?)", ["test_value1", "ok"]) c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() self.assertEqual(value, "ok") raise ValueError("Catch me") with DbCursor() as c: c.execute("SELECT count(*) FROM options WHERE key = ?", ["test_value1"]) count, = c.fetchone() self.assertEqual(count, 0)
def students(): with DbCursor() as c: c.execute('''SELECT id, name, sid, login, github, email, super FROM users ORDER BY super DESC, login''') students = c.fetchall() return render_template("ta/students.html", students=students, **_template_common())
def assignments_one_grade_distribution(name): with DbCursor() as c: data = Datasets.grade_distribution(c, name) if not data: abort(404) resp = Response(json.dumps(data)) resp.headers["Content-Type"] = "application/json" return resp
def students_one(identifier, type_): with DbCursor() as c: student = None if type_ in ("id", "user_id"): student = get_user_by_id(c, identifier) elif type_ in ("github", "_github_explicit"): student = get_user_by_github(c, identifier) elif type_ == "login": student = get_user_by_login(c, identifier) elif type_ in ("sid", "student_id"): student = get_user_by_student_id(c, identifier) if student is None: abort(404) user_id, _, _, _, _, _ = student super_ = get_super(c, user_id) photo = None if student_photos_enabled: photo = get_photo(c, user_id) c.execute( '''SELECT users.id, users.name, users.github, groupsusers.`group` FROM groupsusers LEFT JOIN users ON groupsusers.user = users.id WHERE groupsusers.`group` IN (SELECT `group` FROM groupsusers WHERE user = ?)''', [user_id]) groups = OrderedDict() for g_user_id, g_name, g_github, g_group in c.fetchall(): groups.setdefault(g_group, []).append( (g_user_id, g_name, g_github)) grouplimit = get_grouplimit(c, user_id) c.execute( '''SELECT transaction_name, source, assignment, score, slipunits, updated, description FROM gradeslog WHERE user = ? ORDER BY updated DESC''', [user_id]) entries = c.fetchall() full_scores = { assignment.name: assignment.full_score for assignment in config.assignments } events = [entry + (full_scores.get(entry[2]), ) for entry in entries] c.execute( "SELECT assignment, score, slipunits, updated FROM grades WHERE user = ?", [user_id]) grade_info = { assignment: (score, slipunits, updated) for assignment, score, slipunits, updated in c.fetchall() } assignments_info = [(a.name, a.full_score, a.weight, a.due_date) + grade_info.get(a.name, (None, None, None)) for a in config.assignments] return render_template("ta/students_one.html", student=student, super_=super_, photo=photo, groups=groups.items(), grouplimit=grouplimit, events=events, assignments_info=assignments_info, **_template_common())
def pushhook(): payload_bytes = request.get_data() if request.form.get("_csrf_token"): # You should not be able to use a CSRF token for this abort(400) try: payload = json.loads(payload_bytes) assert isinstance(payload, dict) if payload.get("action", "push") != "push": logging.warning("Dropped GitHub pushhook payload because action was %s" % str(payload.get("action"))) return ('', 204) ref = payload["ref"] before = payload["before"] after = payload["after"] assert isinstance(ref, basestring) assert isinstance(before, basestring) assert isinstance(after, basestring) repo_name = payload["repository"]["name"] assert isinstance(repo_name, basestring) file_list = get_diff_file_list(repo_name, before, after) if not file_list: file_list = [] # This is a useful hook to use, if you want to add custom logic to determine which jobs get # run on a Git push. # # Arguments: # jobs -- The original list of jobs (default: empty list) # repo_name -- The name of the repo that caused the pushhook # ref -- The name of the ref that was pushed (e.g. "refs/heads/master") # modified_files -- A list of files that were changed in the push, relative to repo root # # Returns: # A list of job names. (e.g. ["hw0", "hw0-style-check"]) jobs_to_run = apply_filters("pushhooks-jobs-to-run", [], repo_name, ref, file_list) if not jobs_to_run: return ('', 204) # We could probably grab this from the payload, but let's call this method for the sake # of consistency. message = get_commit_message(repo_name, after) for job_to_run in jobs_to_run: while True: try: with DbCursor() as c: build_name = create_build(c, job_to_run, repo_name, after, message) break except apsw.Error: logging.exception("Failed to create build, retrying...") job = Job(build_name, repo_name, "GitHub push") dockergrader_queue.enqueue(job) return ('', 204) except Exception: logging.exception("Error occurred while processing GitHub pushhook payload") abort(500)
def rate_limit_fail_build(build_name): assert MAX_JOBS_ALLOWED is not None message = "Cannot have more than {} builds in progress or queued.".format( MAX_JOBS_ALLOWED) with DbCursor() as c: c.execute( '''UPDATE builds SET status = ?, updated = ?, log = ? WHERE build_name = ?''', [FAILED, now_str(), message, build_name])
def job2(): with DbCursor() as c: c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() ready1.wait() c.execute("UPDATE options SET value = ? WHERE key = ?", [value + "y", "test_value1"]) ready2.set()
def test_conflict(self): """This test is actually kind of dumb and useless...""" with DbCursor() as c: c.execute("DELETE FROM options WHERE key = ?", ["test_value1"]) c.execute("INSERT INTO options (key, value) VALUES (?, ?)", ["test_value1", "ok"]) ready1 = threading.Event() ready2 = threading.Event() def job1(): with DbCursor() as c: c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() ready1.set() ready2.wait() with self.assertRaises(apsw.BusyError): c.execute("UPDATE options SET value = ? WHERE key = ?", [value + "a", "test_value1"]) def job2(): with DbCursor() as c: c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() ready1.wait() c.execute("UPDATE options SET value = ? WHERE key = ?", [value + "y", "test_value1"]) ready2.set() thread1 = threading.Thread(target=job1) thread2 = threading.Thread(target=job2) thread1.start() thread2.start() thread1.join() thread2.join() with DbCursor() as c: c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() c.execute("DELETE FROM options WHERE key = ?", ["test_value1"]) self.assertEqual("oky", value)
def should_limit_source(repo_name): if MAX_JOBS_ALLOWED is None: return False with DbCursor() as c: c.execute( '''SELECT count(*) FROM builds WHERE source = ? AND (status = ? OR status = ?)''', [repo_name, QUEUED, IN_PROGRESS]) count = int(c.fetchone()[0]) return count > MAX_JOBS_ALLOWED
def job1(): with DbCursor() as c: c.execute("SELECT value FROM options WHERE key = ?", ["test_value1"]) value, = c.fetchone() ready1.set() ready2.wait() with self.assertRaises(apsw.BusyError): c.execute("UPDATE options SET value = ? WHERE key = ?", [value + "a", "test_value1"])
def send_template(*args, **kwargs): """ Enqueues an email to be sent on the background thread. See docstring for create_email for arguments. """ if not config.mailer_enabled: raise RuntimeError("Cannot send mail while mailer is disabled") email = create_email(*args, **kwargs) with DbCursor() as c: job = mailer_queue.create(c, "send", email) mailer_queue.enqueue(job)
def builds_one(name): with DbCursor() as c: c.execute( '''SELECT build_name, status, score, source, `commit`, message, job, started, log FROM builds WHERE build_name = ? LIMIT 1''', [name]) build = c.fetchone() if not build: abort(404) build_info = build + (get_assignment_by_name(build[6]).full_score, ) return render_template("ta/builds_one.html", build_info=build_info, **_template_common())
def mark_as_complete(self, transaction_id): while True: try: with DbCursor() as c: c.execute( "UPDATE %s SET completed = 1 WHERE id = ?" % self.database_table, [transaction_id]) break except Exception: logging.exception( "[%s] Error occurred while marking %s as done" % (self.queue_name, transaction_id))
def sql(): query = "" query_headers = query_rows = query_error = None query_more = False if request.method == "POST": action = request.form.get("f_action") query = request.form.get("f_query") try: if query: with DbCursor(read_only=True) as c: c.execute(query) query_rows = [] # We are not allowed to modify the query itself, so we're forced to truncate # long lists of results with Python. for _ in range(1000): row = c.fetchone() if not row: break else: query_headers = c.getdescription() query_rows.append(map(stringify, row)) if c.fetchone(): query_more = True if not query_rows: query_error = "No results" except apsw.Error: query_error = traceback.format_exc() if action == "export" and query_headers: result_string = io.StringIO() result_writer = csv.writer(result_string, delimiter=",", quotechar='"') result_writer.writerow( [header_name for header_name, header_type in query_headers]) if query_rows: result_writer.writerows(query_rows) resp = Response(result_string.getvalue()) resp.headers["Content-Type"] = "text/csv" resp.headers[ "Content-Disposition"] = "attachment; filename=query_results.csv" return resp return render_template("ta/sql.html", query=query, query_headers=query_headers, query_rows=query_rows, query_error=query_error, query_more=query_more, **_template_common())
def assignments_one(name, page): page_size = 50 assignment = get_assignment_by_name(name) if not assignment: abort(404) with DbCursor() as c: c.execute( '''SELECT id, name, sid, github, email, super, score, slipunits, updated FROM grades LEFT JOIN users ON grades.user = users.id WHERE assignment = ? ORDER BY super DESC, login''', [name]) grades = c.fetchall() c.execute( '''SELECT build_name, source, status, score, `commit`, message, started FROM builds WHERE job = ? ORDER BY started DESC LIMIT ? OFFSET ?''', [name, page_size + 1, (page - 1) * page_size]) builds = c.fetchall() if not builds and page > 1: abort(404) more_pages = len(builds) == page_size + 1 if more_pages: builds = builds[:-1] c.execute( '''SELECT COUNT(*), AVG(score) FROM grades WHERE assignment = ? AND score IS NOT NULL''', [name]) stats = c.fetchone() if stats[0] == 0: variance = None stddev = None else: c.execute( "SELECT AVG((score - ?) * (score - ?)) FROM grades WHERE assignment = ?", [stats[1], stats[1], name]) variance, = c.fetchone() stddev = sqrt(variance) assignment_info = ( (assignment.name, assignment.full_score, assignment.min_score, assignment.max_score, assignment.weight, assignment.due_date, assignment.category, assignment.is_group, assignment.manual_grading, assignment.not_visible_before, assignment.cannot_build_after, assignment.start_auto_building, assignment.end_auto_building) + stats + (stddev, )) return render_template("ta/assignments_one.html", grades=grades, builds=builds, assignment_info=assignment_info, page=page, more_pages=more_pages, **_template_common())
def group_names_and_emails(): with DbCursor() as c: c.execute( """SELECT groupsusers.`group`, GROUP_CONCAT(users.id, "|"), GROUP_CONCAT(users.name, "|"), GROUP_CONCAT(users.sid, "|"), GROUP_CONCAT(users.login, "|"), GROUP_CONCAT(users.github, "|"), GROUP_CONCAT(users.email, "|") FROM groupsusers LEFT JOIN users ON groupsusers.user = users.id GROUP BY groupsusers.`group`""") dataset = c.fetchall() headers = [ "Group Name", "Database ID", "Name", "SID", "Login", "GitHub Username", "Email" ] return headers, dataset
def gradeslog_one(name): with DbCursor() as c: c.execute( '''SELECT gradeslog.transaction_name, gradeslog.source, users.id, users.name, users.github, users.super, gradeslog.assignment, gradeslog.score, gradeslog.slipunits, gradeslog.updated, gradeslog.description FROM gradeslog LEFT JOIN users ON gradeslog.user = users.id WHERE gradeslog.transaction_name = ? LIMIT 1''', [name]) entry = c.fetchone() assignment = get_assignment_by_name(entry[6]) full_score = assignment.full_score if assignment else 0.0 return render_template("ta/gradeslog_one.html", entry=entry, full_score=full_score, **_template_common())
def recover(self): """ Scans the database for uncompleted jobs and re-enqueues them. """ assert not self.recovered, "ResumableQueue should only be recovered from DB once" self.recovered = True with DbCursor() as c: c.execute( 'SELECT id, operation, payload FROM %s WHERE completed = 0' % self.database_table) with self.queue_cv: for transaction_id, operation, payload in c.fetchall(): payload = self.unserialize_arguments(payload) self.queue.append((transaction_id, operation, payload)) self.queue_cv.notify()
def assignments(): with DbCursor() as c: c.execute( "SELECT assignment, score, slipunits, updated FROM grades WHERE user = ?", [user_id()]) grade_info = { assignment: (score, slipunits, updated) for assignment, score, slipunits, updated in c.fetchall() } template_common = _template_common(c) assignments_info = [(a.name, a.full_score, a.weight, a.due_date) + grade_info.get(a.name, (None, None, None)) for a in config.assignments if now_compare(a.not_visible_before) >= 0] return render_template("dashboard/assignments.html", assignments_info=assignments_info, **template_common)