def post(self, request, **kwargs):
pk = kwargs.get('pk')
activetype = kwargs.get('actiontype')
sqlobj = self.model.objects.get(pk=pk)
res_data = {'status': 0}
#根据id获取sql内容，执行
if activetype == 'execute':
sqlcontent = sqlobj.sqlcontent
dbobj = self.dbmodel.objects.get(name=sqlobj.dbname)
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute;' % (
dbobj.user, dbobj.password, dbobj.host, dbobj.port)
exerz = table_structure(dbaddr, sqlobj.dbname,
sqlcontent) #此处已经对SQL语句执行完成
opidlist = []
for i in exerz: #分析执行结果
resultCode = i[4]
if resultCode != None:
sqlobj.status = 2
res_data['status'] = -1
break
else:
opidlist.append(i[7])
self.affected_rows += i[6]
self.exe_time += float(i[9])
sqlobj.status = 0
res_data['status'] = 0
sqlobj.rollbackdb = i[8]
sqlobj.status = 0
sqlobj.rollbackopid = opidlist
if activetype == "rollback":
rollbackopid = sqlobj.rollbackopid #获取sql的回滚id集合
rollbackdb = sqlobj.rollbackdb
#获取回滚语句
backsqls = ''
for opid in eval(rollbackopid)[1:]:
#对每个回滚id，在$_$Inception_backup_infomation$_$里获取它操作的表
sql = 'select tablename from $_$Inception_backup_information$_$ where opid_time=%s' % (
opid)
baktable = getbak(sql, rollbackdb)[0][0]
#每个回滚id获取到的回滚语句(可能是多条)
rollbacksql = 'select rollback_statement from %s where opid_time = %s' % (
baktable, opid)
perback = getbak(rollbacksql, rollbackdb) #每条语句的回滚结果
for baksql in perback:
#对可能多条的回滚语句，取出每一个
backsqls += baksql[0]
print backsqls
#执行回滚
dbobj = self.dbmodel.objects.get(name=sqlobj.dbname)
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute;' % (
dbobj.user, dbobj.password, dbobj.host, dbobj.port)
exerz = table_structure(dbaddr, sqlobj.dbname, backsqls)
sqlobj.save()
return JsonResponse(res_data)
def post(self, request, **kwargs):
webdata = QueryDict(request.body).dict()
username = request.user.get_username()
#inc11321eption.table_structure(webdata['sqlcontent'])
#dbname, env, sqlcontent, note
#通过前端的数据，拼接目标地址
obj = self.model.objects.get(
Q(name=webdata.get('dbname')) & Q(env=webdata.get('env')))
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-check;;' % (
obj.user, saltpwd.decrypt(obj.password), obj.host, obj.port)
print(dbaddr, obj.name, webdata['sqlcontent'])
sql_review = inception.table_structure(dbaddr, obj.name,
webdata['sqlcontent'])
print(sql_review)
for perrz in sql_review:
if perrz[4] != 'None':
print(perrz[4])
return JsonResponse({'status': -2, 'msg': perrz[4]})
#保存正常的SQL
userobj = User.objects.get(username=request.user)
webdata['commiter'] = username
sqlobj = InceptSql.objects.create(**webdata)
treaterobj = User.objects.get_or_create(username=webdata['treater'])[0]
sqlobj.sqlusers.add(userobj, treaterobj) #绑定提交人
return JsonResponse({'status': 0})
def post(self, request, **kwargs):
webdata = QueryDict(request.body).dict()
dbqs = dbconf.objects.filter(name=webdata['dbname'],
env=webdata['env'])
sqltext = inception.table_structure('check', webdata['sqlcontent'],
dbqs[0])
ret = {}
message = []
for i in sqltext:
# 语句有错误，保存报错信息
if i[2] != 0:
x = []
x.append(i[5])
x.append(i[4])
message.append(x)
ret['errormessage'] = message
ret['status'] = -1
# 语句合法，读取用户名、语句等信息
else:
ret['status'] = 0
userobj = self.request.user
webdata['commiter'] = userobj.first_name
webdata['user_obj'] = userobj
webdata['sqlcontent'] = webdata['sqlcontent'].lower()
# 保存信息到数据库
if ret['status'] == 0:
sqlconf.objects.create(**webdata)
return JsonResponse(ret)
def put(self, request, **kwargs):
pk = kwargs.get('pk')
sqlobj = self.model.objects.get(pk=pk) # 获取SQL列表对象
dbqs = dbconf.objects.filter(name=sqlobj.dbname,
env=sqlobj.env) # 获取数据库配置的对象
# 将读出的ID（字符串格式）转成列表
bkids = sqlobj.rollbackopid.split(',')
# 因为ID的字符串结尾是逗号，转换后会生成一个空字符，因此用remove做下处理
while '' in bkids:
bkids.remove('')
# 获取回滚语句
affected_rows = 0
execute_time = 0
for backid in bkids:
rollback = inception.rollbackdb(sqlobj.backdb, backid)
# 执行回滚
for i in rollback:
incobj = inception.table_structure('execute', i[0], dbqs[0])
print incobj
for i in incobj:
affected_rows += int(i[6])
execute_time += float(i[9])
self.model.objects.filter(pk=pk).update(condtion=-3)
return JsonResponse({
'status': 0,
'execute_time': execute_time,
'affected_rows': affected_rows
})
def post(self, request, **kwargs):
webdata = QueryDict(request.body).dict()
username = request.user.get_username()
#dbname, env, sqlcontent, not
dbname = webdata.get('dbname')
env = webdata.get('env')
sqlcontent = webdata.get('sqlcontent')
dbobj = model_to_dict(DBConf.objects.get(name=dbname, env=env))
dbhost = dbobj['host']
dbuser = dbobj['user']
dbpasswd = dbobj['password']
dbport = dbobj['port']
dbinfo = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-check;' % (
dbuser, dbpasswd, dbhost, dbport)
sql_review = table_structure(dbinfo, dbname, sqlcontent)
for perrz in sql_review:
if perrz[4] != "None":
return JsonResponse({'status': -2, 'msg': perrz[4]})
#检测sql，保存正常的sql
userobj = User.objects.get(username=request.user)
webdata['commiter'] = username
# # webdata['treater'] = username
# treater=webdata['treater']
treaterobj = User.objects.get_or_create(username=webdata['treater'])[0]
sqlobj = InceptSql.objects.create(**webdata)
sqlobj.sqluser.add(userobj, treaterobj) #绑定提交人，执行人
return JsonResponse({'status': 0})
def rtquery():
form = InceptionTableStructure()
sqlresult = {}
if request.method == "POST":
sqlcode = request.form.get('sqlcode')
sqlresult = inception.table_structure(sqlcode)
return render_template('v_rtquery.html',sqlresult=sqlresult,abc=sqlcode)
return render_template('v_rtquery.html')
def inception_table_structure():
form = InceptionTableStructure()
sql_review = {}
if request.method == "POST":
mysql_structure = request.form.get('mysql_structure')
sql_review = inception.table_structure(mysql_structure)
return render_template('dba_tool/inception_table_structure.html',sql_review = sql_review,abc = mysql_structure)
return render_template('dba_tool/inception_table_structure.html')
def rtquery():
form = InceptionTableStructure()
sqlresult = {}
if request.method == "POST":
sqlcode = request.form.get('sqlcode')
sqlresult = inception.table_structure(sqlcode)
return render_template('v_rtquery.html',
sqlresult=sqlresult,
abc=sqlcode)
return render_template('v_rtquery.html')
def inception_table_structure():
form = InceptionTableStructure()
sql_review = {}
if request.method == "POST":
mysql_structure = request.form.get('mysql_structure')
mysql_ip = request.form['mysql_host']
mysql_port = request.form['mysql_port']
mysql_user = request.form['mysql_user']
mysql_password = request.form['mysql_password']
mysql_database = request.form['mysql_database']
mysql_action = request.form['mysql_action']
sql_review = inception.table_structure(mysql_structure,mysql_ip,mysql_port,mysql_user,mysql_password,mysql_database,mysql_action)
return render_template('dba_tool/inception_table_structure.html',sql_review = sql_review,abc = mysql_structure)
return render_template('dba_tool/inception_table_structure.html')
def inception(request):
if request.method == "POST":
form = InceptionTableStructure(request.POST)
if form.is_valid():
mysql_structure = form.cleaned_data['sql']
ip = form.cleaned_data['ip']
port = form.cleaned_data['port']
dbname = form.cleaned_data['dbname']
sql_review = table_structure(mysql_structure,ip,port,dbname) #调用inception.py中的table_structure函数
if sql_review == ['None']:
sql_review = ['Successful']
return render_to_response('inception.html',{'form':form,'sql_review':sql_review})
else:
form = InceptionTableStructure()
return render_to_response('inception.html',{'form':form}) #must fill form
def inception(request):
if request.method == "POST":
form = InceptionTableStructure(request.POST)
if form.is_valid():
mysql_structure = form.cleaned_data['sql']
ip = form.cleaned_data['ip']
port = form.cleaned_data['port']
dbname = form.cleaned_data['dbname']
sql_review = table_structure(
mysql_structure, ip, port,
dbname) #调用inception.py中的table_structure函数
if sql_review == ['None']:
sql_review = ['Successful']
return render_to_response('inception.html', {
'form': form,
'sql_review': sql_review
})
else:
form = InceptionTableStructure()
return render_to_response('inception.html',
{'form': form}) #must fill form
def post(self, request, **kwargs):
pk = kwargs.get('pk')
webdata = QueryDict(request.body).dict()
sqlobj = self.model.objects.get(pk=pk)
# 获取语句详情
if webdata['todo'] == 'details':
data = sqlobj.sqlcontent
return JsonResponse({'status': 0, 'data': data})
# 备注详情
elif webdata['todo'] == 'note':
data = sqlobj.note
return JsonResponse({'status': 0, 'data': data})
# 执行SQL语句
else:
# 获取数据库服务器信息
dbqs = dbconf.objects.filter(name=sqlobj.name, env=sqlobj.env)
# 执行SQL语句
incobj = inception.table_structure('execute', sqlobj.sqlcontent,
dbqs[0])
affected_rows = 0
execute_time = 0
for i in incobj:
affected_rows += int(i[6])
execute_time += float(i[9])
# 修改状态,写入SQL语句额外参数
self.model.objects.filter(id=pk).update(operate=3, condtion=2)
backid = ''
for i in incobj:
if i[3] == "Execute Successfully\nBackup successfully":
backid += "%s," % i[7]
self.model.objects.filter(pk=pk).update(backdb=i[8], backid=backid)
return JsonResponse({
'status': 0,
'execute_time': execute_time,
'affected_rows': affected_rows
})
def post(self, request, **kwargs):
webdata = QueryDict(request.body).dict()
mysql_structure = webdata['sqlcontent']
inception.table_structure(mysql_structure)
return JsonResponse({'status': 0})
def post(self, request, **kwargs):
pk = kwargs.get('pk')
actiontype = kwargs.get('actiontype')
sqlobj = self.model.objects.get(pk=pk)
ret = {'status': 0}
if actiontype == 'execute':
#根据id获取SQL的内容
sqlcontent = sqlobj.sqlcontent
dbobj = self.dbmodel.objects.get(
Q(name=sqlobj.dbname) & Q(env=sqlobj.env))
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute' % (
dbobj.user, saltpwd.decrypt(
dbobj.password), dbobj.host, dbobj.port)
print(saltpwd.decrypt(dbobj.password))
exerz = inception.table_structure(dbaddr, dbobj.name,
sqlcontent) # 这里执行SQL语句
print(exerz)
affected_rows = 0 #影响行数
execute_time = 0 #执行时间
opidlist = [] #回滚id列表
for i in exerz: # 分析执行完的结果
successcode = i[4]
if successcode != 'None': #执行失败的
sqlobj.status = 2
ret['status'] = -1
ret['msg'] = i[4]
sqlobj.executerz = i[4]
break
else: #执行成功修改状态为已执行
opidlist.append(i[7])
affected_rows += 1
execute_time += float(i[9])
sqlobj.rollbackdb = i[8]
sqlobj.status = 0
sqlobj.exe_affected_rows = affected_rows
ret['status'] = 0
ret['affected_rows'] = affected_rows
ret['execute_time'] = execute_time
ret['Warning'] = ''
sqlobj.rollbackopid = opidlist
elif actiontype == 'rollback':
affected_rows = 0 #影响行数
# 第一步：获取回滚语句
rollbackopid = sqlobj.rollbackopid #取出回滚id列表
rollbackdb = sqlobj.rollbackdb #取出回滚库名
backsqls = ''
for opid in eval(rollbackopid)[1:]: # 遍历回滚id，拼接回滚语句
#1.)通过opid 备份库名, 查出备份库,备份表名
sql = 'select tablename from $_$Inception_backup_information$_$ where opid_time = %s' % (
opid)
baktable = inception.getbak(sql, rollbackdb)[0][0]
#2.)通过opid 及备份库表名查出回滚语句集合
rollbacksql = 'select rollback_statement from %s where opid_time = %s' % (
baktable, opid)
perback = inception.getbak(rollbacksql, rollbackdb)
'''
((u'DELETE FROM `inc_test2`.`mytable1` WHERE id=38;',),)
'''
#3.) 循环回滚语句集合,拼接成一个字符串
for baksql in perback:
backsqls += baksql[0]
#第二步: 执行回滚语句
dbobj = self.dbmodel.objects.get(
Q(name=sqlobj.dbname) & Q(env=sqlobj.env))
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute' % (
dbobj.user, saltpwd.decrypt(
dbobj.password), dbobj.host, dbobj.port)
try:
#1.)执行回滚语句
exerz = inception.table_structure(dbaddr, dbobj.name, backsqls)
for i in exerz: # 分析执行完的结果
successcode = i[4]
if successcode == 'None': #回滚成功
sqlobj.status = -3
affected_rows += 1
sqlobj.roll_affected_rows = affected_rows
ret['status'] = 0
ret['rollnum'] = affected_rows
except Basemodel as e:
print(e)
ret['status'] = 3
elif actiontype == 'pause':
pk = kwargs.get('pk')
actiontype = kwargs.get('pause')
sqlobj = self.model.objects.get(pk=pk)
sqlobj.status = -2
elif actiontype == 'cancelpause':
pk = kwargs.get('pk')
actiontype = kwargs.get('pause')
sqlobj = self.model.objects.get(pk=pk)
sqlobj.status = -1
elif actiontype == 'reject':
pk = kwargs.get('pk')
actiontype = kwargs.get('reject')
sqlobj = self.model.objects.get(pk=pk)
sqlobj.status = 1
sqlobj.save()
return JsonResponse(ret)
def post(self, request, **kwargs):
pk = kwargs.get('pk')
webdata = QueryDict(request.body).dict()
sqlobj = self.model.objects.get(pk=pk)
# 获取语句详情
if webdata['todo'] == 'details':
data = sqlobj.sqlcontent
return JsonResponse({'status': 0, 'data': data})
# 备注详情
elif webdata['todo'] == 'note':
data = sqlobj.note
return JsonResponse({'status': 0, 'data': data})
elif webdata['todo'] == 'pause':
sqlobj.condtion = -2
sqlobj.save()
return JsonResponse({'status': 0})
# 执行SQL语句
else:
# 获取数据库服务器信息
dbqs = dbconf.objects.get(name=sqlobj.dbname, env=sqlobj.env)
# 执行SQL语句
incobj = inception.table_structure('execute', sqlobj.sqlcontent,
dbqs)
message = []
ret = {}
affected_rows = 0
execute_time = 0
rollbackopid = ''
for i in incobj:
print i
# 执行失败
if i[4] != 'None':
x = []
x.append(i[5])
x.append(i[4])
message.append(x)
ret['errormessage'] = message
ret['status'] = -1
sqlobj.condtion = 2
sqlobj.save()
return JsonResponse(ret)
break
# 执行、备份成功
elif i[3] == "Execute Successfully\nBackup successfully":
print 1111
rollbackopid += "%s," % i[7]
affected_rows += int(i[6])
execute_time += float(i[9])
self.model.objects.filter(pk=pk).update(condtion=0)
else:
affected_rows += int(i[6])
execute_time += float(i[9])
self.model.objects.filter(pk=pk).update(backdb=i[8],
rollbackopid=rollbackopid)
if webdata['treater'] != request.user.first_name:
self.model.objects.filter(pk=pk).update(
daiwork=request.user.first_name)
# sqlobj.daiwork = request.user.first_name
# sqlobj.save()
ret['status'] = 0
ret['execute_time'] = execute_time
ret['affected_rows'] = affected_rows
return JsonResponse(ret)
def post(self, request, **kwargs):
#webdata = QueryDict(request.body).dict()
sqlid = kwargs.get('pk')
actiontype = kwargs.get('actiontype')
sqlinfo = InceptSql.objects.get(id=sqlid)
username = request.user.get_username()
dbname = sqlinfo.dbname # 取数据库名
ret = {'status': 0}
if actiontype == 'execute':
sqlcontent = sqlinfo.sqlcontent
env = sqlinfo.env
# 根据选择的数据库环境，匹配地址
dbobj = dbconf.objects.filter(name=dbname, env=env)[0]
pc = prpcrypt(crykey)
dbpasspwd = pc.decrypt(dbobj.password)
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute;' % (
dbobj.user, dbpasspwd, dbobj.host, dbobj.port)
# 配置文件的inception部分
# 执行SQL（防止同一个SQL被人已执行了，这边还没刷新 但点了执行，产生bug。执行前先检查status）
sqlstatus = sqlinfo.status
if sqlstatus != -1:
return JsonResponse({'status': -2})
exerz = inception.table_structure(
dbaddr, dbname, sqlcontent) # 遇到错误的语句，包括它后面的都不会执行 只检查
# 改变本条sql的状态
affected_rows = 0
execute_time = 0
opids = []
for rz in exerz:
rztag = rz[4]
if rztag == 'None' or re.findall('Warning', rztag): # 执行成功
ret['Warning'] = ""
if re.findall('Warning', rztag):
ret['Warning'] = rztag
sqlinfo.status = 0
# 执行结果，受影响的条数，执行所耗时间，回滚语句
sqlinfo.rollbackdb = rz[8]
affected_rows += rz[6]
execute_time += float(rz[9])
opids.append(rz[7].replace(
"'", "")) # rz[7].replace("'","") : 每条sql执行后的回滚opid
else: # 执行失败的结果
sqlinfo.status = 2
sqlinfo.executerz = exerz
ret['msg'] = rztag
ret['status'] = -1
break
sqlinfo.rollbackopid = opids
sqlinfo.exe_affected_rows = affected_rows
ret['affected_rows'] = affected_rows
ret['execute_time'] = '%.3f' % execute_time # 保留3位小数
if username != sqlinfo.treater: # 如果是dba或总监代执行的
note = sqlinfo.note + ' [' + username + '代执行]'
sqlinfo.note = note
# mail
if env == 1: # 线上环境，发邮件提醒
treater = sqlinfo.treater # 执行人
commiter = sqlinfo.commiter # 提交人
mailto_users = [treater, commiter]
mailto_users = list(
set(mailto_users)) # 去重（避免提交人和执行人是同一人，每次收2封邮件的bug）
mailto_list = [
UserProfile.objects.get(username=username).email
for m in mailto_users
]
# 发送邮件，并判断结果
mailtype = 'execute'
send_mail.delay(mailto_list, username, sqlid, sqlinfo.note,
mailtype, sqlcontent, dbname)
elif actiontype == 'reject':
sqlinfo.status = 1
if username != sqlinfo.treater:
note = sqlinfo.note + ' [' + username + '代放弃]'
sqlinfo.note = note
elif actiontype == 'pause':
sqlinfo.status = -2
elif actiontype == 'cancelpause':
sqlinfo.status = -1
elif actiontype == 'rollback': # 回滚
rollbackopid = sqlinfo.rollbackopid
rollbackdb = sqlinfo.rollbackdb # 回滚库
env = sqlinfo.env
# 根据选择的数据库环境，匹配地址
dbobj = dbconf.objects.filter(name=dbname, env=env)[0]
pc = prpcrypt(crykey)
dbpasspwd = pc.decrypt(dbobj.password)
# 拼接回滚语句
backsqls = '' # 回滚语句
for opid in eval(rollbackopid)[1:]:
# 1 从回滚总表中获取表名
backsource = 'select tablename from $_$Inception_backup_information$_$ where opid_time = "%s" ' % opid
backtable = inception.getbak(backsource, rollbackdb)[0][0]
# 2 从回滚子表中获取回滚语句
backcontent = 'select rollback_statement from %s.%s where opid_time = "%s" ' % (
rollbackdb, backtable, opid)
per_rollback = inception.getbak(backcontent) # 获取回滚数据
for i in per_rollback: # 累加拼接
backsqls += i[0]
# 拼接回滚语句 执行回滚操作，修改sql状态
dbaddr = '--user=%s; --password=%s; --host=%s; --port=%s; --enable-execute;' % (
dbobj.user, dbpasspwd, dbobj.host, dbobj.port)
exerz = inception.table_structure(dbaddr, dbname, backsqls)
sqlinfo.status = -3
roll_affected_rows = len(exerz) - 1
sqlinfo.roll_affected_rows = roll_affected_rows
ret['rollnum'] = roll_affected_rows # 执行回滚语句的结果，除去第一个use 数据库的
if username != sqlinfo.treater:
note = sqlinfo.note + ' [' + username + '代回滚]'
sqlinfo.note = note
sqlinfo.save()
return JsonResponse(ret)
