class Members(db.Model): __tablename__ = "members" id = db.Column(db.Integer, primary_key=True) table_id = db.Column(db.Integer, db.ForeignKey('tables.id'), nullable=False) user_id = db.Column(db.Text, db.ForeignKey('users.email'), nullable=False) # Can be either 'creator', 'admin', 'editor' or 'visitor' role = db.Column(db.String(20), nullable=False) def get_table_id(self): return self.table_id def get_member_id(self): return self.user_id def get_member_role(self): return self.role def set_member_role(self, role): self.role = role def to_dict(self): return { 'id': self.id, 'table_id': self.table_id, 'member_email': self.user_id, 'member_role': self.role, }
class Tasks(db.Model): __tablename__ = "tasks" id = db.Column(db.Integer, primary_key=True) description = db.Column(db.Text, nullable=False) column_id = db.Column(db.Integer, db.ForeignKey('columns.id')) def get_id(self): '''Return the task's id.''' return self.id def get_description(self): '''Return the task's description.''' return self.description def to_dict(self): return {'id': self.id, 'description': self.description, 'column_id': self.column_id}
class Users(db.Model, flask_login.UserMixin): __tablename__ = "users" email = db.Column(db.Text, primary_key=True) username = db.Column(db.Text, nullable=False) password_hash = db.Column(db.Text, nullable=False) tables = db.relationship('Tables', backref='owner', cascade='all,delete', lazy=True) membership = db.relationship('Members', backref='membership', lazy=True) def set_password(self, password): '''Set the user's password.''' self.password_hash = generate_password_hash(password) def check_password(self, password): '''Check if the provided password and the user's password match.''' return check_password_hash(self.password_hash, password) def get_id(self): '''Return the user's email.''' return self.email def get_name(self): '''Return the user's name.''' return self.username def get_table_by_id(self, id): ''' Return corresponding table, mostly used when retrieveing a table shared with the user''' if self.membership: table, _ = self.get_tables_shared_with_me() for t in table: if t.id == id: return (t, 'Found') return (None, 'Not found') return (None, 'Not found') def get_table_by_name(self, name): '''Return the table that matches the name provided.''' table = Tables.query.filter_by(name=name, creator=self.email).first() if table: return (table, "Table '{}' found".format(name)) table = Tables.query.filter_by(name=name).first() if table and table.get_member_by_email(self.email): return (table, "Table '{}' found".format(name)) return (None, "Table '{}' not found".format(name)) def get_private_tables(self): '''Return a list of the user's private tables.''' if self.tables: table = [t for t in self.tables if not t.shared] if table: return (table, "Found") return (None, "You don't have any private table yet") return (None, "You don't have any private table yet") def get_tables_shared_with_others(self): '''Return a list of the user's shared tables.''' if self.tables: table = [t for t in self.tables if t.shared] if table: return (table, "Found") return (None, "You did not share any table yet") return (None, "You did not share any table yet") def get_tables_shared_with_me(self): '''Return a list of the tables shared with the user.''' table = [] if self.membership: for m in self.membership: t = Tables.query.filter_by(id=m.get_table_id()).first() if t and t.creator != self.email: table.append(t) if table: return (table, "Found") return (None, "No table shared with you") def add_table(self, name): '''Create a new table if it doesn't already exists.''' if name: table = Tables(name=name, creator=self.email, shared=False) if Tables.query.filter_by(name=name).first() is None: db.session.add(table) db.session.commit() return (table, "Table '{}' successfully created".format(name)) return (None, "A table with name: '{}' already exists".format(name)) return (None, "Looks like you did not give us a table name") def remove_table_by_name(self, name): '''Remove the table from the list of the user's tables.''' table = db.session.query(Tables).filter( Tables.name == name, Tables.creator == self.email).first() if table: db.session.delete(table) db.session.commit() return True return False def to_dict(self): return {'email': self.email, 'username': self.username}
class Columns(db.Model): __tablename__ = "columns" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False) table_id = db.Column(db.Integer, db.ForeignKey('tables.id')) tasks = db.relationship('Tasks', backref=db.backref('column'), cascade='all,delete', lazy=True) def get_name(self): '''Return the column's name.''' return self.name def get_task_by_id(self, id): '''Return the task that matches the name.''' task = Tasks.query.filter_by(id=id, column_id=self.id).first() if task: return (task, "Task found") return (None, "Task not found") def get_tasks(self): '''Return the tasks of the column.''' if self.tasks: return (self.tasks, "Found") return (None, "You don't have any tasks yet") def add_task(self, description): '''Add a task to the column.''' if description: task = Tasks(description=description, column_id=self.id) db.session.add(task) db.session.commit() return (task, "Task successfully added") return (None, "You did not give a description to the task") def remove_task_by_id(self, id): '''Remove the task matching id from the list of the column's tasks.''' task, _ = self.get_task_by_id(id) if task: db.session.delete(task) db.session.commit() return True return False def move_task_to(self, task_id, column): '''Move the task matching id to a new column.''' task, _ = self.get_task_by_id(task_id) if task and column: new_task, _ = column.add_task(task.get_description()) self.remove_task_by_id(task_id) return new_task return None def to_dict(self): return {'id': self.id, 'name': self.name, 'table_id': self.table_id, 'tasks': [task.to_dict() for task in self.tasks], }
class Tables(db.Model): __tablename__ = "tables" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.Text, nullable=False, unique=True) creator = db.Column(db.Text, db.ForeignKey('users.email'), nullable=False) columns = db.relationship('Columns', backref='table', cascade='all,delete', lazy=True) shared = db.Column(db.Boolean, nullable=False, default=False) members = db.relationship('Members', backref='table', cascade='all,delete', lazy=True) def get_name(self): '''Return the table's name.''' return self.name def get_creator(self): '''Return the table's creator.''' return self.creator def get_column_by_name(self, name): '''Return the column that matches the name.''' if name: column = Cols.query.filter_by(name=name, table_id=self.id).first() if column: return (column, "Column '{}' found".format(name)) return (None, "Column '{}' not found".format(name)) return (None, "You did not provide the column name") def get_column_by_id(self, id): '''Return the column that matches the id.''' column = Cols.query.filter_by(id=id, table_id=self.id).first() if column: return (column, "Column found") return (None, "Column not found") def get_columns(self): '''Return the columns of the table.''' if self.columns: return (self.columns, "Found") return (None, "You don't have any columns yet") def add_column(self, name, current_user_email): '''Add a column to the table.''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() in ("admin", "editor")): if name: column = Cols(name=name, table_id=self.id) is_column, _ = self.get_column_by_name(name) if not is_column: db.session.add(column) db.session.commit() return (column, "Column '{}' successfully created".format(name)) return ( None, "You already have a column with name: '{}'".format(name)) return (None, "Looks like you did not give us a column name") return (None, "You don't have the right to add a column") def delete_column_by_name(self, name, current_user_email): '''Remove the column from the list of the table's columns.''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() in ("admin", "editor")): if name: col = db.session.query(Cols).filter( Cols.name == name, Cols.table_id == self.id).first() if col: db.session.delete(col) db.session.commit() return (True, "'{}' successfully deleted".format(name)) return (False, "The column you want to delete does not exist") return (None, "Looks like you did not give us a column name") return (None, "You don't have the right to delete this column") def get_member_by_email(self, email): '''Return the member that matches the email or None if do not exist.''' members, _ = self.get_members() if not members: return None for member in members: if member.get_member_id() == email: return member return None def get_members(self): '''Return table's members if the table is shared, and if there is any.''' if self.shared and self.members: return (self.members, "Found") return (None, '''This table is either not shared, or do not have any member yet''') def add_member(self, new_member_email, current_user_email): '''Add a column to the table.''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() == "admin"): if self.shared and new_member_email: if new_member_email == self.creator: if self.get_member_by_email(new_member_email): return (None, 'You cannot add the creator !') member = Members(table_id=self.id, user_id=new_member_email, role='creator') db.session.add(member) db.session.commit() return (member, "Ok") else: member = Members(table_id=self.id, user_id=new_member_email, role='visitor') is_member = self.get_member_by_email(new_member_email) if is_member: return ( None, " '{}' is already a member".format(new_member_email)) return (member, "Ok") return (None, '''Looks like you did not give us an email, or maybe you're trying to add members to a private table''') return (None, "You don't have the right to add new members") def delete_member_by_email(self, email, current_user_email): '''Remove a member from the list of the table's members.''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() == "admin"): if email == self.creator: return (False, 'You cannot delete the creator') member = db.session.query(Members).filter( Members.user_id == email, Members.table_id == self.id).first() if member: db.session.delete(member) db.session.commit() return (True, "member deleted") return (False, "something went wrong, please try again later") return (False, "You don't have the right to add new members") def set_member_as_admin(self, member_email, current_user_email): '''Grant member with admin privileges''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() == "admin"): if member_email == self.creator: return (False, 'You cannot change the status of the creator') member = db.session.query(Members).filter( Members.user_id == member_email, Members.table_id == self.id).first() if member: member.set_member_role("admin") db.session.add(member) db.session.commit() return (True, "{} granted 'admin' privileges".format(member_email)) return (False, "something went wrong, please try again later") return ( False, "You don't have the right to change member's role on this table") def set_member_as_editor(self, member_email, current_user_email): '''Grant member with editor privileges''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() == "admin"): if member_email == self.creator: return (False, 'You cannot change the status of the creator') member = db.session.query(Members).filter( Members.user_id == member_email, Members.table_id == self.id).first() if member: member.set_member_role("editor") db.session.add(member) db.session.commit() return (True, "{} granted 'editor' privileges".format(member_email)) return (False, "something went wrong, please try again later") return ( False, "You don't have the right to change member's role on this table") def set_member_as_visitor(self, member_email, current_user_email): '''Grant member with visitor privileges''' if self.creator == current_user_email or ( self.get_member_by_email(current_user_email) and self.get_member_by_email(current_user_email).get_member_role() == "admin"): if member_email == self.creator: return (False, 'You cannot change the status of the creator') member = db.session.query(Members).filter( Members.user_id == member_email, Members.table_id == self.id).first() if member: member.set_member_role("visitor") db.session.add(member) db.session.commit() return (True, "{} granted 'visitor' privileges".format(member_email)) return (False, "something went wrong, please try again later") return ( False, "You don't have the right to change member's role on this table") def share(self, current_user_email): if not self.shared: self.shared = True self.add_member(current_user_email, current_user_email) db.session.commit() def rename(self, name): '''Rename the table if there is no existing table with the same name already ''' if name: table = Tables.query.filter_by(name=name).first() if table is None: self.name = name db.session.commit() return (self, "Updated successfully !") return (None, "A table with name: '{}' already exists".format(name)) return (None, "Looks like you did not give us a table name") def to_dict(self, current_user_email): member = self.get_member_by_email(current_user_email) if member: current_user_role = member.get_member_role() else: current_user_role = 'visitor' return { 'id': self.id, 'name': self.name, 'creator': self.creator, 'columns': [col.to_dict() for col in self.columns], 'members': [memb.to_dict() for memb in self.members], 'current_user_role': current_user_role }