Method for designing tree data storage mode in relational database by using preorder traversal

Abstract

The invention discloses a method for designing a tree data storage mode in a relational database by using preorder traversal, which comprises the following steps: S1, basic node data of a tree structure is constructed in the relational database, each node comprises id, name, lvalue and rvalue attributes, and the lvalue and the rvalue are used for identifying the position relationship of the nodes in a tree through an interval representation method; s2, setting a query condition according to a node ID needing to be queried, and determining a descendant node range of the node through lvalue and rvalue attributes of the node; s3, the query results are subjected to ascending sort of the left value lvalue, and a sort list (sort List) is constructed; and S4, by traversing the sortedList, in combination with the temporary set level List and the cache level NodeCache, calculating and setting a father node ID (pid) of each node and whether the father node ID (pid) is a leaf node isLeaf attribute or not according to the logic of pre-order traversal, thereby completing the rapid construction of the tree structure.

Description

Technical Field

[0001] The present invention relates to the field of tree data storage methods in databases, and in particular to a method for designing a tree data storage method in a relational database by using pre-order traversal. Background Art

[0002] With the rapid development of information technology, relational databases play a vital role in data storage and management, especially when processing hierarchical data, such as organizational structure, directory tree, etc., relational databases have become the preferred data storage solution for enterprises due to their powerful data consistency and integrity control mechanisms. However, traditional relational databases face challenges such as low data query efficiency and long response time when processing tree data structures.

[0003] At present, the mainstream approach to storing and querying tree data in relational databases is to store the corresponding unique identifier of the parent data (such as PID) in each piece of data, and build the tree structure layer by layer through recursive queries. Although this method can accurately reflect the hierarchical relationship of the data, its performance bottleneck becomes increasingly prominent when the data volume is huge or the tree structure is complex.

[0004] Existing tree data storage and query technologies usually include the following steps: first, a data table containing attributes such as node ID and parent node ID is established in the database; second, when a tree structure needs to be built, starting from the specified node, all its child nodes are obtained through recursive query; finally, a complete tree structure is built based on the query results. This method is simple and intuitive to implement, but the query efficiency is low, especially when the tree structure is complex or the data volume is large.

[0005] When processing tree data in relational databases, the existing technology has low query efficiency and needs to execute multiple SQL queries. As the number of tree structure levels increases, the query time increases exponentially. The existing technology also consumes a lot of memory and requires a large amount of memory resources during recursive queries, increasing the risk of memory stack overflow. The technology also has poor scalability and is difficult to adapt to data processing requirements in large data volumes and high concurrency scenarios. Summary of the invention

[0006] Based on this, the purpose of the present invention is to provide a method for designing a tree data storage method in a relational database using pre-order traversal, so as to solve the technical problems that the existing method has low query efficiency, needs to execute multiple SQL queries, and with the increase of the tree structure level, the query time increases exponentially, the memory consumption is large, a large amount of memory resources are required during the recursive query process, the risk of memory stack overflow is increased, and the scalability is poor, making it difficult to adapt to the data processing needs in large data volumes and high concurrency scenarios.

[0007] In view of the above problems, the present application provides a method for designing a tree data storage method in a relational database using pre-order traversal.

[0008] The present application provides a method for designing a tree data storage method in a relational database using pre-order traversal, characterized in that it includes the following steps: S1: constructing basic node data of a tree structure in a relational database, each node containing id, name, l_value, and r_value attributes, wherein l_value and r_value are used to identify the position relationship of the node in the tree through interval representation; S2: setting query conditions according to the node ID to be queried, and determining the range of descendant nodes of the node through the l_value and r_value attributes of the node; S3: sorting the query results in ascending order of the left value l_value, and constructing a sorted list sortedList; S4: by traversing sortedList, combining the temporary set levelList and the cache levelNodeCache, according to the logic of pre-order traversal, calculating and setting the parent node ID (pid) of each node and the isLeaf attribute of whether it is a leaf node, thereby completing the rapid construction of the tree structure.

[0009] In summary, the present invention mainly has the following beneficial effects:

