采用左右值编码来存储无限分级树形结构-创新互联
参考文档：
https://blog.csdn.net/comiunknown/article/details/1586020
python实现：
from app.cmdb.config import dbconfig, cmdbsql, modelconfig, balantsql
from app.cmdb.utils.dbutil import MysqlConnect
balant_connect = MysqlConnect(db_params=dbconfig.balant_db_params_sit)
# cmdb_connect = MysqlConnect(db_params=dbconfig.cmdb_db_params)
class CatalogTemplate(object):
def __init__(self, tag_id, catalog_id):
"""
:param tag_id: 基准节点
:param catalog_id: 基准目录
"""
self.tag_id = tag_id
self.catalog_id = catalog_id
(self.lft, self.rht) = self.get_lft_rht()
def get_tag(self):
"""
获取节点id
:return: 0 或 id
"""
try:
catalog_tag_id = balant_connect.query_execute(
"""select id from mon_catalog_tag where tag_id = %d and catalog_id = %d""" % (self.tag_id, self.catalog_id))
if catalog_tag_id:
return catalog_tag_id[0][0]
else:
return 0
except:
return 0
def get_lft_rht(self):
"""
获取该节点的左右id
:return:列表[lft, rht]
"""
try:
if self.get_tag():
result = balant_connect.query_execute("""select lft, rht from mon_catalog_tag
where tag_id = %d and catalog_id = %d limit 1""" % (self.tag_id, self.catalog_id))
return result[0]
else:
return 0, 0
except Exception as e:
raise Exception("记录不存在，info %s" % e)
def get_layer(self):
"""
获取节点层级
:return: 层数
"""
try:
if self.get_tag():
result = balant_connect.query_execute("""select count(*) from mon_catalog_tag where lft <= '%d' and rht >=
%d and catalog_id = %d""" % (self.lft, self.rht, self.catalog_id))
return result[0][0]
else:
return 0
except Exception as e:
raise Exception("记录不存在，info %s" % e)
def get_tree_list(self):
"""
获取该节点及其子孙节点
:return:节点列表
"""
if self.get_tag():
try:
return balant_connect.query_execute("""select * from
(SELECT tag_id, lft, rht, %d as layer FROM mon_catalog_tag where catalog_id = %d ORDER BY lft) AS TreeList where lft between %d and %d order by lft
asc""" % (self.get_layer(), self.catalog_id, self.lft, self.rht))
except Exception as e:
raise Exception("记录不存在，info %s" % e)
def add_sub_node(self, tag_name, catalog_name):
"""
插入子节点, 只能添加末节点
:param tag_name:待插入节点的名称
:param catalog_name:待插入目录的名称
:return: 无
"""
tag_id = balant_connect.query_execute("""select id from mon_tag where name = '%s'""" % tag_name)
catalog_id = balant_connect.query_execute("""select id from mon_catalog where name = '%s'""" % catalog_name)
insert_tag_id = balant_connect.query_execute(
"""select id from mon_catalog_tag where tag_id = %d and catalog_id = %d""" % (tag_id[0][0], catalog_id[0][0]))
if self.get_tag() and not insert_tag_id:
rht = balant_connect.query_execute("""select rht from mon_catalog_tag where tag_id = %d""" % self.tag_id)
balant_connect.dml_execute("""update mon_catalog_tag set rht=rht+2 where rht>=%d""" % rht[0][0])
balant_connect.dml_execute("""update mon_catalog_tag set lft=lft+2 where lft>=%d""" % rht[0][0])
balant_connect.dml_execute("""insert into mon_catalog_tag (parent_id, lft, rht, tag_id, catalog_id)
values (%d,%d,%d,%d,%d)""" % (self.get_tag(), rht[0][0], int(rht[0][0])+1, tag_id[0][0], catalog_id[0][0]))
else:
print("插入节点错误，请检查参数和层级关系！")
return
def del_node(self, tag_name, catalog_name):
"""
删除节点
:param tag_name:待插入节点的名称
:param catalog_name:待插入目录的名称
:return: 无
"""
tag_id = balant_connect.query_execute("""select id from mon_tag where name = '%s'""" % tag_name)
catalog_id = balant_connect.query_execute("""select id from mon_catalog where name = '%s'""" % catalog_name)
del_tag_id = balant_connect.query_execute(
"""select id from mon_catalog_tag where tag_id = %d and catalog_id = %d""" % (
tag_id[0][0], catalog_id[0][0]))
lft_rht = balant_connect.query_execute("""select lft, rht from mon_catalog_tag
where tag_id = %d and catalog_id = %d limit 1""" % (tag_id[0][0], catalog_id[0][0]))
lft_id = lft_rht[0][0]
rht_id = lft_rht[0][1]
if self.get_tag() and del_tag_id:
balant_connect.dml_execute("""delete from mon_catalog_tag where lft>=%d and rht<=%d""" % (lft_id, rht_id))
balant_connect.dml_execute("""update mon_catalog_tag set lft=lft-(%d-%d+1) where lft>%d""" % (rht_id, lft_id, lft_id))
balant_connect.dml_execute("""update mon_catalog_tag set rht=rht-(%d-%d+1) where rht>%d""" % (rht_id, lft_id, rht_id))
else:
print("插入节点错误，请检查参数和层级关系！")
return
def move_node(self):
pass
def target_add_tag():
"""
查询所有tag信息，更新tag表和目录表
:return:
"""
data_mw = dict()
sql_cmdb = cmdbsql.cmdb_select_app_tag
# result = cmdb_connect.query_execute(sql_cmdb, db_params=dbconfig.cmdb_db_params)
result = [('ASURA-BAR', 'ASU_BAR_CNSZ17_WAS', 'IBM WAS 6.x', 'ASU_BAR_CNSZ17_WAS_01', '生产')]
# sysCode | vclName | soft | hostname | env
# [('ASURA-BAR', 'ASU_BAR_CNSZ17_WAS', 'IBM WAS 6.x', 'ASU_BAR_CNSZ17_WAS_01', '生产')]
for i in result:
cur = balant_connect.query_execute(balantsql.balant_select_tag % i[3])
print(cur)
# [(None, 'ASU_BAR_CNSZ17_WAS_01', 'middleware', 7092)]
try:
# 更新tag
if cur[0][0] is None:
balant_connect.dml_execute(balantsql.balant_update_tag % (i[0], i[0]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[0], i[3], i[0]))
balant_connect.dml_execute(balantsql.balant_update_tag % (i[1], i[1]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[1], i[3], i[1]))
balant_connect.dml_execute(balantsql.balant_update_tag % (i[4], i[4]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[4], i[3], i[4]))
elif i[0] not in cur[0][0]: # 判断cmdb是否存在tag
balant_connect.dml_execute(balantsql.balant_update_tag % (i[0], i[0]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[0], i[3], i[0]))
elif i[1] not in cur[0][0]: # 判断cmdb是否存在tag
balant_connect.dml_execute(balantsql.balant_update_tag % (i[1], i[1]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[1], i[3], i[1]))
elif i[4] not in cur[0][0]: # 判断cmdb是否存在tag
balant_connect.dml_execute(balantsql.balant_update_tag % (i[4], i[4]))
balant_connect.dml_execute(balantsql.balant_update_target_tag % (i[3], i[4], i[3], i[4]))
# 更新catalog
sysCode_tag_id = balant_connect.query_execute("""select id from mon_tag where name = '%s'""" % i[0])
catalog_id = balant_connect.query_execute("""select id from mon_catalog where name = '应用'""")
catalog = CatalogTemplate(0, catalog_id[0][0])
catalog.add_sub_node(i[0], "应用")
catalog = CatalogTemplate(sysCode_tag_id[0][0], catalog_id[0][0])
catalog.add_sub_node(i[1], "应用")
except Exception as e:
raise Exception("Error info: %s" % e)
return data_mw另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
文章标题：采用左右值编码来存储无限分级树形结构-创新互联
分享链接：http://myzitong.com/article/cspicd.html