models.py - definition of database models

In this file we define our SQLAlchemy data models. These get translated into relational database tables.

Many of the models in this file were generated automatically for us by the sqlacodegen tool See pypi for details Although we are defining these using the declarative base style we will be using the SQLAlchemy Core for queries. We are using SQLAlchemy 1.4.x in preparation for 2.0 and the more unified interface it provides for mixing declarative with core style queries.

The models are created to be backward compatible with our current web2py implementation of Runestone. Some decisions we would make differently but we won’t be changing those things until we port the instructor interface to the FastAPI framework.

Migrations

We use Alembic for tracking database migration information. To create a new migration automatically after you have made changes to this file, run alembic revision --autogenerate -m "simple message" this will generate a new file in alembic/versions. To apply changes to the database run alembic upgrade head.

docs to write: It is also possible…

Imports

These are listed in the order prescribed by PEP 8.

Standard library

import re
from typing import Dict, Type
 

Third-party imports

from pydantic import validator
from sqlalchemy import (
    Column,
    ForeignKey,
    Index,
    Integer,
    String,
    Date,
    DateTime,
    Text,
    types,
    Float,
    inspect,
)
from sqlalchemy.ext.declarative import declared_attr
from sqlalchemy.sql.schema import UniqueConstraint
 

Local application imports

from .db import Base
from .schemas import BaseModelNone, sqlalchemy_to_pydantic

Web2Py boolean type

Define a web2py-compatible Boolean type. See custom types.

class Web2PyBoolean(types.TypeDecorator):
    impl = types.CHAR(1)
    python_type = bool

From the docs: “The requirements for cacheable elements is that they are hashable and also that they indicate the same SQL rendered for expressions using this type every time for a given cache value.”

    cache_ok = True

    def process_bind_param(self, value, dialect):
        if value:
            return "T"
        elif value is None:
            return None
        elif not value:
            return "F"
        else:
            assert False, f"{value} is not T or F"

    def process_result_value(self, value, dialect):
        if value == "T":
            return True
        elif value == "F":
            return False
        elif value is None:
            return None
        else:
            assert False, f"{value} is not T or F"

    def copy(self, **kw):
        return Web2PyBoolean(self.impl.length)
 
 

Schema Definition

Provide a container to store information about each type of Runestone Component. While a namedtuple would be better, this can’t be used since the fields aren’t modifiable after creation; see the comment on init_graders.

class RunestoneComponentDict:
    def __init__(self, model: Type[Base], validator: Type[BaseModelNone]):
        self.grader = None
        self.model = model
        self.validator = validator
 
 

Store this information in a dict whose key is the component’s name, as a string.

runestone_component_dict: Dict[str, RunestoneComponentDict] = {}
 
 

register_answer_table: Provide a decorator for a class that populates this table.

def register_answer_table(
    sql_alchemy_cls: Type[Base],
) -> Type[Base]:
    global runestone_component_dict

    table_name = sql_alchemy_cls.__tablename__
    runestone_component_dict[table_name] = RunestoneComponentDict(
        sql_alchemy_cls,
        sqlalchemy_to_pydantic(sql_alchemy_cls),
    )
    return sql_alchemy_cls
 
 

IdMixin

Always name a table’s ID field the same way.

class IdMixin:
    id = Column(Integer, primary_key=True)

Useinfo

This defines the useinfo table in the database. This table logs nearly every click generated by a student. It gets very large and needs a lot of indexes to keep Runestone from bogging down.

User info logged by the log_book_event endpoint. See there for more info.

class Useinfo(Base, IdMixin):
    __tablename__ = "useinfo"
    __table_args__ = (Index("sid_divid_idx", "sid", "div_id"),)
 

timestamp: when this entry was recorded by this webapp.

    timestamp = Column(DateTime, index=True, nullable=False)

sid: TODO: The student id? (user) which produced this row.

    sid = Column(String(512), index=True, nullable=False)

The type of question (timed exam, fill in the blank, etc.).

    event = Column(String(512), index=True, nullable=False)

TODO: What is this? The action associated with this log entry?

    act = Column(String(512), nullable=False)

div_id: the ID of the question which produced this entry.

    div_id = Column(String(512), index=True, nullable=False)

course_id: the Courses course_name NOT the id this row refers to. TODO: Use the id instead!

    course_id = Column(
        String(512), ForeignKey("courses.course_name"), index=True, nullable=False
    )

These are not currently in web2py but I’m going to add them

    ##chapter = Column(String, unique=False, index=False)
    ##sub_chapter = Column(String, unique=False, index=False)

UseinfoValidation = sqlalchemy_to_pydantic(Useinfo)

Answers to specific question types

class AnswerMixin(IdMixin):

See timestamp.

    timestamp = Column(DateTime, nullable=False)

See div_id.

    div_id = Column(String(512), index=True, nullable=False)

See sid.

    sid = Column(String(512), index=True, nullable=False)
 

See course_name. Mixins with foreign keys need special treatment.

    @declared_attr
    def course_name(cls):
        return Column(
            String(512), ForeignKey("courses.course_name"), index=True, nullable=False
        )

    def to_dict(self):
        return {c.key: getattr(self, c.key) for c in inspect(self).mapper.column_attrs}


@register_answer_table
class TimedExam(Base, AnswerMixin):
    __tablename__ = "timed_exam"

See the timed exam endpoint parameters for documentation on these columns..

    correct = Column(Integer, nullable=False)
    incorrect = Column(Integer, nullable=False)
    skipped = Column(Integer, nullable=False)
    time_taken = Column(Integer, nullable=False)

True if the act endpoint parameter was 'reset'; otherwise, False.

    reset = Column(Web2PyBoolean)

TimedExamValidator = sqlalchemy_to_pydantic(TimedExam)

Like an AnswerMixin, but also has a boolean correct field.

class CorrectAnswerMixin(AnswerMixin):

correct: True if this answer is correct.

    correct = Column(Web2PyBoolean, nullable=False)

Some question types (drag-n-drop, for example) don’t provide this. A more accurate approach would be to specify this for each answer class, but I’m avoiding that extra work for now.

    percent = Column(Float)
 
 

An answer to a multiple-choice question.

@register_answer_table
class MchoiceAnswers(Base, CorrectAnswerMixin):
    __tablename__ = "mchoice_answers"

answer: The answer to this question. TODO: what is the format?

    answer = Column(String(50), nullable=False)
    __table_args__ = (
        Index(
            "mult_scd_idx",
            "div_id",
            "course_name",
            "sid",
        ),
    )
 
 

An answer to a fill-in-the-blank question.

@register_answer_table
class FitbAnswers(Base, CorrectAnswerMixin):
    __tablename__ = "fitb_answers"