DATA CHANGE TRACKING AND EVENT NOTIFICATION

• US 20130006963A1
• Filed: 06/30/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,818,944 B2
• Time in Patent Office: Days
• US Class Current: 707/717
• 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 comprising:

identifying an active view that retrieves records from a database;

determining one or more physical tables in the database that are referenced by the active view;

tracking one or more columns in the one or more physical tables that are used by the active view;

identifying a change to the one or more physical tables in a transaction log of the database; and

in a first instance when the change does not affect the one or more columns, treating the change as an ignorable change for the active view.

2. The method according to claim 1, wherein an individual tracked column is returned as output by the active view.

3. The method according to claim 1, wherein an individual tracked column is used in a query condition by the active view.

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

in a second instance when the change does affect the one or more columns, recomputing the active view.

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

in the first instance, identifying another active view that returns at least one of the tracked columns; and

recomputing the another active view.

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

in the first instance, identifying another active view that is conditioned on at least one of the tracked columns; and

recomputing the another active view.

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

in the first instance, identifying another active view that is not affected by the change; and

treating the change as an ignorable change for the another active view.

8. A system comprising:

a rewrite module configured to;

associate an active view with an entity that receives notifications reflecting the active view;

rewrite the active view using a filter condition, the filter condition identifying one or more changed rows of a physical database table; and

execute the rewritten active view to retrieve the changed rows from the physical database table; and

one or more processors configured to execute the rewrite module.

9. The system according to claim 8, wherein the filter condition reflects a primary key of the physical database table.

10. The system according to claim 8, wherein the changed rows are identified from a transaction log.

11. The system according to claim 8, wherein the rewrite module is configured to rewrite the active view by recursively parsing through at least one intermediate view to identify the physical database table.

12. The system according to claim 8, wherein the rewrite module is further configured to provide an output of the rewritten active view to the entity.

13. The system according to claim 12, wherein the output comprises data from the changed rows.

14. The system according to claim 12, wherein the changed rows have changed since the most recent execution of the active view by the entity.

15. The system according to claim 8, wherein the rewrite module is further configured to rewrite the active view using another filter condition identifying other changed rows of another physical database table.

16. The system according to claim 15, wherein the rewrite module is configured to perform a UNION operation on the changed rows of the physical database table and the other changed rows of the another physical database table.

17. 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:

identifying an active view that retrieves records from a database, the active view being associated with an entity;

determining one or more physical tables in the database that are referenced by the active view;

tracking one or more columns in the one or more physical tables that are referenced by the active view;

identifying a change to the one or more physical tables in a transaction log of the database;

in a first instance when the change does not affect the one or more columns that are referenced by the active view, treating the change as an ignorable change for the active view;

in a second instance when the change does affect the one or more columns;

rewriting the active view using a condition that includes a primary key obtained from the transaction log;

retrieving changed rows from the database using the condition; and

providing an output of the rewritten active view to the entity, the output reflecting only the changed rows.

18. The one or more computer-readable storage media according to claim 17, wherein the change is identified using a timestamp or version number from the transaction log.

19. The one or more computer-readable storage media according to claim 17, wherein the entity is a user and the output of the rewritten active view is provided to the user via a notification at a mobile device.

20. The one or more computer-readable storage media according to claim 17, wherein, in the first instance, the change does not affect any column returned by the active view and also does not affect any condition of the active view.

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.

22. A method comprising:

registering a subscription for an entity to receive a notification relating to a personal health care record stored in a health care database;

associating the subscription with a health care concept; and

in an instance when a transaction log of the health care database is updated to include a value that relates to the health care concept, sending the notification to the entity.