Sql statement testing method and device, electronic equipment and storage medium
By extracting subquery statements from SQL statements and generating test result data, the problem of time-consuming SQL statement testing methods is solved, enabling rapid location of fault information and improving development efficiency.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING QIYI CENTURY SCI & TECH CO LTD
- Filing Date
- 2022-07-06
- Publication Date
- 2026-07-07
Smart Images
Figure CN115309768B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of computer technology, and in particular to a method, apparatus, electronic device and storage medium for testing SQL statements. Background Technology
[0002] In this era of information explosion, all industries generate a large amount of data in their daily operations, thus creating numerous data development tasks.
[0003] Currently, data development tasks are generally implemented using SQL (Structured Query Language) statements. During data development or online debugging, SQL statements are tested to test the development data. Existing SQL statement testing methods mostly involve testing SQL statements through platforms with testing capabilities, such as different databases or script libraries.
[0004] However, the above SQL statement test process is quite lengthy, and when a SQL statement fails, it takes a lot of time to locate the fault information, especially for more complex SQL statements. Summary of the Invention
[0005] In view of this, in order to solve the technical problem that it takes a long time to locate the fault information of SQL statements, the present invention provides an SQL statement testing method, device, electronic device and storage medium.
[0006] In a first aspect, embodiments of the present invention provide a method for testing SQL statements, the method comprising:
[0007] Extract at least one subquery statement from the SQL statement to be tested;
[0008] Execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements;
[0009] Based on at least one of the first query results, test result data corresponding to the SQL statement is generated, so as to locate the fault information of the SQL statement based on the test result data.
[0010] In one possible implementation, extracting at least one subquery statement from the SQL statement to be tested includes:
[0011] Generate a syntax tree for the SQL statement to be tested, wherein each child node of the syntax tree corresponds to a subquery statement;
[0012] Extract at least one subquery statement from the syntax tree.
[0013] In one possible implementation, executing at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements includes:
[0014] In the case where there are multiple subquery statements in the SQL statement, determine the dependencies between the multiple subquery statements;
[0015] Execute multiple subquery statements sequentially according to the aforementioned dependency relationship to obtain the first query result corresponding to each subquery statement.
[0016] In one possible implementation, the sequential execution of multiple sub-query statements according to the dependency relationship includes:
[0017] Based on the dependencies, a query tree corresponding to the SQL statement is generated, where the root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery statement.
[0018] The leaf nodes in the query tree are traversed sequentially from bottom to top, and the subquery statements corresponding to the leaf nodes are executed.
[0019] In one possible implementation, generating test result data corresponding to the SQL statement based on at least one of the first query results includes:
[0020] Replace the subquery statement corresponding to each leaf node in the query tree with the first query result corresponding to that subquery statement;
[0021] The replaced query tree is then used to determine the test result data corresponding to the SQL statement.
[0022] Secondly, embodiments of the present invention provide an SQL statement testing device, the device comprising:
[0023] The extraction module is used to extract at least one subquery statement from the SQL statement to be tested;
[0024] The execution module is configured to execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements;
[0025] A generation module is used to generate test result data corresponding to the SQL statement based on at least one of the first query results, so as to locate the fault information of the SQL statement based on the test result data.
[0026] In one possible implementation, the extraction module is specifically used for:
[0027] Generate a syntax tree for the SQL statement to be tested, wherein each child node of the syntax tree corresponds to a subquery statement;
[0028] Extract at least one subquery statement from the syntax tree.
[0029] In one possible implementation, the execution module includes:
[0030] The determination submodule is used to determine the dependencies between multiple subquery statements when there are multiple subquery statements in the SQL statement;
[0031] The execution submodule is used to execute multiple subquery statements sequentially according to the dependency relationship, and obtain the first query result corresponding to each subquery statement.
[0032] In one possible implementation, the execution submodule is specifically used for:
[0033] Based on the dependencies, a query tree corresponding to the SQL statement is generated, where the root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery statement.
[0034] The leaf nodes in the query tree are traversed sequentially from bottom to top, and the subquery statements corresponding to the leaf nodes are executed.
[0035] In one possible implementation, the generation module is specifically used for:
[0036] Replace the subquery statement corresponding to each leaf node in the query tree with the first query result corresponding to that subquery statement;
[0037] The replaced query tree is then used to determine the test result data corresponding to the SQL statement.
[0038] Thirdly, embodiments of the present invention provide an electronic device, including: a processor and a memory, wherein the processor is configured to execute an SQL statement testing program stored in the memory to implement the SQL statement testing method described in any one of the first aspects.
[0039] Fourthly, embodiments of the present invention provide a storage medium storing one or more programs, which can be executed by one or more processors to implement the SQL statement testing method described in any one aspect.
[0040] The technical solution provided by this invention extracts at least one subquery statement from the SQL statement to be tested; executes the at least one subquery statement to obtain a first query result corresponding to the at least one subquery statement; and generates test result data corresponding to the SQL statement based on the at least one first query result, so as to locate the fault information of the SQL statement based on the test result data. In this technical solution, by extracting and executing subquery statements from the SQL statement, test result data corresponding to the SQL statement is obtained, and fault information of the SQL statement is located based on the test result data. This saves time in locating fault information of the SQL statement, thereby achieving rapid location of fault information of the SQL statement. Attached Figure Description
[0041] Figure 1 A flowchart illustrating an embodiment of an SQL statement testing method provided by this invention;
[0042] Figure 2 A flowchart illustrating an embodiment of another SQL statement testing method provided by this invention;
[0043] Figure 3 This is a schematic diagram of a query tree structure provided in an embodiment of the present invention;
[0044] Figure 4 A flowchart illustrating another embodiment of the SQL statement testing method provided by this invention;
[0045] Figure 5 A block diagram illustrating an embodiment of an SQL statement testing device provided by this invention;
[0046] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation
[0047] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.
[0048] The SQL statement testing method provided by the present invention will be further explained and described below with reference to the accompanying drawings and specific embodiments. The embodiments do not constitute a limitation on the embodiments of the present invention.
[0049] See Figure 1This is a flowchart illustrating an embodiment of an SQL statement testing method provided by this invention. Figure 1 As shown, the process may include the following steps:
[0050] Step 101: Extract at least one subquery statement from the SQL statement to be tested.
[0051] Step 102: Execute at least one subquery statement to obtain the first query result corresponding to at least one subquery statement.
[0052] The following provides a unified explanation of steps 101 and 102:
[0053] The SQL statement to be tested can be any SQL statement corresponding to any node in the data development task. The data development task can have at least one node, and each node can correspond to one SQL statement. The SQL statement can include at least one subquery statement, and can also include modification statements, insertion statements, deletion statements, etc., which are not limited in this embodiment of the invention. For example, the SQL statement is: “select X.*from(select xxx from table_1A join table_2B onA.id=B.id)X join table_3Y on X.id=Y.id”. In this SQL statement, there is a subquery statement “select xxx from table_1A” and an insertion statement “join table_2B on A.id=B.id”.
[0054] In one embodiment, when testing the SQL statement corresponding to any node in the data development task, if the SQL statement has at least one subquery statement, at least one subquery statement can be extracted from the SQL statement first.
[0055] As an optional implementation, a syntax tree of the SQL statement to be tested can be generated. Each child node in the syntax tree can correspond to a subquery statement. The execution entity of this embodiment can extract at least one subquery statement from the syntax tree.
[0056] Optionally, when there is no dependency between the above at least one subquery statement, the above at least one subquery statement can be executed directly to obtain the first query result corresponding to the at least one subquery statement.
[0057] Conversely, when at least one of the above subquery statements constitutes multiple subquery statements, and a dependency relationship is determined between these multiple subquery statements, the multiple subquery statements can be executed sequentially according to the aforementioned dependency relationship to obtain the first query result corresponding to each subquery statement. The specific method for executing multiple subquery statements sequentially according to dependency relationships will be explained below. Figure 2 The process shown will be explained in detail here.
[0058] Step 103: Based on at least one first query result, generate test result data corresponding to the SQL statement, so as to locate the fault information of the SQL statement based on the test result data.
[0059] The error information of the above SQL statement may be a syntax error in the SQL statement or an error in the data table corresponding to the SQL statement. This embodiment of the invention does not limit this.
[0060] In one embodiment, when an SQL statement contains multiple subquery statements and these subquery statements have dependencies on each other, the execution entity of this embodiment can generate dependency data between the first query results (hereinafter referred to as test result data) based on the dependencies and the first query results corresponding to the multiple subquery statements. Users can take snapshots of the test result data so that when a fault occurs in the SQL statement, the fault information can be located based on the test result data. The test result data can be in the form of a data table or a syntax tree; this embodiment does not limit the form. The specific method for generating test result data between the first query results based on dependencies and the first query results corresponding to the multiple subquery statements will be explained below. Figure 2 The process shown will be explained in detail here.
[0061] In another embodiment, when there are no dependencies among at least one subquery statement in the aforementioned SQL statement—that is, the SQL statement may include one or more subquery statements, but there are no dependencies between these multiple subquery statements—at least one subquery statement can be executed directly to obtain a first query result corresponding to at least one subquery statement. This first query result is then used as test result data, allowing the user to locate the fault information of the aforementioned SQL statement based on the test result data. The test result data can be in the form of a data table or a syntax tree; this embodiment of the invention does not limit the form.
[0062] To help understand how users locate SQL statement fault information based on test result data, the following example illustrates the process:
[0063] Suppose this SQL statement is used to query the math score of student "XX" based on the student's student ID. The SQL statement includes two subqueries: subquery A, which queries the student ID of XX based on the class name and the student's name; and subquery A1, which queries the class of the student based on the student's name.
[0064] Continuing with the assumption that the SQL statement testing method provided in this embodiment yields test result data of class 98-1012-A, and then, assuming the user checks the student information table in the database and finds that XX's math score is 95, it indicates a fault in the SQL statement. At this point, the user can check the data table again and find that XX's class is class B, thus pinpointing the fault information of the SQL statement as subquery A1. Furthermore, if the user determines that subquery A1 does not have a syntax error, it can be determined that there is a data error in the data table corresponding to subquery A1.
[0065] The technical solution provided by this invention extracts at least one subquery statement from the SQL statement to be tested; executes the at least one subquery statement to obtain a first query result corresponding to the at least one subquery statement; and generates test result data corresponding to the SQL statement based on the at least one first query result, so as to locate the fault information of the SQL statement based on the test result data. In this technical solution, by extracting and executing subquery statements from the SQL statement, test result data corresponding to the SQL statement is obtained, and fault information of the SQL statement is located based on the test result data. This saves time in locating fault information of the SQL statement, thereby achieving rapid location of fault information of the SQL statement.
[0066] Furthermore, since the SQL statement testing method provided in this embodiment of the invention can quickly locate the fault information of the SQL statement, it can be quickly debugged during the data task development process, thereby improving the efficiency of data task development. Moreover, since there is no need to manually split and combine the SQL language, it can ensure that the same set of SQL code is used for development and deployment, thus avoiding problems during the deployment process.
[0067] See Figure 2 This is a flowchart illustrating an embodiment of another SQL statement testing method provided by the present invention. The flowchart is... Figure 1 Based on the illustrated process, this section focuses on describing how to generate test result data for a SQL statement when it contains multiple subqueries with dependencies between them. For example... Figure 2 As shown, the process may include the following steps:
[0068] Step 201: If there are multiple subquery statements in the SQL statement to be tested, and there are dependencies between the multiple subquery statements, extract the multiple subquery statements from the SQL statement to be tested.
[0069] Step 202: Determine the dependencies between multiple subquery statements.
[0070] The following provides a unified explanation of steps 201 and 202:
[0071] The SQL statement to be tested may contain one subquery statement or multiple subquery statements. There may or may not be a dependency relationship between the multiple subquery statements. This embodiment of the invention does not impose any restrictions on this.
[0072] In one embodiment, before testing the SQL statement to be tested, the executing entity of this embodiment of the invention may first determine whether there are multiple subquery statements in the SQL statement. If there are multiple subquery statements in the SQL statement and there are dependencies between the multiple subquery statements, then the multiple subquery statements are extracted from the SQL statement to be tested, and the dependencies between the multiple subquery statements are determined.
[0073] Step 203: Generate a query tree corresponding to the SQL statement based on the dependency relationship. The root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery statement.
[0074] Step 204: Traverse the leaf nodes in the query tree in a bottom-up order and execute the subquery statements corresponding to the leaf nodes traversed.
[0075] The following provides a unified explanation of steps 203 and 204:
[0076] In one embodiment, a query tree corresponding to an SQL statement can be generated based on the above dependencies. The query tree may include the SQL statement and its subqueries. That is, the root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery.
[0077] For example, see Figure 3 This is a schematic diagram of a query tree structure provided in an embodiment of the present invention, such as... Figure 3 As shown, the root node of the query tree is the SQL statement to be tested mentioned above. The SQL statement may include subquery statement A, subquery statement B, and subquery statement C. Subquery statement A includes subquery statement A1 and subquery statement A2; subquery statement C includes subquery statement C1.
[0078] Optionally, the leaf nodes in the query tree can be traversed sequentially from bottom to top, and the subquery statements corresponding to the traversed leaf nodes can be executed to obtain the first query results of multiple subquery statements.
[0079] Step 205: Replace the subquery statement corresponding to each leaf node in the query tree with the first query result corresponding to that subquery statement.
[0080] Step 206: Determine the replaced query tree as the test result data corresponding to the SQL statement, so as to locate the fault information of the SQL statement based on the test result data.
[0081] The following provides a unified explanation of steps 205 and 206:
[0082] As can be seen from the description of steps 203 and 204 above, after the execution subject of this embodiment of the invention extracts multiple subquery statements from the SQL statement to be tested and determines the dependency relationship between the multiple subquery statements, it can generate a query tree corresponding to the above SQL statement, traverse the leaf nodes in the query tree in a bottom-up order, and execute the subquery statements corresponding to the traversed leaf nodes to obtain the first query result.
[0083] Optionally, since the query tree can represent the dependencies between the subquery statements, the subquery statement corresponding to each leaf node in the query tree can be directly replaced with the first query result corresponding to that subquery statement, thereby obtaining the test result data corresponding to the SQL statement.
[0084] Specifically, as a possible implementation, after each leaf node in the query tree executes the corresponding subquery statement, the subquery statement can be directly replaced with the first query result. For example, assuming the subquery statement is "SELECT name FROM student" and the first query result after executing the subquery statement is "XX", then the subquery statement "SELECT name FROM student" can be directly replaced with XX.
[0085] As another possible implementation, a temporary table can be allocated for the subquery statement corresponding to each leaf node in the query tree. The subquery statement corresponding to each leaf node in the query tree can be replaced with the temporary table. After the first query result is obtained by executing the subquery statement, the first query result can be stored in the temporary table, thereby obtaining a query tree with a temporary table corresponding to each child node. The query tree can be used as test result data to locate the fault information of the SQL statement based on the test result data.
[0086] In one embodiment, the executing entity of this invention may specify the database name where the query tree is located, the data table name of the corresponding subquery statement, and the storage time of the temporary table, such as 3 days. That is, after 3 days, the temporary table can be deleted.
[0087] The technical solution provided by this invention extracts multiple subquery statements from the SQL statement under test, given that these subquery statements have dependencies on each other. The dependencies between these subquery statements are then determined. Based on these dependencies, a query tree corresponding to the SQL statement is generated. The root node of the query tree corresponds to the SQL statement, and each leaf node corresponds to a subquery statement. The leaf nodes in the query tree are traversed sequentially from bottom to top, and the subquery statements corresponding to the traversed leaf nodes are executed. Then, the subquery statement corresponding to each leaf node in the query tree is replaced with the first query result corresponding to that subquery statement, and the resulting query tree is used as the test result data for the SQL statement. In this technical solution, by replacing the leaf nodes in the query tree corresponding to the SQL statement with the first query result corresponding to that subquery statement, test result data is obtained between the first query results corresponding to the subquery statements of the SQL statement. This allows for the location of fault information in the SQL statement based on the test result data. This saves time in locating fault information in the SQL statement, thus achieving rapid fault location.
[0088] See Figure 4 This is a flowchart illustrating another embodiment of the SQL statement testing method provided by this invention. Figure 4 As shown, the process may include the following:
[0089] First, generate a syntax tree from the SQL statement to be tested, and determine whether a subquery statement exists in the syntax tree. If so, extract the subquery statement from the SQL statement and generate a corresponding query tree. Optionally, traverse the query tree sequentially from bottom to top and execute the subquery statements encountered during the traversal.
[0090] Afterwards, a temporary table can be used to replace the above subquery statement with the first query result of the subquery statement (the process of using a temporary table to replace the above subquery statement with the first query result of the subquery statement can be found in the description of steps 205 and 206 above, and will not be repeated here), and finally the test result data is obtained, and the fault information of the SQL statement can be located based on the test result data.
[0091] Conversely, if there is no subquery statement in the syntax tree, there is no need to generate a query tree. The SQL statement can be executed directly to obtain the first query result, and the fault information of the SQL statement can be located based on the first query result.
[0092] The technical solution provided by this invention determines whether a subquery statement exists in the SQL statement to be tested. If multiple subqueries exist, a query tree of these subqueries is generated. The leaf nodes of the query tree are traversed sequentially from bottom to top, and the subqueries corresponding to each leaf node are executed. Then, the subquery statement corresponding to each leaf node is replaced with the first query result corresponding to that subquery statement. The resulting query tree is then used as the test result data for the SQL statement. If no subqueries are extracted from the SQL statement to be tested, the SQL statement is executed to obtain the first query result, which is used to locate the SQL statement's fault information. In this technical solution, by replacing the leaf nodes of the query tree corresponding to the SQL statement with the first query result corresponding to that subquery statement, test result data between the first query results corresponding to the subqueries of the SQL statement is obtained, and the fault information of the SQL statement is located based on the test result data. This saves time in locating the fault information of the SQL statement, thus achieving rapid location of SQL statement fault information.
[0093] See Figure 5 This is a block diagram of an embodiment of an SQL statement testing device provided by an embodiment of the present invention. Figure 5 As shown, the device includes:
[0094] Extraction module 51 is used to extract at least one subquery statement from the SQL statement to be tested;
[0095] Execution module 52 is used to execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements;
[0096] The generation module 53 is used to generate test result data corresponding to the SQL statement based on at least one of the first query results, so as to locate the fault information of the SQL statement based on the test result data.
[0097] In one possible implementation, the extraction module 51 is specifically used for:
[0098] Generate a syntax tree for the SQL statement to be tested, wherein each child node of the syntax tree corresponds to a subquery statement;
[0099] Extract at least one subquery statement from the syntax tree.
[0100] In one possible implementation, the execution module 52 includes (not shown in the figure):
[0101] The determination submodule is used to determine the dependencies between multiple subquery statements when there are multiple subquery statements in the SQL statement;
[0102] The execution submodule is used to execute multiple subquery statements sequentially according to the dependency relationship, and obtain the first query result corresponding to each subquery statement.
[0103] In one possible implementation, the execution submodule is specifically used for:
[0104] Based on the dependencies, a query tree corresponding to the SQL statement is generated, where the root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery statement.
[0105] The leaf nodes in the query tree are traversed sequentially from bottom to top, and the subquery statements corresponding to the leaf nodes are executed.
[0106] In one possible implementation, the generation module 53 is specifically used for:
[0107] Replace the subquery statement corresponding to each leaf node in the query tree with the first query result corresponding to that subquery statement;
[0108] The replaced query tree is then used to determine the test result data corresponding to the SQL statement.
[0109] Figure 6 This is a schematic diagram of the structure of an electronic device provided in an embodiment of the present invention. Figure 6 The illustrated electronic device 600 includes at least one processor 601, a memory 602, at least one network interface 604, and a user interface 603. The various components in the electronic device 600 are coupled together via a bus system 605. It is understood that the bus system 605 is used to implement communication between these components. In addition to a data bus, the bus system 605 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in… Figure 6 The general designated all buses as Bus System 605.
[0110] The user interface 603 may include a display, keyboard, or clicking device (e.g., mouse, trackball, touchpad, or touchscreen).
[0111] It is understood that the memory 602 in this embodiment of the invention can be volatile memory or non-volatile memory, or may include both volatile and non-volatile memory. The non-volatile memory can be read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), or flash memory. The volatile memory can be random access memory (RAM), which is used as an external cache. By way of example, but not limitation, many forms of RAM are available, such as Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), Synchronous DRAM (SDRAM), Double Data Rate Synchronous DRAM (DDRSDRAM), Enhanced Synchronous DRAM (ESDRAM), Synchlink DRAM (SLDRAM), and Direct Rambus RAM (DRRAM). The memory 602 described herein is intended to include, but is not limited to, these and any other suitable types of memory.
[0112] In some implementations, memory 602 stores elements, executable units or data structures, or subsets thereof, or extended sets thereof: operating system 6021 and application program 6022.
[0113] The operating system 6021 includes various system programs, such as the framework layer, core library layer, and driver layer, used to implement various basic business functions and handle hardware-based tasks. The application program 6022 includes various applications, such as a media player and a browser, used to implement various application functions. The program implementing the method of this embodiment can be included in the application program 6022.
[0114] In this embodiment of the invention, by calling the program or instructions stored in memory 602, specifically the program or instructions stored in application program 6022, processor 601 executes the method steps provided in each method embodiment, including, for example:
[0115] Extract at least one subquery statement from the SQL statement to be tested;
[0116] Execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements;
[0117] Based on the first query result corresponding to at least one of the subquery statements, test result data corresponding to the SQL statement is generated, so as to locate the fault information of the SQL statement based on the test result data.
[0118] The methods disclosed in the above embodiments of the present invention can be applied to processor 601, or implemented by processor 601. Processor 601 may be an integrated circuit chip with signal processing capabilities. In the implementation process, each step of the above method can be completed by the integrated logic circuit of the hardware in processor 601 or by instructions in the form of software. The processor 601 may be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. It can implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of the present invention. The general-purpose processor may be a microprocessor or any conventional processor. The steps of the methods disclosed in the embodiments of the present invention can be directly embodied in the execution of a hardware decoding processor, or executed by a combination of hardware and software units in the decoding processor. The software units may be located in random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, or other mature storage media in the art. The storage medium is located in memory 602. Processor 601 reads the information in memory 602 and, in conjunction with its hardware, completes the steps of the above method.
[0119] It is understood that the embodiments described herein can be implemented in hardware, software, firmware, middleware, microcode, or a combination thereof. For hardware implementation, the processing unit can be implemented in one or more application-specific integrated circuits (ASICs), digital signal processors (DSPs), digital signal processing devices (DSPDs), programmable logic devices (PLDs), field-programmable gate arrays (FPGAs), general-purpose processors, controllers, microcontrollers, microprocessors, other electronic units for performing the functions described herein, or combinations thereof.
[0120] For software implementation, the techniques described herein can be implemented by units that perform the functions described herein. The software code can be stored in memory and executed by a processor. The memory can be implemented in the processor or external to the processor.
[0121] The electronic device provided in this embodiment may be as follows: Figure 6 The electronic device shown can perform the following: Figure 1 , Figure 2 and Figure 4 This involves all the steps of the SQL statement testing method, thereby achieving... Figure 1 , Figure 2 and Figure 4 For details on the technical effectiveness of the SQL statement testing method, please refer to [link / reference]. Figure 1 , Figure 2 and Figure 4 The relevant descriptions are presented concisely and will not be elaborated upon here.
[0122] This invention also provides a storage medium (computer-readable storage medium). This storage medium stores one or more programs. The storage medium may include volatile memory, such as random access memory; the memory may also include non-volatile memory, such as read-only memory, flash memory, hard disk, or solid-state drive; the memory may also include combinations of the above types of memory.
[0123] When one or more programs in the storage medium can be executed by one or more processors, the above-described SQL statement testing method executed on the electronic device side can be implemented.
[0124] The processor is used to execute the SQL statement test program stored in the memory to implement the following steps of the SQL statement test method executed on the electronic device side:
[0125] Extract at least one subquery statement from the SQL statement to be tested;
[0126] Execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements;
[0127] Based on the first query result corresponding to at least one of the subquery statements, test result data corresponding to the SQL statement is generated, so as to locate the fault information of the SQL statement based on the test result data.
[0128] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this invention.
[0129] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein can be implemented in hardware, a software module executed by a processor, or a combination of both. The software module can be located in random access memory (RAM), main memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, hard disk, removable disk, CD-ROM, or any other form of storage medium known in the art.
[0130] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above description is only a specific embodiment of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.
Claims
1. A method for testing SQL statements, characterized in that, The method includes: Extract at least one subquery statement from the SQL statement to be tested; Execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements; Based on at least one of the first query results, test result data corresponding to the SQL statement is generated to locate the fault information of the SQL statement based on the test result data; wherein, the fault information includes syntax errors of the SQL statement or data errors in the data table corresponding to the SQL statement; when there are multiple subquery statements in the SQL statement, the test result data is the query tree corresponding to the SQL statement, and the subquery statements corresponding to the leaf nodes in the query tree are replaced with the first query results corresponding to the subquery statements or a temporary table storing the first query results.
2. The method according to claim 1, characterized in that, Extracting at least one subquery statement from the SQL statement to be tested includes: Generate a syntax tree for the SQL statement to be tested, wherein each child node of the syntax tree corresponds to a subquery statement; Extract at least one subquery statement from the syntax tree.
3. The method according to claim 1, characterized in that, The execution of at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements includes: In the case where there are multiple subquery statements in the SQL statement, determine the dependencies between the multiple subquery statements; Execute multiple subquery statements sequentially according to the aforementioned dependency relationship to obtain the first query result corresponding to each subquery statement.
4. The method according to claim 3, characterized in that, The step of sequentially executing multiple sub-query statements according to the dependency relationship includes: Based on the dependencies, a query tree corresponding to the SQL statement is generated, where the root node of the query tree corresponds to the SQL statement, and each leaf node of the query tree corresponds to a subquery statement. The leaf nodes in the query tree are traversed sequentially from bottom to top, and the subquery statements corresponding to the leaf nodes are executed.
5. An SQL statement testing device, characterized in that, The device includes: The extraction module is used to extract at least one subquery statement from the SQL statement to be tested; The execution module is configured to execute at least one of the subquery statements to obtain a first query result corresponding to at least one of the subquery statements; A generation module is configured to generate test result data corresponding to the SQL statement based on at least one of the first query results, so as to locate the fault information of the SQL statement based on the test result data; wherein, the fault information includes syntax errors of the SQL statement or data errors in the data table corresponding to the SQL statement; when there are multiple subquery statements in the SQL statement, the test result data is a query tree corresponding to the SQL statement, and the subquery statements corresponding to the leaf nodes in the query tree are replaced with the first query results corresponding to the subquery statements or a temporary table storing the first query results.
6. The apparatus according to claim 5, characterized in that, The extraction module is specifically used for: Generate a syntax tree for the SQL statement to be tested, wherein each child node of the syntax tree corresponds to a subquery statement; Extract at least one subquery statement from the syntax tree.
7. The apparatus according to claim 5, characterized in that, The execution module includes: The determination submodule is used to determine the dependencies between multiple subquery statements when there are multiple subquery statements in the SQL statement; The execution submodule is used to execute multiple subquery statements sequentially according to the dependency relationship, and obtain the first query result corresponding to the multiple subquery statements.
8. An electronic device, characterized in that, include: A processor and a memory, the processor being configured to execute an SQL statement test program stored in the memory to implement the SQL statement test method according to any one of claims 1 to 4.
9. A storage medium, characterized in that, The storage medium stores one or more programs, which can be executed by one or more processors to implement the SQL statement testing method according to any one of claims 1 to 4.