A method, device, equipment and storage medium for fast debugging of SQL statements

Abstract

The present invention relates to the field of software testing technology, and specifically to a method, device, equipment, and storage medium for rapid debugging of SQL statements, the method comprising: obtaining a data definition language from a database management system; obtaining an SQL statement to be debugged, and if the SQL statement sets a variable mapping, automatically mapping fields and data, and replacing the corresponding data with variables; determining whether the SQL statement is an Insert statement, and if so, performing a full format specification check on the SQL statement according to a pre-stored Java script specification; if not, performing a marker check in the format specification check on the SQL statement; and outputting a check result, the check result including all non-standard or erroneous location information. It can be understood that the technical solution shown in the present invention can automatically perform variable mapping and format specification check, assisting users in debugging SQL statements, with simple operation and improved work efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of software testing, and in particular to a method, device, equipment and storage medium for rapid debugging of SQL statements. Background Art

[0002] Currently, software testers usually write and debug SQL statements manually. Manual SQL statement testing often suffers from the following problems: it is time-consuming, error messages are inaccurate, and efficiency is low.

[0003] A lot of time is often wasted when automated scripts add, delete, modify, and query databases. This is especially true when inserting data, which often contains variable fields such as IDs, dates, and amounts. This also requires that when writing SQL, variables must correspond one-to-one with columns and fields. This becomes difficult when there are too many fields in the database table. Inaccurate error messages are often encountered when debugging SQL, prompting messages about missing commas, quotation marks, and incorrect field types. The problem is that the prompts are neither precise nor easy to understand, requiring users to spend considerable time reading and understanding the long string of error messages, which is quite inefficient for users with poor English proficiency. Inefficiency is also reflected in the fact that although error messages are provided, they are not given all at once (this refers to when there are multiple irregularities or errors in the SQL statement). This means that when the user makes changes and executes the statement again, they will find a new error message, which repeats itself, resulting in low efficiency.

[0004] Therefore, the current testing and debugging methods for SQL statements are inconvenient to operate and have low efficiency. Summary of the Invention

[0005] In view of this, the purpose of the present invention is to provide a method, device, equipment and storage medium for rapid debugging of SQL statements to solve the problems of inconvenient operation and low efficiency in the existing testing and debugging methods for SQL statements.

[0006] According to a first aspect of an embodiment of the present invention, a method for quickly debugging an SQL statement is provided, comprising:

[0007] Obtain data definition language from database management systems;

[0008] Get the SQL statement to be debugged. If the SQL statement has variable mapping set, automatically map the fields and data and replace the corresponding data with variables.

[0009] Determine whether the SQL statement is an Insert statement. If so, perform a full format specification check on the SQL statement according to a pre-stored Java script specification. If not, perform a marker check on the SQL statement in the format specification check.

[0010] Output the verification result, which includes all non-standard or erroneous location information.

[0011] Preferably, after outputting the verification result, the method further comprises:

[0012] If the verification result is error-free, the SQL statement is executed with IDEA+Java to implement addition, deletion, modification and query of the database.

[0013] Preferably, after obtaining the data definition language from the database management system, the method further includes:

[0014] The data definition language is uploaded and backed up.

[0015] According to a second aspect of an embodiment of the present invention, there is provided a device for quickly debugging SQL statements, comprising:

[0016] DDL acquisition module, used to obtain data definition language from the database management system;

[0017] An SQL statement acquisition module is used to acquire the SQL statement to be debugged. If the SQL statement is set with variable mapping, it automatically maps fields and data and replaces the corresponding data with variables.

[0018] A verification module, configured to determine whether the SQL statement is an Insert statement; if so, perform a full format specification check on the SQL statement according to a pre-stored Java script specification; if not, perform a marker check on the SQL statement in the format specification check;

[0019] The output module is used to output the verification result, which includes all non-standard or erroneous position information.

[0020] Preferably, the device further comprises:

[0021] The execution module is used to execute the SQL statement with IDEA+Java if the verification result is error-free, so as to realize the addition, deletion, modification and query of the database.

[0022] Preferably, the device further comprises:

[0023] The upload import module is used to upload and back up the data definition language.

[0024] Preferably, the verification result also includes all the irregular or erroneous modification prompts.

[0025] According to a third aspect of an embodiment of the present invention, a device for quickly debugging SQL statements is provided, comprising:

[0026] A main controller, and a memory connected to the main controller;

[0027] The memory stores program instructions;

[0028] The main controller is used to execute program instructions stored in the memory and perform any of the above methods.

