Database correction method and device, equipment and storage medium

By determining and comparing DDL and DML in the database, generating a differential object collection and inversely parsing it into a script, the problem of low Flyway universality and automation is solved, and efficient and accurate database correction is achieved.

CN118885484APending Publication Date: 2024-11-01WEBANK (CHINA)
View PDF 0 Cites 0 Cited by

Patent Information

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

AI Technical Summary

Technical Problem

The existing database management tool Flyway is poorly versatile and error-prone in the database version management and correction process, and lacks a highly automated method to implement database correction.

Method used

By determining the data definition language (DDL) and data manipulation language (DML) in the benchmark library and the target library, converting it into a table-building syntax tree and a data syntax tree, performing differential comparisons, generating a collection of differential objects, and inversely parsing it into Database scripts, automatically executed for corrections.

Benefits of technology

It improves the versatility and automation of database corrections, reduces errors in manual scripting, improves correction efficiency and reliability, and is suitable for different types of databases.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118885484A_ABST
    Figure CN118885484A_ABST
Patent Text Reader

Abstract

The invention provides a database correction method, apparatus and device, and a storage medium. The method comprises the steps of determining a first DDL and a first DML corresponding to each to-be-compared reference table in a reference library and a second DDL and a second DML corresponding to each to-be-compared target table in a target library; respectively determining a first table building object set corresponding to each first DDL, a first data object set corresponding to each first DML, a second table building object set corresponding to each second DDL and a second data object set corresponding to each second DML; performing difference comparison on the first table building object set and the second table building object set, and performing difference comparison on the first data object set and the second data object set to obtain a difference object set; performing inverse analysis on each difference object in the difference object set to obtain a database script corresponding to each difference object; and executing the database scripts corresponding to the different objects in the target library, and correcting the target library into the reference library. The method has good universality and high automation degree.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a database correction method, device, equipment and storage medium. Background Art

[0002] As software development becomes increasingly complex and the iteration speed accelerates, database version management becomes particularly important as one of the core components of software applications. Database version management mainly involves tracking, recording and managing changes to database structure and data, allowing users to access and restore to a specific version of the data.

[0003] Currently, the commonly used database management tool is Flyway, which can be used for database version management and database modification. Specifically, Flyway requires developers to write database scripts based on the baseline table and the target table and in a specific format agreed upon by Flyway, and execute the database scripts written by the developers to eliminate the differences between the baseline table and the target table. This method has poor versatility and is prone to errors.

[0004] Therefore, how to implement a database correction method with high versatility and high degree of automation is an issue that needs to be considered urgently. Summary of the invention

[0005] The present application provides a database correction method, apparatus, device and storage medium to improve the versatility and automation of the database correction method.

[0006] In a first aspect, the present application provides a database correction method, the method comprising:

[0007] Determine a first data definition language (Data Definition Language, DDL) and a first data manipulation language (Data Manipulation Language, DML) corresponding to each reference table to be compared in the reference library and a second DDL and a second DML corresponding to each target table to be compared in the target library;

[0008] Determine respectively a first table creation object set corresponding to each first DDL, a first data object set corresponding to each first DML, a second table creation object set corresponding to each second DDL, and a second data object set corresponding to each second DML;

[0009] Performing a difference comparison between the first table creation object set and the second table creation object set, and performing a difference comparison between the first data object set and the second data object set, to obtain a difference object set;

[0010] De-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object;

[0011] The database scripts corresponding to the different objects are executed in the target library, thereby correcting the target library to the reference library.

[0012] The database correction method provided by the present application, after determining the first DDL and the first DML of each benchmark table to be compared in the benchmark library, and the second DDL and the second DML of each target table to be compared in the target library, since it is impossible to directly compare the differences between each first DDL and each second DDL, first determine the first table building object set according to each first DDL, determine the first data object set according to each first DML, determine the second table building object set according to each second DDL, and determine the second data object set according to each second DML. After obtaining the above object sets, the difference comparison can be performed; specifically, the first table building object set and the second table building object set are compared, and the first data object set and the second data object set are compared, so as to obtain the difference object set, and then each difference object in the difference object set is reversed to obtain the database script corresponding to each difference object, without the need to manually write the database script, the degree of automation is high and it is not easy to make mistakes, and the database script obtained by executing the reverse analysis can realize the correction of the target library to the benchmark library. The database correction method proposed by the present application is not restricted by the types of the benchmark library and the target library, and does not need to write the database script according to a specific format, and has good versatility.

[0013] In a possible design, each reference table to be compared in the reference library and each target table to be compared in the target library are determined in the following manner, including:

[0014] For a data table having the same table name in the benchmark library and the target library, if the DDL corresponding to the data table in the benchmark library is different from the DDL corresponding to the data table in the target library, and / or the DML corresponding to the data table in the benchmark library is different from the DML corresponding to the data table in the target library, then it is determined that the data table is the benchmark table to be compared in the benchmark library and the data table is the target table to be compared in the target library;

[0015] The data tables in the reference library and the target library that do not have the same table name are determined as reference tables to be compared in the reference library or target tables to be compared in the target library.

[0016] Before comparing the reference table and the target table, the present application first screens the reference table and the target table, thereby reducing the workload of subsequent difference comparison, and thus being able to obtain a difference object set more quickly, thereby improving the efficiency of database correction.

[0017] In a possible design, determining the first table creation object set corresponding to each first DDL includes:

[0018] For any first DDL, the first DDL is parsed to generate a table creation syntax tree corresponding to the first DDL; each node on the table creation syntax tree corresponding to the first DDL is traversed, and the data value of each node is obtained through the listener as each table creation object in the first table creation object set.

[0019] Since it is impossible to directly compare the differences between the first DDL and the second DDL, it is necessary to first convert the first DDL into a table building syntax tree, and traverse the nodes of the table building syntax tree to determine the first table building object set; similarly, the same processing method is used for each first DML, each second DDL, and each second DML, first convert them into corresponding syntax trees, and then traverse the syntax trees to determine the corresponding object set.

[0020] In a possible design, comparing the first table creation object set and the second table creation object set by differences includes:

[0021] Creating a difference object of a newly added table type according to the table creation objects that exist in the first table creation object set but do not exist in the second table creation object set;

[0022] Creating a difference object of a deleted table type according to a table creation object that does not exist in the first table creation object set but exists in the second table creation object set;

[0023] A modification table difference comparison is performed on the table creation objects existing in the first table creation object set and in the second table creation object set, and a corresponding modification table type difference object is created.

[0024] In a possible design, the modified table difference comparison includes at least one of a column difference comparison, an index difference comparison, a partition definition difference comparison, a partition difference comparison, and a table attribute difference comparison.

[0025] Performing various types of modification table difference comparisons ensures a comprehensive difference comparison between the first table creation object set and the second table creation object set, thereby improving the reliability of the database correction method proposed in the present application.

[0026] In a possible design, comparing the first data object set and the second data object set for differences includes:

[0027] For a data table that exists in the reference database but does not exist in the target database, creating a difference object of a newly added data type according to a data object corresponding to the data table in the first data object set;

[0028] For a data table that does not exist in the reference database but exists in the target database, creating a difference object with the data type deleted according to the data object corresponding to the data table in the second data object set;

[0029] For a data table existing in the benchmark library and in the target library, each field value of the data object corresponding to the data table in the first data object set is compared with each field value of the data object corresponding to the data table in the second data object set; if any field value is inconsistent, a difference object of the updated data type is created based on the data object corresponding to the data table in the first data object set and the data object corresponding to the data table in the second data object set.

[0030] In a possible design, before de-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object, the method further includes:

[0031] Merging multiple difference objects of the modified table type for the same table in the difference object set into one difference object;

[0032] The difference objects in the difference object set are sorted according to the type to which the difference objects belong.

[0033] If the difference objects are not merged, then multiple difference objects of the same table modification type will be deconstructed into multiple database scripts and executed in batches. Executing the database scripts deconstructed from the difference objects of the same table modification type requires a series of resource-intensive operations, such as verifying the table structure, creating a new table structure, copying data, rebuilding indexes, etc., which may cause the table to be locked and affect concurrency performance. Therefore, merging multiple difference objects of the same table modification type into one difference object can reduce the probability of locking the table, reduce the impact on concurrency performance, and improve the execution efficiency of database scripts.

[0034] In a possible design, creating a difference object of a newly added table type according to a table creation object that exists in the first table creation object set but does not exist in the second table creation object set includes:

[0035] If a table creation object exists in the first table creation object set but does not exist in the second table creation object set, and the table creation object has a sub-table attribute, then the preset sub-table rule is used to determine the table names of the sub-tables;

[0036] A difference object of a newly added table type corresponding to each sub-table is created according to the table names of the sub-tables.

[0037] In a second aspect, the present application further provides a database correction device, the device comprising: a determination unit and a comparison unit;

[0038] The determining unit is used to determine the first DDL and the first DML corresponding to each reference table to be compared in the reference library and the second DDL and the second DML corresponding to each target table to be compared in the target library; and to respectively determine the first table creation object set corresponding to each first DDL, the first data object set corresponding to each first DML, the second table creation object set corresponding to each second DDL, and the second data object set corresponding to each second DML;

[0039] The comparison unit is used to compare the differences between the first table creation object set and the second table creation object set, and compare the differences between the first data object set and the second data object set to obtain a difference object set; reverse-parse each difference object in the difference object set to obtain a database script corresponding to each difference object; and execute the database script corresponding to each difference object in the target library, thereby correcting the target library to the benchmark library.

[0040] In a possible design, the determination unit determines each benchmark table to be compared in the benchmark library and each target table to be compared in the target library in the following manner: for a data table having the same table name in the benchmark library and the target library, if the DDL corresponding to the data table in the benchmark library and the DDL corresponding to the data table in the target library are different, and / or the DML corresponding to the data table in the benchmark library and the DML corresponding to the data table in the target library are different, then the data table is determined to be the benchmark table to be compared in the benchmark library and the data table is the target table to be compared in the target library; a data table that does not have the same table name in the benchmark library and the target library is determined to be the benchmark table to be compared in the benchmark library or the target table to be compared in the target library.

[0041] In a possible design, the determination unit is used to determine the first table creation object set corresponding to each first DDL: for any first DDL, the first DDL is parsed to generate a table creation syntax tree corresponding to the first DDL; each node on the table creation syntax tree corresponding to the first DDL is traversed, and the data value of each node is obtained through the listener as each table creation object in the first table creation object set.

[0042] In a possible design, the comparison unit is used to perform a difference comparison between the first table creation object set and the second table creation object set: create a difference object of a new table type based on the table creation objects that exist in the first table creation object set but not in the second table creation object set; create a difference object of a deleted table type based on the table creation objects that do not exist in the first table creation object set but exist in the second table creation object set; perform a modified table difference comparison on the table creation objects that exist in the first table creation object set and in the second table creation object set, and create a corresponding modified table type difference object.

[0043] In a possible design, the modified table difference comparison includes at least one of a column difference comparison, an index difference comparison, a partition definition difference comparison, a partition difference comparison, and a table attribute difference comparison.

[0044] In a possible design, the comparison unit is used to compare the first data object set and the second data object set for differences: for a data table that exists in the benchmark library but does not exist in the target library, create a difference object of the newly added data type according to the data object corresponding to the data table in the first data object set; for a data table that does not exist in the benchmark library but exists in the target library, create a difference object of the deleted data type according to the data object corresponding to the data table in the second data object set; for a data table that exists in the benchmark library and the target library, compare each field value of the data object corresponding to the data table in the first data object set with each field value of the data object corresponding to the data table in the second data object set; if any field value is inconsistent, create a difference object of the updated data type according to the data object corresponding to the data table in the first data object set and the data object corresponding to the data table in the second data object set.

[0045] In a possible design, before de-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object, the comparison unit is further used to merge multiple difference objects of the modified table type for the same table in the difference object set into one difference object; and sorting each difference object in the difference object set according to the type to which the difference object belongs.

[0046] In a possible design, the comparison unit is used to create a difference object of a newly added table type based on a table creation object that exists in the first table creation object set but does not exist in the second table creation object set: if a table creation object exists in the first table creation object set but does not exist in the second table creation object set, and the table creation object has a sub-table attribute, a preset sub-table rule is used to determine the table name of each sub-table; and a difference object of the newly added table type corresponding to each sub-table is created based on the table name of each sub-table.

[0047] In a third aspect, the present application further provides a database correction device, the device comprising: a processor, and a memory communicatively connected to the processor;

[0048] The memory stores computer-executable instructions;

[0049] The processor executes the computer-executable instructions stored in the memory to implement the method described in the first aspect above.

[0050] In a fourth aspect, the present application further provides a computer-readable storage medium, wherein the storage medium stores computer-executable instructions, and when the computer-executable instructions are executed by a processor, they are used to implement the method described in the first aspect above.

[0051] In a fifth aspect, the present application further provides a computer program product, wherein the computer program product comprises a computer program, and when the computer program is executed by a processor, the method described in the first aspect is implemented. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the drawings required for use in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0053] Figure 1 A schematic diagram of a database correction method flow chart provided in an embodiment of the present application;

[0054] Figure 2 A schematic diagram of each functional module provided in the embodiment of the present application;

[0055] Figure 3 A functional diagram of a library table information acquisition module provided in an embodiment of the present application;

[0056] Figure 4 A functional diagram of the analysis module provided in the embodiment of the present application;

[0057] Figure 5 A schematic diagram of a partial syntax tree provided in an embodiment of the present application;

[0058] Figure 6 A schematic diagram of difference objects provided in an embodiment of the present application;

[0059] Figure 7 Schematic diagram of the structure of the database correction device provided in the embodiment of the present application Figure 1 ;

