WO2015117429A1

WO2015117429A1 - Method and device for merging database contents

Info

Publication number: WO2015117429A1
Application number: PCT/CN2014/090271
Authority: WO
Inventors: 龙涛
Original assignee: 中兴通讯股份有限公司
Priority date: 2014-07-16
Filing date: 2014-11-04
Publication date: 2015-08-13
Also published as: CN105320681A; CN105320681B

Abstract

Provided are a method and device for merging database contents. The method comprises: establishing a mirror database corresponding to a source database, what is contained in the mirror database being completely identical to that in the source database (11); and according to a merging policy corresponding to a source table in the mirror database, conducting table information merging on the source table of the mirror database and a destination table, which is required to be merged with the source table, in a destination database (12). By means of the provided solution, the problem of merging application programs with complicated service logic in the prior art is solved, and the capability for processing the merging of database contents is improved.

Description

Database content merging method and device

Technical field

The present invention relates to the field of computers, and in particular, to a method and apparatus for merging database contents.

Background technique

With the development of computer technology, the use of databases in mainstream software systems is becoming more and more common. Especially in some application scenarios, it is necessary to combine two sets of independent database contents of the same application software into one set of database contents for management.

For the consolidation of the database, the data migration tool provided by the database management system itself is usually used for data merge migration. However, since this data migration tool is a generalized tool, it can only handle simple data migration logic, such as appending data from a table in database A to a table in database B.

For applications with complex business logic, this migration tool is often powerless and has many limitations. Therefore, how to implement the merging of applications for complex business logic is a problem to be solved.

Summary of the invention

Embodiments of the present invention provide a database content merging method and apparatus to at least solve the merging problem of an application for complex business logic in the prior art.

In order to achieve the above object, the present invention provides a database content merging method, including:

Establishing a mirror database corresponding to the source database, where the mirror database is the same as the source database;

And according to the merging policy corresponding to the source table in the mirror database, the source table of the mirror database is merged with the destination table in the destination database that needs to be merged with the source table.

The step of establishing a mirror database corresponding to the source database includes:

Back up the source database to get a backup file carrying the form information;

Select the database management system to which the destination database belongs;

Establishing a mirror database corresponding to the source database in the database management system, and adding table information carried in the backup file to the mirror database.

The step of combining the source information of the mirror database with the destination table of the destination database that needs to be merged with the source table according to the merge policy corresponding to the source table in the mirror database includes: :

Obtaining a merge policy corresponding to the source table in the mirror database in the configuration file;

When there is a detection policy in the merge policy, according to the detection strategy, the sql statement and/or the merge plug-in is selected to detect whether the table in the mirror database and the destination database can be combined with the form information, and if the detection is successful, according to the merge policy The table merge strategy combines the source tables of the mirror database with the destination tables in the destination database that need to be merged with the source tables.

The step of combining the source table of the mirror database with the destination table of the destination database that needs to be merged with the source table according to the table merge policy in the merge policy includes:

Obtain a collection of all source tables in the mirror database;

Establishing a correspondence between the table of the source table and the table merge policy that needs to be merged with the destination table in the destination database;

The table information is merged in the priority table in the source table and the destination table in the destination database.

The step of merging the form information on the source table and the destination table in the destination database includes:

Obtaining a table merging strategy corresponding to the source table in the collection of source tables;

If the table merging strategy is a specified merging strategy and the specified merging strategy type is merging or modifying the policy, it is determined whether there is a destination table with the same name as the source table in the destination database, and if present, the source table and the same name as the source table The destination form merges the form information. Otherwise, a destination form with the same name as the source form is created in the destination database, and the form information is merged between the source form and the destination form.

The steps of combining the form information of the source form and the destination form include:

Determining whether the table structure of the source table and the destination table are consistent, and if so, combining the table information with the source table and the destination table according to the specified merge policy of the source table; otherwise, converting the table structure of the source table to the table with the destination table After the structure is consistent, the form information is merged between the source table and the destination table according to the specified merge policy of the source table.

The step of merging the form information with the source table and the destination table according to the specified merging policy of the source table includes:

If the source table does not specify a merge policy, the total number of records of the source table is obtained;

If the total number of records in the source table is not 0, an insert statement is executed to insert all the records in the source table into the destination table.

If the source table has a specified merge policy, modify the source table according to the specified merge policy to obtain a record of the modified source table;

Insert the record of the modified source table into the destination table.

The steps of modifying the source table to obtain the record of the modified source table include:

Modifying or deleting the record in the source table that violates the constraint rule or the record of the primary key conflict, and obtains the record of the modified source table; or

Adding a fixed value to the self-incrementing column or sequence in the source table to obtain a record of the modified source table; or

There is a foreign key dependency on the source table, and in the case of modifying the dependent parent table, the dependent content table is also modified to obtain a record of the modified source table.

