Apparatus and method for message queuing in a database system

• US 6,058,389 A
• Filed: 10/31/1997
• Issued: 05/02/2000
• Est. Priority Date: 10/31/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Jain, Sandeep, To, Alvin, Chandra, Sashikanth, Shah, Premal, Gawlick, Dieter, Rizvi, Hasan
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US08/961,597
• Time in Patent Office: 914 Days
• Field of Search: 707/10, 707/103, 707/1, 707/100, 709/100, 709/102, 709/103, 709/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 2209/548 Queue
• CPC Class Codes: G06F 9/546 Message passing systems or structures, e.g. queues
• CPC Class Codes: Y10S 707/99931 Database or file accessing

Abstract

An advanced message queuing system is integrated into a database system. A queue is an ordered list of messages. Messages are requests for processing by an application. Messages are database objects and can represent events or data. Messages comprise user data and control information such as a queue name. Each queue is part of a table in a relational database. A queue table holds a set of queues. Dictionary tables store configuration information describing queues and queue tables. Messages are entered into a queue by instructing the database system using an enqueuing command attached to a message and control information. The control information describes how to order, schedule, and execute the message, and can include a result queue name into which a result message is written after execution. The system responds to a dequeuing command by delivering a copy of a message from the queue. A user can define message order within a queue, message delay factors, and exception processing. Messages may be retained in their queues after delivery and can be preserved, queried, documented, correlated, reviewed and tracked, alone or in a set comprising a transaction, regardless of message state or execution state. The system can be used to develop large-scale, message-oriented distributed applications. Existing development tools for database applications can also be used to develop queuing applications. Administrative functions to create, delete, and specify access control for queues are provided. The system provides transactional integrity; a single transaction applies to both the database and the queue. A single transaction log is maintained.

Claims

1. A method for receiving, queuing and executing messages in a database system in a computer system including a memory, comprising the steps of:

establishing a queue in the memory using a table of the database system;

receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

waiting until said condition is satisfied;

executing said message by an application program; and

retaining said message in said queue.

2. The method recited in claim 1, wherein the step of establishing a queue in memory includes the step of establishing a queue table in which the queue is stored.

3. The method recited in claim 1, wherein the step of establishing a queue in memory includes the step of establishing a dictionary table in the memory, said dictionary table comprising descriptive information about said queue.

4. The method recited in claim 1, wherein the step of receiving a message to the queue includes the step of receiving to the queue a message comprising an abstract data type.

5. The method recited in claim 1, wherein the step of receiving a message to the queue includes the step of receiving to the queue a message comprising a large object binary handle identifying a location of a large object binary attachment.

6. The method recited in claim 1, wherein the step of receiving to the queue a message includes the step of receiving to the queue a message comprising a reference to a second message in a second queue.

7. The method recited in claim 6, wherein the step of executing said message includes the steps of:

(d1) establishing a transaction comprising said message and said second message; and

(d2) forwarding said transaction to a third queue.

8. The method recited in claim 1, wherein the step of establishing a queue in memory includes the step of establishing said queue in a meta-data cache of said database system.

9. The method recited in claim 1, further comprising the steps of:

dequeuing said message from said queue;

retaining said message in said queue; and

dequeuing said message from said queue again.

10. The method recited in claim 1, further comprising the steps of:

establishing an exception queue in said table;

raising an exception if said application program cannot process said message;

attempting to process said message a plurality of times; and

enqueuing said message in said exception queue exception when said application program cannot process said message.

11. The method recited in claim 1, wherein the step of receiving a message to said queue includes the steps of reading an order for said message from an order table and enqueuing said message in said table in said order.

12. The method recited in claim 1, wherein the step of receiving a message to said queue includes the step of receiving a message comprising a priority of said message in said queue.

13. The method recited in claim 1, wherein the step of receiving a message to said queue includes the steps of:

establishing in said queue a first message including a first priority within said queue;

receiving a second message including a second priority higher than said first priority; and

