A SQL refactoring method, device, equipment and storage medium
By generating an abstract syntax tree for the SQL template and applying a binary decision model for parameter replacement, the problem of insufficient semantic recognition in SQL refactoring is solved, achieving higher accuracy and readability.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2025-05-21
- Publication Date
- 2026-03-24
AI Technical Summary
In existing technologies, semantic code blocks cannot be identified during SQL refactoring, which leads to parameter replacement disrupting the original logical structure, resulting in syntax errors and reducing the integrity and readability of SQL content.
By generating an abstract syntax tree of the SQL template, identifying dynamic code blocks, and determining parameter replacement strategies based on a binary decision model, precise replacement and self-healing processes are performed to generate the target SQL.
It improves the accuracy and completeness of SQL refactoring, ensures that parameter replacements are accurately applied to the required locations, avoids errors, and enhances the quality and readability of SQL code.
Smart Images
Figure CN120508568B_ABST
Abstract
Description
TECHNICAL FIELD
[0001] The present application relates to the technical field of database security, in particular to a SQL reconstruction method and device, equipment and a storage medium. BACKGROUND
[0002] With the complication of software development, it is impossible to determine the specific parameter values in the SQL statement when writing code in many scenarios. For example, in an enterprise-level report system, users can query data according to different time ranges, departments, product categories and other conditions. Therefore, it is necessary to dynamically generate SQL that meets the application scenario by replacing parameters in some placeholders in the original SQL.
[0003] In the prior art, mechanical parameter and statement replacement is often used to achieve SQL template filling reconstruction by scanning placeholders character by character and replacing statements in the placeholders. However, this method cannot identify semantic code blocks, resulting in the destruction of the original logical structure when replacing parameters, and in the case of complex code structure, syntax errors may occur, reducing the integrity and readability of the SQL content. SUMMARY
[0004] Based on the above problems, the present application provides a SQL reconstruction method, device, equipment and storage medium, which aims to avoid errors in the SQL reconstruction process and improve the integrity and readability of the SQL content.
[0005] The embodiments of the present application disclose the following technical solutions:
[0006] In a first aspect, the present application provides a SQL reconstruction method, comprising:
[0007] parsing the obtained 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 and the dependency relationship of the parameters in the SQL template; the nodes of the abstract syntax tree include static nodes and dynamic nodes; the dynamic nodes are nodes corresponding to dynamic code blocks;
[0008] When traversing the abstract syntax tree, based on the parameters input by the user, determine the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model; the parameter replacement strategy includes a parameter replacement method and a SQL self-healing processing method after parameter replacement;
[0009] According to the parameter replacement strategy corresponding to each dynamic node, respectively execute the corresponding parameter replacement strategy for each dynamic node to obtain a target abstract syntax tree;
[0010] Convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.
[0011] Optionally, the method described above, the SQL template is parsed to generate the abstract syntax tree corresponding to the SQL template, comprising:
[0012] The SQL template is scanned using a regular matching method to identify a plurality of dynamic code blocks in the SQL template; the dynamic code blocks include code blocks corresponding to ${...} placeholders and code blocks corresponding to #{...} placeholders;
[0013] According to the plurality of dynamic code blocks and the syntax position corresponding to each code block in the plurality of dynamic code blocks, the 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.
[0014] Optionally, the method described above, when traversing the abstract syntax tree, based on the parameters input by the user, the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree is determined from a preset binary decision model, comprising:
[0015] Traverse the abstract syntax tree to identify the parameter name and the placeholder type corresponding to each dynamic node in turn;
[0016] For each dynamic node, the following operations are performed:
[0017] Based on the parameters input by the user, it is judged whether the target parameter corresponding to the parameter name exists in the parameters and whether the target parameter is legal, and the parameter state of the parameter name is determined;
[0018] Based on the parameter state 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.
[0019] Optionally, the method described above, the preset binary decision model is a mapping matrix of parameter name parameter state and dynamic code block placeholder type: the row identifier of the mapping matrix represents the parameter state 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.
[0020] Optionally, if the target parameter corresponding to the parameter name exists in the parameter and the target parameter is legal, the method as described above, according to the parameter replacement strategy corresponding to each dynamic node respectively, performs the corresponding parameter replacement strategy on each dynamic node to obtain a target abstract syntax tree, including:
[0021] If the placeholder type corresponding to the dynamic node is a ${...} placeholder, the parameter name in the dynamic node is replaced by the target parameter to obtain a first replaced dynamic code block, and a semantic check is performed on the first replaced dynamic code block to obtain a first target abstract syntax tree;
[0022] Alternatively,
[0023] 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 replaced dynamic code block, and a parameter sterilization is performed on the second replaced dynamic code block to obtain a second target abstract syntax tree.
[0024] Optionally, if the target parameter corresponding to the parameter name does not exist in the parameter, the method as described above, according to the parameter replacement strategy corresponding to each dynamic node respectively, performs the corresponding parameter replacement strategy on each dynamic node to obtain a target abstract syntax tree, including:
[0025] 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 a syntax tree reconstruction is performed on the first deleted abstract syntax tree to obtain a first target abstract syntax tree;
[0026] Alternatively,
[0027] 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 a breakpoint continuation is performed on the second deleted abstract syntax tree to obtain a second target abstract syntax tree.
[0028] Optionally, the method as described above further includes:
[0029] The target abstract syntax tree is converted 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;
[0030] 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, a modified target abstract syntax tree is obtained, and a target SQL is generated according to the modified target abstract syntax tree.
[0031] In a second aspect, the present application provides a SQL reconstruction device, comprising:
[0032] An SQL parsing module is configured to parse the obtained 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 and is configured to describe the syntax position of a dynamic code block in the SQL template and the dependency relationship of parameters; a node of the abstract syntax tree comprises a static node and a dynamic node; the dynamic node is a node corresponding to a dynamic code block;
[0033] A strategy determination module is configured to determine, based on a user's input parameter, a parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree from a preset binary decision model when the abstract syntax tree is traversed; the parameter replacement strategy comprises a parameter replacement mode and a SQL self-recovery processing mode after parameter replacement;
[0034] A strategy execution module is configured to execute the corresponding parameter replacement strategy on each dynamic node according to the parameter replacement strategy corresponding to each dynamic node, to obtain a target abstract syntax tree;
[0035] A structure conversion module is configured to convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.
[0036] In a third aspect, the present application provides an electronic device, comprising a processor and a memory in communication with the processor;
[0037] The memory stores computer execution instructions;
[0038] The processor executes the computer execution instructions stored in the memory to implement the SQL reconstruction method described in any of the above embodiments.
[0039] In a fourth aspect, the present application provides a computer readable storage medium, which stores computer execution instructions; when the computer execution instructions are executed by a processor, the computer execution instructions are used to implement the SQL reconstruction method described in any of the above embodiments.
[0040] Compared with the prior art, the present application has the following beneficial effects:
[0041] The SQL template is parsed to generate an abstract syntax tree corresponding to the SQL template; the abstract syntax tree is a structured representation of the SQL template, and 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 nodes corresponding to the dynamic code block, and can clearly describe the syntax position of the dynamic code block in the SQL template and the dependency relationship of the parameters. When the parameter replacement is performed, each dynamic code block can be accurately positioned, and the error replacement problem that may occur in the mechanical replacement is avoided, so that the replacement operation is accurately applied to the required position, and the accuracy of the SQL reconstruction is improved; when the abstract syntax tree is traversed, a preset binary decision model is used to determine a suitable parameter replacement strategy for each dynamic node according to the parameters input by the user. The parameter replacement strategy includes a parameter replacement mode and a SQL self-recovery processing mode after the parameter replacement; each dynamic node is executed according to the corresponding parameter replacement strategy of each dynamic node, to obtain a target abstract syntax tree, so as to adapt to various business scenarios and parameter inputs; the target abstract syntax tree is converted into a standard SQL statement to generate a target SQL, and errors that may occur in the SQL reconstruction process are avoided, the completeness of the SQL content is ensured, and the SQL code quality and readability are improved. BRIEF DESCRIPTION OF DRAWINGS
[0042] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the drawings needed in the embodiments or prior art description will be briefly introduced. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.
[0043] Figure 1 A flowchart of a SQL reconstruction method provided by an embodiment of the present application;
[0044] Figure 2 A structural diagram of a SQL reconstruction device provided by an embodiment of the present application;
[0045] Figure 3 A structural diagram of an electronic device provided by an embodiment of the present application. DETAILED DESCRIPTION
[0046] In order to make the purposes, technical solutions and advantages of the present application clearer, the present application will be further described in detail below with reference to the drawings and in conjunction with specific embodiments. It should be particularly pointed out that the embodiments described in the embodiments of the present application are only some of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor fall within the scope of protection of the present application.
[0047] It should be noted that, unless otherwise defined, the technical terms or scientific terms used in the embodiments of the present application should be understood as the usual meanings understood by those of ordinary skill in the art to which the present application belongs. The terms "first", "second" and the like used in the embodiments of the present application do not represent any order, number or importance, but are only used to distinguish different components. The terms "include" or "contain" and the like mean that the elements or objects before the terms cover the elements or objects listed after the terms and their equivalents, and do not exclude other elements or objects. The terms "connect" or "connected" and the like are not limited to physical or mechanical connections, but can include electrical connections, whether direct or indirect. The terms "up", "down", "left", "right" and the like are only used to represent relative positional relationships, and when the absolute position of the described object changes, the relative positional relationship may also change accordingly.
[0048] As described above, the current mechanical replacement method is essentially based on character matching and replacement operations, which can only simply find and replace placeholders, and cannot deeply analyze the semantics of SQL statements, thereby destroying the logic of the entire SQL statement.
[0049] After research, the present application proposes a SQL reconstruction method, device, equipment and storage medium, which realizes SQL and avoids errors in the SQL reconstruction process, and improves the integrity and readability of the SQL content.
[0050] In order to enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor fall within the scope of protection of the present application.
[0051] Referring to Figure 1 FIG. 1 is a flowchart of a SQL reconstruction method according to an embodiment of the present application;
[0052] S101: parsing the obtained SQL template to generate an abstract syntax tree corresponding to the SQL template.
[0053] The Abstract Syntax Tree (AST) is a structured representation of the SQL template, used to describe the syntax position of the dynamic code block and the dependency relationship of the parameters in the SQL template. 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.
[0054] The SQL template is a SQL code file provided for a user, containing ${…?param} and #{…?param} placeholders.
[0055] Specifically, as an implementable manner, a specific implementation of "parsing the obtained SQL template to generate an Abstract Syntax Tree corresponding to the SQL template" in S101 can include the following steps:
[0056] S1011: Scan the SQL template by using a regular matching method to identify a plurality of dynamic code blocks in the SQL template.
[0057] The dynamic code blocks include code blocks corresponding to ${…} placeholders and code blocks corresponding to #{…} placeholders.
[0058] In this embodiment, for example, if the obtained SQL template is:
[0059] SELECT * FROM trade
[0060] WHERE 1=1
[0061] #{AND department IN (?deptList)}
[0062] ${AND pay_time>'?payTime'}
[0063] The code block corresponding to the #{…} placeholder #{AND department IN (?deptList)} is identified by using a regular expression, and the parameter name is?deptList. The code block corresponding to the ${…} placeholder ${AND pay_time>'?payTime'} corresponds to the parameter name?payTime, and the mapping relationship between the parameter name and the dynamic code block is determined.
[0064] S1012: According to the plurality of dynamic code blocks and the syntax position of each code block in the plurality of dynamic code blocks, an Abstract Syntax Tree corresponding to the SQL template is constructed.
[0065] The placeholder type, the internal content, and the parameter name in the dynamic code block are marked in the node corresponding to the dynamic code block.
[0066] In this embodiment, the corresponding abstract syntax tree is constructed based on the determined dynamic syntax block and the respective corresponding syntax position. The root node of the abstract syntax tree is SELECT * FROM trade and WHERE 1 = 1, the first child node is the code block corresponding to the #{...} placeholder, the corresponding placeholder type of which is #{...}, the internal content of which is AND department IN (?deptList), and the parameter name of which is?deptList; and the second child node is the code block corresponding to the ${...} placeholder, the corresponding placeholder type of which is ${...}, the internal content of which is AND pay_time > '?payTime', and the parameter name of which is?payTime.
[0067] In this embodiment, the regular matching method is used to scan the SQL template to identify the plurality of dynamic code blocks in the SQL template; and the abstract syntax tree corresponding to the SQL template is constructed according to the plurality of dynamic code blocks and the respective corresponding syntax position of each code block in the plurality of dynamic code blocks, which can enhance the parsing ability and flexibility of the SQL template.
[0068] S102: When traversing the abstract syntax tree, determine the respective corresponding parameter replacement strategy of each dynamic node in the abstract syntax tree from the preset binary decision model based on the parameter input by the user.
[0069] The parameter replacement strategy includes a parameter replacement method and a SQL self-healing processing method after parameter replacement.
[0070] In this embodiment, the preset binary decision model is a mapping matrix of the parameter state of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter state 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 truth value is represented as shown in Table 1:
[0071] Table 1 Binary decision model
[0072]
[0073] Specifically, one specific implementation of "determining the respective corresponding parameter replacement strategy of each dynamic node in the abstract syntax tree from the preset binary decision model based on the parameter input by the user when traversing the abstract syntax tree" in S102 can include the following steps:
[0074] S1021: Traverse the abstract syntax tree to identify the parameter name and the placeholder type corresponding to each dynamic node in turn.
[0075] In this embodiment, starting from the root node of the abstract syntax tree, a depth first search (DFS) or a breadth first search (BFS) is performed to check each dynamic node and extract the placeholder type and the parameter name corresponding to each dynamic node.
[0076] For each dynamic node, the following operations are performed:
[0077] S1022: Determine the parameter state of the parameter name based on the target parameter corresponding to the parameter name in the user input parameters and whether the target parameter is legal.
[0078] In this embodiment, multiple user input parameters are received, and a parameter existence check is performed on each parameter name to determine whether the target parameter corresponding to the parameter name exists in the multiple input parameters. At the same time, a legality check is performed on the target parameter. When it is determined that the target parameter corresponding to the parameter name exists and is legal, the parameter state of the parameter name is existing and legal. When it is determined that the target parameter corresponding to the parameter name exists but is not legal, or the target parameter corresponding to the parameter name does not exist, the parameter state of the parameter name is non-existing.
[0079] S1023: Determine the parameter replacement strategy corresponding to the dynamic node based on the parameter state of the parameter name and the placeholder type corresponding to the dynamic node using a preset binary decision model.
[0080] In this embodiment, the parameter replacement strategy corresponding to the current dynamic node is determined based on the preset binary decision model. For example, if the parameter state of the parameter name is existing and legal, and the placeholder type corresponding to the dynamic node is the ${...} placeholder, it is determined that the parameter replacement strategy corresponding to the dynamic node is static replacement and semantic check. If the parameter state of the parameter name is existing and legal, and the placeholder type corresponding to the dynamic node is the #{...} placeholder, it is determined that the parameter replacement strategy corresponding to the dynamic node is pre-compiled binding and parameter sanitization. If the parameter state of the parameter name is non-existing, and the placeholder type corresponding to the dynamic node is the ${...} placeholder, it is determined that the parameter replacement strategy corresponding to the dynamic node is code block deletion and syntax tree reconstruction. If the parameter state of the parameter name is non-existing, and the placeholder type corresponding to the dynamic node is the #{...} placeholder, it is determined that the parameter replacement strategy corresponding to the dynamic node is code block deletion and breakpoint continuation.
[0081] In the embodiment, the abstract syntax tree is traversed to identify the parameter name corresponding to each dynamic node and the placeholder type in turn; for each dynamic node, the following operations are performed: determining the parameter state of the parameter name based on whether the target parameter corresponding to the parameter name exists in the parameters input by the user and whether the target parameter is legal; determining the parameter replacement strategy corresponding to the dynamic node based on the parameter state of the parameter name and the placeholder type corresponding to the dynamic node by using a preset binary decision model.
[0082] S103: performing the corresponding parameter replacement strategy on each dynamic node respectively according to the respective parameter replacement strategy corresponding to each dynamic node to obtain a target abstract syntax tree.
[0083] Specifically, if the target parameter corresponding to the parameter name exists in the parameters and the target parameter is legal, the corresponding parameter replacement strategy is performed on each dynamic node respectively according to the respective parameter replacement strategy corresponding to each dynamic node to obtain a target abstract syntax tree, including:
[0084] If the placeholder type corresponding to the dynamic node is the ${...} placeholder, the parameter name in the dynamic node is replaced by the target parameter to obtain a first replaced dynamic code block, and the first replaced dynamic code block is subjected to semantic checking processing to obtain a first target abstract syntax tree.
[0085] In the embodiment, if the placeholder type corresponding to the dynamic node is the ${...} placeholder, the target parameter corresponding to the parameter name of the node is obtained from the multiple parameters input by the user to generate a first replaced dynamic code block; the first replaced dynamic code block is subjected to syntax checking to ensure that the replaced code block conforms to the SQL syntax, and the first replaced dynamic code block is subjected to context checking to verify whether the value type of the target parameter matches the value type corresponding to the parameter name, thereby obtaining the first target abstract syntax tree after parameter replacement.
[0086] Or,
[0087] 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, and the parameter value corresponding to the precompiled placeholder in the parameters is bound to obtain a second replaced dynamic code block, and the second replaced dynamic code block is subjected to parameter sterilization processing to obtain a second target abstract syntax tree.
[0088] In the 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 replaced 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 use by the database driver. Then, the target parameter is escaped or filtered, for example, special characters'and " are removed, and it is ensured that the value type of the target parameter is compatible with the database field type, and then the disinfected target parameter binding information is associated with the abstract syntax tree to generate a second target abstract syntax tree.
[0089] In the embodiment, if the placeholder type corresponding to the dynamic node is the ${...} placeholder, the parameter name in the dynamic node is replaced with the target parameter to obtain a first replaced dynamic code block, and the first replaced dynamic code block is subjected to semantic checking to obtain a first target abstract syntax tree. Alternatively, 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, and the parameter value corresponding to the precompiled placeholder in the parameter is bound to obtain a second replaced dynamic code block, and the second replaced dynamic code block is subjected to parameter disinfection to obtain a second target abstract syntax tree, thereby achieving safe and compliant processing of the two types of placeholders and balancing the flexibility and security requirements.
[0090] On the basis of the above embodiments, in the parameter name replacement process, parameter adaptive analysis can be performed to determine the corresponding parameter processing process according to the parameter type corresponding to the parameter name. 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, get(0); for example, if the code block is #{AND pay_time >?payTime}, then the parameter replacement 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, get(0) after List <object>The string processing is converted to be comma-separated; for example, if the placeholder is #{AND in (?tradeId)}, the parameter replacement can be written as AND id IN ('00A','00B','00C','00D') after parameter replacement. When the parameter type is multi-value parameter 2, the parameter name?param can receive Map<String, Object>, and is automatically parsed to adapt to the syntax, get(key), and the like; for example, if the placeholder is #{AND pay_time between?payStartTime and?payEndTime}, the parameter replacement can be written as AND pay_time between '2022-01-01' and '2023-01-01' after parameter replacement.
[0091] Optionally, in the method, if the target parameter corresponding to the parameter name does not exist in the parameter, the corresponding parameter replacement strategy of each dynamic node is performed on each dynamic node respectively according to the respective parameter replacement strategy of each dynamic node, and the target abstract syntax tree is obtained, including:
[0092] 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 the first deleted abstract syntax tree is subjected to syntax tree reconstruction processing to obtain a first target abstract syntax tree.
[0093] In this embodiment, for example, if the current code block is as follows:
[0094] SELECT * FROM trade
[0095] WHERE 1=1
[0096] #{and status=1 AND department IN (?deptList)}
[0097] ${AND pay_time>'?payTime'}
[0098] If the deptList parameter input by the user is missing, the second child node of ${AND pay_time>'?payTime'} corresponding to the payTime parameter is deleted, a first deleted abstract syntax tree is obtained, the semantics of the first deleted abstract syntax tree is checked, for example, the remaining conditions under the WHERE_NODE can be checked first, if only 1=1 is left, 1=1 is a redundant condition, and then the entire WHERE clause is deleted. If there are other valid conditions, for example, #{...} is not deleted, the WHERE keyword is retained, and the structural legality of the SQL code is checked to ensure that the WHERE clause is not empty or only contains invalid conditions.
[0099] Or,
[0100] 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 processed by breakpoint continuation to obtain a second target abstract syntax tree.
[0101] In this embodiment, if the deptList parameter input 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 a second deleted abstract syntax tree; and the second deleted abstract syntax tree is processed by breakpoint continuation, 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 a second target abstract syntax tree.
[0102] In this embodiment, 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 the first deleted abstract syntax tree is processed by syntax tree reconstruction 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 processed by breakpoint continuation to obtain a second target abstract syntax tree, which can dynamically adapt to changes in SQL structure, avoid code redundancy, and improve SQL readability.
[0103] S104: Convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.
[0104] In this embodiment, based on the target abstract syntax tree after parameter replacement and automatic healing processing, a complete target SQL is generated according to the node type by traversing the target abstract syntax tree.
[0105] In the embodiment, the abstract syntax tree corresponding to the SQL template is generated by parsing the obtained SQL template; the abstract syntax tree is a structured representation of the SQL template, and 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 node is a node corresponding to the dynamic code block, and can clearly describe the syntax position of the dynamic code block in the SQL template and the dependency relationship of the parameters. When the parameter replacement is performed, each dynamic code block can be accurately positioned, and the error replacement problem that may occur in the mechanical replacement is avoided, so that the replacement operation is accurately applied to the required position, and the accuracy of the SQL reconstruction is improved; when the abstract syntax tree is traversed, the preset binary decision model is used to determine a suitable parameter replacement strategy for each dynamic node according to the parameters input by the user. The parameter replacement strategy includes a parameter replacement manner and a SQL self-recovery processing manner 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 respectively, and a target abstract syntax tree is obtained to adapt to various business scenarios and parameter inputs; the target abstract syntax tree is converted into a standard SQL statement to generate a target SQL, and thus the errors that may occur in the SQL reconstruction process can be avoided, the completeness of the SQL content is ensured, and the SQL code quality and readability are improved.
[0106] Further, on the basis of the above-mentioned embodiments, in order to further realize and improve the debugging efficiency of the operation and maintenance personnel, reduce the risk of misoperation, and solve the SQL splicing black box problem in the traditional scheme, the method further includes:
[0107] The target abstract syntax tree is converted into a visual structure tree by calling a visual tool.
[0108] The tree structure of the visual structure tree is consistent with the tree structure of the target abstract syntax tree.
[0109] In response to a replacement operation of a parameter name corresponding to a target dynamic code block in the visual structure tree by the user, 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.
[0110] In this embodiment, the visualization tool is called through the API or the UI component, and tree data of the target abstract syntax tree is passed to the visualization tool, where the tree data includes but is not limited to node types, parent-child relationships, attributes, and the like. Each abstract syntax tree node is mapped to a node of the visualization tree and retains the original hierarchy and association. Among them, the node corresponding to the dynamic code block can be identified by using a special style, such as highlighting, icons. The user can edit the parameter name of the target dynamic code block node in the visualization tree by clicking, and the visualization tool returns the editing operation to the back end as an event to synchronize the adaptive parameter update of the target abstract syntax tree, and re-traverse the modified target abstract syntax tree to generate SQL according to the standard process.
[0111] In this embodiment, the target abstract syntax tree is converted into a visualization structure tree by calling the visualization tool. The tree structure of the visualization 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 visualization 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 a target SQL is generated according to the modified target abstract syntax tree, which realizes a closed loop from visualization interaction to abstract syntax tree modification to SQL generation, and improves the ease of use and maintainability of the dynamic SQL template.
[0112] Referring to Figure 2 The figure is a structural schematic diagram of a SQL reconstruction device provided by an embodiment of the present application; as Figure 2 As shown in the solid line box in
[0113] The SQL parsing module 21 is configured to parse the obtained 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 and 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 nodes corresponding to the dynamic code blocks. The policy determination module 22 is configured to determine, 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 a parameter input by a user; the parameter replacement strategy includes a parameter replacement manner and a SQL self-healing processing manner after parameter replacement. The policy execution module 23 is configured to execute the corresponding parameter replacement strategy on each dynamic node according to the parameter replacement strategy corresponding to each dynamic node, to obtain a target abstract syntax tree. The structure conversion module 24 is configured to convert the target abstract syntax tree into a standard SQL statement to generate a target SQL.
[0114] The SQL reconstruction device provided in the embodiments of the present application can execute the technical solutions shown in the method embodiments, and has similar implementation principles and beneficial effects, which will not be repeated here.
[0115] Further, on the basis of the above embodiments, the SQL parsing module 21 is specifically configured to scan the SQL template in a regular matching manner, and identify a plurality of 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 according to the plurality of dynamic code blocks and the respective syntax positions of each code block in the plurality of dynamic code blocks; the placeholder types, internal contents, and parameter name markers in the dynamic code blocks are marked in the nodes corresponding to the dynamic code blocks.
[0116] The SQL reconstruction device provided in the embodiments of the present application can execute the technical solutions shown in the method embodiments, and has similar implementation principles and beneficial effects, which will not be repeated here.
[0117] Further, on the basis of the above embodiments, the strategy determination module 22 is specifically configured to traverse the abstract syntax tree, and identify the parameter name and the placeholder type corresponding to each dynamic node in sequence; for each dynamic node, the following operations are performed: determining the parameter state of the parameter name based on whether the target parameter corresponding to the parameter name exists in the parameters input by the user and whether the target parameter is legal; and determining the parameter replacement strategy corresponding to the dynamic node based on the parameter state of the parameter name and the placeholder type corresponding to the dynamic node, by using a preset binary decision model.
[0118] The SQL reconstruction device provided in the embodiments of the present application can execute the technical solutions shown in the method embodiments, and has similar implementation principles and beneficial effects, which will not be repeated here.
[0119] Further, on the basis of the above embodiments, the binary decision model preset in the strategy determination module 22 is a mapping matrix of the parameter state of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter state 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.
[0120] The SQL reconstruction device provided in the embodiments of the present application can execute the technical solutions shown in the method embodiments, and has similar implementation principles and beneficial effects, which will not be repeated here.
[0121] Furthermore, based on the above embodiments, if there is a target parameter corresponding to the parameter name in the parameters and the target parameter is valid, then the strategy 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 processing 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, convert the parameter name in the dynamic node to a precompiled placeholder, bind the parameter value corresponding to the precompiled placeholder in the parameters, to obtain a second replacement dynamic code block, and perform parameter sanitization processing on the second replacement dynamic code block to obtain a second target abstract syntax tree.
[0122] The SQL reconstruction apparatus provided in this application embodiment 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.
[0123] Furthermore, based on the above embodiments, if the target parameter corresponding to the parameter name does not exist in the parameters, the strategy execution module 23 is specifically used to delete the dynamic node if the placeholder type corresponding to the dynamic node is ${...} placeholder, to obtain a first deleted abstract syntax tree, and to reconstruct 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, the dynamic node is deleted to obtain a second deleted abstract syntax tree, and to continue the second deleted abstract syntax tree from its breakpoints to obtain a second target abstract syntax tree.
[0124] The SQL reconstruction apparatus provided in this application embodiment 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.
[0125] Furthermore, such as Figure 2 As shown in the dashed box, based on the above embodiments, the device 20 may also include a visualization module 25.
[0126] Specifically, the visualization module 25 is used to convert the target abstract syntax tree into a visualization structure tree by calling a visualization tool; the tree structure of the visualization 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 visualization 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 the modified target abstract syntax tree, and the target SQL is generated based on the modified target abstract syntax tree.
[0127] The SQL reconstruction apparatus provided in the embodiments of the present application can execute the technical solutions shown in the method embodiments, and the implementation principles and beneficial effects are similar, which will not be repeated here.
[0128] Referring to Figure 3 The figure is a structural schematic diagram of an electronic device provided in the embodiments of the present application, which includes:
[0129] The memory 11 is configured to store a computer program.
[0130] The processor 12 is configured to execute the computer program to implement the steps of the SQL reconstruction method described in any of the method embodiments.
[0131] In the embodiments, the device can be a vehicle-mounted computer, a PC (Personal Computer), and can also be a terminal device such as a smart phone, a tablet computer, a palm computer, a portable computer, etc.
[0132] The device can include the memory 11, the processor 12 and the bus 13.
[0133] The memory 11 includes at least one type of readable storage medium, such as a flash memory, a hard disk, a multimedia card, a card-type memory (for example, an SD or DX memory, etc.), a magnetic memory, a disk, an optical disk, etc. The memory 11 can be an internal storage unit of the device in some embodiments, for example, a hard disk of the device. The memory 11 can also be an external storage device of the device in other embodiments, for example, a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. Further, the memory 11 can include both the internal storage unit and the external storage device of the device. The memory 11 can be used to store application software and various data installed in the device, such as program codes for executing the SQL reconstruction method, and can also be used to temporarily store data that has been output or will be output. The processor 12 can be a central processing unit (CPU) in some embodiments.
[0134] The processor 12 can be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip in some embodiments, which is configured to run program codes or process data stored in the memory 11, such as program codes for executing the SQL reconstruction method.
[0135] The bus 13 can be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus, etc. The bus can 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 to represent the bus in the figure, but it does not mean that there is only one bus or only one type of bus.
[0136] Further, the device can also include a network interface 14, which can optionally include a wired interface and / or a wireless interface (such as a WI-FI interface, a Bluetooth interface, etc.), and is usually used to establish a communication connection between the device and other electronic devices.
[0137] Optionally, the device can also include a user interface 15, which can include a display, an input unit such as a keyboard, and optionally the user interface 15 can also include a standard wired interface, a wireless interface. Optionally, in some embodiments, the display can be an LED display, a liquid crystal display, a touch liquid crystal display, and an OLED (Organic Light-Emitting Diode) touch, etc. Among them, the display can also be appropriately called a display screen or a display unit, which is used to display information processed in the device and to display a visualized user interface.
[0138] Figure 3 Only the device with components 11-15 is shown, and those skilled in the art can understand that, Figure 3 The structure shown does not constitute a limitation on the device, and can include fewer or more components than shown, or combine certain components, or different component arrangements.
[0139] Based on the same inventive concept, corresponding to the method of any of the above embodiments, the embodiments of the present application also provide a computer readable storage medium, the computer readable storage medium stores computer instructions, the computer instructions are used to make the computer execute the SQL reconstruction method as described in any of the above embodiments.
[0140] The computer readable medium of the embodiments of the present application includes permanent and non-permanent, removable and non-removable media, which can realize information storage 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 technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information accessible by a computing device.
[0141] The storage medium of the above embodiments stores computer instructions for causing the computer to execute the SQL reconstruction method according to any one of the above embodiments, and has the beneficial effects of the corresponding method embodiments, which are not described here.
[0142] It should be noted that each of the embodiments in the specification adopts a progressive description manner, and the same and similar parts between each embodiment can be referred to each other. Each embodiment focuses on the difference from other embodiments. Especially, since the method, device, electronic device and medium are basically similar to the method embodiment, the description is relatively simple, and the relevant part can be referred to the part of the method embodiment. The above described method, device, electronic device and medium are only illustrative, and the units described as separate components can be or can not be physically separated, and the components prompted as units can be or can not be physical units, that is, they can be located in one place, or can be distributed on multiple network units. According to the actual needs, part or all of the modules can be selected to achieve the purpose of the embodiment. Those skilled in the art can understand and implement it without creative labor.
[0143] The above is only one specific embodiment of the present application, but the protection scope of the present application is not limited to this. Any skilled person in the art can easily think of changes or replacements within the technical range disclosed in the present application, which should be covered in the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.< / object> < / object> < / object>
Claims
1. A SQL refactoring method, characterized in that, include: The obtained SQL template is parsed 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 syntactic positions of dynamic code blocks and the dependencies between parameters in the SQL template; The nodes of the abstract syntax tree include static nodes and dynamic nodes; The dynamic node is the node corresponding to the dynamic code block; When traversing the abstract syntax tree, based on the parameters passed in by the user, the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree is determined from the preset binary decision model. The parameter replacement strategy includes operations for replacing or deleting parameters for dynamic nodes, and the corresponding SQL self-healing processing method after the operation is performed; the SQL self-healing processing method is to perform semantic checks, parameter disinfection, syntax tree reconstruction, and breakpoint continuation processing operations on the SQL abstract syntax tree after parameter replacement or deletion; the binary decision model is a mapping matrix between the parameter state of the parameter name and the placeholder type of the dynamic code block. The binary decision value in the binary decision model is used to characterize the parameter replacement strategy corresponding to the current row and the current column. According to the parameter replacement strategy corresponding to each dynamic node, the corresponding parameter replacement strategy is executed for each dynamic node to obtain the target abstract syntax tree; The target abstract syntax tree is converted into standard SQL statements to generate the target SQL.
2. The method according to claim 1, characterized in that, The step of parsing the obtained SQL template to generate the abstract syntax tree corresponding to the SQL template includes: The SQL template is scanned using regular expression matching 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. Based on the plurality of dynamic code blocks and the syntax position corresponding to each of the plurality of 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.
3. The method according to any one of claims 1-2, characterized in that, The step of determining the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree based on user-input parameters during traversal includes: Traverse the abstract syntax tree to identify the parameter name and placeholder type corresponding to each dynamic node in turn; For each dynamic node, perform the following operations: Based on the parameters input by the user, determine whether there is a target parameter corresponding to the parameter name in the parameters and whether the target parameter is valid, and determine the parameter status of the parameter name; 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.
4. The method according to claim 3, characterized in that, The preset binary decision model is a mapping matrix between the parameter state of the parameter name and the placeholder type of the dynamic code block: the row identifier of the mapping matrix represents the parameter state 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, which 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 a target parameter corresponding to the parameter name exists in the parameters and the target parameter is valid, then the step of 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 ${...} placeholder, then the parameter name in the dynamic node is replaced with the target parameter to obtain the first replacement dynamic statement block, and the semantic check processing is performed on the first replacement dynamic code block to obtain the first target abstract syntax tree; or, If the placeholder type corresponding to the dynamic node is #{...} placeholder, then 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 the second replacement dynamic code block. Then, parameter sanitization processing is performed on the second replacement dynamic code block to obtain the 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 the step of 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 ${...} placeholder, then the dynamic node is deleted to obtain the first deleted abstract syntax tree, and the first deleted abstract syntax tree is reconstructed to obtain the first target abstract syntax tree. or, If the placeholder type corresponding to the dynamic node is #{...} placeholder, then the dynamic node is deleted to obtain the second deleted abstract syntax tree, and the second deleted abstract syntax tree is reconnected to obtain the second target abstract syntax tree.
7. The method according to claim 1, characterized in that, The method further includes: The target abstract syntax tree is converted into a visual structure tree by invoking 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 visualization 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 the modified target abstract syntax tree, and the target SQL is generated based on the modified target abstract syntax tree.
8. An SQL refactoring apparatus, characterized in that, include: The SQL parsing module is used to parse the obtained 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 syntactic positions of dynamic code blocks and the dependencies between parameters in the SQL template; The nodes of the abstract syntax tree include static nodes and dynamic nodes; The dynamic node is the node corresponding to the dynamic code block; The strategy determination module is used to determine the parameter replacement strategy corresponding to each dynamic node in the abstract syntax tree based on the parameters input by the user when traversing the abstract syntax tree. The parameter replacement strategy includes operations for replacing or deleting parameters for dynamic nodes, and the corresponding SQL self-healing processing method after the operation is performed; the SQL self-healing processing method is to perform semantic checks, parameter disinfection, syntax tree reconstruction, and breakpoint continuation processing operations on the SQL abstract syntax tree after parameter replacement or deletion; the binary decision model is a mapping matrix between the parameter state of the parameter name and the placeholder type of the dynamic code block. The binary decision value in the binary decision model is used to characterize the parameter replacement strategy corresponding to the current row and the current column. The strategy execution module is used to execute the corresponding parameter replacement strategy for each dynamic node according to the parameter replacement strategy corresponding to each dynamic node, so as to obtain the target abstract syntax tree; The structure conversion module is used to convert the target abstract syntax tree into standard SQL statements and generate the 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-executed instructions; The processor executes computer execution instructions stored in the memory to implement the method as described in 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, when executed by a processor, are used to implement the method as described in any one of claims 1 to 7.
Citation Information
Patent Citations
Data blood relationship analysis method
CN113742368A
Structured query language conversion method, system and equipment and storage medium
CN119025547A