class Post(db.Model): id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow) created_by = db.Column(UUID(as_uuid=True), db.ForeignKey('registered_user.id'), nullable=False) content = db.Column(db.String(1000), nullable=False) status = db.Column(db.String, db.ForeignKey('review_status_kind.name'), default='pending') prompt_id = db.Column(UUID(as_uuid=True), db.ForeignKey('prompt.id'), nullable=False) reviews = db.relationship('PostReview', lazy='dynamic') def with_reviews(self): self.reviews = [r for r in self.reviews.all()] return self def save_to_db(self): db.session.add(self) db.session.commit() @classmethod def get_pending(cls): return cls.query.filter_by(status='pending').all() @classmethod def get_approved(cls): return cls.query.filter_by(status='approved').all() @classmethod def get_rejected(cls): return cls.query.filter_by(status='rejected').all()
class FoodKind(BaseMixin, UserDefinedNameMixin, db.Model): user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('app_user.id')) unit_of_measurement_id = db.Column(UUID(as_uuid=True), db.ForeignKey('unit_of_measurement.id'), default=UNIT_OF_MEASURE_IS_SELF_ID) serving_size = db.Column(db.Float, default=0.0) unit_of_measurement = db.relationship('UnitOfMeasurement', lazy='joined') nutrition_info = db.relationship('FoodKindNutritionInfo', lazy='joined', uselist=False) categories = db.relationship('FoodCategory', secondary='food_kind_category', lazy='subquery', backref=db.backref('food_kinds', lazy=True)) stock_items = db.relationship('StockItem', lazy=True) def full_dict(self): remove = ['unit_of_measurement_id'] return { **{ key: getattr(self, key) for key in self.cols_dict().keys() if key not in remove }, 'unit_of_measurement': self.unit_of_measurement, 'categories': self.categories, 'nutrition_info': self.nutrition_info or {} }
class Notification(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) status = db.Column(db.String, nullable=False, default='unread') title = db.Column(db.String) description = db.Column(db.String) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) interview_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, user_id, title, description, interview_id): self.user_id = user_id self.title = title self.description = description self.interview_id = interview_id @classmethod def get_pag(cls, data, id): return cls.query.filter(cls.user_id == id).order_by( cls.created_at.desc()).paginate(data['page'], 10, False) @classmethod def unreadNotificationsCount(cls, id): return cls.query.filter(cls.user_id == id, cls.status == 'unread').count()
class PromptReview(db.Model): id = db.Column(UUID(as_uuid=True), primary_key=True) prompt_id = db.Column(UUID(as_uuid=True), db.ForeignKey('prompt.id'), nullable=False) date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow) date_modified = db.Column(TIMESTAMP(timezone=True)) created_by = db.Column(UUID(as_uuid=True), db.ForeignKey('registered_user.id'), nullable=False) is_approved = db.Column(db.Boolean, nullable=False) comments = db.Column(db.String(400))
class StockItem(UuidPrimaryKeyMixin, CreatedUpdatedTimestampMixin, db.Model): stock_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock.id'), nullable=False) item_id = db.Column(UUID(as_uuid=True), db.ForeignKey('item.id'), nullable=False) snapshots = db.relationship('StockItemSnapshot', lazy=True)
class UserProfile(TimestampMixin, db.Model): user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('registered_user.id'), primary_key=True) role_id = db.Column(db.Integer, db.ForeignKey('user_role.id')) username = db.Column(db.String, unique=True, nullable=False) role = db.relationship('UserRole', lazy='joined') def save_to_db(self): db.session.add(self) db.session.commit()
class StockItemSnapshot(UuidPrimaryKeyMixin, CreatedUpdatedTimestampMixin, db.Model): '''Represents the state of an item at any given time''' stock_item_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock_item.id'), nullable=False) maintainer_id = db.Column(UUID(as_uuid=True), db.ForeignKey('maintainer.id'), nullable=False) unit_of_measure_id = db.Column(db.Integer, db.ForeignKey('unit_of_measure.id'), nullable=False) quantity = db.Column(db.Float, nullable=False, default=0.0)
class Curriculum_Category(db.Model, BaseModelMixin): category_id = db.Column(db.Integer, db.ForeignKey('category.id'), primary_key=True) curriculum_id = db.Column(db.Integer, db.ForeignKey('curriculum.id'), primary_key=True) category = db.relationship("Category") created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self): pass
class Message(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) status = db.Column(db.String, nullable=False, default='unread') text = db.Column(db.String) created_by = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) to_interview = db.Column(db.Integer, db.ForeignKey('interview.id'), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) read_at = db.Column(db.DateTime) def __init__(self, created_by, text): self.created_by = created_by self.text = text
class Requirement(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) key = db.Column(db.String) value = db.Column(db.String) job_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False) def __init__(self, key, value): self.key = key self.value = value
class StockItemState(TimestampMixin, db.Model): stock_item_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock_item.id')) packaging_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('packaging_kind.id')) packaging_state_id = db.Column(UUID(as_uuid=True), db.ForeignKey('packaging_state.id')) quantity = db.Column(db.Integer, default=0) # has lazy loaded 'backref'ed relationship field 'food_item' packaging_kind = db.relationship('PackagingKind', lazy='joined') packaging_state = db.relationship('PackagingState', lazy='joined') def full_dict(self): remove = ['packaging_kind_id', 'packaging_state_id'] return { **{ key: getattr(self, key) for key in self.cols_dict().keys() if key not in remove }, 'packaging_kind': self.packaging_kind, 'packaging_state': self.packaging_state }
class Search(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, text, user_id): self.text = text self.user_id = user_id
class StockItem(TimestampMixin, db.Model): stock_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock.id')) food_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('food_kind.id')) date_item_was_new = db.Column(DATE(), default=datetime.utcnow) expiration_date = db.Column(DATE(), nullable=False) # 'states' field in StockItem instance is sqlalchemy query object which can # be used to specify whether or not to grab all previous 'StockItemState's (a lot) # or just the most recent one...or something else states = db.relationship('StockItemState', lazy='dynamic', backref=db.backref('food_item', lazy=True)) food_kind = db.relationship('FoodKind', lazy='joined') def full_dict(self): current_state = self.states.order_by(text('date_created desc')).first() return { **self.cols_dict(), 'food_kind': self.food_kind.full_dict(), 'current_state': current_state.full_dict() if current_state else None, }
class Language(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) curriculum_id = db.Column(db.Integer, db.ForeignKey('curriculum.id'), nullable=False) def __init__(self, name): self.name = name
class Rating(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) description = db.Column(db.String) score = db.Column(db.Integer) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) company_id = db.Column(db.Integer, db.ForeignKey('company.id'), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, description, score, user_id, company_id): self.description = description self.score = score self.user_id = user_id self.company_id = company_id @classmethod def get_by_user_id_company(cls, id, companyId): return cls.query.filter(cls.user_id == id and cls.company_id == companyId).first()
class Application(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) status = db.Column(db.String, nullable=False, default='created') job_id = db.Column(db.Integer, db.ForeignKey('job.id'), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, user_id): self.user_id = user_id @classmethod def get_pag(cls, data): filtro = cls.query filtro = filtro.filter(cls.status == data['status']) if 'user' in data: filtro = filtro.filter(cls.user_id == data['user']) if 'job' in data: filtro = filtro.filter(cls.job_id == data['job']) if 'order' in data and data['order'] == 'asc': filtro = filtro.order_by(cls.created_at.asc()) else: filtro = filtro.order_by(cls.created_at.desc()) if 'page' in data and 'per_page' in data: return filtro.paginate(int(data['page']), int(data['per_page']), False, 40) else: return filtro.paginate(1, 10, False) @classmethod def get_by_user_and_job(cls, user_id, job_id): filtro = cls.query filtro = filtro.filter(cls.user_id == user_id) filtro = filtro.filter(cls.job_id == job_id) return filtro.first()
class UserLogin(db.Model): id = db.Column(db.Integer, primary_key=True) date_created = db.Column(TIMESTAMP(timezone=True), default=datetime.utcnow) user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('registered_user.id'), nullable=False) def __repr__(self): return f"UserLogin {self.user_id} - {self.date_created}" def save_to_db(self): db.session.add(self) db.session.commit()
class Education(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100)) place = db.Column(db.String) start_date = db.Column(db.DateTime) end_date = db.Column(db.DateTime) curriculum_id = db.Column(db.Integer, db.ForeignKey('curriculum.id'), nullable=False) def __init__(self, name): self.name = name
class Subcategory(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) jobs = db.relationship('Job', backref='subcategory', lazy=False, cascade='all, delete-orphan') category_id = db.Column(db.Integer, db.ForeignKey('category.id'), nullable=False) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, name): self.name = name @classmethod def getByName(cls, name): return cls.query.filter(cls.name == name).first()
class RegisteredUser(db.Model): id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4) oauth_openid = db.Column( db.String, unique=True) # if the user registered through OAuth2 username = db.Column(db.String) email = db.Column(db.String, unique=True, nullable=False) role_id = db.Column(db.Integer, db.ForeignKey('user_role.id')) role = db.relationship('UserRole', lazy='joined') logins = db.relationship('UserLogin', lazy='dynamic') posts = db.relationship('Post', lazy='dynamic') prompts = db.relationship('Prompt', lazy='dynamic') def __repr__(self): return f"RegisteredUser {self.id} - {self.email} - {self.username} - {self.role}" @classmethod def get_profile_user(cls, user_id): '''Use this method to fetch data to display when any given logged on user views their profile information''' user = cls.query.get(user_id) if user: return dict( id=user.id, username=user.username, email=user.email, role=user.role, posts=[p for p in user.posts.all()], prompts=[p for p in user.prompts.all()], ) @classmethod def get_profile_admin(cls, user_id): '''Use this method to fetch data about users for admins to review/use in their tasks''' user = cls.query.get(user_id) if user: return dict( id=user.id, username=user.username, email=user.email, role=user.role, posts=[p.with_reviews() for p in user.posts.all()], prompts=[p.with_reviews() for p in user.prompts.all()], logins=[l for l in user.logins.all()], ) def save_to_db(self): db.session.add(self) db.session.commit()
class ItemModel(db.Model): __tablename__ = "items" id = db.Column(db.Integer, primary_key=True) item = db.Column(db.String(80)) price = db.Column(db.Float(precision=2)) store_id = db.Column(db.Integer, db.ForeignKey("stores.id")) store = db.relationship('StoreModel') def __init__(self, item, price, store_id): self.item = item self.price = price self.store_id = store_id def json(self): return { "name": self.item, "price": self.price, "store_id": self.store_id } @classmethod def find_by_name(cls, name): return cls.query.filter_by(item=name).first() # @classmethod def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() @classmethod def get_all(cls): return cls.query.all() @classmethod def delete_all(cls): return cls.query.delete()
class Stock(TimestampMixin, UserDefinedNameMixin, db.Model): user_id = db.Column(UUID(as_uuid=True), db.ForeignKey('app_user.id')) # each snapshot is a collection of 'stock_item_state's. a snapshot # represents the state of a 'stock' at a given moment in time snapshots = db.relationship('Snapshot', lazy='dynamic', backref=db.backref('stock', lazy=True)) # represents the 'food_item's in a 'stock' stock_items = db.relationship('StockItem', lazy='joined') def __init__(self, **kwargs): super(Stock, self).__init__(**kwargs) self.user_id = kwargs.get('user_id') def full_dict(self): return { **self.cols_dict(), 'snapshots': [s for s in self.snapshots.all()], 'items': [item.full_dict() for item in self.stock_items] }
class Role(db.Model): id = db.Column(db.Integer(), primary_key=True) name = db.Column(db.String(50), unique=True) user_id = db.Column(db.Integer, db.ForeignKey('user.id')) def __init__(self, name): self.name = name def __repr__(self): return '<Role {}>'.format(self.name) def json(self): return {"id": self.id, "name": self.name}, 200 def save_to_db(self): db.session.add(self) db.session.commit() @classmethod def find_role_by_name(cls, name): return cls.query.filter_by(name=name).first()
class Snapshot(TimestampMixin, db.Model): stock_id = db.Column(UUID(as_uuid=True), db.ForeignKey('stock.id')) stock_item_states = db.relationship('StockItemState', secondary='snapshot_stock_item_state', lazy='subquery') # has lazy loaded 'backref' prop 'stock'
from sqlalchemy.dialects.postgresql import UUID from app.database.db import db snapshot_stock_item_state = db.Table( 'snapshot_stock_item_state', db.Column('snapshot_id', db.ForeignKey('snapshot.id'), primary_key=True), db.Column('stock_item_state_id', db.ForeignKey('stock_item_state.id'), primary_key=True), )
from app.database.db import db user_role_permission = db.Table( 'user_role_permission', db.Column('role_id', db.Integer, db.ForeignKey('user_role.id'), primary_key=True), db.Column('permission_id', db.Integer, db.ForeignKey('user_permission.id'), primary_key=True))
class FoodKindNutritionInfo(TimestampMixin, db.Model): food_kind_id = db.Column(UUID(as_uuid=True), db.ForeignKey('food_kind.id'), primary_key=True) calories_per_serving = db.Column(db.Integer, default=0) notes = db.Column(db.String)
class Job(db.Model, BaseModelMixin): id = db.Column(db.Integer, primary_key=True) url = db.Column(db.String, nullable=False) title = db.Column(db.String, nullable=False) location = db.Column(db.String) workday = db.Column(db.String) contract_type = db.Column(db.String) salary = db.Column(db.Integer) salary_max = db.Column(db.Integer) description = db.Column(db.String) requirements = db.relationship('Requirement', backref='job', lazy=False, cascade='all, delete-orphan') applications = db.relationship('Application', backref='job', lazy=False, cascade='all, delete-orphan') company_id = db.Column(db.Integer, db.ForeignKey('company.id')) interviews = db.relationship('Interview', backref='job', lazy=False, cascade='all, delete-orphan') subcategory_id = db.Column(db.Integer, db.ForeignKey('subcategory.id')) active = db.Column(db.Boolean, default=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, url, title): self.url = url self.title = title @classmethod def get_pag(cls, data): filtro = cls.query filtro = filtro.filter(Job.active == True) if 'search' in data and data['search'] is not None: filtro = filtro.filter( db.or_(Job.title.contains(data['search'].strip()), Job.description.contains(data['search'].strip()))) if 'workday' in data and data['workday'] is not None: filtro = filtro.filter(Job.workday == data['workday']) if 'category' in data and data['category'] is not None: categoryId = str(data['category']) subcat = [ row for row in db.engine.execute( 'select * from subcategory where subcategory.category_id = ' + categoryId) ] filtro = filtro.filter(Job.subcategory_id == subcat[0]['id']) if 'minSalary' in data and data['minSalary'] is not None: filtro = filtro.filter(Job.salary > int(data['minSalary'])) if 'location' in data and data['location'] is not None: if data['location'] == 'remote': filtro = filtro.filter(Job.location == data['location']) else: filtro = filtro.filter( func.lower(Job.location).contains( data['location'].strip().lower())) if 'freelance' in data and data[ 'freelance'] is not None and data['freelance'] is True: filtro = filtro.filter(func.lower(Job.url).contains('workana')) if 'page' in data and 'per_page' in data: return filtro.paginate(data['page'], data['per_page'], False, 40) else: return filtro.paginate(1, 10, False) @classmethod def get_pag_company(cls, data): filtro = cls.query if 'company_id' in data and data['company_id'] is not None: filtro = filtro.filter(Job.company_id == data['company_id']) if 'page' in data and 'per_page' in data: return filtro.paginate(data['page'], data['per_page'], False, 40) else: return filtro.paginate(1, 10, False) @classmethod def search_by_title(cls, title): filtro = cls.query.filter(Job.title.contains(title)) return filtro.paginate(1, 5, False).items @classmethod def search_by_url(cls, url): filtro = cls.query.filter(Job.url == url) filtro = filtro.filter(Job.active == True).first() return filtro @classmethod def get_all_by_url(cls, url): filtro = cls.query.filter(Job.url.contains(url)) return filtro @classmethod def get_id(cls): filtro = db.engine.execute( "select setval('job_id_seq', (select max(id) from job));") return [row for row in filtro] @classmethod def search_by_id(cls, id): filtro = cls.query.filter(Job.id == id) filtro = filtro.filter(Job.active == True).first() return filtro
from sqlalchemy.dialects.postgresql import UUID from app.database.db import db item_tag = db.Table( 'item_tag', db.Column('item_id', UUID, db.ForeignKey('item.id'), primary_key=True), db.Column('tag_id', UUID, db.ForeignKey('tag.id'), primary_key=True))
from sqlalchemy.dialects.postgresql import UUID from app.database.db import db stock_maintainer = db.Table( 'stock_maintainer', db.Column('stock_id', UUID, db.ForeignKey('stock.id'), primary_key=True), db.Column('maintainer_id', UUID, db.ForeignKey('maintainer.id'), primary_key=True))