SQL statement parsing and associated generation method, apparatus and system, and medium

Abstract

An SQL statement parsing and associated generation method, apparatus and system, and a medium. The method comprises: collecting original SQL statements of a target industry (S101); performing parsing on the original SQL statements on the basis of preset AST rules, so as to obtain syntax modules, hierarchies, data types and data content in the original SQL statements (S102); performing associated storage on the syntax modules, the hierarchies, the data types and the data content in a progressive storage manner, so as to obtain first storage data, second storage data and third storage data (S103); after dividing the first storage data and the second storage data into N groups, respectively performing probability statistics and correlation calculation, and performing mapping to obtain a generation probability of the hierarchies, the syntax modules and the data types (S104); performing feature analysis on the third storage data, and then performing mapping to obtain a generation probability of the data content (S105); and on the basis of the generation probabilities, associatively generating corresponding SQL statements (S106).

Description

SQL statement parsing and relation generation methods, devices, systems, and media

[0001] This application claims priority to Chinese Patent Application No. 202411440670.5, filed with the Chinese Patent Office on October 16, 2024, the entire contents of which are incorporated herein by reference. Technical Field

[0002] This application relates to the field of database technology, and for example to a method, apparatus, system and medium for parsing and generating SQL statements. Background Technology

[0003] Currently, when modifying or adding features to a database, testers spend a significant amount of time designing test cases and then manually writing appropriate Structured Query Language (SQL) statements. While automated SQL generation tools have been developed to replace testers in manually writing complex SQL, the probability of each statement module appearing in these tools is determined by human experience, making them highly dependent on human judgment. This renders them unsuitable for new environments or when new testers are hired. Furthermore, many open-source SQL syntax generation tools currently rely on fully random generation based on predetermined probabilities. This results in a lack of correlation between generated statement blocks, making it difficult to adapt to the characteristics of data across different industries. This significantly reduces the accuracy and effectiveness of statements with many constraints, such as complex statements or targeted optimizations. Summary of the Invention

[0004] This application provides a method, apparatus, system, and medium for parsing and generating SQL statements that can be applied to financial technology or other related fields. It aims to parse and generate targeted SQL statements as test cases based on the data characteristics of different industries, and to generate test cases that are closer to the actual environment more quickly.

[0005] The technical solution of this application is as follows:

[0006] A method for parsing and generating SQL statements, including:

[0007] Collect raw SQL statements from the target industry;

[0008] The original SQL statement is parsed according to the preset AST rules to obtain the syntax modules, levels, data types and data content in the original SQL statement;

[0009] The syntax modules, levels, data types, and data content are associated and stored in a progressive storage manner to obtain first stored data, second stored data, and third stored data;

[0010] After dividing the first and second stored data into N groups, probability statistics and correlation calculations are performed on each group. Based on the correlation calculation results, the generation probability of the level, the generation probability of the syntax module, and the generation probability of the data type are mapped to obtain the generation probability.

[0011] The third stored data is subjected to feature analysis, and the generation probability of the data content is obtained by mapping the feature analysis results.

[0012] The corresponding SQL statement is generated based on 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.

[0013] An SQL statement parsing and relation generation device, comprising:

[0014] The data acquisition module is configured to collect raw SQL statements from the target industry.

[0015] The parsing module is configured to parse the original SQL statement according to a preset AST rule to obtain the syntax module, level, data type and data content in the original SQL statement;

[0016] The progressive storage module is configured to associate and store the syntax module, hierarchy, data type and data content in a progressive storage manner to obtain first stored data, second stored data and third stored data;

[0017] The correlation calculation module is configured to divide the first stored data and the second stored data into N groups and then perform probability statistics and correlation calculations on each group. Based on the correlation calculation results, the generation probability of the level, the generation probability of the syntax module, and the generation probability of the data type are mapped to the data.

[0018] The feature analysis module is configured to perform feature analysis on the third stored data and map the generation probability of the data content based on the feature analysis results.

[0019] The associated generation module is configured to generate corresponding SQL statements based on 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.

[0020] A system for parsing and generating SQL statements, the system comprising at least one processor; and,

[0021] A memory communicatively connected to the at least one processor; wherein,

[0022] The memory stores instructions that can be executed by the at least one processor. When the instructions are executed by the at least one processor, the at least one processor is able to execute the above-described SQL statement parsing and association generation method.

[0023] A non-volatile computer-readable storage medium stores computer-executable instructions that, when executed by one or more processors, enable the one or more processors to perform the above-described SQL statement parsing and association generation method. Attached Figure Description

