CN105320681A - Database content combination method and device - Google Patents

Abstract

The present invention provides a database content merging method and device, wherein the method includes: establishing a mirror database corresponding to the source database, the mirror database is completely the same as the source database; according to the merge A strategy for merging table information between the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table. Through the solution provided by the invention, the problem of merging application programs of complex business logic in the prior art is solved, and the capability of processing database content merging is improved.

Description

Method and device for merging database content

technical field

The invention relates to the field of computers, in particular to a database content merging method and device.

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 merge two sets of independent database contents of the same application software into one set of database contents for management.

For the merging of databases, the data migration tools provided by the database management system are usually used for data merging and migration. However, since this data migration tool is a general tool, it can only handle simple data migration logic, such as appending data from a table in database A to a certain table in database B.

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

Contents of the invention

The object of the present invention is to provide a method and device for merging database content, which solves the problem of merging application programs for complex business logic in the prior art.

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

Establish a mirror database corresponding to the source database, which is the same as the source database in the mirror database;

According to the merging strategy 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.

Wherein, the step of establishing a mirror image database corresponding to the source database includes:

Back up the source database to obtain a backup file with table information;

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

A mirror database corresponding to the source database is established in the database management system, and the table information carried in the backup file is added to the mirror database.

Wherein, according to the merging strategy corresponding to the source table in the mirror database, the step of merging table information on the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table includes :

Obtain the merge strategy corresponding to the source table in the mirror database in the configuration file;

When there is a detection strategy in the merging strategy, according to the detection strategy, select the sql statement and/or merge plug-in to detect whether the tables in the mirror database and the destination database can perform table information merging, if the detection is successful, then according to the merging strategy A table merging strategy is used to merge table information between the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table.

Wherein, according to the table merging strategy in the merging strategy, the step of merging table information for the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table includes:

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

Establish the corresponding relationship between the tables in the set of source tables that need to be merged with the destination table in the destination database and the table merge strategy;

The source table and the destination table in the destination database are merged according to the order of priority.

Wherein, the step of merging table information on the source table and the destination table in the destination database includes:

Obtain the table merging strategy corresponding to the source table in the set of source tables;

If the table merging strategy is a designated merging strategy and the designated merging strategy type is a merge or a modification strategy, then it is judged whether there is a destination table with the same name as the source table in the destination database, and if it exists, the source table and the source table with the same name as the source table Merge the table information of the destination table, otherwise, create a destination table with the same name as the source table in the destination database, and then merge the table information of the source table and the destination table.

Wherein, the steps of merging the table information of the source table and the destination table include:

Determine whether the table structure of the source table and the destination table are consistent, if so, merge the table information of the source table and the destination table according to the specified merge strategy of the source table, otherwise, convert the table structure of the source table into the table structure of the destination table After the structures are consistent, the table information of the source table and the destination table are merged according to the specified merging strategy of the source table.

Wherein, according to the specified merging strategy of the source table, the step of merging the table information of the source table and the destination table includes:

If the source table does not specify a merge strategy, obtain the total number of records in the source table;

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.

Wherein, according to the specified merging strategy of the source table, the step of merging the table information of the source table and the destination table includes:

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

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

Wherein, the steps of modifying the source table and obtaining the records of the modified source table include:

Modifying or deleting the records in the source table that violate the constraint rules or the records with primary key conflicts to obtain the modified records in the source table; or

Adding a fixed value to an auto-increment column or sequence in the source table to obtain a modified record in the source table; or

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

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

If the target table uses a trigger and the target table is not dependent on other tables, when inserting the record of the source table in the target table, use the trigger to automatically generate the incremental ID of the record; otherwise, when inserting the record of the source table in the target table , disable the trigger, and restore the trigger to the state before the merge after the record insertion is completed.

Embodiments of the present invention also provide a database content merging device, including:

Establishing a module for establishing 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, according to the merging strategy corresponding to the source table in the mirror database, merge table information between the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table.

Among them, building modules include:

The backup sub-module is used to back up the source database to obtain a backup file carrying table information;

The connection sub-module is used to select the database management system to which the target database belongs;

The establishment sub-module is used to establish a mirror database corresponding to the source database in the database management system, and add the table information carried in the backup file to the mirror database.

Among them, the merge module includes:

The obtaining submodule is used to obtain the merge strategy corresponding to the source table in the mirror database in the configuration file;

The merging submodule is used for when there is a detection strategy in the merging strategy, according to the detection strategy, select the sql statement and/or merge plug-in to detect whether the tables in the mirror database and the destination database can perform table information merging, if the detection is successful, 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.

Above-mentioned technical scheme of the present invention has following technical effect:

By adopting the method of the present invention, a general flow of database content merging can be realized. For tables that do not require special processing, the table records in the source database are appended to the table with the same name in the destination database by default directly through the merge main program; Specify the corresponding merging strategy in , and the merging main program will merge data according to the merging strategy; for most application scenarios, developers only need to configure some sql statements in the strategy configuration file to modify the data that cannot be directly merged In extremely complex application scenarios, developers can integrate the secondary development plug-ins implemented by their own codes into the merge process; in addition, the present invention also adopts a concurrent thread processing mechanism to improve the merge process speed.

Description of drawings

FIG. 1 is a flowchart of a method for merging database content provided by an embodiment of the present invention;

2 is a flow chart of a specific embodiment of a method for merging database content provided by an embodiment of the present invention;

FIG. 3 is a flow chart of table merging in the method for merging database content provided by an embodiment of the present invention;

Fig. 4 is a structural block diagram of a device for merging database content provided by an embodiment of the present invention.

detailed description

In order to make the purpose, technical solutions and advantages of the embodiments of the present invention more clear, specific embodiments will be described in detail below with reference to the accompanying drawings.

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

Step 11, establishing a mirror database corresponding to the source database, wherein the mirror database is the same as the source database;

Step 12: According to the merging strategy corresponding to the source table in the mirror database, perform table information merging on the source table in the mirror database and the destination table in the destination database that needs to be merged with the source table.

In this embodiment of the present invention, by establishing a mirror database exactly the same as the source database, and according to the merge strategy corresponding to the source table in the mirror database, the source table of the mirror database and the destination database need to be merged with the source table The purpose of the table is to merge table information; thus, it is possible to merge two sets of independent database contents of the same application software into one set of database contents, which can adapt to various complex business logics.

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

Step 111, backing up the source database to obtain a backup file carrying table information;

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

Step 113, establishing a mirror database corresponding to the source database in the database management system, and adding the 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 methods), and then the temporary The mirror database acts as the source database. This enables users to simultaneously access the source and destination databases in SQL statements.

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

Step 121, obtaining the merge strategy corresponding to the source table in the mirror database in the configuration file;

Step 122, when there is a detection strategy in the merging strategy, according to the detection strategy, select the sql statement and/or merge plug-in to detect whether the tables in the mirror database and the destination database can perform table information merging, if the detection is successful, then according to the described The table merging strategy in the merging strategy is to merge table information between the source table of the mirror database and 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, users need to configure some "merging strategies" to intervene in the merging process. Some scenarios during the merging process do not need to be merged. Therefore, it is necessary to Perform detection to identify some application scenarios that cannot be merged. For example, in the student registration information management system, two or more student records with identical ID numbers are not allowed to appear. If such conflicts are detected in the contents of the two databases to be merged before the merger, follow-up should be prohibited. The merging process; for another example, the contents of two databases to be merged are generated by different versions of application software, and the data structures are incompatible, so they should not be merged. Detect before merging, and according to the table merging strategy in the merging strategy, perform table information merging on the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table, which can improve business processing efficiency .

In the above-mentioned embodiment, in step 122, according to the table merging strategy in the merging strategy, the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table are processed for table information. The specific implementation process of the merger may include:

Step 1221, obtaining the set of all source tables in the mirror database;

Step 1222, establishing the corresponding relationship between the table that needs to be merged with the destination table in the destination database and the table merge strategy in the set of source tables;

Step 1223, performing table information merging on the source table and the destination table in the destination database in order of priority.

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

(1) Back up all source databases that need to participate in the merger, and obtain their backup files;

(2) The database management system where the specified target database is located is successfully connected as a system administrator (or other roles that meet the operating authority);

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

(4) Parsing all merge strategies in the configuration file;

(5) According to the detection strategy in the merge strategy, perform the detection work before merging. If the return fails, go to step (9) to end the merge work; if the return is successful, or if no detection strategy is configured, continue to process the subsequent steps ;

(6) The merging device sequence obtains all table information collections in all source databases to be merged, and then combines all the merging strategies obtained in step (2) to establish a mapping relationship between all tables to be merged and the merging strategy. Here, to be merged The table is: in the set of source tables, the table that needs to be merged with the destination table in the destination database, and sorted according to the priority defined in the merge strategy;

(7) Concurrently process all forms of the same priority level in the order of priority from high to low, and after the forms of this priority level are all processed, then process the forms with lower priority levels until all forms are processed;

(8) The temporary database created by the merge device deletes;

(9) The merger work is over.

In the above-mentioned embodiments of the present invention, the specific implementation process of merging table information on the source table and the destination table in the destination database includes:

Step 12231, obtain the table merging strategy corresponding to the source table in the set of source tables;

Step 12232, if the table merging strategy is a specified merging strategy and the specified merging strategy type is merging or modification strategy, then judge whether there is a target table with the same name as the source table in the set of source tables in the destination database, and if so, then Merge the table information of the source table and the destination table with the same name as the source table in the source table and the destination database, otherwise, create a destination table with the same name as the source table in the destination database, and then merge the source table and the destination table.

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

