SQL (Structured Query Language) where clause parametric variable test data acquisition method and device
Through large models, SQL is rewritten and verified, accurate test data is generated, which solves the problem that SQL cannot be accurately tested and not launched in the existing technology, and improves the accuracy and efficiency of database performance testing.
Patent Information
- Application Number
- CN202411524405.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-29
- Publication Date
- 2025-02-14
AI Technical Summary
When performing database performance testing, the prior art cannot accurately perform performance testing on unlined SQL, resulting in inaccurate test results.
A method for obtaining test data from SQL where clause parameter variables is proposed, rewritten the test SQL through a large model, generate a target SQL collection, and execute these SQLs in the target database to obtain test data. The method includes adding random sampling conditions when the amount of data is greater than the preset threshold, and correcting SQL based on the exception results to ensure the accuracy of the test data.
Through the big model, SQL is rewritten and its execution efficiency is verified, and it is adapted to multiple databases, which improves the universality, reliability and efficiency of test data generation, and obtains accurate test data from historical data.
Smart Images
Figure CN119440982A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of testing technology, and in particular to a method and device for acquiring SQL where clause parameter variable test data. Background Art
[0002] Database performance testing is a key step to ensure that applications run efficiently. By performing performance testing on the database, you can discover and solve problems that may cause slow system response or resource exhaustion in advance. Performance testing helps optimize query efficiency and reduce unnecessary database load, thereby improving user experience. In addition, it can also help identify bottlenecks in hardware or configuration, guide the reasonable allocation of computing resources, and ensure the stability and scalability of the system in high-concurrency scenarios.
[0003] Database SQL is widely used in software development, and its execution efficiency is the focus of performance testing. The accuracy of SQL test data is the key to database performance testing. In the prior art, database SQL logs are usually captured from the production environment, and the SQL recorded in the database log is replayed. This method is not suitable for performance testing of SQL that has not been put online. It only tests SQL parameter variable assignments that have appeared within a certain time range in production, and does not test SQL parameter variable assignments that may appear in theory even though they have not appeared in the production environment, resulting in inaccurate database performance testing.
[0004] Therefore, there is an urgent need for a test data generation method with high accuracy to generate test data with high accuracy, thereby improving the accuracy of database performance testing. Summary of the invention
[0005] The present application aims to solve one of the technical problems in the related art at least to some extent.
[0006] To this end, the first purpose of the present application is to propose a method for obtaining SQL where clause parameter variable test data to improve the accuracy of the test data.
[0007] The second objective of the present application is to provide a device for acquiring SQL where clause parameter variable test data.
[0008] The third objective of the present application is to provide an electronic device.
[0009] A fourth objective of the present application is to provide a computer-readable storage medium.
[0010] A fifth object of the present application is to provide a computer program product.
[0011] To achieve the above-mentioned purpose, the first embodiment of the present application proposes a method for obtaining SQL where clause parameter variable test data, including:
[0012] Generate a first prompt word of the big model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the big model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining a value of a parameter variable of a where clause in the SQL to be tested;
[0013] When the amount of data in the first target SQL-related table in the target database is greater than a preset threshold, a random sampling condition is added to the first target SQL to generate a second target SQL;
[0014] Execute the second target SQL, and if the execution result of the second target SQL is abnormal, modify the second target SQL according to the type of the abnormal result to obtain the third target SQL;
[0015] Execute the third target SQL to obtain the test data of the parameter variable of the where clause in the SQL to be tested.
[0016] To achieve the above-mentioned purpose, the second aspect of the present application proposes a SQL where clause parameter variable test data acquisition device, comprising:
[0017] A generation module, used to generate a first prompt word of a large model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining a value of a parameter variable of a where clause in the SQL to be tested;
[0018] A rewriting module, used for adding a random sampling condition to the first target SQL to generate a second target SQL when the amount of data in the table related to the first target SQL in the target database is greater than a preset threshold;
[0019] A verification module, used for executing the second target SQL, and in the case where the execution result of the second target SQL is abnormal, correcting the second target SQL according to the type of the abnormal result to obtain the third target SQL;
[0020] The acquisition module is used to execute the third target SQL and obtain the test data of the parameter variable of the where clause in the SQL to be tested.
[0021] To achieve the above-mentioned purpose, the third aspect of the present application provides an electronic device, including:
[0022] at least one processor; and
[0023] a memory communicatively connected to at least one processor; wherein,
[0024] The memory stores instructions that can be executed by at least one processor. The instructions are executed by the at least one processor so that the at least one processor can execute the method of the above embodiment.
[0025] To achieve the above objectives, the fourth aspect of the present application proposes a computer-readable storage medium storing computer instructions, wherein the computer instructions are used to enable a computer to execute the method according to the above embodiment.
[0026] To achieve the above objectives, the fifth aspect of the present application proposes a computer program product, including a computer program, which implements the method of the above embodiment when executed by a processor.
[0027] The SQL where clause parameter variable test data acquisition method, device, electronic device and storage medium provided by the present application generate the first prompt word of the large model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested using the large model to generate the first target SQL set, wherein the first target SQL set includes the first target SQL for obtaining the value of the where clause parameter variable in the SQL to be tested, and when the amount of data obtained in the first target SQL related table in the target database is greater than the preset threshold, a random sampling condition is added to the first target SQL to generate the second target SQL, and then the second target SQL is executed. When the execution result of the second target SQL is abnormal, the second target SQL is corrected according to the type of the abnormal result to obtain the third target SQL, and then the third target SQL is executed to obtain the test data of the where clause parameter variable in the SQL to be tested. Thus, the SQL to be tested is rewritten by the large model, and the execution efficiency and reliability of the first target SQL are verified, which can adapt to multiple databases and improve the versatility, reliability and efficiency of test data generation. In addition, based on the third target SQL finally obtained, the test data of the where clause parameter variable of the SQL to be tested is obtained from the historical data, which improves the accuracy of generating test data.
[0028] Additional aspects and advantages of the present application will be given in part in the description below, and in part will become apparent from the description below, or will be learned through the practice of the present application. BRIEF DESCRIPTION OF THE DRAWINGS
[0029] The above and / or additional aspects and advantages of the present application will become apparent and easily understood from the following description of the embodiments in conjunction with the accompanying drawings, in which:
[0030] Figure 1A flowchart of a method for acquiring test data for a SQL where clause parameter variable provided in an embodiment of the present application;
[0031] Figure 2 A structural diagram of a SQL where clause parameter variable test data acquisition device provided in an embodiment of the present application. DETAILED DESCRIPTION
[0032] The embodiments of the present application are described in detail below, and examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals throughout represent the same or similar elements or elements having the same or similar functions. The embodiments described below with reference to the accompanying drawings are exemplary and are intended to be used to explain the present application, and should not be construed as limiting the present application.
[0033] SQL (Structured Query Language) is a standard computer language used to manage and process relational databases. Compared with interface stress testing, direct stress testing for database SQL can test SQL in more forms without the need to deploy an application server environment, bypassing the tedious internal verification of the application interface and external system verification. The test script development efficiency is higher and the SQL detection range is wider.
[0034] In direct stress testing of database SQL, the main sources of tested SQL are as follows: (1) SQL manually combed by the development team, such as: SQL related to the main business process of the system, system batch task SQL, report-related SQL, authentication-related SQL; (2) SQL captured by monitoring tools in a certain period of time, such as: TOP20 slow SQL summarized in Oracle's AWR report, database hot SQL captured by Dynatrace monitoring tools; (3) Database SQL cache. Among them, a large number of test SQLs come from SQL manually combed by the development team and SQL captured by monitoring tools. These SQLs are SQLs with parameter variables but no specific values, referred to as parameterized SQL.
[0035] In SQL, the WHERE clause is a very important component, which is used to specify the conditions for retrieving data from a database table. In other words, the WHERE clause is used to filter records so that only records that meet the specified conditions are selected and returned. Parameter variables are placeholders used to store dynamic values in SQL queries. They can be used as part of the conditions in the WHERE clause to filter data based on the values of these parameter variables. The specific syntax and implementation of parameter variables may vary depending on different database systems (such as MySQL, SQL Server, Oracle, etc.). Taking JDBC as an example, Stringsql = "SELECT*FROM employees WHERE salary>?"; before executing this query, you need to provide a specific value for ?.
[0036] During the stress test, we need to assign values to the parameter variables of the SQL being tested. These assignments are called test data for parameter variables in direct stress testing of database SQL. Generally, the test data for assigning parameter variables to SQL where clauses is obtained based on the following methods:
[0037] 1. Capture the database SQL log from the production environment and replay the SQL recorded in the database log. This method is not suitable for performance testing of SQL that has not been put online. It only tests the SQL parameter variable assignments that have appeared within a certain time range in production. It does not test the SQL parameter variable assignments that may appear in theory even though they have not appeared in the production environment. It may not capture special parameter SQL. It also requires the conditions of obtaining complete logs from the production environment and deploying monitoring or auditing tools.
[0038] 2. SQL parameter variable test data extraction is implemented through fixed program code. This method has not yet formed a standard for SQL parameter variable performance test data extraction. It is necessary to write code separately for the special syntax of each database product. It cannot handle parameter variable test data extraction with complex SQL writing, and cannot flexibly change the extraction logic according to business rules. It is not flexible enough and has a high development iteration cost.
[0039] 3. Manual extraction after the tester analyzes the SQL features independently. This method relies on the tester's ability to analyze and understand SQL and their professional qualities in performance testing, and the efficiency of test data generation is low.
[0040] The software testing method and device of the embodiments of the present application are described below with reference to the accompanying drawings.
[0041] The software testing method of the embodiment of the present application is executed by the software testing device provided by the embodiment of the present application. The device can be configured in a computer device or a terminal device to achieve the accuracy of performance testing.
[0042] Figure 1 A flowchart of a software testing method provided in an embodiment of the present application.
[0043] like Figure 1 As shown, the software testing method includes the following steps:
[0044] Step 101, based on the SQL to be tested and the target database type, a first prompt word of a large model is generated to rewrite the SQL to be tested using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining the value of a parameter variable of a where clause in the SQL to be tested.
[0045] In this application, attribute information prompt words are generated, and the big model is called based on the attribute information prompt words to identify the table name, column name, and parameter type corresponding to the parameter variable. A condition acquisition prompt word is generated, and the big model is called based on the condition acquisition prompt word to obtain the relevant condition expression that does not contain the parameter variable in the SQL to be tested. After that, a table relationship generation prompt word is generated, and the big model is called based on the table relationship generation prompt word to obtain the table association clause in the SQL to be tested. Then, the table name, column name, and condition expression are spliced based on the grammar rules corresponding to the target database type to generate the first target SQL set.
[0046] Exemplarily, the attribute information prompt words may consist of the SQL to be tested, the requirements for the output parameter variables, the description information of the parameter variables, etc. The attribute information prompt words are as follows:
[0047] "<SQL to be tested>
[0048] Manual notes: <Developers and testers’ description of parameter variables>
[0049] Answer in Chinese: The table name, column name, and variable type (single-value type or multi-value type) of the parameter variable corresponding to the SQL parameter variable.
[0050] Note:
[0051] 1. If the parameter variable contains #{}, ${} symbols, only the characters in {} are used as the name of the parameter variable; if the parameter variable contains ? or :1, and {} is not used, the name of the parameter variable is the corresponding column name;
[0052] 2. If the parameter variable is passed in as a single value and the variable type of the parameter variable is a single value type, the value range is written as [1,1]; if the parameter variable is passed in as multiple values and the variable type of the parameter variable is a multiple value type, the value range of the parameter variable is set according to the manual remarks information, and the value range is written as [minimum number, maximum number]. (If there is no description of the number range of a parameter variable in the manual remarks, the value range is written as null).
[0053] Output format (punctuation marks are in English):
[0054] {"Parameter variable name":("Table name","Column name","Single value","Value range"),"Parameter variable name":("Table name","Column name","Multiple values","Value range"),"Parameter variable name":("Table name","Column name","Multiple values","Value range")}".
[0055] Exemplarily, the condition acquisition prompt words may consist of the SQL to be tested, the requirement to output the relevant condition expression, etc. The condition acquisition prompt words are as follows:
[0056] "<SQL to be tested>
[0057] Answer in Chinese: The where clause of this SQL does not contain a conditional expression with parameter variables.
[0058] Output format (punctuation marks are in English):
[0059] {"table name":[conditional expression, conditional expression,...],"table name":[conditional expression, conditional expression,...]}. If not, output: None
[0060] Note: Exclude 1=1 conditional expression"
[0061] Exemplarily, the table relationship generation prompt words may be composed of the SQL to be tested, the requirements of the output table association clause, etc. The table relationship generation prompt words are as follows:
[0062] "<SQL to be tested>
[0063] Answer in Chinese:
[0064] Use the from clause or join clause to write out the core association method between table , table , etc. in the SQL, for example: from tableA join tableB on tableA.a=tableB.b. If the association method between table d and table a cannot be directly given based on the above information, None is output.
[0065] Note: Do not include parameterized fields in the association method.
[0066] Finally, based on the grammatical rules corresponding to the target database type, the table name, column name, and conditional expression are concatenated to generate the first target SQL set.
[0067] for example:
[0068] SELECT <column name 1> as <parameter variable 1>, <column name 2> as <parameter variable 2>, ... WHERE <related condition expression>.
[0069] Optionally, the first prompt word of the big model may be generated based on the SQL to be tested, preset business constraints, and the target database type, so as to rewrite the SQL to be tested using the big model to generate a first target SQL set.
[0070] In this application, the SQL to be tested is related to the actual functional business of the system. For some SQL, preset business constraints need to be added to make the test data of the parameter variables obtained by the first target SQL more in line with the actual situation. This improves the accuracy of generating test data. The preset business constraints of the SQL to be tested are mainly placed in the manual remarks of the first prompt word. Business constraints include the following two common types:
[0071] 1. Time range constraint type. For example, the time field is the same parameterized query field INSERTDATE, and the conditional expression in the SQL to be tested is:
[0072] a.INSERTDATE>=TO_DATE('#{INSERTDATE_1}','yyyy-mm-ddhh24:mi:ss')anda.INSERTDATE<=TO_DATE('#{INSERTDATE_2}',
[0073] 'yyyy-mm-dd hh24:mi:ss')
[0074] INSERTDATE queries data for a maximum of one week. In this case, #{INSERTDATE_1} should be at most 7 days smaller than #{INSERTDATE_2}. The business constraint is: In each row of data obtained by the first target SQL query, INSERTDATE_1 is at most 7 days smaller than INSERTDATE_2.
[0075] 2. Use the like keyword to query the type. Assume that the conditional expression in the SQL to be tested is:
[0076] file_name LIKE concat(concat('%',:3),'%')
[0077] Parameter variable placeholder: 3 corresponds to the query condition field file_name, and the SQL to be tested is a forward and backward fuzzy query. If the application system limits the minimum length of the fuzzy query of file_name to 2 characters and the maximum length to 10 characters, then the business constraint is: the file_name obtained by the query result should be randomly intercepted from 2 to 10 consecutive characters. If the parameter variable can pass in multiple values (i.e. list type), manually note the minimum and maximum number of values that can be passed in.
[0078] It should be noted that the role of the big model in step 101 is the SQL generator.
[0079] Step 102: When the amount of data in the first target SQL-related table in the target database is greater than a preset threshold, a random sampling condition is added to the first target SQL to generate a second target SQL.
[0080] The related table of the first target SQL may be a data table queried by the first target SQL.
[0081] In the present application, the information of the relevant table can be queried from the target database, and the data volume of the first target SQL relevant table (such as the number of rows of data obtained). Afterwards, when the data volume is greater than the preset threshold, the random sampling condition can be added to the first target SQL based on the random sampling method applicable to the target database type to generate the second target SQL. Thereby reducing the amount of data obtained by the second target SQL to reduce the amount of data processing and improve data processing efficiency. Among them, the random sampling method applicable to the target database type can be pre-set in the system, and the random sampling conditions corresponding to each random sampling method can also be pre-set in the system.
[0082] Optionally, the big model can be used as a SQL rewriter to generate the second target SQL. At this time, the input information of the big model is: the input information of the SQL generator + the output result of the SQL generator + the preset threshold of the table data volume. The output result of the big model is: the second target SQL set.
[0083] In the process of data processing as a data SQL rewriter, the big model uses the Function Calling technology to call the function method corresponding to the statistical information (such as the number of table rows) of the second target SQL related table to obtain the data volume of the first target SQL related table. When the data volume is greater than the preset threshold of the table data volume, the big model adds random sampling conditions to the first target SQL according to the random sampling method applicable to the first target SQL and the target database type to generate the second target SQL.
[0084] Step 103: execute the second target SQL. If the execution result of the second target SQL is abnormal, modify the second target SQL according to the type of the abnormal result to obtain the third target SQL.
[0085] In this application, a return data amount constraint is added to the second target SQL, so that only a small amount of data (such as 10 rows of data) is returned when the second target SQL is executed. After that, the second target SQL is executed. In the case where the second target SQL is successfully executed, the original second target SQL is retained as the third target SQL. In the case where the execution result of the second target SQL is abnormal, the second target SQL is corrected according to the type of the abnormal result to obtain the third target SQL. This ensures the reliability of test data generation.
[0086] Optionally, the big model can be used as a data SQL verification debugger to generate the third target SQL using the big model. At this time, the input information of the big model is: the input information of the data SQL generator + the output information of the data SQL rewriter. The output result of the big model is: the third target SQL set.
[0087] When the big model is used as a SQL validation debugger to process data, a constraint on the amount of returned data is added to the second target SQL, so that only a small amount of data (such as 10 rows of data) is returned when the second target SQL is executed. After that, Function Calling is used to connect to the database to execute the modified second target SQL and obtain the execution result. Then, the big model modifies the second target SQL according to the execution result. For example, if the execution result of the second target SQL is abnormal, the second target SQL is modified according to the type of abnormal result to obtain the third target SQL. If the execution result of the second target SQL is normal but the amount of data returned is less than the preset threshold, the query condition restriction in the second target SQL is reduced.
[0088] Step 104: execute the third target SQL to obtain test data of the parameter variables of the where clause in the SQL to be tested.
[0089] In the present application, the third target SQL may be executed to obtain historical data corresponding to the parameter variables of the where clause in the SQL to be tested, and the historical data corresponding to each parameter variable may be directly used as the test data of each parameter variable.
[0090] Optionally, the historical data corresponding to each parameter variable can be counted separately to expand the test data of each parameter variable. For example, random numbers are taken within the extreme value range of the historical data corresponding to the parameter variable to generate the test data of the parameter variable, thereby improving the coverage of the test data.
[0091] Optionally, when the parameter type of the parameter variable is a list, execute the third target SQL to obtain multiple historical data of the parameter variable, and then randomly sample the historical data based on the number of list elements corresponding to the parameter variable to obtain the test data corresponding to the parameter variable, thereby ensuring the accuracy and extensiveness of the data. Example 1: The parameter variable is name, and the three historical data corresponding to name are: Xiao Ming 、 Xiaohong, Xiaohuang, the pseudo code of the where clause corresponding to the names is: the names should be in List 1, the number of elements in List 1 is 2, the number of randomly sampled historical data should be less than or equal to 2, and the test data corresponding to the name can be [Xiaoming, Xiaohong], [Xiaoming Xiaohuang], [Xiaohong, Xiaohuang], [Xiaoming], [Xiaohong], [Xiaohuang].
[0092] In the insurance field, the business is relatively complex, and most of the SQLs requested by the information system contain multiple parameter variables. At this time, it is necessary to consider the relationship and combination between the parameter variables in the SQL, analyze and understand the data distribution of the tables touched by the SQL, and maximize the coverage of the parameter variable test data.
[0093] For example, the where clause of the SQL to be tested contains multiple parameter variables: {X1, X2, ..., Xs-1, Xs}, and the total number of test data required by the SQL to be tested is P.
[0094] Example 2: {1,1,...,1,1} indicates that all parameter variables are single-valued, and the historical data corresponding to each parameter variable can be directly used as the test data of each parameter variable.
[0095] Example 3: {n1,1,...,1,1} indicates that one of the parameter variables is a list and the other parameter variables are single values. The historical data corresponding to the n1 parameter variable is grouped using the method in Example 1 above to obtain the in-group test data of the n1 parameter variable. Afterwards, each in-group test result corresponding to the n1 parameter variable is combined with the historical data corresponding to the other parameter variables to obtain the final test data. In addition, the historical data corresponding to the other parameter variables can be randomly sampled and then combined with each in-group test result corresponding to the n1 parameter variable to obtain the final test data.
[0096] Example 4: {n1,n2,...,n s-1 ,1}、{n1,n2,...,n s-1 ,n s} indicates that multiple or all parameter variables are lists. The method in Example 1 can be used to group the historical data corresponding to each list-type parameter variable to obtain the in-group test data of each list-type parameter variable. Afterwards, the in-group test data of each list-type parameter variable is combined to generate the final test data. Alternatively, before grouping the historical data corresponding to each list-type parameter variable, the historical data of some or all of the list-type parameter variables can be randomly sampled to generate the final test data based on the combination of historical data obtained by random sampling. For example, when the distribution discreteness of the historical data of a certain list-type parameter variable is greater than or equal to a preset threshold, all the historical data of the list-type parameter variable are retained. When the distribution discreteness of the historical data of a certain list-type parameter variable is less than the preset threshold, the historical data of the list-type parameter variable is randomly sampled. This ensures the coverage of the test data and avoids the omission of test scenarios. In the present application, the first prompt word of the large model is generated based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining the numerical value of the parameter variable of the where clause in the SQL to be tested, and when the amount of data in the first target SQL related table in the target database is greater than the preset threshold, a random sampling condition is added to the first target SQL to generate a second target SQL, and then the second target SQL is executed. When the execution result of the second target SQL is abnormal, the second target SQL is corrected according to the type of the abnormal result to obtain the third target SQL, and then the third target SQL is executed to obtain the test data of the parameter variable of the where clause in the SQL to be tested. Thus, by rewriting the SQL to be tested through the large model and verifying the execution efficiency and reliability of the first target SQL, a variety of databases can be adapted, which improves the versatility, reliability and efficiency of test data generation. In addition, based on the third target SQL finally obtained, the test data of the parameter variable of the where clause of the SQL to be tested is obtained from the historical data, which improves the accuracy of generating test data.
[0097] In order to implement the above embodiment, the present application also proposes a SQL where clause parameter variable test data acquisition device.
[0098] Figure 2 A structural diagram of a SQL where clause parameter variable test data acquisition device provided in an embodiment of the present application.
[0099] like Figure 2 As shown, the SQL where clause parameter variable test data acquisition device includes a generation module 210, a rewriting module 220, a verification module 230, and an acquisition module 240.
[0100] A generating module 210 is used to generate a first prompt word of a large model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining a value of a parameter variable of a where clause in the SQL to be tested;
[0101] The rewriting module 220 is used to add a random sampling condition to the first target SQL to generate a second target SQL when the amount of data in the table related to the first target SQL in the target database is greater than a preset threshold;
[0102] The verification module 230 is used to execute the second target SQL, and if the execution result of the second target SQL is abnormal, modify the second target SQL according to the type of the abnormal result to obtain the third target SQL;
[0103] The acquisition module 240 is used to execute the third target SQL and acquire the test data of the parameter variables of the where clause in the SQL to be tested.
[0104] Furthermore, in a possible implementation of the embodiment of the present application, the generating module 210 is used to:
[0105] A first prompt word of a large model is generated based on the SQL to be tested, preset business constraints, and target database type, so as to rewrite the SQL to be tested using the large model to generate a first target SQL set.
[0106] Furthermore, in a possible implementation of the embodiment of the present application, the generating module 210 is used to:
[0107] Generate attribute information prompt words to identify the table name, column name, and parameter type corresponding to the parameter variable;
[0108] Generate condition acquisition prompt words to obtain relevant condition expressions that do not contain parameter variables in the SQL to be tested;
[0109] Generate table relationship generation prompt words to obtain table association clauses in the SQL to be tested;
[0110] Based on the grammatical rules corresponding to the target database type, the table name, column name, and conditional expression are concatenated to generate a first target SQL set.
[0111] Furthermore, in a possible implementation of the embodiment of the present application, the acquisition module 240 is used to:
[0112] When the parameter type of the parameter variable is a list, execute the third target SQL to obtain historical data;
[0113] Randomly sample historical data based on the number of list elements corresponding to the parameter variable to obtain test data corresponding to the parameter variable.
[0114] It should be noted that the above explanation of the SQL where clause parameter variable test data acquisition method embodiment is also applicable to the SQL where clause parameter variable test data acquisition device of this embodiment, and will not be repeated here.
[0115] In the present application, the first prompt word of the large model is generated based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining the numerical value of the parameter variable of the where clause in the SQL to be tested, and when the amount of data in the first target SQL related table in the target database is greater than the preset threshold, a random sampling condition is added to the first target SQL to generate a second target SQL, and then the second target SQL is executed. When the execution result of the second target SQL is abnormal, the second target SQL is corrected according to the type of the abnormal result to obtain the third target SQL, and then the third target SQL is executed to obtain the test data of the parameter variable of the where clause in the SQL to be tested. Thus, by rewriting the SQL to be tested through the large model and verifying the execution efficiency and reliability of the first target SQL, a variety of databases can be adapted, which improves the versatility, reliability and efficiency of test data generation. In addition, based on the third target SQL finally obtained, the test data of the parameter variable of the where clause of the SQL to be tested is obtained from the historical data, which improves the accuracy of generating test data.
[0116] In order to implement the above embodiments, the present application also proposes an electronic device, comprising: a processor, and a memory communicatively connected to the processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory to implement the method provided by the above embodiments.
[0117] In order to implement the above embodiments, the present application also proposes a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the methods provided by the above embodiments.
[0118] In order to implement the above embodiments, the present application also proposes a computer program product, including a computer program, which implements the methods provided by the above embodiments when executed by a processor.
[0119] The collection, storage, use, processing, transmission, provision and disclosure of user personal information involved in this application are in compliance with relevant laws and regulations and do not violate public order and good morals.
[0120] It should be noted that personal information from users should be collected for legitimate and reasonable purposes and should not be shared or sold outside of these legitimate uses. In addition, such collection / sharing should be carried out after receiving the user's informed consent, including but not limited to notifying the user to read the user agreement / user notice and sign the agreement / authorization including authorization of relevant user information before the user uses the function. In addition, any necessary steps should be taken to protect and safeguard access to such personal information data and ensure that others who have access to personal information data comply with its privacy policy and procedures.
[0121] This application is expected to provide an implementation scheme for users to selectively block the use or access of personal information data. That is, this application is expected to provide hardware and / or software to prevent or block access to such personal information data. Once the personal information data is no longer needed, risks can be minimized by limiting data collection and deleting data. In addition, when applicable, personal identifiers are removed from such personal information to protect the privacy of users.
[0122] In the description of the aforementioned embodiments, the description with reference to the terms "one embodiment", "some embodiments", "example", "specific example", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described may be combined in any one or more embodiments or examples in a suitable manner. In addition, those skilled in the art may combine and combine the different embodiments or examples described in this specification and the features of the different embodiments or examples, without contradiction.
[0123] In addition, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include at least one of the features. In the description of this application, the meaning of "plurality" is at least two, such as two, three, etc., unless otherwise clearly and specifically defined.
[0124] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, fragment or portion of code comprising one or more executable instructions for implementing the steps of a custom logical function or process, and the scope of the preferred embodiments of the present application includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in the reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present application belong.
[0125] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute the instructions), or in combination with these instruction execution systems, devices or apparatuses. For the purposes of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in combination with these instruction execution systems, devices or apparatuses. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium and then editing, interpreting or processing in other suitable ways if necessary, and then stored in a computer memory.
[0126] It should be understood that the various parts of the present application can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.
[0127] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.
[0128] In addition, each functional unit in each embodiment of the present application may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.
[0129] The storage medium mentioned above may be a read-only memory, a magnetic disk or an optical disk, etc. Although the embodiments of the present application have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limiting the present application. A person of ordinary skill in the art may change, modify, replace and modify the above embodiments within the scope of the present application.
Claims
1. A method for acquiring SQL where clause parameter variable test data, characterized in that: The method comprises: Generate a first prompt word of the big model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the big model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining a value of a parameter variable of a where clause in the SQL to be tested; When the amount of data in the first target SQL related table in the target database is greater than a preset threshold, adding a random sampling condition to the first target SQL to generate a second target SQL; Execute the second target SQL, and if the execution result of the second target SQL is abnormal, modify the second target SQL according to the type of the abnormal result to obtain the third target SQL; Execute the third target SQL to obtain test data of the parameter variables of the where clause in the SQL to be tested.
2. The method according to claim 1, characterized in that The first prompt word of the large model is generated based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set, including: A first prompt word of a large model is generated based on the SQL to be tested, preset business constraints, and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set.
3. The method according to claim 1, characterized in that The first prompt word of the large model is generated based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set, including: Generate attribute information prompt words for identifying the table name, column name, and parameter type corresponding to the parameter variable; Generate a condition acquisition prompt word for acquiring a related condition expression that does not contain the parameter variable in the SQL to be tested; Generate a table relationship generation prompt word, which is used to obtain the table association clause in the SQL to be tested; The table name, the column, and the conditional expression are concatenated based on the grammatical rules corresponding to the target database type to generate the first target SQL set.
4. The method according to claim 1, characterized in that The executing the third target SQL to obtain the test data of the parameter variable of the where clause in the SQL to be tested includes: When the parameter type of the parameter variable is a list, executing the third target SQL to obtain historical data; The historical data is randomly sampled based on the number of list elements corresponding to the parameter variable to obtain test data corresponding to the parameter variable.
5. A device for acquiring SQL where clause parameter variable test data, characterized in that: The device comprises: A generation module, used to generate a first prompt word of a large model based on the SQL to be tested and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set, wherein the first target SQL set includes a first target SQL for obtaining a value of a parameter variable of a where clause in the SQL to be tested; A rewriting module, configured to add a random sampling condition to the first target SQL to generate a second target SQL when the amount of data in the first target SQL related table in the target database is greater than a preset threshold; A verification module, configured to execute the second target SQL, and if the execution result of the second target SQL is abnormal, modify the second target SQL according to the type of the abnormal result to obtain a third target SQL; The acquisition module is used to execute the third target SQL and obtain the test data of the parameter variable of the where clause in the SQL to be tested.
6. The device according to claim 5, characterized in that The generating module is used for: A first prompt word of a large model is generated based on the SQL to be tested, preset business constraints, and the target database type, so as to rewrite the SQL to be tested by using the large model to generate a first target SQL set.
7. The device according to claim 5, characterized in that The generating module is used for: Generate attribute information prompt words for identifying the table name, column name, and parameter type corresponding to the parameter variable; Generate a condition acquisition prompt word for acquiring a related condition expression that does not contain the parameter variable in the SQL to be tested; Generate a table relationship generation prompt word, which is used to obtain the table association clause in the SQL to be tested; The table name, the column, and the conditional expression are concatenated based on the grammatical rules corresponding to the target database type to generate the first target SQL set.
8. The device according to claim 5, characterized in that The acquisition module is used to: When the parameter type of the parameter variable is a list, executing the third target SQL to obtain historical data; The historical data is randomly sampled based on the number of list elements corresponding to the parameter variable to obtain test data corresponding to the parameter variable.
9. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 4.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 4 when executed by a processor.
11. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 4 when being executed by a processor.