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])