[0060] Figure 8 Schematic diagram of the structure of the database correction device provided in the embodiment of the present application Figure 2 . DETAILED DESCRIPTION

[0061] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application will be further described in detail below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present application, rather than all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of the present application.

[0062] The application scenarios described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application, and do not constitute a limitation on the technical solutions provided by the embodiments of the present application. It is known to those skilled in the art that with the emergence of new application scenarios, the technical solutions provided by the embodiments of the present application are also applicable to similar technical problems. In the description of the present application, unless otherwise specified, the meaning of "multiple" is two or more.

[0063] In addition to the above-mentioned problems of poor versatility and low efficiency, using Flyway to modify the database also has the problem of being unable to optimize the database script and being insufficiently flexible. For example, when there are several database operations on the same table, the database scripts can actually be merged to reduce the time of locking the table when the table is changed. However, Flyway cannot perform this optimization and can only execute the database scripts in sequence.

[0064] To solve some problems of Flyway, this application proposes Figure 1 The database correction method shown in the figure can be executed by a server or a functional module in the server, and the present application does not limit this. For example, it is assumed that the server includes a library table information acquisition module, a parsing module, a difference generation module, a database script generation module and a database script execution module, wherein the library table information acquisition module can be used to execute step 100, the parsing module can be used to execute step 101, the difference generation module can be used to execute step 102, the database script generation module can be used to execute step 103, and the database script execution module can be used to execute step 104. The relationship between the above functional modules can be referred to Figure 2 .

[0065] First, a brief introduction to the professional terms or tools involved in this application is given:

[0066] DDL: Data Definition Language, a specific category of Structured Query Language (SQL), is used to define database objects, such as databases, data tables, and data fields.

[0067] DML: Data Manipulation Language, a specific category of SQL language, is used to add, delete, and modify data in database tables;

[0068] ANTLR: Another Tool for Language Recognition, is a grammatical analyzer that can be used to process and translate structured text. ANTLR can generate lexical analyzers and grammatical analyzers based on user-defined grammar rules.

[0069] MD5: Message-DigestAlgorithm, information digest algorithm, is a widely used cryptographic hash function that can generate a 128-bit hash value to ensure the integrity and consistency of information transmission.

[0070] The database correction method proposed in this application specifically includes:

[0071] Step 100: The library table information acquisition module determines the first DDL and the first DML corresponding to each reference table to be compared in the reference library and the second DDL and the second DML corresponding to each target table to be compared in the target library.

[0072] Exemplarily, the reference library may also be referred to as a reference database, and the table in the reference library may be referred to as a reference table or a reference database table, and the target library may also be referred to as a target database, which is a database that needs to be corrected, and the table in the target library may be referred to as a target table or a target database table. The database correction method of the present application is to correct the target library so that the target library and the reference library have the same structure and data.

[0073] Exemplarily, the library table information acquisition module can acquire the first DDL and the first DML of the entire benchmark library by reading the database table information under a preset path.

[0074] For example, assuming that the preset path is app / db / myschema, the library table information acquisition module can obtain the first DDL and the first DML of the entire benchmark library by reading the database table information under the app / db / myschema path. Assume that the first DDL and the first DML of the entire benchmark library obtained are as shown in Table 1:

[0075]

[0076] Table 1

[0077] According to Table 1, there are 5 data tables in the benchmark library, namely benchmark table A, benchmark table B, benchmark table C, benchmark table D and benchmark table E.

[0078] Exemplarily, the library table information acquisition module may also acquire the second DDL and the second DML of the entire target library through a database query statement.

[0079] For example, first execute the "show tables" statement to obtain the target library table set TargetTableList, which includes target table A, target table B, target table C and target table F; then traverse TargetTableList, query the table structure and data of each target table, take target table C as an example, execute the "showcreate table target table C" statement to obtain the DDL of target table C, execute the "select column_namefrominformation_schema.columns where table_schema='myschema'and table_name='target table C'" statement to obtain all columns (seqNo, trans, createTime) of target table C, and finally execute the "selectseqNo,trans,createTime from target table C" statement to obtain the data of target table C. So far, the second DDL and the second DML of target table C can be obtained; the same method can be used to process target table A, target table B and target table F. Assume that the second DDL and the second DML of the full amount of the target library obtained are shown in Table 2:

[0080]

[0081]

[0082] Table 2

[0083] Exemplarily, the present application provides the following two methods to determine each reference table to be compared in the reference library and each target table to be compared in the target library:

[0084] Method 1: Use the first DDL and the first DML in the benchmark database as the benchmark table to be compared in the benchmark database, and use the second DDL and the second DML in the target database as the target table to be compared in the target database.

[0085] For example, the reference table A, reference table B, reference table C, reference table D and reference table E shown in Table 1 are used as reference tables to be compared in the reference library, and the target table A, target table B, target table C and target table F shown in Table 2 are used as target tables to be compared in the target library;

[0086] Method 2: For a data table with the same table name in the benchmark library and the target library, if the DDL corresponding to the data table in the benchmark library is different from the DDL corresponding to the data table in the target library, and / or the DML corresponding to the data table in the benchmark library is different from the DML corresponding to the data table in the target library, then the data table is determined to be the benchmark table to be compared in the benchmark library and the data table is the target table to be compared in the target library; and the data table that does not have the same table name in the benchmark library and the target library is determined to be the benchmark table to be compared in the benchmark library or the target table to be compared in the target library.

[0087] Among them, the MD5 value of DDL can be calculated to determine whether the DDL corresponding to the data table in the benchmark library and the DDL corresponding to the data table in the target library are the same. Similarly, the MD5 value of DML can be calculated to determine whether the DML corresponding to the data table in the benchmark library and the DML corresponding to the data table in the target library are the same. If the DDL corresponding to the data table in the benchmark library and the DDL corresponding to the data table in the target library are the same, and the DML corresponding to the data table in the benchmark library and the DML corresponding to the data table in the target library are also the same, it means that the data table has not changed, and there is no need to perform subsequent difference comparison. The DDL and DML corresponding to the data table can be deleted from the full first DDL, first DML and full second DDL, second DML obtained above.

[0088] For example, taking Table 1 and Table 2 as examples, the data tables with the same table names in the benchmark database and the target database are benchmark table A, target table A, benchmark table B, target table B, benchmark table C, and target table C. The MD5 values ​​of the DDL and DML of these six data tables are calculated. Assume that the calculation results are shown in Table 3:

[0089] MD5 value of DDL MD5 value of DML Target Table A a94a8fe5ccb19ba61c4c0873d391e987 Target Table B 60b725f10c9c85c70d97880dfe8191b3 9e107d9d372bb6826bd81d3542a419d6 Target Table C b994a3d904f4f780d139a4f26173b930 d8e8fca2dc0f896fd7cb4cb0031ba249 Benchmark Table A 37693cfc748049e45d87b8c7d8b9aacd Benchmark Table B 60b725f10c9c85c70d97880dfe8191b3 9e107d9d372bb6826bd81d3542a419d6 Benchmark Table C b994a3d904f4f780d139a4f26173b930 7f83b1657ff1fc53b92dc18148a1d65d

