def query(workflow_id):
"""为延时或异步任务准备的queryx, 传入工单ID即可"""
workflow = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow.status == 'workflow_timingtask':
# 将工单状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
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=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
query_engine = get_engine(instance=workflow.instance)
with FuncTimer() as t:
query_result = query_engine.query(
workflow.db_name, workflow.sqlworkflowcontent.sql_content)
# if workflow.instance.db_type == 'pgsql': # TODO 此处判断待优化，请在 修改传参方式后去除
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content,
# schema_name=workflow.schema_name)
# else:
# query_result = query_engine.query(workflow.db_name, workflow.sqlworkflowcontent.sql_content)
query_result.query_time = t.cost
return query_result
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 = Const.workflowStatus['exception']
elif task.result.warning or task.result.error:
workflow.status = Const.workflowStatus['exception']
execute_result = task.result
else:
workflow.status = Const.workflowStatus['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.status),
operator='',
operator_display='系统')
# 发送消息
send_msg(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(workflow_id, user=None):
"""为延时或异步任务准备的execute, 传入工单ID和执行人信息"""
# 使用当前读防止重复执行
with transaction.atomic():
workflow_detail = SqlWorkflow.objects.select_for_update().get(
id=workflow_id)
# 只有审核通过和定时执行的数据才可以继续执行
if workflow_detail.status not in [
'workflow_review_pass', 'workflow_timingtask'
]:
raise Exception('工单状态不正确，禁止执行！')
# 将工单状态修改为执行中
else:
SqlWorkflow(
id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
# 增加执行日志
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=5,
operation_type_desc='执行工单',
operation_info='人工操作执行' if user else '系统定时执行',
operator=user.username if user else '',
operator_display=user.display if user else '系统')
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
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)
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
if mode == "auto":
status = "workflow_executing"
operation_type = 5
operation_type_desc = '执行工单'
operation_info = "自动操作执行"
finish_time = None
else:
status = "workflow_finish"
operation_type = 6
operation_type_desc = '手工工单'
operation_info = "确认手工执行结束"
finish_time = datetime.datetime.now()
# 将流程状态修改为对应状态
SqlWorkflow(
id=workflow_id, status=status,
finish_time=finish_time).save(update_fields=['status', 'finish_time'])
# 增加工单日志
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=operation_type,
operation_type_desc=operation_type_desc,
operation_info=operation_info,
operator=request.user.username,
operator_display=request.user.display)
if mode == "auto":
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1,
task_name=f'sqlreview-execute-{workflow_id}')
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute(request):
workflow_id = request.POST['workflow_id']
if workflow_id == '' or workflow_id is None:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_id = int(workflow_id)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = 'workflow_executing'
workflow_detail.reviewok_time = timezone.now()
workflow_detail.save()
async_task('sql.utils.execute_sql.execute',
workflow_detail.id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1)
# 增加工单日志
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=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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 timing_task(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 = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
schedule_name = f"sqlreview-timing-{workflow_id}"
if on_correct_time_period(workflow_id, run_date) is False:
context = {'errMsg': '不在可执行时间范围内，如果需要修改执 行时间请重新提交工单!'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sql_schedule(schedule_name, run_date, workflow_id)
# 增加工单日志
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=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display)
except Exception as msg:
logger.error(f"定时执行工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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 stop(request):
user = request.user
workflow_id = request.POST.get('workflow_id')
stop_remark = request.POST.get('stop_remark')
if not workflow_id:
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
if not stop_remark:
return JsonResponse({'status': 1, 'msg': '终止原因不能为空', 'data': []})
try:
group_list = user_groups(user)
group_ids = [group.group_id for group in group_list]
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.group_id not in group_ids:
return JsonResponse({'status': 1, 'msg': '您无权操作', 'data': []})
# 只有工单流转起来后，才能停止
if workflow.status not in [
'workflow_finish', 'workflow_review_pass',
'workflow_exception', 'workflow_pause'
]:
return JsonResponse({
'status': 1,
'msg': '该工单无法停止，操作非法',
'data': []
})
with transaction.atomic():
Schedule.objects.filter(name=f'sqlcron-{workflow_id}').update(
repeats=0)
workflow.status = 'workflow_stop'
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(stop_remark),
operator=user.username,
operator_display=user.display,
)
except Exception as e:
logger.error(f'发生异常，错误信息：{traceback.format_exc()}')
return JsonResponse({'status': 1, 'msg': f'发生异常，错误信息：{e}'})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def pause(request):
user = request.user
workflow_id = request.POST.get('workflow_id')
if not workflow_id:
return JsonResponse({'status': 1, 'msg': '参数不完整，请确认后提交', 'data': []})
try:
group_list = user_groups(user)
group_ids = [group.group_id for group in group_list]
workflow = SqlWorkflow.objects.get(id=workflow_id)
if workflow.group_id not in group_ids:
return JsonResponse({'status': 1, 'msg': '您无权操作', 'data': []})
# 在‘已审核’、‘在执行’、‘已执行’的状态下工单才能被暂停
if workflow.status not in [
'workflow_review_pass', 'workflow_executing', 'workflow_finish'
]:
return JsonResponse({
'status': 1,
'msg': '该工单非暂停状态，操作非法',
'data': []
})
with transaction.atomic():
Schedule.objects.filter(name=f'sqlcron-{workflow_id}').update(
repeats=0)
workflow.status = 'workflow_pause'
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=5,
operation_type_desc='执行工单',
operation_info='执行结果：已暂停任务',
operator=user.username,
operator_display=user.display,
)
except Exception as e:
logger.error(f'发生异常，错误信息：{traceback.format_exc()}')
return JsonResponse({'status': 1, 'msg': f'发生异常，错误信息：{e}'})
return JsonResponse({'status': 0, 'msg': '', 'data': []})
def execute(workflow_id):
"""为延时或异步任务准备的execute, 传入工单ID即可"""
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow_detail.status == Const.workflowStatus['timingtask']:
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=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
execute_engine = get_engine(workflow=workflow_detail)
return execute_engine.execute_workflow()
def execute(workflow_id):
"""为延时或异步任务准备的execute, 传入工单ID即可"""
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
# 给定时执行的工单增加执行日志
if workflow_detail.status == 'workflow_timingtask':
# 将工单状态修改为执行中
SqlWorkflow(id=workflow_id, status='workflow_executing').save(update_fields=['status'])
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=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统'
)
execute_engine = get_engine(instance=workflow_detail.instance)
return execute_engine.execute_workflow(workflow=workflow_detail)
def timingtask(request):
workflow_id = request.POST.get('workflow_id')
run_date = request.POST.get('run_date')
if run_date is None or workflow_id is None:
context = {'errMsg': '时间不能为空'}
return render(request, 'error.html', context)
elif run_date < datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'):
context = {'errMsg': '时间不能小于当前时间'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if can_timingtask(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M")
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = 'workflow_timingtask'
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id)
# 增加工单日志
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=4,
operation_type_desc='定时执行',
operation_info="定时执行时间：{}".format(run_date),
operator=request.user.username,
operator_display=request.user.display
)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id,)))
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)
# 将流程状态修改为执行中
SqlWorkflow(id=workflow_id,
status='workflow_executing').save(update_fields=['status'])
# 增加工单日志
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=5,
operation_type_desc='执行工单',
operation_info="人工操作执行",
operator=request.user.username,
operator_display=request.user.display)
# 加入执行队列
async_task('sql.utils.execute_sql.execute',
workflow_id,
hook='sql.utils.execute_sql.execute_callback',
timeout=-1)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute_select(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)
# 根据执行模式进行对应修改
mode = request.POST.get('mode')
status = "workflow_finish"
operation_type = 6
operation_type_desc = '手工工单'
operation_info = "确认手工执行结束"
finish_time = datetime.datetime.now()
# 将流程状态修改为对应状态
SqlWorkflow(
id=workflow_id, status=status,
finish_time=finish_time).save(update_fields=['status', 'finish_time'])
# 增加工单日志
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=operation_type,
operation_type_desc=operation_type_desc,
operation_info=operation_info,
operator=request.user.username,
operator_display=request.user.display)
fileName = datetime.datetime.now().strftime("%Y%m%d%H%M%S") + ".txt"
output_file = "/tmp/" + fileName
def output_data():
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance = workflow_detail.instance
sql_content = workflow_detail.sqlworkflowcontent.sql_content.replace(
";", "").replace("'", '"')
sql_content = sql_content + " limit 10000;" if "limit" not in sql_content else sql_content
sql_cmd = f"""/usr/bin/mysql -u{instance.user} -p'{instance.raw_password}' --default-character-set=utf8 -h{instance.host} -P{instance.port} -e 'use {workflow_detail.db_name};{sql_content}' >{output_file}"""
result = os.system(sql_cmd)
return result
if output_data() == 0 and os.path.exists(output_file):
def file_iterator(fileName, chunk_size=512):
with open(fileName, 'r') as f:
while True:
c = f.read(chunk_size)
if c:
yield c
else:
break
response = StreamingHttpResponse(file_iterator(output_file))
response['Content-Type'] = 'application/octet-stream'
response['Content-Disposition'] = 'attachment;filename="{0}"'.format(
fileName)
return response
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def cancel(request):
workflow_id = request.POST.get('workflow_id')
if workflow_id == '' or workflow_id is None:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_id = int(workflow_id)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
audit_remark = request.POST.get('cancel_remark')
if audit_remark is None:
context = {'errMsg': '终止原因不能为空'}
return render(request, 'error.html', context)
user = request.user
if can_cancel(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口取消或者驳回
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflow_detail.status != 'workflow_manreviewing':
# 增加工单日志
if user.username == workflow_detail.engineer:
Audit.add_log(
audit_id=audit_id,
operation_type=3,
operation_type_desc='取消执行',
operation_info="取消原因：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
Audit.add_log(
audit_id=audit_id,
operation_type=2,
operation_type_desc='审批不通过',
operation_info="审批备注：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
if user.username == workflow_detail.engineer:
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise PermissionDenied
# 删除定时执行job
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflow_id)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
workflow_detail.status = 'workflow_abort'
workflow_detail.audit_remark = audit_remark
workflow_detail.save()
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 消息通知
sys_config = SysConfig()
if sys_config.get('mail') or sys_config.get('ding'):
# 再次获取审核信息
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
base_url = sys_config.get('archery_base_url',
'http://127.0.0.1:8000').rstrip('/')
workflow_url = "{base_url}/workflow/{audit_id}".format(
base_url=base_url, audit_id=audit_detail.audit_id)
async_task(notify,
audit_info=audit_detail,
workflow_url=workflow_url,
audit_remark=audit_remark,
timeout=60)
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
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 post(self, request):
# 参数验证
serializer = AuditWorkflowSerializer(data=request.data)
if not serializer.is_valid():
return Response(serializer.errors,
status=status.HTTP_400_BAD_REQUEST)
audit_type = request.data['audit_type']
workflow_type = request.data['workflow_type']
workflow_id = request.data['workflow_id']
audit_remark = request.data['audit_remark']
engineer = request.data['engineer']
user = Users.objects.get(username=engineer)
# 审核查询权限申请
if workflow_type == 1:
audit_status = 1 if audit_type == 'pass' else 2
if audit_remark is None:
audit_remark = ''
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['query']
).audit_id
# 调用工作流接口审核
audit_result = Audit.audit(audit_id, audit_status,
user.username, audit_remark)
# 按照审核结果更新业务表审核状态
audit_detail = Audit.detail(audit_id)
if audit_detail.workflow_type == WorkflowDict.workflow_type[
'query']:
# 更新业务表审核状态,插入权限信息
_query_apply_audit_call_back(
audit_detail.workflow_id,
audit_result['data']['workflow_status'])
except Exception as msg:
logger.error(traceback.format_exc())
raise serializers.ValidationError({'errors': msg})
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'query-priv-audit-{workflow_id}')
return Response({
'msg': 'passed'
}) if audit_type == 'pass' else Response({'msg': 'canceled'})
# 审核SQL上线申请
elif workflow_type == 2:
# SQL上线申请通过
if audit_type == 'pass':
# 权限验证
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口审核
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.
workflow_type['sqlreview']).audit_id
audit_result = Audit.audit(
audit_id,
WorkflowDict.workflow_status['audit_success'],
user.username, audit_remark)
# 按照审核结果更新业务表审核状态
if audit_result['data'][
'workflow_status'] == WorkflowDict.workflow_status[
'audit_success']:
# 将流程状态修改为审核通过
SqlWorkflow(id=workflow_id,
status='workflow_review_pass').save(
update_fields=['status'])
except Exception as msg:
logger.error(traceback.format_exc())
raise serializers.ValidationError({'errors': msg})
else:
# 开启了Pass阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Pass' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'sqlreview-pass-{workflow_id}')
return Response({'msg': 'passed'})
# SQL上线申请驳回/取消
elif audit_type == 'cancel':
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
if audit_remark is None:
raise serializers.ValidationError({"errors": "终止原因不能为空"})
if can_cancel(user, workflow_id) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口取消或者驳回
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.
workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflow_detail.status != 'workflow_manreviewing':
# 增加工单日志
if user.username == workflow_detail.engineer:
Audit.add_log(audit_id=audit_id,
operation_type=3,
operation_type_desc='取消执行',
operation_info="取消原因：{}".format(
audit_remark),
operator=user.username,
operator_display=user.display)
else:
Audit.add_log(audit_id=audit_id,
operation_type=2,
operation_type_desc='审批不通过',
operation_info="审批备注：{}".format(
audit_remark),
operator=user.username,
operator_display=user.display)
else:
if user.username == workflow_detail.engineer:
Audit.audit(
audit_id, WorkflowDict.
workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
Audit.audit(
audit_id, WorkflowDict.
workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise serializers.ValidationError(
{"errors": "Permission Denied"})
# 删除定时执行task
if workflow_detail.status == 'workflow_timingtask':
schedule_name = f"sqlreview-timing-{workflow_id}"
del_schedule(schedule_name)
# 将流程状态修改为人工终止流程
workflow_detail.status = 'workflow_abort'
workflow_detail.save()
except Exception as msg:
logger.error(f"取消工单报错，错误信息：{traceback.format_exc()}")
raise serializers.ValidationError({'errors': msg})
else:
# 发送取消、驳回通知，开启了Cancel阶段通知参数才发送消息通知
sys_config = SysConfig()
is_notified = 'Cancel' in sys_config.get('notify_phase_control').split(',') \
if sys_config.get('notify_phase_control') else True
if is_notified:
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.
workflow_type['sqlreview'])
if audit_detail.current_status in (
WorkflowDict.workflow_status['audit_abort'],
WorkflowDict.workflow_status['audit_reject']):
async_task(
notify_for_audit,
audit_id=audit_detail.audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'sqlreview-cancel-{workflow_id}')
return Response({'msg': 'canceled'})
# 审核数据归档申请
elif workflow_type == 3:
audit_status = 1 if audit_type == 'pass' else 2
if audit_remark is None:
audit_remark = ''
if Audit.can_review(user, workflow_id, workflow_type) is False:
raise serializers.ValidationError({"errors": "你无权操作当前工单！"})
# 使用事务保持数据一致性
try:
with transaction.atomic():
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['archive']
).audit_id
# 调用工作流插入审核信息，更新业务表审核状态
audit_status = Audit.audit(
audit_id, audit_status, user.username,
audit_remark)['data']['workflow_status']
ArchiveConfig(
id=workflow_id,
status=audit_status,
state=True if audit_status
== WorkflowDict.workflow_status['audit_success'] else
False).save(update_fields=['status', 'state'])
except Exception as msg:
logger.error(traceback.format_exc())
raise serializers.ValidationError({'errors': msg})
else:
# 消息通知
async_task(notify_for_audit,
audit_id=audit_id,
audit_remark=audit_remark,
timeout=60,
task_name=f'archive-audit-{workflow_id}')
return Response({
'msg': 'passed'
}) if audit_type == 'pass' else Response({'msg': 'canceled'})
def cancel(request):
"""
终止流程
:param request:
:return:
"""
workflow_id = int(request.POST.get('workflow_id', 0))
if workflow_id == 0:
context = {'errMsg': 'workflow_id参数为空.'}
return render(request, 'error.html', context)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
audit_remark = request.POST.get('cancel_remark')
if audit_remark is None:
context = {'errMsg': '终止原因不能为空'}
return render(request, 'error.html', context)
user = request.user
if can_cancel(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 调用工作流接口取消或者驳回
audit_id = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
# 仅待审核的需要调用工作流，审核通过的不需要
if workflow_detail.status != 'workflow_manreviewing':
# 增加工单日志
if user.username == workflow_detail.engineer:
Audit.add_log(
audit_id=audit_id,
operation_type=3,
operation_type_desc='取消执行',
operation_info="取消原因：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
Audit.add_log(
audit_id=audit_id,
operation_type=2,
operation_type_desc='审批不通过',
operation_info="审批备注：{}".format(audit_remark),
operator=request.user.username,
operator_display=request.user.display)
else:
if user.username == workflow_detail.engineer:
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_abort'],
user.username, audit_remark)
# 非提交人需要校验审核权限
elif user.has_perm('sql.sql_review'):
Audit.audit(audit_id,
WorkflowDict.workflow_status['audit_reject'],
user.username, audit_remark)
else:
raise PermissionDenied
# 删除定时执行job
if workflow_detail.status == 'workflow_timingtask':
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(
workflow_id)
del_sqlcronjob(job_id)
# 将流程状态修改为人工终止流程
workflow_detail.status = 'workflow_abort'
workflow_detail.save()
except Exception as msg:
logger.error(f"取消工单报错，错误信息：{traceback.format_exc()}")
context = {'errMsg': msg}
return render(request, 'error.html', context)
else:
# 仅未审核通过又取消的工单需要发送消息，审核通过的不发送
audit_detail = Audit.detail_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview'])
if audit_detail.current_status == WorkflowDict.workflow_status[
'audit_abort']:
async_task(notify_for_audit,
audit_id=audit_detail.audit_id,
audit_remark=audit_remark,
timeout=60)
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))
