Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version

Persisted Permutations

, , , 4 May 2004
An article on how to persist permutations of items in relational databases.
sourcedemo.zip
CLEAR;
SET feedback off
SET define off
set serveroutput on
SPOOL CreateExample.log
DROP TABLE detailtable_disp_total CASCADE CONSTRAINTS;
DROP TABLE detailtable_disp_partial CASCADE CONSTRAINTS;
DROP TABLE detailtable_consecutive CASCADE CONSTRAINTS;
DROP TABLE mastertable CASCADE CONSTRAINTS;
DROP TABLE detailtable_perm CASCADE CONSTRAINTS;
DROP TABLE detailtable_perm_raw CASCADE CONSTRAINTS;
DROP TABLE mastertable_perm CASCADE CONSTRAINTS;
DROP TABLE mastertable_perm_raw CASCADE CONSTRAINTS;
--------------------------------------------------------------------------------
PROMPT Creating table MASTERTABLE
CREATE TABLE mastertable
(
  itemgroup RAW(16) NOT NULL
);

ALTER TABLE mastertable
  ADD CONSTRAINT pk_mastertable PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_CONSECUTIVE
CREATE TABLE detailtable_consecutive
(
  RANK         NUMBER(38) NOT NULL,
  rankminusone NUMBER(38),
  item         RAW(16) NOT NULL,
  itemgroup    RAW(16)
);

ALTER TABLE detailtable_consecutive
  ADD CONSTRAINT pk_detailtable_consecutive PRIMARY KEY (item);

ALTER TABLE detailtable_consecutive
  ADD CONSTRAINT ak_detailtable_consecutive UNIQUE (RANK,itemgroup)
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE detailtable_consecutive
  ADD CONSTRAINT fk_det_cons_ref_master FOREIGN KEY (itemgroup)
  REFERENCES mastertable (itemgroup);


ALTER TABLE detailtable_consecutive
  ADD CONSTRAINT fk_cons_ref_cons FOREIGN KEY (rankminusone,itemgroup)
  REFERENCES detailtable_consecutive (RANK,itemgroup)
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE detailtable_consecutive
  ADD CONSTRAINT chk_detailtable_consecutive CHECK ((RANK =1 AND rankminusone IS NULL)OR RANK>1 AND rankminusone=RANK-1)   DEFERRABLE INITIALLY DEFERRED;
--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_DISP_PARTIAL
CREATE TABLE detailtable_disp_partial
(
  RANK      NUMBER(38) NOT NULL,
  item      RAW(16) NOT NULL,
  itemgroup RAW(16)
);
ALTER TABLE detailtable_disp_partial
  ADD CONSTRAINT pk_detailtable_disp_partial PRIMARY KEY (item);

ALTER TABLE detailtable_disp_partial
  ADD CONSTRAINT fk_det_dsp_part_ref_master  FOREIGN KEY (itemgroup)
  REFERENCES mastertable (itemgroup);

ALTER TABLE detailtable_disp_partial
  ADD CONSTRAINT  chk_detailtable_disp_partial CHECK (RANK > 0);

--------------------------------------------------------------------------------
PROMPT Creating table DETAILTABLE_DISP_TOTAL
CREATE TABLE detailtable_disp_total
(
  RANK      NUMBER(38) NOT NULL,
  item      RAW(16) NOT NULL,
  itemgroup RAW(16)
);

ALTER TABLE detailtable_disp_total
  ADD CONSTRAINT pk_detailtable_disp_total  PRIMARY KEY (item);

ALTER TABLE detailtable_disp_total
  ADD CONSTRAINT ak_detailtable_disp_total UNIQUE (RANK,itemgroup) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE detailtable_disp_total
  ADD CONSTRAINT fk_disp_tot_ref_master  FOREIGN KEY (itemgroup)
  REFERENCES mastertable (itemgroup);
ALTER TABLE detailtable_disp_total
  ADD CONSTRAINT  chk_detailtable_disp_total CHECK (RANK > 0);
--------------------------------------------------------------------------------
PROMPT Creating table MASTERTABLE_PERM
CREATE TABLE mastertable_perm
(
  itemgroup        RAW(16) NOT NULL,
  grouppermutation NUMBER(38)
);
ALTER TABLE mastertable_perm
  ADD CONSTRAINT pk_mastertable_perm PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------  
PROMPT Creating table DETAILTABLE_PERM
CREATE TABLE detailtable_perm
(
  item      RAW(16) NOT NULL,
  itemgroup RAW(16) NOT NULL
);
ALTER TABLE detailtable_perm
  ADD CONSTRAINT pk_detailtable_perm PRIMARY KEY (item);
ALTER TABLE detailtable_perm
  ADD CONSTRAINT ak_detailtable_perm UNIQUE (item,itemgroup);
ALTER TABLE detailtable_perm
  ADD CONSTRAINT fk_detperm_ref_masterper FOREIGN KEY (itemgroup)
  REFERENCES mastertable_perm (itemgroup);

--------------------------------------------------------------------------------  
PROMPT Creating table MASTERTABLE_PERM_RAW
CREATE TABLE mastertable_perm_raw
(
  itemgroup        RAW(16) NOT NULL,
  grouppermutation RAW(2000)
);
ALTER TABLE mastertable_perm_raw
  ADD CONSTRAINT pk_mastertable_perm_raw PRIMARY KEY (itemgroup);