[0029] According to a fourth aspect of an embodiment of the present invention, a computer-readable storage medium is provided, storing a computer program, wherein when the computer program is executed by a processor, the computer program implements any of the above methods.

[0030] The technical solutions provided by the embodiments of the present invention may have the following beneficial effects:

[0031] It is understood that the technical solution shown in the present invention can obtain a data definition language from a database management system; obtain the SQL statement to be debugged, and if the SQL statement sets a variable mapping, automatically map the fields to the data and replace the corresponding data with the variables; determine whether the SQL statement is an Insert statement; if so, perform a full format specification check on the SQL statement according to the pre-stored Java script specification; if not, perform a marker check on the SQL statement in the format specification check; and output the check result, which includes all non-standard or erroneous location information. It is understood that the technical solution shown in the present invention can automatically perform variable mapping and format specification check, assisting users in debugging SQL statements, simplifying the operation, and improving work efficiency.

[0032] It is to be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0034] Figure 1 This is a schematic diagram showing the steps of a method for quickly debugging SQL statements according to an exemplary embodiment;

[0035] Figure 2 This is a schematic diagram showing an application of a SQL statement quick debugging tool according to an exemplary embodiment;

[0036] Figure 3 This is a schematic diagram of a display interface of a SQL statement quick debugging tool according to an exemplary embodiment;

[0037] Figure 4 This is a flowchart of an operation of a SQL statement quick debugging tool according to an exemplary embodiment;

[0038] Figure 5 The figure is a schematic block diagram of a device for fast debugging SQL statements according to an exemplary embodiment. DETAILED DESCRIPTION

[0039] Exemplary embodiments will be described in detail herein, examples of which are illustrated in the accompanying drawings. In the following description, when referring to the drawings, like numbers in different figures represent like or similar elements unless otherwise indicated. The embodiments described in the following exemplary embodiments are not intended to represent all possible embodiments consistent with the present invention. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present invention, as detailed in the appended claims.

[0040] Example 1

[0041] Figure 1 This is a schematic diagram of a method for quickly debugging SQL statements according to an exemplary embodiment. Figure 1 , provides a method for fast debugging SQL statements, including:

[0042] Step S11: Obtaining data definition language from a database management system;

[0043] It should be noted that, after obtaining the data definition language from the database management system, the following steps are also included:

[0044] The data definition language is uploaded and backed up.

[0045] In practice, DDL (Data Definition Language) is exported through the DBMS (Database Management System), and the DDL information is uploaded and backed up by the import function of sqlTool. At this time, sqlTool obtains information such as the type, length, and default value of the library table fields.

[0046] Step S12: Obtain the SQL statement to be debugged. If the SQL statement is set with variable mapping, automatically map the fields to the data and replace the corresponding data with variables.

[0047] In actual application, the staff can open the sqlTool tool (a tool that can execute this method) and paste the corresponding SQL statement so that the sqlTool tool can obtain the SQL statement to be debugged, and then determine the execution of two paths based on whether the variable mapping is set. If the variable is set, the background will automatically map the field and data and replace the corresponding data with the variable. Otherwise, it will go directly to the next step.

[0048] Step S13: Determine whether the SQL statement is an Insert statement. If so, perform a full format check on the SQL statement according to a pre-stored Java script specification. If not, perform a marker check on the SQL statement in the format check.

[0049] Determine the SQL type. If it is an Insert statement, check the SQL tags, types, lengths, default values, etc. one by one according to the Java script specification; if it is another type of statement, only check the tags.

[0050] Step S14: output the verification result, which includes all non-standard or erroneous location information.

[0051] Preferably, the irregular or erroneous position may be highlighted.

[0052] It should be noted that the database management system can correspond to MySQL or Oracle. MySQL is a relational database management system. Its small size, fast speed, and overall low cost, coupled with its open source nature, make it a popular choice for small, medium, and large websites as their website database. Oracle, on the other hand, is a relational database management software. This database features excellent portability, ease of use, and powerful performance. Many large websites have chosen the Oracle system, which is considered the world's best database product.

[0053] It is understood that the technical solution shown in this embodiment can obtain the data definition language from the database management system; obtain the SQL statement to be debugged, and if the SQL statement is set with variable mapping, automatically map the fields and data, and replace the corresponding data with variables; determine whether the SQL statement is an Insert statement, and if so, perform all format specifications verification on the SQL statement according to the pre-stored Java script specification; if not, perform the marker verification in the format specification verification on the SQL statement; and output the verification result, which includes all non-standard or erroneous location information. It is understood that the technical solution shown in this embodiment can automatically perform variable mapping and format specification verification, assist users in debugging SQL statements, simplify operation, and improve work efficiency.

