2.3.1.3. db.crud module

db.crud.bucket_count(n: int) → str

Bucket a raw count into a coarse range label.

We never report exact course/student counts – only the bucket – to keep the data non-identifying.

async db.crud.build_checkin_payload() → dict

Assemble the exact (anonymous) check-in payload.

Contains only: a random per-install id, the version, the operator’s self-declared region/institution, the list of base courses served, and bucketed (never exact) course/student counts. No personal data, no IP.

async db.crud.check_datafile_exists(filename: str, owner: str, course_id: str) → bool

Check if a datafile with the same filename, owner, and course already exists.

Parameters:

  • filename – str, the filename to check

  • owner – str, the owner (username) to check

  • course_id – str, the course_id to check

Returns:

bool, True if exists, False otherwise

async db.crud.check_domain_approval(domain_name: str, approval_type: InstrumentedAttribute) → bool

Check if a domain approval exists for a given approval type. :param domain_name: str, domain name to verify :param approval_type: sqlalchemy.orm.attributes.InstrumentedAttribute, the type of approval (e.g., ‘DomainApprovals.lti1p3’)

async db.crud.consume_reset_token(token: str) → Optional[AuthUserValidator]
async db.crud.copy_course_attributes(basecourse_id: int, new_course_id: int)

Copy all course attributes from a base course to a new course

async db.crud.count_courses() → int

Return the total number of courses on this install.

async db.crud.count_distinct_student_answers(div_id: str, course_name: str, start_time: datetime, exclude_sid: str = None) → int

Count distinct students who answered a question after a given time.

Parameters:

  • div_id – str, the question div_id

  • course_name – str, the course name

  • start_time – datetime, only count answers after this time

  • exclude_sid – str, a sid to exclude from the count (e.g. the instructor)

Returns:

int, count of distinct students

async db.crud.count_enrolled_students() → int

Return the number of distinct enrolled students on this install.

async db.crud.count_matching_questions(name: str) → int

Count the number of Question entries that match the given name.

Parameters:

name – str, the name (div_id) of the question

Returns:

int, the number of matching questions

async db.crud.count_peer_messages(div_id: str, course_name: str, start_time: datetime) → int

Count messages sent during peer instruction for a question.

Parameters:

  • div_id – str, the question div_id

  • course_name – str, the course name

  • start_time – datetime, only count messages after this time

Returns:

int, count of messages

async db.crud.count_useinfo_for(div_id: str, course_name: str, start_date: datetime) → List[tuple]

return a list of tuples that include the [(act, count), (act, count)] act is a freeform field in the useinfo table that varies from event type to event type.

Parameters:

  • div_id (str) – Unique identifier of a Runestone component

  • course_name (str) – The current course

  • start_date (datetime.datetime) –

Returns:

A list of tuples [(act, count), (act), count)]

Return type:

List[tuple]

async db.crud.create_answer_table_entry(log_entry: LogItemIncoming, event: str) → LogItemIncoming

Populate the xxx_answers table with the incoming data

Parameters:

  • log_entry (schemas.LogItemIncoming) –

  • event (str) – Will be something like “mchoice”, “parsons”, etc.

Returns:

Returns the newly created item

Return type:

schemas.LogItemIncoming

async db.crud.create_api_token(course_id: int, provider: str, token: str) → api_tokens

Create a new API token for a course with encrypted storage.

Parameters:

  • course_id – int, the id of the course

  • provider – str, the provider name

  • token – str, plaintext token to encrypt and store

Returns:

APITokenValidator, the newly created token record

async db.crud.create_assignment(assignment: assignments) → assignments

Create a new Assignment object with the given data (assignment)

Parameters:

assignment – AssignmentValidator, the AssignmentValidator object representing the assignment data

Returns:

AssignmentValidator, the newly created AssignmentValidator object

async db.crud.create_assignment_question(assignmentQuestion: assignment_questions) → assignment_questions

Create a new AssignmentQuestion object with the given data (assignmentQuestion)

Parameters:

assignmentQuestion – AssignmentQuestionValidator, the AssignmentQuestionValidator object representing the assignment question data

Returns:

AssignmentQuestionValidator, the newly created AssignmentQuestionValidator object

async db.crud.create_book_author(author: str, document_id: str) → None

Creates a new BookAuthor object using the provided parameters and saves it in the database.

Parameters:

  • author – str, the name of the author

  • document_id – str, the unique identifier of the book

Returns:

None

async db.crud.create_code_entry(data: code) → code

Create a new code entry with the given data (data)

Parameters:

data – CodeValidator, the CodeValidator object representing the code entry data

Returns:

CodeValidator, the newly created CodeValidator object

async db.crud.create_course(course_info: courses) → None

Creates a new course in the database.

Parameters:

course_info (CoursesValidator) – A CoursesValidator instance representing the course to be created.

Returns:

None

async db.crud.create_course_attribute(course_id: int, attr: str, value: str)

Create a new course attribute for a given course (course_id)

Parameters:

  • course_id – int, the id of the course

  • attr – str, the attribute name

  • value – str, the attribute value

async db.crud.create_course_instructor(course_id: int, instructor_id: int) → None

Add an instructor to a course by creating a new CourseInstructor relationship.

Parameters:

  • course_id – int, the id of the course

  • instructor_id – int, the id of the instructor to add

Returns:

None

async db.crud.create_deadline_exception(course_id: int, username: str, time_limit: float, deadline: int, visible: bool, assignment_id: int = None, allowLink: Optional[bool] = None) → deadline_exceptions

Create a new deadline exception for a given username and assignment_id.

Parameters:

  • username – str, the username of the student

  • assignment_id – int, the id of the assignment

Returns:

DeadlineExceptionValidator, the DeadlineExceptionValidator object

async db.crud.create_domain_approval(domain_name: str, approval_type: InstrumentedAttribute) → DomainApprovals

Create a new domain approval for a given domain name and approval type. :param domain_name: str, the domain name to approve :param approval_type: sqlalchemy.orm.attributes.InstrumentedAttribute, the type of approval (e.g., ‘DomainApprovals.lti1p3’)

async db.crud.create_editor_for_basecourse(user_id: int, bc_name: str) → EditorBasecourse

Creates a new editor for a given basecourse.

Parameters:

  • user_id (int) – The ID of the user creating the editor.

  • bc_name (str) – The name of the basecourse for which the editor is being created.

Returns:

The newly created editor for the basecourse.

Return type:

EditorBasecourse

async db.crud.create_group(group_name)

Create a new group with the given name (group_name)

Parameters:

group_name – str, the name of the group to be created

Returns:

AuthGroup, the newly created AuthGroup object

async db.crud.create_initial_courses_users()

This function populates the database with the common base courses and creates a test user.

async db.crud.create_instructor_course_entry(iid: int, cid: int) → CourseInstructor

Create a new CourseInstructor entry with the given instructor id (iid) and course id (cid) Sanity checks to make sure that the instructor is not already associated with the course

Parameters:

  • iid – int, the id of the instructor

  • cid – int, the id of the course

Returns:

CourseInstructor, the newly created CourseInstructor object

async db.crud.create_invoice_request(user_id: str, course_name: str, amount: float, email: str) → InvoiceRequest

Create a new invoice request.

Parameters:

  • user_id – str, the id of the user

  • course_name – str, the name of the course

  • amount – float, the amount of the invoice

  • email – str, the email address of the user

Returns:

InvoiceRequest, the InvoiceRequest object

async db.crud.create_library_book(bookid: str, vals: Dict[str, Any]) → None

Creates a new Library object using the provided parameters and saves it in the database.

Parameters:

  • bookid – str, the unique identifier of the book

  • vals – Dict[str, Any], the dictionary containing the properties of the book

Returns:

None