--------------------------------------------------------------------------------  
PROMPT Creating table DETAILTABLE_PERM_RAW
CREATE TABLE detailtable_perm_raw
(
  item      RAW(16) NOT NULL,
  itemgroup RAW(16) NOT NULL
);
ALTER TABLE detailtable_perm_raw
  ADD CONSTRAINT pk_detailtable_perm_raw PRIMARY KEY (item);
ALTER TABLE detailtable_perm_raw
  ADD CONSTRAINT ak_detailtable_perm_raw UNIQUE (item,itemgroup);
ALTER TABLE detailtable_perm_raw
  ADD CONSTRAINT fk_detperm_ref_masterper_raw FOREIGN KEY (itemgroup)
  REFERENCES mastertable_perm_raw (itemgroup);
PROMPT Creating packages and spec
PROMPT Please note that maxint is defined in package spec common
-------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------- 
prompt Creating package COMMON
CREATE OR REPLACE PACKAGE COMMON
IS
   maxint   CONSTANT NUMBER (38, 0) := 99999999999999999999999999999999999999;
   --10^39-1
END;                                   
/

prompt Creating package CONSECUTIVE
CREATE OR REPLACE PACKAGE CONSECUTIVE
IS
END;
/
prompt Creating package PARTIALORDER
CREATE OR REPLACE PACKAGE PARTIALORDER
IS
   PROCEDURE redistranksglobally (itemgroupguid IN RAW);

   PROCEDURE additemtogroup (
      itemguid          IN   RAW,
      itemgroupguid     IN   RAW,
      predecessorrank   IN   INTEGER,
      allowsamerank     IN   BOOLEAN
   );
END;
/

prompt Creating type RAWTABLE
create or replace type RawTable as varray(32000) of  raw(16)
/

prompt Creating package PERMUTATIONS
CREATE OR REPLACE PACKAGE PERMUTATIONS
IS
--------------------------------------------------------------------------------
   FUNCTION arrangecategoryitems (initems IN rawtable)
      RETURN NUMBER;

--------------------------------------------------------------------------------
   FUNCTION reversearrangecategoryitems (
      permutation   IN   NUMBER,
      items         IN   rawtable
   )
      RETURN rawtable;

--------------------------------------------------------------------------------
   PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT NUMBER);

--------------------------------------------------------------------------------
   PROCEDURE reversearrangecategoryitems (
      permutation   IN              NUMBER,
      items         IN OUT NOCOPY   rawtable
   );

--------------------------------------------------------------------------------
   PROCEDURE addnewitemingroup (
      itemgroupguid     IN   RAW,
      newitemguid       IN   RAW,
      itempredecessor   IN   RAW
   );

--------------------------------------------------------------------------------
   PROCEDURE updatepermutationforgroup (
      itemgroupguid   IN   RAW,
      itemguids       IN   rawtable
   );

--------------------------------------------------------------------------------
   FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
      RETURN rawtable;
--------------------------------------------------------------------------------
END;
/

prompt Creating package PERMUTATIONSWITHRAW
CREATE OR REPLACE PACKAGE PERMUTATIONSWITHRAW
IS
--------------------------------------------------------------------------------
   FUNCTION arrangecategoryitems (initems IN rawtable)
      RETURN RAW;

--------------------------------------------------------------------------------
   PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT RAW);

--------------------------------------------------------------------------------
   FUNCTION reversearrangecategoryitems (permutation IN RAW, items IN rawtable)
      RETURN rawtable;

--------------------------------------------------------------------------------
   PROCEDURE reversearrangecategoryitems (
      permutation   IN              RAW,
      items         IN OUT NOCOPY   rawtable
   );

--------------------------------------------------------------------------------
   PROCEDURE addnewitemingroup (
      itemgroupguid     IN   RAW,
      newitemguid       IN   RAW,
      itempredecessor   IN   RAW
   );

--------------------------------------------------------------------------------
   PROCEDURE updatepermutationforgroup (
      itemgroupguid   IN   RAW,
      itemguids       IN   rawtable
   );

--------------------------------------------------------------------------------
   FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
      RETURN rawtable;
--------------------------------------------------------------------------------
END;
/

prompt Creating package TOTALORDER
CREATE OR REPLACE PACKAGE TOTALORDER
IS
   PROCEDURE additemtogroup (
      itemguid          IN   RAW,
      itemgroupguid     IN   RAW,
      predecessorrank   IN   NUMBER
   );

   PROCEDURE redistranksglobally (itemgroupguid IN RAW);
END;
/

prompt Creating package body COMMON
CREATE OR REPLACE PACKAGE BODY COMMON
IS
END;
/

prompt Creating package body CONSECUTIVE
CREATE OR REPLACE PACKAGE BODY CONSECUTIVE
IS
--------------------------------------------------------------------------------
   PROCEDURE insertnewrank (
      itemgroupguid   IN   RAW,
      newrank         IN   NUMBER,
      newitem         IN   RAW
   )
   IS
   BEGIN
      UPDATE detailtable_consecutive
         SET detailtable_consecutive.RANK = RANK + 1,
             detailtable_consecutive.rankminusone = RANK
       WHERE detailtable_consecutive.RANK >= newrank
         AND detailtable_consecutive.itemgroup = itemgroupguid;

      INSERT INTO detailtable_consecutive
                  (RANK, rankminusone, item,
                   itemgroup
                  )
           VALUES (newrank, DECODE (newrank, 1, NULL, newrank - 1), newitem,
                   itemgroupguid
                  );
   END;

