System and method for the merging of databases

• US 9,043,278 B1
• Filed: 04/14/2014
• Issued: 05/26/2015
• Est. Priority Date: 05/09/2012
• Status: Active Grant

• Current Assignee: Bertec Corporation
• Original Assignee: Bertec Corporation
• Inventors: Wilson, Todd Christopher, Berme, Necip
• Primary Examiner(s): BOWEN, RICHARD L
• Application Number:
  US14/252,001
• Time in Patent Office: 407 Days
• Field of Search: None
• US Class Current: 707/626
• CPC Class Codes: G06F 16/214 Database migration support
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

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 system for merging two databases with one another, the system comprising:

first, second, and third computing devices having respective first, second, and third databases stored thereon, the first, second, and third databases each comprising one or more records, each of the one or more records having a unique identifier and timestamp associated therewith, the first, second, and third computing devices being operatively connected to one another by a network connection in a peer-to-peer manner without using a central master server, each of the first, second, and third computing devices being peers with respect to one another such that syncing occurs between the first computing device and the second computing device, syncing occurs between the first computing device and the third computing device, and syncing occurs between the second computing device and the third computing device;

the first computing device being specially programmed to;

prior to determining whether it is already syncing with the second computing device, send an initial command to the second computing device;

when the second computing device responds to the initial command sent by the first computing device, receive at least the following;

(i) a unique identifier of the second computing device, (ii) a maximum data block size that the second computing device is capable of receiving as one payload, and (iii) a last time the second computing device communicated with the first computing device;

determine whether it is already syncing with the second computing device;

when it is determined that the first computing device is already syncing with the second computing device, communications between the first computing device and the second computing device are ended;

when it is determined that the first computing device is not already syncing with the second computing device, generate a first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database by using a timestamp filter, the timestamp filter comprising a first last-seen timestamp value which is indicative of the last time the first computing device communicated with the second computing device, the first last-seen timestamp value being specific to communications between the first computing device and the second computing device, wherein records having timestamps older than the first last-seen timestamp value are not included in the first filtered listing, the timestamp filter further comprising a second last-seen timestamp value that is specific to communications between the first computing device and the third computing device;

send the first filtered listing of one or more unique identifiers and timestamps from the first computing device to the second computing device;

the second computing device being specially programmed to;

generate a second filtered listing of one or more unique identifiers and timestamps associated with the one or more records in the second database using the first last-seen timestamp value sent to the second computing device from the first computing device;

compare the first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database to the second filtered listing of one or more unique identifiers and timestamps associated with the one or more records in the second database;

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

when it is determined that one or more unique identifiers in the first filtered listing do not exist in the second filtered listing, add one or more records associated with 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 filtered listing also exist in the second filtered 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 filtered listing to one or more timestamps of one or more corresponding records in the second filtered listing;

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

when it is determined that one or more records in the second filtered listing have a more recent timestamp than one or more corresponding records in the first filtered listing with matching unique identifiers, add 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 filtered listing do not exist in the first filtered listing, add one or more records associated with the one or more unique identifiers that do not exist to the record send list;

transfer the record request list to the first computing device;

copy the one or more records on the record send list into a second export queue of the second computing device; and

the first computing device being further specially programmed to;

copy the one or more records on the record request list into a first export queue of the first computing device.

2. The system according to claim 1, wherein the first computing device is further specially programmed to:

after sending the initial command to the second computing device, determine whether the second computing device responds to the initial command within a predetermined timeframe.

3. The system according to claim 1, wherein at least one of the first and second last-seen timestamp values of the timestamp filter is expressed in the form of a Julian date.

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

5. A method for merging two databases with one another, the method comprising the steps of:

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

connecting each of the first, second, and third computing devices to one another by utilizing a network connection in a peer-to-peer manner without using a central master server, each of the first, second, and third computing devices being peers with respect to one another such that syncing occurs between the first computing device and the second computing device, syncing occurs between the first computing device and the third computing device, and syncing occurs between the second computing device and the third computing device;

prior to determining whether the first computing device is already syncing with the second computing device, sending an initial command to the second computing device using the first computing device;

