From:
                                                                       16:55
Subject: RE: hierarchical data constraint and stored
procedure..To: INTERBASE@linux.mers.com

I solved this a very long time ago [but I also had to get some help],
and I seem to remember that correct indexing was crucial - but once I
had that, populating the display results was very fast off a huge
Hierarchy table.  Somebody suggested that you have to normalise and I
agree.

/* Table: NAMES, Owner: SYSDBA */
CREATE TABLE NAMES (IDCODE CODE NOT NULL,
        NAME VARCHAR(60));

CREATE TABLE HIERARCHY
    (IDCODE CODE NOT NULL,
     HIORDER SORTORDER NOT NULL,
     PARENTIDCODE CODE NOT NULL);

CREATE INDEX HIERARCHYCODE
ON HIERARCHY (IDCODE);

COMMIT;

CREATE UNIQUE INDEX HIERARCHYPARENT
ON HIERARCHY (PARENTIDCODE,
IDCODE, HIORDER);

COMMIT;
----------------------
/* Table: ANCESTOR, Owner: SYSDBA */
CREATE TABLE ANCESTOR (IDCODE CODE NOT NULL,
        PARENTIDCODE CODE NOT NULL);

CREATE INDEX ANCESTORPARENT ON ANCESTOR (PARENTIDCODE);
COMMIT;
-------------------

Using the following sql's I can populate parents and children for any
given ID.  Supplying IDCODE would be application specific.  The
HIORDER field is used to tell the application how to display the
results to the user but you may not need it.  I don't know if you need
a LEVEL field at all.

/*  Get the children */

select names.name name,
hierarchy.idcode idcode,
hierarchy.hiorder hiorder
from names, hierarchy
where names.idcode = hierarchy.idcode
and hierarchy.parentidcode = :idcode
order by hierarchy.hiorder

/* Get the parents */
Select Names.Name Name,
Hierarchy.parentidcode idcode
from Names, Hierarchy
where Hierarchy.idcode = :idcode
order by names.name

This method was very very quick on a very very large hierarchy table -
in this instance there wasn't the strict parent - children
relationship that you describe in point (1.).

In order to solve the other problem you need something like this?:-

create procedure GetFamily (Idcode integer)
   returns (IdCode integer,
            Level integer,
            Name varchar(..),  etc)
as
declare variable ParentId integer;
begin
/* Parent, level 0 */
select N.Idcode, O, N.names, from Names N, Hierarchy H
  where N.Idcode = H.ParentIdCode
  and H.Idcode = :Idcode
  into :Idcode, :Level, :Name;
suspend;
/* Child, level 1' */
    ParentId = Idcode;
    for select N.Idcode, 1, N.name
    from  Names N, Hierarchy H
      where N.Idcode = H.Idcode
      and H.Idcode = :ParentIdCode
      order by whatever
      into :Idcode, :Level, :Name,
       do suspend;

/*     and so on .......  */

  end
end;

Or something like that.

There's bound to be a better way - Diane will know.

Jo Jenkinson
Ghana
joj@africaonline.com.gh
I am a programmer - I don't do relationships.

Phil wrote:-
>
> Hi,
>
> Been scratching my head over two related problems today,
> and have got to the stage where I can't see for looking :-)
>
<snipped - the problem>

************************************************************************
* This message came from the list servers at http://www.mers.com       *
* To remove yourself from the list: send an email to listproc@mers.com *
* in the body of the message put: unsubscribe INTERBASE                *
* InterBase Search Engine http://www.mers.com/searchsite.html          *
* InterBase FAQ http://www.mers.com/faqinterbase.html                  *
************************************************************************