[0010] The present invention proposes a method for assembling tree structure data in reverse order, which defines node attributes in a database, including left value, right value, etc.; constructs basic data and sets node attributes; quickly queries descendant nodes by comparing the left value and right value of the node; uses temporary collection and cache to assist in calculating the node hierarchy and parent-child relationship, and sets pid and isLeaf attributes. This solution avoids the problems of slow response and large memory consumption caused by traditional recursive query, realizes the rapid acquisition of basic data of the tree interface, significantly improves the response performance of the tree data interface, and effectively reduces query time and memory usage. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] Figure 1 A schematic diagram of node construction of the present invention;

[0012] Figure 2 It is a logical sequence diagram of the present invention;

[0013] Figure 3 Construct a display diagram for the prior art of the present invention;

[0014] Figure 4 It is a diagram showing the prior art sequence of the present invention. DETAILED DESCRIPTION

[0015] The following will be combined with the accompanying drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and cannot be understood as limiting the present invention.

[0016] The following describes an embodiment of the present invention based on its overall structure.

[0017] A method for designing a tree data storage method in a relational database using pre-order traversal, comprising the following steps:

[0018] S1: Build the basic node data of the tree structure in the relational database. Each node contains the attributes of id, name, l_value, and r_value. l_value and r_value are used to identify the position relationship of the node in the tree through interval representation.

[0019] Relational databases organize data in the form of tables, where each record represents a data item, and multiple related tables are related to each other through relationships, such as foreign keys. The tree structure is an important nonlinear data structure that simulates a hierarchical data set. In a tree structure, each node can have zero or more child nodes, but only one parent node, except for the root node, which has no parent node.

[0020] It is worth mentioning that each node contains a series of basic attributes, which together describe the characteristics and positional relationships of the nodes.

[0021] Here we explain the professional English vocabulary needed in the article and the role of nouns. The specific explanations are as follows:

[0022] id: The unique identifier of a node, used to uniquely identify a node in the database.

[0023] name: The name of the node, providing a human-readable description of the node.

[0024] l_value (left value): A value used in interval notation. Together with r_value (right value), it defines an interval. All values ​​in this interval represent the descendant nodes of the current node (including the current node itself). In the interval notation of the tree structure, the node with the smaller left value is the ancestor of the node with the larger right value.

[0025] r_value (right value): Also used in interval representation, together with l_value, it defines the position range of the node in the tree.

[0026] Interval representation: This is a method of representing the position relationship of nodes in a tree structure. By assigning a left value (l_value) and a right value (r_value) to each node, all descendant nodes of a node can be quickly determined. Specifically, if the l_value of a node is less than or equal to a certain value and this value is less than or equal to the r_value of the node, then the node represented by this value is the descendant node of the current node.

[0027] Node position relationship: l_value and r_value can be used to clearly represent the hierarchy and parent-child relationship between nodes in the tree. Although it may be more intuitive to directly represent the parent-child relationship between nodes through the pid (parent node ID) attribute, the interval representation method provides a more efficient way to query and process node relationships in a tree structure, especially when dealing with large amounts of data and complex queries.

[0028] Data storage: These attributes (id, name, l_value, r_value) and possible other attributes (such as level, pid, isLeaf, etc.) are stored in a table in a relational database for efficient query and management.

[0029] S1 specifically includes:

[0030] S110: assign a unique ID and name to each node;

[0031] S120: According to the tree structure, assign a left value (l_value) and a right value (r_value) to each node, ensuring that the left value of any node is less than its right value, and the left value of any non-root node is greater than the left value of its parent node and less than the right value of its parent node, thereby forming a nested interval representation tree structure;

[0032] S130: According to the actual structure of the tree, determine and set the level of each node in the tree, where the root node is the first level.

[0033] When building tree-structured basic data in a relational database, you first need to assign a unique ID and a name to each node. These two attributes are the basic information of the node, used to uniquely identify and describe the node. The ID is usually a self-incrementing integer or a unique identifier such as UUID, while the name is the specific name of the node, such as "department name", "category name", etc.