[0090] Table 3

[0091] According to Table 3, the MD5 value of the first DDL of the reference table B is the same as the MD5 value of the second DDL of the target table B, and the MD5 value of the first DML of the reference table B is also the same as the MD5 value of the second DML of the target table B, indicating that the table structure and table data of the reference table B and the target table B are the same, and there is no need to perform subsequent difference comparison. The reference table B and the target table B can be removed. Therefore, the reference table to be compared and the target table to be compared determined by the library table information acquisition module through the second method are shown in Table 4:

[0092]

[0093]

[0094] Table 4

[0095] The processing process of the above method 2 can also be referred to Figure 3 , which is equivalent to screening all the benchmark tables and target tables, removing the databases that do not need to be compared with each other, saving resources, and speeding up the process of subsequent difference comparison, thereby improving the efficiency of database correction.

[0096] Step 101: The parsing module determines the first table creation object set corresponding to each first DDL, the first data object set corresponding to each first DML, the second table creation object set corresponding to each second DDL, and the second data object set corresponding to each second DML.

[0097] For example, the parsing module uses ANTLR to write a syntax parser MyParser, which can convert DDL into a table building syntax tree and DML into a data syntax tree, and then use a listener to obtain the data value of the table building syntax tree as a table building object and obtain the data value of the data syntax tree as a data object, such as Figure 4 As shown; wherein, the listener is a design pattern that allows an object to be notified and perform corresponding operations when a specific event occurs. When the event source generates an event, the listener object will receive the event information. In this application, the event source is the exit node event of the traversal syntax tree, and the listener action is to assign values ​​to the attributes related to the node.

[0098] Further exemplarily, for any first DDL, the first DDL is first input into a syntax parser to parse the first DDL and generate a table creation syntax tree corresponding to the first DDL; then, each node on the table creation syntax tree corresponding to the first DDL is traversed, and the data value of each node is obtained through a listener as each table creation object in the first table creation object set; wherein, when traversing the syntax tree, a pre-order traversal method can be adopted, first visiting the root node, and then visiting its subtrees from left to right.

[0099] For example, by inputting the first DDL of benchmark table A into the syntax parser, a table creation syntax tree corresponding to the first DDL of benchmark table A can be obtained, and each node on the table creation syntax tree is traversed in preorder, and the data value of each node is obtained through the listener, and added to the first table creation object set as the table creation object in the first table creation object set.

[0100] Similarly, for any first DML, the first DML is first input into the syntax parser to parse the first DML and generate a data syntax tree corresponding to the first DML; then, each node on the table creation syntax tree corresponding to the first DML is traversed, and the data value of each node is obtained through the listener as each data object in the first data object set.

[0101] For example, by inputting the first DML of the reference table C into the syntax parser, a data syntax tree corresponding to the first DML of the reference table C can be obtained, and each node on the data syntax tree is traversed in preorder, and the data value of each node is obtained through the listener, and added to the first data object set as a data object in the first data object set.

[0102] Exemplarily, for any second DDL, the second DDL is first input into the syntax parser to parse the second DDL and generate a table creation syntax tree corresponding to the second DDL; then, each node on the table creation syntax tree corresponding to the second DDL is traversed, and the data value of each node is obtained through the listener as each table creation object in the second table creation object set.

[0103] For example, assuming that the second DDL of target table A is input into the syntax parser, a table building syntax tree corresponding to the second DDL of target table A can be obtained. The table building syntax tree is as follows: Figure 5 It should be noted that due to limited space, Figure 5 The following is only part of the syntax tree for table creation. Figure 5 Taking the partial table creation syntax tree shown in the figure as an example, the following explains how to obtain the table creation object based on the table creation syntax tree:

[0104] Figure 5 The root node of the table building syntax tree shown is columnDeclaration. Therefore, when using the pre-order traversal method, the columnDeclaration node is visited first, and the traversal Figure 5The table building syntax tree shown will generate the following events: Event ①: When exiting the access to the columnDeclaration node, the listener executes the steps: creating a column declaration object Column; Event ②: When exiting the scanning of the columnName node, the listener executes the steps: creating a column name attribute colmnName, assigning the column name attribute with the leaf node value col1, and assigning it to the corresponding member variable of its parent node column declaration object Column, that is, obtaining Colunmn.colunmnName = col1; Event ③: When exiting the scanning of the columnDefinition node, the listener executes the steps: creating a column definition object Definition, and assigning it to the corresponding member variable of its parent node column declaration object Column. That is, we get Column.columnDefinition = Definition; Event ④: When exiting the scanning of the dataType node, the listener executes the following steps: creating a data type attribute, assigning the data type attribute with the leaf node value string, and assigning it to the corresponding member variable of its parent node column definition object Definition, that is, we get Definition.dataType = string; Event ⑤: When exiting the scanning of the commentColumnConstraint node, the listener executes the following steps: creating a field comment attribute, assigning the field comment attribute with the leaf node value 'column 1', and assigning it to the corresponding member variable of its parent node column definition object Definition, that is, we get Definition.comment = 'column 1'. Assume that the complete table creation syntax tree corresponding to the second DDL of the target table A is traversed, and the table creation object obtained is as follows Figure 6 shown.

[0105] Exemplarily, for any second DML, the second DML is first input into a syntax parser to parse the second DML to generate a data syntax tree corresponding to the second DML; then, each node on the data syntax tree corresponding to the second DML is traversed, and the data value of each node is obtained through a listener as each data object in the second data object set.

[0106] For example, it is assumed that the first table creation object set determined by the parsing module according to the first DDL of each reference table to be compared in the reference library, that is, according to the first DDL of reference table A, reference table C, reference table D, and reference table E, includes CreateTableBaseA, CreateTableBaseC, CreateTableBaseD, and CreateTableBaseE, and the first table creation object set is recorded as BaseTableList; it is assumed that the first data object set determined by the parsing module according to the first DML of each reference table to be compared in the reference library, that is, according to the first DML of reference table C, includes InsertBaseC, and the first data object set is recorded as BaseDataList; Assume that the second table creation object set determined by the parsing module according to the second DDL of each target table to be compared in the target library, that is, according to the second DDL of target table A, target table C and target table F, includes CreateTableTargetA, CreateTableTargetC, and CreateTableTargetF, and the second table creation object set is recorded as TargetTableList; assume that the second data object set determined by the parsing module according to the second DML of each target table to be compared in the target library, that is, according to target table C and target table F, includes InsertTargetC and InsertTargetF, and the second data object set is recorded as TargetDataList.

[0107] Since it is impossible to directly compare the differences between the DDL and DML of the data table, the present application first converts the DDL and DML of the data table into objects for the difference comparison in step 102 .

[0108] Step 102: The difference generation module performs a difference comparison between the first table creation object set and the second table creation object set, and performs a difference comparison between the first data object set and the second data object set to obtain a difference object set.

