class Customer(User): """Customer class inherit from User table.""" __tablename__ = 'customer' __mapper_args__ = {'polymorphic_identity': 'customer'} id = Column(db.String(80), db.ForeignKey("user.id"), primary_key=True) m_credit_no = Column(db.String(64), nullable=True) phone_no = Column(db.String(15), nullable=True) address = Column(db.String(255), nullable=True) def __init__(self, id, email, m_credit_no, phone_no, address, password=None, **kwargs): """init.""" super(Customer, self).__init__(id, email, password, **kwargs) self.m_credit_no = m_credit_no self.phone_no = phone_no self.address = address def address(self): """address.""" return '{0}'.format(self.address) def __repr__(self): """Represent instance as an unique string.""" return '<Customer({id!r})>'.format(id=self.id)
class Rates(db.Model): """Rate relationship table.""" __tablename__ = 'rates' feedback_id = db.Column(db.Integer, db.ForeignKey('feedback.id'), primary_key=True) rater_id = db.Column(db.String(80), db.ForeignKey('user.id'), primary_key=True) rated_id = db.Column(db.String(80), db.ForeignKey('user.id')) rating = db.Column(db.Integer, nullable=False) feedbacks = db.relation("Feedback", backref="ratings") rater = db.relationship("User", back_populates="ratings_given", primaryjoin="Rates.rater_id==User.id") def __init__(self, feedback_id, rater_id, rated_id, rating, **kwargs): """Create instance.""" db.Model.__init__(self, feedback_id=feedback_id, rater_id=rater_id, rated_id=rated_id, rating=rating, **kwargs) def __repr__(self): """Represent instance as an unique string.""" return '<Rates({!r}{!r})>rated_id={},rating={}'.format( self.feedback_id, self.rater_id, self.rated_id, self.rating)
class OrderConsistsOf(Model): """Books one order consists of.""" __tablename__ = 'consists_of' # use ForeignKey here consists_order_id = Column(db.Integer, db.ForeignKey('orders.id'), primary_key=True) consists_isbn13 = Column(db.String(13), db.ForeignKey('book.isbn13'), primary_key=True) # Non foreign key consists_qty = Column(db.Integer, default=1) book = db.relationship("Book", back_populates="book_in_order") order = db.relationship("Order", back_populates="main_order") def __init__(self, consists_order_id, consists_isbn13, consists_qty, **kwargs): """Create instance.""" db.Model.__init__(self, consists_order_id=consists_order_id, consists_isbn13=consists_isbn13, consists_qty=consists_qty, **kwargs) def __repr__(self): """Represent instance as a unique string.""" return '<OrderConsistsOf({order_id})>'.format( order_id=self.consists_order_id) @classmethod def get_by_id(cls, tuplething): """Get order by ID.""" if any((isinstance(tuplething[0], basestring) and isinstance(tuplething[1], basestring), isinstance(tuplething[0], str) and isinstance(tuplething[1], str)), ): return cls.query.get(tuplething) return None def to_json(self): """JSON.""" return dict(id=self.consists_order_id, isbn13=self.consists_isbn13, quantity=self.consists_qty)
class Order(Model): """Order model.""" __tablename__ = 'orders' id = db.Column(db.Integer, autoincrement=True, primary_key=True) customer_id = Column(db.String(80), db.ForeignKey('user.id')) customer = db.relationship(Customer, backref="orders") main_order = db.relationship("OrderConsistsOf", back_populates="order") # customer_id = Column(db.String(80), unique=True, nullable=False) date = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow) qty = Column(db.Integer, default=1) status = Column(db.Boolean(), default=True) def __init__(self, customer_id, **kwargs): """Create instance.""" db.Model.__init__(self, customer_id=customer_id, **kwargs) @property def details(self): """Order and customer details.""" return '{0} {1}'.format(self.id, self.customer_id) @classmethod def get_by_id(cls, id): """Get order by ID.""" if any((isinstance(id, basestring), isinstance(id, str)), ): return cls.query.get(str(id)) return None # def get_customer_history(cls, customer_id): # if any( # (isinstance(customer_id, basestring), # isinstance(customer_id, str)), # ): # return cls.query.get(str(customer_id)) # return None def __repr__(self): """Represent instance as a unique string.""" return '<Order({id!r})>'.format(id=self.id) def to_json(self): """More json.""" return dict(id=self.id, customer=self.customer_id, status=self.status)
class User(UserMixin, Model): """A user of the app.""" __tablename__ = 'user' id = Column(db.String(80), primary_key=True, nullable=False) email = Column(db.String(80), unique=True, nullable=False) #: The hashed password password = Column(db.Binary(128), nullable=True) created_at = Column(db.DateTime, nullable=False, default=dt.datetime.utcnow) first_name = Column(db.String(30), nullable=True) last_name = Column(db.String(30), nullable=True) active = Column(db.Boolean(), default=False) is_admin = Column(db.Boolean(), default=False) user_type = Column('type', db.String(50)) __mapper_args__ = { 'polymorphic_on': 'user_type', 'polymorphic_identity': 'manager' } # followed = db.relationship('Rates', # primaryjoin=(Rates.rater_id == id), # secondaryjoin=(Rates.rated_id == id), # backref=db.backref('rates')) ratings_given = db.relationship("Rates", back_populates="rater", primaryjoin="Rates.rater_id==User.id") reviews = db.relationship("Feedback", back_populates="user") def __init__(self, id, email, password=None, **kwargs): """Create instance.""" db.Model.__init__(self, id=id, email=email, **kwargs) if password: self.set_password(password) else: self.password = None @classmethod def get_by_id(cls, record_id): """Get record by ID.""" if any( (isinstance(record_id, basestring), isinstance(record_id, str)), ): return cls.query.get(str(record_id)) return None def set_password(self, password): """Set password.""" self.password = bcrypt.generate_password_hash(password) def check_password(self, value): """Check password.""" return bcrypt.check_password_hash(self.password, value) @property def full_name(self): """Full user name.""" return '{0} {1}'.format(self.first_name, self.last_name) def __repr__(self): """Represent instance as a unique string.""" return '<User({id!r})>'.format(id=self.id)
class Book(Model): """Book.""" __tablename__ = 'book' isbn13 = Column(db.String(13), nullable=False, primary_key=True) title = Column(db.String(128), nullable=False) author = Column(db.String(128), nullable=False) publisher = Column(db.String(128), nullable=False) year_of_pub = Column(db.Integer, nullable=False) num_of_copies = Column(db.Integer, nullable=False) price = Column(db.Integer, nullable=False) format = Column(db.String(9), nullable=False) keywords = Column(db.String(128), nullable=False) subject = Column(db.String(128), nullable=False) reviewers = db.relationship("Feedback", back_populates="book") book_in_order = db.relationship("OrderConsistsOf", back_populates="book") # @aggregated('avgfb', db.Column(db.Float)) # def avg_feedback_score(self): # """Avg.""" # return db.func.avg(Feedback.score) def __init__(self, isbn13, title, author, publisher, year_of_pub, num_of_copies, price, format, keywords, subject): """Init.""" db.Model.__init__(self, isbn13=isbn13, title=title, author=author, publisher=publisher, year_of_pub=year_of_pub, num_of_copies=num_of_copies, price=price, format=format, keywords=keywords, subject=subject) def __repr__(self): """Represent instance as a unique string.""" return '<isbn13({isbn13})>'.format(isbn13=self.isbn13) def to_json(self): """To json.""" return dict(isbn13=self.isbn13, title=self.title, author=self.author, publisher=self.publisher, year_of_pub=self.year_of_pub, num_of_copies=self.num_of_copies, price=self.price, format=self.format, keywords=self.keywords, subject=self.subject) @classmethod def get_by_id(cls, isbn13): """Get by id.""" if any((isinstance(isbn13, basestring), isinstance(isbn13, str)), ): return cls.query.get(str(isbn13)) return None @property def book_title(self): """Book title.""" return '{0} {1}'.format(self.title) @hybrid_property def avgfb(self): """Avg.""" if len(self.reviewers) > 0: return sum([f.score for f in self.reviewers]) / len(self.reviewers) else: return 0 # @avgfb.expression # def avgfb(self): # """Avg exp.""" # return Book.avgfb @avgfb.expression def avgfb(cls): """Return avg of score for books from feedback.""" # http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html#correlated-subquery-relationship-hybrid return Feedback.query.with_entities( func.avg( Feedback.score).label("total")).filter_by(book_id=cls.isbn13)
class Feedback(db.Model): """Feedback relationship table.""" __tablename__ = 'feedback' id = db.Column(db.Integer, autoincrement=True, primary_key=True) user_id = db.Column(db.String(80), db.ForeignKey("user.id"), primary_key=True) book_id = db.Column(db.String(13), db.ForeignKey("book.isbn13"), primary_key=True) score = db.Column(db.Integer, nullable=False) # 0 to 10. date = db.Column(db.DateTime, nullable=False, default=dt.datetime.utcnow) short_text = db.Column(db.String(255), nullable=True, default="") user = db.relationship("User", back_populates="reviews") book = db.relationship("Book", back_populates="reviewers") def __init__(self, user_id, book_id, score, **kwargs): """Create instance.""" db.Model.__init__(self, user_id=user_id, book_id=book_id, score=score, **kwargs) def __repr__(self): """Represent instance as an unique string.""" return '<Feedback({!r}:{!r}{!r})>score={},text={}' \ .format(self.id, self.user_id, self.book_id, self.score, self.short_text) def has_rated(self, current_user_id): """Check if user has rated feedback.""" rcount = Rates.query.filter_by(rater_id=current_user_id, feedback_id=self.id).count() return rcount != 0 def total_rating(self): """Return sum of ratings for feedback. func.sum(Rates.rating).label("total") returns a Column or same type object it was given. Hence, need to use "with_entities(Rates ..." """ # total = db.session.query(func.sum(Rates.rating).label( # "total")).filter(Rates.feedback_id == self.id).scalar() total = Rates.query.with_entities( func.sum(Rates.rating).label("total")).filter_by( feedback_id=self.id).scalar() if not total: total = 0 return total def avg_rating(self): """Return avg of ratings for feedback.""" avg = Rates.query.with_entities(func.avg( Rates.rating).label("total")).filter_by( feedback_id=self.id).scalar() if not avg: return "None" return "{:.2f}".format(avg)