[0024] Figure 1 is a flowchart of an SQL statement parsing and association generation method provided in this application;

[0025] Figure 2 is a flowchart of an SQL statement parsing and association generation method provided in this application;

[0026] Figure 3 is a schematic diagram of the functional modules of an SQL statement parsing and association generation device provided in this application;

[0027] Figure 4 is a schematic diagram of the hardware structure of an SQL statement parsing and association generation system provided in this application. Detailed Implementation

[0028] To address the problems existing in related technologies, this application proposes a method for parsing and generating SQL statements. This method supports direct execution on various operating systems that support glibc, such as Linux and Windows, and also supports generating .so or .dll dynamic libraries that can be called by other Python and C++ programs. Secondly, it requires a normally functioning database instance based on MySQL, and ensures that the MySQL environment can communicate normally with the runtime environment of this application. As shown in Figure 1, the SQL statement parsing and generating method provided in this embodiment includes the following steps:

[0029] S101. Collect the original SQL statements of the target industry.

[0030] In this embodiment, different industries have different database operation requirements. For example, the financial industry typically involves transaction processing and customer information management, the healthcare industry typically involves electronic health record management and appointment scheduling, the retail industry typically involves inventory management and sales analysis, and the manufacturing industry typically involves production scheduling and supply chain management. These database operation requirements result in different characteristics of SQL statements in the production environments of different industries. Therefore, when generating test case SQL statements for a target industry, the original SQL statements of the target industry are first collected. For example, the original SQL statements are extracted from past test cases of the target industry, and the SQL used in the industry application is statistically analyzed. Finally, the data is stored in an Excel or Comma-Separated Values ​​(CSV) file. At the same time, the data of the industry application database in the field production environment can also be copied to the test database through a collection and playback tool. This process collects original SQL statement samples that cover the key operations of the target industry and reflect the specific database operation mode of the industry, providing a reliable sample basis for accurately generating SQL statements that are close to the actual environment.

[0031] S102. Parse the original SQL statement according to the preset AST rules to obtain the syntax modules, levels, data types and data content in the original SQL statement.

[0032] In this embodiment, Abstract Syntax Tree (AST) parsing technology is used to parse the original SQL statement according to preset AST rules. AST is a tree-like representation of the abstract syntactic structure of source code, an intermediate representation used by compilers or interpreters when processing source code. Each node in the AST represents a syntactic construct in the source code, such as variable declarations, expressions, function calls, control structures, etc. The root node of the tree typically represents the entire source code file, while child nodes represent specific syntactic elements and their relationships.

[0033] The preset AST rules are an improvement on the conventional AST rules, performing syntax module parsing, hierarchical parsing, data type parsing, and data content parsing on the original SQL statements. Specifically, the improved AST rules reverse-parse the collected original SQL statements into various syntax modules, and label them according to Data Definition Language (DDL) statements, Data Manipulation Language (DML) statements, and Data Query Language (DQL) statements. Hierarchical parsing requires adding the identification of sub-modules such as function nesting, subqueries, and nested joins on to the conventional AST rules. At the same time, the remaining data parts after parsing syntax modules, including function parameters, join on conditions, and where conditions, are divided into two categories for identification: fields and constants. Fields obtain their data type and corresponding data content by querying table structure information. Data content is categorized by data type (custom categories include integer, character, date / time, and floating-point). Constants are also identified and categorized into custom categories, keywords, or unknown types based on their characteristics. By reverse parsing, a 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.

[0034] S103. The syntax module, level, data type and data content are associated and stored in a progressive storage manner to obtain the first stored data, the second stored data and the third stored data.

[0035] In this embodiment, the parsed syntax modules, levels, data types, and data content are associated and stored in three different and progressive storage methods to obtain first stored data, second stored data, and third stored data, thus obtaining a hierarchical data storage structure. This hierarchical data storage method can reflect the various components of the original SQL statement and their interrelationships, which is helpful for subsequent data analysis and probability calculation, so as to obtain the probability distribution and correlation of each component of the SQL statement, and provide related reference information for the probability of each part when automatically generating SQL statements. While retaining randomness, it also makes the statement closer to the actual business scenario.

[0036] In some embodiments, the step of storing the syntax module, hierarchy, data type, and data content in a progressive storage manner to obtain first stored data, second stored data, and third stored data includes:

[0037] The parsed syntax module name is used as the key and the level is used as the value for data storage, thus obtaining the first stored data;

[0038] The names of the syntax modules obtained from each level of parsing are used as keys and the data types are used as values ​​to store the data, thus obtaining the second storage data.

