def get_snapshot_tree(cluster_name, parent_path="/"): """获取快照的树形结构信息 数据库表结构设计参考: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ """ # 获取每个节点的路径和深度,节点返回结果是按照前序遍历顺序组织的 sql_tpl = ( "SELECT node.id, node.node_path, (COUNT(parent.node_path)-1) AS depth " "FROM zd_snapshot_tree AS node, zd_snapshot_tree AS parent " "WHERE node.cluster_name = '{0}' AND parent.cluster_name = '{0}' " # 每个zk集群单独构建一棵树 "AND node.node_path like '{1}%%' " "AND node.left BETWEEN parent.left AND parent.right " "GROUP BY node.node_path " "ORDER BY node.left" ) sql = sql_tpl.format(cluster_name, parent_path) records = ZdSnapshotTree.raw(sql) # 节点返回结果是按照前序遍历顺序组织的, 通过记录上层深度和节点id映射关系可以构造出树结构 nodes = [] last_depth_mapping = {} for record in records: last_depth_mapping[record.depth] = record.id parent_id = last_depth_mapping.get(record.depth - 1, -1) nodes.append( {"id": record.id, "pId": parent_id, "name": record.node_path.rsplit("/", 1)[-1], "path": record.node_path} ) return nodes
def get_snapshot_tree(cluster_name, parent_path="/"): """获取快照的树形结构信息 数据库表结构设计参考: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ """ # 获取每个节点的路径和深度,节点返回结果是按照前序遍历顺序组织的 sql_tpl = ("SELECT node.id, node.node_path, (COUNT(parent.node_path)-1) AS depth " "FROM zd_snapshot_tree AS node, zd_snapshot_tree AS parent " "WHERE node.cluster_name = '{0}' AND parent.cluster_name = '{0}' " # 每个zk集群单独构建一棵树 "AND node.node_path like '{1}%%' " "AND node.left BETWEEN parent.left AND parent.right " "GROUP BY node.node_path " "ORDER BY node.left") sql = sql_tpl.format(cluster_name, parent_path) records = ZdSnapshotTree.raw(sql) # 节点返回结果是按照前序遍历顺序组织的, 通过记录上层深度和节点id映射关系可以构造出树结构 nodes = [] last_depth_mapping = {} for record in records: last_depth_mapping[record.depth] = record.id parent_id = last_depth_mapping.get(record.depth - 1, -1) nodes.append({ "id": record.id, "pId": parent_id, "name": record.node_path.rsplit('/', 1)[-1], "path": record.node_path }) return nodes
def _get_tree_children_from_path(cluster_name, parent_path): """在快照树形结构表(zd_snapshot_tree)获取某个节点所有的子节点路径 """ sql_tpl = ("SELECT node.node_path FROM zd_snapshot_tree AS node, zd_snapshot_tree AS parent " "WHERE parent.cluster_name = %s AND parent.node_path = %s " "AND node.cluster_name = %s" "AND node.left BETWEEN parent.left AND parent.right") children = ZdSnapshotTree.raw(sql_tpl, cluster_name, parent_path, cluster_name) return [child.node_path for child in children]