The step of inserting the record of the modified source table into the destination table includes:

If the destination table uses a trigger and the destination table is not dependent on other tables, the incremental ID of the record is automatically generated by the trigger when the record of the source table is inserted in the destination table; otherwise, when the record of the source table is inserted in the destination table , the trigger is disabled, and after the record insertion is completed, the trigger is restored to the state before the merge.

An embodiment of the present invention further provides a database content merging device, including:

Establishing a module, configured to establish a mirror database corresponding to the source database, where the mirror database is the same as the source database;

The merging module is configured to merge the source table of the mirror database with the destination table of the target database that needs to be merged with the source table according to a merging policy corresponding to the source table in the mirror database.

Among them, the building module includes:

The backup sub-module is set as a backup source database to obtain a backup file carrying the form information;

The connection submodule is set to select a database management system to which the destination database belongs;

And establishing a sub-module, configured to establish a mirror database corresponding to the source database in the database management system, and add table information carried in the backup file to the mirror database.

Among them, the merge module includes:

Obtaining a submodule, configured to obtain a merge policy corresponding to the source table in the mirror database in the configuration file;

The merging sub-module is set to use the sql statement and/or the merge plug-in to detect whether the table in the mirror database and the destination database can be combined with the table information according to the detection policy, and if the detection is successful, According to the table merging strategy in the merging strategy, the source table of the mirror database is merged with the destination table in the destination database that needs to be merged with the source table.

The above technical solution of the present invention has the following technical effects:

By adopting the method of the present invention, a general process of merging database contents can be realized. For tables that do not require special processing, the table records in the source database are appended to the table of the same name in the destination database by default by merging the main program; for the table containing the complex business logic data that is not recognized by the merge main program, by the policy configuration file Specify the corresponding merge strategy in the merged main program according to the merge strategy to merge the data; for most application scenarios, the developer only needs to configure some SQL statements in the policy configuration file to achieve the modification cannot be directly merged. The purpose of the tabular data, in the extremely complex application scenario, the developer can integrate the secondary development plug-in implemented by the encoding into the merge process; in addition, the present invention also adopts the processing mechanism of the concurrent thread to improve the processing of the merge. speed.

DRAWINGS

FIG. 1 is a flowchart of a method for merging database contents according to an embodiment of the present invention;

2 is a flowchart of a specific embodiment of a method for merging database content according to an embodiment of the present invention;

3 is a flowchart of table merging in a method for merging database contents according to an embodiment of the present invention;

FIG. 4 is a structural block diagram of an apparatus for merging database contents according to an embodiment of the present invention.

detailed description

The specific embodiments are described in detail below with reference to the accompanying drawings.

As shown in FIG. 1, an embodiment of the present invention provides a database content merging method, including:

Step 11: Establish a mirror database corresponding to the source database, where the mirror database is the same as the source database;

Step 12: According to the merging policy corresponding to the source table in the mirror database, merge the source table of the mirror database with the destination table in the destination database that needs to be merged with the source table, and merge the table information.

The embodiment of the present invention needs to merge with the source table in the source table and the destination database of the mirror database by establishing a mirror database identical to the source database and according to the merge policy corresponding to the source table in the mirror database. The purpose table is used to merge the form information; thus, the two sets of independent database contents of the same application software can be combined into a set of database content solutions, which can adapt to various complicated business logics.

In a specific embodiment of the present invention, the foregoing step 11 may include:

Step 111: Backing up the source database to obtain a backup file carrying the form information;

Step 112, selecting a database management system to which the destination database belongs;

Step 113: Establish a mirror database corresponding to the source database in the database management system, and add table information carried in the backup file to the mirror database.

In this embodiment of the present invention, a temporary mirror database is created in the database management system where the destination database is located, and the source database is restored to the temporary mirror database through the backup recovery function (or other data migration method), and then the temporary database is restored. The mirror database is used as the source database. This allows the user to access both the source and destination databases in the sql statement.

In another embodiment of the present invention, the foregoing step 12 may specifically include:

Step 121: Obtain a merge policy corresponding to the source table in the mirror database in the configuration file.

Step 122: When there is a detection policy in the merge policy, according to the detection policy, the sql statement and/or the merge plug-in is selected to detect whether the table in the mirror database and the destination database can be combined with the form information, and if the detection is successful, according to the The table merging strategy in the merging strategy merges the source table of the mirror database with the destination table in the destination database that needs to be merged with the source table.