[0039] The data types contained in the syntax modules of each parsed level are used as keys and the data content is used as values ​​for data storage, thus obtaining the third storage data.

[0040] In this embodiment, the data is stored in association using three different and progressive storage methods: First, for each parsed syntax module, its name is used as the key and its level as the value in the data structure, resulting in the first stored data, ensuring that the hierarchical information of each syntax module is correctly recorded and associated. Second, for each syntax module within a level, its name is used as the key and its data type as the value, resulting in the second stored data, ensuring that the data type information of each module is correctly recorded and associated. Third, for each syntax module within a level, its contained data type is used as the key and its data content as the value, resulting in the third stored data, ensuring that the data content is correctly associated with its corresponding data type. By storing the various components of the SQL statement (such as syntax modules, levels, data types, and data content) in a multi-layered structured manner, a solid foundation is provided for subsequent data analysis, statistics, and automatic SQL statement generation. This not only helps improve the accuracy and efficiency of SQL statement generation but also better adapts to the needs of different industries and application scenarios.

[0041] S104. After dividing the first stored data and the second stored data into N groups, perform probability statistics and correlation calculations on each group. Based on the correlation calculation results, map the generation probability of the level, the generation probability of the syntax module, and the generation probability of the data type.

[0042] In this embodiment, the data obtained from the first and second storage methods are divided into N groups for statistical analysis. Specifically, the first stored data (names and levels of syntax modules) and the second stored data (names and data types of syntax modules) are each divided into N equal groups. Probability statistical analysis is performed on each group, such as calculating the probability of each syntax module appearing in a specific level and the probability of each data type appearing in a specific syntax module. Furthermore, statistical methods (such as Pearson correlation coefficient and Spearman rank correlation coefficient) are used to analyze the correlation between different syntax modules and data types. Based on the correlation calculation results, the generation probability of levels, syntax modules, and data types is mapped. By performing probability statistics and correlation calculations on the first and second stored data by group, the likelihood of different levels, syntax modules, and data types appearing when generating new SQL statements can be predicted. This allows for the generation of SQL statements that better match actual usage patterns, improving the accuracy and effectiveness of the statements.

[0043] In some embodiments, dividing the first stored data and the second stored data into N groups and then performing probability statistics and correlation calculations on each group includes:

[0044] After dividing the first and second stored data into N groups, the probability of each level in each of the N groups, the probability of each syntax module in each level, and the probability of the data type contained in each syntax module in each level are statistically obtained.

[0045] Based on the probability of each grammatical module in each level of N groups, calculate the correlation coefficient between grammatical modules in each level of all groups to obtain the first correlation coefficient matrix between grammatical modules in each level.

[0046] Based on the probability of the data types contained in each grammar module in each level of N groups, calculate the correlation coefficient between the grammar module and the data type in each level of all groups, and obtain the second correlation coefficient matrix between the grammar module and the data type in each level.

[0047] In this embodiment, when performing probability statistics and correlation calculations on the first and second stored data, the first and second stored data are first divided into N equal groups. Statistics are then performed on each group to obtain the probability of each level, the probability of each syntax module within each level, and the probability of the data type contained in each syntax module within each level. The level probability refers to the ratio of the number of SQL statements corresponding to a certain level to the total number of SQL statements T collected: T SQL statements are collected from the field, and each syntax module and its level are obtained by parsing according to the standardized SQL syntax. The ratio (Tx / T) of the number of statements Tx (x = 1, 2, 3...) corresponding to a certain level to the total number of statements T is the probability of that level. As shown in Table 1, the first row indicates that SQL statement T1 has only one level (meaning the statement is relatively simple), SQL statement T2 has two levels (slightly more complex), and SQL statement T3 has three levels (increasingly complex).

[0048] The level can identify the complexity of an SQL statement, and Tx / T here can be mapped to the probability of generating a statement of varying complexity, i.e., the level probability.

[0049] In addition to directly counting the hierarchy of the entire SQL statement, it's also necessary to consider specific modules. For example, the `select_list` module has members such as functions, window functions, and subqueries that can be further nested (level +1). Similarly, `from` clauses can have subqueries (level +1), `joinon` (level +1), and so on. Combining the hierarchy and module member names, for instance, if the function member `func` in the `select_list` module group contains T21 nested statements at two levels, then the probability of the corresponding level (=2) + module name (=select_list_func) is T21 / T. This involves counting the number of times the syntax module appears in each level and calculating the probability of each syntax module appearing in each level. Furthermore, it also involves counting the number of times data types appear in each syntax module at each level and calculating the probability of each data type appearing in each syntax module at each level.

