class Resume(db.Model):
__tablename__ = 'resume'
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey("contact.id"),
nullable=False)
name = db.Column(db.String(100), nullable=False)
date_created = db.Column(db.Date, nullable=False)
gdoc_id = db.Column(db.String(255))
class User(BaseModel, UserMixin):
__tablename__ = 'users'
username = db.Column(db.String(20))
# 存取密码的散列值
password_hash = db.Column(db.String(128))
avatar = db.Column(db.String(60), default='avatar_default.png')
def set_hash_password(self, password):
self.password_hash = generate_password_hash(password)
db.session.commit()
def validate_password(self, password):
return check_password_hash(self.password_hash, password)
def set_avatar(self, new_avatar):
self.avatar = new_avatar
db.session.commit()
@property
def is_admin(self):
"""判断是否是 admin 用户, 账号硬编码为 admin, admin"""
admin = self.__class__.get(username='******')
if self.password_hash != admin.password_hash or self.username != admin.username:
return False
return True
@classmethod
def validate_login(cls, form):
username = form.get('username', '')
password = form.get('password', '')
user = cls.get(username=username)
if user is not None and user.validate_password(password):
return user
return None
@classmethod
def register(cls, form):
username = form.get('username', '')
password = form.get('password', '')
if username and password and cls.get(username=username) is None:
# 检查用户名不能重复
user = cls.new(username=username)
user.set_hash_password(password)
return user
return None
def __eq__(self, other):
if not isinstance(other, User):
return False
return self.name == other.name
def __hash__(self):
return hash(self.name)
class OpportunityApp(db.Model):
__tablename__ = 'opportunity_app'
#table columns
id = db.Column(db.String, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
opportunity_id = db.Column(db.String,
db.ForeignKey('opportunity.id'),
nullable=False)
resume_id = db.Column(db.Integer,
db.ForeignKey('resume_snapshot.id'),
nullable=True)
interest_statement = db.Column(db.String(2000), nullable=True)
stage = db.Column(db.Integer, nullable=False, default=0)
is_active = db.Column(db.Boolean, nullable=False, default=True)
interview_date = db.Column(db.Date)
interview_time = db.Column(db.String)
resume = db.relationship('ResumeSnapshot')
contact = db.relationship('Contact', back_populates='applications')
opportunity = db.relationship('Opportunity')
__table_args__ = (db.Index('oppapp_contact_opportunity',
'contact_id',
'opportunity_id',
unique=True), )
#calculated fields
@hybrid_property
def status(self):
return ApplicationStage(self.stage)
@hybrid_property
def program_id(self):
return self.opportunity.program_id
@hybrid_property
def interview_completed(self):
if self.interview_date and self.interview_time:
interview_scheduled = dt.datetime.strptime(
f'{self.interview_date} {self.interview_time}',
'%Y-%m-%d %H:%M:%S')
return interview_scheduled < dt.datetime.now()
else:
return False
# for more info on why to use setattr() read this:
# https://medium.com/@s.azad4/modifying-python-objects-within-the-sqlalchemy-framework-7b6c8dd71ab3
def update(self, **update_dict):
for field, value in update_dict.items():
print(field, value)
if field in UPDATE_FIELDS:
setattr(self, field, value)
class ResumeSection(db.Model):
__tablename__ = 'resume_section'
#table columns
id = db.Column(db.Integer, primary_key=True)
resume_id = db.Column(db.Integer, db.ForeignKey('resume.id'), nullable=False)
name = db.Column(db.String(100), nullable=False)
min_count = db.Column(db.Integer)
max_count = db.Column(db.Integer)
#relationships
#resume = db.relationship('Resume', back_populates='sections')
items = db.relationship('ResumeItem', back_populates='section',
cascade='all, delete, delete-orphan')
class Movie(BaseModel):
__tablename__ = 'movies'
title = db.Column(db.String(60))
year = db.Column(db.String(4))
def __eq__(self, other):
if not isinstance(other, Movie):
return False
return self.title == other.title \
and self.year == other.year
def __hash__(self):
return hash((self.title, self.year))
class ProgramsCompleted(db.Model):
__tablename__ = 'programs_completed'
# table columns
id = db.Column(db.Integer, primary_key=True)
profile_id = db.Column(db.Integer,
db.ForeignKey('profile.id'),
nullable=False)
fellowship = db.Column(db.Boolean, default=False)
mayoral_fellowship = db.Column(db.Boolean, default=False)
public_allies = db.Column(db.Boolean, default=False)
kiva = db.Column(db.Boolean, default=False)
civic_innovators = db.Column(db.Boolean, default=False)
elevation_awards = db.Column(db.Boolean, default=False)
#relationships
profile = db.relationship('Profile', back_populates='programs_completed')
#methods
def update(self, **update_dict):
UPDATE_FIELDS = [
'fellowship',
'mayoral_fellowship',
'public_allies',
'kiva',
'civic_innovators',
'elevation_awards',
]
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
class Email(db.Model):
__tablename__ = 'email'
#table columns
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
is_primary = db.Column(db.Boolean, default=False)
email = db.Column(db.String(100), nullable=False)
type = db.Column(db.Enum(Type, name='EmailType'))
#relationships
contact = db.relationship('Contact', back_populates='email_primary')
class Ingredient(TimestampMixin, DB.Model):
""" An ingredient belongs to a recipe """
__tablename__ = 'ingredients'
id = DB.Column(DB.Integer, primary_key=True)
recipe_id = DB.Column(
DB.Integer, DB.ForeignKey('recipes.id'), nullable=False
)
title = DB.Column(DB.String)
description = DB.Column(DB.String)
def __repr__(self):
return "<Ingredient(ingredient_id='{}', recipe_id='{}', title='{}', description='{}')>".format( # NOQA
self.id, self.recipe_id, self.title, self.description) # NOQA
class RoleChoice(db.Model):
__tablename__ = 'role_choice'
# table columns
id = db.Column(db.Integer, primary_key=True)
profile_id = db.Column(db.Integer,
db.ForeignKey('profile.id'),
nullable=False)
advocacy_public_policy = db.Column(db.Boolean, default=False)
community_engagement_outreach = db.Column(db.Boolean, default=False)
data_analysis = db.Column(db.Boolean, default=False)
fundraising_development = db.Column(db.Boolean, default=False)
marketing_public_relations = db.Column(db.Boolean, default=False)
program_management = db.Column(db.Boolean, default=False)
#relationships
profile = db.relationship('Profile', back_populates='roles')
#methods
def update(self, **update_dict):
UPDATE_FIELDS = [
'advocacy_public_policy',
'community_engagement_outreach',
'data_analysis',
'fundraising_development',
'marketing_public_relations',
'program_management',
]
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
class Program(db.Model):
#table columns
id = db.Column(db.Integer, nullable=False, primary_key=True)
name = db.Column(db.String(100), nullable=False)
trello_board_id = db.Column(db.String)
#relationship fields
program_apps = db.relationship('ProgramApp',
back_populates='program',
cascade='all, delete, delete-orphan')
opportunities = db.relationship('Opportunity',
back_populates='program',
cascade='all, delete, delete-orphan')
class Instruction(TimestampMixin, DB.Model):
""" A recipe consists of instructions (the steps one has to follow in order
to prepare the meal). This class the contains the business logic of an
instruction.
"""
__tablename__ = 'instructions'
id = DB.Column(DB.Integer, primary_key=True)
recipe_id = DB.Column(DB.Integer, DB.ForeignKey('recipes.id'),
nullable=False)
title = DB.Column(DB.String)
description = DB.Column(DB.String)
def __repr__(self):
return "<Instruction(instruction_id='{}', recipe_id='{}', title='{}', description='{}')>".format( # NOQA
self.id, self.recipe_id, self.title, self.description) # NOQA
class TagItem(db.Model):
__tablename__ = 'tag_item'
#table columns
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
tag_id = db.Column(db.Integer, db.ForeignKey('tag.id'), nullable=False)
score = db.Column(db.Integer)
#relationships
#contact = db.relationship('Contact', back_populates='tags')
#tag = db.relationship('Tag', back_populates='contacts')
resumes = db.relationship('ResumeItem',
back_populates='tag',
cascade='all, delete, delete-orphan')
class Skill(db.Model):
__tablename__ = 'skill'
#table columns
id = db.Column(db.String, nullable=False, primary_key=True)
name = db.Column(db.String, nullable=False)
#relationships
capabilities = db.relationship('Capability',
secondary=capability_skills,
lazy='subquery',
back_populates='related_skills'
)
__table_args__ = (
db.UniqueConstraint('name', name='skill_name_uniq'),
)
class ProgramContact(db.Model):
__tablename__ = 'program_contact'
#table columns
id = db.Column(db.Integer, nullable=False, primary_key=True)
contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'), nullable=False)
program_id = db.Column(db.Integer, db.ForeignKey('program.id'), nullable=False)
card_id = db.Column(db.String(25))
stage = db.Column(db.Integer)
is_approved = db.Column(db.Boolean, default=False)
is_active = db.Column(db.Boolean, default=True)
#relationship fields
program = db.relationship('Program', back_populates='contacts')
contact = db.relationship('Contact', back_populates='programs')
# for more info on why to use setattr() read this:
# https://medium.com/@s.azad4/modifying-python-objects-within-the-sqlalchemy-framework-7b6c8dd71ab3
def update(self, **update_dict):
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
db.session.commit()
@hybrid_property
def applications(self):
return [app for app in self.contact.applications
if app.program_id == self.program_id]
class Capability(db.Model):
__tablename__ = 'capability'
#table columns
id = db.Column(db.String, nullable=False, primary_key=True)
name = db.Column(db.String, nullable=False)
#relationships
related_skills = db.relationship('Skill',
secondary=capability_skills,
lazy='subquery',
back_populates='capabilities')
recommended_skills = db.relationship('SkillRecommendation',
order_by='SkillRecommendation.order',
cascade='all, delete')
__table_args__ = (
db.UniqueConstraint('name', name='capability_name_uniq'),
)
class SkillRecommendation(db.Model):
"""These are recommendations the system makes to users about skills they
should consider choosing in certain capabilities
"""
__tablename__ = 'capability_skill_recommendations'
#table columns
capability_id = db.Column(db.String, db.ForeignKey('capability.id'), nullable=False)
skill_id = db.Column(db.String, db.ForeignKey('skill.id'), nullable=False)
order = db.Column(db.Integer, nullable=False)
#relationships
skill = db.relationship('Skill')
__table_args__ = (
db.PrimaryKeyConstraint('capability_id', 'skill_id', name='cap_skill_rec_pk'),
db.UniqueConstraint('capability_id', 'order', name='cap_skill_rec_order_uniq'),
)
class Achievement(db.Model):
__tablename__ = 'achievement'
#table columns
id = db.Column(db.Integer, primary_key=True)
exp_id = db.Column(db.Integer,
db.ForeignKey('experience.id'),
nullable=False)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
description = db.Column(db.String)
#relationships
experience = db.relationship('Experience', back_populates='achievements')
contact = db.relationship('Contact', back_populates='achievements')
skill_items = db.relationship('AchievementSkill',
cascade='all, delete, delete-orphan')
def add_skill(self, skill, capability=None):
capability_id = capability.id if capability else None
experience_skill = self.experience.add_skill(skill)
achievement_skill = (AchievementSkill.query.filter_by(
achievement_id=self.id,
parent_id=experience_skill.id,
capability_id=capability_id).first())
if not achievement_skill:
achievement_skill = AchievementSkill(experience_skill, self,
capability)
self.skill_items.append(achievement_skill)
return achievement_skill
#calculated fields
@hybrid_property
def skills(self):
skills = []
for skill_item in self.skill_items:
if not skill_item.deleted:
skills.append({
'name': skill_item.skill.name,
'capability_id': skill_item.capability_id,
})
return sorted(skills, key=lambda skill: skill['name'])
class CapabilitySkillSuggestion(db.Model):
"""These are suggestions the user makes about which skills should be
associated with which capablities
"""
__tablename__ = 'capability_skill_suggestions'
#table columns
capability_id = db.Column(db.String, db.ForeignKey('capability.id'), nullable=False)
skill_id = db.Column(db.String, db.ForeignKey('skill.id'), nullable=False)
contact_id = db.Column(db.Integer, db.ForeignKey('contact.id'), nullable=False)
#relationships
capability = db.relationship('Capability')
skill = db.relationship('Skill')
contact = db.relationship('Contact', back_populates='capability_skill_suggestions')
__table_args__ = (
db.PrimaryKeyConstraint('contact_id', 'capability_id', 'skill_id', name='cap_skill_suggestion_pk'),
)
class ContactSkill(db.Model):
__tablename__ = 'contact_skill_item'
#table columns
id = db.Column(db.Integer, primary_key=True)
skill_id = db.Column(db.String, db.ForeignKey('skill.id'))
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
deleted = db.Column(db.Boolean, nullable=False, default=False)
#relationships
skill = db.relationship('Skill')
experiences = db.relationship('ExperienceSkill',
back_populates='parent',
cascade='all, delete, delete-orphan')
contact = db.relationship('Contact', back_populates='skill_items')
def __init__(self, skill=None, contact=None):
self.skill = skill
self.contact = contact
class ExperienceSkill(db.Model):
__tablename__ = 'experience_skill_item'
#table columns
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer, db.ForeignKey('contact_skill_item.id'))
experience_id = db.Column(db.Integer, db.ForeignKey('experience.id'))
#relationships
parent = db.relationship('ContactSkill', back_populates='experiences')
achievements = db.relationship('AchievementSkill',
back_populates='parent',
cascade='all, delete, delete-orphan')
skill = association_proxy('parent', 'skill')
deleted = association_proxy('parent', 'deleted')
experience = db.relationship('Experience', back_populates='skill_items')
def __init__(self, parent=None, experience=None):
self.parent = parent
self.experience = experience
class ProgramApp(db.Model):
__tablename__ = 'program_app'
#table columns
id = db.Column(db.Integer, nullable=False, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
program_id = db.Column(db.Integer,
db.ForeignKey('program.id'),
nullable=False)
is_approved = db.Column(db.Boolean, default=False)
is_interested = db.Column(db.Boolean, default=False)
decision_date = db.Column(db.Date)
#relationship fields
program = db.relationship('Program', back_populates='program_apps')
contact = db.relationship('Contact', back_populates='program_apps')
# for more info on why to use setattr() read this:
# https://medium.com/@s.azad4/modifying-python-objects-within-the-sqlalchemy-framework-7b6c8dd71ab3
def update(self, **update_dict):
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
@hybrid_property
def status(self):
if not self.is_interested:
return 'Not interested'
elif self.is_interested and self.is_approved:
return 'Eligible'
else:
return 'Waiting for approval'
class UserSession(UserMixin, db.Model):
__tablename__ = 'user_session'
#table columns
# This is the session id, not the user's id
# This means it changes every time the user starts a new session
id = db.Column(db.String, primary_key=True)
auth_id = db.Column(db.String, nullable=False)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
jwt = db.Column(db.String(1000), nullable=False)
expiration = db.Column(db.DateTime,
default=datetime.utcnow,
nullable=False)
#relationships
contact = db.relationship('Contact')
@hybrid_property
def is_authenticated(self):
return self.expiration > datetime.utcnow()
class Token(BaseModel):
__tablename__ = "token"
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
token = db.Column(db.String(250))
token1 = db.Column(db.String(250))
@staticmethod
async def generate_key():
return binascii.hexlify(os.urandom(20)).decode()
@staticmethod
async def get_or_create(user_id):
check = await Token.query.where(Token.user_id == user_id).gino.first()
if check:
return check.token
key = await Token.generate_key()
token = await Token.create(user_id=user_id, token=key)
return token.token
@staticmethod
async def check_token(token):
return await Token.query.where(Token.token == token).gino.first()
class AchievementSkill(db.Model):
__tablename__ = 'achievement_skill_item'
#table columns
id = db.Column(db.Integer, primary_key=True)
parent_id = db.Column(db.Integer,
db.ForeignKey('experience_skill_item.id'))
achievement_id = db.Column(db.Integer, db.ForeignKey('achievement.id'))
capability_id = db.Column(db.String,
db.ForeignKey('capability.id'),
nullable=True)
#relationships
parent = db.relationship('ExperienceSkill', back_populates='achievements')
skill = association_proxy('parent', 'skill')
deleted = association_proxy('parent', 'deleted')
capability = db.relationship('Capability')
achievement = db.relationship('Achievement', back_populates='skill_items')
def __init__(self, parent=None, achievement=None, capability=None):
self.parent = parent
self.achievement = achievement
if capability is not None:
self.capability = capability
class Recipe(TimestampMixin, DB.Model):
""" Encapsulates the business logic of a recipe in the yummy recipes
system.
A recipe belongs to a User
"""
__tablename__ = 'recipes'
id = DB.Column(DB.Integer, primary_key=True)
user_id = DB.Column(DB.Integer, DB.ForeignKey('users.id'), nullable=False)
title = DB.Column(DB.String, unique=True)
description = DB.Column(DB.String)
fulfilled = DB.Column(DB.Boolean)
instructions = DB.relationship('Instruction', backref='recipe', lazy=True)
ingredients = DB.relationship('Ingredient', backref='recipe', lazy=True)
def __repr__(self):
return "<Recipe(user_id='{}', title='{}', description='{}')>".format(
self.user_id, self.title, self.description)
def save(self):
"""This method persists the recipe object in the database"""
DB.session.add(self)
DB.session.commit()
class ContactAddress(db.Model):
__tablename__ = 'contact_address'
#table columns
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
profile_id = db.Column(db.Integer,
db.ForeignKey('profile.id'),
nullable=False)
is_primary = db.Column(db.Boolean, default=True)
street1 = db.Column(db.String)
street2 = db.Column(db.String)
city = db.Column(db.String)
state = db.Column(db.String)
country = db.Column(db.String)
zip_code = db.Column(db.String)
#relationships
contact = db.relationship('Contact', back_populates='addresses')
profile = db.relationship('Profile', back_populates='addresses')
#methods
def update(self, **update_dict):
UPDATE_FIELDS = [
'street1',
'street2',
'city',
'state',
'country',
'zip_code',
]
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
class ResumeItem(db.Model):
__tablename__ = 'resume_item'
#table columns
resume_order = db.Column(db.Integer, nullable=False, primary_key=True)
section_id = db.Column(db.Integer,
db.ForeignKey('resume_section.id'),
nullable=False,
primary_key=True)
exp_id = db.Column(db.Integer, db.ForeignKey('experience.id'))
tag_id = db.Column(db.Integer, db.ForeignKey('tag_item.id'))
achievement_id = db.Column(db.Integer, db.ForeignKey('achievement.id'))
resume_id = db.Column(db.Integer, db.ForeignKey('resume.id'))
indented = db.Column(db.Boolean, default=False)
#relationships
section = db.relationship('ResumeSection', back_populates='items')
experience = db.relationship('Experience')
tag = db.relationship('TagItem')
achievement = db.relationship('Achievement')
resume = db.relationship('Resume')
class ResumeSnapshot(db.Model):
__tablename__ = 'resume_snapshot'
id = db.Column(db.Integer, primary_key=True)
resume = db.Column(db.Text, nullable=False)
class Race(db.Model):
__tablename__ = 'race'
# table columns
id = db.Column(db.Integer, primary_key=True)
contact_id = db.Column(db.Integer,
db.ForeignKey('contact.id'),
nullable=False)
profile_id = db.Column(db.Integer,
db.ForeignKey('profile.id'),
nullable=False)
american_indian = db.Column(db.Boolean, default=False)
asian = db.Column(db.Boolean, default=False)
black = db.Column(db.Boolean, default=False)
hawaiian = db.Column(db.Boolean, default=False)
hispanic = db.Column(db.Boolean, default=False)
south_asian = db.Column(db.Boolean, default=False)
white = db.Column(db.Boolean, default=False)
not_listed = db.Column(db.Boolean, default=False)
race_other = db.Column(db.String)
#relationships
contact = db.relationship('Contact', back_populates='race')
profile = db.relationship('Profile', back_populates='race')
#methods
def update(self, **update_dict):
UPDATE_FIELDS = [
'american_indian', 'asian', 'black', 'hawaiian', 'hispanic',
'south_asian', 'white', 'not_listed', 'race_other'
]
for field, value in update_dict.items():
if field in UPDATE_FIELDS:
setattr(self, field, value)
class TimestampMixin:
created = DB.Column(DB.DateTime, nullable=False, default=datetime.utcnow)
modified = DB.Column(DB.DateTime, onupdate=datetime.utcnow)
