def execute_callback(task):
"""异步任务的回调, 将结果填入数据库等等
使用django-q的hook, 传入参数为整个task
task.result 是真正的结果
"""
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'
elif task.result.warning or task.result.error:
workflow.status = 'workflow_exception'
execute_result = task.result
else:
workflow.status = 'workflow_finish'
execute_result = task.result
workflow.execute_result = execute_result.json()
workflow.audit_remark = ''
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='系统'
)
# 发送消息
notify_for_execute(workflow)
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 是真正的结果
"""
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'
if workflow.sqlworkflowcontent.execute_result:
execute_result = json.loads(
workflow.sqlworkflowcontent.execute_result)
else:
execute_result = []
execute_result.append(
ReviewResult(id=0,
stage='Execute failed',
errlevel=2,
stagestatus='异常终止',
errormessage=task.result,
sql='执行异常信息',
affected_rows=0,
actual_affected_rows=0,
sequence='0_0_0',
backup_dbname=None,
execute_time=0,
sqlsha1='').__dict__)
execute_result = json.dumps(execute_result)
elif task.result.warning or task.result.error:
execute_result = task.result
workflow.status = 'workflow_exception'
execute_result = execute_result.json()
else:
execute_result = task.result
workflow.status = 'workflow_finish'
execute_result = execute_result.json()
# 保存执行结果
workflow.sqlworkflowcontent.execute_result = execute_result
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='系统')
# 发送消息
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 post(self, request):
# 参数验证
serializer = ExecuteWorkflowSerializer(data=request.data)
if not serializer.is_valid():
return Response(serializer.errors,
status=status.HTTP_400_BAD_REQUEST)
workflow_type = request.data['workflow_type']
workflow_id = request.data['workflow_id']
# 执行SQL上线工单
if workflow_type == 2:
mode = request.data['mode']
engineer = request.data['engineer']
user = Users.objects.get(username=engineer)
# 校验多个权限
if not (user.has_perm('sql.sql_execute')
or user.has_perm('sql.sql_execute_for_resource_group')):
raise serializers.ValidationError({"errors": "你无权执行当前工单！"})
if can_execute(user, workflow_id) is False:
raise serializers.ValidationError({"errors": "你无权执行当前工单！"})
if on_correct_time_period(workflow_id) is False:
raise serializers.ValidationError(
{"errors": "不在可执行时间范围内，如果需要修改执行时间请重新提交工单!"})
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 交由系统执行
if mode == "auto":
# 修改工单状态为排队中
SqlWorkflow(
id=workflow_id,
status="workflow_queuing").save(update_fields=['status'])
# 删除定时执行任务
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
user,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_id}')
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='工单执行排队中',
operator=user.username,
operator_display=user.display)
# 线下手工执行
elif mode == "manual":
# 将流程状态修改为执行结束
SqlWorkflow(id=workflow_id,
status="workflow_finish",
finish_time=datetime.datetime.now()).save(
update_fields=['status', 'finish_time'])
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='手工工单',
operation_info='确认手工执行结束',
operator=user.username,
operator_display=user.display)
# 开启了Execute阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Execute' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
notify_for_execute(SqlWorkflow.objects.get(id=workflow_id))
# 执行数据归档工单
elif workflow_type == 3:
async_task('sql.archiver.archive',
workflow_id,
timeout=-1,
task_name=f'archive-{workflow_id}')
return Response({'msg': '开始执行，执行结果请到工单详情页查看'})
def execute(request):
"""
执行SQL
:param request:
:return:
"""
# 校验多个权限
if not (request.user.has_perm('sql.sql_execute')
or request.user.has_perm('sql.sql_execute_for_resource_group')):
raise PermissionDenied
workflow_id = int(request.POST.get('workflow_id', 0))
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
if on_correct_time_period(workflow_id) is False:
context = {'errMsg': '不在可执行时间范围内，如果需要修改执行时间请重新提交工单!'}
return render(request, 'error.html', context)
# 获取审核信息
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
# 交由系统执行
if mode == "auto":
# 修改工单状态为排队中
SqlWorkflow(id=workflow_id,
status="workflow_queuing").save(update_fields=['status'])
# 删除定时执行任务
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
request.user,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_id}')
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='工单执行排队中',
operator=request.user.username,
operator_display=request.user.display)
# 线下手工执行
elif mode == "manual":
# 将流程状态修改为执行结束
SqlWorkflow(id=workflow_id,
status="workflow_finish",
finish_time=datetime.datetime.now()).save(
update_fields=['status', 'finish_time'])
# 增加工单日志
Audit.add_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='手工工单',
operation_info='确认手工执行结束',
operator=request.user.username,
operator_display=request.user.display)
# 发送消息
notify_for_execute(SqlWorkflow.objects.get(id=workflow_id))
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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))