--------------------------------------------------------------------------------
   PROCEDURE removerank (itemgroupguid IN RAW, deleterank IN NUMBER)
   IS
   BEGIN
      DELETE      detailtable_consecutive
            WHERE detailtable_consecutive.itemgroup = itemgroupguid
              AND detailtable_consecutive.RANK = deleterank;

      UPDATE detailtable_consecutive
         SET detailtable_consecutive.RANK = RANK - 1,
             detailtable_consecutive.rankminusone =
                              DECODE (rankminusone,
                                      1, NULL,
                                      rankminusone - 1
                                     )
       WHERE detailtable_consecutive.RANK > deleterank
         AND detailtable_consecutive.itemgroup = itemgroupguid;
   END;
--------------------------------------------------------------------------------
END;
/

prompt Creating package body PARTIALORDER
CREATE OR REPLACE PACKAGE BODY PARTIALORDER
IS
--------------------------------------------------------------------------------
   CURSOR ranks (groupid RAW)
   IS
      SELECT        RANK, item
               FROM detailtable_disp_partial t
              WHERE t.itemgroup = groupid
           ORDER BY RANK ASC
      FOR UPDATE OF RANK;

--------------------------------------------------------------------------------
   PROCEDURE redistranksglobally (itemgroupguid IN RAW)
   IS
      index_          INTEGER;
      previousrank_   NUMBER;
      previosindex_   INTEGER;
      isfirst_        BOOLEAN;
      newrank         NUMBER (38, 0);
      n               INTEGER;
   BEGIN
      SELECT COUNT (1)
        INTO n
        FROM detailtable_disp_partial
       WHERE detailtable_disp_partial.itemgroup = itemgroupguid;

      isfirst_ := TRUE;

      FOR r IN ranks (itemgroupguid)
      LOOP
         IF isfirst_
         THEN
            index_ := 1;
            previousrank_ := r.RANK;
            previosindex_ := 1;
            isfirst_ := FALSE;
         END IF;

         IF (index_ != previosindex_ AND r.RANK = previousrank_)
         THEN
            newrank := ROUND (common.maxint / (n + 1)) * previosindex_;
         ELSE
            newrank := ROUND (common.maxint / (n + 1)) * index_;
            previosindex_ := index_;
            previousrank_ := r.RANK;
         END IF;

         UPDATE detailtable_disp_partial
            SET detailtable_disp_partial.RANK = NVL (newrank, r.RANK)
          WHERE CURRENT OF ranks;

         index_ := index_ + 1;
      END LOOP;
   END;

--------------------------------------------------------------------------------
   PROCEDURE additemtogroup (
      itemguid          IN   RAW,
      itemgroupguid     IN   RAW,
      predecessorrank   IN   INTEGER,
      allowsamerank     IN   BOOLEAN
   )
   IS
      aux          INTEGER;
      hirank       INTEGER;
      lowrank      INTEGER;
      curentrank   INTEGER;
      preditem     RAW (16);
   BEGIN
      BEGIN
         SELECT MIN (RANK)
           INTO hirank
           FROM detailtable_disp_partial
          WHERE detailtable_disp_partial.RANK > predecessorrank;

         IF hirank IS NULL
         THEN
            hirank := common.maxint;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            hirank := common.maxint;
      END;

      curentrank := ROUND (predecessorrank / 2 + hirank / 2);

      IF    curentrank = predecessorrank
         OR hirank = curentrank AND NOT allowsamerank
      THEN
         BEGIN
            SELECT detailtable_disp_partial.item
              INTO preditem
              FROM detailtable_disp_partial
             WHERE detailtable_disp_partial.itemgroup = itemgroupguid
               AND detailtable_disp_partial.RANK = predecessorrank;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               preditem := NULL;
         END;

         redistranksglobally (itemgroupguid => itemgroupguid);

         IF preditem IS NULL
         THEN
            lowrank := 1;
         ELSE
            SELECT detailtable_disp_partial.RANK
              INTO lowrank
              FROM detailtable_disp_partial
             WHERE detailtable_disp_partial.itemgroup = itemgroupguid
               AND detailtable_disp_partial.item = preditem;
         END IF;

         BEGIN
            SELECT MIN (RANK)
              INTO hirank
              FROM detailtable_disp_partial
             WHERE detailtable_disp_partial.RANK > lowrank;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               hirank := common.maxint;
         END;

         curentrank := ROUND (lowrank / 2 + hirank / 2);

         INSERT INTO detailtable_disp_partial
                     (RANK, item, itemgroup
                     )
              VALUES (curentrank, itemguid, itemgroupguid
                     );
      ELSE
         INSERT INTO detailtable_disp_partial
                     (RANK, item, itemgroup
                     )
              VALUES (curentrank, itemguid, itemgroupguid
                     );
      END IF;
   END;
--------------------------------------------------------------------------------
END;
/

prompt Creating package body PERMUTATIONS
CREATE OR REPLACE PACKAGE BODY PERMUTATIONS
IS
--------------------------------------------------------------------------------
   PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT NUMBER)
   IS
   BEGIN
      RESULT := arrangecategoryitems (initems => initems);
   END;