Determine whether the table structure of the source table and the destination table are consistent, if so, merge the table information of the source table and the destination table according to the specified merge strategy of the source table, otherwise, convert the table structure of the source table into the table structure of the destination table After the structures are consistent, the table information of the source table and the destination table are merged according to the specified merging strategy of the source table.

Further, in step 12232, according to the specified merging policy of the source table, the step of merging the source table and the destination table includes:

Step 122321, if the source table does not specify a merge strategy, 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 merging strategy, modify the source table according to the specified merging strategy to obtain the modified records of the source table; insert the modified records of the source table into the destination table.

In step 122322, the source table is modified, and the steps of obtaining the modified source table records include:

Modifying or deleting the records in the source table that violate the constraint rules or the records with primary key conflicts to obtain the modified records in the source table; or

Adding a fixed value to an auto-increment column or sequence in the source table to obtain a modified record in the source table; or

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

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

If the target table uses a trigger and the target table is not dependent on other tables, when inserting the record of the source table in the target table, use the trigger to automatically generate the incremental ID of the record; otherwise, when inserting the record of the source table in the target table , disable the trigger, and restore the trigger to the state before the merge after the record insertion is completed.

The specific implementation process of merging the tables is shown in Figure 3, including:

① Find the table merging strategy corresponding to the table. If the table merging strategy is specified and the strategy type is no merging strategy, the table merging process ends here, and go to step ⑧; if the table does not specify a table merging strategy, or the strategy type To modify the strategy, continue the subsequent merge process;

②If the table with the same name does not exist in the destination database, create a table with the same name in the same structure and go to step ④;

③ Obtain the structure information of the table in the source database and the target database, and compare whether the table structure is consistent; if they are consistent, go to the next step; if they are not consistent, determine whether the table has specified a table merge strategy, if no table merge strategy is specified, or If the table merging strategy is specified and the "merging processor" is set to complete the conversion process where the table structures in the source database and the destination database are inconsistent, the merging device will automatically convert the table structure in the source database to make it consistent with the table structure in the destination database ; If a merge strategy is specified and the merge strategy setting does not allow the "merge processor" to complete the conversion process of inconsistent table structures in the source database and the destination database, do nothing and go to the next step;

④ If the table does not specify a table merging strategy, then obtain the total number of records of the table in the source database, if the total number of records is 0, then go to step ⑧ to end the merging process of the table, otherwise go to step ⑥ to perform additional merging of table records;

⑤ If the table specifies a merging strategy, the merging device executes a series of steps to modify the database content according to the strategy; if the merging strategy setting does not allow the "merging processor" to complete the merging process of the modified data, then go to step ⑦, Otherwise, continue to the following steps;

⑥ Execute the insert statement, and insert all the records of the table in the source database into the table with the same name in the destination database;

⑦If the table specifies a merge strategy, and the strategy setting disables certain triggers, restore the triggers in the destination database to the state before the merge;

⑧ The table merging process ends.

In the above process, specifically, the table modification content merge policy is a child node of the database policy, which is used to modify the data to be merged during the merge process, including the following content:

① Table name, specify the specific table corresponding to the strategy;

②Strategy type, which is "Modify" type;

③Priority level, optional. If no priority level is specified, the default is to inherit the priority level of the parent node database. After the table with high priority level is merged, the table with low priority level can be merged. For tables with foreign key dependencies, the "merge processor" can automatically identify them from the system tables of the database management system, and then automatically set the priority, which does not require the user to set the priority.

④sql statement, sub-node, optional, multiple sub-nodes can be configured, used to modify the table structure or data content in the source database or the destination database (not only limited to modifying the currently processed table, but also other associated dependent tables);

⑤Merge plug-in name, sub-node, optional, multiple sub-nodes can be configured, used for merging the main program to call the secondary development merging plug-in implemented by the user in the case of complex business logic that cannot be processed by the sql statement before merging;

⑥Whether the "merge processor" completes the conversion process of inconsistent table structures in the source database and the destination database, child nodes, optional. The default is "Yes", when the structure of the table in the source database and the destination database are inconsistent, the "merge processor" will convert the structure of the table in the source database to make it the same as the structure of the table in the destination database; if it is "No" If , it means that the conversion is completed by the sql statement configured by the user or the "merge plug-in", and the "merge processor" does not participate in the conversion process.

⑦Whether the merge process of the modified data is completed by the "merge processor", sub-node, optional, the default is "Yes", which means that the "merge processor" executes a series of user-configured sql or "merge plug-in" to modify After the data is to be merged, the "merge processor" will simply append the contents of the table in the source database to the table with the same name in the destination database; if the configuration is "No", it means that the merge process of the table has been configured in the user-configured sql or "merge "plug-in", no longer need "merge processor" to participate, for some complex business logic, can not be merged by simple appending, then you can block the process of "merge processor" in this way at this time, completely The merge process of some tables is led by the user;