In this embodiment of the present invention, in order to adapt to complex business logic, the user needs to configure some “merging policies” to intervene in the merge process. Some scenarios in the merge process do not need to be merged. Therefore, before the merge, The detection is performed to identify some application scenarios that cannot be merged. For example, in the student registration information management system, two or more student records with the same identity card number are not allowed. If the two sets of database contents to be merged are detected before the merger, such conflicts should be prohibited. The merge process; for example, the contents of the two databases to be merged are generated by different versions of the application software, and the data structures are incompatible and should not be merged. Performing detection before merging, according to the table merging strategy in the merging strategy, merging the source table of the mirror database with the destination table of the destination database that needs to be merged with the source table, can improve service processing efficiency .

In the foregoing embodiment, in step 122, according to the table merging policy in the merging policy, the source table of the mirror database and the destination table in the destination database that need to be merged with the source table are used to perform table information. The specific implementation process of the merger may include:

Step 1221: Obtain a set of all source tables in the mirror database.

Step 1222: Establish a correspondence between the table and the table merging strategy in the set of the source table that needs to be merged with the destination table in the destination database;

In step 1223, the form information is merged in the source table and the destination table in the destination database in order of priority.

The specific implementation of this embodiment is shown in FIG. 2, and the database content merge process as a whole includes:

(1) Backup all source databases that need to participate in the merge to get their backup files;

(2) Specify the database management system where the destination database is located, and successfully connect as the system administrator (or other role that satisfies the operation authority);

(3) Specify all backup files of the source database that need to participate in the merge, automatically create a corresponding temporary mirror database for each source database, and restore the contents of all source databases;

(4) Parse all merge policies in the configuration file;

(5) Perform the pre-merger detection according to the detection strategy in the merge policy. If the return fails, go to step (9) to end the merge; if the return is successful, or if no detection policy is configured, continue with the subsequent steps. ;

(6) The merge device sequence acquires all the table information sets in the source database to be merged, and combines all the merge policies obtained in step (2) to establish a mapping relationship between all the merged tables and the merge policy, where to be merged. The table is: in the collection of source tables, the tables that need to be merged with the destination table in the destination database, and sorted according to the priority level defined in the merge strategy;

(7) All forms of the same priority level are processed concurrently according to the order of priority from high to low. After the form of the priority level is processed, the table with lower priority is processed until all the forms are processed;

(8) The merged device deletes the created temporary database;

(9) The merger work is over.

In the above embodiment of the present invention, the specific implementation process for combining the form information in the source table and the destination table in the destination database includes:

Step 12231: Obtain a table merging policy corresponding to the source table in the set of source tables;

Step 12232: If the table merge policy is a specified merge policy and the merge policy type is a merge or a modify policy, determine whether there is a destination table in the destination database having the same name as the source table in the set of the source table, if yes, Form information is merged between the source table and the destination database with the same name as the source table. Otherwise, a destination table with the same name as the source table is created in the destination database, and the table information is merged between the source table and the destination table.

In a further step 12232, the step of merging the form information with the source form and the destination form includes:

Further, in step 12232, according to the specified merge policy of the source table, the step of combining the form information with the source form and the destination form includes:

Step 122321: If the source table does not specify a merge policy, obtain the total number of records in the source table; if the total number of records in the source table is not 0, execute an insert statement to insert all the records in the source table into the destination table.

Step 122322: If the source table has a specified merge policy, modify the source table according to the specified merge policy to obtain a record of the modified source table; insert the record of the modified source table into the destination table.

In step 122322, the step of modifying the source table to obtain the record of the modified source table includes:

The specific implementation process of the consolidation of the table is shown in Figure 3, including:

1 Find the table merge policy corresponding to the table. If the table merge policy is specified and the policy type is not merged, the table merge process ends here, go to step 8; if the table does not specify a table merge policy, or the policy type To modify the strategy, continue the subsequent merge process;

2 If the same name table does not exist in the destination database, create the same name table with the same structure in it, and go to step 4;

3 Obtain the structure information of the table in the source database and the destination database, and compare whether the table structure is consistent; if they are consistent, go to the next step; if they are inconsistent, determine whether the table specifies the table merge policy, if no table merge policy is specified, or Specifying the table merge strategy and setting the conversion process of the table structure in the source database and the destination database by the "merging processor", the merging device automatically converts the table structure in the source database to be consistent with the table structure in the destination database. If the merge policy is specified and the merge policy is not set by the "merge processor" to complete the conversion process of the table structure in the source database and the destination database, then no processing is performed, and the next step is performed;

4 If the table does not specify a table merge policy, the total number of records in the source database is obtained. If the total number of records is 0, then go to step 8 to end the merge process of the table, otherwise go to step 6 to perform additional merge of the table records;

5 If the table specifies a merge policy, the merging device performs a series of steps of modifying the contents of the database according to the policy; if the merge policy is not set by the "merging processor" to complete the merge process of the modified data, then go to step 7, Otherwise continue the next steps;

6 Execute the insert statement to insert all the records of the table in the source database into the table of the same name in the destination database;

