class Base(db.Model): __abstract__ = True created_at = db.Column(db.DateTime(timezone=True), default=datetime.utcnow) updated_at = db.Column(db.DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)
class RatingModel(db.Model): __tablename__ = "ratings" id = db.Column(db.Integer, primary_key=True) comment_id = db.Column(db.Integer, db.ForeignKey("comments.id")) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) rating = db.Column(db.Integer, nullable=False) def __init__(self, comment_id, user_id, rating): self.comment_id = comment_id self.user_id = user_id self.rating = rating def save_to_db(self): db.session.add(self) db.session.commit() @classmethod def does_exist(cls, user_id, comment_id): rating = cls.query.filter_by(user_id=user_id, comment_id=comment_id) return rating.count() > 0 @classmethod def find_by_comment_id_user_id(cls, comment_id, user_id): return cls.query.filter_by(comment_id=comment_id, user_id=user_id).first()
class User(Base): __tablename__ = 'users' __table_args__ = dict(extend_existing=True) id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String(50), index=True, nullable=False, unique=True) name = db.Column(db.String(80), nullable=False)
class AddressModel(db.Model): __tablename__ = "addresses" id = db.Column(db.Integer, primary_key=True) is_deleted = db.Column(db.Integer) line1 = db.Column(db.String(80)) line2 = db.Column(db.String(80)) city = db.Column(db.String(80)) state = db.Column(db.String(80)) zip = db.Column(db.String(10)) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) company_id = db.Column(db.Integer, db.ForeignKey("companies.id")) # company = db.relationship("CompanyModel") user_id = db.Column(db.Integer, db.ForeignKey("users.id")) def __init__(self, line1, line2, city, state, zip, company_id, user_id): self.line1 = line1 self.line2 = line2 self.city = city self.state = state self.zip = zip self.company_id = company_id self.user_id = user_id self.is_deleted = 0 def json(self): return { "id": self.id, "line1": self.line1, "line2": self.line2, "city": self.city, "state": self.state, "zip": self.zip, "company_id": self.company_id, "user_id": self.user_id, "is_deleted": self.is_deleted } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_all(cls): return cls.query.filter_by(is_deleted=0) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class User(db.Model): id = db.Column(db.Integer, primary_key=True) nickname = db.Column(db.String(64), nullable=False, unique=True) email = db.Column(db.String(120), nullable=True) password = db.Column(db.String(64)) def get_id(self): try: return unicode(self.id) except NameError: return str(self.id) def __repr__(self): return '<User %r>' % (self.nickname)
class Product(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), unique=True, nullable=False) descr = db.Column(db.String(200)) price = db.Column(db.Float, nullable=False) qty = db.Column(db.Integer, nullable=False) def __init__(self, name, descr, price, qty): self.name = name self.descr = descr self.price = price self.qty = qty def __repr__(self): return f"Product('{self.id}', '{self.name}')"
class RevokedTokenModel(db.Model): __tablename__ = "revoked_tokens" id = db.Column(db.Integer, primary_key=True) jti = db.Column(db.String(128)) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) def __init__(self, jti): self.jti = jti def save_to_db(self): db.session.add(self) db.session.commit() @classmethod def is_jti_blacklisted(cls, jti): jti = cls.query.filter_by(jti=jti).first() return jti
class AttachmentModel(db.Model): __tablename__ = "attachments" id = db.Column(db.Integer, primary_key=True) attachment_name = db.Column(db.String(200), nullable=False, unique=True) # need inverse relation so order retrieves all its tracking logs track_log_id = db.Column(db.Integer, db.ForeignKey("tracking_logs.id")) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) is_deleted = db.Column(db.Integer, default=0) def __init__(self, attachment_name, track_log_id): self.attachment_name = attachment_name self.track_log_id = track_log_id def json(self): return { "id": self.id, "attachment_name": self.attachment_name, "track_log_id": self.track_log_id } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_track_log_id(cls, track_log_id): return cls.query.filter_by(track_log_id=track_log_id) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class TrackingModel(db.Model): __tablename__ = "tracking_logs" id = db.Column(db.Integer, primary_key=True) message = db.Column(db.String(200)) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) # need inverse relation so order retrieves all its tracking logs order_id = db.Column(db.Integer, db.ForeignKey("orders.id")) # staff who summited the tracking log # no need for inverse relation staff_id = db.Column(db.Integer, db.ForeignKey("staffs.id")) staff = db.relationship("StaffModel") # user comments, coresponds to an existing staff tracking log # no need for inverse relation user_id = db.Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship("UserModel") is_deleted = db.Column(db.Integer) attachments = db.relationship("AttachmentModel") def __init__(self, message, order_id, staff_id, user_id): self.message = message self.order_id = order_id self.staff_id = staff_id self.user_id = user_id self.is_deleted = 0 def json(self): return { "id": self.id, "message": self.message, "order_id": self.order_id, "staff_id": self.staff_id, "user_id": self.user_id, "is_deleted": self.is_deleted } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_order_id(cls, order_id): return cls.query.filter_by(order_id=order_id) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class UserModel(db.Model): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True) status = db.Column(db.String(10), default="ACTIVE") is_deleted = db.Column(db.Integer, default=0) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) role = db.Column(db.String(15), nullable=False) username = db.Column(db.String(25), unique=True, nullable=False) password_hash = db.Column(db.String(128), nullable=False) email = db.Column(db.String, nullable=False, unique=True) profile_img = db.Column(db.String, nullable=False) comments = db.relationship("CommentModel", lazy="dynamic") def __init__(self, role, username, password_hash, email, profile_img): # role = (admin, guest, user) self.role = role # username = (admin, guest, any user name) self.username = username self.password_hash = password_hash self.email = email self.profile_img = profile_img @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_username(cls, username): return cls.query.filter_by(username=username, is_deleted=0).first() @classmethod def find_by_email(cls, email): return cls.query.filter_by(email=email, is_deleted=0).first() def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class VideoModel(db.Model): __tablename__ = "videos" id = db.Column(db.Integer, primary_key=True) vid = db.Column(db.String, nullable=False) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) is_deleted = db.Column(db.Integer, nullable=False, default=0) title = db.Column(db.String, nullable=False) author = db.Column(db.String, nullable=False) description = db.Column(db.String) video_date = db.Column(db.String) def __init__(self, vid, title, author, description, video_date): self.vid = vid self.title = title self.author = author self.description = description self.video_date = video_date @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_vid(cls, vid): return cls.query.filter_by(vid=vid, is_deleted=0).first() @classmethod def find_by_author(cls, author): return cls.query.filter_by(author=author, is_deleted=0) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class Books(db.Model): __tablename__ = 'books' id = db.Column(db.Integer, primary_key=True, autoincrement=True) name = db.Column(db.String(64), unique=True, nullable=False) category = db.Column(db.String(64)) price = db.Column(db.Float) user_id = db.Column(db.Integer) status = db.Column(db.Enum('0', '1'), default='1') def to_dict(self): return { 'name': self.name, 'category': self.category, 'price': self.price, }
class CompanyModel(db.Model): __tablename__ = "companies" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) email = db.Column(db.String(80)) phone = db.Column(db.String(20)) is_deleted = db.Column(db.Integer) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) staffs = db.relationship("StaffModel", back_populates="company") addresses = db.relationship("AddressModel", lazy="dynamic") def __init__(self, name, email, phone): self.name = name self.email = email self.phone = phone self.is_deleted = 0 def json(self): return { "id": self.id, "name": self.name, "email": self.email, "phone": self.phone, "is_deleted": self.is_deleted, "addresses": [address.json() for address in self.addresses.all()] } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name, is_deleted=0).first() @classmethod def find_by_email(cls, email): return cls.query.filter_by(email=email, is_deleted=0).first() @classmethod def find_all(cls): return cls.query.filter_by(is_deleted=0).order_by(CompanyModel.name) def get_active_addresses(self): addresses = self.addresses.all() active_addresses = filter(lambda address: address.is_deleted == 0, addresses) return active_addresses def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class Load(db.Model): __tablename__ = "load" #__table_args__ = {'extend_existing': True} #__table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True, autoincrement=True)
class FireModel(db.Model): __tablename__ = "firms" id = db.Column(db.Integer, primary_key=True) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) latitude = db.Column(db.Float) longitude = db.Column(db.Float) bright_ti4 = db.Column(db.Float) scan = db.Column(db.Float) track = db.Column(db.Float) acq_datetime = db.Column(db.String) satellite = db.Column(db.String) confidence = db.Column(db.String) version = db.Column(db.String) bright_ti5 = db.Column(db.Float) frp = db.Column(db.Float) daynight = db.Column(db.String) def __init__(self, latitude, longitude, bright_ti4, scan, track, acq_datetime, satellite, confidence, version, bright_ti5, frp, daynight): self.latitude = latitude self.longitude = longitude self.bright_ti4 = bright_ti4 self.scan = scan self.track = track self.acq_datetime = acq_datetime self.satellite = satellite self.confidence = confidence self.version = version self.bright_ti5 = bright_ti5 self.frp = frp self.daynight = daynight # find a list of locations for the given position @classmethod def find_by_location(cls, latitude, longitude): pass @classmethod def find_by_datetime(cls, datetime): pass 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(db.Model, UserMixin): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True) password_hash = db.Column(db.String(256)) name = db.Column(db.String(50)) email = db.Column(db.String(50)) phone = db.Column(db.String(20)) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) orders = db.relationship("OrderModel", lazy="dynamic") is_deleted = db.Column(db.Integer) def __init__(self, hashed_password, name, email, phone): self.password_hash = hashed_password self.name = name self.email = email self.phone = phone self.is_deleted = 0 def json(self): return { "id": self.id, "name": self.name, "email": self.email, "phone": self.phone, "orders": [order.json() for order in self.orders.all()], "is_deleted": self.is_deleted } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name, is_deleted=0).first() @classmethod def find_by_email(cls, email): return cls.query.filter_by(email=email, is_deleted=0).first() @classmethod def find_all(cls): return cls.query.filter_by(is_deleted=0).order_by(cls.name) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit() ###################################################################### #### overrides UserMixin. adding "user_" identifier to distinguish #### from staffs during @flask_login.user_loader callback. ########### def get_id(self): try: return "user_" + str(self.id) except AttributeError: raise NotImplementedError('No `id` attribute - override `get_id`')
class Person(db.Model): __tablename__ = "person" #__table_args__ = {'extend_existing': True} #__table_args__ = {'extend_existing': True} id = db.Column(db.Integer, primary_key=True, autoincrement=True) load_id = db.Column(db.Integer, db.ForeignKey("load.id", ondelete="CASCADE"), nullable=False) load = db.relationship('Load', backref=db.backref('load')) #lazy='joined' citizen_id = db.Column(db.Integer, index=True, nullable=False) town = db.Column(db.String(256), index=True, nullable=False) street = db.Column(db.String(120), nullable=False) building = db.Column(db.String(120), nullable=False) apartment = db.Column(db.Integer, nullable=False) name = db.Column(db.String(128), nullable=False) birth_date = db.Column(db.Date, nullable=False) gender = db.Column(db.String(6), nullable=False) relatives = db.Column(ARRAY(db.Integer)) @staticmethod def all_fields(): return [ 'citizen_id', 'town', 'street', 'building', 'apartment', 'name', 'birth_date', 'gender', 'relatives' ] # return ['id', 'load_id', 'citizen_id', 'town', 'street', # 'building', 'apartment', 'name', 'birth_date', 'gender', 'relatives'] def __repr__(self): return f"id={self.id},\n" \ f"load_id={self.load_id},\n" \ f"citizen_id={self.citizen_id},\n" \ f"town={self.town},\n" \ f"street={self.street},\n" \ f"building={self.building},\n" \ f"apartment={self.apartment},\n" \ f"name={self.name},\n" \ f"birth_date={self.birth_date},\n" \ f"gender={self.gender},\n" \ f"relatives={self.relatives},\n"
class CommentModel(db.Model): __tablename__ = "comments" id = db.Column(db.Integer, primary_key=True) date = db.Column(db.DateTime, nullable=False) text = db.Column(db.String) ratings = db.relationship("RatingModel") # a comment has either (parent_comment_id and top_comment_id) or a single vid top_comment_id = db.Column(db.Integer, nullable=False) parent_comment_id = db.Column(db.Integer, nullable=False) vid = db.Column(db.String) user_id = db.Column(db.Integer, db.ForeignKey("users.id")) user = db.relationship("UserModel") is_deleted = db.Column(db.Integer, default=0) edit_date = db.Column(db.DateTime, nullable=False) def __init__(self, text, user_id, **kwargs): self.text = text self.user_id = user_id date = datetime.utcnow() self.date = date self.edit_date = date if 'parent_comment_id' in kwargs.keys(): self.parent_comment_id = kwargs['parent_comment_id'] else: self.parent_comment_id = 0 if 'top_comment_id' in kwargs.keys(): self.top_comment_id = kwargs['top_comment_id'] else: self.top_comment_id = 0 if 'vid' in kwargs.keys(): self.vid = kwargs['vid'] else: self.vid = 0 # jason representation of each comment def to_json(self, user_id): ratings_count = [rating.rating for rating in self.ratings] user_ids_ratings = dict( (rating.user_id, rating.rating) for rating in self.ratings) voted = 0 if user_id in user_ids_ratings: voted = user_ids_ratings[user_id] return { "id": self.id, "date": str(self.date), "edit_date": str(self.edit_date), "is_deleted": self.is_deleted, "text": self.text if self.is_deleted == 0 else "", "user_id": self.user_id, "username": self.user.username, "profile_img": self.user.profile_img, "top_comment_id": self.top_comment_id, "parent_comment_id": self.parent_comment_id, "like": ratings_count.count(1), "dislike": ratings_count.count(-1), "count": CommentModel.query.filter_by(top_comment_id=self.id, is_deleted=0).count(), "voted": voted } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id).first() @classmethod def find_all_by_vid(cls, vid): return cls.query.filter_by(vid=vid).order_by(cls.date.desc()) @classmethod def find_all_by_parent_comment_id(cls, parent_comment_id): return cls.query.filter_by( parent_comment_id=parent_comment_id).order_by(cls.date.desc()) @classmethod def find_all_by_top_comment_id(cls, top_comment_id): return cls.query.filter_by(top_comment_id=top_comment_id).order_by( cls.date.desc()) @classmethod def find_all_by_user_id(cls, user_id): return cls.query.filter_by(user_id=user_id).order_by(cls.date.desc()) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 # db.session.add(self) db.session.commit()
class StaffModel(db.Model, UserMixin): __tablename__ = "staffs" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(50)) role = db.Column(db.String(15)) password_hash = db.Column (db.String(256)) company_id = db.Column(db.Integer, db.ForeignKey("companies.id")) company = db.relationship("CompanyModel", back_populates="staffs", uselist=False) orders = db.relationship("OrderModel", back_populates="staff") date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) is_deleted = db.Column(db.Integer) def __init__(self, name, role,password_hash,company_id): self.name =name self.role = role self.password_hash = password_hash self.company_id=company_id self.is_deleted = 0 def json(self): if not self.company: ret_msg = { "id": self.id, "name": self.name, "role": self.role, "is_deleted":self.is_deleted } else: ret_msg = { "id": self.id, "name": self.name, "role": self.role, "company": self.company.json(), "is_deleted":self.is_deleted } return ret_msg,200 @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name, is_deleted=0).first() @classmethod def find_all(cls): return cls.query.filter_by(is_deleted=0).order_by(cls.company_id) @classmethod def find_by_company_id(cls, company_id): return cls.query.filter_by(company_id=company_id, is_deleted=0).order_by(cls.name) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted=1 db.session.add(self) db.session.commit() ############################ #### overrides UserMixin. adding "staff_" identifier to distinguish #### from users during @flask_login.user_loader callback. ########### def get_id(self): try: return "staff_"+str(self.id) except AttributeError: raise NotImplementedError('No `id` attribute - override `get_id`')
class OrderModel(db.Model): __tablename__ = "orders" id = db.Column(db.Integer, primary_key=True) ur_code = db.Column(db.String(100)) # name of the ordered service name = db.Column(db.String(80)) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) staff_id = db.Column(db.Integer, db.ForeignKey("staffs.id")) # filled in when user registers new account and add this order in account user_id = db.Column(db.Integer, db.ForeignKey("users.id")) staff = db.relationship("StaffModel", back_populates="orders", uselist=False) user = db.relationship("UserModel") company = db.relationship( CompanyModel, secondary= "join(StaffModel, CompanyModel, StaffModel.company_id==CompanyModel.id)", primaryjoin=staff_id == StaffModel.id, uselist=False, backref="orders") is_deleted = db.Column(db.Integer) tracking_logs = db.relationship("TrackingModel", lazy="dynamic") def __init__(self, ur_code, name, staff_id): self.ur_code = ur_code self.name = name self.staff_id = staff_id # user_id = 1 is unregistered user, updates when user registers account self.user_id = 1 self.is_deleted = 0 def json(self): return { "id": self.id, "ur_code": self.ur_code, "name": self.name, "staff_id": self.staff_id, "user_id": self.user_id, "tracking_logs": [log.json() for log in self.tracking_logs.all()], "is_deleted": self.is_deleted } @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id, is_deleted=0).first() @classmethod def find_by_ur_code(cls, ur_code): return cls.query.filter_by(ur_code=ur_code, is_deleted=0).first() @classmethod def find_by_staff_id(cls, staff_id): return db.session.query(OrderModel).filter_by(staff_id=staff_id, is_deleted=0).order_by( cls.date) @classmethod def find_by_user_id(cls, user_id): return cls.query.filter_by(user_id=user_id, is_deleted=0).order_by(cls.date) @classmethod def find_by_company(cls, company): return cls.query.filter(cls.company.has(id=company.id)).order_by( cls.date) @classmethod def find_by_company_id(cls, company_id): return cls.query.filter(cls.company.has(id=company_id)).order_by( cls.date) @classmethod def find_all(cls): return cls.query.filter_by(is_deleted=0).order_by(cls.date) def save_to_db(self): db.session.add(self) db.session.commit() def delete_from_db(self): self.is_deleted = 1 db.session.add(self) db.session.commit()
class User(db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True, autoincrement=True) mobile = db.Column(db.String(16), unique=True, nullable=False) nickname = db.Column(db.String(64), unique=True) password = db.Column(db.String(128), nullable=False)
class FireModel(db.Model): __tablename__="firms" id = db.Column(db.Integer, primary_key=True) date = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) latitude = db.Column(db.Float) longitude = db.Column(db.Float) bright_ti4 = db.Column(db.Float) scan = db.Column(db.Float) track = db.Column(db.Float) acq_datetime = db.Column(db.String) satellite = db.Column(db.String) confidence = db.Column(db.String) version = db.Column(db.String) bright_ti5 = db.Column(db.Float) frp = db.Column(db.Float) daynight = db.Column(db.String) def __init__( self, latitude, longitude, bright_ti4, scan, track, acq_datetime, satellite, confidence, version, bright_ti5, frp, daynight ): self.latitude = latitude self.longitude = longitude self.bright_ti4 = bright_ti4 self.scan= scan self.track = track self.acq_datetime = acq_datetime self.satellite = satellite self.confidence = confidence self.version = version self.bright_ti5 = bright_ti5 self.frp=frp self.daynight = daynight @classmethod def find_by_datetime(cls, datetime): pass def json(self): return { "latitude": self.latitude, "longitude": self.longitude, "bright_ti4": self.bright_ti4, "scan":self.scan, "track":self.track, "acq_datetime":self.acq_datetime, "satellite":self.satellite, "confidence":self.confidence, "version":self.version, "bright_ti5":self.bright_ti5, "frp":self.frp, "daynight":self.daynight } def save_to_db (self): db.session.add(self) db.session.commit() def delete_from_db(self): db.session.delete(self) db.session.commit() @classmethod def delete_all(cls): cls.query.delete() @classmethod def find_all(cls): return cls.query.all() # find a list of locations for the given position @classmethod def find_by_location(cls, latitude_min, latitude_max, longitude_min, longitude_max): return cls.query.filter( cls.latitude >= latitude_min, cls.latitude<=latitude_max, cls.longitude>=longitude_min, cls.longitude<=longitude_max ).first()