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)