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