when the second computing device responds to the initial command sent by the first computing device, receiving at least the following information at the first computing device;

(i) a unique identifier of the second computing device, (ii) a maximum data block size that the second computing device is capable of receiving as one payload, and (iii) a last time the second computing device communicated with the first computing device;

determining, by using the first computing device, whether the first computing device is already syncing with the second computing device;

when it is determined that the first computing device is already syncing with the second computing device, ending communications between the first computing device and the second computing device;

when it is determined that the first computing device is not already syncing with the second computing device, generating, by using the first computing device, a first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database by using a timestamp filter, the timestamp filter comprising a first last-seen timestamp value which is indicative of the last time the first computing device communicated with the second computing device, the first last-seen timestamp value being specific to communications between the first computing device and the second computing device, wherein records having timestamps older than the first last-seen timestamp value are not included in the first filtered listing, the timestamp filter further comprising a second last-seen timestamp value that is specific to communications between the first computing device and the third computing device;

sending the first filtered listing of one or more unique identifiers and timestamps from the first computing device to the second computing device;

comparing, by using the second computing device, the first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database to a second filtered listing of one or more unique identifiers and timestamps associated with the one or more records in the second database, the second filtered listing being generated using the first last-seen timestamp value sent to the second computing device from the first computing device;

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

when it is determined that one or more unique identifiers in the first filtered listing do not exist in the second filtered listing, adding one or more records associated with 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 filtered listing also exist in the second filtered 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 filtered listing to one or more timestamps of one or more corresponding records in the second filtered listing;

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

when it is determined that one or more records in the second filtered listing have a more recent timestamp than one or more corresponding records in the first filtered listing with matching unique identifiers, adding 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 filtered listing do not exist in the first filtered listing, adding one or more records associated with the one or more unique identifiers that do not exist to the record send list;

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

copying, using the second computing device, the one or more records on the record send list into a second export queue of the second computing device; and

copying, using the first computing device, the one or more records on the record request list into a first export queue of the first computing device.

6. The method according to claim 5, further comprising the steps of:

exporting the one or more records in the first export queue from the first computing device to the second computing device; and

exporting the one or more records in the second export queue from the second computing device to the first computing device.

7. The method according to claim 6, further comprising the steps of:

after each of the one or more records in the first export queue is sent from the first computing device to the second computing device, determining, by using the second computing device, whether each of the one or more records sent from the first computing device exists in the second database by comparing one or more unique identifiers of the one or more records sent from the first computing device to one or more unique identifiers of the one or more records in the second database;

when it is determined that one or more records sent from the first computing device do not exist in the second database, insert the one or more records associated with the one or more unique identifiers that do not exist into the second database;

when it is determined that the one or more records sent from the first computing device also exist in the second database, further determine a most recent version of the one or more records also existing in the second database by comparing one or more timestamps of the one or more records sent from the first computing device to one or more timestamps of one or more corresponding records in the second database; and

when it is determined that the one or more records sent from the first computing device have a more recent timestamp than one or more corresponding records in the second database with matching unique identifiers, update the one or more corresponding records in the second database.

8. The method according to claim 6, further comprising the steps of:

after each of the one or more records in the second export queue is sent from the second computing device to the first computing device, determining, by using the first computing device, whether each of the one or more records sent from the second computing device exists in the first database by comparing one or more unique identifiers of the one or more records sent from the second computing device to one or more unique identifiers of the one or more records in the first database;

when it is determined that one or more records sent from the second computing device do not exist in the first database, insert the one or more records associated with the one or more unique identifiers that do not exist into the first database;

when it is determined that the one or more records sent from the second computing device also exist in the first database, further determine a most recent version of the one or more records also existing in the first database by comparing one or more timestamps of the one or more records sent from the second computing device to one or more timestamps of one or more corresponding records in the first database; and

when it is determined that the one or more records sent from the second computing device have a more recent timestamp than one or more corresponding records in the first database with matching unique identifiers, update the one or more corresponding records in the first database.

9. The method according to claim 5, further comprising the step of:

when the first computing device has never previously communicated with the second computing device, setting the first last-seen timestamp value equal to zero by using the first computing device.

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

11. The method according to claim 5, wherein the method for merging two databases with one another is an asynchronous process that is capable of operating on the first and second computing devices while other applications are being simultaneously executed by the first and second computing devices.

12. The method according to claim 5, wherein the method for merging two databases with one another is a resumable process that is capable of being restarted at any point in the process if an interruption in the network connection between the first and second computing devices occurs.

13. A system for merging two databases with one another, the system comprising:

first, second, and third computing devices having respective first, second, and third databases stored thereon, the first, second, and third databases each comprising one or more records, each of the one or more records having a unique identifier and timestamp associated therewith, the first, second, and third computing devices being operatively connected to one another by a network connection in a peer-to-peer manner without using a central master server, each of the first, second, and third computing devices being peers with respect to one another;

the first computing device being specially programmed to;

perform a user datagram protocol process in order to determine a sync group list of networked computing devices that are capable of being synced with the first computing device by broadcasting a data packet that at least includes a sync group name and a database schema to the second and third computing devices;

receive a response from the second and third computing devices during the user datagram protocol process, and add the second and third computing devices to the sync group list so that the first computing device is capable of syncing with the second computing device, and the first computing device is capable of syncing with the third computing device;

determine whether the first computing device is already syncing with the second computing device;

when it is determined that the first computing device is already syncing with the second computing device, communications between the first computing device and the second computing device are ended;

when it is determined that the first computing device is not already syncing with the second computing device, generate a first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database by using a timestamp filter, the timestamp filter comprising a first last-seen timestamp value which is indicative of the last time the first computing device communicated with the second computing device, the first last-seen timestamp value being specific to communications between the first computing device and the second computing device, wherein records having timestamps older than the first last-seen timestamp value are not included in the first filtered listing, the timestamp filter further comprising a second last-seen timestamp value that is specific to communications between the first computing device and the third computing device;

send the first filtered listing of one or more unique identifiers and timestamps from the first computing device to the second computing device;

the second computing device being specially programmed to;

generate a second filtered listing of one or more unique identifiers and timestamps associated with the one or more records in the second database using the first last-seen timestamp value sent to the second computing device from the first computing device;

compare the first filtered listing of the one or more unique identifiers and timestamps associated with the one or more records in the first database to the second filtered listing of one or more unique identifiers and timestamps associated with the one or more records in the second database;

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

when it is determined that one or more unique identifiers in the first filtered listing do not exist in the second filtered listing, add one or more records associated with 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 filtered listing also exist in the second filtered 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 filtered listing to one or more timestamps of one or more corresponding records in the second filtered listing;

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

when it is determined that one or more records in the second filtered listing have a more recent timestamp than one or more corresponding records in the first filtered listing with matching unique identifiers, add 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 filtered listing do not exist in the first filtered listing, add one or more records associated with the one or more unique identifiers that do not exist to the record send list;

transfer the record request list to the first computing device;

copy the one or more records on the record send list into a second export queue of the second computing device; and

the first computing device being further specially programmed to;

copy the one or more records on the record request list into a first export queue of the first computing device.

14. The system according to claim 13, wherein the first computing device is further specially programmed to export the one or more records in the first export queue to the second computing device;

and

wherein the second computing device is further specially programmed to export the one or more records in the second export queue to the first computing device.

15. The system according to claim 13, wherein the first computing device is further specially programmed to:

prior to determining whether it is already syncing with the second computing device, send an initial command to the second computing device; and

when the second computing device responds to the initial command sent by the first computing device, receive at least the following;

(i) a unique identifier of the second computing device, (ii) a maximum data block size that the second computing device is capable of receiving as one payload, and (iii) a last time the second computing device communicated with the first computing device.

16. The system according to claim 15, wherein the first computing device is further specially programmed to:

after sending the initial command to the second computing device, determine whether the second computing device responds to the initial command within a predetermined timeframe.

17. The system according to claim 13, wherein, when the first computing device has never previously communicated with the second computing device, the first computing device is further specially programmed to:

set the first last-seen timestamp value equal to zero.