Systems and methods for database notification interface to efficiently identify events and changed data

• US 9,292,587 B2
• Filed: 07/21/2010
• Issued: 03/22/2016
• Est. Priority Date: 07/21/2010
• Status: Active Grant

• Current Assignee: Citrix Systems, Inc. (Cloud Software Group)
• Original Assignee: Citrix Systems, Inc. (Cloud Software Group)
• Inventors: Kann, Jong J., Rafiq, Pierre P.
• Primary Examiner(s): Huang, Miranda
• Application Number:
  US12/841,003
• Publication Number:
  US 20120023077A1
• Time in Patent Office: 2,071 Days
• Field of Search: 707/613, 707/310, 707/620, 707/625, 707/626
• US Class Current: 1/1
• CPC Class Codes: G06F 16/275 Synchronous replication

Abstract

Systems and methods for providing notifications of changes made in a database table to a remote application is presented. The system and methods manage change notification of a table in a database by notifying an application registered to receive notifications of changes made to a database table. A database manager executing on a device may receive a request to register an application to be notified when a first table of a plurality of tables in a database is changed. The database manager may establish a notification table comprising fields of the first table and one or more additional fields for managing notification. The database manager may establish a trigger on the first table to invoke a trigger procedure to copy a changed row of the first table to the notification table. The database manager may create a rule for the notification table to notify registered applications when a new row is inserted into the notification table.

Claims

1. A method for managing a change notification of a table in a database, the method comprising:

(a) receiving, by a database manager executing on a device, a request to register an application to be notified when a first table of a plurality of tables in a database is changed, the request comprising a schema name, a table name, a notification type and an application bit as input;

(b) establishing, by the database manager, a notification table comprising copies of fields included in one or more entries of the first table and one or more additional fields for managing a notification, an additional field of the one or more additional fields comprising a bit-mask identifying one or more applications registered for notification of one or more tables, the notification table further comprising an update identifier including information corresponding to a particular update event corresponding to an update made to at least one entry of the first table and a transaction sequence field including a sequence of bits to record a sequence of update events occurring in a single transaction that caused the update made to at least one entry of the first table;

(c) establishing, by the database manager, a trigger on the first table to invoke a trigger procedure to copy a changed row of the first table to the notification table; and

(d) creating, by the database manager, a rule to notify the registered application when a new row is inserted into the notification table.

2. The method of claim 1, wherein step (a) further comprises assigning, by the database manager, to the application a bit in the bit-mask.

3. The method of claim 2, wherein step (b) further comprising defining a first field of the notification table storing the bit-mask.

4. The method of claim 1, wherein step (c) further comprises creating the trigger to invoke the trigger procedure after one of an insert, delete or update event after the event has occurred.

5. The method of claim 1 wherein step (c) further comprises creating one or more listener fields to identify one or more additional applications to be notified when the first table in the database is changed.

6. The method of claim 1, further comprising communicating by the database manager responsive to the rule, a name of a changed table to the application.

7. The method of claim 1, further comprising querying by the application the notification table for notification entries and upon handling the notification entries clearing an assigned application bit in a bit-mask stored in the notification table.

8. The method of claim 1, further comprising deleting, by the database manager, one or more notification entries in the notification table upon determining that the bit-mask identifies that all registered applications have processed the one or more notification entries.

9. The method of claim 1, further comprising updating, by the database manager upon an update event to the first table, a first row having an original entry of the first table and a second row having a new entry for the original entry.

10. A system for managing a change notification of a table in a database, the system comprising:

a database manager executing on a device including one or more hardware processors, the database manager managing a plurality of tables in a database;

an application registrar receives a request to register an application to be notified when a first table of the plurality of tables is changed, wherein the request comprises a schema name, a table name, a notification type and an application bit as input;

a notifier establishes a notification table comprising copies of fields included in one or more entries of the first table and one or more additional fields for managing a notification, an additional field of the one or more additional fields comprising a bit-mask identifying one or more applications registered for notification of one or more tables, the notification table further comprising an update identifier including information corresponding to a particular update event corresponding to an update made to at least one entry of the first table and a transaction sequence field including a sequence of bits to record a sequence of update events occurring in a single transaction that caused the update made to at least one entry of the first table, and

wherein the database manager establishes a trigger on the first table to invoke a trigger procedure to copy a changed row of the first table to the notification table and creates a rule for the notifier to notify the registered application when a new row is inserted into the notification table.

11. The system of claim 10, wherein the application registrar further assigns to the application a bit in the bit-mask.

12. The system of claim 11, wherein the notifier further defines a first field of the notification table storing the bit-mask.

13. The system of claim 10, wherein the database manager further creates the trigger to invoke the trigger procedure after one of an insert, delete or update event after the event has occurred.

14. The system of claim 10, wherein the notifier further creating one or more listener fields to identify one or more additional applications to be notified when the first table in the database is changed.

15. The system of claim 10, wherein the database manager further communicates, responsive to the rule, a name of a changed table to the application.

16. The system of claim 10, wherein the application further queries the notification table for notification entries and wherein the database manager upon handling the notification entries clears an assigned application bit in a bit-mask stored in the notification table.

17. The system of claim 10, wherein the notifier further deletes one or more notification entries in the notification table upon determining that the bit-mask identifies that all registered applications have processed the one or more notification entries corresponding to the first table.

18. The system of claim 10, wherein the database manager further updates, upon an update event to the first table, a first row having an original entry of the first table and a second row having a new entry for the original entry.