Data Change Tracking and Event Notification

• US 20130007069A1
• Filed: 08/09/2011
• Published: 01/03/2013
• 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
• Granted Patent: US 8,972,459 B2
• Time in Patent Office: Days
• 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-20. -20. (canceled)

21. A method comprising:

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

identifying, using a message pipeline of messages that manage changes to the database, one or more records that have been altered in the one or more physical tables by the messages during an interval of time; and

updating the materialized view table to reflect the one or more records that were altered by the messages during the interval of time.

23. The method according to claim 21, wherein updating the materialized view table comprises deleting one or more rows from the materialized view table.

24. The method according to claim 23, wherein updating 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.

25. The method according to claim 21, wherein updating the materialized view table comprises overwriting one or more rows of the materialized view table without deleting the one or more rows.

26. The method according to claim 21, further comprising extracting primary keys from a queryable transaction log and using the extracted primary keys to update the materialized view table.

27. The method according to claim 26, wherein the messages of the message pipeline constrain which primary keys are extracted from the queryable transaction log.

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

22. (canceled)

29. One or more 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:

creating a materialized view table using tables of a database;

identifying records that have been altered in the tables by messages of a message pipeline during an interval of time; and

updating the materialized view table to reflect the records that were altered by the messages during the interval of time.

30. The one or more computer-readable storage media according to claim 29, wherein the altered records are identified using temporal metadata associated with the messages.

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

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

33. The one or more computer-readable storage media according to claim 31, wherein the message identifiers are sequentially increasing.

34. The one or more computer-readable storage media according to claim 29, comprising further computer-readable instructions which, when executed by one or more computing devices, cause the one or more computing devices to perform:

performing the identifying only for changes to the tables that are not ignorable changes for the materialized view table.

35. The one or more computer-readable storage media according to claim 34, wherein the ignorable changes are changes to the tables that do not affect columns that are returned by the materialized view table.

36. The one or more computer-readable storage media according to claim 34, wherein the ignorable changes are changes to the tables that do not affect columns that are used as conditions by the materialized view table.

37. A system comprising:

a materialization module configured to;

create a materialized view table based on one or more physical tables of a database;

maintain the materialized view table, wherein, to maintain the materialized view table, the materialization module is further configured to;

identify, for a particular interval of time, records that are altered in the one or more physical tables of the database during the particular interval of time, the altered records being identified using temporal metadata associated with messages of a message pipeline that manage changes to the database; and

update the materialized view table to reflect the one or more records that were altered by the messages during the particular interval of time; and

respond to a view query from an application using the materialized view table instead of the one or more tables of the physical database; and

one or more processors configured to execute the materialization module.

38. The system according to claim 37, wherein the materialization module is configured to maintain the materialized view table responsive to detecting events that cause the materialized view table to be out of date.

39. The system according to claim 37, wherein the materialization module is configured to maintain the materialized view table periodically based on a set refresh interval.

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

41. The system according to claim 40, wherein the application lists, based on the view query:

whether the patient is presently admitted for treatment,

symptoms of the patient, or

treatments administered to the patient.