class Application(db.Model):
__tablename__ = 'applications'
application_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.user_id'),
nullable=False,
index=True)
application_type = db.Column(db.Enum('open', 'cancel'), nullable=False)
shop_name = db.Column(db.String(32), nullable=False)
shop_info = db.Column(db.String(256), nullable=False, default='')
shop_contact = db.Column(db.String(32), nullable=False)
shop_location = db.Column(db.Enum(
'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
'Zhi Xin Building', 'Research A', 'Research B', 'Teaching A',
'Teaching B', 'Teaching C', 'Teaching D'),
nullable=False)
shop_location_detail = db.Column(db.String(256),
nullable=False,
default='')
shop_license_number = db.Column(db.String(32), nullable=False)
application_status = db.Column(db.Enum('pending', 'approved', 'denied'),
nullable=False)
class Administrator(db.Model):
__tablename__ = 'administrators'
administrator_name = db.Column(db.String(32),
primary_key=True,
nullable=False,
unique=True,
index=True)
administrator_password = db.Column(db.String(64), nullable=False)
class Purchased_Product(db.Model):
__tablename__ = 'purchased_products'
product_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
order_id = db.Column(db.Integer, db.ForeignKey('orders.order_id'), nullable=False, index=True)
product_name = db.Column(db.String(32))
product_price = db.Column(db.Float)
quantity = db.Column(db.Integer)
order = db.relationship('Order', back_populates='purchased_products') # order and purchased products
class Bookmark(db.Model):
__tablename__ = 'bookmarks'
user_id = db.Column(db.Integer,
db.ForeignKey('users.user_id'),
primary_key=True,
nullable=False,
index=True)
shop_id = db.Column(db.Integer,
db.ForeignKey('shops.shop_id'),
primary_key=True,
nullable=False,
index=True)
class Order(db.Model):
__tablename__ = 'orders'
order_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
shop_id = db.Column(db.Integer,
db.ForeignKey('shops.shop_id'),
nullable=False,
index=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.user_id'),
nullable=False,
index=True)
user_contact = db.Column(db.String(32), nullable=False)
user_location = db.Column(db.Enum(
'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
'Zhi Xin Building', 'Cheng Dao Building', 'Dao Yuan Building',
'Li Wen Building', 'Qi Xian Building', 'Sports Hall', 'Research A',
'Research B', 'Teaching A', 'Teaching B', 'Teaching C', 'Teaching D',
'Administration Building', 'Staff Residence 1', 'Staff Residence 2',
'Staff Residence 3', 'Staff Residence 4'),
nullable=False)
delivery_fee = db.Column(db.Integer, nullable=False, default=0)
create_time = db.Column(db.DateTime,
nullable=False,
default=datetime.utcnow)
order_status = db.Column(db.Enum('pending', 'approved', 'denied',
'delivering', 'finished', 'cancelled'),
nullable=False)
class Application(db.Model):
__tablename__ = 'applications'
application_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
application_type = db.Column(db.Enum('open', 'cancel'))
shop_name = db.Column(db.String(32))
shop_info = db.Column(db.String(256))
shop_contact = db.Column(db.String(32))
shop_location = db.Column(db.String(32))
shop_location_detail = db.Column(db.String(256))
shop_license_number = db.Column(db.String(32))
application_status = db.Column(db.Enum('pending', 'approved', 'denied'))
user = db.relationship('User', back_populates='applications') # user and applications
class Purchased_Product(db.Model):
__tablename__ = 'purchased_products'
product_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
order_id = db.Column(db.Integer,
db.ForeignKey('orders.order_id'),
nullable=False,
index=True)
product_name = db.Column(db.String(32), nullable=False)
product_price = db.Column(db.Float, nullable=False)
quantity = db.Column(db.Integer, nullable=False, default=1)
class User(db.Model):
__tablename__ = 'users'
user_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
email = db.Column(db.String(64), nullable=False, unique=True)
user_password = db.Column(db.String(64))
user_name = db.Column(db.String(32))
user_avatar = db.Column(db.LargeBinary)
user_contact = db.Column(db.String(32))
available_balance = db.Column(db.Float)
frozen_balance = db.Column(db.Float)
user_status = db.Column(db.Enum('normal', 'blocked'))
shops = db.relationship('Shop', back_populates='user', cascade='all, delete-orphan') # user and shops
orders = db.relationship('Order', back_populates='user', cascade='all, delete-orphan') # user and orders
applications = db.relationship('Application', back_populates='user', cascade='all, delete-orphan') # user and applications
bookmarked_shops = db.relationship('Shop', secondary='bookmarks', back_populates='bookmarked_by_users') # users and shops which they bookmarked
class Order(db.Model):
__tablename__ = 'orders'
order_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
shop_id = db.Column(db.Integer, db.ForeignKey('shops.shop_id'), nullable=False, index=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
user_location = db.Column(db.String(32))
phone_number = db.Column(db.String(32))
delivery_fee = db.Column(db.Integer)
create_time = db.Column(db.DateTime, default=datetime.utcnow)
order_status = db.Column(db.Enum('pending', 'approved', 'denied', 'delivering', 'finished', 'cancelled'))
user = db.relationship('User', back_populates='orders') # user and orders
shop = db.relationship('Shop', back_populates='orders') # shop and orders
purchased_products = db.relationship('Purchased_Product', back_populates='order', cascade='all, delete-orphan') # order and purchased products
class Product(db.Model):
__tablename__ = 'products'
product_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
shop_id = db.Column(db.Integer, db.ForeignKey('shops.shop_id'), nullable=False, index=True)
product_name = db.Column(db.String(32), index=True)
product_avatar = db.Column(db.LargeBinary)
product_info = db.Column(db.String(256))
product_price = db.Column(db.Float)
total_sale = db.Column(db.Integer)
shop = db.relationship('Shop', back_populates='products') # shop and products
class User(db.Model):
__tablename__ = 'users'
user_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
email = db.Column(db.String(64), nullable=False, unique=True)
user_password = db.Column(db.String(64), nullable=False)
user_name = db.Column(db.String(32),
nullable=False,
default='Unnamed User')
user_avatar = db.Column(db.LargeBinary, default=None)
user_contact = db.Column(db.String(32), nullable=False, default='')
available_balance = db.Column(db.Float, nullable=False)
frozen_balance = db.Column(db.Float, nullable=False)
user_status = db.Column(db.Enum('normal', 'blocked'),
nullable=False,
default='normal')
class Product(db.Model):
__tablename__ = 'products'
product_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
shop_id = db.Column(db.Integer,
db.ForeignKey('shops.shop_id'),
nullable=False,
index=True)
product_name = db.Column(db.String(32), nullable=False, index=True)
product_avatar = db.Column(db.LargeBinary, default=None)
product_info = db.Column(db.String(256), nullable=False, default='')
product_price = db.Column(db.Float, nullable=False)
total_sale = db.Column(db.Integer, nullable=False)
class Shop(db.Model):
__tablename__ = 'shops'
shop_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True,
nullable=False,
unique=True,
index=True)
user_id = db.Column(db.Integer,
db.ForeignKey('users.user_id'),
nullable=False,
index=True)
shop_name = db.Column(db.String(32), nullable=False, index=True)
shop_avatar = db.Column(db.LargeBinary, default=None)
shop_info = db.Column(db.String(256), nullable=False, default='')
shop_delivery_fee = db.Column(db.Integer, nullable=False, default=0)
shop_rate_total = db.Column(db.Integer, nullable=False)
shop_rate_number = db.Column(db.Integer, nullable=False)
shop_balance = db.Column(db.Float, nullable=False)
shop_contact = db.Column(db.String(32), nullable=False)
shop_location = db.Column(db.Enum(
'Student Center', 'Shaw College', 'Muse College', 'Deligentia College',
'Harmonia College', 'Le Tian Building', 'Zhi Ren Building',
'Zhi Xin Building', 'Research A', 'Research B', 'Teaching A',
'Teaching B', 'Teaching C', 'Teaching D'),
nullable=False)
shop_location_detail = db.Column(db.String(256),
nullable=False,
default='')
shop_license_number = db.Column(db.String(32), nullable=False)
shop_status = db.Column(db.Enum('open', 'closed', 'blocked', 'cancelled'),
nullable=False)
class Shop(db.Model):
__tablename__ = 'shops'
shop_id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, unique=True, index=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'), nullable=False, index=True)
shop_name = db.Column(db.String(32), index=True)
shop_avatar = db.Column(db.LargeBinary)
shop_info = db.Column(db.String(256))
shop_delivery_fee = db.Column(db.Integer)
shop_rate_total = db.Column(db.Integer)
shop_rate_number = db.Column(db.Integer)
shop_balance = db.Column(db.Float)
shop_contact = db.Column(db.String(32))
shop_location = db.Column(db.String(32))
shop_location_detail = db.Column(db.String(256))
shop_license_number = db.Column(db.String(32))
shop_status = db.Column(db.Enum('open', 'closed', 'blocked', 'cancelled'))
user = db.relationship('User', back_populates='shops') # user and shops
products = db.relationship('Product', back_populates='shop', cascade='all, delete-orphan') # shop and products
orders = db.relationship('Order', back_populates='shop', cascade='all, delete-orphan') # shop and orders
bookmarked_by_users = db.relationship('User', secondary='bookmarks', back_populates='bookmarked_shops') # shops and users whom they bookmarked by
