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")