[0109] Exemplarily, an empty set DiffList may be defined as a difference object set, and the difference generation module adds the difference objects obtained by the difference comparison to DiffList, thereby obtaining a difference object set.

[0110] Exemplarily, after the difference generation module compares the first table creation object set with the second table creation object set, it obtains difference objects of the newly added table type, difference objects of the deleted table type, and difference objects of the modified table type.

[0111] First, the acquisition of difference objects of the newly added table type is introduced: based on the table objects that exist in the first table object set but not in the second table object set, create difference objects of the newly added table type; specifically, traverse the first table object set, for each table object, if the table object does not exist in the second table object set, it means that there is no data table corresponding to the table object in the target library, then it is necessary to create a difference object of the newly added table type, and add the difference object of the newly added table type to the difference object set DiffList, which is used to create a new data table in the target library that is missing from the benchmark library.

[0112] For example, the first table creation object set includes CreateTableBaseA, CreateTableBaseC, CreateTableBaseD, and CreateTableBaseE, and the second table creation object set includes CreateTableTargetA, CreateTableTargetC, and CreateTableTargetF. The table creation objects that exist in the first table creation object set but not in the second table creation object set are CreateTableBaseD and CreateTableBaseE. Therefore, the difference objects of the newly added table type are CreateTableTargetD and CreateTableTargetE, and they are added to the difference object set DiffList. The difference object set DiffList is updated as shown in Table 5:

[0113]

[0114] Table 5

[0115] One thing that needs to be explained is that if a table creation object exists in the first table creation object set but does not exist in the second table creation object set, and the table creation object has a sub-table attribute, the preset sub-table rule is used to determine the table names of each sub-table; and a difference object of the newly added table type corresponding to each sub-table is created based on the table names of each sub-table.

[0116] The preset table partitioning rules used to determine the table names mainly include the following:

[0117] ① SHARDING N is defined at the end of the base table, where SHARDING is a keyword indicating that the table needs to be split, and N indicates that it needs to be split into N tables. The difference generation module will automatically generate a difference object of the newly created table type for the split table based on the base table structure. For example, for a table named mytable and SHARDING 3, the solution will generate three tables that are isomorphic to mytable: mytable_0, mytable_1, and mytable_2. It can be seen that the table name suffix of this mode table is incremented from 0, and the increment step is 1.

[0118] ②Add SHARDING(start,N,step) to the end of the base table, where SHARDING is the keyword indicating that the table needs to be split, N indicates that it needs to be split into N tables, start indicates the starting number of the table name suffix, and step indicates the step length of the table name increment. For example, if the table name is mytable and SHARING(10,3,10) is used, the solution will generate three tables that are isomorphic to mytable: mytable_10, mytable_20, and mytable_30.

[0119] ③Add the definition SHARDING(key,…) to the end of the base table, where SHARDING is the keyword indicating the table to be sharded, and “key,…” is the user-defined suffix of the sharded table name. For example, for a table named mytable, and SHARDING(red,green,yellow), the solution will generate three tables isomorphic to mytable: mytable_red, mytable_green, and mytable_yellow. Compared with the above two rules, this rule is more personalized and supports customizing the suffix of the sharded table.

[0120] For example, assuming that the base table D has sub-table attributes, and the sub-table names determined by the above rule ① are Table D_0, Table D_1, and Table D_2, respectively, and the difference objects of the newly created table types created according to the sub-table names are CreateTableTargetD_0, CreateTableTargetD_1, and CreateTableTargetD_2, then CreateTableTargetD_0, CreateTableTargetD_1, and CreateTableTargetD_2 can be used to replace CreateTableTargetD in the difference object set. This replacement process can be performed when generating a difference object of a newly added table type in the difference generation module, or when optimizing the difference object in the database script generation module, and this application does not limit this.

[0121] Next, the acquisition of difference objects of the deletion table type is introduced: based on the table objects that do not exist in the first table object set but exist in the second table object set, a difference object of the deletion table type is created; specifically, the second table object set is traversed, for each table object, if the table object does not exist in the first table object set, it means that there is no data table corresponding to the table object in the benchmark library, then it is necessary to create a difference object of the deletion table type, and add the difference object of the deletion table type to the difference object set DiffList, which is used to delete the data tables in the target library that are more than the benchmark library.

[0122] For example, the first table creation object set includes CreateTableBaseA, CreateTableBaseC, CreateTableBaseD, and CreateTableBaseE, and the second table creation object set includes CreateTableTargetA, CreateTableTargetC, and CreateTableTargetF. The table creation object that does not exist in the first table creation object set but exists in the second table creation object set is CreateTableTargetF. Therefore, a difference object of the drop table type is created as DropTableTargetF, and it is added to the difference object set DiffList. The difference object set DiffList is updated as shown in Table 6:

[0123]

[0124] Table 6

[0125] Next, we will introduce how to obtain difference objects of modified table types: for table objects that exist in the first table object set and in the second table object set, we perform a modified table difference comparison and create a corresponding modified table type difference object. Specifically, a table object that exists in the first table object set and in the second table object set indicates that the data table corresponding to the table object exists in both the base database and the target database. In this case, we need to compare table attribute values. For example, base table A and target table A need to perform a modified table difference comparison, and base table C and target table C also need to perform a modified table difference comparison.

[0126] Since the table attributes corresponding to each type of table modification operation are different, the generated difference objects are also different. Therefore, this application designs a difference comparator in the difference generation module for common table modification operations. Each difference comparator contains several rules and a difference object generation method. The first table creation object set and the second table creation object set are input into the difference generator. If the rules are hit, the difference generator will create the corresponding difference object and add it to the difference set.

[0127] Among them, the difference comparators designed in the difference generation module include column difference comparator, index difference comparator, partition definition difference comparator, partition difference comparator and table attribute difference comparator. Correspondingly, when the difference generation module performs modified table difference comparison on the first table building object set and the second table building object set, it is necessary to perform at least one of column difference comparison, index difference comparison, partition definition difference comparison, partition difference comparison and table attribute difference comparison.

[0128] The following is an introduction to the various difference comparators in the difference generation module:

[0129] ① Column difference comparator: If a column exists in the base table but not in the target table, it is a new column scenario, and the difference device creates a difference object of the new column type for the corresponding column; if a column exists in the target table but not in the base table, it is a deleted column scenario, and the difference device creates a difference object of the deleted column type for the corresponding column; if a column exists in both the base table and the target table, the column attribute object is taken out and the attribute values ​​are compared item by item. If there is a difference in attribute values, it is a modified column scenario, and the difference device creates a difference object of the modified column type for the corresponding column.

[0130] For example, assume that the data of the benchmark table A and the target table A are as shown in Table 7:

[0131]

[0132] Table 7

