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