Apparatus and method for message queuing in a database system6058389Abstract 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 What is claimed is: Description FIELD OF THE INVENTION
______________________________________
Column Contents
______________________________________
QUEUE Queue name
MSG.sub.-- ID Message identifier
CORR.sub.-- ID User-provided correlation identifier
MSG.sub.-- PRIORITY
Message priority
MSG.sub.-- STATE State of the message (READY to be
processed, DELAYED,
PROCESSED,
or EXPIRED)
DELAY Time after which the message will be
ready to be processed
EXPIRATION Message expiration time in seconds
TIME.sub.-- MANAGER.sub.-- INFO
Date used by time manager process to
monitor messages
LOCAL.sub.-- ORDER.sub.-- NO
Local order number of message
CHAIN.sub.-- NO Chain number of message
DSCN Dependent transaction number
CSCN Commit transaction number
ENQ.sub.-- TIME Original enqueue time
ENQ.sub.-- USER.sub.-- ID
User identifier for the user who
enqueued the message
ENQ.sub.-- TXN.sub.-- ID
Current transaction
identifier
DEQ.sub.-- TIME Time when the message was
dequeued
DEQ.sub.-- TXN.sub.-- ID
Transaction which performed the
dequeue
DEQ.sub.-- USER.sub.-- ID
User id of the user who dequeued the
message
RETRY.sub.-- COUNT
Number of retries
REPLY.sub.-- QUEUE.sub.-- OWNER
Reply queue schema
REPLY.sub.-- QUEUE
Reply queue name
EXCEPTION.sub.-- QUEUE.sub.-- OWNER
Exception queue schema
EXCEPTION.sub.-- QUEUE
Exception queue name
ARG.sub.-- COUNT Number of arguments
USER.sub.-- DATA User data for use by an application or
process
______________________________________
Configuration information for queue tables and queues are stored in two dictionary tables. A Queue Table Dictionary Table 212 stores configuration information for queue tables and a Queue Dictionary Table 210 stores configuration information for all queues defined in the system. In one embodiment, each row of the Queue Table Dictionary Table 212 has configuration information for one queue table 200, and the Queue Table Dictionary Table 212 has the following columns:
______________________________________
Column Contents
______________________________________
OWNER Queue table schema
QUEUETABLE Queue table name
TYPE Type of user data stored in queues in
the queue table (OBJECT TYPE for
user-defined object types or VARIANT
for internal use)
OBJECT.sub.-- TYPE
Abstract data type of user data stored in
queues of the queue table (provided
only when TYPE has the value
OBJECT TYPE)
OBJECT.sub.-- NUMBER
Number of queue table object
TABLE.sub.-- COMMENT
User comment about the queue table
______________________________________
In this embodiment, each row of the Queue Dictionary Table 210 represents a queue defined in a queue table of the queuing system, and the Queue Dictionary Table 210 has the following columns:
______________________________________
Column Contents
______________________________________
OWNER Queue schema name
NAME Queue name
QUEUETABLE Queue table containing the queue
named NAME
QID Identifier for the queue
QTYPE Queue type (NORM for normal queue,
EXPT for exception queue)
MAX.sub.-- RETRIES
Number of times a message is
processed before being moved to an
exception queue
RETRY.sub.-- INTERVAL
Time lapse before retry takes place
ENQUEUE Boolean flag identifying whether
ENQUEUE is disabled/enabled
DEQUEUE Boolean flag identifying whether
DEQUEUE is disabled/enabled
TRACKING Boolean flag identifying whether queue
tracking is disabled or enabled
RETENTION The duration for which dequeued
messages will be retained in the queue
(TRANSIENT if messages retained for
the duration specified in DURATION;
PERMANENT if message retained
permanently)
DURATION Number of days for which the message
is retained
QUEUE.sub.-- COMMENT
User comment about the queue
______________________________________
To enable users to specify a default sorting order for each queue table when a queue table is created, the queuing system also includes a Queue Table Sort table 214. Each row in the Queue Table Sort table 214 represents a sort order for a queue table. The table has the following columns:
______________________________________
Column Contents
______________________________________
OBJ.sub.-- NO Object number
SORT.sub.-- POS
Position of this column; position
defines where to order
SORT.sub.-- COLUMN
Position of the column used in the sort
expression of DEQUEUE
SORT.sub.-- ORDER
Column order (allowed values are 1 for
Ascending, 2 for Descending)
COLUMN.sub.-- NAME
Column name for DEQUEUE sort
order
______________________________________
In one embodiment, there is also a Time Manager table or Timetable 216. The Timetable 216 has a row for each message 208 that is subject to a delay time or expiration time. Each row contains columns that store the message identifier, queue name, queue schema, time in seconds, and a flag indicating whether the time is a delay time or expiration time. Each of the tables 200, 210, 212, 214 and 216 are stored non-volatile storage or in main memory of the computer system 100 and are logically coupled to the database server 310. An agent is a queue user. Two types of agents interact with the queuing system: producers that place messages in a queue, and consumers that retrieve or dequeue messages. Any number of agents may access a queue at a given time. Such agents are considered outside the structure of the queuing system, and therefore are not described in detail in this document. However, they interact with the queuing system and are discussed as the context requires. Preferably, agents are internally represented in the queuing system using an abstract data type Agent having attributes that store the agent's name, address, and protocol. Processes of the queuing system are carried out by computer program segments configured to execute the operations described herein. The processes and operations described above can be implemented in computer programs using any suitable programming language, such as a language that is generally usable for developing application programs for a relational database system. The processes are embodied in executable computer program segments that form a part of a database server of a relational database system, so that the processes are called from within an application program developed for that relational database system. For example, the processes can be compiled into an Advanced Queuing (AQ) system 318 of a kernel portion of the database server of the RDBMS. In this embodiment, the processes are accessible through calls to kernel functions that implement the processes, and through a public application program interface (API) so that an application program can access the processes using API calls. The API can be the Oracle Call Interface (OCI), and can be implemented in the Structured Query Language (SQL) or in PL/SQL. Application programs include calls to OCI functions, using OCI handles, or API calls that carry out message enqueuing, dequeuing, and queue management. Application programs also can call queue system functions using executive or EXEC statements embedded within the program. For example, an application program written in the C language can access queue functions by using the EXEC compiler directive to embed SQL statements and queue system function calls in the program. Other processes are implemented as background processes. In one embodiment, the processes are implemented in the same programming source language used for other elements of the database server. For example, the queuing system is provided with a C language interface as a part of the kernel of the database server, comprising a set of C language functions implementing the processes of the invention. Each of the C functions corresponds to one of the processes of the system, e.g., ENQUEUE or DROP.sub.-- QTABLE. Each C function accepts one C data structure ("struct") as a parameter. Each C function converts values passed to it in the C structure into values understandable by the processes of the queuing system. In this embodiment, an SQL interpreter is coupled to the C functions. In this way, the processes can be called either as C functions or by directing SQL statements to the SQL interpreter, which translates the SQL statements into an appropriate C function call. Thus, the SQL interpreter forms an API in which each API call has the name of the process given above. General Features The following general aspects characterize an embodiment of the invention. SQL ACCESS. Messages are placed in rows of a database table. Messages can be queried using standard SQL statements. Thus, an application program can use SQL to access message properties, message history, and message content. Existing SQL technology, such as indexes, can be used to optimize access to messages. Integrated Database Level Operation Support All standard database features such as recovery and restart are supported. The queuing system can be administered through existing database management tools. Because queues are implemented in database tables, the operational benefits of high availability, scalability and reliability apply to queue data. Also, database application development and management tools can be used with queues; for example, queues can be imported and exported. Structured Payload An application program can specify message content using abstract data types (ADTs) based upon the rich typing system of an existing database system. When the invention is implemented with an object-relational database management system, such as Oracle8, the application program may use relational data types as well as user-defined types. As a result of messages having strongly typed content, powerful features are enabled. For example, the queuing system can be used to provide content-based message routing whereby an external agent or application program examines content of a message and routes the message to another queue based upon its content. Retention and Message History An application program can specify that messages shall be retained in a queue after consumption or dequeuing by the program or another program. The duration of retention can be specified. The system stores information about the history of each message, including the time of enqueuing and dequeuing, and the identity of the transaction that carried out enqueuing or dequeuing. The history information can be used for tracking, data warehouse, and data mining operations. Tracking and Event Journals Retained messages can be related to one another. As a result, sequences of related messages can be tracked and used as event journals. Integrated Transactions The integration of control information with content (data payload) in a message simplifies application development and management. Ordering Each message in a queue is ordered within the queue. The order of a message is defined at the time a queue table is created, and is used for sorting messages when a DEQUEUE operation is executed. In response to a DEQUEUE request, the system retrieves the first message according to the sort order, skipping messages that are to be processed by other transactions. The system can execute steps to control the order of messages in a queue. The system is configured to associate columns in a queue table with a message of a queue so that the columns are used to order messages. By default, messages are ordered in ascending or first in, first out (FIFO) order. The order of a message also can be specified using a priority code, or by a sequence deviation parameter as described below. The system is also configured to execute steps such that message processing is delayed, for example, so that a DEQUEUE request for a message will fail until a specified time delay has passed. However, the time delay will be ignored if the system receives a message identifier or a correlation identifier for the message at the time a DEQUEUE operation is requested. Thus, if a user of the system knows the specific identity of a message and requests it, the system will not force the user to wait until the time delay has passed. Sequence Deviation The system is configured to change the dequeuing order of a specific message if the message contains control information indicating that the message should be processed before a reference message. The system is configured to accept urgent message enqueue requests; urgent messages are automatically placed at the top of a queue. Time Constraints The system is configured to carry out steps that cause a specific message to be processed only within a particular time interval or "time window." Correlation Identifiers The system is configured to carry out steps that cause a specific message to be tagged with a label called a "correlation identifier." Correlation identifiers facilitate human use of the system by enabling messages to be retrieved by a mnemonic alphanumeric name. Reply Queue The system is configured to carry out steps that cause a message received from an application program, such as applications 301, 302, to be tagged with a reply queue identifier. The application program can provide the name of a reply queue or output queue at the time that the application requests to ENQUEUE a message. When the message is processed, the processing application or transaction can direct an output message to the named reply queue. Exception Queue The system includes an exception queue 206 in each queue table 200. If a message cannot be executed according to control information associated with the message (e.g., within a given time window), if execution of a message results in failure, or if an external application server or an application program 301, 302 is unable to process a message, the message is moved to the exception queue 206. A user can specify a different exception queue. The system can be configured to attempt to pass a message to an external application server several times before enqueuing the message in the exception queue. The exception queue enables rapid detection of failed message execution because such failed messages are directed to, and can be reviewed in, a single location. Non-transactional Requests Normally ENQUEUE and DEQUEUE requests are made in the context of a larger transaction containing other requests and statements in the Structured Query Language (SQL). The system enables a message to be treated as an independent transaction. An application can specify that a specific request is a transaction itself, thereby making its result immediately available to other transactions. For example, messages can be made visible to other applications either immediately after an enqueue or dequeue operation is completed, or only after the transaction is committed. Browsing The system is configured normally to delete a message after the DEQUEUE operation results in successful processing of the message. However, the system is also configured to receive a BROWSE option that is set in control information provided to the system with the DEQUEUE request. When a message is "browsed," it remains available in the queue for further processing. When a message is removed, it is not available for subsequent dequeue requests. Security When a message is enqueued in a queue, the system assigns the message the same security context as the process or application issuing the ENQUEUE request. If the security context changes as multiple messages in a transaction are processed, then the system sends a message to a security system of the relational database system requesting the current security context to be re-set to the context of the original message. In this way, an application server can inherit the security context of the first message in a transaction, which is the same context as the process, which created the transaction. Multiple Consumers Per Message A message can be dequeued by more than one application. Each queue acts as a message repository, and each application has a different view of the same queue. Accordingly, a message can be dequeued and deleted from an application's view, while the same message remains in the queue view of another application. Subscription & Recipient Lists An application programmer or administrator can specify a list of subscribers that can retrieve messages from a queue. In this context, the term "subscribers" means application programs or processes that use messages from a queue. Different queues can have different subscribers, and a consumer program can be a subscriber to more than one queue. Further, specific messages in a queue can be directed to specific recipients who may or may not be subscribers to the queue, thereby overriding the subscriber list. Message Grouping Messages belonging to one queue can be grouped to form a set that can only be consumed by one subscriber at a time. All messages belonging to a group are created in the same transaction and all messages created in one transaction belong to the same group. Accordingly, complex messages can be segmented into several simple messages. Navigation Messages in Dequeuing A consuming application or process can select the first message in a queue, or select a particular message and thereafter select the next message. Selection can be specified through ordering, and can be limited using a correlation identifier. Specific messages can be retrieved using a message identifier. Waiting for Messages A dequeue request can be issued against an empty queue. An application can specify whether, and for how long, the request should wait for a message to arrive. Retry with Delay Generally a message can be consumed or dequeued exactly once. If an attempt to dequeue a message fails, the message will be made available for reprocessing after a user-specified delay. Reprocessing will be attempted multiple times, and the maximum number of retries can be specified by the application or user. Queue System Operation Operation and structure of the system and its components to carry out the foregoing processes is described in the following section. Enqueue Operation The system adds a message to a queue by receiving and executing an ENQUEUE operation request or process. The process comprises computer program segments configured to carry out the steps described below. An ENQUEUE request can be an SQL statement having the name ENQUEUE and accepting the parameters shown in Table
TABLE 1
______________________________________
ENQUEUE Parameters
______________________________________
Queue Name
Enqueue Options
Message Properties
User Data (Payload)
Message Identifier
______________________________________
All parameters are optional except Queue Name and User Data. The Enqueue Options parameter is a data structure that stores the following values: Visibility; Relative Message Identifier; and Sequence Deviation Code. The Message Properties parameter is a data structure that stores the following values: Priority Code; Delay; Expiration Time; Correlation Identifier; Attempts; Recipient List; Exception Queue; Enqueue Time; and State. In one embodiment, values reflecting message priority, delay, expiration, correlation, attempts at execution, recipient list, exception queue, enqueue time, and message state are grouped together and stored as a message properties object. The parameters have the following definitions and effects on the system and its components: Queue Name: The Queue Name value identifies the name of the queue to which the message should be enqueued. The value may not be an exception queue because the system is configured to prohibit users from enqueuing messages to the exception queue; only the system may do so. Correlation Identifier: The correlation identifier is a value that identifies a message to a user of the system. The correlation identifier can be any convenient data type, preferably a data type that is known to and usable by an application program 301, 302 in communication with the system. Visibility: the Visibility parameter specifies the transactional behavior of the message to be enqueued If Visibility is set to ON.sub.-- COMMIT, then the message is part of a current transaction along with other messages (the "standard option"), and changes to the queue are not made until the calling application program indicates that the transaction should commit. By default, the Visibility parameter is ON.sub.-- COMMIT. If the Visibility parameter is IMMEDIATE, then the enqueued message is not part of the current transaction but rather is part of a recursive transaction (the "immediate option"). Processes for beginning and committing recursive transactions are provided by the database server 310. A process for evaluating the Visibility parameter during an ENQUEUE operation is shown in FIG. 6A. Step 600 indicates that Visibility parameter value processing is part of the ENQUEUE and DEQUEUE processes. In step 602, the process tests whether the Immediate option is selected, i.e., whether the Visibility parameter is set to IMMEDIATE. If the parameter is ON.sub.-- COMMIT, then in step 604 the process carries out a table insert operation (for ENQUEUE processes), or select and delete operations (for DEQUEUE processes). Thus, in this step the process either inserts a row into the queue table (to enqueue a message) or selects rows from the table (to dequeue). If Destructive dequeue has been selected (as discussed further herein), a delete operation is also done to remove the message row from the queue table. If the Visibility parameter is IMMEDIATE, then Immediate mode is selected and in step 606 the process starts a new recursive transaction by calling a kernel function in database server 310. In step 608 the process then carries out an insert, select or delete in the same manner as described above for step 604. In step 610 the process calls another kernel function of database server to commit the recursive transaction. In step 612 the process flushes the log file to make the changed data persistent. Thus, steps 610 and 612 cause the changes to the queue table made in step 608 to become permanent. In step 614 processing continues with other ENQUEUE or DEQUEUE steps. Priority: Message priority is specified using the Priority parameter. Message priority is reviewed when messages are dequeued if the priority is specified as one of the column orderings when a queue table is created. The value of the Priority parameter may be any integer, including a negative integer; a smaller number indicates a higher priority. The default value is one. Delay: the value of the Delay parameter represents the earliest time when the message should be scheduled for processing. If the value is NO.sub.-- DELAY, then the enqueuing process is requesting immediate processing of the message. If the value is a number, then the system will not provide the message in response to a subsequent DEQUEUE operation until the Delay value in seconds has passed. However, if the DEQUEUE operation provides a correlation identifier or message identifier for the message, then the system will provide the message even if the Delay time is not complete. When a message is enqueued with a Delay value set, the State of the message is set to WAITING. When the Delay time expires, the State of the message is set to READY. Expiration: the value of the Expiration parameter specifies the latest time at which the message can be processed, expressed in seconds with reference to the Delay value. If a message is not processed before the Expiration number of seconds have passed, the message is removed from the current queue and copied into an exception queue. Also, its State property is changed from READY to EXPIRED. A user using the Exception Queue parameter specifies the exception queue. If no exception queue is specified, or if an exception queue is specified but it does not exist when needed, the message is copied to a default exception queue 206 of the current queue table 200. If the value of Expiration is NEVER, then the message will never expire. Sequence Deviation: The value of the Sequence Deviation parameter specifies whether the current message should be enqueued with a higher priority than the other messages already in the queue. Acceptable values are BEFORE, TOP, or NULL. Sequence deviation is described further below. Relative Message Identifier: The value of this parameter is the message identifier of the message that should be before the currently enqueued message. This parameter is ignored if the Sequence Deviation is not specified. The parameter is required if the Sequence Deviation parameter has a value of BEFORE. Exception Queue: The value of this parameter is the name of an exception queue to which the user of the system desires to have exception messages directed. Errors and exceptions are not defined by the database queuing system. Each application program 301, 302 defines error and exception conditions that result in queuing of messages in an exception queue. User Data: the value of the User Data parameter is any data which a user or application wishes to transmit to another application or process. The data type of the parameter must match the data type of the value of the Queue parameter. Message Identifier: If the user does not supply a message identifier, then the system internally generates a unique message identifier and includes it in the request. Thus, the message identifier persists for subsequent operations. Attempts: The Attempts parameter represents the number of attempts that have been made to dequeue the message. The value of the Attempts parameter is set by the queuing system and cannot be specified by the enqueuing application. Recipient List: The Recipient List parameter is a set of values of type Agent. Thus, the Recipient List identifies applications or processes to which messages are to be forwarded when dequeued. Enqueue Time: The Enqueue Time parameter specifies the time that the message was enqueued. This value is determined and set by the system and cannot be set by an application program. State: The State parameter specifies the state of the message at the time of a dequeue operation. Its value cannot be set at enqueue time. Valid values are WAITING, READY, PROCESSED, and EXPIRED. When the State value is WAITING, a message Delay value has not yet been reached. When the State value is READY, the message is ready to be processed. When the State value is PROCESSED, the message has been processed but has been retained in the queue. When the State value is EXPIRED, the message has been processed and has been moved to the Exception Queue. The Priority, Expiration, and Delay values, among others, represent conditions that must be satisfied before a message in a queue can be executed by an application program. FIG. 9A is a flow diagram showing an overview of certain steps carried out by the ENQUEUE process in operation. When the ENQUEUE process is invoked, e.g., through an internal C function call or through an SQL statement in an RDBMS application program, as shown in step 900, the process reads all the values of each parameter described above and passed with an ENQUEUE request, and stores them in a local memory area for rapid access. In step 902 the process carries out a series of error checks on the values. For example, the process checks whether a Priority value is in the correct range; if not, an exception is raised. In step 904 the process carries out a series of validity checks on other parameter values. For example, an exception is raised in the Expiration value is less than zero. Also, if an Expiration interval is specified, it must be greater than or equal to the Delay value. The Sequence Deviation value must be valid. If a Relative Message Identifier is specified, the process tests whether the referenced message exists. If not, an exception is raised. In step 904, the process tests whether a queue identified by the Queue Schema and Queue Name values exists. If the queue exists, it is loaded to the library cache in step 905 using the cache load process described elsewhere herein. In step 906, the process tests whether the queue is an exception queue; if so, an exception is raised because enqueue operations are not permitted on exception queues. Otherwise in step 907 the Queue Table row corresponding to the named queue is read. In step 908, if the Sequence Deviation parameter has a value, then sequence deviation processing is carried out according to the steps described herein in connection with FIG. 4A. In step 910 the process carries out transaction-related processing according to the steps described herein in connection with FIG. 6A. As described elsewhere herein, the transaction-related steps include the step of inserting a row representing the message to be enqueued into the queue table. In step 912, the process notifies other processes that a message has been enqueued, for example, by posting a system message to the queue identifier of the named queue. If a DEQUEUE process is waiting for a message in the named queue, as described below, such posting will awaken the DEQUEUE process and trigger dequeue processing. In step 914 the process evaluates the Delay and Expiration values in the manner discussed herein under the heading "Time Manager Process" and in connection with FIGS. 6B, 7A and 7C. In step 916 the process returns a message identifier of the message just enqueued to the calling process, application program, or machine. Dequeue Operation The system retrieves a message from a queue by receiving and executing a DEQUEUE operation request or process. A DEQUEUE request can be an SQL statement or C function having the name DEQUEUE and including the parameters shown in Table 2:
TABLE 2
______________________________________
DEQUEUE Parameters
______________________________________
Queue Name
Message Identifier
User Data
Dequeue.sub.-- Options
Message.sub.-- Properties
______________________________________
The Dequeue.sub.-- Options parameter is a data structure comprising the following attributes: Consumer.sub.-- Name; Dequeue.sub.-- Mode; Navigation; Visibility; Wait; Message Identifier; and Correlation. The Message.sub.-- Properties parameter is a data structure comprising the same attributes identified above in the discussion of the ENQUEUE operation. Parameters for the DEQUEUE operation that have the same name as parameters defined above for ENQUEUE operate in the same way for the DEQUEUE operation as for the ENQUEUE operation, except as described herein. The order in which messages are dequeued can be specified in several ways. As described above, if the Message Identifier or Correlation Identifier of a message 208 is known to an application program 301, 302 and is provided in a DEQUEUE request, all other orderings are overridden and the system will provide the identified message. A default dequeue order can be established for each queue table when a queue table is created as described herein in connection with the Table Sort table. If no default dequeue order is so established, and a message identifier or correlation identifier are not provided in a DEQUEUE request, then the system uses FIFO ordering to select the next message to dequeue. Only messages that are ready to be processed will be provided by the system in response to a DEQUEUE request. For example, messages having a delay time later than the current time are not provided in response to a DEQUEUE request. Following is a description of parameters used in the DEQUEUE operation and how they affect operation of DEQUEUE processing. In the Dequeue-Options parameter, the Consumer.sub.-- Name attribute stores a value identifying a process or application program that is consuming the dequeued message. When Consumer.sub.-- Name has a value, only those messages matching the consumer name are accessed during the DEQUEUE operation. In this way, a queue can provide messages to multiple consuming programs or processes. Consumer.sub.-- Name is set to NULL when a queue is not used for multiple consumers. The Navigation attribute specifies the position in the queue of the message that will be retrieved. The DEQUEUE operation locates the position, applies a search criteria to messages at that position, and retrieves selected messages that match the search criteria. Valid values are NEXT.sub.-- MESSAGE, NEXT.sub.-- TRANS-ACTION, and FIRST.sub.-- MESSAGE. When Navigation is set to NEXT MESSAGE, which is the default value, the DEQUEUE operation will retrieve the next message that is available and that matches the search criteria. If the previous message belongs to a message group, the DEQUEUE operation will retrieve the next available message that matches the search criteria and belongs to the message group. When Navigation is set to NEXT.sub.-- TRANSACTION, the DEQUEUE operation skips any messages in the current transaction group, and retrieves the first message of the next transaction group. Message grouping must be enabled for the queue. When Navigation is set to FIRST.sub.-- MESSAGE, the DEQUEUE operation retrieves the first message that is available and that matches the search criteria. The dequeue position is reset to the beginning of the queue. The Visibility, Wait, Message.sub.-- Identifier, and Correlation attributes of the Dequeue.sub.-- Options parameter affect DEQUEUE operation, with respect to the message to be dequeued, in the same manner as discussed above in connection with the ENQUEUE operation. Queue Name: Identifies the name of the queue from which the message should be dequeued. Unless a Message Identifier or Correlation Identifier value is provided, only elements that are ready to be processed (i.e., whose time delay has passed) are dequeued. The Message Identifier of the DEQUEUE operation is a system-generated value that uniquely identifies the message that is dequeued. If only a Message Identifier is specified, the system retrieves the first available message with the specified Message Identifier. If a Correlation Identifier is also specified, only the message with both the specified Message Identifier and Correlation Identifier will be retrieved. Correlation Identifier: Specifies the user-supplied identification for the message to be dequeued. See "Message Identifier," above. Dequeue Mode: DEQUEUE requests are processed by the system in one of three different modes: REMOVE mode (Dequeue Mode value of REMOVE, which is the default value); Non-destructive or "browse" mode (Dequeue Mode value of BROWSE); and Non-destructive With Locking mode (Dequeue Mode value of LOCKED). In Destructive dequeuing, which is the default mode, the system deletes a retrieved message from the queue after retrieval. In Non-destructive dequeuing, the system reads a retrieved message from the queue without deleting it. In Non-destructive With Locking, the system locks the retrieved message in the queue so that no other application or process can retrieve it, and returns a copy of the message to the calling application or process. Wait Time: The value of the Wait Time parameter is a number of seconds that the system is to wait for a message to become available for a dequeue operation. If the value is NULL, the system will wait indefinitely. If the system receives a DEQUEUE request with a Wait Time value that is not NULL and nonzero, but no message in the queue is ready for processing, the system will wait for a time period equal to the Wait Time value for a message to become available. If Wait Time is specified as zero, then DEQUEUE will fail if no message is immediately available. Wait processing is assisted by use of the QID queue identifier numbers provided in the queue table. When the DEQUEUE process is waiting for a message to become available, it continually checks the queue table for messages having the QID corresponding to the Queue Name. In this way, searching is accelerated. Priority, Delay, Expiration: After dequeuing a message, the system places the priority, delay, and expiration values of the message in these fields. Retry: The Retry parameter holds the number of dequeue operations attempted before the message is processed successfully. The system loads the Retry parameter with a value only if a dequeue operation fails due to a system crash, operation abort by a user, or other fails. Exception Queue: This parameter identifies the name of a an exception queue to which the message is to be directed by the requesting application program if the message cannot be processed successfully. In operation, the dequeue process generally follows the steps shown in FIGS. 9B to 9C. Generally, the search criteria for messages to be dequeued is determined by the Consumer.sub.-- Name, Message Identifier and Correlation attributes of the Dequeue.sub.-- Options parameter. Only messages in the READY state are dequeued, unless a specific Message Identifier is passed to the DEQUEUE operation. The dequeue order is determined by the values specified at the time the queue table is created; they can be overridden by providing values for the Message Identifier and the Correlation attributes of the Dequeue.sub.-- Options parameter. In step 950, the process reads all the parameter values. In step 952, the process evaluates whether the Message Identifier value or the Correlation Identifier value is provided. If a valid Message Identifier exists, then in step 954 the process selects a message from the named queue based upon the Message Identifier. If a valid Correlation Identifier exists, then in step 956 the process selects a message from the named queue based upon the Correlation Identifier. Selection is done with SQL SELECT statements. If neither value is supplied, then in step 958 by default the process selects the next message from the queue according to the order specified in the Queue Table Order table. The process next evaluates the Dequeue Mode value in steps 960 and 966. If its value is D, then the process reads the queue message data in step 962 and then saves the current state in step 964. This enables the process to delete the message later by returning to the same state. If the mode value is L, the process locks the queue table in step 968, reads the message in step 970, and unlocks the table in step 972. If the mode value is B, the process reads the message in step 974. Thereafter, in step 976 the process carries out transaction sensing and transaction-based updating according to steps substantially similar to those shown in FIG. 6A for the ENQUEUE operation. In step 978, the process tests whether the Wait value is specified. If so, then in step 980 the process sets an internal timer variable to the Wait value, and in step 982 commences waiting for an event to occur having the current Queue Identifier. In step 984 the process tests whether the ENQUEUE operation has posted a system message to the current Queue Identifier, which indicates that a newly enqueued message is ready for processing. If not, the process continues to wait. If there is no Wait value, or if an ENQUEUE post arrives, then in step 986 the process carries out sequence deviation processing according to steps substantially similar to those shown in FIG. 4A in connection with the ENQUEUE operation. One of ordinary skill in this art will recognize that identical steps may not be used because the steps of FIG. 4A involve, in part, insertion of messages in a queue. However, substantially similar steps can be used to locate the next message to dequeue. The process next carries out Retention processing in the manner shown in FIG. 8. After a message is dequeued, as shown in FIG. 8, the process reads the Queue Dictionary Table for the current queue and tests in step 750 whether a value in the Retention column has been specified. If the value or column is undefined, then no further processing is done. Otherwise in step 752 the process tests whether the column holds a NULL value. If it is null, then as shown in step 754 messages of that queue are to be retained indefinitely, and no further processing is done. If the value is non-NULL, then in step 756 the process sets the Time Manager Info column of the current queue in the Queue Table to the value of the Retention Time column of the Queue Dictionary Table. In step 758, the process then updates the state of the current message to PROCESSED. In this way, even after a message is dequeued, the message is retained to maintain a history of what has been done, when, and by what process. The Time Manager, upon awakening and finding a message of status PROCESSED in the queue, will delete the message as described in connection with FIG. 7A-7B. However, the Time Manager will not find a PROCESSED message in the queue or the Timetable until its retention time is up. The Time Manager identifies processed messages that exist beyond their retention time by analyzing the time.sub.-- manager.sub.-- info and msg.sub.-- state fields of the messages. The Time Manager will remove these messages from the queue. If Destructive mode is specified in the Dequeue Mode value, as tested in step 990, then in step 992 the process deletes the dequeued message from the current queue. Dequeue processing is then complete as shown by step 994. Structure and Operation for Multiple Message Consumers To provide a mechanism that enables multiple consumer processes to dequeue messages, an index structure is provided. In one embodiment, the index structure comprises an index keyed to the Queue.sub.-- Name and Sort.sub.-- Order columns of the Queue Table. An application that needs to dequeue a message provides a value for the Queue.sub.-- Name, and a value for the Sort.sub.-- Order parameter is obtained from the index. The application name <appname> is also provided as a parameter to the dequeue operation. The application name is encoded into a bit pattern that is stored in a column of the Queue Table called <consumers>. The dequeue operation effectively carries out the following operations, which are expressed in the Structured Query Language (SQL):
______________________________________
select
*
from queue.sub.-- table
where state = READY and name =<queue.sub.-- name> and bit(consumers,
appname)
order by <sort.sub.-- order>
for update skip locked;
______________________________________
In an alternate embodiment, the application name is omitted from the Queue Table as described above, the index structure is a table called Queue.sub.-- Table.sub.-- Index that is keyed to the Queue.sub.-- Name and Sort.sub.-- Order columns of the Queue Table. The index table also has an <appname> column, and a Queue.sub.-- Table.sub.-- Rowid column that maps one or more rows of the index table to one row in the Queue Table. Using this structure, a dequeue operation is accomplished using the following two queries:
______________________________________
select
queue.sub.-- table.sub.-- index.sub.-- rowid, queue.sub.-- table.sub
.-- rowid
from queue.sub.-- table.sub.-- index, queue.sub.-- table
where name = <queue.sub.-- name> and appname = <appname>
and state = READY
order by <sort.sub.-- order>
update skip locked;
select
*
from queue.sub.-- table
where rowid = <queue.sub.-- table.sub.-- rowid>
______________________________________
Thus, the first query yields a rowid for the next ready message in the queue table for the specified queue that has not been previously dequeued by the specified application. The second query retrieves records matching that rowid from the queue table. With this preferred structure, a message enqueue operation is carried out using the following two SQL INSERT statements. The <appname>s of messages in a queue are stored in a catalog table called aq$.sub.-- queues.
______________________________________
insert
into queue.sub.-- table
values
(queue.sub.-- name, msgid, . . ., user.sub.-- data);
insert
into queue.sub.-- table.sub.-- index
select
aq$q.appname, queue.sub.-- name, sort.sub.-- order, queue.sub.--
table.sub.-- rowid
from aq$.sub.-- queue aq$q
where where aq$q.queue.sub.-- name = queue.sub.-- name;
______________________________________
In this embodiment, it is preferable for the Queue Table to maintain a reference count of the applications that have a pointer to a particular message from the index, that is, applications for which a message is within the current view. When a message is dequeued by an application, the corresponding index entry is deleted from the queue table index and the reference count of the message in the queue table is decremented. When the reference count reaches zero, the message is either deleted or archived. This process can be implemented using the following SQL statements:
______________________________________
delete
from queue.sub.-- table.sub.-- index
where rowid = <queue.sub.-- table.sub.-- index.sub.-- rowid>
update
queue.sub.-- table
set reference.sub.-- count = reference.sub.-- count - 1
where rowid = <queue.sub.-- table.sub.-- rowid>
if (reference.sub.-- count = 0)
delete from queue.sub.-- table
where rowid = -<queue table.sub.-- rowid>
______________________________________
In this embodiment, there is a need to ensure that each application that is consuming messages is allowed to dequeue a message exactly once and is never allowed to dequeue a message that was enqueued earlier than a previously dequeued message. The process shown in FIG. 6C accomplishes this requirement. Storage, such as a variable stored in main memory, is provided for each application that dequeues messages to store a value of the highest message sequence number processed by the application. Sequence numbers are obtained from a database server kernel process called a sequence. Sequences provide ever-increasing integer values that can be used as serial numbers and the like in database applications. In step 630, each application stores the highest sequence number of messages processed thus far by the application. When a message enqueue operation is initiated, as shown in step 632, the process obtains a new sequence number from a sequence as shown in step 634. In step 636, the new sequence number is compared to the highest sequence number for the current application. If the new sequence number is greater than the highest sequence number, then in step 640 the new sequence number is stored in association with the message that is being enqueued. When an application dequeues a message, as shown in step 642, the dequeue operation reads the highest sequence number of messages previously processed by the current application, as shown in step 644. Using an appropriate SQL statement or the equivalent, the dequeue operation then selects for dequeuing only messages that have a sequence number greater than the highest sequence number associated with the current application, as shown in step 646. Such selection is enabled by establishing an index on the queue table having the sequence number as principal key. In step 648, the highest sequence number associated with the current application is incremented to a value equal to the highest sequence number of a message that was selected in step 646. As shown in step 650, further processing operations can be carried out, or control can return to step 632 at which another enqueue operation is carried out. Using this process, all applications that consume messages from a queue will receive messages in the same order. The time for an enqueue is not dependent on the number of messages that have been dequeued or the number of applications that have consumed messages. In another preferred embodiment, an application can specify if a queue is enabled for multiple consumers. In one variation of this embodiment, the application specifies this preference when queue tables are created. Alternatively, the dequeue operation has an <appname> parameter that defaults to a NULL value. If no <appname> is specified, the dequeue operation skips the processes described above and selects messages without reference to indexes. In this way, an application can optimize operation when only a single application consumes messages from the queue. Database Application Programming Using Enqueue and Dequeue Operations The following pseudo-code presents an example API that can be used to implement the invention. Preferably, the API calls described below are integrated with the Oracle8 database system.
__________________________________________________________________________
OCIEnqueue(OCISvcCtx
*svch, /* service context(IN) */
OCIError *errh, /* error handle(IN) */
text queue.sub.-- name,
/* target queue for enqueue(IN)
*/
OCIEnqueueOptions *enqueue.sub.-- options, /* options(IN) */
OCIMessageProperties *message.sub.-- properties,/* mesg
properties(IN)
*/
OCIType *payload.sub.-- tdo,
/* TDO of adt(IN) */
dvoid **payload, /* pointer to pointer to instance
of ADT(IN) */
dvoid **payload.sub.-- ind,
/* ptr to ptr to indicator
structure(IN) */
OCIRaw **msgid, /* message id (OUT) */
ub4 mode);
/* not used (IN) */
OCIDequeue(OCISvcCtx
*svch, /* service context(IN) */
OCIError *errh, /* error handle(IN) */
text * queue.sub.-- name,
/* target queue for enqueue(IN) */
OCIDequeueOptions *dequeue.sub.-- options, /* options(IN) */
OCIMessageProperties **message.sub.-- properties,
/* properties(IN/OUT) */
OCIType *payload.sub.-- tdo,
/* TDO of adt(IN) */
dvoid **payload,
/* ptr to pointer to instance of ADT(IN/OUT) */
dvoid **payload.sub.-- ind
/* ptr to ptr to indicator structure(IN) */
OCIRaw **msgid, /* message id (OUT) */
ub4 mode); /* not used (IN) */
__________________________________________________________________________
Preferably, the API includes three function calls that enable an application program to specify message options and message properties. In one embodiment, these function calls are named OCIEnqueueOptions or dbms.sub.-- aq.enqueue.sub.-- options.sub.-- t, OCIMessageProperties or dbms.sub.-- aq.message.sub.-- properties.sub.-- t, and OCIDequeueOptions or dbms.sub.-- aq.dequeue.sub.-- options.sub.-- t. The table below lists the attribute names and attribute types for the three function calls.
__________________________________________________________________________
Handle Attribute Name Attribute Type
__________________________________________________________________________
OCI.sub.-- ENQUEUE.sub.-- OPTIONS
OCI.sub.-- VISIBILITY
ub4(OCI.sub.-- ON.sub.-- COMMIT/OCI.sub.-- IMMEDIATE)
OCI.sub.-- ENQUEUE.sub.-- OPTIONS
OCI.sub.-- RELATIVE.sub.-- MSGID
OCIRaw *
OCI.sub.-- ENQUEUE.sub.-- OPTIONS
OCI.sub.-- SEQUENCE.sub.-- DEVIATION
ub4(OCI.sub.-- BEFORE/OCI.sub.-- TOP)
OCI.sub.-- DEQUEUE.sub.-- OPTIONS
OCI.sub.-- CONSUMER.sub.-- NAME
text *
OCI.sub.-- DEQUEUE.sub.-- OPTIONSOCI.sub.-- DEQUEUE.sub.-- MODE
OCI.sub.-- BROWSE, OCI.sub.-- LOCKED,
OCI.sub.-- REMOVE
OCI.sub.-- DEQUEUE.sub.-- OPTIONS
OCI.sub.-- NAVIGATION
ub4(OCI.sub.-- FIRST.sub.-- MESSAGE,
OCI.sub.-- NEXT.sub.-- MESSAGE,
OCI.sub.-- NEXT.sub.-- TRANSACTION)
OCI.sub.-- DEQUEUE.sub.-- OPTIONS
OCI.sub.-- VISIBILITY
ub4(OCI.sub.-- ON.sub.-- COMMIT/OCI.sub.-- IMMEDIATE)
OCI.sub.-- DEQUEUE.sub.-- OPTIONS
OCI.sub.-- WAIT
ub4(OCI.sub.-- FOREVER/OCI.sub.-- NO.sub.-- WAIT)
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- PRIORITY
ub4
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- DELAY
ub4
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- EXPIRATION
ub4(OCI.sub.-- NEVER)
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- CORRELATION
text *
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- ATTEMPTS
ub4
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- RECIPIENT.sub.-- LIST
OCIColl *
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- EXCEPTION.sub.-- QUEUE
text *
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- ENQUEUE.sub.-- TIME
OCIDate (only OCIAttrGet)
OCI.sub.-- MESSAGE.sub.-- PROPERTIES
OCI.sub.-- STATE
ub4 (only OCIAttrGet)
__________________________________________________________________________
Examples of Enqueue/Dequeue Operations Enqueue Followed by Dequeue Table 3 is an example of SQL statements that may be used to command an embodiment of the invention to enqueue and then dequeue a single message without any other parameters. Only the queue name and the user data need to be specified. The programming language syntax of Table 3 and other tables herein is the syntax of the SQL language as implemented in an RDBMS, such as the Oracle.RTM. 7 RDBMS, and is well known in the art.
TABLE 3
__________________________________________________________________________
Simple Enqueue/Dequeue
__________________________________________________________________________
DECLARE
my.sub.-- msgid number;
my.sub.-- user.sub.-- data long raw := hextoraw (`7`)
BEGIN
ENQUEUE (queue.sub.-- name=>`my.sub.-- queue`,
enqueue.sub.-- options=>my.sub.-- enqueue.sub.-- options, message.sub.--
properties=>
my.sub.-- message.sub.-- properties, payload=>my.sub.-- user.sub.--
data,
message.sub.-- identifier=>my.sub.-- msgid);
DEQUEUE (queue.sub.-- name=>`my.sub.-- queue`,
dequeue.sub.-- options=>my.sub.-- dequeue.sub.-- options,
message.sub.-- properties=>output.sub.-- message.sub.-- properties,
output.sub.-- message.sub.-- identifier=>my.sub.-- msgid,
payload=>my.sub.-- user.sub.-- data);
__________________________________________________________________________
Enqueue and Dequeue by Priority As shown in Table 4, when two messages are enqueued with the same priority, the message that was enqueued earlier will be dequeued first. However, if two messages have different priorities, the message with the higher priority will be dequeued first.
TABLE 4
__________________________________________________________________________
Engueue/Dequeue Showing Effect of Priorities
__________________________________________________________________________
{Application calls ENQUEUE}
DECLARE
my.sub.-- msgid number;
my.sub.-- priority number;
my.sub.-- user.sub.-- data long raw := hextoraw(`7`);
BEGIN
ENQUEUE (queue.sub.-- name=>`my.sub.-- queue`, priority=>2,
payload=>my.sub.-- user.sub.-- data, message.sub.-- identifier=>
my.sub.-- msgid);
{Upon completing the ENQUEUE, my.sub.-- msgid=23}
{Application calls ENQUEUE again}
ENQUEUE (queue.sub.-- name=>`my.sub.-- queue`, priority=>5,
payload=>my.sub.-- user.sub.-- data, message.sub.-- identifier=>
my.sub.-- msgid);
{Upon completing the ENQUEUE, my.sub.-- msgid=24}
DEQUEUE (request.sub.-- schema=>`my.sub.-- queue`, priority=my.sub.--
priority,
payload=>my.sub.-- user.sub.-- data);
END;
{Upon completing the DEQUEUE, message 24 will be retrieved before
message 23 since priority 5 is superior to priority 2, even
though the
message identifier of message 23 is ordered in FIFO order
earlier than
message 24}
__________________________________________________________________________
Non-destructive Dequeue Followed by Normal Dequeue An application can browse (or "preview") a message without deleting the message from a queue, lock the message in the queue and retrieve the message identifier by using the steps described in Table 5:
TABLE 5
______________________________________
Non-destructive Dequeue Followed bv Normal Dequeue
______________________________________
DECLARE
lock.sub.-- msgid number;
my.sub.-- user.sub.-- data long raw := hextoraw(`7`);
DEQUEUE (queue=>`my.sub.-- queue`, dequeue.sub.-- mode=>`L`,
message.sub.-- identifier=>lock.sub.-- msgid, payload=>my.sub.-- user.sub.
-- data);
{a moment later, the application can remove the locked message from the
queue as follows}
DEQUEUE (queue.sub.-- name=>`my.sub.-- queue`, message.sub.-- identifier=>
lock.sub.-- msgid,
payload=>my.sub.-- user.sub.-- data);
______________________________________
Enqueue Followed by Scheduled Dequeue Applications can enqueue a message with a time window that restricts retrieval of the message to particular start and stop time periods. For example, if an application wants a message to be dequeued no earlier than one (1) week from now, but no later than three (3) weeks from now (which is two weeks from the earliest dequeue data), the message is enqueued with the following request:
______________________________________
DEQUEUE(queue.sub.-- name=>`my.sub.-- queue`, delay=>system.sub.--
date+7,
expiration=>2*7*24*60*60, payload=>`my.sub.-- data`);
______________________________________
Using Sequence Deviation To Define Dequeue Order At ENQUEUE time, the Relative Message Identifier and Sequence Deviation parameters can be used to specify the order in which messages are to be dequeued. This is advantageous when several messages have the same Priority value but an application wishes to specify which message should dequeue first. In the absence of sequence deviation, the earliest queued message (i.e., the message with the lowest Message Identifier) would dequeue first. For example, if an application is to enqueue three messages, and the messages are to be dequeued in the order (2, 1, 3), the process shown in Table 6 is used to enqueue the messages.
TABLE 6
______________________________________
Sequence Deviation
______________________________________
DECLARE
my.sub.-- msgid
number;
my.sub.-- msgid1
number;
my.sub.-- msgid2
number;
dev.sub.-- data
long raw := hextoraw(`7`);
{enqueue the first message}
ENQUEUE (queue.sub.-- name=>`dev.sub.-- queue`,
enqueue.sub.-- options=>my.sub.-- enqueue.sub.-- options,
message.sub.-- properties=>my.sub.-- message.sub.-- properties,
payload=>dev.sub.-- data,
message.sub.-- identifier=>my.sub.-- msgid);
{insert the second message, specifying that it should be enqueued ahead
of
message 1 }
ENQUEUE (queue.sub.-- name=>`dev.sub.-- queue`,
enqueue.sub.-- options=>my.sub.-- enqueue.sub.-- options,
message.sub.-- properties=>my.sub.-- message.sub.-- properties,
payload=>dev.sub.-- data,
relative.sub.-- message.sub.-- identifier=>my.sub.-- msgid,
sequence.sub.-- deviation=>`B`,
message.sub.-- identifier=>my.sub.-- msgid 1);
{insert the third message}
ENQUEUE (queue.sub.-- name=>`dev.sub.-- queue`,
enqueue.sub.-- options=>my.sub.-- enqueue.sub.-- options,
message.sub.-- properties=>my.sub.-- message.sub.-- properties,
payload=>dev.sub.-- data,
message.sub.-- identifier=>my.sub.-- msgid2);
______________________________________
Normally, a newly enqueued message is added to the end of the queue and is processed after previously enqueued messages having equivalent Priority. Sequence Deviation is a process of enqueuing a message in an order different from a normal (FIFO) ordering sequence. In one embodiment, a set of related messages are organized as a Chain. Messages in a chain are related by the Relative Message Identifier that identifies the next message in the chain. Each Chain has a Chain Number that is stored in the Queue Table. Each Chain has a Local Order that is the order in which messages in the Chain are processed. A higher Local Order number indicates that a message should be processed earlier. The first message in a Chain is the Head of the Chain. The Delay value of all messages in a Chain must be in descending order starting from the Head. Further, Sequence Deviation will succeed only if a requesting process observes certain restrictions on the values of the Delay, Expiration, and Priority parameters. For a message with Sequence Deviation, the Delay value must be less than or equal to the Delay value of another message before which the current message is to be inserted. The Priority value of the current message must be greater than the Priority value of the other message. Thus, for a message M1 to be inserted in front of a message M that is currently in a queue, the following conditions must be true: Delay(M1)<=Delay(M) AND Priority(M1)>Priority(M) The Sequence Deviation process within the ENQUEUE process operates in three possible ways depending on the value of the Sequence Deviation parameter in an ENQUEUE request. As shown in FIG. 4A, in step 400 the process tests whether the Sequence Deviation value is BEFORE, TOP, or NULL, and branches to one of three sub-processes accordingly. If the value is NULL, then no sequence deviation occurs; the message will be processed after any other queue message with the same priority has been processed. In step 402, the Chain Number value of the new message is set to null, because the message does not belong to a Chain, and the Local Order Number is set to zero. At step 404 the message is inserted into the queue, and the process is complete at step 406. If the value is BEFORE then the new message is enqueued before or ahead of the queue message specified by the value of the Relative Message ID parameter (called the "referenced message" herein). In step 410 the process reads the Relative Message Identifier of the message passed in the ENQUEUE request, and reads the values in the Queue Table for the referenced message, including the Chain Number and Order Number of the referenced message. As shown in step 412, if the Chain Number of the referenced message is NULL (i.e., that message is not part of a Chain), then in step 414 the process obtains the next available chain number. Next, in step 416 the process assigns that value to the Chain Number column of the referenced message and the new message. In step 418, the process then selects messages matching that Chain Number, and having an Order Number higher than the Order Number of the referenced message, from the Queue Table using an SQL statement. In step 420 the process tests whether messages were retrieved by the SWL query. If not, then in step 422 the Order Number assigned to the new message is calculated by adding the Order Number of the referenced message to a Maximum Order Number and dividing by 2. If the select query returns a set of messages, then as shown in step 424 the lowest Order Number is chosen. Then in step 426 the Order Number assigned to the new message is calculated by adding that lowest Order Number to the Order Number of the message already in the queue and dividing by 2. During this process, the chain is locked. The BEFORE Sequence Deviation process is then complete and the ENQUEUE process resumes. If the value is TOP, then the message is enqueued ahead of any other message of the same queue. No Relative Message Identifier needs to be specified. In step 430, the process reads the Queue Table Sort table values to determine before which message to insert the new message. If no such values are defined for the current queue, e.g., if the table query fails, then in step 434 the Enqueue Time values of queue messages are read from the queue table, and the earliest enqueue time is located. The message with the earliest enqueue time becomes the referenced message. In step 436 the Order Number of the current message is set to zero and the Chain Number is set to NULL. As shown in FIG. 4B, a queue table 220 can contain a queue 222 comprising a plurality of messages 224a, 224b, 224c, 224d, 224e. Each of the messages 224a-224e includes a Delay parameter value 234a-234e and a Priority value 236a-236e. The messages 224a-224e are ordered in the queue such that the message 224a with the highest Priority value (100) and the lowest Delay value (0) is at the top. Successive messages have increasingly higher Delay values and increasingly lower Priority values. Each message can have other parameters 250a-250e. The system receives an ENQUEUE request for another message 238 with the Sequence Deviation parameter set to BEFORE and the Relative Message Identifier set to Message 5 (224e). The Delay value 234f of message 238 is 10 and its Priority value 236f is 80. The ENQUEUE request will succeed because Delay value 234f is less than Delay value 234e and Priority value 236f is greater than Priority value 236e. Time Manager Process The queuing system further includes a Time Manager process which uses the Delay and Expiration parameter values of an ENQUEUE request to time message delays and notify the DEQUEUE process that a delay is complete. Other functions of the Time Manager process include deleting expired messages from the Queue Table 200 and moving expired messages to an Exception Queue 206. In one embodiment, the Time Manager process communicates with the Timetable database table 216, and is configured as a background process running on the same computer, server, machine or process that is running the queuing system. The Time Manager process uses existing inter-process communications facilities in the database server 310 kernel to communicate with other processes such as ENQUEUE. When the queuing system is started, the Time Manager process is initialized by creating the Timetable 216, creating an index for the Timetable 216 based upon its Time column, and entering a row identifying the Timetable 216 in the Queue Table 200. Preferably initialization of the Time Manager process is triggered by placing a parameter aq.sub.-- tm.sub.-- processes in an initialization parameter file of the database system with which the queue system is integrated. After initialization, the time manager process can be started and stopped using procedures called START.sub.-- TIME.sub.-- MANAGER and STOP.sub.-- TIME.sub.-- MANAGER. As described herein, the ENQUEUE process writes rows into the Timetable 216 when the Delay or Expiration parameters of an enqueued message 208 contain values. FIG. 6B, is a flow diagram of Delay and Expiration processing. In step 620, a determination is made as to whether the Delay and Expiration parameters of an enqueued message 208 have values that are greater than zero. If the Delay and Expiration parameters have values that are greater than zero, then at step 622 the ENQUEUE process inserts the referenced message into the Timetable 216. At step 624, it is determined if the value of the SGA variable in the ENQUEUE request is greater than the values of the Delay and Expiration parameters of the enqueued message 208. If the value of the SGA variable in the ENQUEUE request is greater than the values of the Delay and Expiration parameters, then at step 626, the ENQUEUE process posts the time manager, when the Time Manager process is started, in step 700 it reads rows in the Timetable 216 and in step 702 tests whether the Timetable is empty. If so, then control returns to step 700 and the Time Manager essentially goes to sleep until ENQUEUE writes a row into the Timetable. If a Timetable row is found, in step 704 the Time Manager reads all entries in the Timetable to locate the message row with the least Time value. In step 706, the Time Manager sets an internal timer or alarm variable to such least Time value. In step 708, the Time Manager tests whether the alarm value is equal to the system clock time, i.e., whether the Time Manager should wake up and start substantive processing. If the Time Manager should wake up, control is passed to step 710. Step 710 is also reached when the process is awakened from an external signal, e.g., a message posted by the ENQUEUE process. Therefore, in step 710 the process tests whether the alarm went off or whether the process has been externally posted. If the alarm went off, then in step 712 of FIG. 7B the process locates a row in the Timetable having a Time value equal to the alarm time. In step 714 the process tests the Exp.sub.-- Or.sub.-- Del value to determine whether the Time is an Expiration or Delay value. If it is a Delay value, then the process must next determine whether the message is unexpired, i.e., whether the Expiration value is not NULL, in step 716. If there is an Expiration value, in step 718 the value is copied into the Time column of the Timetable. In step 720 the process checks whether another message in the Timetable has the same alarm time, and if so, control is passed to step 714 to repeat the process for such other messages. If the Expiration value is NULL at step 716, then at step 722 the process deletes the message from the Timetable, and then notifies the DEQUEUE process that the formerly delayed message is ready for processing. If the alarm time is an Expiration time, then the test of step 714 will be negative and control is passed to step 726 in which the process reads the message and all its values from the Timetable. In step 728 the process checks whether the message is still in the original queue of the Queue Table, i.e., whether the message has been dequeued and processed. If the message is no longer in the queue, control is passed to step 720 to check for other messages. If the message is in the queue, then in step 730 the process determines the name of an exception queue to use, either the default exception queue or the name in the Exception Queue column of the message. In step 732 the process moves a copy of the message to the designated Exception Queue, and then deletes the message from the Timetable (step 734). If the Time Manager awoke due to posting by ENQUEUE, as shown in step 740 of the FIG. 7C, then the Time Manager will reset its alarm time to Time value of the enqueued message, or the minimum time it has processed at that point. This is done by reading the value of the SGA variable in the ENQUEUE request, if there is one (steps 742, 746). The SGA variable stores the minimum absolute time processed by the Time Manager so far. Alternatively, the Time value is selected by choosing a message with a minimum time value from the Timetable and using its Time value. In this way, the Time Manager resets its alarm so that the alarm is never later than the Delay or Expiration value of a just-enqueued message. Without these steps, the Time Manager alarm could go off after the Time value of the just-enqueued message, causing the system to miss an event. Configuration and Administration of Queues Security and Access Privileges The queuing system includes computer program segments, which can cause a computer to carry out steps to enable configuration, management, and administration of queues. Incorrect usage of administration processes can adversely affect database system performance; therefore, in one embodiment, the configuration and administration processes of the system are accessible only to human users having the system administrator privilege. In another embodiment, the system is provided with means to define a queue administrator role and a queue user role for queue administrators and queue users, respectively. A user, process or application assigned the queue administrator role is permitted to access the queue administration processes and the queue use processes described herein. A user, process or application assigned the queue user role is permitted to access the queue use processes described herein. The system includes access definitions for these roles in a manner consistent with the access control mechanism used by the relational database system, and that access control mechanism (which is well known) carries out the steps of granting and revoking access privileges to particular users according to the role definitions. For example, when the queue system is configured for use with an Oracle.RTM. 7 RDBMS, the <grant> and <revoke> commands can be used by the system administrator to assign such roles and security privileges to specific users or user accounts. In another embodiment, the queuing system is integrated with the Oracle8 database system. The queuing system is implemented as software packages called DBMS.sub.-- AQADM and DBMS.sub.-- AQ. The packages are owned by the system user, SYS, and they operate under SYS's security domain. Accordingly, a user who has been granted the security privilege called AQ.sub.-- ADMINISTRATOR.sub.-- ROLE can create, drop, start and stop queues. Similarly, a user who has been granted AQ.sub.-- USER.sub.-- ROLE role can enqueue and dequeue to any queues. If additional security is required, an application program can provide a wrapper on top of DBMS.sub.-- AQADM and DBMS.sub.-- AQ that will limit the application users to access or manage a specific set of queues. Queue Table Creation The system includes a CREATE.sub.-- QTABLE process or computer program element that comprises computer program steps that carry out the following processes with effects on the system and its components as described below. The CREATE.sub.-- QTABLE process accepts the parameters shown in Table 7:
TABLE 7
______________________________________
CREATE QTABLE Parameters
______________________________________
Queue Table
Queue Payload Type
Storage Clause
Sort List
Multiple.sub.-- Consumers
Message.sub.-- Grouping
User Comment
Auto.sub.-- Commit
______________________________________
When called from an SQL statement by an application program, CREATE.sub.-- QTABLE creates a table in the relational database system to keep messages of queues, and a read-only RDBMS view usable by application programs for querying queue data. Parameters of the CREATE.sub.-- QTABLE process operate as follows: Sort List: The CREATE.sub.-- QTABLE process is configured to accept a default queue order in the Sort List parameter. The Sort List parameter preferably is a character string containing a list of <sort.sub.-- column, sort.sub.-- order> pairs, each pair separated by commas from the next pair. Each <sort.sub.-- column, sort.sub.-- order> pair identifies a Queue Table 200 column used as a sort key by the DEQUEUE process. For example, if a Sort List value contains a list including the pair <sort.sub.-- column.sub.-- n, sort.sub.-- order.sub.-- n>, the pair identifies the column in the Queue Table that will be used as the nth sort key when the rows are ordered for dequeuing. The sort.sub.-- order.sub.-- n element of the pair specifies the order in which the column sort.sub.-- column.sub.-- n should be sorted. FIFO order is used by the DEQUEUE process by default. After a queue table is created using CREATE.sub.-- QTABLE, the CREATE.sub.-- QTABLE process assigns the default sort order of the queue table to all queues in the table. Once the sort order is created, it cannot be changed. However, an application program can retrieve messages in a different order using the DEQUEUE process and by providing values for the Message Identifier or Correlation Identifier parameters. Queue Table: specifies the name of the new queue table to be created. Queue Payload Type: this parameter specifies the abstract data type of the user data that will be stored in the queue. If the Queue Payload Type parameter is not specified or has a NULL value, a queue of variant data type will be created. If the Queue Payload Type parameter is specified and has a non-NULL value, the abstract data type specified must exist when CREATE.sub.-- QTABLE is called or requested. Storage.sub.-- Clause: the value of this parameter specifies the storage format for the new queue table. The value of this parameter enables, for example, a large binary object such as a graphic image to be stored in a location apart from the queue, yet still referenced within a queue. In this way, queue operations are not burdened or slowed by manipulating large binary files or objects. User Comment: the value of this parameter is a user-specified description of the queue table; it is added to the queue catalog in the RDBMS. Multiple.sub.-- Consumers: The value of this parameter specifies the message grouping behavior for queues created in the table. When Multiple.sub.-- Consumers is set to NONE, each message is treated individually. When Multiple.sub.-- Consumers is set to TRANSACTIONAL, messages enqueued as part of one transaction are considered part of the same group and must be dequeued as a group of related messages. The Auto.sub.-- Commit parameter stores a Boolean value. When Auto.sub.-- Commit is TRUE, the DEQUEUE process causes the current transaction, if any, to commit before the operation is carried out. The operation becomes persistent when the call returns. TRUE is the default value. When Auto.sub.-- Commit is FALSE, the dequeue operation is assumed to be part of the current transaction, so that removal of a message from the queue persists only when the calling process or program issues a transaction commit instruction. In operation, when the CREATE.sub.-- QTABLE process is invoked for a named queue table, the process checks whether the queue table exists in the Queue Table Dictionary Table. If the queue table does not exist in the dictionary table, the process also checks whether the named queue table exists in the system anyway. If not, a new queue table is created. In either case, the dictionary table is updated to reflect the new queue table or the existing queue table found in the system. These steps are carried out to account for the possibility that a queue table could be created but not entered into the dictionary table due to a system failure. Information about the queue ordering parameters is inserted into the Queue Table Sort table. The Sort List value is parsed, and each Sort Column found therein is inserted as a row into the Queue Table Sort table. An index is created on the sort order columns. A view is created that allows a user to query the message data. The queue table view also provides the dequeue history for single consumer queue messages. To query the list of recipients or the dequeue history of a message in a multiple-consumer queue, a calling application program or process may query the queue table for the message of interest using an SQL query. The CREATE SEQUENCE SQL statement is used to generate a message identifier for messages in the queues of the queue table. Last, the default exception queue is created. Queue Table Deletion The system includes a DROP.sub.-- QTABLE process or computer program element that comprises computer program steps that carry out the following processes with effects on the system and its components as described below. The function of the DROP.sub.-- QTABLE process is to delete or drop entire queue tables, and all queues within them, from storage in the system. The DROP.sub.-- QTABLE process accepts three parameters: the Queue Table name to drop; a Force parameter, and an Auto.sub.-- Commit parameter. The Force parameter accepts a Boolean value. If the Force value is TRUE, then all queues in the named queue table are stopped and dropped by deleting their tables and reclaiming memory used for their storage. If the Force value is FALSE, then the system will not stop queues in the queue table. An administrator must separately stop them before they can be dropped. The Auto.sub.-- Commit parameter accepts a Boolean value; a TRUE value specifies that the current transaction, if any, must commit before the drop operation is carried out. When Auto.sub.-- Commit is FALSE, then the drop operation is part of the current transaction and will become persistent only when the calling process or program issues a commit instruction. Queue Creation The system includes a CREATE.sub.-- Q process or computer program element that comprises computer program steps that carry out the following processes with effects on the system and its components as described below. The function of the CREATE.sub.-- Q process is to create a specific, uniquely named queue in the Queue Dictionary Table and in storage in the system. The CREATE.sub.-- Q process operates using the parameters shown in Table 8:
TABLE 8
______________________________________
CREATE Q Process Parameters
______________________________________
Queue Name
Queue Table
Queue Type
Maximum Retries
Retry Delay
Retention Time
Auto.sub.-- Commit
User Comment
______________________________________
Placing values in these parameters and calling the CREATE.sub.-- Q process causes the CREAT | ||||||