[0133] After comparison by the column difference comparator, it is found that the column that exists in the benchmark table A but not in the target table A is col3, which means that col3 should be added to the target table A, that is, a difference object of the added column type should be created, which contains the column declaration of col3: AlterTableAAddCol3; the column that exists in the target table A but not in the benchmark table A is col4, which means that col4 should be deleted in the target table A, that is, a difference object of the deleted column type should be created, which contains the column name of col4: AlterTableADropCol4; for the columns that exist in both the target table A and the benchmark table A, namely col1 and col2, the attributes of the columns are compared item by item, and it is found that the attributes of col1 have changed, but col2 has not changed, so there is no need to process col2; for col1, a difference object of the modified column type should be created: AlterTableAChangeCol1.

[0134] In addition, after the reference table C and the target table C are input into the column difference comparator, it is found that the relevant rules of the modified table are not hit. Therefore, after the column difference comparison, the difference object set is updated to Table 8:

[0135]

[0136] Table 8

[0137] ② Index difference comparator: If the index exists in the baseline table but not in the target table, it is a new index scenario, and the index comparison difference comparator creates a corresponding difference object of the new index type; if the index exists in the target table but not in the baseline table, it is a deletion index scenario, and the difference comparator creates a corresponding deletion index type difference object; if the index exists in both the baseline table and the target table, its attribute object is taken out and the attribute values ​​are compared item by item. If there is a difference in attribute values, it is an index modification scenario. The index comparison difference comparator handles this scenario by deleting first and then creating a new one, that is, creating a difference object that first deletes the index and then creates the new index.

[0138] For example, assume that the indexes of the benchmark table A and the target table A are as shown in Table 9:

[0139]

[0140]

[0141] Table 9

[0142] After comparison by the index difference comparator, it is found that: the base table A has only one primary key index, and the primary key index also exists in the target table A. Therefore, there is no need to create a difference object of the new index type; the ordinary index idx_col in the target table A does not exist in the base table A, which means that the index should be deleted in the target table A, that is, a difference object of the deleted index type should be created: AlterTableADropIndex, and the object attributes include the name of the ordinary index idx_col; for the primary key index that exists in both the base table A and the target table A, the index column has not changed and is col1. Therefore, there is no need to create a difference object to modify the index.

[0143] In addition, after the reference table C and the target table C are input into the index difference comparator, it is found that the relevant rules of the modified table are not hit. Therefore, after the index difference comparison, the difference object set is updated to Table 10:

[0144]

[0145] Table 10

[0146] ③ Partition definition difference comparator: If the base table has a partition function definition and the target table does not, it is a non-partition table to partition table scenario, and the difference device creates a corresponding change to partition table difference object; if the target table has a partition function definition and the base table does not, it is a partition table to non-partition table scenario, and the difference device creates a corresponding change to non-partition table difference object; if both the target table and the base table have partition function definitions, the function type and parameters are compared. When there is a difference between the two, it is a partition definition modification scenario, and the difference device creates a corresponding partition definition modification type difference object.

[0147] Assume that after the reference table A and the target table A as well as the reference table C and the target table C are input into the partition definition difference comparator, it is found that the relevant rules for modifying the table are not hit. Therefore, after the partition definition difference comparison, the difference object set remains unchanged and is still Table 10.

[0148] ④ Partition difference comparator: When the base table is a partitioned table and the target table is a non-partitioned table, it is a non-partitioned table to partition table scenario. At this time, it is necessary to create corresponding partitions for the target table according to the partitions of the base table. The partition difference comparator will generate the corresponding difference objects of the newly added partition type based on this principle. When both the target table and the base table are partitioned tables, they need to be compared item by item, and the comparison uses the partition name as the unique key: for partitions that exist in the base table but not in the target table, a new partition should be added to the target table, and the partition difference comparator creates a corresponding difference object of the newly added partition type; for partitions that exist in the target table but not in the base table, the partition should be deleted in the target table, and the partition difference comparator creates a corresponding difference object of the deleted partition type.

[0149] For example, assume that both the base table A and the target table A are non-partitioned tables. Therefore, the rules for partition differences are not triggered and no difference objects are generated. Assume that the partitioning of the base table C and the target table C is as shown in Table 11:

[0150] Partition name collection Target Table C p202401, p202402, 202403 Benchmark Table C p202401, p202402, p202403, p202404

[0151] Table 11

[0152] According to Table 11, the partitions of the reference table C and the target table C are compared. It is found that the partition named p202404 only exists in the reference table C, indicating that a new partition needs to be added in the target table C. Therefore, a new partition difference object AlterTableCAddPartition is created. This object contains the partition definition of the partition p202404 in the reference table C. The difference object set is updated to Table 12:

[0153]

[0154]

[0155] Table 12

[0156] ⑤ Table attribute difference comparator: There are some attribute values ​​in the database table, such as table comments and table character sets, which are saved in the table object in the form of key values. The table attribute difference comparator will compare the attribute values ​​of the two based on the key values. If the attribute values ​​of the target table are different from those of the benchmark table, the benchmark table will be used as the basis to create a modified table attribute value difference object.

[0157] For example, assume that the table attributes of the benchmark table A and the target table A are as shown in Table 13:

[0158]

[0159] Table 13

[0160] Comparing the table attributes of the benchmark table A shown in Table 13 with the table attributes of the target table A, it is found that all key values ​​of the target table A exist in the benchmark table A, and the values ​​are consistent; all key values ​​of the benchmark table A also exist in the target table A, and the values ​​are consistent, and the table attribute difference rule is not triggered. Therefore, no difference object is generated; similarly, the above comparison of the table attribute differences between the benchmark table C and the target table C does not trigger the table attribute difference rule. Therefore, no difference object is generated; that is, the table difference object set remains unchanged and is still Table 12.

[0161] Further illustratively, the difference generation module also performs a difference comparison between the first data object set and the second data object set to obtain difference objects of newly added data types, difference objects of deleted data types, and difference objects of updated data types.

[0162] Specifically, for a data table that exists in the reference database but does not exist in the target database, a difference object of a newly added data type is created according to a data object corresponding to the data table in the first data object set;

[0163] For a data table that does not exist in the reference database but exists in the target database, create a difference object of the deleted data type according to the data object corresponding to the data table in the second data object set;

[0164] For a data table that exists in both the benchmark library and the target library, compare each field value of the data object corresponding to the data table in the first data object set with each field value of the data object corresponding to the data table in the second data object set; if any field value is inconsistent, create a difference object with updated data type based on the data object corresponding to the data table in the first data object set and the data object corresponding to the data table in the second data object set.

[0165] For example, assume that the data of the benchmark table C and the target table C are as shown in Table 14:

[0166]

[0167]

[0168] Table 14