async db.crud.create_lti1p1_config(course_name: str, course_id: int) → LtiKey

Generate an LTI 1.1 consumer key and secret, store them, and associate them with the given course. There is no real magic about the keys so a UUID is just as good a solution as anything.

Parameters:

  • course_name – str, the name of the course (used to build the consumer key)

  • course_id – int, the id of the course

Returns:

LtiKey, the newly created LtiKey record

async db.crud.create_lti_course(course_id: int, lti_id: str) → CourseLtiMap

Create a new course in the LTI map.

Parameters:

  • course_id – int, the id of the course

  • lti_id – str, the LTI id of the course

Returns:

CourseLtiMap, the CourseLtiMap object

async db.crud.create_membership(group_id, user_id)

Create a new membership record with the given group id (group_id) and user id (user_id)

Parameters:

  • group_id – int, the id of the group

  • user_id – int, the id of the user

Returns:

AuthMembership, the newly created AuthMembership object

async db.crud.create_question(question: questions) → questions

Add a row to the question table.

Parameters:

question (QuestionValidator) – A question object

Returns:

A representation of the row inserted.

Return type:

QuestionValidator

async db.crud.create_question_grade_entry(sid: str, course_name: str, qid: str, grade: int) → question_grades

Create a new QuestionGrade entry with the given sid, course_name, qid, and grade.

async db.crud.create_selected_question(sid: str, selector_id: str, selected_id: str, points: Optional[int] = None, competency: Optional[str] = None) → selected_questions

Create a new SelectedQuestion entry with the given sid, selector_id, selected_id, points, and competency.

Parameters:

  • sid – str, the student id

  • selector_id – str, the id of the question selector

  • selected_id – str, the id of the selected question

  • points – int, the points earned (optional)

  • competency – str, the competency (optional)

Returns:

SelectedQuestionValidator, the newly created SelectedQuestionValidator object

async db.crud.create_timed_exam_entry(sid: str, exam_id: str, course_name: str, start_time: datetime) → timed_exam

Create a new TimedExam entry with the given sid, exam_id, course_name, and start_time.

Parameters:

  • sid – str, the student id

  • exam_id – str, the id of the timed exam

  • course_name – str, the name of the course

  • start_time – datetime, the start time of the exam

Returns:

TimedExamValidator, the TimedExamValidator object

async db.crud.create_traceback(exc: Exception, request: Request, host: str)

Create a new TraceBack entry with the given Exception, Request, and host.

Parameters:

  • exc – Exception, the exception that occurred

  • request – Request, the request object

  • host – str, the hostname

async db.crud.create_useinfo_entry(log_entry: useinfo) → useinfo

Add a row to the useinfo table.

Parameters:

log_entry (UseinfoValidation) – Log entries contain a timestamp, an event, details about the event, a student id, and the identifier of the book element that was interacted with.

Returns:

A representation of the row inserted.

Return type:

UseinfoValidation

async db.crud.create_user(user: AuthUserValidator) → Optional[AuthUserValidator]

The given user will have the password in plain text. First we will hash the password then add this user to the database.

Parameters:

user – AuthUserValidator, the AuthUserValidator object representing the user to be created

Returns:

Optional[AuthUserValidator], the newly created AuthUserValidator object if successful, None otherwise

async db.crud.create_user_chapter_progress_entry(user, last_page_chapter, status) → user_chapter_progress

Create a new UserChapterProgress entry with the given user (user), chapter label (last_page_chapter), and status (status)

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object representing the user

  • last_page_chapter – str, the chapter label of the last page accessed

  • status – int, the completion status

Returns:

UserChapterProgressValidator, the newly created UserChapterProgressValidator object

async db.crud.create_user_course_entry(user_id: int, course_id: int) → UserCourse

Create a new user course entry for a given user (user_id) and course (course_id)

Parameters:

  • user_id – int, the user id

  • course_id – int, the course id

Returns:

UserCourse, the newly created UserCourse object

async db.crud.create_user_experiment_entry(sid: str, ab: str, group: int) → user_experiment

Create a new UserExperiment entry with the given sid, ab, and group.

Parameters:

  • sid – str, the student id

  • ab – str, the name of the AB experiment

  • group – int, the experiment group number

Returns:

UserExperimentValidator, the UserExperimentValidator object

async db.crud.create_user_state_entry(user_id: int, course_name: str) → user_state

Create a new UserState entry with the given user id (user_id) and course name (course_name)

Parameters:

  • user_id – int, the id of the user

  • course_name – str, the name of the course

Returns:

UserStateValidator, the newly created UserStateValidator object

async db.crud.create_user_sub_chapter_progress_entry(user, last_page_chapter, last_page_subchapter, status=-1) → user_sub_chapter_progress

Create a new UserSubChapterProgress entry with the given user (user), chapter label (last_page_chapter), subchapter label (last_page_subchapter), and status (status)

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object representing the user

  • last_page_chapter – str, the chapter label of the last page accessed

  • last_page_subchapter – str, the subchapter label of the last page accessed

  • status – int, the completion status (default is -1)

Returns:

UserSubChapterProgressValidator, the newly created UserSubChapterProgressValidator object

async db.crud.create_user_topic_practice(user: AuthUserValidator, last_page_chapter: str, last_page_subchapter: str, qname: str, now_local: datetime, now: datetime, tz_offset: float)

Add a new UserTopicPractice entry for the given user, chapter, subchapter, and question.

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object

  • last_page_chapter – str, the label of the chapter

  • last_page_subchapter – str, the label of the subchapter

  • qname – str, the name of the question to be assigned first when the topic is presented; will be rotated

  • now_local – datetime.datetime, the current local datetime

  • now – datetime.datetime, the current utc datetime

  • tz_offset – float, the timezone offset

Returns:

None

async db.crud.delete_api_token(course_id: int, token_id: Optional[int] = None) → int

Delete API token(s) for a given course. If token_id is provided, only delete the token with that id. If token_id is not provided, delete all tokens for the course.

Parameters:

  • course_id – int, the id of the course

  • token_id – Optional[int], the id of a specific token to delete (optional)

Returns:

int, the number of tokens deleted

async db.crud.delete_course_completely(course_name: str) → bool

Completely delete a course and all associated data.

WARNING: This is a destructive operation that cannot be undone.

This function will delete: - All student enrollments in the course - All assignments and grades - All course sections - Student progress data (useinfo, timed_exam, etc.) - Course customizations and settings - LTI integrations

Parameters:

course_name – str, the name of the course to delete

Returns:

bool, True if deletion was successful

async db.crud.delete_course_instructor(course_id: int, instructor_id: int) → None

Remove an instructor from a course by deleting the CourseInstructor relationship.

Parameters:

  • course_id – int, the id of the course

  • instructor_id – int, the id of the instructor to remove

Returns:

None

async db.crud.delete_datafile(acid: str, course_id: str) → bool

Delete a datafile by its acid and course_id.

Parameters:

  • acid – str, the acid of the datafile to delete

  • course_id – str, the course_id

Returns:

bool, True if deleted, False if not found

async db.crud.delete_deadline_exception(entry_id: int) → None

Delete a deadline exception by its ID.

Parameters:

entry_id – int, the ID of the deadline exception to delete

async db.crud.delete_lti1p3_course(rs_course_id: int) → int

Delete an LTI1.3 course mapping by the rs_course_id it is associated with

async db.crud.delete_lti_course(course_id: int) → bool

Delete a course from the LTI map.

Parameters:

course_id – int, the id of the course

async db.crud.delete_one_user_topic_practice(dbid: int) → None

Delete a single UserTopicPractice entry for the given id.

Used by self-paced topic selection. If a student un-marks a page as completed then if there is a card from the page it will be removed from the set of possible flashcards a student can see.

