class Customer(db.Model): __tablename__ = 'customers' __table_args__ = (db.UniqueConstraint('psid', 'id', name='unique_customer_orders'), ) id = db.Column(db.Integer, primary_key=True) psid = db.Column(db.String, unique=True) name = db.Column(db.String(80)) phone_number = db.Column(db.String) address = db.Column(db.String) created_time = db.Column(db.DateTime) orders = db.relationship('Order', backref='customer', lazy='select') page_id = db.Column(db.String, db.ForeignKey('vendors.page_id')) def __init__(self, psid, page_id): self.psid = psid self.page_id = page_id self.created_time = datetime.datetime.utcnow() self.name = '' self.phone_number = 0 self.address = '' @classmethod def find_by_psid(cls, psid): return cls.query.filter_by(psid=psid).first() def save(self): db.session.add(self) db.session.commit() def remove(self): db.session.remove(self) db.session.commit()
class Article(db.Model): sid = db.Column(db.Integer, primary_key=True) source_system_id = db.Column(db.Text, nullable=False) title = db.Column(db.Text, nullable=False) body = db.Column(db.Text, nullable=False) link = db.Column(db.Text, nullable=False) created_at = db.Column(db.DateTime, default=datetime.datetime.utcnow) source_updated_at = db.Column(db.DateTime) unread = db.Column(db.Boolean, default=True, nullable=False) feed_source_sid = db.Column(db.Integer, db.ForeignKey('feed_source.sid'), nullable=False) feed_source = db.relationship('FeedSource', backref=db.backref('article', lazy=True)) __table_args__ = (db.UniqueConstraint('feed_source_sid', 'source_system_id', name='uc_feed_source_id'), ) @classmethod def insert_from_feed(cls, feed_source_sid, feed_articles): articles = [] for article in feed_articles: articles.append({ 'title': article['title'], 'body': article['summary'], 'link': article['link'], 'source_system_id': article['id'], 'source_updated_at': article['source_updated_at'], 'feed_source_sid': feed_source_sid }) insert_stmt = insert(Article).values(articles).on_conflict_do_nothing( constraint='uc_feed_source_id') db.engine.execute(insert_stmt)
class Datapoint(db.Model): __table_args__ = (db.UniqueConstraint("name", "freq", "date"), ) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, nullable=False) freq = db.Column(db.String, nullable=False) date = db.Column(db.Date, nullable=False) value = db.Column(db.Float, nullable=False) def __init__(self, name, freq, date, value): self.name = name self.freq = freq self.date = datetime.strptime(date, "%Y-%m-%d").date() # actually, utils.to_date should be called # but if we import utils, we get import recursion self.value = value @property def serialized(self): # Add serialize method for jsonify return { 'freq': self.freq, 'name': self.name, 'date': datetime.strftime(self.date, "%Y-%m-%d"), 'value': self.value }
class Article(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.Text, nullable=False) body = db.Column(db.Text, nullable=False) link = db.Column(db.Text, nullable=False) guid = db.Column(db.String(255), nullable=False) unread = db.Column(db.Boolean(255), default=True, nullable=False) source_id = db.Column(db.Integer, db.ForeignKey('source.id'), nullable=False) source = db.relationship('Source', backref=db.backref('articles', lazy=True)) date_added = db.Column(db.DateTime, default=datetime.datetime.utcnow) date_published = db.Column(db.DateTime) __table_args__ = (db.UniqueConstraint('source_id', 'guid', name='uc_source_guid'), ) @classmethod def insert_from_feed(cls, source_id, feed_articles): stmt = Article.__table__.insert().prefix_with('IGNORE') articles = [] for article in feed_articles: articles.append({ 'title': article['title'], 'body': article['summary'], 'link': article['link'], 'guid': article['id'], 'source_id': source_id, 'date_published': article['published'], }) db.engine.execute(stmt, articles)
class SeatReservationModel(db.Model): """Docstring here.""" __tablename__ = "seat_reservation" id = db.Column(db.Integer, primary_key=True) price = db.Column(db.Float(6, 2), nullable=False) created_at = db.Column(db.DateTime, default=datetime.now) updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now) seat_id = db.Column(db.Integer, db.ForeignKey("seat.id"), nullable=False) seats = db.relationship(SeatModel, backref="seats", lazy=True) reservation_id = db.Column(db.Integer, db.ForeignKey("reservation.id"), nullable=False) reservation = db.relationship(ReservationModel, backref="reservation", lazy=True) movie_screen_id = db.Column(db.Integer, db.ForeignKey("movie_screen.id"), nullable=False) movie_screen = db.relationship(MovieScreenModel, backref="movie_screen", lazy=True) promo_id = db.Column(db.Integer) db.UniqueConstraint( seat_id, reservation_id, movie_screen_id, ) def json(self): """JSON represation of SeatReservationModel.""" return { "id": self.id, "reservation": self.reservation.json(), "cinema": self.movie_screen.screen.cinema.json(), "screen": self.movie_screen.json(), "seat": self.seats.json() } @classmethod def find(cls, *, data: dict) -> "SeatReservationModel": """Docstring here.""" temp_reservation = cls.query.filter_by(**data).all() return temp_reservation def save_to_db(self): """Docstring here.""" db.session.add(self) db.session.commit() @classmethod def save_all(cls, *, seat_reservations: list): """Docstring here.""" db.session.add_all(seat_reservations) db.session.commit()
class SeenReply(db.Model): __tablename__ = "seen_replies" __table_args__ = (db.UniqueConstraint("reply_id", "journalist_id"), ) id = Column(Integer, primary_key=True) reply_id = Column(Integer, ForeignKey("replies.id"), nullable=False) journalist_id = Column(Integer, ForeignKey("journalists.id"), nullable=False) reply = relationship("Reply", backref=backref("seen_replies", cascade="all,delete")) journalist = relationship("Journalist", backref=backref("seen_replies"))
class SeenMessage(db.Model): __tablename__ = "seen_messages" __table_args__ = (db.UniqueConstraint("message_id", "journalist_id"),) id = Column(Integer, primary_key=True) message_id = Column(Integer, ForeignKey("submissions.id"), nullable=False) journalist_id = Column(Integer, ForeignKey("journalists.id"), nullable=True) message = relationship( "Submission", backref=backref("seen_messages", lazy="dynamic", cascade="all,delete") ) # type: RelationshipProperty[Submission] journalist = relationship( "Journalist", backref=backref("seen_messages") ) # type: RelationshipProperty[Journalist]
class User(db.Model): ''' The data model''' # table name __tablename__ = 'users' __table_args__ = (db.UniqueConstraint('email'), ) id = db.Column(db.Integer(), primary_key=True, autoincrement=True) name = db.Column(db.String(255), nullable=False) email = db.Column(db.String(255), nullable=False) password = db.Column(db.String(32), nullable=False) def as_dict(self): return {c.name: getattr(self, c.name) for c in self.__table__.columns}
class Notification(db.Model): id = db.Column(db.Integer(), primary_key=True) data = db.Column(db.Text, nullable=False) user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) user = db.relationship("User", back_populates="notifications") unique_constraint = db.UniqueConstraint('data', 'user_id') def __init__(self, data, user_id): self.data = data self.user_id = user_id 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 json(self, index): return {str(index): {'data': self.data, 'id': self.id}} @classmethod def get_all_by_user(cls, user_id=None, user=None): notif = None if user_id: notif = cls.query.filter_by(user_id=user_id).all() else: notif = cls.query.filter_by(user=user).all() if len(notif) < 1: return None return notif @classmethod def add(cls, data, user_id): Notification(data, user_id).save_to_db() @classmethod def delete(cls, user=None, user_id=None, id=None, data=None): N = None if user: N = cls.get_all_by_user(user=user) elif user_id: N = cls.get_all_by_user(user_id=user_id) if not N: return if id: N = filter(lambda x: x.id == id, N) elif data: N = filter(lambda x: x.data == data, N) for n in N: n.delete_from_db()
class EventModel(db.Model): __tablename__ = 'events' #This is table name __tableargs__ = (db.UniqueConstraint('name', 'date', 'city')) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), unique=True, nullable=False) place = db.Column(db.String(30), nullable=False) city = db.Column(db.String(30), nullable=False) date = db.Column(db.String(30), nullable=False) price = db.Column(db.Integer, nullable=False) total_available_tickets = db.Column(db.Integer, nullable=False) artists = db.relationship('ArtistModel', secondary=artists, backref=db.backref('events', lazy='dynamic')) def __init__(self, name, place, city, date, price, total_available_tickets): self.name = name self.place = place self.city = city self.date = date self.price = price self.total_available_tickets = total_available_tickets def json(self): artistList = [] for artist in self.artists: artistList.append(artist) return { "id": self.id, "name": self.name, "place": self.place, "city": self.city, "date": self.date, "artists": [artist.json() for artist in artistList], "price": self.price, "total_available_tickets": self.total_available_tickets } 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 find_by_id(cls, id): return cls.query.filter_by(id=id).first()
class ProjectModel(db.Model): __tablename__ = 'projects' __table_args__ = (db.UniqueConstraint('user_id', 'project_name', name='unique_project_name'), ) id = db.Column(db.Integer, primary_key=True) project_name = db.Column(db.String(80)) description = db.Column(db.String(80)) is_archived = db.Column(db.Boolean, default=False) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) users = db.relationship('UserModel') tasks = db.relationship("TaskModel", back_populates="projects", cascade="all, delete") def __init__(self, project_name, description): self.project_name = project_name self.description = description self.is_archived = False def json(self): return { "project_id": self.id, "project_name": self.project_name, "description": self.description, "is_archived": self.is_archived, } def jsonTasks(self): return {'tasks': [task.json() for task in self.tasks]} @classmethod def find_by_id(cls, id): return cls.query.filter_by(id=id).first() @classmethod def find_all(cls): return cls.query.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 LectureStudents(db.Model): __tablename__ = 'lecture_students' # WORKS ! __table_args__ = (db.UniqueConstraint('lecture_id', 'student_id', name='unique_lecture_student'), ) id = db.Column(db.Integer, primary_key=True) lecture_id = db.Column(db.Integer, db.ForeignKey('lectures.id', ondelete='CASCADE'), nullable=False) student_id = db.Column(db.Integer, db.ForeignKey('students.id', ondelete='CASCADE'), nullable=False) final_grade = db.Column(db.Integer, nullable=True) student = db.relationship("Student", backref=backref('lecture_students', passive_deletes=False, cascade="all, delete-orphan")) lecture = db.relationship("Lecture", backref=backref('lecture_students', passive_deletes=False, cascade="all, delete-orphan")) grades = db.relationship("Grade", back_populates="lecture_student") # Do i need 'passive_deletes' for on_delete=Cascade ? # https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete ### First to do ###################################### # lecture student - have to be unique together ! - po zbudowaniu endpointa <--------- WORKS and DONE # Potrzebuje nastepujace endpointy: # 1 Bulk partial update - only student_id - if exist dont delete it cause of final_grade <---- zeby sprawdzic czy to bedzie dzialalo narazie musze zbudwac endpoint ktory pozwoli dawanie gradow # 2 Assigning single student to lecture - could be done on student endpoint or separate endpoint - must have update/delete/patch capabilities FOR EXERICSE DO BOTH # 3 Test deleteing student and lecture - If on delete cascade works !!! # NEXT STEPS: # 1. Test ondelete cascade - student and lecture # 2. Finish endpoint - detail/patch/delete # 3. Try Bulk Update - if it works # Maly Problem : # Przy M2M lecture_students - both lecture->students i students->lecture wskazuje id tabeli posredniej a nie bezposrednio do wlasciwego resource. Rozwiazania: # 1. zmienic na lecture_students # 2. dodac dodatkowa propoerty na schema students/lecture i zmappowac id # 3. bezposrednio zmappowac na modelu relacje - ale i tak chce miec final_grade # 4. czy mouna nested fields jako main fields
class Article(db.Model): id = db.Column(db.Integer, primary_key=True) title = db.Column(db.Text, nullable=False) body = db.Column(db.Text, nullable=False) link = db.Column(db.Text, nullable=False) guid = db.Column(db.String(255), nullable=False) unread = db.Column(db.Boolean, default=True, nullable=False) source_id = db.Column(db.Integer, db.ForeignKey('source.id'), nullable=False) source = db.relationship('Source', db.backref('articles', lazy=True)) date_added = db.Column(db.DateTime, default=datetime.datetime.utcnow) date_published = db.Column(db.DateTime) __table_args__ = (db.UniqueConstraint('source_id', 'guid', name='uc_source_guid'), )
class Customer(db.Model): __tablename__ = "customers" __table_args__ = (db.UniqueConstraint('id', 'account_id', name='unique_account'), ) id = db.Column(db.BigInteger(), primary_key=True) name = db.Column(db.String(64), nullable=False) account_id = db.Column(db.BigInteger(), db.ForeignKey("accounts.id"), nullable=False) _account = None @property def account(self) -> Account: assert self._account is not None, "Load account using query" return self._account
class Url(dbs.Model): """ This is the main logical entrypoint model for the url shortening application. As we want to prevent database pollution with multiple shortcodes for the same URL, the Url is the parent model for all underlying database models. For non time consuming development reasons, only One-to-One relations are set. """ __tablename__ = 'url' __table_args__ = (dbs.UniqueConstraint('url'), ) id = dbs.Column(dbs.Integer, primary_key=True) url = dbs.Column(dbs.String, nullable=False) shortcode = dbs.relationship('Shortcode', uselist=False, back_populates='url') @classmethod def insert_url(cls, url, shortcode=None): """ This method creates a new Url record and returns the related shortcode. If the Url already exist, the existing related shortcode is returned. :param url: The provided URL. :type url: str :param shortcode: The provided shortcode. :type shortcode: str :return: The related shortcode. :rtype: str """ _url = Url.query.filter_by(url=url).first() if _url is None: _shortcode = Shortcode.insert(shortcode=shortcode) _url = cls(url=url, shortcode=_shortcode) dbs.session.add(_url) dbs.session.commit() return _url.shortcode.shortcode
class Article(db.Model): id = db.Column(db.Integer, primary_key = True) title = db.Column(db.Text, nullable = False) body = db.Column(db.Text, nullable = False) link = db.Column(db.Text, nullable = False) guid = db.Column(db.String(255), nullable = False) unread = db.Column(db.Boolean, default = True, nullable = False) distress = db.Column(db.Integer, default = 0, nullable = False) img_link = db.Column(db.Text, nullable = True) img_credit = db.Column(db.Text, nullable = True) tags = db.Column(db.Text, nullable = True) source_id = db.Column(db.Integer, db.ForeignKey('source.id'), nullable = False) source = db.relationship('Source', backref = db.backref('articles', lazy = True)) date_added = db.Column(db.DateTime, default = datetime.datetime.utcnow) date_published = db.Column(db.DateTime) __table_args__ = ( db.UniqueConstraint('source_id', 'guid', name='uc_source_guid'), ) @classmethod def insert_feed(cls, source_id, feed_articles): insert = Article.__table__.insert().prefix_with('IGNORE') article_list = [] for position in feed_articles: distress = filter_dir.binary_filter.classify_bert([position['title']]) article_list.append({ 'title': position['title'], 'body': position['summary'], 'link': position['link'], 'guid': position['id'], 'distress': int(distress), 'source_id': source_id, 'date_published': position['published'], 'img_link': position['img_link'], 'img_credit': position['img_credit'], 'tags': position['tags'] }) db.engine.execute(insert, article_list) count = db.session.query(func.count(Article.title)).scalar() if count>100: db.session.query(func.min(Article.date_added)).one().delete() db.session.commit()
class Rate(db.Model): __tablename__ = 'rate' __table_args__ = ( db.UniqueConstraint('currency_id', 'date'), ) id = db.Column(db.Integer(), primary_key=True) currency_id = db.Column(db.Integer(), db.ForeignKey('currency.id'), nullable=False) date = db.Column(db.DateTime(timezone=True), nullable=False) rate = db.Column(db.Numeric(), nullable=False) volume = db.Column(db.Numeric(), nullable=False) @classmethod async def update_or_create(cls, currency_id, date, rate, volume) -> None: update_query = cls.update.values( rate=rate, volume=volume, ).where( and_(cls.currency_id == currency_id, cls.date == date), ) update, _ = await update_query.gino.status() if update == 'UPDATE 0': # TODO find a better way to check if nothing was found try: await cls.create(currency_id=currency_id, date=date, rate=rate, volume=volume) except asyncpg.UniqueViolationError: await update_query.gino.status() @classmethod async def get_last_rate(cls, currency_id): return await cls.query.where( and_(cls.currency_id == currency_id), ).order_by(cls.date.desc()).gino.first() @classmethod async def get_avg_volume(cls, currency_id: int, last_days=None): query = func.avg(cls.volume).select().where(cls.currency_id == currency_id) if last_days: last_days_dt = datetime.utcnow().replace( hour=0, minute=0, second=0, microsecond=0, ) - timedelta(days=last_days - 1) query = query.where(Rate.date >= last_days_dt) return await query.gino.scalar()
class CategoryModel(db.Model): """CategoryModel is the model class for the Category resource. -- tablename: Categories -- Columns: - id (integer, primary key) - name (text, unique constraint) - type: (text) Methods: json: returns json representaion of itself find_by_name: finds and returns the CategoryModel object with specified name save_to_db: delete_from_db: """ __tablename__ = 'Categories' __table_args__ = (db.UniqueConstraint("name"), ) _id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text) type = db.Column(db.Text) budget = db.Column(db.Float(precision=2)) def __init__(self, name, type, budget): self.name = name self.type = type self.budget = budget def json(self): return {"name": self.name, "type": self.type, "budget": self.budget} @classmethod def find_by_name(cls, name): return cls.query.filter_by(name=name).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()
class SetModel(db.Model): __tablename__ = 'sets' __table_args__ = (db.UniqueConstraint('set_name', name='unique_name_commit'), ) set_id = db.Column(db.Integer, primary_key=True, autoincrement=True) set_name = db.Column(db.String(32), primary_key=True) description = db.Column(db.String(128)) def __init__(self, name, description): self.set_name = name self.description = description def json(self): return { 'set_id': self.set_id, 'set_name': self.set_name, 'description': self.description } @classmethod def find_by_id(cls, _id): return cls.query.filter_by(set_id=_id).first() @classmethod def find_by_name(cls, name): return cls.query.filter_by(set_name=name).first() @classmethod def find_all(cls): return cls.query.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 User(Bot, db.Model): __tablename__ = 'users' __table_args__ = (db.UniqueConstraint('psid', 'id', name='unique_user_orders'), ) id = db.Column(db.Integer, primary_key=True) psid = db.Column(db.String, unique=True) name = db.Column(db.String(80)) phone_number = db.Column(db.String) address = db.Column(db.String) orders = db.relationship('Order', backref='user', lazy='dynamic') def __init__(self, psid): super().__init__() self.psid = psid self.name = '' self.phone_number = 0 self.address = '' @classmethod def find_by_psid(cls, psid): return cls.query.filter_by(psid=psid).first() def get_info(self): request_endpoint = '{}/{}'.format(self.graph_url, self.psid) response = requests.get( request_endpoint, params=self.auth_args ) result = response.json() self.name = result['first_name'] def save(self): db.session.add(self) db.session.commit() def remove(self): db.session.remove(self) db.session.commit()
class OrderUserItemsModel(db.Model): ''' ========================= | Items | ------------------------- | <PK> id: int | | user_id: str | | details: str | | <FK>order: Order | ========================= ''' __tablename__ = 'order_user_items' __table_args__ = ( db.UniqueConstraint('order_id', 'user_id', 'item_name', 'topping', 'ice_percentage', 'sugar_percentage', 'note'), ) id = db.Column(db.Integer, primary_key=True) count = db.Column(db.Integer, nullable=False, default=1) is_valid = db.Column(db.Boolean, unique=False, default=True) user_id = db.Column(db.String(80), nullable=False) order_id = db.Column(db.Integer, db.ForeignKey('orders.id'), nullable=False) item_name = db.Column(db.String(80), db.ForeignKey('items.name'), nullable=False) order = db.relationship('OrderModel', backref=db.backref("order_user_items", cascade="all, delete-orphan")) items = db.relationship('ItemModel', backref=db.backref("order_user_items", cascade="all, delete-orphan")) topping = db.Column(db.String(80), nullable=False) ice_percentage = db.Column(db.Integer, nullable=False) sugar_percentage = db.Column(db.Integer, nullable=False) note = db.Column(db.String(500), nullable=True) created_at = db.Column(db.DateTime, default=datetime.utcnow) def __init__(self, order_id, user_id, item_info): self.order_id = order_id self.user_id = user_id self.item_name = item_info["flavor"] self.topping = item_info["topping"] self.ice_percentage = item_info["ice"] self.sugar_percentage = item_info["sugar"] self.count = item_info["count"] self.note = item_info["note"] def __repr__(self): return "<OrderUserItems ({})>".format(self.__dict__) def json(self): return { "id": self.id, "order_id": self.order_id, "user_id": self.user_id, "item_name": self.item_name, "topping": self.topping, "ice_percentage": self.ice_percentage, "sugar_percentage": self.sugar_percentage, "note": self.note, "count": self.count, "created_at": self.created_at } @classmethod def find_user_order_item_id(cls, order_id, user_id, item_id): return cls.query.filter_by(order_id=order_id).filter_by(user_id=user_id).filter_by(id=item_id).first() @classmethod def find_user_order_item_details(cls, order_id, user_id, item_info): return cls.query.filter_by(order_id=order_id).filter_by(user_id=user_id).filter_by(item_name=item_info["flavor"]).filter_by(topping=item_info["topping"]).filter_by(ice_percentage=item_info["ice"]).filter_by(sugar_percentage=item_info["sugar"]).filter_by(note=item_info["note"]).first() @classmethod def find_user_order(cls, order_id, user_id): return cls.query.filter_by(order_id=order_id).filter_by(user_id=user_id).first() @classmethod def find_order_items(cls, order_id): return cls.query.filter_by(order_id=order_id).all() @classmethod def update_user_order_item(cls, item, item_info): item.item_name = item_info["flavor"] item.topping = item_info["topping"] item.ice_percentage = item_info["ice"] item.sugar_percentage = item_info["sugar"] item.count = item_info["count"] item.note = item_info["note"] return item @classmethod def find_user_items(cls, order_id, user_id): return cls.query.filter_by(order_id=order_id).filter_by(user_id=user_id).all() @classmethod def find_order_items(cls, order_id): return cls.query.filter_by(order_id=order_id).all() def save_to_db(self): with make_session_scope(db.session) as session: db.session.add(self) def delete_from_db(self): with make_session_scope(db.session) as session: session.delete(self)
class EventModel(db.Model): __tablename__ = 'event' #This is table name __table_args__ = (db.UniqueConstraint('name', 'date', 'city'), ) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), unique=True, nullable=False) place = db.Column(db.String(30), nullable=False) city = db.Column(db.String(30), nullable=False) date = db.Column(db.String(30), nullable=False) price = db.Column(db.Integer, nullable=False) total_available_tickets = db.Column(db.Integer, nullable=False) def __str__(self): return str(self.id) + self.name + self.city def json(self): return { "id": self.id, "name": self.name, "place": self.place, "city": self.city, "date": self.date, "price": self.price, "total_available_tickets": self.total_available_tickets, 'artists': list(map(lambda x: x.json(), list(self.artists))) } def save_to_db(self): if self.id and EventModel.query.get(self.id): db.session.commit() else: db.session.add(self) db.session.commit() def delete_from_db(self): if self.id and EventModel.query.get(self.id): db.session.delete(self) db.session.commit() else: raise Exception("Warning not in DB") @classmethod def find_by_id(cls, id): if id: return EventModel.query.get(id) else: return None @classmethod def find_all(cls): return EventModel.query.all() def add_artist(self, modelArtist): self.artists.append(modelArtist) def __init__(self, name, place, city, date, price, total_available_tickets): self.name = name self.place = place self.city = city self.date = date self.price = price self.total_available_tickets = total_available_tickets
class MovieModel(db.Model): """A model that will interact with the movie table SQL queries. Contains multiple functions that can perform the basic CRUD operation for 1 row/entry in the movie table. """ __tablename__ = "movie" id = db.Column(db.Integer, primary_key=True) cinema_id = db.Column(db.Integer, db.ForeignKey("cinema.id"), nullable=False) cinema = db.relationship("CinemaModel", backref="movie_cinema", lazy=True) name = db.Column(db.String(120), nullable=False) description = db.Column(db.Text, nullable=False) duration = db.Column(db.Integer, nullable=False) release_date = db.Column(db.Date, nullable=False) company = db.Column(db.String(120)) genre = db.Column(db.String(64)) casts = db.Column(db.Text) rating = db.Column(db.Float(2, 1)) youtube = db.Column(db.Text) price = db.Column(db.Float) created_at = db.Column(db.DateTime, default=datetime.now) updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now) db.UniqueConstraint(name, cinema_id) def __init__( self,name, cinema_id, description, duration, release_date, price, company=None, genre=None, casts=None, youtube=None, rating=None ): self.name = name self.cinema_id = cinema_id self.description = description self.duration = duration self.release_date = release_date self.rating = rating self.price = price self.company = company self.genre = genre self.casts = casts self.youtube = youtube def json(self): """JSON representation of the MovieModel.""" return { "id": self.id, "name": self.name, "cinema": self.cinema.json(), "description": self.description, "duration": self.duration, "release_date": self.release_date, "rating": self.rating, "price": self.price, "company": self.company, "genre": self.genre, "casts": ast.literal_eval( json.loads(self.casts if self.casts else '{"casts": "[]"}')["casts"] ), "youtube": json.dumps(json.loads(self.youtube if self.youtube else '{"results": "none"}').get("results")) } @classmethod def find_by_id(cls, *, id: int, cinema_id: int) -> "MovieModel": """Find a movie by id.""" return cls.query.filter_by(id=id, cinema_id=cinema_id).first() @classmethod def find_by_name(cls, *, name: str, cinema_id: int) -> "MovieModel": """Find a movie by name.""" return cls.query.filter_by(name=name, cinema_id=cinema_id).first() def save_to_db(self): """Save a new movie in the database.""" db.session.add(self) db.session.commit() def update(self, update_data: dict): """Update a movie in the database.""" (db.session.query(MovieModel) .filter_by(id=self.id) .update(update_data)) db.session.commit() def remove_from_db(self): """Remove a movie from the database.""" db.session.delete(self) db.session.commit() @classmethod def calculate_page_size(cls): """Calculate the page size of the movie page.""" return math.ceil(db.session.query(cls.id).count() / PAGINATION_SIZE)
class PasswordRecoveryModel(db.Model): __tablename__ = 'password_recovery' __table_args__ = (db.UniqueConstraint('key'), ) SIZE = 32 VALID_UNTIL = timedelta(hours=1) user_id = db.Column(db.Integer(), db.ForeignKey('users.id'), primary_key=True) key = db.Column(db.String(SIZE), nullable=False) time = db.Column(db.DateTime(), nullable=False) def __init__(self, user_id, key=None): self.user_id = user_id self.time = datetime.now() self.key = self.generate_key() if key is None else key def json(self): return { 'user_id': self.user_id, 'valid_until': (self.time + self.VALID_UNTIL).strftime('%H:%M:%S') } 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_from_db(self, data): """ Updates through a dictionary with paris of string of name of attribute and it's value. Following same structure as json(). In case of wanting to modify an attribute of an enum use the string name of one of the values. Will raise Exception in case of invalid enum value if it isn't contained inside the possible values of the enum. """ for attr, newValue in data.items(): if newValue is not None: cls = getattr(self, attr) # Checks if value is of the attribute that's trying to be modified is an Enum if isinstance(cls, Enum): # Checks if the enum doesn't contain the newValue if newValue not in type(cls).__members__: raise Exception( f"Enum {type(cls).__name__} doesn't have value: {newValue}" ) # Gets the object of the enum with same name as newValue setattr(self, attr, type(cls)[newValue]) else: setattr(self, attr, newValue) db.session.commit() def send_email(self, email, url_root): message = f"Has sol·licitat una recuperació de contrasenya. Accedeix a {url_root}reset?key={self.key} "\ f"per canviar de contrasenya. \n L'enllaç deixarà de ser vàlid en {self.VALID_UNTIL} o si es torna " \ f"a solicitar un canvi en la mateixa compte." send_email(email, 'Password recovery', message) def has_time_expired(self): return self.time + self.VALID_UNTIL < datetime.now() @classmethod def find_by_id(cls, user_id): cls.clean_expired_keys() return cls.query.filter_by(user_id=user_id).first() @classmethod def find_by_key(cls, key): cls.clean_expired_keys() return cls.query.filter_by(key=key).first() @classmethod def clean_expired_keys(cls): """ Cleans all entries that their time has expired. Will be called every time a query to the model is made. Expiration time is decided through constant class variable VALID_UNTIL. """ time = datetime.now() - cls.VALID_UNTIL cls.query.filter(cls.time <= time).delete() @classmethod def generate_key(cls): """ Generates a random key avoiding duplicating keys using the most secure random generator of the OS. The key will be made by a combination of uppercase and lowercase letters and numbers. """ new_key = ''.join(random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(cls.SIZE)) while cls.query.filter_by(key=new_key).count() != 0: new_key = ''.join( random.SystemRandom().choice(string.ascii_letters + string.digits) for _ in range(cls.SIZE)) return new_key
class CampsiteModel(db.Model): __tablename__ = "campsites" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(80)) lat = db.Column(db.Float(precision=6)) lng = db.Column(db.Float(precision=5)) __table_args__ = ( db.UniqueConstraint("name", "lat", "lng", name="_name_lat_lng_uc"), ) zipcodes = db.relationship("ZipcodeModel", secondary="travel_time", lazy="noload") # dont' need this line because backref in weather_forecasts creates realtionship and "weather_forecasts" list # weather_forecasts = db.relationship( # "WeatherForecastModel", backref=db.backref("campsite", lazy="joined") # ) # state_id = db.Column(db.Integer, db.ForeignKey("states.id")) # state = db.relationship("StateModel") # hooks items and stores tables together def __init__(self, name, lat, lng): self.name = name self.lat = lat self.lng = lng def json(self): return { "name": self.name, "id": self.id, "lat": self.lat, "lng": self.lng, "forecasts": [forecast.json() for forecast in self.weather_forecasts], } def json_without_forecasts(self): return { "name": self.name, "id": self.id, "lat": self.lat, "lng": self.lng, } @classmethod def find_by_name(cls, name): # this line replaces everything below return cls.query.filter_by( name=name ).first() # gets first row, converts row to ItemModel object and returns that. Query is part of sqlalchemy @classmethod def find_by_id(cls, _id): return cls.query.filter_by(id=_id).first() @classmethod def find_by_distance_as_crow_flies( cls, origin_lat, origin_lng, acceptable_distance ): """ Get a list of all campsites within acceptable_distance of zipcode, as the crow flies """ EARTH_RADIUS = 3960 max_lat = origin_lat + np.rad2deg(acceptable_distance / EARTH_RADIUS) min_lat = origin_lat - np.rad2deg(acceptable_distance / EARTH_RADIUS) max_lng = origin_lng + np.rad2deg( acceptable_distance / EARTH_RADIUS / np.cos(np.deg2rad(origin_lat)) ) min_lng = origin_lng - np.rad2deg( acceptable_distance / EARTH_RADIUS / np.cos(np.deg2rad(origin_lat)) ) # Note: this returns campsites within a square boundary. Calculating which campsites # are within a radius is more expensive but could be done after this first cut below # Depends on whether cost of getting drive time for extra points outweighs cost of calculating points within radius # see https://www.movable-type.co.uk/scripts/latlong-db.html return cls.query.filter( cls.lat > min_lat, cls.lat < max_lat, cls.lng > min_lng, cls.lng < max_lng ).all() @classmethod def find_by_duration(cls, zipcode_id, max_duration): return ( cls.query.join(TravelTimeModel) .filter( (TravelTimeModel.zipcode_id == zipcode_id) & (TravelTimeModel.duration_value < max_duration) ) .all() ) # is this the right place to throw this error, or in the resource path? whatever we do, do the same for Upsert def save_to_db(self): try: db.session.add(self) db.session.commit() except IntegrityError: db.session.rollback() print("duplicate campsite entry detected!") def upsert(self): try: db.session.add(self) db.session.commit() except IntegrityError: db.session.rollback() print("duplicate campsite entry detected!") def delete(self): db.session.delete(self) db.session.commit()
class ScheduleModel(db.Model): """A model that will interact with the schedule table SQL queries. Contains multiple functions that can perform the basic CRUD operation for 1 row/entry in the schedule table. """ __tablename__ = "schedule" id = db.Column(db.Integer, primary_key=True) created_at = db.Column(db.DateTime, default=datetime.now) updated_at = db.Column(db.DateTime, default=datetime.now, onupdate=datetime.now) play_datetime = db.Column(db.DateTime, nullable=False) end_datetime = db.Column(db.DateTime, nullable=False) screen_id = db.Column(db.Integer, db.ForeignKey("screen.id"), nullable=False) screen = db.relationship(ScreenModel, backref="schedule_screen", lazy=True) db.UniqueConstraint( screen_id, play_datetime, end_datetime, ) def __init__(self, play_datetime, end_datetime, screen): self.play_datetime = play_datetime self.end_datetime = end_datetime self.screen = screen def json(self): """JSON representation of the ScheduleModel.""" return { "id": self.id, "play_datetime": self.play_datetime, "end_datetime": self.end_datetime, "screen": self.screen.json() } @classmethod def find_by_id(cls, id: int) -> "ScheduleModel": """Find a schedule in the database by id.""" return cls.query.filter_by(id=id).first() @classmethod def find_conflicts(cls, screen_id, scheds): """Find a schedule in the database by screen_id its schedule.""" for sched in scheds: yield (cls.query.from_statement( db.text( SELECT_CONFLICT_SCHEDULE_QUERY.format(screen_id=screen_id, **sched))).first()) def save_to_db(self): """Save a new schedule in the database.""" db.session.add(self) db.session.commit() def update(self, update_data: dict): """Update a schedule in the database.""" (db.session.query(ScheduleModel).filter_by( id=self.id).update(update_data)) def remove_from_db(self): """Remove a schedule from the database.""" db.session.delete(self) db.session.commit() @classmethod def count_instances(cls, master_schedule_id: int) -> list: """Count instances of a master_schedule_id.""" return cls.query.filter_by( master_schedule_id=master_schedule_id).count()
class Vendor(db.Model): __tablename__ = 'vendors' __table_args__ = (db.UniqueConstraint('page_id', 'id', name='unique_vendor_customers'), ) id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, unique=True) # unique username = db.Column(db.String, nullable=True) # unique uid = db.Column(db.String, unique=True) # unique menu_info = db.Column(db.String) address_info = db.Column(db.String) menu = db.Column(NestedMutableJson) prices = db.Column(NestedMutableJson) arabic = db.Column(NestedMutableJson) password = db.Column(db.String) access_token = db.Column(db.String) is_setup = db.Column(db.Boolean) opening_hours = db.Column(db.Time) closing_hours = db.Column(db.Time) page_id = db.Column(db.String, unique=True) # unique customers = db.relationship('Customer', backref='vendor', lazy='select') orders = db.relationship('Order', backref='vendor', lazy='select') def __init__(self, name='', user_name='', password='', uid='', access_token='', page_id='', address_info='', menu_info=''): self.name = name self.uid = uid self.address_info = address_info self.menu_info = menu_info self.username = user_name self.password = password self.access_token = access_token self.page_id = page_id self.menu = {} self.closing_hours = datetime.datetime.utcnow().time() self.opening_hours = datetime.datetime.utcnow().time() self.prices = {} self.arabic = {} self.is_setup = False def is_open(self): time = datetime.datetime.utcnow().time() print(self.closing_hours) print(self.opening_hours) print(time) if time > self.opening_hours and time < self.closing_hours: return True else: return False def set_working_hours(opening_hours, closing_hours): pass def check_password(self, password): return check_password_hash(self.password, password) @classmethod def find_by_page_id(cls, page_id): return cls.query.filter_by(page_id=page_id).first() @classmethod def find_by_uid(cls, uid): return cls.query.filter_by(uid=uid).first() def save(self): print('Vendor Saved') db.session.add(self) db.session.commit() def remove(self): db.session.remove(self) db.session.commit()
class TravelTimeModel(db.Model): __tablename__ = "travel_time" id = db.Column(db.Integer, primary_key=True) zipcode_id = db.Column(db.Integer, db.ForeignKey("zipcodes.id")) campsite_id = db.Column(db.Integer, db.ForeignKey("campsites.id")) duration_value = db.Column(db.Integer) duration_text = db.Column(db.String(80)) __table_args__ = ( db.UniqueConstraint("zipcode_id", "campsite_id", name="_campsite_zipcode_uc"), ) # don't need zipcode info, hence lazy="noload" zipcode = db.relationship( "ZipcodeModel", backref=db.backref("travel_time", cascade="all, delete-orphan", lazy="noload"), ) campsite = db.relationship( "CampsiteModel", backref=db.backref("travel_time", cascade="all, delete-orphan"), ) # leave id off object creation since DB autoincrements it... we should never be entering an ID def __init__(self, zipcode_id, campsite_id, duration_value, duration_text): self.zipcode_id = zipcode_id self.campsite_id = campsite_id self.duration_value = duration_value self.duration_text = duration_text @classmethod def find_by_ids(cls, zipcode_id, campsite_id): return cls.query.filter_by( zipcode_id=zipcode_id, campsite_id=campsite_id ).first() # think that db.joinedload(campsite) will auto load campsite for each duration in one select statement rather than two # Actually commenting out joinedload seems to have no effect on contents or time of api response! why? @classmethod def find_campsites_by_duration(cls, zipcode_id, willing_duration): return ( cls.query.filter_by(zipcode_id=zipcode_id) .filter(cls.duration_value < willing_duration, cls.duration_value >= 0) .options(db.joinedload(cls.campsite)) .all() ) # method that also filters by forecast temp # returns all durations/campsites where campsite has at least one temp above min temp (not sure why it works this way) # about twice as slow as find_campsites_by_duration # how to speed up? # note: must import campsitemodel and weatherforcastmodel and check those models to make sure no circular imports # @classmethod # def find_campsites_by_duration_and_temp( # cls, zipcode_id, willing_duration, min_temp # ): # return ( # cls.query.filter_by(zipcode_id=zipcode_id) # .filter(cls.duration_value < willing_duration, cls.duration_value >= 0) # .join(cls.campsite) # .join(CampsiteModel.weather_forecasts) # .filter(WeatherForecastModel.temperature > min_temp) # .all() # ) @classmethod def get_duration_from_google( cls, zipcode_lat, zipcode_lng, campsite_lat, campsite_lng ): # for hosted on Digital Ocean (and Heroku too?) api_key = os.environ.get("GMAPS_API_KEY") # if hosted locally if not api_key: api_key = config.GMAPS_API_KEY serviceurl = "https://maps.googleapis.com/maps/api/distancematrix/json?" params = { "origins": f"{zipcode_lat},{zipcode_lng}", "destinations": f"{campsite_lat},{campsite_lng}", "key": api_key, } response = requests.get(serviceurl, params=params) js = response.json() duration_value = js["rows"][0]["elements"][0]["duration"]["value"] duration_text = js["rows"][0]["elements"][0]["duration"]["text"] return (duration_value, duration_text) # get durations in batches of 25 campsite locations to save time @classmethod def get_durations_from_google(cls, zipcode_lat, zipcode_lng, campsite_locations): # for hosted on Digital Ocean (and Heroku too?) api_key = os.environ.get("GMAPS_API_KEY") # if hosted locally if not api_key: api_key = config.GMAPS_API_KEY serviceurl = "https://maps.googleapis.com/maps/api/distancematrix/json?" # polyline encoding destinations tuples for batching distance matrix api call destinations_param = "enc:" + polyline.encode(campsite_locations) + ":" params = { "origins": f"{zipcode_lat},{zipcode_lng}", "destinations": destinations_param, "key": api_key, } response = requests.get(serviceurl, params=params) js = response.json() elements = js["rows"][0]["elements"] return elements def save_to_db(self): db.session.add(self) db.session.commit() def delete(self): db.session.delete(self) db.session.commit() def json(self): return { "duration": {"value": self.duration_value, "text": self.duration_text}, "campsite": self.campsite.json(), }
class UserModel(db.Model): __tablename__ = 'apiUsers' __table_args__ = (db.UniqueConstraint('username', 'businessId', 'employeeCode', name='unique_user_per_business'), ) id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(120), unique=True, nullable=False) password = db.Column(db.String(120), nullable=False) employee_name = db.Column(db.String(120), nullable=False) email = db.Column(db.String(120), nullable=False) mobile = db.Column(db.String(120), nullable=False) businessId = db.Column(db.Integer, nullable=False) employeeCode = db.Column(db.String(120), nullable=False) terminal = db.Column(db.String(120)) is_admin = db.Column(db.String(120)) status = db.Column(db.String(50), nullable=False) def __init__(self, username, password, employee_name, email, mobile, businessId, employeeCode, terminal, is_admin, status): self.username = username self.password = password self.employee_name = employee_name self.email = email self.mobile = mobile self.businessId = businessId self.employeeCode = employeeCode self.terminal = terminal self.is_admin = is_admin self.status = status def json(self): return { 'username': self.username, 'password': self.generate_hash(self.password), 'employee_name': self.employee_name, 'email': self.email, 'mobile': self.mobile, 'businessId': self.businessId, 'employeeCode': self.employeeCode, 'terminal': self.terminal, 'is_admin': self.is_admin, 'status': self.status } @classmethod def find_by_user(cls, username): return cls.query.filter_by(username=username).first() @classmethod def find_by_busIdempCode(cls, username, businessId, employeeCode): return cls.query.filter_by(username=username).filter_by( businessId=businessId).filter_by(employeeCode=employeeCode).all() @classmethod def find_by_user_list(cls, employee_list): return cls.query.filter(cls.employeeCode.in_(employee_list)).all() @staticmethod def generate_hash(password): return sha256.hash(password) @staticmethod def verify_hash(password, hash): return sha256.verify(password, hash) @staticmethod def verify_secret_key(secret_key_data): if secret_key_data == app.config['JWT_SECRET_KEY']: return 'is_admin' elif secret_key_data == app.config['JWT_SUPERUSER_SECRET_KEY']: return 'is_superuser' else: return 'None' @staticmethod def verify_active_user(secret_key_data): if secret_key_data == app.config['JWT_SECRET_KEY']: return 'active' elif secret_key_data == app.config['JWT_SUPERUSER_SECRET_KEY']: return 'active' else: return 'inactive' @classmethod def find_by_status(cls, businessId, status): return cls.query.filter(cls.username != 'Superuser').filter_by( businessId=businessId).filter_by(status=status).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 EventModel(db.Model): __tablename__ = n.EVNTS #This is table name id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), nullable=False) place = db.Column(db.String(30), nullable=False) city = db.Column(db.String(30), nullable=False) date = db.Column(db.String(10), nullable=False) price = db.Column(db.Integer, nullable=False) total_available_tickets = db.Column(db.Integer, nullable=False) artists = db.relationship('ArtistModel', secondary=artists_in_event, backref=db.backref(n.EVNTS, lazy='dynamic')) __table_args__ = (db.UniqueConstraint(n.NM, n.DATE, n.CTY),) def __init__(self, name, place, city, date, price, total_available_tickets): self.name = name self.place = place self.city = city self.date = date self.price = price self.total_available_tickets = total_available_tickets def json(self): return { n.EVNT: { n.ID: self.id, n.NM: self.name, n.PLC: self.place, n.CTY: self.city, n.DATE: self.date, n.ARTS: [a.json()[n.ART] for a in self.artists], n.PRC: self.price, n.TAT: self.total_available_tickets } } 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 modify_from_db(self, name, place, city, date, price, total_available_tickets): self.name = name self.place = place self.city = city self.date = date self.price = price self.total_available_tickets = total_available_tickets db.session.add(self) db.session.commit() def sell(self, number_of_tickets): self.total_available_tickets = self.total_available_tickets - number_of_tickets def get_artists(self): return {n.ARTS: [a.json()[n.ART] for a in self.artists]} @classmethod def find_by_id(cls, eid): return EventModel.query.filter_by(id=eid).first() @classmethod def find_by_name(cls, ename): return EventModel.query.filter_by(name=ename).all() @classmethod def find_by_place(cls, eplace): return EventModel.query.filter_by(place=eplace).all() @classmethod def find_by_city(cls, ecity): return EventModel.query.filter_by(city=ecity).all() @classmethod def find_by_date(cls, edate): return EventModel.query.filter_by(date=edate).all() @classmethod def find_by_artist(cls, eartist_name): return EventModel.query.filter(EventModel.artists.any(name=eartist_name)).all() @classmethod def find_by_uniqueness(cls, ename, edate, ecity): return EventModel.query.filter_by(name=ename, date=edate, city=ecity).first() @classmethod def return_all(cls): return {n.EVNTS: [e.json()[n.EVNT] for e in EventModel.query.all()] }