class BaseModel(db.Model):
__abstract__ = True
id = db.Column(db.Integer, primary_key=True)
created = db.Column(db.DateTime, default=db.func.now())
updated = db.Column(db.DateTime,
default=db.func.now(),
onupdate=db.func.now())
class BaseModel:
"""
Base Model Mixin
- Provide CRD operation (Update and bulk_insert have been removed since we dont use those in this project)
- Add timestamps (created, updated)
- Usage: Class AModel(ModelMixin, db.Model)
- Why add rollback: Since we give user permission to set commit=False, there would be cases session commit a lot
of transactions in one session, so we should rollback to the start rather than keep the successful transactions
in that session
"""
# Add __table_args so that if this table is already presented in the given MetaData, apply further arguments
# within the constructor to the existing table.
# MetaData: A collection of Table objects and their associated schema constructs.
__table_args__ = {'extend_existing': True}
created = db.Column(db.DateTime, default=datetime.now)
updated = db.Column(db.DateTime,
default=datetime.now,
onupdate=datetime.now)
@classmethod
def find_by_id(cls, _id):
"""
Find the object by its id
:param _id: id of the object you want to find
:return: The object that match that id, None if not found.
"""
return cls.query.get(_id)
def save(self, commit=True):
"""
Save the object to the database or simple add to the session
:param commit: If you want to commit to the database immediately, default = True
:raise Exception: many exception can be raised, seek help(sqlalchemy.exc)
:return: the object itself
"""
db.session.add(self)
if commit:
try:
db.session.commit()
except Exception:
db.session.rollback()
raise
return self
def delete(self, commit=True):
"""
Delete object from database
:param commit: If you want to commit to the database immediately, default = True
:raise Exception: many exception can be raised, seek help(sqlalchemy.exc)
"""
db.session.delete(self)
if commit:
try:
db.session.commit()
except Exception:
db.session.rollback()
raise
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(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 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 BaseModel:
created_at = db.Column(db.DateTime, server_default=db.func.now())
updated_at = db.Column(db.DateTime,
server_default=db.func.now(),
server_onupdate=db.func.now())
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 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, db.Model):
__tablename__ = 'items'
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)
category_id = db.Column(db.Integer,
db.ForeignKey('categories.id'),
nullable=False)
creator_id = db.Column(db.Integer,
db.ForeignKey('users.id'),
nullable=False)
class User(db.Model):
"""User Model for 'users' table"""
__tablename__ = "users"
username = db.Column(db.String(30), unique=True, nullable=False)
password_hash = db.Column(db.String(80), nullable=False)
name = db.Column(db.String(50), nullable=True)
def __repr__(self) -> str:
return (
f"<User(id={self.id}, username={self.username}, name={self.name}, "
+ f"created_at={self.created_at}, updated_at={self.updated_at})>"
)
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 Habit(db.Model):
"""Habit Model for 'habits' table"""
__tablename__ = "habits"
user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
name = db.Column(db.String(255), nullable=False)
countable = db.Column(db.Boolean, default=False, nullable=False)
status = db.Column(db.String(32), default=HabitStatus.ACTIVE, nullable=False)
def __repr__(self) -> str:
return (
f"<Habit(id={self.id}, name={self.name}, user_id={self.user_id}, "
+ f"countable={self.countable}, status={self.status}, "
+ f"created_at={self.created_at}, updated_at={self.updated_at})>"
)
class Log(db.Model):
"""Log Model for 'logs' table"""
__tablename__ = "logs"
habit_id = db.Column(db.Integer,
db.ForeignKey("habits.id"),
nullable=False)
date = db.Column(db.Date, nullable=False)
count = db.Column(db.Integer, nullable=True) # defaults to NULL
status = db.Column(db.String(32), default=LogStatus.ACTIVE, nullable=False)
def __repr__(self) -> str:
return (
f"<Log(habit_id={self.habit_id}, date={self.date}, count={self.count}, "
+
f"id={self.id}, created_at={self.created_at}, updated_at={self.updated_at})>"
)
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 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 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 UserModel(BaseModel):
__tablename__ = 'user'
username = db.Column(db.String(45))
hashed_password = db.Column(db.String(128))
items = db.relationship('ItemModel', lazy='dynamic')
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")