SQL (Structured Query Language) reconstruction method and device, equipment and storage medium

Abstract

The invention discloses an SQL (Structured Query Language) reconstruction method and device, equipment and a storage medium. The method comprises the steps that an SQL template is analyzed, and an abstract syntax tree corresponding to the SQL template is generated; the abstract syntax tree is used for describing a dependency relationship between a syntax position and a parameter of a dynamic code block in the SQL template; dynamic nodes in the abstract syntax tree are nodes corresponding to the dynamic code blocks; when traversing the abstract syntax tree, determining a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model based on parameters transmitted by a user; the parameter replacement strategy comprises a parameter replacement mode and an SQL self-healing processing mode after parameter replacement; according to the parameter replacement strategies corresponding to the dynamic nodes, the corresponding parameter replacement strategies are executed on the dynamic nodes, a target abstract syntax tree is obtained, and then a target SQL is generated. By means of the method, errors in the SQL reconstruction process are avoided, and then the integrity and readability of the SQL content are improved.

Description

Technical Field

[0001] The present application relates to the field of database security technology, and in particular to an SQL reconstruction method, apparatus, device, and storage medium. Background Art

[0002] With the increasing complexity of software development, in many scenarios, it's impossible to determine the specific parameter values in SQL statements during code development. For example, in an enterprise-level reporting system, users may query data based on various criteria, such as time ranges, departments, and product categories. Therefore, dynamically generating SQL that meets the application scenario by replacing parameters with placeholders in the original SQL becomes a necessary technical solution.

[0003] In existing technologies, mechanical parameter and statement replacement is often used to reconstruct SQL templates by scanning placeholders character by character and replacing the statements within them. However, this method fails to recognize semantic code blocks, resulting in the destruction of the original logical structure when replacing parameters. In complex code structures, syntax errors may occur, reducing the integrity and readability of the SQL content. Summary of the Invention

[0004] Based on the above problems, the present application provides a SQL reconstruction method, apparatus, device and storage medium, the purpose of which is to avoid errors in the SQL reconstruction process and improve the integrity and readability of SQL content.

[0005] The embodiments of this application disclose the following technical solutions: In a first aspect, the present application provides an SQL reconstruction method, comprising: Parsing the acquired SQL template to generate an abstract syntax tree corresponding to the SQL template; the abstract syntax tree is a structured representation of the SQL template, used to describe the syntax position of the dynamic code block in the SQL template and the dependency relationship between parameters; the nodes of the abstract syntax tree include static nodes and dynamic nodes; the dynamic nodes are nodes corresponding to the dynamic code blocks; When traversing the abstract syntax tree, based on the parameters input by the user, a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree is determined from a preset binary decision model; the parameter replacement strategy includes a parameter replacement method and an SQL self-healing processing method after the parameter replacement; According to the parameter replacement strategy corresponding to each dynamic node, the corresponding parameter replacement strategy is executed on each dynamic node to obtain a target abstract syntax tree; The target abstract syntax tree is converted into a standard SQL statement to generate target SQL.

[0006] Optionally, in the above method, parsing the acquired SQL template to generate an abstract syntax tree corresponding to the SQL template includes: Scanning the SQL template using a regular matching method to identify multiple dynamic code blocks in the SQL template; the dynamic code blocks include code blocks corresponding to ${...} placeholders and code blocks corresponding to #{...} placeholders; An abstract syntax tree corresponding to the SQL template is constructed based on the multiple dynamic code blocks and the syntax position corresponding to each code block in the multiple dynamic code blocks; the placeholder type, internal content and parameter name in the dynamic code block are marked in the node corresponding to the dynamic code block.

[0007] Optionally, in the above method, when traversing the abstract syntax tree, determining the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model based on the parameters input by the user includes: Traversing the abstract syntax tree, and sequentially identifying the parameter name and placeholder type corresponding to each dynamic node; For each dynamic node, do the following: Based on the parameters input by the user, it is determined whether the target parameter corresponding to the parameter name exists in the parameters and whether the target parameter is legal, and the parameter status of the parameter name is determined; Based on the parameter status of the parameter name and the placeholder type corresponding to the dynamic node, a parameter replacement strategy corresponding to the dynamic node is determined using a preset binary decision model.

[0008] Optionally, in the method described above, the preset binary decision model is a mapping matrix of the parameter status of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter status of the parameter name; the column identifier of the mapping matrix represents the placeholder type of the dynamic code block; the numerical value of the mapping matrix is a binary decision value, and the binary decision value is used to characterize the parameter replacement strategy corresponding to the current row and the current column.

[0009] Optionally, in the above method, if the target parameter corresponding to the parameter name exists in the parameters and the target parameter is legal, then executing the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node to obtain the target abstract syntax tree includes: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, replacing the parameter name in the dynamic node with the target parameter to obtain a first replacement dynamic code block, and performing semantic checking on the first replacement dynamic code block to obtain a first target abstract syntax tree; or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the parameter name in the dynamic node is converted into a precompiled placeholder, and the parameter value corresponding to the precompiled placeholder in the parameter is bound to obtain a second replacement dynamic code block, and the second replacement dynamic code block is subjected to parameter disinfection processing to obtain a second target abstract syntax tree.

