Database script management method and system, electronic equipment and storage medium
By verifying the received database scripts in a checksum simulation environment, the problem of low management efficiency of traditional database scripts is solved, and automatic auditing and development efficiency are achieved.
Patent Information
- Application Number
- CN202411342638.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-25
- Publication Date
- 2025-02-25
AI Technical Summary
During the traditional software development process, the management efficiency of database scripts is low, the script quality cannot be guaranteed, the review is difficult, and the online time is affected.
Provide a database script management method, including verifying the received database script, establishing a simulation environment, and determining whether the script is executed normally in the simulation environment.
Automatic audit of database scripts is realized, reducing manual workload and improving software development efficiency.
Smart Images
Figure CN119512907A_ABST
Abstract
Description
Background Art
[0002] In the traditional software development process, the management of database scripts is usually inefficient. Developers manually write database scripts, verify and execute them in the test library, and submit them for production execution along with the version when they are released for production. This production method faces the following serious problems:
[0003] 1) The quality of the submitted database scripts cannot be guaranteed, and manual operations completely rely on the experience of developers;
[0004] 2) Database script auditing is difficult. It is difficult to audit database scripts during the development phase. If the database scripts are audited during the online phase, it will be too late and will affect the online time. Moreover, it is difficult for auditors to find problems from the database scripts alone. Summary of the invention
[0005] The technical problem to be solved by the present invention is to address the deficiencies of the prior art and specifically provide a database script management method, system, electronic device and storage medium, as follows:
[0006] 1) In the first aspect, the present invention provides a database script management method, and the specific technical solution is as follows:
[0007] Verify the received database script;
[0008] When the received database script passes verification, a simulation environment corresponding to the received database script is established;
[0009] Determine whether the received database script is executed normally in the simulation environment corresponding to the received database script.
[0010] The beneficial effects of a database script management method provided by the present invention are as follows:
[0011] When developing database-related software, the database scripts involved can be automatically reviewed, reducing the workload of manual review and helping to improve the efficiency of software development.
[0012] Based on the above solution, a database script management method of the present invention can also be improved as follows.
[0013] Furthermore, it also includes:
[0014] When the received database scripts are multiple database scripts with an associated relationship, verify each database script;
[0015] When each database script passes verification, a simulation environment corresponding to multiple database scripts with associated relationships is established;
[0016] It is determined whether each database script is normally executed in a simulation environment corresponding to a plurality of database scripts having an associated relationship.
[0017] Furthermore, the multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
[0018] Furthermore, it also includes:
[0019] When the plurality of database scripts with an associated relationship include a database script of the DDL class, and when each database script with an associated relationship is normally executed in the simulation environment, a secondary verification is performed on each database script of the DDL class.
[0020] 2) In the second aspect, the present invention also provides a database script management system, and the specific technical solution is as follows:
[0021] It includes a verification module, a simulation environment establishment module and a determination module;
[0022] The verification module is used to: verify the received database script;
[0023] The simulation environment establishment module is used to: establish a simulation environment corresponding to the received database script after the received database script passes verification;
[0024] The determination module is used to determine whether the received database script is executed normally in the simulation environment corresponding to the received database script.
[0025] Based on the above solution, a database script management system of the present invention can also be improved as follows.
[0026] Furthermore, the verification module is also used to: when the received database scripts are multiple database scripts with an associated relationship, verify each database script;
[0027] The simulation environment establishment module is also used to: establish a simulation environment corresponding to a plurality of database scripts having an associated relationship after each database script has passed verification;
[0028] The determination module is also used to determine whether each database script is normally executed in the simulation environment corresponding to the multiple database scripts having an associated relationship.
[0029] Furthermore, the multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
[0030] Furthermore, the verification module is also used for: when the multiple database scripts with an associated relationship include a database script of the DDL class, and when each database script with an associated relationship is executed normally in the simulation environment, performing a secondary verification on each database script of the DDL class.
[0031] 3) In a third aspect, the present invention further provides an electronic device, comprising a processor, the processor being coupled to a memory, the memory storing at least one computer program, the at least one computer program being loaded and executed by the processor, so that the electronic device implements any one of the above-mentioned database script management methods.
[0032] 4) In a fourth aspect, the present invention further provides a computer-readable storage medium, in which at least one computer program is stored, and the at least one computer program is loaded and executed by a processor so that a computer implements any of the above-mentioned database script management methods.
[0033] It should be noted that the beneficial effects achieved by the technical solutions of the second to fourth aspects of the present invention and the corresponding possible implementation methods can be found in the above-mentioned technical effects of the first aspect and its corresponding possible implementation methods, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:
[0035] Figure 1 This is one of the flow charts of a database script management method according to an embodiment of the present invention;
[0036] Figure 2 The second flowchart of a database script management method according to an embodiment of the present invention;
[0037] Figure 3 A schematic diagram of the structure of a database script management system according to an embodiment of the present invention;
[0038] Figure 4 The figure is a schematic diagram of the structure of an electronic device according to an embodiment of the present invention. DETAILED DESCRIPTION
[0039] In order to make the objectives, technical solutions and advantages of the present invention more clear, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings.
[0040] like Figure 1 As shown, a database script management method according to an embodiment of the present invention comprises the following steps:
[0041] S1. Verify the received database script, specifically:
[0042] S10. Check whether there are any statements in the received database script that do not meet the preset specifications. If so, issue a corresponding reminder to facilitate the developer of the database script to modify the statements that do not meet the preset specifications, stop executing subsequent steps, and wait for receiving a new database script. If not, continue to execute subsequent steps, that is, continue to execute S11, wherein the preset specifications can be set according to actual conditions.
[0043] S11. Writing inspection rules according to database management specifications to check whether there are sensitive operations in the received database script. If so, blocking the sensitive operations and issuing a reminder of the existence of sensitive operations so that the developer of the database script can modify the sensitive operations and stop executing subsequent steps and wait for a new database script. If not, continue to execute subsequent steps, that is, continue to execute S2. The inspection rules written in the database management specifications can be set according to actual conditions, and multiple sensitive operations can be listed in advance. Then, checking whether there are pre-listed sensitive operations in the received database script. If so, it means that there are sensitive operations in the received database script. If not, there are no sensitive operations in the received database script.
[0044] Optionally, in S10, the received database script is checked for statements that do not conform to preset specifications through the following two steps, specifically:
[0045] S100. Check the sensitive words and sensitive words of high-risk operations in the received database script. When the received database script contains sensitive words or sensitive words of high-risk operations, issue a corresponding reminder and wait for a new database script. When the received database script does not contain sensitive words or sensitive words of high-risk operations, continue to execute subsequent steps, that is, continue to execute S101, wherein the sensitive words and sensitive words of high-risk operations can be set according to actual conditions. For example, the drop keyword used to delete a table or delete a field is a sensitive word for high-risk operations and is directly prohibited.
[0046] S101. Perform syntax tree parsing on the received database script, parse the statements in the received database script into a standard change operation data structure, obtain a standard data structure, and then verify the standard data structure according to verification rules, wherein the verification rules can be set according to actual conditions. For example, the verification rules are: for statements that modify fields, it is not allowed to modify the field type or reduce the field length.
[0047] When all statements in the received database script meet the preset specifications and when there are no sensitive operations in the received database script, it means that the received database script has passed the verification. Otherwise, the received database script has failed the verification and waits for receiving a new database script before verification.
[0048] S2. When the received database script passes verification, a simulation environment corresponding to the received database script is established.
[0049] The implementation process of establishing a simulation environment corresponding to the received database script is as follows:
[0050] A simulation environment is created in memory by using a database (H2 embedded memory database or other database), a temporary memory link (the database disappears when the link is closed, which means that when a database connection is closed, all resources related to it will also be closed, including but not limited to the database connection itself, result sets, etc.) and pre-parsing the table (target table) in the database corresponding to the received database script, and executing the DDL statement corresponding to the table structure of the target table.
[0051] Among them, the database corresponding to the database script is the target database, the operating environment of the created simulation environment is the same as the operating environment of the target database, and the process of obtaining the DDL statement corresponding to the table structure of the target table is: use jdbc to obtain the table structure of the target table and convert it into DDL statements.
[0052] S3. Determine whether the received database script is executed normally in the simulation environment corresponding to the received database script, then:
[0053] If the received database script can be executed normally in the simulation environment, that is, no error message is generated when the received database script is executed in the simulation environment; if the received database script cannot be executed normally in the simulation environment, that is, at least one error message is generated when the received database script is executed in the simulation environment, then it means that there is a problem with the received database script.
[0054] Optionally, in the above technical solution, it also includes:
[0055] When the received database scripts are multiple database scripts with an associated relationship, verify each database script;
[0056] When each database script passes verification, a simulation environment corresponding to multiple database scripts with associated relationships is established;
[0057] It is determined whether each database script is normally executed in a simulation environment corresponding to a plurality of database scripts having an associated relationship.
[0058] Determine whether each database script can be executed normally in the simulation environment. If any database script cannot be executed normally, it means that there is a problem with the database script. If each database script can be executed normally, it means that all the associated database scripts have no problem.
[0059] Multiple database scripts with an associated relationship mean that: when executing multiple database scripts, there is interaction between the data processed by different database scripts, and / or, when executing multiple database scripts, they need to be executed according to a preset process, and only when all are executed can a certain function be realized. In other words, multiple database scripts are developed to jointly complete a function, and the function can be set according to actual conditions, such as synchronization function or data filtering function.
[0060] Since multiple database scripts with an associated relationship often operate on the same database (target database), the operating environment of the created simulation environment only needs to be the same as the operating environment of the target database.
[0061] Optionally, in the above technical solution, the multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
[0062] Optionally, in the above technical solution, it also includes:
[0063] S4. When the plurality of database scripts having an associated relationship include a database script of the DDL class, and when each database script having an associated relationship is executed normally in the simulation environment, a secondary verification is performed on each database script of the DDL class.
[0064] The process of performing a secondary check on any database script of the DDL type includes:
[0065] Read the table structure in the execution result of the database script of the DDL class (the execution result obtained after executing the database script of the DDL class in the simulation environment), compare the table structure in the execution result with the original table structure in the simulation environment, and obtain the change amount. If the coupling verification rule is configured, the coupling verification rule is used to verify the change amount and obtain the secondary verification result, until the secondary verification result corresponding to each database script of the DDL class is obtained, then:
[0066] When all the secondary verification results are passed, it means that there are no problems with all the database scripts with associated relationships. When any of the secondary verification results are failed, it means that there are problems with the database script corresponding to the secondary verification result. In other words, there are problems with all the database scripts with the received associated relationships, and subsequent steps are stopped to receive multiple new database scripts with associated relationships.
[0067] Among them, the coupling verification rules can be set according to actual conditions, and different coupling verification rules have different verification processes.
[0068] Among them, when no coupling verification rule is configured, the change amount corresponding to each database script of the DDL class can be manually verified to obtain a secondary verification result corresponding to each database script of the DDL class.
[0069] The present invention is described by the following examples. Figure 2 As shown, specifically including:
[0070] S201, receiving database script:
[0071] The developer enters the application of multiple database scripts with related relationships that need to be submitted on the page, specifically including DDL database scripts and / or DML database scripts, and sets the target database, which is: the database to which the multiple database scripts submitted by the developer are applied (that is, the database corresponding to the database scripts);
[0072] After the developer fills out the application, click Submit for Review, and the backend will review the submitted database scripts, which means that the subsequent steps will begin.
[0073] S202. Verify each database script:
[0074] Specifically, each database script is verified through S10 to S11 above. When all statements in each database script meet the preset specifications and when there are no sensitive operations in each database script, it means that each database script with an associated relationship has passed the verification.
[0075] S203: Obtain target databases and target tables corresponding to multiple database scripts having an associated relationship.
[0076] The target database is the application database, but the target database is divided into a database used in the test environment and a database used in the production environment, which are different databases. In this method, all database scripts that have passed the verification are executed in the test environment, and when releasing the version, the database scripts that meet expectations and pass various verifications are manually executed in the production environment.
[0077] S204. Build simulation environment:
[0078] According to the target database and target table, copy the table structure of the target database and target table to the embedded memory database, and build a simulation environment for executing all verified database scripts.
[0079] The embedded memory database is a database contained in the memory of the execution subject (such as an electronic device, etc.) for executing the method and system of the present invention, and is used to realize the construction of the simulation environment.
[0080] S205: Determine whether each database script meets expectations:
[0081] In the simulation environment, all database scripts that have passed the verification are executed. Based on the execution results, it is determined whether each database script is executed normally in the simulation environment corresponding to multiple database scripts with associated relationships, that is, whether each database script meets expectations. For each database script of the DML class, no error is reported, which means it meets expectations (that is, the inspection has passed).
[0082] S206, obtain the change amount:
[0083] When each database script of the DDL class meets expectations, read the table structure in the execution result of any database script of the DDL class (the execution result obtained after executing the database script of the DDL class in the simulation environment), compare the table structure in the execution result with the original table structure in the simulation environment, and obtain the change amount, until the change amount corresponding to each database script of the DDL class is obtained.
[0084] S207, Secondary verification:
[0085] If a coupling verification rule is configured, the coupling verification rule is used to verify the change amount corresponding to any database script of the DDL class, and a secondary verification result is obtained until a secondary verification result corresponding to each database script of the DDL class is obtained. For example, the coupling verification rule is a coupling verification rule for set table verification, and the coupling verification rule for set table verification is specifically: the table structure of prpt, prpc, prpp, prpcp, and prpcopy, as well as the table structure between multiple libraries are required to be consistent, and a set table verification is required according to the change amount. The principle of set table verification is to write the table structure change amount data into the database and use SQL scripts to implement verification.
[0086] At present, when developing software with database coupling, there will be a lot of database script review work, which is no longer feasible manually. The present invention can automatically perform coupling verification through S207 with high efficiency.
[0087] S208. After executing S202 to S207, if each database script with an associated relationship is automatically reviewed and passed, a message will be pushed to the front-end page through the SSE method, and it will be determined whether there is a DDL-type database script. If so, it will be transferred to manual review or all database scripts with an associated relationship will be executed directly in the test environment.
[0088] Among them, the specific implementation process of manual review is as follows:
[0089] When there are DDL-type database scripts in the submitted database scripts, the development team leader views the DDL-type database scripts submitted by the developer on the page, that is, manually reviews the DDL-type database scripts and clicks to pass or fail the review. After the review is passed, all database scripts with associated relationships will be executed in the test environment. When the functions used by all database scripts with associated relationships are launched, they will be submitted to the production environment and executed along with the version release.
[0090] The beneficial effects of the present invention are as follows:
[0091] 1) During the software development phase of the software development cycle, the database scripts involved are reviewed and the script management is targeted.
[0092] 2) Automatically implement syntax checking, rule checking, and coupling checking of data scripts, greatly reducing the inspection workload.
[0093] 3) Combine manual review with automatic review. Manual review can be responsible for reviewing the business meaning part, and the program is responsible for checking the standardization, correctness and various regulatory requirements.
[0094] In the above embodiments, although the steps are numbered S1, S2, etc., these are only specific embodiments given by the present invention. Those skilled in the art may adjust the execution order of S1, S2, etc. according to actual conditions, which is also within the protection scope of the present invention. It can be understood that in some embodiments, some or all of the above embodiments may be included.
[0095] like Figure 3 As shown, a database script management system 200 according to an embodiment of the present invention includes a verification module 201, a simulation environment establishment module 202 and a determination module 203;
[0096] The verification module 201 is used to: verify the received database script;
[0097] The simulation environment establishment module 202 is used to: establish a simulation environment corresponding to the received database script after the received database script passes the verification;
[0098] The determination module 203 is used to determine whether the received database script is executed normally in the simulation environment corresponding to the received database script.
[0099] Optionally, in the above technical solution, the verification module 201 is further used to: when the received database scripts are multiple database scripts with an associated relationship, verify each database script;
[0100] The simulation environment establishment module 202 is also used to: establish a simulation environment corresponding to a plurality of database scripts having an associated relationship after each database script has passed verification;
[0101] The determination module 203 is further used to determine whether each database script is normally executed in the simulation environment corresponding to the multiple database scripts having an associated relationship.
[0102] Optionally, in the above technical solution, the multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
[0103] Optionally, in the above technical solution, the verification module 203 is also used for: when multiple database scripts with an associated relationship include a database script of the DDL class, and when each database script with an associated relationship is executed normally in the simulation environment, performing a secondary verification on each database script of the DDL class.
[0104] It should be noted that the beneficial effects of a database script management system 200 provided in the above embodiment are the same as the beneficial effects of the above database script management method, which will not be described in detail here. In addition, when the system provided in the above embodiment realizes its functions, it only uses the division of the above functional modules as an example. In actual applications, the above functions can be assigned to different functional modules as needed, that is, the system can be divided into different functional modules according to actual conditions to complete all or part of the functions described above. In addition, the system and method embodiments provided in the above embodiment belong to the same concept, and the specific implementation process is detailed in the method embodiment, which will not be described in detail here.
[0105] like Figure 4 As shown, an electronic device 300 according to an embodiment of the present invention includes a processor 320, the processor 320 is coupled to a memory 310, and the memory 310 stores at least one computer program 330, and the at least one computer program 330 is loaded and executed by the processor 320, so that the electronic device 300 implements any of the above database script management methods, specifically:
[0106] The electronic device 300 may have relatively large differences due to different configurations or performances, and may include one or more processors 320 (Central Processing Units, CPU) and one or more memories 310, wherein at least one computer program 330 is stored in the one or more memories 310, and the at least one computer program 330 is loaded and executed by the one or more processors 320, so that the electronic device 300 implements any database script management method provided in the above embodiments. Of course, the electronic device 300 may also have components such as a wired or wireless network interface, a keyboard, and an input and output interface for input and output. The electronic device 300 may also include other components for realizing device functions, which will not be described in detail here. The electronic device may specifically be a computer, etc.
[0107] A computer-readable storage medium according to an embodiment of the present invention stores at least one computer program, and the at least one computer program is loaded and executed by a processor so that a computer implements any of the above database script management methods.
[0108] Optionally, the computer-readable storage medium may be a read-only memory (ROM), a random access memory (RAM), a compact disc (CD-ROM), a magnetic tape, a floppy disk, an optical data storage device, and the like.
[0109] In an exemplary embodiment, a computer program product or a computer program is also provided, the computer program product or the computer program includes computer instructions, the computer instructions are stored in a computer-readable storage medium. A processor of an electronic device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that the electronic device executes any of the above-mentioned database script management methods.
[0110] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application are used to distinguish similar objects and represent the definition of a specific order or sequence. The order of use of similar objects can be interchanged where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than the order shown or described.
[0111] Those skilled in the art know that the present invention can be implemented as a system, method or computer program product. Therefore, the present invention can be specifically implemented in the following forms, namely: it can be complete hardware, it can be complete software (including firmware, resident software, microcode, etc.), or it can be a combination of hardware and software, which is generally referred to as "circuit", "module" or "system" herein. In addition, in some embodiments, the present invention can also be implemented in the form of a computer program product in one or more computer-readable media, and the computer-readable medium contains computer-readable program code.
[0112] Any combination of one or more computer-readable media can be used. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples of computer-readable storage media (a non-exhaustive list) include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present invention, a computer-readable storage medium can be any tangible medium containing or storing a program, which can be used by an instruction execution system, device or device or used in combination with it.
[0113] Although the embodiments of the present invention have been shown and described above, it is to be understood that the above embodiments are exemplary and are not to be construed as limitations of the present invention. A person skilled in the art may change, modify, replace and vary the above embodiments within the scope of the present invention.
Claims
1. A database script management method, characterized in that: include: Verify the received database script; When the received database script passes verification, a simulation environment corresponding to the received database script is established; Determine whether the received database script is executed normally in the simulation environment corresponding to the received database script.
2. A database script management method according to claim 1, characterized in that: Also includes: When the received database scripts are multiple database scripts with an associated relationship, verify each database script; When each database script passes the verification, a simulation environment corresponding to the plurality of database scripts having the associated relationship is established; Determine whether each database script is normally executed in the simulation environment corresponding to the plurality of database scripts having the associated relationship.
3. A database script management method according to claim 2, characterized in that: The multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
4. A database script management method according to claim 3, characterized in that: Also includes: When the plurality of database scripts with an associated relationship include a database script of the DDL class, and when each database script with an associated relationship is normally executed in the simulation environment, a secondary verification is performed on each database script of the DDL class.
5. A database script management system, characterized in that: It includes a verification module, a simulation environment establishment module and a determination module; The verification module is used to: verify the received database script; The simulation environment establishment module is used to: establish a simulation environment corresponding to the received database script after the received database script passes the verification; The determination module is used to determine whether the received database script is executed normally in the simulation environment corresponding to the received database script.
6. A database script management system according to claim 5, characterized in that: The verification module is also used for: when the received database scripts are multiple database scripts with an associated relationship, verifying each database script; The simulation environment establishment module is also used to: establish a simulation environment corresponding to the multiple database scripts with associated relationships after each database script passes the verification; The determination module is further used to determine whether each database script is normally executed in the simulation environment corresponding to the multiple database scripts having the associated relationship.
7. A database script management system according to claim 6, characterized in that: The multiple database scripts having an associated relationship include DDL-type database scripts and / or DML-type database scripts.
8. A database script management system according to claim 7, characterized in that: The verification module is further used for: when the multiple database scripts with an associated relationship include a database script of the DDL class, and when each database script with an associated relationship is executed normally in the simulation environment, performing a secondary verification on each database script of the DDL class.
9. An electronic device, characterized in that: The electronic device includes a processor, the processor is coupled to a memory, the memory stores at least one computer program, and the at least one computer program is loaded and executed by the processor so that the electronic device implements a database script management method as described in any one of claims 1 to 4.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores at least one computer program, and the at least one computer program is loaded and executed by a processor so that a computer implements a database script management method as claimed in any one of claims 1 to 4.