--------------------------------------------------------------------------------
   FUNCTION arrangecategoryitems (initems IN rawtable)
      RETURN NUMBER
   IS
      i       INTEGER;
      s       INTEGER;
      aux     RAW (16);
      r       INTEGER;
      t       INTEGER;
      f       DOUBLE PRECISION;
      items   rawtable;
   BEGIN
      items := initems;
      i := 0;
      s := 1;
      t := items.COUNT;
      r := t;
      f := 0;

      LOOP
         s := 1;
         i := 0;

         LOOP
            i := i + 1;
            EXIT WHEN i > r;

            IF items (i) > items (s)
            THEN
               s := i;
            END IF;
         END LOOP;

         aux := items (s);
         items (s) := items (r);
         items (r) := aux;
         f := r * f + s - 1;
         r := r - 1;
         EXIT WHEN r <= 1;
         NULL;
      END LOOP;

      RETURN f;
   END;

--------------------------------------------------------------------------------
   PROCEDURE reversearrangecategoryitems (
      permutation   IN              NUMBER,
      items         IN OUT NOCOPY   rawtable
   )
   IS
   BEGIN
      items := reversearrangecategoryitems (permutation, items);
   END;

--------------------------------------------------------------------------------
   FUNCTION reversearrangecategoryitems (
      permutation   IN   NUMBER,
      items         IN   rawtable
   )
      RETURN rawtable
   IS
      RESULT   rawtable;
      i        NUMBER (38, 0);
      s        NUMBER (38, 0);
      aux      RAW (16);
      r        NUMBER (38, 0);
      t        NUMBER (38, 0);
      f        NUMBER (38, 0);
   BEGIN
      RESULT := items;
      i := 0;
      s := 1;
      t := items.COUNT;
      f := permutation;
      r := 1;

      LOOP
         r := r + 1;
         EXIT WHEN r > t;
         s := MOD (f, r);
         f := FLOOR (f / r);
         aux := RESULT (s + 1);
         RESULT (s + 1) := RESULT (r);
         RESULT (r) := aux;
      END LOOP;

      LOOP
         i := i + 1;
         EXIT WHEN i > RESULT.COUNT;
      END LOOP;

      RETURN RESULT;
   END;

--------------------------------------------------------------------------------
   PROCEDURE updatepermutationforgroup (
      itemgroupguid   IN   RAW,
      itemguids       IN   rawtable
   )
   IS
      newpermutation   NUMBER (38, 0);
   BEGIN
      newpermutation := arrangecategoryitems (initems => itemguids);

      UPDATE mastertable_perm
         SET mastertable_perm.grouppermutation = newpermutation
       WHERE mastertable_perm.itemgroup = itemgroupguid;
   END;

--------------------------------------------------------------------------------
   PROCEDURE addnewitemingroup (
      itemgroupguid     IN   RAW,
      newitemguid       IN   RAW,
      itempredecessor   IN   RAW
   )
   IS
      CURSOR detailcursor (itemid RAW)
      IS
         SELECT   detailtable_perm.item
             FROM detailtable_perm
            WHERE detailtable_perm.itemgroup = itemid
         ORDER BY detailtable_perm.item ASC;

      initialtable         rawtable;
      curentindex          NUMBER (38, 0);
      initialpermutation   NUMBER (38, 0);
      i                    NUMBER (38, 0);
   BEGIN
      initialtable := NULL;
      curentindex := 0;

      IF newitemguid IS NULL
      THEN
         raise_application_error (-20455, 'No newitemguid was specified');
      END IF;

      FOR c IN detailcursor (itemgroupguid)
      LOOP
         IF initialtable IS NULL
         THEN
            curentindex := 1;
            initialtable := rawtable (c.item);
         ELSE
            initialtable.EXTEND;
            curentindex := curentindex + 1;
            initialtable (curentindex) := c.item;
         END IF;
      END LOOP;

      SELECT mastertable_perm.grouppermutation
        INTO initialpermutation
        FROM mastertable_perm
       WHERE mastertable_perm.itemgroup = itemgroupguid;

      permutations.reversearrangecategoryitems
                                           (permutation      => initialpermutation,
                                            items            => initialtable
                                           );
      i := initialtable.COUNT ();
      initialtable.EXTEND ();

      LOOP
         IF initialtable (i) = itempredecessor
         THEN
            initialtable (i + 1) := newitemguid;
            i := 0;
         ELSE
            initialtable (i + 1) := initialtable (i);
         END IF;

         i := i - 1;

         IF itempredecessor IS NULL AND i = 0
         THEN
            initialtable (1) := newitemguid;
         END IF;

         EXIT WHEN i <= 0;
      END LOOP;

      INSERT INTO detailtable_perm
                  (item, itemgroup
                  )
           VALUES (newitemguid, itemgroupguid
                  );

      updatepermutationforgroup (itemgroupguid      => itemgroupguid,
                                 itemguids          => initialtable
                                );
   END;

