def homolog(self): if self.species.name == "H**o sapiens": ## if gene is a human gene, return the mouse homolog ## homology_rel = db.relationship( "Homology", backref="genes", primaryjoin="genes.entrez_gene_id == homology.human_id" ) return homology_rel.mouse_url elif self.species.name == "Mus musculus": homology_rel = db.relationship( "Homology", backref="genes", primaryjoin="genes.entrez_gene_id == homology.mouse_id" ) return homology_rel.human_url else: return "None"
class FlicketAction(Base): """ SQL table that stores the action history of a ticket. For example, if a user claims a ticket that action is stored here. The action is associated with either the ticket_id (if no posts) or post_id (of lastest post). The reason for this is displaying within the ticket view. """ __tablename__ = 'flicket_ticket_action' id = db.Column(db.Integer, primary_key=True) ticket_id = db.Column(db.Integer, db.ForeignKey(FlicketTicket.id)) ticket = db.relationship(FlicketTicket) post_id = db.Column(db.Integer, db.ForeignKey(FlicketPost.id)) post = db.relationship(FlicketPost) assigned = db.Column(db.Boolean) claimed = db.Column(db.Boolean) released = db.Column(db.Boolean) closed = db.Column(db.Boolean) opened = db.Column(db.Boolean) user_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser, foreign_keys=[user_id]) recipient_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) recipient = db.relationship(FlicketUser, foreign_keys=[recipient_id]) date = db.Column(db.DateTime) def output_action(self): """ Method used in ticket view to show what action has taken place in ticket. :return: """ _date = self.date.strftime('%d-%m-%Y %H:%M') if self.assigned: return 'Ticket assigned to <a href="mailto:{1}">{0}</a> by <a href="mailto:{3}">{2}</a> | {4}'.format( self.recipient.name, self.recipient.email, self.user.name, self.user.email, _date) if self.claimed: return 'Ticked claimed by <a href="mailto:{}">{}</a> | {}'.format( self.user.email, self.user.name, _date) if self.released: return 'Ticket released by <a href="mailto:{}">{}</a> | {}'.format( self.user.email, self.user.name, _date) if self.closed: return 'Ticked closed by <a href="mailto:{}">{}</a> | {}'.format( self.user.email, self.user.name, _date) def __repr__(self): return ( '<Class FlicketAction: ticket_id={}, post_id={}, assigned={}, unassigned={}, claimed={},' 'released={}, closed={}, opened={}, user_id={}, recipient_id={}, date={}>' ).format(self.ticket_id, self.post_id, self.assigned, self.unassigned, self.claimed, self.released, self.closed, self.opened, self.user_id, self.recipient_id, self.date)
class User(Base): __tablename__ = "account" email = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) admin = db.Column(db.Boolean, nullable=False) recipes = db.relationship("Recipe", backref='account', lazy=True) def __init__(self, email, username, password): self.email = email self.username = username self.password = password self.admin = False def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return True def is_authenticated(self): return True def roles(self): if self.admin: return ["ADMIN"] return ["USER"] @staticmethod def get_user_commentcount(): stmt = text("SELECT COUNT(*) FROM comment" " WHERE account_id = :account") response = db.engine.execute(stmt, account = current_user.id) res = 0 for row in response: res += row[0] return res @staticmethod def get_commentcount_on_own_recipes(): stmt = text("SELECT COUNT(*) FROM account" " LEFT JOIN recipe ON recipe.account_id = account.id" " LEFT JOIN comment ON comment.recipe_id = recipe.id" " WHERE recipe.account_id = :account AND comment.recipe_id = recipe.id ") response = db.engine.execute(stmt, account = current_user.id) res = 0 for row in response: res += row[0] return res
class Post(Base): __tablename__ = "posts" date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) body = db.Column(db.String(1000), nullable=False) liked_by_users = db.relationship("User", secondary=post_likes) topic_id = db.Column(db.Integer, db.ForeignKey("topics.id"), nullable=False, index=True) topic = db.relationship("Topic", back_populates="posts") author_id = db.Column(db.Integer, db.ForeignKey("accounts.id"), nullable=False, index=True) author = db.relationship("User", back_populates="posts") def __init__(self, body): self.body = body @staticmethod def find_most_liked_posts_today(amount=5): stmt = text( "SELECT Posts.id, Posts.date_created, Posts.date_modified, " "Posts.body, Post_likes.likes, Posts.topic_id, Posts.author_id, " "Accounts.username FROM Posts JOIN (SELECT post_id, COUNT(post_id) AS likes " "FROM Post_likes GROUP BY post_id) AS Post_likes ON Posts.id = Post_likes.post_id " "JOIN Accounts ON Posts.author_id = Accounts.id WHERE date(Posts.date_created) = date('now') " "ORDER BY likes DESC LIMIT :amount;").params(amount=amount) result = db.engine.execute(stmt) response = [] for row in result: response.append({ "id": row[0], "date_created": TimeFormatter.get_timestamp(row[1]), "date_modified": TimeFormatter.get_timestamp(row[2]), "body": row[3], "likes": row[4], "topic_id": row[5], "author": { "id": row[6], "username": row[7] }, "preview": True }) return response
class User(db.Model): __tablename__ = "account" id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) lists = db.relationship("Lists", cascade='all, delete-orphan', backref='account', lazy=True) admin = db.Column(db.Boolean, nullable=False) def __init__(self, name, username, password, admin): self.name = name self.username = username self.password = password self.admin = admin def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return False def is_authenticated(self): return True def get_admin(self): return self.admin #yhteenvetokyselyt @staticmethod def users_with_waiting_jobs(): stmt = text( "SELECT DISTINCT Account.id, Account.name FROM Account, Lists, Jobs" " WHERE Account.id = Lists.account_id AND Lists.id = Jobs.list_id" " AND Jobs.id NOT IN(SELECT Jobs.id FROM Jobs WHERE" " Jobs.status=2 OR Jobs.status=3)") tulos = db.engine.execute(stmt) lista = [] for row in tulos: lista.append({"id": row[0], "nimi": row[1]}) return lista @staticmethod def users_and_lists_count(): stms = text("SELECT Account.id, Account.name, COUNT(*) FROM Account" " INNER JOIN Lists ON Account.id=Lists.account_id" " GROUP BY Account.id ORDER BY COUNT(Lists.id) DESC") tulos = db.engine.execute(stms) lista = [] for row in tulos: lista.append({"id": row[0], "nimi": row[1], "lists": row[2]}) return lista @staticmethod def users_with_no_lists(): smtu = text( "SELECT DISTINCT Account.id, Account.name FROM Account WHERE NOT EXISTS(" "SELECT DISTINCT Lists.account_id FROM Lists WHERE" " Account.id=Lists.account_id)") tulos = db.engine.execute(smtu) lista = [] for row in tulos: lista.append({"id": row[0], "nimi": row[1]}) return lista
class Viikko(Base): __tablename__ = "viikko" vuosi = db.Column(db.Integer, nullable=False) numero = db.Column(db.Integer, nullable=False) paivat = db.relationship("Paiva", backref='viikko', lazy=True, cascade="all, delete-orphan") def __init__(self, vuosi, numero): self.vuosi = vuosi self.numero = numero @staticmethod def create_7_days(self): maanantai = Paiva("maanantai") maanantai.viikko_id = self.id tiistai = Paiva("tiistai") tiistai.viikko_id = self.id keskiviikko = Paiva("keskiviikko") keskiviikko.viikko_id = self.id torstai = Paiva("torstai") torstai.viikko_id = self.id perjantai = Paiva("perjantai") perjantai.viikko_id = self.id lauantai = Paiva("lauantai") lauantai.viikko_id = self.id sunnuntai = Paiva("sunnuntai") sunnuntai.viikko_id = self.id db.session().add(maanantai) db.session().add(tiistai) db.session().add(keskiviikko) db.session().add(torstai) db.session().add(perjantai) db.session().add(lauantai) db.session().add(sunnuntai) db.session().commit() Paiva.create_24_hours(maanantai) Paiva.create_24_hours(tiistai) Paiva.create_24_hours(keskiviikko) Paiva.create_24_hours(torstai) Paiva.create_24_hours(perjantai) Paiva.create_24_hours(lauantai) Paiva.create_24_hours(sunnuntai) @staticmethod def get_viikot(user): stmt = text("SELECT count(tunti.id), viikko.id, account.username " "From viikko, paiva, tunti, tunti_user, account " "WHERE viikko.id = paiva.viikko_id " "AND paiva.id = tunti.paiva_id " "AND tunti_user.tunti_id = tunti.id " "AND account.id = tunti_user.account_id " "AND tunti.tila > 0 " "GROUP BY viikko.id, account.username") res = db.engine.execute(stmt) response = [] for row in res: if row[2] == user.username: vid = int(row[1]) viikko = Viikko.query.filter(Viikko.id == vid).first() response.append({"viikko":viikko, "tunteja":row[0]}) return response @staticmethod def find_overflow(): stmt = text("SELECT distinct viikko.id, count(tunti.id) FROM tunti, paiva, viikko " "WHERE viikko.id = paiva.viikko_id " "AND paiva.id = tunti.paiva_id " "AND tunti.tila > 1 " "GROUP BY viikko.id " "ORDER BY viikko.id") res = db.engine.execute(stmt) response = [] for row in res: viikko = Viikko.query.filter(Viikko.id == int(row[0])).first() response.append({"vuosi":viikko.vuosi ,"numero":viikko.numero, "over":row[1]}) return response
class UserTest(db.Model): id = db.Column(db.Integer, primary_key=True) answered_questions = db.relationship('AnsweredQuestion', backref='answered_question', lazy=False)
class Task(db.Model): """ A model of tasks """ __tablename__ = "tasks" id = db.Column(db.Integer, primary_key=True) title = db.Column(db.Text()) description = db.Column(db.Text()) created = db.Column(DateTime, default=datetime.utcnow()) done = db.Column(db.Boolean) creator_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False) position = db.Column(db.Text()) priority = db.Column(db.Boolean, default=False) board = db.Column(db.Integer, default=1) is_team_task = db.Column(db.Boolean, default=False) user = relationship('User', back_populates='tasks') team_tasks = relationship('TeamTask', backref='tasks') comments = db.relationship('Comment', backref='task', lazy='dynamic') def __init__(self, **kwargs): super(Task, self).__init__(**kwargs) if self.board is None: self.board = Board.TODO @staticmethod def insert_boards(): """ Inserts the task into one of the three boards todo, doing, or done, if it doesn't have a value defined yet. """ default_board = Board.TODO tasks = Task.query.filter_by(board=None) for i in tasks: i.board = default_board db.session.add(i) db.session.commit() @staticmethod def boards(): boards = {'TODO': Board.TODO, 'DOING': Board.DOING, 'DONE': Board.DONE} return boards def move_left(self): if self.board == Board.DONE: self.set_doing() elif self.board == Board.DOING: self.set_todo() else: pass def move_right(self): if self.board == Board.TODO: self.set_doing() elif self.board == Board.DOING: self.set_done() else: pass def set_doing(self): """ Sets task state to doing """ self.board = Board.DOING self.done = False def set_done(self): """ Sets task to done """ self.board = Board.DONE self.done = True def set_todo(self): self.board = Board.TODO self.done = False def to_json(self): json_task = { 'description': self.description, 'id': self.id, 'position': self.position, 'priority': self.priority, 'title': self.title } return json_task def __repr__(self): return "<Task priority: {}, title: {}, self.description: {}, position: {}, board: {}>".format( self.priority, self.title, self.description, self.position, self.board) def __hash__(self, other): return hash(self.__repr__()) def __eq__(self, other): """ Checks equals """ if isinstance(other, Task): return ((self.id == other.id) and (self.title == other.title) and (self.description == other.description) and (self.creator_id == other.creator_id))
class User(db.Model, UserMixin): """ User模型 """ __tablename__ = 'users' id = db.Column(db.Integer, primary_key=True, index=True) email = db.Column(db.String(64), unique=True, index=True) username = db.Column(db.String(64), unique=True, index=True) password_hash = db.Column(db.String(128)) role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) real_name = db.Column(db.String(64)) location = db.Column(db.String(64)) about_me = db.Column(db.Text(512)) avatar = db.Column(db.String(128), default='avatar.jpg') member_since = db.Column(db.DateTime(), default=datetime.utcnow) posts = db.relationship('Post', backref='author', lazy='dynamic') followed = db.relationship( 'Follow', foreign_keys=[Follow.follower_id], backref=db.backref('follower', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') followers = db.relationship( 'Follow', foreign_keys=[Follow.followed_id], backref=db.backref('followed', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') comments = db.relationship('Comment', backref='author', lazy='dynamic') def __init__(self, **kwargs): """ :summary: User模型初始化函数,生成用户时自动赋予角色,创建用户的数据文件夹,super()函数继承父类, :param kwargs: """ super(User, self).__init__(**kwargs) if self.role is None: self.role = Role.query.filter_by(default=True).first() # 设置password的可写不可读 @property def password(self): raise AttributeError('密码是一个不可读的属性') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) # 验证密码 def verify_password(self, password): """ :summary: 验证密码 :param password: :return: """ return check_password_hash(self.password_hash, password) def generate_reset_token(self, expiration=3600): """ :summary: 生成重设密码的token :param expiration: token失效时间,单位为秒 :return: """ s = TimedJSONWebSignatureSerializer( secret_key=current_app.config['SECRET_KEY'], expires_in=expiration) return s.dumps({'reset_password': self.id}) def reset_password(self, token, new_password): """ :summary: 验证token并重设密码 :param token: :param new_password: :return: """ s = TimedJSONWebSignatureSerializer( secret_key=current_app.config['SECRET_KEY']) try: data = s.loads(token) except: return False if data.get('reset_password') != self.id: return False self.password = new_password db.session.add(self) return True def can(self, permissions): """ :summary: 检查用户是否具有指定的权限,使用位与操作来实现 :param permissions: :return: """ return self.role is not None and ( self.role.permissions & permissions) == permissions def is_administrator(self): """ :summary: 检查管理员权限的功能经常用到,因此使用单独的方法 is_administrator() 实现 :return: """ return self.can(Permission.ADMINISTER) @staticmethod def create_fake(count=100): """ :summary: 创建虚假的用户数据 :param count: 数量 :return: """ from random import seed import forgery_py from sqlalchemy.exc import IntegrityError seed() for i in range(count): u = User( email=forgery_py.internet.email_address(), username=forgery_py.internet.user_name(True), password=forgery_py.lorem_ipsum.word(), real_name=forgery_py.name.full_name(), location=forgery_py.address.city(), about_me=forgery_py.lorem_ipsum.sentence(), member_since=forgery_py.date.date(True) ) db.session.add(u) # 因为用户名和邮箱只能是唯一的,而随机生成的数据可能会重复 # 因此在数据库提交的时候会引发IntegrityError错误使得程序停止运行 # 这里使用try来捕获这个异常并且回滚数据库操作,就能保证正常运行 try: db.session.commit() except IntegrityError: db.session.rollback() @staticmethod def add_self_follows(): """ :summary: 让用户把自己设置为关注者 :return: """ for u in User.query.all(): if not u.is_following(u): u.follow(u) db.session.add(u) db.session.commit() def follow(self, user): """ :summary: 关注一个用户 :param user: :return: """ if not self.is_following(user): f = Follow(follower=self, followed=user) db.session.add(f) def unfollow(self, user): """ :summary: 取消关注一个用户 :param user: :return: """ f = self.followed.filter_by(followed_id=user.id).first() if f: db.session.delete(f) def is_following(self, user): """ :summary: 判断是否正在关注某个用户 :param user: :return: """ f = self.followed.filter_by(followed_id=user.id).first() return f is not None def is_followed_by(self, user): """ :summary: 判断是否被某个用户关注 :param user: :return: """ f = self.followers.filter_by(follower_id=user.id).first() return f is not None @property def followed_posts(self): """ :summary: 获取所关注用户的微博,使用@property装饰器将该方法定义为属性,则在调用时候就可以不用加() :return: """ return Post.query.join( Follow, Follow.followed_id == Post.author_id).filter( Follow.follower_id == self.id)
class User(db.Model): __tablename__ = "account" id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(20), nullable=False) password = db.Column(db.String(30), nullable=False) email = db.Column(db.String(144)) phone = db.Column(db.String(20)) roles = db.relationship("Role", secondary="account_role") registrations = db.relationship("AssignmentRegistration", backref='account', lazy=True) def __init__(self, name, username, password, email, phone): self.name = name self.username = username self.password = password self.email = email self.phone = phone def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return False def is_authenticated(self): return True def role_ok(self, role): for user_role in self.roles: if user_role.name == role: return True @staticmethod def delete_user(user_id): db.engine.connect().execute( "DELETE FROM account_assignment WHERE account_assignment.account_id = :id", id=user_id) db.engine.connect().execute( "DELETE FROM account_role WHERE account_role.account_id = :id", id=user_id) db.engine.connect().execute( "DELETE FROM account WHERE account.id = :id", id=user_id) @staticmethod def switch_roles(role_one, role_two, user_id): r = Role.query.filter_by(name=role_one).first() user_role = UserRole.query.filter_by(account_id=user_id, role_id=r.id).first() db.session.delete(user_role) r = Role.query.filter_by(name=role_two).first() db.session.add(UserRole(user_id, r.id)) db.session().commit()
class User(Base): __tablename__ = "account" name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) items = db.relationship("Item", backref='account', lazy=True) categorys = db.relationship("Category", backref='account', lazy=True) def __init__(self, name, username, password): self.name = name self.username = username self.password = password def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return False def is_authenticated(self): return True @staticmethod def find_users_with_no_items(expired=True): stmt = text("SELECT Account.id, Account.name FROM Account" " LEFT JOIN Item ON Item.account_id = Account.id" " WHERE (Item.expired IS null OR Item.expired IS :done)" " GROUP BY Account.id" " HAVING COUNT(Item.id) = 0").params(done=expired) res = db.engine.execute(stmt) response = [] for row in res: response.append({"id":row[0], "name":row[1]}) return response @staticmethod def show_users_number_of_items(): stmt = text("SELECT Account.username AS nimi, COUNT(Item.name) as lkm" " FROM Account, Item WHERE Account.id = Item.account_id" " GROUP BY nimi" " ORDER BY lkm DESC") res = db.engine.execute(stmt) response = [] for row in res: print(row) response.append({"name":row[0], "lkm":row[1]}) return response @staticmethod def show_users_number_of_categories(): stmt = text("SELECT Account.username AS nimi, COUNT(Category.name) as lkm" " FROM Account, Category WHERE Account.id = Category.account_id" " GROUP BY nimi" " ORDER BY lkm DESC") res = db.engine.execute(stmt) response = [] for row in res: print(row) response.append({"name":row[0], "lkm":row[1]}) return response
class Teams(db.Model): id = db.Column(db.Integer, primary_key=True) team_name = db.Column(db.String(50), nullable=False) sponsor = db.Column(db.String(50), nullable=False) players = db.relationship('Players', cascade="all,delete", backref='team')
class Item(Base): name = db.Column(db.String(144), nullable=False) description = db.Column(db.String(1000), nullable=False) price = db.Column(db.Integer, nullable=False) sold = db.Column(db.Boolean, default=False, nullable=False) date_sold = db.Column(db.DateTime) # References to external tables user = db.relationship('User', foreign_keys='Item.account_id') account_id = db.Column(db.Integer, db.ForeignKey('account.id')) games = db.relationship('Game', secondary=Game_item, lazy='subquery') def __init__(self, name, description, price): self.name = name self.description = description self.price = price @staticmethod def items_unsold_total(): stmt = text(""" SELECT COUNT(*) FROM Item WHERE NOT sold """) res = db.engine.execute(stmt) total = 0 for row in res: total = row[0] return total @staticmethod def item_has_a_game(item): number_of_games = len(item.games) return number_of_games != 0 @staticmethod def delete_game(item_id, game_id): print('deleting games with id ' + str(game_id)) item = Item.query.get(item_id) for game in item.games[:]: print('game id: ' + str(game.id)) if str(game.id) == str(game_id): print('found game!') item.games.remove(game) else: print('no match') @staticmethod def check_game_in_item(item, gameName): newGame = Game.query.filter_by(name=gameName).first() for game in item.games: if str(game.id) == str(newGame.id): return True return False @staticmethod def most_active_city(): stmt = text(''' SELECT Account.location, COUNT(*) as amount FROM Item LEFT JOIN Account ON (Item.account_id = Account.id) WHERE sold GROUP BY Account.location ORDER BY amount LIMIT 1 ''') res = db.engine.execute(stmt) result = [{'Place': 'Kanada', 'Amount': 0}] for row in res: result = [] result.append({'Place': row[0], 'Amount': row[1]}) return result
class Genres(db.Model, UserMixin): id = db.Column(db.Integer, primary_key=True, unique = True) name = db.Column(db.String(100), nullable = False, unique = True) folder_path = db.Column(db.String(100), nullable = False, unique = True) tracks = db.relationship('Tracks', backref = 'genre', lazy = True)
class Artists(db.Model, UserMixin): id = db.Column(db.Integer, primary_key=True, unique = True) name = db.Column(db.String(100), nullable=False, unique= True) default_genre = db.Column(db.String(100),nullable = False, unique= True) tracks = db.relationship('Tracks', backref = 'artist', lazy = True)
class User(UserMixin, db.Model): """ User accont db model """ __tablename__ = "users" id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(100), nullable=False, unique=True) password = db.Column(db.String(200), nullable=False) email = db.Column(db.String(200), nullable=True) about_me = db.Column(db.Text(), nullable=True) confirmed = db.Column(db.Boolean, default=False) created = db.Column(DateTime, default=datetime.utcnow()) last_seen = db.Column(DateTime, default=datetime.utcnow()) role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) avatar_hash = db.Column(db.String(32)) tasks = relationship("Task", back_populates='user', cascade="all, delete-orphan") teams = relationship("Team", secondary="team_members") team_memberships = relationship('TeamMember', back_populates='user', cascade="all, delete-orphan") messages_sent = relationship('Message', foreign_keys='Message.sender_id', backref='author', lazy='dynamic') messages_received = relationship('Message', foreign_keys='Message.recipient_id', backref='recipient', lazy='dynamic') comments = db.relationship('Comment', backref='author', lazy='dynamic') last_message_read_time = db.Column(db.DateTime) # Notifications relationship, not just related to Message-class # necessarily notifications = db.relationship('Notification', backref='user', lazy='dynamic') def __init__(self, **kwargs): # Initializes user roles """ Sets user roles. Sets Admin if email matches """ super(User, self).__init__(**kwargs) if self.role is None: # This will not work, if in the registration form # the user is not instantiated with at least the # email, i.e., u = User(email=form.email.data) # Checks whether the email address of the user matches that of the admin's if self.email == os.getenv('ADMIN'): self.role = Role.query.filter_by( role_name='Administrator').first() if self.role is None: self.role = Role.query.filter_by(default_role=True).first() # Gravatar if self.email is not None and self.avatar_hash is None: self.avatar_hash = self.gravatar_hash() def __repr__(self): return '<User {}>'.format(self.username) def __eq__(self, other): """ Allows comparing user objects """ if isinstance(self, User): return self.id == other.id return False def set_password(self, password): """ Sets user password, hashed """ print("generate_password_hash: ", generate_password_hash(password)) self.password = generate_password_hash(password) def check_password(self, password): """ Checks user password if you want to verify that this method works, you can do this: generated_password = generate_password_hash(password) if self.password == generated_password: print("Passwords match") notice that check_password_hash is a werkzeug method. """ return check_password_hash(self.password, password) def generate_auth_token(self, expiration): """ Generates an auth token for API """ s = Serializer(current_app.config["SECRET_KEY"], expires_in=expiration) return s.dumps({'id': self.id}).decode('utf-8') @staticmethod def verify_auth_token(token): """ Verifies auth token sent by user to API """ s = Serializer(current_app.config['SECRET_KEY']) try: data = s.loads(token) except: return None return User.query.get(data['id']) def generate_confirmation_token(self, expires_in=3600): """ Generates confirmation tokens. Used, for example, when a new user registers and needs to confirm their email address to start using their account. """ return jwt.encode({ 'confirm': self.id, 'exp': time() + expires_in }, current_app.config['SECRET_KEY'], algorithm='HS256').decode('utf-8') # @staticmethod def confirm(self, token): """ Confirms the token given as argument. This is used at least with the email confirmation function, when a user first registers on the site. """ try: id = jwt.decode(token, current_app.config['SECRET_KEY'], algorithms=['HS256'])['confirm'] except: return False if id != self.id: return False try: expiration = jwt.decode(token, current_app.config['SECRET_KEY'], algorithms=['HS256'])['exp'] except: return False # Token vastasi id:tä self.confirmed = True db.session.add(self) # Committed to the db.session # in the auth confirm route return True def get_reset_password_token(self, expires_in=3600): """ Sends a user a password reset token in email """ return jwt.encode( { 'reset_password': self.id, 'exp': time() + expires_in }, current_app.config['SECRET_KEY'], algorithm='HS256').decode('utf-8') @staticmethod def verity_reset_password_token(token): """ Verifies a password token generated for user """ try: id = jwt.decode(token, current_app.config['SECRET_KEY'], algorithms=['HS256'])['reset_password'] except: return return User.query.get(id) # User role check def can(self, perm): """ Retruns true if the requested permission is present in the role. """ return self.role is not None and self.role.has_permission(perm) def is_administrator(self): return self.can(Permission.ADMIN) def gravatar_hash(self): """ Gravatar method will use the stored hash, if available If not, this method will recalculate the gravatar hash """ return hashlib.md5(self.email.lower().encode('utf-8')).hexdigest() def gravatar(self, size=100, default='identicon', rating='g'): """ Fetches an avatar given a person's user account using the gravatar.com service, which turns a person's email address into a profile picture, if they've registered to gravatar.com """ url = 'https://secure.gravatar.com/avatar' # An email address is turned into an md5 hash on gravatar # so the url is https://secure.gravatar.com/avatar/[email address hashed with md5] # hash = hashlib.md5(self.email.lower().encode('utf-8')).hexdigest() hash = self.avatar_hash or self.gravatar_hash() return '{url}/{hash}?s={size}&d={default}&r={rating}'.format( url=url, hash=hash, size=size, default=default, rating=rating) def get_team_role(self, team_id): """ Returns a user's team role, used in layouts, for example """ # tm = TeamMember object tm = self.get_team_member_object(team_id) teamrole = TeamRole.query.filter_by(id=tm.team_role_id).first() return teamrole def can_moderate_team(self, id): """ Checks that user can moderate team """ tm = self.get_team_member_object(id) if tm is None: return False print("tm: ", tm) teamrole = TeamRole.query.filter_by(id=tm.team_role_id).first() if teamrole is None: print("TEAM ROLE OLI MUKAMAS NONE") return False if teamrole is not None and teamrole.has_permission( TeamPermission.MODERATE_TEAM): return True print("Kaatui has_permission kohtaan!!!") return False def can_team(self, id, team_perm): """ Checks if the user has the required permissions to carry out a function on the team site parameters: - id=team id - team_perm = team permission """ tm = self.get_team_member_object(id) if tm is None: return False teamrole = TeamRole.query.filter_by(id=tm.team_role_id).first() if teamrole is None: return False print("Team role has permission: ", teamrole.has_permission(team_perm)) if teamrole is not None and teamrole.has_permission(team_perm): return True print("Kaatui has_permission kohtaan!!!") return False def is_team_role(self, team_id, team_role_name): """ Checks user privileges using team_roles names """ tm = self.get_team_member_object(team_id) if tm is None: return False teamrole_user = TeamRole.query.filter_by(id=tm.team_role_id).first() if teamrole_user is not None and teamrole_user.team_role_name == team_role_name: return True return False def is_team_administrator(self, team_id): tm = self.get_team_member_object(team_id) if tm is None: return False if tm.is_team_administrator(): return True return False def is_team_moderator(self, team_id): tm = self.get_team_member_object(team_id) if tm is None: return False if tm.is_team_moderator(): return True return False def get_team_member_object(self, team_id): """ Finds the role of the user in a given group """ for i in self.team_memberships: if i.team_id is not None and i.team_id == team_id: return i return None def new_messages(self): """ Helper method uses the last_message_read_time to determine whether there are unread messages and returns the number of unread messages """ last_read_time = self.last_message_read_time or datetime(1900, 1, 1) return Message.query.filter_by(recipient=self).filter( Message.timestamp > last_read_time).count() def add_notification(self, name, data): """ Helper method to make it easier to work with notification objects Adds user notifications. Also ensures that if a notification with the same name already exists, it gets removed first. For messages, the main notification is 'unread_message_count' Called wherever, for example, the unread_message_count changes. Here, when receiving a message (send_message() route and message viewing route messages()) """ self.notifications.filter_by(name=name).delete() n = Notification(name=name, payload_json=json.dumps(data), user=self) db.session.add(n) return n
class Enviro(Base): __tablename__ = "enviro" name = db.Column(db.String(144), nullable=False) public = db.Column(db.Boolean, nullable=False) etype = db.Column(db.String(200), nullable=False) descrip = db.Column(db.String(5000), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey("account.id"), nullable=False) account_name = db.Column(db.String(144), nullable=False) monsters = db.relationship("EnviroMonster", back_populates="enviro", cascade="all, delete-orphan") def __init__(self, name, etype, descrip): self.name = name self.etype = etype self.descrip = descrip @staticmethod def local_monsters(enviro_id): stmt = text( "SELECT Monster.id, Monster.name, Monster.public FROM Enviro" " JOIN EnviroMonster ON EnviroMonster.enviro_id = Enviro.id" " JOIN Monster ON Monster.id = EnviroMonster.monster_id" " WHERE EnviroMonster.enviro_id = :enviro" " ORDER BY LOWER(Monster.name)").params(enviro=enviro_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"id": row[0], "name": row[1], "public": row[2]}) return response @staticmethod def addable_monsters(enviro_id, enviro_account_id): stmt = text( "SELECT Monster.id, Monster.name FROM Monster" " WHERE id NOT IN (SELECT Monster.id FROM Enviro" " JOIN EnviroMonster ON EnviroMonster.enviro_id = Enviro.id" " JOIN Monster ON Monster.id = EnviroMonster.monster_id" " WHERE EnviroMonster.enviro_id = :enviro)" " AND Monster.account_id = :account" " ORDER BY LOWER(Monster.name)").params(enviro=enviro_id, account=enviro_account_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"id": row[0], "name": row[1]}) return response @staticmethod def search_admin(state, account_id, name, etype, owner): query = "SELECT Enviro.id, Enviro.name, Enviro.etype, Enviro.public," query += " Enviro.account_id, Enviro.account_name FROM Enviro" query += " WHERE LOWER(Enviro.name) LIKE LOWER(:name)" query += " AND Enviro.etype LIKE :etype" query += " AND LOWER(Enviro.account_name) LIKE LOWER(:owner)" if state == "1": query += " AND Enviro.account_id = :account" elif state == "2": query += " AND Enviro.account_id != :account" query += " ORDER BY LOWER(Enviro.name)" stmt = text(query).params(account=account_id, name='%' + name + '%', etype='%' + etype + '%', owner='%' + owner + '%') res = db.engine.execute(stmt) response = [] for row in res: response.append({ "id": row[0], "name": row[1], "etype": row[2], "public": row[3], "account_id": row[4], "account_name": row[5] }) return response @staticmethod def search(state, account_id, name, etype, owner): query = "SELECT Enviro.id, Enviro.name, Enviro.etype, Enviro.public," query += " Enviro.account_id, Enviro.account_name FROM Enviro" query += " WHERE LOWER(Enviro.name) LIKE LOWER(:name)" query += " AND Enviro.etype LIKE :etype" query += " AND LOWER(Enviro.account_name) LIKE LOWER(:owner)" if state == "1": query += " AND Enviro.account_id = :account" elif state == "2": query += " AND Enviro.account_id != :account AND Enviro.public" else: query += " AND (Enviro.account_id = :account OR Enviro.public)" query += " ORDER BY LOWER(Enviro.name)" stmt = text(query).params(account=account_id, name='%' + name + '%', etype='%' + etype + '%', owner='%' + owner + '%') res = db.engine.execute(stmt) response = [] for row in res: response.append({ "id": row[0], "name": row[1], "etype": row[2], "public": row[3], "account_id": row[4], "account_name": row[5] }) return response
class Match(db.Model): id=db.Column(db.Integer, primary_key=True) date=db.Column(db.Date, default=db.func.current_date()) place=db.Column(db.String(144), nullable=False) winner_id=db.Column(db.Integer) winning_category= db.Column(db.String(144)) comment=db.Column(db.String(100)) creator_id= db.Column(db.Integer, db.ForeignKey('account.id'), nullable= False) points=db.relationship('Points', secondary=matchpoints, backref='match') fighters=db.relationship('Fighter', secondary=matchfighter, backref='match') def __init__(self, date, place, winning_category, winner_id, comment, creator_id): self.date=date self.place = place self.winning_category= winning_category self.winner_id=winner_id self.comment=comment self.creator_id=creator_id @staticmethod def get_fighters(match_id): stmt= text("SELECT id, name, belt FROM Fighter " + "LEFT JOIN matchfighter ON matchfighter.fighter_id = Fighter.id " + "WHERE matchfighter.match_id = :id").params(id=match_id) res=db.engine.execute(stmt) response=[] for row in res: response.append({"id":row[0], "name":row[1], "belt":row[2]}) if len(response)==0: response. append({"id":'<POISTETTU>', "name":'<POISTETTU>', "belt":'<POISTETTU>'}) response. append({"id":'<POISTETTU>', "name":'<POISTETTU>', "belt":'<POISTETTU>'}) elif len(response)==1: response. append({"id":'<POISTETTU>', "name":'<POISTETTU>', "belt":'<POISTETTU>'}) return response @staticmethod def get_matches_by_fighter(fighters): fighter_ids=[] for fighter in fighters: fighter_ids.append(fighter.id) match_ids= db.session().execute(select( [matchfighter.c.match_id], matchfighter.c.fighter_id.in_(fighter_ids), distinct=True )).fetchall() matches=[] for id in match_ids: matches.append(Match.query.get_or_404(id)) return matches @staticmethod def filter_matches(belt, club, clubs, winning_category): club_filter='' for c in clubs: if int(c[0])==int(club): club_filter=c[1] #only category if (belt == '-1' and club == '-1'): stmt= text("SELECT match.id FROM match" + " WHERE match.winning_category=:category" + " ORDER BY match.date DESC").params(category=winning_category) #only belt elif (winning_category == '-1' and club == '-1'): stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE fighter.belt=:belt" + " ORDER BY match.date DESC").params(belt=belt) # only club elif (winning_category == '-1' and belt == '-1'): stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE fighter.club=:club" + " ORDER BY match.date DESC").params(club=club_filter) # win + belt elif club == '-1': stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE match.winning_category=:category" + " AND fighter.belt=:belt" + " ORDER BY match.date DESC").params(category=winning_category, belt=belt) #win + club elif belt == '-1': stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE match.winning_category=:category" + " AND fighter.club=:club" + " ORDER BY match.date DESC").params(category=winning_category, club=club_filter) # club + belt elif winning_category == '-1': stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE fighter.club=:club" + " AND fighter.belt=:belt" + " ORDER BY match.date DESC").params(club=club_filter, belt=belt) #all else: stmt= text("SELECT DISTINCT match.id FROM match" + " JOIN matchfighter ON matchfighter.match_id = match.id" + " JOIN fighter ON fighter.id = matchfighter.fighter_id" + " WHERE match.winning_category=:category" + " AND fighter.club=:club" + " AND fighter.belt=:belt" + " ORDER BY match.date DESC").params(category=winning_category, club=club_filter, belt=belt) res= db.engine.execute(stmt) matches=[] for row in res: matches.append(Match.query.get_or_404(row[0])) return matches
class Recipe(db.Model): __tablename__ = "recipe" id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(60), nullable=False) account_id = db.Column(db.Integer, db.ForeignKey('account.id'), nullable=False) recipeIngredients = db.relationship('Ingredient', secondary=recipe_ingredients, backref=db.backref('recipe_ingredient', lazy='joined')) add_favorites = db.relationship('User', secondary=favorites, backref=db.backref('favorites', lazy='joined')) def __init__(self, name): self.name = name @staticmethod def find_recipes_with_ingredients(): stmt = """SELECT recipe.id, recipe.name FROM recipe LEFT JOIN recipe_ingredients ON recipe_ingredients.recipe_id = recipe.id WHERE (ingredients_id IS NOT null) GROUP BY recipe.id HAVING COUNT(recipe_ingredients.ingredients_id) > 0""" res = db.engine.execute(stmt) response = [] for row in res: response.append({"id": row[0], "name": row[1]}) return response @staticmethod def get_recipe_count(): stmt = """SELECT COUNT(DISTINCT recipe_id) FROM recipe_ingredients""" res = db.engine.execute(stmt) response = [] for row in res: response.append(row[0]) return response @staticmethod def count_ingredient_recipe(): stmt = """SELECT COUNT(recipe_ingredients.ingredients_id), recipe.name FROM recipe_ingredients LEFT JOIN recipe ON recipe.id = recipe_ingredients.recipe_id WHERE (recipe_id IS NOT null) GROUP BY recipe.name HAVING COUNT(recipe_ingredients.ingredients_id) > 0 ORDER BY COUNT(recipe_ingredients.ingredients_id) DESC LIMIT 3""" res = db.engine.execute(stmt) response = [] for row in res: print(row[0]) response.append({"id": row[0], "name": row[1]}) return response
class Test(db.Model): id = db.Column(db.Integer, primary_key=True) questions = db.relationship('Question', backref='test_question', lazy=False)
class Role(db.Model): # follow the best pratice: __tablename__ = 'roles' id = db.Column(db.Integer, primary_key=True) # role info: name = db.Column(db.String(64), unique=True) permissions = db.Column(db.Integer, default=Permission.NONE) # RBAC: users = db.relationship('User', backref='role', lazy=True) def __init__(self, **kwargs): super(Role, self).__init__(**kwargs) if self.permissions is None: self.permissions = Permission.NONE def __repr__(self): return '<Role %r>' % self.name def has_permission(self, perm): return self.permissions & perm == perm def reset_permission(self): self.permissions = Permission.NONE def add_permission(self, perm): if not self.has_permission(perm): self.permissions += perm def remove_permissio(self, perm): if self.has_permission(perm): self.permissions -= perm @staticmethod def init_roles(): roles = { "customer": [Permission.NONE], "barista": [Permission.GET_DRINKS_DETAIL], "manager": [ Permission.GET_DRINKS_DETAIL, Permission.POST_DRINKS, Permission.PATCH_DRINKS, Permission.DELETE_DRINKS, Permission.ADMIN ] } try: for name in roles: # get role: role = Role.query.filter(Role.name == name).first() if role is None: role = Role(name=name) # set permissions: role.reset_permission() for perm in roles[name]: role.add_permission(perm) # commit: db.session.add(role) db.session.commit() # on successful db insert, flash a prompt. print('[Init Roles]: Done.') except: db.session.rollback() # on unsuccessful db insert, flash an error instead. print( '[Init Roles]: An error occurred. Cannot init roles for APP.') finally: db.session.close()
class Kayttaja(db.Model): __tablename__ = "kayttaja" id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) username = db.Column(db.String(144), nullable=False) name = db.Column(db.String(144), nullable=False) address = db.Column(db.String(200), nullable=True) password = db.Column(db.String(144), nullable=False) email = db.Column(db.String(144)) phonenumber = db.Column(db.String(20)) saunat = db.relationship("Sauna", secondary=saunaadmin, cascade="all,delete", back_populates='admins') roles = db.Column(db.String(10), nullable=False) def __init__(self, username, name, phonenumber, address, password, roles): self.username = username self.name = name self.phonenumber = phonenumber self.address = address self.password = password self.roles = roles def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return False def is_authenticated(self): return True def roles(self): return roles @staticmethod def saunat_joihin_varauksia(kayttaja_id): stmt = text( 'SELECT DISTINCT name FROM sauna LEFT JOIN vuoro ON sauna.id = vuoro.sauna_id WHERE vuoro.reserver_id = :kayttajaid' ).params(kayttajaid=kayttaja_id) res = db.engine.execute(stmt) response = [] for row in res: response.append(row[0]) return response @staticmethod def saunavarausten_maara(kayttaja_id): countstmt = text( 'SELECT COUNT(DISTINCT name) FROM sauna LEFT JOIN vuoro ON sauna.id = vuoro.sauna_id WHERE vuoro.reserver_id = :kayttajaid' ).params(kayttajaid=kayttaja_id) count = db.engine.execute(countstmt) count2 = [] for row in count: count2.append(row[0]) return count2[0] @staticmethod def tulot_saunoista(kayttaja_id): stmt = text( "SELECT SUM(price) FROM Vuoro LEFT JOIN Sauna ON Vuoro.sauna_id = Sauna.id LEFT JOIN saunaadmin ON Sauna.id = saunaadmin.sauna_id WHERE saunaadmin.kayttaja_id = :kayttaja_id AND Vuoro.date < CURRENT_DATE AND Vuoro.varattu ='1'" ).params(kayttaja_id=kayttaja_id) res = db.engine.execute(stmt) response = [] for row in res: response.append(row[0]) return response[0]
class Category(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), nullable=False) products = db.relationship("Product", backref="category", lazy=True)
class User(db.Model, UserMixin): '''User table''' __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20), index=True, nullable=False) email = db.Column(db.String(120), index=True, unique=True, nullable=False) email_confirmed = db.Column(db.Boolean, nullable=False, default=False) email_confirmed_on = db.Column(db.DateTime, nullable=True) image_file = db.Column(db.String(20), nullable=False, default='default.jpg') password = db.Column(db.String(60), nullable=False) account_created_on = db.Column(db.DateTime, nullable=False, default=datetime.utcnow) location = db.Column(db.String(100), nullable=True) website_url = db.Column(db.String(200), nullable=True) profile_summary = db.Column(db.String(300), nullable=True) post_count = db.Column(db.Integer, default=0) posts = db.relationship('Post', cascade='all, delete', backref='author', lazy=True) comments = db.relationship('Comment', backref='author', lazy='dynamic') roles = db.relationship('Role', secondary=users_roles, backref=db.backref('users'), lazy='dynamic') def __repr__(self): return '<User {}>'.format(self.username) @property def post_count(self): '''Return post count''' return Post.query.filter(Post.user_id == self.id).count() @property def comment_count(self): '''Return comment count''' return Comment.query.filter(Comment.user_id == self.id).count() def create_password_reset_token(self): '''Create the reset token''' serializer = URLSafeTimedSerializer(environ.get('SECRET_KEY')) return serializer.dumps({'user_id': self.id}) # "Self" paramenter not needed since method is static. @staticmethod def verify_password_reset_token(token): '''Load the reset token''' serializer = URLSafeTimedSerializer(environ.get('SECRET_KEY')) try: user_id = serializer.loads(token)['user_id'] except Exception: return None return User.query.get(user_id)
class User(db.Model): __tablename__ = "account" #association table requires that these are here????? id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) #end of sadly required copy paste. name = db.Column(db.String(144), nullable=False) username = db.Column(db.String(144), nullable=False) password = db.Column(db.String(144), nullable=False) posts = db.relationship("Post", backref='account', cascade="all, delete, delete-orphan", lazy=True) comments = db.relationship("Comment", backref='account',cascade="all, delete, delete-orphan", lazy=True) #relation between users -> followers(following you) and followed(users you follow) followed = db.relationship( 'User', secondary=followers, primaryjoin=(followers.c.follower_id == id), secondaryjoin=(followers.c.followed_id == id), backref=db.backref('followers', lazy='dynamic'), lazy='dynamic') #relation between users and posts liked = db.relationship( "Post", secondary=likes, backref=db.backref("likes",lazy='dynamic'), lazy='dynamic' ) def __init__(self, name, username, password): self.name = name self.username = username self.password = password def get_id(self): return self.id def is_active(self): return True def is_anonymous(self): return False def is_authenticated(self): return True # methods for handling followers and followed users def check_following_status(self, user): return self.followed.filter(followers.c.followed_id == user.id).count() > 0 def add_follow(self, user): if not self.check_following_status(user): self.followed.append(user) def unfollow(self, user): if self.check_following_status(user): self.followed.remove(user) # methods for handling followers and followed users def check_like_status(self, post): return self.liked.filter(likes.c.post_id == post.id).count() > 0 def add_like(self, post): if not self.check_like_status(post): self.liked.append(post) def remove_like(self, post): if self.check_like_status(post): self.liked.remove(post) #method for getting posts by followed users def get_followed_posts(self): return Post.query.join( followers, (followers.c.followed_id == Post.account_id)).filter( followers.c.follower_id == self.id).order_by( Post.date_modified.desc())
class User(db.Model): __tablename__ = 'users' userID = db.Column(db.Integer(), primary_key=True, unique=True, nullable=False) username = db.Column(db.String(45), nullable=False) password_hash = db.Column(db.String(100), nullable=False) confirmed = db.Column(db.Boolean(), default=False, nullable=False) firstname = db.Column(db.String(45)) lastname = db.Column(db.String(45)) email = db.Column(db.String(45), nullable=False) location = db.Column(db.String(64)) about_me = db.Column(db.Text()) member_since = db.Column(db.DateTime(), default=datetime.utcnow) last_seen = db.Column(db.DateTime(), default=datetime.utcnow) posts = db.relationship('Post', backref='author', lazy='dynamic') followed = db.relationship('Follow', foreign_keys=[Follow.followerID], backref=db.backref('follower', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') followers = db.relationship('Follow', foreign_keys=[Follow.followedID], backref=db.backref('followed', lazy='joined'), lazy='dynamic', cascade='all, delete-orphan') comments = db.relationship('Comment', backref='user', lazy='dynamic') roleID = db.Column(db.Integer(), db.ForeignKey('user_role.roleID')) @property def password(self): raise AttributeError('Password is not readable attribute') @password.setter def password(self, password): self.password_hash = generate_password_hash(password) def verify_password(self, password): return check_password_hash(self.password_hash, password) def is_authenticated(self): if isinstance(self, AnonymousUserMixin): return False else: return True def is_active(self): return True def is_anonymous(self): if isinstance(self, AnonymousUserMixin): return True else: return False def get_id(self): return self.userID @login_manager.user_loader def load_user(userID): return User.query.get(int(userID)) def generate_confirmation_token(self): serializer = Serializer(current_app.config['SECRET_KEY']) return serializer.dumps(self.userID) def confirm(self, token, expiration=3600): serializer = Serializer(current_app.config['SECRET_KEY']) try: data = serializer.loads(token, max_age=expiration) except: return False if data != self.userID: return False self.confirmed = True db.session.add(self) db.session.commit() return True def ping(self): self.last_seen = datetime.utcnow() db.session.add(self) db.session.commit() def gravatar(self, size=100, default='identicon', rating='g'): if request.is_secure: url = 'https://secure.gravatar.com/avatar' else: url = 'http://www.gravatar.com/avatar' hash = hashlib.md5(self.email.encode('utf-8')).hexdigest() return '{url}/{hash}?s={size}&d={default}&r={rating}'.format(url=url, hash=hash,size=size, default=default, rating=rating ) def follow(self, user): if not self.is_following(user): f = Follow(follower=self, followed=user) db.session.add(f) db.session.commit() def unfollow(self, user): f = self.followed.filter_by(followedID=user.userID).first() if f: db.session.delete(f) db.session.commit() def is_following(self, user): return self.followed.filter_by(followedID=user.userID).first() is not None def is_followed_by(self, user): return self.followers.filter_by(followerID=user.userID).first() is not None @staticmethod def generate_fake(count=100): from sqlalchemy.exc import IntegrityError from random import seed import forgery_py seed() for i in range(count): role = UserRole.query.get(2) u = User(email=forgery_py.internet.email_address(), username=forgery_py.internet.user_name(True), password=forgery_py.lorem_ipsum.word(), firstname=forgery_py.name.first_name(), lastname=forgery_py.name.last_name(), location=forgery_py.name.location(), about_me=forgery_py.lorem_ipsum.sentence(), member_since=forgery_py.date.date(True), role=role) db.session.add(u) try: db.session.commit() except IntegrityError as err: print(err) db.session.rollback() @staticmethod def verify_auth_token(token): serializer = Serializer(current_app.config['SECRET_KEY']) try: data = serializer.loads(token) except SignatureExpired as ex: flash('Token has expired', 'error') except BadSignature: flash('Signature doesn\'t match', 'error') user = User.query.get(data['id']) return user def __repr__(self): return "<User '{}'>".format(self.username)
class Event(db.Model): id = db.Column(db.Integer, primary_key=True) date_created = db.Column(db.DateTime, default=db.func.current_timestamp()) date_modified = db.Column(db.DateTime, default=db.func.current_timestamp(), onupdate=db.func.current_timestamp()) name = db.Column(db.String(144), nullable=False) location = db.Column(db.String(144), nullable=False) date_time = db.Column(db.DateTime, nullable=False) attendee_max = db.Column(db.Integer, default=0) attendee_min = db.Column(db.Integer, default=0) participants = db.relationship("User", secondary=attends, backref=db.backref("attending", lazy="dynamic")) # declare relationship between event and comment so # removal of an event causes deletion of all related comments comments = db.relationship("Comment", cascade="all, delete", backref="comment") def __init__(self, name, location): self.name = name self.location = location def find_participants_for_event(event_id): stmt = text("SELECT Account.name" " FROM Account" " LEFT JOIN Participation ON Participation.account_id = Account.id" " WHERE Participation.event_id = :event_id").params(event_id=event_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"name":row[0]}) return response # query for list view. Includes information for event and number of # comments and participants for specific event def find_all_events_attend_and_comment_count(): stmt = text("SELECT Event.id, Event.name, Event.location," " COUNT(DISTINCT Comment.id), COUNT(DISTINCT Participation.account_id)" " FROM Event" " LEFT JOIN Comment ON Comment.event_id = Event.id" " LEFT JOIN Participation ON Participation.event_id = Event.id" " GROUP BY Event.id" " ORDER BY Event.date_time ASC") res = db.engine.execute(stmt) response = [] for row in res: response.append({"id":row[0], "name":row[1], "location":row[2], "commentCount":row[3], "attendeeCount":row[4]}) return response def find_all_events_attend_and_comment_count_for_user(account_id): stmt = text("SELECT Event.id, Event.name, Event.location," " COUNT(DISTINCT Comment.id), COUNT(DISTINCT Participation.account_id)" " FROM Event" " LEFT JOIN Comment ON Comment.event_id = Event.id" " LEFT JOIN Participation ON Participation.event_id = Event.id" " WHERE Participation.event_id IN " " ( " " SELECT Event.id " " FROM Event " " LEFT JOIN Participation ON Participation.event_id = Event.id " " WHERE Participation.account_id = :account_id " " ) " " GROUP BY Event.id" " ORDER BY Event.date_time ASC").params(account_id=account_id) res = db.engine.execute(stmt) response = [] for row in res: response.append({"id":row[0], "name":row[1], "location":row[2], "commentCount":row[3], "attendeeCount":row[4]}) return response def participant_count(event_id): stmt = text("SELECT COUNT(*) FROM Participation" " JOIN Event ON Participation.event_id = Event.id" " WHERE Event.id = :event_id").params(event_id=event_id) res = db.engine.execute(stmt) return res.scalar()
class FlicketTicket(Base): __tablename__ = 'flicket_topic' id = db.Column(db.Integer, primary_key=True) title = db.Column(db.String(field_size['title_max_length']), index=True) content = db.Column(db.String(field_size['content_max_length'])) started_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) user = db.relationship(FlicketUser, foreign_keys='FlicketTicket.started_id') date_added = db.Column(db.DateTime()) date_modified = db.Column(db.DateTime()) modified_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) modified = db.relationship(FlicketUser, foreign_keys='FlicketTicket.modified_id') status_id = db.Column(db.Integer, db.ForeignKey(FlicketStatus.id)) current_status = db.relationship(FlicketStatus) category_id = db.Column(db.Integer, db.ForeignKey(FlicketCategory.id)) category = db.relationship(FlicketCategory) assigned_id = db.Column(db.Integer, db.ForeignKey(FlicketUser.id)) assigned = db.relationship(FlicketUser, foreign_keys='FlicketTicket.assigned_id') ticket_priority_id = db.Column(db.Integer, db.ForeignKey(FlicketPriority.id)) ticket_priority = db.relationship(FlicketPriority) posts = db.relationship("FlicketPost", back_populates="ticket") # find all the images associated with the topic uploads = db.relationship( 'FlicketUploads', primaryjoin="and_(FlicketTicket.id == FlicketUploads.topic_id)") # finds all the users who are subscribed to the ticket. subscribers = db.relationship('FlicketSubscription', order_by='FlicketSubscription.user_def') # finds all the actions associated with the post actions = db.relationship( 'FlicketAction', primaryjoin="and_(FlicketTicket.id == FlicketAction.ticket_id)") @property def num_replies(self): n_replies = FlicketPost.query.filter_by(ticket_id=self.id).count() return n_replies @property def id_zfill(self): return str(self.id).zfill(5) def is_subscribed(self, user): for s in self.subscribers: if s.user == user: return True return False def get_subscriber_emails(self): """ Function to return a list of email addresses of subscribed users. :return: """ emails = list() for user in self.subscribers: emails.append(user.user.email) return emails @staticmethod def query_tickets(form, **kwargs): """ Returns a filtered query and modified form based on form submission :param form: :param kwargs: :return: """ ticket_query = FlicketTicket.query for key, value in kwargs.items(): if key == 'status' and value: ticket_query = ticket_query.filter( FlicketTicket.current_status.has( FlicketStatus.status == value)) form.status.data = FlicketStatus.query.filter_by( status=value).first().id if key == 'category' and value: ticket_query = ticket_query.filter( FlicketTicket.category.has( FlicketCategory.category == value)) form.category.data = FlicketCategory.query.filter_by( category=value).first().id if key == 'department' and value: department_filter = FlicketDepartment.query.filter_by( department=value).first() ticket_query = ticket_query.filter( FlicketTicket.category.has( FlicketCategory.department == department_filter)) form.department.data = department_filter.id if key == 'user_id' and value: ticket_query = ticket_query.filter_by(assigned_id=int(value)) user = FlicketUser.query.filter_by(id=value).first() form.username.data = user.username if key == 'content' and value: # search the titles form.content.data = key f1 = FlicketTicket.title.ilike('%' + value + '%') f2 = FlicketTicket.content.ilike('%' + value + '%') f3 = FlicketTicket.posts.any( FlicketPost.content.ilike('%' + value + '%')) ticket_query = ticket_query.filter(f1 | f2 | f3) ticket_query = ticket_query.order_by(FlicketTicket.id.desc()) return ticket_query, form @staticmethod def my_tickets(ticket_query): """ Function to return all tickets created by or assigned to user. :return: """ ticket_query = ticket_query.filter( (FlicketTicket.started_id == g.user.id) | (FlicketTicket.assigned_id == g.user.id)).order_by( FlicketTicket.id.desc()) return ticket_query @staticmethod def form_redirect(form, page, url='flicket_bp.tickets'): """ :param form: :param page: :param url: :return: """ department = '' category = '' status = '' user_id = '' user = FlicketUser.query.filter_by(username=form.username.data).first() if user: user_id = user.id # convert form inputs to it's table title if form.department.data: department = FlicketDepartment.query.filter_by( id=form.department.data).first().department if form.category.data: category = FlicketCategory.query.filter_by( id=form.category.data).first().category if form.status.data: status = FlicketStatus.query.filter_by( id=form.status.data).first().status redirect_url = url_for(url, content=form.content.data, page=page, department=department, category=category, status=status, user_id=user_id) return redirect_url def __repr__(self): return 'Class FlicketTicket: id={}, title={}, status={}, assigned={},'.format( self.id, self.title, self.current_status, self.assigned)
class Feature(Base): user_id = db.Column(db.Integer, nullable=True) title = db.Column(db.String, nullable=False) description = db.Column(db.String, nullable=False) category_id = db.Column(db.Integer, db.ForeignKey(FeatureCategory.id), index=True) category = db.relationship("FeatureCategory", foreign_keys=[category_id], lazy="joined") def __init__(self, title, description, userId): self.title = title self.description = description self.user_id = userId self.category_id = FeatureCategory.get_first().id @property def authorized_to_modify(self): return current_user.is_admin or current_user.id == self.user_id @property def current_user_liked(self): count = db.session.query(Like).filter_by(user_id=current_user.id, feature_id=self.id).count() return count > 0 @staticmethod def count_by_category(category_id): return Feature.query.filter_by(category_id=category_id).count() @staticmethod def get_paginated(page_num, page_size, category_id): skip_count = (page_num - 1) * page_size current_user_id = -1 if not current_user.is_authenticated else current_user.id stmt = text(""" SELECT Feature.*, (SELECT COUNT(*) FROM "like" WHERE feature_id=Feature.id) AS like_count, (SELECT COUNT(*) FROM "like" WHERE feature_id=Feature.id AND user_id=:current_user ) AS current_user_liked FROM Feature WHERE category_id=:category_id ORDER BY like_count DESC LIMIT :page_size OFFSET :skip_count """).params(current_user=current_user_id, category_id=category_id, skip_count=skip_count, page_size=page_size) res = db.engine.execute(stmt) response = [] keys = res.keys() for row in res: # When no Features match query, there will still be one row with bunch of None values if (row[0] is None): break feature = {} for i, key in enumerate(keys): feature[key] = row[i] if (current_user.is_authenticated): feature[ "authorized_to_modify"] = current_user.is_admin or feature[ "user_id"] == current_user.id else: feature["authorized_to_modify"] = False response.append(feature) return response
class Computer(db.Model): id = db.Column(db.Integer, primary_key=True) serial = db.Column(db.String) person_id = db.Column(db.Integer, db.ForeignKey('person.id')) person = db.relationship('Person', backref=db.backref('computers'))
class MenuStoreRelation(db.Model, TimeStampMixin, SerializableModelMixin): id = db.Column(db.Integer, primary_key=True) menu_id = db.Column(db.Integer, db.ForeignKey('menu.id')) menu = db.relationship('Menu', foreign_keys=[menu_id]) store_id = db.Column(db.Integer, db.ForeignKey('store.id')) store = db.relationship('Store', foreign_keys=[store_id])