[0034] In a tree structure, in order to efficiently query and manage the parent-child relationship between nodes, we can use left value and right value, i.e. l_value and r_value, to represent the nested relationship of nodes. This step is the key to building a tree structure.

[0035] When allocating, you can use the pre-order traversal method to traverse the entire tree and assign left and right values ​​to each traversed node. During the traversal process, maintain a global counter to generate the left and right values ​​of the node. When traversing to a node, first assign it a left value, assign the current value of the counter to the left value, and increase the counter by 1, then recursively assign left and right values ​​to its child nodes, and finally assign the right value to the node. At this time, the value of the counter is the right value of the node, and then increase the counter by 1.

[0036] In a tree structure, each node has a level attribute, which indicates the depth of the node in the tree. The level of the root node is usually 1 or 0, depending on the specific implementation, and the level of its child nodes is the root node level plus 1, and so on.

[0037] When determining the level of a node, you can do it during the pre-order traversal. When traversing to each node, based on the level of its parent node, for the root node, you can assume that its parent node level is 0 or 1, and calculate and set the level of the current node. In this way, after traversing the entire tree, each node will have an accurate level attribute.

[0038] S2: Set the query condition according to the node ID to be queried, and determine the range of the node's descendant nodes through the node's l_value and r_value attributes;

[0039] In the document, each node is assigned two key attributes, l_value and r_value. These two values ​​are generated by the pre-order traversal process of the tree and are used to logically represent the position relationship of the node in the tree. Specifically, the l_value value of all descendant nodes of a node will be greater than the l_value value of the node and less than the r_value value of the node.

[0040] S2 specifically includes:

[0041] S210: Select a target node as a query starting point;

[0042] S220: Determine the query condition as a set of nodes whose left values ​​of all nodes are greater than the left value of the target node and less than the right value of the target node.

[0043] Based on the above rules, we can set the conditions for SQL queries. The query will select all records whose l_value is greater than the target node's l_value and whose r_value is less than the target node's r_value. These records are all the descendant nodes of the target node.

[0044] Apply the above conditions to the SQL query statement and execute the query to retrieve all records that meet the conditions from the database. These records are the complete list of descendant nodes of the target node. In this way, the performance problems caused by the traditional recursive query method, such as the risk of long interface response time and memory stack overflow, can be avoided. Because this method only needs to execute one SQL query to find all descendant nodes, the efficiency of data query is greatly improved.

[0045] The data obtained from the query may need further processing, such as sorting in a certain order, setting parent-child relationship attributes, etc. These operations can be performed after the query according to actual needs.

[0046] S3: Sort the query results in ascending order of the left value l_value and construct a sorted list sortedList;

[0047] S3 specifically includes:

[0048] S310: constructing basic tree data in a relational database, ensuring that each data record contains necessary attributes such as id, name, l_value, r_value, and level;

[0049] S320: query all nodes of a certain node by a specific condition, and determine its range based on the l_value and r_value attributes of the node;

[0050] S330: After obtaining the query results, sort them in ascending order according to l_value;

[0051] S340: Traverse the sorted node list, and use temporary collections and caches to assist in calculating the node hierarchy and parent node ID. During the traversal process, the node hierarchy is dynamically determined by comparing the right value of the current node with the right value recorded in the cache, and its parent node ID is updated.

[0052] S350: Storing the node level and the node itself into a cache.

[0053] We need to query all descendant nodes of a particular node. Based on the pre-order traversal characteristics of the tree and the l_value and r_value attributes of the node, we can determine that the l_value and r_value of all descendant nodes of a node will meet certain range conditions, that is, the l_value of all descendant nodes will be greater than the l_value of the node and less than its r_value.

[0054] By executing a SQL query, we can retrieve all node data that meet the conditions based on this range condition. The data set obtained by the query (assuming it is resultSet) contains multiple node data, which are returned in the order in which they are stored in the database, not necessarily in the order of l_value. Therefore, we need to sort these query results based on the l_value of the node to ensure that the sorted results are arranged in ascending order of l_value.

