class Eqinfo(db.Model): __tablename__ = 'eqinfo' __table_args__ = (db.ForeignKeyConstraint(['eqid', 'eqname'], ['eqname.eqid', 'eqname.eqname'], ondelete='CASCADE', onupdate='CASCADE'), db.ForeignKeyConstraint(['ksid'], ['keshi.id'], ondelete='CASCADE', onupdate='CASCADE'), db.Index('e', 'eqid', 'eqname')) id = db.Column(db.Integer, primary_key=True) ksid = db.Column(db.Integer) eqid = db.Column(db.String(10), nullable=True) eqname = db.Column(db.String(10), nullable=True) eqprdata = db.Column(db.Date, nullable=True) equsedata = db.Column(db.Date, nullable=True) eqce1filename = db.Column(db.String(255), nullable=True) eqce1file_url = db.Column(db.String(255), nullable=True) eqce2filename = db.Column(db.String(255), nullable=True) eqce2file_url = db.Column(db.String(255), nullable=True) eqce3filename = db.Column(db.String(255), nullable=True) eqce3file_url = db.Column(db.String(255), nullable=True) eqce4filename = db.Column(db.String(255), nullable=True) eqce4file_url = db.Column(db.String(255), nullable=True) eqce5filename = db.Column(db.String(255), nullable=True) eqce5file_url = db.Column(db.String(255), nullable=True) eqname1 = db.relationship( 'Eqname', primaryjoin= 'and_(Eqinfo.eqid == Eqname.eqid, Eqinfo.eqname == Eqname.eqname)', backref='eqinfos') def __init__(self, ksid, eqid, eqname, eqprdata, equsedata, eqce1filename, eqce1file_url, eqce2filename, eqce2file_url, eqce3filename, eqce3file_url, eqce4filename, eqce4file_url, eqce5filename, eqce5file_url): self.ksid = ksid self.eqid = eqid self.eqname = eqname self.eqprdata = eqprdata self.equsedata = equsedata self.eqce1filename = eqce1filename self.eqce1file_url = eqce1file_url self.eqce2filename = eqce2filename self.eqce2file_url = eqce2file_url self.eqce3filename = eqce3filename self.eqce3file_url = eqce3file_url self.eqce4filename = eqce4filename self.eqce4file_url = eqce4file_url self.eqce5filename = eqce5filename self.eqce5file_url = eqce5file_url def save(self): db.session.add(self) db.session.commit()
class AllergenAlternative(db.Model): __tablename__ = 'allergen_alternative' __table_args__ = ( db.ForeignKeyConstraint( ['step_id', 'ingredient_id'], ['step_ingredient.step_id', 'step_ingredient.ingredient_id'] ), ) # id = db.Column(db.Integer, primary_key=True) step_id = db.Column(db.Integer, primary_key=True) ingredient_id = db.Column(db.Integer, primary_key=True) # step_id = db.Column(db.Integer, db.ForeignKey('step_ingredient.step_id'), primary_key=True) # ingredient_id = db.Column(db.Integer, db.ForeignKey('step_ingredient.ingredient_id'), primary_key=True) alt_ingredient_id = db.Column(db.Integer, db.ForeignKey('ingredient.id'), primary_key=True) notes = db.Column(db.String(50)) # ingredient_id = db.Column(db.Integer, db.ForeignKey("address.id")) # alt_ingredient_id = db.Column(db.Integer, db.ForeignKey("address.id")) # orig_ingredient = db.relationship("StepIngredient", foreign_keys=[step_id, ingredient_id]) orig_ingredient = db.relationship('StepIngredient', primaryjoin="and_(AllergenAlternative.step_id==StepIngredient.step_id, AllergenAlternative.ingredient_id==StepIngredient.ingredient_id)") alt_ingredient = db.relationship("Ingredient", foreign_keys=[alt_ingredient_id]) # step_ingredient = db.relationship('StepIngredient', backref='alternatives', foreign_keys=[step_id, ingredient_id]) def __repr__(self): return 'Step #{0} substitute {1} for {2} with the following notes: "{3}"' \ .format(self.step_id, self.alt_ingredient_id, self.ingredient_id, self.notes)
class Boarding_passes(db.Model): __tablename__ = 'boarding_passes' __table_args__ = ( db.UniqueConstraint('flight_id', 'boarding_no', name='boarding_passes_flight_id_boarding_no_key'), db.UniqueConstraint('flight_id', 'seat_no', name='boarding_passes_flight_id_seat_no_key'), db.PrimaryKeyConstraint('ticket_no', 'flight_id', name='boarding_passes_pkey'), db.ForeignKeyConstraint( ['ticket_no', 'flight_id'], ['ticket_flights.ticket_no', 'ticket_flights.flight_id'], name='boarding_passes_ticket_no_fkey')) ticket_no = db.Column(CHAR(13), nullable=False) flight_id = db.Column(INTEGER, nullable=False) boarding_no = db.Column(INTEGER, nullable=False) seat_no = db.Column(CHAR(4), nullable=False) def __init__(self, ticket_no, flight_id, boarding_no, seat_no): self.ticket_no = ticket_no self.flight_id = flight_id self.boarding_no = boarding_no self.seat_no = seat_no
class Staff(db.Model, UserMixin): __tablename__ = 'staffs' staff_id = db.Column(db.String(64), primary_key=True) first_name = db.Column(db.String(64), nullable=False) last_name = db.Column(db.String(64), nullable=False) school_code = db.Column(db.CHAR(3), nullable=False) department_code = db.Column(db.CHAR(3), nullable=False) role = db.Column(db.String(64), nullable=False) db.ForeignKeyConstraint( [department_code, school_code], [Department.department_code, Department.school_code]) account = db.relationship('StaffAccount', uselist=False, back_populates='owner') announcements = db.relationship('Announcement', backref='sender', lazy=True) events = db.relationship('Activity', backref='creator', lazy=True) def __str__(self): return self.first_name + self.first_name def __repr__(self): return f"<Staff: {self.first_name} {self.last_name}>" def get_id(self): return self.staff_id
class Message(db.Model): __tablename__ = 'messages' id = db.Column(db.Integer, primary_key=True) sender_id = db.Column(db.Integer, nullable=False) recver_id = db.Column(db.Integer, nullable=False) text = db.Column(db.Text) timestamp = db.Column(db.DateTime, default=datetime.utcnow) chat_user1 = db.Column(db.Integer, nullable=False) chat_user2 = db.Column(db.Integer, nullable=False) __table_args__ = (db.ForeignKeyConstraint(['chat_user1', 'chat_user2'],['chats.user1_id', 'chats.user2_id'], ondelete='CASCADE'),) def __repr__(self): return '<Message {}-{}>'.format(self.id, self.timestamp) def serialize(self): return { 'id':self.id, "sender_id": self.sender_id, "recver_id":self.recver_id, "text":self.text, "timestamp":str(self.timestamp) }
class Question(Base): """A class representing a question of a problem """ def __init__(self, question, difficulty, marks, answer, solution, problem_id, test_id): self.question = question self.difficulty = difficulty self.marks = marks self.answer = answer self.solution = solution self.problem_id = problem_id self.test_id = test_id __tablename__ = 'question' question_id = db.Column(db.Integer, primary_key=True) question = db.Column(db.String(511), nullable=False) difficulty = db.Column(db.Enum(Difficulty)) marks = db.Column(db.Integer, nullable=False) answer = db.Column(db.String(255), nullable=False) solution = db.Column(db.String(2047), nullable=False) problem_id = db.Column(db.Integer, primary_key=True) test_id = db.Column(db.Integer, primary_key=True) problem = relationship('Problem') __table_args__ = (db.ForeignKeyConstraint( [problem_id, test_id], [Problem.problem_id, Problem.test_id]), {})
class TestHistory(Base): """A class representing the test history of a student """ def __init__(self, student_id, test_id, problem_id, question_id, answer, is_correct, is_answered): self.student_id = student_id self.question_id = question_id self.problem_id = problem_id self.test_id = test_id self.answer = answer self.is_correct = is_correct self.is_answered = is_answered __tablename__ = 'test_history' test_history_id = db.Column(db.Integer, primary_key=True) student_id = db.Column(db.ForeignKey('auth_user.auth_user_id'), nullable=False, index=True) test_id = db.Column(db.Integer, nullable=False, index=True) problem_id = db.Column(db.Integer, nullable=False, index=True) question_id = db.Column(db.Integer, nullable=False, index=True) answer = db.Column(db.String(255), nullable=True) is_correct = db.Column(db.Boolean, nullable=True) is_answered = db.Column(db.Boolean, nullable=False, default=0) question = relationship('Question') auth_user = relationship('AuthUser') __table_args__ = (db.ForeignKeyConstraint( [problem_id, test_id, question_id], [Question.problem_id, Question.test_id, Question.question_id]), {})
class Events(db.Model): """ Define the Event class with the following columns and relationships: Events are any type of action that happened to a request after it was submitted id - an integer that is the primary key of an Events request_id - a foreign key that links to a request's primary key user_id - a foreign key that links to the user_id of the person who performed the event response_id - a foreign key that links to the primary key of a response type - a string containing the type of event that occurred timestamp - a datetime that keeps track of what time an event was performed previous_value - a string containing the old value of the event new_value - a string containing the new value of the event """ __tablename__ = 'events' id = db.Column(db.Integer, primary_key=True) request_id = db.Column(db.String(19), db.ForeignKey('requests.id')) user_guid = db.Column(db.String(64)) # who did the action auth_user_type = db.Column( db.Enum(user_type_auth.AGENCY_USER, user_type_auth.AGENCY_LDAP_USER, user_type_auth.PUBLIC_USER_FACEBOOK, user_type_auth.PUBLIC_USER_MICROSOFT, user_type_auth.PUBLIC_USER_YAHOO, user_type_auth.PUBLIC_USER_LINKEDIN, user_type_auth.PUBLIC_USER_GOOGLE, user_type_auth.PUBLIC_USER_NYC_ID, user_type_auth.ANONYMOUS_USER, name='auth_user_type')) response_id = db.Column(db.Integer, db.ForeignKey('responses.id')) type = db.Column(db.String(64)) timestamp = db.Column(db.DateTime, default=datetime.utcnow()) previous_value = db.Column(JSON) new_value = db.Column(JSON) __table_args__ = (db.ForeignKeyConstraint( [user_guid, auth_user_type], [Users.guid, Users.auth_user_type]), ) def __init__(self, request_id, user_guid, auth_user_type, type_, previous_value=None, new_value=None, response_id=None, timestamp=None): self.request_id = request_id self.user_guid = user_guid self.auth_user_type = auth_user_type self.response_id = response_id self.type = type_ self.previous_value = previous_value self.new_value = new_value self.timestamp = timestamp def __repr__(self): return '<Events %r>' % self.id
class RoundContestResult(db.Model): round_id = db.Column(db.String(200), db.ForeignKey('round.id', ondelete='cascade'), nullable=False) contest_id = db.Column(db.String(200), db.ForeignKey('targeted_contest.id', ondelete='cascade'), nullable=False) __table_args__ = ( db.PrimaryKeyConstraint('round_id', 'targeted_contest_choice_id'), db.ForeignKeyConstraint(['round_id', 'contest_id'], ['round_contest.round_id', 'round_contest.contest_id'], ondelete='cascade') ) targeted_contest_choice_id = db.Column(db.String(200), db.ForeignKey('targeted_contest_choice.id', ondelete='cascade'), nullable=False) result = db.Column(db.Integer)
class Recipe_Step_Ingredient(db.Model): __tablename__ = 'recipe_step_ingredient' recipe_name = db.Column(db.String(128), primary_key=True) step = db.Column(db.String(2048), primary_key=True) ingredient_name = db.Column(db.String(128), db.ForeignKey("ingredient.name"), primary_key=True) __table_args__ = (db.ForeignKeyConstraint( ['recipe_name', 'step'], ['recipe_step.recipe_name', 'recipe_step.step'], ), )
class Maininfo(db.Model): __tablename__ = 'maininfo' __table_args__ = (db.ForeignKeyConstraint(['eqid', 'eqname'], ['eqname.eqid', 'eqname.eqname'], ondelete='CASCADE', onupdate='CASCADE'), db.ForeignKeyConstraint(['ksid'], ['keshi.id'], ondelete='CASCADE', onupdate='CASCADE'), db.Index('e', 'eqid', 'eqname')) id = db.Column(db.Integer, primary_key=True) ksid = db.Column(db.Integer) eqid = db.Column(db.String(10), nullable=True) eqname = db.Column(db.String(10), nullable=True) maid = db.Column(db.String(255), nullable=True) lxfs = db.Column(db.String(255), nullable=True) wxdata = db.Column(db.Date, nullable=True) ren = db.Column(db.String(255), nullable=True) pj = db.Column(db.String(255), nullable=True) eqname1 = db.relationship( 'Eqname', primaryjoin= 'and_(Maininfo.eqid == Eqname.eqid, Maininfo.eqname == Eqname.eqname)', backref='maininfos') def __init__(self, ksid, eqid, eqname, maid, lxfs, wxdata, ren, pj): self.ksid = ksid self.eqid = eqid self.eqname = eqname self.maid = maid self.lxfs = lxfs self.wxdata = wxdata self.ren = ren self.pj = pj def save(self): db.session.add(self) db.session.commit()
class Relatives(db.Model): import_id = db.Column(db.Integer, primary_key=True) citizen_id = db.Column(db.Integer, primary_key=True) relative = db.Column(db.Integer, primary_key=True) __table_args__ = ( db.ForeignKeyConstraint([import_id, citizen_id], [Citizens.import_id, Citizens.citizen_id]), {} ) def __init__(self, import_id, citizen_id, relative): self.import_id = import_id self.citizen_id = citizen_id self.relative = relative
class Review(db.Model): __tablename__ = "review" reviewer = db.Column(db.String(80), primary_key=True) pname = db.Column(db.String(80), primary_key=True) cname = db.Column(db.String(80), primary_key=True) score1 = db.Column(db.Float, nullable=False) score2 = db.Column(db.Float, nullable=False) score3 = db.Column(db.Float, nullable=False) year = db.Column(db.Integer, nullable=True) school = db.Column(db.String(10), nullable=True) comment = db.Column(db.String(300), nullable=True) advice = db.Column(db.String(300), nullable=True) __table_args__ = (db.ForeignKeyConstraint( ['pname', 'cname'], ['prof_course.pname', 'prof_course.cname']), ) prof_course = db.relationship('Prof_Course', back_populates='review') def __init__(self, reviewer, pname, cname, score1, score2, score3, year=None, school=None, comment=None, advice=None): self.reviewer = reviewer self.pname = pname self.cname = cname self.score1 = score1 self.score2 = score2 self.score3 = score3 self.school = school self.year = year self.comment = comment self.advice = advice def serialize(self): return { 'professor': self.pname, 'course': self.cname, 'score1': self.score1, 'score2': self.score2, 'score3': self.score3, 'year': self.year, 'school': self.school, 'comment': self.comment, 'advice': self.advice }
class Ininfo(db.Model): __tablename__ = 'ininfo' __table_args__ = (db.ForeignKeyConstraint(['eqid', 'eqname'], ['eqname.eqid', 'eqname.eqname'], ondelete='CASCADE', onupdate='CASCADE'), db.ForeignKeyConstraint(['ksid'], ['keshi.id'], ondelete='CASCADE', onupdate='CASCADE'), db.Index('e', 'eqid', 'eqname')) id = db.Column(db.Integer, primary_key=True) ksid = db.Column(db.Integer) eqid = db.Column(db.String(10), nullable=True) eqname = db.Column(db.String(10), nullable=True) prid = db.Column(db.String(255), nullable=True) smjfilename = db.Column(db.String(255), nullable=True) smjfile_url = db.Column(db.String(255), nullable=True) eqname1 = db.relationship( 'Eqname', primaryjoin= 'and_(Ininfo.eqid == Eqname.eqid, Ininfo.eqname == Eqname.eqname)', backref='ininfos') def __init__(self, ksid, eqid, eqname, prid, smjfilename, smjfile_url): self.ksid = ksid self.eqid = eqid self.eqname = eqname self.prid = prid self.smjfilename = smjfilename self.smjfile_url = smjfile_url def save(self): db.session.add(self) db.session.commit()
class AppInstance(db.Model): __tablename__ = 'app_instances' course_code = db.Column(db.CHAR(3), primary_key=True) number = db.Column(db.Integer, primary_key=True) admission_year = db.Column(db.Integer, primary_key=True) db.ForeignKeyConstraint([course_code, number, admission_year], [StudentAccount.course_code, StudentAccount.number, StudentAccount.admission_year]) token = db.Column(db.String(240), nullable=False) account = db.relationship('StudentAccount', back_populates='app', uselist=False) def updateToken(self, token): self.token = token db.session.commit()
class Seats(db.Model): __tablename__ = 'seats' __table_args__ = (db.PrimaryKeyConstraint('aircraft_code', 'seat_no', name='boarding_passes_pkey'), db.ForeignKeyConstraint( ['aircraft_code'], ['aircrafts_data.aircraft_code'], name='boarding_passes_ticket_no_fkey')) aircraft_code = db.Column(CHAR(3), nullable=False) seat_no = db.Column(CHAR(4), nullable=False) fare_conditions = db.Column(CHAR(10), nullable=False) def __init__(self, aircraft_code, seat_no, fare_conditions): self.aircraft_code = aircraft_code self.seat_no = seat_no self.fare_conditions = fare_conditions
class Test(db.Model): test_name = db.Column(db.String(500), primary_key=True) variant = db.Column(db.Integer, primary_key=True) laboratory_theme = db.Column(db.String(500), primary_key=True) input_data = db.Column(db.Text, nullable=False) expected_result = db.Column(db.Text, nullable=False) output_data = db.Column(db.Text) __table_args__ = (db.ForeignKeyConstraint(('variant', 'laboratory_theme'), ('task.variant', 'task.laboratory_theme')), {}) def __init__(self, test_name, variant, laboratory_theme, input_data, expected_result, output_data): self.test_name = test_name self.variant = variant self.laboratory_theme = laboratory_theme self.input_data = input_data self.expected_result = expected_result self.output_data = output_data
class StudentAccount(db.Model): __tablename__ = 'student_accounts' course_code = db.Column(db.CHAR(3), primary_key=True) number = db.Column(db.Integer, primary_key=True) admission_year = db.Column(db.Integer, primary_key=True) db.ForeignKeyConstraint([course_code, number, admission_year], [Student.course_code, Student.number, Student.admission_year]) owner = db.relationship('Student', back_populates='account', uselist=False) app = db.relationship('AppInstance', back_populates='account', uselist=False) password = db.Column(db.String(120), nullable=False) def set_password(self, password): self.password = generate_password_hash(password) def check_password(self, password): return check_password_hash(self.password, password)
class DistrictToSchoolMapping(db.Model): __tablename__ = 'schooltodistrictmapping' id = db.Column(db.Integer, primary_key=True) school_name = db.Column(db.Text, index=False, unique=False, nullable=False) school_server_code = db.Column(db.String(64), nullable=False) school_udisce_code = db.Column(db.String(64), nullable=False) server_id = db.Column(db.Text, index=False, unique=True, nullable=False) dateUpdated = db.Column(db.DateTime, unique=False, nullable=True) distirct_code = db.Column( db.String(64), #db.ForeignKey('districtdetails.distirct_code'), nullable=False) state_code = db.Column( db.String(64), #db.ForeignKey('districtdetails.state_code'), nullable=False) __table_args__ = (db.ForeignKeyConstraint( ['distirct_code', 'state_code'], ['districtdetails.distirct_code', 'districtdetails.state_code'], name='schooltodistrictmapping_distirct_code_fkey1'), )
class Course(db.Model): __tablename__ = 'courses' course_code = db.Column(db.CHAR(3), primary_key=True) name = db.Column(db.String(256), nullable=False) school_code = db.Column(db.CHAR(3), nullable=False) department_code = db.Column(db.CHAR(3), nullable=False) db.ForeignKeyConstraint( [school_code, department_code], [Department.school_code, Department.department_code]) students = db.relationship('Student', backref='course', lazy=False) permitted_senders = db.relationship('PermittedCourse', backref='course', lazy=False) def __str__(self): return self.name def __repr__(self): return f"<Course: {self.name}>"
class Eqname(db.Model): __tablename__ = 'eqname' __table_args__ = ( db.ForeignKeyConstraint(['ksid'], ['keshi.id'], ondelete='CASCADE', onupdate='CASCADE'), db.Index('eqid', 'eqid', 'eqname'), ) id = db.Column(db.Integer, primary_key=True) ksid = db.Column(db.Integer) eqid = db.Column(db.String(10), nullable=True) eqname = db.Column(db.String(10), nullable=True) eqmt = db.Column(db.String(255), nullable=True) def __init__(self, ksid, eqid, eqname): self.ksid = ksid self.eqid = eqid self.eqname = eqname def save(self): db.session.add(self) db.session.commit()
class Outcome(db.Model): __tablename__ = "outcomes" RESULT_CHOICES = ( "awarded", "cancelled", "none-suitable", ) id = db.Column(db.Integer, primary_key=True) external_id = db.Column(db.BigInteger, nullable=False, default=random_positive_external_id, unique=True) completed_at = db.Column(db.DateTime, nullable=True) start_date = db.Column(db.Date, nullable=True) end_date = db.Column(db.Date, nullable=True) awarding_organisation_name = db.Column(db.String, nullable=True) # should be enough scale to represent up to £1b - 1p award_value = db.Column(db.Numeric(11, 2), nullable=True) result = db.Column(db.String, nullable=False) direct_award_project_id = db.Column( db.Integer, db.ForeignKey('direct_award_projects.id'), nullable=True, ) direct_award_search_id = db.Column( db.Integer, db.ForeignKey('direct_award_searches.id'), nullable=True, ) direct_award_archived_service_id = db.Column( db.Integer, db.ForeignKey('archived_services.id'), nullable=True, ) # NOTE though Outcome currently has the *ability* to be associated with a Brief/BriefResponse, at time of # writing this is not yet *used* for reporting brief awards - those are still using their own mechanism, but should # be ported over to this mechanism as soon as time allows. brief_id = db.Column( db.Integer, db.ForeignKey('briefs.id'), nullable=True, ) brief_response_id = db.Column( db.Integer, db.ForeignKey('brief_responses.id'), nullable=True, ) # The following constraints enforce this general scheme: # All Outcomes must point at either a DirectAwardProject OR a Brief. If the Outcome's "result" is # "awarded", then a DirectAwardProject-based Outcome must also point at the DirectAwardProject's active # search and "winning" archived_service_id or a Brief-based Outcome must also point at the winning # BriefResponse. Only one "completed" Outcome can point at its target (DirectAwardProject or Brief) at once. # # Overlapping compound foreign keys are used to ensure the correctness of the # DirectAwardProject-DirectAwardSearch-ArchivedService and Brief-BriefResponse chains. # # These particular restrictions were chosen to be enforced at a database level because they're all part of an # interrelated system of rules related to cross-table relationships. Contraints which only deal with intra-row # rules can be handled safely (enough) with app-side validation. __table_args__ = ( db.CheckConstraint(sql_case( ( # if awarded, the "null-ness" of direct_award_project_id, direct_award_search_id & # direct_award_archived_service_id must be the same (result == "awarded", sql_and( (direct_award_project_id.is_(None) == direct_award_search_id.is_(None)), (direct_award_project_id.is_(None) == direct_award_archived_service_id.is_(None)), )), ), # if not awarded, we shouldn't have direct_award_search_id or direct_award_archived_service_id set else_=sql_and(direct_award_search_id.is_(None), direct_award_archived_service_id.is_(None)), ), name="ck_outcomes_direct_award_keys_nullable"), db.CheckConstraint(sql_case( ( # if awarded, the "null-ness" of brief_response_id and brief_id must be the same (result == "awarded", brief_response_id.is_(None) == brief_id.is_(None),), ), # if not awarded, brief_response_id should not be set else_=brief_response_id.is_(None), ), name="ck_outcomes_brief_keys_nullable"), # the "null-ness" of direct_award_project_id and brief_id must *never* be the same db.CheckConstraint( (direct_award_project_id.is_(None) != brief_id.is_(None)), name="ck_outcomes_either_brief_xor_direct_award", ), # only one completed award can point at a DirectAwardProject at once db.Index( "idx_outcomes_completed_direct_award_project_unique", direct_award_project_id, postgresql_where=(completed_at.isnot(None)), unique=True, ), # only one completed award can point at a Brief at once db.Index( "idx_outcomes_completed_brief_unique", brief_id, postgresql_where=(completed_at.isnot(None)), unique=True, ), # direct_award_archived_service_id must actually be a result of the given DirectAwardSearch db.ForeignKeyConstraint( (direct_award_archived_service_id, direct_award_search_id,), ("direct_award_search_result_entries.archived_service_id", "direct_award_search_result_entries.search_id",), name="fk_outcomes_da_service_id_da_search_id", deferrable=True, initially="DEFERRED", ), # direct_award_search_id must actually belong the given DirectAwardProject db.ForeignKeyConstraint( (direct_award_search_id, direct_award_project_id,), ("direct_award_searches.id", "direct_award_searches.project_id",), name="fk_outcomes_da_search_id_da_project_id", deferrable=True, initially="DEFERRED", ), # brief_response_id must actually belong to the given Brief db.ForeignKeyConstraint( (brief_response_id, brief_id,), ("brief_responses.id", "brief_responses.brief_id",), name="fk_outcomes_brief_response_id_brief_id", deferrable=True, initially="DEFERRED", ), ) direct_award_project = db.relationship( "DirectAwardProject", foreign_keys=direct_award_project_id, # named as such to be explicit that this includes incomplete "outcomes" backref="outcomes_all", ) direct_award_search = db.relationship( "DirectAwardSearch", foreign_keys=direct_award_search_id, # named as such to be explicit that this includes incomplete "outcomes" backref="outcomes_all", ) direct_award_search_result_entry = db.relationship( "DirectAwardSearchResultEntry", foreign_keys=(direct_award_search_id, direct_award_archived_service_id,), uselist=False, # being able to write to this relationship could cause ambiguities with the other relationships that also # use some of their keys viewonly=True, # named as such to be explicit that this includes incomplete "outcomes" backref=backref("outcomes_all", viewonly=True), ) direct_award_archived_service = db.relationship( "ArchivedService", foreign_keys=direct_award_archived_service_id, # named as such to be explicit that this includes incomplete "outcomes" backref="outcomes_all", ) brief_response = db.relationship( "BriefResponse", foreign_keys=brief_response_id, # named as such to be explicit that this includes incomplete "outcomes" backref="outcomes_all", ) brief = db.relationship( "Brief", foreign_keys=brief_id, # named as such to be explicit that this includes incomplete "outcomes" backref="outcomes_all", ) def update_from_json(self, update_data): award_dict = update_data.get("award", {}) if not isinstance(award_dict, dict): raise ValidationError(f"'award' expected to be a dictionary") for k, v in award_dict.items(): key_mapping = { "startDate": "start_date", "endDate": "end_date", "awardingOrganisationName": "awarding_organisation_name", "awardValue": "award_value", } if k in key_mapping: if k == "awardValue": try: v = decimal.Decimal(v) if v is not None else None except decimal.InvalidOperation: raise ValidationError(f"Failed to parse {v!r} as decimal for field {k!r}") elif k in ("startDate", "endDate",): if v is not None: try: v = datetime.datetime.strptime(v, DATE_FORMAT).date() except ValueError as e: raise ValidationError(f"Failed to parse {v!r} as date for field {k!r}: {e.args[0]}") setattr(self, key_mapping[k], v) def serialize(self): return { "id": self.external_id, "completed": self.completed_at is not None, "completedAt": self.completed_at.strftime(DATETIME_FORMAT) if self.completed_at is not None else None, "result": self.result, **( { "resultOfDirectAward": { "project": { "id": self.direct_award_project.external_id, }, **( { # heavily abbreviated versions of these objects' serializations "search": { "id": self.direct_award_search_id, }, "archivedService": { "id": self.direct_award_archived_service_id, "service": { "id": self.direct_award_archived_service.service_id, }, }, } if self.result == "awarded" else {} ), }, } if self.direct_award_project_id is not None else {} ), **( { "resultOfFurtherCompetition": { "brief": { "id": self.brief_id, }, **( { "briefResponse": { "id": self.brief_response_id, }, } if self.result == "awarded" else {} ) }, } if self.brief_id is not None else {} ), **( { "award": { "startDate": self.start_date and self.start_date.strftime(DATE_FORMAT), "endDate": self.end_date and self.end_date.strftime(DATE_FORMAT), "awardingOrganisationName": self.awarding_organisation_name, # don't risk json representing this as a float "awardValue": str(self.award_value) if self.award_value is not None else None, }, } if self.result == "awarded" else {} ), } @validates( "start_date", "end_date", "awarding_organisation_name", "award_value", ) def _validates_data_complete_if_completed_at(self, key, value): if key == "award_value": if value is not None and value < 0: raise ValidationError(f"{key} must be greater than or equal to zero, got {value!r}") if self.completed_at and self.result == "awarded" and value in (None, "",): raise ValidationError( f"{key} cannot be None or empty if Outcome with result={self.result!r} is 'completed'." f" Received {value!r}" ) if self.result not in (None, "awarded",) and value is not None: raise ValidationError( f"{key} cannot be set for Outcomes with result={self.result!r}. Attempted to set value {value!r}" ) return value @validates("completed_at", "result") def _validates_completed_at_data_complete_if_set(self, key, value): if key == "result" and value not in self.RESULT_CHOICES: raise ValidationError(f"{value!r} is not a valid choice for field {key!r}") result = value if key == "result" else self.result completed_at = value if key == "completed_at" else self.completed_at if result == "awarded" and completed_at is not None: failures = ", ".join( "{}={}".format(fkey, repr(getattr(self, fkey))) for fkey in ( "start_date", "end_date", "awarding_organisation_name", "award_value", ) if (getattr(self, fkey) in (None, "",)) ) if failures: raise ValidationError( f"Outcome with result={result!r} cannot be 'completed' with None or empty data fields," f" but {failures}" ) if result not in (None, "awarded",): failures = ", ".join( "{}={}".format(fkey, repr(getattr(self, fkey))) for fkey in ( "start_date", "end_date", "awarding_organisation_name", "award_value", ) if getattr(self, fkey) is not None ) if failures: raise ValidationError( f"Outcomes with result={result!r} cannot have award-related data fields set, but {failures}" ) return value
class Batting(db.Model): __tablename__ = 'batting' id = db.Column(db.Integer, primary_key=True) playerID = db.Column(db.String(9), db.ForeignKey("people.playerID")) yearID = db.Column(db.SmallInteger) teamID = db.Column(db.String(3)) person = db.relationship("People") appearance = db.relationship('Appearances', foreign_keys=[playerID, teamID, yearID]) __table_args__ = (db.ForeignKeyConstraint( [playerID, teamID, yearID], [Appearances.playerID, Appearances.teamID, Appearances.yearID]), ) H = db.Column(db.SmallInteger) HR = db.Column(db.SmallInteger) AB = db.Column(db.SmallInteger) BB = db.Column(db.SmallInteger) HBP = db.Column(db.SmallInteger) SF = db.Column(db.SmallInteger) SH = db.Column(db.SmallInteger) B2 = db.Column('2B', db.SmallInteger) B3 = db.Column('3B', db.SmallInteger) @hybrid_property def plate_appearance(self): return self.AB + self.BB + (self.HBP or 0) + (self.SF or 0) + (self.SH or 0) @plate_appearance.expression def plate_appearance(cls): return cls.AB + cls.BB + func.coalesce(cls.HBP, 0) + func.coalesce( cls.SF, 0) + func.coalesce(cls.SH, 0) @hybrid_property def batting_avg(self): return self.H / self.AB if self.AB > 0 else 0 @batting_avg.expression def batting_avg(cls): return case([(cls.AB, cls.H / cls.AB)], else_=0) @hybrid_property def onbase_percent(self): return (self.H + self.BB + (self.HBP or 0)) / (self.AB + self.BB + (self.HBP or 0) + (self.SF or 0)) if ( self.AB + self.BB + (self.HBP or 0) + (self.SF or 0)) > 0 else 0 @onbase_percent.expression def onbase_percent(cls): return case([(cls.H + cls.BB + func.coalesce(cls.HBP, 0)) / (cls.AB + cls.BB + func.coalesce(cls.HBP, 0) + func.coalesce(cls.SF, 0))], else_=0) @hybrid_property def slug_percent(self): return (self.H + self.B2 + 2 * self.B3 + 3 * self.HR) / self.AB if self.AB > 0 else 0 @slug_percent.expression def slug_percent(cls): return case([(cls.H + cls.B2 + 2 * cls.B3 + 3 * cls.HR) / cls.AB], else_=0)
class UserRequests(db.Model): """ Define the UserRequest class with the following columns and relationships: A UserRequest is a many to many relationship between users who are related to a certain request user_guid and request_id are combined to create a composite primary key user_guid = a foreign key that links to the primary key of the User table request_id = a foreign key that links to the primary key of the Request table request_user_type: Defines a user by their relationship to the request. Requester submitted the request, Agency is a user from the agency to whom the request is assigned. Anonymous request_user_type is not needed, since anonymous users can always browse a request for public information. """ __tablename__ = 'user_requests' user_guid = db.Column(db.String(64), primary_key=True) auth_user_type = db.Column(db.Enum(user_type_auth.AGENCY_USER, user_type_auth.AGENCY_LDAP_USER, user_type_auth.PUBLIC_USER_FACEBOOK, user_type_auth.PUBLIC_USER_MICROSOFT, user_type_auth.PUBLIC_USER_YAHOO, user_type_auth.PUBLIC_USER_LINKEDIN, user_type_auth.PUBLIC_USER_GOOGLE, user_type_auth.PUBLIC_USER_NYC_ID, user_type_auth.ANONYMOUS_USER, name='auth_user_type'), primary_key=True) request_id = db.Column(db.String(19), db.ForeignKey("requests.id"), primary_key=True) request_user_type = db.Column( db.Enum(user_type_request.REQUESTER, user_type_request.AGENCY, name='request_user_type')) permissions = db.Column(db.BigInteger) # Note: If an anonymous user creates a request, they will be listed in the UserRequests table, but will have the # same permissions as an anonymous user browsing a request since there is no method for authenticating that the # current anonymous user is in fact the requester. __table_args__ = (db.ForeignKeyConstraint( [user_guid, auth_user_type], [Users.guid, Users.auth_user_type]), ) @property def val_for_events(self): """ JSON to store in Events 'new_value' field. """ return { "user_guid": self.user_guid, "auth_user_type": self.auth_user_type, "request_user_type": self.request_user_type, "permissions": self.permissions } def has_permission(self, perm): """ Ex: has_permission(permission.ADD_NOTE) """ return bool(self.permissions & perm) def add_permissions(self, permissions): """ :param permissions: list of permissions from app.constants.permissions """ self.permissions |= reduce(ior, permissions) db.session.commit() def remove_permissions(self, permissions): """ :param permissions: list of permissions from app.constants.permissions """ self.permissions &= ~reduce(ior, permissions) db.session.commit() def set_permissions(self, permissions): """ :param permissions: list of permissions from app.constants.permissions or a permissions bitmask """ if isinstance(permissions, list): self.permissions = reduce(ior, permissions) else: self.permissions = permissions db.session.commit() def get_permissions(self): return [ i for i, p in enumerate(permission.ALL) if bool(self.permissions & p.value) ]