[0050] Based on the probabilities of each grammatical module in each level of the N sets of data, the correlation coefficients between grammatical modules in each level of all groups are calculated, resulting in the first correlation coefficient matrix between grammatical modules in each level. This involves calculating the correlation coefficients of each module and its corresponding level pairwise, yielding the correlation coefficient matrix between modules at each level. A correlation coefficient closer to 1 indicates a stronger positive correlation, closer to -1 indicates a stronger negative correlation, and closer to 0 indicates a weaker correlation. This allows us to determine the magnitude of the correlation between modules and the distribution characteristics such as the increase or decrease in the probability of a module appearing as the complexity of the statement increases. Similarly, based on the probabilities of the data types contained in each grammatical module in each level of the N sets 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. In some embodiments, if the correlation coefficient between the grammatical module and the data type obtained here is 1, it is not included in the subsequent calculation of the probability mapping based on the correlation calculation results and is directly mapped to a 100% probability to save computational resources and improve generation efficiency.

[0051] This embodiment, based on hierarchical associative storage, performs probability statistics and correlation analysis on the two types of stored data. This allows the first correlation coefficient matrix to reveal the interrelationships between different syntax modules, helping to understand which modules frequently appear together. This enables a more reasonable combination of them when generating SQL statements. Furthermore, the second correlation coefficient matrix reveals the relationship between syntax modules and data types, which helps in selecting appropriate data types when generating SQL statements. This serves as a guide for generating SQL statements, achieving industry data-driven optimization of SQL statement generation and reducing reliance on human experience.

[0052] In some embodiments, the step of mapping the generation probability of the hierarchy, the generation probability of the syntax module, and the generation probability of the data type based on the relevance calculation results includes:

[0053] The generation probability of each level is obtained by mapping the probabilities of each of the N groups according to a preset statistical index.

[0054] In each of the N groups of levels, a first baseline probability and a corresponding first probability boundary value are determined from the probabilities of each syntax module. In each of the N groups of levels, a second baseline probability and a corresponding second probability boundary value are determined from the probabilities of the data types contained in each syntax module.

[0055] The generation probability of the syntax module at each level is calculated based on the first baseline probability, the first correlation coefficient matrix, and the first probability boundary value.

[0056] The generation probability of the data types contained in the syntax module is calculated based on the second baseline probability, the second correlation coefficient matrix, and the second probability boundary value.

[0057] In this embodiment, when mapping the generation probabilities of each item, the generation probability of each level can be analyzed using preset statistical indicators (such as mean, median, mode, etc.) to map these statistical indicators to the generation probabilities of each level in the N groups. The generation probabilities of syntax modules and data types are then combined with the corresponding correlation coefficient matrix to provide correlation information between variables. Specifically, among the probabilities of each syntax module in each of the N groups of levels, one set is selected as the first baseline probability, and the corresponding first probability boundary value is determined. The generation probability of each syntax module in each level is then the first baseline probability + correlation coefficient * (difference between the first baseline probability and the first probability boundary value / number of levels). Similarly, among the probabilities of the data types contained in each syntax module in each of the N groups of levels, one set is selected as the second baseline probability, and the corresponding second probability boundary value is determined, thereby calculating the generation probability of the data types contained in the syntax module. By using the baseline probabilities and the correlation coefficient matrix, the generation probabilities of syntax modules and data types at each level can be calculated more accurately, making the generated SQL statements more consistent with actual database operation patterns.

[0058] S105. Perform feature analysis on the third stored data, and map the generation probability of the data content based on the feature analysis results.

[0059] In this embodiment, feature analysis is performed on the third-stored data obtained using the third storage method. Specifically, the third-stored data is a dataset obtained by associating and storing the data types contained in each syntax module at each level as keys and the data content as values. Therefore, during feature analysis, feature analysis can be performed on data content of the same broad category to obtain the feature distribution of data content under each data type. Based on the feature analysis results, the generation probability of the data content can be predicted, thereby improving the accuracy of SQL statement generation.

[0060] In some embodiments, the data type includes integer, character, date / time, and floating-point types, and the feature analysis of the third stored data includes:

[0061] For the integer data in the third stored data, analyze and obtain the numerical range and numerical characteristic distribution of the integers;

[0062] For the character data in the third storage data, analyze and obtain the characteristic distribution of character length and ASCII code;

[0063] For the date and time data in the third stored data, after segmentation according to the standard date and time format, the numerical range and numerical feature distribution are analyzed and obtained;