⑧Auto-increment column and its corresponding sequence name, specify which column in the current table is an auto-increment column, child node, optional. The auto-increment column in the physical definition can be identified by the "merge processor" from the system table of the database management system, so even if the user does not specify the specific column name, it can be automatically processed; in some scenarios, although a column is not defined As self-increment column, but it uses sequence or other business logic algorithm to achieve similar self-increment function, "merge processor" cannot recognize this situation, so the corresponding column name must be specified by the user; if sequence is used, also need Specify the sequence name, and the "merge processor" can use some algorithms to automatically modify the value of the column based on these two pieces of information, so as to avoid violating the uniqueness constraint of the auto-increment column and synchronize the current value of the sequence;

⑨The name and column name of the self-incrementing column-associated foreign key sub-table, which is a sub-node of ⑦, optional, and multiple sub-nodes can be configured. If the table has an associated child table, and the parent-child relationship is maintained through the uniqueness of the auto-increment column of the parent table, you need to specify the corresponding When adding a column, it can automatically modify the foreign key column of the associated subtable;

⑩The name of the trigger that needs to be disabled, optional, sub-node, multiple sub-nodes can be configured. It is up to the user to decide whether to disable the specified trigger during the process of merging the current table according to the business logic, and restore its original state after the merging is completed.

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

<databasesource="db1"destination="db1">

<! -- Merge table strategy that needs to be modified, name specifies the table name, strategy specifies the strategy type,

level specifies the priority level,

-->

<tablename="table_c" strategy="modify" level="1">

<! --sql statement, modify the data content, delete the record with duplicate id in the source database -->

<sql>deletefrom%SOURCE(db1)%.table_cwhereidin(selectidfromdb1.table_c)</sql>

<! -- merge plugin name -->

<plugin>com.merge.plugin.table.test</plugin>

<! -- Specify the merge processor to complete the conversion process of table structure differences -->

<transformByTool>true</transformByTool>

<! -- Specify the merge processor to complete the merge process of the modified data -->

<mergeByTool>true</mergeByTool>

<! --coluemn specifies the auto-increment column name, sequence specifies the sequence name -->

<identitycolumn="c1"sequence="seq1">

<! —Associate the sub-table of the auto-increment column, specify the source database name, table name, column name-->

<childTabledb="db1" name="table_d"column="cc1"></>

</identity>

<! -- Specify the name of the trigger to be disabled -->

<disableTriggerdb="db1" name="tg1"></disableTrigger>

</table>

</database>

By adopting the method of the present invention, all databases that need to be merged are backed up in the database management system where the source database is located. Set the parameters of the database management system where the connection target database is located, and log in as a system administrator. Specify the backup file path of the source database, and create and restore a temporary database for each source database in the database management system where the target database is located. Parse policy configuration files. Merge all tables concurrently according to the default processing flow and user-specified merge strategy. Restores the state of triggers that were disabled during the merge. Delete the temporary database. A common flow of database content merging can be implemented. The overall merging process is completed by the merging main program; for tables that do not require special processing, the merging main program will append the table records in the source database to the table with the same name in the destination database by default; for complex business logic that cannot be recognized by the merging main program For the data table, the developer specifies its merging strategy in the strategy configuration file, and the merging main program merges the data according to its merging strategy; in most application scenarios, developers only need to configure some sql statements in the strategy configuration file The purpose of modifying table data that cannot be directly merged can be achieved. In extremely complex application scenarios, developers can integrate the secondary development plug-in implemented by their own coding into the merge process; in addition, the merge device adopts a concurrent thread processing mechanism , to improve the processing speed of merging.

Embodiments of the present invention also provide a database content merging device, including:

Establishing a module for establishing 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, according to the merging strategy corresponding to the source table in the mirror database, merge table information between the source table of the mirror database and the destination table in the destination database that needs to be merged with the source table.

Among them, building modules include:

The backup sub-module is used to back up the source database to obtain a backup file carrying table information;

The connection sub-module is used to select the database management system to which the target database belongs;

The establishment sub-module is used to establish a mirror database corresponding to the source database in the database management system, and add the table information carried in the backup file to the mirror database.

Among them, the merge module includes:

The obtaining submodule is used to obtain the merge strategy corresponding to the source table in the mirror database in the configuration file;

The merging submodule is used for when there is a detection strategy in the merging strategy, according to the name of the detection strategy, select the sql statement and/or the merging plug-in to detect whether the tables in the mirror database and the destination database can perform table information merging, 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 device is a device corresponding to the above-mentioned method, and all the implementation modes in the above-mentioned method embodiments are applicable to the embodiments of the device, and can also achieve the same technical effect.

Specifically, as shown in Figure 4, the specific framework of the device is as follows:

The above-mentioned merging module can specifically be a merging processor, which has a built-in default merging process flow and a set of alternative general merging strategy processing flows; the "merging main program" is responsible for parsing all the "merging strategy" information contained in the system , for tables that do not specify a "merge strategy" in the system, use the default process for merging, and for tables that specify a "merge strategy", use the process flow corresponding to the "merge strategy" for merging. It should not rely on the business logic of a specific application software, but only handle data merging from the perspective of the database;

Merge strategy, some tables cannot be merged correctly through the default processing flow, users need to specify a special table merge strategy, so that the "merge processor" performs data merge according to this table merge strategy; the syntax definition of the table merge strategy is also different It should rely on the business logic of a specific application software, but provide a common table merging strategy for various application software.

Merge plug-in, the complexity of the business logic merge process of some tables is beyond the range that the "merge main program" can handle, and the user needs to specify the "merge plug-in" corresponding to the table in the "merge strategy" for the "merge main program" Call the plug-in to complete the merge work; the "merge plug-in" is implemented by the user according to the standard interface defined by the merge device.

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

Scenario 1: Table a in source database A has a corresponding table a in destination database A' with the same table structure. In this case, all records in table a in source database A are appended by default. Go to the table a in the destination database A';

The second scenario: table a in the source database A has a corresponding table a in the destination database A', and the table structure is different (if the design is reasonable, the application should try to avoid this situation), this situation By default, the structure of table a in the source database A needs to be converted to make it the same as the structure of table a in the destination database A' (the prerequisite is that the application allows this conversion), and then merge in the first scenario ; The processing flow of converting the table structure can be processed automatically by the merge processor according to the difference of the table information obtained from the database system table, or according to the merge strategy configured by the user.

The third scenario: 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 them in the way of the (1) scenario;

The fourth scenario: table a in the target database A' has no corresponding table a in the source database A. In this case, no merging is required by default.

It can be seen that among the above four scenarios, the fourth scenario can be ignored, and the other three can be transformed into the first scenario for processing. However, the default processing method of the first scenario is the simplest merge method (this is also the processing method of the built-in data migration tool of the database management system), which cannot adapt to complex business logic, so users need to configure some "merge strategies" to Merge process to intervene.

Scenarios of a special merge process that require specifying a "merge strategy" during the merge process generally include the following:

(1) Before merging, detection is required to identify some application scenarios that cannot be merged. For example, in the student registration information management system, two or more student records with identical ID numbers are not allowed to appear. If such conflicts are detected in the contents of the two databases to be merged before the merger, follow-up should be prohibited. The merging process; for another example, the contents of two databases to be merged are generated by different versions of application software, and the data structures are incompatible, so they should not be merged.

(2) Some tables do not need to participate in the merge. Some tables that store initialized data must have the same data in each set of database content, and only one content in the target database needs to be reserved. Such tables should not participate in the merger; for example, in the student registration information management system There is a table used to save the types of student cadre positions. When each system is installed, the records of all student cadre position types are automatically filled in during the database initialization stage, and the table will not be added, deleted or modified during the system operation; Then when merging, if the table records in the source database are merged 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 definition of some tables in the source database is inconsistent with the definition in the destination database, and needs to be converted to be consistent before merging.

(4) Some tables only need to merge part of the content in the source database, not all of the content. Also take the table used to store student cadre positions in the student registration information management system as an example. If some records in this table are automatically filled in when the system is installed and initialized the database, these records will not be deleted during system operation. and modification operations, but some records of cadre position types input by users may be added; when merging, the records added by users in the source database should be merged instead of the records initialized during system installation; in more complicated cases , if the initialization data of the table will also be deleted and modified during system operation, then theoretically all records with different cadre position names in the source database and the target database should be merged into the target database, but the cadre position names are the same records should not be merged.

(5) Some tables may violate data constraints when merging - such as primary key conflicts, violations of unique constraints, etc. (Table structure definition or business logic constraints can be regarded as violations of data constraints). For records that violate data constraints, the business logic of the application software is different, and the strategies adopted may be different; users can choose to delete the records that violate the constraints in the source database before merging, or choose to modify the records that violate the constraints in the source database before merging. to merge.

(6) Some tables use self-incrementing columns (or sequences), and there may be conflicting IDs when merging. You can uniformly add a fixed value to the self-incrementing columns (or columns using sequences) in the source database to eliminate conflicting IDs, and then perform the merge operation; this algorithm can be used to obtain this fixed value: fixed value = The maximum value of the column in the destination database + 1 - the minimum value of the column in the source database; for auto-increment columns, the auto-increment column insertion feature should be enabled when merging records, so that the ID value of the inserted record can be retained; for sequences, because its It will not be automatically synchronized with the table records, so the current value of the sequence in the destination database should also be adjusted, and the same fixed value should also be added.

(7) Some tables have parent-child dependencies—such as foreign key dependencies, etc. (table structure definition or business logic dependencies can be regarded as dependencies), and the relevant content of the dependent parent table is added when merging Deletion operation - such as the above-mentioned (4) and (5) scenarios, then the dependent sub-table should also be modified accordingly so as to continue to meet the original dependencies; in the (6) scenario For example, if the auto-increment column is a foreign key dependency, if the auto-increment column of the dependent parent table is added with a fixed value to avoid constraint conflicts when merging, then the associated column of the dependent child table also needs to be added with the same value when merging. fixed value.

(8) Some databases or tables have sequential dependencies in the order of merging, and a priority level needs to be assigned to them. By default, in order to improve the speed of merging, for tables of the same priority level, the merging device should perform multi-table merging processing in a concurrent manner, and there is no order of who comes first among these tables; if some tables specify a higher If the priority is higher, it should be merged first. After all the tables under this priority are merged, the process of merging the tables with lower priority can be carried out.

(9) Some tables use triggers, and it is necessary to decide whether to disable triggers according to different business logics. For example, in some application software, the effect of adding a sequence to a trigger is used to simulate the effect of an auto-increment column (there is no concept of an auto-increment column in Oracle), and each time a record is inserted into the corresponding table, the trigger will be automatically invoked. Logic, modify its ID to a sequence of incremental integers; when merging the table, if the table is not dependent on other tables, you can safely use the trigger function in the destination database to automatically generate an incremental ID for the merged record; if the table If it is dependent on other tables, it is recommended to temporarily disable the triggers in the destination database. Instead, use the method described in the (6) scenario to merge records, and then restore the trigger status in the destination database after the merger is complete. The benefits of doing so When merging dependent subtables, you only need to simply add the same fixed value to the associated columns of the 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 default process of converting the table structure of the source database by 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 types of merging, the user needs to specify the merging strategy definition corresponding to the specific table in the "Merging Strategy". If no merging strategy is specified for the table, all the records of the table in the source database will be appended to the destination database by default. inside the table.

In addition, in most cases, the various application scenarios above can be realized by executing SQL statements. For example, in the first scenario, the detection process before merging usually only needs to execute some cross-database connection queries to determine; 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 simply by using SQL statements, and the user needs to implement a special "merge plug-in" to complete this function, which will increase the user's use of merge The difficulty of installation; therefore, in order to simplify the merging process and make the user only need to understand sql knowledge to complete most of the merging functions, it is necessary to integrate the source database and the target database in the same database management system. Therefore, this merging device is in The first steps at 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 methods), and then uses the temporary mirror database as the source database . This enables users to simultaneously access the source and destination databases in SQL statements.

Aiming at the above-mentioned scenarios that need to specify the special merging process of the "merging strategy", this merging device summarizes the corresponding merging strategy, and builds its functions into the "merging processor" for users to use various configuration files Set the "merge strategy" of some special tables for the carrier. There are mainly the following merging strategies:

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

①The name of the source database;

②The name of the target database;

③Priority level, optional, if no priority level is specified, it will be the lowest level by default; because the merging of databases will eventually be converted into the merging of sub-elements of the database, the set value of the priority of the database will be inherited by its sub-elements, Sub-elements with high priority are merged first, sub-elements with low priority are merged later, and sub-elements with the same priority can be merged concurrently.

An example of xml configuration is given below:

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

<databasesource="db1"destination="db1">

</database>

<! --level specifies the priority level, the larger the value, the higher the level, if not specified, the default is 0-->

<databasesource="db2"destination="db2"level="1">

</database>

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

①Strategy name, optional; some "merge plug-ins" may need to recognize this name, and implement different processing procedures according to the name;

②Prompt message for failed detection, used for "merge processor" to record logs or display to users;

③sql statement, sub-node, optional, multiple sub-nodes can be configured, and it is agreed that the detection fails if a certain value is returned;

Note: It is important to explain about sql. In most cases, the sql configured by the user needs to access the source database and the destination database at the same time. However, since this merging device replaces the source database with a temporary mirror database, the real name of the source database should be Temporary mirror database name, in order to avoid duplicate name conflicts between the temporary database name and the database name in the destination database (two databases with the same name cannot be created in one database management system), the merging device can use a certain algorithm to generate a certain unique string to As the name of the temporary mirror database, it is unpredictable for the user, so when specifying the name of the source database in the sql statement, do not use the real database name, but replace it with a macro definition, for example, use %SOURCE( db1)% to represent 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.

④Merge plug-in name, optional, used for the "merge processor" to call the user-implemented "merge plug-in" for detection when the sql statement cannot be detected; usually, the name of the merge plug-in is the secondary development implemented by the user class name.

③ and ④ can choose one of them, and they can also exist at the same time. If any one returns a failure, it means that the detection fails.

This strategy addresses the scenario (1) described above in this article that requires special handling. An example of xml configuration is given below:

(3) The non-merging strategy of the table is a child node of the database strategy, including the following content:

① Table name, specify the specific table corresponding to the strategy;

②Strategy type, which is the "non-merge" type.

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

(4) The modification content of the table The merge strategy is a child node of the database strategy, which is used to modify the data to be merged during the merge process, including the following content:

① Table name, specify the specific table corresponding to the strategy;

②Strategy type, which is "Modify" type;

③Priority level, optional. If no priority level is specified, the default is to inherit the priority level of the parent node database. After the table with high priority level is merged, the table with low priority level can be merged. For tables with foreign key dependencies, the "merge processor" can automatically identify them from the system tables of the database management system, and then automatically set the priority, which does not require the user to set the priority.

④sql statement, sub-node, optional, multiple sub-nodes can be configured, used to modify the table structure or data content in the source database or the destination database (not only limited to modifying the currently processed table, but also other associated dependent tables);

⑤Merge plug-in name, sub-node, optional, multiple sub-nodes can be configured, used for merging the main program to call the secondary development merging plug-in implemented by the user in the case of complex business logic that cannot be processed by the sql statement before merging;

⑥Whether the "merge processor" completes the conversion process of inconsistent table structures in the source database and the destination database, child nodes, optional. The default is "Yes", when the structure of the table in the source database and the destination database are inconsistent, the "merge processor" will convert the structure of the table in the source database to make it the same as the structure of the table in the destination database; if it is "No" If , it means that the conversion is completed by the sql statement configured by the user or the "merge plug-in", and the "merge processor" does not participate in the conversion process.

⑦Whether the merge process of the modified data is completed by the "merge processor", sub-node, optional, the default is "Yes", which means that the "merge processor" executes a series of user-configured sql or "merge plug-in" to modify After the data is to be merged, the "merge processor" will simply append the contents of the table in the source database to the table with the same name in the destination database; if the configuration is "No", it means that the merge process of the table has been configured in the user-configured sql or "merge "plug-in", no longer need "merge processor" to participate, for some complex business logic, can not be merged by simple appending, then you can block the process of "merge processor" in this way at this time, completely The merge process of some tables is led by the user;

⑧Auto-increment column and its corresponding sequence name, specify which column in the current table is an auto-increment column, child node, optional. The auto-increment column in the physical definition can be identified by the "merge processor" from the system table of the database management system, so even if the user does not specify the specific column name, it can be automatically processed; in some scenarios, although a column is not defined As self-increment column, but it uses sequence or other business logic algorithm to achieve similar self-increment function, "merge processor" cannot recognize this situation, so the corresponding column name must be specified by the user; if sequence is used, also need Specify the sequence name, and the "merge processor" can use some algorithms to automatically modify the value of the column based on these two pieces of information, so as to avoid violating the uniqueness constraint of the auto-increment column and synchronize the current value of the sequence;

⑨The name and column name of the self-incrementing column-associated foreign key sub-table, which is a sub-node of ⑦, optional, and multiple sub-nodes can be configured. If the table has an associated child table, and the parent-child relationship is maintained through the uniqueness of the auto-increment column of the parent table, you need to specify the corresponding When adding a column, it can automatically modify the foreign key column of the associated subtable;

⑩The name of the trigger that needs to be disabled, optional, sub-node, multiple sub-nodes can be configured. It is up to the user to decide whether to disable the specified trigger during the process of merging the current table according to the business logic, and restore its original state after the merging is completed.

The device of the present invention can realize the common flow of database content merging. The overall merging process is completed by the merging main program; for tables that do not require special processing, the merging main program will append the table records in the source database to the table with the same name in the destination database by default; for complex business logic that cannot be recognized by the merging main program For the data table, the developer specifies its merging strategy in the strategy configuration file, and the merging main program merges the data according to its merging strategy; in most application scenarios, developers only need to configure some sql statements in the strategy configuration file The purpose of modifying table data that cannot be directly merged can be achieved. In extremely complex application scenarios, developers can integrate the secondary development plug-in implemented by their own coding into the merge process; in addition, the merge device adopts a concurrent thread processing mechanism , to improve the processing speed of merging.

The above is only a preferred embodiment of the present invention, it should be pointed out that, for those of ordinary skill in the art, without departing from the principle of the present invention, some improvements and modifications can also be made, and these improvements and modifications can also be made. It should be regarded as the protection scope of the present invention.

Claims (13)

1. a data-base content merging method, is characterized in that, comprising:

Set up the mirror database that source database is corresponding, identical with described source database in described mirror database;

The consolidation strategy corresponding according to the source form in described mirror database, to the source form of described mirror database, the object form with needing in destination data storehouse to carry out with described source form merging, carries out form data merging.

2. the method for claim 1, is characterized in that, the described step setting up mirror database corresponding to source database comprises:

Backup source database, obtains the backup file carrying form data;

Choose the data base management system (DBMS) belonging to destination data storehouse;

In described data base management system (DBMS), set up mirror database corresponding to described source database, and the form data of carrying in described backup file is added in described mirror database.

