def get(self, request):
"""
api: /new/version/sql/review/get/struct
根据实例名获取规则信息
"""
flag = request.GET.get("flag")
instance_name = request.GET.get("instance_name")
db_type = Instance.objects.get(instance_name=instance_name).db_type
if flag.upper() not in ["OBJ", "SQLPLAN", "SQLSTAT", "TEXT"]:
raise APIError(u"规则类型不正确", 30058)
records = self.mongo_client.get_collection("rule").find({
"rule_type":
flag.upper(),
"db_type":
db_type
})
temp = []
for value in records:
temp.append([
value["rule_name"], value["rule_summary"],
value["rule_status"], value["weight"], value["max_score"],
value["rule_type"], value["db_type"],
value.get("exclude_obj_type", "无")
])
context = {"message": u"查询成功", "errcode": 80050, "data": temp}
return context
def post(self, request):
"""
根据任务id，规则名称，hash_value获取统计信息和文本
"""
argument = json.loads(request.body)
task_uuid = argument.get("task_uuid", None)
sql_id_hash = argument.get("sql_id_hash", None)
rule_name = argument.get("rule_name", None)
if not task_uuid:
raise APIError(u"任务id不正确", 30063)
plan = []
sql_id = argument.get("id", None)
if sql_id == "v":
search_key = rule_name + "." + sql_id_hash + "#v"
keyword = sql_id_hash + "#v"
else:
search_key = rule_name + "." + sql_id_hash + "#" + sql_id
keyword = sql_id_hash + "#" + sql_id
record = self.mongo_client.get_collection("results").find_one(
{"task_uuid": str(task_uuid)}, {str(search_key): 1})
task_info = self.mongo_client.get_collection("job").find_one(
{"id": str(task_uuid)})
stat_title = []
stat_data = []
obj_title = []
obj_data = []
if record[rule_name][keyword]["stat"]:
temp = record[rule_name][keyword]["stat"]
stat_title = temp.keys()
stat_data = temp.values()
sql_fulltext = sqlparse.format(
record[rule_name][keyword]["sql_fulltext"], reindent=True)
if int(task_info["desc"]["port"]) == 1521:
flag = "O"
if record[rule_name][keyword]["obj_info"]:
temp = record[rule_name][keyword]["obj_info"]
obj_title = temp.keys()
[obj_data.append(str(data)) for data in temp.values()]
plan = record[rule_name][keyword]["plan"]
# 将执行计划根据id进行排序
plan.sort(key=lambda x: x['ID'])
else:
obj_title = []
obj_data = []
flag = "mysql"
plan = record[rule_name][keyword]["plan"]
context = {
"sql_fulltext": sql_fulltext,
"plan": plan,
"obj_title": obj_title,
"obj_data": [obj_data],
"stat_title": list(stat_title),
"stat_data": [list(stat_data)],
"flag": flag,
"message": u"查询成功",
"errcode": 80054
}
return context
def _jsonRes(request, *args, **kwargs):
try:
response = func(request, *args, **kwargs)
if not isinstance(response, dict):
raise APIError(u"类型错误", 10000)
if 'message' not in response:
response.update({'message': ''})
if 'errcode' not in response:
response.update({'errcode': 0})
except APIError as e:
response = {'message': e.message, 'errcode': e.errcode}
return HttpResponse(json.dumps(response,
cls=ExtendJSONEncoder,
bigint_as_string=True),
content_type='application/json')
def post(self, request):
"""
获取文本类规则的详细信息
"""
argument = json.loads(request.body)
task_uuid = argument.get("task_uuid", None)
sql_id_hash = argument.get("sql_id_hash", None)
rule_name = argument.get("rule_name", None)
if not task_uuid:
raise APIError(u"任务id不正确", 30063)
sql_id = sql_id_hash + "#v"
search_key = rule_name + "." + sql_id
record = self.mongo_client.get_collection("results"). \
find_one(
{"task_uuid": str(task_uuid)},
{str(search_key): 1}
)
sqltext = sqlparse.format(
record[rule_name][sql_id]["sql_text"], reindent=True
)
sqlstat = record[rule_name][sql_id]["stat"]
stat_title = []
stat_list = []
for index, value in enumerate(sqlstat):
if index == 0:
stat_title = value.keys()
temp = []
[temp.append(str(data)) for data in value.values()]
stat_list.append(temp)
context = {
"message": u"查询成功",
"errcode": 80055,
"sqltext": sqltext,
"stat_title": list(stat_title),
"stat_list": stat_list,
"sql_id": sql_id.split("#")[0]
}
return context
def post(self, request):
"""
根据任务id和规则名称获取规则违反的具体信息
"""
task_uuid = request.POST.get("task_uuid", None)
rule_name = request.POST.get("rule_name", None)
if not task_uuid:
raise APIError(u"任务id不正确", 30063)
results = self.mongo_client.get_collection("results").find_one(
{"task_uuid": task_uuid}, {rule_name: 1})
rule_info = self.mongo_client.get_collection("rule").find_one(
{"rule_name": rule_name})
job_info = self.mongo_client.get_collection("job").find_one(
{"id": task_uuid})
search_temp = {}
if job_info["desc"]["port"] == "1521":
search_temp.update({
"DB_IP":
job_info["desc"]["db_ip"],
"OWNER":
job_info["desc"]["owner"],
"INSTANCE_NAME":
job_info["desc"]["instance_name"].upper()
})
table_title = []
title = []
records = []
flag = ""
# 根据规则类型走不通分支
if rule_info["rule_type"] == "OBJ":
if results[rule_name]["records"]:
for data in rule_info["output_parms"]:
table_title.append(data["parm_desc"])
for data in rule_info["input_parms"]:
title.append([data["parm_value"], data["parm_desc"]])
for data in results[rule_name]["records"]:
if data not in records:
records.append(data)
flag = rule_info["rule_type"]
elif rule_info["rule_type"] in ["SQLPLAN", "SQLSTAT"]:
for key in results[rule_name].keys():
if "#" in key:
if results[rule_name][key]["obj_name"]:
obj_name = results[rule_name][key]["obj_name"]
else:
obj_name = u"空"
cost = results[rule_name][key].get("cost", None) \
if results[rule_name][key].get("cost", None) else "空"
if results[rule_name][key].get("stat"):
count = results[rule_name][key].get("stat").get(
"ts_cnt", u"空")
else:
count = "空"
records.append([
key.split("#")[0], results[rule_name][key]["sql_text"],
key.split("#")[1],
key.split("#")[2], obj_name, cost, count
])
flag = rule_info["rule_type"]
title = rule_name
table_title = ""
elif rule_info["rule_type"] == "TEXT":
for key in results[rule_name].keys():
if "#" in key:
if len(results[rule_name][key]["sql_text"]) > 40:
sqltext = results[rule_name][key]["sql_text"][:40]
else:
sqltext = results[rule_name][key]["sql_text"]
records.append([key.split("#")[0], sqltext])
flag = rule_info["rule_type"]
title = rule_name
table_title = ""
solution = "<br>".join(rule_info["solution"])
context = {
"task_uuid": task_uuid,
"title": title,
"table_title": table_title,
"rule_name": rule_name,
"records": records,
"flag": flag,
"solution": solution,
"message": u"查询成功",
"errcode": 80051
}
return context
def post(self, request):
"""
api:/new/version/sql/review/task/rule/info
根据任务id获取任务详情
"""
flag = request.POST.get("flag", None)
if flag == "1":
task_uuid = request.POST.get("task_uuid", None)
else:
task_uuid = request.POST.get("task_uuid", None)
if len(task_uuid) > 10:
raise APIError(u"选择任务过多", 30063)
if not task_uuid:
raise APIError(u"请选择相应任务", 30064)
rule_type = request.POST.get("rule_type", None)
if not rule_type:
raise APIError(u"任务类型不能为空", 30065)
results = self.mongo_client.get_collection("results").find_one(
{"task_uuid": task_uuid})
rule_info = self.mongo_client.get_collection("rule").find(
{"rule_type": rule_type.upper()})
if not rule_info:
raise APIError(u"任务类型不正确", 30066)
job_info = self.mongo_client.get_collection("job").find_one(
{"id": task_uuid})
rule_summary = {}
for value in rule_info:
rule_summary.update({
value["rule_name"]:
[value["rule_summary"], value["exclude_obj_type"]],
})
search_temp = {}
# mysql数据库的任务
port = job_info["desc"]["port"]
search_temp.update({
"DB_IP": job_info["desc"]["db_ip"],
"OWNER": job_info["desc"]["owner"]
})
mysql_score = []
[
mysql_score.append(float(data["max_score"]))
for data in self.mongo_client.get_collection("rule").find(
{
"db_type": "mysql",
"rule_type": rule_type.upper()
})
]
total_score = sum(mysql_score)
rules = []
rule_flag = ""
# 根据规则类型分类，响应不同结果
if rule_type.upper() == "OBJ":
for key, value in results.items():
if value and isinstance(results[key], dict):
if value["records"]:
temp_set = []
# compute records value
[temp_set.append(temp[0]) for temp in value["records"]]
search_temp.update(
{"OBJECT_TYPE": rule_summary[key][1]})
# prevent object
prevent_obj = self.mongo_client. \
get_collection("exclude_obj_info"). \
find(search_temp)
prevent_temp = []
[
prevent_temp.append(data["OBJECT_NAME"])
for data in prevent_obj
]
final_set = list(set(temp_set) - set(prevent_temp))
score = value["scores"]
rules.append(
[key, rule_summary[key][0],
len(final_set), score])
rule_flag = rule_type.upper()
elif rule_type.upper() in ["SQLPLAN", "SQLSTAT", "TEXT"]:
for key, value in results.items():
if value and isinstance(results[key], dict):
num = 0
for temp in results[key].keys():
if "#" in temp:
num += 1
rules.append(
[key, rule_summary[key][0], num, value["scores"]])
rule_flag = rule_type.upper()
context = {
"task_uuid": task_uuid,
"ip": search_temp["DB_IP"],
"port": port,
"schema": search_temp["OWNER"],
"rules": rules,
"rule_flag": rule_flag,
"total_score": total_score,
"message": u"查询成功",
"errcode": 80050
}
return context
def get(self, request):
"""
api: /new/version/sql/review/job/data
获取任务详情列表
"""
sEcho = request.GET.get("sEcho", None)
start = request.GET.get("start", None)
username = request.GET.get("username", None)
operuser = request.GET.get("operuser", None)
status = request.GET.get("status", None)
if status:
if status not in ["0", "1", "2"]:
raise APIError(u"状态不正确", 30060)
starttime = request.GET.get("starttime", None)
endtime = request.GET.get("endtime", None)
if starttime and endtime:
try:
starttime = time.strftime("%Y-%m-%d %H:%M:%S",
time.strptime(starttime, '%Y-%m-%d'))
endtime = time.strftime("%Y-%m-%d %H:%M:%S",
time.strptime(endtime, '%Y-%m-%d'))
except Exception:
raise APIError(u"时间戳不正确", 30062)
sql = {}
if username:
sql.update({"name": {"$regex": username}})
if operuser:
sql.update({"operator_user": {"$regex": operuser}})
if status:
sql.update({"status": status})
if starttime:
sql.update({"create_time": {"$gt": starttime}})
if endtime:
sql.update({"end_time": {"$lt": endtime}})
records = self.mongo_client.get_collection("job").find(sql). \
sort("create_time", DESCENDING).skip(int(start)).limit(10)
number = self.mongo_client.get_collection("job").find(sql).count()
result = []
for value in records:
temp = {}
if value["status"] == "1":
task_status = "成功"
elif value["status"] == "0":
task_status = "失败"
else:
task_status = "正在运行"
temp.update({
"operuser": value["operator_user"],
"username": value["name"].split("#")[0],
"create_time": value["create_time"],
"status": task_status,
"task_type": value["name"].split("#")[1],
"capture_start_time": value["desc"]["capture_time_s"],
"capture_stop_time": value["desc"]["capture_time_e"],
"id": value["id"]
})
result.append(temp)
temp = {}
res_data = {
"sEcho": sEcho,
"iTotalRecords": number,
"iTotalDisplayRecords": number,
"aaData": result
}
return res_data
def post(self, request):
rule_name = request.POST.get("id")
db_type = request.POST.get("dbtype")
flag = request.POST.get("flag")
oldvalue = request.POST.get("value", None)
value = request.POST.get("value", None)
if db_type == "Oracle":
dbtype = "O"
elif db_type == "Mysql":
dbtype = "mysql"
else:
raise APIError(u"db类型不正确", 30055)
if not rule_name:
raise APIError(u"规则名称不正确", 30057)
rule_name = rule_name.split("$")[0]
record = self.mongo_client.get_collection("rule"). \
find_one({"rule_name": rule_name, "db_type": dbtype})
if not record:
raise APIError(u"没有相关规则", 30055)
if flag == "maxscore":
self.mongo_client.get_collection("rule").update_one(
{
"rule_name": rule_name,
"db_type": dbtype
}, {"$set": {
"max_score": str(value)
}})
elif flag == "status":
if value not in ["ON", "OFF"] or oldvalue not in ["ON", "OFF"]:
raise APIError(u"状态不正确", 30054)
self.mongo_client.get_collection("rule").update_one(
{
"rule_name": rule_name,
"db_type": dbtype
}, {"$set": {
"rule_status": value
}})
elif flag == "weight":
try:
value = float(value)
except Exception:
raise APIError(u"设置错误", 30059)
self.mongo_client.get_collection("rule").update_one(
{
"rule_name": rule_name,
"db_type": dbtype
}, {"$set": {
"weight": value
}})
elif flag in ["parm1", "parm2", "parm3", "parm4", "parm5"]:
num = int(flag[-1]) - 1
edit_parm = "input_parms." + str(num) + ".parm_value"
if len(record['input_parms']) < int(flag[-1]):
raise APIError(u"设置错误", 30055)
try:
value = float(value)
except Exception:
raise APIError(u"设置错误", 30059)
self.mongo_client.get_collection("rule").update_one(
{
"rule_name": rule_name,
"db_type": dbtype
}, {"$set": {
edit_parm: value
}})
elif flag == "rule_cmd":
self.mongo_client.get_collection("rule").update_one(
{
"rule_name": rule_name,
"db_type": dbtype
}, {"$set": {
'rule_cmd': value
}})
context = {
"message": u"规则设置成功",
"errcode": 80025,
"data": value,
"olddata": oldvalue
}
return context