def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
# https://stackoverflow.com/questions/7835272/django-operationalerror-2006-mysql-server-has-gone-away
if connection.connection and not connection.is_usable():
close_old_connections()
workflow_id = task.args[0]
# 判断工单状态，如果不是执行中的，不允许更新信息，直接抛错记录日志
with transaction.atomic():
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.status != 'workflow_executing':
raise Exception(f'工单{workflow.id}状态不正确，禁止重复更新执行结果！')
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
execute_result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content)
execute_result.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
else:
execute_result = task.result
workflow.status = 'workflow_finish'
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result.json()
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# DDL工单结束后清空实例资源缓存
if workflow.syntax_type == 1:
r = get_redis_connection("default")
for key in r.scan_iter(match='*insRes*', count=2000):
r.delete(key)
# 发送消息
notify_for_execute(workflow)
def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
close_old_connections()
workflow_id = task.args[0]
workflow = SqlWorkflow.objects.get(id=workflow_id)
workflow.finish_time = task.stopped
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
execute_result = ReviewSet(
full_sql=workflow.sqlworkflowcontent.sql_content)
execute_result.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
else:
execute_result = task.result
workflow.status = 'workflow_finish'
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result.json()
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(
workflow.get_status_display()),
operator='',
operator_display='系统')
# DDL工单结束后清空实例资源缓存
if workflow.syntax_type == 1:
r = get_redis_connection("default")
for key in r.scan_iter(match='*insRes*', count=2000):
r.delete(key)
# 发送消息
notify_for_execute(workflow)
def newquery(request):
"""正式提交SQL, 此处生成工单"""
workflow_name = request.POST.get('workflow_name')
demand_url = request.POST.get('demand_url', '')
group_name = request.POST.get('group_name')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
first_run_time = request.POST.get('first_run_time')
schedule_type = request.POST.get('period')
minutes = request.POST.get('minutes')
receivers = request.POST.getlist('receivers')
cc_list = request.POST.getlist('cc_list')
is_backup = False
sql_content = request.POST.get('sql_content').strip()
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if not all([
workflow_name, group_name, instance_name, db_name, first_run_time,
schedule_type, sql_content
]):
result['status'] = 1
result['msg'] = '请检查提交工单填写内容是否完整'
return HttpResponse(json.dumps(result),
content_type='application/json')
user = request.user
group_id = ResourceGroup.objects.get(group_name=group_name).group_id
instance = Instance.objects.get(instance_name=instance_name)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(user,
tag_codes=['can_read']).get(instance_name=instance_name)
except instance.DoesNotExist:
result['status'] = 1
result['msg'] = '你所在组未关联该实例'
return HttpResponse(json.dumps(result),
content_type='application/json')
try:
config = SysConfig()
# 查询前的检查，禁用语句检查，语句切分
query_engine = get_engine(instance=instance)
query_check_info = query_engine.query_check(db_name=db_name,
sql=sql_content)
if query_check_info.get('bad_query'):
# 引擎内部判断为 bad_query
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
if query_check_info.get(
'has_star') and config.get('disable_star') is True:
# 引擎内部判断为有 * 且禁止 * 选项打开
result['status'] = 1
result['msg'] = query_check_info.get('msg')
return HttpResponse(json.dumps(result),
content_type='application/json')
sql_content = query_check_info['filtered_sql']
# 查询权限校验，并且获取limit_num
priv_check_info = query_priv_check(user, instance, db_name,
sql_content, 0)
if priv_check_info['status'] != 0:
result['status'] = 1
result['msg'] = priv_check_info['msg']
return HttpResponse(json.dumps(result),
content_type='application/json')
except Exception as e:
logger.error(
f'查询异常报错，查询语句：{sql_content}\n，错误信息：{traceback.format_exc()}')
result['status'] = 1
result['msg'] = f'查询异常报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
check_result = ReviewSet(full_sql=sql_content)
check_result.rows = [
ReviewResult(
id=1,
errlevel=0,
stagestatus='Audit completed',
errormessage='None',
sql=sql_content,
affected_rows=0,
execute_time=0,
)
]
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
sql_workflow = SqlWorkflow.objects.create(
order_type="sqlcron_order",
workflow_name=workflow_name,
demand_url=demand_url,
group_id=group_id,
group_name=group_name,
engineer=request.user.username,
engineer_display=request.user.display,
audit_auth_groups=Audit.settings(
group_id, WorkflowDict.workflow_type['sqlreview']),
status='workflow_manreviewing',
is_backup=is_backup,
instance=instance,
db_name=db_name,
is_manual=0,
syntax_type=3, #0、未知，1、DDL，2、DML 3、QUERY'
create_time=timezone.now(),
run_date_start=None,
run_date_end=None,
cc_list=cc_list,
)
sql_workflow.receivers.set(
Users.objects.filter(username__in=receivers))
sql_workflow_content = SqlWorkflowContent.objects.create(
workflow=sql_workflow,
sql_content=sql_content,
review_content=check_result.json(),
execute_result='')
sched = schedule(
'sql.utils.query_sql.query',
sql_workflow.id,
hook='sql.utils.query_sql.query_callback',
name=f'sqlcron-{sql_workflow.id}',
schedule_type=schedule_type,
minutes=minutes,
next_run=first_run_time,
repeats=0, # 在审批结束前创建但不启用
timeout=-1,
)
sql_workflow.schedule = sched
sql_workflow.save()
sql_workflow_content.save()
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
Audit.add(WorkflowDict.workflow_type['sqlreview'], sql_workflow.id)
except Exception as e:
logger.error(f"提交工单报错，错误信息：{traceback.format_exc()}")
result['status'] = 1
result['msg'] = f'提交工单报错，错误信息：{e}'
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
# 进行消息通知
audit_id = Audit.detail_by_workflow_id(
workflow_id=sql_workflow.id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
async_task(notify_for_audit,
audit_id=audit_id,
cc_users=receivers,
timeout=60,
task_name=f'sqlreview-submit-{sql_workflow.id}')
result['status'] = 0
result['data'] = {
'redirect': reverse('sql:sqlcrondetail', args=(sql_workflow.id, ))
}
return HttpResponse(json.dumps(result), content_type='application/json')
def query_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.review_set 是真正的结果
"""
# https://stackoverflow.com/questions/7835272/django-operationalerror-2006-mysql-server-has-gone-away
if connection.connection and not connection.is_usable():
close_old_connections()
workflow_id = task.args[0]
workflow = SqlWorkflow.objects.get(id=workflow_id)
workflow.finish_time = task.stopped
query_result = task.result
filename = ''
review_set = ReviewSet(full_sql=workflow.sqlworkflowcontent.sql_content)
if not task.success:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
review_set.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=query_result,
sql=workflow.sqlworkflowcontent.sql_content)
]
elif query_result.error:
# 不成功会返回错误堆栈信息，构造一个错误信息
workflow.status = 'workflow_exception'
review_set.rows = [
ReviewResult(stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=query_result.error,
sql=workflow.sqlworkflowcontent.sql_content)
]
else:
filename = f"QueryTask{workflow.id}-{int(time.time())}.xls"
save2excel(f"{settings.DOWNLOAD_DIR}/{filename}",
[query_result.column_list] + list(query_result.rows))
review_set.rows = [
ReviewResult(
errlevel=0,
stagestatus='Execute Successfully',
errormessage='None',
sql=query_result.full_sql,
affected_rows=query_result.affected_rows,
execute_time=query_result.query_time,
)
]
workflow.status = 'workflow_finish'
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = review_set.json()
workflow.sqlworkflowcontent.save()
workflow.save()
# 增加工单日志
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行工单',
operation_info=f'执行结果：{workflow.get_status_display()}' +
(f'，文件: [{filename}]' if filename else ''),
operator='',
operator_display='系统')
# 发送消息
notify_for_execute(workflow,
filename_list=([f"{settings.DOWNLOAD_DIR}/{filename}"]
if filename else None))