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)
]