[0055] The purpose of this step is to facilitate the subsequent processing of these node data, especially to determine the hierarchical relationship and parent-child relationship between them.

[0056] After the node list sortedList is sorted, we need to traverse this list. During the traversal, two auxiliary data structures are used: if levelList is not empty, traverse levelList in reverse order and compare the right value of the current node with the right value of the node in levelList. If the right value of the current node is greater than the right value of the node in levelList, it means that the level of the current node is either the same as the node in levelList or above it. At this time, the corresponding node in levelList needs to be deleted, and the level of the current node needs to be updated as needed.

[0057] The parent node pNode of the current node is searched through levelNodeCache, and its level is the level of the current node minus 1. If the parent node is found, the pid attribute of the current node is set to the id of the parent node; if not found, that is, the current node is a direct child node of the root node, the pid is set to 0.

[0058] Determine whether the current node is a leaf node by checking whether the difference between its right value and its left value is 1. If so, set its isLeaf property to true. Store the level and node data of the current node in levelNodeCache and levelList for use by subsequent nodes.

[0059] S4: By traversing sortedList, combining the temporary set levelList and the cache levelNodeCache, the parent node ID (pid) of each node and the isLeaf attribute of whether it is a leaf node are calculated and set according to the logic of pre-order traversal, so as to complete the rapid construction of the tree structure.

[0060] S410: Initialize the temporary set levelList and cache levelNodeCache;

[0061] S420: traverse each node in sortedList, and determine the level and parent node of the current node by comparing the right value with the right value of the node in levelList;

[0062] S430: Set the pid attribute of the current node to the id of the parent node, or set it to 0 if there is no parent node;

[0063] S440: setting the isLeaf attribute according to the difference between the l_value and r_value of the current node;

[0064] S450: Update the current node and its level information to levelNodeCache and levelList.

[0065] By processing the sorted node list sortedList, combining the temporary set levelList and the cache levelNodeCache, and using the logic of pre-order traversal, the parent node ID (pid) and the isLeaf attribute of each node are correctly set.

[0066] Among them, levelList is a temporary collection used to assist in calculating the hierarchical relationship of the current node during the traversal process. Its elements are usually sorted according to the node's level or right value to facilitate quick positioning during comparison. LevelNodeCache is a cache used to store calculated nodes and their level information. Its key is usually the level number, and the value is the corresponding node data. The purpose of this cache is to speed up the search process of the parent node and avoid repeated calculations.

[0067] Take each node from sortedList and traverse it in order. During the traversal, it is constantly compared with the nodes in levelList. levelList needs to be traversed in reverse order according to the right value of the node. If the right value of the current node is greater than the right value of a node in levelList, it means that the level of the current node is not lower than that of the node, and may belong to the same level or a higher level. At this time, all nodes whose right value is less than or equal to the right value of the current node need to be deleted from levelList, because they are no longer the ancestor nodes of the current node.

[0068] The level of the current node is determined by adding one to the length of levelList, and then the node corresponding to the level minus one is taken from levelNodeCache as the parent node pNode of the current node.

[0069] If the corresponding parent node cannot be found, that is, there is no node with the level minus one in the levelNodeCache, the pid of the current node is set to 0, indicating that it is a root node or an independent node. If the parent node pNode is successfully found, the pid attribute of the current node is set to the id of the parent node. If the parent node is not found, that is, there is no node with the level minus one in the levelNodeCache, the pid attribute of the current node is set to 0.

[0070] Calculate the difference between the current node's l_value and r_value. If the difference is 1, it means that the node has no child nodes, that is, it is a leaf node, and the isLeaf attribute is set to true. If the difference is greater than 1, it means that the node has child nodes, and the isLeaf attribute is set to false.

[0071] Add the current node and its calculated level information to levelNodeCache for subsequent node parent search. At the same time, add the current node to levelList for subsequent traversal and comparison.