--------------------------------------------------------------------------------
   FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
      RETURN rawtable
   IS
      CURSOR detailcursor (itemid RAW)
      IS
         SELECT   detailtable_perm.item
             FROM detailtable_perm
            WHERE detailtable_perm.itemgroup = itemid
         ORDER BY detailtable_perm.item ASC;

      initialtable         rawtable;
      curentindex          NUMBER (38, 0);
      initialpermutation   NUMBER (38, 0);
      i                    NUMBER (38, 0);
   BEGIN
      initialtable := NULL;
      curentindex := 0;

      FOR c IN detailcursor (itemgroupguid)
      LOOP
         IF initialtable IS NULL
         THEN
            curentindex := 1;
            initialtable := rawtable (c.item);
         ELSE
            initialtable.EXTEND;
            curentindex := curentindex + 1;
            initialtable (curentindex) := c.item;
         END IF;
      END LOOP;

      SELECT mastertable_perm.grouppermutation
        INTO initialpermutation
        FROM mastertable_perm
       WHERE mastertable_perm.itemgroup = itemgroupguid;

      permutations.reversearrangecategoryitems
                                           (permutation      => initialpermutation,
                                            items            => initialtable
                                           );
      RETURN initialtable;
   END;
--------------------------------------------------------------------------------
END;
/

prompt Creating package body PERMUTATIONSWITHRAW
CREATE OR REPLACE PACKAGE BODY PERMUTATIONSWITHRAW
IS
--------------------------------------------------------------------------------
   PROCEDURE arrangecategoryitems (initems IN rawtable, RESULT OUT RAW)
   IS
   BEGIN
      RESULT := arrangecategoryitems (initems => initems);
   END;

--------------------------------------------------------------------------------
   FUNCTION arrangecategoryitems (initems IN rawtable)
      RETURN RAW
   IS
      items           rawtable;
      i               INTEGER;
      s               INTEGER;
      aux             RAW (2000);
      r               INTEGER;
      t               INTEGER;
      RESULT          RAW (2000);
      x               PLS_INTEGER;
      bitposition     PLS_INTEGER;
      bitsize         PLS_INTEGER;
      samesizecount   PLS_INTEGER;
      samesizeindex   PLS_INTEGER;
      xasraw          RAW (20);
      curent          PLS_INTEGER;
      auxitem         RAW (16);
   BEGIN
      RESULT := UTL_RAW.copies ('00', 2000);
      bitposition := 0;
      bitsize := 1;
      samesizecount := 1;
      samesizeindex := 1;
      items := initems;
      t := items.COUNT;
      r := t;

      LOOP
         s := 1;
         i := 0;

         LOOP
            i := i + 1;
            EXIT WHEN i > r;

            IF items (i) > items (s)
            THEN
               s := i;
            END IF;
         END LOOP;


         auxitem := items (s);
         items (s) := items (r);
         items (r) := auxitem;
         x := (s - 1) * POWER (2, (24 - bitsize - (bitposition MOD 8)));
         xasraw := UTL_RAW.SUBSTR (UTL_RAW.cast_from_binary_integer (x), 2, 3);
         curent := FLOOR (bitposition / 8);

         IF curent > 0
         THEN
            aux := UTL_RAW.copies ('00', curent);
         ELSE
            aux := NULL;
         END IF;

         aux := UTL_RAW.CONCAT (aux, xasraw);
         RESULT := UTL_RAW.bit_or (RESULT, aux);
         bitposition := bitposition + bitsize;
         samesizeindex := samesizeindex + 1;

         IF (samesizeindex > samesizecount)
         THEN
            BEGIN
               samesizeindex := 1;
               samesizecount := samesizecount * 2;
               bitsize := bitsize + 1;
            END;
         END IF;

         r := r - 1;
         EXIT WHEN r <= 1;
         NULL;
      END LOOP;

      RETURN RESULT;
   END;

--------------------------------------------------------------------------------
   PROCEDURE reversearrangecategoryitems (
      permutation   IN              RAW,
      items         IN OUT NOCOPY   rawtable
   )
   IS
   BEGIN
      items := reversearrangecategoryitems (permutation, items);
   END;

--------------------------------------------------------------------------------
   FUNCTION reversearrangecategoryitems (permutation IN RAW, items IN rawtable)
      RETURN rawtable
   IS
      RESULT          rawtable;
      i               INTEGER;
      s               INTEGER;
      aux             RAW (16);
      r               INTEGER;
      t               INTEGER;
      x               PLS_INTEGER;
      bitposition     PLS_INTEGER;
      bitsize         PLS_INTEGER;
      samesizecount   PLS_INTEGER;
      samesizeindex   PLS_INTEGER;
      xasraw          RAW (20);
      curent          PLS_INTEGER;
      MASK            RAW (3);

      TYPE auxvarray IS VARRAY (32000) OF INTEGER;

      cj              auxvarray;
   BEGIN
      bitposition := 0;
      bitsize := 1;
      samesizecount := 1;
      samesizeindex := 1;
      cj := NULL;
      RESULT := items;
      r := 0;

      LOOP
         r := r + 1;
         EXIT WHEN r > items.COUNT - 1;
         curent := FLOOR (bitposition / 8);
         xasraw := UTL_RAW.SUBSTR (permutation, curent + 1, 3);
         MASK :=
            CASE bitposition MOD 8
               WHEN 0
                  THEN 'FFFFFF'
               WHEN 1
                  THEN '7FFFFF'
               WHEN 2
                  THEN '3FFFFF'
               WHEN 3
                  THEN '1FFFFF'
               WHEN 4
                  THEN '0FFFFF'
               WHEN 5
                  THEN '07FFFF'
               WHEN 6
                  THEN '03FFFF'
               WHEN 7
                  THEN '01FFFF'
            END;
         xasraw := UTL_RAW.bit_and (xasraw, MASK);
         x := UTL_RAW.cast_to_binary_integer (xasraw);
         s := FLOOR (x / POWER (2, (24 - bitsize - (bitposition MOD 8))));

         IF cj IS NULL
         THEN
            cj := auxvarray (s + 1);
         ELSE
            cj.EXTEND ();
            cj (cj.COUNT) := s + 1;
         END IF;

         bitposition := bitposition + bitsize;
         samesizeindex := samesizeindex + 1;

         IF (samesizeindex > samesizecount)
         THEN
            BEGIN
               samesizeindex := 1;
               samesizecount := samesizecount * 2;
               bitsize := bitsize + 1;
            END;
         END IF;
      END LOOP;

      r := cj.COUNT () + 1;

      LOOP
         r := r - 1;
         EXIT WHEN r <= 0;
         aux := items (cj (r));
         RESULT (cj (r)) := RESULT (RESULT.COUNT - r + 1);
         RESULT (RESULT.COUNT - r + 1) := aux;
      END LOOP;

      RETURN RESULT;
   END;

