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