7 If the table specifies a merge policy and the policy setting disables certain triggers, the triggers in the recovery destination database are in the pre-merge state;

8 The table merge process ends.

In the above process, specifically, the modified content merge policy of the table is a child node of the database policy, and is set to modify the data to be merged in the merge process, and includes the following contents:

1 table name, specifying the specific table corresponding to the policy;

2 strategy type, which is the "modification" type;

The priority level is optional. If you do not specify a priority level, the default is to inherit the priority of the parent node database. After the table with the highest priority is merged, the table with the lower priority level can be merged. For tables with foreign key dependencies, the "Merge Processor" can be automatically identified from the system table of the database management system, and then the priority is automatically set, without the user having to set the priority.

4sql statement, child node, optional, can be configured with multiple child nodes, set to modify the table structure or data content in the source database or destination database (not only limited to modify the currently processed table, but also modify other associated dependency tables);

5 merge plug-in name, sub-node, optional, configurable multiple sub-nodes, set to the complex business logic that can not be processed by the sql statement before the merge, the secondary development merge plug-in for the merge main program call user to handle;

6 Whether the "consolidation processor" is used to complete the conversion process of the table structure in the source database and the destination database, child nodes, optional. The default is "Yes". When the table structure is inconsistent in the source database and the destination database, the "merge processor" is used to convert the structure of the table in the source database to be the same as the structure of the table in the destination database; If it is "No", it means that the conversion is completed by the user-configured sql statement or "merging plug-in", and the "merging processor" does not participate in the conversion process.

7 Whether the merge process of the modified data is completed by the "Merge Processor", the child node is optional, and the default is "Yes", which means that the "Merge Processor" executes a series of user-configured sql or "Merge Plugin" to modify After the data is to be merged, the "Merge Processor" finally simply appends the contents of the table in the source database to the table of the same name in the destination database; if the configuration is "No", it indicates that the merge process of the table has been configured in the user's SQL or "Merge" The "plug-in" is completed, and the "merging processor" is no longer required to participate. For some complex business logics, the merge process cannot be merged by simple appending. In this case, the process of "merging the processor" can be masked in this way, completely The user merges the process of merging certain tables;

8 self-incrementing column and its corresponding sequence name, specify which column in the current table is an auto-increment column, child node, optional. The self-addition column on the physical definition, the "merging processor" can be identified from the system table of the database management system, so the user can automatically process even if the specific column name is not specified; in some scenarios, a column is not defined. Adding columns, but using sequence or other business logic algorithms to achieve similar auto-increment functions, the "merge processor" does not recognize this situation, so the user must specify the corresponding column name; if a sequence is used, it also needs Specify the sequence name. Based on these two pieces of information, the "Merge Processor" can use some algorithms to automatically modify the value of the column to avoid the constraint of violating the uniqueness of the self-incrementing column and synchronize the current value of the sequence;

9 self-additional association foreign key sub-table name and column name, is a child node of 7, optional, can be configured with multiple child nodes. If the table has an associated child table, and the parent-child relationship is maintained by the uniqueness of the parent table self-incrementing column, the corresponding child table name and column name need to be specified in the parent table policy, so that the merge main program is modifying the self table. The foreign key column of the associated sub-table can be automatically modified when the column is added;

10 Trigger names that need to be disabled, optional, child nodes, configurable multiple child nodes. Whether the specified trigger is disabled in the process of merging the current table according to the business logic is determined by the user, and the original state is restored after the merge is completed.

This strategy is applicable to a wide range of scenarios (3), (4), (5), (6), (7), (8), (9) that require special handling as described above. An example of an xml configuration is given below:

With the method of the present invention, all databases that need to participate in the merge are backed up in the database management system where the source database is located. Set the parameters of the database management system where the destination database is connected, and log in as the system administrator. Specify the backup file path of the source database. In the database management system where the destination database is located, create a temporary database for each source database and restore it. Parse the policy configuration file. All tables are merged concurrently according to the default processing flow and the user-specified merge policy. Restores the trigger state that was disabled during the merge process. Delete the temporary database. A common process for merging database content can be implemented. The overall merge process is completed by the merge main program; for tables that do not require special processing, the merge main program defaults the records in the source database. Append to the table of the same name in the destination database; for the table containing the complex business logic data that the merge main program cannot recognize, the developer specifies the merge policy in the policy configuration file, and the merge main program performs the data according to the merge strategy. Consolidation; in most application scenarios, developers only need to configure some SQL statements in the policy configuration file to modify the table data that cannot be directly merged. In extremely complex application scenarios, developers can implement their own coding. The secondary development plug-in is integrated into the merge process; in addition, the merge device uses the processing mechanism of the concurrent thread to improve the processing speed of the merge.

Among them, the building module includes:

Among them, the merge module includes:

The merging sub-module is set to use the sql statement and/or the merge plug-in to detect whether the table in the mirror database and the destination database can be merged with the form information according to the name of the detection policy when the detection policy has a detection policy. Then, according to the table merging strategy in the merging strategy, the source table of the mirror database is merged with the destination table in the destination database that needs to be merged with the source table.

The device is a device corresponding to the above method, and all the implementations in the foregoing method embodiments are applicable to the embodiment of the device, and the same technical effects can be achieved.

Specifically, as shown in FIG. 4, the specific structure of the device is as follows:

The above merge module may specifically be a merge processor, a merge processor built-in default merge process flow, and a set of alternative common merge policy process flow; the "merging main program" is responsible for parsing all "merger policy" information contained in the system. For the table in the system that does not specify the "merging policy", use the default process to merge. For the table that specifies the "merging policy", use the processing flow corresponding to the "merging policy" to merge. It should not rely on the business logic of a specific application, but only deal with data merging from the perspective of the database;

The merge policy, some tables can not be merged correctly through the default processing flow, the user needs to specify a special table merge strategy, so that the "merging processor" according to this table merge strategy for data merge; the syntax definition of the table merge strategy is also not It should rely on the business logic of a specific application software, but provide a table consolidation strategy that can be used by various application software.

Merging plug-ins, the complexity of the business logic merge process of some tables exceeds the scope that the "merging main program" can handle, and the user needs to specify the "merging plug-in" corresponding to the table in the "merging policy" for "merging the main program" The plugin is called to complete the merge work; the "merging plugin" is implemented by the user according to the standard interface defined by the merge device.

For example, the contents of the database A and the database A' are merged, and the merging device takes the database A as the source database and the database A' as the destination database, and the combined final result is embodied in the destination database A'. For the following scenarios, the "Merge Processor" has a default processing flow:

The first scenario: the table a in the source database A has a table a corresponding to the destination database A', and the table structure is the same. In this case, all the records of the table a in the source database A are all added by default. Go to table a in destination database A';

The second scenario: table a in source database A has a table a corresponding to it in destination database A', and the table structure is different (if the design is reasonable, the application should try to avoid this situation), this case Next, the default is to convert the structure of table a in source database A to be the same as the structure of table a in destination database A' (provided the application allows this conversion), and then merge in the first scenario. The processing flow of the conversion table structure may be automatically processed by the merge processor according to the difference of the table information obtained in the database system table, or may be processed according to the merge policy configured by the user.

The third scenario: the table a in the source database A has no corresponding table a in the destination database A'. In this case, the merge processor creates a table with the same name and the same structure in the destination database A'. And then merge in the manner of (1) scene;

The fourth scenario: Table a in the destination database A' has no table a corresponding to it in the source database A. In this case, no merge is required by default.

It can be seen that in the above four scenarios, the fourth scenario can be ignored, and the other three can be converted into the first scenario to be processed. However, the default processing method of the first scenario is the simplest merge mode (this is also the way the database management system comes with the data migration tool), which cannot adapt to complex business logic. Therefore, users need to configure some "merging strategies" to The merger process intervenes.

There are several scenarios for the special merge process that needs to specify the merge policy during the merge process:

(1) Before the merge, it is necessary to detect and identify some application scenarios that cannot be merged. For example, in the student registration information management system, two or more student records with the same identity card number are not allowed. If the two sets of database contents to be merged are detected before the merger, such conflicts should be prohibited. The merge process; for example, the contents of the two databases to be merged are generated by different versions of the application software, and the data structures are incompatible and should not be merged.

(2) Some tables do not need to participate in the merger. Some tables that hold initialization data must have the same data in each set of database content, and only need to retain one content in the destination database. Such tables should not be involved in the merger; for example, in the student registration information management system There is a table for storing the types of student cadres. When each system is installed, the database stage is automatically filled in with the records of all types of student cadres, and the table will not be added, deleted or modified during the system operation; Then, when merging the table records in the source database into the destination database, there will be two sets of duplicate records in the table, which will lead to business logic errors, so the table should not be merged.

(3) The definitions of some tables in the source database are inconsistent with the definitions in the destination database, and need to be converted to be consistent before being merged.

(4) Some tables only need to merge part of the content in the source database, not the entire content. For example, in the table of the student registration information management system for storing the types of student cadres, if some records in the table are automatically filled in when the system is installed, the system does not delete the records during the system operation. And modify the operation, but some user input cadre job category records may be added; when merging, the new records added by the user in the source database should be merged, and the records initialized during system installation should be merged; in more complicated cases If the initialization data of the table is also deleted and modified during the system operation, then in theory, all the records of the source database and the cadre position in the destination database should be merged into the destination database, and the cadre positions have the same name. The records should not be merged.