--------------------------------------------------------------------------------
   PROCEDURE updatepermutationforgroup (
      itemgroupguid   IN   RAW,
      itemguids       IN   rawtable
   )
   IS
      newpermutation   RAW (2000);
   BEGIN
      newpermutation := arrangecategoryitems (initems => itemguids);

      UPDATE mastertable_perm_raw
         SET mastertable_perm_raw.grouppermutation = newpermutation
       WHERE mastertable_perm_raw.itemgroup = itemgroupguid;
   END;

--------------------------------------------------------------------------------
   PROCEDURE addnewitemingroup (
      itemgroupguid     IN   RAW,
      newitemguid       IN   RAW,
      itempredecessor   IN   RAW
   )
   IS
      CURSOR detailcursor (itemid RAW)
      IS
         SELECT   detailtable_perm_raw.item
             FROM detailtable_perm_raw
            WHERE detailtable_perm_raw.itemgroup = itemid
         ORDER BY detailtable_perm_raw.item ASC;

      initialtable         rawtable;
      curentindex          NUMBER (38, 0);
      initialpermutation   RAW (2000);
      i                    NUMBER (38, 0);
   BEGIN
      initialtable := NULL;
      curentindex := 0;

      IF newitemguid IS NULL
      THEN
         raise_application_error (-20455, 'No newitemguid was specified');
      END IF;

      FOR c IN detailcursor (itemgroupguid)
      LOOP
         IF initialtable IS NULL
         THEN
            curentindex := 1;
            initialtable := rawtable (c.item);
         ELSE
            initialtable.EXTEND;
            curentindex := curentindex + 1;
            initialtable (curentindex) := c.item;
         END IF;
      END LOOP;

      SELECT mastertable_perm_raw.grouppermutation
        INTO initialpermutation
        FROM mastertable_perm_raw
       WHERE mastertable_perm_raw.itemgroup = itemgroupguid;

      permutationswithraw.reversearrangecategoryitems
                                           (permutation      => initialpermutation,
                                            items            => initialtable
                                           );
      i := initialtable.COUNT ();
      initialtable.EXTEND ();

      LOOP
         IF initialtable (i) = itempredecessor
         THEN
            initialtable (i + 1) := newitemguid;
            i := 0;
         ELSE
            initialtable (i + 1) := initialtable (i);
         END IF;

         i := i - 1;

         IF itempredecessor IS NULL AND i = 0
         THEN
            initialtable (1) := newitemguid;
         END IF;

         EXIT WHEN i <= 0;
      END LOOP;

      INSERT INTO detailtable_perm_raw
                  (item, itemgroup
                  )
           VALUES (newitemguid, itemgroupguid
                  );

      updatepermutationforgroup (itemgroupguid      => itemgroupguid,
                                 itemguids          => initialtable
                                );
   END;

--------------------------------------------------------------------------------
   FUNCTION retriveitemsorderedforgroup (itemgroupguid IN RAW)
      RETURN rawtable
   IS
      CURSOR detailcursor (itemid RAW)
      IS
         SELECT   detailtable_perm_raw.item
             FROM detailtable_perm_raw
            WHERE detailtable_perm_raw.itemgroup = itemid
         ORDER BY detailtable_perm_raw.item ASC;

      initialtable         rawtable;
      curentindex          NUMBER (38, 0);
      initialpermutation   RAW (2000);
      i                    NUMBER (38, 0);
   BEGIN
      initialtable := NULL;
      curentindex := 0;

      FOR c IN detailcursor (itemgroupguid)
      LOOP
         IF initialtable IS NULL
         THEN
            curentindex := 1;
            initialtable := rawtable (c.item);
         ELSE
            initialtable.EXTEND;
            curentindex := curentindex + 1;
            initialtable (curentindex) := c.item;
         END IF;
      END LOOP;

      SELECT mastertable_perm_raw.grouppermutation
        INTO initialpermutation
        FROM mastertable_perm_raw
       WHERE mastertable_perm_raw.itemgroup = itemgroupguid;

      permutationswithraw.reversearrangecategoryitems
                                           (permutation      => initialpermutation,
                                            items            => initialtable
                                           );
      RETURN initialtable;
   END;
--------------------------------------------------------------------------------
END;
/

