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)