1from types import SimpleNamespace
2import datetime as dt
3
4import pytest
5import sqlalchemy as sa
6from sqlalchemy.ext.associationproxy import association_proxy
7from sqlalchemy.ext.declarative import declarative_base
8from sqlalchemy.orm import sessionmaker, relationship, backref, column_property, synonym
9
10
11class AnotherInteger(sa.Integer):
12    """Use me to test if MRO works like we want"""
13
14    pass
15
16
17class AnotherText(sa.types.TypeDecorator):
18    """Use me to test if MRO and `impl` virtual type works like we want"""
19
20    impl = sa.UnicodeText
21
22
23@pytest.fixture()
24def Base():
25    return declarative_base()
26
27
28@pytest.fixture()
29def engine():
30    return sa.create_engine("sqlite:///:memory:", echo=False)
31
32
33@pytest.fixture()
34def session(Base, models, engine):
35    Session = sessionmaker(bind=engine)
36    Base.metadata.create_all(bind=engine)
37    return Session()
38
39
40@pytest.fixture()
41def models(Base):
42
43    # models adapted from https://github.com/wtforms/wtforms-sqlalchemy/blob/master/tests/tests.py
44    student_course = sa.Table(
45        "student_course",
46        Base.metadata,
47        sa.Column("student_id", sa.Integer, sa.ForeignKey("student.id")),
48        sa.Column("course_id", sa.Integer, sa.ForeignKey("course.id")),
49    )
50
51    class Course(Base):
52        __tablename__ = "course"
53        id = sa.Column(sa.Integer, primary_key=True)
54        name = sa.Column(sa.String(255), nullable=False)
55        # These are for better model form testing
56        cost = sa.Column(sa.Numeric(5, 2), nullable=False)
57        description = sa.Column(sa.Text, nullable=True)
58        level = sa.Column(sa.Enum("Primary", "Secondary"))
59        has_prereqs = sa.Column(sa.Boolean, nullable=False)
60        started = sa.Column(sa.DateTime, nullable=False)
61        grade = sa.Column(AnotherInteger, nullable=False)
62        transcription = sa.Column(AnotherText, nullable=False)
63
64        @property
65        def url(self):
66            return f"/courses/{self.id}"
67
68    class School(Base):
69        __tablename__ = "school"
70        id = sa.Column("school_id", sa.Integer, primary_key=True)
71        name = sa.Column(sa.String(255), nullable=False)
72
73        student_ids = association_proxy(
74            "students", "id", creator=lambda sid: Student(id=sid)
75        )
76
77        @property
78        def url(self):
79            return f"/schools/{self.id}"
80
81    class Student(Base):
82        __tablename__ = "student"
83        id = sa.Column(sa.Integer, primary_key=True)
84        full_name = sa.Column(sa.String(255), nullable=False, unique=True)
85        dob = sa.Column(sa.Date(), nullable=True)
86        date_created = sa.Column(
87            sa.DateTime, default=dt.datetime.utcnow, doc="date the student was created"
88        )
89
90        current_school_id = sa.Column(
91            sa.Integer, sa.ForeignKey(School.id), nullable=False
92        )
93        current_school = relationship(School, backref=backref("students"))
94        possible_teachers = association_proxy("current_school", "teachers")
95
96        courses = relationship(
97            Course,
98            secondary=student_course,
99            backref=backref("students", lazy="dynamic"),
100        )
101
102        @property
103        def url(self):
104            return f"/students/{self.id}"
105
106    class Teacher(Base):
107        __tablename__ = "teacher"
108        id = sa.Column(sa.Integer, primary_key=True)
109
110        full_name = sa.Column(
111            sa.String(255), nullable=False, unique=True, default="Mr. Noname"
112        )
113
114        current_school_id = sa.Column(
115            sa.Integer, sa.ForeignKey(School.id), nullable=True
116        )
117        current_school = relationship(School, backref=backref("teachers"))
118        curr_school_id = synonym("current_school_id")
119
120        substitute = relationship("SubstituteTeacher", uselist=False, backref="teacher")
121
122        @property
123        def fname(self):
124            return self.full_name
125
126    class SubstituteTeacher(Base):
127        __tablename__ = "substituteteacher"
128        id = sa.Column(sa.Integer, sa.ForeignKey("teacher.id"), primary_key=True)
129
130    class Paper(Base):
131        __tablename__ = "paper"
132
133        satype = sa.Column(sa.String(50))
134        __mapper_args__ = {"polymorphic_identity": "paper", "polymorphic_on": satype}
135
136        id = sa.Column(sa.Integer, primary_key=True)
137        name = sa.Column(sa.String, nullable=False, unique=True)
138
139    class GradedPaper(Paper):
140        __tablename__ = "gradedpaper"
141
142        __mapper_args__ = {"polymorphic_identity": "gradedpaper"}
143
144        id = sa.Column(sa.Integer, sa.ForeignKey("paper.id"), primary_key=True)
145
146        marks_available = sa.Column(sa.Integer)
147
148    class Seminar(Base):
149        __tablename__ = "seminar"
150
151        title = sa.Column(sa.String, primary_key=True)
152        semester = sa.Column(sa.String, primary_key=True)
153
154        label = column_property(title + ": " + semester)
155
156    lecturekeywords_table = sa.Table(
157        "lecturekeywords",
158        Base.metadata,
159        sa.Column("keyword_id", sa.Integer, sa.ForeignKey("keyword.id")),
160        sa.Column("lecture_id", sa.Integer, sa.ForeignKey("lecture.id")),
161    )
162
163    class Keyword(Base):
164        __tablename__ = "keyword"
165
166        id = sa.Column(sa.Integer, primary_key=True)
167        keyword = sa.Column(sa.String)
168
169    class Lecture(Base):
170        __tablename__ = "lecture"
171        __table_args__ = (
172            sa.ForeignKeyConstraint(
173                ["seminar_title", "seminar_semester"],
174                ["seminar.title", "seminar.semester"],
175            ),
176        )
177
178        id = sa.Column(sa.Integer, primary_key=True)
179        topic = sa.Column(sa.String)
180        seminar_title = sa.Column(sa.String, sa.ForeignKey(Seminar.title))
181        seminar_semester = sa.Column(sa.String, sa.ForeignKey(Seminar.semester))
182        seminar = relationship(
183            Seminar, foreign_keys=[seminar_title, seminar_semester], backref="lectures"
184        )
185        kw = relationship("Keyword", secondary=lecturekeywords_table)
186        keywords = association_proxy(
187            "kw", "keyword", creator=lambda kw: Keyword(keyword=kw)
188        )
189
190    return SimpleNamespace(
191        Course=Course,
192        School=School,
193        Student=Student,
194        Teacher=Teacher,
195        SubstituteTeacher=SubstituteTeacher,
196        Paper=Paper,
197        GradedPaper=GradedPaper,
198        Seminar=Seminar,
199        Lecture=Lecture,
200        Keyword=Keyword,
201    )
202