class Answer(db.Model):
id = db.Column(db.Integer, primary_key=True)
content = db.Column(db.String(144), nullable=False)
createdOn = db.Column(db.DateTime, default=datetime.datetime.utcnow)
user_id = db.Column(db.Integer,
db.ForeignKey('account.id'),
nullable=False)
post_id = db.Column(db.Integer, db.ForeignKey('post.id'), nullable=False)
def __init__(self, content, user_id, post_id):
self.content = content
self.user_id = user_id
self.post_id = post_id
@staticmethod
def deleteUnconnectedAnswers():
stmt = text("SELECT Answer.id, Answer.post_id FROM Answer")
res = db.engine.execute(stmt)
for row in res:
stmt = text("SELECT * FROM Post where Post.id = :postId").params(
postId=row[1])
result = db.engine.execute(stmt)
if (result == ""):
stmt = text("Delete FROM Answer where Answer.id = :answerId"
).params(answerId=row[0])
db.engine.execute(stmt)
return True
class Group(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(144), nullable=False)
def __init__(self, name):
self.name = name
@staticmethod
def isEmpty(groupId):
stmt = text("SELECT COUNT (*) FROM Post"
" WHERE Post.group_id = :groupId").params(groupId=groupId)
res = db.engine.execute(stmt)
if (res == 0):
return True
return False
@staticmethod
def getUsers(groupId):
stmt = text("SELECT u.id, u.username "
" FROM account u, \"group\" g, groupuser gu"
" WHERE gu.user_id = u.id AND g.id = :groupId").params(groupId=groupId)
res = db.engine.execute(stmt)
return [{"id": row[0], "username": row[1]} for row in res]
class User(db.Model):
__tablename__ = "account"
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(144), nullable=False)
password = db.Column(db.String(144), nullable=False)
messageCount = db.Column(db.Integer)
admin = db.Column(db.Boolean, nullable=False)
groupusers = db.relationship("Groupuser", backref='account', lazy=True)
def __init__(self, username, password):
self.username = username
self.password = password
self.messageCount = 0
if (username == "admin"):
self.admin = True
else:
self.admin = False
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 is_admin(self):
return self.admin
@staticmethod
def getGroups(userId):
stmt = text(
"SELECT g.id, g.name "
" FROM \"group\" g, account u, groupuser gu"
" WHERE gu.user_id = :userId AND g.id = gu.group_id").params(
userId=userId)
res = db.engine.execute(stmt)
return [{"id": row[0], "name": row[1]} for row in res]
@staticmethod
def getMessageCount(userId):
stmt = text("SELECT COUNT (*) FROM Post"
" WHERE Post.user_id = :userId").params(userId=userId)
postCount = db.engine.execute(stmt).fetchone()
stmt = text("SELECT COUNT (*) FROM Answer"
" WHERE Answer.user_id = :userId").params(userId=userId)
answerCount = db.engine.execute(stmt).fetchone()
return int(postCount[0]) + int(answerCount[0])
class Groupuser(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('account.id'),
nullable=False)
group_id = db.Column(db.Integer, db.ForeignKey('group.id'),
nullable=False)
def __init__(self, userId, groupId):
self.user_id = userId
self.group_id = groupId
class Area(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(144), nullable=False)
messageCount = db.Column(db.Integer, default=0)
def __init__(self, name):
self.name = name
@staticmethod
def getMessageCount(areaId):
stmt = text("SELECT COUNT (*) FROM Post"
" WHERE Post.area_id = :areaId").params(areaId=areaId)
res = db.engine.execute(stmt).fetchone()
return int(res[0])
class Post(db.Model):
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(144), nullable=False)
content = db.Column(db.String(144), nullable=False)
createdOn = db.Column(db.DateTime, default=datetime.datetime.utcnow)
messageCount = db.Column(db.Integer, default=0)
user_id = db.Column(db.Integer,
db.ForeignKey('account.id'),
nullable=False)
area_id = db.Column(db.Integer, db.ForeignKey('area.id'))
group_id = db.Column(db.Integer, db.ForeignKey('group.id'))
def __init__(self, title, content, user_id, area_id, group_id):
self.title = title
self.content = content
self.user_id = user_id
self.area_id = area_id
self.group_id = group_id
@staticmethod
def getMessageCount(postId):
stmt = text("SELECT COUNT (*) FROM Answer"
" WHERE Answer.post_id = :postId").params(postId=postId)
res = db.engine.execute(stmt).fetchone()
return int(res[0])
@staticmethod
def getRelatedAnswers(postId):
stmt = text(
"SELECT * FROM Answer"
" WHERE (Answer.post_id = :postId) ORDER BY answer.createdOn ASC"
).params(postId=postId)
res = db.engine.execute(stmt)
return res
@staticmethod
def deleteGroupPosts(groupId):
stmt = text(
"DELETE FROM Post"
" WHERE (Post.group_id = :groupId)").params(groupId=groupId)
db.engine.execute(stmt)
Answer.deleteUnconnectedAnswers()
return True
@staticmethod
def deleteAreaPosts(areaId):
stmt = text("DELETE FROM Post"
" WHERE (Post.area_id = :areaId)").params(areaId=areaId)
db.engine.execute(stmt)
Answer.deleteUnconnectedAnswers()
return True