[0010] Optionally, in the above method, if the target parameter corresponding to the parameter name does not exist in the parameters, executing the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node to obtain the target abstract syntax tree includes: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, deleting the dynamic node to obtain a first deleted abstract syntax tree, and performing syntax tree reconstruction on the first deleted abstract syntax tree to obtain a first target abstract syntax tree; or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the dynamic node is deleted to obtain a second deleted abstract syntax tree, and the second deleted abstract syntax tree is resumed to obtain a second target abstract syntax tree.

[0011] Optionally, the method as described above further includes: By calling a visualization tool, the target abstract syntax tree is converted into a visualization structure tree; the tree structure of the visualization structure tree is consistent with the tree structure of the target abstract syntax tree; In response to a user's replacement operation on a parameter name corresponding to a target dynamic code block in the visual structure tree, the same replacement operation is performed on a dynamic code block corresponding to the target dynamic code block in the target abstract syntax tree to obtain a modified target abstract syntax tree, and target SQL is generated according to the modified target abstract syntax tree.

[0012] In a second aspect, the present application provides an SQL reconstruction device, comprising: An SQL parsing module is configured to parse the acquired SQL template and generate an abstract syntax tree corresponding to the SQL template; the abstract syntax tree is a structured representation of the SQL template and is configured to describe the syntax positions of dynamic code blocks in the SQL template and the dependency relationships between parameters; the nodes of the abstract syntax tree include static nodes and dynamic nodes; the dynamic nodes are nodes corresponding to dynamic code blocks; a strategy determination module for determining, when traversing the abstract syntax tree, a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model based on parameters input by the user; the parameter replacement strategy includes a parameter replacement method and an SQL self-healing processing method after the parameter replacement; a policy execution module, configured to execute the corresponding parameter replacement policy on each dynamic node according to the parameter replacement policy corresponding to each dynamic node, to obtain a target abstract syntax tree; The structure conversion module is used to convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.

[0013] In a third aspect, the present application provides an electronic device, comprising: a processor, and a memory communicatively connected to the processor; Memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the SQL reconstruction method described in any one of the above embodiments.

[0014] In a fourth aspect, the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the SQL reconstruction method described in any one of the above embodiments.

[0015] Compared with the prior art, this application has the following beneficial effects: By parsing the acquired SQL template, an abstract syntax tree corresponding to the SQL template is generated; the abstract syntax tree is a structured representation of the SQL template, used to describe the grammatical position of dynamic code blocks in the SQL template and the dependency relationship between parameters; the nodes of the abstract syntax tree include static nodes and dynamic nodes; the dynamic nodes are nodes corresponding to dynamic code blocks and can clearly describe the grammatical position of dynamic code blocks in the SQL template and the dependency relationship between parameters. When performing parameter replacement, each dynamic code block can be accurately located, avoiding the problem of incorrect replacement that may occur with mechanical replacement, ensuring that the replacement operation is accurately applied to the required location, and improving the accuracy of SQL reconstruction; when traversing the abstract syntax tree, with the help of a preset binary decision model, the appropriate parameter replacement strategy can be determined for each dynamic node based on the parameters entered by the user. The parameter replacement strategy includes a parameter replacement method and an SQL self-healing processing method after parameter replacement; according to the parameter replacement strategy corresponding to each dynamic node, the corresponding parameter replacement strategy is executed on each dynamic node to obtain a target abstract syntax tree to adapt to various business scenarios and parameter inputs; the target abstract syntax tree is converted into a standard SQL statement to generate the target SQL, thereby avoiding errors that may occur during the SQL reconstruction process, ensuring the integrity of the SQL content, and improving the quality and readability of the SQL code. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0017] Figure 1 A flowchart of an SQL reconstruction method provided in an embodiment of the present application; Figure 2 A schematic diagram of the structure of an SQL reconstruction device provided in an embodiment of the present application; Figure 3 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0018] To make the objectives, technical solutions, and advantages of this application more clearly understood, the application is further described in detail below in conjunction with specific embodiments and with reference to the accompanying drawings. It should be noted that the embodiments described in the embodiments of this application are only part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making any creative efforts are within the scope of protection of this application.

[0019] It should be noted that, unless otherwise defined, the technical or scientific terms used in the embodiments of this application should have the ordinary meaning understood by people with ordinary skills in the field to which this application belongs. The words "first", "second" and similar terms used in the embodiments of this application do not indicate any order, quantity or importance, but are only used to distinguish different components. Words such as "include" or "comprise" mean that the elements or objects preceding the word include the elements or objects listed after the word and their equivalents, but do not exclude other elements or objects. Words such as "connect" or "connected" are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. "Up", "down", "left", "right" and the like are only used to indicate relative positional relationships. When the absolute position of the described object changes, the relative positional relationship may also change accordingly.

[0020] As described above, the current mechanical replacement method is essentially based on character matching and replacement operations. It can only simply find placeholders and replace them, but cannot conduct in-depth analysis of the semantics of the SQL statement, thereby destroying the logic of the entire SQL statement.