[0054] In specific practice, the technical solution shown in this embodiment can be implemented by means of a tool, for example, the above method can be integrated into sqlTool to implement the above functions. Figure 2 1 is a schematic diagram illustrating an application of a SQL statement quick debugging tool according to an exemplary embodiment, wherein the sqlTool is the SQL statement quick debugging tool shown.

[0055] In actual applications, staff (Actor) can enter the data definition language (DLL) through the database management system (DBMS), and sqlTool obtains the data definition language from the database management system. After that, the user pastes the SQL statement to be debugged into sqlTool, and sqlTool performs functional operations on the SQL statement and generates a verification result. The staff debugs and modifies the SQL language according to the verification result. After the modification is completed, the modified SQL language is executed through IDEA+Java to realize the addition, deletion, modification and query of the database.

[0056] Figure 3 This is a schematic diagram of the display interface of a SQL statement quick debugging tool according to an exemplary embodiment. When using the sqlTool containing the above method, the staff can import the DLL information through the import button, and then select the SQL type and paste the SQL statement. After clicking the check button, the sqlTool automatically performs the check and generates the check result.

[0057] Figure 4 This is a flowchart of an SQL statement quick debugging tool according to an exemplary embodiment. Figure 4 After the staff enters sqlTool, sqlTool can obtain the SQL type, which can be analyzed or manually selected by the staff. It then automatically determines whether variable mapping is set. If variable mapping is set, it replaces the corresponding value of the field with the corresponding variable. It then determines whether it is an Insert statement. If so, it performs full format standard verification: checking variables, marker positions, and other information. If errors occur, they are highlighted. Preferably, the verification result also includes modification prompts for all non-standard or incorrect statements. If it is not an Insert statement, only marker verification is performed. Finally, the verification result is output.

[0058] It should be noted that, after outputting the verification result, the method may further include:

[0059] If the verification result is error-free, the SQL statement is executed with IDEA+Java to implement addition, deletion, modification and query of the database.

[0060] In practice, after the SQL statement entered by the staff is verified, if the verification result is no problem, it can be directly executed to increase work efficiency. The IDEA is an integrated environment for Java language development (can also be used for other languages).

[0061] Example 2

[0062] Figure 5This is a schematic block diagram of a device for fast debugging SQL statements according to an exemplary embodiment. Figure 5 , provides a SQL statement fast debugging device, including:

[0063] DDL acquisition module 101, used to obtain data definition language from the database management system;

[0064] SQL statement acquisition module 102, used to acquire the SQL statement to be debugged, and if the SQL statement is set with variable mapping, automatically map the fields and data, and replace the corresponding data with variables;

[0065] Verification module 103, configured to determine whether the SQL statement is an Insert statement; if so, perform a full format specification check on the SQL statement according to a pre-stored Java script specification; if not, perform a marker check on the SQL statement in the format specification check;

[0066] The output module 104 is configured to output a verification result, which includes all non-standard or erroneous location information.

[0067] It can be understood that the technical solution shown in this embodiment can obtain the data definition language from the database management system through the DDL acquisition module 101; obtain the SQL statement to be debugged through the SQL statement acquisition module 102, and if the SQL statement sets variable mapping, automatically map the fields and data, and replace the corresponding data with variables; determine whether the SQL statement is an Insert statement through the verification module 103, and if so, perform all format specifications verification on the SQL statement according to the pre-stored Java script specification; if not, perform the marker verification in the format specification verification on the SQL statement; output the verification result through the output module 104, and the verification result includes all non-standard or erroneous location information. It can be understood that the technical solution shown in this embodiment can automatically perform variable mapping and format specification verification, assist users in debugging SQL statements, simplify operation, and improve work efficiency.

[0068] It should be noted that the device further includes:

[0069] The execution module is used to execute the SQL statement with IDEA+Java if the verification result is error-free, so as to realize the addition, deletion, modification and query of the database.

[0070] It should be noted that the device further includes:

[0071] The upload import module is used to upload and back up the data definition language.

[0072] Example 3

[0073] According to a third aspect of an embodiment of the present invention, a device for quickly debugging SQL statements is provided, comprising:

[0074] A main controller, and a memory connected to the main controller;

[0075] The memory stores program instructions;

[0076] The main controller is used to execute program instructions stored in the memory and perform any of the above methods.

[0077] Example 4