(5) Some tables may violate data constraints when merging—such as primary key conflicts, violation of uniqueness constraints, etc. (table structure definitions or business logic constraints, can be considered as violations of data constraints). For records that violate data constraints, the application logic of the application software is different, and the adopted strategy may be different; the user may choose to delete the source database. After the unconstrained records are merged, you can also choose to modify the records in the source database that violate the constraints before merging.

(6) Some tables use self-incrementing columns (or sequences), and there may be conflicting IDs when merging. You can eliminate the conflicting ID by adding a fixed value to the self-incrementing column in the source database (or the column using the sequence), and then perform the merge operation; this fixed value can be obtained by using this algorithm: fixed value = The maximum value of the column in the destination database is +1 - the minimum value of the column in the source database; for the self-incrementing column, the self-incrementing column insertion feature should be turned on when merging records, so that the ID value of the inserted record can be retained; It is not automatically synchronized with the table record, so you should also adjust the current value of the sequence in the destination database, plus the same fixed value.

(7) Some tables have parent-child dependencies—such as foreign key dependencies (table structure definitions or business logic dependencies, can be regarded as existential dependencies), and the related content of the dependent parent table is increased during the merge. Pruning operations - such as the (4) and (5) scenarios described above, then the dependent subtable should also be modified accordingly to continue to satisfy the original dependencies; in the (6) scenario For example, if you add a dependency to a foreign key, if the dependent column of the dependent parent table adds a fixed value to avoid the constraint conflict, the associated column of the dependent child table needs to be the same when merging. Fixed value.

(8) Some databases or tables have sequential dependencies in the order of merging, and need to be assigned a priority level. By default, in order to increase the speed of merging, for the same priority level table, the merging device should perform the merging process of multiple tables in a concurrent manner. There is no order between those tables before and after; if some tables specify more If the priority is high, the merge should be performed first. After all the tables in the priority are merged, the merge process of the lower priority table can be performed.

(9) Some tables use triggers, and it is necessary to decide whether to disable triggers depending on the business logic. For example, in some applications, the use of triggers and sequences to simulate the effect of self-incrementing columns (there is no concept of self-incrementing columns in Oracle), each time a record is inserted into the corresponding table, the trigger is automatically called. Logic, modify the incrementing integer whose ID is a sequence; when merging the table, if the table is not dependent on other tables, you can safely use the function of the trigger in the destination database to automatically generate an incremental ID for the merged record; if the table Dependent on other tables, it is recommended to temporarily disable the trigger in the destination database, but use the method described in the (6) scenario to merge the records, and then restore the trigger state in the destination database after the merge, the benefits of doing so When merging dependent subtables, you only need to add the same fixed value to the associated columns of dependent subtables, which is more efficient and the processing flow is simpler.

(10) Some tables have inconsistent structures defined in the source database and the destination database, and the process of the default conversion source database table structure of the merge processor cannot adapt to the business logic, and the user needs to implement the conversion process by himself.

For the special processing scenarios of several combinations of appeals, the user needs to specify the merge policy definition corresponding to the specific table in the "Merge Policy". If there is no table specifying the merge policy, all the records of the table in the source database are all added to the destination database by default. Inside the table.

In addition, the above various application scenarios, in most cases, can be implemented by executing the sql statement, for example, the detection process before the merge in the first scenario, usually only needs to perform some cross-database connection query to judge; However, if the source database and the destination database are not in the same database management system, it is difficult to implement the detection function by simply using the sql statement, and the user needs to implement a special "merging plug-in" to complete this function, which will increase the user's use of the merge. The difficulty of the device; therefore, in order to simplify the merging process, as far as possible, the user only needs to understand sql knowledge to complete most of the merging functions, and the source database and the destination database need to be integrated in the same database management system. Therefore, the merging device is The first steps in your work are:

The merging device creates a temporary mirror database in the database management system where the destination database is located, restores the source database to the temporary mirror database through the backup recovery function (or other data migration method), and then uses the temporary mirror database as the source database. . This allows the user to access both the source and destination databases in the sql statement.

For the scenario of the special merge process that needs to specify the "merging policy" described above, the merging device summarizes the corresponding merging strategy and built its function into the "merging processor" for the user to use various configuration files. Set the "merging strategy" for some special tables for the carrier. There are mainly the following merger strategies:

(1) Database policy, specify the name of the database that needs to participate in the consolidation. If there are multiple databases that need to be merged, you need to configure multiple database policies. The strategy includes the following:

1 source database name;

2 destination database name;

3 Priority level, optional. If you do not specify a priority level, the default is the lowest level; because the merge of the database is finally converted into the merging of the child elements of the database, so the set value of the database priority will be inherited by its child elements. Sub-elements with higher priority are merged first, sub-elements with lower priority are merged, and sub-elements of the same priority can be merged concurrently.

An example of an xml configuration is given below:

<! --Database policy: Specify the source and destination database names that need to participate in the merge—>

</database>

<! --level specifies the priority level. The higher the value, the higher the level. If not specified, the default is 0-->

</database>

(2) Detection strategy, optional, can configure multiple detection strategies, including the following:

1 policy name, optional; some "merging plug-ins" may need to identify this name, according to the different names to achieve different processing procedures;

2 detecting the failure information, for the "combining processor" to record the log or display it to the user;

3sql statement, child node, optional, can be configured with multiple child nodes, the agreement to return a certain value is the detection failure;

Note: For sql, it is important to explain that in most cases, the user-configured sql needs to access both the source database and the destination database. However, since the merge device replaces the source database with a temporary mirror database, the real source database name should be Temporary mirror database name, in order to avoid the duplicate name conflict between the temporary database name and the database name in the destination database (two databases with the same name cannot be created in a database management system), the merging device can use an algorithm to generate a unique string. As the name of the temporary mirror database, it is unpredictable for the user, so when specifying the source database name in the sql statement, do not use the real database name, but instead use the macro definition, for example, use %SOURCE ( Db1)% indicates the temporary mirror database name of the source database db1, and the "merge processor" will automatically replace this macro definition with the actual database name when executing sql.

4 merge plug-in name, optional, used in the case that the sql statement cannot be detected, for the "merging processor" to call the user-implemented "merging plug-in" for detection; usually, the merged plug-in name is the user-developed secondary development Class name.

3 and 4 can be selected either, or they can exist at the same time. If any one fails, the detection fails.

This strategy is for the scenario (1) described above that requires special handling. An example of an xml configuration is given below:

(3) The non-merging policy of the table, which is a child node of the database policy, includes the following contents:

1 table name, specifying the specific table corresponding to the policy;

2 Strategy type, which is the "not merged" type.

This strategy is for the scenario (2) that needs special handling as described above in this article. The specified table does not participate in the merge, and only the table data of the same name in the destination database is retained. An example of an xml configuration is given below:

(4) The modified content merge policy of the table, which is a child node of the database policy, is set to modify the data to be merged during the merge process, and includes the following contents:

1 table name, specifying the specific table corresponding to the policy;

2 strategy type, which is the "modification" type;

The priority level is optional. If you do not specify a priority level, the default is to inherit the priority of the parent node database. After the table with the highest priority is merged, the table with the lower priority level can be merged. For tables with foreign key dependencies, The "Merge Processor" is automatically recognized from the system table of the database management system, and then the priority is automatically set, without the user having to set the priority.

6 Whether the "consolidation processor" is used to complete the conversion process of the table structure in the source database and the destination database, child nodes, optional. The default is "Yes". When the table structure is inconsistent in the source database and the destination database, the "merge processor" is used to convert the structure of the table in the source database to be the same as the structure of the table in the destination database; if "No" If the conversion is indicated by the user-configured sql statement or "merging plug-in", the "merging processor" does not participate in the conversion process.

The apparatus of the present invention can implement a general flow of database content merging. The overall merge process is completed by the merge main program; for tables that do not require special processing, the merge main program defaults the table records in the source database to the same name table in the destination database; for complex business logic that is not recognized by the merge main program The table of data, the developer specifies its merge strategy in the policy configuration file, and the merged main program merges the data according to its merge strategy; in most application scenarios, the developer only needs to configure some SQL statements in the policy configuration file. The purpose of modifying the table data that cannot be directly merged can be achieved. In an extremely complex application scenario, the developer can integrate the secondary development plug-in implemented by the code into the merge process; in addition, the merge device adopts the processing mechanism of the concurrent thread. , improve the processing speed of the merger.

The above description is only a preferred embodiment of the present invention, and it should be noted that those skilled in the art can also make several improvements and retouchings without departing from the principles of the present invention. It should be considered as the scope of protection of the present invention.

Industrial applicability

As described above, a database content merging method and apparatus provided by an embodiment of the present invention have the following beneficial effects: a general flow of database content merging can be implemented. For tables that do not require special processing, the table records in the source database are appended to the table of the same name in the destination database by default by merging the main program; for the table containing the complex business logic data that is not recognized by the merge main program, by the policy configuration file Specify the corresponding merge strategy in the merged main program according to the merge strategy to merge the data; for most application scenarios, the developer only needs to configure some SQL statements in the policy configuration file to achieve the modification cannot be directly merged. The purpose of the tabular data, in the extremely complex application scenario, the developer can integrate the secondary development plug-in implemented by the encoding into the merge process; in addition, the present invention also adopts the processing mechanism of the concurrent thread to improve the processing of the merge. speed.

Claims

A database content merging method includes:

Establishing a mirror database corresponding to the source database, where the mirror database is the same as the source database;

