Script auditing method and device, storage medium and program product

By building and simulating the execution database table structure model, the problem that DDL script execution does not support rollback is solved, the script execution efficiency and security is improved, errors and repairs are reduced, and database stability is ensured.

CN118964196APending Publication Date: 2024-11-15MIGU CO LTD +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411083905.X
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-08-08
Publication Date
2024-11-15

AI Technical Summary

Technical Problem

The existing database management system does not support rollback when executing DDL scripts, resulting in limited processing speed and inefficient, and prone to errors in complex database structures and large amounts of data operations, increasing communication costs and resource waste.

Method used

By obtaining the structural information of the database table, building a table structure model and mocking the execution of the database script, comparing and updating the table structure model with the expected model, discovering and fixing potential problems in advance, reducing subsequent errors and repairing work, and improving script execution efficiency and security.

Benefits of technology

Without affecting the production database, it significantly improves the efficiency and security of script execution, reduces communication costs, ensures the stability of the database, enhances the ability of script review, and discovers and fixes potential problems in advance.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118964196A_ABST
    Figure CN118964196A_ABST
Patent Text Reader

Abstract

The invention relates to the technical field of databases, and particularly provides a script auditing method and device, a storage medium and a program product. According to the method, a table model structure of a database table is constructed based on obtained structure information of the database table, a database script is simulated and executed on the table model structure to obtain an updated table structure model, and the table model structure is further compared with the updated table structure model to obtain an inspection result of the database script. According to the whole scheme, the database script is simulated and executed in the table model structure, accuracy and normative auditing on the database script is achieved before the database script is applied to actual database generation, potential problems are found and repaired in advance under the condition that database production is not affected, and the database production efficiency is improved. Therefore, the script execution efficiency and security are remarkably improved, the communication cost is reduced, and the stability of the database is ensured.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present disclosure relates to the field of database technology, and in particular to a script auditing method, device, storage medium and program product. Background Art

[0002] With the rapid development of the Internet, in order to quickly seize the market, the iteration cycle of major companies' products has become shorter and shorter. How to deliver new product features quickly, stably and securely has become particularly important.

[0003] During the delivery process, an important step is database script execution. The lack of support for rollback after DDL (Data Definition Language) script execution limits the processing speed of this step. Once a problem occurs, either manual rollback or execution of a specific rollback script provided by the developer is inefficient. Exposing problems in DDL scripts in advance is the key to speeding up this step. Summary of the invention

[0004] In view of the above problems, the present disclosure is proposed. The present disclosure provides a script audit method, device, storage medium and program product.

[0005] According to a first aspect of the present disclosure, a script audit method is provided, comprising:

[0006] Get the structural information of the database table stored in the database;

[0007] Based on the structural information, construct a table structure model of the database table;

[0008] Simulate and execute a database script on the table structure model to obtain an updated table structure model;

[0009] The updated table structure model is compared with the expected model structure to obtain an audit result of the database script.

[0010] In addition, according to the script auditing method of the first aspect of the present disclosure, based on the structural information, constructing a table structure model of the database table includes:

[0011] Construct a hash table or a tree structure corresponding to the structural information to obtain the table structure model.

[0012] In addition, according to the script auditing method of the first aspect of the present disclosure, based on the structural information, constructing a table structure model of the database table includes:

[0013] Based on the structural information, construct the table structure model in the memory;

[0014] After comparing the updated table structure model with the expected model structure and obtaining the audit result of the database script, the method further includes:

[0015] The table structure model in the memory is cleared.

[0016] In addition, according to the script audit method of the first aspect of the present disclosure, simulating and executing a database script on the table structure model to obtain an updated table structure model includes:

[0017] When executing the target script statement included in the database script on the table structure model and the execution fails, obtaining the most recent executable script statement that is not affected by the execution result of the target script statement from the unexecuted script statements of the database script;

[0018] The most recently executable script statement is executed on the table structure model until all script statements included in the database script are executed on the table structure model to obtain the updated table structure model.

[0019] In addition, according to the script auditing method of the first aspect of the present disclosure, executing the most recently executable script statement on the table structure model includes:

[0020] In the case where the most recently executable script statement represents a newly added target field, obtaining original fields included in the table structure model;

[0021] In a case where the original field includes the target field, generating an execution result indicating execution failure;

[0022] When the original field does not include the target field, the target field is added to the table structure model.

[0023] In addition, according to the script auditing method of the first aspect of the present disclosure, before simulating the execution of the database script on the table structure model and obtaining the execution result, the method further includes:

[0024] Performing script structure detection on the database script to obtain a script structure detection result;

[0025] It is determined that the script structure detection result indicates that the structure of the database script is correct.

[0026] According to a second aspect of the present disclosure, a script auditing device is provided, comprising:

[0027] An acquisition module is used to obtain the structural information of the database table stored in the database;

[0028] A construction module, used to construct a table structure model of the database table based on the structure information;

[0029] An acquisition module is used to simulate and execute a database script on the table structure model to obtain an updated table structure model;

[0030] A comparison module is used to compare the update table structure model with the expected model structure to obtain the audit result of the database script.

[0031] According to a third aspect of the present disclosure, a computer device is provided, comprising a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of the method described in the first aspect.

[0032] According to a fourth aspect of the present disclosure, a computer-readable storage medium is provided, on which a computer program / instruction is stored, and when the computer program / instruction is executed by a processor, the steps of the method described in the first aspect are implemented.

[0033] According to a fifth aspect of the present disclosure, there is provided a computer program product, comprising a computer program / instruction, which implements the steps of the method described in the first aspect when executed by a processor.

[0034] As will be described in detail below, in the solution provided by the embodiment of the present disclosure, based on the structural information of the acquired database table, a table model structure of the database table is constructed, and the table model structure is simulated and executed by the database script to obtain an updated table structure model, and the audit result of the database script is obtained by further comparing the table model structure with the updated table structure model. The entire solution simulates the execution of the database script in the table model structure, realizes the accuracy and standardization audit of the database script before applying the database script to the actual generated database, can avoid errors during the execution of some databases, enhances the ability of script review, and discovers and fixes potential problems in advance without affecting the production database, reducing subsequent errors and repair work, thereby significantly improving the efficiency and security of script execution, reducing communication costs, and ensuring the stability of the database.

[0035] It is to be understood that both the foregoing general description and the following detailed description are exemplary, and are intended to provide further explanation of the technology as claimed. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] The above and other purposes, features and advantages of the present disclosure will become more apparent by describing the embodiments of the present disclosure in more detail in conjunction with the accompanying drawings. The accompanying drawings are used to provide a further understanding of the embodiments of the present disclosure and constitute a part of the specification. Together with the embodiments of the present disclosure, they are used to explain the present disclosure and do not constitute a limitation of the present disclosure. In the accompanying drawings, the same reference numerals generally represent the same components or steps.

[0037] Figure 1is a flowchart illustrating an application of a script auditing method according to an embodiment of the present disclosure.

[0038] Figure 2 2 is a schematic diagram illustrating an application of a table structure model represented by a tree structure according to an embodiment of the present disclosure.

[0039] Figure 3 It is a schematic diagram illustrating an application executing executable script statements on a table structure model according to an embodiment of the present disclosure.

[0040] Figure 4 is a flowchart illustrating an application of a script auditing method according to an embodiment of the present disclosure.

[0041] Figure 5 2 is a structural diagram illustrating an application of a script auditing device according to an embodiment of the present disclosure.

[0042] Figure 6 is a hardware block diagram illustrating a computer device according to an embodiment of the present disclosure.

[0043] Figure 7 is a schematic diagram illustrating a computer-readable storage medium according to an embodiment of the present disclosure.

[0044] Figure 8 is a schematic diagram illustrating a computer program product according to an embodiment of the present disclosure. DETAILED DESCRIPTION

[0045] In order to make the purpose, technical scheme and advantages of the embodiments of the present disclosure clearer, the technical scheme in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are only part of the embodiments of the present disclosure, rather than all of the embodiments. The components of the embodiments of the present disclosure generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the following detailed description of the embodiments of the present disclosure provided in the drawings is not intended to limit the scope of the present disclosure for protection, but merely represents the selected embodiments of the present disclosure. Based on the embodiments of the present disclosure, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present disclosure.

[0046] It should be noted that similar reference numerals and letters denote similar items in the following drawings, and therefore, once an item is defined in one drawing, further definition and explanation thereof is not required in subsequent drawings.

[0047] The term "and / or" herein only describes an association relationship, indicating that three relationships may exist. For example, A and / or B may represent the following three situations: A exists alone, A and B exist at the same time, and B exists alone. In addition, the term "at least one" herein represents any combination of at least two of any one or more of a plurality of. For example, including at least one of A, B, and C may represent including any one or more elements selected from the set consisting of A, B, and C.

[0048] A database is a warehouse that organizes, stores, and manages data according to data structures. With the development of information technology and the market, especially after the 1990s, data management is no longer just about storing and managing data, but has transformed into various data management methods required by users. There are many types of databases, from the simplest tables that store various data to large database systems that can store massive amounts of data, which have been widely used in various aspects.

[0049] Database scripts mainly include DDL scripts and DML (Data Manipulation Language) scripts. DDL scripts are mainly used to define and modify database structures, while DML scripts are mainly used for operations such as adding, deleting, modifying, and querying data. When the Internet of Things industry delivers new product functions, an important link is the execution of database scripts. Existing database management systems usually support a rollback mechanism, but the DDL script does not support rollback after execution, which restricts the processing speed of this link. Once a problem occurs, either manual rollback or execution of a specific rollback script provided by the developer is inefficient.

[0050] in:

[0051] Manual rollback refers to manual rollback when a problem occurs after the DDL script is executed. This method is inefficient and prone to errors.

[0052] A specific rollback script refers to a specific rollback script written by a developer to perform a rollback when a problem occurs in the execution of a DDL script. Although this method can reduce manual intervention, it still has the problem of low efficiency.

[0053] In addition, some database management systems provide an automatic rollback mechanism that can automatically roll back when the DDL script fails to execute. However, this method usually requires specific configuration and settings, and there may be problems with incomplete rollback. Whether it is setting up a failed rollback before execution or manually reviewing the rollback effect after the rollback, manual intervention is required, which reduces efficiency.

[0054] Combined with the above-mentioned related technologies, the current conventional solution in the industry is usually to first conduct a round of manual review of the SQL script, and then execute the SQL script into the corresponding database manually or by program at a specified time. In the existing database script execution process, especially in a fast-iteration product development environment, the execution of DDL (data definition language) scripts faces a series of challenges. These challenges are mainly reflected in the following aspects:

[0055] Limitations of the rollback mechanism: Traditional database management systems usually require manual rollback or rely on specific rollback scripts provided by developers when an error occurs when executing DDL scripts. This approach is not only inefficient but also prone to errors, especially in the case of complex database structures and large amounts of data operations.

[0056] Execution efficiency issues: Since DDL scripts do not support automatic rollback, when problems occur during execution, operation and maintenance personnel need to spend a lot of time viewing logs, manually rolling back executed scripts, or trying to execute rollback scripts. This not only increases the complexity of operations, but may also lead to degraded database performance in the production environment.

[0057] Communication costs: Developers writing DDL scripts in development and test environments may not be able to fully foresee all situations in the production environment. When scripts need to be adjusted due to changes in requirements or differences in the environment, the communication costs between developers and operations personnel increase significantly, which may lead to delays in going online and waste of resources.

[0058] Incomplete problem identification: When executing scripts in batches, if an error occurs, the entire script execution process will be terminated immediately. This means that problems that may exist in subsequent scripts cannot be discovered and handled in a timely manner, resulting in the need for multiple repeated executions and rollbacks, greatly reducing the efficiency of script deployment.

[0059] In order to alleviate the technical problems existing in the related art, the embodiments of the present disclosure provide a script auditing method, device, equipment, storage medium and computer program product. The method constructs a table model structure of the database table based on the acquired structural information of the database table, and simulates the execution of the database script on the table model structure to obtain an updated table structure model, and further obtains the audit result of the database script by comparing the table model structure with the updated table structure model. The entire scheme simulates the execution of the database script in the table model structure, realizes the accuracy and standardization audit of the database script before applying the database script to the actual generated database, can avoid errors during the execution of some databases, enhances the ability of script review, and discovers and fixes potential problems in advance without affecting the production database, reducing subsequent errors and repair work, thereby significantly improving the efficiency and security of script execution, reducing communication costs, and ensuring the stability of the database.

[0060] To facilitate understanding of this embodiment, a script auditing method disclosed in the embodiment of the present disclosure is first introduced in detail. The execution subject of the script auditing method provided in the embodiment of the present disclosure is generally a computer device with certain computing capabilities, and the computer device includes, for example, a terminal device or a server or other processing device. In some possible implementations, the script auditing method can be implemented by a processor calling a computer-readable instruction stored in a memory.

[0061] See also Figure 1 FIG. 1 is a flowchart of a script audit method provided by an embodiment of the present disclosure, wherein the method comprises the following steps:

[0062] Step 101: Obtain structural information of a database table stored in a database.

[0063] In this embodiment, structural information is used to characterize the structure of a database table. Structural information includes, but is not limited to, table name, field, definition, index, primary key, foreign key, etc. Among them, in a relational database, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. The primary key is a unique identifier that can identify a record, such as a record that includes an ID number, name, and age. The ID number is the only one that can identify the person in this record, and the others may be repeated, so the ID number is the primary key. The foreign key is used to associate with another table, and is a field that can identify another table record, and is used to maintain data consistency. For example, if a field in table A is the primary key of table B, then it can be a foreign key of table A.

[0064] In this embodiment, the pre-built in-memory audit data script tool can be used to connect to the database to obtain the structural information of the database table. This connection is safe and is only used to read the database structure, and does not involve data writing operations. Then query the database to obtain the structural information of all related tables, including table names, field definitions, indexes, primary keys, foreign keys, etc.

[0065] Step 102: construct a table structure model of the database table based on the structure information.

[0066] It should be understood that the table structure model is a virtual database environment, which simulates the state of the production database, but does not contain the actual business data in the production database. For example, the database table is a student table, and the student table includes fields such as name, age, and student number. Taking the "name" field as an example, the "name" column specifically includes student names such as "Zhang San", "Li Si", and "Wang Wu". Then the fields "name", "age", and "student number" are part of the structural information of this student table, and the student names "Zhang San", "Li Si", and "Wang Wu" are the actual business data in this student table. Corresponding to this embodiment, the structural information obtained refers to the fields "name", "age", and "student number", while the student names "Zhang San", "Li Si", and "Wang Wu" do not belong to the structural information in this embodiment.

[0067] In one or more embodiments, constructing a table structure model of a database table based on the structure information may include the following steps:

[0068] Construct a hash table or tree structure corresponding to the structural information to obtain a table structure model.

[0069] In the application, a hash table or tree structure corresponding to the structural information can be constructed on a storage device other than the storage database to obtain a table structure model corresponding to the database table. Each database table in the database corresponds to a table structure model. For example, if the users table already exists in the production database, the table structure model in the storage device will copy its structure but will not contain the actual business data in the production database.

[0070] As an example, in the storage device, the table structure model of the users table will contain an integer field called id, a string field called username, and a string field called email. The types, lengths, and constraints (such as whether to allow nulls) of these fields will be exactly the same as the table structure in the production database. Each table has a corresponding data structure in memory, such as a hash table or a tree structure, which is used to store the metadata of the table.

[0071] Please refer to Figure 2 , Figure 2 FIG. 1 is a schematic diagram of a table structure model represented by a tree structure according to an embodiment of the present disclosure. Figure 2 As shown, in the tree structure, the root node is the table name in the structural information, and the child nodes of the root node are the column information, constraints and indexes in the structural information, wherein the column information can be further divided into column name, column type and field length, the constraints can be further divided into constraint name and constraint type, and the index can be further divided into index name and included fields.

[0072] In the application, column types can include numeric type, date and time type, and string type. Numeric types do not need to be enclosed in quotation marks. Numeric types can be divided into one-byte tiny integer (TINYINT), two-byte small integer (SMALLINT), four-byte integer (INT), eight-byte large integer (BIGINT), four-byte single-precision floating point type (FLOAT), eight-byte double-precision floating point type (DOUBLE), fixed-point decimal (DECIMAL (M, D)), and BOOL / BOOLEAN.

[0073] The date and time types must be enclosed in quotation marks. The date and time types can be divided into date type (DATE), time type (TIME), and date and time type (DATETIME). As an example, the date type can be 2020-4-12, the time type can be 16:48:25, and the date and time type can be 2020-4-1216:48:25.

[0074] String types must be enclosed in quotation marks. String types can be divided into variable-length strings (VARCHAR(M)), fixed-length characters (CHAR(M)) and large variable-length strings (TEXT(M)).

[0075] The column name can be understood as the column header of each column in the database table. Taking the aforementioned student table as an example, the student table includes three columns with column headers of name, age, and student number, so the column names are name, age, and student number.

[0076] The three common types of integrity constraints in relational databases can include entity integrity constraints, referential integrity constraints, and user-defined integrity constraints. Entity integrity constraints require that each table has a unique identifier, and the primary key field in each table cannot be empty or have duplicate values. Referential integrity constraints require that references to non-existent entities are not allowed in relationships. User-defined integrity constraints are constraints for a specific relational database, which reflect the requirements that the data involved in a specific application must meet. Usually, user-defined completeness constraints can include non-null constraints, unique constraints, check constraints, foreign key constraints, etc.

[0077] In one or more embodiments, the storage device used to construct the table structure model includes but is not limited to the memory of a computer device. Constructing the table structure model in the memory facilitates subsequent data cleanup after the audit is completed.

[0078] Step 103: simulate and execute the database script on the table structure model to obtain an updated table structure model.

[0079] After the table structure model is built, the in-memory audit data script tool will simulate the execution of the database script. This process is similar to executing the script in the production database, but in fact all operations are performed on the table structure model. For example, if there is an ALTER TABLE statement in the database script to add a new field, the system will add this field in the table structure model without any impact on the production database.

[0080] For another example, suppose there is an ALTER TABLE statement in the script to add a new age field to the users table. In the table structure model, this operation will be executed and the age field will be added to the structure of the users table. The system will check whether this operation complies with the database specifications, for example, whether it violates existing constraints.

[0081] It should be understood that a database script includes multiple script statements. When executing a database script on a table structure model, each script statement is traversed until the execution of each script statement is completed. During the simulation execution process, even if an error is found in a DDL statement, the program will not terminate, but will continue to review the remaining scripts, and output all error information after summarizing them, which helps to solve all problems in the script at one time. It is more advanced than the existing database simulation execution technology.

[0082] In a specific implementation, in one or more embodiments, simulating and executing a database script on a table structure model to obtain an updated table structure model includes:

[0083] When executing a target script statement included in a database script on a table structure model and the execution fails, obtaining a most recent executable script statement that is not affected by the execution result of the target script statement from unexecuted script statements of the database script;

[0084] The most recently executable script statement is executed on the table structure model until all script statements included in the database script are executed on the table structure model to obtain an updated table structure model.

[0085] It should be understood that some script statements in a database script are not completely independent, but are associated with each other. For example, a target script statement needs to adopt the execution result of a previous script statement when executing, or a next script statement needs to adopt the execution result of a target script statement when executing. Therefore, if the execution of the target script statement fails, the next script statement associated with the target script statement will not be able to execute normally. Therefore, in this case, the execution of other script statements associated with the target script statement is skipped, and the latest executable script statement in the database script that is not affected by the execution result of the target script statement is executed instead.

[0086] As an example, a database script includes 5 script statements 1, 2, 3, 4, and 5. Script statements 2, 3, and 4 are associated with each other, that is, when executing script statement 3 on the table structure model, the result after executing script statement 2 is needed, and when executing script statement 4 on the table structure model, the result of executing script statement 3 is needed. If script statement 2 fails to execute, it will affect the execution of subsequent script statements 3 and 4. Script statements 3 and 4 cannot be executed due to the failure of script statement 2. In this case, once script statement 2 fails to execute, script statements 3 and 4 will no longer be executed, but one script statement will be selected from the unexecuted script statements 1 and 5 for execution.

[0087] In one or more optional embodiments, the process of executing executable script statements on a table structure model is described by taking the most recent executable script statement used to characterize the addition of a target field as an example. Specifically, the following steps may be included:

[0088] When the latest executable script statement represents a newly added target field, obtain the original fields included in the table structure model;

[0089] In the case where the original field includes the target field, generating an execution result indicating execution failure;

[0090] If the original field does not include the target field, add the target field to the table structure model.

[0091] It should be understood that if the original field includes the target field, then the target field cannot be added to the table structure model based on the most recent executable script statement. In this case, it means that the execution of the most recent executable script statement has failed. If the original field does not include the target field, then the target field can be added to the table structure model.

[0092] As an example, given Figure 3 The schematic diagram of executing executable script statements on the table structure model is shown in Figure 3 The following steps may be included:

[0093] Step 301: Get the table name and column information of the AddColumn object from the script statement.

[0094] It should be understood that the table name of the added column object refers to the table name of the database table to which the column needs to be added. The column information here refers to the information of the column to be added, and the column information may include the column name, column type and field length.

[0095] Step 302: Obtain the column information contained in the memory according to the table name.

[0096] The table name can be matched with the table name in the table model structure stored in the memory to obtain a table model structure matching the table name, and then the column information can be obtained from the table model structure. It should be understood that when the table model structure is represented by a tree structure, the table name in the table model structure can be obtained from the root node of the tree structure. The column information in the table model structure can be obtained from the child nodes of the root node.

[0097] Step 303 : determine whether the newly added column exists based on the column information; if so, execute step 304 ; otherwise, execute step 305 .

[0098] If the column information contains the column name of the newly added column, it is considered that the newly added column exists; otherwise, it is considered that the newly added column does not exist.

[0099] Step 304: Record detailed error information.

[0100] Step 305: Add the newly added column to the table model structure.

[0101] It should be understood that after the audit is passed, the corresponding data is added to the memory. In the above example, this includes adding the newly added column to the existing fields of the table. This is because subsequent scripts may need to reference this new field, so the model of the table must be updated in memory so that subsequent scripts can operate this field correctly. This process ensures that the model in memory is synchronized with the execution state of the script for subsequent simulation and operation.

[0102] In one or more embodiments, in order to further verify the accuracy and standardization of the database script, the database script may be preprocessed before the simulated execution. Specifically, the following steps may be included before the simulated execution of the database script on the table structure model and the execution result is obtained:

[0103] Perform script structure detection on database scripts to obtain script structure detection results;

[0104] Determine the script structure test results indicate that the structure of the database script is correct.

[0105] The script structure detection may include syntax checking, semantic analysis, and other detection methods that can determine whether the script structure is correct.

[0106] Step 104: Compare the updated table structure model with the expected model structure to obtain the audit result of the database script.

[0107] Among them, when the updated table structure model is consistent with the expected model structure, an audit result representing the passing of the audit can be obtained. When the updated table structure model is inconsistent with the expected model structure, an audit result representing the failing of the audit can be obtained.

[0108] It should be understood that in the case of an audit result indicating that the audit failed, the problems found during the simulated execution, such as violations of database constraints, data type mismatches, foreign key reference errors, etc., will be summarized and a report will be generated. The report includes error descriptions, affected DDL statements, and recommended repair measures. This report can help developers quickly locate problems and correct them. Developers correct the script based on the information in the report. The corrected script is simulated and executed again through the system until all problems are resolved.

[0109] In the solution provided by the embodiment of the present disclosure, based on the structural information of the acquired database table, a table model structure of the database table is constructed, and the table model structure is simulated to execute the database script to obtain an updated table structure model, and the audit result of the database script is obtained by further comparing the table model structure with the updated table structure model. The entire solution simulates the execution of the database script in the table model structure, and realizes the accuracy and standardization audit of the database script before applying the database script to the actual generated database, which can avoid errors in the execution of some databases, enhance the ability of script review, and discover and fix potential problems in advance without affecting the production database, reducing subsequent errors and repair work, thereby significantly improving the efficiency and security of script execution, reducing communication costs, and ensuring the stability of the database.

[0110] For ease of understanding, the present disclosure also provides the following examples: Figure 4 The script audit method shown is as follows Figure 4 As shown, the method may include the following steps:

[0111] Step 401: script parsing.

[0112] A parser with a script parsing function is used to obtain the DDL script in the database script.

[0113] The parser extracts the table structure information in the DDL script, including field definitions, indexes, primary keys, and foreign keys. For example, for a CREATE TABLE statement, the parser identifies the table name, field type, default value, and other information. Example: Assume there is a DDL script that contains a statement to create a table called users. The parser identifies that this table has three fields: id (primary key), username, and email, where id is an auto-incrementing primary key. It should be understood that the table structure defined by these script statements in the DDL script is actually the expected model structure corresponding to the DDL script.

[0114] Step 402: constructing a table model structure.

[0115] After parsing the script, the built in-memory audit data script tool is used to connect to the application database in order to obtain the structural information of the database tables stored in the application database. This connection is safe and is only used to read the database structure, and does not involve data writing operations. Then the database is queried to obtain the structural information of all related tables, including table names, field definitions, indexes, primary keys, foreign keys, etc. This information is used to build an accurate table structure model in memory. The table structure model is a virtual database environment that simulates the state of the production database, but does not contain the actual business data in the production database.

[0116] For example, in memory, the table structure model of the users table will contain an integer field called id, a string field called username, and a string field called email. The types, lengths, and constraints (such as whether to allow nulls) of these fields will be exactly the same as the table structure in the production database. Each database table has a corresponding table structure model in memory, such as a hash table or tree structure, which is used to store the metadata of the database table.

[0117] Step 403: Simulate execution.

[0118] After the table structure model is built in memory, the in-memory audit data script tool simulates the execution of the DDL script. This process is similar to executing the script in the production database, but all operations are performed on the table structure model in memory. For example, if there is an ALTER TABLE statement in the script to add a new field, the system will add this field to the table structure model in memory without any impact on the production database.

[0119] Example: Assume that there is an ALTER TABLE statement in the script to add a new age field to the users table. In the table structure model, this operation will be executed and the age field will be added to the structure of the users table. The system will check whether this operation complies with the database specifications, for example, whether it violates existing constraints.

[0120] During the simulation execution, even if an error is found in a DDL statement, the program will not terminate, but will continue to review the remaining script statements and output all error information together, which helps to solve all problems in the script at one time. It is more advanced than the existing database simulation execution technology.

[0121] Step 404: Script verification.

[0122] The following introduces script verification from several aspects: script preprocessing, simulation execution, result analysis, error detection, problem feedback, correction and re-verification.

[0123] Script preprocessing: Before simulation execution, the in-memory audit data script tool will preprocess the DDL script, including syntax checking and semantic analysis, to ensure that the script is structurally correct.

[0124] Simulated execution: The in-memory audit data script tool simulates the execution of each DDL statement in the table structure model in memory. For example, if the database script contains a statement to create a new table, the in-memory audit data script tool will create a new table in the memory model and check whether there are any operations that violate database specifications, such as duplicate primary key definitions, illegal data types, etc.

[0125] Result analysis: After execution, the memory audit data script tool analyzes the results of the simulation execution. It checks whether the data structure of the updated table structure model is consistent with the expected model structure and whether there are any potential conflicts or errors.

[0126] Error Detection: If any problems are found during simulation execution, such as database constraint violations, data type mismatches, foreign key reference errors, etc., these errors are logged and detailed error information is provided.

[0127] Problem feedback: The memory audit data script tool summarizes all the problems found and generates a report that includes error descriptions, affected DDL statements, and recommended repair measures. This report can help developers quickly locate problems and make corrections.

[0128] Correction and revalidation: The developer corrects the script based on the information in the report. The corrected script is simulated and executed again through the system until all issues are resolved.

[0129] The embodiments of the present disclosure have the following advantages:

[0130] Through in-memory simulation execution, the database script will not be applied to the actual generated database, so it will not have any actual impact or pollute the data, ensuring security.

[0131] Before deploying in the production environment, the quality of the DDL script is tested in advance, which reduces the errors and accidents that may occur during the deployment process, reduces the communication costs between development and operation and maintenance personnel, and improves work efficiency.

[0132] During the simulation execution, even if an error is found in a DDL statement, the program will not terminate, but will continue to review the remaining scripts, summarize all error information and output them together, which helps to solve all problems in the script at one time. Since it does not affect the data, the execution speed is faster, and the impact between execution steps is small. Each simulation execution process can be fully executed, all specification issues are screened at one time, and feedback is centralized at the end, and the execution of subsequent scripts will not be suspended immediately after an error occurs.

[0133] Since all operations are completed in memory and do not involve actual database operations, there will be no impact on the performance of the production database.

[0134] By introducing intelligent detection and warning mechanisms during the simulation execution process, potential problems can be identified before the DDL script is executed in the real environment, thereby improving the speed and efficiency of the entire delivery process.

[0135] Enhanced script review capabilities reduce subsequent errors and repair work, thereby improving the reliability and stability of production databases.

[0136] Without the intervention of testers and standard testing processes, any script developer can use the tool to perform standardized screening without spending time and resources to build and maintain the test environment, making it easier for developers to discover and handle potential script problems.

[0137] The present disclosure also provides a script auditing device for executing the script auditing method provided in any of the above embodiments. Figure 5 As shown, the device comprises:

[0138] An acquisition module 51 is used to acquire structural information of a database table stored in a database;

[0139] A construction module 52, configured to construct a table structure model of the database table based on the structure information;

[0140] An acquisition module 53 is used to simulate and execute a database script on the table structure model to obtain an updated table structure model;

[0141] The comparison module 54 is used to compare the update table structure model with the expected model structure to obtain the audit result of the database script.

[0142] In one or more embodiments, the building block 52 is used to:

[0143] Construct a hash table or a tree structure corresponding to the structural information to obtain the table structure model.

[0144] In one or more embodiments, the building block 52 is used to:

[0145] Based on the structural information, construct the table structure model in the memory;

[0146] The device is also used to:

[0147] After comparing the updated table structure model with the expected model structure and obtaining the audit result of the database script, the table structure model in the memory is cleared.

[0148] In one or more embodiments, the obtaining module 53 is used to:

[0149] When executing the target script statement included in the database script on the table structure model and the execution fails, obtaining the most recent executable script statement that is not affected by the execution result of the target script statement from the unexecuted script statements of the database script;

[0150] The most recently executable script statement is executed on the table structure model until all script statements included in the database script are executed on the table structure model to obtain the updated table structure model.

[0151] In one or more embodiments, the obtaining module 53 is used to:

[0152] In the case where the most recently executable script statement represents a newly added target field, obtaining original fields included in the table structure model;

[0153] In a case where the original field includes the target field, generating an execution result indicating execution failure;

[0154] When the original field does not include the target field, the target field is added to the table structure model.

[0155] In one or more embodiments, the device is also used to:

[0156] The database script is simulated and executed on the table structure model, and before obtaining the execution result, the script structure detection is performed on the database script to obtain the script structure detection result;

[0157] It is determined that the script structure detection result indicates that the structure of the database script is correct.

[0158] The script auditing device provided in the embodiment of the present disclosure and the script auditing method provided in the embodiment of the present disclosure are based on the same inventive concept and have the same beneficial effects as the methods adopted, run or implemented therein.

[0159] The present disclosure also provides a computer device to execute the script audit method. Figure 6 It shows a schematic diagram of a computer device provided by some embodiments of the present disclosure. Figure 6 As shown, the computer device 8 includes: a processor 800, a memory 801, a bus 802 and a communication interface 803, wherein the processor 800, the communication interface 803 and the memory 801 are connected via the bus 802; the memory 801 stores a computer program that can be run on the processor 800, and when the processor 800 runs the computer program, the script auditing method provided in any of the aforementioned embodiments of the present disclosure is executed.

[0160] The memory 801 may include a high-speed random access memory (RAM), and may also include a non-volatile memory, such as at least one disk storage. The communication connection between the device network element and at least one other network element is realized through at least one communication interface 803 (which may be wired or wireless), and the Internet, wide area network, local area network, metropolitan area network, etc. may be used.

[0161] The bus 802 may be an ISA bus, a PCI bus, or an EISA bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc. The memory 801 is used to store programs, and the processor 800 executes the programs after receiving execution instructions. The script audit method disclosed in any implementation of the above-mentioned embodiment of the present disclosure may be applied to the processor 800, or implemented by the processor 800.

[0162] The processor 800 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 hardware integrated logic circuit in the processor 800 or the instruction in the form of software. The above processor 800 can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic devices, discrete gates or transistor logic devices, discrete hardware components. The disclosed methods, steps and logic block diagrams in the embodiments of the present disclosure can be implemented or executed. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor. The steps of the method disclosed in conjunction with the embodiments of the present disclosure can be directly embodied as a hardware decoding processor to be executed, or the hardware and software modules in the decoding processor can be executed. The software module can be located in a mature storage medium in the field such as a random access memory, a flash memory, a read-only memory, a programmable read-only memory or an electrically erasable programmable memory, a register, etc. The storage medium is located in the memory 801, and the processor 800 reads the information in the memory 801 and completes the steps of the above method in combination with its hardware.

[0163] The computer device provided by the embodiment of the present disclosure and the script auditing method provided by the embodiment of the present disclosure are based on the same inventive concept and have the same beneficial effects as the methods adopted, run or implemented by them.

[0164] The present disclosure also provides a computer-readable storage medium corresponding to the script audit method provided in the above embodiment. Figure 7 The computer-readable storage medium shown is a CD 30 on which a computer program (ie, a program product) is stored. When the computer program is run by a processor, the script audit method provided by any of the aforementioned embodiments will be executed.

[0165] The present disclosure also provides a computer program product corresponding to the script audit method provided in the above embodiment. Figure 8 The computer program product 900 includes computer program 901 instructions, and the computer program / instructions are executed by a processor to perform the script auditing method provided by any of the aforementioned embodiments.

[0166] It should be noted that examples of the computer-readable storage medium may also include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other optical or magnetic storage media, which are not listed here one by one.

[0167] The computer-readable storage medium provided by the above-mentioned embodiments of the present disclosure and the script auditing method provided by the embodiments of the present disclosure are based on the same inventive concept and have the same beneficial effects as the methods adopted, run or implemented by the application programs stored therein.

[0168] It should be noted that:

[0169] In the description provided herein, a large number of specific details are described. However, it is understood that the embodiments of the present disclosure can be practiced without these specific details. In some instances, well-known structures and technologies are not shown in detail so as not to obscure the understanding of this description.

[0170] Similarly, it should be understood that in order to streamline the present disclosure and aid in understanding one or more of the various inventive aspects, in the above description of the exemplary embodiments of the present disclosure, the various features of the present disclosure are sometimes grouped together into a single embodiment, figure, or description thereof. However, the disclosed method should not be interpreted as reflecting the following schematic diagram: the claimed disclosure requires more features than the features explicitly recited in each claim. More specifically, as reflected in the claims below, the inventive aspects lie in less than all the features of the single embodiment disclosed above. Therefore, the claims that follow the specific embodiment are hereby expressly incorporated into the specific embodiment, with each claim itself serving as a separate embodiment of the present disclosure.

[0171] In addition, those skilled in the art will appreciate that, although some embodiments described herein include certain features included in other embodiments but not other features, the combination of features of different embodiments is meant to be within the scope of this disclosure and form different embodiments. For example, in the claims below, any one of the claimed embodiments may be used in any combination.

[0172] The above is only a preferred specific implementation of the present disclosure, but the protection scope of the present disclosure is not limited thereto. Any changes or substitutions that can be easily thought of by any technician familiar with the technical field within the technical scope disclosed in the present disclosure should be included in the protection scope of the present disclosure. Therefore, the protection scope of the present disclosure should be based on the protection scope of the claims.

Claims

1. A script auditing method, characterized in that: include: Get the structural information of the database table stored in the database; Based on the structural information, construct a table structure model of the database table; Simulate and execute a database script on the table structure model to obtain an updated table structure model; The updated table structure model is compared with the expected model structure to obtain an audit result of the database script.

2. The method according to claim 1, characterized in that Based on the structural information, constructing a table structure model of the database table includes: Construct a hash table or a tree structure corresponding to the structural information to obtain the table structure model.

3. The method according to claim 1, characterized in that, based on the structural information, constructing a table structure model of the database table comprises: Based on the structural information, construct the table structure model in the memory; After comparing the updated table structure model with the expected model structure and obtaining the audit result of the database script, the method further includes: The table structure model in the memory is cleared.

4. The method according to claim 1, characterized in that: The database script is simulated and executed on the table structure model to obtain an updated table structure model, including: When executing the target script statement included in the database script on the table structure model and the execution fails, obtaining the most recent executable script statement that is not affected by the execution result of the target script statement from the unexecuted script statements of the database script; The most recently executable script statement is executed on the table structure model until all script statements included in the database script are executed on the table structure model to obtain the updated table structure model.

5. The method according to claim 4, characterized in that Executing the most recently executable script statement on the table structure model includes: In the case where the most recently executable script statement represents a newly added target field, obtaining original fields included in the table structure model; In a case where the original field includes the target field, generating an execution result indicating execution failure; When the original field does not include the target field, the target field is added to the table structure model.

6. The method according to claim 1, characterized in that Before simulating and executing the database script on the table structure model to obtain the execution result, the method further includes: Performing script structure detection on the database script to obtain a script structure detection result; It is determined that the script structure detection result indicates that the structure of the database script is correct.

7. A script auditing device, characterized in that: include: An acquisition module is used to obtain the structural information of the database table stored in the database; A construction module, used to construct a table structure model of the database table based on the structure information; An acquisition module is used to simulate and execute a database script on the table structure model to obtain an updated table structure model; A comparison module is used to compare the update table structure model with the expected model structure to obtain the audit result of the database script.

8. A computer device comprising a memory, a processor and a computer program stored in the memory, characterized in that: The processor executes the computer program to implement the steps of the method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program / instruction stored thereon, characterized in that: When the computer program / instructions are executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.

10. A computer program product comprising a computer program / instructions, characterized in that When the computer program / instructions are executed by a processor, the steps of the method according to any one of claims 1 to 6 are implemented.