def count_status_by_user(nlp_task_id, current_user: CurrentUser):
# compose query
q = session.query(DocType) \
.join(MarkJob, DocType.doc_type_id == MarkJob.doc_type_id) \
.join(MarkTask, MarkJob.mark_job_id == MarkTask.mark_job_id) \
.filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, ~MarkJob.is_deleted, ~MarkTask.is_deleted)
# filter by user
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [RoleEnum.reviewer.value]:
q = q.filter(
func.json_contains(MarkJob.reviewer_ids,
str(current_user.user_id)))
elif current_user.user_role in [RoleEnum.annotator.value]:
q = q.filter(
func.json_contains(MarkJob.annotator_ids,
str(current_user.user_id)))
# get grouped (doc_type_id, mark_job_id, count) list
all_status = q.group_by(MarkJob.doc_type_id, MarkJob.mark_job_id) \
.with_entities(MarkJob.doc_type_id, MarkJob.mark_job_id, func.count(MarkTask.mark_task_id)).all()
# filter >= labeled status
q = q.filter(MarkTask.mark_task_status >= int(StatusEnum.labeled))
# get grouped (doc_type_id, mark_job_id, >= labeled count) list
all_finish_marking_status = q.group_by(MarkJob.doc_type_id, MarkJob.mark_job_id) \
.with_entities(MarkJob.doc_type_id, MarkJob.mark_job_id, func.count(MarkTask.mark_task_id)).all()
return all_status, all_finish_marking_status
def get_by_nlp_task_id(
self, nlp_task_id, search, order_by="created_time",
order_by_desc=True, limit=10, offset=0, user_role=None, **kwargs):
# Define allowed filter keys
accept_keys = ["assign_mode", "mark_job_status", "mark_job_type", "doc_type_id"]
# Compose query
q = session.query(MarkJob, DocType).join(
DocType, MarkJob.doc_type_id == DocType.doc_type_id
).filter(DocType.nlp_task_id == nlp_task_id, ~DocType.is_deleted, ~MarkJob.is_deleted)
# Role
if user_role == "管理员":
q = q.filter(DocType.group_id.in_(g.user_groups))
elif user_role == "审核员":
q = q.filter(func.json_contains(MarkJob.reviewer_ids, str(g.user_id)))
elif user_role == "标注员":
q = q.filter(func.json_contains(MarkJob.annotator_ids, str(g.user_id)))
# Filter conditions
for key, val in kwargs.items():
if key in accept_keys and val is not None:
q = q.filter(getattr(MarkJob, key) == val)
if search:
q = q.filter(MarkJob.mark_job_name.like(f'%{search}%'))
count = q.count()
# Order by key
q = q.order_by(text(f"{'-' if order_by_desc else ''}mark_job.{order_by}"))
q = q.offset(offset).limit(limit)
return count, q.all()
def return_nodes(self, query_type, query_params):
try:
if query_type == 'nodeId':
return self.session.query(Nodes).filter(
Nodes.nodeId == query_params)
if query_type == 'nodeName':
return self.session.query(Nodes).filter(
Nodes.nodeName == query_params)
if query_type == 'tierId':
return self.session.query(Nodes).filter(
Nodes.tierId == query_params)
if query_type == 'appId':
return self.session.query(Nodes).filter(
Nodes.appId == query_params)
if query_type == 'ipAddress':
return self.session.query(Nodes).filter(
func.json_contains(Nodes.ipAddress, query_params) ==
1).all()
if query_type == 'macAddress':
return self.session.query(Nodes).filter(
func.json_contains(Nodes.macAddress, query_params) ==
1).all()
except Exception as e:
self.flush_session()
logger.exception(
"Internal backend error: could not return Node details. Error: "
+ str(e))
logger.info("Exception occured for query_type: " + query_type)
logger.info("Exception occured for query_params: " + query_params)
return []
def get_by_mark_job_ids(mark_job_ids,
nlp_task_id,
current_user: CurrentUser,
limit=10,
offset=0) -> (int, List):
q = session.query(DocType)\
.outerjoin(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id)\
.filter(DocType.nlp_task_id == nlp_task_id,
~DocType.is_deleted,
or_(~MarkJob.is_deleted, MarkJob.is_deleted.is_(None)))
# 权限filter
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [
RoleEnum.reviewer.value, RoleEnum.annotator.value
]:
q = q.filter(
or_(
func.json_contains(MarkJob.annotator_ids,
str(current_user.user_id)),
func.json_contains(MarkJob.reviewer_ids,
str(current_user.user_id))))
if mark_job_ids:
q = q.filter(MarkJob.mark_job_id.in_(mark_job_ids))
count = q.count()
items = q.offset(offset).limit(limit).all()
return count, items
def add_to_sqlalchemy_query(self, query, field):
if self.value is None:
return query.filter(field.is_(None))
if self.operator == Operator.NOT_EQUALS:
return query.filter(
func.json_contains(field, self.value, '$') == 0)
else:
return query.filter(
func.json_contains(field, self.value, '$') == 1)
def exist(cls, sid=None, **kwargs):
"""Проверка существования пользователя с данными в базе."""
email_condition = {"value": kwargs.get('email'), "type": "primary"}
if sid is None:
exist = cls.query.filter((cls.login == kwargs.get('login')) | (
(func.json_contains(cls.email, json.dumps(email_condition))
)) | (cls.phone == kwargs.get('phone'))).first()
else:
exist = cls.query.filter(((cls.login == kwargs.get('login')) | (
(func.json_contains(cls.email, json.dumps(email_condition))))
| (cls.phone == kwargs.get('phone')))
& (cls.id != sid)).first()
if exist:
return True
return False
def get_preview_and_next_mark_task_id(current_user, nlp_task_id, task_id,
args):
q = session.query(MarkTask.mark_task_id) \
.outerjoin(UserTask, UserTask.mark_task_id == MarkTask.mark_task_id) \
.join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \
.join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \
.filter(
DocType.nlp_task_id == nlp_task_id,
MarkTask.mark_task_status != int(StatusEnum.processing),
~MarkTask.is_deleted,
or_(~UserTask.is_deleted, UserTask.is_deleted.is_(None)),
~MarkJob.is_deleted,
~DocType.is_deleted
)
if args.get('job_id'):
q = q.filter(MarkJob.mark_job_id == args['job_id'])
if args.get("task_state"):
q = q.filter(MarkTask.mark_task_status == args.get("task_state"))
if args.get("query"):
q = q.filter(Doc.doc_raw_name.contains(args.get("query")))
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [RoleEnum.reviewer.value]:
q = q.filter(
func.json_contains(MarkJob.reviewer_ids,
str(current_user.user_id)))
elif current_user.user_role in [RoleEnum.annotator.value]:
q = q.filter(
func.json_contains(MarkJob.annotator_ids,
str(current_user.user_id)))
q1 = Common().order_by_model_fields(
q.filter(MarkTask.mark_task_id < task_id), MarkTask,
['-mark_task_id'])
q2 = Common().order_by_model_fields(
q.filter(MarkTask.mark_task_id > task_id), MarkTask,
['+mark_task_id'])
next_task_id = q1.limit(1).first()
preview_task_id = q2.limit(1).first()
return preview_task_id[0] if preview_task_id else None, next_task_id[
0] if next_task_id else None
def count_doc_type_by_nlp_task(current_user: CurrentUser) -> [(int, int)]:
q = session.query(DocType.nlp_task_id, func.count(DocType.doc_type_id)) \
.filter(~DocType.is_deleted)
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [
RoleEnum.reviewer.value, RoleEnum.annotator.value
]:
# Reviewer and annotator joins mark_job to filter visible doc types
q = session.query(DocType.nlp_task_id, func.count(DocType.doc_type_id)) \
.join(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id) \
.filter(~DocType.is_deleted, ~MarkJob.is_deleted,
or_(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)),
func.json_contains(MarkJob.annotator_ids, str(current_user.user_id))))
count = q.group_by(DocType.nlp_task_id).all()
return count
def authenticate(cls, check=False, **kwargs):
"""Функция аутентификации."""
login = kwargs.get('login')
password = kwargs.get('password')
if not login or not password:
return (None, 'Не переданы данные\
для аутентификации пользователя!', 'empty')
# email = {"value": login, "verified": True}
# Вход через любую подтвержденную почту, привязанную к пользователю
# user = cls.query.filter((cls.login == login) |
# (func.json_contains(cls.email, json.dumps(email)))).first()
user = cls.query.filter((cls.login == login) | (func.json_contains(
cls.email, json.dumps({"value": login})))).first()
if user:
mail_status = list(
filter(lambda mail: mail['type'] == "primary", user.email))
if mail_status and not mail_status[0]['verified']:
return (None, 'Основная почта не активирована!', 'username')
elif user.password['blocked'] and not check:
return (None, 'Пароль заблокирован!', 'password')
elif not bcrypt.check_password_hash(user.password['value'],
password):
max_fails = current_app.config['MAX_FAILED']
user.password['failed_times'] += 1
if user.password['failed_times'] >= max_fails:
user.password['blocked'] = True
user.password['failed_times'] = 0
CmsUsers.query.filter_by(id=user.id).update(
{'password': user.password})
db.session.commit()
return (None, 'Неверный пароль! Ваш пароль заблокирован!',
'password')
else:
CmsUsers.query.filter_by(id=user.id).update(
{'password': user.password})
db.session.commit()
return (None, 'Неверный пароль! '
'Осталось попыток ввода: %i' %
(max_fails - user.password['failed_times']),
'password')
else:
return (None, 'Пользователь не найден!', 'username')
user.password['failed_times'] = 0
CmsUsers.query.filter_by(id=user.id).update(
{'password': user.password})
db.session.commit()
return (user, 'Успешно!')
def _json_path_clause(kv_pair):
path_expr, value = kv_pair
key, path = _parse_path_expr(path_expr)
json_match = sa_func.json_contains(
sa_func.json_extract(models.Variable.value, path), value)
# NOTE(thomasem): Order is important here. MySQL will short-circuit and
# not properly validate the JSON Path expression when the key doesn't exist
# if the key match is first int he and_(...) expression. So, let's put
# the json_match first.
return and_(json_match, models.Variable.key == key)
def get_user_task_with_doc_and_doc_type(nlp_task_id,
current_user: CurrentUser, args):
q = session.query(UserTask, DocType, Doc) \
.join(MarkTask, MarkTask.mark_task_id == UserTask.mark_task_id) \
.join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \
.join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \
.join(Doc, Doc.doc_id == MarkTask.doc_id) \
.filter(
DocType.nlp_task_id == nlp_task_id,
~UserTask.is_deleted,
~MarkTask.is_deleted,
~Doc.is_deleted
)
# TODO
# 权限
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [RoleEnum.reviewer.value]:
q = q.filter(
func.json_contains(MarkJob.reviewer_ids,
str(current_user.user_id)))
elif current_user.user_role in [RoleEnum.annotator.value]:
# q = q.filter(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)))
q = q.filter(UserTask.annotator_id == current_user.user_id)
if args.get('job_id'):
q = q.filter(MarkTask.mark_job_id == args['job_id'])
if args.get('doc_type_id'):
q = q.filter(MarkJob.doc_type_id == args['doc_type_id'])
if args['task_state']:
q = q.filter(MarkTask.mark_task_status ==
status_str2int_mapper().get(args['task_state']))
if args['query']:
q = q.filter(Doc.doc_raw_name.like(f'%{args["query"]}%'))
q = q.group_by(UserTask)
count = q.count()
processing_count = q.filter(
MarkTask.mark_task_status == int(StatusEnum.processing)).count()
if args['order_by'] and isinstance(args['order_by'], str):
if args['order_by'][1:] == 'task_id':
args['order_by'] = args['order_by'][0] + 'mark_task_id'
q = Common().order_by_model_fields(q, UserTask, [args['order_by']])
items = []
for user_task, doc_type, doc in q.offset(args['offset']).limit(
args['limit']).all():
user_task.doc = doc
user_task.doc_type = doc_type
items.append(user_task)
return count, count - processing_count, items
def get_by_nlp_task_id_by_user(nlp_task_id,
current_user: CurrentUser) -> [DocType]:
q = session.query(DocType, func.group_concat(DocTerm.doc_term_id.distinct()))\
.outerjoin(DocTerm, DocTerm.doc_type_id == DocType.doc_type_id)\
.filter(DocType.nlp_task_id == nlp_task_id,
~DocType.is_deleted,
or_(~DocTerm.is_deleted, DocTerm.is_deleted.is_(None)))
# 权限filter
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [
RoleEnum.reviewer.value, RoleEnum.annotator.value
]:
q = q.outerjoin(MarkJob, MarkJob.doc_type_id == DocType.doc_type_id)\
.filter(~MarkJob.is_deleted,
or_(func.json_contains(MarkJob.annotator_ids, str(current_user.user_id)),
func.json_contains(MarkJob.reviewer_ids, str(current_user.user_id))))
q = q.group_by(DocTerm.doc_type_id, DocType)
count = q.count()
q = q.order_by(DocType.is_favorite.desc(), DocType.created_time.desc())
return count, q.all()
def test2():
# 修改普通数据
with open_session() as session:
# json_contains 中的 val 需要用双引号括起来，所以目标值类型是字符串时，需要两层引号
query = session.query(Cluster).filter(
Cluster.name == "a",
sa_func.json_contains(Cluster.description, '"c"', "$[0]"))
obj = query.one()
print("cluster:", obj)
obj.name = "b"
# 修改 JSON 数组和对象 数据, 需要显示的转换成JSON
# 如果路径标识数组元素，则将相应的值插入该元素位置，然后将任何后续值向右移动。如果路径标识了超出数组末尾的数组位置，则将值插入到数组末尾。
update_info = {
"description":
sa_func.json_array_insert(
Cluster.description, '$[1]',
sa_func.cast(
{
"x": 194,
"y": 68,
"o": 100,
"a": 0,
"f":
"/bao/uploaded/i4/696944147/O1CN01qjcXhk1gVN66z6pog_!!2-item_pic.png",
"type": "I",
"w": 388,
"h": 136
}, JSON)),
"attr":
sa_func.json_insert(
Cluster.attr, "$.thumbnail",
"/bao/uploaded/i1/696944147/O1CN0175JvxQ1gVN6HRrZ44_!!0-item_pic.jpg"
)
}
with open_session() as session:
count = session.query(Cluster).filter(Cluster.name == "b").update(
update_info, synchronize_session=False)
print("update_data count:", count)
with open_session() as session:
for obj in session.query(Cluster).filter(Cluster.name == "b").all():
print(obj)
class Operator(object):
OPERATORS = {
'is_null': lambda f: f.is_(None),
'is_not_null': lambda f: f.isnot(None),
'==': lambda f, a: f == a,
'eq': lambda f, a: f == a,
'!=': lambda f, a: f != a,
'ne': lambda f, a: f != a,
'>': lambda f, a: f > a,
'gt': lambda f, a: f > a,
'<': lambda f, a: f < a,
'lt': lambda f, a: f < a,
'>=': lambda f, a: f >= a,
'ge': lambda f, a: f >= a,
'<=': lambda f, a: f <= a,
'le': lambda f, a: f <= a,
'like': lambda f, a: f.like(a),
'ilike': lambda f, a: f.ilike(a),
'not_ilike': lambda f, a: ~f.ilike(a),
'in': lambda f, a: f.in_(a),
'not_in': lambda f, a: ~f.in_(a),
'any': lambda f, a: f.any(a),
'not_any': lambda f, a: func.not_(f.any(a)),
'json_contains':
lambda f, a: func.json_contains(f, func.json_array(a)),
}
def __init__(self, operator=None):
if not operator:
operator = '=='
if operator not in self.OPERATORS:
raise BadFilterFormat('Operator `{}` not valid.'.format(operator))
self.operator = operator
self.function = self.OPERATORS[operator]
self.arity = len(signature(self.function).parameters)
def get(self):
"""
Retrieve the complete list of metric specifications.
---
tags:
- Metric Specifications
parameters:
- name: metric
in: url
type: string
description: >
A full qualified name for the Metric,
e.g `validate_drp.AM1`
- name: dataset_name
in: url
type: string
description: >
Name of the dataset as in the query metadata,
e.g `validation_data_cfht`
- name: filter_name
in: url
type: string
description: >
Name of the filter as in the query metadada,
e.g. `r`
- name: specification_tag
in: url
type: string
description: >
Name of the specification tag
responses:
200:
description: List of metric specifications successfully retrieved.
"""
queryset = SpecificationModel.query.join(MetricModel)
args = self.parser.parse_args()
metric = args["metric"]
if metric:
queryset = queryset.filter(MetricModel.name == metric)
dataset_name = args["dataset_name"]
if dataset_name:
expr = (SpecificationModel.metadata_query["dataset_name"] ==
dataset_name)
queryset = queryset.filter(expr)
filter_name = args["filter_name"]
if filter_name:
expr = (SpecificationModel.metadata_query["filter_name"] ==
filter_name)
queryset = queryset.filter(expr)
specification_tag = args["tag"]
if specification_tag:
expr = func.json_contains(SpecificationModel.tags,
'"{}"'.format(specification_tag))
queryset = queryset.filter(expr)
return {"specs": [spec.json() for spec in queryset.all()]}
def get_mark_task_with_doc_and_doc_type(self, nlp_task_id,
current_user: CurrentUser, args):
q = session.query(MarkTask, DocType, Doc) \
.join(MarkJob, MarkJob.mark_job_id == MarkTask.mark_job_id) \
.join(DocType, DocType.doc_type_id == MarkJob.doc_type_id) \
.join(Doc, Doc.doc_id == MarkTask.doc_id) \
.filter(
DocType.nlp_task_id == nlp_task_id,
~DocType.is_deleted,
~MarkTask.is_deleted,
~Doc.is_deleted
)
# TODO
# 权限
if current_user.user_role in [
RoleEnum.manager.value, RoleEnum.guest.value
]:
q = q.filter(DocType.group_id.in_(current_user.user_groups))
elif current_user.user_role in [RoleEnum.reviewer.value]:
q = q.filter(
func.json_contains(MarkJob.reviewer_ids,
str(current_user.user_id)))
elif current_user.user_role in [RoleEnum.annotator.value]:
q = q.filter(
func.json_contains(MarkJob.annotator_ids,
str(current_user.user_id)))
if args.get('job_id'):
q = q.filter(MarkTask.mark_job_id == args['job_id'])
if args.get('doc_type_id'):
q = q.filter(MarkJob.doc_type_id == args['doc_type_id'])
if args['task_state']:
q = q.filter(MarkTask.mark_task_status ==
status_str2int_mapper().get(args['task_state']))
if args['query']:
q = q.filter(Doc.doc_raw_name.like(f'%{args["query"]}%'))
q = q.group_by(MarkTask)
count = q.count()
processing_count = q.filter(
MarkTask.mark_task_status == int(StatusEnum.processing)).count()
if args['order_by'] and isinstance(args['order_by'], str):
if args['order_by'][1:] == 'task_id':
args['order_by'] = args['order_by'][0] + 'mark_task_id'
q = Common().order_by_model_fields(q, MarkTask, [args['order_by']])
items = []
results = q.offset(args['offset']).limit(args['limit']).all()
mark_task_ids = [mark_task.mark_task_id for mark_task, _, _ in results]
user_task_map = self._get_user_task_map(
mark_task_ids,
select_keys=(UserTask)) # .annotator_id, UserTask.mark_task_id))
UserTaskPlaceholder = UserTask(
annotator_id=0,
is_deleted=False,
user_task_status=StatusEnum.labeled.value)
for mark_task, doc_type, doc in results:
UserTaskPlaceholder.user_task_result = mark_task.mark_task_result
user_task_list = user_task_map.get(str(mark_task.mark_task_id),
[UserTaskPlaceholder])
mark_task.user_task_list = user_task_list
mark_task.doc = doc
mark_task.doc_type = doc_type
items.append(mark_task)
return count, count - processing_count, items
