Re: �

[Андрій Жук]

Ну, код не особо прчесан.
Вот кусок.
Бюджеты и статьи

CREATE TABLE LINE (
     MEM_ID        IDENTIFIER NOT NULL /* IDENTIFIER = INTEGER NOT NULL */,
     MEM_NAME      NAME NOT NULL /* NAME = VARCHAR(82) DEFAULT 'unknown' 
NOT NULL */,
     MEM_DESC      DESCRIPTION /* DESCRIPTION = VARCHAR(255) */,
     MEM_TYPE      BOOLEAN NOT NULL /* BOOLEAN = VARCHAR(1) DEFAULT 'U' 
NOT NULL CHECK (value in ('Y','N','U')) */,
     LAST_UPDATED  TIMESTAMP NOT NULL
);

CREATE TABLE LINE_SET (
     SET_ID        IDENTIFIER NOT NULL /* IDENTIFIER = INTEGER NOT NULL */,
     SET_NAME      NAME NOT NULL /* NAME = VARCHAR(82) DEFAULT 'unknown' 
NOT NULL */,
     SET_DESC      DESCRIPTION /* DESCRIPTION = VARCHAR(255) */,
     LAST_UPDATED  TIMESTAMP NOT NULL
);

CREATE TABLE LINE_TREE (
     SET_ID     IDENTIFIER NOT NULL /* IDENTIFIER = INTEGER NOT NULL */,
     MEM_ID     IDENTIFIER NOT NULL /* IDENTIFIER = INTEGER NOT NULL */,
     MEM_PID    NONIDENTIFIER /* NONIDENTIFIER = INTEGER */,
     MEM_LEVEL  NONIDENTIFIER /* NONIDENTIFIER = INTEGER */,
     IS_LEAF    NONIDENTIFIER /* NONIDENTIFIER = INTEGER */,
     MEM_ORDER  IDENTIFIER NOT NULL /* IDENTIFIER = INTEGER NOT NULL */
);

CREATE PROCEDURE REORDER_LINETREE (
     IN_SET_ID INTEGER)
AS
DECLARE VARIABLE LMEM_ID INTEGER;
DECLARE VARIABLE LMEM_LEVEL INTEGER;
DECLARE VARIABLE LIS_LEAF INTEGER;
begin
    for select mem_id,outlevel from GET_TREE_LINE(:IN_SET_ID,-1,0) order 
by mem_order into :lmem_id,:lmem_level do
     begin
     if (exists(select 1 from LINE_tree a where
      a.set_id=:in_set_id and a.mem_pid=:lmem_id))then
       LIS_LEAF=0;
      else
       LIS_LEAF=1;

       update LINE_TREE set MEM_LEVEL=:lmem_level, IS_LEAF=:lis_leaf 
where SET_ID=:IN_SET_ID and MEM_ID=:lmem_ID;
     end
end

CREATE PROCEDURE GET_ALL_LINE_FATHER (
     INMEM_ID INTEGER,
     INSET_ID INTEGER,
     IN_LEVEL INTEGER)
RETURNS (
     MEM_PID INTEGER,
     OUT_LEVEL INTEGER)
AS
begin
    MEM_PID = :INMEM_ID;
    OUT_LEVEL = :IN_LEVEL;
    while (exists(select a.mem_pid from line_tree a where 
a.mem_id=:MEM_PID and a.set_id=:INSET_ID)) do
     begin
      select a.mem_pid from line_tree a where a.mem_id=:MEM_PID and 
a.set_id=:INSET_ID into MEM_PID;
      OUT_LEVEL = :OUT_LEVEL + 1;
      suspend;
     end
end

CREATE PROCEDURE GET_SUB_TREE_LINE (
     INSET_ID INTEGER,
     INMEM_PID INTEGER,
     INLEVEL INTEGER,
     REORDER_TREE INTEGER = 0)
RETURNS (
     MEM_PID INTEGER,
     MEM_ID INTEGER,
     OUTLEVEL INTEGER,
     IS_LEAF INTEGER,
     MEM_ORDER INTEGER)
AS
begin
    select count(1)+coalesce(:reorder_tree,0) from line_tree lt
    where lt.mem_level is null and lt.set_id=:inset_id into reorder_tree;

    if (reorder_tree>0) then
      execute procedure reorder_linetree(:inset_id);
    for select a.mem_pid, a.mem_id, a.mem_level, a.is_leaf, a.mem_order
        from line_tree a
    left join GET_ALL_LINE_FATHER(a.mem_id,:inset_id,0) c on 1=1
    where a.set_id=:inset_id
    and c.MEM_PID=:INMEM_PID
    order by mem_order
    into
     :mem_pid,
     :mem_id,
     :outlevel,
     :is_leaf,
     :mem_order
     do
      begin
        if (:mem_pid=INMEM_PID) then
          mem_pid = NULL;
        suspend;
      end
end

отакое


--~--~---------~--~----~------------~-------~--~----~
-~----------~----~----~----~------~----~------~--~---