prompt Creating package body TOTALORDER
CREATE OR REPLACE PACKAGE BODY TOTALORDER
IS
   CURSOR ranks (groupid RAW)
   IS
      SELECT        RANK, item, ROWNUM currentindex
               FROM detailtable_disp_total t
              WHERE t.itemgroup = groupid
           ORDER BY t.RANK ASC
      FOR UPDATE OF t.RANK;

--------------------------------------------------------------------------------
   PROCEDURE redistranksglobally (itemgroupguid IN RAW)
   IS
      n         NUMBER (38, 0);
      f         NUMBER (38, 0);
      ceils     NUMBER (38, 0);
      newrank   NUMBER (38, 0);
   BEGIN
      SELECT COUNT (1)
        INTO n
        FROM detailtable_disp_total
       WHERE detailtable_disp_total.itemgroup = itemgroupguid;

      IF n > 0
      THEN
         f := FLOOR (common.maxint / (n + 1));
         ceils := common.maxint - f * (n + 1);
         newrank := 0;

         FOR r IN ranks (itemgroupguid)
         LOOP
            newrank := newrank + f;

            IF ceils > 0
            THEN
               newrank := newrank + 1;
               ceils := ceils - 1;
            END IF;

            UPDATE detailtable_disp_total
               SET detailtable_disp_total.RANK = newrank
             WHERE CURRENT OF ranks;
         END LOOP;
      END IF;
   END;

--------------------------------------------------------------------------------
   PROCEDURE additemtogroup (
      itemguid          IN   RAW,
      itemgroupguid     IN   RAW,
      predecessorrank   IN   NUMBER
   )
   IS
      aux             NUMBER (38, 0);
      hirank          NUMBER (38, 0);
      lowrank         NUMBER (38, 0);
      currentrank     NUMBER (38, 0);
      preditem        RAW (16);
      localpredrank   NUMBER (38, 0);
   BEGIN
      SELECT COUNT (1)
        INTO aux
        FROM detailtable_disp_total
       WHERE detailtable_disp_total.itemgroup = itemgroupguid;

      IF aux = 0
      THEN
         currentrank := ROUND (common.maxint / 2);
      ELSE
         BEGIN
            SELECT MAX (RANK)
              INTO localpredrank
              FROM detailtable_disp_total
             WHERE detailtable_disp_total.itemgroup = itemgroupguid
               AND RANK <= predecessorrank;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               localpredrank := NULL;
         END;

         -- 1 <= localPredRank <= maxint or localPredRank is null
         IF localpredrank IS NULL
         THEN
            SELECT MIN (RANK)
              INTO localpredrank
              FROM detailtable_disp_total
             WHERE detailtable_disp_total.itemgroup = itemgroupguid;

            -- localPredRank is not null any longer
            IF localpredrank = 1
            THEN
               currentrank := NULL;
            ELSE
               currentrank := ROUND (localpredrank / 2);
            END IF;
         ELSE
            -- there exists 1 <= localPredRank <= maxint
            BEGIN
               SELECT MIN (RANK)
                 INTO hirank
                 FROM detailtable_disp_total
                WHERE detailtable_disp_total.itemgroup = itemgroupguid
                  AND detailtable_disp_total.RANK > localpredrank;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  hirank := NULL;
            END;

            -- hirank is null or hirank > localpredrank
            IF hirank IS NULL
            THEN
               -- localPredRank is already the greatest rank
               IF localpredrank = common.maxint
               THEN
                  currentrank := NULL;
               ELSE
                  currentrank :=
                       localpredrank
                     + ROUND ((common.maxint - localpredrank) / 2);
               END IF;
            ELSE
               -- we need to insert between predrank and hirank
               IF hirank - localpredrank = 1
               THEN
                  currentrank := NULL;
               ELSE
                  currentrank :=
                         localpredrank + ROUND ((hirank - localpredrank) / 2);
               END IF;
            END IF;
         END IF;
      END IF;

      IF currentrank IS NULL
      THEN
         SELECT detailtable_disp_total.item
           INTO preditem
           FROM detailtable_disp_total
          WHERE detailtable_disp_total.itemgroup = itemgroupguid
            AND detailtable_disp_total.RANK = localpredrank;

         redistranksglobally (itemgroupguid => itemgroupguid);

         SELECT detailtable_disp_total.RANK
           INTO lowrank
           FROM detailtable_disp_total
          WHERE detailtable_disp_total.itemgroup = itemgroupguid
            AND detailtable_disp_total.item = preditem;

         SELECT detailtable_disp_total.RANK
           INTO lowrank
           FROM detailtable_disp_total
          WHERE detailtable_disp_total.itemgroup = itemgroupguid
            AND detailtable_disp_total.item = preditem;

         BEGIN
            SELECT MIN (RANK)
              INTO hirank
              FROM detailtable_disp_total
             WHERE detailtable_disp_total.RANK > lowrank;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               hirank := common.maxint;
         END;

         currentrank := lowrank + ROUND ((hirank - lowrank) / 2);

         IF currentrank = lowrank OR currentrank = hirank
         THEN
            raise_application_error (-20101, 'too many items');
         END IF;
      END IF;

      INSERT INTO detailtable_disp_total
                  (RANK, item, itemgroup
                  )
           VALUES (currentrank, itemguid, itemgroupguid
                  );
   END;
