class Tag(db.Model):
__tablename__ = 'tag'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
questions = db.relationship('Question',
secondary='tags_questions',
back_populates='tags')
#FIXME: may add this later
#created_by_id = db.Column(db.Integer, db.ForeignKey('Admin.id'))
#created_by = db.relationship('Admin', back_populates='tags')
def delete(self):
default_tag = Tag.query.get(1)
questions = self.questions[:]
for q in questions:
q.tags.remove(self)
if len(q.tags == 0):
q.tags.append(default_tag)
db.session.delete(self)
db.session.commit()
class Permission(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
roles = db.relationship('Role',
secondary='role_permission',
back_populates='permissions')
class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
users = db.relationship('UserCommon', back_populates='role')
permissions = db.relationship('Permission',
secondary='role_permission',
back_populates='roles')
@staticmethod
def init_role():
roles_permissions_map = {
'Locked': ['VISIT'],
'User': ['VISIT', 'ASK', 'ANSWER', 'REPLY'],
'Moderator': ['VISIT', 'ASK', 'ANSWER', 'REPLY', 'MODERATE'],
'Administrator':
['VISIT', 'ASK', 'ANSWER', 'REPLY', 'MODERATE', 'ADMINISTER']
}
for role_name in roles_permissions_map:
role = Role.query.filter_by(name=role_name).first()
if role is None:
role = Role(name=role_name)
db.session.add(role)
role.permission = []
for permission_name in roles_permissions_map[role_name]:
permission = Permission.query.filter_by(
name=permission_name).first()
if permission is None:
permission = Permission(name=permission_name)
db.session.add(permission)
#important
role.permissions.append(permission)
db.session.commit()
class Review(db.Model):
__tablename__ = config['TABLE_REVIEWS']
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
story = db.Column(db.String(10), index=True, nullable=False)
author = db.Column(db.String(100), index=True, nullable=False)
score = db.Column(db.Integer, index=True, nullable=False)
review = db.Column(db.Integer, index=True, nullable=False)
class SkippedRanking(db.Model):
__tablename__ = 'skipped_rankings'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
r1 = db.Column(db.Integer, db.ForeignKey('regions.id'), nullable=False)
r2 = db.Column(db.Integer, db.ForeignKey('regions.id'), nullable=False)
user = db.relationship('User',
backref=db.backref('skipped_rankings', lazy=True))
class Region(db.Model):
__tablename__ = 'regions'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
shapefile_id = db.Column(db.String(255), nullable=False)
cluster_id = db.Column(db.Integer,
db.ForeignKey('clusters.id'),
nullable=False)
cluster = db.relationship('Cluster',
backref=db.backref('regions', lazy=True))
class KnownRegion(db.Model):
__tablename__ = 'user_known_regions'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
region_id = db.Column(db.Integer,
db.ForeignKey('regions.id'),
nullable=False)
user = db.relationship('User',
backref=db.backref('known_regions', lazy=True))
bcluster = db.relationship('Region',
backref=db.backref('known_by_users', lazy=True))
class Answer(db.Model):
__tablename__ = 'answer'
id = db.Column(db.Integer, primary_key=True)
answer = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow)
#FIXME: do this later
#last_edit_timestamp = db.Column(db.DateTime)
question_id = db.Column(db.Integer, db.ForeignKey('question.id'))
question = db.relationship('Question', back_populates='answers')
author_id = db.Column(db.Integer, db.ForeignKey('user_common.id'))
author = db.relationship('UserCommon', back_populates='answers')
comments = db.relationship('Comment',
back_populates='answer',
cascade='all, delete-orphan')
class Comment(db.Model):
__tablename__ = config['TABLE_COMMENTS']
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
story = db.Column(db.String(10), index=True, nullable=False)
author = db.Column(db.String(100), index=True, nullable=False)
parent_comment = db.Column(db.Integer,
db.ForeignKey(__tablename__ + '.id'))
comment = db.Column(db.Text, nullable=False)
children = db.relationship('Comment')
def __repr__(self):
return '<Story %r>' % self.url
@classmethod
def by_url(cls, url):
comments = cls.query.filter_by(story=url)
return comments
class User(db.Model):
__tablename__ = config['TABLE_USERS']
id = db.Column(db.Integer, primary_key=True, unique=True, index=True)
username = db.Column(db.String(100), unique=True, nullable=False)
password = db.Column(PasswordType(max_length=255, schemes=['bcrypt']),
nullable=False)
salt = db.Column(db.String(16), nullable=False)
rank = db.Column(ChoiceType(UserRank, impl=db.Integer()),
nullable=False,
default=UserRank.NORMAL)
created_time = db.Column(db.DateTime(timezone=False),
default=datetime.datetime.utcnow)
last_login_date = db.Column(db.DateTime(timezone=False),
default=datetime.datetime.utcnow,
nullable=True)
last_login_ip = db.Column(db.Binary(length=16),
default=None,
nullable=True)
def __init__(self, username, password, salt, last_ip):
self.username = username
self.password = password.join(salt)
self.salt = salt
self.last_login_ip = last_ip
def __repr__(self):
return '<User %r>' % self.username
def validate_authorization(self, password):
''' Returns a boolean for whether the user can be logged in '''
checks = [
# Password must match
password == self.password
# Reject inactive and banned users
]
return all(checks)
@classmethod
def by_id(cls, id):
return cls.query.get(id)
@classmethod
def by_username(cls, username):
user = cls.query.filter_by(username=username).first()
return user
@classmethod
def update_last_login(cls, id, update):
cls.query.filter_by(id=id).update(update)
class Question(db.Model):
__tablename__ = "question"
id = db.Column(db.Integer, primary_key=True)
question = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
#FIXME: do this later
#last_edit_timestamp = db.Column(db.DateTime, index=True)
collectors = db.relationship('UserCommon',
secondary='collections_questions',
back_populates='collections')
author_id = db.Column(db.Integer, db.ForeignKey('user_common.id'))
author = db.relationship('UserCommon', back_populates='questions')
answers = db.relationship('Answer',
back_populates='question',
cascade='all, delete-orphan')
tags = db.relationship('Tag',
secondary='tags_questions',
back_populates='questions')
class Story(db.Model):
__tablename__ = config['TABLE_STORIES']
id = db.Column(db.Integer, primary_key=True, autoincrement=True)
url = db.Column(db.String(10), unique=True, nullable=False)
author = db.Column(db.String(100), index=True, nullable=False)
date_posted = db.Column(db.DateTime,
default=datetime.datetime.utcnow,
nullable=False)
title = db.Column(db.String(100), nullable=False)
story = db.Column(db.Text, nullable=False)
synopsis = db.Column(db.String(255), nullable=False)
votes = db.Column(db.Integer, nullable=False, autoincrement=False)
def __init__(self, url, author, title, story, synopsis):
self.url = url
self.author = author
self.title = title
self.story = story
self.synopsis = synopsis
self.votes = 0
def __repr__(self):
return '<Story %r>' % self.url
@classmethod
def by_url(cls, url):
story = cls.query.filter_by(url=url).first()
return story
@classmethod
def list_by_username(cls, username):
stories = cls.query.filter_by(author=username)
return stories
@classmethod
def count_by_username(cls, username):
count = cls.query.filter_by(author=username).count()
return count
@classmethod
def get_list(cls):
stories = cls.query.all()
return stories
@classmethod
def delete_by_username(cls, username):
cls.query.filter_by(author=username).delete()
class User(db.Model):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200), nullable=False)
age = db.Column(db.Integer, nullable=False)
gender = db.Column(db.String(20), nullable=False)
occupation = db.Column(db.String(200), nullable=False)
created_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
def __repr__(self):
return '<User %d %r>' % (self.id, self.name)
class Ranking(db.Model):
__tablename__ = 'comparisons'
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
date = db.Column(db.DateTime, default=datetime.datetime.utcnow)
poorest = db.Column(db.Integer,
db.ForeignKey('regions.id'),
nullable=False)
richest = db.Column(db.Integer,
db.ForeignKey('regions.id'),
nullable=False)
rejudged = db.Column(db.Boolean, default=False)
user = db.relationship('User', backref=db.backref('rankings', lazy=True))
def __repr__(self):
return '<Ranking {} {} Poorest {} Richest {}>'.format(
self.date, self.user_id, self.poorest, self.richest)
class Comment(db.Model):
__tablename__ = 'comment'
id = db.Column(db.Integer, primary_key=True)
comment = db.Column(db.Text)
timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
author_id = db.Column(db.Integer, db.ForeignKey('user_common.id'))
author = db.relationship('UserCommon', back_populates='comments')
answer_id = db.Column(db.Integer, db.ForeignKey('answer.id'))
answer = db.relationship('Answer', back_populates='comments')
replied_id = db.Column(db.Integer, db.ForeignKey('comment.id'))
replied = db.relationship('Comment',
back_populates='replies',
remote_side=[id])
replies = db.relationship('Comment',
back_populates='replied',
cascade='all, delete-orphan')
class Cluster(db.Model):
__tablename__ = 'clusters'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255), nullable=False)
class Bracket(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)
points = db.Column(db.Integer, nullable=False)
max_points = db.Column(db.Integer, nullable=False)
name = db.Column(db.String(30), nullable=False)
year = db.Column(db.String(5), nullable=False)
game1 = db.Column(db.String(30), nullable=False)
game2 = db.Column(db.String(30), nullable=False)
game3 = db.Column(db.String(30), nullable=False)
game4 = db.Column(db.String(30), nullable=False)
game5 = db.Column(db.String(30), nullable=False)
game6 = db.Column(db.String(30), nullable=False)
game7 = db.Column(db.String(30), nullable=False)
game8 = db.Column(db.String(30), nullable=False)
game9 = db.Column(db.String(30), nullable=False)
game10 = db.Column(db.String(30), nullable=False)
game11 = db.Column(db.String(30), nullable=False)
game12 = db.Column(db.String(30), nullable=False)
game13 = db.Column(db.String(30), nullable=False)
game14 = db.Column(db.String(30), nullable=False)
game15 = db.Column(db.String(30), nullable=False)
w_goals = db.Column(db.Integer)
l_goals = db.Column(db.Integer)
class User(db.Model, UserMixin):
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(60), unique=True, nullable=False)
password = db.Column(db.String(60), nullable=False)
db.session.add(role)
role.permission = []
for permission_name in roles_permissions_map[role_name]:
permission = Permission.query.filter_by(
name=permission_name).first()
if permission is None:
permission = Permission(name=permission_name)
db.session.add(permission)
#important
role.permissions.append(permission)
db.session.commit()
role_permission = db.Table(
'role_permission',
db.Column('role_id', db.Integer, db.ForeignKey('role.id')),
db.Column('permission_id', db.Integer, db.ForeignKey('permission.id')))
class Permission(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
roles = db.relationship('Role',
secondary='role_permission',
back_populates='permissions')
class Comment(db.Model):
__tablename__ = 'comment'
id = db.Column(db.Integer, primary_key=True)
class UserCommon(db.Model, UserMixin):
#__abstract__=True
__tablename__ = "user_common"
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(30))
username = db.Column(db.String(30), unique=True)
email = db.Column(db.String(50), unique=True)
password_hash = db.Column(db.String(128))
about = db.Column(db.Text)
joinDate = db.Column(db.DateTime, default=datetime.utcnow, index=True)
is_confirmed = db.Column(db.Boolean, default=False)
avatar_s = db.Column(db.String(64))
avatar_m = db.Column(db.String(64))
avatar_l = db.Column(db.String(64))
collections = db.relationship('Question',
secondary='collections_questions',
back_populates='collectors')
questions = db.relationship('Question',
back_populates='author',
cascade='all, delete-orphan')
answers = db.relationship('Answer', back_populates='author')
comments = db.relationship('Comment', back_populates='author')
role_id = db.Column(db.Integer, db.ForeignKey('role.id'))
role = db.relationship('Role', back_populates='users')
def __init__(self, **kwargs):
super(UserCommon, self).__init__(**kwargs)
self.generate_avatar()
self.set_role()
def set_role(self):
if self.role is None:
self.role = Role.query.filter_by(name='User').first()
db.session.commit()
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def validate_password(self, password):
return check_password_hash(self.password_hash, password)
def can(self, permission_name):
all = Permission.query.all()
print("-----all: ", all)
permission = Permission.query.filter_by(name=permission_name).first()
return permission is not None and self.role is not None and permission in self.role.permissions
def generate_avatar(self):
avatar = Identicon()
filenames = avatar.generate(text=self.username)
self.avatar_s = filenames[0]
self.avatar_m = filenames[1]
self.avatar_l = filenames[2]
db.session.commit()