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