def execute_job(workflowId, url):
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
logger.debug('execute_job:' + job_id + ' start')
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
clusterName = workflowDetail.cluster_name
db_name = workflowDetail.db_name
# 服务器端二次验证，当前工单状态必须为定时执行过状态
if workflowDetail.status != Const.workflowStatus['timingtask']:
raise Exception('工单不是定时执行状态')
# 将流程状态修改为执行中，并更新reviewok_time字段
workflowDetail.status = Const.workflowStatus['executing']
workflowDetail.reviewok_time = timezone.now()
try:
workflowDetail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
logger.debug('execute_job:' + job_id + ' executing')
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
splitReviewResult = InceptionDao().sqlautoReview(workflowDetail.sql_content, workflowDetail.cluster_name, db_name,
isSplit='yes')
workflowDetail.review_content = json.dumps(splitReviewResult)
try:
workflowDetail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back, args=(workflowId, clusterName, url))
t.start()
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)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
url = get_detail_url(request, workflow_id)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['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')
return HttpResponseRedirect(reverse('sql:detail', args=(workflow_id, )))
def execute_call_back(workflow_id, instance_name, url):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
try:
# 交给inception先split，再执行
(finalStatus, finalList) = InceptionDao(instance_name=instance_name).executeFinal(workflow_detail)
# 封装成JSON格式存进数据库字段里
str_json_result = json.dumps(finalList)
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
workflow_detail.execute_result = str_json_result
workflow_detail.finish_time = timezone.now()
workflow_detail.status = finalStatus
workflow_detail.is_manual = 0
workflow_detail.audit_remark = ''
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
except Exception:
logger.error(traceback.format_exc())
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=6,
operation_type_desc='执行结束',
operation_info='执行结果：{}'.format(workflow_detail.status),
operator='',
operator_display='系统'
)
# 发送消息
send_msg(workflow_detail, url)
def query_tree(self, sql_content, instance_name, db_name):
print_info = InceptionDao().query_print(sql_content, instance_name,
db_name)
if print_info:
id = print_info[0][0]
statement = print_info[0][1]
# 返回值为非0的情况下，说明是有错的，1表示警告，不影响执行，2表示严重错误，必须修改
errlevel = print_info[0][2]
query_tree = print_info[0][3]
errmsg = print_info[0][4]
# 提交给inception语法错误的情况
if errmsg == 'Global environment':
errlevel = 2
errmsg = 'Global environment: ' + query_tree
if errlevel == 0:
pass
# print(json.dumps(json.loads(query_tree), indent=4, sort_keys=False, ensure_ascii=False))
return {
'id': id,
'statement': statement,
'errlevel': errlevel,
'query_tree': query_tree,
'errmsg': errmsg
}
else:
return None
def execute_call_back(workflowId, clusterName, url):
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
# 获取审核人
reviewMan = workflowDetail.review_man
dictConn = getMasterConnStr(clusterName)
try:
# 交给inception先split，再执行
(finalStatus, finalList) = InceptionDao().executeFinal(workflowDetail, dictConn)
# 封装成JSON格式存进数据库字段里
strJsonResult = json.dumps(finalList)
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
workflowDetail.execute_result = strJsonResult
workflowDetail.finish_time = timezone.now()
workflowDetail.status = finalStatus
workflowDetail.is_manual = 0
workflowDetail.audit_remark = ''
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception as e:
logger.error(e)
# 发送消息
send_msg(workflowDetail, url)
def execute(request):
workflowId = request.POST['workflowid']
if workflowId == '' or workflowId is None:
context = {'errMsg': 'workflowId参数为空.'}
return render(request, 'error.html', context)
workflowId = int(workflowId)
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
instance_name = workflowDetail.instance_name
db_name = workflowDetail.db_name
url = getDetailUrl(request) + str(workflowId) + '/'
user = request.user
if can_execute(request.user, workflowId) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflowDetail.status = Const.workflowStatus['executing']
workflowDetail.reviewok_time = timezone.now()
workflowDetail.save()
# 判断是通过inception执行还是直接执行，is_manual=0则通过inception执行，is_manual=1代表inception审核不通过，需要直接执行
if workflowDetail.is_manual == 0:
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
splitReviewResult = InceptionDao().sqlautoReview(
workflowDetail.sql_content,
workflowDetail.instance_name,
db_name,
isSplit='yes')
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflowDetail.review_content = json.dumps(splitReviewResult)
try:
workflowDetail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflowId, instance_name, url))
t.start()
else:
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_skipinc_call_back,
args=(workflowId, instance_name, db_name,
workflowDetail.sql_content, url))
t.start()
# 删除定时执行job
if workflowDetail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflowId)
del_sqlcronjob(job_id)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def execute(request):
workflowId = request.POST['workflowid']
if workflowId == '' or workflowId is None:
context = {'errMsg': 'workflowId参数为空.'}
return render(request, 'error.html', context)
workflowId = int(workflowId)
workflowDetail = workflow.objects.get(id=workflowId)
clusterName = workflowDetail.cluster_name
db_name = workflowDetail.db_name
url = getDetailUrl(request) + str(workflowId) + '/'
# 获取审核人
reviewMan = workflowDetail.review_man
reviewMan = reviewMan.split(',')
# 服务器端二次验证，正在执行人工审核动作的当前登录用户必须为审核人或者提交人. 避免攻击或被接口测试工具强行绕过
loginUser = request.session.get('login_username', False)
if loginUser is None or (loginUser not in reviewMan
and loginUser != workflowDetail.engineer):
context = {'errMsg': '当前登录用户不是审核人或者提交人，请重新登录.'}
return render(request, 'error.html', context)
# 服务器端二次验证，当前工单状态必须为审核通过状态
if workflowDetail.status != Const.workflowStatus['pass']:
context = {'errMsg': '当前工单状态不是审核通过，请刷新当前页面！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflowDetail.status = Const.workflowStatus['executing']
workflowDetail.reviewok_time = timezone.now()
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
splitReviewResult = InceptionDao().sqlautoReview(
workflowDetail.sql_content,
workflowDetail.cluster_name,
db_name,
isSplit='yes')
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflowDetail.review_content = json.dumps(splitReviewResult)
try:
workflowDetail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back, args=(workflowId, clusterName, url))
t.start()
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def get_osc_percent(request):
"""
获取该SQL的pt-OSC执行进度和剩余时间
"""
workflow_id = request.POST['workflow_id']
sql_id = request.POST['sqlID']
if workflow_id == '' or workflow_id is None or sql_id == '' or sql_id is None:
context = {"status": -1, 'msg': 'workflow_id或sqlID参数为空.', "data": ""}
return HttpResponse(json.dumps(context), content_type='application/json')
workflow_id = int(workflow_id)
sql_id = int(sql_id)
dict_sha1 = {}
if workflow_id in sqlSHA1_cache:
dict_sha1 = sqlSHA1_cache[workflow_id]
# cachehit = "已命中"
else:
dict_sha1 = get_sql_sha1(workflow_id)
if dict_sha1 != {} and sql_id in dict_sha1:
sql_sha1 = dict_sha1[sql_id]
try:
result = InceptionDao().get_osc_percent(sql_sha1) # 成功获取到SHA1值，去inception里面查询进度
except Exception as msg:
logger.error(traceback.format_exc())
result = {'status': 1, 'msg': msg, 'data': ''}
return HttpResponse(json.dumps(result), content_type='application/json')
if result["status"] == 0:
# 获取到进度值
pct_result = result
else:
# result["status"] == 1, 未获取到进度值,需要与workflow.execute_result对比，来判断是已经执行过了，还是还未执行
execute_result = SqlWorkflow.objects.get(id=workflow_id).execute_result
try:
list_exec_result = json.loads(execute_result)
except ValueError:
list_exec_result = execute_result
if type(list_exec_result) == list and len(list_exec_result) >= sql_id - 1:
if dict_sha1[sql_id] in list_exec_result[sql_id - 1][10]:
# 已经执行完毕，进度值置为100
pct_result = {"status": 0, "msg": "ok", "data": {"percent": 100, "timeRemained": ""}}
else:
# 可能因为前一条SQL是DML，正在执行中；或者还没执行到这一行。但是status返回的是4，而当前SQL实际上还未开始执行。这里建议前端进行重试
pct_result = {"status": -3, "msg": "进度未知", "data": {"percent": -100, "timeRemained": ""}}
elif dict_sha1 != {} and sql_id not in dict_sha1:
pct_result = {"status": 4, "msg": "该行SQL不是由pt-OSC执行的", "data": ""}
else:
pct_result = {"status": -2, "msg": "整个工单不由pt-OSC执行", "data": ""}
return HttpResponse(json.dumps(pct_result), content_type='application/json')
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)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
run_date = datetime.datetime.strptime(run_date, "%Y-%m-%d %H:%M:%S")
url = get_detail_url(request, workflow_id)
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 将流程状态修改为定时执行
workflow_detail.status = Const.workflowStatus['timingtask']
workflow_detail.save()
# 调用添加定时任务
add_sqlcronjob(job_id, run_date, workflow_id, url)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
workflowOb.add_workflow_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_job(workflow_id, url):
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
logger.debug('execute_job:' + job_id + ' start')
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
# 服务器端二次验证，当前工单状态必须为定时执行过状态
if workflow_detail.status != Const.workflowStatus['timingtask']:
raise Exception('工单不是定时执行状态')
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['executing']
workflow_detail.reviewok_time = timezone.now()
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
logger.debug('execute_job:' + job_id + ' executing')
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
split_review_result = InceptionDao(
instance_name=instance_name).sqlautoReview(workflow_detail.sql_content,
db_name,
isSplit='yes')
workflow_detail.review_content = json.dumps(split_review_result)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflow_id, instance_name, url))
t.start()
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
Workflow.add_workflow_log(audit_id=audit_id,
operation_type=5,
operation_type_desc='执行工单',
operation_info='系统定时执行',
operator='',
operator_display='系统')
def stop_osc_progress(request):
"""
中止该SQL的pt-OSC进程
"""
workflow_id = request.POST['workflow_id']
sql_id = request.POST['sqlID']
if workflow_id == '' or workflow_id is None or sql_id == '' or sql_id is None:
context = {"status": -1, 'msg': 'workflow_id或sqlID参数为空.', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
user = request.user
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
try:
review_man = json.loads(workflow_detail.audit_auth_groups)
except ValueError:
review_man = (workflow_detail.audit_auth_groups, )
# 服务器端二次验证，当前工单状态必须为等待人工审核,正在执行人工审核动作的当前登录用户必须为审核人. 避免攻击或被接口测试工具强行绕过
if workflow_detail.status != Const.workflowStatus['executing']:
context = {"status": -1, "msg": '当前工单状态不是"执行中"，请刷新当前页面！', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
if user.username is None or user.username not in review_man:
context = {"status": -1, 'msg': '当前登录用户不是审核人，请重新登录.', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
workflow_id = int(workflow_id)
sql_id = int(sql_id)
if workflow_id in sqlSHA1_cache:
dict_sha1 = sqlSHA1_cache[workflow_id]
else:
dict_sha1 = get_sql_sha1(workflow_id)
if dict_sha1 != {} and sql_id in dict_sha1:
sql_sha1 = dict_sha1[sql_id]
try:
opt_result = InceptionDao().stop_osc_progress(sql_sha1)
except Exception as msg:
logger.error(traceback.format_exc())
result = {'status': 1, 'msg': msg, 'data': ''}
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
opt_result = {"status": 4, "msg": "不是由pt-OSC执行的", "data": ""}
return HttpResponse(json.dumps(opt_result),
content_type='application/json')
def inception(request):
result = {'status': 0, 'msg': 'ok', 'data': []}
inception = InceptionDao()
try:
conn = MySQLdb.connect(host=inception.inception_host,
port=inception.inception_port,
charset='utf8')
cur = conn.cursor()
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = '无法连接inception,\n{}'.format(str(e))
else:
cur.close()
conn.close()
# 返回结果
return HttpResponse(json.dumps(result), content_type='application/json')
def rollback(request):
workflowId = request.GET['workflowid']
if workflowId == '' or workflowId is None:
context = {'errMsg': 'workflowId参数为空.'}
return render(request, 'error.html', context)
workflowId = int(workflowId)
try:
listBackupSql = InceptionDao().getRollbackSqlList(workflowId)
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflowDetail = SqlWorkflow.objects.get(id=workflowId)
workflowName = workflowDetail.workflow_name
rollbackWorkflowName = "【回滚工单】原工单Id:%s ,%s" % (workflowId, workflowName)
context = {'listBackupSql': listBackupSql, 'workflowDetail': workflowDetail,
'rollbackWorkflowName': rollbackWorkflowName}
return render(request, 'rollback.html', context)
def is_autoreview(workflow_id):
workflow_detail = SqlWorkflow.objects.get(id=workflow_id)
sql_content = workflow_detail.sql_content
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
is_manual = workflow_detail.is_manual
# 删除注释语句
sql_content = ''.join(
map(
lambda x: re.compile(r'(^--\s+.*|^/\*.*\*/;\s*$)').sub(
'', x, count=1), sql_content.splitlines(1))).strip()
# 获取正则表达式
auto_review_regex = SysConfig().sys_config.get(
'auto_review_regex', '^alter|^create|^drop|^truncate|^rename|^delete')
p = re.compile(auto_review_regex)
# 判断是否匹配到需要手动审核的语句
is_autoreview = True
for row in sql_content.strip(';').split(';'):
if p.match(row.strip().lower()):
is_autoreview = False
break
if is_autoreview:
# 更新影响行数加测,总语句影响行数超过指定数量则需要人工审核
inception_review = InceptionDao(
instance_name=instance_name).sqlauto_review(
sql_content, db_name)
all_affected_rows = 0
for review_result in inception_review:
SQL = review_result[5]
Affected_rows = review_result[6]
if re.match(r"^update", SQL.strip().lower()):
all_affected_rows = all_affected_rows + int(Affected_rows)
if int(all_affected_rows) > int(SysConfig().sys_config.get(
'auto_review_max_update_rows', 50)):
is_autoreview = False
# inception不支持语法都需要审批
if is_manual == 1:
is_autoreview = False
return is_autoreview
def stopOscProgress(request):
"""中止该SQL的pt-OSC进程"""
workflowId = request.POST['workflowid']
sqlID = request.POST['sqlID']
if workflowId == '' or workflowId is None or sqlID == '' or sqlID is None:
context = {"status": -1, 'msg': 'workflowId或sqlID参数为空.', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
loginUser = request.session.get('login_username', False)
workflowDetail = workflow.objects.get(id=workflowId)
try:
reviewMan = json.loads(workflowDetail.review_man)
except ValueError:
reviewMan = (workflowDetail.review_man, )
# 服务器端二次验证，当前工单状态必须为等待人工审核,正在执行人工审核动作的当前登录用户必须为审核人. 避免攻击或被接口测试工具强行绕过
if workflowDetail.status != Const.workflowStatus['executing']:
context = {"status": -1, "msg": '当前工单状态不是"执行中"，请刷新当前页面！', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
if loginUser is None or loginUser not in reviewMan:
context = {"status": -1, 'msg': '当前登录用户不是审核人，请重新登录.', "data": ""}
return HttpResponse(json.dumps(context),
content_type='application/json')
workflowId = int(workflowId)
sqlID = int(sqlID)
if workflowId in sqlSHA1_cache:
dictSHA1 = sqlSHA1_cache[workflowId]
else:
dictSHA1 = getSqlSHA1(workflowId)
if dictSHA1 != {} and sqlID in dictSHA1:
sqlSHA1 = dictSHA1[sqlID]
try:
optResult = InceptionDao().stopOscProgress(sqlSHA1)
except Exception as msg:
result = {'status': 1, 'msg': msg, 'data': ''}
return HttpResponse(json.dumps(result),
content_type='application/json')
else:
optResult = {"status": 4, "msg": "不是由pt-OSC执行的", "data": ""}
return HttpResponse(json.dumps(optResult), content_type='application/json')
def execute_call_back(workflowId, clusterName, url):
workflowDetail = workflow.objects.get(id=workflowId)
# 获取审核人
reviewMan = workflowDetail.review_man
dictConn = getMasterConnStr(clusterName)
try:
# 交给inception先split，再执行
(finalStatus, finalList) = InceptionDao().executeFinal(workflowDetail, dictConn)
# 封装成JSON格式存进数据库字段里
strJsonResult = json.dumps(finalList)
workflowDetail = workflow.objects.get(id=workflowId)
workflowDetail.execute_result = strJsonResult
workflowDetail.finish_time = timezone.now()
workflowDetail.status = finalStatus
workflowDetail.is_manual = 0
workflowDetail.audit_remark = ''
# 关闭后重新获取连接，防止超时
connection.close()
workflowDetail.save()
except Exception as e:
logger.error(e)
# 如果执行完毕了，则根据settings.py里的配置决定是否给提交者和DBA一封邮件提醒，DBA需要知晓审核并执行过的单子
if SysConfig().sys_config.get('mail') == 'true':
# 给申请人，DBA各发一封邮件
engineer = workflowDetail.engineer
workflowStatus = workflowDetail.status
workflowName = workflowDetail.workflow_name
strTitle = "SQL上线工单执行完毕 # " + str(workflowId)
strContent = "发起人：" + engineer + "\n审核人：" + reviewMan + "\n工单地址：" + url \
+ "\n工单名称： " + workflowName + "\n执行结果：" + workflowStatus
# 邮件通知申请人，审核人，抄送DBA
notify_users = reviewMan.split(',')
notify_users.append(engineer)
listToAddr = [email['email'] for email in users.objects.filter(username__in=notify_users).values('email')]
listCcAddr = [email['email'] for email in users.objects.filter(role='DBA').values('email')]
MailSender().sendEmail(strTitle, strContent, listToAddr, listCcAddr=listCcAddr)
# -*- coding:utf-8 -*-
from sql.utils.inception import InceptionDao
from sql.models import DataMaskingRules, DataMaskingColumns
import simplejson as json
import re
inceptionDao = InceptionDao()
class Masking(object):
# 脱敏数据
def data_masking(self, cluster_name, db_name, sql, sql_result):
result = {'status': 0, 'msg': 'ok', 'data': []}
# 通过inception获取语法树,并进行解析
try:
print_info = self.query_tree(sql, cluster_name, db_name)
except Exception as msg:
result['status'] = 1
result['msg'] = str(msg)
return result
if print_info is None:
result['status'] = 1
result['msg'] = 'inception返回的结果集为空！可能是SQL语句有语法错误'
elif print_info['errlevel'] != 0:
result['status'] = 2
result['msg'] = 'inception返回异常：\n' + print_info['errmsg']
else:
query_tree = print_info['query_tree']
# 获取实例所属环境,获取命中脱敏规则的列数据
table_hit_columns, hit_columns = self.analy_query_tree(query_tree, cluster_name)
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)
instance_name = workflow_detail.instance_name
db_name = workflow_detail.db_name
url = get_detail_url(request, workflow_id)
if can_execute(request.user, workflow_id) is False:
context = {'errMsg': '你无权操作当前工单！'}
return render(request, 'error.html', context)
# 判断是否高危SQL，禁止执行
if SysConfig().sys_config.get('critical_ddl_regex', '') != '':
if InceptionDao().critical_ddl(workflow_detail.sql_content):
context = {'errMsg': '高危语句，禁止执行！'}
return render(request, 'error.html', context)
# 将流程状态修改为执行中，并更新reviewok_time字段
workflow_detail.status = Const.workflowStatus['executing']
workflow_detail.reviewok_time = timezone.now()
workflow_detail.save()
# 判断是通过inception执行还是直接执行，is_manual=0则通过inception执行，is_manual=1代表inception审核不通过，需要直接执行
if workflow_detail.is_manual == 0:
# 执行之前重新split并check一遍，更新SHA1缓存；因为如果在执行中，其他进程去做这一步操作的话，会导致inception core dump挂掉
try:
split_review_result = InceptionDao(
instance_name=instance_name).sqlauto_review(
workflow_detail.sql_content, db_name, is_split='yes')
except Exception as msg:
logger.error(traceback.format_exc())
context = {'errMsg': msg}
return render(request, 'error.html', context)
workflow_detail.review_content = json.dumps(split_review_result)
try:
workflow_detail.save()
except Exception:
# 关闭后重新获取连接，防止超时
connection.close()
workflow_detail.save()
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_call_back,
args=(workflow_id, instance_name, url))
t.start()
else:
# 采取异步回调的方式执行语句，防止出现持续执行中的异常
t = Thread(target=execute_skipinc_call_back,
args=(workflow_id, instance_name, db_name,
workflow_detail.sql_content, url))
t.start()
# 删除定时执行job
if workflow_detail.status == Const.workflowStatus['timingtask']:
job_id = Const.workflowJobprefix['sqlreview'] + '-' + str(workflow_id)
del_sqlcronjob(job_id)
# 增加工单日志
# 获取audit_id
audit_id = Workflow.audit_info_by_workflow_id(
workflow_id=workflow_id,
workflow_type=WorkflowDict.workflow_type['sqlreview']).audit_id
workflowOb.add_workflow_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 autoreview(request):
workflow_id = request.POST.get('workflow_id')
sql_content = request.POST['sql_content']
workflow_title = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = SqlGroup.objects.get(group_name=group_name).group_id
instance_name = request.POST['instance_name']
db_name = request.POST.get('db_name')
is_backup = request.POST['is_backup']
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sql_content is None or workflow_title is None or instance_name is None or db_name is None or is_backup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
user_instances(request.user, 'master').get(instance_name=instance_name)
except Exception:
context = {'errMsg': '你所在组未关联该实例！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sql_content = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
if sql_content[-1] != ";":
context = {'errMsg': "SQL语句结尾没有以;结尾，请后退重新修改并提交！"}
return render(request, 'error.html', context)
# 交给inception进行自动审核
try:
inception_result = InceptionDao(
instance_name=instance_name).sqlauto_review(sql_content, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if inception_result is None or len(inception_result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
json_result = json.dumps(inception_result)
# 遍历result，看是否有任何自动审核不通过的地方，并且按配置确定是标记审核不通过还是放行，放行的可以在工单内跳过inception直接执行
sys_config = SysConfig().sys_config
is_manual = 0
workflow_status = Const.workflowStatus['manreviewing']
for row in inception_result:
# 1表示警告，不影响执行
if row[2] == 1 and sys_config.get('auto_review_wrong', '') == '1':
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 2表示严重错误，或者inception不支持的语法，标记手工执行，可以跳过inception直接执行
elif row[2] == 2:
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
elif re.match(r"\w*comments\w*", row[4]):
is_manual = 1
if sys_config.get('auto_review_wrong', '') in ('', '1', '2'):
workflow_status = Const.workflowStatus['autoreviewwrong']
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for row in sql_content.strip(';').split(';'):
if re.match(r"^alter|^create|^drop|^truncate|^rename",
row.strip().lower()):
sql_syntax = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.user.username
if not workflow_id:
sql_workflow = SqlWorkflow()
sql_workflow.create_time = timezone.now()
else:
sql_workflow = SqlWorkflow.objects.get(id=int(workflow_id))
sql_workflow.workflow_name = workflow_title
sql_workflow.group_id = group_id
sql_workflow.group_name = group_name
sql_workflow.engineer = engineer
sql_workflow.engineer_display = request.user.display
sql_workflow.audit_auth_groups = Workflow.audit_settings(
group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflow_status
sql_workflow.is_backup = is_backup
sql_workflow.review_content = json_result
sql_workflow.instance_name = instance_name
sql_workflow.db_name = db_name
sql_workflow.sql_content = sql_content
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflow_id = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflow_status == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
list_cc_addr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
workflowOb.addworkflowaudit(
request,
WorkflowDict.workflow_type['sqlreview'],
workflow_id,
list_cc_addr=list_cc_addr)
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 simplecheck(request):
sql_content = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
result = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sql_content is None or instance_name is None or db_name is None:
result['status'] = 1
result['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(result),
content_type='application/json')
# # 删除注释语句
# sql_content = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sql_content.splitlines(1))).strip()
# # 去除空行
# sql_content = re.sub('[\r\n\f]{2,}', '\n', sql_content)
sql_content = sql_content.strip()
# 交给inception进行自动审核
try:
inception_result = InceptionDao(
instance_name=instance_name).sqlauto_review(sql_content, db_name)
except Exception as e:
logger.error(traceback.format_exc())
result['status'] = 1
result['msg'] = 'Inception审核报错，请检查Inception配置，错误信息：\n{}'.format(str(e))
return HttpResponse(json.dumps(result),
content_type='application/json')
if inception_result is None or len(inception_result) == 0:
result['status'] = 1
result['msg'] = 'inception返回的结果集为空！可能是SQL语句有语法错误'
return HttpResponse(json.dumps(result),
content_type='application/json')
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
column_list = [
'ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL',
'Affected_rows', 'sequence', 'backup_dbname', 'execute_time', 'sqlsha1'
]
rows = []
check_warning_count = 0
check_error_count = 0
for row_index, row_item in enumerate(inception_result):
row = {}
row['ID'] = row_item[0]
row['stage'] = row_item[1]
row['errlevel'] = row_item[2]
if row['errlevel'] == 1:
check_warning_count = check_warning_count + 1
elif row['errlevel'] == 2:
check_error_count = check_error_count + 1
row['stagestatus'] = row_item[3]
row['errormessage'] = row_item[4]
row['SQL'] = row_item[5]
row['Affected_rows'] = row_item[6]
row['sequence'] = row_item[7]
row['backup_dbname'] = row_item[8]
row['execute_time'] = row_item[9]
# row['sqlsha1'] = row_item[10]
rows.append(row)
result['data']['rows'] = rows
result['data']['column_list'] = column_list
result['data']['CheckWarningCount'] = check_warning_count
result['data']['CheckErrorCount'] = check_error_count
return HttpResponse(json.dumps(result), content_type='application/json')
def pyecharts(request):
# 工单数量统计
bar1 = Bar('SQL上线工单统计(数量)', width="100%")
data = chart_dao.workflow_by_date(1)
begin_date = (date.today() - relativedelta(months=+1)).strftime("%Y-%m-%d")
attr = chart_dao.get_date_list(begin_date,
date.today().strftime("%Y-%m-%d"))
dict = {}
for row in data['rows']:
dict[row[0]] = row[1]
value = [dict.get(day) if dict.get(day) else 0 for day in attr]
bar1.add("月统计", attr, value, is_stack=False, legend_selectedmode='single')
# 工单按组统计
pie1 = Pie('SQL上线工单统计(组)', width="100%")
data = chart_dao.workflow_by_group(1)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie1.add("月统计", attr, value, is_legend_show=False, is_label_show=True)
# 工单按人统计
bar2 = Bar('SQL上线工单统计(用户)', width="100%")
data = chart_dao.workflow_by_user(1)
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
bar2.add("月统计", attr, value, is_label_show=True)
# SQL语句类型统计
pie2 = Pie("SQL上线工单统计(类型)", width="100%")
data = chart_dao.sql_syntax()
attr = [row[0] for row in data['rows']]
value = [row[1] for row in data['rows']]
pie2.add("", attr, value, is_label_show=True)
# SQL执行情况统计
pie3 = Pie("SQL上线工单统计(Inception)", width="100%")
data = InceptionDao().statistic()
attr = data['column_list']
if data['column_list']:
value = [int(row) for row in data['rows'][0]]
else:
value = []
pie3.add("", attr, value, is_legend_show=False, is_label_show=True)
# SQL查询统计(每日检索行数)
line1 = Line("SQL查询统计", width="100%")
begin_date = (date.today() - relativedelta(months=+1)).strftime("%Y-%m-%d")
attr = chart_dao.get_date_list(begin_date,
date.today().strftime("%Y-%m-%d"))
effect_data = chart_dao.querylog_effect_row_by_date(1)
effect_dict = {}
for row in effect_data['rows']:
effect_dict[row[0]] = int(row[1])
effect_value = [
effect_dict.get(day) if effect_dict.get(day) else 0 for day in attr
]
count_data = chart_dao.querylog_count_by_date(1)
count_dict = {}
for row in count_data['rows']:
count_dict[row[0]] = int(row[1])
count_value = [
count_dict.get(day) if count_dict.get(day) else 0 for day in attr
]
line1.add("检索行数",
attr,
effect_value,
is_stack=False,
legend_selectedmode='single',
mark_point=["average"])
line1.add("检索次数",
attr,
count_value,
is_stack=False,
legend_selectedmode='single',
is_smooth=True,
mark_line=["max", "average"])
# SQL查询统计(用户检索行数)
pie4 = Pie("SQL查询统计(用户检索行数)", width="100%")
data = chart_dao.querylog_effect_row_by_user(1)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie4.add("月统计",
attr,
value,
radius=[40, 75],
is_legend_show=False,
is_label_show=True)
# SQL查询统计(DB检索行数)
pie5 = Pie("SQL查询统计(DB检索行数)", width="100%")
data = chart_dao.querylog_effect_row_by_db(1)
attr = [row[0] for row in data['rows']]
value = [int(row[1]) for row in data['rows']]
pie5.add("月统计", attr, value, is_legend_show=False, is_label_show=True)
# 可视化展示页面
page = Page()
page.add(bar1)
page.add(pie1)
page.add(bar2)
page.add(pie2)
page.add(pie3)
page.add(line1)
page.add(pie4)
page.add(pie5)
myechart = page.render_embed() # 渲染配置
host = 'https://pyecharts.github.io/assets/js' # js文件源地址
script_list = page.get_js_dependencies() # 获取依赖的js文件名称（只获取当前视图需要的js）
return render(request, "dashboard.html", {
"myechart": myechart,
"host": host,
"script_list": script_list
})
def get_rollback(self):
"""获取回滚语句列表"""
ExecuteEngine = InceptionDao(instance_name=self.instance_name)
return ExecuteEngine.get_rollback_sql_list(self.workflow.id)
def simplecheck(request):
sqlContent = request.POST.get('sql_content')
instance_name = request.POST.get('instance_name')
db_name = request.POST.get('db_name')
finalResult = {'status': 0, 'msg': 'ok', 'data': {}}
# 服务器端参数验证
if sqlContent is None or instance_name is None or db_name is None:
finalResult['status'] = 1
finalResult['msg'] = '页面提交参数可能为空'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
# # 删除注释语句
# sqlContent = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sqlContent.splitlines(1))).strip()
# # 去除空行
# sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent)
sqlContent = sqlContent.strip()
if sqlContent[-1] != ";":
finalResult['status'] = 1
finalResult['msg'] = 'SQL语句结尾没有以;结尾，请重新修改并提交！'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
# 交给inception进行自动审核
try:
result = InceptionDao().sqlautoReview(sqlContent, instance_name, db_name)
except Exception as e:
finalResult['status'] = 1
finalResult['msg'] = str(e)
return HttpResponse(json.dumps(finalResult), content_type='application/json')
if result is None or len(result) == 0:
finalResult['status'] = 1
finalResult['msg'] = 'inception返回的结果集为空！可能是SQL语句有语法错误'
return HttpResponse(json.dumps(finalResult), content_type='application/json')
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
column_list = ['ID', 'stage', 'errlevel', 'stagestatus', 'errormessage', 'SQL', 'Affected_rows', 'sequence',
'backup_dbname', 'execute_time', 'sqlsha1']
rows = []
CheckWarningCount = 0
CheckErrorCount = 0
for row_index, row_item in enumerate(result):
row = {}
row['ID'] = row_item[0]
row['stage'] = row_item[1]
row['errlevel'] = row_item[2]
if row['errlevel'] == 1:
CheckWarningCount = CheckWarningCount + 1
elif row['errlevel'] == 2:
CheckErrorCount = CheckErrorCount + 1
row['stagestatus'] = row_item[3]
row['errormessage'] = row_item[4]
row['SQL'] = row_item[5]
row['Affected_rows'] = row_item[6]
row['sequence'] = row_item[7]
row['backup_dbname'] = row_item[8]
row['execute_time'] = row_item[9]
# row['sqlsha1'] = row_item[10]
rows.append(row)
finalResult['data']['rows'] = rows
finalResult['data']['column_list'] = column_list
finalResult['data']['CheckWarningCount'] = CheckWarningCount
finalResult['data']['CheckErrorCount'] = CheckErrorCount
return HttpResponse(json.dumps(finalResult), content_type='application/json')
def autoreview(request):
workflowid = request.POST.get('workflowid')
sqlContent = request.POST['sql_content']
workflowName = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = SqlGroup.objects.get(group_name=group_name).group_id
clusterName = request.POST['cluster_name']
db_name = request.POST.get('db_name')
isBackup = request.POST['is_backup']
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sqlContent is None or workflowName is None or clusterName is None or db_name is None or isBackup is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
GroupRelations.objects.get(group_name=group_name,
object_name=clusterName,
object_type=2)
except Exception:
context = {'errMsg': '该组不存在所选主库！'}
return render(request, 'error.html', context)
try:
user_masters(request.user).get(cluster_name=clusterName)
except Exception:
context = {'errMsg': '你所在组未关联该主库！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sqlContent = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;\s*$)').sub('', x, count=1),
# sqlContent.splitlines(1))).strip()
# # 去除空行
# sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent)
sqlContent = sqlContent.strip()
if sqlContent[-1] != ";":
context = {'errMsg': "SQL语句结尾没有以;结尾，请后退重新修改并提交！"}
return render(request, 'error.html', context)
# 交给inception进行自动审核
try:
result = InceptionDao().sqlautoReview(sqlContent, clusterName, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if result is None or len(result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
jsonResult = json.dumps(result)
# 遍历result，看是否有任何自动审核不通过的地方，一旦有，则需要设置is_manual = 0，跳过inception直接执行
workflowStatus = Const.workflowStatus['manreviewing']
# inception审核不通过的工单，标记手动执行标签
is_manual = 0
for row in result:
if row[2] == 2:
is_manual = 1
break
elif re.match(r"\w*comments\w*", row[4]):
is_manual = 1
break
# 判断SQL是否包含DDL语句，SQL语法 1、DDL，2、DML
sql_syntax = 2
for row in sqlContent.strip(';').split(';'):
if re.match(r"^alter|^create|^drop|^truncate|^rename",
row.strip().lower()):
sql_syntax = 1
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.user.username
if not workflowid:
sql_workflow = SqlWorkflow()
sql_workflow.create_time = timezone.now()
else:
sql_workflow = SqlWorkflow.objects.get(id=int(workflowid))
sql_workflow.workflow_name = workflowName
sql_workflow.group_id = group_id
sql_workflow.group_name = group_name
sql_workflow.engineer = engineer
sql_workflow.engineer_display = request.user.display
sql_workflow.review_man = Workflow.auditsettings(
group_id, WorkflowDict.workflow_type['sqlreview'])
sql_workflow.status = workflowStatus
sql_workflow.is_backup = isBackup
sql_workflow.review_content = jsonResult
sql_workflow.cluster_name = clusterName
sql_workflow.db_name = db_name
sql_workflow.sql_content = sqlContent
sql_workflow.execute_result = ''
sql_workflow.is_manual = is_manual
sql_workflow.audit_remark = ''
sql_workflow.sql_syntax = sql_syntax
sql_workflow.save()
workflowId = sql_workflow.id
# 自动审核通过了，才调用工作流
if workflowStatus == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
listCcAddr = [
email['email'] for email in Users.objects.filter(
username__in=notify_users).values('email')
]
workflowOb.addworkflowaudit(
request,
WorkflowDict.workflow_type['sqlreview'],
workflowId,
listCcAddr=listCcAddr)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
def autoreview(request):
workflowid = request.POST.get('workflowid')
sqlContent = request.POST['sql_content']
workflowName = request.POST['workflow_name']
group_name = request.POST['group_name']
group_id = Group.objects.get(group_name=group_name).group_id
clusterName = request.POST['cluster_name']
db_name = request.POST.get('db_name')
isBackup = request.POST['is_backup']
reviewMan = request.POST.get('workflow_auditors')
notify_users = request.POST.getlist('notify_users')
# 服务器端参数验证
if sqlContent is None or workflowName is None or clusterName is None or db_name is None or isBackup is None or reviewMan is None:
context = {'errMsg': '页面提交参数可能为空'}
return render(request, 'error.html', context)
# 验证组权限（用户是否在该组、该组是否有指定实例）
try:
GroupRelations.objects.get(group_name=group_name,
object_name=clusterName,
object_type=2)
except Exception:
context = {'errMsg': '该用户组不存在所选主库！'}
return render(request, 'error.html', context)
# # 删除注释语句
# sqlContent = ''.join(
# map(lambda x: re.compile(r'(^--.*|^/\*.*\*/;[\f\n\r\t\v\s]*$)').sub('', x, count=1),
# sqlContent.splitlines(1))).strip()
# # 去除空行
# sqlContent = re.sub('[\r\n\f]{2,}', '\n', sqlContent)
sqlContent = sqlContent.strip()
if sqlContent[-1] != ";":
context = {'errMsg': "SQL语句结尾没有以;结尾，请后退重新修改并提交！"}
return render(request, 'error.html', context)
# 交给inception进行自动审核
try:
result = InceptionDao().sqlautoReview(sqlContent, clusterName, db_name)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
if result is None or len(result) == 0:
context = {'errMsg': 'inception返回的结果集为空！可能是SQL语句有语法错误'}
return render(request, 'error.html', context)
# 要把result转成JSON存进数据库里，方便SQL单子详细信息展示
jsonResult = json.dumps(result)
# 遍历result，看是否有任何自动审核不通过的地方，一旦有，则为自动审核不通过；没有的话，则为等待人工审核状态
workflowStatus = Const.workflowStatus['manreviewing']
for row in result:
if row[2] == 2:
# 状态为2表示严重错误，必须修改
workflowStatus = Const.workflowStatus['autoreviewwrong']
break
elif re.match(r"\w*comments\w*", row[4]):
workflowStatus = Const.workflowStatus['autoreviewwrong']
break
# 调用工作流生成工单
# 使用事务保持数据一致性
try:
with transaction.atomic():
# 存进数据库里
engineer = request.session.get('login_username', False)
if not workflowid:
Workflow = workflow()
Workflow.create_time = timezone.now()
else:
Workflow = workflow.objects.get(id=int(workflowid))
Workflow.workflow_name = workflowName
Workflow.group_id = group_id
Workflow.group_name = group_name
Workflow.engineer = engineer
Workflow.review_man = reviewMan
Workflow.status = workflowStatus
Workflow.is_backup = isBackup
Workflow.review_content = jsonResult
Workflow.cluster_name = clusterName
Workflow.db_name = db_name
Workflow.sql_content = sqlContent
Workflow.execute_result = ''
Workflow.audit_remark = ''
Workflow.save()
workflowId = Workflow.id
# 自动审核通过了，才调用工作流
if workflowStatus == Const.workflowStatus['manreviewing']:
# 调用工作流插入审核信息, 查询权限申请workflow_type=2
# 抄送通知人
listCcAddr = [
email['email'] for email in users.objects.filter(
username__in=notify_users).values('email')
]
workflowOb.addworkflowaudit(
request,
WorkflowDict.workflow_type['sqlreview'],
workflowId,
listCcAddr=listCcAddr)
except Exception as msg:
context = {'errMsg': msg}
return render(request, 'error.html', context)
return HttpResponseRedirect(reverse('sql:detail', args=(workflowId, )))