[0072] When setting the node pid attribute, if levelList is empty or the right value of the current node is greater than the right values ​​of all nodes in levelList, the current node is a root node or a new level node, its pid is set to 0, and the level is the current levelList length plus 1.

[0073] When levelList is empty, it means that the node currently being processed is the first node in the traversal process, or the previously traversed node levels have all been processed, and the current node opens a new level or a new tree if it is a root node. At this time, since there is no node information in levelList, it is impossible to determine the parent node pid of the current node based on the existing nodes.

[0074] Therefore, the pid of the current node is set to 0, indicating that it is a root node or the first node in a new level, and there is no node before the level, and its level is set to the length of levelList plus 1. Since levelList is empty, the length is 0, so the level is 1, that is, the root node level.

[0075] During the traversal process, if the right value of the current node is greater than the right values ​​of all nodes in levelList, it means that the current node is located above the level where all previous nodes are located or a new level is opened. This is because when traversing in pre-order and building a tree structure, the right value of a node is determined according to the size of its subtree, including all child nodes and sub-child nodes, so the node with a larger right value will be higher in the tree or farther away from the node recorded in the current levelList.

[0076] In this case, the direct parent node of the current node cannot be found from levelList, so the pid of the current node is set to 0, indicating that it is a node of a new level, but not necessarily a root node, because there may be multiple trees or multiple root nodes. At the same time, the level of the current node is set to the length of levelList plus 1 to reflect its new level position in the tree.

[0077] After building the basic node data, the database is indexed and optimized to improve query efficiency. During the query process, a cache mechanism is used to store the parent-child relationship of the queried nodes to reduce repeated queries.

[0078] In relational databases, index is one of the important means to improve query performance. It quickly locates specific data in the database table by establishing a data structure (such as B-tree, hash table, etc.), thereby reducing the amount of database scanning and improving query speed. Provide API interface to allow external systems to query and build tree data by calling the interface.

[0079] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements a method for assembling tree-structured data in reverse order; an electronic device includes a processor and a memory, in which a computer program is stored, and when executed by the processor, the computer program implements a method for assembling tree-structured data in reverse order.

[0080] Those skilled in the art will understand that the various digital numbers such as the first and second involved in this application are only for the convenience of description, and are not used to limit the scope of this application, nor do they indicate a sequence of precedence. "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects associated with each other are in an "or" relationship. "At least one" refers to one or more. At least two refers to two or more. "At least one", "any one" or similar expressions refer to any combination of these items, including any combination of single items (individuals) or plural items (individuals). For example, at least one item (individual, kind) of a, b, or c can represent: a, b, c, ab, ac, bc, or abc, where a, b, c can be single or multiple.

[0081] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in this application is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices.

[0082] The steps of the method or algorithm described in this application can be directly embedded in hardware, software units executed by a processor, or a combination of the two. The software unit can be stored in RAM memory, flash memory, ROM memory, EPROM memory, EEPROM memory, register, hard disk, removable disk, CD-ROM or any other form of storage medium in the art. Exemplarily, the storage medium can be connected to the processor so that the processor can read information from the storage medium and write information to the storage medium. Optionally, the storage medium can also be integrated into the processor. The processor and the storage medium can be arranged in an ASIC, and the ASIC can be arranged in a terminal. Optionally, the processor and the storage medium can also be arranged in different components in the terminal. These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are performed on the computer or other programmable device to produce a computer-implemented process, so that the instructions executed on the computer or other programmable device provide for implementing the process in the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0083] Although the present application has been described in conjunction with specific features and embodiments thereof, it is obvious that various modifications and combinations may be made thereto without departing from the spirit and scope of the present application. Accordingly, this specification and the accompanying drawings are merely exemplary illustrations of the present application and are deemed to have covered any and all modifications, variations, combinations or equivalents within the scope of the present application. Obviously, a person skilled in the art may make various modifications and variations to the present application without departing from the scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the present application and its equivalents, the present application is intended to include these modifications and variations.

Claims