inserting said second message in said queue in a superior position with respect to said first message.

14. The method recited in claim 1, wherein the step of receiving a message to said queue includes the step of establishing a message in said queue wherein said message comprises a start time and an end time for describing when said message is to be executed.

15. The method recited in claim 14, wherein the step of executing said message includes the step of executing said message only after said start time and before said end time.

16. The method recited in claim 1, wherein the step of waiting until said condition is satisfied includes the step of waiting until execution of a second message referenced in said meta-data of said message.

17. The method recited in claim 1, wherein the step of receiving to the queue a message includes the step of receiving to the queue a message comprising a correlation identifier, which describes a message to a user.

18. The method recited in claim 1, wherein the step of receiving to the queue a message includes the step of receiving to the queue a message comprising a name of a reply message queue in said database system, and wherein the step of executing said message includes the step of enqueuing an execution result of said message in said reply message queue.

19. The method recited in claim 1, wherein the step of receiving to the queue a message includes the step of receiving to the queue a message comprising a security context of said message.

20. A computer system comprising:

a processor; and

a memory coupled to said processor;

the memory having stored a queue using a table of a database system; and

sequences of instructions which, when executed by said processor, cause said processor to

receive, queue and execute a message in the database system by causing the processor to perform the steps of;

(a.) receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

(b.) waiting until said condition is satisfied;

(c.) executing said message by an application program; and

(d.) retaining said message in said queue.

21. The system of claim 20, wherein said memory further comprises a queue table in which said queue is stored.

22. The system of claim 20, wherein said memory further a dictionary table in the memory, said dictionary table comprising descriptive information about said queue.

23. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising an abstract data type.

24. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising a large object binary handle identifying a location of a large object binary attachment.

25. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising a reference to a second message in a second queue.

26. The system of claim 25, wherein said sequence of instructions further comprises instructions causing said processor to:

(d1) establish a transaction comprising said message and said second message; and

(d2) forward said transaction to a third queue.

27. The system of claim 20, wherein said queue is stored in a meta-data cache of said database system.

28. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to dequeue said message from said queue;

retain said message in said queue; and

dequeue said message from said queue again.

29. The system of claim 20, wherein said memory further comprises an exception queue in said table and wherein said sequence of instructions further comprises instructions causing said processor to raise an exception if said application program cannot process said message;

attempting to process said message a plurality of times; and

enqueuing said message in said exception queue exception when said application program cannot process said message.

30. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to read an order for said message from an order table and enqueue said message in said table in said order.

31. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive a message comprising a priority of said message in said queue.

32. The system of claim 20, wherein said memory further comprises in said queue a first message including a first priority within said queue;

wherein said sequence of instructions further comprises instructions causing said processor to receive a second message including a second priority higher than said first priority; and

insert said second message in said queue in a superior position with respect to said first message.

33. The system of claim 20, wherein said memory further comprises a message in said queue wherein said message comprises a start time and an end time for describing when said message is to be executed.

34. The system of claim 33, wherein said sequence of instructions further comprises instructions causing said processor to execute said message only after said start time and before said end time.

35. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to wait until execution of a second message referenced in said meta-data of said message.

36. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising a correlation identifier, which describes a message to a user.

37. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising a name of a reply message queue in said database system, and wherein the step of executing said message includes the step of enqueuing an execution result of said message in said reply message queue.

38. The system of claim 20, wherein said sequence of instructions further comprises instructions causing said processor to receive to the queue a message comprising a security context of said message.

39. A computer-readable medium having stored thereon a plurality of sequences of instructions, said plurality of sequences of instructions including sequences of instructions which, when executed by a processor, cause said processor to perform the steps of:

establishing a queue in a memory coupled to a processor using a table of a database system in said memory and executed by said processor;

receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

waiting until said condition is satisfied;

executing said message by an application program in said memory and executed by said processor; and

retaining said message in said queue.

40. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of establishing a queue in memory include instructions causing the processor to execute the step of establishing a queue table in which the queue is stored.

41. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of establishing a queue in memory include instructions causing the processor to execute the step of establishing a dictionary table in the memory, said dictionary table comprising descriptive information about said queue.

42. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to the queue include instructions causing the processor to execute the step of receiving to the queue a message comprising an abstract data type.

43. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to the queue include instructions causing the processor to execute the step of receiving to the queue a message comprising a large object binary handle identifying a location of a large object binary attachment.

44. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving to the queue a message include instructions causing the processor to execute the step of receiving to the queue a message comprising a reference to a second message in a second queue.

45. The computer-readable medium recited in claim 44, wherein the instructions causing the processor to execute the step of executing said message include instructions causing the processor to execute the steps of:

(d1) establishing a transaction comprising said message and said second message; and

(d2) forwarding said transaction to a third queue.

46. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of establishing a queue in memory include instructions causing the processor to execute the step of establishing said queue in a meta-data cache of said database system.

47. The computer-readable medium recited in claim 39, further comprising instructions causing the processor to execute the steps of:

dequeuing said message from said queue;

retaining said message in said queue; and

dequeuing said message from said queue again.

48. The computer-readable medium recited in claim 39, further comprising instructions causing the processor to execute the steps of:

establishing an exception queue in said table;

raising an exception if said application program cannot process said message;

attempting to process said message a plurality of times; and

enqueuing said message in said exception queue exception when said application program cannot process said message.

49. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to said queue include instructions causing the processor to execute the steps of reading an order for said message from an order table and enqueuing said message in said table in said order.

50. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to said queue include instructions causing the processor to execute the step of receiving a message comprising a priority of said message in said queue.

51. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to said queue include instructions causing the processor to execute the steps of:

establishing in said queue a first message including a first priority within said queue;

receiving a second message including a second priority higher than said first priority; and

inserting said second message in said queue in a superior position with respect to said first message.

52. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving a message to said queue include instructions causing the processor to execute the step of establishing a message in said queue wherein said message comprises a start time and an end time for describing when said message is to be executed.

53. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of executing said message include instructions causing the processor to execute the step of executing said message only after said start time and before said end time.

54. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of waiting until said condition is satisfied include instructions causing the processor to execute the step of waiting until execution of a second message referenced in said meta-data of said message.

55. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving to the queue a message include instructions causing the processor to execute the step of receiving to the queue a message comprising a correlation identifier, which describes a message to a user.

56. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving to the queue a message include instructions causing the processor to execute the step of receiving to the queue a message comprising a name of a reply message queue in said database system, and wherein the step of executing said message includes the step of enqueuing an execution result of said message in said reply message queue.

57. The computer-readable medium recited in claim 39, wherein the instructions causing the processor to execute the step of receiving to the queue a message include instructions causing the processor to execute the step of receiving to the queue a message comprising a security context of said message.

58. A computer data signal embodied in a carrier wave and representing sequences of instructions which, when executed by a processor, cause said processor to queue messages in a database system by performing the steps of:

establishing a queue in a memory coupled to a processor using a table of said database system in said memory and executed by said processor;

receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

waiting until said condition is satisfied;

executing said message by an application program in said memory and executed by said processor; and

retaining said message in said queue.

59. A method for receiving, queuing and executing messages in a database system in a computer system including a memory, comprising the steps of:

establishing a queue in the memory using a table of the database system;

receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

executing said message by an application program only when said condition is satisfied; and

retaining said message in said queue.

60. A computer-readable medium having stored thereon a plurality of sequences of instructions, said plurality of sequences of instructions including sequences of instructions which, when executed by a processor, cause said processor to perform the steps of:

establishing a queue in a memory coupled to a processor using a table of a database system in said memory and executed by said processor;

receiving to the queue at least one message comprising message meta-data and message user data, said message meta-data including a condition for executing said message;

executing said message by an application program in said memory and executed by said processor only when said condition is satisfied; and

retaining said message in said queue.