[0064] For the floating-point data in the third stored data, after identifying the accuracy, the numerical range and numerical feature distribution are analyzed and obtained.

[0065] In this embodiment, corresponding feature analysis is performed on the data content of different data types in the third storage data. Integer data is analyzed and stored according to the numerical range and distribution of integers, that is, the minimum and maximum values ​​of integer data are calculated to determine the numerical range, and statistical methods (such as calculating the mean, median, standard deviation, etc.) are used to analyze the distribution characteristics of integer data. Character data is analyzed and stored according to character length and ASCII code, that is, the length of each character data item is measured and the distribution of ASCII code values ​​of each character in the character data is analyzed. Date and time data is segmented according to the standard date and time format and then analyzed for numerical storage range and feature distribution. That is, 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 and feature distribution. Floating-point data is analyzed according to the analysis logic of integer data after identifying the precision, that is, the precision of floating-point data (the number of decimal places) is determined, and the numerical range and distribution characteristics of floating-point data are analyzed. The features obtained from the analysis are used as the basis for generating subsequent data content, thereby improving the accuracy of SQL statement generation.

[0066] In some embodiments, the step of mapping the generation probability of data content based on feature analysis results includes:

[0067] When the data type is integer, date / time, or floating-point, calculate the probability of randomly selecting values ​​within the range using the mean distribution based on the maximum and minimum values ​​of the numerical range, or statistically analyze the probability of each value based on the distribution of numerical characteristics.

[0068] When the data type is character, the selection range is determined by the maximum and minimum values ​​of the string length, the character length is randomly selected using the mean distribution, and the generation probability of each character is determined based on the statistical ASCII feature distribution.

[0069] In this embodiment, when mapping the generation probability of data content, the four data types are divided into two mapping types: one is numeric type, including integer, processed date / time type, and processed floating-point type; the other is character type. The generation probability of numeric type can be implemented in two ways: one is to obtain the maximum value (max) and minimum value (min) of the data content based on the numerical range (suitable for cases with large data volume and few duplicate data), randomly select values ​​within the range [min, max] using a mean distribution, and calculate the probability of randomly selecting values ​​within the range as the generation probability of the data content; the second is to statistically analyze the numerical feature distribution (suitable for cases with small data volume and many duplicate data), statistically analyze the probability of each existing value appearing, and directly use it as the generation probability of the data content. Since date type can be regarded as an integer separated by special symbols, and floating-point type can also be regarded as an integer separated by special symbols ".", the generation probability can also be obtained through the above two methods. The generation probability of characters needs to be determined by combining the character length and ASCII code. The selection range is determined by the maximum and minimum values ​​of the string length. The character length is randomly selected using a mean distribution, and the generation probability of each character is determined based on the statistical distribution of ASCII characteristics. That is, the specific characters use completely random ASCII codes without further data analysis and construction. By calculating the generation probability distribution of data content, numerical values ​​or strings that conform to the characteristics of actual data can be simulated and generated more accurately, ensuring that the generated SQL statements can cover various possible data situations and improving the accuracy and reliability of testing.

[0070] S106. Generate corresponding SQL statements based on 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.

[0071] In this embodiment, based on the generation probabilities of the mapped hierarchy, syntax modules, data types, and data content, SQL syntax modules can be randomly generated according to the SQL execution order, and data types corresponding to the positions in the SQL statement can be generated. Matching data content is then selected at the corresponding data type positions to obtain a complete SQL statement generated through probability association. By parsing past target industry SQL statements, the characteristic distribution of industry data and the correlation between different parts of the statement are obtained. Following this, new SQL statements are automatically generated after obtaining the corresponding generation probabilities. This allows for the automated learning and generation of new SQL statements from actually used SQL statements. The generated SQL statements not only have statistical rationality but also better conform to the actual application scenarios of the target industry, more accurately reflecting the operational patterns of the target industry, thus improving the accuracy and effectiveness of the SQL statements.

[0072] In some embodiments, generating corresponding SQL statements based on the generation probability of hierarchy, generation probability of syntax modules, generation probability of data types, and generation probability of data content includes: replacing the content at corresponding positions in the original SQL statement based on the generation probability of hierarchy, generation probability of syntax modules, generation probability of data types, and generation probability of data content, and generating corresponding SQL statements accordingly.