[0021] After research, this application proposes a SQL reconstruction method, device, equipment and storage medium to implement SQL, avoid errors in the SQL reconstruction process, and improve the integrity and readability of SQL content.

[0022] In order to help those skilled in the art better understand the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of this application.

[0023] See also Figure 1 , which is a flow chart of a SQL reconstruction method provided in an embodiment of the present application; S101: Parse the acquired SQL template to generate an abstract syntax tree corresponding to the SQL template.

[0024] The Abstract Syntax Tree (AST) is a structured representation of the SQL template, used to describe the syntax position and parameter dependencies of dynamic code blocks in the SQL template. The nodes of the AST include static nodes and dynamic nodes. Dynamic nodes are nodes corresponding to dynamic code blocks.

[0025] The SQL template is a SQL code file provided by the user that contains ${…?param} and #{…?param} placeholders.

[0026] Specifically, as an implementable manner, a specific implementation of "parsing the acquired SQL template and generating an abstract syntax tree corresponding to the SQL template" in S101 may include the following steps: S1011: Scan the SQL template using a regular expression matching method to identify multiple dynamic code blocks in the SQL template.

[0027] The dynamic code block includes the code block corresponding to the ${...} placeholder and the code block corresponding to the #{...} placeholder.

[0028] In this embodiment, for example, if the obtained SQL template is: SELECT * FROM trade WHERE 1=1 #{AND department IN (?deptList)} ${AND pay_time>'?payTime'} The regular expression is used to identify the parameter name ?deptList in the code block #{AND department IN (?deptList)} corresponding to the #{...} placeholder; the parameter name ?payTime in the code block ${AND pay_time>'?payTime'} corresponding to the ${...} placeholder, thereby determining the mapping relationship between the parameter name and the dynamic code block.

[0029] S1012: Construct an abstract syntax tree corresponding to the SQL template according to the multiple dynamic code blocks and the syntax position corresponding to each code block in the multiple dynamic code blocks.

[0030] The placeholder type, internal content, and parameter name in the dynamic code block are marked in the node corresponding to the dynamic code block.

[0031] In this embodiment, an abstract syntax tree (ABST) is constructed based on the dynamic syntax blocks identified above and their corresponding syntax positions. The root node in the ABST is SELECT * FROM trade and WHERE 1=1. The first child node is the code block corresponding to the #{...} placeholder, whose corresponding placeholder type is #{...}, whose internal content is ANDdepartment IN (?deptList), and whose parameter name is ?deptList. The second child node is the code block corresponding to the ${...} placeholder, whose corresponding placeholder type is ${...}, whose internal content is AND pay_time>'?payTime', and whose parameter name is ?payTime.

[0032] In this embodiment, a regular matching method is used to scan the SQL template to identify multiple dynamic code blocks in the SQL template; then, based on the multiple dynamic code blocks and the corresponding syntax positions of each code block in the multiple dynamic code blocks, an abstract syntax tree corresponding to the SQL template is constructed, which can enhance the parsing ability and flexibility of the SQL template.

[0033] S102: When traversing the abstract syntax tree, based on the parameters input by the user, a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree is determined from a preset binary decision model.

[0034] The parameter replacement strategy includes the parameter replacement method and the SQL self-recovery processing method after parameter replacement.

[0035] In this embodiment, the preset binary decision model is a mapping matrix between the parameter status of a parameter name and the placeholder type of a dynamic code block: the row identifier of the mapping matrix represents the parameter status of the parameter name; the column identifier of the mapping matrix represents the placeholder type of the dynamic code block; and the numerical value of the mapping matrix is a binary decision value, which is used to represent the parameter replacement strategy corresponding to the current row and the current column. For example, the preset binary decision model can be represented by a parameter-code block mapping matrix, and the true value representation is shown in Table 1: Table 1 Binary decision model

[0036] Specifically, a specific implementation of "when traversing the abstract syntax tree, determining the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model based on the parameters input by the user" in S102 may include the following steps: S1021: Traverse the abstract syntax tree and identify the parameter name and placeholder type corresponding to each dynamic node in turn.

[0037] In this embodiment, starting from the root node of the abstract syntax tree, a depth-first search (DFS) or breadth-first search (BFS) is used to traverse each dynamic node, check each dynamic node, and extract the placeholder type and parameter name corresponding to each dynamic node.

[0038] For each dynamic node, do the following: S1022: Based on the parameters input by the user, it is determined whether there is a target parameter corresponding to the parameter name and whether the target parameter is legal, and the parameter status of the parameter name is determined.

[0039] In this embodiment, multiple parameters passed in by the user are received, and a parameter existence check is performed on each parameter name to determine whether a target parameter corresponding to the parameter name exists among the multiple parameters passed in. At the same time, a validity check is performed on the target parameter. If it is determined that the target parameter corresponding to the parameter name exists and is valid, the parameter status of the parameter name is "exists and is valid." If it is determined that the target parameter corresponding to the parameter name exists and is invalid, or if the target parameter corresponding to the parameter name does not exist, the parameter status of the parameter name is "does not exist."

