SQL (Structured Query Language) statement analysis and association generation method, device and system and medium
Through improved AST syntax tree rules and progressive storage methods, SQL statements are parsed and associated with storage, probability statistics and feature analysis are performed, and SQL statements that are suitable for different industries are generated, which solves the problem of existing tools relying on manual experience and lack of correlation. Improves the accuracy and effectiveness of SQL statements.
Patent Information
- Application Number
- CN202411440670.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-16
- Publication Date
- 2024-11-15
- Estimated Expiration
- 2044-10-16
AI Technical Summary
Existing SQL automatic generation tools rely on manual experience. The generated SQL statements lack relevance and are difficult to adapt to the characteristics of data in different industries, resulting in reduced accuracy and effectiveness of complex statements.
By collecting the original SQL statements of the target industry, using improved AST syntax tree rules to parse and correlate storage according to progressive storage, probabilistic statistics and correlation calculations are performed, and the generation probability of hierarchy, syntax module and data types are mapped to obtain the probability of generation of hierarchy, syntax module and data types, and the characteristics are performed Analysis and generate targeted SQL statements.
Improve the accuracy and effectiveness of SQL statements, the generated statements are closer to the actual environment, adapt to the data characteristics of different industries, and reduce the dependence on manual experience.
Smart Images

Figure CN118964393A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of database technology, and in particular to a method, device, system and medium for SQL statement parsing and association generation. Background Art
[0002] Currently, when modifying or adding functions to a database, testers need to spend a lot of time designing test cases and then handwriting applicable SQL statements. Although SQL automatic generation tools have been developed to replace testers writing complex SQL by hand, the probability of each statement module involved in the tool is set based on manual experience and is highly dependent on manual experience. If you switch to a new environment or a new tester, it will not be applicable. In addition, most of the current open source SQL syntax automatic generation tools are based on full random generation with set probabilities. In this way, the generated statement blocks lack correlation and are difficult to adapt to the characteristics of data in different industries, which greatly reduces the accuracy and effectiveness of statements with more restrictive conditions, such as complex statements and directional optimization. Summary of the invention
[0003] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a SQL statement parsing and association generation method, device, system and medium that can be applied to financial technology or other related fields, aiming to parse and associate data characteristics of different industries to generate targeted SQL statements as test cases, and to generate test cases that are closer to the actual environment more quickly.
[0004] The technical solution of the present invention is as follows: A method for parsing and generating SQL statements, comprising: Collect the original SQL statements of the target industry; Parse the original SQL statement according to the preset AST syntax tree rules to obtain the syntax module, level, data type and data content in the original SQL statement; The grammar modules, levels, data types and data contents are stored in association with each other in a progressive storage manner to obtain first stored data, second stored data and third stored data; Dividing the first stored data and the second stored data into N groups, respectively performing probability statistics and correlation calculation, and mapping the generation probability of the hierarchy, the generation probability of the grammar module, and the generation probability of the data type according to the correlation calculation results; Performing feature analysis on the third stored data, and obtaining a generation probability of the data content according to the feature analysis result mapping; A corresponding SQL statement is generated according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content.
[0005] In one embodiment, the grammar modules, levels, data types, and data contents are stored in an associated manner in a progressive manner to obtain first stored data, second stored data, and third stored data, including: The name of the parsed grammar module is used as the key and the level is used as the value to store data, thereby obtaining the first storage data; The names of the grammar modules in each level obtained by parsing are used as keys, and the data types are used as values to store data, thereby obtaining second storage data; The data type contained in the grammar modules at each level obtained by parsing is used as the key, and the data content is used as the value for data storage to obtain the third storage data.
[0006] In one embodiment, dividing the first stored data and the second stored data into N groups and performing probability statistics and correlation calculation respectively includes: Dividing the first stored data and the second stored data into N groups, and then obtaining statistics of the probabilities of each level of the N groups, the probabilities of each grammatical module in each level, and the probabilities of the data types included in each grammatical module in each level; Calculate the correlation coefficients between the grammatical modules in each level in all groups according to the probability of each grammatical module in each level of the N groups, and obtain a first correlation coefficient matrix between the grammatical modules in each level; The correlation coefficients between the grammatical modules and the data types in each level in all groups are calculated according to the probability of the data types contained in each grammatical module in each level of the N groups, and a second correlation coefficient matrix between the grammatical modules and the data types in each level is obtained.
[0007] In one embodiment, mapping the generation probability of the hierarchy, the generation probability of the grammar module, and the generation probability of the data type according to the correlation calculation result includes: Map the probabilities of each level of N groups according to the preset statistical indicators to obtain the generation probability of the level; Determine a set of first reference probabilities and corresponding first probability boundary values in the probabilities of each grammar module in each level of the N groups, and determine a set of second reference probabilities and corresponding second probability boundary values in the probabilities of the data types included in each grammar module in each level of the N groups; Calculating the generation probability of grammar modules at each level according to the first baseline probability, the first correlation coefficient matrix and the first probability boundary value; The generation probability of the data type included in the grammar module is calculated according to the second reference probability, the second correlation coefficient matrix and the second probability boundary value.
[0008] In one embodiment, the data types include integer type, character type, date and time type, and floating point type, and the feature analysis of the third stored data specifically includes: For the integer data in the third stored data, analyzing and obtaining the numerical range and numerical characteristic distribution of the integers; For the character data in the third stored data, analyzing and obtaining the characteristic distribution of the character length and the ASCII code; For the date and time data in the third stored data, segment them according to the standard date and time format and analyze them to obtain the value range and value feature distribution; For the floating-point data in the third stored data, the accuracy is identified and then analyzed to obtain the value range and value feature distribution.
[0009] In one embodiment, the mapping of the data content generation probability according to the feature analysis result includes: When the data type is integer, date and time, or floating point, the probability of randomly selecting a value within the range using mean distribution is calculated based on the maximum and minimum values of the value range, or the probability of each value is statistically calculated based on the value feature distribution; When the data type is character type, the maximum and minimum values of the string length are used as the selection range, the character length is randomly selected using the mean distribution, and the statistical ASCII feature distribution is used as the generation probability of each character.
[0010] In one embodiment, the generating of the corresponding SQL statement according to the generation probability of the level, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content specifically includes: The generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content are used to replace the content at the corresponding position of the original SQL statement, and a corresponding SQL statement is generated in association.
[0011] A SQL statement parsing and association generating device, comprising: The collection module is used to collect the original SQL statements of the target industry; A parsing module, used to parse the original SQL statement according to a preset AST syntax tree rule to obtain the syntax module, level, data type and data content in the original SQL statement; A progressive storage module, used to associate and store the grammar modules, levels, data types and data contents in a progressive storage manner to obtain first stored data, second stored data and third stored data; A correlation calculation module, used for dividing the first stored data and the second stored data into N groups, and performing probability statistics and correlation calculation respectively, and mapping the generation probability of the hierarchy, the generation probability of the grammar module and the generation probability of the data type according to the correlation calculation result; A feature analysis module, used to perform feature analysis on the third stored data, and obtain a generation probability of the data content according to the feature analysis result mapping; The associated generation module is used to associate and generate corresponding SQL statements according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content.
[0012] A SQL statement parsing and association generation system, the system comprising at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the above-mentioned SQL statement parsing and association generation method.
[0013] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned SQL statement parsing and association generation method.
[0014] Beneficial effects: The present invention discloses a method, device, system and medium for parsing and generating associations of SQL statements. Compared with the prior art, the embodiments of the present invention parse and calculate the correlations and data feature distributions between different levels of content in the original SQL statement, map them to corresponding probabilities, and then generate targeted SQL statements as test cases, thereby improving the effectiveness of the test. BRIEF DESCRIPTION OF THE DRAWINGS
[0015] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which: Figure 1 A flow chart of a method for parsing and generating associations of SQL statements in an embodiment of the present invention; Figure 2 A flowchart of a method for parsing and generating associations of SQL statements in an application embodiment provided by the present invention; Figure 3 A schematic diagram of the functional modules of a device for parsing and generating associations of SQL statements in an embodiment of the present invention; Figure 4 A schematic diagram of the hardware structure of a SQL statement parsing and association generation system in an embodiment of the present invention. DETAILED DESCRIPTION
[0016] In order to make the purpose, technical solution and effect of the present invention clearer and more specific, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below in conjunction with the accompanying drawings.
[0017] Currently, when modifying or adding functions to a database, testers need to spend a lot of time designing test cases and then handwriting applicable SQL statements. Although SQL automatic generation tools have been developed to replace testers writing complex SQL by hand, the probability of each statement module involved in the tool is set based on manual experience and is highly dependent on manual experience. If you switch to a new environment or a new tester, it will not be applicable. In addition, most of the current open source SQL syntax automatic generation tools are based on full random generation with set probabilities. In this way, the generated statement blocks lack correlation and are difficult to adapt to the characteristics of data in different industries, which greatly reduces the accuracy and effectiveness of statements with more restrictive conditions, such as complex statements and directional optimization.
[0018] In order to solve the above problems, the present invention proposes a method for parsing and generating SQL statements. The method in this embodiment supports running directly in multiple operating systems that support glibc, such as Linux and Windows, and also supports generating .so or .dll dynamic libraries to be called by other python and C++ programs. Secondly, there must be a database instance developed based on MySQL that is running normally, and it must be ensured that the environment where MySQL is located can communicate normally with the operating environment of the present invention. Figure 1 As shown, the SQL statement parsing and association generation method provided in this embodiment includes the following steps: S101. Collect original SQL statements of the target industry.
[0019] In this embodiment, since different industries have different database operation requirements, for example, the financial industry usually involves transaction processing and customer information management, the healthcare industry usually involves electronic health record management and appointment scheduling, the retail industry usually involves inventory management and sales analysis, and the manufacturing industry usually involves production scheduling, supply chain management and other database operation requirements, the SQL statements in the production environments of different industries have different characteristics. Therefore, when generating test case SQL statements for the target industry, the original SQL statements of the target industry are first collected, for example, the original SQL statements are extracted from the test cases passed by the target industry, and the SQL statistics used in the industry applications are finally stored as excel or csv files. At the same time, the data of the on-site production environment of the industry application database can also be copied to the test database through the collection and playback tool, etc., so as to collect and obtain the original SQL statement samples that cover the key operations of the target industry and can reflect the industry-specific database operation mode, so as to provide a reliable sample basis for accurately generating SQL statements close to the actual environment.
[0020] S102: Parse the original SQL statement according to preset AST syntax tree rules to obtain syntax modules, levels, data types and data contents in the original SQL statement.
[0021] In this embodiment, the abstract syntax tree (AST) parsing technology is used to parse the original SQL statement according to preset AST syntax tree rules. AST is a tree representation of the abstract syntax structure of the source code, and is an intermediate representation used by a compiler or interpreter when processing the source code. Each node in the AST represents a syntax structure in the source code, such as a variable declaration, expression, function call, control structure, etc. The root node of the tree usually represents the entire source code file, and the child nodes represent specific syntax elements and their relationships.
[0022] The preset AST syntax tree rules are improvements on the conventional AST syntax tree rules to perform syntax module parsing, hierarchical parsing, data type parsing and data content parsing on the original SQL statements. Specifically, the collected original SQL statements are reverse parsed into various syntax modules through the improved AST syntax tree rules, and marked according to DDL statements, DML statements and DQL statements; hierarchical parsing requires adding the identification of sub-modules such as function nesting, subquery, join on nesting, etc. to the conventional AST syntax tree rules. At the same time, the remaining data parts after parsing the syntax module, including function parameters, join on conditions, where conditions, etc., are divided into two categories of fields and constants. The fields obtain the data type and corresponding data content through further table structure information query. The data content is classified according to the data type (custom categories include integer, character, date and time, floating point), and constants are also identified and classified into custom categories, keywords or unknown types according to the characteristics. Through reverse parsing processing, the structured representation of each original SQL statement is obtained, and the content of different hierarchical structures is obtained, so as to better analyze the structural characteristics of SQL statements in the target industry and generate more targeted SQL statements for testing.
[0023] S103, the grammar modules, levels, data types and data contents are stored in association with each other in a progressive storage manner to obtain first stored data, second stored data and third stored data.
[0024] In this embodiment, the grammatical modules, levels, data types and data contents obtained through parsing are associated and stored in three different and progressive storage methods to obtain first storage data, second storage data and third storage data, thereby obtaining a hierarchical data storage structure. Such a hierarchical data storage method can reflect the various components of the original SQL statement and their mutual relationships, which is helpful for subsequent data analysis and probability calculation, so as to obtain the probability distribution and correlation of the various components of the SQL statement, provide associated reference information for the probability of each part when automatically generating SQL statements, retain randomness while making the statement closer to the actual business scenario.
[0025] In one embodiment, the grammar modules, levels, data types, and data contents are stored in an associated manner in a progressive manner to obtain first stored data, second stored data, and third stored data, including: The name of the parsed grammar module is used as the key and the level is used as the value to store data, thereby obtaining the first storage data; The names of the grammar modules in each level obtained by parsing are used as keys, and the data types are used as values to store data, thereby obtaining second storage data; The data type contained in the grammar modules at each level obtained by parsing is used as the key, and the data content is used as the value for data storage to obtain the third storage data.
[0026] In this embodiment, the data are stored in association according to three different and progressive storage methods. The first method is to store the name of each parsed grammar module as a key and the level as a value in the data structure to obtain the first storage data, so as to ensure that the level information of each grammar module is correctly recorded and associated; the second method is to store the name of the grammar module in each level as a key and the data type as a value to obtain the second storage data, so as to ensure that the data type information of each module is correctly recorded and associated; the third method is to store the data type contained in the grammar module in each level as a key and the data content as a value to obtain the third storage data, so as to ensure that the data content is correctly associated with the corresponding data type. By performing multi-level structured storage of the various components of the SQL statement (such as grammar module, level, data type and data content), a solid foundation is provided for the subsequent data analysis, statistics and automatic generation of SQL statements, which not only helps to improve the accuracy and efficiency of SQL statement generation, but also can better adapt to the needs of different industries and application scenarios.
[0027] S104, dividing the first stored data and the second stored data into N groups, and performing probability statistics and correlation calculation respectively, and mapping the generation probability of the hierarchy, the generation probability of the grammar module and the generation probability of the data type according to the correlation calculation results.
[0028] In this embodiment, the data obtained by the first storage method and the second storage method are divided into N groups for statistical analysis, that is, the first storage data (grammar module name and level) and the second storage data (grammar module name and data type) are equally divided into N groups, and probability statistical analysis is performed on each group of data, for example, the probability of each grammar module appearing in a specific level and the probability of each data type appearing in a specific grammar module are calculated, and the correlation between different grammar modules and data types is analyzed by statistical methods (such as Pearson correlation coefficient, Spearman rank correlation coefficient, etc.), and further according to the correlation calculation results, the generation probability of the level, the generation probability of the grammar module and the generation probability of the data type are mapped, and by performing probability statistics and correlation calculation on the first storage data and the second storage data by group, the possibility of different levels, grammar modules and data types appearing when generating new SQL statements is predicted, so that SQL statements that are more in line with actual usage patterns can be generated, thereby improving the accuracy and effectiveness of the statements.
[0029] In one embodiment, dividing the first stored data and the second stored data into N groups and performing probability statistics and correlation calculation respectively includes: Dividing the first stored data and the second stored data into N groups, and then obtaining statistics of the probabilities of each level of the N groups, the probabilities of each grammatical module in each level, and the probabilities of the data types included in each grammatical module in each level; Calculate the correlation coefficients between the grammatical modules in each level in all groups according to the probability of each grammatical module in each level of the N groups, and obtain a first correlation coefficient matrix between the grammatical modules in each level; The correlation coefficients between the grammatical modules and the data types in each level in all groups are calculated according to the probability of the data types contained in each grammatical module in each level of the N groups, and a second correlation coefficient matrix between the grammatical modules and the data types in each level is obtained.
[0030] In this embodiment, when performing probability statistics and correlation calculation on the first storage data and the second storage data, the first storage data and the second storage data are first divided into N groups in equal amounts, and statistics are performed on each group of data to obtain the probability of each level, the probability of each grammatical module in each level, and the probability of the data type contained in each grammatical module in each level, respectively. The level probability refers to: collecting T SQL statements from the scene, parsing according to the SQL standardized grammar to obtain each grammatical module and its level. As shown in Table 1, the first row indicates that T1 SQL statements have only one level (meaning that the statements are relatively simple), T2 SQL statements have two levels (slightly complex), and T3 SQL statements have three levels (increasing complexity).
[0031] Table 1 Statistics of the number of layers of T SQL statements collected Total number of SQL layers The number of corresponding SQL statements 1 T1 2 T2 3 T3 The level can identify the complexity of a SQL statement, and Tx / T here can be mapped to the probability of generating a statement complexity, that is, the level probability.
[0032] In addition to directly counting the level of the entire SQL statement, it is also necessary to combine it with specific modules. For example, the select_list module has functions, window functions, subqueries and other members that can be further nested (level + 1), and the from clause has subqueries (level + 1), joinon (level + 1), etc. Combining the level and module member name, for example, the function member func of the select_list module group contains T21 statements with 2 levels of nesting, then the corresponding probability of level (=2) + module name (=select_list_func) is T21 / T, that is, the number of occurrences of the syntax module in each level is counted, and the probability of occurrence of each syntax module in each level is calculated; and the number of occurrences of the data type in each syntax module at each level is also counted, and the probability of occurrence of the data type contained in each syntax module at each level is calculated.
[0033] According to the probability of each grammatical module in each level in N groups of data, the correlation coefficient between the grammatical modules in each level in all groups is calculated to obtain the first correlation coefficient matrix between the grammatical modules in each level, that is, all modules and their corresponding levels are correlated in pairs to obtain the correlation coefficient matrix between the modules at each level. The closer the correlation coefficient is to 1, the more positively correlated it is, the closer it is to -1, the more negatively correlated it is, and the closer it is to 0, the more irrelevant it is. In this way, it can be obtained which module is highly correlated with which module, and whether the probability increases or decreases as the sentence becomes more complex. Distribution characteristics; similarly, according to the probability of the data type contained in each grammatical module in each level in N groups of data, the correlation coefficient between the grammatical module and the data type can be calculated to reflect the correlation between the grammatical module and the data type. Preferably, if the correlation coefficient between the grammatical module and the data type obtained here is 1, it does not participate in the subsequent calculation of the probability mapping based on the correlation calculation result, and is directly mapped to 100% probability to save computing resources and improve generation efficiency.
[0034] This embodiment performs probability statistics and correlation analysis on the two types of storage data based on hierarchical associative storage, so that the relationship between different grammatical modules can be revealed through a first correlation coefficient matrix, which helps to understand which modules often appear together, so that they can be more reasonably combined when generating SQL statements. The relationship between grammatical modules and data types is revealed through a second correlation coefficient matrix, which helps to select appropriate data types when generating SQL statements. It serves as a guide for generating SQL statements, optimizes the generation of SQL statements in an industry data-driven manner, and reduces reliance on manual experience.
[0035] In one embodiment, mapping the generation probability of the hierarchy, the generation probability of the grammar module, and the generation probability of the data type according to the correlation calculation result includes: Map the probabilities of each level of N groups according to the preset statistical indicators to obtain the generation probability of the level; Determine a set of first reference probabilities and corresponding first probability boundary values in the probabilities of each grammar module in each level of the N groups, and determine a set of second reference probabilities and corresponding second probability boundary values in the probabilities of the data types included in each grammar module in each level of the N groups; Calculating the generation probability of grammar modules at each level according to the first baseline probability, the first correlation coefficient matrix and the first probability boundary value; The generation probability of the data type included in the grammar module is calculated according to the second reference probability, the second correlation coefficient matrix and the second probability boundary value.
[0036] In this embodiment, when mapping to obtain the generation probabilities of each item, the generation probability of the level can be analyzed by preset statistical indicators (such as mean, median, mode, etc.) for the probability of each level of N groups, and these statistical indicators are mapped to the generation probability of the level. The generation probability of the grammar module and the data type is combined with the corresponding correlation coefficient matrix to provide the correlation information between the variables, wherein one group is selected as the first baseline probability from the probabilities of each grammar module in each level of N groups, and the corresponding first probability boundary value is determined. The generation probability of each grammar module in each layer can be obtained by the formula Calculated, where P is the generation probability of the grammar module, P1 is the first benchmark probability, K is the correlation coefficient, Pm is the first probability boundary value, and n is the number of layers. Similarly, among the probabilities of the data types contained in each grammar module in each layer of N groups, one group is selected as the second benchmark probability, and the corresponding second probability boundary value is determined, and then the generation probability of the data type contained in the grammar module is calculated. By using the benchmark probability and correlation coefficient matrix, the generation probability of the grammar module and data type at each layer can be calculated more accurately, so that the generated SQL statement is more in line with the actual database operation mode.
[0037] S105: Perform feature analysis on the third stored data, and obtain generation probability of data content according to feature analysis result mapping.
[0038] In this embodiment, feature analysis is performed on the third storage data obtained by the third storage method, that is, the third storage data specifically stores the data type contained in the grammar module in each level as the key and the data content as the value. Therefore, during feature analysis, feature analysis can be performed on the data content of the same major category to obtain the feature distribution of the data content under each data type, so as to predict the generation probability of the data content based on the feature analysis results, thereby improving the accuracy of SQL statement generation.
[0039] Specifically, the data types include integer type, character type, date and time type, and floating point type, and the feature analysis of the third stored data specifically includes: For the integer data in the third stored data, analyzing and obtaining the numerical range and numerical characteristic distribution of the integers; For the character data in the third stored data, analyzing and obtaining the characteristic distribution of the character length and the ASCII code; For the date and time data in the third stored data, segment them according to the standard date and time format and analyze them to obtain the value range and value feature distribution; For the floating-point data in the third stored data, the accuracy is identified and then analyzed to obtain the value range and value feature distribution.
[0040] In this embodiment, corresponding feature analysis is performed based on the data content of different data types in the third stored data, wherein the integer data is analyzed and stored according to the numerical range and numerical feature distribution of the integer, that is, the minimum and maximum values of the integer data are calculated, the numerical range is determined, and statistical methods (such as calculating the mean, median, standard deviation, etc.) are used to analyze the distribution characteristics of the integer data; the character type is analyzed and stored according to the character length and ASCII code, that is, the length of each character data item is measured, and the ASCII code value distribution of each character in the character data is analyzed; the date and time type is segmented according to the standard date and time format and then the numerical storage range and feature distribution are analyzed, that is, the date and time data is segmented according to the standard format (year, month, day, hour, minute, second), and the segmented values are analyzed for range analysis and feature distribution analysis; the floating point type is analyzed according to the integer type after the precision is identified, that is, the precision of the floating point data (the number of digits after the decimal point) is determined, and the numerical range and distribution characteristics of the floating point data are analyzed, and the characteristics obtained by the analysis are used as the basis for generating subsequent data content, thereby improving the accuracy of SQL statement generation.
[0041] In one embodiment, the mapping of the data content generation probability according to the feature analysis result includes: When the data type is integer, date and time, or floating point, the probability of randomly selecting a value within the range using mean distribution is calculated based on the maximum and minimum values of the value range, or the probability of each value is statistically calculated based on the value feature distribution; When the data type is character type, the maximum and minimum values of the string length are used as the selection range, the character length is randomly selected using the mean distribution, and the statistical ASCII feature distribution is used as the generation probability of each character.
[0042] In this embodiment, when mapping the generation probability of data content, four data types are divided into two for mapping, one is a numerical type, including integer, processed date and time type and processed floating point type, and the other is a character type. The generation probability of the numerical type can be realized in two ways. One is to obtain the maximum value max and the minimum value min of the data content according to the numerical range (applicable to the case of large data volume and small amount of repeated data), and then take the mean distribution to randomly select the value in the range of [min, max], and calculate the probability of randomly selecting the value in the range as the generation probability of the data content; the second is to statistically calculate the numerical feature distribution (applicable to the case of small data volume and large amount of repeated data), and directly use the probability of each existing numerical value as the generation probability of the data content; since the date type can be regarded as an integer separated by special symbols, and the floating point type can also be regarded as an integer separated by the special symbol ".", the generation probability can also be obtained by the above two methods. The generation probability of character type requires the collection of character length and application of ASCII code. The maximum and minimum values of the string length are used as the selection range, and the character length is randomly selected using mean distribution. The statistical ASCII feature distribution is used as the generation probability of each character, that is, the specific character uses a completely random ASCII code without further data analysis and construction. By calculating the generation probability distribution of the data content, it is possible to more accurately simulate and generate values or lengths that meet the actual data characteristics, ensuring that the generated SQL statements can cover various possible data situations and improve the accuracy and reliability of the test.
[0043] S106 , generating a corresponding SQL statement according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type, and the generation probability of the data content.
[0044] In this embodiment, based on the generation probability of the hierarchy obtained by mapping, the generation probability of the grammar module, the generation probability of the data type, and the generation probability of the data content, the SQL grammar module can be randomly generated according to the SQL execution order and the data type of the corresponding position in the SQL statement can be generated, and a certain data content can be selected at the corresponding data type position, so as to obtain a complete SQL statement generated by probability association. By parsing the previous target industry SQL statements to obtain the characteristic distribution of industry data and the correlation of each part in the statement, and then automatically generating new SQL statements after obtaining the corresponding generation probability, it is possible to automatically learn and generate new SQL statements from the actually used SQL statements, and the generated SQL statements are not only statistically reasonable, but also more in line with the actual application scenarios of the target industry, more accurately reflect the operation mode of the target industry, and improve the accuracy and effectiveness of the SQL statements.
[0045] In specific implementation, when a new SQL statement is randomly generated according to the generation probability, the SQL statement can be randomly generated through the existing open source SQL automatic generation tool combined with the generation probability, or because there are already a large number of parsed SQL modules in the parsing stage, the original SQL statement can be replaced at the corresponding position based on the parsing result of the original SQL statement in combination with the calculated generation probability of the level, generation probability of the grammar module, generation probability of the data type and generation probability of the data content, and the levels, grammar modules, data types and data contents in the original SQL statement can be replaced in turn. After the replacement is completed, the generated SQL statement can be checked through the SQL verification tool to ensure that the replaced SQL statement is valid in structure and semantics, and the SQL statement is automatically generated, thereby improving the efficiency of development and testing and reducing the workload of manually writing SQL statements.
[0046] It should be noted that there is not necessarily a certain order between the above-mentioned steps. A person skilled in the art can understand, based on the description of the embodiments of the present invention, that in different embodiments, the above-mentioned steps may have different execution orders, that is, they may be executed in parallel, may be executed interchangeably, and so on.
[0047] To better understand the implementation process of the SQL statement parsing and association generation method provided in the embodiment of the present invention, the following is combined with Figure 2 , giving a specific application example, the implementation process of the SQL statement parsing and association generation method is introduced: like Figure 2 As shown, the SQL statement parsing and association generation method provided by the embodiment of the present invention includes the following steps: Step 1: The form of industry SQL collection includes the extraction of past test cases, SQL statistics used in the application, etc., which are finally stored as Excel or CSV files. At the same time, the data of the on-site production environment of the industry application database needs to be copied to the test database through the collection and playback tool.
[0048] Step 2: Perform multi-level parsing of SQL through improved AST syntax tree rules, including parsing syntax modules, parsing layers, data types and data content. The parsing syntax module is to reversely parse the SQL statements collected in step 1 into various syntax modules, and mark them according to DDL statements, DML statements, and DQL statements. Hierarchical parsing requires adding the recognition of sub-modules such as function nesting, subquery, and join on nesting to the conventional AST syntax tree rules. At the same time, the remaining data after parsing the syntax module, including function parameters, join on conditions, where conditions, etc., are divided into two categories of fields and constants. The analysis of data types and data content is to obtain the data type and corresponding data content through further table structure information query of the field. The data content is classified according to the data type (custom category - integer, character type, date and time type, floating point type), and constants are also identified and classified into custom categories, keywords or unknown types according to their characteristics. Finally, it is stored in three ways. The first one uses the syntax module name as the key and the level as the value. The second one uses the syntax module plus the level as the key and the data category as the value. The third one uses the syntax module plus the level plus the data category as the key and the data content or constant queried from the field as the value for classified storage.
[0049] Step 3: Divide the data of step 2 about the first storage method and the second storage method into N groups for statistical analysis, perform probability statistics and calculate the correlation coefficient matrix by group, first calculate the probability of each level and each module name in each group, then calculate the correlation coefficient between the module names in each level in all groups, and use the same method to obtain the correlation coefficient matrix of each level plus module name and data type. Then, perform feature analysis on the data content of the same category in the data stored in the third storage method in step 2, that is, perform feature analysis on the data content according to the data type, where integer data is analyzed and stored according to the integer value range and numerical feature distribution, character type is analyzed and stored according to character length and ascii code, date and time type is divided according to the standard date and time format and then analyzed for numerical storage range and feature distribution, floating point type is analyzed according to integer type after accuracy recognition, and the features obtained from this part of the analysis will be used as the basis for generating constant values.
[0050] Step 4: Normalize the correlation coefficient matrix of the module name, level and data category obtained in the above step 3, map it into the probability of the grammatical module, level and data type, and then obtain the generation probability of each level and the generation probability of the module name under a certain level and the generation probability of the data category contained in the module name, and then randomly generate SQL statements and the data types of the required parameters according to the current generation probability. Next, map the feature analysis results of the data content to obtain the generation probability of the data content, and further randomly select the corresponding table field or constant according to the data type. If it is a constant, generate the corresponding type of constant or random boundary value according to the characteristics of the data content analyzed in step 3, so as to randomly generate SQL statements according to the probability, so that a complete SQL statement with industry characteristics can be generated.
[0051] In this embodiment, by parsing previous industry SQL statements to obtain the characteristic distribution of industry data and mapping it to generation probability, it is possible to generate SQL statements that are more industry-specific, more complex in hierarchy, and with broader data as test cases, thereby reducing test fault leakage.
[0052] Another embodiment of the present invention provides a SQL statement parsing and association generating device, such as Figure 3 As shown, the device 1 comprises: Collection module 11, used for collecting original SQL statements of target industries; The parsing module 12 is used to parse the original SQL statement according to the preset AST syntax tree rules to obtain the syntax module, level, data type and data content in the original SQL statement; A progressive storage module 13, used to associate and store the grammar modules, levels, data types and data contents in a progressive storage manner to obtain first stored data, second stored data and third stored data; A correlation calculation module 14 is used to divide the first stored data and the second stored data into N groups and perform probability statistics and correlation calculation respectively, and map the generation probability of the hierarchy, the generation probability of the grammar module and the generation probability of the data type according to the correlation calculation result; A feature analysis module 15, configured to perform feature analysis on the third stored data, and obtain a generation probability of the data content according to the feature analysis result mapping; The association generating module 16 is used to associate and generate corresponding SQL statements according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content.
[0053] The module referred to in the present invention refers to a series of computer program instruction segments that can complete specific functions, which is more suitable for describing the execution process of SQL statement parsing and association generation than a program. For the specific implementation of each module, please refer to the corresponding method embodiment above, which will not be repeated here.
[0054] Another embodiment of the present invention provides a SQL statement parsing and association generating system, such as Figure 4 As shown, the system 10 includes: One or more processors 110 and memory 120, Figure 4 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 4 The example of connecting through bus is taken in the following.
[0055] The processor 110 is used to complete various control logics of the system 10, and it can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.
[0056] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the SQL statement parsing and association generation method in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, implements the SQL statement parsing and association generation method in the above method embodiment.
[0057] The memory 120 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required by at least one function; the data storage area may store data created according to the use of the system 10, etc. In addition, the memory 120 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may optionally include a memory remotely arranged relative to the processor 110, and these remote memories may be connected to the system 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.
[0058] One or more units are stored in the memory 120, and when executed by one or more processors 110, the above-described Figure 1 The method comprises steps S101 to S106.
[0059] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S101 to S106.
[0060] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.
[0061] In summary, the method, device, system and medium for parsing and associating SQL statements disclosed in the present invention collects the original SQL statements of the target industry; parses the original SQL statements according to the preset AST syntax tree rules to obtain the syntax modules, levels, data types and data contents in the original SQL statements; associates and stores the syntax modules, levels, data types and data contents in a progressive storage manner to obtain first storage data, second storage data and third storage data; divides the first storage data and the second storage data into N groups and then respectively performs probability statistics and correlation calculations, and maps the generation probability of the level, the generation probability of the syntax module and the generation probability of the data type according to the correlation calculation results; performs feature analysis on the third storage data, and maps the generation probability of the data content according to the feature analysis results; and generates corresponding SQL statements according to the generation probability of the level, the generation probability of the syntax module, the generation probability of the data type and the generation probability of the data content. By parsing and calculating the correlation between the contents of different levels and the data feature distribution in the original SQL statements, and mapping to the corresponding probabilities, a targeted SQL statement is generated as a test case to improve the test effectiveness.
[0062] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.
[0063] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.
Claims
1. A method for parsing and generating associations of SQL statements, It is characterized in that include: Collect the original SQL statements of the target industry; Parse the original SQL statement according to the preset AST syntax tree rules to obtain the syntax module, level, data type and data content in the original SQL statement; The grammar modules, levels, data types and data contents are stored in association with each other in a progressive storage manner to obtain first stored data, second stored data and third stored data; Dividing the first stored data and the second stored data into N groups, respectively performing probability statistics and correlation calculation, and mapping the generation probability of the hierarchy, the generation probability of the grammar module, and the generation probability of the data type according to the correlation calculation results; Performing feature analysis on the third stored data, and obtaining a generation probability of the data content according to the feature analysis result mapping; A corresponding SQL statement is generated according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content.
2. The SQL statement parsing and association generating method according to claim 1, It is characterized in that The step of associating and storing the grammar modules, levels, data types, and data contents in a progressive storage manner to obtain first stored data, second stored data, and third stored data includes: The name of the parsed grammar module is used as the key and the level is used as the value to store data, thereby obtaining the first storage data; The names of the grammar modules in each level obtained by parsing are used as keys, and the data types are used as values to store data, thereby obtaining second storage data; The data type contained in the grammar modules at each level obtained by parsing is used as the key, and the data content is used as the value for data storage to obtain the third storage data.
3. The SQL statement parsing and association generating method according to claim 2, It is characterized in that The dividing the first stored data and the second stored data into N groups and performing probability statistics and correlation calculation respectively includes: Dividing the first stored data and the second stored data into N groups, and then obtaining statistics of the probabilities of each level of the N groups, the probabilities of each grammatical module in each level, and the probabilities of the data types included in each grammatical module in each level; Calculate the correlation coefficients between the grammatical modules in each level in all groups according to the probability of each grammatical module in each level of the N groups, and obtain a first correlation coefficient matrix between the grammatical modules in each level; The correlation coefficients between the grammatical modules and the data types in each level in all groups are calculated according to the probability of the data types contained in each grammatical module in each level of the N groups, and a second correlation coefficient matrix between the grammatical modules and the data types in each level is obtained.
4. The SQL statement parsing and association generating method according to claim 3, It is characterized in that The generation probability of the hierarchy, the generation probability of the grammar module and the generation probability of the data type are obtained by mapping the correlation calculation result, including: Map the probabilities of each level of N groups according to the preset statistical indicators to obtain the generation probability of the level; Determine a set of first reference probabilities and corresponding first probability boundary values in the probabilities of each grammar module in each level of the N groups, and determine a set of second reference probabilities and corresponding second probability boundary values in the probabilities of the data types included in each grammar module in each level of the N groups; Calculating the generation probability of grammar modules at each level according to the first baseline probability, the first correlation coefficient matrix and the first probability boundary value; The generation probability of the data type included in the grammar module is calculated according to the second reference probability, the second correlation coefficient matrix and the second probability boundary value.
5. The SQL statement parsing and association generating method according to claim 2, It is characterized in that The data types include integer, character, date and time, and floating point types, and the feature analysis of the third stored data specifically includes: For the integer data in the third stored data, analyzing and obtaining the numerical range and numerical characteristic distribution of the integers; For the character data in the third stored data, analyzing and obtaining the characteristic distribution of the character length and the ASCII code; For the date and time data in the third stored data, segment them according to the standard date and time format and analyze them to obtain the value range and value feature distribution; For the floating-point data in the third stored data, the accuracy is identified and then analyzed to obtain the value range and value feature distribution.
6. The SQL statement parsing and association generating method according to claim 5, It is characterized in that The generation probability of the data content is obtained according to the feature analysis result mapping, including: When the data type is integer, date and time, or floating point, the probability of randomly selecting a value within the range using mean distribution is calculated based on the maximum and minimum values of the value range, or the probability of each value is statistically calculated based on the value feature distribution; When the data type is character type, the maximum and minimum values of the string length are used as the selection range, the character length is randomly selected using the mean distribution, and the statistical ASCII feature distribution is used as the generation probability of each character.
7. The SQL statement parsing and association generating method according to any one of claims 1 to 6, It is characterized in that The generating of the corresponding SQL statement according to the generation probability of the level, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content specifically includes: The generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content are used to replace the content at the corresponding position of the original SQL statement, and a corresponding SQL statement is generated in association.
8. A SQL statement parsing and association generating device, It is characterized in that include: The collection module is used to collect the original SQL statements of the target industry; A parsing module, used to parse the original SQL statement according to a preset AST syntax tree rule to obtain the syntax module, level, data type and data content in the original SQL statement; A progressive storage module, used to associate and store the grammar modules, levels, data types and data contents in a progressive storage manner to obtain first stored data, second stored data and third stored data; A correlation calculation module, used for dividing the first stored data and the second stored data into N groups, and performing probability statistics and correlation calculation respectively, and mapping the generation probability of the hierarchy, the generation probability of the grammar module and the generation probability of the data type according to the correlation calculation result; A feature analysis module, used to perform feature analysis on the third stored data, and obtain a generation probability of the data content according to the feature analysis result mapping; The association generation module is used to associate and generate corresponding SQL statements according to the generation probability of the hierarchy, the generation probability of the grammar module, the generation probability of the data type and the generation probability of the data content.
9. A SQL statement parsing and association generation system, It is characterized in that The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the SQL statement parsing and association generation method according to any one of claims 1 to 7.
10. A non-volatile computer-readable storage medium, It is characterized in that The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the SQL statement parsing and association generation method according to any one of claims 1 to 7.
Citation Information
Patent Citations
Processing method of statistical information of database operation statements, storage medium and equipment
CN115858627A
SQL optimization method and device, electronic equipment and storage medium
CN116932583A
SQL statement generation method and device, computer equipment and storage medium
CN118568123A
Generic Indexing for Efficiently Supporting Ad-Hoc Query Over Hierarchically Marked-Up Data
US20150134670A1
Cited By
SQL statement parsing and associated generation method, apparatus and system, and medium
WO2026081758A1
