class Variety(db.Model): """Database model for varieties in products (sizes, etc). Contains: - id: int, auto-incremented. - product_id: int, foreign key. - name: string. What is this variety? (e.g. Size small) No validation. - price: float. - available: boolean, default: True. """ __tablename__ = 'varieties' id = db.Column(db.Integer, primary_key=True) product_id = db.Column(db.Integer, db.ForeignKey('products.id')) name = db.Column(db.String, nullable=False) price = db.Column(db.Float, nullable=True) available = db.Column(db.Boolean, default=True) uploads = db.relationship('Upload', backref='variety', lazy='dynamic') orders = db.relationship('OrderProduct', backref='variety', lazy='dynamic') def __init__(self, name, product_id, price=None, available=True): self.name = name self.product_id = product_id self.price = price self.available = available @validates('price') def validate_price(self, key, pr): if pr is not None and pr < 0: raise DBException({ 'message': 'Variety price cannot be less than zero.', 'code': 'price' }) return pr
class MessageThread(db.Model): """Database model for message threads. Contains: - id: int, auto-incremented. - user1: int, foreign key. - user2: int, foreign key. - title: string. Optional. - order_id: int, foreign key (if about an order). """ __tablename__ = 'message_threads' id = db.Column(db.Integer, primary_key=True) user1 = db.Column(db.Integer, db.ForeignKey('users.id')) user2 = db.Column(db.Integer, db.ForeignKey('users.id')) title = db.Column(db.String, nullable=True) order_id = db.Column(db.Integer, db.ForeignKey('orders.id')) messages = db.relationship('Message', backref='thread', lazy='dynamic') def __init__(self, user1, user2, title=None): self.user1 = user1 self.user2 = user2 self.title = title def isParticipant(self, user): return self.user1 == user or self.user2 == user def otherUser(self, user): if self.isParticipant(user): return self.user2 if self.user1 == user else self.user1 return None
class Country(db.Model): """Database model for all countries. Contains: - id: string, ISO 3166-1 code. - value: string, name. """ __tablename__ = 'countries' id = db.Column(db.String(2), primary_key=True) value = db.Column(db.String, unique=True) addresses = db.relationship('Address', backref='country', lazy='dynamic') def __init__(self, country_id, value): self.id = country_id self.value = value
class Reason(db.Model): """Database model for report reasons. Contains: - id: int, auto-incremented. - text: string. """ __tablename__ = 'reasons' id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String, unique=True) reports = db.relationship('Report', backref='reason', lazy='dynamic') def __init__(self, text): self.text = text
class Admins(db.Model): """Database model for admins. Contains: - id: int, auto-incremented. - user_id: int, foreign key. - date: date. """ __tablename__ = 'admins' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id'), unique=True) def __init__(self, user_id): self.user_id = user_id
class Currency(db.Model): """Database model for all supported currencies. Contains: - id: string, ISO-4217 code. - name: string. - symbol: string. """ __tablename__ = 'currencies' id = db.Column(db.String(3), primary_key=True) name = db.Column(db.String, unique=True) symbol = db.Column(db.String, nullable=True) products = db.relationship('Product', backref='currency', lazy='dynamic') def __init__(self, curr_id, name, symbol=None): self.name = name self.id = curr_id self.symbol = symbol
class ProductCategory(db.Model): """Database model for the relationship between products and categories (many-to-many). Contains: - product_id: int, foreign key. - category_id: int, foreign key. """ __tablename__ = 'product_category' product_id = db.Column(db.Integer, db.ForeignKey('products.id'), primary_key=True) category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), primary_key=True) def __init__(self, product_id, category_id): self.product_id = product_id self.category_id = category_id
class Report(db.Model): """Database model for reports to admins. Contains: - id: int, auto-incremented. - sender_id: int, foreign key. - about_id: int, foreign key. - reason: int, foreign key. - date: date. """ __tablename__ = 'reports' id = db.Column(db.Integer, primary_key=True) sender_id = db.Column(db.Integer, db.ForeignKey('users.id')) about_id = db.Column(db.Integer, db.ForeignKey('users.id')) reason_id = db.Column(db.Integer, db.ForeignKey('reasons.id')) message = db.Column(db.String, nullable=False) date = db.Column(db.DateTime, default=datetime.datetime.utcnow) resolved = db.Column(db.Boolean, default=False) def __init__(self, sender_id, about_id, reason_id, message): self.sender_id = sender_id self.about_id = about_id self.reason_id = reason_id self.message = message def getSender(self): return User.query.filter_by(id=self.sender_id).first() def getAbout(self): return User.query.filter_by(id=self.about_id).first()
class Upload(db.Model): """Database model for product-related uploads (photo, video). Contains: - id: int, auto-increment. - filename: string. - date: upload date. - product_id: int, foreign key. - variety_id: int, foreign key. Optional. - order: int, default: 0 """ __tablename__ = 'uploads' id = db.Column(db.Integer, primary_key=True) product_id = db.Column(db.Integer, db.ForeignKey('products.id')) filename = db.Column(db.String) date = db.Column(db.DateTime, default=datetime.datetime.utcnow) variety_id = db.Column(db.Integer, db.ForeignKey('varieties.id')) order = db.Column(db.Integer, default=0) def __init__(self, filename, product_id, variety_id=None, order=0): self.filename = filename self.product_id = product_id self.variety_id = variety_id self.order = order def url(self): return get_upload_url(self.filename)
class Order(db.Model): """Database model for orders. Contains: - id: int, auto-increment. - user_id: int, foreign key. - create_date: date. - update_date: date. - status: string. - address_id: int, foreign key. """ __tablename__ = 'orders' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) update_date = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) status = db.Column(db.String, default='New') address_id = db.Column(db.Integer, db.ForeignKey('addresses.id')) products = db.relationship('Product', secondary='order_product',\ back_populates='orders', lazy='dynamic') message_threads = db.relationship('MessageThread', backref='order', lazy='dynamic') def __init__(self, user_id): self.user_id = user_id
class Message(db.Model): """Database model for messages in a thread. Contains: - id: int, auto-incremented. - thread_id: int, foreign key. - sender_id: int, foreign key. No need for receiver because thread has info. - date: date. - text: string. - seen: date. """ __tablename__ = 'messages' id = db.Column(db.Integer, primary_key=True) thread_id = db.Column(db.Integer, db.ForeignKey('message_threads.id')) sender_id = db.Column(db.Integer, db.ForeignKey('users.id')) text = db.Column(db.String, nullable=False) date = db.Column(db.DateTime, default=datetime.datetime.utcnow) seen = db.Column(db.DateTime) def __init__(self, thread_id, sender_id, text): self.thread_id = thread_id self.sender_id = sender_id self.text = text def see(self, userId): thread = self.thread.first() if userId != self.sender_id and (thread.user1 == userId or thread.user2 == userId): self.seen = datetime.datetime.utcnow()
class Category(db.Model): """Database model for categories. Contains: - id: int, auto-incremented. - name: string. """ __tablename__ = 'categories' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False, unique=True) products = db.relationship('Product', secondary='product_category',\ back_populates='categories', lazy='dynamic') def __init__(self, name): self.name = name @validates('name') def validate_name(self, key, name_input): """Makes sure the name doesn't have any numbers or special chars. Raises a DBException otherwise. """ validate_name_pattern(name_input, optional=False) return name_input
class Address(db.Model): """Database model for addresses (physical). Contains: - id: int, auto-incremented. - name: name to assign to this address (every user can have multiple addresses). - user_id: int, foreign key. - country_id: string, ISO 3166-1 code, foreign key. - city: string. - zip/postal code: string. - phone: string. - text: string. """ __tablename__ = 'addresses' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, default='Default') user_id = db.Column(db.Integer, db.ForeignKey('users.id')) country_id = db.Column(db.String(2), db.ForeignKey('countries.id')) code = db.Column(db.String) _phone = db.Column(db.String) text = db.Column(db.String, nullable=False) orders = db.relationship('Order', backref='address', lazy='dynamic') def __init__(self, name, user_id, text, country_id, code=None, phone=None): self.name = name self.user_id = user_id self.country_id = country_id self.code = code self.phone = phone self.text = text @property def phone(self): return self._phone @phone.setter def phone(self, value): pattern = re.compile( '[^\d\+x]') # all but digits, +, and x (for extensions) stripped = re.sub(pattern, '', value) self._phone = stripped @validates('name') def validate_name(self, key, name_input): """Makes sure the name doesn't have any numbers or special chars. Raises a DBException otherwise. """ validate_name_pattern(name_input, allowNumbers=True) return name_input
class Review(db.Model): """Database model for reviews on products. Contains: - id: int, auto-increment. - user_id: int, foreign key. - product_id: int, foreign key. - rating: float. - title: string. - text: string. - create_date: review date. - update_date: update date. """ __tablename__ = 'reviews' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) product_id = db.Column(db.Integer, db.ForeignKey('products.id')) rating = db.Column(db.Float, nullable=False) title = db.Column(db.String) text = db.Column(db.String) create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) update_date = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) def __init__(self, user_id, product_id, rating, title=None, text=None): self.user_id = user_id self.product_id = product_id self.rating = rating self.title = title self.text = text @validates('rating') def validate_rating(self, key, r): """Makes sure the rating is between 1 and 5 with 0.5 increments only.""" half_or_full = r % 1 == 0 or r % 1 == 0.5 if r < 1 or r > 5 or not half_or_full: raise DBException({'message': 'Rating must be between 1 and 5, with 0.5 increments only.',\ 'code': 'rating'}) return r
class OrderProduct(db.Model): """Database model for relationship between orders and models (many-to-many). Contains: - id: int, auto-incremented. - order_id: int, foreign key. - product_id: int, foreign key. - variety_id: int, foreign key. - quantity: int. - create_date: date. - update_date: date. """ __tablename__ = 'order_product' id = db.Column(db.Integer, primary_key=True) order_id = db.Column(db.Integer, db.ForeignKey('orders.id')) product_id = db.Column(db.Integer, db.ForeignKey('products.id')) variety_id = db.Column(db.Integer, db.ForeignKey('varieties.id')) quantity = db.Column(db.Integer, nullable=False, default=0) create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) update_date = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) def __init__(self, order_id, product_id, variety_id, quantity=0): self.order_id = order_id self.product_id = product_id self.variety_id = variety_id self.quantity = quantity @validates('quantity') def validate_quantity(self, key, q): """Validate that quantity is more than 0.""" if q < 0: raise DBException({ 'message': 'Quantity cannot be less than zero.', 'code': 'quantity' }) return q
class User(db.Model): """Database model for users. Contains: - id: int, auto-incremented. - username: string. - name: string. Optional. - email: string. - password: string. """ __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String, unique=True, nullable=False) password = db.Column(db.String(157), nullable=False) # 128 + salt + algo info email = db.Column(db.String, unique=True, nullable=False) name = db.Column(db.String) picture = db.Column(db.String) location = db.Column(db.String) about = db.Column(db.String(200)) disabled = db.Column(db.Boolean, unique=False, default=False) suspended = db.Column(db.Boolean, unique=False, default=False) addresses = db.relationship('Address', backref='user', lazy='dynamic',\ cascade='save-update, merge, delete') products = db.relationship('Product', backref='seller', lazy='dynamic',\ cascade='save-update, merge, delete') reviews = db.relationship('Review', backref='user', lazy='dynamic',\ cascade='save-update, merge, delete') orders = db.relationship('Order', backref='user', lazy='dynamic',\ cascade='save-update, merge, delete') def __init__(self, username, email, password, name=None, location=None): """Initialize a User using the required fields: username, email, password.""" self.username = username self.email = email self.password = password self.name = name self.location = location def isPassword(self, pw): return check_password_hash(self.password, pw) def name_or_username(self): if self.name: return self.name return self.username def bought(self, productID): return self.orders.join(OrderProduct).join(Product).filter( Product.id == productID).count() > 0 @validates('password') def validate_password(self, key, pw): if len(pw) < 6: raise DBException({'message': 'Password cannot be less than 6 characters long.',\ 'code': 'password'}) return generate_password_hash(pw, method='pbkdf2:sha512:10000') @validates('name') def validate_name(self, key, name_input): """Validate that the name contains anything but numbers and special characters. Raises a DBException if invalid. """ validate_name_pattern(name_input) return name_input @validates('location') def validate_location(self, key, loc): """Validate that the location contains anything but special characters. Raises a DBException if invalid. """ if not no_special_chars(loc, allowNumbers=True, allowComma=True): raise DBException({'message': 'Location cannot contain special characters.',\ 'code': 'location'}) return loc @validates('email') def validate_email(self, key, em): """Validate that the email has an @ and characters before and after it. Raises a DBException if invalid. """ w3c_pattern = re.compile( '^[a-zA-Z0-9.!#$%&’*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$' ) if not w3c_pattern.match(em): raise DBException({ 'message': 'Email must be valid.', 'code': 'email' }) return em.lower() @validates('username') def validate_username(self, key, usr): """Validates that the username begins with a letter, is at least 2 chars long, and can only ever contain letters, numbers, or underscores. Raises a DBException if invalid. """ pattern = re.compile('^[a-zA-Z][\w]+$') if not pattern.match(usr): raise DBException({ 'message': 'Username must be 2 characters (number, letter, or underscore) long, and begin with a letter.' }) return usr.lower()
class Product(db.Model): """Database model for products. Contains: - id: int, auto-incremented. - name: string. - seller_id: int, foreign key. - update_date: date. - create_date: date. - description: string. - price: float. - available: boolean, default: True. """ __tablename__ = 'products' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) seller_id = db.Column(db.Integer, db.ForeignKey('users.id')) create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow) update_date = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow) description = db.Column(db.String) currency_id = db.Column(db.String(3), db.ForeignKey('currencies.id')) price = db.Column(db.Float) categories = db.relationship('Category', secondary='product_category',\ back_populates='products', lazy='dynamic') varieties = db.relationship('Variety', backref='product', lazy='dynamic',\ cascade='save-update, merge, delete') uploads = db.relationship('Upload', backref='product', lazy='dynamic',\ cascade='save-update, merge, delete') reviews = db.relationship('Review', backref='product', lazy='dynamic',\ cascade='save-update, merge, delete') orders = db.relationship('Order', secondary='order_product',\ back_populates='products', lazy='dynamic') available = db.Column(db.Boolean, default=True, nullable=False) force_unavailable = db.Column(db.Boolean, default=False, nullable=False) def __init__(self, name, seller_id, description=None, price=None, currency_id=None): self.name = name self.seller_id = seller_id self.description = description self.price = price self.currency_id = currency_id def random_picture(self): return get_upload_url( self.uploads.order_by(func.random()).first().filename) def avg_rating(self): return db.session.query(func.avg(Review.rating).label('average')).\ join(Product).filter(Product.id == self.id).first()[0] @validates('price') def validate_price(self, key, p): """Makes sure the price is not less than 0.""" if p is not None and p < 0: raise DBException({ 'message': 'Default price cannot be less than zero.', 'code': 'price' }) return p