3. the method for claim 1, it is characterized in that, the described consolidation strategy corresponding according to the source form in described mirror database, to the source form of described mirror database, with the object form needing in destination data storehouse to carry out with described source form merging, the step of carrying out form data merging comprises:

The consolidation strategy that source form in described mirror database in acquisition configuration file is corresponding;

When there being inspection policies in consolidation strategy, according to described inspection policies, choose sql statement and/or merge the form that plug-in unit detects in mirror database and destination data storehouse and can carry out form data merging, if detect successfully, then according to the form consolidation strategy in described consolidation strategy, to the source form of described mirror database, the object form with needing in destination data storehouse to carry out with described source form merging, carries out form data merging.

4. method as claimed in claim 3, it is characterized in that, according to the form consolidation strategy in described consolidation strategy, to the source form of described mirror database, with the object form needing in destination data storehouse to carry out with described source form merging, the step of carrying out form data merging comprises:

To obtain in mirror database the set of active form;

Set up in the set of source form the form needing to carry out with the object form in destination data storehouse merging, with the corresponding relation of form consolidation strategy;

According to priority order carries out form data merging to the object form in source form and destination data storehouse.

5. method as claimed in claim 4, it is characterized in that, the step of the object form in source form and destination data storehouse being carried out to form data merging comprises:

The form consolidation strategy that source form in the set of acquisition source form is corresponding;

If described form consolidation strategy is for specifying consolidation strategy and specifying consolidation strategy type for merging or being amendment strategy, then judge in destination data storehouse, whether to there is the object form of the same name with source form, if exist, then to source form and and source form object form of the same name carry out form data merging, otherwise, in destination data storehouse, create the object form of the same name with source form, then form data merging is carried out to source form and object form.

6. method according to claim 5, is characterized in that, the step of source form and object form being carried out to form data merging comprises:

Judge that whether source form is consistent with the list structure of object form, if, according to the appointment consolidation strategy of described source form, form data merging is carried out to source form and object form, otherwise, the list structure of source form is converted to consistent with the list structure of object form after, then according to the appointment consolidation strategy of described source form, form data merging is carried out to source form and object form.

7. method according to claim 6, is characterized in that, according to the appointment consolidation strategy of described source form, the step of source form and object form being carried out to form data merging comprises:

If described source form does not specify consolidation strategy, obtain the record sum of source form;

If the record sum of described source form is not 0, performs and insert statement, all records in the form of source are inserted in object form.

8. method according to claim 6, is characterized in that, according to the appointment consolidation strategy of described source form, the step of source form and object form being carried out to form data merging comprises:

If described source form has appointment consolidation strategy, then by appointment consolidation strategy, source form is modified, obtain the record of amended source form;

The record of this amended source form is inserted in object form.

9. method according to claim 8, is characterized in that, modifies to source form, and the step obtaining the record of amended source form comprises:

The record of the record or major key conflict of violating constraint rule in the form of described source is modified or deleted, obtains the record of amended source form; Or

To in the form of described source certainly add or sequence adds a fixed value, obtain the record of amended source form; Or

There is external key to described source form to rely on, when modifying to dependence parent table, dependence sublist also being carried out to the amendment of related content, obtaining the record of amended source form.

10. method according to claim 8, is characterized in that, the step that the record of this amended source form is inserted in object form is comprised:

If object form employs trigger and object form is not relied on by other table, then, when inserting the record of source form in object form, what utilize trigger automatically to generate record increases progressively ID; Otherwise when inserting the record of source form in object form, forbid described trigger, after at record, insertion completes, then to recover trigger be state before merging.

11. 1 kinds of data-base contents merge device, it is characterized in that, comprising:

Set up module, for setting up mirror database corresponding to source database, identical with described source database in described mirror database;

Merge module, for the consolidation strategy corresponding according to the source form in described mirror database, to the source form of described mirror database, the object form with needing in destination data storehouse to carry out with described source form merging, carries out form data merging.

12. devices as claimed in claim 11, is characterized in that, set up module and comprise:

Backup submodule, for backup source database, obtains the backup file carrying form data;

Connexon module, for choosing the data base management system (DBMS) belonging to destination data storehouse;

Setting up submodule, for setting up mirror database corresponding to described source database in described data base management system (DBMS), and the form data of carrying in described backup file being added in described mirror database.

13. devices as claimed in claim 11, is characterized in that, merge module and comprise:

Obtain submodule, for obtaining consolidation strategy corresponding to source form in the described mirror database in configuration file;

Merge submodule, for when there being inspection policies in consolidation strategy, according to described inspection policies, choose sql statement and/or merge the form that plug-in unit detects in mirror database and destination data storehouse and can carry out form data merging, if detect successfully, then according to the form consolidation strategy in described consolidation strategy, to the source form of described mirror database, object form with needing in destination data storehouse to carry out with described source form merging, carries out form data merging.