[0040] S1023: Based on the parameter status of the parameter name and the placeholder type corresponding to the dynamic node, a preset binary decision model is used to determine a parameter replacement strategy corresponding to the dynamic node.

[0041] In this embodiment, a parameter replacement strategy corresponding to the current dynamic node is determined based on a preset binary decision model. For example, if the parameter status of the parameter name is "existent and legal" and the placeholder type corresponding to the dynamic node is "${...} placeholder", the parameter replacement strategy corresponding to the dynamic node is determined to be static replacement and semantics check; if the parameter status of the parameter name is "existent and legal" and the placeholder type corresponding to the dynamic node is "#{...} placeholder", the parameter replacement strategy corresponding to the dynamic node is determined to be pre-compiled binding and parameter disinfection; if the parameter status of the parameter name is "non-existent" and the placeholder type corresponding to the dynamic node is "${...} placeholder", the parameter replacement strategy corresponding to the dynamic node is determined to be code block deletion and syntax tree reconstruction; if the parameter status of the parameter name is "non-existent" and the placeholder type corresponding to the dynamic node is "#{...} placeholder", the parameter replacement strategy corresponding to the dynamic node is determined to be code block deletion and breakpoint continuation.

[0042] In this embodiment, the abstract syntax tree is traversed to identify the parameter name and placeholder type corresponding to each dynamic node in turn; for each dynamic node, the following operations are performed: based on the parameters passed in by the user, it is determined whether there is a target parameter corresponding to the parameter name and whether the target parameter is legal, and the parameter status of the parameter name is determined; based on the parameter status of the parameter name and the placeholder type corresponding to the dynamic node, a preset binary decision model is used to determine the parameter replacement strategy corresponding to the dynamic node.

[0043] S103: According to the parameter replacement strategy corresponding to each dynamic node, execute the corresponding parameter replacement strategy on each dynamic node to obtain a target abstract syntax tree.

[0044] Specifically, if the target parameter corresponding to the parameter name exists in the parameter and the target parameter is legal, then the corresponding parameter replacement strategy is executed on each dynamic node according to the parameter replacement strategy corresponding to each dynamic node to obtain the target abstract syntax tree, including: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, the parameter name in the dynamic node is replaced with the target parameter to obtain a first replacement dynamic code block, and a semantic check is performed on the first replacement dynamic code block to obtain a first target abstract syntax tree.

[0045] In this embodiment, if the placeholder type corresponding to the dynamic node is a ${...} placeholder, the target parameter corresponding to the parameter name of the node is obtained from multiple parameters passed in by the user, and a first replacement dynamic code block is generated; then, a syntax check is performed on the first replacement dynamic code block to ensure that the replaced code block conforms to the SQL syntax, and a context check is performed on the first replacement dynamic code block to verify whether the value type of the target parameter matches the value type corresponding to the parameter name, thereby obtaining a first target abstract syntax tree after parameter replacement.

[0046] or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the parameter name in the dynamic node is converted into a precompiled placeholder, and the parameter value corresponding to the precompiled placeholder in the parameter is bound to obtain a second replacement dynamic code block, and the second replacement dynamic code block is parameter sanitized to obtain a second target abstract syntax tree.

[0047] In this embodiment, if the placeholder type corresponding to the dynamic node is the #{...} placeholder, the parameter name in the dynamic node is converted into a precompiled placeholder, a second replacement dynamic code block is generated, and the target parameter corresponding to the parameter name of the node determined from the multiple parameters passed in by the user is stored in the parameter mapping table for subsequent database driver use; the target parameter is then escaped or filtered, for example, special characters ', " are removed, and it is ensured that the value type of the target parameter is compatible with the database field type, and then the sanitized target parameter binding information is associated with the abstract syntax tree to generate a second target abstract syntax tree.

[0048] In this embodiment, if the placeholder type corresponding to the dynamic node is ${...} placeholder, the parameter name in the dynamic node is replaced with the target parameter to obtain a first replacement dynamic code block, and the first replacement dynamic code block is semantically checked to obtain a first target abstract syntax tree; or, if the placeholder type corresponding to the dynamic node is #{...} placeholder, the parameter name in the dynamic node is converted into a precompiled placeholder, and the parameter value corresponding to the precompiled placeholder in the parameter is bound to obtain a second replacement dynamic code block, and the second replacement dynamic code block is parameter disinfected to obtain a second target abstract syntax tree, thereby achieving safe and compliant processing of two types of placeholders and balancing flexibility and security requirements.

[0049] Based on the above embodiment, during the parameter name replacement process, the corresponding parameter processing process can be determined according to the parameter type corresponding to the parameter name through parameter adaptive parsing. For example, the parameter type can be defined as single-value parameter 1, multi-value parameter 1 and multi-value parameter 2. When the parameter type is single-value parameter 1, the parameter name ?param corresponds to List<? <object>Type, take get(0); for example, if the code block is #{AND pay_time>?payTime}, then after parameter replacement, it can be written as AND pay_time>'2022-01-01'. When the parameter type is multi-value parameter 1, the parameter name ?param corresponds to List <List <object>>Type, take get(0), then List <object>Convert to string, separated by commas; for example, if the placeholder is #{AND in (?tradeId)}, then the parameter replacement can be written as AND id IN ('00A','00B','00C','00D'). When the parameter type is multi-value parameter 2, the parameter name ?param can accept Map<String,Object> , and automatically parse it into the adapted syntax, taking the corresponding get(key), etc.; for example, if the placeholder is #{AND pay_time between ?payStartTime and ?payEndTime}, then after parameter replacement, it can be written as AND pay_time between '2022-01-01' and '2023-01-01'.