1. A method for designing a tree data storage method in a relational database using pre-order traversal, characterized in that: The following steps are involved: S1: Build the basic node data of the tree structure in the relational database. Each node contains the attributes of id, name, l_value, and r_value. l_value and r_value are used to identify the position relationship of the node in the tree through interval representation. S2: Set the query condition according to the node ID to be queried, and determine the range of the node's descendant nodes through the node's l_value and r_value attributes; S3: Sort the query results in ascending order of the left value l_value and construct a sorted list sortedList; S4: By traversing sortedList, combining the temporary set levelList and the cache levelNodeCache, the parent node ID (pid) of each node and the isLeaf attribute of whether it is a leaf node are calculated and set according to the logic of pre-order traversal, so as to complete the rapid construction of the tree structure.

2. The method of designing a tree data storage method in a relational database using pre-order traversal according to claim 1, characterized in that: The basic node data for constructing a tree structure in a relational database specifically includes: S110: assign a unique ID and name to each node; S120: According to the tree structure, assign a left value (l_value) and a right value (r_value) to each node, ensuring that the left value of any node is less than its right value, and the left value of any non-root node is greater than the left value of its parent node and less than the right value of its parent node, thereby forming a nested interval representation tree structure; S130: According to the actual structure of the tree, determine and set the level of each node in the tree, where the root node is the first level.

3. The method of designing a tree data storage method in a relational database using pre-order traversal according to claim 1, characterized in that: The setting query conditions specifically include: S210: Select a target node as a query starting point; S220: Determine the query condition as a set of nodes whose left values ​​of all nodes are greater than the left value of the target node and less than the right value of the target node.

4. The method of designing a tree data storage method in a relational database using pre-order traversal according to claim 1, characterized in that: The query results are sorted in ascending order of left values, specifically including: S310: constructing basic tree data in a relational database, ensuring that each data record contains necessary attributes such as id, name, l_value, r_value, and level; S320: query all nodes of a certain node by a specific condition, and determine its range based on the l_value and r_value attributes of the node; S330: After obtaining the query results, sort them in ascending order according to l_value; S340: traversing the sorted node list, using a temporary set and cache to assist in calculating the node hierarchy and parent node ID, wherein during the traversal process, by comparing the right value of the current node with the right value recorded in the cache, the node hierarchy is dynamically determined, and its parent node ID is updated; S350: Storing the node level and the node itself into a cache.

5. The method of designing a tree data storage method in a relational database using pre-order traversal according to claim 1, characterized in that: The calculation and setting of the pid and isLeaf attributes of each node specifically includes: S410: Initialize the temporary set levelList and cache levelNodeCache; S420: traverse each node in sortedList, and determine the level and parent node of the current node by comparing the right value with the right value of the node in levelList; S430: Set the pid attribute of the current node to the id of the parent node, or set it to 0 if there is no parent node; S440: setting the isLeaf attribute according to the difference between the l_value and r_value of the current node; S450: Update the current node and its level information to levelNodeCache and levelList.

6. The method of designing a tree data storage method in a relational database using pre-order traversal according to claim 5, characterized in that: When setting the node pid attribute, if levelList is empty or the right value of the current node is greater than the right values ​​of all nodes in levelList, the current node is a root node or a new level node, its pid is set to 0, and the level is the current levelList length plus 1.

7. A method for designing a tree data storage method in a relational database using pre-order traversal according to any one of claims 1 to 6, characterized in that: After building the basic node data, the database is indexed and optimized to improve query efficiency.

8. A method for designing a tree data storage method in a relational database using pre-order traversal according to any one of claims 1 to 6, characterized in that: During the query process, a cache mechanism is used to store the parent-child relationship of the queried nodes to reduce repeated queries.

9. A method for designing a tree data storage method in a relational database using pre-order traversal according to any one of claims 1 to 6, characterized in that: When the data is updated, the l_value, r_value and level attributes of the node are updated synchronously to maintain the consistency of the tree structure.

10. A method for designing a tree data storage method in a relational database using pre-order traversal according to any one of claims 1 to 9, characterized in that: Provides an API interface, allowing external systems to query and build tree data by calling the interface.

Images