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)