[0050] Optionally, as in the above method, if the target parameter corresponding to the parameter name does not exist in the parameters, then the corresponding parameter replacement strategy is executed on each dynamic node according to its corresponding parameter replacement strategy to obtain the target abstract syntax tree, including: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, the dynamic node is deleted to obtain a first deleted abstract syntax tree, and syntax tree reconstruction is performed on the first deleted abstract syntax tree to obtain a first target abstract syntax tree.

[0051] In this embodiment, for example, if the current code block is as shown in the following embodiment: SELECT * FROM trade WHERE 1=1 #{and status=1 AND department IN (?deptList)} ${AND pay_time>'?payTime'} If the deptList parameter passed in by the user is missing, the second child node of ${AND pay_time>'?payTime'} corresponding to the payTime parameter is deleted, resulting in the first post-deletion abstract syntax tree. The semantics of the post-deletion abstract syntax tree are then checked. For example, the remaining conditions under WHERE_NODE can be checked first. If only 1=1 remains, 1=1 is considered redundant, and the entire WHERE clause is deleted. If other valid conditions exist, such as #{...}, the WHERE keyword is retained, and the SQL code is verified for structural validity to ensure that the WHERE clause is not empty or contains only invalid conditions.

[0052] or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the dynamic node is deleted to obtain a second deleted abstract syntax tree, and the second deleted abstract syntax tree is reconnected to obtain a second target abstract syntax tree.

[0053] In this embodiment, if the deptList parameter passed in by the user is missing, the first child node of #{and status=1 AND department IN (?deptList)} corresponding to the deptList parameter is deleted to obtain the second deleted abstract syntax tree; and the second deleted abstract syntax tree is subjected to breakpoint resumption processing. At this time, if the ${...} placeholder is not deleted, the second child node corresponding to ${AND pay_time>'?payTime'} is updated to the first child node, thereby obtaining the second target abstract syntax tree.

[0054] In this embodiment, if the placeholder type corresponding to the dynamic node is ${...} placeholder, the dynamic node is deleted to obtain a first deleted abstract syntax tree, and the first deleted abstract syntax tree is reconstructed to obtain a first target abstract syntax tree; or, if the placeholder type corresponding to the dynamic node is #{...} placeholder, the dynamic node is deleted to obtain a second deleted abstract syntax tree, and the second deleted abstract syntax tree is reconnected to obtain a second target abstract syntax tree, which can dynamically adapt to changes in SQL structure, avoid code redundancy, and improve SQL readability.

[0055] S104: Convert the target abstract syntax tree into a standard SQL statement to generate target SQL.

[0056] In this embodiment, based on the target abstract syntax tree after parameter replacement and automatic healing processing, the target abstract syntax tree is traversed to generate a complete target SQL according to the node type.

[0057] In this embodiment, an abstract syntax tree (ABST) corresponding to the SQL template is generated by parsing the obtained SQL template. The ABST is a structured representation of the SQL template, used to describe the syntactic position of dynamic code blocks and the parameter dependencies within the SQL template. The ABST nodes include static nodes and dynamic nodes. Dynamic nodes correspond to dynamic code blocks and clearly describe the syntactic position of dynamic code blocks and the parameter dependencies within the SQL template. When performing parameter replacement, each dynamic code block can be precisely located, avoiding the potential for incorrect replacements associated with mechanical replacements. This ensures that replacements are accurately applied to the required locations, improving the accuracy of SQL reconstruction. When traversing the ABST, a pre-set binary decision model is used to determine the appropriate parameter replacement strategy for each dynamic node based on the parameters entered by the user. The parameter replacement strategy includes the parameter replacement method and the SQL self-healing processing method after parameter replacement; according to the parameter replacement strategy corresponding to each dynamic node, the corresponding parameter replacement strategy is executed on each dynamic node to obtain the target abstract syntax tree to adapt to various business scenarios and parameter inputs; the target abstract syntax tree is converted into a standard SQL statement to generate the target SQL, thereby avoiding errors that may occur during the SQL reconstruction process, ensuring the integrity of the SQL content, and improving the quality and readability of the SQL code.

[0058] Furthermore, based on the above embodiment, in order to further improve the debugging efficiency of operation and maintenance personnel, reduce the risk of misoperation, and solve the SQL splicing black box problem in traditional solutions, the method also includes: By calling the visualization tool, the target abstract syntax tree is converted into a visualization structure tree.

[0059] The tree structure of the visualization structure tree is consistent with the tree structure of the target abstract syntax tree.

