class ItemModel(BaseModel):
__tablename__ = "item"
name = db.Column(db.String(20), unique=True, nullable=False)
description = db.Column(db.Text, nullable=False)
category_id = db.Column(db.Integer, db.ForeignKey("category.id"))
user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
category = db.relationship("CategoryModel")
user = db.relationship("UserModel")
class CategoryModel(db.Model, BaseModel):
__tablename__ = 'categories'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(CATEGORY_NAME_LENGTH), nullable=False)
description = db.Column(db.String(MAX_DESC_LENGTH))
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
user = db.relationship('UserModel', lazy='joined')
items = db.relationship('ItemModel', lazy='joined')
def __init__(self, **kwargs):
super(CategoryModel, self).__init__(**kwargs)
class ItemModel(BaseModel):
__tablename__ = 'item'
name = db.Column(db.String(45))
description = db.Column(db.Text)
updated = db.Column(db.DateTime,
default=db.func.now(),
onupdate=db.func.now())
category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
category = db.relationship('CategoryModel')
user = db.relationship('UserModel')
class UserModel(db.Model, BaseModel):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(MAX_USERNAME_LENGTH), unique=True)
hashed_password = db.Column(db.String(HASHED_PASSWORD_LENGTH),
nullable=False)
salt = db.Column(db.String(SALT_LENGTH), nullable=False)
category = db.relationship('CategoryModel', lazy='joined')
item = db.relationship('ItemModel', lazy='joined')
def __init__(self, **kwargs):
super(UserModel, self).__init__(**kwargs)
@classmethod
def find_by_username(cls, username):
return cls.query.filter_by(username=username).first()
class CategoryModel(BaseModel, db.Model):
__tablename__ = 'categories'
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), unique=True, nullable=False)
description = db.Column(db.String(1000), nullable=False)
creator_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
items = db.relationship('ItemModel', backref='category', lazy='dynamic')
class ReviewModel(db.Model):
# Specify the db table name for sqlalchemy to link with this model
__tablename__ = 'reviews'
# Specify the table columns for sqlalchemy to link with this model
# The column variables must match with the instance attributes except for primary key
id = db.Column(db.Integer, primary_key=True)
comment = db.Column(db.String)
rating = db.Column(db.SmallInteger)
suggested_rating = db.Column(db.SmallInteger)
sentiment_score = db.Column(db.Float)
# Define foreign key in the reviews table that links each entry to a single entry in the companies table.
company_id = db.Column(db.Integer, db.ForeignKey('companies.id'))
company = db.relationship('CompanyModel')
user_agent = db.Column(db.String)
ip_address = db.Column(db.String)
def __init__(self, comment: str, rating: int, suggested_rating: int,
sentiment_score: float, company_id: int, user_agent: str,
ip_address: str):
self.comment = comment
self.rating = rating
self.suggested_rating = suggested_rating
self.sentiment_score = sentiment_score
self.company_id = company_id
self.user_agent = user_agent
self.ip_address = ip_address
def json(self):
return {
'comment': self.comment,
'rating': self.rating,
'suggested_rating': self.suggested_rating,
'sentiment_score': self.sentiment_score,
'company': self.company.name,
'user_agent': self.user_agent,
'ip_address': self.ip_address
}
# Get all reviews for a particular company
@classmethod
def find_by_company(cls, company_id: int):
return cls.query.filter_by(company_id=company_id).all()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
class CompanyModel(db.Model):
# Specify the db table name for sqlalchemy to link with this model
__tablename__ = 'companies'
# Specify the table columns for sqlalchemy to link with this model
# The column variables must match with the instance attributes
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String)
logo_url = db.Column(db.String)
website_url = db.Column(db.String)
# Evaluate lazily so it doesn't take up too much resource everytime a company is created
reviews = db.relationship('ReviewModel', lazy='dynamic')
review_count = db.Column(db.Integer)
def __init__(self,
name: str = None,
logo_url: str = None,
website_url: str = None):
self.name = name
self.logo_url = logo_url
self.website_url = website_url
self.review_count = len(self.reviews.all())
def json(self):
return {
'id': self.id,
'name': self.name,
'logo_url': self.logo_url,
'website_url': self.website_url,
'review_count': self.review_count
}
# Find a company in the database by name
@classmethod
def find_by_id(cls, _id):
return cls.query.filter_by(id=_id).first()
def save_to_db(self):
db.session.add(self)
db.session.commit()
def delete_from_db(self):
db.session.delete(self)
db.session.commit()
def update_review_count(self):
self.review_count = len(self.reviews.all())
self.save_to_db()
class UserModel(BaseModel, db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(80), unique=True, nullable=False)
hashed_password = db.Column(db.CHAR(60), nullable=False)
# lazy='dynamic' will return a query object, so we can do more operation like filter_by,...
items = db.relationship('ItemModel', lazy='dynamic')
def __init__(self, **kwargs):
"""
Customized constructor of User Model for hashing password
:param kwargs['email']: email of the User
:param kwargs['password']: prehash-password of the User
"""
prehash_password = kwargs['password']
del kwargs['password']
# bcrypt will automatically generate a salt if not specified (recommended)
self.hashed_password = bcrypt.hash(prehash_password)
kwargs['hashed_password'] = self.hashed_password
super().__init__(**kwargs)
class UserModel(BaseModel):
__tablename__ = "user"
username = db.Column(db.String(20), unique=True, nullable=False)
password = db.Column(db.String(64), nullable=False)
items = db.relationship("ItemModel")
class CategoryModel(BaseModel):
__tablename__ = 'category'
name = db.Column(db.String(45))
description = db.Column(db.Text)
items = db.relationship('ItemModel', lazy='dynamic')
class CategoryModel(BaseModel):
__tablename__ = "category"
name = db.Column(db.String(20), unique=True, nullable=False)
items = db.relationship("ItemModel", lazy="dynamic")
class UserModel(BaseModel):
__tablename__ = 'user'
username = db.Column(db.String(45))
hashed_password = db.Column(db.String(128))
items = db.relationship('ItemModel', lazy='dynamic')