Data change tracking and event notification

• US 8,972,459 B2
• Filed: 08/09/2011
• Issued: 03/03/2015
• Est. Priority Date: 06/30/2011
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chaliparambil, Kishore R., Chan, Chi Kit, Mohiuddin, Imran, Dove, Bryan, Shah, Mehul Y., Madan, Umesh, Emami, Ali, May, Robert A., Gunnerson, Eric
• Primary Examiner(s): Arjomandi, Noosha
• Application Number:
  US13/206,316
• Publication Number:
  US 20130007069A1
• Time in Patent Office: 1,302 Days
• Field of Search: 707/803, 707/625
• US Class Current: 707/803
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06Q 10/10 Office automation; Time management
• CPC Class Codes: G16H 10/60 for patient-specific data, e.g. for electronic patient records

Abstract

The described implementations relate to updating views in a database system. One implementation can identify an active view that retrieves records from a database and determine one or more physical tables in the database that are referenced by the active view. The implementation can track one or more columns in the physical tables that are used by the active view and identify a change to the physical tables in a transaction log of the database. If the change does not affect the one or more columns, the change can be treated as an ignorable change for the active view.

Claims

1. A method performed by at least one computing device, the method comprising:

creating a materialized view table based on one or more physical tables of a database storing a plurality of records;

obtaining first temporal metadata associated with a first network message that was received over a network at a first time;

obtaining second temporal metadata associated with a second network message that was received over the network at a second time that is later than the first time;

identifying an interval during which a subset of the records of the database were altered, the interval being identified by the first temporal metadata and the second temporal metadata; and

performing an update on the materialized view table while limiting the update to the subset of the records that were altered during the interval identified by the first temporal metadata and the second temporal metadata,

wherein limiting the update to the subset of the records comprises using the first temporal metadata and the second temporal metadata to obtain a constrained set of primary keys that are used to extract the subset of the records.

2. The method according to claim 1, wherein performing the update on the materialized view table comprises deleting one or more rows from the materialized view table, the one or more deleted rows corresponding to one or more individual records of the subset.

3. The method according to claim 2, wherein performing the update on the materialized view table comprises reevaluating the one or more rows that are deleted from the materialized view table against the one or more physical tables of the database.

4. The method according to claim 1, wherein performing the update on the materialized view table comprises overwriting one or more rows of the materialized view table without deleting the one or more rows, the one or more overwritten rows corresponding to one or more individual records of the subset.

5. The method according to claim 1, further comprising:

extracting the constrained set of primary keys from a queryable transaction log of ongoing changes to the database.

6. The method according to claim 5, wherein the queryable transaction log comprises a redo log.

7. The method according to claim 5, wherein the queryable transaction log comprises a change data capture (CDC) table.

8. One or more hardware computer-readable storage media comprising computer-readable instructions which, when executed by one or more computing devices, cause the one or more computing devices to perform acts comprising:

maintaining a materialized view table using tables of a database, the database storing multiple records having associated primary keys;

identifying individual records that have been altered in the tables by messages of a message pipeline during an interval of time, wherein the identifying comprises determining the interval of time using temporal metadata reflecting an order in which at least two of the messages of the message pipeline were communicated over a network;

using the temporal metadata reflecting the order in which the at least two of the messages were communicated to constrain which of the primary keys are used for an update of the materialized view table to a constrained set of individual primary keys, wherein the constrained set of individual primary keys correspond to the individual records that have been altered during the interval of time; and

performing the update on the materialized view table using the constrained set of individual primary keys.

9. The one or more hardware computer-readable storage media according to claim 8, wherein the individual records that have been altered comprise medical records.

10. The one or more hardware computer-readable storage media according to claim 8, wherein the temporal metadata comprises message identifiers for the messages in the message pipeline.

11. The one or more hardware computer-readable storage media according to claim 10, wherein the message identifiers are sequential.

12. The one or more hardware computer-readable storage media according to claim 10, wherein the message identifiers are sequentially increasing such that individual messages with relatively higher message identifiers are received by the database over the network after other individual messages with relatively lower message identifiers.

13. The one or more hardware computer-readable storage media according to claim 8, the acts further comprising:

determining that changes to the multiple records include an ignorable change for the materialized view table; and

not performing the identifying for the ignorable change.

14. The one or more hardware computer-readable storage media according to claim 13, wherein the ignorable change is an individual change to the tables that does not affect any columns that are returned by the materialized view table.

15. The one or more hardware computer-readable storage media according to claim 13, wherein the ignorable change is an individual change to the tables that does not affect any columns that are used as conditions by the materialized view table.

16. A system comprising:

one or more hardware processors; and

one or more computer-readable memory devices or storage devices storing instructions which, when executed by the one or more processors, cause the one or more processors to;

access a materialized view table, wherein the materialized view table is created based on one or more physical tables of a database storing multiple records having associated database keys;

use temporal metadata associated with network messages of a message pipeline to select individual records that have been altered in the database during a particular interval of time, wherein the individual records are identified in the one or more physical tables by an associated constrained set of

database keys that excludes at least some other database keys for other records that were not altered in the database during the particular interval of time;

rewrite a view query for the materialized view table using a filter condition that extracts the individual records having the associated constrained set of database keys; and

execute the rewritten view query to update the materialized view table such that the materialized view table reflects the individual records that were altered in the database during the particular interval of time.

17. The system according to claim 16, wherein individual database keys are primary keys.

18. The system according to claim 16, wherein the instructions cause the one or more hardware processors to maintain the materialized view table periodically based on a set refresh interval.

19. The system according to claim 16, wherein the materialized view table reflects at least part of a personal health care record for a patient.

20. The system according to claim 16, embodied as a single computing device.