[0169] According to Table 14, for the column with the primary key value of val1 in the target table C, the corresponding record can be found in the base table C, and then the values ​​of the ordinary columns are compared and found to be the same, which means that the record has not changed; for the column with the primary key value of val4 in the target table C, the corresponding record can be found in the base table C, and then the values ​​of the ordinary columns are compared and found to have changed the value of the column trans, which means that the record has changed, and then a difference object UpdateTableC is created to update the data record. The object contains the following elements: the column trans to be modified, the target value val5New, the original value val5, the primary key column seqNo, and the primary key value val4; for the column with the primary key value of val7 in the base table C, the corresponding record is not found in the target table, which means that new data should be added in the target table C. Therefore, a difference object InsertIntoTableC of the new data type is created, which contains all the elements of the record with the primary key value of val7 in the base table C.

[0170] Add the new difference objects UpdateTableC and InsertIntoTableC to the difference object set. The updated difference object set is shown in Table 15:

[0171]

[0172]

[0173] Table 15

[0174] Step 103: The database script generation module performs reverse analysis on each difference object in the difference object set to obtain a database script corresponding to each difference object.

[0175] Exemplarily, after obtaining the difference object set in step 102, inputting the difference object set into the database script generation module can automatically generate a corresponding database script set without manually writing database scripts, which is efficient and less prone to errors.

[0176] Exemplarily, before de-analyzing each difference object in the difference object set and obtaining the database script corresponding to each difference object, the database script generation module will perform some optimization on the difference object set, for example, sorting each difference object in the difference object set according to the type to which the difference object belongs, and the order from first to last is newly added table type, deleted table type, modified table type, newly added data type, deleted data type, and updated data type; for example, the order of the difference object of the updated data type and the difference object of the newly added data type in Table 15 is adjusted, and the adjusted difference object set is shown in Table 16:

[0177]

[0178] Table 16

[0179] In addition, multiple difference objects of the same table modification type in the difference object collection will be merged into one difference object. This is because each time a table modification operation is performed, the database system needs to perform a series of operations, such as verifying the table structure, creating a new table structure, copying data, rebuilding indexes, etc. These are resource-intensive operations and may lock the table, thereby affecting concurrent performance. Merging difference objects that modify the same table will facilitate the database system to optimize these resource-intensive operations, reduce unnecessary intermediate steps, and improve execution efficiency.

[0180] For example, in the difference object set shown in Table 16, there are 4 difference objects of the modified table type for data table A: AlterTableAAddCol3, AlterTableADropCol4, AlterTableAChangeCol1, and AlterTableADropIndex. Therefore, these 4 difference objects can be merged. Assuming that the merged difference object is AlterTableAMerged, the optimized difference object set is shown in Table 17:

[0181]

[0182] Table 17

[0183] Exemplarily, after the difference object set is optimized, the database script generation module needs to deparse these difference objects. The database script generation module uses the proxy design method, combines the semantics and grammatical rules of MySQL, and writes a deparse method for each specific difference object class, and also writes a deparse method for its member variable class, wherein the deparse method corresponding to the difference object of the newly added table type returns the "create table..." statement, the deparse method corresponding to the difference object of the deleted table type returns the "drop table..." statement, the deparse method corresponding to the difference object of the modified table type returns the "altertable...,..." statement, the deparse method corresponding to the difference object of the added data type returns the "insert into table..." statement, the deparse method corresponding to the difference object of the deleted data type returns the "delete from table..." statement, and the deparse method corresponding to the difference object of the updated data type returns the "updatetable..." statement.

[0184] Exemplarily, an empty list DiffSqlList can be created to store database scripts. The database script generation module sequentially traverses the difference object set DiffList, calls its decomposition method for each difference object, obtains the database script, and appends it to DiffSqlList. After the loop is completed, the database script that corrects the target library to the benchmark library can be obtained.

[0185] Step 104: The database script execution module executes the database script corresponding to each difference object in the target library, thereby correcting the target library to the reference library.

[0186] Exemplarily, the database script execution module sequentially executes the database script obtained in step 103, thereby correcting the target library to be consistent with the reference library. At this point, the correction of the target library has been completed.

[0187] Figure 7 and Figure 8 A schematic diagram of the structure of a possible database correction device provided in an embodiment of the present application. These database correction devices can be used to implement the functions of the library table information acquisition module, the parsing module, the difference generation module, the database script generation module, and the database script execution module in the above method embodiment, and thus can also achieve the beneficial effects possessed by the above method embodiment.

[0188] like Figure 7 As shown, the database correction device 700 includes a determination unit 710 and a comparison unit 720. The database correction device 700 is used to implement the above Figure 1 In the method embodiment shown, the functions of the library table information acquisition module, the parsing module, the difference generation module, the database script generation module and the database script execution module are:

[0189] The determining unit 710 is used to determine the first DDL and the first DML corresponding to each reference table to be compared in the reference database and the second DDL and the second DML corresponding to each target table to be compared in the target database; and to respectively determine the first table creation object set corresponding to each first DDL, the first data object set corresponding to each first DML, the second table creation object set corresponding to each second DDL, and the second data object set corresponding to each second DML;

[0190] The comparison unit 720 is used to perform a difference comparison between the first table creation object set and the second table creation object set, and to perform a difference comparison between the first data object set and the second data object set to obtain a difference object set; to perform a reverse analysis on each difference object in the difference object set to obtain a database script corresponding to each difference object; and to execute the database script corresponding to each difference object in the target library, thereby correcting the target library to the benchmark library.

[0191] In a possible design, the determination unit 710 determines each benchmark table to be compared in the benchmark library and each target table to be compared in the target library in the following manner: for a data table having the same table name in the benchmark library and the target library, if the DDL corresponding to the data table in the benchmark library is different from the DDL corresponding to the data table in the target library, and / or the DML corresponding to the data table in the benchmark library is different from the DML corresponding to the data table in the target library, then the data table is determined to be the benchmark table to be compared in the benchmark library and the data table is the target table to be compared in the target library; a data table that does not have the same table name in the benchmark library and the target library is determined to be the benchmark table to be compared in the benchmark library or the target table to be compared in the target library.

[0192] In a possible design, the determination unit 710 is used to determine the first table creation object set corresponding to each first DDL: for any first DDL, the first DDL is parsed to generate a table creation syntax tree corresponding to the first DDL; each node on the table creation syntax tree corresponding to the first DDL is traversed, and the data value of each node is obtained through the listener as each table creation object in the first table creation object set.

[0193] In a possible design, the comparison unit 720 is used to perform a difference comparison between the first table creation object set and the second table creation object set: create a difference object of a new table type based on the table creation objects that exist in the first table creation object set but not in the second table creation object set; create a difference object of a deleted table type based on the table creation objects that do not exist in the first table creation object set but exist in the second table creation object set; perform a modified table difference comparison on the table creation objects that exist in the first table creation object set and in the second table creation object set, and create a corresponding modified table type difference object.

[0194] In a possible design, the modified table difference comparison includes at least one of a column difference comparison, an index difference comparison, a partition definition difference comparison, a partition difference comparison, and a table attribute difference comparison.

