class Tracker(db.Model):
__tablename__ = 'tracker'
id = db.Column(db.Integer, primary_key=True)
tid = db.Column(db.Integer)
datetime = db.Column(AwareDateTime())
table = db.Column(db.String(255))
inserted = db.Column(db.Boolean())
deleted = db.Column(db.Boolean())
data_before_change = db.Column(db.Integer)
user = db.Column(db.String(255))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Tracker, self).__init__(**kwargs)
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'datetime'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (Tracker.datetime.ilike(search_query),
Tracker.user.ilike(search_query))
return or_(*search_chain)
class EmotionDisagreement(db.Model):
__tablename__ = 'view_emotion_disagreement'
sense_id = db.Column(db.Integer, primary_key=True)
lemma = db.Column(db.String(255))
status = db.Column(db.Integer)
owner0 = db.Column(db.String(255))
owner1 = db.Column(db.String(255))
markedness0 = db.Column(db.String(10))
markedness1 = db.Column(db.String(10))
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'sense_id'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search_by_form_filter(cls, sense_id, user):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if sense_id is not '':
search_chain.append(EmotionDisagreement.sense_id == sense_id)
if user is not '':
search_chain.append(
or_(EmotionDisagreement.owner0 == user,
EmotionDisagreement.owner1 == user))
return and_(*search_chain)
class User(db.Model, UserMixin, SaveDeleteMixin, SerializationMixin):
__tablename__ = 'users'
__serializer__ = UserSerializer
id = Column(db.Integer, primary_key=True)
name = Column(db.String(STRING_LEN), nullable=False)
email = Column(db.String(STRING_LEN), nullable=False, unique=True)
# activation_key = Column(db.String(STRING_LEN))
created_time = Column(db.DateTime, default=get_current_time)
age = Column(db.Integer)
phone = Column(db.String(STRING_LEN))
sex_code = db.Column(db.Integer)
_password = Column('password', db.String(STRING_LEN), nullable=False)
@property
def sex(self):
return SEX_TYPE.get(self.sex_code)
def _get_password(self):
return self._password
def _set_password(self, password):
self._password = generate_password_hash(password)
# Hide password encryption by exposing password field only.
password = db.synonym('_password',
descriptor=property(_get_password, _set_password))
def check_password(self, password):
if self.password is None:
return False
return check_password_hash(self.password, password)
@classmethod
def authenticate(cls, login, password):
user = cls.query.filter(User.email == login).first()
if user:
authenticated = user.check_password(password)
else:
authenticated = False
return user, authenticated
@classmethod
def get_by_id(cls, user_id):
return cls.query.filter_by(id=user_id).first_or_404()
class Morphology(db.Model):
__tablename__ = 'morphology'
id = db.Column(db.Integer, primary_key=True)
lexicalunit_id = db.Column(db.Integer)
word_form = db.Column(db.String(255))
morphological_tag = db.Column(db.String(255))
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'id'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search_by_sense_id(cls, sense_id):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if sense_id is not '':
search_chain.append(Morphology.lexicalunit_id == sense_id)
return and_(*search_chain)
class AdminQuery(db.Model):
__tablename__ = 'admin_query'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(255))
query_text = db.Column(db.Text())
enable_autorun = db.Column(db.Boolean())
type = db.Column('type',
db.Enum(AdminQueryTypeEnum),
default=AdminQueryTypeEnum.statistic)
required_words = ('select', 'from')
banned_words = (
'update ',
'insert ',
'delete ',
'create ',
'start ',
'begin ',
'commit ',
'drop ',
'alter ',
'database',
'show ',
'describe ',
'into ',
'flush ',
'add ',
'modify ',
'load ',
'truncate ',
'mysql',
'grant ',
'use ',
'root',
'localhost',
'commit',
)
special_cases = [
# don't let passwords leak ... probably needs more extensive protection
lambda q: not ('user' in q and 'password' in q),
lambda q: not ('user' in q and '*' in q),
# protect against setting variable but let synset queries be valid
# it's still not safe in many cases, but hey...
lambda q: not ('set' in q and 'synset' not in q),
]
cache_key_template = "ADMIN_QUERY_RESULT_{id}"
@validates('query_text')
def validate_query_text(self, key, query):
q_lower = query.lower()
if all([word in q_lower for word in self.required_words]) and all([
word not in q_lower for word in self.banned_words
]) and all([case(q_lower) for case in self.special_cases]):
return query
else:
class Emotion(db.Model):
__tablename__ = 'emotion'
id = db.Column(db.Integer, primary_key=True)
sense_id = db.Column('lexicalunit_id', db.Integer)
emotions = db.Column(db.String(255))
valuations = db.Column(db.String(255))
markedness = db.Column(db.String(255))
status = db.Column('unitStatus', db.Integer)
example1 = db.Column(db.String(255))
example2 = db.Column(db.String(255))
owner = db.Column(db.String(255))
creation_date = db.Column(AwareDateTime())
super_annotation = db.Column('super_anotation', db.Integer)
class Sense(db.Model):
__tablename__ = 'lexicalunit'
id = db.Column('ID', db.Integer, primary_key=True)
lemma = db.Column(db.String(255))
domain = db.Column(db.Integer)
pos = db.Column(db.Integer)
status = db.Column(db.Integer)
comment = db.Column(db.Text(''))
owner = db.Column(db.String(255))
variant = db.Column(db.Integer)
error_comment = db.Column(db.String(255))
verb_aspect = db.Column(db.Integer)
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(Sense, self).__init__(**kwargs)
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'id'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (
Sense.lemma.ilike(search_query),
Sense.pos.ilike(search_query),
Sense.domain.ilike(search_query),
Sense.variant.ilike(search_query),
Sense.comment.ilike(search_query),
Sense.owner.ilike(search_query),
Sense.status.ilike(search_query),
)
return or_(*search_chain)
class TrackerSenseHistory(db.Model):
__tablename__ = 'view_tracker_sense_history'
id = db.Column(db.Integer, primary_key=True)
datetime = db.Column(AwareDateTime())
user = db.Column(db.String(255))
operation = db.Column(db.String(255))
key_id = db.Column('k_id', db.Integer)
key_lemma = db.Column('k_lemma', db.String(255))
key_pos = db.Column('key_pos', db.Integer)
key_status = db.Column('key_status', db.Integer)
u1_lemma = db.Column('tu1_lemma', db.String(255))
u1_variant = db.Column('tu1_variant', db.Integer)
u1_domain = db.Column('tu1_domain', db.Integer)
u1_pos = db.Column('tu1_pos', db.Integer)
u1_status = db.Column('tu1_status', db.Integer)
u1_comment = db.Column('tu1_comment', db.String(255))
u1_owner = db.Column('tu1_owner', db.String(255))
u2_lemma = db.Column('tu2_lemma', db.String(255))
u2_variant = db.Column('tu2_variant', db.Integer)
u2_domain = db.Column('tu2_domain', db.Integer)
u2_pos = db.Column('tu2_pos', db.Integer)
u2_status = db.Column('tu2_status', db.Integer)
u2_comment = db.Column('tu2_comment', db.String(255))
u2_owner = db.Column('tu2_owner', db.String(255))
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'datetime'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search_by_form_filter(cls, date_from, date_to, sense_id, user, pos,
status):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if date_from is not '':
search_chain.append(
func.DATE(TrackerSenseHistory.datetime) >= date_from)
if date_to is not '':
search_chain.append(
func.DATE(TrackerSenseHistory.datetime) <= date_to)
if sense_id is not '':
search_chain.append(TrackerSenseHistory.key_id == sense_id)
if user is not '':
search_chain.append(TrackerSenseHistory.user == user)
if pos is not '':
search_chain.append(TrackerSenseHistory.key_pos == pos)
if status is not '':
search_chain.append(TrackerSenseHistory.key_status == status)
return and_(*search_chain)
class TrackerSenseRelationsHistory(db.Model):
__tablename__ = 'view_tracker_sense_relations_history'
id = db.Column(db.Integer, primary_key=True)
datetime = db.Column(AwareDateTime())
user = db.Column(db.String(255))
operation = db.Column(db.String(255))
source_id = db.Column(db.Integer)
source_unitstr = db.Column(db.Text(''))
relation_id = db.Column(db.Integer)
relation_name = db.Column(db.String(255))
target_id = db.Column(db.Integer)
target_unitstr = db.Column(db.Text(''))
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'id'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (
TrackerSenseRelationsHistory.id.ilike(search_query),
TrackerSenseRelationsHistory.datetime.ilike(search_query),
TrackerSenseRelationsHistory.user.ilike(search_query))
return or_(*search_chain)
@classmethod
def search_by_form_filter(cls, date_from, date_to, sense_id, user,
relation_type):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
search_chain = list()
if date_from is not '':
search_chain.append(
func.DATE(TrackerSenseRelationsHistory.datetime) >= date_from)
if date_to is not '':
search_chain.append(
func.DATE(TrackerSenseRelationsHistory.datetime) <= date_to)
if sense_id is not '':
search_chain.append(
or_(TrackerSenseRelationsHistory.source_id == sense_id))
if user is not '':
search_chain.append(TrackerSenseRelationsHistory.user == user)
if relation_type is not '':
search_chain.append(
TrackerSenseRelationsHistory.relation_id == relation_type)
return and_(*search_chain)
class User(db.Model, UserMixin):
__tablename__ = 'users'
id = Column(db.Integer, primary_key=True)
name = Column(db.String(STRING_LEN), nullable=False, unique=True)
email = Column(db.String(STRING_LEN), nullable=False, unique=True)
openid = Column(db.String(STRING_LEN), unique=True)
activation_key = Column(db.String(STRING_LEN))
created_time = Column(db.DateTime, default=get_current_time)
age = Column(db.Integer)
phone = Column(db.String(STRING_LEN))
sex_code = db.Column(db.Integer)
_password = Column('password',
db.String(STRING_LEN),
nullable=False,
info={'label': 'password'})
avatar = Column(db.String(STRING_LEN))
@property
def sex(self):
return SEX_TYPE.get(self.sex_code)
def _get_password(self):
return self._password
def _set_password(self, password):
self._password = generate_password_hash(password)
# Hide password encryption by exposing password field only.
password = db.synonym('_password',
descriptor=property(_get_password, _set_password))
def check_password(self, password):
if self.password is None:
return False
return check_password_hash(self.password, password)
# def reset_password(self):
# self.activation_key = str(uuid4())
# db.session.add(self)
# db.session.commit()
# def change_password(self):
# self.password = self.password.data
# self.activation_key = None
# db.session.add(self)
# db.session.commit()
# ================================================================
# Class methods
@classmethod
def authenticate(cls, login, password):
user = cls.query.filter(db.or_(User.name == login,
User.email == login)).first()
if user:
authenticated = user.check_password(password)
else:
authenticated = False
return user, authenticated
# @classmethod
# def search(cls, keywords):
# criteria = []
# for keyword in keywords.split():
# keyword = '%' + keyword + '%'
# criteria.append(db.or_(
# User.name.ilike(keyword),
# User.email.ilike(keyword),
# ))
# q = reduce(db.and_, criteria)
# return cls.query.filter(q)
@classmethod
def get_by_id(cls, user_id):
return cls.query.filter_by(id=user_id).first_or_404()
def check_name(self, name):
return User.query.filter(
db.and_(User.name == name, User.email != self.id)).count() == 0
class Synset(db.Model):
__tablename__ = 'synset'
id = db.Column('ID', db.Integer, primary_key=True)
split = db.Column(db.Integer)
definition = db.Column(db.String(255))
isabstract = db.Column(db.Boolean())
status = db.Column(db.Integer)
comment = db.Column(db.String(255))
owner = db.Column(db.String(255))
unitsstr = db.Column(db.String(1024))
error_comment = db.Column(db.String(255))
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(SynsetTracker, self).__init__(**kwargs)
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'id'
if direction not in ('asc', 'desc'):
direction = 'desc'
return field, direction
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (Synset.id == query,
Synset.unitsstr.ilike(search_query))
return or_(*search_chain)
class User(UserMixin, db.Model):
ROLE = OrderedDict([('USER', 'User'), ('ANONYMOUS', 'Anonymous'),
('ADMIN', 'Administrator')])
__bind_key__ = 'users'
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
# Authentication.
role = db.Column(db.Enum(*ROLE, name='role', native_enum=False),
index=True,
nullable=False,
server_default='USER')
first_name = db.Column('firstname',
db.String(255),
unique=False,
index=True)
last_name = db.Column('lastname', db.String(255), unique=False, index=True)
email = db.Column(db.String(255),
unique=True,
index=True,
nullable=False,
server_default='')
password = db.Column(db.String(128), nullable=False, server_default='')
def __init__(self, **kwargs):
# Call Flask-SQLAlchemy's constructor.
super(User, self).__init__(**kwargs)
self.password = User.encrypt_password(kwargs.get('password', ''))
@classmethod
def find_by_email(cls, email):
"""
Find a user by their e-mail.
:param email: Email
:type email: str
:return: User instance
"""
return User.query.filter(User.email == email).first()
@classmethod
def find_by_fullname(cls, first_name, last_name):
"""
Find a user by first name and last name.
:param first_name: User first name
:param last_name: User last name
:return: User instance
"""
return User.query.filter(
and_(User.first_name == first_name,
User.last_name == last_name)).first()
@classmethod
def encrypt_password(cls, plaintext_password):
"""
Hash a plaintext string using SHA-256 with base64 encoding.
:param plaintext_password: Password in plain text
:type plaintext_password: str
:return: str
"""
if plaintext_password:
hash_object = hashlib.sha256(plaintext_password.encode())
hex_dig = hash_object.digest()
return base64.b64encode(hex_dig).decode()
return None
@classmethod
def deserialize_token(cls, token):
"""
Obtain a user from de-serializing a signed token.
:param token: Signed token.
:type token: str
:return: User instance or None
"""
private_key = TimedJSONWebSignatureSerializer(
current_app.config['SECRET_KEY'])
try:
decoded_payload = private_key.loads(token)
return User.find_by_identity(decoded_payload.get('user_email'))
except Exception:
return None
@classmethod
def search(cls, query):
"""
Search a resource by 1 or more fields.
:param query: Search query
:type query: str
:return: SQLAlchemy filter
"""
if not query:
return ''
search_query = '%{0}%'.format(query)
search_chain = (User.email.ilike(search_query),
User.first_name.ilike(search_query),
User.last_name.ilike(search_query),
User.id.ilike(search_query),
User.role.ilike(search_query))
return or_(*search_chain)
@classmethod
def sort_by(cls, field, direction):
"""
Validate the sort field and direction.
:param field: Field name
:type field: str
:param direction: Direction
:type direction: str
:return: tuple
"""
if field not in cls.__table__.columns:
field = 'id'
if direction not in ('asc', 'desc'):
direction = 'asc'
return field, direction
def fullname(self):
"""
Get user fullname
:return: str
"""
return self.first_name + " " + self.last_name
def is_active(self):
"""
Return whether or not the user account is active, this satisfies
Flask-Login by overwriting the default value.
:return: bool
"""
return True
def get_auth_token(self):
"""
Return the user's auth token. Use their password as part of the token
because if the user changes their password we will want to invalidate
all of their logins across devices. It is completely fine to use
md5 here as nothing leaks.
This satisfies Flask-Login by providing a means to create a token.
:return: str
"""
private_key = current_app.config['SECRET_KEY']
serializer = URLSafeTimedSerializer(private_key)
data = [str(self.id), md5(self.password.encode('utf-8')).hexdigest()]
return serializer.dumps(data)
def authenticated(self, with_password=True, password=''):
"""
Ensure a user is authenticated, and optionally check their password.
:param with_password: Optionally check their password
:type with_password: bool
:param password: Optionally verify this as their password
:type password: str
:return: bool
"""
if with_password:
return self.password == self.encrypt_password(password)
return True
def serialize_token(self, expiration=3600):
"""
Sign and create a token that can be used for things such as resetting
a password or other tasks that involve a one off token.
:param expiration: Seconds until it expires, defaults to 1 hour
:type expiration: int
:return: JSON
"""
private_key = current_app.config['SECRET_KEY']
serializer = TimedJSONWebSignatureSerializer(private_key, expiration)
return serializer.dumps({'user_email': self.email}).decode('utf-8')