[0060] In response to the user's replacement operation on the parameter name corresponding to the target dynamic code block in the visual structure tree, the same replacement operation is performed on the dynamic code block corresponding to the target dynamic code block in the target abstract syntax tree to obtain a modified target abstract syntax tree, and the target SQL is generated according to the modified target abstract syntax tree.

[0061] In this embodiment, the visualization tool is called through an API or UI component, and the tree data of the target abstract syntax tree is passed to the visualization tool, wherein the tree data includes but is not limited to node types, parent-child relationships, and attributes. Each abstract syntax tree node is mapped to a node of the visualization tree, and the original hierarchy and association relationships are retained. Among them, the nodes corresponding to the dynamic code blocks can be marked with special styles, such as highlights and icons. The user can edit the parameter name of the dynamic code block by clicking on the target dynamic code block node in the visualization tree. The visualization tool encapsulates the editing operation as an event and sends it back to the back end to synchronously perform adaptive parameter updates on the target abstract syntax tree, and re-traverse the modified target abstract syntax tree to generate SQL according to the standard process.

[0062] In this embodiment, a visualization tool is invoked to convert the target abstract syntax tree into a visual structure tree. The tree structure of the visual structure tree is consistent with that of the target abstract syntax tree. In response to a user replacing a parameter name corresponding to a target dynamic code block in the visual structure tree, the same replacement operation is performed on the dynamic code block corresponding to the target dynamic code block in the target abstract syntax tree, resulting in a modified target abstract syntax tree. Target SQL is then generated based on the modified target abstract syntax tree. This achieves a closed loop from visual interaction to abstract syntax tree modification to SQL generation, improving the usability and maintainability of dynamic SQL templates.

[0063] See also Figure 2 , which is a structural diagram of an SQL reconstruction device provided by an embodiment of the present application; Figure 2 As shown in the solid line frame, the device 20 includes an SQL parsing module 21 , a policy determination module 22 , a policy execution module 23 and a structure conversion module 24 .

[0064] Among them, the SQL parsing module 21 is used to parse the acquired SQL template and generate an abstract syntax tree corresponding to the SQL template; the abstract syntax tree is a structured representation of the SQL template, which is used to describe the syntax position of the dynamic code block in the SQL template and the dependency relationship of the parameters; the nodes of the abstract syntax tree include static nodes and dynamic nodes; the dynamic nodes are the nodes corresponding to the dynamic code blocks. The strategy determination module 22 is used to determine the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from the preset binary decision model based on the parameters passed in by the user when traversing the abstract syntax tree; the parameter replacement strategy includes the parameter replacement method and the SQL self-healing processing method after the parameter replacement;. The strategy execution module 23 is used to execute the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node, and obtain the target abstract syntax tree. The structure conversion module 24 is used to convert the target abstract syntax tree into a standard SQL statement to generate the target SQL.

[0065] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0066] Furthermore, based on the above embodiment, the SQL parsing module 21 is specifically used to scan the SQL template using a regular matching method to identify multiple dynamic code blocks in the SQL template; the dynamic code blocks include code blocks corresponding to the ${...} placeholder and code blocks corresponding to the #{...} placeholder; according to the multiple dynamic code blocks and the corresponding syntax positions of each code block in the multiple dynamic code blocks, an abstract syntax tree corresponding to the SQL template is constructed; the placeholder type, internal content and parameter name in the dynamic code block are marked in the node corresponding to the dynamic code block.

[0067] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0068] Furthermore, based on the above embodiment, the strategy determination module 22 is specifically used to traverse the abstract syntax tree and identify the parameter name and placeholder type corresponding to each dynamic node in turn; for each dynamic node, the following operations are performed: based on the parameters passed in by the user, it is judged whether there is a target parameter corresponding to the parameter name and whether the target parameter is legal, and the parameter status of the parameter name is determined; based on the parameter status of the parameter name and the placeholder type corresponding to the dynamic node, a preset binary decision model is used to determine the parameter replacement strategy corresponding to the dynamic node.

[0069] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0070] Furthermore, based on the above embodiment, the binary decision model preset in the strategy determination module 22 is a mapping matrix of the parameter status of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter status of the parameter name; the column identifier of the mapping matrix represents the placeholder type of the dynamic code block; the numerical value of the mapping matrix is a binary decision value, and the binary decision value is used to represent the parameter replacement strategy corresponding to the current row and the current column.

[0071] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0072] Further, based on the above embodiment, if there is a target parameter corresponding to the parameter name in the parameter and the target parameter is legal, the policy execution module 23 is specifically used to replace the parameter name in the dynamic node with the target parameter if the placeholder type corresponding to the dynamic node is ${...} placeholder, to obtain a first replacement dynamic code block, and perform semantic checking on the first replacement dynamic code block to obtain a first target abstract syntax tree; or, if the placeholder type corresponding to the dynamic node is #{...} placeholder, then convert the parameter name in the dynamic node into a precompiled placeholder, and bind the parameter value corresponding to the precompiled placeholder in the parameter to obtain a second replacement dynamic code block, and perform parameter disinfection on the second replacement dynamic code block to obtain a second target abstract syntax tree.

