def delLrnode(self):
# 删除LR记录
sql_6 = """delete from lr_node where lr_id='%s'""" % self.lr_id
db.execFetch(sql_6)
level_id_res = redis_client.keys("lr:*:%s" % self.lr_id)
if len(level_id_res) == 0:
level_id = None
self.logger.warn("Not found level_id by lr_id [%s]" % self.lr_id)
else:
level_id = level_id_res[0].split(":")[1]
if level_id:
# sql_check = """select lr_id from lr_node where level_id='%s'""" % level_id
# result = db.execFetch(sql_check)
result = redis_client.keys("lr:%s:*" % level_id)
# print "*"*20
level_cluster, _ = write_level(level_id)
redis_client.hset("level:%s" % level_id, "level_cluster",
level_cluster)
if len(result) <= 1:
sql_delete = """delete from `level_node` where `level_id`='%s'""" % level_id
db.execOnly(sql_delete)
redis_client.delete("level:%s" % level_id)
reset_ancestors()
self.logger.info("Del lr_node with lrid=%s success." % level_id)
return {"code": 200, "status": "success"}
def check_lrg(lrgid, lrgname, vspid):
sqls = """select * from `lr_grid` where `lrg_id`='%s'""" % lrgid
res = db.execFetch(sqls)
if len(res) == 0:
sqli = """insert into `lr_grid` (`lrg_id`, `lrg_name`, `vsp_id`) values ('%s', '%s', '%s')""" % (
lrgid, lrgname, vspid)
db.execOnly(sqli)
return True
return False
def delLrnode(self):
# sql_1 = """select `lr_id`,`plr_id` from `lr_node` where `plr_id` REGEXP '%s'"""%self.lr_id
# sql_3 = """select `plr_id` from `lr_node` where `lr_id`='%s'"""%self.lr_id
# # 更新以本身为父层的LR记录
# res = db.execFetch(sql_1)
# if len(res) > 0:
# for node in res:
# lrid = node[0]
# plrid = eval(node[1])
# plrid.remove(self.lr_id)
# if len(plrid) == 0:
# ret = db.execFetch(sql_3)[0][0]
# new_plr = ret
# else: new_plr = str(plrid)
# self.logger.info("update lr_node -> lrid : %s, parent_lr: %s"%(lrid, new_plr))
# sql_2 = """update `lr_node` set `plr_id`="%s" where `lr_id`='%s'"""%(new_plr, lrid)
# db.execOnly(sql_2)
# # 更新以本身为LR的用户记录
# sql_4 = """select `ep_id`,`lr_id` from `adjacency` where lr_id REGEXP '%s'"""%self.lr_id
# res = db.execFetch(sql_4)
# if len(res) > 0:
# for user in res:
# userid = user[0]
# userlr = eval(user[1])
# userlr.remove(self.lr_id)
# if len(userlr) == 0:
# new_userlr = db.execFetch(sql_3)[0][0]
# else: new_userlr = str(userlr)
# self.logger.info("update user table -> ep_id: %s, lr_id: %s"%(userid, new_userlr))
# sql_5 = """update `adjacency` set `lr_id`="%s" where `ep_id`='%s'"""%(new_userlr, userid)
# db.execOnly(sql_5)
# 删除LR记录
sql_6 = """delete from lr_node where lr_id='%s'""" % self.lr_id
sql_s = """select `level_id` from lr_node where `lr_id`='%s'""" % self.lr_id
level_id_res = db.execFetch(sql_s)
if len(level_id_res) == 0:
level_id = None
self.logger.warn("Not found level_id by lr_id [%s]" % self.lr_id)
else:
level_id = level_id_res[0][0]
# res = db.execFetch(sql_6)
# if res > 0:
if level_id:
sql_check = """select lr_id from lr_node where level_id='%s'""" % level_id
result = db.execFetch(sql_check)
if len(result) <= 1:
sql_delete = """delete from `level_node` where `level_id`='%s'""" % level_id
db.execOnly(sql_delete)
netqos = netQos(level_id)
netqos.lrDel()
db.execFetch(sql_6)
self.logger.info("Del lr_node with lrid=%s success." % level_id)
return {"code": 200, "status": "success"}
def addLrnode(self, sql_data):
sqli = """insert into lr_node (`lr_id`, `name`, `plevel_id`, `ip`, `port`, `operator`, `cloud`, `price`, `lr_type`, `sysload`, `lrc_ids`, `level_id`, `last_subtime`) values ( "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", %s, "%s", "%s", %s)""" % sql_data
try:
res = db.execOnly(sqli)
except:
res = 0
plevel_id = sql_data[2]
level_id = sql_data[-2]
if res > 0:
sql_select = """select `level_id` from `level_node`"""
level_list = [x[0] for x in db.execFetch(sql_select)]
if level_id not in level_list:
sql_1 = """insert into level_node (`level_id`, `plevel_id`) values ('%s', '%s')""" % (
level_id, plevel_id)
db.execOnly(sql_1)
netqos = netQos(level_id)
ret = netqos.lrAddall()
if ret > 0:
self.logger.info("Add lr_node with lrid=%s done." %
self.lr_id)
return {
"code": 200,
"status": "success",
"msg": "Add lr_node with lrid=%s done." % self.lr_id
}
else:
self.logger.error(
"Add lr_node with lrid=%s fail, on update table netqos."
% self.lr_id)
return {
"code":
400,
"status":
"failed",
"msg":
"Add lr_node with lrid=%s fail, on update table netqos."
% self.lr_id
}
else:
self.logger.info("Add lr_node success, level_id exists.")
return {
"code": 200,
"status": "success",
"msg": "Add lr_node success, level_id exists."
}
else:
self.logger.error("Add lr_node with lrid=%s failed." % self.lr_id)
return {
"code": 400,
"status": "exist",
"msg": "Add lr_node with lrid=%s failed." % self.lr_id
}
def initDb():
''' 初始化数据库，在程序运行之初执行一次。或是在数据库数据与SOTP中数据有了差距时重新初始化。 '''
logger = logging.getLogger("nc")
tables = nc_config.tables
# 清除三个表所有数据
logger.info("Truncate tables start...")
for t in tables:
try:
sqld = """truncate table `%s`""" % t
db.execOnly(sqld) # -------------------
logger.info("Truncate table [%s] done." % t)
except Exception, e:
logger.error(e)
sys.exit(0)
def modLrnode(self, sql_data, level_id, plevel_id, old_level, lrid):
sqlu = """update lr_node set `name`="%s", `plevel_id`="%s", `ip`="%s", `port`="%s", `operator`="%s", `cloud`="%s", `price`="%s", `lr_type`="%s", `sysload`=%s, `lrc_ids`="%s", `level_id`='%s', `last_subtime`=%s where `lr_id`='%s' """ % sql_data
sqlu_1 = """update level_node set `plevel_id`='%s' where `level_id`='%s'""" % (
plevel_id, level_id)
sql_select = """select `level_id` from `level_node`"""
level_list = [x[0] for x in db.execFetch(sql_select)]
# sql_parent = """select `plevel_id` from level_node where `level_id`='%s'"""%old_level
# old_parent_level = db.execFetch(sql_parent)[0][0]
if level_id not in level_list:
sql_1 = """insert into level_node (`level_id`, `plevel_id`) values ('%s', '%s')""" % (
level_id, plevel_id)
if old_level:
sql_check = """select lr_id from lr_node where level_id='%s'""" % old_level
result = db.execFetch(sql_check)
if len(result) <= 1:
sql_s_old = """select `plevel_id` from level_node where `level_id`='%s'""" % old_level
sql_u_old = """update level_node set `plevel_id`='%s' where `plevel_id`='%s'"""
need_r = db.execFetch(sql_s_old)
if need_r:
db.execFetch(sql_u_old % (need_r[0][0], old_level))
sql_delete = """delete from `level_node` where `level_id`='%s'""" % old_level
db.execOnly(sql_delete)
netqos = netQos(old_level)
netqos.lrDel()
db.execOnly(sql_1)
netqos = netQos(level_id)
ret = netqos.lrAddall()
if ret > 0:
self.logger.info("Add new level and qos for lrid=%s done." %
self.lr_id)
# return {"code": 200, "status": "success", "msg": "Add new level and qos for lrid=%s done."%self.lr_id}
else:
self.logger.error(
"Add new level for lrid=%s fail, on update table netqos." %
self.lr_id)
# return {"code": 400, "status": "failed", "msg": "Add new level for lrid=%s fail, on update table netqos."%self.lr_id}
else:
if level_id != old_level:
sql_check = """select lr_id from lr_node where level_id='%s'""" % old_level
result = db.execFetch(sql_check)
if len(result) <= 1:
sql_delete = """delete from `level_node` where `level_id`='%s'""" % old_level
db.execOnly(sql_delete)
netqos = netQos(old_level)
netqos.lrDel()
# print sqlu
try:
db.execOnly(sqlu_1)
res = db.execOnly(sqlu)
except Exception, e:
# print "**************",e
res = 0
def delGrid(self):
sqld = """delete from lr_grid where `lrg_id`='%s'""" % self.lrgid
ret = db.execOnly(sqld)
if ret:
self.logger.info("Delete lr_grid [%s] success." % self.lrgid)
else:
self.logger.error("Delete lr_grid [%s] failed." % self.lrgid)
sqls = """select `lrc_id`,`lrg_ids` from lr_cluster"""
res = db.execFetch(sqls)
sqlu = """update `lr_cluster` set `lrg_ids`='%s' where `lrg_ids`='%s'"""
for lrc in res:
lrgs = eval(lrc[1])
if self.lrgid in lrgs:
lrgs.remove(self.lrgid)
db.execOnly(sqlu % (str(lrgs), lrc[0]))
return True
def delCluster(self):
sqld = """delete from `lr_cluster` where `lrc_id`='%s'""" % self.lrcid
ret = db.execOnly(sqld)
if ret:
self.logger.info("Delete lr_cluster [%s] success." % self.lrcid)
else:
self.logger.error("Delete lr_cluster[%s] failed." % self.lrcid)
sqls = """ select `lr_id`,`lrc_ids` from `lr_node` where `lrc_ids`!='[]'"""
res = db.execFetch(sqls)
sqlu = """update `lr_node` set `lrc_ids`="%s" where `lr_id`='%s'"""
for lr in res:
lrc_ids = eval(lr[1])
if self.lrcid in lrc_ids:
lrc_ids.remove(self.lrcid)
db.execOnly(sqlu % (str(lrc_ids), lr[0]))
return True
def modCluster(self, lrc_name, lrg_id, region):
sqlu = """update `lr_cluster` set `lrc_name`="%s", `lrg_ids`="%s", `region`="%s" where `lrc_id`='%s'"""
# print sqlu % (lrc_name, lrg_id, region, self.lrcid)
try:
res = db.execOnly(sqlu % (lrc_name, lrg_id, region, self.lrcid))
except Exception, e:
# print "#####################",e
res = 0
def lrDel(self):
del_list = redis_client.keys("qos:*:%s" % self.src_level)
del_list += redis_client.keys("qos:%s:*" % self.src_level)
for k in del_list:
redis_client.delete(k)
sqld = """delete from net_qos where `level_src`='%s' or `level_dst`='%s'""" % (
self.src_level, self.src_level)
self.logger.info("delete record with level_id -> %s" % self.src_level)
return db.execOnly(sqld)
def modGrid(self, grid_name, vsp_id):
sqlu = """update lr_grid set %s='%s' where lrg_id='%s'"""
if grid_name:
res = db.execOnly(sqlu % ("lrg_name", grid_name, self.lrgid))
if res:
self.logger.info("Modify lr_grid [%s] success." % self.lrgid)
else:
self.logger.error(
"Modify lr_grid [%s] failed, please check if lr_grid exists."
% self.lrgid)
if vsp_id:
res = db.execOnly(sqlu % ("vsp_id", vsp_id, self.lrgid))
if res:
self.logger.info("Modify lr_grid [%s] success." % self.lrgid)
else:
self.logger.error(
"Modify lr_grid [%s] failed, please check if lr_grid exists."
% self.lrgid)
return
def setProperty(self, attr, value):
sqlu = """update lr_node set %s=%s where lr_id='%s'""" % (attr, value,
self.lr_id)
res = db.execOnly(sqlu)
if res == 1:
self.logger.info("Update %s to [%s] where lrid is %s" %
(attr, value, self.lr_id))
else:
self.logger.warning("Update %s to [%s] where lrid is %s failed" %
(attr, value, self.lr_id))
return res
def lrAddall(self):
self.lrAddself()
src_lrc = lrCluster(self.src_level)
src_lr_id = src_lrc.randomLr()
src_price = getLrProperty(src_lr_id, "price")
dst_level_list = getAlllevel()
sql_data = []
for dst_level in dst_level_list:
if dst_level == self.src_level: continue
check_cloud_res, relationship_list = checkCloud(
dst_level, [self.src_level])
dlr_level_list = {}
for k, v in enumerate(relationship_list):
dlr_level_list[v] = k
if dlr_level_list.has_key(self.src_level):
layer_distance = dlr_level_list[self.src_level]
else:
layer_distance = 0
if len(check_cloud_res) <= 1:
weight = path = 0
else:
check_operator_res = checkOperator(dst_level, [self.src_level])
if len(check_operator_res["conn_list"]) != 0: weight = 1
elif len(check_operator_res["half_conn"]) != 0: weight = 0.5
if src_price == 0:
if layer_distance:
path = layer_distance / weight
else:
impassable_num = len(dlr_level_list)
path = impassable_num / weight
else:
if layer_distance:
path = math.sqrt(
src_price / 100) * layer_distance / weight
else:
impassable_num = len(dlr_level_list)
path = math.sqrt(
src_price / 100) * impassable_num / weight
sql_data.append(
(dst_level, self.src_level, weight, layer_distance, path))
# print "*"*40, dst_level, self.src_level, weight, layer_distance, path
redis_client.hmset("qos:%s:%s" % (dst_level, self.src_level), {
"weight": weight,
"layer_distance": layer_distance,
"path": path
})
if not sql_data: return 0
else:
sqli = """insert into net_qos (`level_src`, `level_dst`, `weight`, `layer_distance`, `path`) values (%s, %s, %s, %s, %s)"""
return db.execOnly(sqli, sql_data)
def addLrc(self, lrc_name, lrg_id, region):
sqli = """insert into lr_cluster (`lrc_id`, `lrc_name`, `lrg_ids`, `region`) values ('%s', '%s', '%s', '%s')""" % (
self.lrcid, lrc_name, lrg_id, region)
try:
res = db.execOnly(sqli)
except:
res = 0
if res: self.logger.info("Add lr_cluster [%s] success." % self.lrcid)
else:
self.logger.error(
"Add lr_cluster[%s] failed, please check if lr_cluster exists."
% self.lrcid)
return res
def addGrid(self, grid_name, vsp_id):
sqli = """insert into lr_grid (`lrg_id`, `lrg_name`, `vsp_id`) values ('%s', '%s', '%s')""" % (
self.lrgid, grid_name, vsp_id)
try:
res = db.execOnly(sqli)
except:
res = 0
if res: self.logger.info("Add lr_grid [%s] success." % self.lrgid)
else:
self.logger.error(
"Add lr_grid [%s] failed, please check if lr_grid exists." %
self.lrgid)
return res
def addLrnode(self, sql_data, level_id, plevel_id):
sqli = """insert into lr_node (`lr_id`, `name`, `plevel_id`, `ip`, `port`, `cluster`, `cloud`, `price`, `lr_type`, `sysload`, `level_id`, `last_subtime`, `active`) values ( "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", %s, "%s", %s, '1')""" % sql_data
try:
res = db.execOnly(sqli)
except:
res = 0
if res > 0:
level_cluster, level_type = write_level(level_id)
redis_client.hmset(
"level:%s" % level_id, {
"plevel_id": plevel_id,
"level_cluster": level_cluster,
"level_type": level_type
})
# 生成 ancestors/level 的redis记录
l_list = redis_client.keys("level:%s" % level_id)
if not l_list:
redis_client.rpush("ancestors:%s" % level_id, level_id)
for l in getAncestor(level_id, []):
redis_client.rpush("ancestors:%s" % level_id, l)
self.logger.info("Add lr_node with lrid=%s done." % self.lr_id)
return {
"code": 200,
"status": "success",
"msg": "Add lr_node with lrid=%s done." % self.lr_id
}
else:
self.logger.error(
"Add lr_node with lrid=%s failed, insert statement failed to execute."
% self.lr_id)
return {
"code": 400,
"status": "exist",
"msg": "Add lr_node with lrid=%s failed." % self.lr_id
}
def setProperty(self, dst, attr, value):
sqlu = """update `net_qos` set %s='%s' where `level_src`='%s' and `level_dst`='%s'""" % (
attr, value, self.lr_src, dst)
res = db.execOnly(sqlu)
return res
def lrAddself(self):
dst_level_list = getAlllevel()
# dst_level_list.remove(self.src_level)
level_conn_list, relationship_list = checkCloud(
self.src_level, dst_level_list)
operator_res = checkOperator(self.src_level, dst_level_list)
operator_half_conn_list = set(operator_res["half_conn"])
operator_not_conn_list = set(operator_res["not_conn"])
res_half_list = list(set(level_conn_list) & operator_half_conn_list)
res_conn_list = list(
set(level_conn_list) - set(res_half_list) - operator_not_conn_list)
self.logger.info("level_id: %s - connlist: %s - halflist: %s " %
(self.src_level, res_conn_list, res_half_list))
dlr_level_list = {}
for k, v in enumerate(relationship_list):
dlr_level_list[v] = k
sql_data_1 = []
for dst_level in dst_level_list:
if dst_level == self.src_level:
weight = 1
layer_distance = 0
path = weight
sql_data_1.append(
(self.src_level, dst_level, weight, layer_distance, path))
redis_client.hmset(
"qos:%s:%s" % (self.src_level, dst_level), {
"weight": weight,
"layer_distance": layer_distance,
"path": path
})
continue
dst_lrc = lrCluster(dst_level)
dst_lr_id = dst_lrc.randomLr()
dst_price = getLrProperty(dst_lr_id, "price")
if dlr_level_list.has_key(dst_level):
layer_distance = dlr_level_list[dst_level]
else:
layer_distance = 0
if (dst_level not in res_conn_list) and (dst_level
not in res_half_list):
weight = 0
path = 0
else:
if dst_level in res_conn_list: weight = 1
elif dst_level in res_half_list: weight = 0.5
if dst_price == 0:
if layer_distance:
path = layer_distance / weight
else:
impassable_num = len(dlr_level_list)
path = impassable_num / weight
else:
if layer_distance:
path = math.sqrt(
dst_price / 100) * layer_distance / weight
else:
impassable_num = len(dlr_level_list)
path = math.sqrt(
dst_price / 100) * impassable_num / weight
sql_data_1.append(
(self.src_level, dst_level, weight, layer_distance, path))
redis_client.hmset("qos:%s:%s" % (self.src_level, dst_level), {
"weight": weight,
"layer_distance": layer_distance,
"path": path
})
if not sql_data_1: return 0
else:
sqli = """insert into net_qos (`level_src`, `level_dst`, `weight`, `layer_distance`, `path`) values (%s, %s, %s, %s, %s)"""
return db.execOnly(sqli, sql_data_1)
def modLrnode(self, sql_data, level_id, plevel_id, old_level, lrid):
sqlu = """update lr_node set `name`="%s", `plevel_id`="%s", `ip`="%s", `port`="%s", `cluster`="%s", `cloud`="%s", `price`="%s", `lr_type`="%s", `sysload`=%s, `level_id`='%s', `last_subtime`=%s where `lr_id`='%s' """ % sql_data
sqlu_1 = """update level_node set `plevel_id`='%s' where `level_id`='%s'""" % (
plevel_id, level_id)
sql_select = """select `level_id` from `level_node`"""
level_list = [x[0] for x in db.execFetch(sql_select)]
# sql_parent = """select `plevel_id` from level_node where `level_id`='%s'"""%old_level
# old_parent_level = db.execFetch(sql_parent)[0][0]
if level_id not in level_list:
if old_level:
sql_check = """select lr_id from lr_node where level_id='%s'""" % old_level
result = db.execFetch(sql_check)
if len(result) <= 1:
sql_s_old = """select `level_id`, `plevel_id` from level_node where `level_id`='%s'""" % old_level
sql_u_old = """update level_node set `plevel_id`='%s' where `plevel_id`='%s'"""
need_r = db.execFetch(sql_s_old)
if need_r:
db.execFetch(sql_u_old % (need_r[0][1], old_level))
for l in need_r:
redis_client.hset("level:%s" % l[0], "plevel_id",
l[1])
sql_delete = """delete from `level_node` where `level_id`='%s'""" % old_level
db.execOnly(sql_delete)
redis_client.delete("level:%s" % old_level)
db.execOnly(sqlu)
res = write_level(level_id)
redis_client.hmset(
"level:%s" % level_id, {
"plevel_id": plevel_id,
"level_cluster": res[0],
"level_type": res[1]
})
reset_ancestors()
self.logger.info("Add new level and qos for lrid=%s done." %
self.lr_id)
# return {"code": 200, "status": "success", "msg": "Add new level and qos for lrid=%s done."%self.lr_id}
else:
if level_id != old_level:
sql_check = """select lr_id from lr_node where level_id='%s'""" % old_level
result = db.execFetch(sql_check)
if len(result) <= 1:
sql_delete = """delete from `level_node` where `level_id`='%s'""" % old_level
db.execOnly(sql_delete)
redis_client.delete("level:%s" % old_level)
reset_ancestors()
# print sqlu
try:
db.execOnly(sqlu_1)
result = db.execOnly(sqlu)
write_level(old_level)
res = write_level(level_id)
redis_client.hmset(
"level:%s" % level_id, {
"plevel_id": plevel_id,
"level_cluster": res[0],
"level_type": res[1]
})
redis_client.delete("ancestors:%s" % level_id)
redis_client.rpush("ancestors:%s" % level_id, level_id)
for l in getAncestor(level_id, []):
redis_client.rpush("ancestors:%s" % level_id, l)
except Exception, e:
# print "**************",e
result = 0
for user_lr in api_data:
if user_lr["user_id"] == '' or user_lr[
"node_level"] == '' or user_lr["vsp_id"] == '':
continue
ep_user = "******" % (user_lr["user_id"], user_lr["account"])
query_data.append(
(user_lr["account"], ep_user, str(user_lr["node_level"]),
user_lr["vsp_id"]))
# redis_client.hmset("user:%s"%ep_user, {"level_id": str(user_lr["node_level"]), "vsp_id": user_lr["vsp_id"]})
sqli = """insert into `adjacency` (`account`, `ep_id`, `level_id`, `vsp_id`) values (%s, %s, %s, %s)"""
else:
lr_all_list = []
for lr_info in api_data:
lr_all_list.append(lr_info["id"])
query_data.append(
(lr_info["id"], lr_info["name"], lr_info["p_node_level"],
lr_info["ip"], lr_info["port"], lr_info["op_name"],
lr_info["net_type"], lr_info["price"], lr_info["lr_type"],
lr_info["level_id"], cur_time)) # -------------------
sqli = """insert into `lr_node` (`lr_id`, `name`, `plevel_id`, `ip`, `port`, `cluster`, `cloud`, `price`, `lr_type`, `level_id`, `last_subtime`, `sysload`, `active`) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, '1')"""
logger.info("%s query_data: %s" % (db_table, query_data))
db.execOnly(sqli, query_data)
logger.info("Fill table %s success." % db_table)
# 自lr_node表中提取所有的level（去重），并初始化生成level_node表
sqls_level = """select distinct `level_id` from lr_node"""
level_res = db.execFetch(sqls_level)
for level_temp in level_res:
level = level_temp[0]
result = write_level(level)
if not result: logger.error("Error inserting level/%s" % level)
