-- ************************************************************
-- *** Feel free to use this SQL-code, but please           ***
-- *** do not remove these copyright lines.                 ***
-- *** (C) Roger Felix - Lund - Sweden                      ***
-- *** [roger_felix@hotmail.com, web_master@work.utfors.se] ***
-- *** URL: http://hem.fyristorg.com/publish/index.html     ***
-- ************************************************************

THIS FILE DESCRIBES A WAY TO IMPLEMENT AN NEW ABSTRACT DATATYPE
(A QUEUE) BY THE USE OF ORDINARY ORACLE TABLES, INCLUDING 
RESTRICTED SECTION OPERATIONS ON THE QUEUE SUCH AS SYNCHRONIZED 
PUSHING AND POPPING OF ENTRIES TO AND FROM THE QUEUE.

The queue will be refered as "queue1", and is implemented by creating
a table "queue1", a table "queue1_comm", a sequence "queue1_seqno",
a trigger "queue1_updtrig" and procedures "push" and "pop".


CONTENTS
--------

  1. Create a table to hold the queue.
  2. Create a sequence to keep track of ordering in the queue.
  3. Create a triggering table to communicate with the queue.
     3.1 Insert a line in the above triggering table.
     3.2 Create an update trigger on the above table.
  4. Create functions that update the communication table.
  5. Create the actual RESTRICTED SECTION trigger.

__________________________________________________________________

  1. Create a table to hold the queue.
  ------------------------------------

  This table should, in addition to columnns for the actual 
  queue data, have a column for the queue sequence number.

  create table queue1 (
      SEQNO  number(16,0),
      DATA1  varchar2(24),
      DATA2  number(15,6));

__________________________________________________________________

  2. Create a sequence to keep track of ordering in the queue.
  ------------------------------------------------------------

  This sequence number is used to order data in the queue.

__________________________________________________________________

  3. Create a triggering table to communicate with the queue.
  ------------------------------------------------------------
 
  This table should have one column of datatype varchar2(1) for 
  each operation on the queue, and also include one column for 
  every argument passed to and from the queue. These argument
  columns may be reused for different queue operations.

  EXAMPLE:

  create table queue1_comm (
      POP    varchar2(1),
      PUSH   varchar2(1),
      DATA1  varchar2(24),
      DATA2  number(15,6));
 

  3.1 Insert a line in the above triggering table.
  ------------------------------------------------

  This is just an insert of an empty line. The communications table
  handles and synchronizes calls to the queue operations by updating
  this single row.

  insert into queue1_comm (pop) values ('-');

           
  3.2 Create an update trigger on the above table.
  ------------------------------------------------

  This trigger differs between the queue operations by investigating
  the transitions (eg '+' -> '-' or vice versa) on the operation
  specific columns op the communications table.
__________________________________________________________________

  4. Create functions that update the communication table.
  --------------------------------------------------------

  create or replace 
       procedure push(
          data1_IN in varchar2, 
          data2_IN in number) as

  BEGIN
    /* THIS IS THE ACTUAL QUEUE OPERATION CALL */
    update queue1_comm set 
         PUSH = '-',
         DATA1 = DATA1_IN,
         DATA2 = DATA2_IN;
   
    /* NOW THE COMMUNICATION RECORD IS LOCKED PREVENTING
    OTHER SESSIONS FROM UPDATING UNTIL NEXT COMMIT */

    /* RESET THE COMMUNICATIONS TABLE */
    update queue1_comm set 
         PUSH = '+';
  END;
  /


  create or replace 
       procedure pop(
          data1_OUT in out varchar2, 
          data2_OUT in out number) as
  BEGIN

    /* THIS IS THE ACTUAL QUEUE OPERATION CALL */
    update queue1_comm set 
         POP = '-';

    /* FETCH THE DATA GENERATED BY THE OPERATION */
    select data21, data2 
       into data1_out, data2_out 
          from queue1_comm;
   
    /* NOW THE COMMUNICATION RECORD IS LOCKED PREVENTING
    OTHER SESSIONS FROM UPDATING UNTIL NEXT COMMIT */

    /* RESET THE COMMUNICATIONS TABLE */
    update queue1_comm set 
         POP = '+';
  END;
  /

__________________________________________________________________


  5. Create the actual RESTRICTED SECTION trigger.
  ------------------------------------------------

CREATE OR REPLACE TRIGGER QUEUE1_UPDTRIG BEFORE UPDATE
  ON QUEUE1_COMM
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW 

DECLARE
  seqno   queue1.seqno%TYPE;
BEGIN
   - ***********************************************
   - *** PUSH OPERATION RESTRICTED SECTION       ***
   - ***********************************************
   if :new.push <> old.push THEN 
      /* CHECK FOR NEGATIVE TRANSITION */
      if :new.push = '-' then
            /* GET NEXT SEQNO */
            select queue1_seqno%NEXTVAL into seqno;
            /* PUSH DATA INTO QUEUE */
            insert into queue1 ( 
                 SEQNO,
                 DATA1,
                 DATA2)
             values (
                 SEQNO,
                 :NEW.DATA1, 
                 :NEW.DATA2);
      end if;
   end if;
   - ***********************************************
   - *** POP OPERATION RESTRICTED SECTION        ***
   - ***********************************************
   if :new.pop <> old.pop THEN 
      if :new.pop = '-' then
            -- ********************************************
            -- *** RETRIEVE DATA ON NEGATIVE TRANSITION ***
            -- ********************************************
            /* GET SMALLEST SEQNO */
            select min(seqno)  into seqno from queue1;
            /* RERTRIEVE DATA FROM QUEUE */
            select DATA1, DATA2
                 into :new.data1, :new.data2 
            from queue1 where
                 seqno = seqno; 
       else
            -- *****************************************************
            -- *** ERASE THIS QUEUE ENTRY ON POSITIVE TRANSITION ***
            -- *****************************************************
            /* GET SMALLEST SEQNO */
            select min(seqno)  into seqno from queue1;
            /* ERASE ENTRY HERE */
            delete from queue1 where
                 seqno = seqno; 
      end if;
   end if;
END;
/