class Student(db.Model): __tablename__ = 'students' id = db.Column(db.Integer, primary_key=True) user_id = db.Column(db.Integer(), db.ForeignKey('users.id'), nullable = False, unique = True) educational_group_id = db.Column(db.Integer(), db.ForeignKey('EducationalGroup.id'), nullable = False) entry_year = db.Column(db.Integer(), nullable = False) degree = db.Column(db.String(100), nullable = False) tuition_format = db.Column(db.String(100), nullable = False) tuition_base = db.Column(db.String(100), nullable = False) __table_args__ = (db.CheckConstraint(degree.in_(["бакалавр", "специалист", "магистр"])), db.CheckConstraint(tuition_format.in_(["очная", "заочная", "вечерняя"])), db.CheckConstraint(tuition_base.in_(["контрактная", "бюджетная"])), ) def __init__(self, user_id, educational_group_id, entry_year, degree, tuition_format, tuition_base): self.user_id = user_id self.educational_group_id = educational_group_id self.entry_year = entry_year self.degree = degree self.tuition_format = tuition_format self.tuition_base = tuition_base @staticmethod def create_student(name, surname, second_name, educational_group_id, entry_year, degree, tuition_format, tuition_base): user = User.create_user(name, surname, second_name) student = Student(user_id = user.id, educational_group_id=educational_group_id, entry_year=entry_year, degree=degree, tuition_format=tuition_format, tuition_base=tuition_base) db_add_objects(student) return student def __repr__(self): return "<Student {}:educational_group_id:{}>".format(self.id, self.educational_group_id)
class Tag(db.Model): __tablename__ = "tag" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20), unique=True, nullable=False) background = db.Column(db.String(6), nullable=False, default="000000") color = db.Column(db.String(6), nullable=False, default="ffffff") issues = db.relationship("Issue", secondary=tags_association, back_populates="tags") __table_args__ = ( db.CheckConstraint("length(name) > 0", name="name_min_length"), db.CheckConstraint("length(background) > 5", name="background_min_length"), db.CheckConstraint("length(background) < 7", name="background_max_length"), db.CheckConstraint("length(color) > 5", name="color_min_length"), db.CheckConstraint("length(color) < 7", name="color_max_length"), ) @db.validates("name") def validate_background(self, key, name: str) -> str: if len(name) < 1: raise ValueError(TOO_SHORT) return name @db.validates("background") def validate_background(self, key, background: str) -> str: if len(background) <= 5 or len(background) >= 7: raise ValueError(TYPE_NOT_COLOR) return background @db.validates("color") def validate_background(self, key, color: str) -> str: if len(color) <= 5 or len(color) >= 7: raise ValueError(TYPE_NOT_COLOR) return color def __repr__(self) -> str: """ :return: Returns a string representation of the object """ return f"{self.__class__.__name__}" \ f"('name': '{self.name}', 'background': '{self.background}', 'color': '{self.color}')" def __iter__(self) -> None: """ :return: Yields the items attributes as a dictionary """ yield "id", self.id yield "name", self.name yield "background", self.background yield "color", self.color def __len__(self) -> int: """ :return: Returns the total number of the tables SQL columns """ return 4
class Message(db.Model): """ A message model with a relationship with the Users tables for sender and receiver. Both sides can access the message and read it. When one side deletes a message, the respective id becomes null. If both sides delete the message, it is deleted from the database. """ __tablename__ = "messages" id = db.Column(db.Integer, primary_key=True) subject = db.Column(db.String(50)) content = db.Column(db.String(250)) sent_date = db.Column(db.DateTime, index=True, default=datetime.utcnow) sender_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True) receiver_id = db.Column(db.Integer, db.ForeignKey(User.id), nullable=True) sender_name = db.Column(db.String(30)) receiver_name = db.Column(db.String(30)) read = db.Column(db.Boolean, default=False) __table_args__ = (db.CheckConstraint(sender_id != receiver_id), ) def __init__(self, sender_id, receiver_id, sender, receiver, subject, content): self.sender_id = sender_id self.receiver_id = receiver_id self.sender_name = sender self.receiver_name = receiver self.subject = subject self.content = content def __repr__(self): return f'Sender: {self.sender_name}, Receiver: {self.receiver_name}, ' \ f'Date Sent: {self.sent_date}, Subject: {self.subject}'
class Type(db.Model): __tablename__ = "type" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(20), nullable=False, unique=True) __table_args__ = (db.CheckConstraint("length(name) > 0", name="name_min_length"), ) @db.validates("name") def validate_background(self, key, name: str) -> str: if len(name) < 1: raise ValueError(TOO_SHORT) return name def __repr__(self) -> str: """ :return: Returns a string representation of the object """ return f"{self.__class__.__name__}('name': '{self.name}')" def __iter__(self) -> None: """ :return: Yields the items attributes as a dictionary """ yield "id", self.id yield "name", self.name def __len__(self) -> int: """ :return: Returns the total number of the tables SQL columns """ return 2
class Employee(db.Model): """ An employee of the hospital. It abstracts the information of the different types of employees that the hospital has. """ __tablename__ = 'employee' dni = db.Column(db.Integer, primary_key=True, autoincrement=False) name = db.Column(db.String(45), nullable=False) last_name = db.Column(db.String(45), nullable=False) address = db.Column(db.String(128), nullable=False, default='Desconocida') phone_number = db.Column(db.String(10), unique=True, default='N/A') salary = db.Column(db.Float, nullable=False) available = db.Column(db.Boolean, nullable=False) type = db.Column(db.String(3), nullable=False) __mapper_args__ = { 'polymorphic_identity': 'employee', 'polymorphic_on': type } __table_args__ = (db.CheckConstraint('salary > 0'), ) def __init__(self, dni, name, ln, addr, pn, salary, available, _type): self.dni = dni self.name = name self.last_name = ln self.address = addr self.phone_number = pn self.salary = salary self.available = available self.type = _type def __repr__(self): return '%s %s' % (self.name, self.last_name)
class ParamedicTeam(db.Model): __tablename__ = 'paramedics_team' id = db.Column('id_params_team', db.Integer, nullable=False, primary_key=True) type = db.Column(db.String(2), nullable=False) available = db.Column(db.Boolean, nullable=False) operation_fee = db.Column(db.Float, nullable=False) members = db.relationship('Paramedic', back_populates='team') dispatches = db.relationship('Dispatch', backref='paramedics_team') __table_args__ = (db.CheckConstraint(""" type = 'SB' OR type = 'SA' OR type = 'SV'"""), ) def __init__(self, _type, available, of): self.type = _type self.available = available self.operation_fee = of def _pretty_type(self): if self.type == 'SB': return u'Soporte Básico' elif self.type == 'SA': return 'Soporte Avanzado' else: return 'Soporte Vital' def __repr__(self): return 'Equipo %s: %s' % (self.id, self._pretty_type())
class User(db.Model, UserMixin): __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(32), nullable=False) last_name = db.Column(db.String(32), nullable=False) genre = db.Column(db.CHAR(1), db.CheckConstraint('genre in (\'M\', \'F\')'), nullable=False) username = db.Column(db.String(80), nullable=False, unique=True) email = db.Column(db.String(120), nullable=False, unique=True) _password = db.Column('password', db.String(128), nullable=False) def __init__(self, name, last_name, genre, username, email, pw): self.name = name self.last_name = last_name self.genre = genre self.username = username self.email = email self._password = bcrypt.generate_password_hash(pw) @hybrid_property def password(self): return self._password @password.setter def _set_password(self, pw): self._password = bcrypt.generate_password_hash(pw) def validate_password(self, password): return bcrypt.check_password_hash(bytes(self.password), password) def __repr__(self): return 'User <%r>' % self.username
class User(db.Model): __tablename__ = "user" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), nullable=False) created_on = db.Column(db.DateTime, default=datetime.utcnow()) __table_args__ = (db.CheckConstraint("length(name) > 0", name="name_min_length"), ) @db.validates("name") def validate_background(self, key, name: str) -> str: if len(name) < 1: raise ValueError(TOO_SHORT) return name def __repr__(self) -> str: """ :return: Returns a string representation of the object """ return f"{self.__class__.__name__}('name': '{self.name}', 'created_on': '{str(self.created_on)}')" def __iter__(self) -> None: """ :return: Yields the items attributes as a dictionary """ yield "id", self.id yield "name", self.name yield "created_on", str(self.created_on) def __len__(self) -> int: """ :return: Returns the total number of the tables SQL columns """ return 3
class Resource(Base): account_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False, index=True) address = db.Column(db.String(144), nullable=False) type = db.Column(db.String(144), nullable=False, index=True) name = db.Column(db.String(144), nullable=False) price = db.Column(db.Numeric, db.CheckConstraint("price >= 0 AND price <= 1000000"), nullable=False) bookings = db.relationship("Booking", lazy=True, backref=db.backref("resource", lazy=False), cascade="all, delete-orphan") __table_args__ = (UniqueConstraint("address", "type", "name", name="unique_atn"), ) def __init__(self, account_id, address, type, name, price, communities): self.account_id = account_id self.address = address self.type = type self.name = name self.price = price self.communities = communities def __str__(self): return "%s, %s %s" % (self.address, self.type, self.name) def price_rnd(self): return self.price.quantize(Decimal('.01'), rounding=ROUND_DOWN) def price_str(self): return PRICE % self.price @staticmethod def get_allowed(): if not current_user.is_authenticated: return [] stmt = text( "SELECT * FROM resource WHERE id IN " "(SELECT DISTINCT resource_id " "FROM community_resource, admin " "WHERE community_resource.community_id " "= admin.community_id " "AND admin.account_id = :user_id) " "OR id IN " "(SELECT DISTINCT resource_id " "FROM community_resource, account " "WHERE community_resource.community_id " "= account.community_id " "AND account.id = :user_id)").params(user_id=current_user.get_id()) return db.session.query(Resource).from_statement(stmt).all()
class Dispatch(db.Model): __tablename__ = 'dispatch' id_ambulance = db.Column(db.Integer, db.ForeignKey('ambulance.id_ambulance'), nullable=False) id_params_team = db.Column(db.Integer, db.ForeignKey('paramedics_team.id_params_team'), nullable=False) id_emergency = db.Column(db.Integer, db.ForeignKey('emergency.id_emergency'), nullable=False) dispatch_hour = db.Column(db.DateTime, nullable=False) arrival_hour = db.Column(db.DateTime) distance = db.Column(db.Integer) status = db.Column(db.Integer, nullable=False) fee = db.Column(db.Float) __table_args__ = (db.PrimaryKeyConstraint('id_ambulance', 'id_params_team', 'id_emergency'), db.CheckConstraint('status >= 1 AND status <= 5')) def __init__(self, ambulance, paramedics, emergency, dispatch_hour, arrival_hour=None, distance=0, status=1, fee=0): self.id_ambulance = ambulance self.id_params_team = paramedics self.id_emergency = emergency self.dispatch_hour = dispatch_hour self.arrival_hour = arrival_hour self.distance = distance self.status = status self.fee = fee def pretty_status(self): if self.status == 1: return 'En ruta' elif self.status == 2: return 'En el sitio' elif self.status == 3: return 'Volviendo' elif self.status == 4: return 'Completado' elif self.status == 5: return 'Cancelado' else: raise AttributeError( 'status should be some number between 1 and 5')
class InsurancePlan(db.Model): __tablename__ = 'insurance_plan' id = db.Column('id_insurance_plan', db.Integer, primary_key=True) category = db.Column(db.Integer, nullable=False) coverage_percentage = db.Column(db.Integer, nullable=False) description = db.Column(db.String(60), nullable=False) __table_args__ = (db.CheckConstraint('category >= 1 AND category <= 4'), ) def __init__(self, category, coverage_percentage, description): self.category = category self.coverage_percentage = coverage_percentage self.description = description
class Invoice(Base): price = db.Column(db.Numeric, db.CheckConstraint("price >= 0"), nullable=False) paid = db.Column(db.Boolean, nullable=False, index=True) bookings = db.relationship("Booking", secondary=invoice_booking, lazy="subquery", single_parent=True, backref=db.backref("invoice", lazy=True, cascade="all")) def __init__(self, bookings): self.paid = False self.bookings = bookings self.calculate_price() def __str__(self): return str(self.id) def price_str(self): return PRICE % self.price def calculate_price(self): self.price = sum([Decimal(b.price) for b in self.bookings]) @staticmethod def get_allowed(filter_unpaid=False): if not current_user.is_authenticated: return [] query = ("SELECT * FROM invoice WHERE id IN " "(SELECT invoice_id FROM invoice_booking " "INNER JOIN booking " "ON invoice_booking.booking_id = booking.id " "WHERE booking.account_id IN " "(SELECT id FROM account " "WHERE community_id IN " "(SELECT community.id FROM community " "INNER JOIN admin " "ON community.id = admin.community_id " "WHERE admin.account_id = :user_id) " "OR id = :user_id)) ") params = {"user_id": current_user.get_id()} if filter_unpaid: query += "AND paid IS :false " params["false"] = False query += "ORDER BY date_created" stmt = text(query).params(params) return db.session.query(Invoice).from_statement(stmt).all()
class Patient(db.Model): __tablename__ = 'patient' id = db.Column(db.Integer, primary_key=True, unique=True, autoincrement=True) patientSSN = db.Column(db.Integer, primary_key=True, unique=True) name = db.Column(db.String(30), nullable=False) age = db.Column(db.Integer, db.CheckConstraint('age>0 and age<200'), nullable=False) dateOfAdmission = db.Column(db.DateTime, default=datetime.utcnow) typeOfBed = db.Column(db.String, nullable=False) address = db.Column(db.String, nullable=False) state = db.Column(db.String) city = db.Column(db.String) status = db.Column(db.String, nullable=False) medicine_bill = db.relationship('Medicine', secondary=billing) test_bill = db.relationship('Test', secondary=billing)
class Comment(db.Model): __tablename__ = "comment" id = db.Column(db.Integer, primary_key=True) parent_id = db.Column(db.Integer, db.ForeignKey('issue.id')) author = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) content = db.Column(db.Text, nullable=False) edited = db.Column(db.Boolean, default=False) created_on = db.Column(db.DateTime, default=datetime.utcnow()) __table_args__ = (db.CheckConstraint("length(content) > 0", name="content_min_length"), ) @db.validates("name") def validate_background(self, key, content: str) -> str: if len(content) < 1: raise ValueError(TOO_SHORT) return content def __repr__(self) -> str: """ :return: Returns a string representation of the object """ return f"{self.__class__.__name__}" \ f"('id': '{self.id}', 'parent_id': '{self.parent_id}', 'author': '{self.author}'," \ f" 'content': '{self.content}', 'edited': '{self.edited}', 'created_on': '{self.created_on}')" def __iter__(self) -> None: """ :return: Yields the items attributes as a dictionary """ yield "id", self.id yield "parent_id", self.parent_id yield "author", self.author yield "content", self.content yield "edited", self.edited yield "created_on", str(self.created_on) def __len__(self) -> int: """ :return: Returns the total number of the tables SQL columns """ return 6
class Favorite(db.Model): __tablename__ = "Favorite" source_id = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) target_id = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) date_created = db.Column(db.DateTime, default=func.now(), server_default=func.now(), nullable=False) __table_args__ = ( db.PrimaryKeyConstraint("source_id", "target_id"), db.CheckConstraint("source_id != target_id"), ) # Can't favorite oneself. def __init__(self, source_id, target_id): self.source_id = source_id self.target_id = target_id
class Paramedic(Employee): __tablename__ = 'paramedic' dni = db.Column(db.Integer, db.ForeignKey('employee.dni'), primary_key=True) specialization = db.Column(db.CHAR(3), nullable=False, default='UNK') id_params_team = db.Column(db.Integer, db.ForeignKey('paramedics_team.id_params_team')) team = db.relationship('ParamedicTeam', back_populates='members') __mapper_args__ = {'polymorphic_identity': 'PRM'} __table_args__ = (db.CheckConstraint(""" specialization = 'PAB' OR specialization = 'APA' OR specialization = 'AEM' OR specialization = 'TEM'"""), ) def __init__(self, dni, name, ln, addr, pn, salary, available, _type, specialization): super(Paramedic, self).__init__(dni, name, ln, addr, pn, salary, available, _type) self.specialization = specialization
class Booking(Base): account_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False, index=True) resource_id = db.Column(db.Integer, db.ForeignKey("resource.id"), nullable=False, index=True) start_dt = db.Column(db.DateTime, nullable=False, index=True) end_dt = db.Column(db.DateTime, nullable=False, index=True) price = db.Column(db.Numeric, db.CheckConstraint("price >= 0"), nullable=False) __table_args__ = (CheckConstraint("start_dt < end_dt", name="time_direction"), ) def __init__(self, account_id, resource_id, start_dt, end_dt): self.account_id = account_id self.resource_id = resource_id self.start_dt = start_dt self.end_dt = end_dt self.calculate_price() def __str__(self): start_dt = check_type_dt(self.start_dt) start_date = start_dt.strftime("%Y-%m-%d") return (PRICE + ", %s, %s, %s") % (self.price, self.account, self.resource, start_date) def str_no_account(self): start_dt = check_type_dt(self.start_dt).strftime("%Y-%m-%d %H:%M") return (PRICE + ", %s, %s") % (self.price, self.resource, start_dt) def start_date_str(self): return check_type_dt(self.start_dt).strftime("%Y-%m-%d") def start_time_str(self): return check_type_dt(self.start_dt).strftime("%H:%M") def end_date_str(self): return check_type_dt(self.end_dt).strftime("%Y-%m-%d") def end_time_str(self): return check_type_dt(self.end_dt).strftime("%H:%M") def price_str(self): return PRICE % self.price def calculate_price(self): time_span = self.end_dt - self.start_dt hours = Decimal(time_span.total_seconds()) / 3600 price = Resource.query.get(self.resource_id).price * hours self.price = price.quantize(Decimal('.01'), rounding=ROUND_DOWN) @staticmethod def is_free_time_slot(b): query = ("SELECT id FROM booking " "WHERE resource_id = :resource_id " "AND end_dt > :start_dt " "AND start_dt < :end_dt") stmt = (text(query + " AND id <> :booking_id").params( booking_id=b.id, resource_id=b.resource_id, start_dt=b.start_dt, end_dt=b.end_dt) if b.id else text(query).params(resource_id=b.resource_id, start_dt=b.start_dt, end_dt=b.end_dt)) return not db.engine.execute(stmt).fetchone() @staticmethod def get_allowed_by_account(invoice_id=None): if not current_user.is_authenticated: return [] query = ("SELECT * FROM booking WHERE (account_id IN " "(SELECT account.id FROM account " "INNER JOIN admin " "ON account.community_id = admin.community_id " "WHERE admin.account_id = :user_id) " "OR account_id = :user_id) " "AND id NOT IN " "(SELECT booking_id FROM invoice_booking") params = {"user_id": current_user.get_id()} if invoice_id: query += " WHERE invoice_id <> :invoice_id" params["invoice_id"] = invoice_id query += ") ORDER BY start_dt" stmt = text(query).params(params) return db.session.query(Booking).from_statement(stmt).all() @staticmethod def get_allowed_by_resource(from_dt=None, to_dt=None, resource_ids=None, filter_not_in_invoice=False): if not current_user.is_authenticated: return [] query = ("SELECT * FROM booking WHERE (resource_id IN " "(SELECT DISTINCT community_resource.resource_id " "FROM community_resource INNER JOIN admin " "ON admin.community_id = community_resource.community_id " "WHERE admin.account_id = :user_id) " "OR resource_id IN " "(SELECT community_resource.resource_id " "FROM community_resource INNER JOIN account " "ON account.community_id = community_resource.community_id " "WHERE account.id = :user_id))") params = {"user_id": current_user.get_id()} if from_dt: query += " AND end_dt > :from_dt" params["from_dt"] = from_dt if to_dt: query += " AND start_dt < :to_dt" params["to_dt"] = to_dt if resource_ids: query += " AND resource_id IN (:resource_id_0" params["resource_id_0"] = resource_ids[0] list_length = len(resource_ids) if list_length > 1: for i in range(1, list_length): query += ", :resource_id_%d" % i params["resource_id_%d" % i] = resource_ids[i] query += ")" if filter_not_in_invoice: query += (" AND id NOT IN " "(SELECT DISTINCT booking_id FROM invoice_booking)") query += " ORDER BY start_dt" stmt = text(query).params(params) return db.session.query(Booking).from_statement(stmt).all()
class Issue(db.Model): __tablename__ = "issue" id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(60), nullable=False) reporter = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False) assignee = db.Column(db.Integer, db.ForeignKey('user.id'), default=None) description = db.Column(db.Text) type = db.Column(db.Integer, db.ForeignKey("type.id"), default=None) tags = db.relationship("Tag", secondary=tags_association, back_populates="issues") status = db.Column(db.Integer, default=1) priority = db.Column(db.Integer, default=3) comments = db.relationship("Comment", backref="parent") created_on = db.Column(db.DateTime, default=datetime.utcnow()) __table_args__ = ( db.CheckConstraint("length(title) > 0", name="title_min_length"), db.CheckConstraint(f"priority < {len(data_models.PRIORITY) + 1}", name="priority_max_length"), db.CheckConstraint(f"status < {len(data_models.STATUS) + 1}", name="status_max_length"), ) @db.validates("title") def validate_background(self, key, title: str) -> str: if len(title) < 1: raise ValueError(TOO_SHORT) return title @db.validates("priority") def validate_background(self, key, priority: int) -> int: if not data_models.validate_priority(priority): raise ValueError(OUT_OF_RANGE) return priority @db.validates("status") def validate_background(self, key, status: int) -> int: if not data_models.validate_status(status): raise ValueError(OUT_OF_RANGE) return status def __repr__(self) -> str: """ :return: Returns a string representation of the object. """ return f"{self.__class__.__name__}" \ f"('title': '{self.title}', 'reporter': '{self.reporter}', 'assignee': '{self.assignee}'," \ f"'description': '{self.description}', 'type': '{self.type}', 'status': '{self.status}'," \ f"'priority': '{self.priority}')" def __iter__(self) -> None: """ :return: Yields the items attributes as a dictionary. """ yield "id", self.id yield "title", self.title yield "reporter", self.reporter yield "assignee", self.assignee yield "description", self.description yield "type", self.type yield "tags", self.tags yield "status", self.status yield "priority", self.priority yield "comments", self.comments yield "created_on", str(self.created_on) def __len__(self) -> int: """ :return: Returns the total number of the tables SQL columns """ return 9
class Conversation(db.Model): __tablename__ = "Conversation" id = db.Column(db.Integer, primary_key=True, autoincrement=True) date_created = db.Column(db.DateTime, default=func.now(), server_default=func.now(), nullable=False) alpha = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) beta = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) __table_args__ = ( db.UniqueConstraint("alpha", "beta"), db.CheckConstraint("beta > alpha"), ) def __init__(self, alpha, beta): """An alpha is always less than beta, and never equal.""" self.alpha = alpha self.beta = beta @staticmethod def get_new_message_count(): return {"count": "123"} @staticmethod def find_my_conversations(): statement = text( 'SELECT "Conversation".id AS "conversation_id", "Conversation".date_created AS "conversation_started", ' '(SELECT LEFT(content, 32) FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "latest_message",' '(SELECT date_created FROM "Message" WHERE "Message".conversation_id = "Conversation".id ORDER BY date_created DESC LIMIT 1) AS "date_created",' '(SELECT handle FROM "Account" WHERE id=4) AS "alpha",' '(SELECT handle FROM "Account" WHERE id=3) AS "beta"' 'FROM "Conversation" WHERE alpha=:id OR beta=:id;') connection = db.engine.connect() result_set = connection.execute(statement, id=current_user.id) response = [] for row in result_set: alpha = row["alpha"] beta = row["beta"] if alpha == current_user.handle: other_profile = beta else: other_profile = alpha response.append({ "conversation_id": row["conversation_id"], "date_created": row["date_created"], "other_profile": other_profile, "latest_message": row["latest_message"] }) connection.close() return response @staticmethod def get_conversation(conversation_id): statement = text( 'SELECT id, date_created AS "sent", content, ' '(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".source_id) AS "source", ' '(SELECT "Account".handle FROM "Account" WHERE "Account".id = "Message".target_id) AS "target"' 'FROM "Message" WHERE conversation_id = :conversation_id ORDER BY date_created ASC;' ) connection = db.engine.connect() result_set = connection.execute(statement, conversation_id=conversation_id) response = [] for row in result_set: if row["source"] == current_user.handle: own_message = True else: own_message = False response.append({ "id": row["id"], "sent": row["sent"], "content": row["content"], "own_message": own_message }) connection.close() return response @staticmethod def __get_date_string(datetime): return str(datetime.hour) + ":" + str(datetime.minute) + ":" + str(datetime.second) \ + " " + str(datetime.day) + "." + str(datetime.month) + "." + str(datetime.year)
class Message(db.Model): __tablename__ = "Message" id = db.Column(db.Integer, primary_key=True, autoincrement=True) conversation_id = db.Column(db.Integer, db.ForeignKey("Conversation.id"), nullable=False) source_id = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) target_id = db.Column(db.Integer, db.ForeignKey("Profile.id"), nullable=False) date_created = db.Column(db.DateTime, default=func.now(), server_default=func.now(), nullable=False) content = db.Column(db.String(1024), nullable=False) has_source_deleted = db.Column(db.Boolean, default=False, server_default='f', nullable=False) has_target_deleted = db.Column(db.Boolean, default=False, server_default='f', nullable=False) read = db.Column(db.Boolean, default=False, server_default='f', nullable=False) __table_args__ = (db.CheckConstraint("source_id != target_id"), ) # Can't send a message to oneself. def __init__(self, conversation_id, source_id, target_id, content): self.conversation_id = conversation_id self.source_id = source_id self.target_id = target_id self.content = content @staticmethod def get_latest_message_in_conversation(conversation_id): statement = text( 'SELECT * FROM "Message" WHERE conversation_id=:conversation_id ORDER BY date_created DESC LIMIT 1;' ) connection = db.engine.connect() result_set = connection.execute(statement, conversation_id=conversation_id) response = [] for row in result_set: own_message = False if row[2] == current_user.id: own_message = True response.append({ "date_created": row[4], "content": row[5], "own_message": own_message }) connection.close() return response[0]