[0073] In practice, when randomly generating new SQL statements based on generation probabilities, two methods can be used: First, utilize conventional open-source SQL automatic generation tools to randomly generate SQL statements based on generation probabilities; second, based on the large number of parsed SQL modules already existing in the parsing phase, and combining the calculated generation probabilities of the level, syntax modules, data types, and data content, replace corresponding positions in the original SQL statement. The replacement process involves sequentially replacing each level, syntax module, data type, and data content in the original SQL statement. After the replacement is completed, SQL verification tools can be used to check whether the generated SQL statement is syntactically correct and logically reasonable, ensuring that the replaced SQL statement is valid in structure and semantics. This achieves automated SQL statement generation, improves development and testing efficiency, and reduces the workload of manually writing SQL statements.

[0074] There is no necessary order between the above steps. Those skilled in the art will understand from the description of the embodiments of this application that the above steps may have different execution orders in different embodiments, that is, they may be executed in parallel or in turn, etc.

[0075] To better understand the implementation process of the SQL statement parsing and association generation method provided in this application embodiment, the implementation process of the SQL statement parsing and association generation method will be described below with reference to Figure 2 and through specific application embodiments:

[0076] As shown in Figure 2, the SQL statement parsing and association generation method provided in this application includes the following steps:

[0077] Step 1: Industry SQL collection methods include extracting past test cases and statistically analyzing SQL used in the application. The collected SQL data is ultimately stored in Excel or CSV format files. At the same time, the data from the industry application database in the production environment needs to be copied to the test database using a collection and playback tool.

[0078] Step Two: Using improved AST rules, the SQL statements collected in Step One are reverse-parsed into various syntax modules, and tagged according to DDL statements, DML statements, and DQL statements. Hierarchical parsing requires adding the identification of sub-modules such as nested functions, subqueries, and nested joins on to the regular AST rules. Simultaneously, the remaining data after parsing the syntax modules (including function parameters, join on conditions, where conditions, etc.) is divided into two categories: fields and constants. Fields are retrieved by querying table structure information to obtain data types and corresponding data content. Data content is categorized by data type (custom categories—integer, character, date / time, floating-point). Constants are categorized into custom categories, keywords, or unknown types based on feature identification. Finally, storage is performed in three ways: the first uses the syntax module name as the key and the hierarchy as the value; the second uses the syntax module + hierarchy as the key and the data type as the value; the third uses the syntax module + hierarchy + data type as the key, and the data content retrieved from the field query or the constant as the value for categorized storage.

[0079] Step 3: Divide the data from the first and second storage methods in Step 2 into N groups for statistical analysis. First, calculate the probability of each level and each module name in each group. Then, calculate the correlation coefficient between module names in each level across all groups. Obtain the correlation coefficient matrix between each level + module name and data type using the same method. Furthermore, perform feature analysis on the data content related to the same major category stored in the data from the third storage method in Step 2. Integer data is analyzed and stored according to its numerical range and numerical characteristic distribution; character data is analyzed and stored according to its character length and ASCII code; date and time data is segmented according to the standard date and time format before analyzing its numerical range and characteristic distribution; floating-point data is processed according to integer analysis logic after identifying its precision. The features obtained from this analysis will serve as the basis for generating constant values.

[0080] Step 4: Normalize the correlation coefficient matrix of syntax module names, levels, and data types obtained in Step 3 to obtain the generation probability of each level, the generation probability of module names under a certain level, and the generation probability of data types contained in the module names. Then, randomly generate SQL statements and the required parameter data types based on the current generation probabilities. Next, randomly select corresponding table fields or constants based on the data types. If it is a constant, generate a constant of the corresponding type or a random boundary value based on the characteristics of the data content analyzed in Step 3. In this way, a complete SQL statement with industry characteristics can be generated.

[0081] In this embodiment, by parsing previous industry SQL statements to obtain the feature distribution of industry data and mapping it to the generation probability, it is possible to generate more industry-specific, more complex, and broader SQL statements as test cases, thereby reducing the risk of test failure leakage.

[0082] Another embodiment of this application provides an SQL statement parsing and association generation apparatus, as shown in FIG3, apparatus 1 includes:

[0083] Data acquisition module 11 is configured to collect raw SQL statements from the target industry.

[0084] Parsing module 12 is configured to parse the original SQL statement according to a preset AST rule to obtain the syntax module, level, data type and data content in the original SQL statement;

[0085] The progressive storage module 13 is configured to associate and store the syntax module, level, data type and data content in a progressive storage manner to obtain first stored data, second stored data and third stored data;

[0086] The correlation calculation module 14 is configured to divide the first stored data and the second stored data into N groups and perform probability statistics and correlation calculations respectively, and map 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.

[0087] The feature analysis module 15 is configured to perform feature analysis on the third stored data and map the generation probability of the data content based on the feature analysis results.