[0073] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0074] Further, based on the above embodiment, if the target parameter corresponding to the parameter name does not exist in the parameter, the policy execution module 23 is specifically used to, if the placeholder type corresponding to the dynamic node is ${...} placeholder, delete the dynamic node to obtain a first deleted abstract syntax tree, and perform syntax tree reconstruction on the first deleted abstract syntax tree to obtain a first target abstract syntax tree; or, if the placeholder type corresponding to the dynamic node is #{...} placeholder, delete the dynamic node to obtain a second deleted abstract syntax tree, and perform breakpoint continuation on the second deleted abstract syntax tree to obtain a second target abstract syntax tree.

[0075] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0076] Further, if Figure 2 As shown in the dotted box, based on the above embodiment, the device 20 may further include a visualization module 25 .

[0077] Specifically, the visualization module 25 is used to convert the target abstract syntax tree into a visual structure tree by calling a visualization tool; the tree structure of the visual structure tree is consistent with the tree structure of the target abstract syntax tree; in response to the user's replacement operation on the parameter name corresponding to the target dynamic code block in the visual structure tree, the same replacement operation is performed on the dynamic code block corresponding to the target dynamic code block in the target abstract syntax tree to obtain a modified target abstract syntax tree, and the target SQL is generated according to the modified target abstract syntax tree.

[0078] An SQL reconstruction device provided in an embodiment of the present application can execute the technical solution shown in the above method embodiment. Its implementation principle and beneficial effects are similar and will not be repeated here.

[0079] See also Figure 3 , which is a schematic diagram of the structure of an electronic device provided in an embodiment of the present application, including: Memory 11, for storing computer programs; The processor 12 is configured to implement the steps of an SQL reconstruction method described in any of the above method embodiments when executing the computer program.

[0080] In this embodiment, the device may be an in-vehicle computer, a PC (Personal Computer), or a terminal device such as a smart phone, a tablet computer, a PDA, or a portable computer.

[0081] The device may include a memory 11 , a processor 12 , and a bus 13 .

[0082] The memory 11 includes at least one type of readable storage medium, including flash memory, a hard disk, a multimedia card, a card-type memory (e.g., SD or DX memory), a magnetic memory, a magnetic disk, an optical disk, etc. In some embodiments, the memory 11 may be an internal storage unit of the device, such as the device's hard disk. In other embodiments, the memory 11 may also be an external storage device of the device, such as a plug-in hard disk equipped on the device, a SmartMedia Card (SMC), a Secure Digital (SD) card, a flash memory card, etc. Furthermore, the memory 11 may include both an internal storage unit of the device and an external storage device. The memory 11 can be used not only to store application software installed on the device and various types of data, such as program code for executing the SQL reconstruction method, but also to temporarily store data that has been output or is about to be output. In some embodiments, the processor 12 may be a central processing unit (CPU).

[0083] In some embodiments, the processor 12 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip, configured to execute program codes stored in the memory 11 or process data, such as program codes for executing SQL reconstruction methods.

[0084] The bus 13 may be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 3 Only one thick line is used in the diagram, but this does not mean that there is only one bus or one type of bus.

[0085] Furthermore, the device may also include a network interface 14, which may optionally include a wired interface and / or a wireless interface (such as a WI-FI interface, a Bluetooth interface, etc.), which is generally used to establish a communication connection between the device and other electronic devices.

[0086] Optionally, the device may further include a user interface 15, which may include a display and an input unit such as a keyboard. Optionally, the user interface 15 may also include a standard wired interface or a wireless interface. Optionally, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, or an OLED (Organic Light-Emitting Diode) touchscreen. The display may also be appropriately referred to as a display screen or display unit, and is used to display information processed by the device and to display a visual user interface.

[0087] Figure 3 Only the device with components 11-15 is shown, and it will be understood by those skilled in the art that Figure 3 The structure shown does not constitute a limitation of the device, and may include fewer or more components than shown, or combine certain components, or arrange the components differently.

[0088] Based on the same inventive concept, corresponding to any of the above-mentioned embodiments and methods, an embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable the computer to execute the SQL reconstruction method described in any of the above embodiments.

[0089] The computer-readable media of the embodiments of the present application include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, read-only compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device.

[0090] The computer instructions stored in the storage medium of the above embodiment are used to enable the computer to execute the SQL reconstruction method described in any of the above embodiments, and have the beneficial effects of the corresponding method embodiments, which will not be repeated here.

[0091] It should be noted that the various embodiments in this specification are described in a progressive manner. The same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for methods, devices, electronic devices and media, since they are basically similar to the method embodiments, the description is relatively simple. For relevant parts, refer to the partial description of the method embodiments. The methods, devices, electronic devices and media described above are merely illustrative. The units described as separate components may or may not be physically separated, and the components indicated as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. A person of ordinary skill in the art can understand and implement them without expending any creative effort.

[0092] The above is merely one specific embodiment of the present application, but the scope of protection of the present application is not limited thereto. Any changes or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in this application should be included in the scope of protection of the present application. Therefore, the scope of protection of the present application should be based on the scope of protection of the claims.< / object> < / object> < / object>

Claims