[0195] In a possible design, the comparison unit 720 is used to compare the first data object set and the second data object set: for a data table that exists in the benchmark library but does not exist in the target library, create a difference object of the newly added data type according to the data object corresponding to the data table in the first data object set; for a data table that does not exist in the benchmark library but exists in the target library, create a difference object of the deleted data type according to the data object corresponding to the data table in the second data object set; for a data table that exists in the benchmark library and the target library, compare each field value of the data object corresponding to the data table in the first data object set with each field value of the data object corresponding to the data table in the second data object set; if any field value is inconsistent, create a difference object of the updated data type according to the data object corresponding to the data table in the first data object set and the data object corresponding to the data table in the second data object set.

[0196] In a possible design, before de-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object, the comparison unit 720 is further used to merge multiple difference objects of the same table modification type in the difference object set into one difference object; and sorting each difference object in the difference object set according to the type to which the difference object belongs.

[0197] In a possible design, the comparison unit is used to create a difference object of a newly added table type based on a table creation object that exists in the first table creation object set but does not exist in the second table creation object set: if a table creation object exists in the first table creation object set but does not exist in the second table creation object set, and the table creation object has a sub-table attribute, a preset sub-table rule is used to determine the table name of each sub-table; and a difference object of the newly added table type corresponding to each sub-table is created based on the table name of each sub-table.

[0198] For a more detailed description of the determination unit 710 and the comparison unit 720, please refer to Figure 1 The relevant description in the method embodiment shown is directly obtained and will not be repeated here.

[0199] like Figure 8As shown, the database correction device 800 includes a processor 810 and an interface circuit 820. The processor 810 and the interface circuit 820 are coupled to each other. It can be understood that the interface circuit 820 can be a transceiver or an input-output interface. Optionally, the database correction device 800 can also include a memory 830 for storing instructions executed by the processor 810 or storing input data required by the processor 810 to execute instructions or storing data generated after the processor 810 executes instructions.

[0200] When the database correction device 800 is used to implement Figure 1 When the method is shown, the processor 810 is used to implement the function of the above-mentioned determination unit 710, and the interface circuit 820 is used to implement the function of the above-mentioned comparison unit 720.

[0201] The division of units in the embodiments of the present application is schematic and is only a logical function division. There may be other division methods in actual implementation. In addition, each functional unit in each embodiment of the present application may be integrated into a processor, or may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0202] Although the preferred embodiments of the present application have been described, those skilled in the art may make other changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications falling within the scope of the present application.

[0203] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

Claims

1. A database correction method, characterized in that: The method includes: Determine a first data definition language DDL and a first data manipulation language DML corresponding to each reference table to be compared in the reference database and a second DDL and a second DML corresponding to each target table to be compared in the target database; Determine respectively a first table creation object set corresponding to each first DDL, a first data object set corresponding to each first DML, a second table creation object set corresponding to each second DDL, and a second data object set corresponding to each second DML; Performing a difference comparison between the first table creation object set and the second table creation object set, and performing a difference comparison between the first data object set and the second data object set, to obtain a difference object set; De-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object; The database scripts corresponding to the different objects are executed in the target library, thereby correcting the target library to the reference library.

2. The method according to claim 1, characterized in that Determining each reference table to be compared in the reference library and each target table to be compared in the target library in the following manner includes: For a data table having the same table name in the benchmark library and the target library, if the DDL corresponding to the data table in the benchmark library is different from the DDL corresponding to the data table in the target library, and / or the DML corresponding to the data table in the benchmark library is different from the DML corresponding to the data table in the target library, then it is determined that the data table is the benchmark table to be compared in the benchmark library and the data table is the target table to be compared in the target library; The data tables in the reference library and the target library that do not have the same table name are determined as reference tables to be compared in the reference library or target tables to be compared in the target library.

3. The method according to claim 1, characterized in that The determining of the first table creation object set corresponding to each first DDL includes: For any first DDL, the first DDL is parsed to generate a table creation syntax tree corresponding to the first DDL; each node on the table creation syntax tree corresponding to the first DDL is traversed, and the data value of each node is obtained through the listener as each table creation object in the first table creation object set.

4. The method according to claim 1, characterized in that The comparing the first table creation object set and the second table creation object set by differences includes: Creating a difference object of a newly added table type according to the table creation objects that exist in the first table creation object set but do not exist in the second table creation object set; Creating a difference object of a deleted table type according to a table creation object that does not exist in the first table creation object set but exists in the second table creation object set; A modification table difference comparison is performed on the table creation objects existing in the first table creation object set and in the second table creation object set, and a corresponding modification table type difference object is created.

5. The method according to claim 4, characterized in that The modified table difference comparison includes at least one of a column difference comparison, an index difference comparison, a partition definition difference comparison, a partition difference comparison, and a table attribute difference comparison.

6. The method according to claim 1, characterized in that The comparing the first data object set and the second data object set by differences includes: For a data table that exists in the reference database but does not exist in the target database, creating a difference object of a newly added data type according to a data object corresponding to the data table in the first data object set; For a data table that does not exist in the reference database but exists in the target database, creating a difference object with the data type deleted according to the data object corresponding to the data table in the second data object set; For a data table existing in the benchmark library and in the target library, each field value of the data object corresponding to the data table in the first data object set is compared with each field value of the data object corresponding to the data table in the second data object set; if any field value is inconsistent, a difference object of the updated data type is created based on the data object corresponding to the data table in the first data object set and the data object corresponding to the data table in the second data object set.

7. The method according to claim 1, characterized in that Before de-analyzing each difference object in the difference object set to obtain a database script corresponding to each difference object, the method further includes: Merging multiple difference objects of the modified table type for the same table in the difference object set into one difference object; The difference objects in the difference object set are sorted according to the type to which the difference objects belong.

8. The method according to claim 4, characterized in that The step of creating a difference object of a newly added table type according to the table creation objects that exist in the first table creation object set but do not exist in the second table creation object set includes: If a table creation object exists in the first table creation object set but does not exist in the second table creation object set, and the table creation object has a sub-table attribute, then the preset sub-table rule is used to determine the table names of the sub-tables; A difference object of a newly added table type corresponding to each sub-table is created according to the table names of the sub-tables.

9. A database correction device, characterized in that: The device comprises: a determination unit and a comparison unit; The determining unit is used to determine the first DDL and the first DML corresponding to each reference table to be compared in the reference library and the second DDL and the second DML corresponding to each target table to be compared in the target library; and to respectively determine the first table creation object set corresponding to each first DDL, the first data object set corresponding to each first DML, the second table creation object set corresponding to each second DDL, and the second data object set corresponding to each second DML; The comparison unit is used to compare the differences between the first table creation object set and the second table creation object set, and compare the differences between the first data object set and the second data object set to obtain a difference object set; reverse-parse each difference object in the difference object set to obtain a database script corresponding to each difference object; and execute the database script corresponding to each difference object in the target library, thereby correcting the target library to the benchmark library.

10. A database correction device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 8.

11. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 8 when executed by a processor.

12. A computer program product, characterized in that The invention comprises a computer program, which implements the method according to any one of claims 1 to 8 when being executed by a processor.