Parameters:

qid – int, the id of the UserTopicPractice entry

Returns:

None

async db.crud.delete_user(username)

Delete a user by their username (username)

Parameters:

username – str, the username of the user to be deleted

async db.crud.delete_user_course_entry(user_id: int, course_id: int) → None

Delete a user course entry for a given user (user_id) and course (course_id)

Parameters:

  • user_id – int, the user id

  • course_id – int, the course id

async db.crud.delete_user_experiment_entries(ab: str) → None

Delete all UserExperiment entries for the given AB experiment so the experiment can be re-run with fresh group assignments.

Parameters:

ab – str, the name of the AB experiment

async db.crud.did_send_messages(sid: str, div_id: str, course_name: str) → bool

Return True if the student sent at least one peer chat message for this question.

async db.crud.did_start_timed(sid: str, exam_id: str, course_name: str) → bool

Retrieve the start time for the given sid, exam_id, and course_name.

Parameters:

  • sid – str, the student id

  • exam_id – str, the id of the timed exam

  • course_name – str, the name of the course

Returns:

bool, whether the exam has started

async db.crud.duplicate_assignment(original_assignment_id: int, course_id: int, existing_assignment_names: set) → tuple[rsptx.validation.schemas.assignments, str]

Duplicate an assignment with all its exercises and readings.

Parameters:

  • original_assignment_id – int, the ID of the assignment to duplicate

  • course_id – int, the ID of the course

  • existing_assignment_names – set, set of existing assignment names to avoid duplicates

Returns:

tuple[AssignmentValidator, str], the new assignment and its name

async db.crud.fetch_all_api_tokens(course_id: int) → list[rsptx.validation.schemas.api_tokens]

Fetch all API tokens for a given course.

Parameters:

course_id – int, the id of the course

Returns:

list[APITokenValidator], list of all tokens for the course

async db.crud.fetch_all_assignment_stats(course_name: str, userid: int) → list[rsptx.validation.schemas.grades]

Fetch the Grade information for all assignments for a given student in a given course.

Parameters:

  • course_name (str) – The name of the current course

  • userid (int) – the users numeric id

Return list[AssignmentValidator]:

a list of AssignmentValidator objects

async db.crud.fetch_all_course_attributes(course_id: int) → dict

Retrieve all attributes and their values for a given course (course_id)

Parameters:

course_id – int, the id of the course

Returns:

dict, a dictionary containing all course attributes and their values

async db.crud.fetch_all_datafiles(base_course: str, course_name: str) → list

Fetch all datafiles (source_code entries) for a course.

Fetches from both base_course and course_name to support derived courses that may have copied datafiles.

Parameters:

  • base_course – str, the base course ID

  • course_name – str, the current course name

Returns:

list of SourceCodeValidator objects

async db.crud.fetch_all_deadline_exceptions(course_id: int, assignment_id: Optional[int] = None) → List[dict]

Fetch all deadline exceptions for a given course_id and optional assignment_id.

Parameters:

  • course_id – int, the id of the course

  • assignment_id – Optional[int], the id of the assignment

Returns:

List[dict], a list of deadline exception dictionaries

async db.crud.fetch_all_grades_for_assignment(assignment_id: int) → list[rsptx.validation.schemas.grades]

Fetch all grades for the given assignment id (assignment_id)

Parameters:

assignment_id – int, the id of the assignment

Returns:

List[GradeValidator], a list of GradeValidator objects

async db.crud.fetch_answers(question_id: str, event: str, course_name: str, username: str)

Fetch all answers for a given question.

Parameters:

question_id – int, the id of the question

Returns:

List[AnswerValidator], a list of AnswerValidator objects

async db.crud.fetch_api_token(course_id: int, provider: str) → Optional[api_tokens]

Fetch the least recently used API token for a given course and provider. Updates the last_used field to the current datetime when returning a token.

Parameters:

  • course_id – int, the id of the course

  • provider – str, the provider name

Returns:

Optional[APITokenValidator], the least recently used token or None if not found

async db.crud.fetch_assignment_question(assignment_name: str, question_name: str) → assignment_questions

Retrieve the AssignmentQuestion entry for the given assignment_name and question_name.

Parameters:

  • assignment_name – str, the name of the assignment

  • question_name – str, the name (div_id) of the question

Returns:

AssignmentQuestionValidator, the AssignmentQuestionValidator object

async db.crud.fetch_assignment_questions(assignment_id: int) → List[Tuple[Question, AssignmentQuestion, Chapter, SubChapter]]

Retrieve the AssignmentQuestion entry for the given assignment_name and question_name.

Parameters:

  • assignment_name – str, the name of the assignment

  • question_name – str, the name (div_id) of the question

Returns:

AssignmentQuestionValidator, AssignmentQuestionValidator, ChapterValidator, SubChapterValidator

async db.crud.fetch_assignment_release_for_div_id(course_id: int, div_id: str)

For the assignment question matching div_id in the given course, return a row with the assignment’s released flag and the question’s points.

Parameters:

  • course_id – int, the id of the course

  • div_id – str, the question name (div_id)

Returns:

a Row with released and points, or None if the question is not part of any assignment in the course.

Used by the activecode “grade report” popup (see the assignment server’s getassignmentgrade endpoint).

async db.crud.fetch_assignment_scores(assignment_id: int, course_name: str, username: str) → List[question_grades]

Fetch all scores for a given assignment.

Parameters:

  • assignment_id – int, the id of the assignment

  • course_id – int, the id of the course

  • username – str, the username of the student

Returns:

List[ScoringSpecification], a list of ScoringSpecification objects

async db.crud.fetch_assignments(course_name: str, is_peer: Optional[bool] = False, is_visible: Optional[bool] = False, fetch_all: Optional[bool] = False) → List[assignments]

Fetch all Assignment objects for the given course name. If is_peer is True then only select asssigments for peer isntruction. If is_visible is True then only fetch visible assignments (considering visible_on and hidden_on).

Parameters:

  • course_name – str, the course name

  • is_peer – bool, whether or not the assignment is a peer assignment

  • is_visible – bool, whether to filter by visibility (including scheduled visibility)

  • fetch_all – bool, whether to fetch all assignments regardless of visibility/peer status

Returns:

List[AssignmentValidator], a list of AssignmentValidator objects

async db.crud.fetch_available_students_for_instructor_add(course_id: int) → List[Dict[str, Any]]

Fetch students in the course who are not already instructors.

async db.crud.fetch_base_course(base_course: str) → courses

Fetches a base course by its name.

Parameters:

base_course (str) – The name of the base course to be fetched.

Returns:

A CoursesValidator instance representing the fetched base course.

Return type:

CoursesValidator

async db.crud.fetch_basecourse_courses(base_course: str) → List[courses]

Retrieve a list of courses that share the same base course.

Parameters:

base_course – str, the name of the base course

Returns:

List[CoursesValidator], a list of CoursesValidator objects representing the courses

async db.crud.fetch_books_by_author(author: str) → List[Tuple[Library, BookAuthor]]

Fetches all books written by a given author.

Parameters:

author (str) – The name of the author.

Returns:

A list of tuples, each containing a Library and a BookAuthor object.

Return type:

list[tuple[Library, BookAuthor]]

async db.crud.fetch_chapter_for_subchapter(subchapter: str, base_course: str) → str

Used for pretext books where the subchapter is unique across the book due to the flat structure produced by pretext build. In this case the old RST structure where we get the chapter and subchapter from the URL /book/chapter/subchapter.html gives us the wrong answer of the book.

async db.crud.fetch_code(sid: str, acid: str, course_id: int, limit: int = 0) → List[code]

Retrieve a list of the most recent code entries for the given student id (sid), assignment id (acid), and course id (course_id).

Parameters:

  • sid – str, the id of the student

  • acid – str, the id of the assignment

  • course_id – int, the id of the course

  • limit – int, the maximum number of code entries to retrieve (0 for all)

Returns:

List[CodeValidator], a list of CodeValidator objects representing the code entries

async db.crud.fetch_code_for_sid(sid: str, course_id: int) → List[code]

Return all of a student’s code rows for a course (used for the “Download All Code” CSV export).

Parameters:

  • sid – The student id (username).

  • course_id – The numeric course id (code.course_id).

Returns:

A list of CodeValidator rows ordered oldest first.

async db.crud.fetch_course(course_name: str) → courses

Fetches a course by its name.

Parameters:

course_name (str) – The name of the course to be fetched.

Returns:

A CoursesValidator instance representing the fetched course.

Return type:

CoursesValidator

async db.crud.fetch_course_by_id(course_id: int) → courses

Fetches a course by its id.

Parameters:

course_name (int) – The id of the course to be fetched.

Returns:

A CoursesValidator instance representing the fetched course.

Return type:

CoursesValidator

async db.crud.fetch_course_instructors(course_name: Optional[str] = None) → List[AuthUserValidator]

Retrieve a list of instructors for the given course name (course_name). If course_name is not provided, return a list of all instructors.

Parameters:

course_name – Optional[str], the name of the course (if provided)

Returns:

List[AuthUserValidator], a list of AuthUserValidator objects representing the instructors

async db.crud.fetch_course_practice(course_name: str) → Optional[CoursePractice]

Fetches the course practice row for a given course.

Parameters:

course_name (str) – The name of the course.

Returns:

The CoursePractice object containing the configuration of the practice feature for the given course.

Return type:

Optional[CoursePractice]

async db.crud.fetch_course_students(course_id: int) → List[AuthUserValidator]

Retrieve a list of students for the given course id (course_id)

Parameters:

course_id – int, the id of the course

Returns:

List[AuthUserValidator], a list of AuthUserValidator objects representing the students

async db.crud.fetch_courses_by_institution(institution: str) → List[courses]

Return courses whose institution fuzzy-matches the given string and whose term_start_date is within the last 9 months (i.e. active or recently started). Uses Python difflib so no pg_trgm extension is required.

Parameters:

institution – str, the institution name to match

Returns:

List[CoursesValidator]

async db.crud.fetch_courses_for_user(user_id: int, course_id: Optional[int] = None) → UserCourse

Retrieve a list of courses for a given user (user_id)

Parameters:

  • user_id – int, the user id

  • course_id – Optional[int], the id of the course (optional)

Returns:

List[UserCourse], a list of UserCourse objects representing the courses

async db.crud.fetch_current_instructors_for_course(course_id: int) → List[Dict[str, Any]]

Fetch all instructors for a given course.

async db.crud.fetch_datafile_by_acid(acid: str, course_id: str) → source_code

Fetch a datafile by its acid and course_id.

Parameters:

  • acid – str, the acid of the datafile

  • course_id – str, the course_id

Returns:

SourceCodeValidator or None

async db.crud.fetch_deadline_exception(course_id: int, username: str, assignment_id: int = None, fetch_all: bool = False) → deadline_exceptions

Fetch the deadline exception for a given username and assignment_id.

Parameters:

  • username – str, the username of the student

  • assignment_id – int, the id of the assignment

Returns:

DeadlineExceptionValidator, the DeadlineExceptionValidator object

async db.crud.fetch_grade(userid: int, assignmentid: int) → Optional[grades]

Fetch the Grade object for the given user and assignment.

Parameters:

  • userid – int, the user id

  • assignmentid – int, the assignment id

Returns:

Optional[GradeValidator], the GradeValidator object

async db.crud.fetch_gradebook(course_name: str) → dict

Build the gradebook matrix for a course: every assignment, every enrolled student (instructors excluded), and the per-(student, assignment) total score from the grades table, plus the class average per assignment.

Additive read-only aggregation used only by the Assignment Builder grader. It does not modify any shared fetch_* function.

Parameters:

course_name – str, the course name

Returns:

dict with assignments, students, cells and averages

async db.crud.fetch_group(group_name)

Retrieve a group by its name (group_name)

Parameters:

group_name – str, the name of the group

Returns:

AuthGroup, the AuthGroup object representing the group

async db.crud.fetch_instructor_courses(instructor_id: int, course_id: Optional[int] = None) → List[course_instructor]

Retrieve a list of courses for which the given instructor id (instructor_id) is an instructor. If the optional course_id value is included then return the row for that course to verify that instructor_id is an instructor for course_id

Parameters:

  • instructor_id – int, the id of the instructor

  • course_id – Optional[int], the id of the course (if provided)

Returns:

List[CourseInstructorValidator], a list of CourseInstructorValidator objects representing the courses

async db.crud.fetch_last_answer_table_entry(query_data: AssessmentRequest) → LogItemIncoming

The xxx_answers table contains ALL of the answers a student has made for this question. but most often all we want is the most recent answer

Parameters:

query_data (schemas.AssessmentRequest) –

Returns:

The most recent answer

Return type:

schemas.LogItemIncoming

async db.crud.fetch_last_course_access(sid: str, start_date: datetime) → dict

Return the most recent access time for each course a user has visited.

Looks at the useinfo table for rows belonging to sid since start_date and returns, for each course, the latest timestamp seen. Used to sort a user’s course list so recently-used courses appear first.

Parameters:

  • sid (str) – The student id (username) whose activity to inspect.

  • start_date (datetime.datetime) – Only consider activity at or after this time.

Returns:

A mapping of course_id (the course name) to the most recent access timestamp.

Return type:

dict

async db.crud.fetch_last_page(user: AuthUserValidator, course_name: str)

Retrieve the last page accessed by the given user (user) for the given course name (course_name)

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object representing the user

  • course_name – str, the name of the course

Returns:

Tuple[str, str, str, str, str], a tuple representing the last page accessed

async db.crud.fetch_last_poll_response(sid: str, course_name: str, poll_id: str) → str

Return a student’s (sid) last response to a given poll (poll_id)

Parameters:

  • sid – str, the student id

  • course_name – str, the name of the course

  • poll_id – str, the id of the poll

Returns:

str, the last response of the student for the given poll

async db.crud.fetch_last_useinfo_peergroup(course_name: str) → List[Useinfo]

Fetch the last peergroup entry for each student in the given course.

Parameters:

course_name – str, the name of the course

Returns:

List[Useinfo], a list of Useinfo objects

async db.crud.fetch_late_students_for_assignment(assignment_id: int) → List[dict]

Return the students who saved work for a given assignment after the (accommodation adjusted) deadline.

This is the multi-student counterpart to has_submissions_after_deadline for a single assignment: it answers the late-work question for every student in one query. The due date is only meaningful when the instructor enforces it (Assignment.enforce_due); when it is not enforced the result is empty. Any per-student accommodation in deadline_exceptions extends that student’s deadline. Deadlines are compared in UTC, matching the default behavior of the grading helpers.

Parameters:

assignment_id – int, the id of the assignment to check

Returns:

List[dict], one entry per late student with username, first_name and last_name, ordered by name

async db.crud.fetch_library_book(book)

Retrieve the Library entry for the given book.

Parameters:

book – str, the name of the book

Returns:

Library, the Library object

async db.crud.fetch_library_books()

Retrieve a list of visible library books ordered by shelf section and title.

Returns:

List[LibraryValidator], a list of LibraryValidator objects

async db.crud.fetch_lti1p1_config(course_id: int) → Optional[LtiKey]

Fetch the LTI 1.1 key/secret associated with a course, if any.

Parameters:

course_id – int, the id of the course

Returns:

Optional[LtiKey], the LtiKey record for the course or None

async db.crud.fetch_lti1p1_config_by_consumer(consumer: str) → Optional[LtiKey]

Fetch the LTI 1.1 key record for a given consumer key. Used to validate the OAuth signature of an incoming launch.

Parameters:

consumer – str, the oauth_consumer_key sent by the LMS

Returns:

Optional[LtiKey], the matching LtiKey record or None

async db.crud.fetch_lti1p3_assignments_by_rs_assignment_id(rs_assignment_id: int) → Lti1p3Assignment

Retrieve an LTI1.3 assignment mapping. There may be more than record as one RS course might be mapped to multiple different LTI assignments.

async db.crud.fetch_lti1p3_assignments_by_rs_course_id(rs_course_id: int) → List[Lti1p3Assignment]

Retrieve all LTI1.3 assignment mappings for a course

async db.crud.fetch_lti1p3_config(id: int) → Lti1p3Conf

Retrieve an LTI1.3 platform configuration

async db.crud.fetch_lti1p3_config_by_lti_data(issuer: str, client_id: str) → Lti1p3Conf

Retrieve an LTI1.3 platform config by issuer and client_id.

async db.crud.fetch_lti1p3_course(id: int, with_config: bool = True, with_rs_course: bool = False) → Lti1p3Course

Retrieve an LTI1.3 course by its id Also optionally fetches the associated Lti1p3Conf and/or RS Course

async db.crud.fetch_lti1p3_course_by_id(id: int, with_config: bool = True, with_rs_course: bool = False) → Lti1p3Course

Retrieve an LTI1.3 platform config by its id Also optionally fetches the associated Lti1p3Conf and/or Course

async db.crud.fetch_lti1p3_course_by_lti_data(issuer: str, client_id: str, deploy_id: str, with_config: bool = True) → Lti1p3Course

Retrieve an LTI1.3 platform config by issuer and client_id. Also fetches the associated Lti1p3Conf

async db.crud.fetch_lti1p3_course_by_rs_course(rs_course: courses, with_config: bool = True) → Lti1p3Course

Retrieve an LTI1.3 platform config by its id Also optionally fetches the associated Lti1p3Conf and/or Course

async db.crud.fetch_lti1p3_courses_by_lti_course_id(lti_course_id: str, with_config: bool = True, with_rs_course: bool = False) → List[Lti1p3Course]

Retrieve an LTI1.3 platform config by its lti identifier Also optionally fetches the associated Lti1p3Conf and/or Course

async db.crud.fetch_lti1p3_grading_data_for_assignment(rs_assignment_id: int) → Lti1p3Assignment

Fetch data needed to submit grades for a particular assignment

async db.crud.fetch_lti1p3_user(rs_user_id: int, lti1p3_course_id: int) → Lti1p3User

Retrieve a user’s LTI1.3 mapping for a particular course

async db.crud.fetch_lti1p3_users_for_course(lti1p3_course_id: int, with_rsuser: bool = True) → List[Lti1p3User]

Retrieve all LTI1.3 user mapping for a particular course

async db.crud.fetch_lti_version(course_id: int) → str

Check if a course uses LTI 1.1, 1.3 or none

Parameters:

course_id – int, the id of the course

Returns:

str for LTI version (1.1 or 1.3) or None

async db.crud.fetch_matching_questions(request_data: SelectQRequest) → List[str]

Return a list of question names (div_ids) that match the criteria for a particular question. This is used by select questions and in particular get_question_source

async db.crud.fetch_membership(group_id, user_id)

Retrieve a membership record by the group id (group_id) and user id (user_id)

Parameters:

  • group_id – int, the id of the group

  • user_id – int, the id of the user

Returns:

AuthMembership, the AuthMembership object representing the membership record

async db.crud.fetch_one_assignment(assignment_id: int) → assignments

Fetch one Assignment object with calculated total points for related exercises.

Parameters:

assignment_id – int, the assignment id

Returns:

AssignmentValidator

async db.crud.fetch_one_course_attribute()

Fetch a single course attribute (not implemented)

Raises:

NotImplementedError

async db.crud.fetch_one_user_topic_practice(user: AuthUserValidator, last_page_chapter: str, last_page_subchapter: str) → user_topic_practice

The user_topic_practice table contains information about each topic (flashcard) that a student is eligible to see for a given topic in a course. A particular topic should ony be in the table once per student. This row also contains information about scheduling and correctness to help the practice algorithm select the best question to show a student.

Retrieve a single UserTopicPractice entry for the given user, chapter, and subchapter (i.e., topic).

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object

  • last_page_chapter – str, the label of the chapter

  • last_page_subchapter – str, the label of the subchapter

  • qname – str, the name of the question

Returns:

UserTopicPracticeValidator, the UserTopicPracticeValidator object

async db.crud.fetch_page_activity_counts(chapter: str, subchapter: str, base_course: str, course_name: str, username: str) → Dict[str, int]

Used for the progress bar at the bottom of each page. This function finds all of the components for a particular page (chaper/subchapter) and then finds out which of those elements the student has interacted with. It returns a dictionary of {divid: 0/1}

async db.crud.fetch_peer_useinfo(sid: str, div_id: str, course_name: str) → list

Fetch all useinfo rows for a student on a peer question. Used to compute peer instruction scores (vote1, vote2, sendmessage).

async db.crud.fetch_poll_summary(div_id: str, course_name: str) → List[tuple]

Find the last answer for each student and then aggregate those answers to provide a summary of poll responses for the given question. For a poll, the value of act is a response number 0–N where N is the number of different choices.

Parameters:

  • div_id (str) – The div_id of the poll

  • course_name (str) – The name of the course

Returns:

A list of tuples where the first element is the response number and the second element is the count of students who chose that response.

Return type:

List[tuple]

async db.crud.fetch_previous_selections(sid) → List[str]

Retrieve a list of selected question ids for the given student id (sid).

Parameters:

sid – str, the student id

Returns:

List[str], a list of selected question ids

async db.crud.fetch_problem_data(assignment_id: int, course_name: str) → list

Fetch problem data for a given assignment.

Parameters:

assignment_id – int, the id of the assignment

Returns:

list, a list of tuples containing timestamp, name, sid, event, and act

async db.crud.fetch_qualified_questions(base_course: str, chapter_label: str, sub_chapter_label: str) → list[rsptx.validation.schemas.questions]

Retrieve a list of qualified questions for a given chapter and subchapter.

Parameters:

  • base_course – str, the base course

  • chapter_label – str, the label of the chapter

  • sub_chapter_label – str, the label of the subchapter

Returns:

list[QuestionValidator], a list of QuestionValidator objects

async db.crud.fetch_question(name: str, basecourse: Optional[str] = None, assignment: Optional[str] = None) → questions

Fetch a single matching question row from the database that matches the name (div_id) of the question. If the base course is provided make sure the question comes from that basecourse. basecourse,name pairs are guaranteed to be unique in the questions table

More and more questions have globally unique names in the runestone database and that is definitely a direction to keep pushing. But it is possible that there are duplicates but we are not going to worry about that we are just going to return the first one we find.

Parameters:

  • name – str, the name (div_id) of the question

  • basecourse – str, the base course (optional)

  • assignment – str, the assignment (optional)

Returns:

QuestionValidator, the QuestionValidator object

async db.crud.fetch_question_by_id(question_id: int) → Optional[questions]

Fetch a single question row by its primary key id.

Useful for authorization checks where the caller supplies a question id and we need to confirm which base course the existing row belongs to.

Parameters:

question_id – int, the primary key of the question

Returns:

QuestionValidator if a matching row exists, otherwise None

async db.crud.fetch_question_count_per_subchapter(course_name: str) → Dict[Dict[str, str], int]

Return a dictionary of subchapter_id: count of questions in that subchapter

async db.crud.fetch_question_grade(sid: str, course_name: str, qid: str)

Retrieve the QuestionGrade entry for the given sid, course_name, and qid.

Parameters:

  • sid – str, the student id

  • course_name – str, the course name

  • qid – str, the question id (div_id)

Returns:

QuestionGradeValidator, the QuestionGradeValidator object

async db.crud.fetch_questions_by_search_criteria(criteria: SearchSpecification) → List[questions]

Fetch a list of questions that match the search criteria regular expression matches are case insensitive

Parameters:

search – str, the search string

Returns:

List[QuestionValidator], a list of QuestionValidator objects

async db.crud.fetch_questions_for_chapter_subchapter(base_course: str, skipreading: bool = False, from_source_only: bool = True, pages_only: bool = False, owner: Optional[str] = None) → List[dict]

Fetch all questions for a given base course, where the skipreading and from_source flags are set to the given values.

Parameters:

  • base_course – str, the base course

  • skipreading – bool, whether to skip questions/sections marked as “skipreading” Usually these sections are the Exercises sections at the end of chapters.

  • from_source_only – bool, whether the question is from the source, if this is True then instructor contributed questions will not be included in the result.

  • pages_only – bool, whether to include only pages for reading assignment creation.

Returns:

List[dict], a list of questions in a hierarchical json structure

async db.crud.fetch_reading_assignment_data(assignment_id: int, sid: str) → list

Fetch reading assignment data for a given assignment and student id.

Parameters:

  • assignment_id – int, the id of the assignment

  • sid – str, the student id

Returns:

list[AssignmentQuestionValidator], a list of AssignmentQuestionValidator objects

async db.crud.fetch_reading_assignment_spec(chapter: str, subchapter: str, course_id: int, timezone: Optional[str] = 'UTC') → Optional[int]

Check if a reading assignment is assigned for a given chapter and subchapter.

Parameters:

  • chapter – str, the label of the chapter

  • subchapter – str, the label of the subchapter

  • course_id – int, the id of the course

Returns:

The number of required activities or None if not found

async db.crud.fetch_recent_student_answers(div_id: str, course_name: str, start_time: datetime, limit: int = 4000, exclude_sid: str = None) → List[Tuple[str, str, str]]

Fetch the most recent answer for each student for a given question.

Parameters:

  • div_id – str, the question div_id

  • course_name – str, the course name

  • start_time – datetime, only fetch answers after this time

  • limit – int, maximum number of results to return

  • exclude_sid – str, a sid to exclude (e.g. the instructor)

Returns:

List[Tuple[str, str, str]], list of (sid, answer, correct) tuples

async db.crud.fetch_recent_useinfo(sid: str, course_name: str, days: int = 7) → List[useinfo]

Return the student’s useinfo activity for the last days days, most recent first. Used to build the “Recent Activity” table on the student progress report.

Parameters:

  • sid – The student id (username).

  • course_name – The course name (useinfo.course_id).

  • days – How many days back to look.

Returns:

A list of UseinfoValidation rows ordered newest first.

async db.crud.fetch_selected_question(sid: str, selector_id: str) → selected_questions

Retrieve the SelectedQuestion entry for the given sid and selector_id.

Parameters:

  • sid – str, the student id

  • selector_id – str, the id of the question selector

Returns:

SelectedQuestionValidator, the SelectedQuestionValidator object

async db.crud.fetch_source_code(base_course: str, course_name: str, acid: Optional[str] = None, filename: Optional[str] = None) → source_code

Fetch the source code for a given acid or filename

Note that filenames are not guaranteed to be unique within a course, so acid is the preferred lookup method.

Parameters:

acid – str, the acid of the source code

Returns:

SourceCodeValidator, the SourceCodeValidator object

async db.crud.fetch_student_answers_in_timerange(div_id: str, course_name: str, start_time: datetime, end_time: Optional[datetime] = None, limit: int = 4000, exclude_sid: str = None) → List[Tuple[str, str]]

Fetch the most recent answer for each student within a time range.

Parameters:

  • div_id – str, the question div_id

  • course_name – str, the course name

  • start_time – datetime, only fetch answers after this time

  • end_time – Optional[datetime], only fetch answers before this time

  • limit – int, maximum number of results to return

  • exclude_sid – str, a sid to exclude (e.g. the instructor)

Returns:

List[Tuple[str, str]], list of (sid, answer) tuples

async db.crud.fetch_subchapters(course, chap)

Retrieve all subchapters for a given chapter.

Parameters:

  • course – str, the name of the course

  • chap – str, the label of the chapter

Returns:

ResultProxy, the result of the query

async db.crud.fetch_timed_assessments(course_id: int) → List[Tuple[str, str]]

Retrieve all timed assessments for a given course.

Parameters:

course_id – int, the course id

Returns:

List[Tuple[str, str]], list of (name, description) tuples for timed assessments

async db.crud.fetch_timed_exam(sid: str, exam_id: str, course_name: str) → timed_exam

Retrieve the TimedExam entry for the given sid, exam_id, and course_name.

Parameters:

  • sid – str, the student id

  • exam_id – str, the id of the timed exam

  • course_name – str, the name of the course

Returns:

TimedExamValidator, the TimedExamValidator object

async db.crud.fetch_top10_fitb(dbcourse: courses, div_id: str) → List[tuple]

Return the top 10 answers to a fill in the blank question.

Parameters:

  • dbcourse (CoursesValidator) – The course for which to retrieve the top answers.

  • div_id (str) – The div_id of the fill in the blank question.

Returns:

A list of tuples where the first element is the answer and the second element is the count of times that answer was given.

Return type:

List[tuple]

async db.crud.fetch_useinfo_for_sid(sid: str, course_name: str) → List[useinfo]

Return all of a student’s useinfo rows for a course (used for the “Download History” CSV export).

Parameters:

  • sid – The student id (username).

  • course_name – The course name (useinfo.course_id).

Returns:

A list of UseinfoValidation rows ordered oldest first.

async db.crud.fetch_user(user_name: str, fallback_to_registration: bool = False) → AuthUserValidator

Retrieve a user by their username (user_name)

fallback_to_registration is for LTI logins to match accounts with usernames that have been modified. If user_name is not found, try to find the user by their registration_id (initial id).

Parameters:

user_name – str, the username of the user

Returns:

AuthUserValidator, the AuthUserValidator object representing the user

async db.crud.fetch_user_by_email(email: str) → Optional[AuthUserValidator]
async db.crud.fetch_user_chapter_progress(user, last_page_chapter: str) → user_chapter_progress

Retrieve the UserChapterProgress entry for the given user (user) and chapter label (last_page_chapter).

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object representing the user

  • last_page_chapter – str, the chapter label of the last page accessed

Returns:

UserChapterProgressValidator, the UserChapterProgressValidator object

async db.crud.fetch_user_experiment(sid: str, ab_name: str) → int

When a question is part of an AB experiement (ab_name) get the experiment group for a particular student (sid). The group number will have been randomly assigned by the initial question selection.

This number indicates whether the student will see the 1st or 2nd question in the question list.

Parameters:

  • sid – str, the student id

  • ab_name – str, the name of the AB experiment

Returns:

int, the experiment group number

async db.crud.fetch_user_sub_chapter_progress(user, last_page_chapter=None, last_page_subchapter=None) → List[user_sub_chapter_progress]

Retrieve the UserSubChapterProgress entries for the given user (user) and optional chapter and subchapter.

Parameters:

  • user – AuthUserValidator, the AuthUserValidator object representing the user

  • last_page_chapter – str, the chapter label of the last page accessed (optional)

  • last_page_subchapter – str, the subchapter label of the last page accessed (optional)

Returns:

List[UserSubChapterProgressValidator], a list of UserSubChapterProgressValidator objects

async db.crud.fetch_users_for_course(course_name: str) → list[db.models.AuthUserValidator]

Retrieve a list of users/students enrolled in a given course (course_name)

Parameters:

course_name – str, the name of the course

Returns:

list[AuthUserValidator], a list of AuthUserValidator objects representing the users

async db.crud.fetch_viewed_questions(sid: str, questionlist: List[str]) → List[str]

Retrieve a list of questions from the given questionlist that a student (sid) has viewed before. Used for the selectquestion get_question_source to filter out questions that a student has seen before. One criteria of a select question is to make sure that a student has never seen a question before.

The best approximation we have for that is that they will have clicked on the run button for that question. Of course, they may have seen the question but not run it, but this is the best we can do.

Parameters:

  • sid – str, the student id

  • questionlist – List[str], a list of question ids (div_id)

Returns:

List[str], a list of question ids from the given questionlist that the student has viewed before

async db.crud.gather_served_books() → List[str]

Return the list of visible base course names this install serves.

db.crud.generate_datafile_acid(filename: str, owner: str, course_id: str) → str

Generate a unique acid for a datafile based on filename, owner, and course_id.

Parameters:

  • filename – str, the filename

  • owner – str, the owner (username)

  • course_id – str, the course_id

Returns:

str, the generated acid

async db.crud.get_book_chapters(course_name: str) → List[chapters]

Retrieve all chapters for a given course (course_name)

Parameters:

course_name – str, the name of the course

Returns:

List[ChapterValidator], a list of ChapterValidator objects representing the chapters

async db.crud.get_book_subchapters(course_name: str) → List[sub_chapters]

Retrieve all subchapters for a given course (course_name)

Parameters:

course_name – str, the name of the course

Returns:

List[SubChapterValidator], a list of SubChapterValidator objects

async db.crud.get_course_origin(base_course: str)

Retrieve the origin (markup system) of a given course by its name.

Parameters:

base_course – str, the name of the base course (i.e. courses.course_name)

Returns:

str, the value of the markup_system course attribute, or None if not found

async db.crud.get_courses_per_basecourse() → dict

Gets the number of courses using a basecourse.

Returns:

A dictionary containing the base course name and the number of courses using it.

Return type:

Dict[str,int]

async db.crud.get_last_sent() → Optional[datetime]

Return the time of the last successful check-in, or None if never sent.

async db.crud.get_or_create_instance_id() → str

Return this install’s stable, anonymous instance id, creating it once.

Returns:

A random UUIDv4 string that persists for the life of the install.

Return type:

str

async db.crud.get_peer_votes(div_id: str, course_name: str, voting_stage: int)

Provide the answers for a peer instruction multiple choice question. What percent of students chose each option. This is used for the Review page of Peer Instruction questions.

async db.crud.get_repo_path(book: str) → Optional[str]

Get the repo_path for a book from the library table

Parameters:

book – book name (basecourse)

Returns:

repo_path or None if not found

async db.crud.get_students_per_basecourse() → dict

Gets the number of students using a book for each course.

Returns:

A dictionary containing the course name and the number of students using it.

Return type:

Dict[str,int]

async db.crud.has_submissions_after_deadline(username: str, assignment_id: int, deadline: datetime) → bool

Check whether a student has any logged activity (useinfo) for a question that belongs to the given assignment after the supplied deadline.

Looking at the useinfo table is intentional: it captures a row for every save/submission and is far cheaper than querying each of the per-type *_answers tables individually.

Parameters:

  • username – str, the student’s username (matches useinfo.sid)

  • assignment_id – int, the id of the assignment to check

  • deadline – datetime, naive UTC cutoff; activity recorded strictly after this time is considered late

Returns:

bool, True if the student logged work after the deadline

async db.crud.is_assigned(question_id: str, course_id: int, assignment_id: Optional[int] = None, accommodation: Optional[deadline_exceptions] = None, timezone: Optional[str] = 'UTC') → ScoringSpecification

Check if a question is part of an assignment. If the assignment is not visible, the question is not considered assigned. If the assignment is not yet due – no problem. If the assignment is past due but the instructor has not enforced the due date – no problem. If the assignment is past due but the instructor has not enforced the due date, but HAS released the assignment – then no longer assigned.

Parameters:

  • question_id – str, the name of the question

  • course_id – int, the id of the course

Returns:

ScoringSpecification, the scoring specification object

async db.crud.is_author(userid: int) → bool

Checks if a user is an author.

Parameters:

userid (int) – The ID of the user to check.

Returns:

True if the user is an author, False otherwise.

Return type:

bool

async db.crud.is_editor(userid: int) → bool

Checks if a user is an editor.

Parameters:

userid (int) – The ID of the user to check.

Returns:

True if the user is an editor, False otherwise.

Return type:

bool

async db.crud.is_server_feedback(div_id: str, course: str) → Optional[Dict[str, Any]]

Check if server feedback is available for the given div id (div_id) and course name (course). If server feedback is available and login is required, return the decoded feedback.

Parameters:

  • div_id – str, the id of the div element

  • course – str, the name of the course

Returns:

Optional[Dict[str, Any]], a dictionary representing the decoded feedback (if available)

async db.crud.remove_assignment_questions(assignment_ids: List[int])

Remove all assignment questions for the given assignment ids (assignment_ids)

async db.crud.reorder_assignment_questions(question_ids: List[int])

Reorder the assignment questions with the given question ids (question_ids)

async db.crud.replace_user_experiment_entries(ab: str, assignments: List[Tuple[str, int]]) → None

Replace all UserExperiment entries for the given AB experiment in a single call. It will delete any existing assignments then insert the new ones. This keeps re-running the experiment fast and atomic for larger courses.

Parameters:

  • ab – str, the name of the AB experiment

  • assignments – list of (sid, group) tuples to insert

async db.crud.reset_student_assessment(username: str, assessment_name: str, course_name: str) → bool

Reset a student’s timed assessment by: 1. Updating useinfo records to set act=”start_reset” 2. Deleting timed_exam records 3. Deleting selected_questions records for exam questions

Parameters:

  • username – str, the student’s username

  • assessment_name – str, the name of the assessment to reset

  • course_name – str, the name of the course

Returns:

bool, True if reset was successful

async db.crud.search_exercises(criteria: ExercisesSearchRequest, owner: str) → dict

Smart search for exercises with pagination, filtering, and sorting.

Parameters:

criteria – Search parameters including filters, pagination, and sorting

Returns:

Dictionary with search results and pagination metadata

async db.crud.set_last_sent(when: datetime) → None

Record the time of a successful check-in.

async db.crud.set_manual_total(userid: int, assignmentid: int, course_name: str, score: float, manual: bool = True) → grades

Set the grades row total for a student and flag whether it was set manually. When manual is True, recompute_totals_for preserves this score instead of overwriting it from the per-question grades; when False the row becomes eligible for recomputation again.

Parameters:

  • userid – int, the auth_user id

  • assignmentid – int, the assignment id

  • course_name – str, the course name (used only when inserting a new row)

  • score – float, the total score to store

  • manual – bool, the value for the manual_total flag

Returns:

GradeValidator, the upserted grade

async db.crud.set_reset_token(user_id: int) → str
async db.crud.update_assignment(assignment: assignments, pi_update=False) → None

Update an Assignment object with the given data (assignment)

async db.crud.update_assignment_exercises(payload: UpdateAssignmentExercisesPayload)
async db.crud.update_assignment_question(assignmentQuestion: assignment_questions) → assignment_questions

Update an AssignmentQuestion object with the given data (assignmentQuestion)

async db.crud.update_assignment_released(assignment_id: int, released: bool) → None