--------------------------------------------------------------------------------
END;
/

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
PROMPT Loading test data;
PROMPT Loading MasterTable
INSERT INTO mastertable
     VALUES ('2D3D12F50BB64845A582CC65FB7D0228');


INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (3, '1D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (5, '2D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (7, '3D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (7, '4D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (7, '5D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (10, '6D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (11, '7D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_partial
            (RANK, item,
             itemgroup
            )
     VALUES (14, '8D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
---

INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (3, '1D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (5, '2D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (7, '3D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (8, '4D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (9, '5D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (10, '6D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (11, '7D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_disp_total
            (RANK, item,
             itemgroup
            )
     VALUES (14, '8D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
---

INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (1, NULL, '1D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (2, 1, '2D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (3, 2, '3D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (4, 3, '4D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (5, 4, '5D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (6, 5, '6D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (7, 6, '7D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO detailtable_consecutive
            (RANK, rankminusone, item,
             itemgroup
            )
     VALUES (8, 7, '8D3D12F50BB64845A582CC65FB7D0228',
             '2D3D12F50BB64845A582CC65FB7D0228'
            );
COMMIT ;
PROMPT Loading Master Tables for permutations;
INSERT INTO mastertable_perm
            (itemgroup
            )
     VALUES ('3D3D12F50BB64845A582CC65FB7D0228'
            );
INSERT INTO mastertable_perm_raw
            (itemgroup
            )
     VALUES ('4D3D12F50BB64845A582CC65FB7D0228'
            );
COMMIT ;
PROMPT Loading Detail Tables for permutations and testing.. ;

DECLARE
   a       rawtable;
   perm1   rawtable;
   perm2   rawtable;
   i       INTEGER;
BEGIN
-- Please note the order (F,E,D,C,B,A,9,8)
   a :=
      rawtable ('FD3D12F50BB64845A582CC65FB7D0228',
                'ED3D12F50BB64845A582CC65FB7D0228',
                'DD3D12F50BB64845A582CC65FB7D0228',
                'CD3D12F50BB64845A582CC65FB7D0228',
                'BD3D12F50BB64845A582CC65FB7D0228',
                'AD3D12F50BB64845A582CC65FB7D0228',
                '9D3D12F50BB64845A582CC65FB7D0228',
                '8D3D12F50BB64845A582CC65FB7D0228'
               );
   FORALL i IN 1 .. a.COUNT
      INSERT INTO detailtable_perm
                  (itemgroup, item
                  )
           VALUES ('3D3D12F50BB64845A582CC65FB7D0228', a (i)
                  );
   permutations.updatepermutationforgroup
                         (itemgroupguid      => '3D3D12F50BB64845A582CC65FB7D0228',
                          itemguids          => a
                         );
-- Add another value
   permutations.addnewitemingroup
                        (itemgroupguid        => '3D3D12F50BB64845A582CC65FB7D0228',
                         newitemguid          => '7D3D12F50BB64845A582CC65FB7D0228',
                         itempredecessor      => '8D3D12F50BB64845A582CC65FB7D0228'
                        );
   FORALL i IN 1 .. a.COUNT
      INSERT INTO detailtable_perm_raw
                  (itemgroup, item
                  )
           VALUES ('4D3D12F50BB64845A582CC65FB7D0228', a (i)
                  );
   permutationswithraw.updatepermutationforgroup
                         (itemgroupguid      => '4D3D12F50BB64845A582CC65FB7D0228',
                          itemguids          => a
                         );
-- Add another value
   permutationswithraw.addnewitemingroup
                        (itemgroupguid        => '4D3D12F50BB64845A582CC65FB7D0228',
                         newitemguid          => '7D3D12F50BB64845A582CC65FB7D0228',
                         itempredecessor      => '8D3D12F50BB64845A582CC65FB7D0228'
                        );
   a.EXTEND ();
   a (9) := '7D3D12F50BB64845A582CC65FB7D0228';
   -- Test the order..
   perm1 :=
      permutations.retriveitemsorderedforgroup
                          (itemgroupguid      => '3D3D12F50BB64845A582CC65FB7D0228');
   perm2 :=
      permutationswithraw.retriveitemsorderedforgroup
                          (itemgroupguid      => '4D3D12F50BB64845A582CC65FB7D0228');
   DBMS_OUTPUT.put_line ('Size of initial table :' || a.COUNT);
   DBMS_OUTPUT.put_line ('Size of perm1   table :' || perm1.COUNT);
   DBMS_OUTPUT.put_line ('Size of perm2   table :' || perm2.COUNT);
   DBMS_OUTPUT.put_line
      ('LINE        Initial table                        Perm1 Table                           Perm2 Table       '
      );
   i := 1;

   WHILE i <= a.COUNT
   LOOP
      DBMS_OUTPUT.put_line (   i
                            || '     '
                            || a (i)
                            || '  '
                            || perm1 (i)
                            || '  '
                            || perm2 (i)
                           );
      i := i + 1;
   END LOOP;
END;
/

COMMIT ;
SPOOL off;
PROMPT Loading Done..

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Authors

Daniel Aioanei
Web Developer
Romania Romania
No Biography provided

Adi Malinaru
Software Developer (Senior)
Romania Romania
No Biography provided

No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150327.1 | Last Updated 5 May 2004
Article Copyright 2004 by Daniel Aioanei, Adi Malinaru, Andrei Postolache
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid