Database partial replica generation system

• US 5,758,337 A
• Filed: 08/08/1996
• Issued: 05/26/1998
• Est. Priority Date: 08/08/1996
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Hammond, Bradley Mark
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): HO, RUAY L
• Application Number:
  US08/689,352
• Time in Patent Office: 656 Days
• Field of Search: 395/182.02, 364/724.13, 364/724.05, 364/724.11, 364/184, 364/572, 364/724.16, 364/724.17, 364/525, 371/32, 382/261, 382/270, 707/1, 707/3, 707/4, 707/5, 707/6, 1/1
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99936 Pattern matching access

Abstract

A system for creating database partial replicas is provided where the referential integrity of the original database is maintained in the partial replica. Filters are specified to allow a user to select, using boolean logic, which rows of the original database are to be propagated to the partial replica. A row containing the primary key corresponding to a foreign key of a row propagated to the partial replica is also propagated to the partial replica. Also, a filter may be specified to include rows that are part of a relationship between multiple record sources. The result is a partial replica database containing only those records desired by the user but also all the records necessary to maintain the referential integrity of the original database.

Claims

1. A method of populating a partial replica database from an original database having multiple original record sources each of said original record sources having records wherein certain predetermined relationships apply to said original record sources said predetermined relationships linking a foreign key of a first of said original record sources to a primary key of a second of said original record sources, comprising the steps of:

retrieving a filter associated with said replica database from a memory wherein said filter specifies certain conditions to be applied to said original database for the selection of records for the population of said replica database;

selecting records from said original database wherein said selected records satisfy said conditions specified by said filter;

populating said replica database with said selected records to create a populated replica database, wherein said predetermined relationships of said original database are enforced in said populated replica database.

2. A method according to claim 1 wherein said selecting step includes:

selecting a first record from said first original record source wherein said first record contains a value in a field of said first record satisfying said filter wherein said filter is a boolean filter and said first record contains said foreign key; and

selecting a second record from said second original record source wherein said second record contains said primary key, said primary key is linked by one of said predetermined relationships to said foreign key of said first record.

3. A method according to claim 2 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

4. A method according to claim 2 wherein said step of selecting said second record includes:

determining whether said primary key of said first record is specified by a relationship filter, wherein said relationship filter identifies a predetermined relationship between said primary key of said first original record source and said foreign key of said second original record source; and

selecting said second record from said second original record source wherein said second record contains a foreign key corresponding to said primary key of said first record.

5. A method according to claim 4 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

6. A method according to claim 1 wherein said selecting step includes:

selecting a first record from said first original record source wherein said first record is within said first original record source, said first original record source is specified by said filter and said filter is an all-rows filter;

selecting a second record from said second original record source wherein said second record contains said primary key, said primary key is linked by one of said predetermined relationships to said foreign key of said first record.

7. A method according to claim 6 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

8. A method according to claim 6 wherein said step of selecting said second record includes:

determining whether said primary key of said first record is specified by a relationship filter, wherein said relationship filter identifies a predetermined relationship between said primary key of said first original record source and said foreign key of said second original record source; and

selecting said second record from said second original record source wherein said second record contains a foreign key corresponding to said primary key of said first record.

9. A method according to claim 8 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

10. A method according to claim 1 wherein said retrieving step includes:

retrieving said filter from said memory wherein said filter specifies records in said original database that have been changed since a most recent synchronization between said partial database and said original database.

11. A method of populating a replica database with a record selected from an original database having an original record source containing more than one record, said method comprising the steps of:

defining a filter having a condition in terms of a field within said original record source;

storing said filter in a memory;

receiving a replication signal indicating the need to populate said replica database;

retrieving said filter from said memory in response to said replication signal;

selecting said record from said more than one records wherein said selected record contains a value in said field satisfying said condition specified by said filter; and

copying said selected record from said original record source to a replica record source wherein said replica record source corresponds to said original record source and is part of said replica database.

12. A method according to claim 11 wherein said filter is selected from the group consisting of:

boolean filters, and all-rows filters.

13. A method according to claim 12 wherein said selecting step includes:

selecting a first record from a first original record source wherein said first record contains a value in said field satisfying said condition specified by said filter; and

selecting a second record from a second original record source wherein said second record is related to said first record.

14. A method according to claim 13 wherein said copying step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

15. A method according to claim 13 wherein said step of selecting said second record includes;

selecting said second record wherein said first record contains a foreign key corresponding to a primary key of said second record.

16. A method according to claim 13 wherein said step of selecting said second record includes:

determining whether a primary key of said first record is specified by a relationship filter, wherein said relationship filter identifies a predetermined relationship between said primary key of said first original record source and a foreign key of said second original record source; and

selecting a second record from said second original record source wherein said second record contains a foreign key corresponding to said primary key of said first record.

17. A computer-readable medium having computer-executable instructions for populating a partial replica database from an original database having multiple original record sources each of said original record sources having records wherein certain predetermined relationships apply to said original record sources said predetermined relationships linking a foreign key of a first of said original record sources to a primary key of a second of said original record sources, said method steps comprising:

retrieving a filter associated with said replica database from a memory wherein said filter specifies certain conditions to be applied to said original database for the selection of records for the population of said replica database;

selecting records from said original database wherein said selected records satisfy said conditions specified by said filter; and

populating said replica database with said selected records to create a populated replica database, wherein said predetermined relationships of said original database are enforced in said populated replica database.

18. The computer-readable medium of claim 17 wherein said selecting step includes:

selecting a first record from said first original record source wherein said first record contains a value in a field of said first record satisfying said filter wherein said filter is a boolean filter and said first record contains a foreign key; and

selecting a second record from said second original record source wherein said second record contains said primary key, said primary key is linked by one of said predetermined relationships to said foreign key of said first record.

19. The computer-readable medium of claim 18 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

20. The computer-readable medium of claim 18 wherein said step of selecting said second record includes:

determining whether said primary key of said first record is specified by a relationship filter, wherein said relationship filter identifies a predetermined relationship between said primary key of said first original record source and said foreign key of said second original record source; and

selecting said second record from said second original record source wherein said second record contains a foreign key corresponding to said primary key of said first record.

21. The computer-readable medium of claim 20 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

22. The computer-readable medium of claim 17 wherein said selecting step includes:

selecting a first record from said first original record source wherein said first record is within said first original record source, said first original record source is specified by said filter and said filter is an all-rows filter;

selecting a second record from said second original record source wherein said second record contains said primary key, said primary key is linked by one of said predetermined relationships to said foreign key of said first record.

23. The computer-readable medium of claim 22 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

24. The computer-readable medium of claim 20 wherein said step of selecting said second record includes:

determining whether said primary key of said first record is specified by a relationship filter, wherein said relationship filter identifies a predetermined relationship between said primary key of said first original record source and said foreign key of said second original record source; and

selecting said second record from said second original record source wherein said second record contains a foreign key corresponding to said primary key of said first record.

25. The computer-readable medium of claim 24 wherein said populating step includes:

copying said first record from said first original record source to a first replica record source wherein said first replica record source corresponds to said first original record source and is part of said replica database; and

copying said second record from said second original record source to a second replica record source wherein said second replica record source corresponds to said second original record source and is part of said replica database.

26. The computer-readable medium of claim 17 wherein said retrieving step includes:

retrieving said filter from said memory wherein said filter specifies records in said original database that have been changed since a most recent synchronization between said partial database and said original database.