def get(self, request): user_info = GetUserInfo(request) review_work_info = models.InceptionWorkOrderInfo.objects.filter( work_user=user_info[0]['user_name']).all() audit_work_info = models.InceptionWorkOrderInfo.objects.filter( review_user=user_info[0]['id'], review_status=10).all() detail_sql_info = models.InceptionWorkOrderInfo.objects.filter( work_user=user_info[0]['user_name']).all().values( 'work_order_id', 'inceptionauditdetail__sql_sid', 'inceptionauditdetail__status', 'inceptionauditdetail__error_msg', 'inceptionauditdetail__sql_content', 'inceptionauditdetail__aff_row', ) rollback = models.InceptionAuditDetail.objects.filter(flag=3).exclude( backup_dbname='None').all() return render( request, 'inception/SqlWorkView.html', { 'user_info': user_info, 'review_work_info': review_work_info, 'audit_work_info': audit_work_info, 'detail_sql_info': detail_sql_info, 'rollback_flag': rollback })
def get(self, request): user_info = GetUserInfo(request) host_ip = request.GET.get('host_ip', None) table_schema = request.GET.get('table_schema', None) if not host_ip: host_info = models.HostInfo.objects.filter( app_type__app_name='MySQL').values('host_ip') return render(request, 'get_metadata.html', { 'host_info': host_info, 'user_info': user_info }) # ajax 请求获取数据 result_dict = {'db_info': [], 'table_info': []} if host_ip: db_list = models.MetaDataDatabase.objects.filter( host_ip=host_ip).all() for item in db_list: result_dict['db_info'].append(item.schema_name) if table_schema: table_list = models.MetaDataTables.objects.filter( host_ip=host_ip, table_schema=table_schema).all() for item in table_list: result_dict['table_info'].append(item.table_name) return HttpResponse(json.dumps(result_dict))
def get(self, request, sid, host_id): user_info = GetUserInfo(request) slow_obj = models.SlowQuery.objects.filter( reviewed_status__isnull=True, checksum=sid).values( 'sample', 'first_seen', 'last_seen', 'slowqueryhistory__db_max', 'slowqueryhistory__hostname_max').annotate( ts_cnt=Sum('slowqueryhistory__ts_cnt'), sum_query_time=Sum('slowqueryhistory__query_time_sum'), min_query_time=Max('slowqueryhistory__query_time_min'), max_query_time=Max('slowqueryhistory__query_time_max'), sum_lock_time=Sum('slowqueryhistory__lock_time_sum'), min_lock_time=Max('slowqueryhistory__lock_time_min'), max_lock_time=Max( 'slowqueryhistory__lock_time_max')).order_by('-ts_cnt') db_name = slow_obj[0]['slowqueryhistory__db_max'] slow_host_obj = models.HostAPPAccount.objects.filter(id=host_id).all() host_ip = slow_host_obj[0].host.host_ip app_user = slow_host_obj[0].app_user app_pass = slow_host_obj[0].app_pass app_port = slow_host_obj[0].app_port slow_db = functions.DBAPI(host=host_ip, user=app_user, password=app_pass, port=int(app_port), database=db_name) if slow_db.error: error_msg = '无法连接主机: {hostname}上的数据库, 错误信息: {error_msg}'.format( hostname=host_ip, error_msg=slow_db.error) return render( request, 'SlowQuery/QueryOptimize.html', { 'slow_obj': slow_obj, 'user_info': user_info, 'error_msg': error_msg }) else: sql_content = SQLparser.QueryRewrite().format_sql( slow_obj[0]["sample"]) explain_sql = "explain " + sql_content.lower() explain_result = slow_db.conn_query(explain_sql) explain_col = slow_db.get_col() table_list = SQLparser.QueryTableParser().parse(explain_sql) table_info_dict = None if table_list: table_info_dict = GetTableInfo.get_table_info( slow_db, table_list) return render( request, 'SlowQuery/QueryOptimize.html', { 'slow_obj': slow_obj, 'user_info': user_info, 'explain_result': explain_result, 'explain_col': explain_col, 'table_info_dict': table_info_dict })
def get(self, request): user_info = GetUserInfo(request) obj = forms.SqlComForm() return render( request, 'inception/SqlCommit.html', { 'user_info': user_info, 'SqlComFormObj': obj, 'AuditResult': self.result_dict, })
def get(self, request): user_info = GetUserInfo(request) host_obj = models.HostAPPAccount.objects.all().values_list( 'host__host_ip', 'id') slow_obj = None host_id = request.GET.get('slow_id', None) page_numbers = 10 contacts = "" if host_id: host_id = int(host_id) host_obj = models.HostAPPAccount.objects.all().values_list( 'host__host_ip', 'id') slow_host_obj = models.HostAPPAccount.objects.filter( id=host_id).all() host_ip = slow_host_obj[0].host.host_ip slow_obj = models.SlowQuery.objects.filter( reviewed_status__isnull=True, slowqueryhistory__hostname_max=host_ip).values( 'aid', 'checksum', 'sample', 'slowqueryhistory__db_max', 'slowqueryhistory__hostname_max').annotate( ts_cnt=Sum('slowqueryhistory__ts_cnt'), sum_query_time=Sum('slowqueryhistory__query_time_sum'), min_query_time=Max('slowqueryhistory__query_time_min'), max_query_time=Max('slowqueryhistory__query_time_max'), sum_lock_time=Sum('slowqueryhistory__lock_time_sum'), min_lock_time=Max('slowqueryhistory__lock_time_min'), max_lock_time=Max('slowqueryhistory__lock_time_max' )).order_by('-ts_cnt') if slow_obj: paginator = Paginator(slow_obj, page_numbers) page = request.GET.get('page') try: contacts = paginator.page(page) except PageNotAnInteger: # If page is not an integer, deliver first page. contacts = paginator.page(1) except EmptyPage: # If page is out of range (e.g. 9999), deliver last page of results. contacts = paginator.page(paginator.num_pages) return render( request, 'SlowQuery/SlowQuery.html', { 'slow_obj': slow_obj, 'host_obj': host_obj, 'host_id': host_id, 'contacts': contacts, 'user_info': user_info })
def get(self, request): user_info = GetUserInfo(request) run_work_info = models.InceptionWorkOrderInfo.objects.filter(work_user=user_info[0]['user_name'], review_status=0, work_status=10).all() detail_sql_info = models.InceptionWorkOrderInfo.objects.filter( review_user=user_info[0]['id'], review_status=0 ).all().values( 'work_order_id', 'inceptionauditdetail__sql_sid', 'inceptionauditdetail__status', 'inceptionauditdetail__error_msg', 'inceptionauditdetail__sql_content', 'inceptionauditdetail__aff_row', ) return render(request, 'inception/SqlRunning.html', {'user_info': user_info, 'run_work_info': run_work_info, 'detail_sql_info': detail_sql_info})
def post(self, request): result_dict = {'status': 0, 'error_msg': 1} user_info = GetUserInfo(request) audit_flag = request.POST.get('flag', None) wid = request.POST.get('wid', None) now_time = datetime.datetime.now() if audit_flag and wid: if audit_flag == '驳回': audit_flag = 1 else: audit_flag = 0 try: models.InceptionWorkOrderInfo.objects.filter( work_order_id=wid).update(review_status=audit_flag, review_time=now_time) models.WorkOrderTask.objects.filter(work_order_id=wid).update( audit_status=audit_flag) result_dict['status'] = 1 except Exception as e: result_dict['data'] = str(e) else: result_dict['data'] = '发送数据不对, 请联系管理员' return HttpResponse(json.dumps(result_dict))
def post(self, request): user_info = GetUserInfo(request) obj = forms.SqlComForm(request.POST) if obj.is_valid(): self.result_dict['post_flag'] = 1 work_order_id = get_uuid() host_id = obj.cleaned_data['host'] port = obj.cleaned_data['port'] db_name = obj.cleaned_data['db_name'] run_time = obj.cleaned_data['run_time'] sql_content = 'use ' + db_name + ';' + obj.cleaned_data[ 'sql_content'] db_info = models.HostAPPAccount.objects.filter( host_id=host_id, host__app_type__app_name='MySQL', app_port=port).values('host__host_ip', 'app_user', 'app_pass') # check db info if not db_info: self.result_dict['error'] = '无当前数据库信息,请确认数据库地址与端口号!!!' # check db login else: db_host = db_info[0]['host__host_ip'] db_user = db_info[0]['app_user'] db_passwd = db_info[0]['app_pass'] db_check_flag = functions.DBAPI(db_host, db_user, db_passwd, port) if db_check_flag.error: self.result_dict['error'] = '无连接当前数据库,请确认联系管理员!!!' else: self.result_dict['status'] = 1 sql_audit = functions.SplitSql(3, obj.cleaned_data['sql_content']) sql_audit_status = sql_audit.get_audit() if not sql_audit_status['status']: self.result_dict['status'] = 0 self.result_dict['error'] = '语句不合法' if self.result_dict['status'] == 1: # auto audit sql ince = Inception(db_host=db_host, db_user=db_user, db_passwd=db_passwd, db_port=port, sql_content=sql_content) result = ince.audit_sql() if isinstance(result, dict): self.result_dict['status'] = 0 self.result_dict['error'] = '无连接 Inception,请联系管理员!!!' else: self.result_dict = result_tran(result, self.result_dict) if obj.cleaned_data['is_commit'] == '1' and self.result_dict[ 'status'] == 1: # commit audit sql self.result_dict['running'] = 1 master_result = functions.get_master( db_host, db_user, db_passwd, port, db_name) master_ip = master_result['data'] # InceptionWorkOrderInfo models.InceptionWorkOrderInfo.objects.create( work_title=obj.cleaned_data['title'], work_order_id=work_order_id, work_user=user_info[0]['user_name'], db_host=db_info[0]['host__host_ip'], db_name=db_name, master_host=master_ip, review_user_id=obj.cleaned_data['review_name'], work_cron_time=datetime.datetime.now() if run_time == None else run_time, comm=obj.cleaned_data['comm']) # InceptionAuditDetail for id in self.result_dict['data']: models.InceptionAuditDetail.objects.create( work_order_id=work_order_id, sql_sid=id, flag=1, status=self.result_dict['data'][id]['status'], status_code=self.result_dict['data'][id] ['status_code'], error_msg=self.result_dict['data'][id] ['error_msg'], sql_content=self.result_dict['data'][id]['sql'], aff_row=self.result_dict['data'][id]['rows'], rollback_id=self.result_dict['data'][id] ['rollback_id'], backup_dbname=self.result_dict['data'][id] ['backup_dbname'], execute_time=self.result_dict['data'][id] ['execute_time'], sql_hash=self.result_dict['data'][id]['sql_hash']) # InceAuditSQLContent models.InceAuditSQLContent.objects.create( work_order_id=work_order_id, sql_content=sql_content) # WorkOrderTask models.WorkOrderTask.objects.create( work_order_id=work_order_id, host_ip=master_ip, app_user=db_user, app_pass=db_passwd, app_port=port, db_name=db_name) else: self.result_dict['error'] = json.dumps(obj.errors) return render( request, 'inception/SqlCommit.html', { 'user_info': user_info, 'SqlComFormObj': obj, 'AuditResult': self.result_dict, })
def post(self, request, wid): user_info = GetUserInfo(request) return render(request, 'HostGroupManage.html', {'user_info': user_info})
def index(request): user_info = GetUserInfo(request) return render(request, 'index.html', {'user_info': user_info})
def get(self, request): user_info = GetUserInfo(request) result_dict = { 'table_info': {}, 'column_info': {}, 'index_info': {}, 'table_sc': { 'sql_content': '' } } host_ip = request.GET.get('host_ip', None) table_schema = request.GET.get('table_schema', None) table_name = request.GET.get('table_name', None) table_info = models.MetaDataTables.objects.filter( host_ip=host_ip, table_schema=table_schema, table_name=table_name).all() column_info = models.MetaDataColumns.objects.filter( host_ip=host_ip, table_schema=table_schema, table_name=table_name).all() index_info = models.MetaDataIndexs.objects.filter( host_ip=host_ip, table_schema=table_schema, table_name=table_name).all() table_sc = models.MetaDataTables.objects.filter( table_name=table_name, table_schema=table_schema, host_ip=host_ip).values('metadatatablestructure__content') for item in table_info: result_dict['table_info']['table_name'] = item.table_name result_dict['table_info']['engine'] = item.engine result_dict['table_info']['row_format'] = item.row_format result_dict['table_info']['table_rows'] = item.table_rows result_dict['table_info']['avg_row_length'] = item.avg_row_length result_dict['table_info']['max_data_length'] = item.max_data_length result_dict['table_info']['data_length'] = item.data_length result_dict['table_info']['index_length'] = item.index_length result_dict['table_info']['data_free'] = item.data_free result_dict['table_info']['chip_size'] = item.chip_size result_dict['table_info']['auto_increment'] = item.auto_increment result_dict['table_info']['table_collation'] = item.table_collation result_dict['table_info']['create_time'] = str(item.create_time) result_dict['table_info']['create_time'] = str(item.create_time) result_dict['table_info']['check_time'] = str(item.check_time) result_dict['table_info']['table_comment'] = item.table_comment for item in column_info: result_dict['column_info'][item.column_name] = {} result_dict['column_info'][ item.column_name]['column_name'] = item.column_name result_dict['column_info'][ item.column_name]['column_type'] = item.column_type result_dict['column_info'][ item.column_name]['collation_name'] = item.collation_name result_dict['column_info'][ item.column_name]['is_nullable'] = item.is_nullable result_dict['column_info'][ item.column_name]['column_key'] = item.column_key result_dict['column_info'][ item.column_name]['column_default'] = item.column_default result_dict['column_info'][item.column_name]['extra'] = item.extra result_dict['column_info'][ item.column_name]['privileges'] = item.privileges result_dict['column_info'][ item.column_name]['column_comment'] = item.column_comment for item in index_info: result_dict['index_info'][item.index_md5] = {} result_dict['index_info'][ item.index_md5]['column_name'] = item.column_name result_dict['index_info'][ item.index_md5]['non_unique'] = item.non_unique result_dict['index_info'][ item.index_md5]['index_name'] = item.index_name result_dict['index_info'][ item.index_md5]['seq_in_index'] = item.seq_in_index result_dict['index_info'][ item.index_md5]['cardinality'] = item.cardinality result_dict['index_info'][ item.index_md5]['nullable'] = item.nullable result_dict['index_info'][ item.index_md5]['index_type'] = item.index_type result_dict['index_info'][ item.index_md5]['index_comment'] = item.index_comment for item in table_sc: result_dict['table_sc']['sql_content'] = item[ 'metadatatablestructure__content'] return HttpResponse(json.dumps(result_dict))
def get(self, request): user_info = GetUserInfo(request) return render(request, 'collect_metadata.html', {'user_info': user_info})