System and method for the merging of databases

• US 8,543,540 B1
• Filed: 05/09/2012
• Issued: 09/24/2013
• Est. Priority Date: 05/09/2012
• Status: Active Grant

• Current Assignee: Bertec Corporation
• Original Assignee: Bertec Corporation
• Inventors: Berme, Necip, Wilson, Todd Christopher
• Primary Examiner(s): Ehichioya, Fred I
• Assistant Examiner(s): BOWEN, RICHARD L
• Application Number:
  US13/467,472
• Time in Patent Office: 503 Days
• Field of Search: 707610-616
• US Class Current: 707/626
• CPC Class Codes: G06F 16/214 Database migration support

Abstract

A system and method for merging a first database with a second database (one-way merge), and a system and method for merging two databases with one another (two-way merge) is described herein. During the one-way merge, new or updated records are transferred from a copy of a client database to a server database after it is determined that the server database either does not contain one or more records in the copy of the client database or contains an older version of one or more records in the copy of the client database. During the two-way merge, new or updated records are transferred between a client database and a server database after it is determined that either the client database or the server database does not contain one or more records in the other database or contains an older version of one or more records in the other database.

Claims

1. A method for merging two databases, the method comprising the steps of:

providing first and second computing devices having respective first and second databases stored thereon, the first and second databases each comprising one or more records containing data, each of the one or more records having a unique identifier and a timestamp associated therewith;

operating the second computing device in a one-way merge mode, wherein the first database is merged with the second database by utilizing removable electronic media or a shared device to make a copy of the first database available to the second computing device;

performing the following steps when operating the second computing device in the one-way merge mode;

making the copy of the first database available to the second computing device using the removable electronic media or the shared device so that the second computing device is capable of reading the first database;

comparing, by using the second computing device, a first listing of one or more unique identifiers and timestamps associated with the one or more records in the first database to a second listing of one or more unique identifiers and timestamps associated with the one or more records in the second database without checking the data in each of the one or more records for conflicts and without using a transaction or activity log;

determining, by using the second computing device, whether each of the one or more unique identifiers in the first listing exists in the second listing;

when it is determined that one or more unique identifiers in the first listing do not exist in the second listing, copying one or more records associated with the one or more unique identifiers that do not exist from the first database to the second database;

when it is determined that one or more unique identifiers in the first listing also exist in the second listing, further determining, by using the second computing device, a most recent version of one or more records associated with the one or more unique identifiers existing in both listings by comparing one or more timestamps in the first listing to one or more timestamps of one or more corresponding records in the second listing, wherein the one or more timestamps of each of the one or more records having common unique identifiers are compared only once during the one-way merge mode from the beginning of the one-way merge mode to the end of the one-way merge mode; and

when it is determined that one or more records in the first listing have a more recent timestamp than one or more corresponding records in the second listing with matching unique identifiers, copying the one or more records having more recent timestamps from the first database to the second database without first checking the data in each of the one or more records for conflicts;

determining whether each of the one or more records in the first listing is a last remaining record in the first listing; and

operating the second computing device in a two-way merge mode, wherein the first and second databases are merged with one another by utilizing a network connection to operatively connect the first and second computing devices to each other;

performing the following steps when operating the second computing device in the two-way merge mode;

connecting the first computing device with the second computing device by utilizing the network connection;

sending a first listing of one or more unique identifiers and timestamps associated with the one or more records in the first database from the first computing device to the second computing device;

comparing, by using the second computing device, the first listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database to a second listing of one or more unique identifiers and timestamps associated with the one or more records in the second database without checking the data in each of the one or more records for conflicts and without using a transaction or activity log;

determining, by using the second computing device, whether each of the one or more unique identifiers in the first listing exists in the second listing, and whether each of the one or more unique identifiers in the second listing exists in the first listing;

when it is determined that one or more unique identifiers in the first listing do not exist in the second listing, adding the one or more unique identifiers that do not exist to a record request list;

when it is determined that one or more unique identifiers in the first listing also exist in the second listing, further determining, by using the second computing device, a most recent version of one or more records associated with the one or more unique identifiers existing in both listings by comparing one or more timestamps in the first listing to one or more timestamps of one or more corresponding records in the second listing, wherein the one or more timestamps of each of the one or more records having common unique identifiers are compared only once during the two-way merge mode from the beginning of the two-way merge mode to the end of the two-way merge mode;

when it is determined that one or more records in the first listing have a more recent timestamp than one or more corresponding records in the second listing with matching unique identifiers, adding one or more unique identifiers corresponding to the one or more records having more recent timestamps to the record request list without first checking the data in each of the one or more records for conflicts;

when it is determined that one or more records in the second listing have a more recent timestamp than one or more corresponding records in the first listing with matching unique identifiers, adding one or more unique identifiers corresponding to the one or more records having more recent timestamps to a record send list;

when it is determined that one or more unique identifiers in the second listing do not exist in the first listing, adding the one or more unique identifiers that do not exist to the record send list;

determining whether each of the one or more records in the first listing is a last remaining record in the first listing, and whether each of the one or more records in the second listing is a last remaining record in the second listing;

transferring the record request list from the second computing device to the first computing device;

exporting the one or more records listed on the record request list from the first computing device to the second computing device; and

exporting the one or more records listed on the record send list from the second computing device to the first computing device.

2. The method according to claim 1, further comprising the step of:

utilizing the first computing device for making a backup copy of the first database, the first computing device storing the backup copy of the first database on the removable electronic media or the shared device.

3. The method according to claim 1, wherein the one or more unique identifiers comprise one or more globally unique identifiers.

4. The method according to claim 3, wherein the one or more globally unique identifiers are expressed in a numerical or alpha-numerical notation for encoding binary data.

5. The method according to claim 3, wherein the one or more globally unique identifiers comprise one of the following:

(i) one or more mathematically random numbers, (ii) one or more mathematically pseudo-random numbers, and (iii) one or more heuristic values based upon content in fields of the one or more records.

6. The method according to claim 1, wherein the one or more timestamps comprise both the dates and times when the one or more records were last modified.

7. The method according to claim 6, wherein the one or more timestamps are expressed in the form of Julian dates.

8. The method according to claim 1, wherein the first computing device is a client computing device and the second computing device is a server computing device.

9. A system for merging two databases, the system comprising:

first and second computing devices, each of the first and second computing devices including a microprocessor and memory, the first and second computing devices having respective first and second databases stored thereon, the first and second databases each comprising one or more records containing data, each of the one or more records having a unique identifier and timestamp associated therewith;

removable electronic media or a shared device configured to make a copy of the first database available to the second computing device so that the second computing device is capable of reading the first database;

the second computing device being specially programmed to operate in both of the following two modes;

(i) a one-way merge mode, wherein the first database is merged with the second database by utilizing the removable electronic media or the shared device to make the copy of the first database available to the second computing device; and

(ii) a two-way merge mode, wherein the first and second databases are merged with one another by utilizing a network connection to operatively connect the first and second computing devices to each other;

the second computing device being specially programmed to carry out the following operations in the one-way merge mode;

compare a first listing of one or more unique identifiers and timestamps associated with the one or more records in the first database residing on the removable electronic media or the shared device to a second listing of one or more unique identifiers and timestamps associated with the one or more records in the second database without checking the data in each of the one or more records for conflicts and without using a transaction or activity log;

determine whether each of the one or more unique identifiers in the first listing exists in the second listing;

when it is determined that one or more unique identifiers in the first listing do not exist in the second listing, copy one or more records associated with the one or more unique identifiers that do not exist from the first database to the second database;

when it is determined that one or more unique identifiers in the first listing also exist in the second listing, further determine a most recent version of one or more records associated with the one or more unique identifiers existing in both listings by comparing one or more timestamps in the first listing to one or more timestamps of one or more corresponding records in the second listing, wherein the one or more timestamps of each of the one or more records having common unique identifiers are compared only once during the one-way merge mode from the beginning of the one-way merge mode to the end of the one-way merge mode; and

when it is determined that one or more records in the first listing have a more recent timestamp than one or more corresponding records in the second listing with matching unique identifiers, copy the one or more records having more recent timestamps from the first database to the second database without first checking the data in each of the one or more records for conflicts;

determining whether each of the one or more records in the first listing is a last remaining record in the first listing; and

the second computing device being specially programmed to carry out the following operations in the two-way merge mode;

compare a first listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database to a second listing of one or more unique identifiers and timestamps associated with the one or more records in the second database without checking the data in each of the one or more records for conflicts and without using a transaction or activity log, the first listing being sent from the first computing device to the second computing device via the network connection;

determine whether each of the one or more unique identifiers in the first listing exists in the second listing, and whether each of the one or more unique identifiers in the second listing exists in the first listing;

when it is determined that one or more unique identifiers in the first listing do not exist in the second listing, add the one or more unique identifiers that do not exist to a record request list;

when it is determined that one or more unique identifiers in the first listing also exist in the second listing, further determining a most recent version of one or more records associated with the one or more unique identifiers existing in both listings by comparing one or more timestamps in the first listing to one or more timestamps of one or more corresponding records in the second listing, wherein the one or more timestamps of each of the one or more records having common unique identifiers are compared only once during the two-way merge mode from the beginning of the two-way merge mode to the end of the two-way merge mode;

when it is determined that one or more records in the first listing have a more recent timestamp than one or more corresponding records in the second listing with matching unique identifiers, add one or more unique identifiers corresponding to the one or more records having more recent timestamps to the record request list without first checking the data in each of the one or more records for conflicts;

when it is determined that one or more records in the second listing have a more recent timestamp than one or more corresponding records in the first listing with matching unique identifiers, add one or more unique identifiers corresponding to the one or more records having more recent timestamps to a record send list;

when it is determined that one or more unique identifiers in the second listing do not exist in the first listing, add the one or more unique identifiers that do not exist to the record send list;

wherein, when operating in the two-way merge mode, the second computing device is configured to determine whether each of the one or more records in the first listing is a last remaining record in the first listing, and whether each of the one or more records in the second listing is a last remaining record in the second listing;

wherein, when operating in the two-way merge mode, the second computing device is configured to transfer the record request list to the first computing device; and

wherein, when operating in the two-way merge mode, the first computing device is configured to export the one or more records listed on the record request list to the second computing device, and the second computing device is configured to export the one or more records listed on the record send list to the first computing device.

10. The system according to claim 9, wherein the second computing device is additionally programmed to enable a system user to selectively choose between the one-way merge mode and the two-way merge mode.