[0078] According to a fourth aspect of an embodiment of the present invention, a computer-readable storage medium is provided, storing a computer program, wherein when the computer program is executed by a processor, the computer program implements any of the above methods.

[0079] It can be understood that the same or similar parts of the above embodiments can be referenced to each other, and the contents not described in detail in some embodiments can refer to the same or similar contents in other embodiments.

[0080] It should be noted that, in the description of the present invention, the terms "first", "second", etc. are used for descriptive purposes only and should not be understood as indicating or implying relative importance. In addition, in the description of the present invention, unless otherwise specified, the meaning of "plurality" is at least two.

[0081] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, segment or portion of code comprising one or more executable instructions for implementing the steps of a specific logical function or process, and the scope of the preferred embodiments of the present invention includes alternative implementations in which functions may be performed out of 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 those skilled in the art to which the embodiments of the present invention pertain.

[0082] It should be understood that various parts of the present invention can be implemented using hardware, software, firmware, or a combination thereof. In the above-described embodiments, multiple steps or methods can be implemented using software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented using hardware, as in another embodiment, any one of the following technologies known in the art or a combination thereof can be used: a discrete logic circuit having a logic gate circuit for implementing a logic function on a data signal, an application-specific integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0083] Those skilled in the art will understand that all or part of the steps in the method of the above embodiment can be completed by instructing related hardware through a program, and the program can be stored in a computer-readable storage medium. When the program is executed, it includes one or a combination of the steps of the method embodiment.

[0084] In addition, the functional units in the various embodiments of the present invention may be integrated into a single processing module, or each unit may exist physically separately, or two or more units may be integrated into a single module. The aforementioned integrated modules may be implemented in the form of hardware or in the form of software functional modules. If the integrated modules are implemented in the form of software functional modules and sold or used as independent products, they may also be stored in a computer-readable storage medium.

[0085] The storage medium mentioned above can be a read-only memory, a magnetic disk or an optical disk, etc.

[0086] Throughout this specification, reference to terms such as "one embodiment," "some embodiments," "examples," "specific examples," or "some examples" means that a specific feature, structure, material, or characteristic described in conjunction with that embodiment or example is included in at least one embodiment or example of the present invention. In this specification, schematic representations of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in any one or more embodiments or examples.

[0087] Although the embodiments of the present invention have been shown and described above, it will be understood that the above embodiments are illustrative and are not to be construed as limitations on the present invention. A person skilled in the art may change, modify, replace and modify the above embodiments within the scope of the present invention.

Claims

1. A method for fast debugging of SQL statements, characterized in that: include: Obtain data definition language from database management systems; Get the SQL statement to be debugged. If the SQL statement has variable mapping set, automatically map the fields and data and replace the corresponding data with variables. Determine whether the SQL statement is an Insert statement; if so, perform a format specification check on the SQL statement including field types, lengths, and default values ​​according to a pre-stored Java script specification based on the data definition language; if not, perform a marker check on the SQL statement in the format specification check; Output the verification result, which includes all non-standard or erroneous location information.

2. The method according to claim 1, characterized in that After outputting the verification result, the method further includes: If the verification result is error-free, the SQL statement is executed with IDEA+Java to implement addition, deletion, modification and query of the database.

3. The method according to claim 1, characterized in that After obtaining the data definition language from the database management system, the method further includes: The data definition language is uploaded and backed up.

4. The method according to claim 1, wherein The verification result also includes modification prompts for all the irregularities or errors.

5. A device for fast debugging SQL statements, characterized in that: include: DDL acquisition module, used to obtain data definition language from the database management system; An SQL statement acquisition module is used to acquire the SQL statement to be debugged. If the SQL statement is set with variable mapping, it automatically maps fields and data and replaces the corresponding data with variables. a verification module, configured to determine whether the SQL statement is an Insert statement; if so, performing a format specification check on the SQL statement including field types, lengths, and default values ​​according to a pre-stored Java script specification based on the data definition language; if not, performing a marker check on the SQL statement in the format specification check; The output module is used to output the verification result, which includes all non-standard or erroneous position information.

6. The device according to claim 5, characterized in that Also includes: The execution module is used to execute the SQL statement with IDEA+Java if the verification result is error-free, so as to realize the addition, deletion, modification and query of the database.

7. The device according to claim 5, characterized in that Also includes: The upload import module is used to upload and back up the data definition language.

8. A device for fast debugging SQL statements, characterized in that: include: A main controller, and a memory connected to the main controller; The memory stores program instructions; The main controller is used to execute program instructions stored in the memory and perform the method according to any one of claims 1 to 4.

9. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 4 is implemented.

Images