采用左右值编码来存储无限分级树形结构

参考文档：

https://blog.csdn.net/comiunknown/article/details/1586020

python实现：

from app.cmdb.config import dbconfig, cmdbsql, modelconfig, balantsqlfrom app.cmdb.utils.dbutil import MysqlConnectbalant_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):        passdef 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