1. A SQL reconstruction method, characterized in that: include: Parse the SQL template to generate an abstract syntax tree. The abstract syntax tree is a structured representation of the SQL template, used to describe the syntax position of the dynamic code block in the SQL template and the dependency relationship between parameters; The nodes of the abstract syntax tree include static nodes and dynamic nodes; The dynamic node is a node corresponding to the dynamic code block; When traversing the abstract syntax tree, determining the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model based on the parameters input by the user; The parameter replacement strategy includes the parameter replacement method and the SQL self-recovery processing method after the parameter replacement; According to the parameter replacement strategy corresponding to each dynamic node, the corresponding parameter replacement strategy is executed on each dynamic node to obtain a target abstract syntax tree; The target abstract syntax tree is converted into a standard SQL statement to generate target SQL.

2. The method according to claim 1, characterized in that The step of parsing the acquired SQL template to generate an abstract syntax tree corresponding to the SQL template includes: Scanning the SQL template using a regular matching method to identify multiple dynamic code blocks in the SQL template; the dynamic code blocks include code blocks corresponding to ${...} placeholders and code blocks corresponding to #{...} placeholders; An abstract syntax tree corresponding to the SQL template is constructed based on the multiple dynamic code blocks and the syntax position corresponding to each code block in the multiple dynamic code blocks; the placeholder type, internal content and parameter name in the dynamic code block are marked in the node corresponding to the dynamic code block.

3. The method according to any one of claims 1-2, characterized in that When traversing the abstract syntax tree, based on the parameters input by the user, determining the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model includes: Traversing the abstract syntax tree, and sequentially identifying the parameter name and placeholder type corresponding to each dynamic node; For each dynamic node, do the following: Based on the parameters input by the user, it is determined whether the target parameter corresponding to the parameter name exists in the parameters and whether the target parameter is legal, and the parameter status of the parameter name is determined; Based on the parameter status of the parameter name and the placeholder type corresponding to the dynamic node, a parameter replacement strategy corresponding to the dynamic node is determined using a preset binary decision model.

4. The method according to claim 3, characterized in that The preset binary decision model is a mapping matrix of the parameter status of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter status of the parameter name; the column identifier of the mapping matrix represents the placeholder type of the dynamic code block; the value of the mapping matrix is a binary decision value, and the binary decision value is used to characterize the parameter replacement strategy corresponding to the current row and the current column.

5. The method according to claim 3, characterized in that If the target parameter corresponding to the parameter name exists in the parameter and the target parameter is legal, then executing the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node to obtain a target abstract syntax tree, including: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, replacing the parameter name in the dynamic node with the target parameter to obtain a first replacement dynamic code block, and performing semantic checking on the first replacement dynamic code block to obtain a first target abstract syntax tree; or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the parameter name in the dynamic node is converted into a precompiled placeholder, and the parameter value corresponding to the precompiled placeholder in the parameter is bound to obtain a second replacement dynamic code block, and the second replacement dynamic code block is subjected to parameter disinfection processing to obtain a second target abstract syntax tree.

6. The method according to claim 3, characterized in that If the target parameter corresponding to the parameter name does not exist in the parameters, then executing the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node to obtain a target abstract syntax tree, including: If the placeholder type corresponding to the dynamic node is a ${...} placeholder, deleting the dynamic node to obtain a first deleted abstract syntax tree, and performing syntax tree reconstruction on the first deleted abstract syntax tree to obtain a first target abstract syntax tree; or, If the placeholder type corresponding to the dynamic node is a #{...} placeholder, the dynamic node is deleted to obtain a second deleted abstract syntax tree, and the second deleted abstract syntax tree is resumed to obtain a second target abstract syntax tree.

7. The method according to claim 1, characterized in that The method further comprises: By calling a visualization tool, the target abstract syntax tree is converted into a visualization structure tree; the tree structure of the visualization structure tree is consistent with the tree structure of the target abstract syntax tree; In response to a user's replacement operation on a parameter name corresponding to a target dynamic code block in the visual structure tree, the same replacement operation is performed on a dynamic code block corresponding to the target dynamic code block in the target abstract syntax tree to obtain a modified target abstract syntax tree, and a target SQL is generated according to the modified target abstract syntax tree.

8. A SQL reconstruction device, characterized in that: include: An SQL parsing module is used to parse the acquired SQL template and generate an abstract syntax tree corresponding to the SQL template; The abstract syntax tree is a structured representation of the SQL template, used to describe the syntax position of the dynamic code block in the SQL template and the dependency relationship between parameters; The nodes of the abstract syntax tree include static nodes and dynamic nodes; The dynamic node is a node corresponding to the dynamic code block; a strategy determination module, configured to determine, when traversing the abstract syntax tree, based on parameters input by a user, a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model; The parameter replacement strategy includes the parameter replacement method and the SQL self-recovery processing method after the parameter replacement; a policy execution module, configured to execute the corresponding parameter replacement policy on each dynamic node according to the parameter replacement policy corresponding to each dynamic node, to obtain a target abstract syntax tree; The structure conversion module is used to convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.

9. An electronic device, characterized in that: The device includes: a processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 7 when executed by a processor.

Images