And according to the merging policy corresponding to the source table in the mirror database, the source table of the mirror database is merged with the destination table in the destination database that needs to be merged with the source table.
The method of claim 1, wherein the step of establishing a mirror database corresponding to the source database comprises:

Back up the source database to get a backup file carrying the form information;

Select the database management system to which the destination database belongs;

Establishing a mirror database corresponding to the source database in the database management system, and adding table information carried in the backup file to the mirror database.
The method of claim 1, wherein the source table of the mirror database and the destination database need to be merged with the source table according to a merge policy corresponding to the source table in the mirror database Forms, the steps for merging the form information include:

Obtaining a merge policy corresponding to the source table in the mirror database in the configuration file;

When there is a detection policy in the merge policy, according to the detection strategy, the sql statement and/or the merge plug-in is selected to detect whether the table in the mirror database and the destination database can be combined with the form information, and if the detection is successful, according to the merge policy The table merge strategy combines the source tables of the mirror database with the destination tables in the destination database that need to be merged with the source tables.
The method according to claim 3, wherein, according to the table merging policy in the merging policy, the source table of the mirror database and the destination table in the destination database that need to be merged with the source table are used to perform table information. The steps to merge include:

Obtain a collection of all source tables in the mirror database;

Establishing a correspondence between the table of the source table and the table to be merged with the destination table in the destination database, and the correspondence with the table merging strategy;

The table information is merged in the priority table in the source table and the destination table in the destination database.
The method of claim 4 wherein the step of merging the form information for the destination form in the source form and the destination database comprises:

Obtaining a table merging strategy corresponding to the source table in the collection of source tables;

If the table merging strategy is a specified merging strategy and the specified merging strategy type is merging or modifying the policy, it is determined whether there is a destination table with the same name as the source table in the destination database, and if present, the source table and the same name as the source table The destination form merges the form information. Otherwise, a destination form with the same name as the source form is created in the destination database, and the form information is merged between the source form and the destination form.
The method of claim 5 wherein the step of merging the form information with the source form and the destination form comprises:

Determining whether the table structure of the source table and the destination table are consistent, and if so, combining the table information with the source table and the destination table according to the specified merge policy of the source table; otherwise, converting the table structure of the source table to the table with the destination table After the structure is consistent, the form information is merged between the source table and the destination table according to the specified merge policy of the source table.
The method of claim 6, wherein the step of merging the form information with the source form and the destination form according to the specified merge policy of the source form comprises:

If the source table does not specify a merge policy, the total number of records of the source table is obtained;

If the total number of records in the source table is not 0, an insert statement is executed to insert all the records in the source table into the destination table.
The method of claim 6, wherein the step of merging the form information with the source form and the destination form according to the specified merge policy of the source form comprises:

If the source table has a specified merge policy, modify the source table according to the specified merge policy to obtain a record of the modified source table;

Insert the record of the modified source table into the destination table.
The method of claim 8 wherein the step of modifying the source form to obtain a record of the modified source form comprises:

Modifying or deleting the record in the source table that violates the constraint rule or the record of the primary key conflict, and obtains the record of the modified source table; or

Adding a fixed value to the self-incrementing column or sequence in the source table to obtain a record of the modified source table; or

There is a foreign key dependency on the source table, and in the case of modifying the dependent parent table, the dependent content table is also modified to obtain a record of the modified source table.
The method of claim 8 wherein the step of inserting the record of the modified source form into the destination form comprises:

If the destination table uses a trigger and the destination table is not dependent on other tables, the incremental ID of the record is automatically generated by the trigger when the record of the source table is inserted in the destination table; otherwise, when the record of the source table is inserted in the destination table , the trigger is disabled, and after the record insertion is completed, the trigger is restored to the state before the merge.
A database content merging device, comprising:

Establishing a module, configured to establish a mirror database corresponding to the source database, where the mirror database is the same as the source database;

The merging module is configured to merge the source table of the mirror database with the destination table of the target database that needs to be merged with the source table according to a merging policy corresponding to the source table in the mirror database.
The apparatus of claim 11 wherein the establishing module comprises:

The backup sub-module is set as a backup source database to obtain a backup file carrying the form information;

The connection submodule is set to select a database management system to which the destination database belongs;

And establishing a sub-module, configured to establish a mirror database corresponding to the source database in the database management system, and add table information carried in the backup file to the mirror database.
The apparatus of claim 11 wherein the merging module comprises:

Obtaining a submodule, configured to obtain a merge policy corresponding to the source table in the mirror database in the configuration file;

The merging sub-module is set to use the sql statement and/or the merge plug-in to detect whether the table in the mirror database and the destination database can be combined with the table information according to the detection policy, and if the detection is successful, According to the table merging strategy in the merging strategy, the source table of the mirror database is merged with the destination table in the destination database that needs to be merged with the source table.