[0088] The association generation module 16 is configured to generate corresponding SQL statements based on 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.

[0089] The term "module" as used in this application refers to a series of computer program instruction segments capable of performing specific functions. It is more suitable than a program for describing the execution process of SQL statement parsing and association generation. For specific implementation methods of each module, please refer to the corresponding method embodiments described above, which will not be repeated here.

[0090] Another embodiment of this application provides an SQL statement parsing and association generation system, as shown in Figure 4. System 10 includes:

[0091] One or more processors 110 and memory 120 are provided. Figure 4 uses one processor 110 as an example. The processor 110 and memory 120 can be connected by a bus or other means. Figure 4 uses a bus connection as an example.

[0092] Processor 110 is used to implement various control logics of system 10. 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 microcontroller, an acorn RISC machine (ARM), or other programmable logic devices, discrete gate or transistor logic, discrete hardware components, or any combination of these components. Furthermore, processor 110 can also be any conventional processor, microprocessor, or state machine, and can be implemented as a combination of computing devices, such as a combination of a DSP and a microprocessor, multiple microprocessors, one or more microprocessors combined with a DSP, and / or any other such configuration.

[0093] 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 the program instructions corresponding to the SQL statement parsing and association generation method in the embodiments of this application. 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, thereby implementing the SQL statement parsing and association generation method in the above method embodiments.

[0094] The memory 120 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created according to the use of the system 10. Furthermore, the memory 120 may include high-speed random access memory and may also include non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 120 may include memory remotely located relative to the processor 110, and this remote memory may be connected to the system 10 via a network. Examples of such networks include the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.

[0095] One or more units are stored in memory 120 and, when executed by one or more processors 110, implement the method steps S101 to S106 in FIG1 described above.

[0096] This application provides a non-volatile computer-readable storage medium storing computer-executable instructions that are executed by one or more processors, for example, performing the method steps S101 to S106 in FIG1 described above.

[0097] As examples, non-volatile storage media may include read-only memory (ROM), programmable read-only memory (PROM), electrically programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), or flash memory. Volatile memory may include random access memory (RAM) as an external cache memory. RAM can take many forms, such as Synchronous Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), Synchronous Dynamic Random Access Memory (SDRAM), Double Data Rate Synchronous Dynamic Random Access Memory (DDR SDRAM), Enhanced Synchronous Dynamic Random Access Memory (ESDRAM), Synchlink Dynamic Random Access Memory (SLDRAM), and Direct Rambus Random Access Memory (DRRAM). The memory components or memories disclosed in the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0098] In summary, the SQL statement parsing and association generation method, apparatus, system, and medium disclosed in this application involves: collecting original SQL statements from a target industry; parsing the original SQL statements according to preset AST rules to obtain the syntax modules, levels, data types, and data content; associating and storing the syntax modules, levels, data types, and data content in a progressive storage manner to obtain first stored data, second stored data, and third stored data; dividing the first and second stored data into N groups and performing probability statistics and correlation calculations on each group, mapping the generation probability of levels, syntax modules, and data types based on the correlation calculation results; performing feature analysis on the third stored data, mapping the generation probability of data content based on the feature analysis results; and associating and generating corresponding SQL statements based on the generation probabilities of levels, syntax modules, data types, and data content. By parsing and calculating the correlation and data feature distribution between different levels of content in the original SQL statement, mapping them to corresponding probabilities, and generating targeted SQL statements as test cases, the effectiveness of testing is improved.

[0099] Those skilled in the art will understand that all or part of the processes in the methods of the above embodiments can be implemented by a computer program instructing related hardware (such as a processor, controller, etc.). The computer program can be stored in a non-volatile, computer-readable storage medium, and when executed, it can include the processes of the above method embodiments. The storage medium can be a memory, magnetic disk, floppy disk, flash memory, optical storage, etc.

Claims

1. A method for parsing and generating relational relationships using Structured Query Language (SQL) statements, comprising: Collect raw SQL statements from the target industry; The original SQL statement is parsed according to the preset Abstract Syntax Tree (AST) rules to obtain the syntax modules, levels, data types, and data content in the original SQL statement; The syntax modules, levels, data types, and data content are associated and stored in a progressive storage manner to obtain first stored data, second stored data, and third stored data; After dividing the first and second stored data into N groups, probability statistics and correlation calculations are performed on each group. Based on the correlation calculation results, the generation probability of the level, the generation probability of the syntax module, and the generation probability of the data type are mapped to obtain the generation probability. The third stored data is subjected to feature analysis, and the generation probability of the data content is obtained by mapping the feature analysis results. The corresponding SQL statement is generated based on 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.

