def dealsqltext(sqltext):
lexerSplitor = splitor.LexerSplitor()
list_sql = []
for sql in lexerSplitor.split(sqltext):
sql = lexerSplitor.remove_sqlcomment(sql) # 去除单行和多行注释 hint信息
sql = sql.replace('\t', ' ') #tab符改为空格
sql = sql.replace('\n', ' ') #换行符改为空格
sql = sql.replace(' , ', ',') #逗号前后空格删除
sql = sql.replace(', ', ',')
sql = sql.replace(' ,', ',')
sql = sql.replace('(', ' (') #左括号前添加空格 右括号后添加空格
sql = sql.replace(')', ') ')
sql = removespaces(sql) #删除多余的空格
#sql=sql.upper() #转为大写 这个不对 注释语句里面的值是小写的呢
list_sql.append(sql)
#换行符的问题
return list_sql
def dbause_execute(request): #ajax
#获取前端传入的数据和后台数据库数据
try:
audit_batch = request.GET['batch']
executor = request.user.username
re_batch_info = models.T_DMLAUDIT_BATCH_INFO.objects.get(
audit_batch=audit_batch)
re_modify = re_batch_info.t_dmlaudit_batch_detail_set.all()
#获取连接串信息
onedbinfo = cmdb.models.T_CMDB_DBINFO.objects.get(
db_type=re_batch_info.db_type, app_name=re_batch_info.app_name)
if onedbinfo.db_type == 'oracle':
auditobject=dmlaudit_oracle.DmlAudit(onedbinfo.username,onedbinfo.password,onedbinfo.ipadress,\
onedbinfo.port,onedbinfo.servicename)
elif onedbinfo.db_type == 'mysql':
auditobject=dmlaudit_mysql.DmlAudit(onedbinfo.username,onedbinfo.password,onedbinfo.ipadress,\
onedbinfo.port,onedbinfo.skema)
auditobject.execsql("SET autocommit=off")
except:
return HttpResponse("连接或后台数据异常，请刷新页面并重试")
else:
try:
#防止DBA同时执行
if re_batch_info.execute_status == 'init': #必须是待执行状态
re_batch_info.execute_status = "doing"
re_batch_info.save()
else:
if re_batch_info.execute_status == 'suc':
return HttpResponse("已被他人执行成功")
elif re_batch_info.execute_status == 'fail':
return HttpResponse("已被他人执行失败")
elif re_batch_info.execute_status == 'doing':
return HttpResponse("正被他人执行中")
#执行批次中的语句（备份）
exefailflag = 0
backupfailflag = 0
execute_status = ''
exe_failreason = ''
#os.chdir(constant.backup_filepath)会出现报错 不要在项目中切换路径 使用绝对路径来处理
backupflag = 0
list_backupfilename = []
for obj in re_modify:
if obj.sqltype == 'update' or obj.sqltype == 'delete': #需要备份
#获取备份语句并执行
func_name = obj.sqltype + "_change"
lexerSplitor = splitor.LexerSplitor()
uncomment_sqltext = lexerSplitor.remove_sqlcomment(
obj.sqltext)
backupsql = getattr(auditobject,
func_name)(uncomment_sqltext,
1) #这里取消注释，备份语句一般不会出错
#print backupsql
list_backup = auditobject.execsql(
backupsql) #要么就是list 要么就是str
if isinstance(list_backup, str):
backupfailflag = 1 #备份语句执行失败 全流程结果
backupfailreason = "第" + str(
obj.sqlnum) + "句备份失败--" + list_backup
break
else:
backupfilename = str(audit_batch) + "_" + str(
obj.sqlnum) + ".txt"
form_txt.Maketxt(constant.backup_filepath,
backupfilename, list_backup) #备份到txt
backupflag = 1
list_backupfilename.append(backupfilename)
str_failreason = auditobject.execsql(
obj.sqltext) #正常是none 报错返回str （select返回list)
if str_failreason is not None: #isinstance(str_failreason,str)
exefailflag = 1 #语句执行失败 全流程结束
exefailreason = "第" + str(
obj.sqlnum) + "句执行失败--" + str_failreason
break
else: #不需要备份
str_failreason = auditobject.execsql(obj.sqltext)
if str_failreason is not None:
exefailflag = 1
exefailreason = "第" + str(
obj.sqlnum) + "句执行失败--" + str_failreason
break
if backupflag == 1:
#压缩文件
form_txt.Makezip(constant.backup_filepath,
audit_batch + '.zip', list_backupfilename)
except:
if re_batch_info.execute_status == 'doing': #状态还原
re_batch_info.execute_status = "init"
re_batch_info.save()
try: #mysql下可能是超时 会话杀掉了 close_rollback会失败的
auditobject.close_rollback()
except:
pass
return HttpResponse("平台发生非预期错误，请刷新页面")
else:
if backupfailflag == 1:
execute_status = 'fail'
exe_failreason = backupfailreason
try: #mysql下可能是超时 会话杀掉了 close_rollback会失败的
auditobject.close_rollback()
except:
pass
elif exefailflag == 1:
execute_status = 'fail'
exe_failreason = exefailreason
try: #mysql下可能是超时 会话杀掉了 close_rollback会失败的
auditobject.close_rollback()
except:
pass
else:
execute_status = 'suc'
exe_failreason = ''
auditobject.close_commit() #提交
#存入数据库
re_batch_info.execute_status = execute_status
re_batch_info.exe_failreason = exe_failreason
re_batch_info.executor = executor
re_batch_info.save()
#微信
if constant.sendwechat_flag == 1: #微信开关打开时 发送微信
try:
message_content = ''
if execute_status == 'fail':
message_content = str(
audit_batch) + "批次执行失败！请线下沟通（执行人：" + str(
executor) + "）"
elif execute_status == 'suc':
message_content = str(
audit_batch) + "批次执行成功！（执行人：" + str(executor) + "）"
if message_content != '':
wechat_sender = sendwechat.WeChat() #初始化对象
msg_dict = wechat_sender.send_messages(
message_content) #调用方法发送信息 并返回信息
except:
print "微信发送失败"
else:
pass
#返回执行状态 要么suc 要么fail
if execute_status == 'suc':
return HttpResponse("成功")
elif execute_status == 'fail':
return HttpResponse("失败")
def audit(self,sqltext):
#输入的sqltext可能有很多的换行 有多行注释和单行注释 这不方面确定是什么类型的sql
#所以要处理一下 当然返回入库的还是原sqltext
lexerSplitor = splitor.LexerSplitor()
uncomment_sqltext=lexerSplitor.remove_sqlcomment(sqltext)
if uncomment_sqltext=='':
return None #纯注释语句 直接返回none
sqltext=uncomment_sqltext #mysql的注释执行有些问题 所以统一去掉注释
#非sql_species中类型的语句不通过执行
for key in DmlAudit.sql_species:
res=re.search(key,uncomment_sqltext,re.IGNORECASE|re.DOTALL) #正则处理
if res:
sqltype=DmlAudit.sql_species[key]
if sqltype=='insert':# 要判断是否是insert select
if re.search("(^INSERT INTO.*SELECT.*)",uncomment_sqltext,re.IGNORECASE|re.DOTALL):
sqltype='insert_select'
else:
break
else:
break
else:
sqltype="other"
#语法
if sqltype=='other':
grammar='invalid'
gra_failreason="本平台禁止执行非DML语句"
sqlplan=[]
else:
gra=self.connection.execsql("explain "+sqltext) #要么报错str要么返回list:执行计划
if isinstance(gra,str):
grammar='invalid'
gra_failreason=gra
sqlplan=[]
else:
grammar='valid'
gra_failreason=''
sqlplan=[]
plan_passflag=0
if len(gra)>=2 or gra[0][3]=='ALL': #关联查询 、全表扫描
plan_passflag=1
else:
plan_passflag=0
for item in gra:
#item是个元祖
tmp_list=[]
for i in item:
tmp_list.append(str(i))
strtmp=' | '.join(tmp_list)
sqlplan.append(strtmp)
sqlplan.insert(0,'id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra')
#print sqltype
#print grammar
#print gra_failreason
#print sqlplan
#执行时间预估 todo
if grammar=='valid':
exetime='待开发'
else:
exetime=''
#影响行数
if grammar=='valid':
func_name=sqltype+"_change"
sqlchange= getattr(DmlAudit,func_name)(sqltext)
#print eval(func_name)(sqltext) eval这里不行
try:
rowaffact=int(self.connection.execsql(sqlchange)[0][0])#可能异常不返回数字
except:
rowaffact=0
else:
rowaffact=0
#print rowaffact
#print exetime
#最终审核结果 超过10分钟，影响1000行
if grammar=="invalid":
audit_status="unqualified"
elif grammar=="valid" and rowaffact<=1000 and plan_passflag==0:# and exetime <= '00:10:00':
audit_status="qualified"
else:
audit_status="semi-qualified"
#print audit_status
#print sqltext
re_dict={}
for i in ['sqltext','grammar','gra_failreason','sqlplan','rowaffact','audit_status','sqltype','exetime']:
re_dict[i]=locals()[i]
return re_dict
def audit(self, sqltext):
#输入的sqltext可能有很多的换行 有多行注释和单行注释 这不方面确定是什么类型的sql
#所以要处理一下 当然返回入库的还是原sqltext
lexerSplitor = splitor.LexerSplitor()
uncomment_sqltext = lexerSplitor.remove_sqlcomment(sqltext)
if uncomment_sqltext == '':
return None #纯注释语句 直接返回none
#非sql_species中类型的语句不通过执行
for key in DmlAudit.sql_species:
res = re.search(
key, uncomment_sqltext, re.IGNORECASE | re.DOTALL
) #正则处理 忽略大小写|Make the '.' special character match any character at all
if res:
sqltype = DmlAudit.sql_species[key]
#print sqltype
if sqltype == 'insert': # 要判断是否是insert select
if re.search("(^INSERT\s*INTO.*SELECT.*)",
uncomment_sqltext, re.IGNORECASE | re.DOTALL):
sqltype = 'insert_select'
break
else:
break
else:
break
else:
sqltype = "other"
#print sqltype
#语法
if sqltype == 'other':
grammar = 'invalid'
gra_failreason = "本平台禁止执行非DML语句"
else:
gra = self.connection.execsql("explain plan for " +
sqltext) #要么报错str要么就是none
if gra:
grammar = 'invalid'
gra_failreason = gra
else:
grammar = 'valid'
gra_failreason = ''
#print grammar
#print gra_failreason
#执行计划详情 预估行数和执行时间
sqlplan = []
rowaffact = 0
exetime = ''
if grammar == 'valid':
re_sqlplan = self.connection.execsql(
"select * from table(dbms_xplan.display)")
#select * from table(dbms_xplan.display('','','OUTLINE'));
for item in re_sqlplan:
sqlplan.append(item[0])
if sqltype == 'insert':
tmp_exeplan = re_sqlplan[4][0].split(
'|') #第一行有效数据 insert一般没有plan hash value
#insert如果有sequence 那么有plan hash value 这个时候tmp_exeplan长度一般为1
if len(tmp_exeplan) < 8:
tmp_exeplan = re_sqlplan[5][0].split('|') #第一行有效数据
else:
tmp_exeplan = re_sqlplan[5][0].split('|') #第一行有效数据
#预估执行时间
exetime = tmp_exeplan[7].strip()
if exetime == '': # Bytes有时候不展示 那么7就是空 拿6
exetime = tmp_exeplan[6].strip()
#预估影响行数
evaluate_rows = tmp_exeplan[4]
rows_value = re.search('\d+', evaluate_rows.strip(), 0).group(0)
rows_unit = ''
if re.search('\D+', evaluate_rows.strip(), 0):
rows_unit = re.search('\D+', evaluate_rows.strip(), 0).group(0)
if rows_unit == 'K':
evaluate_rows = int(rows_value) * 1000
elif rows_unit == 'M':
evaluate_rows = int(rows_value) * 1000 * 1000
else:
evaluate_rows = int(rows_value)
#返回影响行数 如果预估的行数比较少 那么执行并返回精确的行数
if evaluate_rows >= 1000:
rowaffact = evaluate_rows
else:
func_name = sqltype + "_change"
sqlchange = getattr(DmlAudit,
func_name)(uncomment_sqltext) #非注释语句进行改写
#print sqlchange
try:
rowaffact = int(self.connection.execsql(sqlchange)[0]
[0]) #可能异常不返回数字的str
except:
rowaffact = evaluate_rows
#print rowaffact
#print exetime
#print sqlplan 列表
#最终审核结果 超过10分钟，影响1000行
if grammar == "invalid":
audit_status = "unqualified"
elif grammar == "valid" and rowaffact <= 1000 and exetime <= '00:10:00':
audit_status = "qualified"
else:
audit_status = "semi-qualified"
#print audit_status
#print sqltext
#audit_status="semi-qualified"#全部是待评估 用于测试
re_dict = {}
for i in [
'sqltext', 'grammar', 'gra_failreason', 'sqlplan', 'rowaffact',
'audit_status', 'sqltype', 'exetime'
]:
re_dict[i] = locals()[i]
#print re_dict
return re_dict