Set only the released flag on an Assignment, controlling whether students can see their grades. Additive, focused update: it touches released and updated_date only and never alters current_index or other fields.

async db.crud.update_assignment_threshold(assignment_id: int, threshold_pct: Optional[float]) → None

Set only the threshold_pct field on an Assignment, controlling threshold scoring during the recompute roll-up. Additive, focused update: it touches threshold_pct and updated_date only and never alters current_index or other fields. A null or zero threshold disables threshold scoring (recompute then uses the raw per-question sum).

async db.crud.update_course_settings(course_id: int, setting: str, value: str) → None

Update a course setting/attribute. Handles both special course table fields and course attributes.

Parameters:

  • course_id – int, the id of the course

  • setting – str, the setting name to update

  • value – str, the value to set

Returns:

None

Raises:

ValueError – If date format is invalid for new_date setting

async db.crud.update_datafile(acid: str, course_id: str, main_code: str) → bool

Update an existing datafile’s content. Note: Filename cannot be changed after creation.

Parameters:

  • acid – str, the acid of the datafile to update

  • course_id – str, the course_id

  • main_code – str, new content

Returns:

bool, True if updated, False if not found

async db.crud.update_deadline_exception(entry_id: int, time_limit: Optional[float] = None, deadline: Optional[int] = None, visible: Optional[bool] = None, allowLink: Optional[bool] = None) → Optional[deadline_exceptions]

Update an existing deadline exception identified by its primary key.

Parameters:

entry_id – int, the id of the deadline exception row

Returns:

the updated DeadlineExceptionValidator or None when not found

async db.crud.update_library_book(bookid: int, vals: dict)

Update the Library entry with the given bookid and values.

Parameters:

  • bookid – int, the id of the book

  • vals – dict, a dictionary of values to update

async db.crud.update_multiple_assignment_questions(exercises: List[AssignmentQuestionUpdateDict]) → list[rsptx.validation.schemas.assignment_questions]

Update multiple AssignmentQuestion objects with the given data (exercises). Also updates the Question table for fields like question_json, htmlsrc, chapter, subchapter, author, autograde, topic, feedback, name, difficulty, and tags if the user is the owner.

Parameters:

exercises – List of dictionaries with fields from both AssignmentQuestionValidator and QuestionValidator

Returns:

List of updated AssignmentQuestionValidator objects

async db.crud.update_question(question: questions) → questions

Update a row in the question table.

Parameters:

question (QuestionValidator) – A question object

Returns:

A representation of the row updated.

Return type:

QuestionValidator

async db.crud.update_question_grade_entry(sid: str, course_name: str, qid: str, grade: int, qge_id: Optional[int] = None) → question_grades

Create a new QuestionGrade entry with the given sid, course_name, qid, and grade.

async db.crud.update_selected_question(sid: str, selector_id: str, selected_id: str)

Update the selected_id of the SelectedQuestion entry for the given sid and selector_id.

Parameters:

  • sid – str, the student id

  • selector_id – str, the id of the question selector

  • selected_id – str, the id of the selected question

async db.crud.update_source_code(acid: str, filename: str, course_id: str, main_code: str, owner: str = None)

Update the source code for a given acid or filename

db.crud.update_source_code_sync(acid: str, filename: str, course_id: str, main_code: str, owner: str = None)

Update the source code for a given acid or filename

async db.crud.update_sub_chapter_progress(user_data: LastPageData)

Update the UserSubChapterProgress entry with the given user data (user_data)

Parameters:

user_data – LastPageData, the LastPageData object representing the user data

async db.crud.update_user(user_id: int, new_vals: dict)

Update a user’s information by their id (user_id)

Parameters:

  • user_id – int, the id of the user

  • new_vals – dict, a dictionary containing the new values to be updated

async db.crud.update_user_state(user_data: LastPageData)

Update the UserState entry with the given user data (user_data)

Parameters:

user_data – LastPageData, the LastPageData object representing the user data

async db.crud.upsert_deadline_exception(course_id: int, username: str, time_limit: Optional[float] = None, deadline: Optional[int] = None, visible: Optional[bool] = None, assignment_id: Optional[int] = None, allowLink: Optional[bool] = None) → deadline_exceptions

Create or update a deadline exception for a (course, student, assignment) triple.

Uniqueness is enforced on (course_id, sid, assignment_id); a NULL assignment_id represents an exception that applies to every assignment for the student.

Parameters:

  • course_id – int, the id of the course

  • username – str, the username of the student

  • time_limit – Optional[float], multiplier for a timed-exam time limit

  • deadline – Optional[int], number of days to extend the deadline

  • visible – Optional[bool], override the assignment visibility

  • assignment_id – Optional[int], the assignment the exception applies to

  • allowLink – Optional[bool], allow link access even when not visible

Returns:

DeadlineExceptionValidator, the persisted exception

async db.crud.upsert_grade(grade: grades) → grades

Insert a new Grade object into the database or update an existing one.

Parameters:

grade – GradeValidator, the GradeValidator object

Returns:

GradeValidator, the GradeValidator object

async db.crud.upsert_installation(payload: dict) → None

Insert or update the Installation record for an incoming check-in.

Matched by instance_id: first_seen is set once, last_seen on every check-in. Deliberately ignores any network/IP information – the only location stored is the self-declared region in the payload.

Parameters:

payload (dict) – The validated check-in payload.

Record the LTI 1.1 grade passback identifiers (lis_result_sourcedid and lis_outcome_url) on a student’s grade row for an assignment, creating the row if it does not yet exist. Called when an assignment is launched from the LMS.

Parameters:

  • user_id – int, the auth_user id

  • assignment_id – int, the assignment id

  • result_sourcedid – str, the LMS-provided result identifier

  • outcome_url – str, the LMS outcome service URL

Returns:

tuple of (current score, first_link) where first_link is True when an existing grade row had no passback identifiers before this call (i.e. this is the first launch, and an already-released grade should be pushed).

async db.crud.upsert_lti1p3_assignment(assignment: Lti1p3Assignment) → Lti1p3Assignment

Insert or update an LTI1.3 assignment mapping.

async db.crud.upsert_lti1p3_config(config: Lti1p3Conf) → Lti1p3Conf

Insert or update an LTI1.3 platform config. issuer and client_id must be provided. If they match an existing record, all other fields are optional and only need to be provided if they are to be updated.

async db.crud.upsert_lti1p3_course(course: Lti1p3Course) → Lti1p3Course

Insert or update an LTI1.3 course.

rs_course_id must be provided and will be used to identify the record to update. all other fields are optional and only need to be provided if they are to be updated.

async db.crud.upsert_lti1p3_user(user: Lti1p3User) → Lti1p3User

Insert or update an LTI1.3 user mapping for a particular course

Record (or refresh) a student’s practice_grades row and, when provided, the LTI 1.1 grade passback identifiers. Called when the practice tool is launched from the LMS. When the identifiers are omitted they are left untouched so an existing passback link is not clobbered.

Parameters:

  • user_id – int, the auth_user id

  • course_name – str, the course name

  • result_sourcedid – Optional[str], the LMS-provided result identifier

  • outcome_url – Optional[str], the LMS outcome service URL

Returns:

PracticeGrade, the upserted practice grade row

async db.crud.user_in_course(user_id: int, course_id: int) → bool

Return true if given user is in indicated course

Parameters:

  • user_id – int, the user id

  • course_id – the id of the course

Returns:

True / False

async db.crud.validate_user_credentials(username: str, password: str) → Optional[AuthUserValidator]

Validate a user’s credentials by their username and password.

Parameters:

  • username – str, the username of the user

  • password – str, the password of the user

Returns:

Optional[AuthUserValidator], the AuthUserValidator object representing the user if valid, None otherwise