db_ebook.py - Runestone eBook Tables¶
This module contains the database table definitions
Files in the model directory are loaded in alphabetical order. This one needs to be loaded after db.py
web2py does not allow you to specify indices programmatically. The indexes for each table should be
documented here, but may not be. The command rsmanage init contains index creation statements for
all performance critical tables.
useinfo¶
The largest and busiest table in the whole database!
This tracks click stream information on every student
Having indices on this table is critical, here is what we have:
create index "course_id_index" on useinfo using btree (course_id);
create index "div_id_index" on useinfo using btree (div_id);
create index "event_index" on useinfo using btree (event);
create index "sid_index" on useinfo using btree (sid);
create index "timestamp_idx" on useinfo using btree ("timestamp");
stores student’s saved code and, unfortunately, comments and grades, which really should be their own table linked to this code —-
Stores the source code for activecodes, including prefix and suffix code, so that prefixes and suffixes can be run when grading Contents of this table are filled when processing activecode directives, in activecod.py source_code ———–
db.define_table(
"source_code",
Field("acid", "string", required=True),
Field("course_id", "string"),
Field(
"includes", "string"
), # comma-separated string of acid main_codes to include when running this source_code
Field(
"available_files", "string"
), # comma-separated string of file_names to make available as divs when running this source_code
Field("main_code", "text"),
Field("suffix_code", "text"), # hidden suffix code
migrate=bookserver_owned("source_code"),
)
##table to store the last position of the user. 1 row per user, per course
user_state¶
db.define_table(
"user_state",
Field("user_id", "integer"),
Field("course_name", "string"),
Field("last_page_url", "string"),
Field("last_page_hash", "string"),
Field("last_page_chapter", "string"),
Field("last_page_subchapter", "string"),
Field("last_page_scroll_location", "string"),
Field("last_page_accessed_on", "datetime"),
migrate=bookserver_owned("user_state"),
)
Table to match instructor(s) to their course(s) course_instructor —————–
db.define_table(
"course_instructor",
Field("course", db.courses),
Field("instructor", db.auth_user),
Field(
"verified", "boolean"
), # some features we want to take the extra step of verifying an instructor - such as instructor guide
Field("paid", "boolean"), # in the future some instructor features will be paid
migrate=bookserver_owned("course_instructor"),
)
timed_exam¶
db.define_table(
"timed_exam",
Field("timestamp", "datetime"),
Field("div_id", "string"),
Field("sid", "string"),
Field("course_name", "string"),
Field("correct", "integer"),
Field("incorrect", "integer"),
Field("skipped", "integer"),
Field("time_taken", "integer"),
Field("reset", "boolean"),
migrate=bookserver_owned("timed_exam"),
)
mchoice_answers¶
- define the following indices
CREATE INDEX mchoice_answers_course_name_idx ON mchoice_answers USING btree (course_name); CREATE INDEX mchoice_answers_div_id_idx ON mchoice_answers USING btree (div_id); CREATE INDEX mchoice_answers_sid_idx ON mchoice_answers USING btree (sid);
fitb_answers¶
dragndrop_answers¶
db.define_table(
"dragndrop_answers",
Field("timestamp", "datetime"),
Field("div_id", "string"),
Field("sid", "string"),
Field("course_name", "string"),
Field("answer", "string"),
Field("correct", "boolean"),
Field("min_height", "string"),
Field("percent", "double"),
migrate=bookserver_owned("dragndrop_answers"),
)
clickablearea_answers¶
parsons_answers¶
db.define_table(
"parsons_answers",
Field("timestamp", "datetime"),
Field("div_id", "string"),
Field("sid", "string"),
Field("course_name", "string"),
Field("answer", "string"),
Field("source", "string"),
Field("correct", "boolean"),
Field("percent", "double"),
migrate=bookserver_owned("parsons_answers"),
)
codelens_answers¶
db.define_table(
"codelens_answers",
Field("timestamp", "datetime"),
Field("div_id", "string"),
Field("sid", "string"),
Field("course_name", "string"),
Field("answer", "string"),
Field("source", "string"),
Field("correct", "boolean"),
Field("percent", "double"),
migrate=bookserver_owned("codelens_answers"),
)
shortanswer_answers¶
unittest_answers¶
- define the following indices
CREATE INDEX unittest_answers_course_name_idx ON unittest_answers USING btree (course_name); CREATE INDEX unittest_answers_div_id_idx ON unittest_answers USING btree (div_id); CREATE INDEX unittest_answers_sid_idx ON unittest_answers USING btree (sid);
db.define_table(
"unittest_answers",
Field("timestamp", "datetime"),
Field("div_id", "string"),
Field("sid", "string"),
Field("course_name", "string"),
Field("answer", "string"),
Field("passed", "integer"),
Field("failed", "integer"),
Field("correct", "boolean"),
Field("percent", "double"),
migrate=bookserver_owned("unittest_answers"),
)
webwork_answers¶
webwork_answers¶
payments¶
A Stripe charge ID. Per the Stripe docs, this is always 255 characters or less.
lp_answers¶
invoice_request¶
editor_basecourse¶
This table tracks the userid of a person who has editing authority over a particular book.
course_attributes¶
The course attribute table allows us to add parameters to each course without having to add columns to the courses table every time we have something new to store. for example we could have a “source” key value pair to indicate if a course is built with runestone or pretext, or to store the latex macros for a pretext course TODO: migrate allow_pairs, download_enabled, and others from courses to this table.
selected_questions¶
We define a table to keep track of the questions that were randomly selected for
each student in a generated exam.
The following index is important for fast performance to lookup the question selected for
a strudent, but will also keep us from having duplicate entries and causing confusion!
CREATE UNIQUE INDEX selector_sid_unique ON selected_questions USING btree (selector_id, sid)
db.define_table(
"selected_questions",
Field("selector_id", "string"),
Field("sid", "string"),
Field("selected_id", "string"),
Field("points", "integer"),
Field("competency", "string"),
migrate=bookserver_owned("selected_questions"),
)
db.define_table(
"user_experiment",
Field("experiment_id", "string"),
Field("sid", "string"),
Field("exp_group", "integer"),
migrate=bookserver_owned("user_experiment"),
)
def getCourseAttribute(course_id: int, attr_name: str):
res = (
db(
(db.course_attributes.course_id == course_id)
& (db.course_attributes.attr == attr_name)
)
.select(db.course_attributes.value, **SELECT_CACHE)
.first()
)
if res:
return res.value
else:
return None
this check has to be here to ensure that the course_attributes table is defined.
- if auth.user:
- if getCourseAttribute(auth.user.course_id, “lti_interface”):
settings.lti_interface = True