Data change tracking and event notification

• US 8,818,944 B2
• Filed: 06/30/2011
• Issued: 08/26/2014
• 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/172,889
• Publication Number:
  US 20130006963A1
• Time in Patent Office: 1,153 Days
• Field of Search: 707/624, 707/714, 707/648
• US Class Current: 707/624
• 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 a first active view that retrieves records from a database;

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

tracking one or more first columns in the one or more physical tables that are used by the first 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 tracked first columns, treating the change as an ignorable change for the first active view;

in the first instance, identifying a second active view that returns an individual second column that is affected by the change or that is conditioned on the individual second column that is affected by the change; and

in the first instance, recomputing the second active view so that the second active view reflects the change,

wherein the transaction log is separate from the one or more physical tables and includes both;

data that was included in the one or more physical tables of the database before the change was made to the one or more physical tables, and

other data that was included in the one or physical tables of the database after the change was made to the one or more physical tables.

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

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

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

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

5. The method according to claim 1, wherein the second active view returns the individual second column that is affected by the change.

6. The method according to claim 1, wherein the second active view is conditioned on the individual second column that is affected by the change.

7. The method according to claim 1, wherein treating the change as an ignorable change comprises not recomputing the first active view in the first instance.

8. The method of claim 1, wherein the transaction log comprises a change data capture table.

9. The method of claim 1, wherein the transaction log comprises a redo log.

10. A system comprising:

a rewrite module configured to;

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

identify one or more changed rows of a physical database table from a transaction log;

extract, from the transaction log, one or more primary keys for the one or more changed rows of the physical database table;

rewrite the active view using a filter condition identifying the one or more primary keys extracted from the transaction log; and

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

one or more processors configured to execute the rewrite module,

wherein the transaction log is separate from the physical database table and includes both;

data that was included in the physical database before the change was made to the physical database table, and

other data that was included in the physical database table after the change was made to the physical database table.

11. The system according to claim 10, wherein the rewrite module is embodied as computer-readable instructions executable by the one or more processors.

12. The system according to claim 10, wherein the rewrite module is further configured to identify the one or more changed rows in the transaction log from a time beginning with a most recent time that the entity was notified of a previous change to the active view.

13. The system according to claim 10, 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.

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

15. The system according to claim 14, wherein the output comprises data from the one or more changed rows.

16. The system according to claim 14, wherein the rewrite module is further configured to identify the one or more changed rows in the transaction log from a time period beginning with a most recent execution of the active view.

17. The system according to claim 10, wherein the rewrite module is further configured to rewrite the active view using a different filter condition identifying one or more different changed rows of a different physical database table.

18. The system according to claim 17, wherein the rewrite module is configured to perform a UNION operation on the one or more changed rows of the physical database table and the one or more different changed rows of the different physical database table.

19. One or more hardware computer-readable storage devices 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 tracked 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 tracked 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,

wherein the transaction log is separate from the one or more physical tables.

20. The one or more hardware computer-readable storage devices according to claim 19, wherein the change is identified using a timestamp or version number from the transaction log.

21. The one or more hardware computer-readable storage devices according to claim 19, 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.

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