2. The method of claim 1, wherein, The method of associating and storing the syntax modules, levels, data types, and data content in a progressive storage manner to obtain first stored data, second stored data, and third stored data includes: The parsed syntax module name is used as the key and the level is used as the value for data storage, thus obtaining the first stored data; The names of the syntax modules obtained from each level of parsing are used as keys and the data types are used as values ​​to store the data, thus obtaining the second storage data. The data types contained in the syntax modules of each parsed level are used as keys and the data content is used as values ​​for data storage, thus obtaining the third storage data.

3. The method of claim 2, wherein, The step of dividing the first stored data and the second stored data into N groups and then performing probability statistics and correlation calculations on each group includes: After dividing the first and second stored data into N groups, the probability of each level in each of the N groups, the probability of each syntax module in each level, and the probability of the data type contained in each syntax module in each level are statistically obtained. Based on the probability of each grammatical module in each level of N groups, calculate the correlation coefficient between grammatical modules in each level of all groups to obtain the first correlation coefficient matrix between grammatical modules in each level. Based on the probability of the data types contained in each grammar module in each level of N groups, calculate the correlation coefficient between the grammar module and the data type in each level of all groups, and obtain the second correlation coefficient matrix between the grammar module and the data type in each level.

4. The method of claim 3, wherein, The process of mapping the generation probability of the hierarchy, the generation probability of the syntax module, and the generation probability of the data type based on the correlation calculation results includes: The generation probability of each level is obtained by mapping the probabilities of each of the N groups according to a preset statistical index. In each of the N groups of levels, a first baseline probability and a corresponding first probability boundary value are determined from the probabilities of each syntax module. In each of the N groups of levels, a second baseline probability and a corresponding second probability boundary value are determined from the probabilities of the data types contained in each syntax module. Based on the first baseline probability, the first correlation coefficient matrix, and the first probability boundary value, the generation probability of the syntax module at each level is calculated. Based on the second baseline probability, the second correlation coefficient matrix, and the second probability boundary value, the generation probability of the data types contained in the syntax module is calculated.

5. The method of claim 2, wherein, The data types include integer, character, date / time, and floating-point types. The feature analysis of the third stored data includes: For the integer data in the third stored data, analyze and obtain the numerical range and numerical characteristic distribution of the integers; For the character data in the third storage data, analyze and obtain the characteristic distribution of character length and ASCII code; For the date and time data in the third stored data, after segmentation according to the standard date and time format, the numerical range and numerical feature distribution are analyzed and obtained; For the floating-point data in the third stored data, after identifying the accuracy, the numerical range and numerical feature distribution are analyzed and obtained.

6. The method of claim 5, wherein, The generation probability of data content obtained by mapping based on feature analysis results includes: When the data type is integer, date / time, or floating-point, calculate the probability of randomly selecting values ​​within the range using the mean distribution based on the maximum and minimum values ​​of the numerical range, or statistically analyze the probability of each value based on the distribution of numerical characteristics. When the data type is character, 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 generation probability of each character is determined based on the statistical ASCII feature distribution.

7. The method of claim 1-6, wherein, The step of generating corresponding SQL statements based on 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 includes: Based on 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, the content at the corresponding position in the original SQL statement is replaced, and the corresponding SQL statement is generated accordingly.

8. An apparatus for parsing and generating SQL statements, comprising: The data acquisition module is configured to collect raw SQL statements from the target industry. The parsing module is configured to parse the original SQL statement according to a preset AST rule to obtain the syntax module, level, data type and data content in the original SQL statement; The progressive storage module is configured to associate and store the syntax module, hierarchy, data type and data content in a progressive storage manner to obtain first stored data, second stored data and third stored data; The correlation calculation module is configured to divide the first stored data and the second stored data into N groups and then perform probability statistics and correlation calculations on each group. Based on the correlation calculation results, the generation probability of the level, the generation probability of the syntax module, and the generation probability of the data type are mapped to the data. The feature analysis module is configured to perform feature analysis on the third stored data and map the generation probability of the data content based on the feature analysis results. The associated generation module is configured to generate corresponding SQL statements based on 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.

9. A SQL statement parsing and relation 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, which, when executed by the at least one processor, enable the at least one processor to perform the SQL statement parsing and association generation method according to any one of claims 1-7.

10. A non-volatile computer-readable storage medium storing computer-executable instructions that, when executed by one or more processors, enable the one or more processors to perform the SQL statement parsing and association generation method according to any one of claims 1-7.

Images