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})
